--- /dev/null
+#include "../include/dbsetup.h"\r
+#include "../include/dbconversions.h"\r
+#include "../include/option.h"\r
+#include "../include/db/sqlite3db.h"\r
+\r
+#include <Poco/DateTime.h>\r
+#include <Poco/Timespan.h>\r
+#include <Poco/DateTimeFormatter.h>\r
+\r
+void SetupDB()\r
+{\r
+\r
+ Poco::DateTime date;\r
+ std::string tempval="";\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+\r
+ db->Open("fms.db3");\r
+ db->SetBusyTimeout(10000); // set timeout to 10 seconds\r
+\r
+ tempval="";\r
+ Option::Instance()->Get("VacuumOnStartup",tempval);\r
+ if(tempval=="true")\r
+ {\r
+ db->Execute("VACUUM;");\r
+ }\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
+ Major INTEGER,\\r
+ Minor INTEGER\\r
+ );");\r
+\r
+ SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");\r
+ st.Step();\r
+ if(st.RowReturned())\r
+ {\r
+ int major;\r
+ int minor;\r
+ st.ResultInt(0,major);\r
+ st.ResultInt(1,minor);\r
+ st.Finalize();\r
+ if(major==1 && minor==0)\r
+ {\r
+ ConvertDB0100To0101();\r
+ major=1;\r
+ minor=1;\r
+ }\r
+ if(major==1 && (minor==1 || minor==2))\r
+ {\r
+ ConvertDB0101To0103();\r
+ major=1;\r
+ minor=3;\r
+ }\r
+ if(major==1 && minor==3)\r
+ {\r
+ ConvertDB0103To0104();\r
+ major=1;\r
+ minor=4;\r
+ }\r
+ if(major==1 && minor==4)\r
+ {\r
+ ConvertDB0104To0105();\r
+ major=1;\r
+ minor=5;\r
+ }\r
+ if(major==1 && minor==5)\r
+ {\r
+ ConvertDB0105To0106();\r
+ major=1;\r
+ minor=6;\r
+ }\r
+ if(major==1 && minor==6)\r
+ {\r
+ ConvertDB0106To0107();\r
+ major=1;\r
+ minor=7;\r
+ }\r
+ if(major==1 && minor==7)\r
+ {\r
+ ConvertDB0107To0108();\r
+ major=1;\r
+ minor=8;\r
+ }\r
+ if(major==1 && minor==8)\r
+ {\r
+ ConvertDB0108To0109();\r
+ major=1;\r
+ minor=9;\r
+ }\r
+ if(major==1 && minor==9)\r
+ {\r
+ ConvertDB0109To0110();\r
+ major=1;\r
+ minor=10;\r
+ }\r
+ if(major==1 && minor==10)\r
+ {\r
+ ConvertDB0110To0111();\r
+ major=1;\r
+ minor=11;\r
+ }\r
+ if(major==1 && minor==11)\r
+ {\r
+ ConvertDB0111To0112();\r
+ major=1;\r
+ minor=12;\r
+ }\r
+ }\r
+ else\r
+ {\r
+ db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,12);");\r
+ }\r
+\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=12;");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
+ Option TEXT UNIQUE,\\r
+ OptionValue TEXT NOT NULL,\\r
+ OptionDescription TEXT,\\r
+ Section TEXT,\\r
+ SortOrder INTEGER,\\r
+ ValidValues TEXT\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
+ LocalIdentityID INTEGER PRIMARY KEY,\\r
+ Name TEXT,\\r
+ PublicKey TEXT UNIQUE,\\r
+ PrivateKey TEXT UNIQUE,\\r
+ SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
+ PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
+ PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
+ PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
+ FreesiteEdition INTEGER,\\r
+ InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedIdentity DATETIME,\\r
+ InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedPuzzle DATETIME,\\r
+ InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedTrustList DATETIME,\\r
+ LastInsertedBoardList DATETIME,\\r
+ LastInsertedMessageList DATETIME,\\r
+ LastInsertedFreesite DATETIME,\\r
+ DateCreated DATETIME,\\r
+ MinMessageDelay INTEGER DEFAULT 0,\\r
+ MaxMessageDelay INTEGER DEFAULT 0\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r
+ LocalIdentityID INTEGER,\\r
+ Day DATE,\\r
+ InsertIndex INTEGER\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\\r
+ LocalIdentityID INTEGER,\\r
+ Day DATE,\\r
+ InsertIndex INTEGER\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\\r
+ IdentityID INTEGER,\\r
+ Day DATE,\\r
+ RequestIndex INTEGER,\\r
+ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
+ UUID TEXT UNIQUE,\\r
+ LocalIdentityID INTEGER,\\r
+ Day DATE,\\r
+ InsertIndex INTEGER,\\r
+ Type TEXT,\\r
+ MimeType TEXT,\\r
+ PuzzleData TEXT,\\r
+ PuzzleSolution TEXT,\\r
+ FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
+ IdentityID INTEGER PRIMARY KEY,\\r
+ PublicKey TEXT UNIQUE,\\r
+ Name TEXT,\\r
+ SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
+ PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
+ PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
+ FreesiteEdition INTEGER,\\r
+ DateAdded DATETIME,\\r
+ LastSeen DATETIME,\\r
+ LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ AddedMethod TEXT,\\r
+ Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\\r
+ PurgeDate DATETIME\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
+ IdentityID INTEGER,\\r
+ Day DATE,\\r
+ RequestIndex INTEGER,\\r
+ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\\r
+ IdentityID INTEGER,\\r
+ Day DATE,\\r
+ RequestIndex INTEGER,\\r
+ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
+ UUID TEXT UNIQUE,\\r
+ Type TEXT,\\r
+ MimeType TEXT,\\r
+ PuzzleData TEXT\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\\r
+ LocalIdentityID INTEGER,\\r
+ Day DATE,\\r
+ UUID TEXT UNIQUE,\\r
+ Solution TEXT,\\r
+ Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\\r
+ LocalIdentityID INTEGER,\\r
+ IdentityID INTEGER,\\r
+ LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ MessageTrustComment TEXT,\\r
+ LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ TrustListTrustComment TEXT\\r
+ );");\r
+\r
+ db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ 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
+ END;");\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ 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
+ END;");\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ 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
+ END;");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
+ IdentityID INTEGER,\\r
+ TargetIdentityID INTEGER,\\r
+ MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\\r
+ TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\\r
+ MessageTrustComment TEXT,\\r
+ TrustListTrustComment TEXT\\r
+ );");\r
+\r
+ db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");\r
+ db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\\r
+ BoardID INTEGER PRIMARY KEY,\\r
+ BoardName TEXT UNIQUE,\\r
+ BoardDescription TEXT,\\r
+ DateAdded DATETIME,\\r
+ SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\\r
+ AddedMethod TEXT\\r
+ );");\r
+\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
+ MessageID INTEGER PRIMARY KEY,\\r
+ IdentityID INTEGER,\\r
+ FromName TEXT,\\r
+ MessageDate DATE,\\r
+ MessageTime TIME,\\r
+ Subject TEXT,\\r
+ MessageUUID TEXT UNIQUE,\\r
+ ReplyBoardID INTEGER,\\r
+ Body TEXT,\\r
+ MessageIndex INTEGER\\r
+ );");\r
+\r
+ db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
+ MessageID INTEGER,\\r
+ ReplyToMessageUUID TEXT,\\r
+ ReplyOrder INTEGER\\r
+ );");\r
+\r
+ db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
+ MessageID INTEGER,\\r
+ BoardID INTEGER\\r
+ );");\r
+\r
+ db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");\r
+ db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\\r
+ IdentityID INTEGER,\\r
+ Day DATE,\\r
+ RequestIndex INTEGER,\\r
+ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\\r
+ IdentityID INTEGER,\\r
+ Day DATE,\\r
+ RequestIndex INTEGER,\\r
+ FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
+ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\\r
+ LocalIdentityID INTEGER,\\r
+ Day DATE,\\r
+ InsertIndex INTEGER,\\r
+ MessageUUID TEXT UNIQUE,\\r
+ MessageXML TEXT,\\r
+ Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\\r
+ SendDate DATETIME\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
+ FileInsertID INTEGER PRIMARY KEY,\\r
+ MessageUUID TEXT,\\r
+ FileName TEXT,\\r
+ Key TEXT,\\r
+ Size INTEGER,\\r
+ MimeType TEXT,\\r
+ Data BLOB\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
+ LocalIdentityID INTEGER,\\r
+ Day DATE,\\r
+ InsertIndex INTEGER,\\r
+ Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\\r
+ BoardID INTEGER UNIQUE,\\r
+ ModifyLocalMessageTrust INTEGER,\\r
+ ModifyLocalTrustListTrust INTEGER\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\\r
+ LocalIdentityID INTEGER,\\r
+ Day DATE,\\r
+ InsertIndex INTEGER,\\r
+ Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\\r
+ IdentityID INTEGER,\\r
+ Day DATE,\\r
+ RequestIndex INTEGER,\\r
+ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
+ );"); \r
+\r
+ // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed\r
+ db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\\r
+ LocalIdentityID INTEGER,\\r
+ Date DATETIME\\r
+ );");\r
+\r
+ // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page\r
+ db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\\r
+ LocalIdentityID INTEGER\\r
+ );");\r
+\r
+ // low / high / message count for each board\r
+ db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
+ SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
+ FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
+ WHERE MessageID>=0 OR MessageID IS NULL \\r
+ GROUP BY tblBoard.BoardID;");\r
+\r
+ // calculates peer trust\r
+ // 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
+ // need the +1 so that when the values are 0 the result is not 0\r
+ db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
+ db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
+ SELECT TargetIdentityID, \\r
+ ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
+ ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
+ FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
+ WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
+ AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
+ GROUP BY TargetIdentityID;");\r
+\r
+ db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \\r
+ SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \\r
+ FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \\r
+ GROUP BY tblIdentity.IdentityID;");\r
+\r
+ /*\r
+ These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
+ All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
+ */\r
+ // drop existing triggers\r
+ db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
+ db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
+ db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
+ db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
+/*\r
+ // update PeerTrustLevel when deleting a record from tblPeerTrust\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ 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
+ END;");\r
+\r
+ // update PeerTrustLevel when inserting a record into tblPeerTrust\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ 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
+ END;");\r
+\r
+ // update PeerTrustLevel when updating a record in tblPeerTrust\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ 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
+ 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
+ END;");\r
+\r
+ // 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
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
+ END;");\r
+*/\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
+ DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
+ END;");\r
+\r
+ db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
+ DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
+ DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
+ DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
+ DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
+ DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
+ DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
+ END;");\r
+\r
+ db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ END;");\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
+ END;");\r
+\r
+ // delete introduction puzzles that were half-way inserted\r
+ db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
+\r
+ // delete stale introduction puzzles (2 or more days old)\r
+ date-=Poco::Timespan(2,0,0,0,0);\r
+ db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+ db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
+\r
+ date=Poco::Timestamp();\r
+ // insert SomeDude's public key\r
+ 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
+ // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
+ //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
+ // insert garfield's public key\r
+ 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
+ // insert alek's public key\r
+ 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
+ // insert Luke771's public key\r
+ 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
+ // insert falafel's public key\r
+ 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
+ // insert cptn_insano's public key\r
+ 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
+ // insert Flink's public key\r
+ 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
+ // insert Kane's public key\r
+ 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
+ // inserts boardstat's public key\r
+ 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
+\r
+ // TODO remove sometime after 0.1.17\r
+ FixCapitalBoardNames();\r
+\r
+ // run analyze - may speed up some queries\r
+ db->Execute("ANALYZE;");\r
+\r
+}\r
projects / fms.git / blobdiff