-#ifdef _WIN32\r
- #include <winsock2.h>\r
-#endif\r
-\r
-#ifdef XMEM\r
- #include <xmem.h>\r
-#endif\r
-\r
-bool wantshutdown=false;\r
-\r
-void SetupDB()\r
-{\r
-\r
- DateTime date;\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
-\r
- db->Open("fms.db3");\r
- db->SetBusyTimeout(10000); // set timeout to 10 seconds\r
- db->Execute("VACUUM;");\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
- }\r
- else\r
- {\r
- db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,6);");\r
- }\r
-\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");\r
-\r
- db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
- Option TEXT UNIQUE,\\r
- OptionValue TEXT NOT NULL,\\r
- OptionDescription 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
- 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
- InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
- LastInsertedBoardList DATETIME,\\r
- InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
- LastInsertedMessageList DATETIME,\\r
- LastInsertedFreesite DATETIME,\\r
- DateCreated DATETIME\\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
- 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
- MessageTrustComment TEXT,\\r
- TrustListTrustComment TEXT\\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 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
- );");\r
-\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01 12:00:00');");\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00');");\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('public','Public discussion','2007-12-01 12:00:00');");\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('test','Test board','2007-12-01 12:00:00');");\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 TABLE IF NOT EXISTS tblMessageBoard(\\r
- MessageID INTEGER,\\r
- BoardID INTEGER\\r
- );");\r
-\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
- );");\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
- // 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
- /*\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("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
- END;");\r
-\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
- 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.SetToGMTime();\r
- date.Add(0,0,0,-2);\r
- db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
- db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
-\r
- date.SetToGMTime();\r
- // insert SomeDude's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"',50);");\r
- // insert Shadow Panther's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");\r
- // insert garfield's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");\r
-\r
- // TODO remove sometime after 0.1.17\r
- FixCapitalBoardNames();\r
-\r
-}\r
-\r
-void ConvertDB0100To0101()\r
-{\r
- // added unique constraint to public and private key\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
- db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\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
- 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
- InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
- LastInsertedBoardList DATETIME,\\r
- InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
- LastInsertedMessageList DATETIME\\r
- );");\r
- db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");\r
- db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");\r
-}\r
-\r
-void ConvertDB0101To0103()\r
-{\r
- // remove default 50 from trust fields and set default to NULL\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
- db->Execute("DROP TABLE IF EXISTS tblIdentity;");\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
- 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
- );");\r
- db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");\r
- db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
-\r
- // add SaveReceivedMessages field to tblBoard\r
- db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");\r
-\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
-}\r
-\r
-void ConvertDB0103To0104()\r
-{\r
- // add MessageIndex to tblMessage\r
- DateTime date;\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex INTEGER;");\r
- db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
- db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");\r
- date.SetToGMTime();\r
- db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
-}\r
-\r
-void ConvertDB0104To0105()\r
-{\r
- // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity\r
- // add MessageTrustComment,TrustListTrustComment to tblPeerTrust\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");\r
- db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");\r
- db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");\r
- db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");\r
- db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");\r
-}\r
-\r
-void ConvertDB0105To0106()\r