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
117 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,15);");
\r
120 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=15;");
\r
122 db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
\r
132 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");
\r
134 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
135 Option TEXT UNIQUE,\
\r
136 OptionValue TEXT NOT NULL,\
\r
137 OptionDescription TEXT,\
\r
139 SortOrder INTEGER,\
\r
141 DisplayType TEXT CHECK (DisplayType IN ('textbox','textarea','select','multiselect')) DEFAULT 'textbox',\
\r
142 DisplayParam1 TEXT,\
\r
143 DisplayParam2 TEXT,\
\r
144 Mode TEXT CHECK (Mode IN ('simple','advanced')) DEFAULT 'simple'\
\r
147 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
148 LocalIdentityID INTEGER PRIMARY KEY,\
\r
150 PublicKey TEXT UNIQUE,\
\r
151 PrivateKey TEXT UNIQUE,\
\r
152 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
153 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
154 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
155 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
156 FreesiteEdition INTEGER,\
\r
157 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
158 LastInsertedIdentity DATETIME,\
\r
159 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
160 LastInsertedPuzzle DATETIME,\
\r
161 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
162 LastInsertedTrustList DATETIME,\
\r
163 LastInsertedBoardList DATETIME,\
\r
164 LastInsertedMessageList DATETIME,\
\r
165 LastInsertedFreesite DATETIME,\
\r
166 DateCreated DATETIME,\
\r
167 MinMessageDelay INTEGER DEFAULT 0,\
\r
168 MaxMessageDelay INTEGER DEFAULT 0\
\r
171 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
172 LocalIdentityID INTEGER,\
\r
174 InsertIndex INTEGER\
\r
177 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
178 LocalIdentityID INTEGER,\
\r
180 InsertIndex INTEGER\
\r
183 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
184 IdentityID INTEGER,\
\r
186 RequestIndex INTEGER,\
\r
187 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
190 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
192 LocalIdentityID INTEGER,\
\r
194 InsertIndex INTEGER,\
\r
198 PuzzleSolution TEXT,\
\r
199 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
203 PurgeDate is not used yet
\r
205 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
206 IdentityID INTEGER PRIMARY KEY,\
\r
207 PublicKey TEXT UNIQUE,\
\r
209 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
210 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
211 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
212 FreesiteEdition INTEGER,\
\r
213 DateAdded DATETIME,\
\r
214 LastSeen DATETIME,\
\r
215 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
216 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
217 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
218 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
220 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
221 PurgeDate DATETIME\
\r
224 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
225 IdentityID INTEGER,\
\r
227 RequestIndex INTEGER,\
\r
228 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
231 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
232 IdentityID INTEGER,\
\r
234 RequestIndex INTEGER,\
\r
235 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
242 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
243 LocalIdentityID INTEGER,\
\r
247 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
250 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
251 LocalIdentityID INTEGER,\
\r
252 IdentityID INTEGER,\
\r
253 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
254 MessageTrustComment TEXT,\
\r
255 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
256 TrustListTrustComment TEXT\
\r
259 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
261 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
264 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
267 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
270 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
273 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
276 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
279 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
280 IdentityID INTEGER,\
\r
281 TargetIdentityID INTEGER,\
\r
282 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
283 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
284 MessageTrustComment TEXT,\
\r
285 TrustListTrustComment TEXT\
\r
288 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
289 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
291 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
292 BoardID INTEGER PRIMARY KEY,\
\r
293 BoardName TEXT UNIQUE,\
\r
294 BoardDescription TEXT,\
\r
295 DateAdded DATETIME,\
\r
296 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
298 Forum TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\
\r
301 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
302 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
303 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Seed Board','true');");
\r
304 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Seed Board','true');");
\r
306 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
307 MessageID INTEGER PRIMARY KEY,\
\r
308 IdentityID INTEGER,\
\r
313 MessageUUID TEXT UNIQUE,\
\r
314 ReplyBoardID INTEGER,\
\r
316 MessageIndex INTEGER,\
\r
317 Read INTEGER CHECK(Read IN(0,1)) DEFAULT 0\
\r
320 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
322 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
323 MessageID INTEGER,\
\r
324 ReplyToMessageUUID TEXT,\
\r
325 ReplyOrder INTEGER\
\r
328 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
329 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);");
\r
331 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
332 MessageID INTEGER,\
\r
336 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
337 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
339 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
340 IdentityID INTEGER,\
\r
342 RequestIndex INTEGER,\
\r
343 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
347 Key is for anonymous messages (future)
\r
349 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
350 IdentityID INTEGER,\
\r
352 RequestIndex INTEGER,\
\r
353 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
354 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
355 Tries INTEGER DEFAULT 0,\
\r
359 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
361 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
362 LocalIdentityID INTEGER,\
\r
364 InsertIndex INTEGER,\
\r
365 MessageUUID TEXT UNIQUE,\
\r
367 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
371 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
372 FileInsertID INTEGER PRIMARY KEY,\
\r
381 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
382 LocalIdentityID INTEGER,\
\r
384 InsertIndex INTEGER,\
\r
385 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
388 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
389 BoardID INTEGER UNIQUE,\
\r
390 ModifyLocalMessageTrust INTEGER,\
\r
391 ModifyLocalTrustListTrust INTEGER\
\r
394 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
395 LocalIdentityID INTEGER,\
\r
397 InsertIndex INTEGER,\
\r
398 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
401 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
402 IdentityID INTEGER,\
\r
404 RequestIndex INTEGER,\
\r
405 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
408 // begin thread db schema
\r
409 db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\
\r
410 ThreadID INTEGER PRIMARY KEY,\
\r
412 FirstMessageID INTEGER,\
\r
413 LastMessageID INTEGER\
\r
416 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");
\r
417 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");
\r
418 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");
\r
420 db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\
\r
422 MessageID INTEGER,\
\r
426 db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_ThreadID ON tblThreadPost(ThreadID);");
\r
427 db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");
\r
429 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\
\r
432 DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\
\r
434 // end thread db schema
\r
436 // low / high / message count for each board
\r
437 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
438 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
439 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
440 WHERE MessageID>=0 OR MessageID IS NULL \
\r
441 GROUP BY tblBoard.BoardID;");
\r
443 // calculates peer trust
\r
444 // 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
445 // need the +1 so that when the values are 0 the result is not 0
\r
446 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
447 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
448 SELECT TargetIdentityID, \
\r
449 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
450 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
451 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
452 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
453 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
454 GROUP BY TargetIdentityID;");
\r
456 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
457 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
458 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
459 GROUP BY tblIdentity.IdentityID;");
\r
462 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
463 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
465 // drop existing triggers
\r
466 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
467 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
468 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
469 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
471 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
472 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
475 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
478 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
479 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
482 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
485 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
486 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
489 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
490 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
493 // 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
494 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
497 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
501 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
504 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
505 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
508 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
509 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
512 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
513 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
514 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
515 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
516 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
517 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
518 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
521 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
522 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
525 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
526 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
527 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
528 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
529 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
530 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
531 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
534 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
537 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
540 // delete introduction puzzles that were half-way inserted
\r
541 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
543 // delete stale introduction puzzles (2 or more days old)
\r
544 date-=Poco::Timespan(2,0,0,0,0);
\r
545 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
546 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
548 date=Poco::Timestamp();
\r
549 // insert SomeDude's public key
\r
550 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
551 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
552 //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
553 // insert garfield's public key -haven't seen in a while - disabling for now
\r
554 //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
555 // insert alek's public key - haven't seen in a while - disabling for now
\r
556 //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
557 // insert Luke771's public key
\r
558 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
559 // insert falafel's public key
\r
560 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
561 // insert cptn_insano's public key
\r
562 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
563 // insert Flink's public key - haven't seen in a while - disabling for now
\r
564 //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
565 // insert Kane's public key
\r
566 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
567 // inserts boardstat's public key
\r
568 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
570 // TODO remove sometime after 0.1.17
\r
571 FixCapitalBoardNames(db);
\r
573 // run analyze - may speed up some queries
\r
574 db->Execute("ANALYZE;");
\r
578 const bool VerifyDB(SQLite3DB::DB *db)
\r
580 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
582 if(st.RowReturned())
\r
584 std::string res="";
\r
585 st.ResultText(0,res);
\r
592 // try to reindex and vacuum database in case of index corruption
\r
593 st=db->Prepare("REINDEX;");
\r
595 st=db->Prepare("VACUUM;");
\r
598 // check integrity again
\r
599 st=db->Prepare("PRAGMA integrity_check;");
\r
601 st.ResultText(0,res);
\r
618 const std::string TestDBIntegrity(SQLite3DB::DB *db)
\r
620 std::string result="";
\r
622 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
624 while(st.RowReturned())
\r
626 std::string text="";
\r
627 st.ResultText(0,text);
\r