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
107 if(major==1 && minor==13)
\r
109 ConvertDB0113To0114();
\r
116 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,14);");
\r
119 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=14;");
\r
121 db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
\r
131 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");
\r
133 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
134 Option TEXT UNIQUE,\
\r
135 OptionValue TEXT NOT NULL,\
\r
136 OptionDescription TEXT,\
\r
138 SortOrder INTEGER,\
\r
142 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
143 LocalIdentityID INTEGER PRIMARY KEY,\
\r
145 PublicKey TEXT UNIQUE,\
\r
146 PrivateKey TEXT UNIQUE,\
\r
147 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
148 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
149 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
150 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
151 FreesiteEdition INTEGER,\
\r
152 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
153 LastInsertedIdentity DATETIME,\
\r
154 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
155 LastInsertedPuzzle DATETIME,\
\r
156 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
157 LastInsertedTrustList DATETIME,\
\r
158 LastInsertedBoardList DATETIME,\
\r
159 LastInsertedMessageList DATETIME,\
\r
160 LastInsertedFreesite DATETIME,\
\r
161 DateCreated DATETIME,\
\r
162 MinMessageDelay INTEGER DEFAULT 0,\
\r
163 MaxMessageDelay INTEGER DEFAULT 0\
\r
166 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
167 LocalIdentityID INTEGER,\
\r
169 InsertIndex INTEGER\
\r
172 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
173 LocalIdentityID INTEGER,\
\r
175 InsertIndex INTEGER\
\r
178 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
179 IdentityID INTEGER,\
\r
181 RequestIndex INTEGER,\
\r
182 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
185 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
187 LocalIdentityID INTEGER,\
\r
189 InsertIndex INTEGER,\
\r
193 PuzzleSolution TEXT,\
\r
194 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
198 PurgeDate is not used yet
\r
200 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
201 IdentityID INTEGER PRIMARY KEY,\
\r
202 PublicKey TEXT UNIQUE,\
\r
204 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
205 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
206 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
207 FreesiteEdition INTEGER,\
\r
208 DateAdded DATETIME,\
\r
209 LastSeen DATETIME,\
\r
210 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
211 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
212 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
213 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
215 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
216 PurgeDate DATETIME\
\r
219 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
220 IdentityID INTEGER,\
\r
222 RequestIndex INTEGER,\
\r
223 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
226 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
227 IdentityID INTEGER,\
\r
229 RequestIndex INTEGER,\
\r
230 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
237 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
238 LocalIdentityID INTEGER,\
\r
242 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
245 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
246 LocalIdentityID INTEGER,\
\r
247 IdentityID INTEGER,\
\r
248 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
249 MessageTrustComment TEXT,\
\r
250 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
251 TrustListTrustComment TEXT\
\r
254 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
256 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT 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 trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
265 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
268 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
271 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
274 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
275 IdentityID INTEGER,\
\r
276 TargetIdentityID INTEGER,\
\r
277 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
278 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
279 MessageTrustComment TEXT,\
\r
280 TrustListTrustComment TEXT\
\r
283 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
284 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
286 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
287 BoardID INTEGER PRIMARY KEY,\
\r
288 BoardName TEXT UNIQUE,\
\r
289 BoardDescription TEXT,\
\r
290 DateAdded DATETIME,\
\r
291 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
293 Forum TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\
\r
296 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board','true');");
\r
297 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board','true');");
\r
298 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board','true');");
\r
299 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board','true');");
\r
301 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
302 MessageID INTEGER PRIMARY KEY,\
\r
303 IdentityID INTEGER,\
\r
308 MessageUUID TEXT UNIQUE,\
\r
309 ReplyBoardID INTEGER,\
\r
311 MessageIndex INTEGER,\
\r
312 Read INTEGER CHECK(Read IN(0,1)) DEFAULT 0\
\r
315 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
317 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
318 MessageID INTEGER,\
\r
319 ReplyToMessageUUID TEXT,\
\r
320 ReplyOrder INTEGER\
\r
323 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
324 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);");
\r
326 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
327 MessageID INTEGER,\
\r
331 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
332 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
334 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
335 IdentityID INTEGER,\
\r
337 RequestIndex INTEGER,\
\r
338 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
342 Key is for anonymous messages (future)
\r
344 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
345 IdentityID INTEGER,\
\r
347 RequestIndex INTEGER,\
\r
348 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
349 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
350 Tries INTEGER DEFAULT 0,\
\r
354 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
356 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
357 LocalIdentityID INTEGER,\
\r
359 InsertIndex INTEGER,\
\r
360 MessageUUID TEXT UNIQUE,\
\r
362 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
366 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
367 FileInsertID INTEGER PRIMARY KEY,\
\r
376 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
377 LocalIdentityID INTEGER,\
\r
379 InsertIndex INTEGER,\
\r
380 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
383 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
384 BoardID INTEGER UNIQUE,\
\r
385 ModifyLocalMessageTrust INTEGER,\
\r
386 ModifyLocalTrustListTrust INTEGER\
\r
389 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\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 tblBoardListRequests(\
\r
397 IdentityID INTEGER,\
\r
399 RequestIndex INTEGER,\
\r
400 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
403 // begin thread db schema
\r
404 db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\
\r
405 ThreadID INTEGER PRIMARY KEY,\
\r
407 FirstMessageID INTEGER,\
\r
408 LastMessageID INTEGER\
\r
411 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");
\r
412 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");
\r
413 db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");
\r
415 db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\
\r
417 MessageID INTEGER,\
\r
421 db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_ThreadID ON tblThreadPost(ThreadID);");
\r
422 db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");
\r
424 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\
\r
427 DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\
\r
429 // end thread db schema
\r
431 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
432 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
433 LocalIdentityID INTEGER,\
\r
437 // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page
\r
438 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\
\r
439 LocalIdentityID INTEGER\
\r
442 // Temporary table for form passwords
\r
443 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpFormPassword(\
\r
448 // low / high / message count for each board
\r
449 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
450 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
451 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
452 WHERE MessageID>=0 OR MessageID IS NULL \
\r
453 GROUP BY tblBoard.BoardID;");
\r
455 // calculates peer trust
\r
456 // 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
457 // need the +1 so that when the values are 0 the result is not 0
\r
458 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
459 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
460 SELECT TargetIdentityID, \
\r
461 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
462 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
463 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
464 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
465 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
466 GROUP BY TargetIdentityID;");
\r
468 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
469 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
470 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
471 GROUP BY tblIdentity.IdentityID;");
\r
474 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
475 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
477 // drop existing triggers
\r
478 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
479 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
480 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
481 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
483 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
484 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
487 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 PeerTrustLevel when inserting a record into tblPeerTrust
\r
491 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
494 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
497 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
498 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
501 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
502 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
505 // 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
506 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
509 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
513 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
516 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
517 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
520 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
521 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
524 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
525 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
526 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
527 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
528 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
529 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
530 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
533 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
534 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
537 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
538 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
539 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
540 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
541 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
542 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
543 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
546 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
549 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
552 // delete introduction puzzles that were half-way inserted
\r
553 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
555 // delete stale introduction puzzles (2 or more days old)
\r
556 date-=Poco::Timespan(2,0,0,0,0);
\r
557 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
558 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
560 date=Poco::Timestamp();
\r
561 // insert SomeDude's public key
\r
562 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
563 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
564 //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
565 // insert garfield's public key -haven't seen in a while - disabling for now
\r
566 //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
567 // insert alek's public key - haven't seen in a while - disabling for now
\r
568 //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
569 // insert Luke771's public key
\r
570 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
571 // insert falafel's public key
\r
572 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
573 // insert cptn_insano's public key
\r
574 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
575 // insert Flink's public key
\r
576 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
577 // insert Kane's public key
\r
578 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
579 // inserts boardstat's public key
\r
580 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
582 // TODO remove sometime after 0.1.17
\r
583 FixCapitalBoardNames();
\r
585 // run analyze - may speed up some queries
\r
586 db->Execute("ANALYZE;");
\r
590 const bool VerifyDB()
\r
592 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
593 SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");
\r
595 if(st.RowReturned())
\r
597 std::string res="";
\r
598 st.ResultText(0,res);
\r