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
22 std::string CreateShortIdentityName(const std::string &name, const std::string &publickey)
\r
24 std::string result="";
\r
25 std::vector<std::string> keyparts;
\r
27 StringFunctions::SplitMultiple(publickey,"@,",keyparts);
\r
30 if(keyparts.size()>1 && keyparts[1].size()>8)
\r
32 result+="@"+keyparts[1].substr(0,4)+"...";
\r
42 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
44 db->Open("fms.db3");
\r
45 db->SetBusyTimeout(10000); // set timeout to 10 seconds
\r
46 //db->Execute("VACUUM;"); // not needed every startup
\r
48 db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\
\r
53 SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");
\r
55 if(st.RowReturned())
\r
59 st.ResultInt(0,major);
\r
60 st.ResultInt(1,minor);
\r
62 if(major==1 && minor==0)
\r
64 ConvertDB0100To0101();
\r
68 if(major==1 && (minor==1 || minor==2))
\r
70 ConvertDB0101To0103();
\r
74 if(major==1 && minor==3)
\r
76 ConvertDB0103To0104();
\r
80 if(major==1 && minor==4)
\r
82 ConvertDB0104To0105();
\r
86 if(major==1 && minor==5)
\r
88 ConvertDB0105To0106();
\r
92 if(major==1 && minor==6)
\r
94 ConvertDB0106To0107();
\r
98 if(major==1 && minor==7)
\r
100 ConvertDB0107To0108();
\r
107 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,8);");
\r
110 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=8;");
\r
112 db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\
\r
113 Option TEXT UNIQUE,\
\r
114 OptionValue TEXT NOT NULL,\
\r
115 OptionDescription TEXT\
\r
118 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
119 LocalIdentityID INTEGER PRIMARY KEY,\
\r
121 PublicKey TEXT UNIQUE,\
\r
122 PrivateKey TEXT UNIQUE,\
\r
123 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
124 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
125 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
126 PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\
\r
127 FreesiteEdition INTEGER,\
\r
128 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
129 LastInsertedIdentity DATETIME,\
\r
130 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
131 LastInsertedPuzzle DATETIME,\
\r
132 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
133 LastInsertedTrustList DATETIME,\
\r
134 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
135 LastInsertedBoardList DATETIME,\
\r
136 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
137 LastInsertedMessageList DATETIME,\
\r
138 LastInsertedFreesite DATETIME,\
\r
139 DateCreated DATETIME\
\r
142 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\
\r
143 LocalIdentityID INTEGER,\
\r
145 InsertIndex INTEGER\
\r
148 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\
\r
149 LocalIdentityID INTEGER,\
\r
151 InsertIndex INTEGER\
\r
154 db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\
\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 tblIntroductionPuzzleInserts(\
\r
163 LocalIdentityID INTEGER,\
\r
165 InsertIndex INTEGER,\
\r
169 PuzzleSolution TEXT,\
\r
170 FoundSolution BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\
\r
173 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
174 IdentityID INTEGER PRIMARY KEY,\
\r
175 PublicKey TEXT UNIQUE,\
\r
177 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
178 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
179 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
180 FreesiteEdition INTEGER,\
\r
181 DateAdded DATETIME,\
\r
182 LastSeen DATETIME,\
\r
183 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
184 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
185 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
186 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
188 MessageTrustComment TEXT,\
\r
189 TrustListTrustComment TEXT,\
\r
190 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\
\r
193 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\
\r
194 IdentityID INTEGER,\
\r
196 RequestIndex INTEGER,\
\r
197 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
200 db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\
\r
201 IdentityID INTEGER,\
\r
203 RequestIndex INTEGER,\
\r
204 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\
\r
211 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\
\r
212 LocalIdentityID INTEGER,\
\r
216 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
219 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\
\r
220 LocalIdentityID INTEGER,\
\r
221 IdentityID INTEGER,\
\r
222 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
223 MessageTrustComment TEXT,\
\r
224 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
225 TrustListTrustComment TEXT\
\r
228 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");
\r
230 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \
\r
233 UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \
\r
236 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \
\r
239 UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \
\r
242 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \
\r
245 UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=old.IdentityID; \
\r
248 db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\
\r
249 IdentityID INTEGER,\
\r
250 TargetIdentityID INTEGER,\
\r
251 MessageTrust INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\
\r
252 TrustListTrust INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\
\r
253 MessageTrustComment TEXT,\
\r
254 TrustListTrustComment TEXT\
\r
257 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");
\r
258 db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");
\r
260 db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\
\r
261 BoardID INTEGER PRIMARY KEY,\
\r
262 BoardName TEXT UNIQUE,\
\r
263 BoardDescription TEXT,\
\r
264 DateAdded DATETIME,\
\r
265 SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\
\r
269 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");
\r
270 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initialt Board');");
\r
271 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");
\r
272 db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");
\r
274 db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
\r
275 MessageID INTEGER PRIMARY KEY,\
\r
276 IdentityID INTEGER,\
\r
281 MessageUUID TEXT UNIQUE,\
\r
282 ReplyBoardID INTEGER,\
\r
284 MessageIndex INTEGER\
\r
287 db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");
\r
289 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\
\r
290 MessageID INTEGER,\
\r
291 ReplyToMessageUUID TEXT,\
\r
292 ReplyOrder INTEGER\
\r
295 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");
\r
297 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\
\r
298 MessageID INTEGER,\
\r
302 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");
\r
303 db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");
\r
305 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\
\r
306 IdentityID INTEGER,\
\r
308 RequestIndex INTEGER,\
\r
309 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
312 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\
\r
313 IdentityID INTEGER,\
\r
315 RequestIndex INTEGER,\
\r
316 FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\
\r
317 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
320 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
322 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\
\r
323 LocalIdentityID INTEGER,\
\r
325 InsertIndex INTEGER,\
\r
326 MessageUUID TEXT UNIQUE,\
\r
328 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
331 db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\
\r
332 LocalIdentityID INTEGER,\
\r
334 InsertIndex INTEGER,\
\r
335 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
338 db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\
\r
339 BoardID INTEGER UNIQUE,\
\r
340 ModifyLocalMessageTrust INTEGER,\
\r
341 ModifyLocalTrustListTrust INTEGER\
\r
344 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\
\r
345 LocalIdentityID INTEGER,\
\r
347 InsertIndex INTEGER,\
\r
348 Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\
\r
351 db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\
\r
352 IdentityID INTEGER,\
\r
354 RequestIndex INTEGER,\
\r
355 Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
\r
358 // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed
\r
359 db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\
\r
360 LocalIdentityID INTEGER,\
\r
364 // low / high / message count for each board
\r
365 db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \
\r
366 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \
\r
367 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \
\r
368 WHERE MessageID>=0 OR MessageID IS NULL \
\r
369 GROUP BY tblBoard.BoardID;");
\r
371 // calculates peer trust
\r
372 // 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
373 // need the +1 so that when the values are 0 the result is not 0
\r
374 db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");
\r
375 db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \
\r
376 SELECT TargetIdentityID, \
\r
377 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \
\r
378 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \
\r
379 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \
\r
380 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \
\r
381 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \
\r
382 GROUP BY TargetIdentityID;");
\r
385 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed
\r
386 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class
\r
388 // drop existing triggers
\r
389 db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");
\r
390 db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");
\r
391 db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");
\r
392 db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");
\r
394 // update PeerTrustLevel when deleting a record from tblPeerTrust
\r
395 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \
\r
398 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
401 // update PeerTrustLevel when inserting a record into tblPeerTrust
\r
402 db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \
\r
405 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
408 // update PeerTrustLevel when updating a record in tblPeerTrust
\r
409 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \
\r
412 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
413 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
416 // 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
417 db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \
\r
420 UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\
\r
424 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \
\r
427 DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\
\r
428 DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\
\r
431 db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");
\r
432 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \
\r
435 DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\
\r
436 DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\
\r
437 DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\
\r
438 DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\
\r
439 DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\
\r
440 DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\
\r
441 DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\
\r
444 db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");
\r
445 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \
\r
448 DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
449 DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
450 DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
451 DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
452 DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
453 DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\
\r
454 DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\
\r
457 db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \
\r
460 DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\
\r
463 // delete introduction puzzles that were half-way inserted
\r
464 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");
\r
466 // delete stale introduction puzzles (2 or more days old)
\r
467 date.SetToGMTime();
\r
468 date.Add(0,0,0,-2);
\r
469 db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
470 db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");
\r
472 date.SetToGMTime();
\r
473 // insert SomeDude's public key
\r
474 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
475 // insert Shadow Panther's public key
\r
476 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
477 // insert garfield's public key
\r
478 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
480 // TODO remove sometime after 0.1.17
\r
481 FixCapitalBoardNames();
\r
483 // run analyze - may speed up some queries
\r
484 db->Execute("ANALYZE;");
\r
488 void ConvertDB0100To0101()
\r
490 // added unique constraint to public and private key
\r
491 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
492 db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");
\r
493 db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");
\r
494 db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\
\r
495 LocalIdentityID INTEGER PRIMARY KEY,\
\r
497 PublicKey TEXT UNIQUE,\
\r
498 PrivateKey TEXT UNIQUE,\
\r
499 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
500 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
501 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
502 InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\
\r
503 LastInsertedIdentity DATETIME,\
\r
504 InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\
\r
505 LastInsertedPuzzle DATETIME,\
\r
506 InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\
\r
507 LastInsertedTrustList DATETIME,\
\r
508 InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\
\r
509 LastInsertedBoardList DATETIME,\
\r
510 InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\
\r
511 LastInsertedMessageList DATETIME\
\r
513 db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");
\r
514 db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");
\r
515 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");
\r
518 void ConvertDB0101To0103()
\r
520 // remove default 50 from trust fields and set default to NULL
\r
521 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
522 db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");
\r
523 db->Execute("DROP TABLE IF EXISTS tblIdentity;");
\r
524 db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\
\r
525 IdentityID INTEGER PRIMARY KEY,\
\r
526 PublicKey TEXT UNIQUE,\
\r
528 SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\
\r
529 PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\
\r
530 PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\
\r
531 DateAdded DATETIME,\
\r
532 LastSeen DATETIME,\
\r
533 LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
534 PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
535 LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\
\r
536 PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\
\r
538 db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");
\r
539 db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");
\r
541 // add SaveReceivedMessages field to tblBoard
\r
542 db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");
\r
544 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");
\r
547 void ConvertDB0103To0104()
\r
549 // add MessageIndex to tblMessage
\r
551 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
552 db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex INTEGER;");
\r
553 db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");
\r
554 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");
\r
555 date.SetToGMTime();
\r
556 db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");
\r
557 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");
\r
560 void ConvertDB0104To0105()
\r
562 // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity
\r
563 // add MessageTrustComment,TrustListTrustComment to tblPeerTrust
\r
564 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
565 db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");
\r
566 db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");
\r
567 db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");
\r
568 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");
\r
569 db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");
\r
570 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");
\r
573 void ConvertDB0105To0106()
\r
575 // add Publish Freesite
\r
576 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
577 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");
\r
578 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");
\r
579 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");
\r
582 void ConvertDB0106To0107()
\r
584 // add AddedMethod to tblBoard
\r
585 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
586 db->Execute("ALTER TABLE tblBoard ADD COLUMN AddedMethod TEXT;");
\r
587 db->Execute("ALTER TABLE tblIdentity ADD COLUMN Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false';");
\r
588 db->Execute("UPDATE tblIdentity SET Hidden='false' WHERE Hidden IS NULL;");
\r
589 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");
\r
592 void ConvertDB0107To0108()
\r
594 // add FreesiteEdition to tblLocalIdentity and tblIdentity
\r
595 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
596 db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN FreesiteEdition INTEGER;");
\r
597 db->Execute("ALTER TABLE tblIdentity ADD COLUMN FreesiteEdition INTEGER;");
\r
598 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=8;");
\r
601 void SetupDefaultOptions()
\r
603 // 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
605 std::ostringstream tempstr; // must set tempstr to "" between db inserts
\r
606 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
607 SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");
\r
611 tempstr << LogFile::LOGLEVEL_DEBUG;
\r
612 st.Bind(0,"LogLevel");
\r
613 st.Bind(1,tempstr.str());
\r
614 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
619 st.Bind(0,"NNTPListenPort");
\r
621 st.Bind(2,"The port that the NNTP service will listen for incoming connections.");
\r
625 // NNTPBindAddresses
\r
626 st.Bind(0,"NNTPBindAddresses");
\r
627 st.Bind(1,"localhost");
\r
628 st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");
\r
632 st.Bind(0,"NNTPAllowPost");
\r
634 st.Bind(2,"Allow posting messages from NNTP. Setting to false will make the newsgroups read only.");
\r
639 st.Bind(0,"StartNNTP");
\r
641 st.Bind(2,"Start NNTP server.");
\r
645 st.Bind(0,"StartHTTP");
\r
647 st.Bind(2,"Start HTTP server. WARNING: If you turn this off, you won't be able to access the administration pages.");
\r
651 st.Bind(0,"HTTPListenPort");
\r
653 st.Bind(2,"Port HTTP server will listen on.");
\r
657 st.Bind(0,"HTTPAccessControl");
\r
658 st.Bind(1,"-0.0.0.0/0,+127.0.0.1");
\r
659 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
663 // StartFreenetUpdater
\r
664 st.Bind(0,"StartFreenetUpdater");
\r
666 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
671 st.Bind(0,"FCPHost");
\r
672 st.Bind(1,"127.0.0.1");
\r
673 st.Bind(2,"Host name or address of Freenet node.");
\r
678 st.Bind(0,"FCPPort");
\r
680 st.Bind(2,"The port that Freenet is listening for FCP connections on.");
\r
684 st.Bind(0,"MessageBase");
\r
686 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
690 st.Bind(0,"MaxIdentityRequests");
\r
692 st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");
\r
696 st.Bind(0,"MaxIdentityIntroductionRequests");
\r
698 st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files. Each identity may have multiple requests pending.");
\r
702 st.Bind(0,"MaxIntroductionPuzzleRequests");
\r
704 st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");
\r
708 st.Bind(0,"MaxTrustListRequests");
\r
710 st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");
\r
714 st.Bind(0,"MaxMessageListRequests");
\r
716 st.Bind(2,"Maximum number of concurrent requests for new Message Lists");
\r
720 st.Bind(0,"MaxMessageRequests");
\r
722 st.Bind(2,"Maximum number of concurrent requests for new Messages");
\r
726 st.Bind(0,"MinLocalMessageTrust");
\r
728 st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");
\r
732 st.Bind(0,"MinPeerMessageTrust");
\r
734 st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");
\r
738 st.Bind(0,"MinLocalTrustListTrust");
\r
740 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
744 st.Bind(0,"MinPeerTrustListTrust");
\r
746 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
750 st.Bind(0,"MessageDownloadMaxDaysBackward");
\r
752 st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");
\r
756 st.Bind(0,"MessageListDaysBackward");
\r
758 st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");
\r
762 st.Bind(0,"MaxPeerMessagesPerDay");
\r
764 st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");
\r
768 st.Bind(0,"MaxBoardListRequests");
\r
770 st.Bind(2,"The maximum number of concurrent requests for new Board Lists. Set to 0 to disable.");
\r
774 st.Bind(0,"MaxBoardsPerMessage");
\r
776 st.Bind(2,"The maximum number of boards a received message may be sent to. Boards over this limit will be ignored.");
\r
780 st.Bind(0,"SaveMessagesFromNewBoards");
\r
782 st.Bind(2,"Set to true to automatically save messages posted to new boards. Set to false to ignore messages to new boards.");
\r
786 st.Bind(0,"ChangeMessageTrustOnReply");
\r
788 st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");
\r
792 st.Bind(0,"AddNewPostFromIdentities");
\r
793 st.Bind(1,"false");
\r
794 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
798 st.Bind(0,"DeleteMessagesOlderThan");
\r
800 st.Bind(2,"Automatically delete messages older than this many days.");
\r
806 void SetupLogFile()
\r
809 std::string configval;
\r
812 date.SetToGMTime();
\r
814 LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");
\r
815 LogFile::Instance()->OpenFile();
\r
816 LogFile::Instance()->SetWriteNewLine(true);
\r
817 LogFile::Instance()->SetWriteDate(true);
\r
818 LogFile::Instance()->SetWriteLogLevel(true);
\r
820 if(Option::Instance()->Get("LogLevel",configval)==false)
\r
823 Option::Instance()->Set("LogLevel",configval);
\r
825 if(StringFunctions::Convert(configval,loglevel)==false)
\r
827 loglevel=LogFile::LOGLEVEL_DEBUG;
\r
828 Option::Instance()->Set("LogLevel",loglevel);
\r
830 LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);
\r
833 void SetupNetwork()
\r
837 WSAStartup(MAKEWORD(2,2),&wsadata);
\r
843 ThreadController::Instance()->ShutdownThreads();
\r
847 LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");
\r
848 LogFile::Instance()->WriteNewLine();
\r
851 void ShutdownNetwork()
\r
858 void SigHandler(int signum)
\r
864 void FixCapitalBoardNames()
\r
866 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
868 SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");
\r
869 SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");
\r
870 SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");
\r
871 SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");
\r
872 SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");
\r
873 SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");
\r
876 while(st.RowReturned())
\r
880 std::string name="";
\r
881 std::string lowername="";
\r
883 st.ResultInt(0,boardid);
\r
884 st.ResultText(1,name);
\r
887 StringFunctions::LowerCase(lowername,lowername);
\r
889 if(name!=lowername)
\r
891 st2.Bind(0,lowername);
\r
894 if(st2.RowReturned())
\r
896 st2.ResultInt(0,newboardid);
\r
898 upd2.Bind(0,newboardid);
\r
899 upd2.Bind(1,boardid);
\r
903 upd3.Bind(0,newboardid);
\r
904 upd3.Bind(1,boardid);
\r
908 del.Bind(0,boardid);
\r
914 upd.Bind(0,lowername);
\r
915 upd.Bind(1,boardid);
\r