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(10000); // set timeout to 10 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 tblOption(\
\r
116 Option TEXT UNIQUE,\
\r
117 OptionValue TEXT NOT NULL,\
\r
118 OptionDescription TEXT,\
\r
120 SortOrder INTEGER,\
\r
124 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
125 LocalIdentityID INTEGER PRIMARY KEY,\
\r
127 PublicKey TEXT UNIQUE,\
\r
128 PrivateKey TEXT UNIQUE,\
\r
129 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
130 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
131 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
132 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
133 FreesiteEdition INTEGER,\
\r
134 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
135 LastInsertedIdentity DATETIME,\
\r
136 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
137 LastInsertedPuzzle DATETIME,\
\r
138 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
139 LastInsertedTrustList DATETIME,\
\r
140 LastInsertedBoardList DATETIME,\
\r
141 LastInsertedMessageList DATETIME,\
\r
142 LastInsertedFreesite DATETIME,\
\r
143 DateCreated DATETIME,\
\r
144 MinMessageDelay INTEGER DEFAULT 0,\
\r
145 MaxMessageDelay INTEGER DEFAULT 0\
\r
148 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
149 LocalIdentityID INTEGER,\
\r
151 InsertIndex INTEGER\
\r
154 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
155 LocalIdentityID INTEGER,\
\r
157 InsertIndex INTEGER\
\r
160 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
161 IdentityID INTEGER,\
\r
163 RequestIndex INTEGER,\
\r
164 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
167 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
169 LocalIdentityID INTEGER,\
\r
171 InsertIndex INTEGER,\
\r
175 PuzzleSolution TEXT,\
\r
176 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
179 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
180 IdentityID INTEGER PRIMARY KEY,\
\r
181 PublicKey TEXT UNIQUE,\
\r
183 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
184 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
185 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
186 FreesiteEdition INTEGER,\
\r
187 DateAdded DATETIME,\
\r
188 LastSeen DATETIME,\
\r
189 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
190 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
191 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
192 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
194 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\
\r
195 PurgeDate DATETIME\
\r
198 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
199 IdentityID INTEGER,\
\r
201 RequestIndex INTEGER,\
\r
202 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
205 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
206 IdentityID INTEGER,\
\r
208 RequestIndex INTEGER,\
\r
209 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
216 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
217 LocalIdentityID INTEGER,\
\r
221 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
224 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
225 LocalIdentityID INTEGER,\
\r
226 IdentityID INTEGER,\
\r
227 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
228 MessageTrustComment TEXT,\
\r
229 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
230 TrustListTrustComment TEXT\
\r
233 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
235 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
238 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
241 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
244 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
247 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
250 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
253 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
254 IdentityID INTEGER,\
\r
255 TargetIdentityID INTEGER,\
\r
256 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
257 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
258 MessageTrustComment TEXT,\
\r
259 TrustListTrustComment TEXT\
\r
262 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
263 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
265 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
266 BoardID INTEGER PRIMARY KEY,\
\r
267 BoardName TEXT UNIQUE,\
\r
268 BoardDescription TEXT,\
\r
269 DateAdded DATETIME,\
\r
270 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
274 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");
\r
275 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");
\r
276 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");
\r
277 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");
\r
279 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
280 MessageID INTEGER PRIMARY KEY,\
\r
281 IdentityID INTEGER,\
\r
286 MessageUUID TEXT UNIQUE,\
\r
287 ReplyBoardID INTEGER,\
\r
289 MessageIndex INTEGER\
\r
292 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
294 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
295 MessageID INTEGER,\
\r
296 ReplyToMessageUUID TEXT,\
\r
297 ReplyOrder INTEGER\
\r
300 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
302 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
303 MessageID INTEGER,\
\r
307 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
308 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
310 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
311 IdentityID INTEGER,\
\r
313 RequestIndex INTEGER,\
\r
314 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
317 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
318 IdentityID INTEGER,\
\r
320 RequestIndex INTEGER,\
\r
321 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
322 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
325 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
327 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
328 LocalIdentityID INTEGER,\
\r
330 InsertIndex INTEGER,\
\r
331 MessageUUID TEXT UNIQUE,\
\r
333 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\
\r
337 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
338 FileInsertID INTEGER PRIMARY KEY,\
\r
347 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
348 LocalIdentityID INTEGER,\
\r
350 InsertIndex INTEGER,\
\r
351 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
354 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
355 BoardID INTEGER UNIQUE,\
\r
356 ModifyLocalMessageTrust INTEGER,\
\r
357 ModifyLocalTrustListTrust INTEGER\
\r
360 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
361 LocalIdentityID INTEGER,\
\r
363 InsertIndex INTEGER,\
\r
364 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
367 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
368 IdentityID INTEGER,\
\r
370 RequestIndex INTEGER,\
\r
371 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
374 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
375 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
376 LocalIdentityID INTEGER,\
\r
380 // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page
\r
381 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\
\r
382 LocalIdentityID INTEGER\
\r
385 // low / high / message count for each board
\r
386 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
387 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
388 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
389 WHERE MessageID>=0 OR MessageID IS NULL \
\r
390 GROUP BY tblBoard.BoardID;");
\r
392 // calculates peer trust
\r
393 // 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
394 // need the +1 so that when the values are 0 the result is not 0
\r
395 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
396 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
397 SELECT TargetIdentityID, \
\r
398 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
399 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
400 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
401 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
402 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
403 GROUP BY TargetIdentityID;");
\r
405 db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \
\r
406 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \
\r
407 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \
\r
408 GROUP BY tblIdentity.IdentityID;");
\r
411 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
412 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
414 // drop existing triggers
\r
415 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
416 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
417 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
418 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
420 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
421 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
424 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
427 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
428 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
431 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
434 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
435 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
438 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 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
442 // 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
443 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
446 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
450 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
453 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
454 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
457 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
458 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
461 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
462 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
463 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
464 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
465 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
466 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
467 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
470 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
471 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
474 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
475 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
476 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
477 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
478 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
479 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
480 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
483 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
486 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
489 // delete introduction puzzles that were half-way inserted
\r
490 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
492 // delete stale introduction puzzles (2 or more days old)
\r
493 date-=Poco::Timespan(2,0,0,0,0);
\r
494 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
495 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");
\r
497 date=Poco::Timestamp();
\r
498 // insert SomeDude's public key
\r
499 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
500 // insert Shadow Panther's public key - haven't seen in a while - disabling for now
\r
501 //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
502 // insert garfield's public key
\r
503 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
504 // insert alek's public key
\r
505 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
506 // insert Luke771's public key
\r
507 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
508 // insert falafel's public key
\r
509 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
510 // insert cptn_insano's public key
\r
511 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
512 // insert Flink's public key
\r
513 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
514 // insert Kane's public key
\r
515 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
516 // inserts boardstat's public key
\r
517 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
519 // TODO remove sometime after 0.1.17
\r
520 FixCapitalBoardNames();
\r
522 // run analyze - may speed up some queries
\r
523 db->Execute("ANALYZE;");
\r