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