src/dbmaintenancethread.cpp

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