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
79 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,6);");
\r
82 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");
\r
84 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
85 Option TEXT UNIQUE,\
\r
86 OptionValue TEXT NOT NULL,\
\r
87 OptionDescription TEXT\
\r
90 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
91 LocalIdentityID INTEGER PRIMARY KEY,\
\r
93 PublicKey TEXT UNIQUE,\
\r
94 PrivateKey TEXT UNIQUE,\
\r
95 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
96 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
97 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
98 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
99 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
100 LastInsertedIdentity DATETIME,\
\r
101 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
102 LastInsertedPuzzle DATETIME,\
\r
103 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
104 LastInsertedTrustList DATETIME,\
\r
105 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
106 LastInsertedBoardList DATETIME,\
\r
107 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
108 LastInsertedMessageList DATETIME,\
\r
109 LastInsertedFreesite DATETIME,\
\r
110 DateCreated DATETIME\
\r
113 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
114 LocalIdentityID INTEGER,\
\r
116 InsertIndex INTEGER\
\r
119 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
120 LocalIdentityID INTEGER,\
\r
122 InsertIndex INTEGER\
\r
125 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\r
126 IdentityID INTEGER,\
\r
128 RequestIndex INTEGER,\
\r
129 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
132 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\
\r
134 LocalIdentityID INTEGER,\
\r
136 InsertIndex INTEGER,\
\r
140 PuzzleSolution TEXT,\
\r
141 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
144 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
145 IdentityID INTEGER PRIMARY KEY,\
\r
146 PublicKey TEXT UNIQUE,\
\r
148 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
149 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
150 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
151 DateAdded DATETIME,\
\r
152 LastSeen DATETIME,\
\r
153 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
154 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
155 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
156 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
158 MessageTrustComment TEXT,\
\r
159 TrustListTrustComment TEXT\
\r
162 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
163 IdentityID INTEGER,\
\r
165 RequestIndex INTEGER,\
\r
166 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
169 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
170 IdentityID INTEGER,\
\r
172 RequestIndex INTEGER,\
\r
173 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
180 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
181 LocalIdentityID INTEGER,\
\r
185 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
188 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
189 IdentityID INTEGER,\
\r
190 TargetIdentityID INTEGER,\
\r
191 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
192 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
193 MessageTrustComment TEXT,\
\r
194 TrustListTrustComment TEXT\
\r
197 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
198 BoardID INTEGER PRIMARY KEY,\
\r
199 BoardName TEXT UNIQUE,\
\r
200 BoardDescription TEXT,\
\r
201 DateAdded DATETIME,\
\r
202 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true'\
\r
205 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01 12:00:00');");
\r
206 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00');");
\r
207 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('public','Public discussion','2007-12-01 12:00:00');");
\r
208 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('test','Test board','2007-12-01 12:00:00');");
\r
210 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
211 MessageID INTEGER PRIMARY KEY,\
\r
212 IdentityID INTEGER,\
\r
217 MessageUUID TEXT UNIQUE,\
\r
218 ReplyBoardID INTEGER,\
\r
220 MessageIndex INTEGER\
\r
223 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
224 MessageID INTEGER,\
\r
225 ReplyToMessageUUID TEXT,\
\r
226 ReplyOrder INTEGER\
\r
229 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
230 MessageID INTEGER,\
\r
234 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
235 IdentityID INTEGER,\
\r
237 RequestIndex INTEGER,\
\r
238 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
241 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
242 IdentityID INTEGER,\
\r
244 RequestIndex INTEGER,\
\r
245 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
246 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
249 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
251 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
252 LocalIdentityID INTEGER,\
\r
254 InsertIndex INTEGER,\
\r
255 MessageUUID TEXT UNIQUE,\
\r
257 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
260 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
261 LocalIdentityID INTEGER,\
\r
263 InsertIndex INTEGER,\
\r
264 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
267 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
268 BoardID INTEGER UNIQUE,\
\r
269 ModifyLocalMessageTrust INTEGER,\
\r
270 ModifyLocalTrustListTrust INTEGER\
\r
273 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
274 LocalIdentityID INTEGER,\
\r
276 InsertIndex INTEGER,\
\r
277 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
280 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
281 IdentityID INTEGER,\
\r
283 RequestIndex INTEGER,\
\r
284 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
287 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
288 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
289 LocalIdentityID INTEGER,\
\r
293 // low / high / message count for each board
\r
294 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
295 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
296 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
297 WHERE MessageID>=0 OR MessageID IS NULL \
\r
298 GROUP BY tblBoard.BoardID;");
\r
300 // calculates peer trust
\r
301 // 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
302 // need the +1 so that when the values are 0 the result is not 0
\r
303 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
304 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
305 SELECT TargetIdentityID, \
\r
306 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
307 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
308 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
309 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
310 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
311 GROUP BY TargetIdentityID;");
\r
314 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
315 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
317 // drop existing triggers
\r
318 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
319 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
320 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
321 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
323 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
324 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
327 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
330 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
331 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\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 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
338 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
341 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
342 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
345 // 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
346 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
349 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
353 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
356 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
357 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
360 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
363 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
364 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
365 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
366 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
367 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
368 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
371 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
374 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
375 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
376 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
377 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
378 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
379 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
382 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
385 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
388 // delete introduction puzzles that were half-way inserted
\r
389 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
391 // delete stale introduction puzzles (2 or more days old)
\r
392 date.SetToGMTime();
\r
393 date.Add(0,0,0,-2);
\r
394 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
395 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
397 date.SetToGMTime();
\r
398 // insert SomeDude's public key
\r
399 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
400 // insert Shadow Panther's public key
\r
401 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");
\r
402 // insert garfield's public key
\r
403 db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");
\r
405 // TODO remove sometime after 0.1.17
\r
406 FixCapitalBoardNames();
\r
410 void ConvertDB0100To0101()
\r
412 // added unique constraint to public and private key
\r
413 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
414 db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");
\r
415 db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");
\r
416 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
417 LocalIdentityID INTEGER PRIMARY KEY,\
\r
419 PublicKey TEXT UNIQUE,\
\r
420 PrivateKey TEXT UNIQUE,\
\r
421 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
422 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
423 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
424 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
425 LastInsertedIdentity DATETIME,\
\r
426 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
427 LastInsertedPuzzle DATETIME,\
\r
428 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
429 LastInsertedTrustList DATETIME,\
\r
430 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
431 LastInsertedBoardList DATETIME,\
\r
432 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
433 LastInsertedMessageList DATETIME\
\r
435 db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");
\r
436 db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");
\r
437 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");
\r
440 void ConvertDB0101To0103()
\r
442 // remove default 50 from trust fields and set default to NULL
\r
443 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
444 db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");
\r
445 db->Execute("DROP TABLE IF EXISTS tblIdentity;");
\r
446 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
447 IdentityID INTEGER PRIMARY KEY,\
\r
448 PublicKey TEXT UNIQUE,\
\r
450 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
451 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
452 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
453 DateAdded DATETIME,\
\r
454 LastSeen DATETIME,\
\r
455 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
456 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
457 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
458 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\
\r
460 db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");
\r
461 db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");
\r
463 // add SaveReceivedMessages field to tblBoard
\r
464 db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");
\r
466 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");
\r
469 void ConvertDB0103To0104()
\r
471 // add MessageIndex to tblMessage
\r
473 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
474 db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex INTEGER;");
\r
475 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
476 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");
\r
477 date.SetToGMTime();
\r
478 db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");
\r
479 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");
\r
482 void ConvertDB0104To0105()
\r
484 // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity
\r
485 // add MessageTrustComment,TrustListTrustComment to tblPeerTrust
\r
486 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
487 db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");
\r
488 db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");
\r
489 db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");
\r
490 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");
\r
491 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");
\r
492 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");
\r
495 void ConvertDB0105To0106()
\r
497 // add Publish Freesite
\r
498 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
499 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");
\r
500 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");
\r
501 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");
\r
504 void SetupDefaultOptions()
\r
506 // 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
508 std::ostringstream tempstr; // must set tempstr to "" between db inserts
\r
509 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
510 SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");
\r
514 tempstr << LogFile::LOGLEVEL_DEBUG;
\r
515 st.Bind(0,"LogLevel");
\r
516 st.Bind(1,tempstr.str());
\r
517 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
522 st.Bind(0,"NNTPListenPort");
\r
524 st.Bind(2,"The port that the NNTP service will listen for incoming connections.");
\r
528 // NNTPBindAddresses
\r
529 st.Bind(0,"NNTPBindAddresses");
\r
530 st.Bind(1,"localhost");
\r
531 st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");
\r
535 st.Bind(0,"NNTPAllowPost");
\r
537 st.Bind(2,"Allow posting messages from NNTP. Setting to false will make the newsgroups read only.");
\r
542 st.Bind(0,"StartNNTP");
\r
544 st.Bind(2,"Start NNTP server.");
\r
548 st.Bind(0,"StartHTTP");
\r
550 st.Bind(2,"Start HTTP server. WARNING: If you turn this off, you won't be able to access the administration pages.");
\r
554 st.Bind(0,"HTTPListenPort");
\r
556 st.Bind(2,"Port HTTP server will listen on.");
\r
560 st.Bind(0,"HTTPAccessControl");
\r
561 st.Bind(1,"-0.0.0.0/0,+127.0.0.1");
\r
562 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
566 // StartFreenetUpdater
\r
567 st.Bind(0,"StartFreenetUpdater");
\r
569 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
574 st.Bind(0,"FCPHost");
\r
575 st.Bind(1,"127.0.0.1");
\r
576 st.Bind(2,"Host name or address of Freenet node.");
\r
581 st.Bind(0,"FCPPort");
\r
583 st.Bind(2,"The port that Freenet is listening for FCP connections on.");
\r
587 st.Bind(0,"MessageBase");
\r
589 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
593 st.Bind(0,"MaxIdentityRequests");
\r
595 st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");
\r
599 st.Bind(0,"MaxIdentityIntroductionRequests");
\r
601 st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files. Each identity may have multiple requests pending.");
\r
605 st.Bind(0,"MaxIntroductionPuzzleRequests");
\r
607 st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");
\r
611 st.Bind(0,"MaxTrustListRequests");
\r
613 st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");
\r
617 st.Bind(0,"MaxMessageListRequests");
\r
619 st.Bind(2,"Maximum number of concurrent requests for new Message Lists");
\r
623 st.Bind(0,"MaxMessageRequests");
\r
625 st.Bind(2,"Maximum number of concurrent requests for new Messages");
\r
629 st.Bind(0,"MinLocalMessageTrust");
\r
631 st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");
\r
635 st.Bind(0,"MinPeerMessageTrust");
\r
637 st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");
\r
641 st.Bind(0,"MinLocalTrustListTrust");
\r
643 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
647 st.Bind(0,"MinPeerTrustListTrust");
\r
649 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
653 st.Bind(0,"MessageDownloadMaxDaysBackward");
\r
655 st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");
\r
659 st.Bind(0,"MessageListDaysBackward");
\r
661 st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");
\r
665 st.Bind(0,"MaxPeerMessagesPerDay");
\r
667 st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");
\r
671 st.Bind(0,"MaxBoardListRequests");
\r
673 st.Bind(2,"The maximum number of concurrent requests for new Board Lists. Set to 0 to disable.");
\r
677 st.Bind(0,"MaxBoardsPerMessage");
\r
679 st.Bind(2,"The maximum number of boards a received message may be sent to. Boards over this limit will be ignored.");
\r
683 st.Bind(0,"SaveMessagesFromNewBoards");
\r
685 st.Bind(2,"Set to true to automatically save messages posted to new boards. Set to false to ignore messages to new boards.");
\r
689 st.Bind(0,"ChangeMessageTrustOnReply");
\r
691 st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");
\r
695 st.Bind(0,"AddNewPostFromIdentities");
\r
696 st.Bind(1,"false");
\r
697 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
701 st.Bind(0,"DeleteMessagesOlderThan");
\r
703 st.Bind(2,"Automatically delete messages older than this many days.");
\r
709 void SetupLogFile()
\r
712 std::string configval;
\r
715 date.SetToGMTime();
\r
717 LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");
\r
718 LogFile::Instance()->OpenFile();
\r
719 LogFile::Instance()->SetWriteNewLine(true);
\r
720 LogFile::Instance()->SetWriteDate(true);
\r
721 LogFile::Instance()->SetWriteLogLevel(true);
\r
723 if(Option::Instance()->Get("LogLevel",configval)==false)
\r
726 Option::Instance()->Set("LogLevel",configval);
\r
728 if(StringFunctions::Convert(configval,loglevel)==false)
\r
730 loglevel=LogFile::LOGLEVEL_DEBUG;
\r
731 Option::Instance()->Set("LogLevel",loglevel);
\r
733 LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);
\r
736 void SetupNetwork()
\r
740 WSAStartup(MAKEWORD(2,2),&wsadata);
\r
746 ThreadController::Instance()->ShutdownThreads();
\r
750 LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");
\r
751 LogFile::Instance()->WriteNewLine();
\r
754 void ShutdownNetwork()
\r
761 void SigHandler(int signum)
\r
767 void FixCapitalBoardNames()
\r
769 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
771 SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");
\r
772 SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");
\r
773 SQLite3DB::Statement del=db->Prepare("DELTE FROM tblBoard WHERE BoardID=?;");
\r
774 SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");
\r
775 SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");
\r
776 SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");
\r
779 while(st.RowReturned())
\r
783 std::string name="";
\r
784 std::string lowername="";
\r
786 st.ResultInt(0,boardid);
\r
787 st.ResultText(1,name);
\r
790 StringFunctions::LowerCase(lowername,lowername);
\r
792 if(name!=lowername)
\r
794 st2.Bind(0,lowername);
\r
797 if(st2.RowReturned())
\r
799 st2.ResultInt(0,newboardid);
\r
801 upd2.Bind(0,newboardid);
\r
802 upd2.Bind(1,boardid);
\r
806 upd3.Bind(0,newboardid);
\r
807 upd3.Bind(1,boardid);
\r
811 del.Bind(0,boardid);
\r
817 upd.Bind(0,lowername);
\r
818 upd.Bind(1,boardid);
\r