diff --git a/native/cpp/CommonCpp/DatabaseManagers/CMakeLists.txt b/native/cpp/CommonCpp/DatabaseManagers/CMakeLists.txt --- a/native/cpp/CommonCpp/DatabaseManagers/CMakeLists.txt +++ b/native/cpp/CommonCpp/DatabaseManagers/CMakeLists.txt @@ -8,6 +8,7 @@ "DatabaseQueryExecutor.h" "SQLiteQueryExecutor.h" "SQLiteUtils.h" + "SQLiteSchema.h" "SQLiteConnectionManager.h" "NativeSQLiteConnectionManager.h" "entities/SQLiteStatementWrapper.h" @@ -25,6 +26,8 @@ set(DBM_SRCS "SQLiteQueryExecutor.cpp" "SQLiteUtils.cpp" + "SQLiteSchema.cpp" + "SQLiteSchemaMigrations.cpp" "SQLiteConnectionManager.cpp" "NativeSQLiteConnectionManager.cpp" "entities/SQLiteDataConverters.cpp" diff --git a/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp b/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp --- a/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp +++ b/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp @@ -1,5 +1,6 @@ #include "SQLiteQueryExecutor.h" #include "Logger.h" +#include "SQLiteSchema.h" #include "SQLiteUtils.h" #include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h" @@ -57,998 +58,6 @@ SQLiteConnectionManager SQLiteQueryExecutor::connectionManager; #endif -bool create_table(sqlite3 *db, std::string query, std::string tableName) { - char *error; - sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error creating '" << tableName << "' table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_drafts_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS drafts (threadID TEXT UNIQUE PRIMARY KEY, " - "text TEXT);"; - return create_table(db, query, "drafts"); -} - -bool rename_threadID_to_key(sqlite3 *db) { - sqlite3_stmt *key_column_stmt; - sqlite3_prepare_v2( - db, - "SELECT name AS col_name FROM pragma_table_xinfo ('drafts') WHERE " - "col_name='key';", - -1, - &key_column_stmt, - nullptr); - sqlite3_step(key_column_stmt); - - auto num_bytes = sqlite3_column_bytes(key_column_stmt, 0); - sqlite3_finalize(key_column_stmt); - if (num_bytes) { - return true; - } - - char *error; - sqlite3_exec( - db, - "ALTER TABLE drafts RENAME COLUMN `threadID` TO `key`;", - nullptr, - nullptr, - &error); - if (error) { - std::ostringstream stringStream; - stringStream << "Error occurred renaming threadID column in drafts table " - << "to key: " << error; - Logger::log(stringStream.str()); - sqlite3_free(error); - return false; - } - return true; -} - -bool create_persist_account_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS olm_persist_account(" - "id INTEGER UNIQUE PRIMARY KEY NOT NULL, " - "account_data TEXT NOT NULL);"; - return create_table(db, query, "olm_persist_account"); -} - -bool create_persist_sessions_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS olm_persist_sessions(" - "target_user_id TEXT UNIQUE PRIMARY KEY NOT NULL, " - "session_data TEXT NOT NULL);"; - return create_table(db, query, "olm_persist_sessions"); -} - -bool drop_messages_table(sqlite3 *db) { - char *error; - sqlite3_exec(db, "DROP TABLE IF EXISTS messages;", nullptr, nullptr, &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error dropping 'messages' table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool recreate_messages_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS messages ( " - "id TEXT UNIQUE PRIMARY KEY NOT NULL, " - "local_id TEXT, " - "thread TEXT NOT NULL, " - "user TEXT NOT NULL, " - "type INTEGER NOT NULL, " - "future_type INTEGER, " - "content TEXT, " - "time INTEGER NOT NULL);"; - return create_table(db, query, "messages"); -} - -bool create_messages_idx_thread_time(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "CREATE INDEX IF NOT EXISTS messages_idx_thread_time " - "ON messages (thread, time);", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error creating (thread, time) index on messages table: " - << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_messages_idx_target_message_type_time(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "ALTER TABLE messages " - " ADD COLUMN target_message TEXT " - " AS (IIF( " - " JSON_VALID(content), " - " JSON_EXTRACT(content, '$.targetMessageID'), " - " NULL " - " )); " - "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time " - " ON messages (target_message, type, time);", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream - << "Error creating (target_message, type, time) index on messages table: " - << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool update_messages_idx_target_message_type_time(sqlite3 *db) { - char *error; - int sidebarSourceTypeInt = static_cast(MessageType::SIDEBAR_SOURCE); - std::string sidebarSourceType = std::to_string(sidebarSourceTypeInt); - - auto query = - "DROP INDEX IF EXISTS messages_idx_target_message_type_time;" - "ALTER TABLE messages DROP COLUMN target_message;" - "ALTER TABLE messages " - " ADD COLUMN target_message TEXT " - " AS (IIF(" - " JSON_VALID(content)," - " COALESCE(" - " JSON_EXTRACT(content, '$.targetMessageID')," - " IIF(" - " type = " + - sidebarSourceType + - " , JSON_EXTRACT(content, '$.id')," - " NULL" - " )" - " )," - " NULL" - " ));" - "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time " - " ON messages (target_message, type, time);"; - - sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream - << "Error creating (target_message, type, time) index on messages table: " - << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_media_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS media ( " - "id TEXT UNIQUE PRIMARY KEY NOT NULL, " - "container TEXT NOT NULL, " - "thread TEXT NOT NULL, " - "uri TEXT NOT NULL, " - "type TEXT NOT NULL, " - "extras TEXT NOT NULL);"; - return create_table(db, query, "media"); -} - -bool create_media_idx_container(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "CREATE INDEX IF NOT EXISTS media_idx_container " - "ON media (container);", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error creating (container) index on media table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_threads_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS threads ( " - "id TEXT UNIQUE PRIMARY KEY NOT NULL, " - "type INTEGER NOT NULL, " - "name TEXT, " - "description TEXT, " - "color TEXT NOT NULL, " - "creation_time BIGINT NOT NULL, " - "parent_thread_id TEXT, " - "containing_thread_id TEXT, " - "community TEXT, " - "members TEXT NOT NULL, " - "roles TEXT NOT NULL, " - "current_user TEXT NOT NULL, " - "source_message_id TEXT, " - "replies_count INTEGER NOT NULL);"; - return create_table(db, query, "threads"); -} - -bool update_threadID_for_pending_threads_in_drafts(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "UPDATE drafts SET key = " - "REPLACE(REPLACE(REPLACE(REPLACE(key, 'type4/', '')," - "'type5/', ''),'type6/', ''),'type7/', '')" - "WHERE key LIKE 'pending/%'", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error update pending threadIDs on drafts table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool enable_write_ahead_logging_mode(sqlite3 *db) { - char *error; - sqlite3_exec(db, "PRAGMA journal_mode=wal;", nullptr, nullptr, &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error enabling write-ahead logging mode: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_metadata_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS metadata ( " - "name TEXT UNIQUE PRIMARY KEY NOT NULL, " - "data TEXT);"; - return create_table(db, query, "metadata"); -} - -bool add_not_null_constraint_to_drafts(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "CREATE TABLE IF NOT EXISTS temporary_drafts (" - "key TEXT UNIQUE PRIMARY KEY NOT NULL, " - "text TEXT NOT NULL);" - "INSERT INTO temporary_drafts SELECT * FROM drafts " - "WHERE key IS NOT NULL AND text IS NOT NULL;" - "DROP TABLE drafts;" - "ALTER TABLE temporary_drafts RENAME TO drafts;", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error adding NOT NULL constraint to drafts table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool add_not_null_constraint_to_metadata(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "CREATE TABLE IF NOT EXISTS temporary_metadata (" - "name TEXT UNIQUE PRIMARY KEY NOT NULL, " - "data TEXT NOT NULL);" - "INSERT INTO temporary_metadata SELECT * FROM metadata " - "WHERE data IS NOT NULL;" - "DROP TABLE metadata;" - "ALTER TABLE temporary_metadata RENAME TO metadata;", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error adding NOT NULL constraint to metadata table: " - << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool add_avatar_column_to_threads_table(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "ALTER TABLE threads ADD COLUMN avatar TEXT;", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error adding avatar column to threads table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool add_pinned_count_column_to_threads(sqlite3 *db) { - sqlite3_stmt *pinned_column_stmt; - sqlite3_prepare_v2( - db, - "SELECT name AS col_name FROM pragma_table_xinfo ('threads') WHERE " - "col_name='pinned_count';", - -1, - &pinned_column_stmt, - nullptr); - sqlite3_step(pinned_column_stmt); - - auto num_bytes = sqlite3_column_bytes(pinned_column_stmt, 0); - sqlite3_finalize(pinned_column_stmt); - - if (num_bytes) { - return true; - } - - char *error; - sqlite3_exec( - db, - "ALTER TABLE threads ADD COLUMN pinned_count INTEGER NOT NULL DEFAULT 0;", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error adding pinned_count column to threads table: " - << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_message_store_threads_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS message_store_threads (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " start_reached INTEGER NOT NULL," - " last_navigated_to BIGINT NOT NULL," - " last_pruned BIGINT NOT NULL" - ");"; - return create_table(db, query, "message_store_threads"); -} - -bool create_reports_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS reports (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " report TEXT NOT NULL" - ");"; - return create_table(db, query, "reports"); -} - -bool create_persist_storage_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS persist_storage (" - " key TEXT UNIQUE PRIMARY KEY NOT NULL," - " item TEXT NOT NULL" - ");"; - return create_table(db, query, "persist_storage"); -} - -bool recreate_message_store_threads_table(sqlite3 *db) { - char *errMsg = 0; - - // 1. Create table without `last_navigated_to` or `last_pruned`. - std::string create_new_table_query = - "CREATE TABLE IF NOT EXISTS temp_message_store_threads (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " start_reached INTEGER NOT NULL" - ");"; - - if (sqlite3_exec(db, create_new_table_query.c_str(), NULL, NULL, &errMsg) != - SQLITE_OK) { - Logger::log( - "Error creating temp_message_store_threads: " + std::string{errMsg}); - sqlite3_free(errMsg); - return false; - } - - // 2. Dump data from existing `message_store_threads` table into temp table. - std::string copy_data_query = - "INSERT INTO temp_message_store_threads (id, start_reached)" - "SELECT id, start_reached FROM message_store_threads;"; - - if (sqlite3_exec(db, copy_data_query.c_str(), NULL, NULL, &errMsg) != - SQLITE_OK) { - Logger::log( - "Error dumping data from existing message_store_threads to " - "temp_message_store_threads: " + - std::string{errMsg}); - sqlite3_free(errMsg); - return false; - } - - // 3. Drop the existing `message_store_threads` table. - std::string drop_old_table_query = "DROP TABLE message_store_threads;"; - - if (sqlite3_exec(db, drop_old_table_query.c_str(), NULL, NULL, &errMsg) != - SQLITE_OK) { - Logger::log( - "Error dropping message_store_threads table: " + std::string{errMsg}); - sqlite3_free(errMsg); - return false; - } - - // 4. Rename the temp table back to `message_store_threads`. - std::string rename_table_query = - "ALTER TABLE temp_message_store_threads RENAME TO message_store_threads;"; - - if (sqlite3_exec(db, rename_table_query.c_str(), NULL, NULL, &errMsg) != - SQLITE_OK) { - Logger::log( - "Error renaming temp_message_store_threads to message_store_threads: " + - std::string{errMsg}); - sqlite3_free(errMsg); - return false; - } - - return true; -} - -bool create_users_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS users (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " user_info TEXT NOT NULL" - ");"; - return create_table(db, query, "users"); -} - -bool create_keyservers_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS keyservers (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " keyserver_info TEXT NOT NULL" - ");"; - return create_table(db, query, "keyservers"); -} - -bool enable_rollback_journal_mode(sqlite3 *db) { - char *error; - sqlite3_exec(db, "PRAGMA journal_mode=DELETE;", nullptr, nullptr, &error); - - if (!error) { - return true; - } - - std::stringstream error_message; - error_message << "Error disabling write-ahead logging mode: " << error; - Logger::log(error_message.str()); - sqlite3_free(error); - return false; -} - -bool create_communities_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS communities (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " community_info TEXT NOT NULL" - ");"; - return create_table(db, query, "communities"); -} - -bool create_messages_to_device_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS messages_to_device (" - " message_id TEXT NOT NULL," - " device_id TEXT NOT NULL," - " user_id TEXT NOT NULL," - " timestamp BIGINT NOT NULL," - " plaintext TEXT NOT NULL," - " ciphertext TEXT NOT NULL," - " PRIMARY KEY (message_id, device_id)" - ");" - - "CREATE INDEX IF NOT EXISTS messages_to_device_idx_id_timestamp" - " ON messages_to_device (device_id, timestamp);"; - - return create_table(db, query, "messages_to_device"); -} - -bool create_integrity_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS integrity_store (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " thread_hash TEXT NOT NULL" - ");"; - return create_table(db, query, "integrity_store"); -} - -bool create_synced_metadata_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS synced_metadata (" - " name TEXT UNIQUE PRIMARY KEY NOT NULL," - " data TEXT NOT NULL" - ");"; - return create_table(db, query, "synced_metadata"); -} - -bool create_keyservers_synced(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS keyservers_synced (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " keyserver_info TEXT NOT NULL" - ");"; - bool success = create_table(db, query, "keyservers_synced"); - if (!success) { - return false; - } - - std::string copyData = - "INSERT INTO keyservers_synced (id, keyserver_info)" - "SELECT id, keyserver_info " - "FROM keyservers;"; - - char *error; - sqlite3_exec(db, copyData.c_str(), nullptr, nullptr, &error); - if (error) { - return false; - } - - return true; -} - -bool create_aux_user_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS aux_users (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " aux_user_info TEXT NOT NULL" - ");"; - return create_table(db, query, "aux_users"); -} - -bool add_version_column_to_olm_persist_sessions_table(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "ALTER TABLE olm_persist_sessions " - " RENAME COLUMN `target_user_id` TO `target_device_id`; " - "ALTER TABLE olm_persist_sessions " - " ADD COLUMN version INTEGER NOT NULL DEFAULT 1;", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error updating olm_persist_sessions table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_thread_activity_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS thread_activity (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " thread_activity_store_entry TEXT NOT NULL" - ");"; - return create_table(db, query, "thread_activity"); -} - -bool create_received_messages_to_device(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS received_messages_to_device (" - " id INTEGER PRIMARY KEY," - " message_id TEXT NOT NULL," - " sender_device_id TEXT NOT NULL," - " plaintext TEXT NOT NULL," - " status TEXT NOT NULL" - ");"; - return create_table(db, query, "received_messages_to_device"); -} - -bool recreate_outbound_p2p_messages_table(sqlite3 *db) { - std::string query = - "DROP TABLE IF EXISTS messages_to_device;" - "CREATE TABLE IF NOT EXISTS outbound_p2p_messages (" - " message_id TEXT NOT NULL," - " device_id TEXT NOT NULL," - " user_id TEXT NOT NULL," - " timestamp BIGINT NOT NULL," - " plaintext TEXT NOT NULL," - " ciphertext TEXT NOT NULL," - " status TEXT NOT NULL," - " PRIMARY KEY (message_id, device_id)" - ");" - - "CREATE INDEX IF NOT EXISTS outbound_p2p_messages_idx_id_timestamp" - " ON outbound_p2p_messages (device_id, timestamp);"; - - return create_table(db, query, "outbound_p2p_messages"); -} - -bool create_entries_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS entries (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " entry TEXT NOT NULL" - ");"; - return create_table(db, query, "entries"); -} - -bool create_message_store_local_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS message_store_local (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " local_message_info TEXT NOT NULL" - ");"; - return create_table(db, query, "message_store_local"); -} - -bool add_supports_auto_retry_column_to_p2p_messages_table(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "ALTER TABLE outbound_p2p_messages" - " ADD COLUMN supports_auto_retry INTEGER DEFAULT 0", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error updating outbound_p2p_messages table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_message_search_table(sqlite3 *db) { - std::string query = - "CREATE VIRTUAL TABLE IF NOT EXISTS message_search USING fts5(" - " original_message_id UNINDEXED," - " message_id UNINDEXED," - " processed_content," - " tokenize = porter" - ");"; - return create_table(db, query, "message_search"); -} - -bool recreate_inbound_p2p_messages_table(sqlite3 *db) { - std::string query = - "DROP TABLE IF EXISTS received_messages_to_device;" - "CREATE TABLE IF NOT EXISTS inbound_p2p_messages (" - " id INTEGER PRIMARY KEY," - " message_id TEXT NOT NULL," - " sender_device_id TEXT NOT NULL," - " plaintext TEXT NOT NULL," - " status TEXT NOT NULL," - " sender_user_id TEXT NOT NULL" - ");"; - - return create_table(db, query, "inbound_p2p_messages"); -} - -bool add_timestamps_column_to_threads_table(sqlite3 *db) { - char *error; - sqlite3_exec( - db, - "ALTER TABLE threads" - " ADD COLUMN timestamps TEXT;", - nullptr, - nullptr, - &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error updating threads table: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - -bool create_dm_operations_table(sqlite3 *db) { - std::string query = - "CREATE TABLE IF NOT EXISTS dm_operations (" - " id TEXT PRIMARY KEY," - " type TEXT NOT NULL," - " operation TEXT NOT NULL" - ");" - "CREATE INDEX IF NOT EXISTS dm_operations_idx_type" - " ON dm_operations (type);"; - - return create_table(db, query, "dm_operations"); -} - -bool create_schema(sqlite3 *db) { - char *error; - int sidebarSourceTypeInt = static_cast(MessageType::SIDEBAR_SOURCE); - std::string sidebarSourceType = std::to_string(sidebarSourceTypeInt); - auto query = - "CREATE TABLE IF NOT EXISTS drafts (" - " key TEXT UNIQUE PRIMARY KEY NOT NULL," - " text TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS messages (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " local_id TEXT," - " thread TEXT NOT NULL," - " user TEXT NOT NULL," - " type INTEGER NOT NULL," - " future_type INTEGER," - " content TEXT," - " time INTEGER NOT NULL," - " target_message TEXT AS (" - " IIF(" - " JSON_VALID(content)," - " COALESCE(" - " JSON_EXTRACT(content, '$.targetMessageID')," - " IIF(" - " type = " + - sidebarSourceType + - " , JSON_EXTRACT(content, '$.id')," - " NULL" - " )" - " )," - " NULL" - " )" - " )" - ");" - - "CREATE TABLE IF NOT EXISTS olm_persist_account (" - " id INTEGER UNIQUE PRIMARY KEY NOT NULL," - " account_data TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS olm_persist_sessions (" - " target_device_id TEXT UNIQUE PRIMARY KEY NOT NULL," - " session_data TEXT NOT NULL," - " version INTEGER NOT NULL DEFAULT 1" - ");" - - "CREATE TABLE IF NOT EXISTS media (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " container TEXT NOT NULL," - " thread TEXT NOT NULL," - " uri TEXT NOT NULL," - " type TEXT NOT NULL," - " extras TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS threads (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " type INTEGER NOT NULL," - " name TEXT," - " description TEXT," - " color TEXT NOT NULL," - " creation_time BIGINT NOT NULL," - " parent_thread_id TEXT," - " containing_thread_id TEXT," - " community TEXT," - " members TEXT NOT NULL," - " roles TEXT NOT NULL," - " current_user TEXT NOT NULL," - " source_message_id TEXT," - " replies_count INTEGER NOT NULL," - " avatar TEXT," - " pinned_count INTEGER NOT NULL DEFAULT 0," - " timestamps TEXT" - ");" - - "CREATE TABLE IF NOT EXISTS metadata (" - " name TEXT UNIQUE PRIMARY KEY NOT NULL," - " data TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS message_store_threads (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " start_reached INTEGER NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS reports (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " report TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS persist_storage (" - " key TEXT UNIQUE PRIMARY KEY NOT NULL," - " item TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS users (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " user_info TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS keyservers (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " keyserver_info TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS keyservers_synced (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " keyserver_info TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS communities (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " community_info TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS outbound_p2p_messages (" - " message_id TEXT NOT NULL," - " device_id TEXT NOT NULL," - " user_id TEXT NOT NULL," - " timestamp BIGINT NOT NULL," - " plaintext TEXT NOT NULL," - " ciphertext TEXT NOT NULL," - " status TEXT NOT NULL," - " supports_auto_retry INTEGER DEFAULT 0," - " PRIMARY KEY (message_id, device_id)" - ");" - - "CREATE TABLE IF NOT EXISTS integrity_store (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " thread_hash TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS synced_metadata (" - " name TEXT UNIQUE PRIMARY KEY NOT NULL," - " data TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS aux_users (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " aux_user_info TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS thread_activity (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " thread_activity_store_entry TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS inbound_p2p_messages (" - " id INTEGER PRIMARY KEY," - " message_id TEXT NOT NULL," - " sender_device_id TEXT NOT NULL," - " plaintext TEXT NOT NULL," - " status TEXT NOT NULL," - " sender_user_id TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS entries (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " entry TEXT NOT NULL" - ");" - - "CREATE TABLE IF NOT EXISTS message_store_local (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " local_message_info TEXT NOT NULL" - ");" - - "CREATE VIRTUAL TABLE IF NOT EXISTS message_search USING fts5(" - " original_message_id UNINDEXED," - " message_id UNINDEXED," - " processed_content," - " tokenize = porter" - ");" - - "CREATE TABLE IF NOT EXISTS dm_operations (" - " id TEXT PRIMARY KEY," - " type TEXT NOT NULL," - " operation TEXT NOT NULL" - ");" - - "CREATE INDEX IF NOT EXISTS media_idx_container" - " ON media (container);" - - "CREATE INDEX IF NOT EXISTS messages_idx_thread_time" - " ON messages (thread, time);" - - "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time" - " ON messages (target_message, type, time);" - - "CREATE INDEX IF NOT EXISTS outbound_p2p_messages_idx_id_timestamp" - " ON outbound_p2p_messages (device_id, timestamp);" - - "CREATE INDEX IF NOT EXISTS dm_operations_idx_type" - " ON dm_operations (type);"; - - sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); - - if (!error) { - return true; - } - - std::ostringstream stringStream; - stringStream << "Error creating tables: " << error; - Logger::log(stringStream.str()); - - sqlite3_free(error); - return false; -} - // We don't want to run `PRAGMA key = ...;` // on main web database. The context is here: // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web @@ -1058,125 +67,6 @@ #endif } -typedef bool ShouldBeInTransaction; -typedef std::function MigrateFunction; -typedef std::pair SQLiteMigration; -std::vector> migrations{ - {{1, {create_drafts_table, true}}, - {2, {rename_threadID_to_key, true}}, - {4, {create_persist_account_table, true}}, - {5, {create_persist_sessions_table, true}}, - {15, {create_media_table, true}}, - {16, {drop_messages_table, true}}, - {17, {recreate_messages_table, true}}, - {18, {create_messages_idx_thread_time, true}}, - {19, {create_media_idx_container, true}}, - {20, {create_threads_table, true}}, - {21, {update_threadID_for_pending_threads_in_drafts, true}}, - {22, {enable_write_ahead_logging_mode, false}}, - {23, {create_metadata_table, true}}, - {24, {add_not_null_constraint_to_drafts, true}}, - {25, {add_not_null_constraint_to_metadata, true}}, - {26, {add_avatar_column_to_threads_table, true}}, - {27, {add_pinned_count_column_to_threads, true}}, - {28, {create_message_store_threads_table, true}}, - {29, {create_reports_table, true}}, - {30, {create_persist_storage_table, true}}, - {31, {recreate_message_store_threads_table, true}}, - {32, {create_users_table, true}}, - {33, {create_keyservers_table, true}}, - {34, {enable_rollback_journal_mode, false}}, - {35, {create_communities_table, true}}, - {36, {create_messages_to_device_table, true}}, - {37, {create_integrity_table, true}}, - {38, {[](sqlite3 *) { return true; }, false}}, - {39, {create_synced_metadata_table, true}}, - {40, {create_keyservers_synced, true}}, - {41, {create_aux_user_table, true}}, - {42, {add_version_column_to_olm_persist_sessions_table, true}}, - {43, {create_thread_activity_table, true}}, - {44, {create_received_messages_to_device, true}}, - {45, {recreate_outbound_p2p_messages_table, true}}, - {46, {create_entries_table, true}}, - {47, {create_message_store_local_table, true}}, - {48, {create_messages_idx_target_message_type_time, true}}, - {49, {add_supports_auto_retry_column_to_p2p_messages_table, true}}, - {50, {create_message_search_table, true}}, - {51, {update_messages_idx_target_message_type_time, true}}, - {52, {recreate_inbound_p2p_messages_table, true}}, - {53, {add_timestamps_column_to_threads_table, true}}, - {54, {create_dm_operations_table, true}}}}; - -enum class MigrationResult { SUCCESS, FAILURE, NOT_APPLIED }; - -MigrationResult applyMigrationWithTransaction( - sqlite3 *db, - const MigrateFunction &migrate, - int index) { - sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); - auto db_version = SQLiteUtils::getDatabaseVersion(db); - if (index <= db_version) { - sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); - return MigrationResult::NOT_APPLIED; - } - auto rc = migrate(db); - if (!rc) { - sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); - return MigrationResult::FAILURE; - } - auto database_version_set = SQLiteUtils::setDatabaseVersion(db, index); - if (!database_version_set) { - sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); - return MigrationResult::FAILURE; - } - sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr); - return MigrationResult::SUCCESS; -} - -MigrationResult applyMigrationWithoutTransaction( - sqlite3 *db, - const MigrateFunction &migrate, - int index) { - auto db_version = SQLiteUtils::getDatabaseVersion(db); - if (index <= db_version) { - return MigrationResult::NOT_APPLIED; - } - auto rc = migrate(db); - if (!rc) { - return MigrationResult::FAILURE; - } - sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); - auto inner_db_version = SQLiteUtils::getDatabaseVersion(db); - if (index <= inner_db_version) { - sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); - return MigrationResult::NOT_APPLIED; - } - auto database_version_set = SQLiteUtils::setDatabaseVersion(db, index); - if (!database_version_set) { - sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); - return MigrationResult::FAILURE; - } - sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr); - return MigrationResult::SUCCESS; -} - -bool set_up_database(sqlite3 *db) { - sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); - auto db_version = SQLiteUtils::getDatabaseVersion(db); - auto latest_version = migrations.back().first; - if (db_version == latest_version) { - sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); - return true; - } - if (db_version != 0 || !create_schema(db) || - !SQLiteUtils::setDatabaseVersion(db, latest_version)) { - sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); - return false; - } - sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr); - return true; -} - void SQLiteQueryExecutor::migrate() { // We don't want to run `PRAGMA key = ...;` // on main web database. The context is here: @@ -1201,7 +91,7 @@ Logger::log(version_msg.str()); if (db_version == 0) { - auto db_created = set_up_database(db); + auto db_created = SQLiteSchema::setupDatabase(db); if (!db_created) { sqlite3_close(db); Logger::log("Database structure creation error."); @@ -1213,34 +103,7 @@ return; } - for (const auto &[idx, migration] : migrations) { - const auto &[applyMigration, shouldBeInTransaction] = migration; - - MigrationResult migrationResult; - if (shouldBeInTransaction) { - migrationResult = applyMigrationWithTransaction(db, applyMigration, idx); - } else { - migrationResult = - applyMigrationWithoutTransaction(db, applyMigration, idx); - } - - if (migrationResult == MigrationResult::NOT_APPLIED) { - continue; - } - - std::stringstream migration_msg; - if (migrationResult == MigrationResult::FAILURE) { - migration_msg << "migration " << idx << " failed." << std::endl; - Logger::log(migration_msg.str()); - sqlite3_close(db); - throw std::runtime_error(migration_msg.str()); - } - if (migrationResult == MigrationResult::SUCCESS) { - migration_msg << "migration " << idx << " succeeded." << std::endl; - Logger::log(migration_msg.str()); - } - } - + SQLiteSchema::migrate(db); sqlite3_close(db); } diff --git a/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.h b/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.h new file mode 100644 --- /dev/null +++ b/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.h @@ -0,0 +1,50 @@ +#pragma once + +#include +#include +#include +#include + +namespace comm { + +enum class MigrationResult { SUCCESS, FAILURE, NOT_APPLIED }; + +typedef bool ShouldBeInTransaction; + +typedef std::function MigrateFunction; + +typedef std::pair SQLiteMigration; + +typedef std::vector> SQLiteMigrations; + +class SQLiteSchema { +private: + static SQLiteMigrations migrations; + + // Running migrations and handling the database version. + static MigrationResult applyMigrationWithTransaction( + sqlite3 *db, + const MigrateFunction &migrate, + int index); + // Running migrations and handling the database version. + static MigrationResult applyMigrationWithoutTransaction( + sqlite3 *db, + const MigrateFunction &migrate, + int index); + // Creating database schema from scratch. + static bool createSchema(sqlite3 *db); + +public: + // Utility method used to create a table during migration. This should be used + // only in the context of this class, but couldn't be private because + // migrations are defined as a standalone function, so it wouldn't be + // possible to implement this as private. + static bool + createTable(sqlite3 *db, std::string query, std::string tableName); + // Method to create a database schema from scratch and handle the version. + static bool setupDatabase(sqlite3 *db); + // Method to run migrations. + static void migrate(sqlite3 *db); +}; + +} // namespace comm diff --git a/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.cpp b/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.cpp new file mode 100644 --- /dev/null +++ b/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.cpp @@ -0,0 +1,343 @@ +#include "SQLiteSchema.h" + +#include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h" +#include "Logger.h" +#include "SQLiteUtils.h" + +#include + +#include +#include +#include + +namespace comm { + +bool SQLiteSchema::createSchema(sqlite3 *db) { + char *error; + int sidebarSourceTypeInt = static_cast(MessageType::SIDEBAR_SOURCE); + std::string sidebarSourceType = std::to_string(sidebarSourceTypeInt); + auto query = + "CREATE TABLE IF NOT EXISTS drafts (" + " key TEXT UNIQUE PRIMARY KEY NOT NULL," + " text TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS messages (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " local_id TEXT," + " thread TEXT NOT NULL," + " user TEXT NOT NULL," + " type INTEGER NOT NULL," + " future_type INTEGER," + " content TEXT," + " time INTEGER NOT NULL," + " target_message TEXT AS (" + " IIF(" + " JSON_VALID(content)," + " COALESCE(" + " JSON_EXTRACT(content, '$.targetMessageID')," + " IIF(" + " type = " + + sidebarSourceType + + " , JSON_EXTRACT(content, '$.id')," + " NULL" + " )" + " )," + " NULL" + " )" + " )" + ");" + + "CREATE TABLE IF NOT EXISTS olm_persist_account (" + " id INTEGER UNIQUE PRIMARY KEY NOT NULL," + " account_data TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS olm_persist_sessions (" + " target_device_id TEXT UNIQUE PRIMARY KEY NOT NULL," + " session_data TEXT NOT NULL," + " version INTEGER NOT NULL DEFAULT 1" + ");" + + "CREATE TABLE IF NOT EXISTS media (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " container TEXT NOT NULL," + " thread TEXT NOT NULL," + " uri TEXT NOT NULL," + " type TEXT NOT NULL," + " extras TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS threads (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " type INTEGER NOT NULL," + " name TEXT," + " description TEXT," + " color TEXT NOT NULL," + " creation_time BIGINT NOT NULL," + " parent_thread_id TEXT," + " containing_thread_id TEXT," + " community TEXT," + " members TEXT NOT NULL," + " roles TEXT NOT NULL," + " current_user TEXT NOT NULL," + " source_message_id TEXT," + " replies_count INTEGER NOT NULL," + " avatar TEXT," + " pinned_count INTEGER NOT NULL DEFAULT 0," + " timestamps TEXT" + ");" + + "CREATE TABLE IF NOT EXISTS metadata (" + " name TEXT UNIQUE PRIMARY KEY NOT NULL," + " data TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS message_store_threads (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " start_reached INTEGER NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS reports (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " report TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS persist_storage (" + " key TEXT UNIQUE PRIMARY KEY NOT NULL," + " item TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS users (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " user_info TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS keyservers (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " keyserver_info TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS keyservers_synced (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " keyserver_info TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS communities (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " community_info TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS outbound_p2p_messages (" + " message_id TEXT NOT NULL," + " device_id TEXT NOT NULL," + " user_id TEXT NOT NULL," + " timestamp BIGINT NOT NULL," + " plaintext TEXT NOT NULL," + " ciphertext TEXT NOT NULL," + " status TEXT NOT NULL," + " supports_auto_retry INTEGER DEFAULT 0," + " PRIMARY KEY (message_id, device_id)" + ");" + + "CREATE TABLE IF NOT EXISTS integrity_store (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " thread_hash TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS synced_metadata (" + " name TEXT UNIQUE PRIMARY KEY NOT NULL," + " data TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS aux_users (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " aux_user_info TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS thread_activity (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " thread_activity_store_entry TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS inbound_p2p_messages (" + " id INTEGER PRIMARY KEY," + " message_id TEXT NOT NULL," + " sender_device_id TEXT NOT NULL," + " plaintext TEXT NOT NULL," + " status TEXT NOT NULL," + " sender_user_id TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS entries (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " entry TEXT NOT NULL" + ");" + + "CREATE TABLE IF NOT EXISTS message_store_local (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " local_message_info TEXT NOT NULL" + ");" + + "CREATE VIRTUAL TABLE IF NOT EXISTS message_search USING fts5(" + " original_message_id UNINDEXED," + " message_id UNINDEXED," + " processed_content," + " tokenize = porter" + ");" + + "CREATE TABLE IF NOT EXISTS dm_operations (" + " id TEXT PRIMARY KEY," + " type TEXT NOT NULL," + " operation TEXT NOT NULL" + ");" + + "CREATE INDEX IF NOT EXISTS media_idx_container" + " ON media (container);" + + "CREATE INDEX IF NOT EXISTS messages_idx_thread_time" + " ON messages (thread, time);" + + "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time" + " ON messages (target_message, type, time);" + + "CREATE INDEX IF NOT EXISTS outbound_p2p_messages_idx_id_timestamp" + " ON outbound_p2p_messages (device_id, timestamp);" + + "CREATE INDEX IF NOT EXISTS dm_operations_idx_type" + " ON dm_operations (type);"; + + sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error creating tables: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool SQLiteSchema::setupDatabase(sqlite3 *db) { + sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); + auto db_version = SQLiteUtils::getDatabaseVersion(db); + + auto latest_version = migrations.back().first; + + if (db_version == latest_version) { + sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); + return true; + } + + if (db_version != 0 || !createSchema(db) || + !SQLiteUtils::setDatabaseVersion(db, latest_version)) { + sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); + return false; + } + sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr); + return true; +} + +bool SQLiteSchema::createTable( + sqlite3 *db, + std::string query, + std::string tableName) { + char *error; + sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error creating '" << tableName << "' table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +MigrationResult SQLiteSchema::applyMigrationWithTransaction( + sqlite3 *db, + const MigrateFunction &migrate, + int index) { + sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); + auto db_version = SQLiteUtils::getDatabaseVersion(db); + if (index <= db_version) { + sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); + return MigrationResult::NOT_APPLIED; + } + auto rc = migrate(db); + if (!rc) { + sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); + return MigrationResult::FAILURE; + } + auto database_version_set = SQLiteUtils::setDatabaseVersion(db, index); + if (!database_version_set) { + sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); + return MigrationResult::FAILURE; + } + sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr); + return MigrationResult::SUCCESS; +} + +MigrationResult SQLiteSchema::applyMigrationWithoutTransaction( + sqlite3 *db, + const MigrateFunction &migrate, + int index) { + auto db_version = SQLiteUtils::getDatabaseVersion(db); + if (index <= db_version) { + return MigrationResult::NOT_APPLIED; + } + auto rc = migrate(db); + if (!rc) { + return MigrationResult::FAILURE; + } + sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr); + auto inner_db_version = SQLiteUtils::getDatabaseVersion(db); + if (index <= inner_db_version) { + sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); + return MigrationResult::NOT_APPLIED; + } + auto database_version_set = SQLiteUtils::setDatabaseVersion(db, index); + if (!database_version_set) { + sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); + return MigrationResult::FAILURE; + } + sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr); + return MigrationResult::SUCCESS; +} + +void SQLiteSchema::migrate(sqlite3 *db) { + for (const auto &[idx, migration] : migrations) { + const auto &[applyMigration, shouldBeInTransaction] = migration; + + MigrationResult migrationResult; + if (shouldBeInTransaction) { + migrationResult = applyMigrationWithTransaction(db, applyMigration, idx); + } else { + migrationResult = + applyMigrationWithoutTransaction(db, applyMigration, idx); + } + + if (migrationResult == MigrationResult::NOT_APPLIED) { + continue; + } + + std::stringstream migration_msg; + if (migrationResult == MigrationResult::FAILURE) { + migration_msg << "migration " << idx << " failed." << std::endl; + Logger::log(migration_msg.str()); + sqlite3_close(db); + throw std::runtime_error(migration_msg.str()); + } + if (migrationResult == MigrationResult::SUCCESS) { + migration_msg << "migration " << idx << " succeeded." << std::endl; + Logger::log(migration_msg.str()); + } + } +} + +} // namespace comm diff --git a/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchemaMigrations.cpp b/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchemaMigrations.cpp new file mode 100644 --- /dev/null +++ b/native/cpp/CommonCpp/DatabaseManagers/SQLiteSchemaMigrations.cpp @@ -0,0 +1,827 @@ +#include "SQLiteSchema.h" + +#include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h" +#include "Logger.h" + +#include +#include +#include +#include + +namespace comm { + +bool create_drafts_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS drafts (threadID TEXT UNIQUE PRIMARY KEY, " + "text TEXT);"; + return SQLiteSchema::createTable(db, query, "drafts"); +} + +bool rename_threadID_to_key(sqlite3 *db) { + sqlite3_stmt *key_column_stmt; + sqlite3_prepare_v2( + db, + "SELECT name AS col_name FROM pragma_table_xinfo ('drafts') WHERE " + "col_name='key';", + -1, + &key_column_stmt, + nullptr); + sqlite3_step(key_column_stmt); + + auto num_bytes = sqlite3_column_bytes(key_column_stmt, 0); + sqlite3_finalize(key_column_stmt); + if (num_bytes) { + return true; + } + + char *error; + sqlite3_exec( + db, + "ALTER TABLE drafts RENAME COLUMN `threadID` TO `key`;", + nullptr, + nullptr, + &error); + if (error) { + std::ostringstream stringStream; + stringStream << "Error occurred renaming threadID column in drafts table " + << "to key: " << error; + Logger::log(stringStream.str()); + sqlite3_free(error); + return false; + } + return true; +} + +bool create_persist_account_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS olm_persist_account(" + "id INTEGER UNIQUE PRIMARY KEY NOT NULL, " + "account_data TEXT NOT NULL);"; + return SQLiteSchema::createTable(db, query, "olm_persist_account"); +} + +bool create_persist_sessions_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS olm_persist_sessions(" + "target_user_id TEXT UNIQUE PRIMARY KEY NOT NULL, " + "session_data TEXT NOT NULL);"; + return SQLiteSchema::createTable(db, query, "olm_persist_sessions"); +} + +bool drop_messages_table(sqlite3 *db) { + char *error; + sqlite3_exec(db, "DROP TABLE IF EXISTS messages;", nullptr, nullptr, &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error dropping 'messages' table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool recreate_messages_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS messages ( " + "id TEXT UNIQUE PRIMARY KEY NOT NULL, " + "local_id TEXT, " + "thread TEXT NOT NULL, " + "user TEXT NOT NULL, " + "type INTEGER NOT NULL, " + "future_type INTEGER, " + "content TEXT, " + "time INTEGER NOT NULL);"; + return SQLiteSchema::createTable(db, query, "messages"); +} + +bool create_messages_idx_thread_time(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "CREATE INDEX IF NOT EXISTS messages_idx_thread_time " + "ON messages (thread, time);", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error creating (thread, time) index on messages table: " + << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_messages_idx_target_message_type_time(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "ALTER TABLE messages " + " ADD COLUMN target_message TEXT " + " AS (IIF( " + " JSON_VALID(content), " + " JSON_EXTRACT(content, '$.targetMessageID'), " + " NULL " + " )); " + "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time " + " ON messages (target_message, type, time);", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream + << "Error creating (target_message, type, time) index on messages table: " + << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool update_messages_idx_target_message_type_time(sqlite3 *db) { + char *error; + int sidebarSourceTypeInt = static_cast(MessageType::SIDEBAR_SOURCE); + std::string sidebarSourceType = std::to_string(sidebarSourceTypeInt); + + auto query = + "DROP INDEX IF EXISTS messages_idx_target_message_type_time;" + "ALTER TABLE messages DROP COLUMN target_message;" + "ALTER TABLE messages " + " ADD COLUMN target_message TEXT " + " AS (IIF(" + " JSON_VALID(content)," + " COALESCE(" + " JSON_EXTRACT(content, '$.targetMessageID')," + " IIF(" + " type = " + + sidebarSourceType + + " , JSON_EXTRACT(content, '$.id')," + " NULL" + " )" + " )," + " NULL" + " ));" + "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time " + " ON messages (target_message, type, time);"; + + sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream + << "Error creating (target_message, type, time) index on messages table: " + << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_media_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS media ( " + "id TEXT UNIQUE PRIMARY KEY NOT NULL, " + "container TEXT NOT NULL, " + "thread TEXT NOT NULL, " + "uri TEXT NOT NULL, " + "type TEXT NOT NULL, " + "extras TEXT NOT NULL);"; + return SQLiteSchema::createTable(db, query, "media"); +} + +bool create_media_idx_container(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "CREATE INDEX IF NOT EXISTS media_idx_container " + "ON media (container);", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error creating (container) index on media table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_threads_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS threads ( " + "id TEXT UNIQUE PRIMARY KEY NOT NULL, " + "type INTEGER NOT NULL, " + "name TEXT, " + "description TEXT, " + "color TEXT NOT NULL, " + "creation_time BIGINT NOT NULL, " + "parent_thread_id TEXT, " + "containing_thread_id TEXT, " + "community TEXT, " + "members TEXT NOT NULL, " + "roles TEXT NOT NULL, " + "current_user TEXT NOT NULL, " + "source_message_id TEXT, " + "replies_count INTEGER NOT NULL);"; + return SQLiteSchema::createTable(db, query, "threads"); +} + +bool update_threadID_for_pending_threads_in_drafts(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "UPDATE drafts SET key = " + "REPLACE(REPLACE(REPLACE(REPLACE(key, 'type4/', '')," + "'type5/', ''),'type6/', ''),'type7/', '')" + "WHERE key LIKE 'pending/%'", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error update pending threadIDs on drafts table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool enable_write_ahead_logging_mode(sqlite3 *db) { + char *error; + sqlite3_exec(db, "PRAGMA journal_mode=wal;", nullptr, nullptr, &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error enabling write-ahead logging mode: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_metadata_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS metadata ( " + "name TEXT UNIQUE PRIMARY KEY NOT NULL, " + "data TEXT);"; + return SQLiteSchema::createTable(db, query, "metadata"); +} + +bool add_not_null_constraint_to_drafts(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "CREATE TABLE IF NOT EXISTS temporary_drafts (" + "key TEXT UNIQUE PRIMARY KEY NOT NULL, " + "text TEXT NOT NULL);" + "INSERT INTO temporary_drafts SELECT * FROM drafts " + "WHERE key IS NOT NULL AND text IS NOT NULL;" + "DROP TABLE drafts;" + "ALTER TABLE temporary_drafts RENAME TO drafts;", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error adding NOT NULL constraint to drafts table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool add_not_null_constraint_to_metadata(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "CREATE TABLE IF NOT EXISTS temporary_metadata (" + "name TEXT UNIQUE PRIMARY KEY NOT NULL, " + "data TEXT NOT NULL);" + "INSERT INTO temporary_metadata SELECT * FROM metadata " + "WHERE data IS NOT NULL;" + "DROP TABLE metadata;" + "ALTER TABLE temporary_metadata RENAME TO metadata;", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error adding NOT NULL constraint to metadata table: " + << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool add_avatar_column_to_threads_table(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "ALTER TABLE threads ADD COLUMN avatar TEXT;", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error adding avatar column to threads table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool add_pinned_count_column_to_threads(sqlite3 *db) { + sqlite3_stmt *pinned_column_stmt; + sqlite3_prepare_v2( + db, + "SELECT name AS col_name FROM pragma_table_xinfo ('threads') WHERE " + "col_name='pinned_count';", + -1, + &pinned_column_stmt, + nullptr); + sqlite3_step(pinned_column_stmt); + + auto num_bytes = sqlite3_column_bytes(pinned_column_stmt, 0); + sqlite3_finalize(pinned_column_stmt); + + if (num_bytes) { + return true; + } + + char *error; + sqlite3_exec( + db, + "ALTER TABLE threads ADD COLUMN pinned_count INTEGER NOT NULL DEFAULT 0;", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error adding pinned_count column to threads table: " + << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_message_store_threads_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS message_store_threads (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " start_reached INTEGER NOT NULL," + " last_navigated_to BIGINT NOT NULL," + " last_pruned BIGINT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "message_store_threads"); +} + +bool create_reports_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS reports (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " report TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "reports"); +} + +bool create_persist_storage_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS persist_storage (" + " key TEXT UNIQUE PRIMARY KEY NOT NULL," + " item TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "persist_storage"); +} + +bool recreate_message_store_threads_table(sqlite3 *db) { + char *errMsg = 0; + + // 1. Create table without `last_navigated_to` or `last_pruned`. + std::string create_new_table_query = + "CREATE TABLE IF NOT EXISTS temp_message_store_threads (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " start_reached INTEGER NOT NULL" + ");"; + + if (sqlite3_exec(db, create_new_table_query.c_str(), NULL, NULL, &errMsg) != + SQLITE_OK) { + Logger::log( + "Error creating temp_message_store_threads: " + std::string{errMsg}); + sqlite3_free(errMsg); + return false; + } + + // 2. Dump data from existing `message_store_threads` table into temp table. + std::string copy_data_query = + "INSERT INTO temp_message_store_threads (id, start_reached)" + "SELECT id, start_reached FROM message_store_threads;"; + + if (sqlite3_exec(db, copy_data_query.c_str(), NULL, NULL, &errMsg) != + SQLITE_OK) { + Logger::log( + "Error dumping data from existing message_store_threads to " + "temp_message_store_threads: " + + std::string{errMsg}); + sqlite3_free(errMsg); + return false; + } + + // 3. Drop the existing `message_store_threads` table. + std::string drop_old_table_query = "DROP TABLE message_store_threads;"; + + if (sqlite3_exec(db, drop_old_table_query.c_str(), NULL, NULL, &errMsg) != + SQLITE_OK) { + Logger::log( + "Error dropping message_store_threads table: " + std::string{errMsg}); + sqlite3_free(errMsg); + return false; + } + + // 4. Rename the temp table back to `message_store_threads`. + std::string rename_table_query = + "ALTER TABLE temp_message_store_threads RENAME TO message_store_threads;"; + + if (sqlite3_exec(db, rename_table_query.c_str(), NULL, NULL, &errMsg) != + SQLITE_OK) { + Logger::log( + "Error renaming temp_message_store_threads to message_store_threads: " + + std::string{errMsg}); + sqlite3_free(errMsg); + return false; + } + + return true; +} + +bool create_users_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS users (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " user_info TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "users"); +} + +bool create_keyservers_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS keyservers (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " keyserver_info TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "keyservers"); +} + +bool enable_rollback_journal_mode(sqlite3 *db) { + char *error; + sqlite3_exec(db, "PRAGMA journal_mode=DELETE;", nullptr, nullptr, &error); + + if (!error) { + return true; + } + + std::stringstream error_message; + error_message << "Error disabling write-ahead logging mode: " << error; + Logger::log(error_message.str()); + sqlite3_free(error); + return false; +} + +bool create_communities_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS communities (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " community_info TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "communities"); +} + +bool create_messages_to_device_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS messages_to_device (" + " message_id TEXT NOT NULL," + " device_id TEXT NOT NULL," + " user_id TEXT NOT NULL," + " timestamp BIGINT NOT NULL," + " plaintext TEXT NOT NULL," + " ciphertext TEXT NOT NULL," + " PRIMARY KEY (message_id, device_id)" + ");" + + "CREATE INDEX IF NOT EXISTS messages_to_device_idx_id_timestamp" + " ON messages_to_device (device_id, timestamp);"; + + return SQLiteSchema::createTable(db, query, "messages_to_device"); +} + +bool create_integrity_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS integrity_store (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " thread_hash TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "integrity_store"); +} + +bool create_synced_metadata_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS synced_metadata (" + " name TEXT UNIQUE PRIMARY KEY NOT NULL," + " data TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "synced_metadata"); +} + +bool create_keyservers_synced(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS keyservers_synced (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " keyserver_info TEXT NOT NULL" + ");"; + bool success = SQLiteSchema::createTable(db, query, "keyservers_synced"); + if (!success) { + return false; + } + + std::string copyData = + "INSERT INTO keyservers_synced (id, keyserver_info)" + "SELECT id, keyserver_info " + "FROM keyservers;"; + + char *error; + sqlite3_exec(db, copyData.c_str(), nullptr, nullptr, &error); + if (error) { + return false; + } + + return true; +} + +bool create_aux_user_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS aux_users (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " aux_user_info TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "aux_users"); +} + +bool add_version_column_to_olm_persist_sessions_table(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "ALTER TABLE olm_persist_sessions " + " RENAME COLUMN `target_user_id` TO `target_device_id`; " + "ALTER TABLE olm_persist_sessions " + " ADD COLUMN version INTEGER NOT NULL DEFAULT 1;", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error updating olm_persist_sessions table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_thread_activity_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS thread_activity (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " thread_activity_store_entry TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "thread_activity"); +} + +bool create_received_messages_to_device(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS received_messages_to_device (" + " id INTEGER PRIMARY KEY," + " message_id TEXT NOT NULL," + " sender_device_id TEXT NOT NULL," + " plaintext TEXT NOT NULL," + " status TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "received_messages_to_device"); +} + +bool recreate_outbound_p2p_messages_table(sqlite3 *db) { + std::string query = + "DROP TABLE IF EXISTS messages_to_device;" + "CREATE TABLE IF NOT EXISTS outbound_p2p_messages (" + " message_id TEXT NOT NULL," + " device_id TEXT NOT NULL," + " user_id TEXT NOT NULL," + " timestamp BIGINT NOT NULL," + " plaintext TEXT NOT NULL," + " ciphertext TEXT NOT NULL," + " status TEXT NOT NULL," + " PRIMARY KEY (message_id, device_id)" + ");" + + "CREATE INDEX IF NOT EXISTS outbound_p2p_messages_idx_id_timestamp" + " ON outbound_p2p_messages (device_id, timestamp);"; + + return SQLiteSchema::createTable(db, query, "outbound_p2p_messages"); +} + +bool create_entries_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS entries (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " entry TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "entries"); +} + +bool create_message_store_local_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS message_store_local (" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " local_message_info TEXT NOT NULL" + ");"; + return SQLiteSchema::createTable(db, query, "message_store_local"); +} + +bool add_supports_auto_retry_column_to_p2p_messages_table(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "ALTER TABLE outbound_p2p_messages" + " ADD COLUMN supports_auto_retry INTEGER DEFAULT 0", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error updating outbound_p2p_messages table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_message_search_table(sqlite3 *db) { + std::string query = + "CREATE VIRTUAL TABLE IF NOT EXISTS message_search USING fts5(" + " original_message_id UNINDEXED," + " message_id UNINDEXED," + " processed_content," + " tokenize = porter" + ");"; + return SQLiteSchema::createTable(db, query, "message_search"); +} + +bool recreate_inbound_p2p_messages_table(sqlite3 *db) { + std::string query = + "DROP TABLE IF EXISTS received_messages_to_device;" + "CREATE TABLE IF NOT EXISTS inbound_p2p_messages (" + " id INTEGER PRIMARY KEY," + " message_id TEXT NOT NULL," + " sender_device_id TEXT NOT NULL," + " plaintext TEXT NOT NULL," + " status TEXT NOT NULL," + " sender_user_id TEXT NOT NULL" + ");"; + + return SQLiteSchema::createTable(db, query, "inbound_p2p_messages"); +} + +bool add_timestamps_column_to_threads_table(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "ALTER TABLE threads" + " ADD COLUMN timestamps TEXT;", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error updating threads table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + +bool create_dm_operations_table(sqlite3 *db) { + std::string query = + "CREATE TABLE IF NOT EXISTS dm_operations (" + " id TEXT PRIMARY KEY," + " type TEXT NOT NULL," + " operation TEXT NOT NULL" + ");" + "CREATE INDEX IF NOT EXISTS dm_operations_idx_type" + " ON dm_operations (type);"; + + return SQLiteSchema::createTable(db, query, "dm_operations"); +} + +SQLiteMigrations SQLiteSchema::migrations{ + {{1, {create_drafts_table, true}}, + {2, {rename_threadID_to_key, true}}, + {4, {create_persist_account_table, true}}, + {5, {create_persist_sessions_table, true}}, + {15, {create_media_table, true}}, + {16, {drop_messages_table, true}}, + {17, {recreate_messages_table, true}}, + {18, {create_messages_idx_thread_time, true}}, + {19, {create_media_idx_container, true}}, + {20, {create_threads_table, true}}, + {21, {update_threadID_for_pending_threads_in_drafts, true}}, + {22, {enable_write_ahead_logging_mode, false}}, + {23, {create_metadata_table, true}}, + {24, {add_not_null_constraint_to_drafts, true}}, + {25, {add_not_null_constraint_to_metadata, true}}, + {26, {add_avatar_column_to_threads_table, true}}, + {27, {add_pinned_count_column_to_threads, true}}, + {28, {create_message_store_threads_table, true}}, + {29, {create_reports_table, true}}, + {30, {create_persist_storage_table, true}}, + {31, {recreate_message_store_threads_table, true}}, + {32, {create_users_table, true}}, + {33, {create_keyservers_table, true}}, + {34, {enable_rollback_journal_mode, false}}, + {35, {create_communities_table, true}}, + {36, {create_messages_to_device_table, true}}, + {37, {create_integrity_table, true}}, + {38, {[](sqlite3 *) { return true; }, false}}, + {39, {create_synced_metadata_table, true}}, + {40, {create_keyservers_synced, true}}, + {41, {create_aux_user_table, true}}, + {42, {add_version_column_to_olm_persist_sessions_table, true}}, + {43, {create_thread_activity_table, true}}, + {44, {create_received_messages_to_device, true}}, + {45, {recreate_outbound_p2p_messages_table, true}}, + {46, {create_entries_table, true}}, + {47, {create_message_store_local_table, true}}, + {48, {create_messages_idx_target_message_type_time, true}}, + {49, {add_supports_auto_retry_column_to_p2p_messages_table, true}}, + {50, {create_message_search_table, true}}, + {51, {update_messages_idx_target_message_type_time, true}}, + {52, {recreate_inbound_p2p_messages_table, true}}, + {53, {add_timestamps_column_to_threads_table, true}}, + {54, {create_dm_operations_table, true}}}}; + +} // namespace comm diff --git a/native/ios/Comm.xcodeproj/project.pbxproj b/native/ios/Comm.xcodeproj/project.pbxproj --- a/native/ios/Comm.xcodeproj/project.pbxproj +++ b/native/ios/Comm.xcodeproj/project.pbxproj @@ -54,6 +54,8 @@ 8B99BAAC28D50F3000EB5ADB /* libnative_rust_library.a in Frameworks */ = {isa = PBXBuildFile; fileRef = 8B99BAAB28D50F3000EB5ADB /* libnative_rust_library.a */; }; 8B99BAAE28D511FF00EB5ADB /* lib.rs.cc in Sources */ = {isa = PBXBuildFile; fileRef = 8B99BAAD28D511FF00EB5ADB /* lib.rs.cc */; }; 8BC9568529FC49B00060AE4A /* JSIRust.cpp in Sources */ = {isa = PBXBuildFile; fileRef = 8BC9568429FC49B00060AE4A /* JSIRust.cpp */; }; + 8E1805562DA954B600B772A4 /* SQLiteSchema.cpp in Sources */ = {isa = PBXBuildFile; fileRef = 8E1805552DA954B600B772A4 /* SQLiteSchema.cpp */; }; + 8E18055A2DA95E7C00B772A4 /* SQLiteSchemaMigrations.cpp in Sources */ = {isa = PBXBuildFile; fileRef = 8E1805582DA95E7C00B772A4 /* SQLiteSchemaMigrations.cpp */; }; 8E2CC2592B5C99B0000C94D6 /* KeyserverStore.cpp in Sources */ = {isa = PBXBuildFile; fileRef = 8E2CC2582B5C99B0000C94D6 /* KeyserverStore.cpp */; }; 8E3994552B039A7C00D5E950 /* UserStore.cpp in Sources */ = {isa = PBXBuildFile; fileRef = 8E3994532B039A7C00D5E950 /* UserStore.cpp */; }; 8E43C32C291E5B4A009378F5 /* TerminateApp.mm in Sources */ = {isa = PBXBuildFile; fileRef = 8E43C32B291E5B4A009378F5 /* TerminateApp.mm */; }; @@ -251,6 +253,9 @@ 8B99BAAD28D511FF00EB5ADB /* lib.rs.cc */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.cpp.cpp; path = lib.rs.cc; sourceTree = ""; }; 8BC9568329FC49920060AE4A /* JSIRust.h */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.c.h; path = JSIRust.h; sourceTree = ""; }; 8BC9568429FC49B00060AE4A /* JSIRust.cpp */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.cpp.cpp; path = JSIRust.cpp; sourceTree = ""; }; + 8E1805542DA954B600B772A4 /* SQLiteSchema.h */ = {isa = PBXFileReference; lastKnownFileType = sourcecode.c.h; path = SQLiteSchema.h; sourceTree = ""; }; + 8E1805552DA954B600B772A4 /* SQLiteSchema.cpp */ = {isa = PBXFileReference; lastKnownFileType = sourcecode.cpp.cpp; path = SQLiteSchema.cpp; sourceTree = ""; }; + 8E1805582DA95E7C00B772A4 /* SQLiteSchemaMigrations.cpp */ = {isa = PBXFileReference; lastKnownFileType = sourcecode.cpp.cpp; path = SQLiteSchemaMigrations.cpp; sourceTree = ""; }; 8E2CC2562B5C999A000C94D6 /* KeyserverStoreOperations.h */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.c.h; path = KeyserverStoreOperations.h; sourceTree = ""; }; 8E2CC2572B5C99B0000C94D6 /* KeyserverStore.h */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.c.h; name = KeyserverStore.h; path = PersistentStorageUtilities/DataStores/KeyserverStore.h; sourceTree = ""; }; 8E2CC2582B5C99B0000C94D6 /* KeyserverStore.cpp */ = {isa = PBXFileReference; fileEncoding = 4; lastKnownFileType = sourcecode.cpp.cpp; name = KeyserverStore.cpp; path = PersistentStorageUtilities/DataStores/KeyserverStore.cpp; sourceTree = ""; }; @@ -551,6 +556,9 @@ 71BE843F2636A944002849D2 /* DatabaseManagers */ = { isa = PBXGroup; children = ( + 8E1805582DA95E7C00B772A4 /* SQLiteSchemaMigrations.cpp */, + 8E1805542DA954B600B772A4 /* SQLiteSchema.h */, + 8E1805552DA954B600B772A4 /* SQLiteSchema.cpp */, 8EF29DA82DA52AE7003D677E /* SQLiteUtils.h */, 8EF29DA92DA52AE7003D677E /* SQLiteUtils.cpp */, CB3CCB002B7246F400793640 /* NativeSQLiteConnectionManager.cpp */, @@ -1213,6 +1221,7 @@ isa = PBXSourcesBuildPhase; buildActionMask = 2147483647; files = ( + 8E1805562DA954B600B772A4 /* SQLiteSchema.cpp in Sources */, CBAB638A2BFCCA9B003B089F /* EntryStore.cpp in Sources */, CB3CCB012B72470700793640 /* NativeSQLiteConnectionManager.cpp in Sources */, CBA5F8852B6979F7005BE700 /* SQLiteConnectionManager.cpp in Sources */, @@ -1277,6 +1286,7 @@ 711B408425DA97F9005F8F06 /* dummy.swift in Sources */, 8E86A6D329537EBB000BBE7D /* DatabaseManager.cpp in Sources */, CBDEC69B28ED867000C17588 /* GlobalDBSingleton.mm in Sources */, + 8E18055A2DA95E7C00B772A4 /* SQLiteSchemaMigrations.cpp in Sources */, DFD5E77E2B05264000C32B6A /* AESCrypto.mm in Sources */, 8EA59BD62A6E8E0400EB4F53 /* DraftStore.cpp in Sources */, 13B07FC11A68108700A75B9A /* main.m in Sources */, diff --git a/web/cpp/SQLiteQueryExecutorBindings.cpp b/web/cpp/SQLiteQueryExecutorBindings.cpp --- a/web/cpp/SQLiteQueryExecutorBindings.cpp +++ b/web/cpp/SQLiteQueryExecutorBindings.cpp @@ -1,4 +1,6 @@ #include "SQLiteQueryExecutor.cpp" +#include "SQLiteSchema.cpp" +#include "SQLiteSchemaMigrations.cpp" #include "SQLiteUtils.cpp" #include "entities/InboundP2PMessage.h" #include "entities/OutboundP2PMessage.h" diff --git a/web/shared-worker/_generated/comm_query_executor.wasm b/web/shared-worker/_generated/comm_query_executor.wasm index 0000000000000000000000000000000000000000..0000000000000000000000000000000000000000 GIT binary patch literal 0 Hc$@