X-Git-Url: https://git.pterodactylus.net/?a=blobdiff_plain;f=src%2Fdbmaintenancethread.cpp;h=4d48786418d2c55ec49728d39eb3e4a8963db9e6;hb=9ae3b1434e51788e6feb72e1415ec800d05c535a;hp=e9f03e0dd68f53cbae1f8a7b2d0b361843fc0439;hpb=59a5414ec47a2932a7802fcd1d98c4d80166564f;p=fms.git diff --git a/src/dbmaintenancethread.cpp b/src/dbmaintenancethread.cpp index e9f03e0..4d48786 100644 --- a/src/dbmaintenancethread.cpp +++ b/src/dbmaintenancethread.cpp @@ -4,6 +4,8 @@ #include "../include/threadbuilder.h" #include "../include/dbsetup.h" +#include + #include #include #include @@ -21,38 +23,60 @@ DBMaintenanceThread::DBMaintenanceThread() m_last6hour-=Poco::Timespan(0,5,42,0,0); m_last1day=Poco::Timestamp(); m_last1day-=Poco::Timespan(0,23,51,0,0); - - - } void DBMaintenanceThread::Do10MinuteMaintenance() { + std::vector > m_unthreadedmessages; 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 + // 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\ + 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;"); + WHERE tblMessageBoard.BoardID=? AND tblThreadPost.MessageID IS NULL;"); boardst.Step(); while(boardst.RowReturned()) { int boardid=-1; + boardst.ResultInt(0,boardid); + boardst.Step(); + + selectst.Bind(0,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); @@ -72,14 +96,9 @@ void DBMaintenanceThread::Do10MinuteMaintenance() selectst.Reset(); boardst.Step(); + boardst.Reset(); } - - // TODO - remove after corruption issue fixed - if(ll=="8") - { - std::string dbres=TestDBIntegrity(m_db); - m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() middle TestDBIntegrity 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;"); @@ -105,6 +124,12 @@ void DBMaintenanceThread::Do10MinuteMaintenance() { 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"); @@ -174,6 +199,9 @@ 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);"); @@ -215,6 +243,10 @@ void DBMaintenanceThread::Do6HourMaintenance() st.Step(); } + st.Finalize(); + st2.Finalize(); + upd.Finalize(); + m_db->Execute("COMMIT;"); m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance"); @@ -274,6 +306,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()) { @@ -297,27 +330,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(); } @@ -355,6 +388,14 @@ 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");