src/dbmaintenancethread.cpp

   1 #include "../include/dbmaintenancethread.h"\r
   2 #include "../include/stringfunctions.h"\r
   3 #include "../include/option.h"\r
   4 #include "../include/threadbuilder.h"\r
   5 \r
   6 #include <Poco/Timestamp.h>\r
   7 #include <Poco/Timespan.h>\r
   8 #include <Poco/DateTimeFormatter.h>\r
   9 #include <Poco/Thread.h>\r
  10 \r
  11 DBMaintenanceThread::DBMaintenanceThread()\r
  12 {\r
  13         // move last maintenance times back so they will all run soon\r
  14         m_last10minute=Poco::Timestamp();\r
  15         m_last30minute=Poco::Timestamp();\r
  16         m_last30minute-=Poco::Timespan(0,0,11,0,0);\r
  17         m_last1hour=Poco::Timestamp();\r
  18         m_last1hour-=Poco::Timespan(0,0,49,0,0);\r
  19         m_last6hour=Poco::Timestamp();\r
  20         m_last6hour-=Poco::Timespan(0,5,42,0,0);\r
  21         m_last1day=Poco::Timestamp();\r
  22         m_last1day-=Poco::Timespan(0,23,51,0,0);\r
  23 \r
  24         m_deletemessagesolderthan=180;\r
  25         std::string tempval="180";\r
  26         Option::Instance()->Get("DeleteMessagesOlderThan",tempval);\r
  27         StringFunctions::Convert(tempval,m_deletemessagesolderthan);\r
  28 \r
  29         m_messagedownloadmaxdaysbackward=5;\r
  30         tempval="5";\r
  31         Option::Instance()->Get("MessageDownloadMaxDaysBackward",tempval);\r
  32         StringFunctions::Convert(tempval,m_messagedownloadmaxdaysbackward);\r
  33 \r
  34 }\r
  35 \r
  36 \r
  37 void DBMaintenanceThread::Do10MinuteMaintenance()\r
  38 {\r
  39 \r
  40         ThreadBuilder tb;\r
  41         SQLite3DB::Statement boardst=m_db->Prepare("SELECT BoardID FROM tblBoard WHERE Forum='true';");\r
  42         // select messages for a board that aren't in a thread\r
  43         SQLite3DB::Statement selectst=m_db->Prepare("SELECT tblMessage.MessageID FROM tblMessage \\r
  44                                                                                                 INNER JOIN tblMessageBoard ON tblMessage.MessageID=tblMessageBoard.MessageID\\r
  45                                                                                                 LEFT JOIN tblThreadPost ON tblMessage.MessageID=tblThreadPost.MessageID \\r
  46                                                                                                 LEFT JOIN tblThread ON tblThreadPost.ThreadID=tblThread.ThreadID\\r
  47                                                                                                 WHERE tblMessageBoard.BoardID=? AND tblThread.BoardID IS NULL;");\r
  48 \r
  49         boardst.Step();\r
  50         while(boardst.RowReturned())\r
  51         {\r
  52                 int boardid=-1;\r
  53 \r
  54                 boardst.ResultInt(0,boardid);\r
  55 \r
  56                 selectst.Bind(0,boardid);\r
  57                 selectst.Step();\r
  58 \r
  59                 while(selectst.RowReturned())\r
  60                 {\r
  61                         int messageid=-1;\r
  62 \r
  63                         selectst.ResultInt(0,messageid);\r
  64 \r
  65                         tb.Build(messageid,boardid,true);\r
  66 \r
  67                         selectst.Step();\r
  68                 }\r
  69                 selectst.Reset();\r
  70 \r
  71                 boardst.Step();\r
  72         }\r
  73 \r
  74         // now rebuild threads where the message has been deleted\r
  75         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
  76         st.Step();\r
  77         while(st.RowReturned())\r
  78         {\r
  79                 int messageid=-1;\r
  80                 int boardid=-1;\r
  81 \r
  82                 st.ResultInt(0,messageid);\r
  83                 st.ResultInt(1,boardid);\r
  84 \r
  85                 tb.Build(messageid,boardid,true);\r
  86 \r
  87                 st.Step();\r
  88         }\r
  89 \r
  90         m_log->debug("PeriodicDBMaintenance::Do10MinuteMaintenance");\r
  91 }\r
  92 \r
  93 void DBMaintenanceThread::Do30MinuteMaintenance()\r
  94 {\r
  95         // UNCOMMENT method in run when code is placed here\r
  96         m_log->debug("PeriodicDBMaintenance::Do30MinuteMaintenance");\r
  97 }\r
  98 \r
  99 void DBMaintenanceThread::Do1HourMaintenance()\r
 100 {\r
 101         // recalculate all trust levels - this is CPU instensive\r
 102         // do 1 identity at a time as doing it with 1 UPDATE statement locks that database for the duration\r
 103         SQLite3DB::Statement st=m_db->Prepare("SELECT TargetIdentityID,PeerMessageTrust,PeerTrustListTrust FROM vwCalculatedPeerTrust;");\r
 104         SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=?, PeerTrustListTrust=? WHERE IdentityID=?");\r
 105         st.Step();\r
 106         while(st.RowReturned())\r
 107         {\r
 108                 int identityid=0;\r
 109                 int trust=0;\r
 110                 \r
 111                 st.ResultInt(0,identityid);\r
 112 \r
 113                 upd.Bind(0,identityid);\r
 114                 if(st.ResultNull(1)==false)\r
 115                 {\r
 116                         trust=0;\r
 117                         st.ResultInt(1,trust);\r
 118                         upd.Bind(0,trust);\r
 119                 }\r
 120                 else\r
 121                 {\r
 122                         upd.Bind(0);\r
 123                 }\r
 124                 if(st.ResultNull(2)==false)\r
 125                 {\r
 126                         trust=0;\r
 127                         st.ResultInt(2,trust);\r
 128                         upd.Bind(1,trust);\r
 129                 }\r
 130                 else\r
 131                 {\r
 132                         upd.Bind(1);\r
 133                 }\r
 134                 upd.Bind(2,identityid);\r
 135                 upd.Step();\r
 136                 upd.Reset();\r
 137 \r
 138                 st.Step();\r
 139         }\r
 140 \r
 141         // set null peer trust for identities without a calculated trust\r
 142         st=m_db->Prepare("SELECT IdentityID FROM tblIdentity WHERE IdentityID NOT IN (SELECT TargetIdentityID FROM vwCalculatedPeerTrust);");\r
 143         upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=NULL, PeerTrustListTrust=NULL WHERE IdentityID=?;");\r
 144         st.Step();\r
 145         while(st.RowReturned())\r
 146         {\r
 147                 int identityid=0;\r
 148                 st.ResultInt(0,identityid);\r
 149                 upd.Bind(0,identityid);\r
 150                 upd.Step();\r
 151                 upd.Reset();\r
 152                 st.Step();\r
 153         }\r
 154 \r
 155         // insert all identities not in trust list already\r
 156         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
 157 \r
 158         m_log->debug("PeriodicDBMaintenance::Do1HourMaintenance");\r
 159 }\r
 160 \r
 161 void DBMaintenanceThread::Do6HourMaintenance()\r
 162 {\r
 163 \r
 164         // 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
 165         SQLite3DB::Statement st=m_db->Prepare("SELECT MessageID FROM tblMessage WHERE ReplyBoardID NOT IN (SELECT BoardID FROM tblBoard);");\r
 166         SQLite3DB::Statement st2=m_db->Prepare("SELECT BoardID FROM tblMessageBoard WHERE MessageID=?;");\r
 167         SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE MessageID=?;");\r
 168         st.Step();\r
 169         while(st.RowReturned())\r
 170         {\r
 171                 // find a valid boardid for the message\r
 172                 int messageid=0;\r
 173                 int boardid=0;\r
 174 \r
 175                 st.ResultInt(0,messageid);\r
 176 \r
 177                 st2.Bind(0,messageid);\r
 178                 st2.Step();\r
 179                 if(st2.RowReturned())\r
 180                 {\r
 181                         st2.ResultInt(0,boardid);\r
 182                         upd.Bind(0,boardid);\r
 183                         upd.Bind(1,messageid);\r
 184                         upd.Step();\r
 185                         upd.Reset();\r
 186                 }\r
 187                 st2.Reset();\r
 188                 \r
 189                 st.Step();\r
 190         }\r
 191 \r
 192         m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance");\r
 193 }\r
 194 \r
 195 void DBMaintenanceThread::Do1DayMaintenance()\r
 196 {\r
 197         Poco::DateTime date;\r
 198 \r
 199         // delete all puzzles 2 or more days old\r
 200         date=Poco::Timestamp();\r
 201         date-=Poco::Timespan(2,0,0,0,0);\r
 202         m_db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 203         m_db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 204 \r
 205         // delete all identities we've never seen and were added more than 20 days ago\r
 206         date=Poco::Timestamp();\r
 207         date-=Poco::Timespan(20,0,0,0,0);\r
 208         m_db->Execute("DELETE FROM tblIdentity WHERE LastSeen IS NULL AND DateAdded<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 209 \r
 210         // delete old identity requests - we don't need them anymore\r
 211         date=Poco::Timestamp();\r
 212         date-=Poco::Timespan(2,0,0,0,0);\r
 213         m_db->Execute("DELETE FROM tblIdentityRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 214 \r
 215         // delete old board list inserts/requests - we don't need them anymore\r
 216         date=Poco::Timestamp();\r
 217         date-=Poco::Timespan(2,0,0,0,0);\r
 218         m_db->Execute("DELETE FROM tblBoardListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 219         m_db->Execute("DELETE FROM tblBoardListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 220 \r
 221         // delete old local identity inserts - we don't need them anymore\r
 222         date=Poco::Timestamp();\r
 223         date-=Poco::Timespan(2,0,0,0,0);\r
 224         m_db->Execute("DELETE FROM tblLocalIdentityInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 225 \r
 226         // delete old message list inserts/requests - we don't need them anymore\r
 227         date=Poco::Timestamp();\r
 228         date-=Poco::Timespan(2,0,0,0,0);\r
 229         m_db->Execute("DELETE FROM tblMessageListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 230         m_db->Execute("DELETE FROM tblMessageListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 231 \r
 232         // delete old trust list inserts/requests - we don't need them anymore\r
 233         date=Poco::Timestamp();\r
 234         date-=Poco::Timespan(2,0,0,0,0);\r
 235         m_db->Execute("DELETE FROM tblTrustListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 236         m_db->Execute("DELETE FROM tblTrustListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 237 \r
 238         // delete trust lists from identities we aren't trusting anymore\r
 239         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
 240 \r
 241         // remove identityid from messages where the identity has been deleted\r
 242         m_db->Execute("UPDATE tblMessage SET IdentityID=NULL WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");\r
 243 \r
 244         // try to re-attach messages from identities that were previously deleted, but have been since re-added\r
 245         // first get the names from messages that have a NULL IdentityID\r
 246         SQLite3DB::Statement st=m_db->Prepare("SELECT FromName FROM tblMessage WHERE IdentityID IS NULL GROUP BY FromName;");\r
 247         st.Step();\r
 248         while(st.RowReturned())\r
 249         {\r
 250                 std::string name="";\r
 251                 std::string namepart="";\r
 252                 std::string publickey="";\r
 253                 int identityid=0;\r
 254                 st.ResultText(0,name);\r
 255 \r
 256                 std::vector<std::string> parts;\r
 257                 StringFunctions::Split(name,"@",parts);\r
 258 \r
 259                 // name can have a @ in it - so reattach all parts except the last which is the key\r
 260                 for(long i=0; i<parts.size()-1; i++)\r
 261                 {\r
 262                         if(i!=0)\r
 263                         {\r
 264                                 namepart+="@";\r
 265                         }\r
 266                         namepart+=parts[i];\r
 267                 }\r
 268 \r
 269                 // find identities with this name\r
 270                 SQLite3DB::Statement st2=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");\r
 271                 st2.Bind(0,namepart);\r
 272                 st2.Step();\r
 273                 while(st2.RowReturned())\r
 274                 {\r
 275                         publickey="";\r
 276                         identityid=0;\r
 277                         st2.ResultText(1,publickey);\r
 278                         // check if public key matches 2nd part\r
 279                         if(parts.size()>1 && publickey.find(parts[1])==4)\r
 280                         {\r
 281                                 // we have the identity - so update the messages table with the identityid\r
 282                                 st2.ResultInt(0,identityid);\r
 283 \r
 284                                 SQLite3DB::Statement st3=m_db->Prepare("UPDATE tblMessage SET IdentityID=? WHERE FromName=? AND IdentityID IS NULL;");\r
 285                                 st3.Bind(0,identityid);\r
 286                                 st3.Bind(1,name);\r
 287                                 st3.Step();\r
 288                         }\r
 289                         st2.Step();\r
 290                 }\r
 291 \r
 292                 st.Step();\r
 293         }\r
 294 \r
 295         // delete single use identities that are older than 7 days\r
 296         date=Poco::Timestamp();\r
 297         date-=Poco::Timespan(7,0,0,0,0);\r
 298         st=m_db->Prepare("DELETE FROM tblIdentity WHERE SingleUse='true' AND DateAdded<?;");\r
 299         st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));\r
 300         st.Step();\r
 301 \r
 302         // delete local single use identities that are older than 7 days\r
 303         date=Poco::Timestamp();\r
 304         date-=Poco::Timespan(7,0,0,0,0);\r
 305         st=m_db->Prepare("DELETE FROM tblLocalIdentity WHERE SingleUse='true' AND DateCreated<?;");\r
 306         st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));\r
 307         st.Step();\r
 308 \r
 309         // delete old messages\r
 310         date=Poco::Timestamp();\r
 311         date-=Poco::Timespan(m_deletemessagesolderthan,0,0,0,0);\r
 312         m_log->trace("PeriodicDBMaintenance::Do1DayMaintenance deleting messages prior to "+Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));\r
 313         st=m_db->Prepare("DELETE FROM tblMessage WHERE MessageDate<?;");\r
 314         st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));\r
 315         st.Step();\r
 316 \r
 317         // delete old message requests\r
 318         date=Poco::Timestamp();\r
 319         date-=Poco::Timespan(m_messagedownloadmaxdaysbackward,0,0,0,0);\r
 320         st=m_db->Prepare("DELETE FROM tblMessageRequests WHERE Day<?;");\r
 321         st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));\r
 322         st.Step();\r
 323 \r
 324         // delete tblIdentityTrust for local identities and identities that have been deleted\r
 325         m_db->Execute("DELETE FROM tblIdentityTrust WHERE LocalIdentityID NOT IN (SELECT LocalIdentityID FROM tblLocalIdentity);");\r
 326         m_db->Execute("DELETE FROM tblIdentityTrust WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");\r
 327 \r
 328         m_log->debug("PeriodicDBMaintenance::Do1DayMaintenance");\r
 329 \r
 330 }\r
 331 \r
 332 void DBMaintenanceThread::run()\r
 333 {\r
 334         m_log->debug("DBMaintenanceThread::run thread started.");\r
 335 \r
 336         Poco::DateTime now;\r
 337         int i=0;\r
 338 \r
 339         do\r
 340         {\r
 341                 now=Poco::Timestamp();\r
 342 \r
 343                 if((m_last10minute+Poco::Timespan(0,0,10,0,0))<=now)\r
 344                 {\r
 345                         Do10MinuteMaintenance();\r
 346                         m_last10minute=Poco::Timestamp();\r
 347                 }\r
 348                 /*\r
 349                 if((m_last30minute+Poco::Timespan(0,0,30,0,0))<=now)\r
 350                 {\r
 351                         Do30MinuteMaintenance();\r
 352                         m_last30minute=Poco::Timestamp();\r
 353                 }\r
 354                 */\r
 355                 if((m_last1hour+Poco::Timespan(0,1,0,0,0))<=now)\r
 356                 {\r
 357                         Do1HourMaintenance();\r
 358                         m_last1hour=Poco::Timestamp();\r
 359                 }\r
 360                 if((m_last6hour+Poco::Timespan(0,6,0,0,0))<=now)\r
 361                 {\r
 362                         Do6HourMaintenance();\r
 363                         m_last6hour=Poco::Timestamp();\r
 364                 }\r
 365                 if((m_last1day+Poco::Timespan(1,0,0,0,0))<=now)\r
 366                 {\r
 367                         Do1DayMaintenance();\r
 368                         m_last1day=Poco::Timestamp();\r
 369                 }\r
 370 \r
 371                 i=0;\r
 372                 while(i++<5 && !IsCancelled())\r
 373                 {\r
 374                         Poco::Thread::sleep(1000);\r
 375                 }\r
 376 \r
 377         }while(!IsCancelled());\r
 378 \r
 379         m_log->debug("DBMaintenanceThread::run thread exiting.");\r
 380 }\r