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
123 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,16);");
\r
126 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=16;");
\r
128 db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
\r
138 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");
\r
140 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
141 Option TEXT UNIQUE,\
\r
142 OptionValue TEXT NOT NULL,\
\r
143 OptionDescription TEXT,\
\r
145 SortOrder INTEGER,\
\r
147 DisplayType TEXT CHECK (DisplayType IN ('textbox','textarea','select','multiselect')) DEFAULT 'textbox',\
\r
148 DisplayParam1 TEXT,\
\r
149 DisplayParam2 TEXT,\
\r
150 Mode TEXT CHECK (Mode IN ('simple','advanced')) DEFAULT 'simple'\
\r
153 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
154 LocalIdentityID INTEGER PRIMARY KEY,\
\r
156 PublicKey TEXT UNIQUE,\
\r
157 PrivateKey TEXT UNIQUE,\
\r
158 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
159 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
160 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
161 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
162 FreesiteEdition INTEGER,\
\r
163 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
164 LastInsertedIdentity DATETIME,\
\r
165 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
166 LastInsertedPuzzle DATETIME,\
\r
167 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
168 LastInsertedTrustList DATETIME,\
\r
169 LastInsertedBoardList DATETIME,\
\r
170 LastInsertedMessageList DATETIME,\
\r
171 LastInsertedFreesite DATETIME,\
\r
172 DateCreated DATETIME,\
\r
173 MinMessageDelay INTEGER DEFAULT 0,\
\r
174 MaxMessageDelay INTEGER DEFAULT 0\
\r
177 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
178 LocalIdentityID INTEGER,\
\r
180 InsertIndex INTEGER\
\r
183 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
184 LocalIdentityID INTEGER,\
\r
186 InsertIndex INTEGER\
\r
189 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
190 IdentityID INTEGER,\
\r
192 RequestIndex INTEGER,\
\r
193 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
196 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
198 LocalIdentityID INTEGER,\
\r
200 InsertIndex INTEGER,\
\r
204 PuzzleSolution TEXT,\
\r
205 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
209 PurgeDate is not used yet
\r
211 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
212 IdentityID INTEGER PRIMARY KEY,\
\r
213 PublicKey TEXT UNIQUE,\
\r
215 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
216 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
217 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
218 FreesiteEdition INTEGER,\
\r
219 DateAdded DATETIME,\
\r
220 LastSeen DATETIME,\
\r
221 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
222 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
223 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
224 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
226 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
227 PurgeDate DATETIME,\
\r
228 FailureCount INTEGER CHECK(FailureCount>=0) DEFAULT 0\
\r
231 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
232 IdentityID INTEGER,\
\r
234 RequestIndex INTEGER,\
\r
235 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
238 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
239 IdentityID INTEGER,\
\r
241 RequestIndex INTEGER,\
\r
242 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
249 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
250 LocalIdentityID INTEGER,\
\r
254 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
257 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
258 LocalIdentityID INTEGER,\
\r
259 IdentityID INTEGER,\
\r
260 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
261 MessageTrustComment TEXT,\
\r
262 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
263 TrustListTrustComment TEXT\
\r
266 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
268 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
271 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
274 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust 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 trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
283 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
286 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
287 IdentityID INTEGER,\
\r
288 TargetIdentityID INTEGER,\
\r
289 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
290 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
291 MessageTrustComment TEXT,\
\r
292 TrustListTrustComment TEXT\
\r
295 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
296 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
298 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
299 BoardID INTEGER PRIMARY KEY,\
\r
300 BoardName TEXT UNIQUE,\
\r
301 BoardDescription TEXT,\
\r
302 DateAdded DATETIME,\
\r
303 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
305 Forum TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\
\r
308 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
309 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
310 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Seed Board','true');");
\r
311 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Seed Board','true');");
\r
313 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
314 MessageID INTEGER PRIMARY KEY,\
\r
315 IdentityID INTEGER,\
\r
320 MessageUUID TEXT UNIQUE,\
\r
321 ReplyBoardID INTEGER,\
\r
323 MessageIndex INTEGER,\
\r
324 Read INTEGER CHECK(Read IN(0,1)) DEFAULT 0\
\r
327 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
329 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
330 MessageID INTEGER,\
\r
331 ReplyToMessageUUID TEXT,\
\r
332 ReplyOrder INTEGER\
\r
335 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
336 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);");
\r
338 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
339 MessageID INTEGER,\
\r
343 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
344 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
346 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
347 IdentityID INTEGER,\
\r
349 RequestIndex INTEGER,\
\r
350 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
354 Key is for anonymous messages (future)
\r
356 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
357 IdentityID INTEGER,\
\r
359 RequestIndex INTEGER,\
\r
360 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
361 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
362 Tries INTEGER DEFAULT 0,\
\r
364 FromIdentityID INTEGER\
\r
367 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
369 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
370 LocalIdentityID INTEGER,\
\r
372 InsertIndex INTEGER,\
\r
373 MessageUUID TEXT UNIQUE,\
\r
375 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
379 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
380 FileInsertID INTEGER PRIMARY KEY,\
\r
389 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
390 LocalIdentityID INTEGER,\
\r
392 InsertIndex INTEGER,\
\r
393 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
396 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
397 BoardID INTEGER UNIQUE,\
\r
398 ModifyLocalMessageTrust INTEGER,\
\r
399 ModifyLocalTrustListTrust INTEGER\
\r
402 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
403 LocalIdentityID INTEGER,\
\r
405 InsertIndex INTEGER,\
\r
406 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
409 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
410 IdentityID INTEGER,\
\r
412 RequestIndex INTEGER,\
\r
413 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
416 // begin thread db schema
\r
417 db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\
\r
418 ThreadID INTEGER PRIMARY KEY,\
\r
420 FirstMessageID INTEGER,\
\r
421 LastMessageID INTEGER\
\r
424 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");
\r
425 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");
\r
426 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");
\r
428 db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\
\r
430 MessageID INTEGER,\
\r
434 db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_ThreadID ON tblThreadPost(ThreadID);");
\r
435 db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");
\r
437 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\
\r
440 DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\
\r
442 // end thread db schema
\r
444 // low / high / message count for each board
\r
445 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
446 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
447 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
448 WHERE MessageID>=0 OR MessageID IS NULL \
\r
449 GROUP BY tblBoard.BoardID;");
\r
451 // calculates peer trust
\r
452 // 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
453 // need the +1 so that when the values are 0 the result is not 0
\r
454 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
455 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
456 SELECT TargetIdentityID, \
\r
457 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
458 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
459 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
460 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
461 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
462 GROUP BY TargetIdentityID;");
\r
464 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
465 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
466 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
467 GROUP BY tblIdentity.IdentityID;");
\r
470 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
471 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
473 // drop existing triggers
\r
474 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
475 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
476 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
477 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
479 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
480 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
483 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
486 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
487 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\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 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
494 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
497 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
498 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
501 // 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
502 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
505 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
509 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
512 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
513 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
516 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
517 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
520 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
521 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
522 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
523 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
524 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
525 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
526 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
529 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
530 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
533 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
534 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
535 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
536 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
537 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
538 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
539 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
542 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
545 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
548 // delete introduction puzzles that were half-way inserted
\r
549 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
551 // delete stale introduction puzzles (2 or more days old)
\r
552 date-=Poco::Timespan(2,0,0,0,0);
\r
553 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
554 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
556 date=Poco::Timestamp();
\r
557 // insert SomeDude's public key
\r
558 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
559 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
560 //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
561 // insert garfield's public key -haven't seen in a while - disabling for now
\r
562 //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
563 // insert alek's public key - haven't seen in a while - disabling for now
\r
564 //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
565 // insert Luke771's public key
\r
566 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
567 // insert falafel's public key
\r
568 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
569 // insert cptn_insano's public key
\r
570 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
571 // insert Flink's public key - haven't seen in a while - disabling for now
\r
572 //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
573 // insert Kane's public key
\r
574 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
575 // inserts boardstat's public key
\r
576 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
578 // TODO remove sometime after 0.1.17
\r
579 FixCapitalBoardNames(db);
\r
581 // run analyze - may speed up some queries
\r
582 db->Execute("ANALYZE;");
\r
586 const bool VerifyDB(SQLite3DB::DB *db)
\r
588 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
590 if(st.RowReturned())
\r
592 std::string res="";
\r
593 st.ResultText(0,res);
\r
600 // try to reindex and vacuum database in case of index corruption
\r
601 st=db->Prepare("REINDEX;");
\r
603 st=db->Prepare("VACUUM;");
\r
606 // check integrity again
\r
607 st=db->Prepare("PRAGMA integrity_check;");
\r
609 st.ResultText(0,res);
\r
626 const std::string TestDBIntegrity(SQLite3DB::DB *db)
\r
628 std::string result="";
\r
630 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
632 while(st.RowReturned())
\r
634 std::string text="";
\r
635 st.ResultText(0,text);
\r