From ac0cd7a6c7210efa43cf6823e9b68f98ebf13e79 Mon Sep 17 00:00:00 2001
From: George Hazan <ghazan@miranda.im>
Date: Thu, 7 Jan 2021 14:32:43 +0300
Subject: Dbx_sqlite:

- fix: records from events_srt should be also removed when a contact is deleted;
- fix: last unread event should be stored in cache when new ts is larger, not smaller;
- fix: history length should be calculated using events_srt either;
- SQL syntax fixes;
- code cleaning
---
 plugins/Dbx_sqlite/src/dbcontacts.cpp | 85 ++++++++++++++++++-----------------
 1 file changed, 44 insertions(+), 41 deletions(-)

(limited to 'plugins/Dbx_sqlite/src/dbcontacts.cpp')

diff --git a/plugins/Dbx_sqlite/src/dbcontacts.cpp b/plugins/Dbx_sqlite/src/dbcontacts.cpp
index 0cf3a85d57..ca3680c159 100755
--- a/plugins/Dbx_sqlite/src/dbcontacts.cpp
+++ b/plugins/Dbx_sqlite/src/dbcontacts.cpp
@@ -6,15 +6,17 @@ enum {
 	SQL_CTC_STMT_DELETE,
 	SQL_CTC_STMT_DELETESETTINGS,
 	SQL_CTC_STMT_DELETEEVENTS,
+	SQL_CTC_STMT_DELETEEVENTS_SRT,
 	SQL_CTC_STMT_NUM
 };
 
 static char *ctc_stmts[SQL_CTC_STMT_NUM] = {
-	"select count(1) from contacts limit 1;",
-	"insert into contacts values (null);",
-	"delete from contacts where id = ?;",
-	"delete from settings where contact_id = ?;",
-	"delete from events where contact_id = ?;"
+	"SELECT COUNT(1) FROM contacts LIMIT 1;",
+	"INSERT INTO contacts VALUES (null);",
+	"DELETE FROM contacts WHERE id = ?;",
+	"DELETE FROM settings WHERE contact_id = ?;",
+	"DELETE FROM events WHERE contact_id = ?;",
+	"DELETE FROM events_srt WHERE contact_id = ?;"
 };
 
 static sqlite3_stmt *ctc_stmts_prep[SQL_CTC_STMT_NUM] = { 0 };
@@ -25,7 +27,7 @@ void CDbxSQLite::InitContacts()
 		sqlite3_prepare_v3(m_db, ctc_stmts[i], -1, SQLITE_PREPARE_PERSISTENT, &ctc_stmts_prep[i], nullptr);
 
 	sqlite3_stmt *stmt = nullptr;
-	sqlite3_prepare_v2(m_db, "select contacts.id, count(events.id) from contacts left join events on events.contact_id = contacts.id group by contacts.id;", -1, &stmt, nullptr);
+	sqlite3_prepare_v2(m_db, "SELECT contacts.id, COUNT(es.id) FROM contacts LEFT JOIN events_srt es ON es.contact_id = contacts.id GROUP BY contacts.id;", -1, &stmt, nullptr);
 	int rc = 0;
 	while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
 		MCONTACT hContact = sqlite3_column_int64(stmt, 0);
@@ -96,37 +98,44 @@ LONG CDbxSQLite::DeleteContact(MCONTACT hContact)
 	if (hContact == 0)
 		return 1;
 
-	{
-		mir_cslock lock(m_csDbAccess);
+	mir_cslockfull lock(m_csDbAccess);
 
-		sqlite3_stmt *stmt = ctc_stmts_prep[SQL_CTC_STMT_DELETEEVENTS];
-		sqlite3_bind_int64(stmt, 1, hContact);
-		int rc = sqlite3_step(stmt);
-		assert(rc == SQLITE_DONE);
-		sqlite3_reset(stmt);
-		if (rc != SQLITE_DONE)
-			return 1;
+	sqlite3_stmt *stmt = ctc_stmts_prep[SQL_CTC_STMT_DELETEEVENTS];
+	sqlite3_bind_int64(stmt, 1, hContact);
+	int rc = sqlite3_step(stmt);
+	assert(rc == SQLITE_DONE);
+	sqlite3_reset(stmt);
+	if (rc != SQLITE_DONE)
+		return 1;
 
-		stmt = ctc_stmts_prep[SQL_CTC_STMT_DELETESETTINGS];
-		sqlite3_bind_int64(stmt, 1, hContact);
-		rc = sqlite3_step(stmt);
-		assert(rc == SQLITE_DONE);
-		sqlite3_reset(stmt);
-		if (rc != SQLITE_DONE)
-			return 1;
+	stmt = ctc_stmts_prep[SQL_CTC_STMT_DELETEEVENTS_SRT];
+	sqlite3_bind_int64(stmt, 1, hContact);
+	int rc = sqlite3_step(stmt);
+	assert(rc == SQLITE_DONE);
+	sqlite3_reset(stmt);
+	if (rc != SQLITE_DONE)
+		return 1;
 
-		stmt = ctc_stmts_prep[SQL_CTC_STMT_DELETE];
-		sqlite3_bind_int64(stmt, 1, hContact);
-		rc = sqlite3_step(stmt);
-		assert(rc == SQLITE_DONE);
-		sqlite3_reset(stmt);
-		if (rc != SQLITE_DONE)
-			return 1;
-	}
+	stmt = ctc_stmts_prep[SQL_CTC_STMT_DELETESETTINGS];
+	sqlite3_bind_int64(stmt, 1, hContact);
+	rc = sqlite3_step(stmt);
+	assert(rc == SQLITE_DONE);
+	sqlite3_reset(stmt);
+	if (rc != SQLITE_DONE)
+		return 1;
+
+	stmt = ctc_stmts_prep[SQL_CTC_STMT_DELETE];
+	sqlite3_bind_int64(stmt, 1, hContact);
+	rc = sqlite3_step(stmt);
+	assert(rc == SQLITE_DONE);
+	sqlite3_reset(stmt);
+	if (rc != SQLITE_DONE)
+		return 1;
 
 	m_cache->FreeCachedContact(hContact);
-	NotifyEventHooks(g_hevContactDeleted, hContact);
 
+	lock.unlock();
+	NotifyEventHooks(g_hevContactDeleted, hContact);
 	return 0;
 }
 
@@ -143,17 +152,11 @@ LONG CDbxSQLite::GetContactSize(void)
 
 /////////////////////////////////////
 
-bool DBCachedContact::HasCount() const
-{
-	return m_count > -1;
-}
-
 void DBCachedContact::AddEvent(MEVENT hDbEvent, uint32_t timestamp, bool unread)
 {
-	m_count = HasCount()
-		? m_count + 1
-		: 1;
-	if (unread && m_unreadTimestamp > timestamp) {
+	m_count = HasCount() ? m_count + 1 : 1;
+	
+	if (unread && timestamp > m_unreadTimestamp) {
 		m_unread = hDbEvent;
 		m_unreadTimestamp = timestamp;
 	}
@@ -165,7 +168,7 @@ void DBCachedContact::EditEvent(MEVENT hDbEvent, uint32_t timestamp, bool unread
 		m_unread = 0;
 		m_unreadTimestamp = 0;
 	}
-	else if (unread && m_unreadTimestamp > timestamp) {
+	else if (unread && timestamp > m_unreadTimestamp) {
 		m_unread = hDbEvent;
 		m_unreadTimestamp = timestamp;
 	}
-- 
cgit v1.2.3