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
21 Option::Instance()->Get("VacuumOnStartup",tempval);
\r
24 db->Execute("VACUUM;");
\r
27 db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\
\r
32 SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");
\r
34 if(st.RowReturned())
\r
38 st.ResultInt(0,major);
\r
39 st.ResultInt(1,minor);
\r
41 if(major==1 && minor==0)
\r
43 ConvertDB0100To0101();
\r
47 if(major==1 && (minor==1 || minor==2))
\r
49 ConvertDB0101To0103();
\r
53 if(major==1 && minor==3)
\r
55 ConvertDB0103To0104();
\r
59 if(major==1 && minor==4)
\r
61 ConvertDB0104To0105();
\r
65 if(major==1 && minor==5)
\r
67 ConvertDB0105To0106();
\r
71 if(major==1 && minor==6)
\r
73 ConvertDB0106To0107();
\r
77 if(major==1 && minor==7)
\r
79 ConvertDB0107To0108();
\r
83 if(major==1 && minor==8)
\r
85 ConvertDB0108To0109();
\r
89 if(major==1 && minor==9)
\r
91 ConvertDB0109To0110();
\r
95 if(major==1 && minor==10)
\r
97 ConvertDB0110To0111();
\r
101 if(major==1 && minor==11)
\r
103 ConvertDB0111To0112();
\r
110 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,12);");
\r
113 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=12;");
\r
115 db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
\r
125 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");
\r
127 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
128 Option TEXT UNIQUE,\
\r
129 OptionValue TEXT NOT NULL,\
\r
130 OptionDescription TEXT,\
\r
132 SortOrder INTEGER,\
\r
136 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
137 LocalIdentityID INTEGER PRIMARY KEY,\
\r
139 PublicKey TEXT UNIQUE,\
\r
140 PrivateKey TEXT UNIQUE,\
\r
141 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
142 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
143 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
144 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
145 FreesiteEdition INTEGER,\
\r
146 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
147 LastInsertedIdentity DATETIME,\
\r
148 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
149 LastInsertedPuzzle DATETIME,\
\r
150 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
151 LastInsertedTrustList DATETIME,\
\r
152 LastInsertedBoardList DATETIME,\
\r
153 LastInsertedMessageList DATETIME,\
\r
154 LastInsertedFreesite DATETIME,\
\r
155 DateCreated DATETIME,\
\r
156 MinMessageDelay INTEGER DEFAULT 0,\
\r
157 MaxMessageDelay INTEGER DEFAULT 0\
\r
160 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
161 LocalIdentityID INTEGER,\
\r
163 InsertIndex INTEGER\
\r
166 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
167 LocalIdentityID INTEGER,\
\r
169 InsertIndex INTEGER\
\r
172 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
173 IdentityID INTEGER,\
\r
175 RequestIndex INTEGER,\
\r
176 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
179 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
181 LocalIdentityID INTEGER,\
\r
183 InsertIndex INTEGER,\
\r
187 PuzzleSolution TEXT,\
\r
188 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
191 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
192 IdentityID INTEGER PRIMARY KEY,\
\r
193 PublicKey TEXT UNIQUE,\
\r
195 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
196 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
197 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
198 FreesiteEdition INTEGER,\
\r
199 DateAdded DATETIME,\
\r
200 LastSeen DATETIME,\
\r
201 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
202 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
203 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
204 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
206 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
207 PurgeDate DATETIME\
\r
210 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
211 IdentityID INTEGER,\
\r
213 RequestIndex INTEGER,\
\r
214 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
217 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
218 IdentityID INTEGER,\
\r
220 RequestIndex INTEGER,\
\r
221 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
228 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
229 LocalIdentityID INTEGER,\
\r
233 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
236 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
237 LocalIdentityID INTEGER,\
\r
238 IdentityID INTEGER,\
\r
239 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
240 MessageTrustComment TEXT,\
\r
241 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
242 TrustListTrustComment TEXT\
\r
245 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
247 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
250 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
253 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
256 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
259 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
262 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
265 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
266 IdentityID INTEGER,\
\r
267 TargetIdentityID INTEGER,\
\r
268 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
269 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
270 MessageTrustComment TEXT,\
\r
271 TrustListTrustComment TEXT\
\r
274 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
275 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
277 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
278 BoardID INTEGER PRIMARY KEY,\
\r
279 BoardName TEXT UNIQUE,\
\r
280 BoardDescription TEXT,\
\r
281 DateAdded DATETIME,\
\r
282 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
286 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");
\r
287 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");
\r
288 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");
\r
289 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");
\r
291 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
292 MessageID INTEGER PRIMARY KEY,\
\r
293 IdentityID INTEGER,\
\r
298 MessageUUID TEXT UNIQUE,\
\r
299 ReplyBoardID INTEGER,\
\r
301 MessageIndex INTEGER\
\r
304 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
306 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
307 MessageID INTEGER,\
\r
308 ReplyToMessageUUID TEXT,\
\r
309 ReplyOrder INTEGER\
\r
312 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
314 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
315 MessageID INTEGER,\
\r
319 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
320 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
322 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
323 IdentityID INTEGER,\
\r
325 RequestIndex INTEGER,\
\r
326 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
329 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
330 IdentityID INTEGER,\
\r
332 RequestIndex INTEGER,\
\r
333 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
334 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
337 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
339 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
340 LocalIdentityID INTEGER,\
\r
342 InsertIndex INTEGER,\
\r
343 MessageUUID TEXT UNIQUE,\
\r
345 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
349 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
350 FileInsertID INTEGER PRIMARY KEY,\
\r
359 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
360 LocalIdentityID INTEGER,\
\r
362 InsertIndex INTEGER,\
\r
363 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
366 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
367 BoardID INTEGER UNIQUE,\
\r
368 ModifyLocalMessageTrust INTEGER,\
\r
369 ModifyLocalTrustListTrust INTEGER\
\r
372 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
373 LocalIdentityID INTEGER,\
\r
375 InsertIndex INTEGER,\
\r
376 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
379 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
380 IdentityID INTEGER,\
\r
382 RequestIndex INTEGER,\
\r
383 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
386 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
387 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
388 LocalIdentityID INTEGER,\
\r
392 // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page
\r
393 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\
\r
394 LocalIdentityID INTEGER\
\r
397 // low / high / message count for each board
\r
398 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
399 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
400 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
401 WHERE MessageID>=0 OR MessageID IS NULL \
\r
402 GROUP BY tblBoard.BoardID;");
\r
404 // calculates peer trust
\r
405 // 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
406 // need the +1 so that when the values are 0 the result is not 0
\r
407 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
408 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
409 SELECT TargetIdentityID, \
\r
410 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
411 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
412 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
413 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
414 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
415 GROUP BY TargetIdentityID;");
\r
417 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
418 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
419 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
420 GROUP BY tblIdentity.IdentityID;");
\r
423 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
424 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
426 // drop existing triggers
\r
427 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
428 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
429 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
430 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
432 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
433 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
436 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
439 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
440 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
443 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
446 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
447 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
450 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=old.TargetIdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=old.TargetIdentityID) WHERE IdentityID=old.TargetIdentityID;\
\r
451 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
454 // 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
455 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
458 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
462 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
465 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
466 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
469 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
470 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
473 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
474 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
475 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
476 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
477 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
478 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
479 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
482 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
483 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
486 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
487 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
488 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
489 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
490 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
491 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
492 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
495 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
498 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
501 // delete introduction puzzles that were half-way inserted
\r
502 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
504 // delete stale introduction puzzles (2 or more days old)
\r
505 date-=Poco::Timespan(2,0,0,0,0);
\r
506 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
507 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
509 date=Poco::Timestamp();
\r
510 // insert SomeDude's public key
\r
511 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
512 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
513 //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
514 // insert garfield's public key
\r
515 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
516 // insert alek's public key
\r
517 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
518 // insert Luke771's public key
\r
519 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
520 // insert falafel's public key
\r
521 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
522 // insert cptn_insano's public key
\r
523 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
524 // insert Flink's public key
\r
525 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
526 // insert Kane's public key
\r
527 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
528 // inserts boardstat's public key
\r
529 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
531 // TODO remove sometime after 0.1.17
\r
532 FixCapitalBoardNames();
\r
534 // run analyze - may speed up some queries
\r
535 db->Execute("ANALYZE;");
\r