+ 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
+ // 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