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
19 db->Execute("PRAGMA synchronous = FULL;");
\r
21 db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\
\r
26 SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");
\r
28 if(st.RowReturned())
\r
32 st.ResultInt(0,major);
\r
33 st.ResultInt(1,minor);
\r
35 if(major==1 && minor==0)
\r
37 ConvertDB0100To0101();
\r
41 if(major==1 && (minor==1 || minor==2))
\r
43 ConvertDB0101To0103();
\r
47 if(major==1 && minor==3)
\r
49 ConvertDB0103To0104();
\r
53 if(major==1 && minor==4)
\r
55 ConvertDB0104To0105();
\r
59 if(major==1 && minor==5)
\r
61 ConvertDB0105To0106();
\r
65 if(major==1 && minor==6)
\r
67 ConvertDB0106To0107();
\r
71 if(major==1 && minor==7)
\r
73 ConvertDB0107To0108();
\r
77 if(major==1 && minor==8)
\r
79 ConvertDB0108To0109();
\r
83 if(major==1 && minor==9)
\r
85 ConvertDB0109To0110();
\r
89 if(major==1 && minor==10)
\r
91 ConvertDB0110To0111();
\r
95 if(major==1 && minor==11)
\r
97 ConvertDB0111To0112();
\r
101 if(major==1 && minor==12)
\r
103 ConvertDB0112To0113();
\r
110 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,13);");
\r
113 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");
\r
115 db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
\r
125 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");
\r
127 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
128 Option TEXT UNIQUE,\
\r
129 OptionValue TEXT NOT NULL,\
\r
130 OptionDescription TEXT,\
\r
132 SortOrder INTEGER,\
\r
136 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
137 LocalIdentityID INTEGER PRIMARY KEY,\
\r
139 PublicKey TEXT UNIQUE,\
\r
140 PrivateKey TEXT UNIQUE,\
\r
141 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
142 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
143 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
144 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
145 FreesiteEdition INTEGER,\
\r
146 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
147 LastInsertedIdentity DATETIME,\
\r
148 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
149 LastInsertedPuzzle DATETIME,\
\r
150 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
151 LastInsertedTrustList DATETIME,\
\r
152 LastInsertedBoardList DATETIME,\
\r
153 LastInsertedMessageList DATETIME,\
\r
154 LastInsertedFreesite DATETIME,\
\r
155 DateCreated DATETIME,\
\r
156 MinMessageDelay INTEGER DEFAULT 0,\
\r
157 MaxMessageDelay INTEGER DEFAULT 0\
\r
160 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
161 LocalIdentityID INTEGER,\
\r
163 InsertIndex INTEGER\
\r
166 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
167 LocalIdentityID INTEGER,\
\r
169 InsertIndex INTEGER\
\r
172 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
173 IdentityID INTEGER,\
\r
175 RequestIndex INTEGER,\
\r
176 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
179 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
181 LocalIdentityID INTEGER,\
\r
183 InsertIndex INTEGER,\
\r
187 PuzzleSolution TEXT,\
\r
188 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
192 PurgeDate is not used yet
\r
194 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
195 IdentityID INTEGER PRIMARY KEY,\
\r
196 PublicKey TEXT UNIQUE,\
\r
198 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
199 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
200 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
201 FreesiteEdition INTEGER,\
\r
202 DateAdded DATETIME,\
\r
203 LastSeen DATETIME,\
\r
204 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
205 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
206 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
207 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
209 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
210 PurgeDate DATETIME\
\r
213 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
214 IdentityID INTEGER,\
\r
216 RequestIndex INTEGER,\
\r
217 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
220 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
221 IdentityID INTEGER,\
\r
223 RequestIndex INTEGER,\
\r
224 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
231 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
232 LocalIdentityID INTEGER,\
\r
236 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
239 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
240 LocalIdentityID INTEGER,\
\r
241 IdentityID INTEGER,\
\r
242 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
243 MessageTrustComment TEXT,\
\r
244 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
245 TrustListTrustComment TEXT\
\r
248 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
250 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
253 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
256 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
259 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
262 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
265 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
268 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
269 IdentityID INTEGER,\
\r
270 TargetIdentityID INTEGER,\
\r
271 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
272 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
273 MessageTrustComment TEXT,\
\r
274 TrustListTrustComment TEXT\
\r
277 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
278 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
280 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
281 BoardID INTEGER PRIMARY KEY,\
\r
282 BoardName TEXT UNIQUE,\
\r
283 BoardDescription TEXT,\
\r
284 DateAdded DATETIME,\
\r
285 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
289 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");
\r
290 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");
\r
291 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");
\r
292 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");
\r
294 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
295 MessageID INTEGER PRIMARY KEY,\
\r
296 IdentityID INTEGER,\
\r
301 MessageUUID TEXT UNIQUE,\
\r
302 ReplyBoardID INTEGER,\
\r
304 MessageIndex INTEGER\
\r
307 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
309 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
310 MessageID INTEGER,\
\r
311 ReplyToMessageUUID TEXT,\
\r
312 ReplyOrder INTEGER\
\r
315 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
317 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
318 MessageID INTEGER,\
\r
322 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
323 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
325 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
326 IdentityID INTEGER,\
\r
328 RequestIndex INTEGER,\
\r
329 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
333 Key is for anonymous messages (future)
\r
335 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
336 IdentityID INTEGER,\
\r
338 RequestIndex INTEGER,\
\r
339 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
340 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
341 Tries INTEGER DEFAULT 0,\
\r
345 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
347 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
348 LocalIdentityID INTEGER,\
\r
350 InsertIndex INTEGER,\
\r
351 MessageUUID TEXT UNIQUE,\
\r
353 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
357 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
358 FileInsertID INTEGER PRIMARY KEY,\
\r
367 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
368 LocalIdentityID INTEGER,\
\r
370 InsertIndex INTEGER,\
\r
371 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
374 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
375 BoardID INTEGER UNIQUE,\
\r
376 ModifyLocalMessageTrust INTEGER,\
\r
377 ModifyLocalTrustListTrust INTEGER\
\r
380 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
381 LocalIdentityID INTEGER,\
\r
383 InsertIndex INTEGER,\
\r
384 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
387 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
388 IdentityID INTEGER,\
\r
390 RequestIndex INTEGER,\
\r
391 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
394 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
395 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
396 LocalIdentityID INTEGER,\
\r
400 // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page
\r
401 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\
\r
402 LocalIdentityID INTEGER\
\r
405 // Temporary table for form passwords
\r
406 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpFormPassword(\
\r
411 // low / high / message count for each board
\r
412 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
413 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
414 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
415 WHERE MessageID>=0 OR MessageID IS NULL \
\r
416 GROUP BY tblBoard.BoardID;");
\r
418 // calculates peer trust
\r
419 // 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
420 // need the +1 so that when the values are 0 the result is not 0
\r
421 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
422 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
423 SELECT TargetIdentityID, \
\r
424 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
425 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
426 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
427 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
428 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
429 GROUP BY TargetIdentityID;");
\r
431 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
432 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
433 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
434 GROUP BY tblIdentity.IdentityID;");
\r
437 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
438 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
440 // drop existing triggers
\r
441 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
442 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
443 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
444 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
446 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
447 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
450 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
453 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
454 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
457 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
460 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
461 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
464 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
465 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
468 // 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
469 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
472 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
476 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
479 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
480 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
483 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
484 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
487 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
488 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
489 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
490 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
491 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
492 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
493 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
496 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
497 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
500 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
501 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
502 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
503 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
504 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
505 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
506 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
509 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
512 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
515 // delete introduction puzzles that were half-way inserted
\r
516 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
518 // delete stale introduction puzzles (2 or more days old)
\r
519 date-=Poco::Timespan(2,0,0,0,0);
\r
520 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
521 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
523 date=Poco::Timestamp();
\r
524 // insert SomeDude's public key
\r
525 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
526 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
527 //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
528 // insert garfield's public key -haven't seen in a while - disabling for now
\r
529 //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
530 // insert alek's public key - haven't seen in a while - disabling for now
\r
531 //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
532 // insert Luke771's public key
\r
533 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
534 // insert falafel's public key
\r
535 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
536 // insert cptn_insano's public key
\r
537 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
538 // insert Flink's public key
\r
539 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
540 // insert Kane's public key
\r
541 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
542 // inserts boardstat's public key
\r
543 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
545 // TODO remove sometime after 0.1.17
\r
546 FixCapitalBoardNames();
\r
548 // run analyze - may speed up some queries
\r
549 db->Execute("ANALYZE;");
\r
553 const bool VerifyDB()
\r
555 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
556 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
558 if(st.RowReturned())
\r
560 std::string res="";
\r
561 st.ResultText(0,res);
\r