From 442980bfbf83cd423b42811e4da0d5b651005781 Mon Sep 17 00:00:00 2001 From: George Hazan Date: Sun, 1 Oct 2023 20:32:37 +0300 Subject: fixes #3670 (Long and incorrect loading of messaging window) --- plugins/Dbx_sqlite/src/dbcheck.cpp | 2 +- plugins/Dbx_sqlite/src/dbevents.cpp | 58 +++++++++++++++++++---------------- plugins/Dbx_sqlite/src/dbintf.cpp | 2 +- plugins/Dbx_sqlite/src/dbintf.h | 3 +- plugins/Dbx_sqlite/src/dbsettings.cpp | 44 ++++++++++++++++++++++++++ 5 files changed, 79 insertions(+), 30 deletions(-) (limited to 'plugins/Dbx_sqlite/src') 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); } } -- cgit v1.2.3