1 #include "../include/dbsetup.h"
\r
2 #include "../include/dbconversions.h"
\r
3 #include "../include/option.h"
\r
4 #include "../include/db/sqlite3db.h"
\r
6 #include <Poco/DateTime.h>
\r
7 #include <Poco/Timespan.h>
\r
8 #include <Poco/DateTimeFormatter.h>
\r
13 Poco::DateTime date;
\r
14 std::string tempval="";
\r
15 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
17 db->Open("fms.db3");
\r
18 db->SetBusyTimeout(20000); // set timeout to 20 seconds
\r
20 db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\
\r
25 SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");
\r
27 if(st.RowReturned())
\r
31 st.ResultInt(0,major);
\r
32 st.ResultInt(1,minor);
\r
34 if(major==1 && minor==0)
\r
36 ConvertDB0100To0101();
\r
40 if(major==1 && (minor==1 || minor==2))
\r
42 ConvertDB0101To0103();
\r
46 if(major==1 && minor==3)
\r
48 ConvertDB0103To0104();
\r
52 if(major==1 && minor==4)
\r
54 ConvertDB0104To0105();
\r
58 if(major==1 && minor==5)
\r
60 ConvertDB0105To0106();
\r
64 if(major==1 && minor==6)
\r
66 ConvertDB0106To0107();
\r
70 if(major==1 && minor==7)
\r
72 ConvertDB0107To0108();
\r
76 if(major==1 && minor==8)
\r
78 ConvertDB0108To0109();
\r
82 if(major==1 && minor==9)
\r
84 ConvertDB0109To0110();
\r
88 if(major==1 && minor==10)
\r
90 ConvertDB0110To0111();
\r
94 if(major==1 && minor==11)
\r
96 ConvertDB0111To0112();
\r
100 if(major==1 && minor==12)
\r
102 ConvertDB0112To0113();
\r
109 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,13);");
\r
112 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");
\r
114 db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
\r
124 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");
\r
126 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
127 Option TEXT UNIQUE,\
\r
128 OptionValue TEXT NOT NULL,\
\r
129 OptionDescription TEXT,\
\r
131 SortOrder INTEGER,\
\r
135 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
136 LocalIdentityID INTEGER PRIMARY KEY,\
\r
138 PublicKey TEXT UNIQUE,\
\r
139 PrivateKey TEXT UNIQUE,\
\r
140 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
141 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
142 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
143 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
144 FreesiteEdition INTEGER,\
\r
145 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
146 LastInsertedIdentity DATETIME,\
\r
147 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
148 LastInsertedPuzzle DATETIME,\
\r
149 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
150 LastInsertedTrustList DATETIME,\
\r
151 LastInsertedBoardList DATETIME,\
\r
152 LastInsertedMessageList DATETIME,\
\r
153 LastInsertedFreesite DATETIME,\
\r
154 DateCreated DATETIME,\
\r
155 MinMessageDelay INTEGER DEFAULT 0,\
\r
156 MaxMessageDelay INTEGER DEFAULT 0\
\r
159 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
160 LocalIdentityID INTEGER,\
\r
162 InsertIndex INTEGER\
\r
165 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
166 LocalIdentityID INTEGER,\
\r
168 InsertIndex INTEGER\
\r
171 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
172 IdentityID INTEGER,\
\r
174 RequestIndex INTEGER,\
\r
175 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
178 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
180 LocalIdentityID INTEGER,\
\r
182 InsertIndex INTEGER,\
\r
186 PuzzleSolution TEXT,\
\r
187 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
191 PurgeDate is not used yet
\r
193 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
194 IdentityID INTEGER PRIMARY KEY,\
\r
195 PublicKey TEXT UNIQUE,\
\r
197 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
198 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
199 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
200 FreesiteEdition INTEGER,\
\r
201 DateAdded DATETIME,\
\r
202 LastSeen DATETIME,\
\r
203 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
204 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
205 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
206 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
208 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
209 PurgeDate DATETIME\
\r
212 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
213 IdentityID INTEGER,\
\r
215 RequestIndex INTEGER,\
\r
216 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
219 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
220 IdentityID INTEGER,\
\r
222 RequestIndex INTEGER,\
\r
223 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
230 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
231 LocalIdentityID INTEGER,\
\r
235 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
238 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
239 LocalIdentityID INTEGER,\
\r
240 IdentityID INTEGER,\
\r
241 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
242 MessageTrustComment TEXT,\
\r
243 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
244 TrustListTrustComment TEXT\
\r
247 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
249 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
252 UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \
\r
255 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
258 UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \
\r
261 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
264 UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=old.IdentityID; \
\r
267 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
268 IdentityID INTEGER,\
\r
269 TargetIdentityID INTEGER,\
\r
270 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
271 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
272 MessageTrustComment TEXT,\
\r
273 TrustListTrustComment TEXT\
\r
276 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
277 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
279 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
280 BoardID INTEGER PRIMARY KEY,\
\r
281 BoardName TEXT UNIQUE,\
\r
282 BoardDescription TEXT,\
\r
283 DateAdded DATETIME,\
\r
284 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
288 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");
\r
289 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");
\r
290 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");
\r
291 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");
\r
293 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
294 MessageID INTEGER PRIMARY KEY,\
\r
295 IdentityID INTEGER,\
\r
300 MessageUUID TEXT UNIQUE,\
\r
301 ReplyBoardID INTEGER,\
\r
303 MessageIndex INTEGER\
\r
306 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
308 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
309 MessageID INTEGER,\
\r
310 ReplyToMessageUUID TEXT,\
\r
311 ReplyOrder INTEGER\
\r
314 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
316 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
317 MessageID INTEGER,\
\r
321 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
322 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
324 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
325 IdentityID INTEGER,\
\r
327 RequestIndex INTEGER,\
\r
328 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
332 Key is for anonymous messages (future)
\r
334 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
335 IdentityID INTEGER,\
\r
337 RequestIndex INTEGER,\
\r
338 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
339 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
340 Tries INTEGER DEFAULT 0,\
\r
344 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
346 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
347 LocalIdentityID INTEGER,\
\r
349 InsertIndex INTEGER,\
\r
350 MessageUUID TEXT UNIQUE,\
\r
352 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
356 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
357 FileInsertID INTEGER PRIMARY KEY,\
\r
366 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
367 LocalIdentityID INTEGER,\
\r
369 InsertIndex INTEGER,\
\r
370 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
373 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
374 BoardID INTEGER UNIQUE,\
\r
375 ModifyLocalMessageTrust INTEGER,\
\r
376 ModifyLocalTrustListTrust INTEGER\
\r
379 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
380 LocalIdentityID INTEGER,\
\r
382 InsertIndex INTEGER,\
\r
383 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
386 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
387 IdentityID INTEGER,\
\r
389 RequestIndex INTEGER,\
\r
390 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
393 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
394 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
395 LocalIdentityID INTEGER,\
\r
399 // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page
\r
400 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\
\r
401 LocalIdentityID INTEGER\
\r
404 // Temporary table for form passwords
\r
405 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpFormPassword(\
\r
410 // low / high / message count for each board
\r
411 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
412 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
413 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
414 WHERE MessageID>=0 OR MessageID IS NULL \
\r
415 GROUP BY tblBoard.BoardID;");
\r
417 // calculates peer trust
\r
418 // do the (MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1)/100.0 - so if MessageTrust or TrustListTrust is NULL, the calc will be NULL and it won't be included at all in the average
\r
419 // need the +1 so that when the values are 0 the result is not 0
\r
420 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
421 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
422 SELECT TargetIdentityID, \
\r
423 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
424 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
425 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
426 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
427 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
428 GROUP BY TargetIdentityID;");
\r
430 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
431 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
432 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
433 GROUP BY tblIdentity.IdentityID;");
\r
436 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
437 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
439 // drop existing triggers
\r
440 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
441 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
442 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
443 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
445 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
446 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
449 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=old.TargetIdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=old.TargetIdentityID) WHERE IdentityID=old.TargetIdentityID;\
\r
452 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
453 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
456 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=new.TargetIdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=new.TargetIdentityID) WHERE IdentityID=new.TargetIdentityID;\
\r
459 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
460 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
463 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=old.TargetIdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=old.TargetIdentityID) WHERE IdentityID=old.TargetIdentityID;\
\r
464 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=new.TargetIdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=new.TargetIdentityID) WHERE IdentityID=new.TargetIdentityID;\
\r
467 // recalculate all Peer TrustLevels when updating Local TrustLevels on tblIdentity - doesn't really need to be all, but rather all identities the updated identity has a trust level for. It's easier to update everyone for now.
\r
468 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
471 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
475 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
478 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
479 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
482 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
483 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
486 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
487 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
488 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
489 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
490 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
491 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
492 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
495 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
496 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
499 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
500 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
501 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
502 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
503 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
504 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
505 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
508 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
511 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
514 // delete introduction puzzles that were half-way inserted
\r
515 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
517 // delete stale introduction puzzles (2 or more days old)
\r
518 date-=Poco::Timespan(2,0,0,0,0);
\r
519 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
520 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
522 date=Poco::Timestamp();
\r
523 // insert SomeDude's public key
\r
524 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust,AddedMethod) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"',50,'Initial Identity');");
\r
525 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
526 //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
527 // insert garfield's public key
\r
528 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
529 // insert alek's public key
\r
530 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@lTjeI6V0lQsktXqaqJ6Iwk4TdsHduQI54rdUpHfhGbg,0oTYfrxxx8OmdU1~60gqpf3781qzEicM4Sz97mJsBM4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
531 // insert Luke771's public key
\r
532 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@mdXK~ZVlfTZhF1SLBrvZ--i0vOsOpa~w9wv~~psQ-04,gXonsXKc7aexKSO8Gt8Fwre4Qgmmbt2WueO7VzxNKkk,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
533 // insert falafel's public key
\r
534 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
535 // insert cptn_insano's public key
\r
536 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@bloE1LJ~qzSYUkU2nt7sB9kq060D4HTQC66pk5Q8NpA,DOOASUnp0kj6tOdhZJ-h5Tk7Ka50FSrUgsH7tCG1usU,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
537 // insert Flink's public key
\r
538 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@q2TtkNBOuuniyJ56~8NSopCs3ttwe5KlB31ugZtWmXA,6~PzIupS8YK7L6oFNpXGKJmHT2kBMDfwTg73nHdNur8,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
539 // insert Kane's public key
\r
540 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@Ofm~yZivDJ5Z2fSzZbMiLEUUQaIc0KHRdZMBTaPLO6I,WLm4s4hNbOOurJ6ijfOq4odz7-dN7uTUvYxJRwWnlMI,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
541 // inserts boardstat's public key
\r
542 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@aYWBb6zo2AM13XCNhsmmRKMANEx6PG~C15CWjdZziKA,X1pAG4EIqR1gAiyGFVZ1iiw-uTlh460~rFACJ7ZHQXk,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");
\r
544 // TODO remove sometime after 0.1.17
\r
545 FixCapitalBoardNames();
\r
547 // run analyze - may speed up some queries
\r
548 db->Execute("ANALYZE;");
\r