1 #include "../include/global.h"
\r
2 #include "../include/datetime.h"
\r
3 #include "../include/logfile.h"
\r
4 #include "../include/option.h"
\r
5 #include "../include/stringfunctions.h"
\r
6 #include "../include/db/sqlite3db.h"
\r
7 #include "../include/freenet/freenetmasterthread.h"
\r
8 #include "../include/nntp/nntplistener.h"
\r
9 #include "../include/http/httpthread.h"
\r
10 #include "../include/threadcontroller.h"
\r
13 #include <winsock2.h>
\r
20 bool wantshutdown=false;
\r
26 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
28 db->Open("fms.db3");
\r
29 db->SetBusyTimeout(10000); // set timeout to 10 seconds
\r
30 db->Execute("VACUUM;");
\r
32 db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\
\r
37 SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");
\r
39 if(st.RowReturned())
\r
43 st.ResultInt(0,major);
\r
44 st.ResultInt(1,minor);
\r
46 if(major==1 && minor==0)
\r
48 ConvertDB0100To0101();
\r
52 if(major==1 && (minor==1 || minor==2))
\r
54 ConvertDB0101To0103();
\r
58 if(major==1 && minor==3)
\r
60 ConvertDB0103To0104();
\r
64 if(major==1 && minor==4)
\r
66 ConvertDB0104To0105();
\r
73 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,5);");
\r
76 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");
\r
78 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
79 Option TEXT UNIQUE,\
\r
80 OptionValue TEXT NOT NULL,\
\r
81 OptionDescription TEXT\
\r
84 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
85 LocalIdentityID INTEGER PRIMARY KEY,\
\r
87 PublicKey TEXT UNIQUE,\
\r
88 PrivateKey TEXT UNIQUE,\
\r
89 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
90 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
91 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
92 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
93 LastInsertedIdentity DATETIME,\
\r
94 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
95 LastInsertedPuzzle DATETIME,\
\r
96 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
97 LastInsertedTrustList DATETIME,\
\r
98 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
99 LastInsertedBoardList DATETIME,\
\r
100 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
101 LastInsertedMessageList DATETIME,\
\r
102 DateCreated DATETIME\
\r
105 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
106 LocalIdentityID INTEGER,\
\r
108 InsertIndex INTEGER\
\r
111 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
112 LocalIdentityID INTEGER,\
\r
114 InsertIndex INTEGER\
\r
117 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
118 IdentityID INTEGER,\
\r
120 RequestIndex INTEGER,\
\r
121 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
124 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
126 LocalIdentityID INTEGER,\
\r
128 InsertIndex INTEGER,\
\r
132 PuzzleSolution TEXT,\
\r
133 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
136 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
137 IdentityID INTEGER PRIMARY KEY,\
\r
138 PublicKey TEXT UNIQUE,\
\r
140 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
141 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
142 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
143 DateAdded DATETIME,\
\r
144 LastSeen DATETIME,\
\r
145 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
146 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
147 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
148 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
150 MessageTrustComment TEXT,\
\r
151 TrustListTrustComment TEXT\
\r
154 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
155 IdentityID INTEGER,\
\r
157 RequestIndex INTEGER,\
\r
158 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
161 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
162 IdentityID INTEGER,\
\r
164 RequestIndex INTEGER,\
\r
165 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
172 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
173 LocalIdentityID INTEGER,\
\r
177 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
180 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
181 IdentityID INTEGER,\
\r
182 TargetIdentityID INTEGER,\
\r
183 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
184 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
185 MessageTrustComment TEXT,\
\r
186 TrustListTrustComment TEXT\
\r
189 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
190 BoardID INTEGER PRIMARY KEY,\
\r
191 BoardName TEXT UNIQUE,\
\r
192 BoardDescription TEXT,\
\r
193 DateAdded DATETIME,\
\r
194 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true'\
\r
197 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01 12:00:00');");
\r
198 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00');");
\r
199 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('public','Public discussion','2007-12-01 12:00:00');");
\r
200 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('test','Test board','2007-12-01 12:00:00');");
\r
202 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
203 MessageID INTEGER PRIMARY KEY,\
\r
204 IdentityID INTEGER,\
\r
209 MessageUUID TEXT UNIQUE,\
\r
210 ReplyBoardID INTEGER,\
\r
212 MessageIndex INTEGER\
\r
215 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
216 MessageID INTEGER,\
\r
217 ReplyToMessageUUID TEXT,\
\r
218 ReplyOrder INTEGER\
\r
221 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
222 MessageID INTEGER,\
\r
226 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
227 IdentityID INTEGER,\
\r
229 RequestIndex INTEGER,\
\r
230 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
233 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
234 IdentityID INTEGER,\
\r
236 RequestIndex INTEGER,\
\r
237 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
238 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
241 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
243 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
244 LocalIdentityID INTEGER,\
\r
246 InsertIndex INTEGER,\
\r
247 MessageUUID TEXT UNIQUE,\
\r
249 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
252 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
253 LocalIdentityID INTEGER,\
\r
255 InsertIndex INTEGER,\
\r
256 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
259 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
260 BoardID INTEGER UNIQUE,\
\r
261 ModifyLocalMessageTrust INTEGER,\
\r
262 ModifyLocalTrustListTrust INTEGER\
\r
265 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
266 LocalIdentityID INTEGER,\
\r
268 InsertIndex INTEGER,\
\r
269 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
272 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
273 IdentityID INTEGER,\
\r
275 RequestIndex INTEGER,\
\r
276 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
279 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
280 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
281 LocalIdentityID INTEGER,\
\r
285 // low / high / message count for each board
\r
286 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
287 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
288 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
289 WHERE MessageID>=0 OR MessageID IS NULL \
\r
290 GROUP BY tblBoard.BoardID;");
\r
292 // calculates peer trust
\r
293 // 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
294 // need the +1 so that when the values are 0 the result is not 0
\r
295 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
296 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
297 SELECT TargetIdentityID, \
\r
298 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
299 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
300 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
301 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
302 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
303 GROUP BY TargetIdentityID;");
\r
306 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
307 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
309 // drop existing triggers
\r
310 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
311 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
312 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
313 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
315 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
316 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
319 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
322 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
323 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
326 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
329 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
330 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
333 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
334 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
337 // 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
338 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
341 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
345 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
348 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
349 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
352 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
355 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
356 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
357 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
358 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
359 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
360 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
363 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
366 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
367 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
368 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
369 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
370 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
371 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
374 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
377 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
380 // delete introduction puzzles that were half-way inserted
\r
381 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
383 // delete stale introduction puzzles (2 or more days old)
\r
384 date.SetToGMTime();
\r
385 date.Add(0,0,0,-2);
\r
386 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
387 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
389 date.SetToGMTime();
\r
390 // insert SomeDude's public key
\r
391 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
392 // insert Shadow Panther's public key
\r
393 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");
\r
394 // insert garfield's public key
\r
395 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");
\r
397 // TODO remove sometime after 0.1.17
\r
398 FixCapitalBoardNames();
\r
402 void ConvertDB0100To0101()
\r
404 // added unique constraint to public and private key
\r
405 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
406 db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");
\r
407 db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");
\r
408 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
409 LocalIdentityID INTEGER PRIMARY KEY,\
\r
411 PublicKey TEXT UNIQUE,\
\r
412 PrivateKey TEXT UNIQUE,\
\r
413 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
414 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
415 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
416 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
417 LastInsertedIdentity DATETIME,\
\r
418 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
419 LastInsertedPuzzle DATETIME,\
\r
420 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
421 LastInsertedTrustList DATETIME,\
\r
422 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
423 LastInsertedBoardList DATETIME,\
\r
424 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
425 LastInsertedMessageList DATETIME\
\r
427 db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");
\r
428 db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");
\r
429 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");
\r
432 void ConvertDB0101To0103()
\r
434 // remove default 50 from trust fields and set default to NULL
\r
435 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
436 db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");
\r
437 db->Execute("DROP TABLE IF EXISTS tblIdentity;");
\r
438 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
439 IdentityID INTEGER PRIMARY KEY,\
\r
440 PublicKey TEXT UNIQUE,\
\r
442 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
443 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
444 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
445 DateAdded DATETIME,\
\r
446 LastSeen DATETIME,\
\r
447 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
448 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
449 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
450 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\
\r
452 db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");
\r
453 db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");
\r
455 // add SaveReceivedMessages field to tblBoard
\r
456 db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");
\r
458 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");
\r
461 void ConvertDB0103To0104()
\r
463 // add MessageIndex to tblMessage
\r
465 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
466 db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex INTEGER;");
\r
467 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
468 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");
\r
469 date.SetToGMTime();
\r
470 db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");
\r
471 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");
\r
474 void ConvertDB0104To0105()
\r
476 // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity
\r
477 // add MessageTrustComment,TrustListTrustComment to tblPeerTrust
\r
478 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
479 db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");
\r
480 db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");
\r
481 db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");
\r
482 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");
\r
483 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");
\r
484 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");
\r
487 void SetupDefaultOptions()
\r
489 // 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
491 std::ostringstream tempstr; // must set tempstr to "" between db inserts
\r
492 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
493 SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");
\r
497 tempstr << LogFile::LOGLEVEL_DEBUG;
\r
498 st.Bind(0,"LogLevel");
\r
499 st.Bind(1,tempstr.str());
\r
500 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
505 st.Bind(0,"NNTPListenPort");
\r
507 st.Bind(2,"The port that the NNTP service will listen for incoming connections.");
\r
511 // NNTPBindAddresses
\r
512 st.Bind(0,"NNTPBindAddresses");
\r
513 st.Bind(1,"localhost");
\r
514 st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");
\r
518 st.Bind(0,"NNTPAllowPost");
\r
520 st.Bind(2,"Allow posting messages from NNTP. Setting to false will make the newsgroups read only.");
\r
525 st.Bind(0,"StartNNTP");
\r
527 st.Bind(2,"Start NNTP server.");
\r
531 st.Bind(0,"StartHTTP");
\r
533 st.Bind(2,"Start HTTP server.");
\r
537 st.Bind(0,"HTTPListenPort");
\r
539 st.Bind(2,"Port HTTP server will listen on.");
\r
543 st.Bind(0,"HTTPAccessControl");
\r
544 st.Bind(1,"-0.0.0.0/0,+127.0.0.1");
\r
545 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
549 // StartFreenetUpdater
\r
550 st.Bind(0,"StartFreenetUpdater");
\r
552 st.Bind(2,"Start Freenet Updater thread.");
\r
557 st.Bind(0,"FCPHost");
\r
558 st.Bind(1,"127.0.0.1");
\r
559 st.Bind(2,"Host name or address of Freenet node.");
\r
564 st.Bind(0,"FCPPort");
\r
566 st.Bind(2,"The port that Freenet is listening for FCP connections on.");
\r
570 st.Bind(0,"MessageBase");
\r
572 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
576 st.Bind(0,"MaxIdentityRequests");
\r
578 st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");
\r
582 st.Bind(0,"MaxIdentityIntroductionRequests");
\r
584 st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files. Each identity may have multiple requests pending.");
\r
588 st.Bind(0,"MaxIntroductionPuzzleRequests");
\r
590 st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");
\r
594 st.Bind(0,"MaxTrustListRequests");
\r
596 st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");
\r
600 st.Bind(0,"MaxMessageListRequests");
\r
602 st.Bind(2,"Maximum number of concurrent requests for new Message Lists");
\r
606 st.Bind(0,"MaxMessageRequests");
\r
608 st.Bind(2,"Maximum number of concurrent requests for new Messages");
\r
612 st.Bind(0,"MinLocalMessageTrust");
\r
614 st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");
\r
618 st.Bind(0,"MinPeerMessageTrust");
\r
620 st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");
\r
624 st.Bind(0,"MinLocalTrustListTrust");
\r
626 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
630 st.Bind(0,"MinPeerTrustListTrust");
\r
632 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
636 st.Bind(0,"MessageDownloadMaxDaysBackward");
\r
638 st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");
\r
642 st.Bind(0,"MessageListDaysBackward");
\r
644 st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");
\r
648 st.Bind(0,"MaxPeerMessagesPerDay");
\r
650 st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");
\r
654 st.Bind(0,"MaxBoardListRequests");
\r
656 st.Bind(2,"The maximum number of concurrent requests for new Board Lists. Set to 0 to disable.");
\r
660 st.Bind(0,"MaxBoardsPerMessage");
\r
662 st.Bind(2,"The maximum number of boards a received message may be sent to. Boards over this limit will be ignored.");
\r
666 st.Bind(0,"ChangeMessageTrustOnReply");
\r
668 st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");
\r
672 st.Bind(0,"AddNewPostFromIdentities");
\r
673 st.Bind(1,"false");
\r
674 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
678 st.Bind(0,"DeleteMessagesOlderThan");
\r
680 st.Bind(2,"Automatically delete messages older than this many days.");
\r
686 void SetupLogFile()
\r
689 std::string configval;
\r
692 date.SetToGMTime();
\r
694 LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");
\r
695 LogFile::Instance()->OpenFile();
\r
696 LogFile::Instance()->SetWriteNewLine(true);
\r
697 LogFile::Instance()->SetWriteDate(true);
\r
698 LogFile::Instance()->SetWriteLogLevel(true);
\r
700 if(Option::Instance()->Get("LogLevel",configval)==false)
\r
703 Option::Instance()->Set("LogLevel",configval);
\r
705 if(StringFunctions::Convert(configval,loglevel)==false)
\r
707 loglevel=LogFile::LOGLEVEL_DEBUG;
\r
708 Option::Instance()->Set("LogLevel",loglevel);
\r
710 LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);
\r
713 void SetupNetwork()
\r
717 WSAStartup(MAKEWORD(2,2),&wsadata);
\r
723 ThreadController::Instance()->ShutdownThreads();
\r
727 LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");
\r
728 LogFile::Instance()->WriteNewLine();
\r
731 void ShutdownNetwork()
\r
738 void SigHandler(int signum)
\r
744 void FixCapitalBoardNames()
\r
746 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
748 SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");
\r
749 SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");
\r
750 SQLite3DB::Statement del=db->Prepare("DELTE FROM tblBoard WHERE BoardID=?;");
\r
751 SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");
\r
752 SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");
\r
753 SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");
\r
756 while(st.RowReturned())
\r
760 std::string name="";
\r
761 std::string lowername="";
\r
763 st.ResultInt(0,boardid);
\r
764 st.ResultText(1,name);
\r
767 StringFunctions::LowerCase(lowername,lowername);
\r
769 if(name!=lowername)
\r
771 st2.Bind(0,lowername);
\r
774 if(st2.RowReturned())
\r
776 st2.ResultInt(0,newboardid);
\r
778 upd2.Bind(0,newboardid);
\r
779 upd2.Bind(1,boardid);
\r
783 upd3.Bind(0,newboardid);
\r
784 upd3.Bind(1,boardid);
\r
788 del.Bind(0,boardid);
\r
794 upd.Bind(0,lowername);
\r
795 upd.Bind(1,boardid);
\r