diff --git a/lib/types/thread-types.js b/lib/types/thread-types.js index 605ea25c1..1f26afd35 100644 --- a/lib/types/thread-types.js +++ b/lib/types/thread-types.js @@ -1,470 +1,471 @@ // @flow import t, { type TInterface } from 'tcomb'; import { type AvatarDBContent, type ClientAvatar, clientAvatarValidator, type UpdateUserAvatarRequest, } from './avatar-types.js'; import type { CalendarQuery } from './entry-types.js'; import type { Media } from './media-types.js'; import type { MessageTruncationStatuses, RawMessageInfo, } from './message-types.js'; import type { RawThreadInfo, ResolvedThreadInfo, ThreadInfo, ThickRawThreadInfo, } from './minimally-encoded-thread-permissions-types.js'; import { type ThreadSubscription, threadSubscriptionValidator, } from './subscription-types.js'; import { type ThreadPermissionsInfo, threadPermissionsInfoValidator, type ThreadRolePermissionsBlob, threadRolePermissionsBlobValidator, type UserSurfacedPermission, } from './thread-permission-types.js'; import { type ThinThreadType, type ThickThreadType, threadTypeValidator, } from './thread-types-enum.js'; import type { ClientUpdateInfo, ServerUpdateInfo } from './update-types.js'; import type { UserInfo, UserInfos } from './user-types.js'; import type { SpecialRole } from '../permissions/special-roles.js'; import { type ThreadEntity } from '../utils/entity-text.js'; import { tID, tShape, tUserID } from '../utils/validation-utils.js'; export type LegacyMemberInfo = { +id: string, +role: ?string, +permissions: ThreadPermissionsInfo, +isSender: boolean, }; export const legacyMemberInfoValidator: TInterface = tShape({ id: tUserID, role: t.maybe(tID), permissions: threadPermissionsInfoValidator, isSender: t.Boolean, }); export type ClientLegacyRoleInfo = { +id: string, +name: string, +permissions: ThreadRolePermissionsBlob, +isDefault: boolean, }; export const clientLegacyRoleInfoValidator: TInterface = tShape({ id: tID, name: t.String, permissions: threadRolePermissionsBlobValidator, isDefault: t.Boolean, }); export type ServerLegacyRoleInfo = { +id: string, +name: string, +permissions: ThreadRolePermissionsBlob, +isDefault: boolean, +specialRole: ?SpecialRole, }; export type LegacyThreadCurrentUserInfo = { +role: ?string, +permissions: ThreadPermissionsInfo, +subscription: ThreadSubscription, +unread: ?boolean, }; export const legacyThreadCurrentUserInfoValidator: TInterface = tShape({ role: t.maybe(tID), permissions: threadPermissionsInfoValidator, subscription: threadSubscriptionValidator, unread: t.maybe(t.Boolean), }); export type LegacyThinRawThreadInfo = { +id: string, +type: ThinThreadType, +name?: ?string, +avatar?: ?ClientAvatar, +description?: ?string, +color: string, // hex, without "#" or "0x" +creationTime: number, // millisecond timestamp +parentThreadID: ?string, +containingThreadID?: ?string, +community: ?string, +members: $ReadOnlyArray, +roles: { +[id: string]: ClientLegacyRoleInfo }, +currentUser: LegacyThreadCurrentUserInfo, +sourceMessageID?: string, +repliesCount: number, +pinnedCount?: number, }; export type ThickMemberInfo = { +id: string, +role: ?string, +permissions: ThreadPermissionsInfo, +subscription: ThreadSubscription, +isSender: boolean, }; export type LegacyThickRawThreadInfo = { +thick: true, +id: string, +type: ThickThreadType, +name?: ?string, +avatar?: ?ClientAvatar, +description?: ?string, +color: string, // hex, without "#" or "0x" +creationTime: number, // millisecond timestamp +parentThreadID?: ?string, +containingThreadID?: ?string, +members: $ReadOnlyArray, +roles: { +[id: string]: ClientLegacyRoleInfo }, +currentUser: LegacyThreadCurrentUserInfo, +sourceMessageID?: string, +repliesCount: number, +pinnedCount?: number, }; export type LegacyRawThreadInfo = | LegacyThinRawThreadInfo | LegacyThickRawThreadInfo; export type LegacyRawThreadInfos = { +[id: string]: LegacyRawThreadInfo, }; export const legacyRawThreadInfoValidator: TInterface = tShape({ id: tID, type: threadTypeValidator, name: t.maybe(t.String), avatar: t.maybe(clientAvatarValidator), description: t.maybe(t.String), color: t.String, creationTime: t.Number, parentThreadID: t.maybe(tID), containingThreadID: t.maybe(tID), community: t.maybe(tID), members: t.list(legacyMemberInfoValidator), roles: t.dict(tID, clientLegacyRoleInfoValidator), currentUser: legacyThreadCurrentUserInfoValidator, sourceMessageID: t.maybe(tID), repliesCount: t.Number, pinnedCount: t.maybe(t.Number), }); export type MixedRawThreadInfos = { +[id: string]: LegacyRawThreadInfo | RawThreadInfo, }; export type ThickRawThreadInfos = { +[id: string]: ThickRawThreadInfo, }; export type RawThreadInfos = { +[id: string]: RawThreadInfo, }; export type ServerMemberInfo = { +id: string, +role: ?string, +permissions: ThreadPermissionsInfo, +subscription: ThreadSubscription, +unread: ?boolean, +isSender: boolean, }; export type ServerThreadInfo = { +id: string, +type: ThinThreadType, +name: ?string, +avatar?: AvatarDBContent, +description: ?string, +color: string, // hex, without "#" or "0x" +creationTime: number, // millisecond timestamp +parentThreadID: ?string, +containingThreadID: ?string, +community: ?string, +depth: number, +members: $ReadOnlyArray, +roles: { +[id: string]: ServerLegacyRoleInfo }, +sourceMessageID?: string, +repliesCount: number, +pinnedCount: number, }; export type LegacyThreadStore = { +threadInfos: MixedRawThreadInfos, }; export type ThreadStore = { +threadInfos: RawThreadInfos, }; export type ClientDBThreadInfo = { +id: string, +type: number, +name: ?string, +avatar?: ?string, +description: ?string, +color: string, +creationTime: string, +parentThreadID: ?string, +containingThreadID: ?string, +community: ?string, +members: string, +roles: string, +currentUser: string, +sourceMessageID?: string, +repliesCount: number, +pinnedCount?: number, + +timestamps?: ?string, }; export type ThreadDeletionRequest = { +threadID: string, +accountPassword?: empty, }; export type RemoveMembersRequest = { +threadID: string, +memberIDs: $ReadOnlyArray, }; export type RoleChangeRequest = { +threadID: string, +memberIDs: $ReadOnlyArray, +role: string, }; export type ChangeThreadSettingsResult = { +updatesResult: { +newUpdates: $ReadOnlyArray, }, +newMessageInfos: $ReadOnlyArray, }; export type ChangeThreadSettingsPayload = { +threadID: string, +updatesResult: { +newUpdates: $ReadOnlyArray, }, +newMessageInfos: $ReadOnlyArray, }; export type LeaveThreadRequest = { +threadID: string, }; export type LeaveThreadResult = { +updatesResult: { +newUpdates: $ReadOnlyArray, }, }; export type LeaveThreadPayload = { +updatesResult: { +newUpdates: $ReadOnlyArray, }, }; export type ThreadChanges = Partial<{ +type: ThinThreadType, +name: string, +description: string, +color: string, +parentThreadID: ?string, +newMemberIDs: $ReadOnlyArray, +avatar: UpdateUserAvatarRequest, }>; export type UpdateThreadRequest = { +threadID: string, +changes: ThreadChanges, +accountPassword?: empty, }; export type BaseNewThreadRequest = { +id?: ?string, +name?: ?string, +description?: ?string, +color?: ?string, +parentThreadID?: ?string, +initialMemberIDs?: ?$ReadOnlyArray, +ghostMemberIDs?: ?$ReadOnlyArray, }; type NewThinThreadRequest = | { +type: 3 | 4 | 6 | 7 | 8 | 9 | 10 | 11 | 12, ...BaseNewThreadRequest, } | { +type: 5, +sourceMessageID: string, ...BaseNewThreadRequest, }; export type ClientNewThinThreadRequest = { ...NewThinThreadRequest, +calendarQuery: CalendarQuery, }; export type ServerNewThinThreadRequest = { ...NewThinThreadRequest, +calendarQuery?: ?CalendarQuery, }; export type NewThreadResponse = { +updatesResult: { +newUpdates: $ReadOnlyArray, }, +newMessageInfos: $ReadOnlyArray, +userInfos: UserInfos, +newThreadID: string, }; export type NewThreadResult = { +updatesResult: { +newUpdates: $ReadOnlyArray, }, +newMessageInfos: $ReadOnlyArray, +userInfos: UserInfos, +newThreadID: string, }; export type ServerThreadJoinRequest = { +threadID: string, +calendarQuery?: ?CalendarQuery, +inviteLinkSecret?: string, +defaultSubscription?: ThreadSubscription, }; export type ClientThreadJoinRequest = { +threadID: string, +calendarQuery: CalendarQuery, +inviteLinkSecret?: string, +defaultSubscription?: ThreadSubscription, }; export type ThreadJoinResult = { +updatesResult: { +newUpdates: $ReadOnlyArray, }, +rawMessageInfos: $ReadOnlyArray, +truncationStatuses: MessageTruncationStatuses, +userInfos: UserInfos, }; export type ThreadJoinPayload = { +updatesResult: { newUpdates: $ReadOnlyArray, }, +rawMessageInfos: $ReadOnlyArray, +truncationStatuses: MessageTruncationStatuses, +userInfos: $ReadOnlyArray, +keyserverID: string, }; export type ThreadFetchMediaResult = { +media: $ReadOnlyArray, }; export type ThreadFetchMediaRequest = { +threadID: string, +limit: number, +offset: number, }; export type SidebarInfo = { +threadInfo: ThreadInfo, +lastUpdatedTime: number, +mostRecentNonLocalMessage: ?string, }; export type ToggleMessagePinRequest = { +messageID: string, +action: 'pin' | 'unpin', }; export type ToggleMessagePinResult = { +newMessageInfos: $ReadOnlyArray, +threadID: string, }; type CreateRoleAction = { +community: string, +name: string, +permissions: $ReadOnlyArray, +action: 'create_role', }; type EditRoleAction = { +community: string, +existingRoleID: string, +name: string, +permissions: $ReadOnlyArray, +action: 'edit_role', }; export type RoleModificationRequest = CreateRoleAction | EditRoleAction; export type RoleModificationResult = { +threadInfo: LegacyRawThreadInfo | RawThreadInfo, +updatesResult: { +newUpdates: $ReadOnlyArray, }, }; export type RoleModificationPayload = { +threadInfo: LegacyRawThreadInfo | RawThreadInfo, +updatesResult: { +newUpdates: $ReadOnlyArray, }, }; export type RoleDeletionRequest = { +community: string, +roleID: string, }; export type RoleDeletionResult = { +threadInfo: LegacyRawThreadInfo | RawThreadInfo, +updatesResult: { +newUpdates: $ReadOnlyArray, }, }; export type RoleDeletionPayload = { +threadInfo: LegacyRawThreadInfo | RawThreadInfo, +updatesResult: { +newUpdates: $ReadOnlyArray, }, }; // We can show a max of 3 sidebars inline underneath their parent in the chat // tab. If there are more, we show a button that opens a modal to see the rest export const maxReadSidebars = 3; // We can show a max of 5 sidebars inline underneath their parent // in the chat tab if every one of the displayed sidebars is unread export const maxUnreadSidebars = 5; export type ThreadStoreThreadInfos = LegacyRawThreadInfos; export type ChatMentionCandidate = { +threadInfo: ResolvedThreadInfo, +rawChatName: string | ThreadEntity, }; export type ChatMentionCandidates = { +[id: string]: ChatMentionCandidate, }; export type ChatMentionCandidatesObj = { +[id: string]: ChatMentionCandidates, }; export type UserProfileThreadInfo = { +threadInfo: ThreadInfo, +pendingPersonalThreadUserInfo?: UserInfo, }; diff --git a/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp b/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp index 41ebb1aaa..c2457fe4f 100644 --- a/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp +++ b/native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp @@ -1,3244 +1,3268 @@ #include "SQLiteQueryExecutor.h" #include "Logger.h" #include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h" #include "../NativeModules/PersistentStorageUtilities/ThreadOperationsUtilities/ThreadTypeEnum.h" #include "entities/CommunityInfo.h" #include "entities/EntityQueryHelpers.h" #include "entities/EntryInfo.h" #include "entities/IntegrityThreadHash.h" #include "entities/KeyserverInfo.h" #include "entities/LocalMessageInfo.h" #include "entities/Metadata.h" #include "entities/SQLiteDataConverters.h" #include "entities/SyncedMetadataEntry.h" #include "entities/UserInfo.h" #include #include #include #ifndef EMSCRIPTEN #include "../CryptoTools/CryptoModule.h" #include "CommSecureStore.h" #include "PlatformSpecificTools.h" #include "StaffUtils.h" #endif const int CONTENT_ACCOUNT_ID = 1; const int NOTIFS_ACCOUNT_ID = 2; namespace comm { std::string SQLiteQueryExecutor::sqliteFilePath; std::string SQLiteQueryExecutor::encryptionKey; std::once_flag SQLiteQueryExecutor::initialized; int SQLiteQueryExecutor::sqlcipherEncryptionKeySize = 64; // Should match constant defined in `native_rust_library/src/constants.rs` std::string SQLiteQueryExecutor::secureStoreEncryptionKeyID = "comm.encryptionKey"; int SQLiteQueryExecutor::backupLogsEncryptionKeySize = 32; std::string SQLiteQueryExecutor::secureStoreBackupLogsEncryptionKeyID = "comm.backupLogsEncryptionKey"; std::string SQLiteQueryExecutor::backupLogsEncryptionKey; #ifndef EMSCRIPTEN NativeSQLiteConnectionManager SQLiteQueryExecutor::connectionManager; std::unordered_set SQLiteQueryExecutor::backedUpTablesBlocklist = { "olm_persist_account", "olm_persist_sessions", "metadata", "outbound_p2p_messages", "inbound_p2p_messages", "integrity_store", "persist_storage", "keyservers", }; #else SQLiteConnectionManager SQLiteQueryExecutor::connectionManager; #endif bool create_table(sqlite3 *db, std::string query, std::string tableName) { char *error; sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error creating '" << tableName << "' table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_drafts_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS drafts (threadID TEXT UNIQUE PRIMARY KEY, " "text TEXT);"; return create_table(db, query, "drafts"); } bool rename_threadID_to_key(sqlite3 *db) { sqlite3_stmt *key_column_stmt; sqlite3_prepare_v2( db, "SELECT name AS col_name FROM pragma_table_xinfo ('drafts') WHERE " "col_name='key';", -1, &key_column_stmt, nullptr); sqlite3_step(key_column_stmt); auto num_bytes = sqlite3_column_bytes(key_column_stmt, 0); sqlite3_finalize(key_column_stmt); if (num_bytes) { return true; } char *error; sqlite3_exec( db, "ALTER TABLE drafts RENAME COLUMN `threadID` TO `key`;", nullptr, nullptr, &error); if (error) { std::ostringstream stringStream; stringStream << "Error occurred renaming threadID column in drafts table " << "to key: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } return true; } bool create_persist_account_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS olm_persist_account(" "id INTEGER UNIQUE PRIMARY KEY NOT NULL, " "account_data TEXT NOT NULL);"; return create_table(db, query, "olm_persist_account"); } bool create_persist_sessions_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS olm_persist_sessions(" "target_user_id TEXT UNIQUE PRIMARY KEY NOT NULL, " "session_data TEXT NOT NULL);"; return create_table(db, query, "olm_persist_sessions"); } bool drop_messages_table(sqlite3 *db) { char *error; sqlite3_exec(db, "DROP TABLE IF EXISTS messages;", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error dropping 'messages' table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool recreate_messages_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS messages ( " "id TEXT UNIQUE PRIMARY KEY NOT NULL, " "local_id TEXT, " "thread TEXT NOT NULL, " "user TEXT NOT NULL, " "type INTEGER NOT NULL, " "future_type INTEGER, " "content TEXT, " "time INTEGER NOT NULL);"; return create_table(db, query, "messages"); } bool create_messages_idx_thread_time(sqlite3 *db) { char *error; sqlite3_exec( db, "CREATE INDEX IF NOT EXISTS messages_idx_thread_time " "ON messages (thread, time);", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error creating (thread, time) index on messages table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_messages_idx_target_message_type_time(sqlite3 *db) { char *error; sqlite3_exec( db, "ALTER TABLE messages " " ADD COLUMN target_message TEXT " " AS (IIF( " " JSON_VALID(content), " " JSON_EXTRACT(content, '$.targetMessageID'), " " NULL " " )); " "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time " " ON messages (target_message, type, time);", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error creating (target_message, type, time) index on messages table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool update_messages_idx_target_message_type_time(sqlite3 *db) { char *error; int sidebarSourceTypeInt = static_cast(MessageType::SIDEBAR_SOURCE); std::string sidebarSourceType = std::to_string(sidebarSourceTypeInt); auto query = "DROP INDEX IF EXISTS messages_idx_target_message_type_time;" "ALTER TABLE messages DROP COLUMN target_message;" "ALTER TABLE messages " " ADD COLUMN target_message TEXT " " AS (IIF(" " JSON_VALID(content)," " COALESCE(" " JSON_EXTRACT(content, '$.targetMessageID')," " IIF(" " type = " + sidebarSourceType + " , JSON_EXTRACT(content, '$.id')," " NULL" " )" " )," " NULL" " ));" "CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time " " ON messages (target_message, type, time);"; sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error creating (target_message, type, time) index on messages table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_media_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS media ( " "id TEXT UNIQUE PRIMARY KEY NOT NULL, " "container TEXT NOT NULL, " "thread TEXT NOT NULL, " "uri TEXT NOT NULL, " "type TEXT NOT NULL, " "extras TEXT NOT NULL);"; return create_table(db, query, "media"); } bool create_media_idx_container(sqlite3 *db) { char *error; sqlite3_exec( db, "CREATE INDEX IF NOT EXISTS media_idx_container " "ON media (container);", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error creating (container) index on media table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_threads_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS threads ( " "id TEXT UNIQUE PRIMARY KEY NOT NULL, " "type INTEGER NOT NULL, " "name TEXT, " "description TEXT, " "color TEXT NOT NULL, " "creation_time BIGINT NOT NULL, " "parent_thread_id TEXT, " "containing_thread_id TEXT, " "community TEXT, " "members TEXT NOT NULL, " "roles TEXT NOT NULL, " "current_user TEXT NOT NULL, " "source_message_id TEXT, " "replies_count INTEGER NOT NULL);"; return create_table(db, query, "threads"); } bool update_threadID_for_pending_threads_in_drafts(sqlite3 *db) { char *error; sqlite3_exec( db, "UPDATE drafts SET key = " "REPLACE(REPLACE(REPLACE(REPLACE(key, 'type4/', '')," "'type5/', ''),'type6/', ''),'type7/', '')" "WHERE key LIKE 'pending/%'", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error update pending threadIDs on drafts table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool enable_write_ahead_logging_mode(sqlite3 *db) { char *error; sqlite3_exec(db, "PRAGMA journal_mode=wal;", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error enabling write-ahead logging mode: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_metadata_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS metadata ( " "name TEXT UNIQUE PRIMARY KEY NOT NULL, " "data TEXT);"; return create_table(db, query, "metadata"); } bool add_not_null_constraint_to_drafts(sqlite3 *db) { char *error; sqlite3_exec( db, "CREATE TABLE IF NOT EXISTS temporary_drafts (" "key TEXT UNIQUE PRIMARY KEY NOT NULL, " "text TEXT NOT NULL);" "INSERT INTO temporary_drafts SELECT * FROM drafts " "WHERE key IS NOT NULL AND text IS NOT NULL;" "DROP TABLE drafts;" "ALTER TABLE temporary_drafts RENAME TO drafts;", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error adding NOT NULL constraint to drafts table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool add_not_null_constraint_to_metadata(sqlite3 *db) { char *error; sqlite3_exec( db, "CREATE TABLE IF NOT EXISTS temporary_metadata (" "name TEXT UNIQUE PRIMARY KEY NOT NULL, " "data TEXT NOT NULL);" "INSERT INTO temporary_metadata SELECT * FROM metadata " "WHERE data IS NOT NULL;" "DROP TABLE metadata;" "ALTER TABLE temporary_metadata RENAME TO metadata;", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error adding NOT NULL constraint to metadata table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool add_avatar_column_to_threads_table(sqlite3 *db) { char *error; sqlite3_exec( db, "ALTER TABLE threads ADD COLUMN avatar TEXT;", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error adding avatar column to threads table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool add_pinned_count_column_to_threads(sqlite3 *db) { sqlite3_stmt *pinned_column_stmt; sqlite3_prepare_v2( db, "SELECT name AS col_name FROM pragma_table_xinfo ('threads') WHERE " "col_name='pinned_count';", -1, &pinned_column_stmt, nullptr); sqlite3_step(pinned_column_stmt); auto num_bytes = sqlite3_column_bytes(pinned_column_stmt, 0); sqlite3_finalize(pinned_column_stmt); if (num_bytes) { return true; } char *error; sqlite3_exec( db, "ALTER TABLE threads ADD COLUMN pinned_count INTEGER NOT NULL DEFAULT 0;", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error adding pinned_count column to threads table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_message_store_threads_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS message_store_threads (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " start_reached INTEGER NOT NULL," " last_navigated_to BIGINT NOT NULL," " last_pruned BIGINT NOT NULL" ");"; return create_table(db, query, "message_store_threads"); } bool create_reports_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS reports (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " report TEXT NOT NULL" ");"; return create_table(db, query, "reports"); } bool create_persist_storage_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS persist_storage (" " key TEXT UNIQUE PRIMARY KEY NOT NULL," " item TEXT NOT NULL" ");"; return create_table(db, query, "persist_storage"); } bool recreate_message_store_threads_table(sqlite3 *db) { char *errMsg = 0; // 1. Create table without `last_navigated_to` or `last_pruned`. std::string create_new_table_query = "CREATE TABLE IF NOT EXISTS temp_message_store_threads (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " start_reached INTEGER NOT NULL" ");"; if (sqlite3_exec(db, create_new_table_query.c_str(), NULL, NULL, &errMsg) != SQLITE_OK) { Logger::log( "Error creating temp_message_store_threads: " + std::string{errMsg}); sqlite3_free(errMsg); return false; } // 2. Dump data from existing `message_store_threads` table into temp table. std::string copy_data_query = "INSERT INTO temp_message_store_threads (id, start_reached)" "SELECT id, start_reached FROM message_store_threads;"; if (sqlite3_exec(db, copy_data_query.c_str(), NULL, NULL, &errMsg) != SQLITE_OK) { Logger::log( "Error dumping data from existing message_store_threads to " "temp_message_store_threads: " + std::string{errMsg}); sqlite3_free(errMsg); return false; } // 3. Drop the existing `message_store_threads` table. std::string drop_old_table_query = "DROP TABLE message_store_threads;"; if (sqlite3_exec(db, drop_old_table_query.c_str(), NULL, NULL, &errMsg) != SQLITE_OK) { Logger::log( "Error dropping message_store_threads table: " + std::string{errMsg}); sqlite3_free(errMsg); return false; } // 4. Rename the temp table back to `message_store_threads`. std::string rename_table_query = "ALTER TABLE temp_message_store_threads RENAME TO message_store_threads;"; if (sqlite3_exec(db, rename_table_query.c_str(), NULL, NULL, &errMsg) != SQLITE_OK) { Logger::log( "Error renaming temp_message_store_threads to message_store_threads: " + std::string{errMsg}); sqlite3_free(errMsg); return false; } return true; } bool create_users_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS users (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " user_info TEXT NOT NULL" ");"; return create_table(db, query, "users"); } bool create_keyservers_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS keyservers (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " keyserver_info TEXT NOT NULL" ");"; return create_table(db, query, "keyservers"); } bool enable_rollback_journal_mode(sqlite3 *db) { char *error; sqlite3_exec(db, "PRAGMA journal_mode=DELETE;", nullptr, nullptr, &error); if (!error) { return true; } std::stringstream error_message; error_message << "Error disabling write-ahead logging mode: " << error; Logger::log(error_message.str()); sqlite3_free(error); return false; } bool create_communities_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS communities (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " community_info TEXT NOT NULL" ");"; return create_table(db, query, "communities"); } bool create_messages_to_device_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS messages_to_device (" " message_id TEXT NOT NULL," " device_id TEXT NOT NULL," " user_id TEXT NOT NULL," " timestamp BIGINT NOT NULL," " plaintext TEXT NOT NULL," " ciphertext TEXT NOT NULL," " PRIMARY KEY (message_id, device_id)" ");" "CREATE INDEX IF NOT EXISTS messages_to_device_idx_id_timestamp" " ON messages_to_device (device_id, timestamp);"; return create_table(db, query, "messages_to_device"); } bool create_integrity_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS integrity_store (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " thread_hash TEXT NOT NULL" ");"; return create_table(db, query, "integrity_store"); } bool create_synced_metadata_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS synced_metadata (" " name TEXT UNIQUE PRIMARY KEY NOT NULL," " data TEXT NOT NULL" ");"; return create_table(db, query, "synced_metadata"); } bool create_keyservers_synced(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS keyservers_synced (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " keyserver_info TEXT NOT NULL" ");"; bool success = create_table(db, query, "keyservers_synced"); if (!success) { return false; } std::string copyData = "INSERT INTO keyservers_synced (id, keyserver_info)" "SELECT id, keyserver_info " "FROM keyservers;"; char *error; sqlite3_exec(db, copyData.c_str(), nullptr, nullptr, &error); if (error) { return false; } return true; } bool create_aux_user_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS aux_users (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " aux_user_info TEXT NOT NULL" ");"; return create_table(db, query, "aux_users"); } bool add_version_column_to_olm_persist_sessions_table(sqlite3 *db) { char *error; sqlite3_exec( db, "ALTER TABLE olm_persist_sessions " " RENAME COLUMN `target_user_id` TO `target_device_id`; " "ALTER TABLE olm_persist_sessions " " ADD COLUMN version INTEGER NOT NULL DEFAULT 1;", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error updating olm_persist_sessions table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_thread_activity_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS thread_activity (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " thread_activity_store_entry TEXT NOT NULL" ");"; return create_table(db, query, "thread_activity"); } bool create_received_messages_to_device(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS received_messages_to_device (" " id INTEGER PRIMARY KEY," " message_id TEXT NOT NULL," " sender_device_id TEXT NOT NULL," " plaintext TEXT NOT NULL," " status TEXT NOT NULL" ");"; return create_table(db, query, "received_messages_to_device"); } bool recreate_outbound_p2p_messages_table(sqlite3 *db) { std::string query = "DROP TABLE IF EXISTS messages_to_device;" "CREATE TABLE IF NOT EXISTS outbound_p2p_messages (" " message_id TEXT NOT NULL," " device_id TEXT NOT NULL," " user_id TEXT NOT NULL," " timestamp BIGINT NOT NULL," " plaintext TEXT NOT NULL," " ciphertext TEXT NOT NULL," " status TEXT NOT NULL," " PRIMARY KEY (message_id, device_id)" ");" "CREATE INDEX IF NOT EXISTS outbound_p2p_messages_idx_id_timestamp" " ON outbound_p2p_messages (device_id, timestamp);"; return create_table(db, query, "outbound_p2p_messages"); } bool create_entries_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS entries (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " entry TEXT NOT NULL" ");"; return create_table(db, query, "entries"); } bool create_message_store_local_table(sqlite3 *db) { std::string query = "CREATE TABLE IF NOT EXISTS message_store_local (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " local_message_info TEXT NOT NULL" ");"; return create_table(db, query, "message_store_local"); } bool add_supports_auto_retry_column_to_p2p_messages_table(sqlite3 *db) { char *error; sqlite3_exec( db, "ALTER TABLE outbound_p2p_messages" " ADD COLUMN supports_auto_retry INTEGER DEFAULT 0", nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream stringStream; stringStream << "Error updating outbound_p2p_messages table: " << error; Logger::log(stringStream.str()); sqlite3_free(error); return false; } bool create_message_search_table(sqlite3 *db) { std::string query = "CREATE VIRTUAL TABLE IF NOT EXISTS message_search USING fts5(" " original_message_id UNINDEXED," " message_id UNINDEXED," " processed_content," " tokenize = porter" ");"; return create_table(db, query, "message_search"); } bool recreate_inbound_p2p_messages_table(sqlite3 *db) { std::string query = "DROP TABLE IF EXISTS received_messages_to_device;" "CREATE TABLE IF NOT EXISTS inbound_p2p_messages (" " id INTEGER PRIMARY KEY," " message_id TEXT NOT NULL," " sender_device_id TEXT NOT NULL," " plaintext TEXT NOT NULL," " status TEXT NOT NULL," " sender_user_id TEXT NOT NULL" ");"; return create_table(db, query, "inbound_p2p_messages"); } +bool add_timestamps_column_to_threads_table(sqlite3 *db) { + char *error; + sqlite3_exec( + db, + "ALTER TABLE threads" + " ADD COLUMN timestamps TEXT;", + nullptr, + nullptr, + &error); + + if (!error) { + return true; + } + + std::ostringstream stringStream; + stringStream << "Error updating threads table: " << error; + Logger::log(stringStream.str()); + + sqlite3_free(error); + return false; +} + bool create_schema(sqlite3 *db) { char *error; int sidebarSourceTypeInt = static_cast(MessageType::SIDEBAR_SOURCE); std::string sidebarSourceType = std::to_string(sidebarSourceTypeInt); auto query = "CREATE TABLE IF NOT EXISTS drafts (" " key TEXT UNIQUE PRIMARY KEY NOT NULL," " text TEXT NOT NULL" ");" "CREATE TABLE IF NOT EXISTS messages (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " local_id TEXT," " thread TEXT NOT NULL," " user TEXT NOT NULL," " type INTEGER NOT NULL," " future_type INTEGER," " content TEXT," " time INTEGER NOT NULL," " target_message TEXT AS (" " IIF(" " JSON_VALID(content)," " COALESCE(" " JSON_EXTRACT(content, '$.targetMessageID')," " IIF(" " type = " + sidebarSourceType + " , JSON_EXTRACT(content, '$.id')," " NULL" " )" " )," " NULL" " )" " )" ");" "CREATE TABLE IF NOT EXISTS olm_persist_account (" " id INTEGER UNIQUE PRIMARY KEY NOT NULL," " account_data TEXT NOT NULL" ");" "CREATE TABLE IF NOT EXISTS olm_persist_sessions (" " target_device_id TEXT UNIQUE PRIMARY KEY NOT NULL," " session_data TEXT NOT NULL," " version INTEGER NOT NULL DEFAULT 1" ");" "CREATE TABLE IF NOT EXISTS media (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " container TEXT NOT NULL," " thread TEXT NOT NULL," " uri TEXT NOT NULL," " type TEXT NOT NULL," " extras TEXT NOT NULL" ");" "CREATE TABLE IF NOT EXISTS threads (" " id TEXT UNIQUE PRIMARY KEY NOT NULL," " type INTEGER NOT NULL," " name TEXT," " description TEXT," " color TEXT NOT NULL," " creation_time BIGINT NOT NULL," " parent_thread_id TEXT," " containing_thread_id TEXT," " community TEXT," " members TEXT NOT NULL," " roles TEXT NOT NULL," " current_user TEXT NOT NULL," " source_message_id TEXT," " replies_count INTEGER NOT NULL," " avatar TEXT," - " pinned_count INTEGER NOT NULL DEFAULT 0" + " 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 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);"; 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; } void set_encryption_key( sqlite3 *db, const std::string &encryptionKey = SQLiteQueryExecutor::encryptionKey) { std::string set_encryption_key_query = "PRAGMA key = \"x'" + encryptionKey + "'\";"; char *error_set_key; sqlite3_exec( db, set_encryption_key_query.c_str(), nullptr, nullptr, &error_set_key); if (error_set_key) { std::ostringstream error_message; error_message << "Failed to set encryption key: " << error_set_key; throw std::system_error( ECANCELED, std::generic_category(), error_message.str()); } } int get_database_version(sqlite3 *db) { sqlite3_stmt *user_version_stmt; sqlite3_prepare_v2( db, "PRAGMA user_version;", -1, &user_version_stmt, nullptr); sqlite3_step(user_version_stmt); int current_user_version = sqlite3_column_int(user_version_stmt, 0); sqlite3_finalize(user_version_stmt); return current_user_version; } bool set_database_version(sqlite3 *db, int db_version) { std::stringstream update_version; update_version << "PRAGMA user_version=" << db_version << ";"; auto update_version_str = update_version.str(); char *error; sqlite3_exec(db, update_version_str.c_str(), nullptr, nullptr, &error); if (!error) { return true; } std::ostringstream errorStream; errorStream << "Error setting database version to " << db_version << ": " << error; Logger::log(errorStream.str()); sqlite3_free(error); return false; } // We don't want to run `PRAGMA key = ...;` // on main web database. The context is here: // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web void default_on_db_open_callback(sqlite3 *db) { #ifndef EMSCRIPTEN set_encryption_key(db); #endif } // This is a temporary solution. In future we want to keep // a separate table for blob hashes. Tracked on Linear: // https://linear.app/comm/issue/ENG-6261/introduce-blob-hash-table std::string blob_hash_from_blob_service_uri(const std::string &media_uri) { static const std::string blob_service_prefix = "comm-blob-service://"; return media_uri.substr(blob_service_prefix.size()); } bool file_exists(const std::string &file_path) { std::ifstream file(file_path.c_str()); return file.good(); } void attempt_delete_file( const std::string &file_path, const char *error_message) { if (std::remove(file_path.c_str())) { throw std::system_error(errno, std::generic_category(), error_message); } } void attempt_rename_file( const std::string &old_path, const std::string &new_path, const char *error_message) { if (std::rename(old_path.c_str(), new_path.c_str())) { throw std::system_error(errno, std::generic_category(), error_message); } } bool is_database_queryable( sqlite3 *db, bool use_encryption_key, const std::string &path = SQLiteQueryExecutor::sqliteFilePath, const std::string &encryptionKey = SQLiteQueryExecutor::encryptionKey) { char *err_msg; sqlite3_open(path.c_str(), &db); // According to SQLCipher documentation running some SELECT is the only way to // check for key validity if (use_encryption_key) { set_encryption_key(db, encryptionKey); } sqlite3_exec( db, "SELECT COUNT(*) FROM sqlite_master;", nullptr, nullptr, &err_msg); sqlite3_close(db); return !err_msg; } void validate_encryption() { std::string temp_encrypted_db_path = SQLiteQueryExecutor::sqliteFilePath + "_temp_encrypted"; bool temp_encrypted_exists = file_exists(temp_encrypted_db_path); bool default_location_exists = file_exists(SQLiteQueryExecutor::sqliteFilePath); if (temp_encrypted_exists && default_location_exists) { Logger::log( "Previous encryption attempt failed. Repeating encryption process from " "the beginning."); attempt_delete_file( temp_encrypted_db_path, "Failed to delete corrupted encrypted database."); } else if (temp_encrypted_exists && !default_location_exists) { Logger::log( "Moving temporary encrypted database to default location failed in " "previous encryption attempt. Repeating rename step."); attempt_rename_file( temp_encrypted_db_path, SQLiteQueryExecutor::sqliteFilePath, "Failed to move encrypted database to default location."); return; } else if (!default_location_exists) { Logger::log( "Database not present yet. It will be created encrypted under default " "path."); return; } sqlite3 *db; if (is_database_queryable(db, true)) { Logger::log( "Database exists under default path and it is correctly encrypted."); return; } if (!is_database_queryable(db, false)) { Logger::log( "Database exists but it is encrypted with key that was lost. " "Attempting database deletion. New encrypted one will be created."); attempt_delete_file( SQLiteQueryExecutor::sqliteFilePath.c_str(), "Failed to delete database encrypted with lost key."); return; } else { Logger::log( "Database exists but it is not encrypted. Attempting encryption " "process."); } sqlite3_open(SQLiteQueryExecutor::sqliteFilePath.c_str(), &db); std::string createEncryptedCopySQL = "ATTACH DATABASE '" + temp_encrypted_db_path + "' AS encrypted_comm " "KEY \"x'" + SQLiteQueryExecutor::encryptionKey + "'\";" "SELECT sqlcipher_export('encrypted_comm');" "DETACH DATABASE encrypted_comm;"; char *encryption_error; sqlite3_exec( db, createEncryptedCopySQL.c_str(), nullptr, nullptr, &encryption_error); if (encryption_error) { throw std::system_error( ECANCELED, std::generic_category(), "Failed to create encrypted copy of the original database."); } sqlite3_close(db); attempt_delete_file( SQLiteQueryExecutor::sqliteFilePath, "Failed to delete unencrypted database."); attempt_rename_file( temp_encrypted_db_path, SQLiteQueryExecutor::sqliteFilePath, "Failed to move encrypted database to default location."); Logger::log("Encryption completed successfully."); } typedef bool ShouldBeInTransaction; typedef std::function MigrateFunction; typedef std::pair SQLiteMigration; std::vector> migrations{ {{1, {create_drafts_table, true}}, {2, {rename_threadID_to_key, true}}, {4, {create_persist_account_table, true}}, {5, {create_persist_sessions_table, true}}, {15, {create_media_table, true}}, {16, {drop_messages_table, true}}, {17, {recreate_messages_table, true}}, {18, {create_messages_idx_thread_time, true}}, {19, {create_media_idx_container, true}}, {20, {create_threads_table, true}}, {21, {update_threadID_for_pending_threads_in_drafts, true}}, {22, {enable_write_ahead_logging_mode, false}}, {23, {create_metadata_table, true}}, {24, {add_not_null_constraint_to_drafts, true}}, {25, {add_not_null_constraint_to_metadata, true}}, {26, {add_avatar_column_to_threads_table, true}}, {27, {add_pinned_count_column_to_threads, true}}, {28, {create_message_store_threads_table, true}}, {29, {create_reports_table, true}}, {30, {create_persist_storage_table, true}}, {31, {recreate_message_store_threads_table, true}}, {32, {create_users_table, true}}, {33, {create_keyservers_table, true}}, {34, {enable_rollback_journal_mode, false}}, {35, {create_communities_table, true}}, {36, {create_messages_to_device_table, true}}, {37, {create_integrity_table, true}}, {38, {[](sqlite3 *) { return true; }, false}}, {39, {create_synced_metadata_table, true}}, {40, {create_keyservers_synced, true}}, {41, {create_aux_user_table, true}}, {42, {add_version_column_to_olm_persist_sessions_table, true}}, {43, {create_thread_activity_table, true}}, {44, {create_received_messages_to_device, true}}, {45, {recreate_outbound_p2p_messages_table, true}}, {46, {create_entries_table, true}}, {47, {create_message_store_local_table, true}}, {48, {create_messages_idx_target_message_type_time, true}}, {49, {add_supports_auto_retry_column_to_p2p_messages_table, true}}, {50, {create_message_search_table, true}}, {51, {update_messages_idx_target_message_type_time, true}}, - {52, {recreate_inbound_p2p_messages_table, true}}}}; + {52, {recreate_inbound_p2p_messages_table, true}}, + {53, {add_timestamps_column_to_threads_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 = get_database_version(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 = set_database_version(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 = get_database_version(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 = get_database_version(db); if (index <= inner_db_version) { sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); return MigrationResult::NOT_APPLIED; } auto database_version_set = set_database_version(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 = get_database_version(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) || !set_database_version(db, latest_version)) { sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr); return false; } sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr); return true; } void SQLiteQueryExecutor::migrate() { // We don't want to run `PRAGMA key = ...;` // on main web database. The context is here: // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web #ifndef EMSCRIPTEN validate_encryption(); #endif sqlite3 *db; sqlite3_open(SQLiteQueryExecutor::sqliteFilePath.c_str(), &db); default_on_db_open_callback(db); std::stringstream db_path; db_path << "db path: " << SQLiteQueryExecutor::sqliteFilePath.c_str() << std::endl; Logger::log(db_path.str()); auto db_version = get_database_version(db); std::stringstream version_msg; version_msg << "db version: " << db_version << std::endl; Logger::log(version_msg.str()); if (db_version == 0) { auto db_created = set_up_database(db); if (!db_created) { sqlite3_close(db); Logger::log("Database structure creation error."); throw std::runtime_error("Database structure creation error"); } Logger::log("Database structure created."); sqlite3_close(db); return; } for (const auto &[idx, migration] : migrations) { const auto &[applyMigration, shouldBeInTransaction] = migration; MigrationResult migrationResult; if (shouldBeInTransaction) { migrationResult = applyMigrationWithTransaction(db, applyMigration, idx); } else { migrationResult = applyMigrationWithoutTransaction(db, applyMigration, idx); } if (migrationResult == MigrationResult::NOT_APPLIED) { continue; } std::stringstream migration_msg; if (migrationResult == MigrationResult::FAILURE) { migration_msg << "migration " << idx << " failed." << std::endl; Logger::log(migration_msg.str()); sqlite3_close(db); throw std::runtime_error(migration_msg.str()); } if (migrationResult == MigrationResult::SUCCESS) { migration_msg << "migration " << idx << " succeeded." << std::endl; Logger::log(migration_msg.str()); } } sqlite3_close(db); } SQLiteQueryExecutor::SQLiteQueryExecutor() { SQLiteQueryExecutor::migrate(); #ifndef EMSCRIPTEN SQLiteQueryExecutor::initializeTablesForLogMonitoring(); std::string currentBackupID = this->getMetadata("backupID"); if (!StaffUtils::isStaffRelease() || !currentBackupID.size()) { return; } SQLiteQueryExecutor::connectionManager.setLogsMonitoring(true); #endif } SQLiteQueryExecutor::SQLiteQueryExecutor(std::string sqliteFilePath) { SQLiteQueryExecutor::sqliteFilePath = sqliteFilePath; SQLiteQueryExecutor::migrate(); } sqlite3 *SQLiteQueryExecutor::getConnection() { if (SQLiteQueryExecutor::connectionManager.getConnection()) { return SQLiteQueryExecutor::connectionManager.getConnection(); } SQLiteQueryExecutor::connectionManager.initializeConnection( SQLiteQueryExecutor::sqliteFilePath, default_on_db_open_callback); return SQLiteQueryExecutor::connectionManager.getConnection(); } void SQLiteQueryExecutor::closeConnection() { SQLiteQueryExecutor::connectionManager.closeConnection(); } SQLiteQueryExecutor::~SQLiteQueryExecutor() { SQLiteQueryExecutor::closeConnection(); } std::string SQLiteQueryExecutor::getDraft(std::string key) const { static std::string getDraftByPrimaryKeySQL = "SELECT * " "FROM drafts " "WHERE key = ?;"; std::unique_ptr draft = getEntityByPrimaryKey( SQLiteQueryExecutor::getConnection(), getDraftByPrimaryKeySQL, key); return (draft == nullptr) ? "" : draft->text; } std::unique_ptr SQLiteQueryExecutor::getThread(std::string threadID) const { static std::string getThreadByPrimaryKeySQL = "SELECT * " "FROM threads " "WHERE id = ?;"; return getEntityByPrimaryKey( SQLiteQueryExecutor::getConnection(), getThreadByPrimaryKeySQL, threadID); } void SQLiteQueryExecutor::updateDraft(std::string key, std::string text) const { static std::string replaceDraftSQL = "REPLACE INTO drafts (key, text) " "VALUES (?, ?);"; Draft draft = {key, text}; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceDraftSQL, draft); } bool SQLiteQueryExecutor::moveDraft(std::string oldKey, std::string newKey) const { std::string draftText = this->getDraft(oldKey); if (!draftText.size()) { return false; } static std::string rekeyDraftSQL = "UPDATE OR REPLACE drafts " "SET key = ? " "WHERE key = ?;"; rekeyAllEntities( SQLiteQueryExecutor::getConnection(), rekeyDraftSQL, oldKey, newKey); return true; } std::vector SQLiteQueryExecutor::getAllDrafts() const { static std::string getAllDraftsSQL = "SELECT * " "FROM drafts;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllDraftsSQL); } void SQLiteQueryExecutor::removeAllDrafts() const { static std::string removeAllDraftsSQL = "DELETE FROM drafts;"; removeAllEntities(SQLiteQueryExecutor::getConnection(), removeAllDraftsSQL); } void SQLiteQueryExecutor::removeDrafts( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeDraftsByKeysSQLStream; removeDraftsByKeysSQLStream << "DELETE FROM drafts " "WHERE key IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeDraftsByKeysSQLStream.str(), ids); } void SQLiteQueryExecutor::removeAllMessages() const { static std::string removeAllMessagesSQL = "DELETE FROM messages;"; removeAllEntities(SQLiteQueryExecutor::getConnection(), removeAllMessagesSQL); } std::string SQLiteQueryExecutor::getThickThreadTypesList() const { std::stringstream resultStream; for (auto it = THICK_THREAD_TYPES.begin(); it != THICK_THREAD_TYPES.end(); ++it) { int typeInt = static_cast(*it); resultStream << typeInt; if (it + 1 != THICK_THREAD_TYPES.end()) { resultStream << ","; } } return resultStream.str(); } std::vector SQLiteQueryExecutor::getInitialMessages() const { static std::string getInitialMessagesSQL = "SELECT " " s.id, s.local_id, s.thread, s.user, s.type, s.future_type, " " s.content, s.time, m.id, m.container, m.thread, m.uri, m.type, " " m.extras " "FROM ( " " SELECT " " m.*, " " ROW_NUMBER() OVER ( " " PARTITION BY thread ORDER BY m.time DESC, m.id DESC " " ) AS r " " FROM messages AS m " ") AS s " "LEFT JOIN media AS m " " ON s.id = m.container " "INNER JOIN threads AS t " " ON s.thread = t.id " "WHERE s.r <= 20 OR t.type NOT IN ( " + this->getThickThreadTypesList() + ") " "ORDER BY s.time, s.id;"; SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), getInitialMessagesSQL, "Failed to retrieve initial messages."); return this->processMessagesResults(preparedSQL); } std::vector SQLiteQueryExecutor::fetchMessages( std::string threadID, int limit, int offset) const { static std::string query = "SELECT " " m.id, m.local_id, m.thread, m.user, m.type, m.future_type, " " m.content, m.time, media.id, media.container, media.thread, " " media.uri, media.type, media.extras " "FROM messages AS m " "LEFT JOIN media " " ON m.id = media.container " "WHERE m.thread = ? " "ORDER BY m.time DESC, m.id DESC " "LIMIT ? OFFSET ?;"; SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), query, "Failed to fetch messages."); bindStringToSQL(threadID.c_str(), preparedSQL, 1); bindIntToSQL(limit, preparedSQL, 2); bindIntToSQL(offset, preparedSQL, 3); return this->processMessagesResults(preparedSQL); } std::vector SQLiteQueryExecutor::processMessagesResults( SQLiteStatementWrapper &preparedSQL) const { std::string prevMsgIdx{}; std::vector messages; for (int stepResult = sqlite3_step(preparedSQL); stepResult == SQLITE_ROW; stepResult = sqlite3_step(preparedSQL)) { Message message = Message::fromSQLResult(preparedSQL, 0); if (message.id == prevMsgIdx) { messages.back().second.push_back(Media::fromSQLResult(preparedSQL, 8)); } else { prevMsgIdx = message.id; std::vector mediaForMsg; if (sqlite3_column_type(preparedSQL, 8) != SQLITE_NULL) { mediaForMsg.push_back(Media::fromSQLResult(preparedSQL, 8)); } messages.push_back(std::make_pair(std::move(message), mediaForMsg)); } } return messages; } void SQLiteQueryExecutor::removeMessages( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeMessagesByKeysSQLStream; removeMessagesByKeysSQLStream << "DELETE FROM messages " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMessagesByKeysSQLStream.str(), ids); } void SQLiteQueryExecutor::removeMessagesForThreads( const std::vector &threadIDs) const { if (!threadIDs.size()) { return; } std::stringstream removeMessagesByKeysSQLStream; removeMessagesByKeysSQLStream << "DELETE FROM messages " "WHERE thread IN " << getSQLStatementArray(threadIDs.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMessagesByKeysSQLStream.str(), threadIDs); } void SQLiteQueryExecutor::replaceMessage(const Message &message) const { static std::string replaceMessageSQL = "REPLACE INTO messages " "(id, local_id, thread, user, type, future_type, content, time) " "VALUES (?, ?, ?, ?, ?, ?, ?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceMessageSQL, message); } void SQLiteQueryExecutor::updateMessageSearchIndex( std::string originalMessageID, std::string messageID, std::string processedContent) const { sqlite3 *db = SQLiteQueryExecutor::getConnection(); int bindResult = 0; std::unique_ptr preparedSQL; static std::string insertMessageSearchResultSQL = "INSERT INTO message_search(" " original_message_id, message_id, processed_content) " "VALUES (?, ?, ?);"; static std::string updateMessageSearchResultSQL = "UPDATE message_search " "SET message_id = ?, processed_content = ? " "WHERE original_message_id = ?;"; if (originalMessageID == messageID) { preparedSQL = std::make_unique( db, insertMessageSearchResultSQL, "Failed to update message search entry."); bindStringToSQL(originalMessageID, *preparedSQL, 1); bindStringToSQL(messageID, *preparedSQL, 2); bindResult = bindStringToSQL(processedContent, *preparedSQL, 3); } else { preparedSQL = std::make_unique( db, updateMessageSearchResultSQL, "Failed to update message search entry."); bindStringToSQL(messageID, *preparedSQL, 1); bindStringToSQL(processedContent, *preparedSQL, 2); bindResult = bindStringToSQL(originalMessageID, *preparedSQL, 3); } if (bindResult != SQLITE_OK) { std::stringstream error_message; error_message << "Failed to bind key to SQL statement. Details: " << sqlite3_errstr(bindResult) << std::endl; throw std::runtime_error(error_message.str()); } sqlite3_step(*preparedSQL); } void SQLiteQueryExecutor::rekeyMessage(std::string from, std::string to) const { static std::string rekeyMessageSQL = "UPDATE OR REPLACE messages " "SET id = ? " "WHERE id = ?"; rekeyAllEntities( SQLiteQueryExecutor::getConnection(), rekeyMessageSQL, from, to); } void SQLiteQueryExecutor::removeAllMedia() const { static std::string removeAllMediaSQL = "DELETE FROM media;"; removeAllEntities(SQLiteQueryExecutor::getConnection(), removeAllMediaSQL); } void SQLiteQueryExecutor::removeMediaForMessages( const std::vector &msgIDs) const { if (!msgIDs.size()) { return; } std::stringstream removeMediaByKeysSQLStream; removeMediaByKeysSQLStream << "DELETE FROM media " "WHERE container IN " << getSQLStatementArray(msgIDs.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMediaByKeysSQLStream.str(), msgIDs); } void SQLiteQueryExecutor::removeMediaForMessage(std::string msgID) const { static std::string removeMediaByKeySQL = "DELETE FROM media " "WHERE container IN (?);"; std::vector keys = {msgID}; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMediaByKeySQL, keys); } void SQLiteQueryExecutor::removeMediaForThreads( const std::vector &threadIDs) const { if (!threadIDs.size()) { return; } std::stringstream removeMediaByKeysSQLStream; removeMediaByKeysSQLStream << "DELETE FROM media " "WHERE thread IN " << getSQLStatementArray(threadIDs.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMediaByKeysSQLStream.str(), threadIDs); } void SQLiteQueryExecutor::replaceMedia(const Media &media) const { static std::string replaceMediaSQL = "REPLACE INTO media " "(id, container, thread, uri, type, extras) " "VALUES (?, ?, ?, ?, ?, ?)"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceMediaSQL, media); } void SQLiteQueryExecutor::rekeyMediaContainers(std::string from, std::string to) const { static std::string rekeyMediaContainersSQL = "UPDATE media SET container = ? WHERE container = ?;"; rekeyAllEntities( SQLiteQueryExecutor::getConnection(), rekeyMediaContainersSQL, from, to); } void SQLiteQueryExecutor::replaceMessageStoreThreads( const std::vector &threads) const { static std::string replaceMessageStoreThreadSQL = "REPLACE INTO message_store_threads " "(id, start_reached) " "VALUES (?, ?);"; for (auto &thread : threads) { replaceEntity( SQLiteQueryExecutor::getConnection(), replaceMessageStoreThreadSQL, thread); } } void SQLiteQueryExecutor::removeAllMessageStoreThreads() const { static std::string removeAllMessageStoreThreadsSQL = "DELETE FROM message_store_threads;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllMessageStoreThreadsSQL); } void SQLiteQueryExecutor::removeMessageStoreThreads( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeMessageStoreThreadsByKeysSQLStream; removeMessageStoreThreadsByKeysSQLStream << "DELETE FROM message_store_threads " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMessageStoreThreadsByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllMessageStoreThreads() const { static std::string getAllMessageStoreThreadsSQL = "SELECT * " "FROM message_store_threads;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllMessageStoreThreadsSQL); } std::vector SQLiteQueryExecutor::getAllThreads() const { static std::string getAllThreadsSQL = "SELECT * " "FROM threads;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllThreadsSQL); }; void SQLiteQueryExecutor::removeThreads(std::vector ids) const { if (!ids.size()) { return; } std::stringstream removeThreadsByKeysSQLStream; removeThreadsByKeysSQLStream << "DELETE FROM threads " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeThreadsByKeysSQLStream.str(), ids); }; void SQLiteQueryExecutor::replaceThread(const Thread &thread) const { static std::string replaceThreadSQL = "REPLACE INTO threads (" " id, type, name, description, color, creation_time, parent_thread_id," " containing_thread_id, community, members, roles, current_user," - " source_message_id, replies_count, avatar, pinned_count) " - "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; + " source_message_id, replies_count, avatar, pinned_count, timestamps) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceThreadSQL, thread); }; void SQLiteQueryExecutor::removeAllThreads() const { static std::string removeAllThreadsSQL = "DELETE FROM threads;"; removeAllEntities(SQLiteQueryExecutor::getConnection(), removeAllThreadsSQL); }; void SQLiteQueryExecutor::replaceReport(const Report &report) const { static std::string replaceReportSQL = "REPLACE INTO reports (id, report) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceReportSQL, report); } void SQLiteQueryExecutor::removeAllReports() const { static std::string removeAllReportsSQL = "DELETE FROM reports;"; removeAllEntities(SQLiteQueryExecutor::getConnection(), removeAllReportsSQL); } void SQLiteQueryExecutor::removeReports( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeReportsByKeysSQLStream; removeReportsByKeysSQLStream << "DELETE FROM reports " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeReportsByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllReports() const { static std::string getAllReportsSQL = "SELECT * " "FROM reports;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllReportsSQL); } void SQLiteQueryExecutor::setPersistStorageItem( std::string key, std::string item) const { static std::string replacePersistStorageItemSQL = "REPLACE INTO persist_storage (key, item) " "VALUES (?, ?);"; PersistItem entry{ key, item, }; replaceEntity( SQLiteQueryExecutor::getConnection(), replacePersistStorageItemSQL, entry); } void SQLiteQueryExecutor::removePersistStorageItem(std::string key) const { static std::string removePersistStorageItemByKeySQL = "DELETE FROM persist_storage " "WHERE key IN (?);"; std::vector keys = {key}; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removePersistStorageItemByKeySQL, keys); } std::string SQLiteQueryExecutor::getPersistStorageItem(std::string key) const { static std::string getPersistStorageItemByPrimaryKeySQL = "SELECT * " "FROM persist_storage " "WHERE key = ?;"; std::unique_ptr entry = getEntityByPrimaryKey( SQLiteQueryExecutor::getConnection(), getPersistStorageItemByPrimaryKeySQL, key); return (entry == nullptr) ? "" : entry->item; } void SQLiteQueryExecutor::replaceUser(const UserInfo &userInfo) const { static std::string replaceUserSQL = "REPLACE INTO users (id, user_info) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceUserSQL, userInfo); } void SQLiteQueryExecutor::removeAllUsers() const { static std::string removeAllUsersSQL = "DELETE FROM users;"; removeAllEntities(SQLiteQueryExecutor::getConnection(), removeAllUsersSQL); } void SQLiteQueryExecutor::removeUsers( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeUsersByKeysSQLStream; removeUsersByKeysSQLStream << "DELETE FROM users " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeUsersByKeysSQLStream.str(), ids); } void SQLiteQueryExecutor::replaceKeyserver( const KeyserverInfo &keyserverInfo) const { static std::string replaceKeyserverSQL = "REPLACE INTO keyservers (id, keyserver_info) " "VALUES (:id, :keyserver_info);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceKeyserverSQL, keyserverInfo); static std::string replaceKeyserverSyncedSQL = "REPLACE INTO keyservers_synced (id, keyserver_info) " "VALUES (:id, :synced_keyserver_info);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceKeyserverSyncedSQL, keyserverInfo); } void SQLiteQueryExecutor::removeAllKeyservers() const { static std::string removeAllKeyserversSQL = "DELETE FROM keyservers;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllKeyserversSQL); static std::string removeAllKeyserversSyncedSQL = "DELETE FROM keyservers_synced;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllKeyserversSyncedSQL); } void SQLiteQueryExecutor::removeKeyservers( const std::vector &ids) const { if (!ids.size()) { return; } auto idArray = getSQLStatementArray(ids.size()); std::stringstream removeKeyserversByKeysSQLStream; removeKeyserversByKeysSQLStream << "DELETE FROM keyservers " "WHERE id IN " << idArray << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeKeyserversByKeysSQLStream.str(), ids); std::stringstream removeKeyserversSyncedByKeysSQLStream; removeKeyserversSyncedByKeysSQLStream << "DELETE FROM keyservers_synced " "WHERE id IN " << idArray << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeKeyserversSyncedByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllKeyservers() const { static std::string getAllKeyserversSQL = "SELECT " " synced.id, " " COALESCE(keyservers.keyserver_info, ''), " " synced.keyserver_info " "FROM keyservers_synced synced " "LEFT JOIN keyservers " " ON synced.id = keyservers.id;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllKeyserversSQL); } std::vector SQLiteQueryExecutor::getAllUsers() const { static std::string getAllUsersSQL = "SELECT * " "FROM users;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllUsersSQL); } void SQLiteQueryExecutor::replaceCommunity( const CommunityInfo &communityInfo) const { static std::string replaceCommunitySQL = "REPLACE INTO communities (id, community_info) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceCommunitySQL, communityInfo); } void SQLiteQueryExecutor::removeAllCommunities() const { static std::string removeAllCommunitiesSQL = "DELETE FROM communities;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllCommunitiesSQL); } void SQLiteQueryExecutor::removeCommunities( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeCommunitiesByKeysSQLStream; removeCommunitiesByKeysSQLStream << "DELETE FROM communities " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeCommunitiesByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllCommunities() const { static std::string getAllCommunitiesSQL = "SELECT * " "FROM communities;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllCommunitiesSQL); } void SQLiteQueryExecutor::replaceIntegrityThreadHashes( const std::vector &threadHashes) const { static std::string replaceIntegrityThreadHashSQL = "REPLACE INTO integrity_store (id, thread_hash) " "VALUES (?, ?);"; for (const IntegrityThreadHash &integrityThreadHash : threadHashes) { replaceEntity( SQLiteQueryExecutor::getConnection(), replaceIntegrityThreadHashSQL, integrityThreadHash); } } void SQLiteQueryExecutor::removeAllIntegrityThreadHashes() const { static std::string removeAllIntegrityThreadHashesSQL = "DELETE FROM integrity_store;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllIntegrityThreadHashesSQL); } void SQLiteQueryExecutor::removeIntegrityThreadHashes( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeIntegrityThreadHashesByKeysSQLStream; removeIntegrityThreadHashesByKeysSQLStream << "DELETE FROM integrity_store " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeIntegrityThreadHashesByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllIntegrityThreadHashes() const { static std::string getAllIntegrityThreadHashesSQL = "SELECT * " "FROM integrity_store;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllIntegrityThreadHashesSQL); } void SQLiteQueryExecutor::replaceSyncedMetadataEntry( const SyncedMetadataEntry &syncedMetadataEntry) const { static std::string replaceSyncedMetadataEntrySQL = "REPLACE INTO synced_metadata (name, data) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceSyncedMetadataEntrySQL, syncedMetadataEntry); } void SQLiteQueryExecutor::removeAllSyncedMetadata() const { static std::string removeAllSyncedMetadataSQL = "DELETE FROM synced_metadata;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllSyncedMetadataSQL); } void SQLiteQueryExecutor::removeSyncedMetadata( const std::vector &names) const { if (!names.size()) { return; } std::stringstream removeSyncedMetadataByNamesSQLStream; removeSyncedMetadataByNamesSQLStream << "DELETE FROM synced_metadata " "WHERE name IN " << getSQLStatementArray(names.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeSyncedMetadataByNamesSQLStream.str(), names); } std::vector SQLiteQueryExecutor::getAllSyncedMetadata() const { static std::string getAllSyncedMetadataSQL = "SELECT * " "FROM synced_metadata;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllSyncedMetadataSQL); } std::optional SQLiteQueryExecutor::getSyncedDatabaseVersion(sqlite3 *db) const { static std::string getDBVersionSyncedMetadataSQL = "SELECT * " "FROM synced_metadata " "WHERE name=\"db_version\";"; std::vector entries = getAllEntities(db, getDBVersionSyncedMetadataSQL); for (auto &entry : entries) { return std::stoi(entry.data); } return std::nullopt; } void SQLiteQueryExecutor::replaceAuxUserInfo( const AuxUserInfo &userInfo) const { static std::string replaceAuxUserInfoSQL = "REPLACE INTO aux_users (id, aux_user_info) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceAuxUserInfoSQL, userInfo); } void SQLiteQueryExecutor::removeAllAuxUserInfos() const { static std::string removeAllAuxUserInfosSQL = "DELETE FROM aux_users;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllAuxUserInfosSQL); } void SQLiteQueryExecutor::removeAuxUserInfos( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeAuxUserInfosByKeysSQLStream; removeAuxUserInfosByKeysSQLStream << "DELETE FROM aux_users " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeAuxUserInfosByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllAuxUserInfos() const { static std::string getAllAuxUserInfosSQL = "SELECT * " "FROM aux_users;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllAuxUserInfosSQL); } void SQLiteQueryExecutor::replaceThreadActivityEntry( const ThreadActivityEntry &threadActivityEntry) const { static std::string replaceThreadActivityEntrySQL = "REPLACE INTO thread_activity (id, thread_activity_store_entry) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceThreadActivityEntrySQL, threadActivityEntry); } void SQLiteQueryExecutor::removeAllThreadActivityEntries() const { static std::string removeAllThreadActivityEntriesSQL = "DELETE FROM thread_activity;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllThreadActivityEntriesSQL); } void SQLiteQueryExecutor::removeThreadActivityEntries( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeThreadActivityEntriesByKeysSQLStream; removeThreadActivityEntriesByKeysSQLStream << "DELETE FROM thread_activity " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeThreadActivityEntriesByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllThreadActivityEntries() const { static std::string getAllThreadActivityEntriesSQL = "SELECT * " "FROM thread_activity;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllThreadActivityEntriesSQL); } void SQLiteQueryExecutor::replaceEntry(const EntryInfo &entryInfo) const { static std::string replaceEntrySQL = "REPLACE INTO entries (id, entry) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceEntrySQL, entryInfo); } void SQLiteQueryExecutor::removeAllEntries() const { static std::string removeAllEntriesSQL = "DELETE FROM entries;"; removeAllEntities(SQLiteQueryExecutor::getConnection(), removeAllEntriesSQL); } void SQLiteQueryExecutor::removeEntries( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeEntriesByKeysSQLStream; removeEntriesByKeysSQLStream << "DELETE FROM entries " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeEntriesByKeysSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getAllEntries() const { static std::string getAllEntriesSQL = "SELECT * " "FROM entries;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllEntriesSQL); } void SQLiteQueryExecutor::replaceMessageStoreLocalMessageInfo( const LocalMessageInfo &localMessageInfo) const { static std::string replaceLocalMessageInfoSQL = "REPLACE INTO message_store_local (id, local_message_info) " "VALUES (?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceLocalMessageInfoSQL, localMessageInfo); } void SQLiteQueryExecutor::removeMessageStoreLocalMessageInfos( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeLocalMessageInfosByKeysSQLStream; removeLocalMessageInfosByKeysSQLStream << "DELETE FROM message_store_local " "WHERE id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeLocalMessageInfosByKeysSQLStream.str(), ids); } void SQLiteQueryExecutor::removeAllMessageStoreLocalMessageInfos() const { static std::string removeAllLocalMessageInfosSQL = "DELETE FROM message_store_local;"; removeAllEntities( SQLiteQueryExecutor::getConnection(), removeAllLocalMessageInfosSQL); } std::vector SQLiteQueryExecutor::getAllMessageStoreLocalMessageInfos() const { static std::string getAllLocalMessageInfosSQL = "SELECT * " "FROM message_store_local;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllLocalMessageInfosSQL); } void SQLiteQueryExecutor::beginTransaction() const { executeQuery(SQLiteQueryExecutor::getConnection(), "BEGIN TRANSACTION;"); } void SQLiteQueryExecutor::commitTransaction() const { executeQuery(SQLiteQueryExecutor::getConnection(), "COMMIT;"); } void SQLiteQueryExecutor::rollbackTransaction() const { executeQuery(SQLiteQueryExecutor::getConnection(), "ROLLBACK;"); } int SQLiteQueryExecutor::getContentAccountID() const { return CONTENT_ACCOUNT_ID; } int SQLiteQueryExecutor::getNotifsAccountID() const { return NOTIFS_ACCOUNT_ID; } std::vector SQLiteQueryExecutor::getOlmPersistSessionsData() const { static std::string getAllOlmPersistSessionsSQL = "SELECT * " "FROM olm_persist_sessions;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), getAllOlmPersistSessionsSQL); } std::optional SQLiteQueryExecutor::getOlmPersistAccountData(int accountID) const { static std::string getOlmPersistAccountSQL = "SELECT * " "FROM olm_persist_account " "WHERE id = ?;"; std::unique_ptr result = getEntityByIntegerPrimaryKey( SQLiteQueryExecutor::getConnection(), getOlmPersistAccountSQL, accountID); if (result == nullptr) { return std::nullopt; } return result->account_data; } void SQLiteQueryExecutor::storeOlmPersistAccount( int accountID, const std::string &accountData) const { static std::string replaceOlmPersistAccountSQL = "REPLACE INTO olm_persist_account (id, account_data) " "VALUES (?, ?);"; OlmPersistAccount persistAccount = {accountID, accountData}; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceOlmPersistAccountSQL, persistAccount); } void SQLiteQueryExecutor::storeOlmPersistSession( const OlmPersistSession &session) const { static std::string replaceOlmPersistSessionSQL = "REPLACE INTO olm_persist_sessions " "(target_device_id, session_data, version) " "VALUES (?, ?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceOlmPersistSessionSQL, session); } void SQLiteQueryExecutor::storeOlmPersistData( int accountID, crypto::Persist persist) const { if (accountID != CONTENT_ACCOUNT_ID && persist.sessions.size() > 0) { throw std::runtime_error( "Attempt to store notifications sessions in SQLite. Notifications " "sessions must be stored in storage shared with NSE."); } std::string accountData = std::string(persist.account.begin(), persist.account.end()); this->storeOlmPersistAccount(accountID, accountData); for (auto it = persist.sessions.begin(); it != persist.sessions.end(); it++) { OlmPersistSession persistSession = { it->first, std::string(it->second.buffer.begin(), it->second.buffer.end()), it->second.version}; this->storeOlmPersistSession(persistSession); } } void SQLiteQueryExecutor::setNotifyToken(std::string token) const { this->setMetadata("notify_token", token); } void SQLiteQueryExecutor::clearNotifyToken() const { this->clearMetadata("notify_token"); } void SQLiteQueryExecutor::stampSQLiteDBUserID(std::string userID) const { this->setMetadata("current_user_id", userID); } std::string SQLiteQueryExecutor::getSQLiteStampedUserID() const { return this->getMetadata("current_user_id"); } void SQLiteQueryExecutor::setMetadata(std::string entryName, std::string data) const { std::string replaceMetadataSQL = "REPLACE INTO metadata (name, data) " "VALUES (?, ?);"; Metadata entry{ entryName, data, }; replaceEntity( SQLiteQueryExecutor::getConnection(), replaceMetadataSQL, entry); } void SQLiteQueryExecutor::clearMetadata(std::string entryName) const { static std::string removeMetadataByKeySQL = "DELETE FROM metadata " "WHERE name IN (?);"; std::vector keys = {entryName}; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMetadataByKeySQL, keys); } std::string SQLiteQueryExecutor::getMetadata(std::string entryName) const { std::string getMetadataByPrimaryKeySQL = "SELECT * " "FROM metadata " "WHERE name = ?;"; std::unique_ptr entry = getEntityByPrimaryKey( SQLiteQueryExecutor::getConnection(), getMetadataByPrimaryKeySQL, entryName); return (entry == nullptr) ? "" : entry->data; } void SQLiteQueryExecutor::addOutboundP2PMessages( const std::vector &messages) const { static std::string addMessage = "REPLACE INTO outbound_p2p_messages (" " message_id, device_id, user_id, timestamp," " plaintext, ciphertext, status, supports_auto_retry) " "VALUES (?, ?, ?, ?, ?, ?, ?, ?);"; for (const OutboundP2PMessage &clientMessage : messages) { SQLiteOutboundP2PMessage message = clientMessage.toSQLiteOutboundP2PMessage(); replaceEntity( SQLiteQueryExecutor::getConnection(), addMessage, message); } } std::vector SQLiteQueryExecutor::getOutboundP2PMessagesByID( const std::vector &ids) const { std::stringstream getOutboundP2PMessageSQLStream; getOutboundP2PMessageSQLStream << "SELECT * " "FROM outbound_p2p_messages " "WHERE message_id IN " << getSQLStatementArray(ids.size()) << ";"; std::string getOutboundP2PMessageSQL = getOutboundP2PMessageSQLStream.str(); SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), getOutboundP2PMessageSQL, "Failed to get outbound messages by ID"); std::vector queryResult = getAllEntitiesByPrimaryKeys( SQLiteQueryExecutor::getConnection(), getOutboundP2PMessageSQL, ids); std::vector result; for (auto &message : queryResult) { result.emplace_back(OutboundP2PMessage(message)); } return result; } std::vector SQLiteQueryExecutor::getAllOutboundP2PMessages() const { std::string query = "SELECT * FROM outbound_p2p_messages " "ORDER BY timestamp;"; SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), query, "Failed to get all messages to device"); std::vector messages; for (int stepResult = sqlite3_step(preparedSQL); stepResult == SQLITE_ROW; stepResult = sqlite3_step(preparedSQL)) { messages.emplace_back(OutboundP2PMessage( SQLiteOutboundP2PMessage::fromSQLResult(preparedSQL, 0))); } return messages; } void SQLiteQueryExecutor::removeOutboundP2PMessage( std::string confirmedMessageID, std::string deviceID) const { std::string query = "DELETE FROM outbound_p2p_messages " "WHERE message_id = ? AND device_id = ?;"; comm::SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), query, "Failed to remove messages to device"); bindStringToSQL(confirmedMessageID.c_str(), preparedSQL, 1); bindStringToSQL(deviceID.c_str(), preparedSQL, 2); sqlite3_step(preparedSQL); } void SQLiteQueryExecutor::removeAllOutboundP2PMessages( const std::string &deviceID) const { static std::string removeMessagesSQL = "DELETE FROM outbound_p2p_messages " "WHERE device_id IN (?);"; std::vector keys = {deviceID}; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMessagesSQL, keys); } void SQLiteQueryExecutor::setCiphertextForOutboundP2PMessage( std::string messageID, std::string deviceID, std::string ciphertext) const { static std::string query = "UPDATE outbound_p2p_messages " "SET ciphertext = ?, status = 'encrypted' " "WHERE message_id = ? AND device_id = ?;"; comm::SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), query, "Failed to set ciphertext for OutboundP2PMessage"); bindStringToSQL(ciphertext.c_str(), preparedSQL, 1); bindStringToSQL(messageID.c_str(), preparedSQL, 2); bindStringToSQL(deviceID.c_str(), preparedSQL, 3); sqlite3_step(preparedSQL); } void SQLiteQueryExecutor::markOutboundP2PMessageAsSent( std::string messageID, std::string deviceID) const { static std::string query = "UPDATE outbound_p2p_messages " "SET status = 'sent' " "WHERE message_id = ? AND device_id = ?;"; comm::SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), query, "Failed to mark OutboundP2PMessage as sent"); bindStringToSQL(messageID.c_str(), preparedSQL, 1); bindStringToSQL(deviceID.c_str(), preparedSQL, 2); sqlite3_step(preparedSQL); } std::vector SQLiteQueryExecutor::resetOutboundP2PMessagesForDevice( std::string deviceID) const { // Query all messages that need to be resent - all message that supports // auto retry or already sent messages. std::string queryMessageIDsToResend = "SELECT message_id " "FROM outbound_p2p_messages " "WHERE device_id = ? AND ( " " supports_auto_retry = 1 " " OR (supports_auto_retry = 0 AND status = 'sent') " ");"; SQLiteStatementWrapper preparedQueryMessageIDsSQL( SQLiteQueryExecutor::getConnection(), queryMessageIDsToResend, "Failed to get all messages to reset"); bindStringToSQL(deviceID.c_str(), preparedQueryMessageIDsSQL, 1); std::vector messageIDs; for (int stepResult = sqlite3_step(preparedQueryMessageIDsSQL); stepResult == SQLITE_ROW; stepResult = sqlite3_step(preparedQueryMessageIDsSQL)) { messageIDs.push_back(getStringFromSQLRow(preparedQueryMessageIDsSQL, 0)); } // Setting ciphertext to an empty string to make sure this message will be // encrypted again with a new session, update the status, and set // supports_auto_retry to true. // Updating supports_auto_retry to true because those are already sent // messages (from the UI perspective), but the recipient failed to decrypt // so needs to be automatically resent. std::stringstream resetMessagesSQLStream; resetMessagesSQLStream << "UPDATE outbound_p2p_messages " << "SET supports_auto_retry = 1, status = 'persisted', ciphertext = '' " << "WHERE message_id IN " << getSQLStatementArray(messageIDs.size()) << ";"; SQLiteStatementWrapper preparedUpdateSQL( SQLiteQueryExecutor::getConnection(), resetMessagesSQLStream.str(), "Failed to reset messages."); for (int i = 0; i < messageIDs.size(); i++) { int bindResult = bindStringToSQL(messageIDs[i], preparedUpdateSQL, i + 1); if (bindResult != SQLITE_OK) { std::stringstream error_message; error_message << "Failed to bind key to SQL statement. Details: " << sqlite3_errstr(bindResult) << std::endl; sqlite3_finalize(preparedUpdateSQL); throw std::runtime_error(error_message.str()); } } sqlite3_step(preparedUpdateSQL); // This handles the case of messages that are encrypted (with a malformed // session) but not yet queued on Tunnelbroker. In this case, this message // is not considered to be sent (from the UI perspective), // and supports_auto_retry is not updated. std::string updateCiphertextQuery = "UPDATE outbound_p2p_messages " "SET ciphertext = '', status = 'persisted'" "WHERE device_id = ? " " AND supports_auto_retry = 0 " " AND status = 'encrypted';"; SQLiteStatementWrapper preparedUpdateCiphertextSQL( SQLiteQueryExecutor::getConnection(), updateCiphertextQuery, "Failed to set ciphertext"); bindStringToSQL(deviceID.c_str(), preparedUpdateCiphertextSQL, 1); sqlite3_step(preparedUpdateCiphertextSQL); return messageIDs; } void SQLiteQueryExecutor::addInboundP2PMessage( InboundP2PMessage message) const { static std::string addMessage = "REPLACE INTO inbound_p2p_messages (" " message_id, sender_device_id, plaintext, status, sender_user_id)" "VALUES (?, ?, ?, ?, ?);"; replaceEntity( SQLiteQueryExecutor::getConnection(), addMessage, message); } std::vector SQLiteQueryExecutor::getAllInboundP2PMessage() const { static std::string query = "SELECT message_id, sender_device_id, plaintext, status, sender_user_id " "FROM inbound_p2p_messages;"; return getAllEntities( SQLiteQueryExecutor::getConnection(), query); } void SQLiteQueryExecutor::removeInboundP2PMessages( const std::vector &ids) const { if (!ids.size()) { return; } std::stringstream removeMessagesSQLStream; removeMessagesSQLStream << "DELETE FROM inbound_p2p_messages " "WHERE message_id IN " << getSQLStatementArray(ids.size()) << ";"; removeEntitiesByKeys( SQLiteQueryExecutor::getConnection(), removeMessagesSQLStream.str(), ids); } std::vector SQLiteQueryExecutor::getRelatedMessages(const std::string &messageID) const { static std::string getMessageSQL = "SELECT " " m.id, m.local_id, m.thread, m.user, m.type, m.future_type, " " m.content, m.time, media.id, media.container, media.thread, " " media.uri, media.type, media.extras " "FROM messages AS m " "LEFT JOIN media " " ON m.id = media.container " "WHERE m.id = ? OR m.target_message = ? " "ORDER BY m.time DESC"; comm::SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), getMessageSQL, "Failed to get latest message edit"); bindStringToSQL(messageID.c_str(), preparedSQL, 1); bindStringToSQL(messageID.c_str(), preparedSQL, 2); return this->processMessagesResults(preparedSQL); } std::vector SQLiteQueryExecutor::searchMessages( std::string query, std::string threadID, std::optional timestampCursor, std::optional messageIDCursor) const { std::stringstream searchMessagesSQL; searchMessagesSQL << "SELECT " " m.id, m.local_id, m.thread, m.user, m.type, m.future_type, " " m.content, m.time, media.id, media.container, media.thread, " " media.uri, media.type, media.extras " "FROM message_search AS s " "LEFT JOIN messages AS m " " ON m.id = s.original_message_id " "LEFT JOIN media " " ON m.id = media.container " "LEFT JOIN messages AS m2 " " ON m2.target_message = m.id " " AND m2.type = ? AND m2.thread = ? " "WHERE s.processed_content MATCH ? " " AND (m.thread = ? OR m2.id IS NOT NULL) "; bool usingCursor = timestampCursor.has_value() && messageIDCursor.has_value(); if (usingCursor) { searchMessagesSQL << " AND (m.time < ? OR (m.time = ? AND m.id < ?)) "; } searchMessagesSQL << "ORDER BY m.time DESC, m.id DESC " << "LIMIT 20;"; comm::SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), searchMessagesSQL.str(), "Failed to get message search results"); auto sidebarSourceType = static_cast(MessageType::SIDEBAR_SOURCE); bindIntToSQL(sidebarSourceType, preparedSQL, 1); bindStringToSQL(threadID.c_str(), preparedSQL, 2); bindStringToSQL(query.c_str(), preparedSQL, 3); bindStringToSQL(threadID.c_str(), preparedSQL, 4); if (usingCursor) { int64_t timestamp = std::stoll(timestampCursor.value()); bindInt64ToSQL(timestamp, preparedSQL, 5); bindInt64ToSQL(timestamp, preparedSQL, 6); bindStringToSQL(messageIDCursor.value(), preparedSQL, 7); } std::vector messages = this->processMessagesResults(preparedSQL); std::vector messageIDs; for (const auto &message : messages) { messageIDs.push_back(message.first.id); } std::vector relatedMessages = this->getRelatedMessagesForSearch(messageIDs); for (auto &entity : relatedMessages) { messages.push_back(std::move(entity)); } return messages; } std::vector SQLiteQueryExecutor::getRelatedMessagesForSearch( const std::vector &messageIDs) const { std::stringstream selectRelatedMessagesSQLStream; selectRelatedMessagesSQLStream << "SELECT " " m.id, m.local_id, m.thread, m.user, " " m.type, m.future_type, m.content, " " m.time, media.id, media.container, " " media.thread, media.uri, media.type, " " media.extras " "FROM messages AS m " "LEFT JOIN media " " ON m.id = media.container " "WHERE m.target_message IN " << getSQLStatementArray(messageIDs.size()) << "ORDER BY m.time DESC"; std::string selectRelatedMessagesSQL = selectRelatedMessagesSQLStream.str(); SQLiteStatementWrapper preparedSQL( SQLiteQueryExecutor::getConnection(), selectRelatedMessagesSQL, "Failed to fetch related messages."); for (int i = 0; i < messageIDs.size(); i++) { int bindResult = bindStringToSQL(messageIDs[i], preparedSQL, i + 1); if (bindResult != SQLITE_OK) { std::stringstream error_message; error_message << "Failed to bind key to SQL statement. Details: " << sqlite3_errstr(bindResult) << std::endl; sqlite3_finalize(preparedSQL); throw std::runtime_error(error_message.str()); } } return this->processMessagesResults(preparedSQL); } #ifdef EMSCRIPTEN std::vector SQLiteQueryExecutor::getAllThreadsWeb() const { auto threads = this->getAllThreads(); std::vector webThreads; webThreads.reserve(threads.size()); for (const auto &thread : threads) { webThreads.emplace_back(thread); } return webThreads; }; void SQLiteQueryExecutor::replaceThreadWeb(const WebThread &thread) const { this->replaceThread(thread.toThread()); }; std::vector SQLiteQueryExecutor::transformToWebMessages( const std::vector &messages) const { std::vector messageWithMedias; for (auto &messageWithMedia : messages) { messageWithMedias.push_back( {std::move(messageWithMedia.first), messageWithMedia.second}); } return messageWithMedias; } std::vector SQLiteQueryExecutor::getInitialMessagesWeb() const { auto messages = this->getInitialMessages(); return this->transformToWebMessages(messages); } std::vector SQLiteQueryExecutor::fetchMessagesWeb( std::string threadID, int limit, int offset) const { auto messages = this->fetchMessages(threadID, limit, offset); return this->transformToWebMessages(messages); } void SQLiteQueryExecutor::replaceMessageWeb(const WebMessage &message) const { this->replaceMessage(message.toMessage()); }; NullableString SQLiteQueryExecutor::getOlmPersistAccountDataWeb(int accountID) const { std::optional accountData = this->getOlmPersistAccountData(accountID); if (!accountData.has_value()) { return NullableString(); } return std::make_unique(accountData.value()); } std::vector SQLiteQueryExecutor::getRelatedMessagesWeb(const std::string &messageID) const { auto relatedMessages = this->getRelatedMessages(messageID); return this->transformToWebMessages(relatedMessages); } std::vector SQLiteQueryExecutor::searchMessagesWeb( std::string query, std::string threadID, std::optional timestampCursor, std::optional messageIDCursor) const { auto messages = this->searchMessages(query, threadID, timestampCursor, messageIDCursor); return this->transformToWebMessages(messages); } #else void SQLiteQueryExecutor::clearSensitiveData() { SQLiteQueryExecutor::closeConnection(); if (file_exists(SQLiteQueryExecutor::sqliteFilePath) && std::remove(SQLiteQueryExecutor::sqliteFilePath.c_str())) { std::ostringstream errorStream; errorStream << "Failed to delete database file. Details: " << strerror(errno); throw std::system_error(errno, std::generic_category(), errorStream.str()); } SQLiteQueryExecutor::generateFreshEncryptionKey(); SQLiteQueryExecutor::migrate(); } void SQLiteQueryExecutor::initialize(std::string &databasePath) { std::call_once(SQLiteQueryExecutor::initialized, [&databasePath]() { SQLiteQueryExecutor::sqliteFilePath = databasePath; folly::Optional maybeEncryptionKey = CommSecureStore::get(SQLiteQueryExecutor::secureStoreEncryptionKeyID); folly::Optional maybeBackupLogsEncryptionKey = CommSecureStore::get( SQLiteQueryExecutor::secureStoreBackupLogsEncryptionKeyID); if (file_exists(databasePath) && maybeEncryptionKey && maybeBackupLogsEncryptionKey) { SQLiteQueryExecutor::encryptionKey = maybeEncryptionKey.value(); SQLiteQueryExecutor::backupLogsEncryptionKey = maybeBackupLogsEncryptionKey.value(); return; } else if (file_exists(databasePath) && maybeEncryptionKey) { SQLiteQueryExecutor::encryptionKey = maybeEncryptionKey.value(); SQLiteQueryExecutor::generateFreshBackupLogsEncryptionKey(); return; } SQLiteQueryExecutor::generateFreshEncryptionKey(); }); } void SQLiteQueryExecutor::initializeTablesForLogMonitoring() { sqlite3 *db; sqlite3_open(SQLiteQueryExecutor::sqliteFilePath.c_str(), &db); default_on_db_open_callback(db); std::vector tablesToMonitor; { SQLiteStatementWrapper preparedSQL( db, "SELECT name FROM sqlite_master WHERE type='table';", "Failed to get all database tables"); for (int stepResult = sqlite3_step(preparedSQL); stepResult == SQLITE_ROW; stepResult = sqlite3_step(preparedSQL)) { std::string table_name = reinterpret_cast(sqlite3_column_text(preparedSQL, 0)); if (SQLiteQueryExecutor::backedUpTablesBlocklist.find(table_name) == SQLiteQueryExecutor::backedUpTablesBlocklist.end()) { tablesToMonitor.emplace_back(table_name); } } // Runs preparedSQL destructor which finalizes the sqlite statement } sqlite3_close(db); SQLiteQueryExecutor::connectionManager.tablesToMonitor = tablesToMonitor; } void SQLiteQueryExecutor::createMainCompaction(std::string backupID) const { std::string finalBackupPath = PlatformSpecificTools::getBackupFilePath(backupID, false); std::string finalAttachmentsPath = PlatformSpecificTools::getBackupFilePath(backupID, true); std::string tempBackupPath = finalBackupPath + "_tmp"; std::string tempAttachmentsPath = finalAttachmentsPath + "_tmp"; if (file_exists(tempBackupPath)) { Logger::log( "Attempting to delete temporary backup file from previous backup " "attempt."); attempt_delete_file( tempBackupPath, "Failed to delete temporary backup file from previous backup " "attempt."); } if (file_exists(tempAttachmentsPath)) { Logger::log( "Attempting to delete temporary attachments file from previous " "backup " "attempt."); attempt_delete_file( tempAttachmentsPath, "Failed to delete temporary attachments file from previous backup " "attempt."); } sqlite3 *backupDB; sqlite3_open(tempBackupPath.c_str(), &backupDB); set_encryption_key(backupDB); sqlite3_backup *backupObj = sqlite3_backup_init( backupDB, "main", SQLiteQueryExecutor::getConnection(), "main"); if (!backupObj) { std::stringstream error_message; error_message << "Failed to init backup for main compaction. Details: " << sqlite3_errmsg(backupDB) << std::endl; sqlite3_close(backupDB); throw std::runtime_error(error_message.str()); } int backupResult = sqlite3_backup_step(backupObj, -1); sqlite3_backup_finish(backupObj); if (backupResult == SQLITE_BUSY || backupResult == SQLITE_LOCKED) { sqlite3_close(backupDB); throw std::runtime_error( "Programmer error. Database in transaction during backup attempt."); } else if (backupResult != SQLITE_DONE) { sqlite3_close(backupDB); std::stringstream error_message; error_message << "Failed to create database backup. Details: " << sqlite3_errstr(backupResult); throw std::runtime_error(error_message.str()); } if (!SQLiteQueryExecutor::backedUpTablesBlocklist.empty()) { std::string removeDeviceSpecificDataSQL = ""; for (const auto &table_name : SQLiteQueryExecutor::backedUpTablesBlocklist) { removeDeviceSpecificDataSQL.append("DELETE FROM " + table_name + ";\n"); } executeQuery(backupDB, removeDeviceSpecificDataSQL); } executeQuery(backupDB, "VACUUM;"); sqlite3_close(backupDB); attempt_rename_file( tempBackupPath, finalBackupPath, "Failed to rename complete temporary backup file to final backup " "file."); std::ofstream tempAttachmentsFile(tempAttachmentsPath); if (!tempAttachmentsFile.is_open()) { throw std::runtime_error( "Unable to create attachments file for backup id: " + backupID); } std::string getAllBlobServiceMediaSQL = "SELECT * FROM media WHERE uri LIKE 'comm-blob-service://%';"; std::vector blobServiceMedia = getAllEntities( SQLiteQueryExecutor::getConnection(), getAllBlobServiceMediaSQL); for (const auto &media : blobServiceMedia) { std::string blobServiceURI = media.uri; std::string blobHash = blob_hash_from_blob_service_uri(blobServiceURI); tempAttachmentsFile << blobHash << "\n"; } tempAttachmentsFile.close(); attempt_rename_file( tempAttachmentsPath, finalAttachmentsPath, "Failed to rename complete temporary attachments file to final " "attachments file."); this->setMetadata("backupID", backupID); this->clearMetadata("logID"); if (StaffUtils::isStaffRelease()) { SQLiteQueryExecutor::connectionManager.setLogsMonitoring(true); } } void SQLiteQueryExecutor::generateFreshEncryptionKey() { std::string encryptionKey = comm::crypto::Tools::generateRandomHexString( SQLiteQueryExecutor::sqlcipherEncryptionKeySize); CommSecureStore::set( SQLiteQueryExecutor::secureStoreEncryptionKeyID, encryptionKey); SQLiteQueryExecutor::encryptionKey = encryptionKey; SQLiteQueryExecutor::generateFreshBackupLogsEncryptionKey(); } void SQLiteQueryExecutor::generateFreshBackupLogsEncryptionKey() { std::string backupLogsEncryptionKey = comm::crypto::Tools::generateRandomHexString( SQLiteQueryExecutor::backupLogsEncryptionKeySize); CommSecureStore::set( SQLiteQueryExecutor::secureStoreBackupLogsEncryptionKeyID, backupLogsEncryptionKey); SQLiteQueryExecutor::backupLogsEncryptionKey = backupLogsEncryptionKey; } void SQLiteQueryExecutor::captureBackupLogs() const { std::string backupID = this->getMetadata("backupID"); if (!backupID.size()) { return; } std::string logID = this->getMetadata("logID"); if (!logID.size()) { logID = "1"; } bool newLogCreated = SQLiteQueryExecutor::connectionManager.captureLogs( backupID, logID, SQLiteQueryExecutor::backupLogsEncryptionKey); if (!newLogCreated) { return; } this->setMetadata("logID", std::to_string(std::stoi(logID) + 1)); } #endif void SQLiteQueryExecutor::restoreFromMainCompaction( std::string mainCompactionPath, std::string mainCompactionEncryptionKey, std::string maxVersion) const { if (!file_exists(mainCompactionPath)) { throw std::runtime_error("Restore attempt but backup file does not exist."); } sqlite3 *backupDB; if (!is_database_queryable( backupDB, true, mainCompactionPath, mainCompactionEncryptionKey)) { throw std::runtime_error("Backup file or encryption key corrupted."); } // We don't want to run `PRAGMA key = ...;` // on main web database. The context is here: // https://linear.app/comm/issue/ENG-6398/issues-with-sqlcipher-on-web #ifdef EMSCRIPTEN std::string plaintextBackupPath = mainCompactionPath + "_plaintext"; if (file_exists(plaintextBackupPath)) { attempt_delete_file( plaintextBackupPath, "Failed to delete plaintext backup file from previous backup " "attempt."); } std::string plaintextMigrationDBQuery = "PRAGMA key = \"x'" + mainCompactionEncryptionKey + "'\";" "ATTACH DATABASE '" + plaintextBackupPath + "' AS plaintext KEY '';" "SELECT sqlcipher_export('plaintext');" "DETACH DATABASE plaintext;"; sqlite3_open(mainCompactionPath.c_str(), &backupDB); char *plaintextMigrationErr; sqlite3_exec( backupDB, plaintextMigrationDBQuery.c_str(), nullptr, nullptr, &plaintextMigrationErr); sqlite3_close(backupDB); if (plaintextMigrationErr) { std::stringstream error_message; error_message << "Failed to migrate backup SQLCipher file to plaintext " "SQLite file. Details" << plaintextMigrationErr << std::endl; std::string error_message_str = error_message.str(); sqlite3_free(plaintextMigrationErr); throw std::runtime_error(error_message_str); } sqlite3_open(plaintextBackupPath.c_str(), &backupDB); #else sqlite3_open(mainCompactionPath.c_str(), &backupDB); set_encryption_key(backupDB, mainCompactionEncryptionKey); #endif int version = this->getSyncedDatabaseVersion(backupDB).value_or(-1); if (version > std::stoi(maxVersion)) { std::stringstream error_message; error_message << "Failed to restore a backup because it was created " << "with version " << version << " that is newer than the max supported version " << maxVersion << std::endl; sqlite3_close(backupDB); throw std::runtime_error(error_message.str()); } sqlite3_backup *backupObj = sqlite3_backup_init( SQLiteQueryExecutor::getConnection(), "main", backupDB, "main"); if (!backupObj) { std::stringstream error_message; error_message << "Failed to init backup for main compaction. Details: " << sqlite3_errmsg(SQLiteQueryExecutor::getConnection()) << std::endl; sqlite3_close(backupDB); throw std::runtime_error(error_message.str()); } int backupResult = sqlite3_backup_step(backupObj, -1); sqlite3_backup_finish(backupObj); sqlite3_close(backupDB); if (backupResult == SQLITE_BUSY || backupResult == SQLITE_LOCKED) { throw std::runtime_error( "Programmer error. Database in transaction during restore attempt."); } else if (backupResult != SQLITE_DONE) { std::stringstream error_message; error_message << "Failed to restore database from backup. Details: " << sqlite3_errstr(backupResult); throw std::runtime_error(error_message.str()); } #ifdef EMSCRIPTEN attempt_delete_file( plaintextBackupPath, "Failed to delete plaintext compaction file after successful restore."); #endif attempt_delete_file( mainCompactionPath, "Failed to delete main compaction file after successful restore."); } void SQLiteQueryExecutor::restoreFromBackupLog( const std::vector &backupLog) const { SQLiteQueryExecutor::connectionManager.restoreFromBackupLog(backupLog); } } // namespace comm diff --git a/native/cpp/CommonCpp/DatabaseManagers/entities/Thread.h b/native/cpp/CommonCpp/DatabaseManagers/entities/Thread.h index 7e36d83de..6fc84e727 100644 --- a/native/cpp/CommonCpp/DatabaseManagers/entities/Thread.h +++ b/native/cpp/CommonCpp/DatabaseManagers/entities/Thread.h @@ -1,132 +1,138 @@ #pragma once #include #include #include #include "Nullable.h" #include "SQLiteDataConverters.h" namespace comm { struct Thread { std::string id; int type; std::unique_ptr name; std::unique_ptr description; std::string color; int64_t creation_time; std::unique_ptr parent_thread_id; std::unique_ptr containing_thread_id; std::unique_ptr community; std::string members; std::string roles; std::string current_user; std::unique_ptr source_message_id; int replies_count; std::unique_ptr avatar; int pinned_count; + std::unique_ptr timestamps; static Thread fromSQLResult(sqlite3_stmt *sqlRow, int idx) { return Thread{ getStringFromSQLRow(sqlRow, idx), getIntFromSQLRow(sqlRow, idx + 1), getStringPtrFromSQLRow(sqlRow, idx + 2), getStringPtrFromSQLRow(sqlRow, idx + 3), getStringFromSQLRow(sqlRow, idx + 4), getInt64FromSQLRow(sqlRow, idx + 5), getStringPtrFromSQLRow(sqlRow, idx + 6), getStringPtrFromSQLRow(sqlRow, idx + 7), getStringPtrFromSQLRow(sqlRow, idx + 8), getStringFromSQLRow(sqlRow, idx + 9), getStringFromSQLRow(sqlRow, idx + 10), getStringFromSQLRow(sqlRow, idx + 11), getStringPtrFromSQLRow(sqlRow, idx + 12), getIntFromSQLRow(sqlRow, idx + 13), getStringPtrFromSQLRow(sqlRow, idx + 14), getIntFromSQLRow(sqlRow, idx + 15), + getStringPtrFromSQLRow(sqlRow, idx + 16), }; } int bindToSQL(sqlite3_stmt *sql, int idx) const { bindStringToSQL(id, sql, idx); bindIntToSQL(type, sql, idx + 1); bindStringPtrToSQL(name, sql, idx + 2); bindStringPtrToSQL(description, sql, idx + 3); bindStringToSQL(color, sql, idx + 4); bindInt64ToSQL(reinterpret_cast(time), sql, idx + 5); bindStringPtrToSQL(parent_thread_id, sql, idx + 6); bindStringPtrToSQL(containing_thread_id, sql, idx + 7); bindStringPtrToSQL(community, sql, idx + 8); bindStringToSQL(members, sql, idx + 9); bindStringToSQL(roles, sql, idx + 10); bindStringToSQL(current_user, sql, idx + 11); bindStringPtrToSQL(source_message_id, sql, idx + 12); bindIntToSQL(replies_count, sql, idx + 13); bindStringPtrToSQL(avatar, sql, idx + 14); - return bindIntToSQL(pinned_count, sql, idx + 15); + bindIntToSQL(pinned_count, sql, idx + 15); + return bindStringPtrToSQL(timestamps, sql, idx + 16); } }; struct WebThread { std::string id; int type; NullableString name; NullableString description; std::string color; std::string creation_time; NullableString parent_thread_id; NullableString containing_thread_id; NullableString community; std::string members; std::string roles; std::string current_user; NullableString source_message_id; int replies_count; NullableString avatar; int pinned_count; + NullableString timestamps; WebThread() = default; WebThread(const Thread &thread) { id = thread.id; type = thread.type; name = NullableString(thread.name); description = NullableString(thread.description); color = thread.color; creation_time = std::to_string(thread.creation_time); parent_thread_id = NullableString(thread.parent_thread_id); containing_thread_id = NullableString(thread.containing_thread_id); community = NullableString(thread.community); members = thread.members; roles = thread.roles; current_user = thread.current_user; source_message_id = NullableString(thread.source_message_id); replies_count = thread.replies_count; avatar = NullableString(thread.avatar); pinned_count = thread.pinned_count; + timestamps = NullableString(thread.timestamps); } Thread toThread() const { Thread thread; thread.id = id; thread.type = type; thread.name = name.resetValue(); thread.description = description.resetValue(); thread.color = color; thread.creation_time = std::stoll(creation_time); thread.parent_thread_id = parent_thread_id.resetValue(); thread.containing_thread_id = containing_thread_id.resetValue(); thread.community = community.resetValue(); thread.members = members; thread.roles = roles; thread.current_user = current_user; thread.source_message_id = source_message_id.resetValue(); thread.replies_count = replies_count; thread.avatar = avatar.resetValue(); thread.pinned_count = pinned_count; + thread.timestamps = timestamps.resetValue(); return thread; } }; } // namespace comm diff --git a/native/cpp/CommonCpp/NativeModules/PersistentStorageUtilities/DataStores/ThreadStore.cpp b/native/cpp/CommonCpp/NativeModules/PersistentStorageUtilities/DataStores/ThreadStore.cpp index d369d3c7d..f68869c21 100644 --- a/native/cpp/CommonCpp/NativeModules/PersistentStorageUtilities/DataStores/ThreadStore.cpp +++ b/native/cpp/CommonCpp/NativeModules/PersistentStorageUtilities/DataStores/ThreadStore.cpp @@ -1,199 +1,210 @@ #include "ThreadStore.h" #include "../../DBOperationBase.h" #include #include namespace comm { OperationType ThreadStore::REMOVE_OPERATION = "remove"; OperationType ThreadStore::REMOVE_ALL_OPERATION = "remove_all"; OperationType ThreadStore::REPLACE_OPERATION = "replace"; ThreadStore::ThreadStore( std::shared_ptr jsInvoker) : BaseDataStore(jsInvoker) { } jsi::Array ThreadStore::parseDBDataStore( jsi::Runtime &rt, std::shared_ptr> threadsVectorPtr) const { size_t numThreads = threadsVectorPtr->size(); jsi::Array jsiThreads = jsi::Array(rt, numThreads); size_t writeIdx = 0; for (const Thread &thread : *threadsVectorPtr) { jsi::Object jsiThread = jsi::Object(rt); jsiThread.setProperty(rt, "id", thread.id); jsiThread.setProperty(rt, "type", thread.type); jsiThread.setProperty( rt, "name", thread.name ? jsi::String::createFromUtf8(rt, *thread.name) : jsi::Value::null()); jsiThread.setProperty( rt, "description", thread.description ? jsi::String::createFromUtf8(rt, *thread.description) : jsi::Value::null()); jsiThread.setProperty(rt, "color", thread.color); jsiThread.setProperty( rt, "creationTime", std::to_string(thread.creation_time)); jsiThread.setProperty( rt, "parentThreadID", thread.parent_thread_id ? jsi::String::createFromUtf8(rt, *thread.parent_thread_id) : jsi::Value::null()); jsiThread.setProperty( rt, "containingThreadID", thread.containing_thread_id ? jsi::String::createFromUtf8(rt, *thread.containing_thread_id) : jsi::Value::null()); jsiThread.setProperty( rt, "community", thread.community ? jsi::String::createFromUtf8(rt, *thread.community) : jsi::Value::null()); jsiThread.setProperty(rt, "members", thread.members); jsiThread.setProperty(rt, "roles", thread.roles); jsiThread.setProperty(rt, "currentUser", thread.current_user); jsiThread.setProperty( rt, "sourceMessageID", thread.source_message_id ? jsi::String::createFromUtf8(rt, *thread.source_message_id) : jsi::Value::null()); jsiThread.setProperty(rt, "repliesCount", thread.replies_count); jsiThread.setProperty(rt, "pinnedCount", thread.pinned_count); if (thread.avatar) { auto avatar = jsi::String::createFromUtf8(rt, *thread.avatar); jsiThread.setProperty(rt, "avatar", avatar); } + if (thread.timestamps) { + auto timestamps = jsi::String::createFromUtf8(rt, *thread.timestamps); + jsiThread.setProperty(rt, "timestamps", timestamps); + } + jsiThreads.setValueAtIndex(rt, writeIdx++, jsiThread); } return jsiThreads; } std::vector> ThreadStore::createOperations( jsi::Runtime &rt, const jsi::Array &operations) const { std::vector> threadStoreOps; for (size_t idx = 0; idx < operations.size(rt); idx++) { jsi::Object op = operations.getValueAtIndex(rt, idx).asObject(rt); std::string opType = op.getProperty(rt, "type").asString(rt).utf8(rt); if (opType == REMOVE_OPERATION) { std::vector threadIDsToRemove; jsi::Object payloadObj = op.getProperty(rt, "payload").asObject(rt); jsi::Array threadIDs = payloadObj.getProperty(rt, "ids").asObject(rt).asArray(rt); for (int threadIdx = 0; threadIdx < threadIDs.size(rt); threadIdx++) { threadIDsToRemove.push_back( threadIDs.getValueAtIndex(rt, threadIdx).asString(rt).utf8(rt)); } threadStoreOps.push_back(std::make_unique( std::move(threadIDsToRemove))); } else if (opType == REMOVE_ALL_OPERATION) { threadStoreOps.push_back(std::make_unique()); } else if (opType == REPLACE_OPERATION) { jsi::Object threadObj = op.getProperty(rt, "payload").asObject(rt); std::string threadID = threadObj.getProperty(rt, "id").asString(rt).utf8(rt); int type = std::lround(threadObj.getProperty(rt, "type").asNumber()); jsi::Value maybeName = threadObj.getProperty(rt, "name"); std::unique_ptr name = maybeName.isString() ? std::make_unique(maybeName.asString(rt).utf8(rt)) : nullptr; jsi::Value maybeDescription = threadObj.getProperty(rt, "description"); std::unique_ptr description = maybeDescription.isString() ? std::make_unique( maybeDescription.asString(rt).utf8(rt)) : nullptr; std::string color = threadObj.getProperty(rt, "color").asString(rt).utf8(rt); int64_t creationTime = std::stoll( threadObj.getProperty(rt, "creationTime").asString(rt).utf8(rt)); jsi::Value maybeParentThreadID = threadObj.getProperty(rt, "parentThreadID"); std::unique_ptr parentThreadID = maybeParentThreadID.isString() ? std::make_unique( maybeParentThreadID.asString(rt).utf8(rt)) : nullptr; jsi::Value maybeContainingThreadID = threadObj.getProperty(rt, "containingThreadID"); std::unique_ptr containingThreadID = maybeContainingThreadID.isString() ? std::make_unique( maybeContainingThreadID.asString(rt).utf8(rt)) : nullptr; jsi::Value maybeCommunity = threadObj.getProperty(rt, "community"); std::unique_ptr community = maybeCommunity.isString() ? std::make_unique(maybeCommunity.asString(rt).utf8(rt)) : nullptr; std::string members = threadObj.getProperty(rt, "members").asString(rt).utf8(rt); std::string roles = threadObj.getProperty(rt, "roles").asString(rt).utf8(rt); std::string currentUser = threadObj.getProperty(rt, "currentUser").asString(rt).utf8(rt); jsi::Value maybeSourceMessageID = threadObj.getProperty(rt, "sourceMessageID"); std::unique_ptr sourceMessageID = maybeSourceMessageID.isString() ? std::make_unique( maybeSourceMessageID.asString(rt).utf8(rt)) : nullptr; int repliesCount = std::lround(threadObj.getProperty(rt, "repliesCount").asNumber()); jsi::Value maybeAvatar = threadObj.getProperty(rt, "avatar"); std::unique_ptr avatar = maybeAvatar.isString() ? std::make_unique(maybeAvatar.asString(rt).utf8(rt)) : nullptr; jsi::Value maybePinnedCount = threadObj.getProperty(rt, "pinnedCount"); int pinnedCount = maybePinnedCount.isNumber() ? std::lround(maybePinnedCount.asNumber()) : 0; + + jsi::Value maybeTimestamps = threadObj.getProperty(rt, "timestamps"); + std::unique_ptr timestamps = maybeTimestamps.isString() + ? std::make_unique(maybeTimestamps.asString(rt).utf8(rt)) + : nullptr; Thread thread{ threadID, type, std::move(name), std::move(description), color, creationTime, std::move(parentThreadID), std::move(containingThreadID), std::move(community), members, roles, currentUser, std::move(sourceMessageID), repliesCount, std::move(avatar), - pinnedCount}; + pinnedCount, + std::move(timestamps)}; threadStoreOps.push_back( std::make_unique(std::move(thread))); } else { throw std::runtime_error("unsupported operation: " + opType); } }; return threadStoreOps; } } // namespace comm diff --git a/web/cpp/SQLiteQueryExecutorBindings.cpp b/web/cpp/SQLiteQueryExecutorBindings.cpp index 97d1dcf32..ee5767a73 100644 --- a/web/cpp/SQLiteQueryExecutorBindings.cpp +++ b/web/cpp/SQLiteQueryExecutorBindings.cpp @@ -1,416 +1,417 @@ #include "SQLiteQueryExecutor.cpp" #include "entities/InboundP2PMessage.h" #include "entities/Nullable.h" #include "entities/OutboundP2PMessage.h" #include #include #include namespace comm { using namespace emscripten; std::string getExceptionMessage(int exceptionPtr) { if (exceptionPtr == 0) { return std::string("Exception pointer value was null"); } std::exception *e = reinterpret_cast(exceptionPtr); if (e) { return std::string(e->what()); } return std::string("Pointer to exception was invalid"); } EMSCRIPTEN_BINDINGS(SQLiteQueryExecutor) { function("getExceptionMessage", &getExceptionMessage); value_object("NullableString") .field("value", &NullableString::value) .field("isNull", &NullableString::isNull); value_object("NullableInt") .field("value", &NullableInt::value) .field("isNull", &NullableInt::isNull); value_object("Draft") .field("key", &Draft::key) .field("text", &Draft::text); value_object("Report") .field("id", &Report::id) .field("report", &Report::report); value_object("PersistItem") .field("key", &PersistItem::key) .field("item", &PersistItem::item); value_object("UserInfo") .field("id", &UserInfo::id) .field("userInfo", &UserInfo::user_info); value_object("KeyserverInfo") .field("id", &KeyserverInfo::id) .field("keyserverInfo", &KeyserverInfo::keyserver_info) .field("syncedKeyserverInfo", &KeyserverInfo::synced_keyserver_info); value_object("MessageStoreThreads") .field("id", &MessageStoreThread::id) .field("startReached", &MessageStoreThread::start_reached); value_object("CommunityInfo") .field("id", &CommunityInfo::id) .field("communityInfo", &CommunityInfo::community_info); value_object("IntegrityThreadHash") .field("id", &IntegrityThreadHash::id) .field("threadHash", &IntegrityThreadHash::thread_hash); value_object("SyncedMetadataEntry") .field("name", &SyncedMetadataEntry::name) .field("data", &SyncedMetadataEntry::data); value_object("AuxUserInfo") .field("id", &AuxUserInfo::id) .field("auxUserInfo", &AuxUserInfo::aux_user_info); value_object("ThreadActivityEntry") .field("id", &ThreadActivityEntry::id) .field( "threadActivityStoreEntry", &ThreadActivityEntry::thread_activity_store_entry); value_object("EntryInfo") .field("id", &EntryInfo::id) .field("entry", &EntryInfo::entry); value_object("LocalMessageInfo") .field("id", &LocalMessageInfo::id) .field("localMessageInfo", &LocalMessageInfo::local_message_info); value_object("WebThread") .field("id", &WebThread::id) .field("type", &WebThread::type) .field("name", &WebThread::name) .field("description", &WebThread::description) .field("color", &WebThread::color) .field("creationTime", &WebThread::creation_time) .field("parentThreadID", &WebThread::parent_thread_id) .field("containingThreadID", &WebThread::containing_thread_id) .field("community", &WebThread::community) .field("members", &WebThread::members) .field("roles", &WebThread::roles) .field("currentUser", &WebThread::current_user) .field("sourceMessageID", &WebThread::source_message_id) .field("repliesCount", &WebThread::replies_count) .field("avatar", &WebThread::avatar) - .field("pinnedCount", &WebThread::pinned_count); + .field("pinnedCount", &WebThread::pinned_count) + .field("timestamps", &WebThread::timestamps); value_object("WebMessage") .field("id", &WebMessage::id) .field("localID", &WebMessage::local_id) .field("thread", &WebMessage::thread) .field("user", &WebMessage::user) .field("type", &WebMessage::type) .field("futureType", &WebMessage::future_type) .field("content", &WebMessage::content) .field("time", &WebMessage::time); value_object("Media") .field("id", &Media::id) .field("container", &Media::container) .field("thread", &Media::thread) .field("uri", &Media::uri) .field("type", &Media::type) .field("extras", &Media::extras); value_object("MessageWithMedias") .field("message", &MessageWithMedias::message) .field("medias", &MessageWithMedias::medias); value_object("OlmPersistSession") .field("targetDeviceID", &OlmPersistSession::target_device_id) .field("sessionData", &OlmPersistSession::session_data) .field("version", &OlmPersistSession::version); value_object("OutboundP2PMessage") .field("messageID", &OutboundP2PMessage::message_id) .field("deviceID", &OutboundP2PMessage::device_id) .field("userID", &OutboundP2PMessage::user_id) .field("timestamp", &OutboundP2PMessage::timestamp) .field("plaintext", &OutboundP2PMessage::plaintext) .field("ciphertext", &OutboundP2PMessage::ciphertext) .field("status", &OutboundP2PMessage::status) .field("supportsAutoRetry", &OutboundP2PMessage::supports_auto_retry); value_object("InboundP2PMessage") .field("messageID", &InboundP2PMessage::message_id) .field("senderDeviceID", &InboundP2PMessage::sender_device_id) .field("senderUserID", &InboundP2PMessage::sender_user_id) .field("plaintext", &InboundP2PMessage::plaintext) .field("status", &InboundP2PMessage::status); class_("SQLiteQueryExecutor") .constructor() .function("updateDraft", &SQLiteQueryExecutor::updateDraft) .function("moveDraft", &SQLiteQueryExecutor::moveDraft) .function("getAllDrafts", &SQLiteQueryExecutor::getAllDrafts) .function("removeAllDrafts", &SQLiteQueryExecutor::removeAllDrafts) .function("removeDrafts", &SQLiteQueryExecutor::removeDrafts) .function( "getInitialMessagesWeb", &SQLiteQueryExecutor::getInitialMessagesWeb) .function("removeAllMessages", &SQLiteQueryExecutor::removeAllMessages) .function("removeMessages", &SQLiteQueryExecutor::removeMessages) .function( "removeMessagesForThreads", &SQLiteQueryExecutor::removeMessagesForThreads) .function("replaceMessageWeb", &SQLiteQueryExecutor::replaceMessageWeb) .function("rekeyMessage", &SQLiteQueryExecutor::rekeyMessage) .function("removeAllMedia", &SQLiteQueryExecutor::removeAllMedia) .function( "removeMediaForThreads", &SQLiteQueryExecutor::removeMediaForThreads) .function( "removeMediaForMessage", &SQLiteQueryExecutor::removeMediaForMessage) .function( "removeMediaForMessages", &SQLiteQueryExecutor::removeMediaForMessages) .function("replaceMedia", &SQLiteQueryExecutor::replaceMedia) .function( "rekeyMediaContainers", &SQLiteQueryExecutor::rekeyMediaContainers) .function( "replaceMessageStoreThreads", &SQLiteQueryExecutor::replaceMessageStoreThreads) .function( "removeMessageStoreThreads", &SQLiteQueryExecutor::removeMessageStoreThreads) .function( "getAllMessageStoreThreads", &SQLiteQueryExecutor::getAllMessageStoreThreads) .function( "removeAllMessageStoreThreads", &SQLiteQueryExecutor::removeAllMessageStoreThreads) .function("setMetadata", &SQLiteQueryExecutor::setMetadata) .function("clearMetadata", &SQLiteQueryExecutor::clearMetadata) .function("getMetadata", &SQLiteQueryExecutor::getMetadata) .function("replaceReport", &SQLiteQueryExecutor::replaceReport) .function("removeReports", &SQLiteQueryExecutor::removeReports) .function("removeAllReports", &SQLiteQueryExecutor::removeAllReports) .function("getAllReports", &SQLiteQueryExecutor::getAllReports) .function( "setPersistStorageItem", &SQLiteQueryExecutor::setPersistStorageItem) .function( "removePersistStorageItem", &SQLiteQueryExecutor::removePersistStorageItem) .function( "getPersistStorageItem", &SQLiteQueryExecutor::getPersistStorageItem) .function("replaceUser", &SQLiteQueryExecutor::replaceUser) .function("removeUsers", &SQLiteQueryExecutor::removeUsers) .function("removeAllUsers", &SQLiteQueryExecutor::removeAllUsers) .function("getAllUsers", &SQLiteQueryExecutor::getAllUsers) .function("replaceThreadWeb", &SQLiteQueryExecutor::replaceThreadWeb) .function("getAllThreadsWeb", &SQLiteQueryExecutor::getAllThreadsWeb) .function("removeAllThreads", &SQLiteQueryExecutor::removeAllThreads) .function("removeThreads", &SQLiteQueryExecutor::removeThreads) .function("replaceKeyserver", &SQLiteQueryExecutor::replaceKeyserver) .function("removeKeyservers", &SQLiteQueryExecutor::removeKeyservers) .function( "removeAllKeyservers", &SQLiteQueryExecutor::removeAllKeyservers) .function("getAllKeyservers", &SQLiteQueryExecutor::getAllKeyservers) .function("replaceCommunity", &SQLiteQueryExecutor::replaceCommunity) .function("removeCommunities", &SQLiteQueryExecutor::removeCommunities) .function( "removeAllCommunities", &SQLiteQueryExecutor::removeAllCommunities) .function("getAllCommunities", &SQLiteQueryExecutor::getAllCommunities) .function( "replaceIntegrityThreadHashes", &SQLiteQueryExecutor::replaceIntegrityThreadHashes) .function( "removeIntegrityThreadHashes", &SQLiteQueryExecutor::removeIntegrityThreadHashes) .function( "removeAllIntegrityThreadHashes", &SQLiteQueryExecutor::removeAllIntegrityThreadHashes) .function( "getAllIntegrityThreadHashes", &SQLiteQueryExecutor::getAllIntegrityThreadHashes) .function( "replaceSyncedMetadataEntry", &SQLiteQueryExecutor::replaceSyncedMetadataEntry) .function( "removeSyncedMetadata", &SQLiteQueryExecutor::removeSyncedMetadata) .function( "removeAllSyncedMetadata", &SQLiteQueryExecutor::removeAllSyncedMetadata) .function( "getAllSyncedMetadata", &SQLiteQueryExecutor::getAllSyncedMetadata) .function("replaceAuxUserInfo", &SQLiteQueryExecutor::replaceAuxUserInfo) .function("removeAuxUserInfos", &SQLiteQueryExecutor::removeAuxUserInfos) .function( "removeAllAuxUserInfos", &SQLiteQueryExecutor::removeAllAuxUserInfos) .function("getAllAuxUserInfos", &SQLiteQueryExecutor::getAllAuxUserInfos) .function( "replaceThreadActivityEntry", &SQLiteQueryExecutor::replaceThreadActivityEntry) .function( "removeThreadActivityEntries", &SQLiteQueryExecutor::removeThreadActivityEntries) .function( "removeAllThreadActivityEntries", &SQLiteQueryExecutor::removeAllThreadActivityEntries) .function( "getAllThreadActivityEntries", &SQLiteQueryExecutor::getAllThreadActivityEntries) .function("replaceEntry", &SQLiteQueryExecutor::replaceEntry) .function("removeEntries", &SQLiteQueryExecutor::removeEntries) .function("removeAllEntries", &SQLiteQueryExecutor::removeAllEntries) .function("getAllEntries", &SQLiteQueryExecutor::getAllEntries) .function( "replaceMessageStoreLocalMessageInfo", &SQLiteQueryExecutor::replaceMessageStoreLocalMessageInfo) .function( "removeMessageStoreLocalMessageInfos", &SQLiteQueryExecutor::removeMessageStoreLocalMessageInfos) .function( "removeAllMessageStoreLocalMessageInfos", &SQLiteQueryExecutor::removeAllMessageStoreLocalMessageInfos) .function( "getAllMessageStoreLocalMessageInfos", &SQLiteQueryExecutor::getAllMessageStoreLocalMessageInfos) .function("beginTransaction", &SQLiteQueryExecutor::beginTransaction) .function("commitTransaction", &SQLiteQueryExecutor::commitTransaction) .function( "getContentAccountID", &SQLiteQueryExecutor::getContentAccountID) .function("getNotifsAccountID", &SQLiteQueryExecutor::getNotifsAccountID) .function( "getOlmPersistSessionsData", &SQLiteQueryExecutor::getOlmPersistSessionsData) .function( "getOlmPersistAccountDataWeb", &SQLiteQueryExecutor::getOlmPersistAccountDataWeb) .function( "storeOlmPersistSession", &SQLiteQueryExecutor::storeOlmPersistSession) .function( "storeOlmPersistAccount", &SQLiteQueryExecutor::storeOlmPersistAccount) .function( "rollbackTransaction", &SQLiteQueryExecutor::rollbackTransaction) .function( "restoreFromMainCompaction", &SQLiteQueryExecutor::restoreFromMainCompaction) .function( "restoreFromBackupLog", &SQLiteQueryExecutor::restoreFromBackupLog) .function( "addOutboundP2PMessages", &SQLiteQueryExecutor::addOutboundP2PMessages) .function( "removeOutboundP2PMessage", &SQLiteQueryExecutor::removeOutboundP2PMessage) .function( "removeAllOutboundP2PMessages", &SQLiteQueryExecutor::removeAllOutboundP2PMessages) .function( "getOutboundP2PMessagesByID", &SQLiteQueryExecutor::getOutboundP2PMessagesByID) .function( "getAllOutboundP2PMessages", &SQLiteQueryExecutor::getAllOutboundP2PMessages) .function( "setCiphertextForOutboundP2PMessage", &SQLiteQueryExecutor::setCiphertextForOutboundP2PMessage) .function( "markOutboundP2PMessageAsSent", &SQLiteQueryExecutor::markOutboundP2PMessageAsSent) .function( "resetOutboundP2PMessagesForDevice", &SQLiteQueryExecutor::resetOutboundP2PMessagesForDevice) .function( "addInboundP2PMessage", &SQLiteQueryExecutor::addInboundP2PMessage) .function( "getAllInboundP2PMessage", &SQLiteQueryExecutor::getAllInboundP2PMessage) .function( "removeInboundP2PMessages", &SQLiteQueryExecutor::removeInboundP2PMessages) .function( "getRelatedMessagesWeb", &SQLiteQueryExecutor::getRelatedMessagesWeb) .function( "updateMessageSearchIndex", &SQLiteQueryExecutor::updateMessageSearchIndex) .function("searchMessages", &SQLiteQueryExecutor::searchMessagesWeb) .function("fetchMessagesWeb", &SQLiteQueryExecutor::fetchMessagesWeb); } } // namespace comm namespace emscripten { namespace internal { template struct BindingType> { using ValBinding = BindingType; using WireType = ValBinding::WireType; static WireType toWireType(const std::vector &vec) { std::vector valVec(vec.begin(), vec.end()); return BindingType::toWireType(val::array(valVec)); } static std::vector fromWireType(WireType value) { return vecFromJSArray(ValBinding::fromWireType(value)); } }; template struct TypeID< T, typename std::enable_if_t::type, std::vector< typename Canonicalized::type::value_type, typename Canonicalized::type::allocator_type>>::value>> { static constexpr TYPEID get() { return TypeID::get(); } }; template struct TypeID> { static constexpr TYPEID get() { return LightTypeID::get(); } }; template struct TypeID> { static constexpr TYPEID get() { return LightTypeID::get(); } }; template struct TypeID &> { static constexpr TYPEID get() { return LightTypeID::get(); } }; template struct TypeID &&> { static constexpr TYPEID get() { return LightTypeID::get(); } }; template struct TypeID &> { static constexpr TYPEID get() { return LightTypeID::get(); } }; template struct BindingType> { using ValBinding = BindingType; using WireType = ValBinding::WireType; static WireType toWireType(std::optional const &opt) { if (!opt.has_value()) { return ValBinding::toWireType(val::null()); } return ValBinding::toWireType(val(opt.value())); } static std::optional fromWireType(WireType value) { val convertedVal = ValBinding::fromWireType(value); if (convertedVal.isNull() || convertedVal.isUndefined()) { return std::nullopt; } return std::make_optional(convertedVal.as()); } }; } // namespace internal } // namespace emscripten diff --git a/web/shared-worker/_generated/comm_query_executor.wasm b/web/shared-worker/_generated/comm_query_executor.wasm index 8b8a90020..6062a765d 100755 Binary files a/web/shared-worker/_generated/comm_query_executor.wasm and b/web/shared-worker/_generated/comm_query_executor.wasm differ diff --git a/web/shared-worker/queries/messages-and-media-queries.test.js b/web/shared-worker/queries/messages-and-media-queries.test.js index caa60048f..adcebf8f8 100644 --- a/web/shared-worker/queries/messages-and-media-queries.test.js +++ b/web/shared-worker/queries/messages-and-media-queries.test.js @@ -1,221 +1,223 @@ // @flow import { threadTypes } from 'lib/types/thread-types-enum.js'; import { getDatabaseModule } from '../db-module.js'; import { createNullableString } from '../types/entities.js'; import { clearSensitiveData } from '../utils/db-utils.js'; const FILE_PATH = 'test.sqlite'; describe('Message and media store queries', () => { let queryExecutor; let dbModule; beforeAll(async () => { dbModule = getDatabaseModule(); }); beforeEach(() => { if (!dbModule) { throw new Error('Database module is missing'); } queryExecutor = new dbModule.SQLiteQueryExecutor(FILE_PATH); if (!queryExecutor) { throw new Error('SQLiteQueryExecutor is missing'); } queryExecutor.replaceMessageWeb({ id: '1', localID: { value: '', isNull: true }, thread: '1', user: '1', type: 0, futureType: { value: 0, isNull: true }, content: { value: '', isNull: true }, time: '0', }); queryExecutor.replaceMessageWeb({ id: '2', localID: { value: '', isNull: true }, thread: '1', user: '1', type: 0, futureType: { value: 0, isNull: true }, content: { value: '', isNull: true }, time: '0', }); queryExecutor.replaceMessageWeb({ id: '3', localID: { value: '', isNull: true }, thread: '2', user: '1', type: 0, futureType: { value: 5, isNull: false }, content: { value: '', isNull: true }, time: '0', }); queryExecutor.replaceMedia({ id: '1', container: '1', thread: '1', uri: '1', type: 'photo', extras: '1', }); queryExecutor.replaceMedia({ id: '2', container: '1', thread: '1', uri: '1', type: 'photo', extras: '1', }); queryExecutor.replaceMedia({ id: '3', container: '3', thread: '2', uri: '1', type: 'photo', extras: '1', }); queryExecutor.replaceMedia({ id: '4', container: '3', thread: '2', uri: '1', type: 'photo', extras: '1', }); queryExecutor.replaceThreadWeb({ id: '1', type: threadTypes.COMMUNITY_OPEN_SUBTHREAD, name: createNullableString(), avatar: createNullableString(), description: createNullableString(), color: 'ffffff', creationTime: '1', parentThreadID: createNullableString(), containingThreadID: createNullableString(), community: createNullableString(), members: '1', roles: '1', currentUser: '{}', sourceMessageID: createNullableString(), repliesCount: 0, pinnedCount: 0, + timestamps: createNullableString(), }); queryExecutor.replaceThreadWeb({ id: '2', type: threadTypes.COMMUNITY_OPEN_SUBTHREAD, name: createNullableString(), avatar: createNullableString(), description: createNullableString(), color: 'ffffff', creationTime: '1', parentThreadID: createNullableString(), containingThreadID: createNullableString(), community: createNullableString(), members: '1', roles: '1', currentUser: '{}', sourceMessageID: createNullableString(), repliesCount: 0, pinnedCount: 0, + timestamps: createNullableString(), }); }); afterEach(() => { clearSensitiveData(dbModule, FILE_PATH, queryExecutor); }); it('should return all messages with media', () => { const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages.length).toBe(3); expect(allMessages[0].medias.length).toBe(2); expect(allMessages[1].medias.length).toBe(0); expect(allMessages[2].medias.length).toBe(2); }); it('should remove all messages', () => { queryExecutor.removeAllMessages(); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages.length).toBe(0); }); it('should remove all media', () => { queryExecutor.removeAllMedia(); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages[0].medias.length).toBe(0); expect(allMessages[1].medias.length).toBe(0); expect(allMessages[2].medias.length).toBe(0); }); it('should remove all messages for threads', () => { queryExecutor.removeMessagesForThreads(['1']); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages.length).toBe(1); }); it('should remove all messages with ids', () => { queryExecutor.removeMessages(['1']); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages.length).toBe(2); }); it('should remove all media for message', () => { queryExecutor.removeMediaForMessage('1'); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages[0].medias.length).toBe(0); expect(allMessages[1].medias.length).toBe(0); expect(allMessages[2].medias.length).toBe(2); }); it('should remove all media for messages', () => { queryExecutor.removeMediaForMessages(['3']); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages[0].medias.length).toBe(2); expect(allMessages[1].medias.length).toBe(0); expect(allMessages[2].medias.length).toBe(0); }); it('should remove all media for threads', () => { queryExecutor.removeMediaForThreads(['2']); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages[0].medias.length).toBe(2); expect(allMessages[1].medias.length).toBe(0); expect(allMessages[2].medias.length).toBe(0); }); it('should rekey media containers', () => { queryExecutor.rekeyMediaContainers('1', '3'); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages[0].medias.length).toBe(0); expect(allMessages[1].medias.length).toBe(0); expect(allMessages[2].medias.length).toBe(4); }); it('should rekey message', () => { queryExecutor.rekeyMessage('3', '2'); const allMessages = queryExecutor.getInitialMessagesWeb(); expect(allMessages.length).toBe(2); const rekeyedMessage = allMessages.find( messageWithMedia => messageWithMedia.message.id === '2', ); expect(rekeyedMessage?.message.thread).toBe('2'); }); it('should correctly handle nullable integer', () => { const allMessages = queryExecutor.getInitialMessagesWeb(); const messageWithNullFutureType = allMessages.find( messageWithMedia => messageWithMedia.message.id === '1', ); const messageWithNonNullIFutureType = allMessages.find( messageWithMedia => messageWithMedia.message.id === '3', ); expect(messageWithNullFutureType?.message.futureType.isNull).toBe(true); expect(messageWithNonNullIFutureType?.message.futureType.isNull).toBe( false, ); expect(messageWithNonNullIFutureType?.message.futureType.value).toBe(5); }); }); diff --git a/web/shared-worker/queries/threads-queries.test.js b/web/shared-worker/queries/threads-queries.test.js index c1ff60d62..f9b86f4bb 100644 --- a/web/shared-worker/queries/threads-queries.test.js +++ b/web/shared-worker/queries/threads-queries.test.js @@ -1,100 +1,104 @@ // @flow import { getDatabaseModule } from '../db-module.js'; +import { createNullableString } from '../types/entities.js'; import { clearSensitiveData } from '../utils/db-utils.js'; const FILE_PATH = 'test.sqlite'; describe('Threads queries', () => { let queryExecutor; let dbModule; beforeAll(async () => { dbModule = getDatabaseModule(); }); beforeEach(() => { if (!dbModule) { throw new Error('Database module is missing'); } queryExecutor = new dbModule.SQLiteQueryExecutor(FILE_PATH); if (!queryExecutor) { throw new Error('SQLiteQueryExecutor is missing'); } queryExecutor.replaceThreadWeb({ id: '1', type: 1, - name: { value: '', isNull: true }, - avatar: { value: '', isNull: true }, - description: { value: '', isNull: true }, + name: createNullableString(), + avatar: createNullableString(), + description: createNullableString(), color: '1', creationTime: '1', - parentThreadID: { value: '', isNull: true }, - containingThreadID: { value: '', isNull: true }, - community: { value: '', isNull: true }, + parentThreadID: createNullableString(), + containingThreadID: createNullableString(), + community: createNullableString(), members: '1', roles: '1', currentUser: '1', - sourceMessageID: { value: '', isNull: true }, + sourceMessageID: createNullableString(), repliesCount: 1, pinnedCount: 1, + timestamps: createNullableString(), }); queryExecutor.replaceThreadWeb({ id: '2', type: 1, - name: { value: '', isNull: true }, - avatar: { value: '', isNull: true }, - description: { value: '', isNull: true }, + name: createNullableString(), + avatar: createNullableString(), + description: createNullableString(), color: '1', creationTime: '1', - parentThreadID: { value: '', isNull: true }, - containingThreadID: { value: '', isNull: true }, - community: { value: '', isNull: true }, + parentThreadID: createNullableString(), + containingThreadID: createNullableString(), + community: createNullableString(), members: '1', roles: '1', currentUser: '1', - sourceMessageID: { value: '', isNull: true }, + sourceMessageID: createNullableString(), repliesCount: 1, pinnedCount: 1, + timestamps: createNullableString(), }); queryExecutor.replaceThreadWeb({ id: '3', type: 1, - name: { value: '', isNull: true }, - avatar: { value: '', isNull: true }, - description: { value: '', isNull: true }, + name: createNullableString(), + avatar: createNullableString(), + description: createNullableString(), color: '1', creationTime: '1', - parentThreadID: { value: '', isNull: true }, - containingThreadID: { value: '', isNull: true }, - community: { value: '', isNull: true }, + parentThreadID: createNullableString(), + containingThreadID: createNullableString(), + community: createNullableString(), members: '1', roles: '1', currentUser: '1', - sourceMessageID: { value: '', isNull: true }, + sourceMessageID: createNullableString(), repliesCount: 1, pinnedCount: 1, + timestamps: createNullableString(), }); }); afterEach(() => { clearSensitiveData(dbModule, FILE_PATH, queryExecutor); }); it('should return all threads', () => { const threads = queryExecutor.getAllThreadsWeb(); expect(threads.length).toBe(3); }); it('should remove all threads', () => { queryExecutor.removeAllThreads(); const threads = queryExecutor.getAllThreadsWeb(); expect(threads.length).toBe(0); }); it('should remove subset of threads', () => { queryExecutor.removeThreads(['2']); const threads = queryExecutor.getAllThreadsWeb(); expect(threads.length).toBe(2); }); }); diff --git a/web/shared-worker/types/entities.js b/web/shared-worker/types/entities.js index a99a728f3..885026430 100644 --- a/web/shared-worker/types/entities.js +++ b/web/shared-worker/types/entities.js @@ -1,184 +1,187 @@ // @flow import type { ClientDBMessageInfo } from 'lib/types/message-types.js'; import type { ClientDBThreadInfo } from 'lib/types/thread-types.js'; import type { Media, WebMessage } from './sqlite-query-executor.js'; export type Nullable = { +value: T, +isNull: boolean, }; export type NullableString = Nullable; export type NullableInt = Nullable; export type WebClientDBThreadInfo = { +id: string, +type: number, +name: NullableString, +avatar: NullableString, +description: NullableString, +color: string, +creationTime: string, +parentThreadID: NullableString, +containingThreadID: NullableString, +community: NullableString, +members: string, +roles: string, +currentUser: string, +sourceMessageID: NullableString, +repliesCount: number, +pinnedCount: number, + +timestamps: NullableString, }; function createNullableString(value: ?string): NullableString { if (value === null || value === undefined) { return { value: '', isNull: true, }; } return { value, isNull: false, }; } function createNullableInt(value: ?string): NullableInt { if (value === null || value === undefined) { return { value: 0, isNull: true, }; } return { value: Number(value), isNull: false, }; } function clientDBThreadInfoToWebThread( info: ClientDBThreadInfo, ): WebClientDBThreadInfo { return { id: info.id, type: info.type, name: createNullableString(info.name), avatar: createNullableString(info.avatar), description: createNullableString(info.description), color: info.color, creationTime: info.creationTime, parentThreadID: createNullableString(info.parentThreadID), containingThreadID: createNullableString(info.containingThreadID), community: createNullableString(info.community), members: info.members, roles: info.roles, currentUser: info.currentUser, sourceMessageID: createNullableString(info.sourceMessageID), repliesCount: info.repliesCount, pinnedCount: info.pinnedCount || 0, + timestamps: createNullableString(info.timestamps), }; } function webThreadToClientDBThreadInfo( thread: WebClientDBThreadInfo, ): ClientDBThreadInfo { let result: ClientDBThreadInfo = { id: thread.id, type: thread.type, name: thread.name.isNull ? null : thread.name.value, avatar: thread.avatar.isNull ? null : thread.avatar.value, description: thread.description.isNull ? null : thread.description.value, color: thread.color, creationTime: thread.creationTime, parentThreadID: thread.parentThreadID.isNull ? null : thread.parentThreadID.value, containingThreadID: thread.containingThreadID.isNull ? null : thread.containingThreadID.value, community: thread.community.isNull ? null : thread.community.value, members: thread.members, roles: thread.roles, currentUser: thread.currentUser, repliesCount: thread.repliesCount, pinnedCount: thread.pinnedCount, + timestamps: thread.timestamps.isNull ? null : thread.timestamps.value, }; if (!thread.sourceMessageID.isNull) { result = { ...result, sourceMessageID: thread.sourceMessageID.value, }; } return result; } function clientDBMessageInfoToWebMessage(messageInfo: ClientDBMessageInfo): { +message: WebMessage, +medias: $ReadOnlyArray, } { return { message: { id: messageInfo.id, localID: createNullableString(messageInfo.local_id), thread: messageInfo.thread, user: messageInfo.user, type: Number(messageInfo.type), futureType: createNullableInt(messageInfo.future_type), content: createNullableString(messageInfo.content), time: messageInfo.time, }, medias: messageInfo.media_infos?.map(({ id, uri, type, extras }) => ({ id, uri, type, extras, thread: messageInfo.thread, container: messageInfo.id, })) ?? [], }; } function webMessageToClientDBMessageInfo({ message, medias, }: { +message: WebMessage, +medias: $ReadOnlyArray, }): ClientDBMessageInfo { let media_infos = null; if (medias?.length !== 0) { media_infos = medias.map(({ id, uri, type, extras }) => ({ id, uri, type, extras, })); } return { id: message.id, local_id: message.localID.isNull ? null : message.localID.value, thread: message.thread, user: message.user, type: message.type.toString(), future_type: message.futureType.isNull ? null : message.futureType.value.toString(), content: message.content.isNull ? null : message.content.value, time: message.time, media_infos, }; } export { clientDBThreadInfoToWebThread, webThreadToClientDBThreadInfo, clientDBMessageInfoToWebMessage, webMessageToClientDBMessageInfo, createNullableString, createNullableInt, }; diff --git a/web/shared-worker/types/entities.test.js b/web/shared-worker/types/entities.test.js index 926e2baa8..57beef6d0 100644 --- a/web/shared-worker/types/entities.test.js +++ b/web/shared-worker/types/entities.test.js @@ -1,64 +1,65 @@ // @flow import type { ClientDBThreadInfo } from 'lib/types/thread-types.js'; import { clientDBThreadInfoToWebThread, webThreadToClientDBThreadInfo, } from './entities.js'; const clientDBThreadInfo: ClientDBThreadInfo = { id: '84015', type: 6, name: 'atul_web', description: 'Hello world!', color: '4b87aa', creationTime: '1679595843051', parentThreadID: '1', members: '[{"id":"256","role":null,"permissions":{"know_of":{"value":true,"source":"1"},"visible":{"value":true,"source":"1"},"voiced":{"value":true,"source":"1"},"edit_entries":{"value":true,"source":"1"},"edit_thread":{"value":true,"source":"1"},"edit_thread_description":{"value":true,"source":"1"},"edit_thread_color":{"value":true,"source":"1"},"delete_thread":{"value":true,"source":"1"},"create_subthreads":{"value":true,"source":"1"},"create_sidebars":{"value":true,"source":"1"},"join_thread":{"value":true,"source":"1"},"edit_permissions":{"value":true,"source":"1"},"add_members":{"value":true,"source":"1"},"remove_members":{"value":true,"source":"1"},"change_role":{"value":true,"source":"1"},"leave_thread":{"value":false,"source":null},"react_to_message":{"value":false,"source":null},"edit_message":{"value":false,"source":null}},"isSender":false},{"id":"83809","role":"84016","permissions":{"know_of":{"value":true,"source":"84015"},"visible":{"value":true,"source":"84015"},"voiced":{"value":true,"source":"84015"},"edit_entries":{"value":true,"source":"84015"},"edit_thread":{"value":true,"source":"84015"},"edit_thread_description":{"value":true,"source":"84015"},"edit_thread_color":{"value":true,"source":"84015"},"delete_thread":{"value":false,"source":null},"create_subthreads":{"value":false,"source":null},"create_sidebars":{"value":true,"source":"84015"},"join_thread":{"value":false,"source":null},"edit_permissions":{"value":false,"source":null},"add_members":{"value":false,"source":null},"remove_members":{"value":false,"source":null},"change_role":{"value":false,"source":null},"leave_thread":{"value":false,"source":null},"react_to_message":{"value":true,"source":"84015"},"edit_message":{"value":true,"source":"84015"}},"isSender":true},{"id":"83969","role":"84016","permissions":{"know_of":{"value":true,"source":"84015"},"visible":{"value":true,"source":"84015"},"voiced":{"value":true,"source":"84015"},"edit_entries":{"value":true,"source":"84015"},"edit_thread":{"value":true,"source":"84015"},"edit_thread_description":{"value":true,"source":"84015"},"edit_thread_color":{"value":true,"source":"84015"},"delete_thread":{"value":false,"source":null},"create_subthreads":{"value":false,"source":null},"create_sidebars":{"value":true,"source":"84015"},"join_thread":{"value":false,"source":null},"edit_permissions":{"value":false,"source":null},"add_members":{"value":false,"source":null},"remove_members":{"value":false,"source":null},"change_role":{"value":false,"source":null},"leave_thread":{"value":false,"source":null},"react_to_message":{"value":true,"source":"84015"},"edit_message":{"value":true,"source":"84015"}},"isSender":true}]', roles: '{"84016":{"id":"84016","name":"Members","permissions":{"know_of":true,"visible":true,"voiced":true,"react_to_message":true,"edit_message":true,"edit_entries":true,"edit_thread":true,"edit_thread_color":true,"edit_thread_description":true,"create_sidebars":true,"descendant_open_know_of":true,"descendant_open_visible":true,"child_open_join_thread":true},"isDefault":true}}', currentUser: '{"role":"84016","permissions":{"know_of":{"value":true,"source":"84015"},"visible":{"value":true,"source":"84015"},"voiced":{"value":true,"source":"84015"},"edit_entries":{"value":true,"source":"84015"},"edit_thread":{"value":true,"source":"84015"},"edit_thread_description":{"value":true,"source":"84015"},"edit_thread_color":{"value":true,"source":"84015"},"delete_thread":{"value":false,"source":null},"create_subthreads":{"value":false,"source":null},"create_sidebars":{"value":true,"source":"84015"},"join_thread":{"value":false,"source":null},"edit_permissions":{"value":false,"source":null},"add_members":{"value":false,"source":null},"remove_members":{"value":false,"source":null},"change_role":{"value":false,"source":null},"leave_thread":{"value":false,"source":null},"react_to_message":{"value":true,"source":"84015"},"edit_message":{"value":true,"source":"84015"}},"subscription":{"home":true,"pushNotifs":true},"unread":false}', repliesCount: 0, containingThreadID: '1', community: '1', avatar: null, pinnedCount: 0, + timestamps: null, }; const clientDBThreadInfoWithAvatar: ClientDBThreadInfo = { ...clientDBThreadInfo, avatar: '{"type":"emoji","color":"4b87aa","emoji":"😀"}', }; const clientDBThreadInfoWithSourceMessageID: ClientDBThreadInfo = { ...clientDBThreadInfo, sourceMessageID: '123', }; describe('ClientDBThreadInfo <> WebClientDBThreadInfo', () => { it('should successfully convert clientDBThreadInfo', () => { const webThread = clientDBThreadInfoToWebThread(clientDBThreadInfo); expect(clientDBThreadInfo).toStrictEqual( webThreadToClientDBThreadInfo(webThread), ); }); it('should successfully convert clientDBThreadInfo with nullable field (field: ?type)', () => { const webThread = clientDBThreadInfoToWebThread( clientDBThreadInfoWithAvatar, ); expect(clientDBThreadInfoWithAvatar).toStrictEqual( webThreadToClientDBThreadInfo(webThread), ); }); it('should successfully convert clientDBThreadInfo with not existing field (field?: type)', () => { const webThread = clientDBThreadInfoToWebThread( clientDBThreadInfoWithSourceMessageID, ); expect(clientDBThreadInfoWithSourceMessageID).toStrictEqual( webThreadToClientDBThreadInfo(webThread), ); }); });