sbx_sqlite: working on "Modify dbx_sqlite data schema #1949"

2 files changed, 63 insertions, 12 deletions

diff --git a/plugins/Dbx_sqlite/src/dbevents.cpp b/plugins/Dbx_sqlite/src/dbevents.cpp
index 52451b4327..9dbb87387d 100644..100755
--- 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
index 7809e18059..6bd71174d3 100644..100755
--- 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();