X-Git-Url: https://git.pterodactylus.net/?a=blobdiff_plain;f=src%2Fdbmaintenancethread.cpp;h=0b1349f1c6f20930d019392a49e5fb72fa6a9efd;hb=ed0732b2550c23c05fc9faf925620e87ee6dee12;hp=f8c33571e3e3a7f139b10a40a303cf40ac5cfe3c;hpb=d5c9f7e6c1dd263dfc85a3cb5941a378a5ddd923;p=fms.git

diff --git a/src/dbmaintenancethread.cpp b/src/dbmaintenancethread.cpp
index f8c3357..0b1349f 100644
--- a/src/dbmaintenancethread.cpp
+++ b/src/dbmaintenancethread.cpp
@@ -1,6 +1,8 @@
 #include "../include/dbmaintenancethread.h"
 #include "../include/stringfunctions.h"
 #include "../include/option.h"
+#include "../include/threadbuilder.h"
+#include "../include/dbsetup.h"
 
 #include <Poco/Timestamp.h>
 #include <Poco/Timespan.h>
@@ -20,33 +22,110 @@ DBMaintenanceThread::DBMaintenanceThread()
 	m_last1day=Poco::Timestamp();
 	m_last1day-=Poco::Timespan(0,23,51,0,0);
 
-	m_deletemessagesolderthan=180;
-	std::string tempval="180";
-	Option::Instance()->Get("DeleteMessagesOlderThan",tempval);
-	StringFunctions::Convert(tempval,m_deletemessagesolderthan);
 
-	m_messagedownloadmaxdaysbackward=5;
-	tempval="5";
-	Option::Instance()->Get("MessageDownloadMaxDaysBackward",tempval);
-	StringFunctions::Convert(tempval,m_messagedownloadmaxdaysbackward);
 
 }
 
 
 void DBMaintenanceThread::Do10MinuteMaintenance()
 {
+	Option option(m_db);
+	std::string ll("");
+	option.Get("LogLevel",ll);
+
+	// TODO - remove after corruption issue fixed
+	if(ll=="8")
+	{
+		std::string dbres=TestDBIntegrity(m_db);
+		m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() start TestDBIntegrity returned "+dbres);
+	}
+
+	ThreadBuilder tb(m_db);
+	SQLite3DB::Statement boardst=m_db->Prepare("SELECT BoardID FROM tblBoard WHERE Forum='true';");
+	// select messages for a board that aren't in a thread
+	SQLite3DB::Statement selectst=m_db->Prepare("SELECT tblMessage.MessageID FROM tblMessage \
+												INNER JOIN tblMessageBoard ON tblMessage.MessageID=tblMessageBoard.MessageID\
+												LEFT JOIN tblThreadPost ON tblMessage.MessageID=tblThreadPost.MessageID \
+												LEFT JOIN tblThread ON tblThreadPost.ThreadID=tblThread.ThreadID\
+												WHERE tblMessageBoard.BoardID=? AND tblThread.BoardID IS NULL;");
+
+	boardst.Step();
+	while(boardst.RowReturned())
+	{
+		int boardid=-1;
+
+		boardst.ResultInt(0,boardid);
+
+		selectst.Bind(0,boardid);
+		selectst.Step();
+
+		while(selectst.RowReturned())
+		{
+			int messageid=-1;
+
+			selectst.ResultInt(0,messageid);
+
+			tb.Build(messageid,boardid,true);
+
+			selectst.Step();
+		}
+		selectst.Reset();
+
+		boardst.Step();
+	}
+
+	// TODO - remove after corruption issue fixed
+	if(ll=="8")
+	{
+		std::string dbres=TestDBIntegrity(m_db);
+		m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() middle TestDBIntegrity returned "+dbres);
+		if(dbres!="ok")
+		{
+			m_db->Execute("REINDEX;");
+			dbres=TestDBIntegrity(m_db);
+			m_log->trace("DBMaintenanceThread::Do10MinuteMaintenenace() middle after reindex returned "+dbres);
+		}
+	}
+
+	// now rebuild threads where the message has been deleted
+	SQLite3DB::Statement st=m_db->Prepare("SELECT tblThreadPost.MessageID, tblThread.BoardID FROM tblThreadPost INNER JOIN tblThread ON tblThreadPost.ThreadID=tblThread.ThreadID LEFT JOIN tblMessage ON tblThreadPost.MessageID=tblMessage.MessageID WHERE tblMessage.MessageID IS NULL;");
+	st.Step();
+	while(st.RowReturned())
+	{
+		int messageid=-1;
+		int boardid=-1;
+
+		st.ResultInt(0,messageid);
+		st.ResultInt(1,boardid);
+
+		tb.Build(messageid,boardid,true);
+
+		st.Step();
+	}
+
+	// delete threads that have no messages
+	m_db->Execute("DELETE FROM tblThread WHERE ThreadID IN (SELECT tblThread.ThreadID FROM tblThread LEFT JOIN tblThreadPost ON tblThread.ThreadID=tblThreadPost.ThreadID WHERE tblThreadPost.ThreadID IS NULL);");
+
+	// TODO - remove after corruption issue fixed
+	if(ll=="8")
+	{
+		std::string dbres=TestDBIntegrity(m_db);
+		m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() end TestDBIntegrity returned "+dbres);
+	}
 
 	m_log->debug("PeriodicDBMaintenance::Do10MinuteMaintenance");
 }
 
 void DBMaintenanceThread::Do30MinuteMaintenance()
 {
-
+	// UNCOMMENT method in run when code is placed here
 	m_log->debug("PeriodicDBMaintenance::Do30MinuteMaintenance");
 }
 
 void DBMaintenanceThread::Do1HourMaintenance()
 {
+
+	m_db->Execute("BEGIN;");
 	// recalculate all trust levels - this is CPU instensive
 	// do 1 identity at a time as doing it with 1 UPDATE statement locks that database for the duration
 	SQLite3DB::Statement st=m_db->Prepare("SELECT TargetIdentityID,PeerMessageTrust,PeerTrustListTrust FROM vwCalculatedPeerTrust;");
@@ -101,15 +180,22 @@ void DBMaintenanceThread::Do1HourMaintenance()
 		st.Step();
 	}
 
+	st.Finalize();
+	upd.Finalize();
+
 	// insert all identities not in trust list already
 	m_db->Execute("INSERT INTO tblIdentityTrust(LocalIdentityID,IdentityID) SELECT LocalIdentityID,IdentityID FROM tblLocalIdentity,tblIdentity WHERE LocalIdentityID || '_' || IdentityID NOT IN (SELECT LocalIdentityID || '_' || IdentityID FROM tblIdentityTrust);");
 
+	m_db->Execute("COMMIT;");
+
 	m_log->debug("PeriodicDBMaintenance::Do1HourMaintenance");
 }
 
 void DBMaintenanceThread::Do6HourMaintenance()
 {
 
+	m_db->Execute("BEGIN;");
+
 	// if we remove a board and the reply boardid is still set to it, we need to replace it with a boardid that does exist
 	SQLite3DB::Statement st=m_db->Prepare("SELECT MessageID FROM tblMessage WHERE ReplyBoardID NOT IN (SELECT BoardID FROM tblBoard);");
 	SQLite3DB::Statement st2=m_db->Prepare("SELECT BoardID FROM tblMessageBoard WHERE MessageID=?;");
@@ -138,6 +224,12 @@ void DBMaintenanceThread::Do6HourMaintenance()
 		st.Step();
 	}
 
+	st.Finalize();
+	st2.Finalize();
+	upd.Finalize();
+
+	m_db->Execute("COMMIT;");
+
 	m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance");
 }
 
@@ -145,6 +237,8 @@ void DBMaintenanceThread::Do1DayMaintenance()
 {
 	Poco::DateTime date;
 
+	m_db->Execute("BEGIN;");
+
 	// delete all puzzles 2 or more days old
 	date=Poco::Timestamp();
 	date-=Poco::Timespan(2,0,0,0,0);
@@ -193,6 +287,7 @@ void DBMaintenanceThread::Do1DayMaintenance()
 	// try to re-attach messages from identities that were previously deleted, but have been since re-added
 	// first get the names from messages that have a NULL IdentityID
 	SQLite3DB::Statement st=m_db->Prepare("SELECT FromName FROM tblMessage WHERE IdentityID IS NULL GROUP BY FromName;");
+	SQLite3DB::Statement findst=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");
 	st.Step();
 	while(st.RowReturned())
 	{
@@ -216,27 +311,27 @@ void DBMaintenanceThread::Do1DayMaintenance()
 		}
 
 		// find identities with this name
-		SQLite3DB::Statement st2=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");
-		st2.Bind(0,namepart);
-		st2.Step();
-		while(st2.RowReturned())
+		findst.Bind(0,namepart);
+		findst.Step();
+		while(findst.RowReturned())
 		{
 			publickey="";
 			identityid=0;
-			st2.ResultText(1,publickey);
+			findst.ResultText(1,publickey);
 			// check if public key matches 2nd part
 			if(parts.size()>1 && publickey.find(parts[1])==4)
 			{
 				// we have the identity - so update the messages table with the identityid
-				st2.ResultInt(0,identityid);
+				findst.ResultInt(0,identityid);
 
 				SQLite3DB::Statement st3=m_db->Prepare("UPDATE tblMessage SET IdentityID=? WHERE FromName=? AND IdentityID IS NULL;");
 				st3.Bind(0,identityid);
 				st3.Bind(1,name);
 				st3.Step();
 			}
-			st2.Step();
+			findst.Step();
 		}
+		findst.Reset();
 
 		st.Step();
 	}
@@ -274,6 +369,16 @@ void DBMaintenanceThread::Do1DayMaintenance()
 	m_db->Execute("DELETE FROM tblIdentityTrust WHERE LocalIdentityID NOT IN (SELECT LocalIdentityID FROM tblLocalIdentity);");
 	m_db->Execute("DELETE FROM tblIdentityTrust WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");
 
+
+	// reduce failure count for each identity
+	m_db->Execute("UPDATE tblIdentity SET FailureCount=0 WHERE FailureCount<(SELECT OptionValue FROM tblOption WHERE Option='FailureCountReduction');");
+	m_db->Execute("UPDATE tblIdentity SET FailureCount=FailureCount-(SELECT OptionValue FROM tblOption WHERE OptionName='FailureCountReduction') WHERE FailureCount>=(SELECT OptionValue FROM tblOption WHERE Option='FailureCountReduction');");
+
+	st.Finalize();
+	findst.Finalize();
+
+	m_db->Execute("COMMIT;");
+
 	m_log->debug("PeriodicDBMaintenance::Do1DayMaintenance");
 
 }
@@ -282,7 +387,23 @@ void DBMaintenanceThread::run()
 {
 	m_log->debug("DBMaintenanceThread::run thread started.");
 
+	LoadDatabase();
+	Option option(m_db);
+	std::string tempval("");
+
+	m_deletemessagesolderthan=180;
+	tempval="180";
+	option.Get("DeleteMessagesOlderThan",tempval);
+	StringFunctions::Convert(tempval,m_deletemessagesolderthan);
+
+	m_messagedownloadmaxdaysbackward=5;
+	tempval="5";
+	option.Get("MessageDownloadMaxDaysBackward",tempval);
+	StringFunctions::Convert(tempval,m_messagedownloadmaxdaysbackward);
+
+
 	Poco::DateTime now;
+	int i=0;
 
 	do
 	{
@@ -293,11 +414,13 @@ void DBMaintenanceThread::run()
 			Do10MinuteMaintenance();
 			m_last10minute=Poco::Timestamp();
 		}
+		/*
 		if((m_last30minute+Poco::Timespan(0,0,30,0,0))<=now)
 		{
 			Do30MinuteMaintenance();
 			m_last30minute=Poco::Timestamp();
 		}
+		*/
 		if((m_last1hour+Poco::Timespan(0,1,0,0,0))<=now)
 		{
 			Do1HourMaintenance();
@@ -314,7 +437,12 @@ void DBMaintenanceThread::run()
 			m_last1day=Poco::Timestamp();
 		}
 
-		Poco::Thread::sleep(1000);
+		i=0;
+		while(i++<5 && !IsCancelled())
+		{
+			Poco::Thread::sleep(1000);
+		}
+
 	}while(!IsCancelled());
 
 	m_log->debug("DBMaintenanceThread::run thread exiting.");