From 7133df1548ca5824fe336d6aae20028f1ca2a758 Mon Sep 17 00:00:00 2001 From: Gluzskiy Alexandr Date: Tue, 14 May 2019 16:09:20 +0300 Subject: sbx_sqlite: working on "Modify dbx_sqlite data schema #1949" --- plugins/Dbx_sqlite/src/dbevents.cpp | 2 + plugins/Dbx_sqlite/src/dbintf.cpp | 73 +++++++++++++++++++++++++++++++------ 2 files changed, 63 insertions(+), 12 deletions(-) mode change 100644 => 100755 plugins/Dbx_sqlite/src/dbevents.cpp mode change 100644 => 100755 plugins/Dbx_sqlite/src/dbintf.cpp (limited to 'plugins/Dbx_sqlite/src') diff --git a/plugins/Dbx_sqlite/src/dbevents.cpp b/plugins/Dbx_sqlite/src/dbevents.cpp old mode 100644 new mode 100755 index 52451b4327..9dbb87387d --- a/plugins/Dbx_sqlite/src/dbevents.cpp +++ b/plugins/Dbx_sqlite/src/dbevents.cpp @@ -17,6 +17,7 @@ enum { SQL_EVT_STMT_FINDPREV, SQL_EVT_STMT_GETIDBYSRVID, SQL_EVT_STMT_SETSRVID, + SQL_EVT_STMT_ADDEVENT_SRT, SQL_EVT_STMT_NUM }; @@ -37,6 +38,7 @@ static char *evt_stmts[SQL_EVT_STMT_NUM] = { "select id, timestamp from events where contact_id = ?1 and id <> ?2 and timestamp < (select timestamp from events where contact_id = ?1 and id = ?2 limit 1) order by timestamp desc, id desc limit 1;", "select id, timestamp from events where module = ? and server_id = ? limit 1;", "update events set server_id = ? where id = ?;", + "insert into events_srt(id, contact_id, timestamp) values (?, ?, ?);", }; static sqlite3_stmt *evt_stmts_prep[SQL_EVT_STMT_NUM] = { 0 }; diff --git a/plugins/Dbx_sqlite/src/dbintf.cpp b/plugins/Dbx_sqlite/src/dbintf.cpp old mode 100644 new mode 100755 index 7809e18059..6bd71174d3 --- a/plugins/Dbx_sqlite/src/dbintf.cpp +++ b/plugins/Dbx_sqlite/src/dbintf.cpp @@ -27,12 +27,44 @@ int CDbxSQLite::Create(const wchar_t *profile) if (rc != SQLITE_OK) return 1; - sqlite3_exec(database, "create table contacts (id integer not null primary key autoincrement);", nullptr, nullptr, nullptr); - sqlite3_exec(database, "create table events (id integer not null primary key autoincrement, contact_id integer not null, module text not null, timestamp integer not null, type integer not null, flags integer not null, size integer not null, data blob, server_id text);", nullptr, nullptr, nullptr); - sqlite3_exec(database, "create index idx_events_contactid_timestamp on events(contact_id, timestamp);", nullptr, nullptr, nullptr); - sqlite3_exec(database, "create index idx_events_module_serverid on events(module, server_id);", nullptr, nullptr, nullptr); - sqlite3_exec(database, "create table settings (contact_id integer not null, module text not null, setting text not null, type integer not null, value any, primary key(contact_id, module, setting)) without rowid;", nullptr, nullptr, nullptr); - sqlite3_exec(database, "create index idx_settings_module on settings(module);", nullptr, nullptr, nullptr); + rc = sqlite3_exec(database, "create table contacts (id integer not null primary key autoincrement);", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } + rc = sqlite3_exec(database, "create table events (id integer not null primary key autoincrement, contact_id integer not null, module text not null," + "timestamp integer not null, type integer not null, flags integer not null, size integer not null, data blob, server_id text);", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } + rc = sqlite3_exec(database, "create index idx_events_contactid_timestamp on events(contact_id, timestamp);", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } + rc = sqlite3_exec(database, "create index idx_events_module_serverid on events(module, server_id);", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } + rc = sqlite3_exec(database, "create table events_srt (id integer not null, contact_id integer not null, timestamp integer, primary key (contact_id, timestamp, id));", + nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } + rc = sqlite3_exec(database, "create table settings (contact_id integer not null, module text not null, setting text not null, type integer not null, value any," + "primary key(contact_id, module, setting)) without rowid;", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } + rc = sqlite3_exec(database, "create index idx_settings_module on settings(module);", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } sqlite3_close(database); @@ -81,12 +113,29 @@ MDatabaseCommon* CDbxSQLite::Load(const wchar_t *profile, int readonly) if (rc != SQLITE_OK) return nullptr; - sqlite3_exec(database, "begin transaction;", nullptr, nullptr, nullptr); - sqlite3_exec(database, "pragma locking_mode = EXCLUSIVE;", nullptr, nullptr, nullptr); - sqlite3_exec(database, "pragma synchronous = NORMAL;", nullptr, nullptr, nullptr); - sqlite3_exec(database, "pragma foreign_keys = OFF;", nullptr, nullptr, nullptr); - sqlite3_exec(database, "pragma journal_mode = OFF;", nullptr, nullptr, nullptr); - sqlite3_exec(database, "commit;", nullptr, nullptr, nullptr); + rc = sqlite3_exec(database, "begin transaction;", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) + { + //TODO: handle error + } + rc = sqlite3_exec(database, "pragma locking_mode = EXCLUSIVE;", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) { + // TODO: handle error + } + rc = sqlite3_exec(database, "pragma synchronous = NORMAL;", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) { + // TODO: handle error + } + sqlite3_exec(database, "pragma foreign_keys = OFF;", nullptr, nullptr, nullptr); + rc = sqlite3_exec(database, "pragma journal_mode = OFF;", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) { + // TODO: handle error + } + rc = sqlite3_exec(database, "commit;", nullptr, nullptr, nullptr); + if (rc != SQLITE_OK) { + // TODO: handle error + } + CDbxSQLite *db = new CDbxSQLite(database); db->InitSettings(); -- cgit v1.2.3