--- /dev/null
+#include "../include/dbmaintenancethread.h"\r
+#include "../include/stringfunctions.h"\r
+#include "../include/option.h"\r
+\r
+#include <Poco/Timestamp.h>\r
+#include <Poco/Timespan.h>\r
+#include <Poco/DateTimeFormatter.h>\r
+#include <Poco/Thread.h>\r
+\r
+DBMaintenanceThread::DBMaintenanceThread()\r
+{\r
+ // move last maintenance times back so they will all run soon\r
+ m_last10minute=Poco::Timestamp();\r
+ m_last30minute=Poco::Timestamp();\r
+ m_last30minute-=Poco::Timespan(0,0,11,0,0);\r
+ m_last1hour=Poco::Timestamp();\r
+ m_last1hour-=Poco::Timespan(0,0,49,0,0);\r
+ m_last6hour=Poco::Timestamp();\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
+ std::string tempval="180";\r
+ Option::Instance()->Get("DeleteMessagesOlderThan",tempval);\r
+ StringFunctions::Convert(tempval,m_deletemessagesolderthan);\r
+\r
+}\r
+\r
+\r
+void DBMaintenanceThread::Do10MinuteMaintenance()\r
+{\r
+\r
+ m_log->debug("PeriodicDBMaintenance::Do10MinuteMaintenance");\r
+}\r
+\r
+void DBMaintenanceThread::Do30MinuteMaintenance()\r
+{\r
+\r
+ m_log->debug("PeriodicDBMaintenance::Do30MinuteMaintenance");\r
+}\r
+\r
+void DBMaintenanceThread::Do1HourMaintenance()\r
+{\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
+ SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=?, PeerTrustListTrust=? WHERE IdentityID=?");\r
+ st.Step();\r
+ while(st.RowReturned())\r
+ {\r
+ int identityid=0;\r
+ int trust=0;\r
+ \r
+ st.ResultInt(0,identityid);\r
+\r
+ upd.Bind(0,identityid);\r
+ if(st.ResultNull(1)==false)\r
+ {\r
+ trust=0;\r
+ st.ResultInt(1,trust);\r
+ upd.Bind(0,trust);\r
+ }\r
+ else\r
+ {\r
+ upd.Bind(0);\r
+ }\r
+ if(st.ResultNull(2)==false)\r
+ {\r
+ trust=0;\r
+ st.ResultInt(2,trust);\r
+ upd.Bind(1,trust);\r
+ }\r
+ else\r
+ {\r
+ upd.Bind(1);\r
+ }\r
+ upd.Bind(2,identityid);\r
+ upd.Step();\r
+ upd.Reset();\r
+\r
+ st.Step();\r
+ }\r
+\r
+ // set null peer trust for identities without a calculated trust\r
+ st=m_db->Prepare("SELECT IdentityID FROM tblIdentity WHERE IdentityID NOT IN (SELECT TargetIdentityID FROM vwCalculatedPeerTrust);");\r
+ upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=NULL, PeerTrustListTrust=NULL WHERE IdentityID=?;");\r
+ st.Step();\r
+ while(st.RowReturned())\r
+ {\r
+ int identityid=0;\r
+ st.ResultInt(0,identityid);\r
+ upd.Bind(0,identityid);\r
+ upd.Step();\r
+ upd.Reset();\r
+ st.Step();\r
+ }\r
+\r
+ m_log->debug("PeriodicDBMaintenance::Do1HourMaintenance");\r
+}\r
+\r
+void DBMaintenanceThread::Do6HourMaintenance()\r
+{\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
+ SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE MessageID=?;");\r
+ st.Step();\r
+ while(st.RowReturned())\r
+ {\r
+ // find a valid boardid for the message\r
+ int messageid=0;\r
+ int boardid=0;\r
+\r
+ st.ResultInt(0,messageid);\r
+\r
+ st2.Bind(0,messageid);\r
+ st2.Step();\r
+ if(st2.RowReturned())\r
+ {\r
+ st2.ResultInt(0,boardid);\r
+ upd.Bind(0,boardid);\r
+ upd.Bind(1,messageid);\r
+ upd.Step();\r
+ upd.Reset();\r
+ }\r
+ st2.Reset();\r
+ \r
+ st.Step();\r
+ }\r
+\r
+ m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance");\r
+}\r
+\r
+void DBMaintenanceThread::Do1DayMaintenance()\r
+{\r
+ Poco::DateTime date;\r
+\r
+ // delete all puzzles 2 or more days old\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(2,0,0,0,0);\r
+ m_db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+ m_db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ // delete all identities we've never seen and were added more than 20 days ago\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(20,0,0,0,0);\r
+ m_db->Execute("DELETE FROM tblIdentity WHERE LastSeen IS NULL AND DateAdded<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ // delete old identity requests - we don't need them anymore\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(2,0,0,0,0);\r
+ m_db->Execute("DELETE FROM tblIdentityRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ // delete old board list inserts/requests - we don't need them anymore\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(2,0,0,0,0);\r
+ m_db->Execute("DELETE FROM tblBoardListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+ m_db->Execute("DELETE FROM tblBoardListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ // delete old local identity inserts - we don't need them anymore\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(2,0,0,0,0);\r
+ m_db->Execute("DELETE FROM tblLocalIdentityInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ // delete old message list inserts/requests - we don't need them anymore\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(2,0,0,0,0);\r
+ m_db->Execute("DELETE FROM tblMessageListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+ m_db->Execute("DELETE FROM tblMessageListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ // delete old trust list inserts/requests - we don't need them anymore\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(2,0,0,0,0);\r
+ m_db->Execute("DELETE FROM tblTrustListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+ m_db->Execute("DELETE FROM tblTrustListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ // delete trust lists from identities we aren't trusting anymore\r
+ m_db->Execute("DELETE FROM tblPeerTrust WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity WHERE (LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust')) AND (PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust')));");\r
+\r
+ // remove identityid from messages where the identity has been deleted\r
+ m_db->Execute("UPDATE tblMessage SET IdentityID=NULL WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");\r
+\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
+ st.Step();\r
+ while(st.RowReturned())\r
+ {\r
+ std::string name="";\r
+ std::string namepart="";\r
+ std::string publickey="";\r
+ int identityid=0;\r
+ st.ResultText(0,name);\r
+\r
+ std::vector<std::string> parts;\r
+ StringFunctions::Split(name,"@",parts);\r
+\r
+ // name can have a @ in it - so reattach all parts except the last which is the key\r
+ for(long i=0; i<parts.size()-1; i++)\r
+ {\r
+ if(i!=0)\r
+ {\r
+ namepart+="@";\r
+ }\r
+ namepart+=parts[i];\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
+ {\r
+ publickey="";\r
+ identityid=0;\r
+ st2.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
+\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
+ }\r
+\r
+ st.Step();\r
+ }\r
+\r
+ // delete single use identities that are older than 7 days\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(7,0,0,0,0);\r
+ st=m_db->Prepare("DELETE FROM tblIdentity WHERE SingleUse='true' AND DateAdded<?;");\r
+ st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));\r
+ st.Step();\r
+\r
+ // delete local single use identities that are older than 7 days\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(7,0,0,0,0);\r
+ st=m_db->Prepare("DELETE FROM tblLocalIdentity WHERE SingleUse='true' AND DateCreated<?;");\r
+ st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));\r
+ st.Step();\r
+\r
+ // delete old messages\r
+ date=Poco::Timestamp();\r
+ date-=Poco::Timespan(m_deletemessagesolderthan,0,0,0,0);\r
+ st=m_db->Prepare("DELETE FROM tblMessage WHERE MessageDate<?;");\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
+ m_log->debug("PeriodicDBMaintenance::Do1DayMaintenance");\r
+\r
+}\r
+\r
+void DBMaintenanceThread::run()\r
+{\r
+ m_log->debug("DBMaintenanceThread::run thread started.");\r
+\r
+ Poco::DateTime now;\r
+\r
+ do\r
+ {\r
+ now=Poco::Timestamp();\r
+\r
+ if((m_last10minute+Poco::Timespan(0,0,10,0,0))<=now)\r
+ {\r
+ Do10MinuteMaintenance();\r
+ m_last10minute=Poco::Timestamp();\r
+ }\r
+ if((m_last30minute+Poco::Timespan(0,0,30,0,0))<=now)\r
+ {\r
+ Do30MinuteMaintenance();\r
+ m_last30minute=Poco::Timestamp();\r
+ }\r
+ if((m_last1hour+Poco::Timespan(0,1,0,0,0))<=now)\r
+ {\r
+ Do1HourMaintenance();\r
+ m_last1hour=Poco::Timestamp();\r
+ }\r
+ if((m_last6hour+Poco::Timespan(0,6,0,0,0))<=now)\r
+ {\r
+ Do6HourMaintenance();\r
+ m_last6hour=Poco::Timestamp();\r
+ }\r
+ if((m_last1day+Poco::Timespan(1,0,0,0,0))<=now)\r
+ {\r
+ Do1DayMaintenance();\r
+ m_last1day=Poco::Timestamp();\r
+ }\r
+\r
+ Poco::Thread::sleep(1000);\r
+ }while(!IsCancelled());\r
+\r
+ m_log->debug("DBMaintenanceThread::run thread exiting.");\r
+}\r
projects / fms.git / blobdiff