X-Git-Url: https://git.pterodactylus.net/?a=blobdiff_plain;f=src%2Fidentitytestglobal.cpp;h=985cb3f26d048496faf1fd1758eb105eff0ce432;hb=8adfd604a97d385869b0ce763b35d014d7aa2cca;hp=2acca4d1f98af4622388fefa3308badb06f56352;hpb=d8f51eac91f86a1e00a05a5058a8fa9eb8732464;p=fms.git

diff --git a/src/identitytestglobal.cpp b/src/identitytestglobal.cpp
index 2acca4d..985cb3f 100644
--- a/src/identitytestglobal.cpp
+++ b/src/identitytestglobal.cpp
@@ -35,12 +35,16 @@ void SetupDB()
 				PublicKey				TEXT,\
 				PrivateKey				TEXT,\
 				SingleUse				BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
+				PublishTrustList		BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
+				PublishBoardList		BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
 				InsertingIdentity		BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
 				LastInsertedIdentity	DATETIME,\
 				InsertingPuzzle			BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
 				LastInsertedPuzzle		DATETIME,\
 				InsertingTrustList		BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
-				LastInsertedTrustList	DATETIME\
+				LastInsertedTrustList	DATETIME,\
+				InsertingBoardList		BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
+				LastInsertedBoardList	DATETIME\
 				);");
 
 	db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
@@ -49,6 +53,19 @@ void SetupDB()
 				InsertIndex			INTEGER\
 				);");
 
+	db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
+				LocalIdentityID		INTEGER,\
+				Day					DATE,\
+				InsertIndex			INTEGER\
+				);");
+
+	db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
+				IdentityID			INTEGER,\
+				Day					DATE,\
+				RequestIndex		INTEGER,\
+				Found				BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
+				);");
+
 	db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
 				UUID				TEXT UNIQUE,\
 				LocalIdentityID		INTEGER,\
@@ -66,6 +83,8 @@ void SetupDB()
 				PublicKey			TEXT,\
 				Name				TEXT,\
 				SingleUse			BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
+				PublishTrustList	BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
+				PublishBoardList	BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
 				DateAdded			DATETIME,\
 				LastSeen			DATETIME,\
 				LocalMessageTrust	INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT 50,\
@@ -75,7 +94,7 @@ void SetupDB()
 				);");
 
 	db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
-				IdentityID			INTEGER PRIMARY KEY,\
+				IdentityID			INTEGER,\
 				Day					DATE,\
 				RequestIndex		INTEGER,\
 				Found				BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
@@ -100,6 +119,90 @@ void SetupDB()
 				Inserted			BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
 				);");
 
+	db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
+				IdentityID			INTEGER,\
+				TargetIdentityID	INTEGER,\
+				MessageTrust		INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
+				TrustListTrust		INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100)\
+				);");
+
+	db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
+				BoardID				INTEGER PRIMARY KEY,\
+				BoardName			TEXT UNIQUE,\
+				BoardDescription	TEXT,\
+				DateAdded			DATETIME\
+				);");
+
+	db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01');");
+	db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('freenet','Discussion about Freenet','2007-12-01');");
+	db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('public','Public discussion','2007-12-01');");
+	db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('test','Test board','2007-12-01');");
+
+	db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
+				MessageID			INTEGER PRIMARY KEY,\
+				MessageDate			DATE,\
+				MessageTime			TIME,\
+				Subject				TEXT,\
+				MessageUUID			TEXT UNIQUE,\
+				ReplyBoardID		INTEGER,\
+				Body				TEXT\
+				);");
+
+	db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
+				MessageID			INTEGER,\
+				ReplyToMessageID	INTEGER,\
+				Order				INTEGER\
+				);");
+	
+	db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
+				MessageID			INTEGER,\
+				BoardID				INTEGER\
+				);");
+
+	// low / high / message count for each board
+	db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
+				SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
+				FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
+				GROUP BY tblBoard.BoardID;");
+
+	// calculates peer trust
+	db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
+				SELECT TargetIdentityID, \
+				ROUND(SUM(MessageTrust*(LocalMessageTrust/100.0))/SUM(LocalMessageTrust/100.0),0) AS 'PeerMessageTrust', \
+				ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(LocalTrustListTrust/100.0),0) AS 'PeerTrustListTrust' \
+				FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
+				WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
+				GROUP BY TargetIdentityID;");
+
+	// update PeerTrustLevel when deleting a record from tblPeerTrust
+	db->Execute("CREATE TRIGGER trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
+				FOR EACH ROW \
+				BEGIN \
+					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;\
+				END;");
+
+	// update PeerTrustLevel when inserting a record into tblPeerTrust
+	db->Execute("CREATE TRIGGER trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
+				FOR EACH ROW \
+				BEGIN \
+					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;\
+				END;");
+
+	// update PeerTrustLevel when updating a record in tblPeerTrust
+	db->Execute("CREATE TRIGGER trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
+				FOR EACH ROW \
+				BEGIN \
+					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;\
+					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;\
+				END;");
+
+	// 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.
+	db->Execute("CREATE TRIGGER trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
+				FOR EACH ROW \
+				BEGIN \
+					UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
+				END;");
+
 }
 
 void SetupDefaultOptions()
@@ -122,7 +225,7 @@ void SetupDefaultOptions()
 	// StartFreenetUpdater
 	st.Bind(0,"StartFreenetUpdater");
 	st.Bind(1,"true");
-	st.Bind(2,"Start Freenet Updater.");
+	st.Bind(2,"Start Freenet Updater thread.");
 	st.Step();
 	st.Reset();
 
@@ -164,6 +267,18 @@ void SetupDefaultOptions()
 	st.Step();
 	st.Reset();
 
+	st.Bind(0,"MaxTrustListRequests");
+	st.Bind(1,"5");
+	st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");
+	st.Step();
+	st.Reset();
+
+	st.Bind(0,"MinLocalTrustListTrust");
+	st.Bind(1,"50");
+	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.");
+	st.Step();
+	st.Reset();
+
 }
 
 void SetupLogFile()