PublicKey TEXT,\\r
PrivateKey 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
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
+ LastInsertedTrustList DATETIME,\\r
+ InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedBoardList DATETIME\\r
);");\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\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
PublicKey TEXT,\\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
DateAdded DATETIME,\\r
LastSeen DATETIME,\\r
LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT 50,\\r
);");\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
- IdentityID INTEGER PRIMARY KEY,\\r
+ IdentityID INTEGER,\\r
Day DATE,\\r
RequestIndex INTEGER,\\r
Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
);");\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
+ );");\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
+ );");\r
+\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01');");\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('freenet','Discussion about Freenet','2007-12-01');");\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('public','Public discussion','2007-12-01');");\r
+ db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('test','Test board','2007-12-01');");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
+ MessageID INTEGER PRIMARY KEY,\\r
+ MessageDate DATE,\\r
+ MessageTime TIME,\\r
+ Subject TEXT,\\r
+ MessageUUID TEXT UNIQUE,\\r
+ ReplyBoardID INTEGER,\\r
+ Body TEXT\\r
+ );");\r
+\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
+ MessageID INTEGER,\\r
+ ReplyToMessageID INTEGER,\\r
+ Order INTEGER\\r
+ );");\r
+ \r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
+ MessageID INTEGER,\\r
+ BoardID 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
+ GROUP BY tblBoard.BoardID;");\r
+\r
+ // calculates peer trust\r
+ db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
+ SELECT TargetIdentityID, \\r
+ ROUND(SUM(MessageTrust*(LocalMessageTrust/100.0))/SUM(LocalMessageTrust/100.0),0) AS 'PeerMessageTrust', \\r
+ ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(LocalTrustListTrust/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
+ GROUP BY TargetIdentityID;");\r
+\r
+ // update PeerTrustLevel when deleting a record from tblPeerTrust\r
+ db->Execute("CREATE TRIGGER 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 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 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 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
\r
void SetupDefaultOptions()\r
// StartFreenetUpdater\r
st.Bind(0,"StartFreenetUpdater");\r
st.Bind(1,"true");\r
- st.Bind(2,"Start Freenet Updater.");\r
+ st.Bind(2,"Start Freenet Updater thread.");\r
st.Step();\r
st.Reset();\r
\r
st.Step();\r
st.Reset();\r
\r
+ st.Bind(0,"MaxTrustListRequests");\r
+ st.Bind(1,"5");\r
+ st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");\r
+ st.Step();\r
+ st.Reset();\r
+\r
+ st.Bind(0,"MinLocalTrustListTrust");\r
+ st.Bind(1,"50");\r
+ st.Bind(2,"Specifies a local trust list trust level that a peer must have before its trust list will be included in the weighted average. Any peers below this number will be excluded from the results.");\r
+ st.Step();\r
+ st.Reset();\r
+\r
}\r
\r
void SetupLogFile()\r
projects / fms.git / blobdiff