Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Files
F3524738
D12759.diff
No One
Temporary
Actions
View File
Edit File
Delete File
View Transforms
Subscribe
Mute Notifications
Award Token
Flag For Later
Size
16 KB
Referenced Files
None
Subscribers
None
D12759.diff
View Options
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<Media> 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<KeyserverInfo> 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<KeyserverInfo>(
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$@<O00001
literal 0
Hc$@<O00001
File Metadata
Details
Attached
Mime Type
text/plain
Expires
Tue, Dec 24, 2:24 PM (8 h, 26 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
2700191
Default Alt Text
D12759.diff (16 KB)
Attached To
Mode
D12759: [SQLite] Add target_message generated column and index
Attached
Detach File
Event Timeline
Log In to Comment