summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--plugins/Dbx_sqlite/src/dbcheck.cpp2
-rw-r--r--plugins/Dbx_sqlite/src/dbevents.cpp58
-rw-r--r--plugins/Dbx_sqlite/src/dbintf.cpp2
-rw-r--r--plugins/Dbx_sqlite/src/dbintf.h3
-rw-r--r--plugins/Dbx_sqlite/src/dbsettings.cpp44
5 files changed, 79 insertions, 30 deletions
diff --git a/plugins/Dbx_sqlite/src/dbcheck.cpp b/plugins/Dbx_sqlite/src/dbcheck.cpp
index 7019503eec..9a2a8d642b 100644
--- a/plugins/Dbx_sqlite/src/dbcheck.cpp
+++ b/plugins/Dbx_sqlite/src/dbcheck.cpp
@@ -36,7 +36,7 @@ int CDbxSQLite::CheckPhase2()
while (sqlite3_step(pQuery) == SQLITE_ROW) {
MEVENT hDbEvent = sqlite3_column_int(pQuery, 0);
MCONTACT hContact = sqlite3_column_int(pQuery, 1);
- uint32_t ts = sqlite3_column_int(pQuery, 2);
+ int64_t ts = sqlite3_column_int64(pQuery, 2);
DeleteEventSrt(hDbEvent, hContact, ts);
cb->pfnAddLogMessage(STATUS_ERROR, CMStringW(FORMAT, TranslateT("Orphaned sorting event with wrong event ID %d:%08X, deleting"), hContact, hDbEvent));
diff --git a/plugins/Dbx_sqlite/src/dbevents.cpp b/plugins/Dbx_sqlite/src/dbevents.cpp
index d8f5bc242e..8dfb608bce 100644
--- a/plugins/Dbx_sqlite/src/dbevents.cpp
+++ b/plugins/Dbx_sqlite/src/dbevents.cpp
@@ -1,13 +1,7 @@
#include "stdafx.h"
-//TODO: hide it inside cursor class
-static const char normal_order_query[] = "SELECT id FROM events_srt WHERE contact_id = ? ORDER BY timestamp, id;";
-static const char normal_order_pos_query[] = "SELECT id FROM events_srt WHERE contact_id = ? AND id >= ? ORDER BY timestamp, id;";
-
-static const char reverse_order_query[] = "SELECT id FROM events_srt WHERE contact_id = ? ORDER BY timestamp desc, id DESC;";
-static const char reverse_order_pos_query[] = "SELECT id FROM events_srt WHERE contact_id = ? AND id <= ? ORDER BY timestamp desc, id DESC;";
-
-static const char add_event_sort_query[] = "INSERT INTO events_srt(id, contact_id, timestamp) VALUES (?, ?, ?);";
+static const char normal_order_query[] = "SELECT id FROM events_srt WHERE contact_id = ? ORDER BY timestamp;";
+static const char reverse_order_query[] = "SELECT id FROM events_srt WHERE contact_id = ? ORDER BY timestamp DESC;";
void CDbxSQLite::InitEvents()
{
@@ -65,6 +59,27 @@ int CDbxSQLite::GetEventCount(MCONTACT hContact)
return (cc == nullptr) ? 0 : cc->m_count;
}
+/////////////////////////////////////////////////////////////////////////////////////////
+
+int CDbxSQLite::AddEventSrt(MEVENT hDbEvent, MCONTACT hContact, int64_t ts)
+{
+ int rc;
+
+ do {
+ auto *stmt = InitQuery("INSERT INTO events_srt(id, contact_id, timestamp) VALUES (?, ?, ?);", qEvAddSrt);
+ sqlite3_bind_int64(stmt, 1, hDbEvent);
+ sqlite3_bind_int64(stmt, 2, hContact);
+ sqlite3_bind_int64(stmt, 3, ts);
+ rc = sqlite3_step(stmt);
+ logError(rc, __FILE__, __LINE__);
+ sqlite3_reset(stmt);
+
+ ts++;
+ } while (rc != SQLITE_DONE);
+
+ return rc;
+}
+
MEVENT CDbxSQLite::AddEvent(MCONTACT hContact, const DBEVENTINFO *dbei)
{
if (dbei == nullptr)
@@ -138,23 +153,12 @@ MEVENT CDbxSQLite::AddEvent(MCONTACT hContact, const DBEVENTINFO *dbei)
MEVENT hDbEvent = sqlite3_last_insert_rowid(m_db);
- stmt = InitQuery(add_event_sort_query, qEvAddSrt);
- sqlite3_bind_int64(stmt, 1, hDbEvent);
- sqlite3_bind_int64(stmt, 2, cc->contactID);
- sqlite3_bind_int64(stmt, 3, tmp.timestamp);
- rc = sqlite3_step(stmt);
- logError(rc, __FILE__, __LINE__);
- sqlite3_reset(stmt);
+ int64_t tsSort = (int64_t)tmp.timestamp * 1000;
+ AddEventSrt(hDbEvent, cc->contactID, tsSort);
cc->m_count++;
if (ccSub != nullptr) {
- stmt = InitQuery(add_event_sort_query, qEvAddSrt);
- sqlite3_bind_int64(stmt, 1, hDbEvent);
- sqlite3_bind_int64(stmt, 2, ccSub->contactID);
- sqlite3_bind_int64(stmt, 3, tmp.timestamp);
- rc = sqlite3_step(stmt);
- logError(rc, __FILE__, __LINE__);
- sqlite3_reset(stmt); //is this necessary ?
+ AddEventSrt(hDbEvent, ccSub->contactID, tsSort);
ccSub->m_count++;
}
@@ -183,7 +187,7 @@ int CDbxSQLite::DeleteEventMain(MEVENT hDbEvent)
return rc;
}
-int CDbxSQLite::DeleteEventSrt(MEVENT hDbEvent, MCONTACT hContact, uint32_t ts)
+int CDbxSQLite::DeleteEventSrt(MEVENT hDbEvent, MCONTACT hContact, int64_t ts)
{
auto *stmt = InitQuery("DELETE FROM events_srt WHERE id = ? AND contact_id = ? AND timestamp = ?;", qEvDelSrt);
sqlite3_bind_int64(stmt, 1, hDbEvent);
@@ -586,7 +590,7 @@ MEVENT CDbxSQLite::FindNextEvent(MCONTACT hContact, MEVENT hDbEvent)
sqlite3_reset(fwd.cur);
fwd.hContact = hContact;
- fwd.cur = InitQuery("SELECT id FROM events_srt WHERE contact_id = ? AND id > ? ORDER BY timestamp, id;", qEvFindNext);
+ fwd.cur = InitQuery("SELECT id FROM events_srt WHERE contact_id = ? AND timestamp > (SELECT timestamp FROM events_srt WHERE id=?) ORDER BY timestamp;", qEvFindNext);
sqlite3_bind_int64(fwd.cur, 1, hContact);
sqlite3_bind_int64(fwd.cur, 2, hDbEvent);
}
@@ -642,7 +646,7 @@ MEVENT CDbxSQLite::FindPrevEvent(MCONTACT hContact, MEVENT hDbEvent)
sqlite3_reset(back.cur);
back.hContact = hContact;
- back.cur = InitQuery("SELECT id FROM events_srt WHERE contact_id = ? AND id < ? ORDER BY timestamp desc, id DESC;", qEvFindPrev);
+ back.cur = InitQuery("SELECT id FROM events_srt WHERE contact_id = ? AND timestamp < (SELECT timestamp FROM events_srt WHERE id=?) ORDER BY timestamp DESC;", qEvFindPrev);
sqlite3_bind_int64(back.cur, 1, hContact);
sqlite3_bind_int64(back.cur, 2, hDbEvent);
}
@@ -757,13 +761,13 @@ CDbxSQLiteEventCursor::CDbxSQLiteEventCursor(MCONTACT _1, sqlite3 *_db, MEVENT h
if (!hDbEvent)
sqlite3_prepare_v2(m_db, reverse_order_query, -1, &cursor, nullptr);
else
- sqlite3_prepare_v2(m_db, reverse_order_pos_query, -1, &cursor, nullptr);
+ sqlite3_prepare_v2(m_db, "SELECT id FROM events_srt WHERE contact_id = ? AND timestamp <= (SELECT timestamp FROM events_srt WHERE id=?) ORDER BY timestamp DESC;", -1, &cursor, nullptr);
}
else {
if (!hDbEvent)
sqlite3_prepare_v2(m_db, normal_order_query, -1, &cursor, nullptr);
else
- sqlite3_prepare_v2(m_db, normal_order_pos_query, -1, &cursor, nullptr);
+ sqlite3_prepare_v2(m_db, "SELECT id FROM events_srt WHERE contact_id = ? AND timestamp >= (SELECT timestamp FROM events_srt WHERE id=?) ORDER BY timestamp;", -1, &cursor, nullptr);
}
sqlite3_bind_int64(cursor, 1, hContact);
if (hDbEvent)
diff --git a/plugins/Dbx_sqlite/src/dbintf.cpp b/plugins/Dbx_sqlite/src/dbintf.cpp
index b983184d32..a7596ec9bb 100644
--- a/plugins/Dbx_sqlite/src/dbintf.cpp
+++ b/plugins/Dbx_sqlite/src/dbintf.cpp
@@ -58,7 +58,7 @@ int CDbxSQLite::Create()
rc = sqlite3_exec(m_db, "CREATE INDEX idx_events_isread ON events(contact_id, is_read, timestamp);", nullptr, nullptr, nullptr);
logError(rc, __FILE__, __LINE__);
- rc = sqlite3_exec(m_db, "CREATE TABLE events_srt (id INTEGER NOT NULL, contact_id INTEGER NOT NULL, timestamp INTEGER, PRIMARY KEY(contact_id, timestamp, id));", nullptr, nullptr, nullptr);
+ rc = sqlite3_exec(m_db, "CREATE TABLE events_srt (id INTEGER NOT NULL, contact_id INTEGER NOT NULL, timestamp INTEGER, PRIMARY KEY(contact_id, timestamp));", nullptr, nullptr, nullptr);
logError(rc, __FILE__, __LINE__);
rc = sqlite3_exec(m_db, "CREATE TABLE settings (contact_id INTEGER NOT NULL, module TEXT NOT NULL, setting TEXT NOT NULL, type INTEGER NOT NULL, value NOT NULL,"
diff --git a/plugins/Dbx_sqlite/src/dbintf.h b/plugins/Dbx_sqlite/src/dbintf.h
index 7b1d82550b..0872fa1096 100644
--- a/plugins/Dbx_sqlite/src/dbintf.h
+++ b/plugins/Dbx_sqlite/src/dbintf.h
@@ -82,8 +82,9 @@ class CDbxSQLite : public MDatabaseCommon, public MIDatabaseChecker, public MZer
CQuery qEvAdd, qEvDel, qEvEdit1, qEvEdit2, qEvBlobSize, qEvGet, qEvGetFlags, qEvSetFlags, qEvGetContact, qEvGetContact2;
CQuery qEvFindFirst, qEvFindNext, qEvFindLast, qEvFindPrev, qEvFindUnread, qEvAddSrt, qEvDelSrt, qEvMetaSplit, qEvMetaMerge;
CQuery qEvGetById, qEvUpdateId, qEvSetJson;
+ int AddEventSrt(MEVENT, MCONTACT, int64_t ts);
int DeleteEventMain(MEVENT);
- int DeleteEventSrt(MEVENT, MCONTACT, uint32_t);
+ int DeleteEventSrt(MEVENT, MCONTACT, int64_t ts);
// settings
void InitSettings();
diff --git a/plugins/Dbx_sqlite/src/dbsettings.cpp b/plugins/Dbx_sqlite/src/dbsettings.cpp
index d9e33abb91..3bce34de20 100644
--- a/plugins/Dbx_sqlite/src/dbsettings.cpp
+++ b/plugins/Dbx_sqlite/src/dbsettings.cpp
@@ -81,6 +81,50 @@ void CDbxSQLite::InitSettings()
logError(rc, __FILE__, __LINE__);
dbv.bVal = 2;
+ }
+
+ if (dbv.bVal < 3) {
+ int rc = sqlite3_exec(m_db, "UPDATE events_srt SET timestamp=timestamp*1000;", 0, 0, 0);
+ logError(rc, __FILE__, __LINE__);
+
+ CQuery updateSql;
+ sqlite3_stmt *updateStmt = InitQuery("update events_srt set timestamp=timestamp+? where id in (select max(id) from events_srt where contact_id=? and timestamp=?);", updateSql);
+
+ sqlite3_stmt *pQuery;
+ rc = sqlite3_prepare_v2(m_db, "SELECT contact_id, timestamp, count(*) AS boo FROM events_srt GROUP BY contact_id, timestamp HAVING boo > 1;", -1, &pQuery, nullptr);
+ logError(rc, __FILE__, __LINE__);
+
+ while (sqlite3_step(pQuery) == SQLITE_ROW) {
+ MCONTACT hContact = sqlite3_column_int(pQuery, 0);
+ int64_t ts = sqlite3_column_int64(pQuery, 1);
+ int iCount = sqlite3_column_int(pQuery, 2);
+
+ for (int i = iCount - 1; i > 0; i--) {
+ sqlite3_bind_int64(updateStmt, 1, i);
+ sqlite3_bind_int64(updateStmt, 2, hContact);
+ sqlite3_bind_int64(updateStmt, 3, ts);
+ rc = sqlite3_step(updateStmt);
+ logError(rc, __FILE__, __LINE__);
+ sqlite3_reset(updateStmt);
+ }
+ }
+
+ rc = sqlite3_exec(m_db, "CREATE TABLE tmp(id INTEGER NOT NULL, contact_id INTEGER NOT NULL, timestamp INTEGER, PRIMARY KEY(contact_id, timestamp));", 0, 0, 0);
+ logError(rc, __FILE__, __LINE__);
+
+ rc = sqlite3_exec(m_db, "INSERT INTO tmp SELECT * FROM events_srt;", 0, 0, 0);
+ logError(rc, __FILE__, __LINE__);
+
+ rc = sqlite3_exec(m_db, "DROP TABLE events_srt;", 0, 0, 0);
+ logError(rc, __FILE__, __LINE__);
+
+ rc = sqlite3_exec(m_db, "ALTER TABLE tmp RENAME TO events_srt;", 0, 0, 0);
+ logError(rc, __FILE__, __LINE__);
+
+ rc = sqlite3_exec(m_db, "VACUUM;", 0, 0, 0);
+ logError(rc, __FILE__, __LINE__);
+
+ dbv.bVal = 3;
WriteContactSetting(0, "Compatibility", "Sqlite", &dbv);
}
}
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-28 08:33:22 +0000