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 @@ -180,6 +180,37 @@ 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 create_media_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS media ( " @@ -400,10 +431,10 @@ 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" + " 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"); } @@ -411,8 +442,8 @@ 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " report TEXT NOT NULL" ");"; return create_table(db, query, "reports"); } @@ -489,8 +520,8 @@ 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " user_info TEXT NOT NULL" ");"; return create_table(db, query, "users"); } @@ -498,8 +529,8 @@ 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " keyserver_info TEXT NOT NULL" ");"; return create_table(db, query, "keyservers"); } @@ -522,8 +553,8 @@ 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " community_info TEXT NOT NULL" ");"; return create_table(db, query, "communities"); } @@ -531,13 +562,13 @@ 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)" + " 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" @@ -549,8 +580,8 @@ 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " thread_hash TEXT NOT NULL" ");"; return create_table(db, query, "integrity_store"); } @@ -558,8 +589,8 @@ 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" + " name TEXT UNIQUE PRIMARY KEY NOT NULL," + " data TEXT NOT NULL" ");"; return create_table(db, query, "synced_metadata"); } @@ -567,8 +598,8 @@ 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " keyserver_info TEXT NOT NULL" ");"; bool success = create_table(db, query, "keyservers_synced"); if (!success) { @@ -603,9 +634,9 @@ sqlite3_exec( db, "ALTER TABLE olm_persist_sessions " - " RENAME COLUMN `target_user_id` TO `target_device_id`; " + " RENAME COLUMN `target_user_id` TO `target_device_id`; " "ALTER TABLE olm_persist_sessions " - " ADD COLUMN version INTEGER NOT NULL DEFAULT 1;", + " ADD COLUMN version INTEGER NOT NULL DEFAULT 1;", nullptr, nullptr, &error); @@ -647,14 +678,14 @@ 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)" + " 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" @@ -675,8 +706,8 @@ 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " local_message_info TEXT NOT NULL" ");"; return create_table(db, query, "message_store_local"); } @@ -686,73 +717,80 @@ sqlite3_exec( db, "CREATE TABLE IF NOT EXISTS drafts (" - " key TEXT UNIQUE PRIMARY KEY NOT NULL," - " text TEXT NOT NULL" + " 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" + " 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)," + " JSON_EXTRACT(content, '$.targetMessageID')," + " NULL" + " )" + " )" ");" "CREATE TABLE IF NOT EXISTS olm_persist_account (" - " id INTEGER UNIQUE PRIMARY KEY NOT NULL," - " account_data TEXT NOT NULL" + " 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" + " 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" + " 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" + " 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" ");" "CREATE TABLE IF NOT EXISTS metadata (" - " name TEXT UNIQUE PRIMARY KEY NOT NULL," - " data TEXT NOT NULL" + " 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" + " 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" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " report TEXT NOT NULL" ");" "CREATE TABLE IF NOT EXISTS persist_storage (" @@ -761,44 +799,44 @@ ");" "CREATE TABLE IF NOT EXISTS users (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " user_info TEXT NOT NULL" + " 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" + " 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" + " 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" + " 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," - " PRIMARY KEY (message_id, device_id)" + " 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 TABLE IF NOT EXISTS integrity_store (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " thread_hash TEXT NOT NULL" + " 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" + " name TEXT UNIQUE PRIMARY KEY NOT NULL," + " data TEXT NOT NULL" ");" "CREATE TABLE IF NOT EXISTS aux_users (" @@ -825,8 +863,8 @@ ");" "CREATE TABLE IF NOT EXISTS message_store_local (" - " id TEXT UNIQUE PRIMARY KEY NOT NULL," - " local_message_info TEXT NOT NULL" + " id TEXT UNIQUE PRIMARY KEY NOT NULL," + " local_message_info TEXT NOT NULL" ");" "CREATE INDEX IF NOT EXISTS media_idx_container" @@ -835,6 +873,9 @@ "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);", @@ -1085,7 +1126,8 @@ {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}}}}; + {47, {create_message_store_local_table, true}}, + {48, {create_messages_idx_target_message_type_time, true}}}}; enum class MigrationResult { SUCCESS, FAILURE, NOT_APPLIED }; @@ -1342,7 +1384,7 @@ "SELECT * " "FROM messages " "LEFT JOIN media " - " ON messages.id = media.container " + " ON messages.id = media.container " "ORDER BY messages.id;"; SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), @@ -1356,12 +1398,12 @@ stepResult = sqlite3_step(preparedSQL)) { Message message = Message::fromSQLResult(preparedSQL, 0); if (message.id == prevMsgIdx) { - allMessages.back().second.push_back(Media::fromSQLResult(preparedSQL, 8)); + allMessages.back().second.push_back(Media::fromSQLResult(preparedSQL, 9)); } else { prevMsgIdx = message.id; std::vector mediaForMsg; - if (sqlite3_column_type(preparedSQL, 8) != SQLITE_NULL) { - mediaForMsg.push_back(Media::fromSQLResult(preparedSQL, 8)); + if (sqlite3_column_type(preparedSQL, 9) != SQLITE_NULL) { + mediaForMsg.push_back(Media::fromSQLResult(preparedSQL, 9)); } allMessages.push_back(std::make_pair(std::move(message), mediaForMsg)); } @@ -1744,12 +1786,12 @@ std::vector SQLiteQueryExecutor::getAllKeyservers() const { static std::string getAllKeyserversSQL = "SELECT " - " synced.id, " - " COALESCE(keyservers.keyserver_info, ''), " - " synced.keyserver_info " + " synced.id, " + " COALESCE(keyservers.keyserver_info, ''), " + " synced.keyserver_info " "FROM keyservers_synced synced " "LEFT JOIN keyservers " - " ON synced.id = keyservers.id;"; + " ON synced.id = keyservers.id;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllKeyserversSQL); } @@ -2270,9 +2312,9 @@ std::string query = "DELETE FROM outbound_p2p_messages " "WHERE timestamp <= (" - " SELECT timestamp " - " FROM outbound_p2p_messages" - " WHERE message_id = ?" + " SELECT timestamp " + " FROM outbound_p2p_messages" + " WHERE message_id = ?" ") " "AND device_id IN (?);"; 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$@