src/freenet/periodicdbmaintenance.cpp

   1 #include "../../include/freenet/periodicdbmaintenance.h"\r
   2 #include "../../include/stringfunctions.h"\r
   3 #include "../../include/option.h"\r
   4 \r
   5 #ifdef XMEM\r
   6         #include <xmem.h>\r
   7 #endif\r
   8 \r
   9 PeriodicDBMaintenance::PeriodicDBMaintenance()\r
  10 {\r
  11         std::string tempval;\r
  12 \r
  13         m_check10mins.SetToGMTime();\r
  14         m_check30mins.SetToGMTime();\r
  15         m_check1hour.SetToGMTime();\r
  16         m_check6hours.SetToGMTime();\r
  17         m_check1day.SetToGMTime();\r
  18 \r
  19         // set back times so they will do their first maintenance soon ( within the next hour ) - stagger so not all run at once\r
  20         m_check30mins.Add(0,-5);\r
  21         m_check1hour.Add(0,-42);\r
  22         m_check6hours.Add(0,-1,-5);\r
  23         m_check1day.Add(0,0,-23);\r
  24 \r
  25         tempval="180";\r
  26         Option::Instance()->Get("DeleteMessagesOlderThan",tempval);\r
  27         StringFunctions::Convert(tempval,m_deletemessagesolderthan);\r
  28 \r
  29 }\r
  30 \r
  31 void PeriodicDBMaintenance::Do10MinuteMaintenance()\r
  32 {\r
  33 \r
  34         m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do10MinuteMaintenance");\r
  35 }\r
  36 \r
  37 void PeriodicDBMaintenance::Do30MinuteMaintenance()\r
  38 {\r
  39 \r
  40         m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do30MinuteMaintenance");\r
  41 }\r
  42 \r
  43 void PeriodicDBMaintenance::Do1HourMaintenance()\r
  44 {\r
  45         // recalculate all trust levels - this is CPU instensive\r
  46         // do 1 identity at a time as doing it with 1 UPDATE statement locks that database for the duration\r
  47         SQLite3DB::Statement st=m_db->Prepare("SELECT TargetIdentityID,PeerMessageTrust,PeerTrustListTrust FROM vwCalculatedPeerTrust;");\r
  48         SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=?, PeerTrustListTrust=? WHERE IdentityID=?");\r
  49         st.Step();\r
  50         while(st.RowReturned())\r
  51         {\r
  52                 int identityid=0;\r
  53                 int trust=0;\r
  54                 \r
  55                 st.ResultInt(0,identityid);\r
  56 \r
  57                 upd.Bind(0,identityid);\r
  58                 if(st.ResultNull(1)==false)\r
  59                 {\r
  60                         trust=0;\r
  61                         st.ResultInt(1,trust);\r
  62                         upd.Bind(0,trust);\r
  63                 }\r
  64                 else\r
  65                 {\r
  66                         upd.Bind(0);\r
  67                 }\r
  68                 if(st.ResultNull(2)==false)\r
  69                 {\r
  70                         trust=0;\r
  71                         st.ResultInt(2,trust);\r
  72                         upd.Bind(1,trust);\r
  73                 }\r
  74                 else\r
  75                 {\r
  76                         upd.Bind(1);\r
  77                 }\r
  78                 upd.Bind(2,identityid);\r
  79                 upd.Step();\r
  80                 upd.Reset();\r
  81 \r
  82                 st.Step();\r
  83         }\r
  84 \r
  85         // set null peer trust for identities without a calculated trust\r
  86         st=m_db->Prepare("SELECT IdentityID FROM tblIdentity WHERE IdentityID NOT IN (SELECT TargetIdentityID FROM vwCalculatedPeerTrust);");\r
  87         upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=NULL, PeerTrustListTrust=NULL WHERE IdentityID=?;");\r
  88         st.Step();\r
  89         while(st.RowReturned())\r
  90         {\r
  91                 int identityid=0;\r
  92                 st.ResultInt(0,identityid);\r
  93                 upd.Bind(0,identityid);\r
  94                 upd.Step();\r
  95                 upd.Reset();\r
  96                 st.Step();\r
  97         }\r
  98 \r
  99         m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do1HourMaintenance");\r
 100 }\r
 101 \r
 102 void PeriodicDBMaintenance::Do6HourMaintenance()\r
 103 {\r
 104 \r
 105         // 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
 106         SQLite3DB::Statement st=m_db->Prepare("SELECT MessageID FROM tblMessage WHERE ReplyBoardID NOT IN (SELECT BoardID FROM tblBoard);");\r
 107         SQLite3DB::Statement st2=m_db->Prepare("SELECT BoardID FROM tblMessageBoard WHERE MessageID=?;");\r
 108         SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE MessageID=?;");\r
 109         st.Step();\r
 110         while(st.RowReturned())\r
 111         {\r
 112                 // find a valid boardid for the message\r
 113                 int messageid=0;\r
 114                 int boardid=0;\r
 115 \r
 116                 st.ResultInt(0,messageid);\r
 117 \r
 118                 st2.Bind(0,messageid);\r
 119                 st2.Step();\r
 120                 if(st2.RowReturned())\r
 121                 {\r
 122                         st2.ResultInt(0,boardid);\r
 123                         upd.Bind(0,boardid);\r
 124                         upd.Bind(1,messageid);\r
 125                         upd.Step();\r
 126                         upd.Reset();\r
 127                 }\r
 128                 st2.Reset();\r
 129                 \r
 130                 st.Step();\r
 131         }\r
 132 \r
 133         m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do6HourMaintenance");\r
 134 }\r
 135 \r
 136 void PeriodicDBMaintenance::Do1DayMaintenance()\r
 137 {\r
 138         DateTime date;\r
 139 \r
 140         // delete all puzzles 2 or more days old\r
 141         date.SetToGMTime();\r
 142         date.Add(0,0,0,-2);\r
 143         m_db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 144         m_db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 145 \r
 146         // delete all identities we've never seen and were added more than 20 days ago\r
 147         date.SetToGMTime();\r
 148         date.Add(0,0,0,-20);\r
 149         m_db->Execute("DELETE FROM tblIdentity WHERE LastSeen IS NULL AND DateAdded<'"+date.Format("%Y-%m-%d")+"';");\r
 150 \r
 151         // delete old identity requests - we don't need them anymore\r
 152         date.SetToGMTime();\r
 153         date.Add(0,0,0,-2);\r
 154         m_db->Execute("DELETE FROM tblIdentityRequests WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 155 \r
 156         // delete old board list inserts/requests - we don't need them anymore\r
 157         date.SetToGMTime();\r
 158         date.Add(0,0,0,-2);\r
 159         m_db->Execute("DELETE FROM tblBoardListInserts WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 160         m_db->Execute("DELETE FROM tblBoardListRequests WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 161 \r
 162         // delete old local identity inserts - we don't need them anymore\r
 163         date.SetToGMTime();\r
 164         date.Add(0,0,0,-2);\r
 165         m_db->Execute("DELETE FROM tblLocalIdentityInserts WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 166 \r
 167         // delete old message list inserts/requests - we don't need them anymore\r
 168         date.SetToGMTime();\r
 169         date.Add(0,0,0,-2);\r
 170         m_db->Execute("DELETE FROM tblMessageListInserts WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 171         m_db->Execute("DELETE FROM tblMessageListRequests WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 172 \r
 173         // delete old trust list inserts/requests - we don't need them anymore\r
 174         date.SetToGMTime();\r
 175         date.Add(0,0,0,-2);\r
 176         m_db->Execute("DELETE FROM tblTrustListInserts WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 177         m_db->Execute("DELETE FROM tblTrustListRequests WHERE Day<'"+date.Format("%Y-%m-%d")+"';");\r
 178 \r
 179         // delete trust lists from identities we aren't trusting anymore\r
 180         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
 181 \r
 182         // remove identityid from messages where the identity has been deleted\r
 183         m_db->Execute("UPDATE tblMessage SET IdentityID=NULL WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");\r
 184 \r
 185         // try to re-attach messages from identities that were previously deleted, but have been since re-added\r
 186         // first get the names from messages that have a NULL IdentityID\r
 187         SQLite3DB::Statement st=m_db->Prepare("SELECT FromName FROM tblMessage WHERE IdentityID IS NULL GROUP BY FromName;");\r
 188         st.Step();\r
 189         while(st.RowReturned())\r
 190         {\r
 191                 std::string name="";\r
 192                 std::string namepart="";\r
 193                 std::string publickey="";\r
 194                 int identityid=0;\r
 195                 st.ResultText(0,name);\r
 196 \r
 197                 std::vector<std::string> parts;\r
 198                 StringFunctions::Split(name,"@",parts);\r
 199 \r
 200                 // name can have a @ in it - so reattach all parts except the last which is the key\r
 201                 for(long i=0; i<parts.size()-1; i++)\r
 202                 {\r
 203                         if(i!=0)\r
 204                         {\r
 205                                 namepart+="@";\r
 206                         }\r
 207                         namepart+=parts[i];\r
 208                 }\r
 209 \r
 210                 // find identities with this name\r
 211                 SQLite3DB::Statement st2=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");\r
 212                 st2.Bind(0,namepart);\r
 213                 st2.Step();\r
 214                 while(st2.RowReturned())\r
 215                 {\r
 216                         publickey="";\r
 217                         identityid=0;\r
 218                         st2.ResultText(1,publickey);\r
 219                         // check if public key matches 2nd part\r
 220                         if(parts.size()>1 && publickey.find(parts[1])==4)\r
 221                         {\r
 222                                 // we have the identity - so update the messages table with the identityid\r
 223                                 st2.ResultInt(0,identityid);\r
 224 \r
 225                                 SQLite3DB::Statement st3=m_db->Prepare("UPDATE tblMessage SET IdentityID=? WHERE FromName=? AND IdentityID IS NULL;");\r
 226                                 st3.Bind(0,identityid);\r
 227                                 st3.Bind(1,name);\r
 228                                 st3.Step();\r
 229                         }\r
 230                         st2.Step();\r
 231                 }\r
 232 \r
 233                 st.Step();\r
 234         }\r
 235 \r
 236         // delete single use identities that are older than 7 days\r
 237         date.SetToGMTime();\r
 238         date.Add(0,0,0,-7);\r
 239         st=m_db->Prepare("DELETE FROM tblIdentity WHERE SingleUse='true' AND DateAdded<?;");\r
 240         st.Bind(0,date.Format("%Y-%m-%d %H:%M:%S"));\r
 241         st.Step();\r
 242 \r
 243         // delete local single use identities that are older than 7 days\r
 244         date.SetToGMTime();\r
 245         date.Add(0,0,0,-7);\r
 246         st=m_db->Prepare("DELETE FROM tblLocalIdentity WHERE SingleUse='true' AND DateCreated<?;");\r
 247         st.Bind(0,date.Format("%Y-%m-%d %H:%M:%S"));\r
 248         st.Step();\r
 249 \r
 250         // delete old messages\r
 251         date.SetToGMTime();\r
 252         date.Add(0,0,0,-m_deletemessagesolderthan);\r
 253         st=m_db->Prepare("DELETE FROM tblMessage WHERE MessageDate<?;");\r
 254         st.Bind(0,date.Format("%Y-%m-%d"));\r
 255         st.Step();\r
 256 \r
 257         // delete tblIdentityTrust for local identities and identities that have been deleted\r
 258         m_db->Execute("DELETE FROM tblIdentityTrust WHERE LocalIdentityID NOT IN (SELECT LocalIdentityID FROM tblLocalIdentity);");\r
 259         m_db->Execute("DELETE FROM tblIdentityTrust WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");\r
 260 \r
 261         m_log->WriteLog(LogFile::LOGLEVEL_DEBUG,"PeriodicDBMaintenance::Do1DayMaintenance");\r
 262 \r
 263 }\r
 264 \r
 265 void PeriodicDBMaintenance::Process()\r
 266 {\r
 267         DateTime now;\r
 268         now.SetToGMTime();\r
 269         \r
 270         if(m_check10mins<=(now-((1.0/1440.0)*10.0)))\r
 271         {\r
 272                 Do10MinuteMaintenance();\r
 273                 m_check10mins=now;\r
 274         }\r
 275         if(m_check30mins<=(now-((1.0/1440.0)*30.0)))\r
 276         {\r
 277                 Do30MinuteMaintenance();\r
 278                 m_check30mins=now;\r
 279         }\r
 280         if(m_check1hour<=(now-(1.0/24.0)))\r
 281         {\r
 282                 Do1HourMaintenance();\r
 283                 m_check1hour=now;\r
 284         }\r
 285         if(m_check6hours<=(now-(1.0/4.0)))\r
 286         {\r
 287                 Do6HourMaintenance();\r
 288                 m_check6hours=now;\r
 289         }\r
 290         if(m_check1day<=(now-(1.0)))\r
 291         {\r
 292                 Do1DayMaintenance();\r
 293                 m_check1day=now;\r
 294         }\r
 295 \r
 296 }\r
 297 \r
 298 void PeriodicDBMaintenance::RegisterWithThread(FreenetMasterThread *thread)\r
 299 {\r
 300         thread->RegisterPeriodicProcessor(this);\r
 301 }\r