Page MenuHomePhabricator
Differential D14573

[SQLite] implement `SQLiteSchema` class to wrap creation and migration logic
AcceptedPublic
Actions

Authored by kamil on Fri, Apr 11, 8:10 AM.

Details

Reviewers
bartek
tomek
Summary

Moving migration code to new class SQLiteSchema to make managing this easier.

This class is implemented in three .cpp files:

  1. SQLiteSchema.cpp - utils and methods to create/migrate
  2. SQLiteSchemaLegacyMigrations.cpp - old, existing migrations executed from the C++ level only, we should consider this to be read-only now
  3. SQLiteSchemaMigrations.cpp - place where we should add "new" migrations, executed from JS level on a standard migration process or on a backup database after restore.

The API on how to use and execute new migrations will be added later in this stack, publishing this first to get some initial feedback.

I added code comments in SQLiteSchema.h, but if something is unclear, I can clarify.

This code is copied and pasted between places, so there is no need to review the logic itself. I made one change, which is annotated with an inline comment.

The most important part to review here is native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.h file.

ENG-10540

Depends on D14572

Test Plan
  1. Checkout older commit and build an app, create an account, checkout to this to make sure migration still works.
  2. Log out to check ifthe database creation works.

Diff Detail

Repository
rCOMM Comm
Branch
poc
Lint
No Lint Coverage
Unit
No Test Coverage

Event Timeline

kamil created this revision.Fri, Apr 11, 8:10 AM
kamil held this revision as a draft.
Herald added a subscriber: ashoat. · View Herald TranscriptFri, Apr 11, 8:10 AM
Harbormaster failed remote builds in B33970: Diff 47756!Fri, Apr 11, 8:22 AM
kamil edited the summary of this revision. (Show Details)Fri, Apr 11, 8:23 AM
kamil edited the test plan for this revision. (Show Details)
kamil updated this revision to Diff 47757.Fri, Apr 11, 8:24 AM

Commit .wasm file

kamil edited the summary of this revision. (Show Details)Fri, Apr 11, 8:25 AM
kamil edited the summary of this revision. (Show Details)
kamil published this revision for review.Fri, Apr 11, 8:29 AM
kamil edited the summary of this revision. (Show Details)
kamil edited the summary of this revision. (Show Details)
kamil edited the summary of this revision. (Show Details)
kamil added inline comments.
native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.cpp
228–230

The only change is this line to get correct latest version

native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.h
18

This is type I added

native/cpp/CommonCpp/DatabaseManagers/SQLiteSchemaMigrations.cpp
14

Note: first item here should start with 55 (or anything greater than legacyMigrations)

Harbormaster completed remote builds in B33971: Diff 47757.Fri, Apr 11, 8:42 AM
tomek accepted this revision.Mon, Apr 14, 1:51 AM
This revision is now accepted and ready to land.Mon, Apr 14, 1:51 AM
tomek added inline comments.Mon, Apr 14, 1:53 AM
native/cpp/CommonCpp/DatabaseManagers/SQLiteSchemaLegacyMigrations.cpp
826

It might be a good idea to add a comment here saying that new migrations shouldn't be added - instead they should be added to the other file.

Revision Contents
Changeset List

PathSize
native/
cpp/
CommonCpp/
DatabaseManagers/
4 lines
1143 lines
60 lines
345 lines
827 lines
16 lines
ios/
Comm.xcodeproj/
14 lines
web/
cpp/
3 lines
shared-worker/
_generated/

Diff 47757

View Options

native/cpp/CommonCpp/DatabaseManagers/CMakeLists.txt

project(comm-databasemanagers) project(comm-databasemanagers)
cmake_minimum_required(VERSION 3.4) cmake_minimum_required(VERSION 3.4)
include(GNUInstallDirs) include(GNUInstallDirs)
set(DBM_HDRS set(DBM_HDRS
"DatabaseManager.h" "DatabaseManager.h"
"DatabaseQueryExecutor.h" "DatabaseQueryExecutor.h"
"SQLiteQueryExecutor.h" "SQLiteQueryExecutor.h"
"SQLiteUtils.h" "SQLiteUtils.h"
"SQLiteSchema.h"
"SQLiteConnectionManager.h" "SQLiteConnectionManager.h"
"NativeSQLiteConnectionManager.h" "NativeSQLiteConnectionManager.h"
"entities/SQLiteStatementWrapper.h" "entities/SQLiteStatementWrapper.h"
"entities/EntityQueryHelpers.h" "entities/EntityQueryHelpers.h"
"entities/SQLiteDataConverters.h" "entities/SQLiteDataConverters.h"
"entities/Draft.h" "entities/Draft.h"
"entities/Media.h" "entities/Media.h"
"entities/Message.h" "entities/Message.h"
"entities/Metadata.h" "entities/Metadata.h"
"entities/OlmPersistAccount.h" "entities/OlmPersistAccount.h"
"entities/OlmPersistSession.h" "entities/OlmPersistSession.h"
"entities/Thread.h" "entities/Thread.h"
) )
set(DBM_SRCS set(DBM_SRCS
"SQLiteQueryExecutor.cpp" "SQLiteQueryExecutor.cpp"
"SQLiteUtils.cpp" "SQLiteUtils.cpp"
"SQLiteSchema.cpp"
"SQLiteSchemaMigrations.cpp"
"SQLiteSchemaLegacyMigrations.cpp"
"SQLiteConnectionManager.cpp" "SQLiteConnectionManager.cpp"
"NativeSQLiteConnectionManager.cpp" "NativeSQLiteConnectionManager.cpp"
"entities/SQLiteDataConverters.cpp" "entities/SQLiteDataConverters.cpp"
"entities/SQLiteStatementWrapper.cpp" "entities/SQLiteStatementWrapper.cpp"
) )
add_library(comm-databasemanagers add_library(comm-databasemanagers
${DBM_HDRS} ${DBM_HDRS}
Show All 18 Lines
View Options

native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp

Loading...
View Options

native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.h

  • This file was added.
#pragma once
#include <sqlite3.h>
#include <functional>
#include <string>
#include <vector>
namespace comm {
enum class MigrationResult { SUCCESS, FAILURE, NOT_APPLIED };
typedef bool ShouldBeInTransaction;
typedef std::function<bool(sqlite3 *)> MigrateFunction;
typedef std::pair<MigrateFunction, ShouldBeInTransaction> SQLiteMigration;
typedef std::vector<std::pair<unsigned int, SQLiteMigration>> SQLiteMigrations;
kamilAuthorUnsubmitted
Done
Inline Actions

This is type I added

kamil: This is type I added
class SQLiteSchema {
private:
// Legacy migrations, modifications to this property should not be done.
// Those migrations are C++ specific and executed each time
// the `SQLiteQueryExecutor` object is created.
// Implementation in SQLiteSchemaLegacyMigrations.cpp
static SQLiteMigrations legacyMigrations;
// SQLite migrations that are currently in use, triggered from the JS level.
// Note that those migrations can be executed not only after application
// update but also after restoring an older backup version.
// Implementation in SQLiteSchemaMigrations.cpp
static SQLiteMigrations migrations;
// Running migrations and handling the database version.
static MigrationResult applyMigrationWithTransaction(
sqlite3 *db,
const MigrateFunction &migrate,
int index);
// Running migrations and handling the database version.
static MigrationResult applyMigrationWithoutTransaction(
sqlite3 *db,
const MigrateFunction &migrate,
int index);
// Creating database schema from scratch.
static bool createSchema(sqlite3 *db);
public:
// Utility method used to create a table during migration. This should be used
// only in the context of this class, but couldn't be private because
// migrations are defined as a standalone function, so it wouldn't be
// possible to implement this as private.
static bool
createTable(sqlite3 *db, std::string query, std::string tableName);
// Method to create a database schema from scratch and handle the version.
static bool setupDatabase(sqlite3 *db);
// Method to run legacy migrations.
static void legacyMigrate(sqlite3 *db);
};
} // namespace comm
View Options

native/cpp/CommonCpp/DatabaseManagers/SQLiteSchema.cpp

  • This file was added.
#include "SQLiteSchema.h"
#include "../NativeModules/PersistentStorageUtilities/MessageOperationsUtilities/MessageTypeEnum.h"
#include "Logger.h"
#include "SQLiteUtils.h"
#include <sqlite3.h>
#include <fstream>
#include <sstream>
#include <string>
namespace comm {
bool SQLiteSchema::createSchema(sqlite3 *db) {
char *error;
int sidebarSourceTypeInt = static_cast<int>(MessageType::SIDEBAR_SOURCE);
std::string sidebarSourceType = std::to_string(sidebarSourceTypeInt);
auto query =
"CREATE TABLE IF NOT EXISTS drafts ("
" key TEXT UNIQUE PRIMARY KEY NOT NULL,"
" text TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS messages ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" local_id TEXT,"
" thread TEXT NOT NULL,"
" user TEXT NOT NULL,"
" type INTEGER NOT NULL,"
" future_type INTEGER,"
" content TEXT,"
" time INTEGER NOT NULL,"
" target_message TEXT AS ("
" IIF("
" JSON_VALID(content),"
" COALESCE("
" JSON_EXTRACT(content, '$.targetMessageID'),"
" IIF("
" type = " +
sidebarSourceType +
" , JSON_EXTRACT(content, '$.id'),"
" NULL"
" )"
" ),"
" NULL"
" )"
" )"
");"
"CREATE TABLE IF NOT EXISTS olm_persist_account ("
" id INTEGER UNIQUE PRIMARY KEY NOT NULL,"
" account_data TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS olm_persist_sessions ("
" target_device_id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" session_data TEXT NOT NULL,"
" version INTEGER NOT NULL DEFAULT 1"
");"
"CREATE TABLE IF NOT EXISTS media ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" container TEXT NOT NULL,"
" thread TEXT NOT NULL,"
" uri TEXT NOT NULL,"
" type TEXT NOT NULL,"
" extras TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS threads ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" type INTEGER NOT NULL,"
" name TEXT,"
" description TEXT,"
" color TEXT NOT NULL,"
" creation_time BIGINT NOT NULL,"
" parent_thread_id TEXT,"
" containing_thread_id TEXT,"
" community TEXT,"
" members TEXT NOT NULL,"
" roles TEXT NOT NULL,"
" current_user TEXT NOT NULL,"
" source_message_id TEXT,"
" replies_count INTEGER NOT NULL,"
" avatar TEXT,"
" pinned_count INTEGER NOT NULL DEFAULT 0,"
" timestamps TEXT"
");"
"CREATE TABLE IF NOT EXISTS metadata ("
" name TEXT UNIQUE PRIMARY KEY NOT NULL,"
" data TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS message_store_threads ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" start_reached INTEGER NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS reports ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" report TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS persist_storage ("
" key TEXT UNIQUE PRIMARY KEY NOT NULL,"
" item TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS users ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" user_info TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS keyservers ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" keyserver_info TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS keyservers_synced ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" keyserver_info TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS communities ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" community_info TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS outbound_p2p_messages ("
" message_id TEXT NOT NULL,"
" device_id TEXT NOT NULL,"
" user_id TEXT NOT NULL,"
" timestamp BIGINT NOT NULL,"
" plaintext TEXT NOT NULL,"
" ciphertext TEXT NOT NULL,"
" status TEXT NOT NULL,"
" supports_auto_retry INTEGER DEFAULT 0,"
" PRIMARY KEY (message_id, device_id)"
");"
"CREATE TABLE IF NOT EXISTS integrity_store ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" thread_hash TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS synced_metadata ("
" name TEXT UNIQUE PRIMARY KEY NOT NULL,"
" data TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS aux_users ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" aux_user_info TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS thread_activity ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" thread_activity_store_entry TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS inbound_p2p_messages ("
" id INTEGER PRIMARY KEY,"
" message_id TEXT NOT NULL,"
" sender_device_id TEXT NOT NULL,"
" plaintext TEXT NOT NULL,"
" status TEXT NOT NULL,"
" sender_user_id TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS entries ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" entry TEXT NOT NULL"
");"
"CREATE TABLE IF NOT EXISTS message_store_local ("
" id TEXT UNIQUE PRIMARY KEY NOT NULL,"
" local_message_info TEXT NOT NULL"
");"
"CREATE VIRTUAL TABLE IF NOT EXISTS message_search USING fts5("
" original_message_id UNINDEXED,"
" message_id UNINDEXED,"
" processed_content,"
" tokenize = porter"
");"
"CREATE TABLE IF NOT EXISTS dm_operations ("
" id TEXT PRIMARY KEY,"
" type TEXT NOT NULL,"
" operation TEXT NOT NULL"
");"
"CREATE INDEX IF NOT EXISTS media_idx_container"
" ON media (container);"
"CREATE INDEX IF NOT EXISTS messages_idx_thread_time"
" ON messages (thread, time);"
"CREATE INDEX IF NOT EXISTS messages_idx_target_message_type_time"
" ON messages (target_message, type, time);"
"CREATE INDEX IF NOT EXISTS outbound_p2p_messages_idx_id_timestamp"
" ON outbound_p2p_messages (device_id, timestamp);"
"CREATE INDEX IF NOT EXISTS dm_operations_idx_type"
" ON dm_operations (type);";
sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error);
if (!error) {
return true;
}
std::ostringstream stringStream;
stringStream << "Error creating tables: " << error;
Logger::log(stringStream.str());
sqlite3_free(error);
return false;
}
bool SQLiteSchema::setupDatabase(sqlite3 *db) {
sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr);
auto db_version = SQLiteUtils::getDatabaseVersion(db);
auto latest_version = std::max(
SQLiteSchema::legacyMigrations.back().first,
SQLiteSchema::migrations.back().first);
kamilAuthorUnsubmitted
Done
Inline Actions

The only change is this line to get correct latest version

kamil: The only change is this line to get correct latest version
if (db_version == latest_version) {
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
return true;
}
if (db_version != 0 || !createSchema(db) ||
!SQLiteUtils::setDatabaseVersion(db, latest_version)) {
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
return false;
}
sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr);
return true;
}
bool SQLiteSchema::createTable(
sqlite3 *db,
std::string query,
std::string tableName) {
char *error;
sqlite3_exec(db, query.c_str(), nullptr, nullptr, &error);
if (!error) {
return true;
}
std::ostringstream stringStream;
stringStream << "Error creating '" << tableName << "' table: " << error;
Logger::log(stringStream.str());
sqlite3_free(error);
return false;
}
MigrationResult SQLiteSchema::applyMigrationWithTransaction(
sqlite3 *db,
const MigrateFunction &migrate,
int index) {
sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr);
auto db_version = SQLiteUtils::getDatabaseVersion(db);
if (index <= db_version) {
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
return MigrationResult::NOT_APPLIED;
}
auto rc = migrate(db);
if (!rc) {
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
return MigrationResult::FAILURE;
}
auto database_version_set = SQLiteUtils::setDatabaseVersion(db, index);
if (!database_version_set) {
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
return MigrationResult::FAILURE;
}
sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr);
return MigrationResult::SUCCESS;
}
MigrationResult SQLiteSchema::applyMigrationWithoutTransaction(
sqlite3 *db,
const MigrateFunction &migrate,
int index) {
auto db_version = SQLiteUtils::getDatabaseVersion(db);
if (index <= db_version) {
return MigrationResult::NOT_APPLIED;
}
auto rc = migrate(db);
if (!rc) {
return MigrationResult::FAILURE;
}
sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr);
auto inner_db_version = SQLiteUtils::getDatabaseVersion(db);
if (index <= inner_db_version) {
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
return MigrationResult::NOT_APPLIED;
}
auto database_version_set = SQLiteUtils::setDatabaseVersion(db, index);
if (!database_version_set) {
sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
return MigrationResult::FAILURE;
}
sqlite3_exec(db, "END TRANSACTION;", nullptr, nullptr, nullptr);
return MigrationResult::SUCCESS;
}
void SQLiteSchema::legacyMigrate(sqlite3 *db) {
for (const auto &[idx, migration] : legacyMigrations) {
const auto &[applyMigration, shouldBeInTransaction] = migration;
MigrationResult migrationResult;
if (shouldBeInTransaction) {
migrationResult = applyMigrationWithTransaction(db, applyMigration, idx);
} else {
migrationResult =
applyMigrationWithoutTransaction(db, applyMigration, idx);
}
if (migrationResult == MigrationResult::NOT_APPLIED) {
continue;
}
std::stringstream migration_msg;
if (migrationResult == MigrationResult::FAILURE) {
migration_msg << "migration " << idx << " failed." << std::endl;
Logger::log(migration_msg.str());
sqlite3_close(db);
throw std::runtime_error(migration_msg.str());
}
if (migrationResult == MigrationResult::SUCCESS) {
migration_msg << "migration " << idx << " succeeded." << std::endl;
Logger::log(migration_msg.str());
}
}
}
} // namespace comm
View Options

native/cpp/CommonCpp/DatabaseManagers/SQLiteSchemaLegacyMigrations.cpp

Loading...
View Options

native/cpp/CommonCpp/DatabaseManagers/SQLiteSchemaMigrations.cpp

Loading...
View Options

native/ios/Comm.xcodeproj/project.pbxproj

Loading...
View Options

web/cpp/SQLiteQueryExecutorBindings.cpp

Loading...
View Options

web/shared-worker/_generated/comm_query_executor.wasm

Loading...