fixes #3670 (Long and incorrect loading of messaging window)

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);

 	}

 }