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
10 void SetupDB(SQLite3DB::DB *db)
\r
13 Poco::DateTime date;
\r
14 std::string tempval="";
\r
16 db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\
\r
21 SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");
\r
23 if(st.RowReturned())
\r
27 st.ResultInt(0,major);
\r
28 st.ResultInt(1,minor);
\r
30 if(major==1 && minor==0)
\r
32 ConvertDB0100To0101(db);
\r
36 if(major==1 && (minor==1 || minor==2))
\r
38 ConvertDB0101To0103(db);
\r
42 if(major==1 && minor==3)
\r
44 ConvertDB0103To0104(db);
\r
48 if(major==1 && minor==4)
\r
50 ConvertDB0104To0105(db);
\r
54 if(major==1 && minor==5)
\r
56 ConvertDB0105To0106(db);
\r
60 if(major==1 && minor==6)
\r
62 ConvertDB0106To0107(db);
\r
66 if(major==1 && minor==7)
\r
68 ConvertDB0107To0108(db);
\r
72 if(major==1 && minor==8)
\r
74 ConvertDB0108To0109(db);
\r
78 if(major==1 && minor==9)
\r
80 ConvertDB0109To0110(db);
\r
84 if(major==1 && minor==10)
\r
86 ConvertDB0110To0111(db);
\r
90 if(major==1 && minor==11)
\r
92 ConvertDB0111To0112(db);
\r
96 if(major==1 && minor==12)
\r
98 ConvertDB0112To0113(db);
\r
102 if(major==1 && minor==13)
\r
104 ConvertDB0113To0114(db);
\r
108 if(major==1 && minor==14)
\r
110 ConvertDB0114To0115(db);
\r
114 if(major==1 && minor==15)
\r
116 ConvertDB0115To0116(db);
\r
120 if(major==1 && minor==16)
\r
122 ConvertDB0116To0117(db);
\r
129 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,17);");
\r
132 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=17;");
\r
134 db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
\r
144 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");
\r
146 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
147 Option TEXT UNIQUE,\
\r
148 OptionValue TEXT NOT NULL,\
\r
149 OptionDescription TEXT,\
\r
151 SortOrder INTEGER,\
\r
153 DisplayType TEXT CHECK (DisplayType IN ('textbox','textarea','select','multiselect')) DEFAULT 'textbox',\
\r
154 DisplayParam1 TEXT,\
\r
155 DisplayParam2 TEXT,\
\r
156 Mode TEXT CHECK (Mode IN ('simple','advanced')) DEFAULT 'simple'\
\r
159 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
160 LocalIdentityID INTEGER PRIMARY KEY,\
\r
162 PublicKey TEXT UNIQUE,\
\r
163 PrivateKey TEXT UNIQUE,\
\r
164 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
165 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
166 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
167 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
168 FreesiteEdition INTEGER,\
\r
169 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
170 LastInsertedIdentity DATETIME,\
\r
171 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
172 LastInsertedPuzzle DATETIME,\
\r
173 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
174 LastInsertedTrustList DATETIME,\
\r
175 LastInsertedBoardList DATETIME,\
\r
176 LastInsertedMessageList DATETIME,\
\r
177 LastInsertedFreesite DATETIME,\
\r
178 DateCreated DATETIME,\
\r
179 MinMessageDelay INTEGER DEFAULT 0,\
\r
180 MaxMessageDelay INTEGER DEFAULT 0\
\r
183 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
184 LocalIdentityID INTEGER,\
\r
186 InsertIndex INTEGER\
\r
189 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
190 LocalIdentityID INTEGER,\
\r
192 InsertIndex INTEGER\
\r
195 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
196 IdentityID INTEGER,\
\r
198 RequestIndex INTEGER,\
\r
199 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
202 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
204 LocalIdentityID INTEGER,\
\r
206 InsertIndex INTEGER,\
\r
210 PuzzleSolution TEXT,\
\r
211 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
215 PurgeDate is not used yet
\r
217 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
218 IdentityID INTEGER PRIMARY KEY,\
\r
219 PublicKey TEXT UNIQUE,\
\r
221 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
222 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
223 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
224 FreesiteEdition INTEGER,\
\r
225 DateAdded DATETIME,\
\r
226 LastSeen DATETIME,\
\r
227 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
228 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
229 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
230 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
232 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
233 PurgeDate DATETIME,\
\r
234 FailureCount INTEGER CHECK(FailureCount>=0) DEFAULT 0\
\r
237 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
238 IdentityID INTEGER,\
\r
240 RequestIndex INTEGER,\
\r
241 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
244 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
245 IdentityID INTEGER,\
\r
247 RequestIndex INTEGER,\
\r
248 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
255 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
256 LocalIdentityID INTEGER,\
\r
260 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
263 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
264 LocalIdentityID INTEGER,\
\r
265 IdentityID INTEGER,\
\r
266 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
267 MessageTrustComment TEXT,\
\r
268 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
269 TrustListTrustComment TEXT\
\r
272 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
274 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
277 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
280 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
283 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
286 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
289 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
292 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
293 IdentityID INTEGER,\
\r
294 TargetIdentityID INTEGER,\
\r
295 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
296 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
297 MessageTrustComment TEXT,\
\r
298 TrustListTrustComment TEXT\
\r
301 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
302 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
304 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
305 BoardID INTEGER PRIMARY KEY,\
\r
306 BoardName TEXT UNIQUE,\
\r
307 BoardDescription TEXT,\
\r
308 DateAdded DATETIME,\
\r
309 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
311 Forum TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\
\r
314 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Seed Board','true');");
\r
315 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Seed Board','true');");
\r
316 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Seed Board','true');");
\r
317 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Seed Board','true');");
\r
319 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
320 MessageID INTEGER PRIMARY KEY,\
\r
321 IdentityID INTEGER,\
\r
326 MessageUUID TEXT UNIQUE,\
\r
327 ReplyBoardID INTEGER,\
\r
330 MessageIndex INTEGER,\
\r
331 Read INTEGER CHECK(Read IN(0,1)) DEFAULT 0\
\r
334 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
336 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
337 MessageID INTEGER,\
\r
338 ReplyToMessageUUID TEXT,\
\r
339 ReplyOrder INTEGER\
\r
342 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
343 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);");
\r
345 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
346 MessageID INTEGER,\
\r
350 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
351 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
353 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
354 IdentityID INTEGER,\
\r
356 RequestIndex INTEGER,\
\r
357 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
361 Key is for anonymous messages (future)
\r
363 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
364 IdentityID INTEGER,\
\r
366 RequestIndex INTEGER,\
\r
367 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
368 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
369 Tries INTEGER DEFAULT 0,\
\r
371 FromIdentityID INTEGER\
\r
374 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
376 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
377 LocalIdentityID INTEGER,\
\r
379 InsertIndex INTEGER,\
\r
380 MessageUUID TEXT UNIQUE,\
\r
382 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
386 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
387 FileInsertID INTEGER PRIMARY KEY,\
\r
396 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
397 LocalIdentityID INTEGER,\
\r
399 InsertIndex INTEGER,\
\r
400 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
403 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
404 BoardID INTEGER UNIQUE,\
\r
405 ModifyLocalMessageTrust INTEGER,\
\r
406 ModifyLocalTrustListTrust INTEGER\
\r
409 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
410 LocalIdentityID INTEGER,\
\r
412 InsertIndex INTEGER,\
\r
413 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
416 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
417 IdentityID INTEGER,\
\r
419 RequestIndex INTEGER,\
\r
420 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
423 // begin thread db schema
\r
424 db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\
\r
425 ThreadID INTEGER PRIMARY KEY,\
\r
427 FirstMessageID INTEGER,\
\r
428 LastMessageID INTEGER\
\r
431 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");
\r
432 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");
\r
433 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");
\r
435 db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\
\r
437 MessageID INTEGER,\
\r
441 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxThreadPost_ThreadMessage ON tblThreadPost(ThreadID,MessageID);");
\r
442 db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");
\r
444 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\
\r
447 DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\
\r
449 // end thread db schema
\r
451 // low / high / message count for each board
\r
452 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
453 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
454 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
455 WHERE MessageID>=0 OR MessageID IS NULL \
\r
456 GROUP BY tblBoard.BoardID;");
\r
458 // calculates peer trust
\r
459 // 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
460 // need the +1 so that when the values are 0 the result is not 0
\r
461 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
462 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
463 SELECT TargetIdentityID, \
\r
464 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
465 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
466 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
467 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
468 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
469 GROUP BY TargetIdentityID;");
\r
471 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
472 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
473 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
474 GROUP BY tblIdentity.IdentityID;");
\r
477 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
478 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
480 // drop existing triggers
\r
481 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
482 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
483 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
484 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
486 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
487 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
490 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
493 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
494 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
497 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
500 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
501 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
504 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
505 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
508 // 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
509 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
512 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
516 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
519 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
520 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
523 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
524 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
527 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
528 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
529 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
530 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
531 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
532 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
533 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
536 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
537 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
540 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
541 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
542 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
543 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
544 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
545 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
546 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
549 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
552 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
555 // delete introduction puzzles that were half-way inserted
\r
556 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
558 // delete stale introduction puzzles (2 or more days old)
\r
559 date-=Poco::Timespan(2,0,0,0,0);
\r
560 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
561 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
563 date=Poco::Timestamp();
\r
564 // insert SomeDude's public key
\r
565 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,'Seed Identity');");
\r
566 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
567 //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")+"','Seed Identity');");
\r
568 // insert garfield's public key -haven't seen in a while - disabling for now
\r
569 //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");
\r
570 // insert alek's public key - haven't seen in a while - disabling for now
\r
571 //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@lTjeI6V0lQsktXqaqJ6Iwk4TdsHduQI54rdUpHfhGbg,0oTYfrxxx8OmdU1~60gqpf3781qzEicM4Sz97mJsBM4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");
\r
572 // insert Luke771's public key
\r
573 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")+"','Seed Identity');");
\r
574 // insert falafel's public key
\r
575 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");
\r
576 // insert cptn_insano's public key
\r
577 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")+"','Seed Identity');");
\r
578 // insert Flink's public key - haven't seen in a while - disabling for now
\r
579 //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")+"','Seed Identity');");
\r
580 // insert Kane's public key
\r
581 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")+"','Seed Identity');");
\r
582 // inserts boardstat's public key
\r
583 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")+"','Seed Identity');");
\r
585 // TODO remove sometime after 0.1.17
\r
586 FixCapitalBoardNames(db);
\r
588 // run analyze - may speed up some queries
\r
589 db->Execute("ANALYZE;");
\r
593 const bool VerifyDB(SQLite3DB::DB *db)
\r
595 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
597 if(st.RowReturned())
\r
599 std::string res="";
\r
600 st.ResultText(0,res);
\r
607 // try to reindex and vacuum database in case of index corruption
\r
608 st=db->Prepare("REINDEX;");
\r
610 st=db->Prepare("VACUUM;");
\r
613 // check integrity again
\r
614 st=db->Prepare("PRAGMA integrity_check;");
\r
616 st.ResultText(0,res);
\r
633 const std::string TestDBIntegrity(SQLite3DB::DB *db)
\r
635 std::string result="";
\r
637 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
639 while(st.RowReturned())
\r
641 std::string text="";
\r
642 st.ResultText(0,text);
\r