db->Open("fms.db3");\r
db->SetBusyTimeout(10000); // set timeout to 10 seconds\r
db->Execute("VACUUM;");\r
- \r
- // TODO remove this - temp fix for problem in 0.1.8\r
- db->Execute("DELETE FROM tblMessageBoard WHERE MessageID NOT IN (SELECT MessageID FROM tblMessage);");\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
Major INTEGER,\\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
}\r
else\r
{\r
- db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,1);");\r
+ db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,5);");\r
}\r
\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=2;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
Option TEXT UNIQUE,\\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
+ LastInsertedMessageList DATETIME,\\r
+ DateCreated DATETIME\\r
);");\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\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 50,\\r
- PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT 50,\\r
- LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT 50,\\r
- PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT 50\\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
);");\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
+ 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 TABLE IF NOT EXISTS tblBoard(\\r
- BoardID INTEGER PRIMARY KEY,\\r
- BoardName TEXT UNIQUE,\\r
- BoardDescription TEXT,\\r
- DateAdded DATETIME\\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
Subject TEXT,\\r
MessageUUID TEXT UNIQUE,\\r
ReplyBoardID INTEGER,\\r
- Body TEXT\\r
+ Body TEXT,\\r
+ MessageIndex INTEGER\\r
);");\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\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
GROUP BY tblBoard.BoardID;");\r
\r
// calculates peer trust\r
+ // do the (MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1)/100.0 - so it 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*(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
+ 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
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
\r
date.SetToGMTime();\r
// insert SomeDude's public key\r
- db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"',51);");\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
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 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
st.Step();\r
st.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 as host.");\r
+ st.Step();\r
+ st.Reset();\r
+\r
// StartFreenetUpdater\r
st.Bind(0,"StartFreenetUpdater");\r
st.Bind(1,"true");\r
st.Step();\r
st.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
+\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
+\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
+\r
}\r
\r
void SetupLogFile()\r
exit(0);\r
}\r
\r
-/*\r
-void ShutdownThreads(std::vector<PThread::Thread *> &threads)\r
+void FixCapitalBoardNames()\r
{\r
- std::vector<PThread::Thread *>::iterator i;\r
- for(i=threads.begin(); i!=threads.end(); i++)\r
- {\r
- (*i)->Cancel();\r
- }\r
-\r
- for(i=threads.begin(); i!=threads.end(); i++)\r
- {\r
- LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_DEBUG,"ShutdownThreads waiting for thread to exit.");\r
- //(*i)->wait();\r
- (*i)->Join();\r
- delete (*i);\r
- }\r
-\r
- threads.clear();\r
-\r
-}\r
-\r
-void StartThreads(std::vector<PThread::Thread *> &threads)\r
-{\r
- std::string startfreenet;\r
- std::string startnntp;\r
- std::string starthttp;\r
-\r
- if(Option::Instance()->Get("StartFreenetUpdater",startfreenet)==false)\r
- {\r
- startfreenet="true";\r
- Option::Instance()->Set("StartFreenetUpdater","true");\r
- }\r
-\r
- if(Option::Instance()->Get("StartNNTP",startnntp)==false)\r
- {\r
- startnntp="true";\r
- Option::Instance()->Set("StartNNTP","true");\r
- }\r
-\r
- if(Option::Instance()->Get("StartHTTP",starthttp)==false)\r
- {\r
- starthttp="true";\r
- Option::Instance()->Set("StartHTTP","true");\r
- }\r
-\r
- if(startfreenet=="true")\r
- {\r
- PThread::Thread *t=new PThread::Thread(new FreenetMasterThread());\r
- threads.push_back(t);\r
- }\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
\r
- if(startnntp=="true")\r
- {\r
- PThread::Thread *t=new PThread::Thread(new NNTPListener());\r
- threads.push_back(t);\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("DELTE 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
- if(starthttp=="true")\r
+ st.Step();\r
+ while(st.RowReturned())\r
{\r
- PThread::Thread *t=new PThread::Thread(new HTTPThread());\r
- threads.push_back(t);\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
-*/\r
projects / fms.git / blobdiff