#include "../include/global.h"\r
-#include "../include/datetime.h"\r
-#include "../include/logfile.h"\r
-#include "../include/option.h"\r
#include "../include/stringfunctions.h"\r
-#include "../include/db/sqlite3db.h"\r
-#include "../include/freenet/freenetmasterthread.h"\r
-#include "../include/nntp/nntplistener.h"\r
-#include "../include/http/httpthread.h"\r
-#include "../include/threadcontroller.h"\r
-\r
-#ifdef _WIN32\r
- #include <winsock2.h>\r
-#endif\r
-\r
-#ifdef XMEM\r
- #include <xmem.h>\r
-#endif\r
-\r
-volatile bool wantshutdown=false;\r
\r
std::string CreateShortIdentityName(const std::string &name, const std::string &publickey)\r
{\r
\r
return result;\r
}\r
-\r
-void SetupDB()\r
-{\r
-\r
- DateTime date;\r
- std::string tempval="";\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
-\r
- db->Open("fms.db3");\r
- db->SetBusyTimeout(10000); // set timeout to 10 seconds\r
-\r
- tempval="";\r
- Option::Instance()->Get("VacuumOnStartup",tempval);\r
- if(tempval=="true")\r
- {\r
- db->Execute("VACUUM;");\r
- }\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
- if(major==1 && minor==6)\r
- {\r
- ConvertDB0106To0107();\r
- major=1;\r
- minor=7;\r
- }\r
- if(major==1 && minor==7)\r
- {\r
- ConvertDB0107To0108();\r
- major=1;\r
- minor=8;\r
- }\r
- if(major==1 && minor==8)\r
- {\r
- ConvertDB0108To0109();\r
- major=1;\r
- minor=9;\r
- }\r
- if(major==1 && minor==9)\r
- {\r
- ConvertDB0109To0110();\r
- major=1;\r
- minor=10;\r
- }\r
- if(major==1 && minor==10)\r
- {\r
- ConvertDB0110To0111();\r
- major=1;\r
- minor=11;\r
- }\r
- if(major==1 && minor==11)\r
- {\r
- ConvertDB0111To0112();\r
- major=1;\r
- minor=12;\r
- }\r
- }\r
- else\r
- {\r
- db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,12);");\r
- }\r
-\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=12;");\r
-\r
- db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
- Option TEXT UNIQUE,\\r
- OptionValue TEXT NOT NULL,\\r
- OptionDescription TEXT,\\r
- Section TEXT,\\r
- SortOrder INTEGER,\\r
- ValidValues 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
- FreesiteEdition INTEGER,\\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
- LastInsertedBoardList DATETIME,\\r
- LastInsertedMessageList DATETIME,\\r
- LastInsertedFreesite DATETIME,\\r
- DateCreated DATETIME,\\r
- MinMessageDelay INTEGER DEFAULT 0,\\r
- MaxMessageDelay INTEGER DEFAULT 0\\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
- FreesiteEdition INTEGER,\\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
- Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\\r
- PurgeDate DATETIME\\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 tblIdentityTrust(\\r
- LocalIdentityID INTEGER,\\r
- IdentityID INTEGER,\\r
- LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
- MessageTrustComment TEXT,\\r
- LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
- TrustListTrustComment TEXT\\r
- );");\r
-\r
- db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");\r
-\r
- db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \\r
- FOR EACH ROW \\r
- BEGIN \\r
- UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \\r
- END;");\r
-\r
- db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \\r
- FOR EACH ROW \\r
- BEGIN \\r
- UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \\r
- END;");\r
-\r
- db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \\r
- FOR EACH ROW \\r
- BEGIN \\r
- UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=old.IdentityID; \\r
- END;");\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
- AddedMethod TEXT\\r
- );");\r
-\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");\r
- db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");\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 INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\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_MessageID ON tblMessageBoard (MessageID);");\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
- SendDate DATETIME\\r
- );");\r
-\r
- db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
- FileInsertID INTEGER PRIMARY KEY,\\r
- MessageUUID TEXT,\\r
- FileName TEXT,\\r
- Key TEXT,\\r
- Size INTEGER,\\r
- MimeType TEXT,\\r
- Data BLOB\\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
- // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page\r
- db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\\r
- LocalIdentityID 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
- 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
- db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \\r
- SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \\r
- FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \\r
- GROUP BY tblIdentity.IdentityID;");\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("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\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
- DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
- END;");\r
-\r
- db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\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
- DELETE FROM tblIdentityTrust 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,AddedMethod) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"',50,'Initial Identity');");\r
- // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
- //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // insert garfield's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // insert alek's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@lTjeI6V0lQsktXqaqJ6Iwk4TdsHduQI54rdUpHfhGbg,0oTYfrxxx8OmdU1~60gqpf3781qzEicM4Sz97mJsBM4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // insert Luke771's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@mdXK~ZVlfTZhF1SLBrvZ--i0vOsOpa~w9wv~~psQ-04,gXonsXKc7aexKSO8Gt8Fwre4Qgmmbt2WueO7VzxNKkk,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // insert falafel's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // insert cptn_insano's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@bloE1LJ~qzSYUkU2nt7sB9kq060D4HTQC66pk5Q8NpA,DOOASUnp0kj6tOdhZJ-h5Tk7Ka50FSrUgsH7tCG1usU,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // insert Flink's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@q2TtkNBOuuniyJ56~8NSopCs3ttwe5KlB31ugZtWmXA,6~PzIupS8YK7L6oFNpXGKJmHT2kBMDfwTg73nHdNur8,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // insert Kane's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@Ofm~yZivDJ5Z2fSzZbMiLEUUQaIc0KHRdZMBTaPLO6I,WLm4s4hNbOOurJ6ijfOq4odz7-dN7uTUvYxJRwWnlMI,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
- // inserts boardstat's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@aYWBb6zo2AM13XCNhsmmRKMANEx6PG~C15CWjdZziKA,X1pAG4EIqR1gAiyGFVZ1iiw-uTlh460~rFACJ7ZHQXk,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
-\r
- // TODO remove sometime after 0.1.17\r
- FixCapitalBoardNames();\r
-\r
- // run analyze - may speed up some queries\r
- db->Execute("ANALYZE;");\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
-{\r
- // add Publish Freesite\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");\r
- db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");\r
-}\r
-\r
-void ConvertDB0106To0107()\r
-{\r
- // add AddedMethod to tblBoard\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("ALTER TABLE tblBoard ADD COLUMN AddedMethod TEXT;");\r
- db->Execute("ALTER TABLE tblIdentity ADD COLUMN Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false';");\r
- db->Execute("UPDATE tblIdentity SET Hidden='false' WHERE Hidden IS NULL;");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");\r
-}\r
-\r
-void ConvertDB0107To0108()\r
-{\r
- // add FreesiteEdition to tblLocalIdentity and tblIdentity\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
- db->Execute("ALTER TABLE tblIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=8;");\r
-}\r
-\r
-void ConvertDB0108To0109()\r
-{\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
- FileInsertID INTEGER PRIMARY KEY,\\r
- MessageUUID TEXT,\\r
- FileName TEXT,\\r
- Key TEXT,\\r
- Size INTEGER,\\r
- Data BLOB\\r
- );");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");\r
-}\r
-\r
-void ConvertDB0109To0110()\r
-{\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- db->Execute("ALTER TABLE tblFileInserts ADD COLUMN MimeType TEXT;");\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=10;");\r
-}\r
-\r
-void ConvertDB0110To0111()\r
-{\r
- /*\r
- Drop MessageTrustComment, TrustListTrustComment FROM tblIdentity\r
-\r
- Drop InsertingMessageList, InsertingBoardList FROM tblLocalIdentity\r
- Add MinMessageDelay, MaxMessageDelay to tblLocalIdentity Default 0\r
-\r
- Add SendDate to tblMessageInserts\r
- */\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
-\r
- db->Execute("ALTER TABLE tblMessageInserts ADD COLUMN SendDate DATETIME;");\r
-\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
- PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
- FreesiteEdition INTEGER,\\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
- LastInsertedBoardList DATETIME,\\r
- LastInsertedMessageList DATETIME,\\r
- LastInsertedFreesite DATETIME,\\r
- DateCreated DATETIME,\\r
- MinMessageDelay INTEGER DEFAULT 0,\\r
- MaxMessageDelay INTEGER DEFAULT 0\\r
- );");\r
- db->Execute("INSERT INTO tblLocalIdentity SELECT LocalIdentityID,Name,PublicKey,PrivateKey,SingleUse,PublishTrustList,PublishBoardList,PublishFreesite,FreesiteEdition,InsertingIdentity,LastInsertedIdentity,InsertingPuzzle,LastInsertedPuzzle,InsertingTrustList,LastInsertedTrustList,LastInsertedBoardList,LastInsertedMessageList,LastInsertedFreesite,DateCreated,0,0 FROM tblLocalIdentityTemp;");\r
- db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
-\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
- FreesiteEdition INTEGER,\\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
- Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\\r
- );");\r
- db->Execute("INSERT INTO tblIdentity SELECT IdentityID,PublicKey,Name,SingleUse,PublishTrustList,PublishBoardList,FreesiteEdition,DateAdded,LastSeen,LocalMessageTrust,PeerMessageTrust,LocalTrustListTrust,PeerTrustListTrust,AddedMethod,Hidden FROM tblIdentityTemp;");\r
- db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
-\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=11;");\r
-}\r
-\r
-void ConvertDB0111To0112()\r
-{\r
- /*\r
- Add Section, SortOrder, ValidValues to tblOption\r
- Add PurgeDate to tblIdentity\r
- */\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
-\r
- db->Execute("ALTER TABLE tblOption ADD COLUMN Section TEXT;");\r
- db->Execute("ALTER TABLE tblOption ADD COLUMN SortOrder INTEGER;");\r
- db->Execute("ALTER TABLE tblOption ADD COLUMN ValidValues TEXT;");\r
-\r
- db->Execute("ALTER TABLE tblIdentity ADD COLUMN PurgeDate DATETIME;");\r
-\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=12;");\r
-}\r
-\r
-void SetupDefaultOptions()\r
-{\r
- // OptionValue should always be inserted as a string, even if the option really isn't a string - just to keep the field data type consistent\r
-\r
- std::ostringstream tempstr; // must set tempstr to "" between db inserts\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
- SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");\r
- SQLite3DB::Statement upd=db->Prepare("UPDATE tblOption SET Section=?, SortOrder=?, ValidValues=? WHERE Option=?;");\r
- int order=0;\r
-\r
- // LogLevel\r
- tempstr.str("");\r
- tempstr << LogFile::LOGLEVEL_DEBUG;\r
- st.Bind(0,"LogLevel");\r
- st.Bind(1,tempstr.str());\r
- st.Bind(2,"The maximum logging level that will be written to file. 0=Fatal Errors, 1=Errors, 2=Warnings, 3=Informational Messages, 4=Debug Messages. Higher levels will include all messages from the previous levels.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Program");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"0|0 - Fatal Errors|1|1 - Errors|2|2 - Warnings|3|3 - Informational Messages|4|4 - Debug Messages");\r
- upd.Bind(3,"LogLevel");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"VacuumOnStartup");\r
- st.Bind(1,"false");\r
- st.Bind(2,"VACUUM the database every time FMS starts. This will defragment the free space in the database and create a smaller database file. Vacuuming the database can be CPU and disk intensive.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Program");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"VacuumOnStartup");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MessageBase");\r
- st.Bind(1,"fms");\r
- st.Bind(2,"A unique string shared by all clients who want to communicate with each other. This should not be changed unless you want to create your own separate communications network.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Program");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MessageBase");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- // StartNNTP\r
- st.Bind(0,"StartNNTP");\r
- st.Bind(1,"true");\r
- st.Bind(2,"Start NNTP server.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"NNTP Server");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"StartNNTP");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- // NNTPListenPort\r
- st.Bind(0,"NNTPListenPort");\r
- st.Bind(1,"1119");\r
- st.Bind(2,"The port that the NNTP service will listen for incoming connections.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"NNTP Server");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"NNTPListenPort");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- // NNTPBindAddresses\r
- st.Bind(0,"NNTPBindAddresses");\r
- st.Bind(1,"localhost");\r
- st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"NNTP Server");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"NNTPBindAddresses");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"NNTPAllowPost");\r
- st.Bind(1,"true");\r
- st.Bind(2,"Allow posting messages from NNTP. Setting to false will make the newsgroups read only.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"NNTP Server");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"NNTPAllowPost");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"StartHTTP");\r
- st.Bind(1,"true");\r
- st.Bind(2,"Start HTTP server. WARNING: If you turn this off, you won't be able to access the administration pages.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"HTTP Server");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"StartHTTP");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"HTTPListenPort");\r
- st.Bind(1,"8080");\r
- st.Bind(2,"Port HTTP server will listen on.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"HTTP Server");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"HTTPListenPort");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"HTTPAccessControl");\r
- st.Bind(1,"-0.0.0.0/0,+127.0.0.1");\r
- st.Bind(2,"Comma separated list of addresses and/or subnet masks that are allowed access to the administration pages. Default is localhost only. + allows a host, - denies a host.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"HTTP Server");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"HTTPAccessControl");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- // StartFreenetUpdater\r
- st.Bind(0,"StartFreenetUpdater");\r
- st.Bind(1,"true");\r
- st.Bind(2,"Set to true to start the Freenet Updater thread and connect to Freenet. Set to false to prevent communication with Freenet.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Freenet Connection");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"StartFreenetUpdater");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- // FCPHost\r
- st.Bind(0,"FCPHost");\r
- st.Bind(1,"127.0.0.1");\r
- st.Bind(2,"Host name or address of Freenet node.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Freenet Connection");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"FCPHost");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- // FCPPort\r
- st.Bind(0,"FCPPort");\r
- st.Bind(1,"9481");\r
- st.Bind(2,"The port that Freenet is listening for FCP connections on.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Freenet Connection");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"FCPPort");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"FProxyPort");\r
- st.Bind(1,"8888");\r
- st.Bind(2,"The port that Freenet is listening for http connections on.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Freenet Connection");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"FProxyPort");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxIdentityRequests");\r
- st.Bind(1,"5");\r
- st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Requests");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxIdentityRequests");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxIdentityIntroductionRequests");\r
- st.Bind(1,"5");\r
- st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files. Each identity may have multiple requests pending.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Requests");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxIdentityIntroductionRequests");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxIntroductionPuzzleRequests");\r
- st.Bind(1,"5");\r
- st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Requests");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxIntroductionPuzzleRequests");\r
- upd.Step();\r
- upd.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
- upd.Bind(0,"Requests");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxTrustListRequests");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxMessageListRequests");\r
- st.Bind(1,"5");\r
- st.Bind(2,"Maximum number of concurrent requests for new Message Lists");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Requests");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxMessageListRequests");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxMessageRequests");\r
- st.Bind(1,"20");\r
- st.Bind(2,"Maximum number of concurrent requests for new Messages");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Requests");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxMessageRequests");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxBoardListRequests");\r
- st.Bind(1,"5");\r
- st.Bind(2,"The maximum number of concurrent requests for new Board Lists. Set to 0 to disable.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Requests");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxBoardListRequests");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MinLocalMessageTrust");\r
- st.Bind(1,"50");\r
- st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Trust");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MinLocalMessageTrust");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MinPeerMessageTrust");\r
- st.Bind(1,"30");\r
- st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Trust");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MinPeerMessageTrust");\r
- upd.Step();\r
- upd.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
- upd.Bind(0,"Trust");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MinLocalTrustListTrust");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MinPeerTrustListTrust");\r
- st.Bind(1,"30");\r
- st.Bind(2,"Specifies a peer 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
- upd.Bind(0,"Trust");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MinPeerTrustListTrust");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"LocalTrustOverridesPeerTrust");\r
- st.Bind(1,"false");\r
- st.Bind(2,"Set to true if you want your local trust levels to override the peer levels when determining which identities you will poll.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Trust");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"LocalTrustOverridesPeerTrust");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MessageDownloadMaxDaysBackward");\r
- st.Bind(1,"5");\r
- st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MessageDownloadMaxDaysBackward");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MessageListDaysBackward");\r
- st.Bind(1,"5");\r
- st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MessageListDaysBackward");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxPeerMessagesPerDay");\r
- st.Bind(1,"200");\r
- st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxPeerMessagesPerDay");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"MaxBoardsPerMessage");\r
- st.Bind(1,"8");\r
- st.Bind(2,"The maximum number of boards a received message may be sent to. Boards over this limit will be ignored.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"MaxBoardsPerMessage");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"SaveMessagesFromNewBoards");\r
- st.Bind(1,"true");\r
- st.Bind(2,"Set to true to automatically save messages posted to new boards. Set to false to ignore messages to new boards.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"SaveMessagesFromNewBoards");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"ChangeMessageTrustOnReply");\r
- st.Bind(1,"0");\r
- st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"ChangeMessageTrustOnReply");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"AddNewPostFromIdentities");\r
- st.Bind(1,"false");\r
- st.Bind(2,"Set to true to automatically create new identities when you send a message using a new name. If you set this to false, posting messages will fail until you manually create the identity.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2,"true|true|false|false");\r
- upd.Bind(3,"AddNewPostFromIdentities");\r
- upd.Step();\r
- upd.Reset();\r
-\r
- st.Bind(0,"DeleteMessagesOlderThan");\r
- st.Bind(1,"180");\r
- st.Bind(2,"Automatically delete messages older than this many days.");\r
- st.Step();\r
- st.Reset();\r
- upd.Bind(0,"Messages");\r
- upd.Bind(1,order++);\r
- upd.Bind(2);\r
- upd.Bind(3,"DeleteMessagesOlderThan");\r
- upd.Step();\r
- upd.Reset();\r
-\r
-}\r
-\r
-void SetupLogFile()\r
-{\r
- DateTime date;\r
- std::string configval;\r
- int loglevel;\r
-\r
- date.SetToGMTime();\r
-\r
- LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");\r
- LogFile::Instance()->OpenFile();\r
- LogFile::Instance()->SetWriteNewLine(true);\r
- LogFile::Instance()->SetWriteDate(true);\r
- LogFile::Instance()->SetWriteLogLevel(true);\r
-\r
- if(Option::Instance()->Get("LogLevel",configval)==false)\r
- {\r
- configval="4";\r
- Option::Instance()->Set("LogLevel",configval);\r
- }\r
- if(StringFunctions::Convert(configval,loglevel)==false)\r
- {\r
- loglevel=LogFile::LOGLEVEL_DEBUG;\r
- Option::Instance()->Set("LogLevel",loglevel);\r
- }\r
- LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);\r
-}\r
-\r
-void SetupNetwork()\r
-{\r
-#ifdef _WIN32\r
- WSAData wsadata;\r
- WSAStartup(MAKEWORD(2,2),&wsadata);\r
-#endif\r
-}\r
-\r
-void Shutdown()\r
-{\r
-\r
- LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_DEBUG,"FMS starting shutdown");\r
-\r
- ThreadController::Instance()->ShutdownThreads();\r
-\r
- ShutdownNetwork();\r
-\r
- LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");\r
- LogFile::Instance()->WriteNewLine();\r
-}\r
-\r
-void ShutdownNetwork()\r
-{\r
-#ifdef _WIN32\r
- WSACleanup();\r
-#endif\r
-}\r
-\r
-void SigHandler(int signum)\r
-{\r
- Shutdown();\r
- exit(0);\r
-}\r
-\r
-void FixCapitalBoardNames()\r
-{\r
- SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
-\r
- SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");\r
- SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");\r
- SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");\r
- SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");\r
- SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");\r
- SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");\r
-\r
- st.Step();\r
- while(st.RowReturned())\r
- {\r
- int boardid=0;\r
- int newboardid=0;\r
- std::string name="";\r
- std::string lowername="";\r
-\r
- st.ResultInt(0,boardid);\r
- st.ResultText(1,name);\r
-\r
- lowername=name;\r
- StringFunctions::LowerCase(lowername,lowername);\r
- \r
- if(name!=lowername)\r
- {\r
- st2.Bind(0,lowername);\r
- st2.Step();\r
-\r
- if(st2.RowReturned())\r
- {\r
- st2.ResultInt(0,newboardid);\r
-\r
- upd2.Bind(0,newboardid);\r
- upd2.Bind(1,boardid);\r
- upd2.Step();\r
- upd2.Reset();\r
-\r
- upd3.Bind(0,newboardid);\r
- upd3.Bind(1,boardid);\r
- upd3.Step();\r
- upd3.Reset();\r
-\r
- del.Bind(0,boardid);\r
- del.Step();\r
- del.Reset();\r
- }\r
- else\r
- {\r
- upd.Bind(0,lowername);\r
- upd.Bind(1,boardid);\r
- upd.Step();\r
- upd.Reset();\r
- }\r
-\r
- st2.Reset();\r
- }\r
- \r
- st.Step();\r
- }\r
-\r
-}\r
projects / fms.git / blobdiff