X-Git-Url: https://git.pterodactylus.net/?p=fms.git;a=blobdiff_plain;f=src%2Fdbmaintenancethread.cpp;h=0563e11556bdab1a0845805da370015b3d63cdd3;hp=f8c33571e3e3a7f139b10a40a303cf40ac5cfe3c;hb=HEAD;hpb=d5c9f7e6c1dd263dfc85a3cb5941a378a5ddd923 diff --git a/src/dbmaintenancethread.cpp b/src/dbmaintenancethread.cpp index f8c3357..0563e11 100644 --- a/src/dbmaintenancethread.cpp +++ b/src/dbmaintenancethread.cpp @@ -1,6 +1,10 @@ #include "../include/dbmaintenancethread.h" #include "../include/stringfunctions.h" #include "../include/option.h" +#include "../include/threadbuilder.h" +#include "../include/dbsetup.h" + +#include #include #include @@ -19,34 +23,129 @@ DBMaintenanceThread::DBMaintenanceThread() m_last6hour-=Poco::Timespan(0,5,42,0,0); 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() { + std::vector > m_unthreadedmessages; + Option option(m_db); + std::string ll(""); + option.Get("LogLevel",ll); + + 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 + // This query was causing the db to be locked and a journal file created. + // build a list of boards and messageids and then use that instead of keeping the query in use + SQLite3DB::Statement selectst=m_db->Prepare("SELECT tblMessage.MessageID FROM tblMessage \ + INNER JOIN tblMessageBoard ON tblMessage.MessageID=tblMessageBoard.MessageID \ + LEFT JOIN (SELECT tblThread.BoardID, tblThreadPost.MessageID FROM tblThread INNER JOIN tblThreadPost ON tblThread.ThreadID=tblThreadPost.ThreadID WHERE tblThread.BoardID=?) AS temp1 ON tblMessage.MessageID=temp1.MessageID \ + WHERE tblMessageBoard.BoardID=? AND temp1.BoardID IS NULL;"); + + boardst.Step(); + while(boardst.RowReturned()) + { + int boardid=-1; + boardst.ResultInt(0,boardid); + boardst.Step(); + + selectst.Bind(0,boardid); + selectst.Bind(1,boardid); + selectst.Step(); + + while(selectst.RowReturned()) + { + int messageid=-1; + + selectst.ResultInt(0,messageid); + + m_unthreadedmessages.push_back(std::pair(boardid,messageid)); + + selectst.Step(); + } + selectst.Reset(); + } + selectst.Finalize(); + boardst.Finalize(); + + for(std::vector >::iterator i=m_unthreadedmessages.begin(); i!=m_unthreadedmessages.end(); i++) + { + tb.Build((*i).second,(*i).first,true); + } + + /* + 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(); + boardst.Reset(); + } + */ + + // 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); + if(dbres!="ok") + { + m_db->Execute("REINDEX;"); + dbres=TestDBIntegrity(m_db); + m_log->trace("DBMaintenanceThread::Do10MinuteMaintenenace() end after reindex 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 +200,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 +244,12 @@ void DBMaintenanceThread::Do6HourMaintenance() st.Step(); } + st.Finalize(); + st2.Finalize(); + upd.Finalize(); + + m_db->Execute("COMMIT;"); + m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance"); } @@ -145,6 +257,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 +307,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 +331,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(); } @@ -270,10 +385,29 @@ void DBMaintenanceThread::Do1DayMaintenance() st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d")); st.Step(); + // delete old frost message requests + date=Poco::Timestamp(); + date-=Poco::Timespan(m_frostmaxdaysbackward,0,0,0,0); + st=m_db->Prepare("DELETE FROM tblFrostMessageRequests WHERE DayExecute("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);"); + + // cap failure count + m_db->Execute("UPDATE tblIdentity SET FailureCount=(SELECT OptionValue FROM tblOption WHERE Option='MaxFailureCount') WHERE FailureCount>(SELECT OptionValue FROM tblOption WHERE Option='MaxFailureCount');"); + // 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 Option='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 +416,27 @@ 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); + + m_frostmaxdaysbackward=5; + tempval="5"; + option.Get("FrostMessageMaxDaysBackward",tempval); + StringFunctions::Convert(tempval,m_frostmaxdaysbackward); + Poco::DateTime now; + int i=0; do { @@ -293,11 +447,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 +470,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.");