#include "../include/threadbuilder.h"\r
#include "../include/dbsetup.h"\r
\r
+#include <vector>\r
+\r
#include <Poco/Timestamp.h>\r
#include <Poco/Timespan.h>\r
#include <Poco/DateTimeFormatter.h>\r
m_last6hour-=Poco::Timespan(0,5,42,0,0);\r
m_last1day=Poco::Timestamp();\r
m_last1day-=Poco::Timespan(0,23,51,0,0);\r
-\r
- m_deletemessagesolderthan=180;\r
- std::string tempval="180";\r
- Option::Instance()->Get("DeleteMessagesOlderThan",tempval);\r
- StringFunctions::Convert(tempval,m_deletemessagesolderthan);\r
-\r
- m_messagedownloadmaxdaysbackward=5;\r
- tempval="5";\r
- Option::Instance()->Get("MessageDownloadMaxDaysBackward",tempval);\r
- StringFunctions::Convert(tempval,m_messagedownloadmaxdaysbackward);\r
-\r
}\r
\r
\r
void DBMaintenanceThread::Do10MinuteMaintenance()\r
{\r
- std::string ll="";\r
- Option::Instance()->Get("LogLevel",ll);\r
+ std::vector<std::pair<long,long> > m_unthreadedmessages;\r
+ Option option(m_db);\r
+ std::string ll("");\r
+ option.Get("LogLevel",ll);\r
\r
- // TODO - remove after corruption issue fixed\r
- if(ll=="8")\r
- {\r
- std::string dbres=TestDBIntegrity();\r
- m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() start TestDBIntegrity returned "+dbres);\r
- }\r
-\r
- ThreadBuilder tb;\r
+ ThreadBuilder tb(m_db);\r
SQLite3DB::Statement boardst=m_db->Prepare("SELECT BoardID FROM tblBoard WHERE Forum='true';");\r
// select messages for a board that aren't in a thread\r
+ // This query was causing the db to be locked and a journal file created.\r
+ // build a list of boards and messageids and then use that instead of keeping the query in use\r
SQLite3DB::Statement selectst=m_db->Prepare("SELECT tblMessage.MessageID FROM tblMessage \\r
- INNER JOIN tblMessageBoard ON tblMessage.MessageID=tblMessageBoard.MessageID\\r
- LEFT JOIN tblThreadPost ON tblMessage.MessageID=tblThreadPost.MessageID \\r
- LEFT JOIN tblThread ON tblThreadPost.ThreadID=tblThread.ThreadID\\r
- WHERE tblMessageBoard.BoardID=? AND tblThread.BoardID IS NULL;");\r
+ INNER JOIN tblMessageBoard ON tblMessage.MessageID=tblMessageBoard.MessageID \\r
+ 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 \\r
+ WHERE tblMessageBoard.BoardID=? AND temp1.BoardID IS NULL;");\r
\r
boardst.Step();\r
while(boardst.RowReturned())\r
{\r
int boardid=-1;\r
+ boardst.ResultInt(0,boardid);\r
+ boardst.Step();\r
+\r
+ selectst.Bind(0,boardid);\r
+ selectst.Bind(1,boardid);\r
+ selectst.Step();\r
+\r
+ while(selectst.RowReturned())\r
+ {\r
+ int messageid=-1;\r
+\r
+ selectst.ResultInt(0,messageid);\r
+\r
+ m_unthreadedmessages.push_back(std::pair<long,long>(boardid,messageid));\r
+\r
+ selectst.Step();\r
+ }\r
+ selectst.Reset();\r
+ }\r
+ selectst.Finalize();\r
+ boardst.Finalize();\r
+\r
+ for(std::vector<std::pair<long,long> >::iterator i=m_unthreadedmessages.begin(); i!=m_unthreadedmessages.end(); i++)\r
+ {\r
+ tb.Build((*i).second,(*i).first,true);\r
+ }\r
+ \r
+ /*\r
+ while(boardst.RowReturned())\r
+ {\r
+ int boardid=-1;\r
\r
boardst.ResultInt(0,boardid);\r
\r
selectst.Reset();\r
\r
boardst.Step();\r
+ boardst.Reset();\r
}\r
-\r
- // TODO - remove after corruption issue fixed\r
- if(ll=="8")\r
- {\r
- std::string dbres=TestDBIntegrity();\r
- m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() middle TestDBIntegrity returned "+dbres);\r
- }\r
+ */\r
\r
// now rebuild threads where the message has been deleted\r
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;");\r
st.Step();\r
}\r
\r
+ // delete threads that have no messages\r
+ 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);");\r
+\r
// TODO - remove after corruption issue fixed\r
if(ll=="8")\r
{\r
- std::string dbres=TestDBIntegrity();\r
+ std::string dbres=TestDBIntegrity(m_db);\r
m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() end TestDBIntegrity returned "+dbres);\r
+ if(dbres!="ok")\r
+ {\r
+ m_db->Execute("REINDEX;");\r
+ dbres=TestDBIntegrity(m_db);\r
+ m_log->trace("DBMaintenanceThread::Do10MinuteMaintenenace() end after reindex returned "+dbres);\r
+ }\r
}\r
\r
m_log->debug("PeriodicDBMaintenance::Do10MinuteMaintenance");\r
\r
void DBMaintenanceThread::Do1HourMaintenance()\r
{\r
+\r
+ m_db->Execute("BEGIN;");\r
// recalculate all trust levels - this is CPU instensive\r
// do 1 identity at a time as doing it with 1 UPDATE statement locks that database for the duration\r
SQLite3DB::Statement st=m_db->Prepare("SELECT TargetIdentityID,PeerMessageTrust,PeerTrustListTrust FROM vwCalculatedPeerTrust;");\r
st.Step();\r
}\r
\r
+ st.Finalize();\r
+ upd.Finalize();\r
+\r
// insert all identities not in trust list already\r
m_db->Execute("INSERT INTO tblIdentityTrust(LocalIdentityID,IdentityID) SELECT LocalIdentityID,IdentityID FROM tblLocalIdentity,tblIdentity WHERE LocalIdentityID || '_' || IdentityID NOT IN (SELECT LocalIdentityID || '_' || IdentityID FROM tblIdentityTrust);");\r
\r
+ m_db->Execute("COMMIT;");\r
+\r
m_log->debug("PeriodicDBMaintenance::Do1HourMaintenance");\r
}\r
\r
void DBMaintenanceThread::Do6HourMaintenance()\r
{\r
\r
+ m_db->Execute("BEGIN;");\r
+\r
// 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\r
SQLite3DB::Statement st=m_db->Prepare("SELECT MessageID FROM tblMessage WHERE ReplyBoardID NOT IN (SELECT BoardID FROM tblBoard);");\r
SQLite3DB::Statement st2=m_db->Prepare("SELECT BoardID FROM tblMessageBoard WHERE MessageID=?;");\r
st.Step();\r
}\r
\r
+ st.Finalize();\r
+ st2.Finalize();\r
+ upd.Finalize();\r
+\r
+ m_db->Execute("COMMIT;");\r
+\r
m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance");\r
}\r
\r
{\r
Poco::DateTime date;\r
\r
+ m_db->Execute("BEGIN;");\r
+\r
// delete all puzzles 2 or more days old\r
date=Poco::Timestamp();\r
date-=Poco::Timespan(2,0,0,0,0);\r
// try to re-attach messages from identities that were previously deleted, but have been since re-added\r
// first get the names from messages that have a NULL IdentityID\r
SQLite3DB::Statement st=m_db->Prepare("SELECT FromName FROM tblMessage WHERE IdentityID IS NULL GROUP BY FromName;");\r
+ SQLite3DB::Statement findst=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");\r
st.Step();\r
while(st.RowReturned())\r
{\r
}\r
\r
// find identities with this name\r
- SQLite3DB::Statement st2=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");\r
- st2.Bind(0,namepart);\r
- st2.Step();\r
- while(st2.RowReturned())\r
+ findst.Bind(0,namepart);\r
+ findst.Step();\r
+ while(findst.RowReturned())\r
{\r
publickey="";\r
identityid=0;\r
- st2.ResultText(1,publickey);\r
+ findst.ResultText(1,publickey);\r
// check if public key matches 2nd part\r
if(parts.size()>1 && publickey.find(parts[1])==4)\r
{\r
// we have the identity - so update the messages table with the identityid\r
- st2.ResultInt(0,identityid);\r
+ findst.ResultInt(0,identityid);\r
\r
SQLite3DB::Statement st3=m_db->Prepare("UPDATE tblMessage SET IdentityID=? WHERE FromName=? AND IdentityID IS NULL;");\r
st3.Bind(0,identityid);\r
st3.Bind(1,name);\r
st3.Step();\r
}\r
- st2.Step();\r
+ findst.Step();\r
}\r
+ findst.Reset();\r
\r
st.Step();\r
}\r
st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));\r
st.Step();\r
\r
+ // delete old frost message requests\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(m_frostmaxdaysbackward,0,0,0,0);\r
+ st=m_db->Prepare("DELETE FROM tblFrostMessageRequests WHERE Day<?;");\r
+ st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));\r
+ st.Step();\r
+\r
// delete tblIdentityTrust for local identities and identities that have been deleted\r
m_db->Execute("DELETE FROM tblIdentityTrust WHERE LocalIdentityID NOT IN (SELECT LocalIdentityID FROM tblLocalIdentity);");\r
m_db->Execute("DELETE FROM tblIdentityTrust WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");\r
\r
+\r
+ // cap failure count\r
+ m_db->Execute("UPDATE tblIdentity SET FailureCount=(SELECT OptionValue FROM tblOption WHERE Option='MaxFailureCount') WHERE FailureCount>(SELECT OptionValue FROM tblOption WHERE Option='MaxFailureCount');");\r
+ // reduce failure count for each identity\r
+ m_db->Execute("UPDATE tblIdentity SET FailureCount=0 WHERE FailureCount<(SELECT OptionValue FROM tblOption WHERE Option='FailureCountReduction');");\r
+ m_db->Execute("UPDATE tblIdentity SET FailureCount=FailureCount-(SELECT OptionValue FROM tblOption WHERE Option='FailureCountReduction') WHERE FailureCount>=(SELECT OptionValue FROM tblOption WHERE Option='FailureCountReduction');");\r
+\r
+ st.Finalize();\r
+ findst.Finalize();\r
+\r
+ m_db->Execute("COMMIT;");\r
+\r
m_log->debug("PeriodicDBMaintenance::Do1DayMaintenance");\r
\r
}\r
{\r
m_log->debug("DBMaintenanceThread::run thread started.");\r
\r
+ LoadDatabase();\r
+ Option option(m_db);\r
+ std::string tempval("");\r
+\r
+ m_deletemessagesolderthan=180;\r
+ tempval="180";\r
+ option.Get("DeleteMessagesOlderThan",tempval);\r
+ StringFunctions::Convert(tempval,m_deletemessagesolderthan);\r
+\r
+ m_messagedownloadmaxdaysbackward=5;\r
+ tempval="5";\r
+ option.Get("MessageDownloadMaxDaysBackward",tempval);\r
+ StringFunctions::Convert(tempval,m_messagedownloadmaxdaysbackward);\r
+\r
+ m_frostmaxdaysbackward=5;\r
+ tempval="5";\r
+ option.Get("FrostMessageMaxDaysBackward",tempval);\r
+ StringFunctions::Convert(tempval,m_frostmaxdaysbackward);\r
+\r
Poco::DateTime now;\r
int i=0;\r
\r
projects / fms.git / blobdiff