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
9 #include <Poco/Timestamp.h>
\r
10 #include <Poco/Timespan.h>
\r
11 #include <Poco/DateTimeFormatter.h>
\r
12 #include <Poco/Thread.h>
\r
14 DBMaintenanceThread::DBMaintenanceThread()
\r
16 // move last maintenance times back so they will all run soon
\r
17 m_last10minute=Poco::Timestamp();
\r
18 m_last30minute=Poco::Timestamp();
\r
19 m_last30minute-=Poco::Timespan(0,0,11,0,0);
\r
20 m_last1hour=Poco::Timestamp();
\r
21 m_last1hour-=Poco::Timespan(0,0,49,0,0);
\r
22 m_last6hour=Poco::Timestamp();
\r
23 m_last6hour-=Poco::Timespan(0,5,42,0,0);
\r
24 m_last1day=Poco::Timestamp();
\r
25 m_last1day-=Poco::Timespan(0,23,51,0,0);
\r
29 void DBMaintenanceThread::Do10MinuteMaintenance()
\r
31 std::vector<std::pair<long,long> > m_unthreadedmessages;
\r
32 Option option(m_db);
\r
34 option.Get("LogLevel",ll);
\r
36 ThreadBuilder tb(m_db);
\r
37 SQLite3DB::Statement boardst=m_db->Prepare("SELECT BoardID FROM tblBoard WHERE Forum='true';");
\r
38 // select messages for a board that aren't in a thread
\r
39 // This query was causing the db to be locked and a journal file created.
\r
40 // build a list of boards and messageids and then use that instead of keeping the query in use
\r
41 SQLite3DB::Statement selectst=m_db->Prepare("SELECT tblMessage.MessageID FROM tblMessage \
\r
42 INNER JOIN tblMessageBoard ON tblMessage.MessageID=tblMessageBoard.MessageID \
\r
43 LEFT JOIN tblThreadPost ON tblMessage.MessageID=tblThreadPost.MessageID \
\r
44 WHERE tblMessageBoard.BoardID=? AND tblThreadPost.MessageID IS NULL;");
\r
47 while(boardst.RowReturned())
\r
50 boardst.ResultInt(0,boardid);
\r
53 selectst.Bind(0,boardid);
\r
56 while(selectst.RowReturned())
\r
60 selectst.ResultInt(0,messageid);
\r
62 m_unthreadedmessages.push_back(std::pair<long,long>(boardid,messageid));
\r
68 selectst.Finalize();
\r
71 for(std::vector<std::pair<long,long> >::iterator i=m_unthreadedmessages.begin(); i!=m_unthreadedmessages.end(); i++)
\r
73 tb.Build((*i).second,(*i).first,true);
\r
77 while(boardst.RowReturned())
\r
81 boardst.ResultInt(0,boardid);
\r
83 selectst.Bind(0,boardid);
\r
86 while(selectst.RowReturned())
\r
90 selectst.ResultInt(0,messageid);
\r
92 tb.Build(messageid,boardid,true);
\r
103 // now rebuild threads where the message has been deleted
\r
104 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
106 while(st.RowReturned())
\r
111 st.ResultInt(0,messageid);
\r
112 st.ResultInt(1,boardid);
\r
114 tb.Build(messageid,boardid,true);
\r
119 // delete threads that have no messages
\r
120 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
122 // TODO - remove after corruption issue fixed
\r
125 std::string dbres=TestDBIntegrity(m_db);
\r
126 m_log->trace("DBMaintenanceThread::Do10MinuteMaintenance() end TestDBIntegrity returned "+dbres);
\r
129 m_db->Execute("REINDEX;");
\r
130 dbres=TestDBIntegrity(m_db);
\r
131 m_log->trace("DBMaintenanceThread::Do10MinuteMaintenenace() end after reindex returned "+dbres);
\r
135 m_log->debug("PeriodicDBMaintenance::Do10MinuteMaintenance");
\r
138 void DBMaintenanceThread::Do30MinuteMaintenance()
\r
140 // UNCOMMENT method in run when code is placed here
\r
141 m_log->debug("PeriodicDBMaintenance::Do30MinuteMaintenance");
\r
144 void DBMaintenanceThread::Do1HourMaintenance()
\r
147 m_db->Execute("BEGIN;");
\r
148 // recalculate all trust levels - this is CPU instensive
\r
149 // do 1 identity at a time as doing it with 1 UPDATE statement locks that database for the duration
\r
150 SQLite3DB::Statement st=m_db->Prepare("SELECT TargetIdentityID,PeerMessageTrust,PeerTrustListTrust FROM vwCalculatedPeerTrust;");
\r
151 SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=?, PeerTrustListTrust=? WHERE IdentityID=?");
\r
153 while(st.RowReturned())
\r
158 st.ResultInt(0,identityid);
\r
160 upd.Bind(0,identityid);
\r
161 if(st.ResultNull(1)==false)
\r
164 st.ResultInt(1,trust);
\r
171 if(st.ResultNull(2)==false)
\r
174 st.ResultInt(2,trust);
\r
181 upd.Bind(2,identityid);
\r
188 // set null peer trust for identities without a calculated trust
\r
189 st=m_db->Prepare("SELECT IdentityID FROM tblIdentity WHERE IdentityID NOT IN (SELECT TargetIdentityID FROM vwCalculatedPeerTrust);");
\r
190 upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=NULL, PeerTrustListTrust=NULL WHERE IdentityID=?;");
\r
192 while(st.RowReturned())
\r
195 st.ResultInt(0,identityid);
\r
196 upd.Bind(0,identityid);
\r
205 // insert all identities not in trust list already
\r
206 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
208 m_db->Execute("COMMIT;");
\r
210 m_log->debug("PeriodicDBMaintenance::Do1HourMaintenance");
\r
213 void DBMaintenanceThread::Do6HourMaintenance()
\r
216 m_db->Execute("BEGIN;");
\r
218 // 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
219 SQLite3DB::Statement st=m_db->Prepare("SELECT MessageID FROM tblMessage WHERE ReplyBoardID NOT IN (SELECT BoardID FROM tblBoard);");
\r
220 SQLite3DB::Statement st2=m_db->Prepare("SELECT BoardID FROM tblMessageBoard WHERE MessageID=?;");
\r
221 SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE MessageID=?;");
\r
223 while(st.RowReturned())
\r
225 // find a valid boardid for the message
\r
229 st.ResultInt(0,messageid);
\r
231 st2.Bind(0,messageid);
\r
233 if(st2.RowReturned())
\r
235 st2.ResultInt(0,boardid);
\r
236 upd.Bind(0,boardid);
\r
237 upd.Bind(1,messageid);
\r
250 m_db->Execute("COMMIT;");
\r
252 m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance");
\r
255 void DBMaintenanceThread::Do1DayMaintenance()
\r
257 Poco::DateTime date;
\r
259 m_db->Execute("BEGIN;");
\r
261 // delete all puzzles 2 or more days old
\r
262 date=Poco::Timestamp();
\r
263 date-=Poco::Timespan(2,0,0,0,0);
\r
264 m_db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
265 m_db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
267 // delete all identities we've never seen and were added more than 20 days ago
\r
268 date=Poco::Timestamp();
\r
269 date-=Poco::Timespan(20,0,0,0,0);
\r
270 m_db->Execute("DELETE FROM tblIdentity WHERE LastSeen IS NULL AND DateAdded<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
272 // delete old identity requests - we don't need them anymore
\r
273 date=Poco::Timestamp();
\r
274 date-=Poco::Timespan(2,0,0,0,0);
\r
275 m_db->Execute("DELETE FROM tblIdentityRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
277 // delete old board list inserts/requests - we don't need them anymore
\r
278 date=Poco::Timestamp();
\r
279 date-=Poco::Timespan(2,0,0,0,0);
\r
280 m_db->Execute("DELETE FROM tblBoardListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
281 m_db->Execute("DELETE FROM tblBoardListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
283 // delete old local identity inserts - we don't need them anymore
\r
284 date=Poco::Timestamp();
\r
285 date-=Poco::Timespan(2,0,0,0,0);
\r
286 m_db->Execute("DELETE FROM tblLocalIdentityInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
288 // delete old message list inserts/requests - we don't need them anymore
\r
289 date=Poco::Timestamp();
\r
290 date-=Poco::Timespan(2,0,0,0,0);
\r
291 m_db->Execute("DELETE FROM tblMessageListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
292 m_db->Execute("DELETE FROM tblMessageListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
294 // delete old trust list inserts/requests - we don't need them anymore
\r
295 date=Poco::Timestamp();
\r
296 date-=Poco::Timespan(2,0,0,0,0);
\r
297 m_db->Execute("DELETE FROM tblTrustListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
298 m_db->Execute("DELETE FROM tblTrustListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
300 // delete trust lists from identities we aren't trusting anymore
\r
301 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
303 // remove identityid from messages where the identity has been deleted
\r
304 m_db->Execute("UPDATE tblMessage SET IdentityID=NULL WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");
\r
306 // try to re-attach messages from identities that were previously deleted, but have been since re-added
\r
307 // first get the names from messages that have a NULL IdentityID
\r
308 SQLite3DB::Statement st=m_db->Prepare("SELECT FromName FROM tblMessage WHERE IdentityID IS NULL GROUP BY FromName;");
\r
309 SQLite3DB::Statement findst=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");
\r
311 while(st.RowReturned())
\r
313 std::string name="";
\r
314 std::string namepart="";
\r
315 std::string publickey="";
\r
317 st.ResultText(0,name);
\r
319 std::vector<std::string> parts;
\r
320 StringFunctions::Split(name,"@",parts);
\r
322 // name can have a @ in it - so reattach all parts except the last which is the key
\r
323 for(long i=0; i<parts.size()-1; i++)
\r
329 namepart+=parts[i];
\r
332 // find identities with this name
\r
333 findst.Bind(0,namepart);
\r
335 while(findst.RowReturned())
\r
339 findst.ResultText(1,publickey);
\r
340 // check if public key matches 2nd part
\r
341 if(parts.size()>1 && publickey.find(parts[1])==4)
\r
343 // we have the identity - so update the messages table with the identityid
\r
344 findst.ResultInt(0,identityid);
\r
346 SQLite3DB::Statement st3=m_db->Prepare("UPDATE tblMessage SET IdentityID=? WHERE FromName=? AND IdentityID IS NULL;");
\r
347 st3.Bind(0,identityid);
\r
358 // delete single use identities that are older than 7 days
\r
359 date=Poco::Timestamp();
\r
360 date-=Poco::Timespan(7,0,0,0,0);
\r
361 st=m_db->Prepare("DELETE FROM tblIdentity WHERE SingleUse='true' AND DateAdded<?;");
\r
362 st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));
\r
365 // delete local single use identities that are older than 7 days
\r
366 date=Poco::Timestamp();
\r
367 date-=Poco::Timespan(7,0,0,0,0);
\r
368 st=m_db->Prepare("DELETE FROM tblLocalIdentity WHERE SingleUse='true' AND DateCreated<?;");
\r
369 st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));
\r
372 // delete old messages
\r
373 date=Poco::Timestamp();
\r
374 date-=Poco::Timespan(m_deletemessagesolderthan,0,0,0,0);
\r
375 m_log->trace("PeriodicDBMaintenance::Do1DayMaintenance deleting messages prior to "+Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));
\r
376 st=m_db->Prepare("DELETE FROM tblMessage WHERE MessageDate<?;");
\r
377 st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));
\r
380 // delete old message requests
\r
381 date=Poco::Timestamp();
\r
382 date-=Poco::Timespan(m_messagedownloadmaxdaysbackward,0,0,0,0);
\r
383 st=m_db->Prepare("DELETE FROM tblMessageRequests WHERE Day<?;");
\r
384 st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));
\r
387 // delete tblIdentityTrust for local identities and identities that have been deleted
\r
388 m_db->Execute("DELETE FROM tblIdentityTrust WHERE LocalIdentityID NOT IN (SELECT LocalIdentityID FROM tblLocalIdentity);");
\r
389 m_db->Execute("DELETE FROM tblIdentityTrust WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");
\r
392 // reduce failure count for each identity
\r
393 m_db->Execute("UPDATE tblIdentity SET FailureCount=0 WHERE FailureCount<(SELECT OptionValue FROM tblOption WHERE Option='FailureCountReduction');");
\r
394 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
399 m_db->Execute("COMMIT;");
\r
401 m_log->debug("PeriodicDBMaintenance::Do1DayMaintenance");
\r
405 void DBMaintenanceThread::run()
\r
407 m_log->debug("DBMaintenanceThread::run thread started.");
\r
410 Option option(m_db);
\r
411 std::string tempval("");
\r
413 m_deletemessagesolderthan=180;
\r
415 option.Get("DeleteMessagesOlderThan",tempval);
\r
416 StringFunctions::Convert(tempval,m_deletemessagesolderthan);
\r
418 m_messagedownloadmaxdaysbackward=5;
\r
420 option.Get("MessageDownloadMaxDaysBackward",tempval);
\r
421 StringFunctions::Convert(tempval,m_messagedownloadmaxdaysbackward);
\r
424 Poco::DateTime now;
\r
429 now=Poco::Timestamp();
\r
431 if((m_last10minute+Poco::Timespan(0,0,10,0,0))<=now)
\r
433 Do10MinuteMaintenance();
\r
434 m_last10minute=Poco::Timestamp();
\r
437 if((m_last30minute+Poco::Timespan(0,0,30,0,0))<=now)
\r
439 Do30MinuteMaintenance();
\r
440 m_last30minute=Poco::Timestamp();
\r
443 if((m_last1hour+Poco::Timespan(0,1,0,0,0))<=now)
\r
445 Do1HourMaintenance();
\r
446 m_last1hour=Poco::Timestamp();
\r
448 if((m_last6hour+Poco::Timespan(0,6,0,0,0))<=now)
\r
450 Do6HourMaintenance();
\r
451 m_last6hour=Poco::Timestamp();
\r
453 if((m_last1day+Poco::Timespan(1,0,0,0,0))<=now)
\r
455 Do1DayMaintenance();
\r
456 m_last1day=Poco::Timestamp();
\r
460 while(i++<5 && !IsCancelled())
\r
462 Poco::Thread::sleep(1000);
\r
465 }while(!IsCancelled());
\r
467 m_log->debug("DBMaintenanceThread::run thread exiting.");
\r