X-Git-Url: https://git.pterodactylus.net/?a=blobdiff_plain;f=src%2Ffreenet%2Fperiodicdbmaintenance.cpp;h=f199fd2e04f63842877d388be32ce3a60047d880;hb=9b22dd53fe62e312c1647310b7ec43aa127090af;hp=01a76c7dd1087dfa67db052ac2eba870512c30cb;hpb=df316253862dc50e8e5a790d9634ef90be37badb;p=fms.git diff --git a/src/freenet/periodicdbmaintenance.cpp b/src/freenet/periodicdbmaintenance.cpp index 01a76c7..f199fd2 100644 --- a/src/freenet/periodicdbmaintenance.cpp +++ b/src/freenet/periodicdbmaintenance.cpp @@ -1,4 +1,6 @@ #include "../../include/freenet/periodicdbmaintenance.h" +#include "../../include/stringfunctions.h" +#include "../../include/option.h" #ifdef XMEM #include @@ -6,6 +8,8 @@ PeriodicDBMaintenance::PeriodicDBMaintenance() { + std::string tempval; + m_check10mins.SetToGMTime(); m_check30mins.SetToGMTime(); m_check1hour.SetToGMTime(); @@ -18,6 +22,10 @@ PeriodicDBMaintenance::PeriodicDBMaintenance() m_check6hours.Add(0,-1,-5); m_check1day.Add(0,0,-23); + tempval="180"; + Option::Instance()->Get("DeleteMessagesOlderThan",tempval); + StringFunctions::Convert(tempval,m_deletemessagesolderthan); + } void PeriodicDBMaintenance::Do10MinuteMaintenance() @@ -35,8 +43,58 @@ void PeriodicDBMaintenance::Do30MinuteMaintenance() void PeriodicDBMaintenance::Do1HourMaintenance() { // recalculate all trust levels - this is CPU instensive - // TODO - will probably have to change this to do 1 identity at a time as this locks that database for the duration - m_db->Execute("UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);"); + // 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;"); + SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=?, PeerTrustListTrust=? WHERE IdentityID=?"); + st.Step(); + while(st.RowReturned()) + { + int identityid=0; + int trust=0; + + st.ResultInt(0,identityid); + + upd.Bind(0,identityid); + if(st.ResultNull(1)==false) + { + trust=0; + st.ResultInt(1,trust); + upd.Bind(0,trust); + } + else + { + upd.Bind(0); + } + if(st.ResultNull(2)==false) + { + trust=0; + st.ResultInt(2,trust); + upd.Bind(1,trust); + } + else + { + upd.Bind(1); + } + upd.Bind(2,identityid); + upd.Step(); + upd.Reset(); + + st.Step(); + } + + // set null peer trust for identities without a calculated trust + st=m_db->Prepare("SELECT IdentityID FROM tblIdentity WHERE IdentityID NOT IN (SELECT TargetIdentityID FROM vwCalculatedPeerTrust);"); + upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=NULL, PeerTrustListTrust=NULL WHERE IdentityID=?;"); + st.Step(); + while(st.RowReturned()) + { + int identityid=0; + st.ResultInt(0,identityid); + upd.Bind(0,identityid); + upd.Step(); + upd.Reset(); + st.Step(); + } m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do1HourMaintenance"); } @@ -44,6 +102,34 @@ void PeriodicDBMaintenance::Do1HourMaintenance() void PeriodicDBMaintenance::Do6HourMaintenance() { + // 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=?;"); + SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE MessageID=?;"); + st.Step(); + while(st.RowReturned()) + { + // find a valid boardid for the message + int messageid=0; + int boardid=0; + + st.ResultInt(0,messageid); + + st2.Bind(0,messageid); + st2.Step(); + if(st2.RowReturned()) + { + st2.ResultInt(0,boardid); + upd.Bind(0,boardid); + upd.Bind(1,messageid); + upd.Step(); + upd.Reset(); + } + st2.Reset(); + + st.Step(); + } + m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do6HourMaintenance"); } @@ -57,8 +143,6 @@ void PeriodicDBMaintenance::Do1DayMaintenance() m_db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';"); m_db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';"); - m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do1DayMaintenance"); - // delete all identities we've never seen and were added more than 20 days ago date.SetToGMTime(); date.Add(0,0,0,-20); @@ -69,6 +153,12 @@ void PeriodicDBMaintenance::Do1DayMaintenance() date.Add(0,0,0,-2); m_db->Execute("DELETE FROM tblIdentityRequests WHERE Day<'"+date.Format("%Y-%m-%d")+"';"); + // delete old board list inserts/requests - we don't need them anymore + date.SetToGMTime(); + date.Add(0,0,0,-2); + m_db->Execute("DELETE FROM tblBoardListInserts WHERE Day<'"+date.Format("%Y-%m-%d")+"';"); + m_db->Execute("DELETE FROM tblBoardListRequests WHERE Day<'"+date.Format("%Y-%m-%d")+"';"); + // delete old local identity inserts - we don't need them anymore date.SetToGMTime(); date.Add(0,0,0,-2); @@ -89,6 +179,87 @@ void PeriodicDBMaintenance::Do1DayMaintenance() // delete trust lists from identities we aren't trusting anymore 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')));"); + // remove identityid from messages where the identity has been deleted + m_db->Execute("UPDATE tblMessage SET IdentityID=NULL WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);"); + + // 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;"); + st.Step(); + while(st.RowReturned()) + { + std::string name=""; + std::string namepart=""; + std::string publickey=""; + int identityid=0; + st.ResultText(0,name); + + std::vector parts; + StringFunctions::Split(name,"@",parts); + + // name can have a @ in it - so reattach all parts except the last which is the key + for(long i=0; iPrepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;"); + st2.Bind(0,namepart); + st2.Step(); + while(st2.RowReturned()) + { + publickey=""; + identityid=0; + st2.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); + + 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(); + } + + st.Step(); + } + + // delete single use identities that are older than 7 days + date.SetToGMTime(); + date.Add(0,0,0,-7); + st=m_db->Prepare("DELETE FROM tblIdentity WHERE SingleUse='true' AND DateAddedPrepare("DELETE FROM tblLocalIdentity WHERE SingleUse='true' AND DateCreatedPrepare("DELETE FROM tblMessage WHERE MessageDateExecute("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);"); + + m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do1DayMaintenance"); + } void PeriodicDBMaintenance::Process()