dbx_sqlite: implemented database checker + version bump

8 files changed, 187 insertions, 31 deletions

diff --git a/plugins/Dbx_sqlite/dbx_sqlite.vcxproj b/plugins/Dbx_sqlite/dbx_sqlite.vcxproj
index 36e99bb7a3..be896cf8bd 100644
--- a/plugins/Dbx_sqlite/dbx_sqlite.vcxproj
+++ b/plugins/Dbx_sqlite/dbx_sqlite.vcxproj
@@ -31,6 +31,7 @@
     <Import Project="$(ProjectDir)..\..\build\vc.common\plugin.props" />
   </ImportGroup>
   <ItemGroup>
+    <ClCompile Include="src\dbcheck.cpp" />
     <ClCompile Include="src\dbcontacts.cpp" />
     <ClCompile Include="src\dbcrypt.cpp" />
     <ClCompile Include="src\dbevents.cpp" />
diff --git a/plugins/Dbx_sqlite/dbx_sqlite.vcxproj.filters b/plugins/Dbx_sqlite/dbx_sqlite.vcxproj.filters
index f2b029085d..1425d52383 100644
--- a/plugins/Dbx_sqlite/dbx_sqlite.vcxproj.filters
+++ b/plugins/Dbx_sqlite/dbx_sqlite.vcxproj.filters
@@ -26,6 +26,9 @@
     <ClCompile Include="src\utils.cpp">
       <Filter>Source Files</Filter>
     </ClCompile>
+    <ClCompile Include="src\dbcheck.cpp">
+      <Filter>Source Files</Filter>
+    </ClCompile>
   </ItemGroup>
   <ItemGroup>
     <ClInclude Include="src\dbintf.h">
diff --git a/plugins/Dbx_sqlite/src/dbcheck.cpp b/plugins/Dbx_sqlite/src/dbcheck.cpp
new file mode 100644
index 0000000000..9d444c2b8d
--- /dev/null
+++ b/plugins/Dbx_sqlite/src/dbcheck.cpp
@@ -0,0 +1,109 @@
+#include "stdafx.h"
+
+/////////////////////////////////////////////////////////////////////////////////////////
+// first try to find events with wrong contact ids
+
+int CDbxSQLite::CheckPhase1()
+{
+	sqlite3_stmt *pQuery;
+	int rc = sqlite3_prepare_v2(m_db, "SELECT id, contact_id FROM events WHERE contact_id <> 0 AND contact_id NOT IN (SELECT id FROM contacts)", -1, &pQuery, nullptr);
+	logError(rc, __FILE__, __LINE__);
+	if (rc)
+		return rc;
+
+	while (sqlite3_step(pQuery) == SQLITE_ROW) {
+		MEVENT hDbEvent = sqlite3_column_int(pQuery, 0);
+		MCONTACT hContact = sqlite3_column_int(pQuery, 1);
+		cb->pfnAddLogMessage(STATUS_ERROR, CMStringW(FORMAT, TranslateT("Orphaned event with wrong contact ID %d, deleting"), hContact));
+		DeleteEvent(hDbEvent);
+	}
+	
+	sqlite3_finalize(pQuery);
+	return 0;
+}
+
+/////////////////////////////////////////////////////////////////////////////////////////
+// then we're wiping orphaned records from Events_srt which have no parent record in Events
+
+int CDbxSQLite::CheckPhase2()
+{
+	sqlite3_stmt *pQuery;
+	int rc = sqlite3_prepare_v2(m_db, "SELECT id, contact_id FROM events_srt WHERE id NOT IN (SELECT id FROM events)", -1, &pQuery, nullptr);
+	logError(rc, __FILE__, __LINE__);
+	if (rc)
+		return rc;
+
+	while (sqlite3_step(pQuery) == SQLITE_ROW) {
+		MEVENT hDbEvent = sqlite3_column_int(pQuery, 0);
+		MCONTACT hContact = sqlite3_column_int(pQuery, 1);
+
+		DeleteEventSrt(hDbEvent);
+		cb->pfnAddLogMessage(STATUS_ERROR, CMStringW(FORMAT, TranslateT("Orphaned sorting event with wrong event ID %d:%08X, deleting"), hContact, hDbEvent));
+	}
+
+	sqlite3_finalize(pQuery);
+	return 0;
+}
+
+/////////////////////////////////////////////////////////////////////////////////////////
+// now try to find orphans in backward direction: from Events_srt to Events
+
+int CDbxSQLite::CheckPhase3()
+{
+	sqlite3_stmt *pQuery;
+	int rc = sqlite3_prepare_v2(m_db, "SELECT id, contact_id FROM events WHERE id NOT IN (SELECT id FROM events_srt)", -1, &pQuery, nullptr);
+	logError(rc, __FILE__, __LINE__);
+	if (rc)
+		return rc;
+
+	while (sqlite3_step(pQuery) == SQLITE_ROW) {
+		MEVENT hDbEvent = sqlite3_column_int(pQuery, 0);
+		MCONTACT hContact = sqlite3_column_int(pQuery, 1);
+
+		DeleteEventMain(hDbEvent);
+		cb->pfnAddLogMessage(STATUS_ERROR, CMStringW(FORMAT, TranslateT("Orphaned event with wrong event ID %d:%08X, deleting"), hContact, hDbEvent));
+	}
+
+	sqlite3_finalize(pQuery);
+	return 0;
+}
+
+/////////////////////////////////////////////////////////////////////////////////////////
+// remove settings with wrong contact ids
+
+int CDbxSQLite::CheckPhase4()
+{
+	sqlite3_stmt *pQuery;
+	int rc = sqlite3_prepare_v2(m_db, "SELECT contact_id,module,setting FROM settings WHERE contact_id <> 0 AND contact_id NOT IN (SELECT id FROM contacts)", -1, &pQuery, nullptr);
+	logError(rc, __FILE__, __LINE__);
+	if (rc)
+		return rc;
+
+	while (sqlite3_step(pQuery) == SQLITE_ROW) {
+		MCONTACT hContact = sqlite3_column_int(pQuery, 0);
+		auto *szModule = (const char *)sqlite3_column_text(pQuery, 1);
+		auto *szSetting = (const char *)sqlite3_column_text(pQuery, 1);
+
+		cb->pfnAddLogMessage(STATUS_ERROR, CMStringW(FORMAT, TranslateT("Orphaned setting [%s:%s] with wrong contact ID %d, deleting"), szModule, szSetting, hContact));
+		DeleteContactSettingWorker(hContact, szModule, szSetting);
+	}
+
+	sqlite3_finalize(pQuery);
+	return 0;
+}
+
+///////////////////////////////////////////////////////////////////////////////
+// MIDatabaseChecker
+
+int CDbxSQLite::CheckDb(int phase)
+{
+	switch (phase) {
+	case 0: return CheckPhase1();
+	case 1: return CheckPhase2();
+	case 2: return CheckPhase3();
+	case 3: return CheckPhase4();
+	}
+
+	DBFlush(true);
+	return ERROR_OUT_OF_PAPER;
+}
diff --git a/plugins/Dbx_sqlite/src/dbevents.cpp b/plugins/Dbx_sqlite/src/dbevents.cpp
index b277bbbfbc..cbfa67485a 100755
--- a/plugins/Dbx_sqlite/src/dbevents.cpp
+++ b/plugins/Dbx_sqlite/src/dbevents.cpp
@@ -149,6 +149,28 @@ MEVENT CDbxSQLite::AddEvent(MCONTACT hContact, const DBEVENTINFO *dbei)
 	return hDbEvent;
 }
 
+/////////////////////////////////////////////////////////////////////////////////////////
+
+int CDbxSQLite::DeleteEventMain(MEVENT hDbEvent)
+{
+	auto *stmt = InitQuery("DELETE FROM events WHERE id = ?;", qEvDel);
+	sqlite3_bind_int64(stmt, 1, hDbEvent);
+	int rc = sqlite3_step(stmt);
+	logError(rc, __FILE__, __LINE__);
+	sqlite3_reset(stmt);
+	return rc;
+}
+
+int CDbxSQLite::DeleteEventSrt(MEVENT hDbEvent)
+{
+	auto *stmt = InitQuery("DELETE FROM events_srt WHERE id = ?;", qEvDelSrt);
+	sqlite3_bind_int64(stmt, 1, hDbEvent);
+	int rc = sqlite3_step(stmt);
+	logError(rc, __FILE__, __LINE__);
+	sqlite3_reset(stmt);
+	return rc;
+}
+
 BOOL CDbxSQLite::DeleteEvent(MEVENT hDbEvent)
 {
 	if (hDbEvent == 0)
@@ -160,19 +182,11 @@ BOOL CDbxSQLite::DeleteEvent(MEVENT hDbEvent)
 		return 1;
 
 	mir_cslockfull lock(m_csDbAccess);
-	sqlite3_stmt *stmt = InitQuery("DELETE FROM events WHERE id = ?;", qEvDel);
-	sqlite3_bind_int64(stmt, 1, hDbEvent);
-	int rc = sqlite3_step(stmt);
-	logError(rc, __FILE__, __LINE__);
-	sqlite3_reset(stmt);
+	int rc = DeleteEventMain(hDbEvent);
 	if (rc != SQLITE_DONE)
 		return 1;
 
-	stmt = InitQuery("DELETE FROM events_srt WHERE id = ?;", qEvDelSrt);
-	sqlite3_bind_int64(stmt, 1, hDbEvent);
-	rc = sqlite3_step(stmt);
-	logError(rc, __FILE__, __LINE__);
-	sqlite3_reset(stmt);
+	rc = DeleteEventSrt(hDbEvent);
 	if (rc != SQLITE_DONE)
 		return 1;
 
@@ -187,6 +201,8 @@ BOOL CDbxSQLite::DeleteEvent(MEVENT hDbEvent)
 	return 0;
 }
 
+/////////////////////////////////////////////////////////////////////////////////////////
+
 BOOL CDbxSQLite::EditEvent(MCONTACT hContact, MEVENT hDbEvent, const DBEVENTINFO *dbei)
 {
 	if (dbei == nullptr)
diff --git a/plugins/Dbx_sqlite/src/dbintf.h b/plugins/Dbx_sqlite/src/dbintf.h
index 6e772eb6ef..c9425d8c67 100755
--- a/plugins/Dbx_sqlite/src/dbintf.h
+++ b/plugins/Dbx_sqlite/src/dbintf.h
@@ -36,12 +36,13 @@ struct CDbxSQLiteEventCursor : public DB::EventCursor
 	CDbxSQLiteEventCursor(MCONTACT _1, sqlite3* m_db, MEVENT hDbEvent, bool reverse = false);
 	~CDbxSQLiteEventCursor() override;
 	MEVENT FetchNext() override;
+
 private:
-	sqlite3* m_db;
-	sqlite3_stmt* cursor;
+	sqlite3 *m_db;
+	sqlite3_stmt *cursor;
 };
 
-class CDbxSQLite : public MDatabaseCommon, public MZeroedObject
+class CDbxSQLite : public MDatabaseCommon, public MIDatabaseChecker, public MZeroedObject
 {
 	ptrW m_wszFileName;
 	sqlite3 *m_db = nullptr;
@@ -95,10 +96,13 @@ class CDbxSQLite : public MDatabaseCommon, public MZeroedObject
 	void UninitEvents();
 	CQuery qEvCount, qEvAdd, qEvDel, qEvEdit, qEvBlobSize, qEvGet, qEvGetFlags, qEvSetFlags, qEvGetContact;
 	CQuery qEvFindFirst, qEvFindNext, qEvFindLast, qEvFindPrev, qEvFindUnread, qEvGetById, qEvAddSrt, qEvDelSrt, qEvMetaSplit, qEvMetaMerge;
+	int DeleteEventMain(MEVENT);
+	int DeleteEventSrt(MEVENT);
 
 	// settings
 	void InitSettings();
 	CQuery qSettModules, qSettWrite, qSettDel, qSettEnum, qSettChanges;
+	int DeleteContactSettingWorker(MCONTACT contactID, LPCSTR szModule, LPCSTR szSetting);
 
 	void DBFlush(bool bForce = false);
 	sqlite3_stmt* InitQuery(const char *szQuery, CQuery &stmt);
@@ -162,4 +166,28 @@ public:
 
 	STDMETHODIMP_(DB::EventCursor*) EventCursor(MCONTACT hContact, MEVENT hDbEvent) override;
 	STDMETHODIMP_(DB::EventCursor*) EventCursorRev(MCONTACT hContact, MEVENT hDbEvent) override;
+
+	////////////////////////////////////////////////////////////////////////////////////////
+	// database checker interface implementation
+
+protected:
+	STDMETHODIMP_(MIDatabaseChecker *) GetChecker() override
+	{	return this;
+	}
+
+	STDMETHODIMP_(BOOL) Start(DBCHeckCallback *callback) override
+	{
+		cb = callback;
+		return ERROR_SUCCESS;
+	}
+	
+	STDMETHODIMP_(BOOL) CheckDb(int phase) override;
+	STDMETHODIMP_(VOID) Destroy() override
+	{}
+
+	DBCHeckCallback *cb;
+	int CheckPhase1(void);
+	int CheckPhase2(void);
+	int CheckPhase3(void);
+	int CheckPhase4(void);
 };
diff --git a/plugins/Dbx_sqlite/src/dbsettings.cpp b/plugins/Dbx_sqlite/src/dbsettings.cpp
index 65fcd75aae..74c9dde075 100755
--- a/plugins/Dbx_sqlite/src/dbsettings.cpp
+++ b/plugins/Dbx_sqlite/src/dbsettings.cpp
@@ -126,6 +126,19 @@ BOOL CDbxSQLite::WriteContactSettingWorker(MCONTACT hContact, DBCONTACTWRITESETT
 
 /////////////////////////////////////////////////////////////////////////////////////////
 
+int CDbxSQLite::DeleteContactSettingWorker(MCONTACT hContact, LPCSTR szModule, LPCSTR szSetting)
+{
+	mir_cslock lock(m_csDbAccess);
+	sqlite3_stmt *stmt = InitQuery("DELETE FROM settings WHERE contact_id = ? AND module = ? AND setting = ?;", qSettDel);
+	sqlite3_bind_int64(stmt, 1, hContact);
+	sqlite3_bind_text(stmt, 2, szModule, (int)mir_strlen(szModule), nullptr);
+	sqlite3_bind_text(stmt, 3, szSetting, (int)mir_strlen(szSetting), nullptr);
+	int rc = sqlite3_step(stmt);
+	logError(rc, __FILE__, __LINE__);
+	sqlite3_reset(stmt);
+	return rc;
+}
+
 BOOL CDbxSQLite::DeleteContactSetting(MCONTACT hContact, LPCSTR szModule, LPCSTR szSetting)
 {
 	if (szSetting == nullptr || szModule == nullptr)
@@ -139,25 +152,10 @@ BOOL CDbxSQLite::DeleteContactSetting(MCONTACT hContact, LPCSTR szModule, LPCSTR
 
 	char *szCachedSettingName = m_cache->GetCachedSetting(szModule, szSetting, mir_strlen(szModule), mir_strlen(szSetting));
 	if (szCachedSettingName[-1] == 0) { // it's not a resident variable
-		mir_cslock lock(m_csDbAccess);
-		sqlite3_stmt *stmt = InitQuery("DELETE FROM settings WHERE contact_id = ? AND module = ? AND setting = ?;", qSettDel);
-		sqlite3_bind_int64(stmt, 1, hContact);
-		sqlite3_bind_text(stmt, 2, szModule, (int)mir_strlen(szModule), nullptr);
-		sqlite3_bind_text(stmt, 3, szSetting, (int)mir_strlen(szSetting), nullptr);
-		int rc = sqlite3_step(stmt);
-		logError(rc, __FILE__, __LINE__);
-		sqlite3_reset(stmt);
-
-		stmt = InitQuery("SELECT CHANGES() FROM settings;", qSettChanges);
-		rc = sqlite3_step(stmt);
-		logError(rc, __FILE__, __LINE__);
-		int deleted = sqlite3_column_int(stmt, 0);
-		sqlite3_reset(stmt);
-		if (deleted == 0)
-			return 1;
-
+		DeleteContactSettingWorker(hContact, szModule, szSetting);
 		DBFlush();
 	}
+
 	m_cache->GetCachedValuePtr(hContact, szCachedSettingName, -1);
 
 	// notify
diff --git a/plugins/Dbx_sqlite/src/stdafx.h b/plugins/Dbx_sqlite/src/stdafx.h
index 9b8cc30a59..e035993f8d 100644
--- a/plugins/Dbx_sqlite/src/stdafx.h
+++ b/plugins/Dbx_sqlite/src/stdafx.h
@@ -11,6 +11,7 @@
 #include <m_crypto.h>
 #include <m_database.h>
 #include <m_gui.h>
+#include <m_langpack.h>
 #include <m_netlib.h>
 #include <m_protocols.h>
 #include <m_metacontacts.h>
diff --git a/plugins/Dbx_sqlite/src/version.h b/plugins/Dbx_sqlite/src/version.h
index 2a3e1bfa64..e525df5960 100644
--- a/plugins/Dbx_sqlite/src/version.h
+++ b/plugins/Dbx_sqlite/src/version.h
@@ -1,7 +1,7 @@
 #define __MAJOR_VERSION          0
 #define __MINOR_VERSION         96
 #define __RELEASE_NUM            1
-#define __BUILD_NUM              1
+#define __BUILD_NUM              2
 
 #include <stdver.h>