1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
|
CREATE TABLE IF NOT EXISTS files ( id TEXT PRIMARY KEY, value TEXT, metadata TEXT NOT NULL, file_name TEXT, file_type TEXT, file_size TEXT, upload_ip TEXT, upload_address TEXT, list_type TEXT, timestamp INTEGER, label TEXT, directory TEXT, channel TEXT, channel_name TEXT, tg_file_id TEXT, tg_chat_id TEXT, tg_bot_token TEXT, is_chunked BOOLEAN DEFAULT FALSE, tags TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE IF NOT EXISTS settings ( key TEXT PRIMARY KEY, value TEXT NOT NULL, category TEXT, description TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE IF NOT EXISTS index_operations ( id TEXT PRIMARY KEY, type TEXT NOT NULL, timestamp INTEGER NOT NULL, data TEXT NOT NULL, processed BOOLEAN DEFAULT FALSE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE IF NOT EXISTS index_metadata ( key TEXT PRIMARY KEY, last_updated INTEGER, total_count INTEGER DEFAULT 0, last_operation_id TEXT, chunk_count INTEGER DEFAULT 0, chunk_size INTEGER DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE IF NOT EXISTS other_data ( key TEXT PRIMARY KEY, value TEXT NOT NULL, type TEXT, description TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP );
CREATE INDEX IF NOT EXISTS idx_files_timestamp ON files(timestamp DESC); CREATE INDEX IF NOT EXISTS idx_files_directory ON files(directory); CREATE INDEX IF NOT EXISTS idx_files_channel ON files(channel); CREATE INDEX IF NOT EXISTS idx_files_file_type ON files(file_type); CREATE INDEX IF NOT EXISTS idx_files_upload_ip ON files(upload_ip); CREATE INDEX IF NOT EXISTS idx_files_created_at ON files(created_at DESC); CREATE INDEX IF NOT EXISTS idx_files_tags ON files(tags);
CREATE INDEX IF NOT EXISTS idx_settings_category ON settings(category);
CREATE INDEX IF NOT EXISTS idx_index_operations_timestamp ON index_operations(timestamp); CREATE INDEX IF NOT EXISTS idx_index_operations_processed ON index_operations(processed); CREATE INDEX IF NOT EXISTS idx_index_operations_type ON index_operations(type);
CREATE INDEX IF NOT EXISTS idx_other_data_type ON other_data(type);
CREATE TRIGGER IF NOT EXISTS update_files_updated_at AFTER UPDATE ON files BEGIN UPDATE files SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id; END;
CREATE TRIGGER IF NOT EXISTS update_settings_updated_at AFTER UPDATE ON settings BEGIN UPDATE settings SET updated_at = CURRENT_TIMESTAMP WHERE key = NEW.key; END;
CREATE TRIGGER IF NOT EXISTS update_index_metadata_updated_at AFTER UPDATE ON index_metadata BEGIN UPDATE index_metadata SET updated_at = CURRENT_TIMESTAMP WHERE key = NEW.key; END;
CREATE TRIGGER IF NOT EXISTS update_other_data_updated_at AFTER UPDATE ON other_data BEGIN UPDATE other_data SET updated_at = CURRENT_TIMESTAMP WHERE key = NEW.key; END;
INSERT OR REPLACE INTO index_metadata (key, last_updated, total_count, last_operation_id) VALUES ('main_index', 0, 0, NULL);
|