version 0.0.4

[fms.git] / src / identitytestglobal.cpp

diff --git a/src/identitytestglobal.cpp b/src/identitytestglobal.cpp
index 2acca4d..985cb3f 100644 (file)
--- a/src/identitytestglobal.cpp
+++ b/src/identitytestglobal.cpp
@@ -35,12 +35,16 @@ void SetupDB()
                                PublicKey                               TEXT,\\r
                                PrivateKey                              TEXT,\\r
                                SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
                                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
                                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
                                );");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r


@@ -49,6 +53,19 @@ void SetupDB()
                                InsertIndex                     INTEGER\\r
                                );");\r
 \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
        db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
                                UUID                            TEXT UNIQUE,\\r
                                LocalIdentityID         INTEGER,\\r


@@ -66,6 +83,8 @@ void SetupDB()
                                PublicKey                       TEXT,\\r
                                Name                            TEXT,\\r
                                SingleUse                       BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\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
                                DateAdded                       DATETIME,\\r
                                LastSeen                        DATETIME,\\r
                                LocalMessageTrust       INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT 50,\\r


@@ -75,7 +94,7 @@ void SetupDB()
                                );");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\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
                                Day                                     DATE,\\r
                                RequestIndex            INTEGER,\\r
                                Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r


@@ -100,6 +119,90 @@ void SetupDB()
                                Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
                                );");\r
 \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
 }\r
 \r
 void SetupDefaultOptions()\r


@@ -122,7 +225,7 @@ void SetupDefaultOptions()
        // StartFreenetUpdater\r
        st.Bind(0,"StartFreenetUpdater");\r
        st.Bind(1,"true");\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


@@ -164,6 +267,18 @@ void SetupDefaultOptions()
        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
 }\r
 \r
 void SetupLogFile()\r