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