1 #include "../include/dbmaintenancethread.h"
\r
2 #include "../include/stringfunctions.h"
\r
3 #include "../include/option.h"
\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
10 DBMaintenanceThread::DBMaintenanceThread()
\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
23 std::string tempval="180";
\r
24 Option::Instance()->Get("DeleteMessagesOlderThan",tempval);
\r
25 StringFunctions::Convert(tempval,m_deletemessagesolderthan);
\r
30 void DBMaintenanceThread::Do10MinuteMaintenance()
\r
33 m_log->debug("PeriodicDBMaintenance::Do10MinuteMaintenance");
\r
36 void DBMaintenanceThread::Do30MinuteMaintenance()
\r
39 m_log->debug("PeriodicDBMaintenance::Do30MinuteMaintenance");
\r
42 void DBMaintenanceThread::Do1HourMaintenance()
\r
44 // recalculate all trust levels - this is CPU instensive
\r
45 // do 1 identity at a time as doing it with 1 UPDATE statement locks that database for the duration
\r
46 SQLite3DB::Statement st=m_db->Prepare("SELECT TargetIdentityID,PeerMessageTrust,PeerTrustListTrust FROM vwCalculatedPeerTrust;");
\r
47 SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=?, PeerTrustListTrust=? WHERE IdentityID=?");
\r
49 while(st.RowReturned())
\r
54 st.ResultInt(0,identityid);
\r
56 upd.Bind(0,identityid);
\r
57 if(st.ResultNull(1)==false)
\r
60 st.ResultInt(1,trust);
\r
67 if(st.ResultNull(2)==false)
\r
70 st.ResultInt(2,trust);
\r
77 upd.Bind(2,identityid);
\r
84 // set null peer trust for identities without a calculated trust
\r
85 st=m_db->Prepare("SELECT IdentityID FROM tblIdentity WHERE IdentityID NOT IN (SELECT TargetIdentityID FROM vwCalculatedPeerTrust);");
\r
86 upd=m_db->Prepare("UPDATE tblIdentity SET PeerMessageTrust=NULL, PeerTrustListTrust=NULL WHERE IdentityID=?;");
\r
88 while(st.RowReturned())
\r
91 st.ResultInt(0,identityid);
\r
92 upd.Bind(0,identityid);
\r
98 m_log->debug("PeriodicDBMaintenance::Do1HourMaintenance");
\r
101 void DBMaintenanceThread::Do6HourMaintenance()
\r
104 // 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
105 SQLite3DB::Statement st=m_db->Prepare("SELECT MessageID FROM tblMessage WHERE ReplyBoardID NOT IN (SELECT BoardID FROM tblBoard);");
\r
106 SQLite3DB::Statement st2=m_db->Prepare("SELECT BoardID FROM tblMessageBoard WHERE MessageID=?;");
\r
107 SQLite3DB::Statement upd=m_db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE MessageID=?;");
\r
109 while(st.RowReturned())
\r
111 // find a valid boardid for the message
\r
115 st.ResultInt(0,messageid);
\r
117 st2.Bind(0,messageid);
\r
119 if(st2.RowReturned())
\r
121 st2.ResultInt(0,boardid);
\r
122 upd.Bind(0,boardid);
\r
123 upd.Bind(1,messageid);
\r
132 m_log->debug("PeriodicDBMaintenance::Do6HourMaintenance");
\r
135 void DBMaintenanceThread::Do1DayMaintenance()
\r
137 Poco::DateTime date;
\r
139 // delete all puzzles 2 or more days old
\r
140 date=Poco::Timestamp();
\r
141 date-=Poco::Timespan(2,0,0,0,0);
\r
142 m_db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
143 m_db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
145 // delete all identities we've never seen and were added more than 20 days ago
\r
146 date=Poco::Timestamp();
\r
147 date-=Poco::Timespan(20,0,0,0,0);
\r
148 m_db->Execute("DELETE FROM tblIdentity WHERE LastSeen IS NULL AND DateAdded<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
150 // delete old identity requests - we don't need them anymore
\r
151 date=Poco::Timestamp();
\r
152 date-=Poco::Timespan(2,0,0,0,0);
\r
153 m_db->Execute("DELETE FROM tblIdentityRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
155 // delete old board list inserts/requests - we don't need them anymore
\r
156 date=Poco::Timestamp();
\r
157 date-=Poco::Timespan(2,0,0,0,0);
\r
158 m_db->Execute("DELETE FROM tblBoardListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
159 m_db->Execute("DELETE FROM tblBoardListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
161 // delete old local identity inserts - we don't need them anymore
\r
162 date=Poco::Timestamp();
\r
163 date-=Poco::Timespan(2,0,0,0,0);
\r
164 m_db->Execute("DELETE FROM tblLocalIdentityInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
166 // delete old message list inserts/requests - we don't need them anymore
\r
167 date=Poco::Timestamp();
\r
168 date-=Poco::Timespan(2,0,0,0,0);
\r
169 m_db->Execute("DELETE FROM tblMessageListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
170 m_db->Execute("DELETE FROM tblMessageListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
172 // delete old trust list inserts/requests - we don't need them anymore
\r
173 date=Poco::Timestamp();
\r
174 date-=Poco::Timespan(2,0,0,0,0);
\r
175 m_db->Execute("DELETE FROM tblTrustListInserts WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
176 m_db->Execute("DELETE FROM tblTrustListRequests WHERE Day<'"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
178 // delete trust lists from identities we aren't trusting anymore
\r
179 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
181 // remove identityid from messages where the identity has been deleted
\r
182 m_db->Execute("UPDATE tblMessage SET IdentityID=NULL WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");
\r
184 // try to re-attach messages from identities that were previously deleted, but have been since re-added
\r
185 // first get the names from messages that have a NULL IdentityID
\r
186 SQLite3DB::Statement st=m_db->Prepare("SELECT FromName FROM tblMessage WHERE IdentityID IS NULL GROUP BY FromName;");
\r
188 while(st.RowReturned())
\r
190 std::string name="";
\r
191 std::string namepart="";
\r
192 std::string publickey="";
\r
194 st.ResultText(0,name);
\r
196 std::vector<std::string> parts;
\r
197 StringFunctions::Split(name,"@",parts);
\r
199 // name can have a @ in it - so reattach all parts except the last which is the key
\r
200 for(long i=0; i<parts.size()-1; i++)
\r
206 namepart+=parts[i];
\r
209 // find identities with this name
\r
210 SQLite3DB::Statement st2=m_db->Prepare("SELECT IdentityID,PublicKey FROM tblIdentity WHERE Name=?;");
\r
211 st2.Bind(0,namepart);
\r
213 while(st2.RowReturned())
\r
217 st2.ResultText(1,publickey);
\r
218 // check if public key matches 2nd part
\r
219 if(parts.size()>1 && publickey.find(parts[1])==4)
\r
221 // we have the identity - so update the messages table with the identityid
\r
222 st2.ResultInt(0,identityid);
\r
224 SQLite3DB::Statement st3=m_db->Prepare("UPDATE tblMessage SET IdentityID=? WHERE FromName=? AND IdentityID IS NULL;");
\r
225 st3.Bind(0,identityid);
\r
235 // delete single use identities that are older than 7 days
\r
236 date=Poco::Timestamp();
\r
237 date-=Poco::Timespan(7,0,0,0,0);
\r
238 st=m_db->Prepare("DELETE FROM tblIdentity WHERE SingleUse='true' AND DateAdded<?;");
\r
239 st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));
\r
242 // delete local single use identities that are older than 7 days
\r
243 date=Poco::Timestamp();
\r
244 date-=Poco::Timespan(7,0,0,0,0);
\r
245 st=m_db->Prepare("DELETE FROM tblLocalIdentity WHERE SingleUse='true' AND DateCreated<?;");
\r
246 st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S"));
\r
249 // delete old messages
\r
250 date=Poco::Timestamp();
\r
251 date-=Poco::Timespan(m_deletemessagesolderthan,0,0,0,0);
\r
252 st=m_db->Prepare("DELETE FROM tblMessage WHERE MessageDate<?;");
\r
253 st.Bind(0,Poco::DateTimeFormatter::format(date,"%Y-%m-%d"));
\r
256 // delete tblIdentityTrust for local identities and identities that have been deleted
\r
257 m_db->Execute("DELETE FROM tblIdentityTrust WHERE LocalIdentityID NOT IN (SELECT LocalIdentityID FROM tblLocalIdentity);");
\r
258 m_db->Execute("DELETE FROM tblIdentityTrust WHERE IdentityID NOT IN (SELECT IdentityID FROM tblIdentity);");
\r
260 m_log->debug("PeriodicDBMaintenance::Do1DayMaintenance");
\r
264 void DBMaintenanceThread::run()
\r
266 m_log->debug("DBMaintenanceThread::run thread started.");
\r
268 Poco::DateTime now;
\r
272 now=Poco::Timestamp();
\r
274 if((m_last10minute+Poco::Timespan(0,0,10,0,0))<=now)
\r
276 Do10MinuteMaintenance();
\r
277 m_last10minute=Poco::Timestamp();
\r
279 if((m_last30minute+Poco::Timespan(0,0,30,0,0))<=now)
\r
281 Do30MinuteMaintenance();
\r
282 m_last30minute=Poco::Timestamp();
\r
284 if((m_last1hour+Poco::Timespan(0,1,0,0,0))<=now)
\r
286 Do1HourMaintenance();
\r
287 m_last1hour=Poco::Timestamp();
\r
289 if((m_last6hour+Poco::Timespan(0,6,0,0,0))<=now)
\r
291 Do6HourMaintenance();
\r
292 m_last6hour=Poco::Timestamp();
\r
294 if((m_last1day+Poco::Timespan(1,0,0,0,0))<=now)
\r
296 Do1DayMaintenance();
\r
297 m_last1day=Poco::Timestamp();
\r
300 Poco::Thread::sleep(1000);
\r
301 }while(!IsCancelled());
\r
303 m_log->debug("DBMaintenanceThread::run thread exiting.");
\r