Page MenuHomePhabricator
Differential D14140

[lib] Introduce ChatThreadItemLoaderCache
ClosedPublic
Actions

Authored by ashoat on Dec 11 2024, 3:49 PM.
Tags
None
Referenced Files
F6091571: D14140.id46394.diff
Mon, Apr 21, 1:54 AM
F6087426: D14140.id46393.diff
Mon, Apr 21, 12:22 AM
F6056217: D14140.diff
Sun, Apr 20, 7:13 AM
Unknown Object (File)
Mar 11 2025, 4:10 PM
Unknown Object (File)
Mar 11 2025, 4:10 PM
Unknown Object (File)
Mar 11 2025, 4:00 PM
Unknown Object (File)
Mar 6 2025, 2:52 PM
Unknown Object (File)
Feb 26 2025, 4:45 AM
View All 58 Files
Subscribers
None

Details

Reviewers
tomek
angelika
Commits
rCOMM55b685af24c9: [lib] Introduce ChatThreadItemLoaderCache
Summary

This is the core of the work to address ENG-9952.

Prior to this stack, we would kick off Promises for each thread. At the end of the stack, we want to only kick off enough Promises to generate the "guaranteed top N", which can be thought of "if every Promise was resolved, which would be in the top N"?

We are able to achieve this guarantee using the "at most time", which was introduced in D14136. Most of the magic is in the loadMostRecent function.

Depends on D14139

Test Plan

I tested this task by playing around with the ChatThreadList on mobile while using a stopwatch to measure how long various operations took. I tested the updated code 3 times against both the most recent Testflight build as well as master. I found that performance was approximately the same as before. I tested scrolling down, selecting threads that were read, selecting threads that were unread, and search.

Diff Detail

Repository
rCOMM Comm
Lint
Lint Not Applicable
Unit
Tests Not Applicable

Revision Contents
Changeset List

Diff 46441

View Options

lib/utils/chat-thread-item-loader-cache.js

#include "SQLiteQueryExecutor.h" #include "SQLiteQueryExecutor.h"
#include "Logger.h" #include "Logger.h"
#include "SQLiteSchema.h"
#include "SQLiteUtils.h" #include "SQLiteUtils.h"
#include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h" #include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h"
#include "../NativeModules/PersistentStorageUtilities/ThreadOperationsUtilities/ThreadTypeEnum.h" #include "../NativeModules/PersistentStorageUtilities/ThreadOperationsUtilities/ThreadTypeEnum.h"
#include "entities/CommunityInfo.h" #include "entities/CommunityInfo.h"
#include "entities/EntityQueryHelpers.h" #include "entities/EntityQueryHelpers.h"
#include "entities/EntryInfo.h" #include "entities/EntryInfo.h"
#include "entities/IntegrityThreadHash.h" #include "entities/IntegrityThreadHash.h"
▲ Show 20 Lines • Show All 41 Lines • ▼ Show 20 Lines
}; };
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
NativeSQLiteConnectionManager SQLiteQueryExecutor::connectionManager; NativeSQLiteConnectionManager SQLiteQueryExecutor::connectionManager;
#else #else
SQLiteConnectionManager SQLiteQueryExecutor::connectionManager; SQLiteConnectionManager SQLiteQueryExecutor::connectionManager;
#endif #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<int>(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<int>(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 = ...;` // We don't want to run `PRAGMA key = ...;`
// on main web database. The context is here: // on main web database. The context is here:
// https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web
void default_on_db_open_callback(sqlite3 *db) { void default_on_db_open_callback(sqlite3 *db) {
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
SQLiteUtils::setEncryptionKey(db, SQLiteQueryExecutor::backupDataKey); SQLiteUtils::setEncryptionKey(db, SQLiteQueryExecutor::backupDataKey);
#endif #endif
} }
typedef bool ShouldBeInTransaction;
typedef std::function<bool(sqlite3 *)> MigrateFunction;
typedef std::pair<MigrateFunction, ShouldBeInTransaction> SQLiteMigration;
std::vector<std::pair<unsigned int, SQLiteMigration>> 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() { void SQLiteQueryExecutor::migrate() {
// We don't want to run `PRAGMA key = ...;` // We don't want to run `PRAGMA key = ...;`
// on main web database. The context is here: // on main web database. The context is here:
// https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
SQLiteUtils::validateEncryption( SQLiteUtils::validateEncryption(
SQLiteQueryExecutor::sqliteFilePath, SQLiteQueryExecutor::backupDataKey); SQLiteQueryExecutor::sqliteFilePath, SQLiteQueryExecutor::backupDataKey);
#endif #endif
sqlite3 *db; sqlite3 *db;
sqlite3_open(SQLiteQueryExecutor::sqliteFilePath.c_str(), &db); sqlite3_open(SQLiteQueryExecutor::sqliteFilePath.c_str(), &db);
default_on_db_open_callback(db); default_on_db_open_callback(db);
std::stringstream db_path; std::stringstream db_path;
db_path << "db path: " << SQLiteQueryExecutor::sqliteFilePath.c_str() db_path << "db path: " << SQLiteQueryExecutor::sqliteFilePath.c_str()
<< std::endl; << std::endl;
Logger::log(db_path.str()); Logger::log(db_path.str());
auto db_version = SQLiteUtils::getDatabaseVersion(db); auto db_version = SQLiteUtils::getDatabaseVersion(db);
std::stringstream version_msg; std::stringstream version_msg;
version_msg << "db version: " << db_version << std::endl; version_msg << "db version: " << db_version << std::endl;
Logger::log(version_msg.str()); Logger::log(version_msg.str());
if (db_version == 0) { if (db_version == 0) {
auto db_created = set_up_database(db); auto db_created = SQLiteSchema::setupDatabase(db);
if (!db_created) { if (!db_created) {
sqlite3_close(db); sqlite3_close(db);
Logger::log("Database structure creation error."); Logger::log("Database structure creation error.");
throw std::runtime_error("Database structure creation error"); throw std::runtime_error("Database structure creation error");
} }
Logger::log("Database structure created."); Logger::log("Database structure created.");
sqlite3_close(db); sqlite3_close(db);
return; return;
} }
for (const auto &[idx, migration] : migrations) { SQLiteSchema::legacyMigrate(db);
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());
}
}
sqlite3_close(db); sqlite3_close(db);
} }
SQLiteQueryExecutor::SQLiteQueryExecutor() { SQLiteQueryExecutor::SQLiteQueryExecutor() {
SQLiteQueryExecutor::migrate(); SQLiteQueryExecutor::migrate();
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
SQLiteQueryExecutor::initializeTablesForLogMonitoring(); SQLiteQueryExecutor::initializeTablesForLogMonitoring();
▲ Show 20 Lines • Show All 1,979 Lines • Show Last 20 Lines
View Options

lib/utils/chat-thread-item-loader-cache.test.js

#include "SQLiteQueryExecutor.h" #include "SQLiteQueryExecutor.h"
#include "Logger.h" #include "Logger.h"
#include "SQLiteSchema.h"
#include "SQLiteUtils.h" #include "SQLiteUtils.h"
#include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h" #include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h"
#include "../NativeModules/PersistentStorageUtilities/ThreadOperationsUtilities/ThreadTypeEnum.h" #include "../NativeModules/PersistentStorageUtilities/ThreadOperationsUtilities/ThreadTypeEnum.h"
#include "entities/CommunityInfo.h" #include "entities/CommunityInfo.h"
#include "entities/EntityQueryHelpers.h" #include "entities/EntityQueryHelpers.h"
#include "entities/EntryInfo.h" #include "entities/EntryInfo.h"
#include "entities/IntegrityThreadHash.h" #include "entities/IntegrityThreadHash.h"
▲ Show 20 Lines • Show All 41 Lines • ▼ Show 20 Lines
}; };
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
NativeSQLiteConnectionManager SQLiteQueryExecutor::connectionManager; NativeSQLiteConnectionManager SQLiteQueryExecutor::connectionManager;
#else #else
SQLiteConnectionManager SQLiteQueryExecutor::connectionManager; SQLiteConnectionManager SQLiteQueryExecutor::connectionManager;
#endif #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<int>(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<int>(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 = ...;` // We don't want to run `PRAGMA key = ...;`
// on main web database. The context is here: // on main web database. The context is here:
// https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web
void default_on_db_open_callback(sqlite3 *db) { void default_on_db_open_callback(sqlite3 *db) {
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
SQLiteUtils::setEncryptionKey(db, SQLiteQueryExecutor::backupDataKey); SQLiteUtils::setEncryptionKey(db, SQLiteQueryExecutor::backupDataKey);
#endif #endif
} }
typedef bool ShouldBeInTransaction;
typedef std::function<bool(sqlite3 *)> MigrateFunction;
typedef std::pair<MigrateFunction, ShouldBeInTransaction> SQLiteMigration;
std::vector<std::pair<unsigned int, SQLiteMigration>> 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() { void SQLiteQueryExecutor::migrate() {
// We don't want to run `PRAGMA key = ...;` // We don't want to run `PRAGMA key = ...;`
// on main web database. The context is here: // on main web database. The context is here:
// https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
SQLiteUtils::validateEncryption( SQLiteUtils::validateEncryption(
SQLiteQueryExecutor::sqliteFilePath, SQLiteQueryExecutor::backupDataKey); SQLiteQueryExecutor::sqliteFilePath, SQLiteQueryExecutor::backupDataKey);
#endif #endif
sqlite3 *db; sqlite3 *db;
sqlite3_open(SQLiteQueryExecutor::sqliteFilePath.c_str(), &db); sqlite3_open(SQLiteQueryExecutor::sqliteFilePath.c_str(), &db);
default_on_db_open_callback(db); default_on_db_open_callback(db);
std::stringstream db_path; std::stringstream db_path;
db_path << "db path: " << SQLiteQueryExecutor::sqliteFilePath.c_str() db_path << "db path: " << SQLiteQueryExecutor::sqliteFilePath.c_str()
<< std::endl; << std::endl;
Logger::log(db_path.str()); Logger::log(db_path.str());
auto db_version = SQLiteUtils::getDatabaseVersion(db); auto db_version = SQLiteUtils::getDatabaseVersion(db);
std::stringstream version_msg; std::stringstream version_msg;
version_msg << "db version: " << db_version << std::endl; version_msg << "db version: " << db_version << std::endl;
Logger::log(version_msg.str()); Logger::log(version_msg.str());
if (db_version == 0) { if (db_version == 0) {
auto db_created = set_up_database(db); auto db_created = SQLiteSchema::setupDatabase(db);
if (!db_created) { if (!db_created) {
sqlite3_close(db); sqlite3_close(db);
Logger::log("Database structure creation error."); Logger::log("Database structure creation error.");
throw std::runtime_error("Database structure creation error"); throw std::runtime_error("Database structure creation error");
} }
Logger::log("Database structure created."); Logger::log("Database structure created.");
sqlite3_close(db); sqlite3_close(db);
return; return;
} }
for (const auto &[idx, migration] : migrations) { SQLiteSchema::legacyMigrate(db);
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());
}
}
sqlite3_close(db); sqlite3_close(db);
} }
SQLiteQueryExecutor::SQLiteQueryExecutor() { SQLiteQueryExecutor::SQLiteQueryExecutor() {
SQLiteQueryExecutor::migrate(); SQLiteQueryExecutor::migrate();
#ifndef EMSCRIPTEN #ifndef EMSCRIPTEN
SQLiteQueryExecutor::initializeTablesForLogMonitoring(); SQLiteQueryExecutor::initializeTablesForLogMonitoring();
▲ Show 20 Lines • Show All 1,979 Lines • Show Last 20 Lines