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
70 if(major==1 && minor==5)
\r
72 ConvertDB0105To0106();
\r
76 if(major==1 && minor==6)
\r
78 ConvertDB0106To0107();
\r
85 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,7);");
\r
88 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");
\r
90 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
91 Option TEXT UNIQUE,\
\r
92 OptionValue TEXT NOT NULL,\
\r
93 OptionDescription TEXT\
\r
96 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
97 LocalIdentityID INTEGER PRIMARY KEY,\
\r
99 PublicKey TEXT UNIQUE,\
\r
100 PrivateKey TEXT UNIQUE,\
\r
101 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
102 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
103 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
104 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
105 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
106 LastInsertedIdentity DATETIME,\
\r
107 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
108 LastInsertedPuzzle DATETIME,\
\r
109 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
110 LastInsertedTrustList DATETIME,\
\r
111 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
112 LastInsertedBoardList DATETIME,\
\r
113 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
114 LastInsertedMessageList DATETIME,\
\r
115 LastInsertedFreesite DATETIME,\
\r
116 DateCreated DATETIME\
\r
119 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
120 LocalIdentityID INTEGER,\
\r
122 InsertIndex INTEGER\
\r
125 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
126 LocalIdentityID INTEGER,\
\r
128 InsertIndex INTEGER\
\r
131 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
132 IdentityID INTEGER,\
\r
134 RequestIndex INTEGER,\
\r
135 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
138 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
140 LocalIdentityID INTEGER,\
\r
142 InsertIndex INTEGER,\
\r
146 PuzzleSolution TEXT,\
\r
147 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
150 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
151 IdentityID INTEGER PRIMARY KEY,\
\r
152 PublicKey TEXT UNIQUE,\
\r
154 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
155 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
156 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
157 DateAdded DATETIME,\
\r
158 LastSeen DATETIME,\
\r
159 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
160 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
161 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
162 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
164 MessageTrustComment TEXT,\
\r
165 TrustListTrustComment TEXT,\
\r
166 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\
\r
169 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
170 IdentityID INTEGER,\
\r
172 RequestIndex INTEGER,\
\r
173 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
176 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
177 IdentityID INTEGER,\
\r
179 RequestIndex INTEGER,\
\r
180 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
187 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
188 LocalIdentityID INTEGER,\
\r
192 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
195 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
196 IdentityID INTEGER,\
\r
197 TargetIdentityID INTEGER,\
\r
198 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
199 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
200 MessageTrustComment TEXT,\
\r
201 TrustListTrustComment TEXT\
\r
204 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
205 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
207 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
208 BoardID INTEGER PRIMARY KEY,\
\r
209 BoardName TEXT UNIQUE,\
\r
210 BoardDescription TEXT,\
\r
211 DateAdded DATETIME,\
\r
212 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
216 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");
\r
217 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initialt Board');");
\r
218 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");
\r
219 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");
\r
221 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
222 MessageID INTEGER PRIMARY KEY,\
\r
223 IdentityID INTEGER,\
\r
228 MessageUUID TEXT UNIQUE,\
\r
229 ReplyBoardID INTEGER,\
\r
231 MessageIndex INTEGER\
\r
234 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
236 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
237 MessageID INTEGER,\
\r
238 ReplyToMessageUUID TEXT,\
\r
239 ReplyOrder INTEGER\
\r
242 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
244 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
245 MessageID INTEGER,\
\r
249 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
250 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
252 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
253 IdentityID INTEGER,\
\r
255 RequestIndex INTEGER,\
\r
256 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
259 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
260 IdentityID INTEGER,\
\r
262 RequestIndex INTEGER,\
\r
263 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
264 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
267 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
269 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
270 LocalIdentityID INTEGER,\
\r
272 InsertIndex INTEGER,\
\r
273 MessageUUID TEXT UNIQUE,\
\r
275 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
278 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
279 LocalIdentityID INTEGER,\
\r
281 InsertIndex INTEGER,\
\r
282 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
285 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
286 BoardID INTEGER UNIQUE,\
\r
287 ModifyLocalMessageTrust INTEGER,\
\r
288 ModifyLocalTrustListTrust INTEGER\
\r
291 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
292 LocalIdentityID INTEGER,\
\r
294 InsertIndex INTEGER,\
\r
295 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
298 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
299 IdentityID INTEGER,\
\r
301 RequestIndex INTEGER,\
\r
302 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
305 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
306 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
307 LocalIdentityID INTEGER,\
\r
311 // low / high / message count for each board
\r
312 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
313 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
314 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
315 WHERE MessageID>=0 OR MessageID IS NULL \
\r
316 GROUP BY tblBoard.BoardID;");
\r
318 // calculates peer trust
\r
319 // 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
320 // need the +1 so that when the values are 0 the result is not 0
\r
321 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
322 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
323 SELECT TargetIdentityID, \
\r
324 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
325 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
326 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
327 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
328 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
329 GROUP BY TargetIdentityID;");
\r
332 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
333 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
335 // drop existing triggers
\r
336 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
337 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
338 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
339 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
341 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
342 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
345 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
348 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
349 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
352 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
355 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
356 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
359 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
360 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
363 // 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
364 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
367 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
371 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
374 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
375 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
378 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
381 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
382 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
383 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
384 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
385 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
386 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
389 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
392 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
393 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
394 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
395 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
396 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
397 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
400 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
403 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
406 // delete introduction puzzles that were half-way inserted
\r
407 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
409 // delete stale introduction puzzles (2 or more days old)
\r
410 date.SetToGMTime();
\r
411 date.Add(0,0,0,-2);
\r
412 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
413 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
415 date.SetToGMTime();
\r
416 // insert SomeDude's public key
\r
417 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
418 // insert Shadow Panther's public key
\r
419 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
420 // insert garfield's public key
\r
421 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
423 // TODO remove sometime after 0.1.17
\r
424 FixCapitalBoardNames();
\r
428 void ConvertDB0100To0101()
\r
430 // added unique constraint to public and private key
\r
431 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
432 db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");
\r
433 db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");
\r
434 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
435 LocalIdentityID INTEGER PRIMARY KEY,\
\r
437 PublicKey TEXT UNIQUE,\
\r
438 PrivateKey TEXT UNIQUE,\
\r
439 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
440 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
441 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
442 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
443 LastInsertedIdentity DATETIME,\
\r
444 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
445 LastInsertedPuzzle DATETIME,\
\r
446 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
447 LastInsertedTrustList DATETIME,\
\r
448 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
449 LastInsertedBoardList DATETIME,\
\r
450 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
451 LastInsertedMessageList DATETIME\
\r
453 db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");
\r
454 db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");
\r
455 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");
\r
458 void ConvertDB0101To0103()
\r
460 // remove default 50 from trust fields and set default to NULL
\r
461 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
462 db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");
\r
463 db->Execute("DROP TABLE IF EXISTS tblIdentity;");
\r
464 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
465 IdentityID INTEGER PRIMARY KEY,\
\r
466 PublicKey TEXT UNIQUE,\
\r
468 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
469 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
470 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
471 DateAdded DATETIME,\
\r
472 LastSeen DATETIME,\
\r
473 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
474 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
475 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
476 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\
\r
478 db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");
\r
479 db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");
\r
481 // add SaveReceivedMessages field to tblBoard
\r
482 db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");
\r
484 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");
\r
487 void ConvertDB0103To0104()
\r
489 // add MessageIndex to tblMessage
\r
491 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
492 db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex INTEGER;");
\r
493 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
494 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");
\r
495 date.SetToGMTime();
\r
496 db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");
\r
497 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");
\r
500 void ConvertDB0104To0105()
\r
502 // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity
\r
503 // add MessageTrustComment,TrustListTrustComment to tblPeerTrust
\r
504 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
505 db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");
\r
506 db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");
\r
507 db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");
\r
508 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");
\r
509 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");
\r
510 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");
\r
513 void ConvertDB0105To0106()
\r
515 // add Publish Freesite
\r
516 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
517 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");
\r
518 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");
\r
519 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");
\r
522 void ConvertDB0106To0107()
\r
524 // add AddedMethod to tblBoard
\r
525 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
526 db->Execute("ALTER TABLE tblBoard ADD COLUMN AddedMethod TEXT;");
\r
527 db->Execute("ALTER TABLE tblIdentity ADD COLUMN Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false';");
\r
528 db->Execute("UPDATE tblIdentity SET Hidden='false' WHERE Hidden IS NULL;");
\r
529 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");
\r
532 void SetupDefaultOptions()
\r
534 // 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
536 std::ostringstream tempstr; // must set tempstr to "" between db inserts
\r
537 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
538 SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");
\r
542 tempstr << LogFile::LOGLEVEL_DEBUG;
\r
543 st.Bind(0,"LogLevel");
\r
544 st.Bind(1,tempstr.str());
\r
545 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
550 st.Bind(0,"NNTPListenPort");
\r
552 st.Bind(2,"The port that the NNTP service will listen for incoming connections.");
\r
556 // NNTPBindAddresses
\r
557 st.Bind(0,"NNTPBindAddresses");
\r
558 st.Bind(1,"localhost");
\r
559 st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");
\r
563 st.Bind(0,"NNTPAllowPost");
\r
565 st.Bind(2,"Allow posting messages from NNTP. Setting to false will make the newsgroups read only.");
\r
570 st.Bind(0,"StartNNTP");
\r
572 st.Bind(2,"Start NNTP server.");
\r
576 st.Bind(0,"StartHTTP");
\r
578 st.Bind(2,"Start HTTP server. WARNING: If you turn this off, you won't be able to access the administration pages.");
\r
582 st.Bind(0,"HTTPListenPort");
\r
584 st.Bind(2,"Port HTTP server will listen on.");
\r
588 st.Bind(0,"HTTPAccessControl");
\r
589 st.Bind(1,"-0.0.0.0/0,+127.0.0.1");
\r
590 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
594 // StartFreenetUpdater
\r
595 st.Bind(0,"StartFreenetUpdater");
\r
597 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
602 st.Bind(0,"FCPHost");
\r
603 st.Bind(1,"127.0.0.1");
\r
604 st.Bind(2,"Host name or address of Freenet node.");
\r
609 st.Bind(0,"FCPPort");
\r
611 st.Bind(2,"The port that Freenet is listening for FCP connections on.");
\r
615 st.Bind(0,"MessageBase");
\r
617 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
621 st.Bind(0,"MaxIdentityRequests");
\r
623 st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");
\r
627 st.Bind(0,"MaxIdentityIntroductionRequests");
\r
629 st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files. Each identity may have multiple requests pending.");
\r
633 st.Bind(0,"MaxIntroductionPuzzleRequests");
\r
635 st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");
\r
639 st.Bind(0,"MaxTrustListRequests");
\r
641 st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");
\r
645 st.Bind(0,"MaxMessageListRequests");
\r
647 st.Bind(2,"Maximum number of concurrent requests for new Message Lists");
\r
651 st.Bind(0,"MaxMessageRequests");
\r
653 st.Bind(2,"Maximum number of concurrent requests for new Messages");
\r
657 st.Bind(0,"MinLocalMessageTrust");
\r
659 st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");
\r
663 st.Bind(0,"MinPeerMessageTrust");
\r
665 st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");
\r
669 st.Bind(0,"MinLocalTrustListTrust");
\r
671 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
675 st.Bind(0,"MinPeerTrustListTrust");
\r
677 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
681 st.Bind(0,"MessageDownloadMaxDaysBackward");
\r
683 st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");
\r
687 st.Bind(0,"MessageListDaysBackward");
\r
689 st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");
\r
693 st.Bind(0,"MaxPeerMessagesPerDay");
\r
695 st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");
\r
699 st.Bind(0,"MaxBoardListRequests");
\r
701 st.Bind(2,"The maximum number of concurrent requests for new Board Lists. Set to 0 to disable.");
\r
705 st.Bind(0,"MaxBoardsPerMessage");
\r
707 st.Bind(2,"The maximum number of boards a received message may be sent to. Boards over this limit will be ignored.");
\r
711 st.Bind(0,"SaveMessagesFromNewBoards");
\r
713 st.Bind(2,"Set to true to automatically save messages posted to new boards. Set to false to ignore messages to new boards.");
\r
717 st.Bind(0,"ChangeMessageTrustOnReply");
\r
719 st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");
\r
723 st.Bind(0,"AddNewPostFromIdentities");
\r
724 st.Bind(1,"false");
\r
725 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
729 st.Bind(0,"DeleteMessagesOlderThan");
\r
731 st.Bind(2,"Automatically delete messages older than this many days.");
\r
737 void SetupLogFile()
\r
740 std::string configval;
\r
743 date.SetToGMTime();
\r
745 LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");
\r
746 LogFile::Instance()->OpenFile();
\r
747 LogFile::Instance()->SetWriteNewLine(true);
\r
748 LogFile::Instance()->SetWriteDate(true);
\r
749 LogFile::Instance()->SetWriteLogLevel(true);
\r
751 if(Option::Instance()->Get("LogLevel",configval)==false)
\r
754 Option::Instance()->Set("LogLevel",configval);
\r
756 if(StringFunctions::Convert(configval,loglevel)==false)
\r
758 loglevel=LogFile::LOGLEVEL_DEBUG;
\r
759 Option::Instance()->Set("LogLevel",loglevel);
\r
761 LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);
\r
764 void SetupNetwork()
\r
768 WSAStartup(MAKEWORD(2,2),&wsadata);
\r
774 ThreadController::Instance()->ShutdownThreads();
\r
778 LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");
\r
779 LogFile::Instance()->WriteNewLine();
\r
782 void ShutdownNetwork()
\r
789 void SigHandler(int signum)
\r
795 void FixCapitalBoardNames()
\r
797 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
799 SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");
\r
800 SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");
\r
801 SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");
\r
802 SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");
\r
803 SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");
\r
804 SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");
\r
807 while(st.RowReturned())
\r
811 std::string name="";
\r
812 std::string lowername="";
\r
814 st.ResultInt(0,boardid);
\r
815 st.ResultText(1,name);
\r
818 StringFunctions::LowerCase(lowername,lowername);
\r
820 if(name!=lowername)
\r
822 st2.Bind(0,lowername);
\r
825 if(st2.RowReturned())
\r
827 st2.ResultInt(0,newboardid);
\r
829 upd2.Bind(0,newboardid);
\r
830 upd2.Bind(1,boardid);
\r
834 upd3.Bind(0,newboardid);
\r
835 upd3.Bind(1,boardid);
\r
839 del.Bind(0,boardid);
\r
845 upd.Bind(0,lowername);
\r
846 upd.Bind(1,boardid);
\r