src/global.cpp

   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
  11 \r
  12 #ifdef _WIN32\r
  13         #include <winsock2.h>\r
  14 #endif\r
  15 \r
  16 #ifdef XMEM\r
  17         #include <xmem.h>\r
  18 #endif\r
  19 \r
  20 bool wantshutdown=false;\r
  21 \r
  22 void SetupDB()\r
  23 {\r
  24 \r
  25         DateTime date;\r
  26         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  27 \r
  28         db->Open("fms.db3");\r
  29         db->SetBusyTimeout(10000);              // set timeout to 10 seconds\r
  30         db->Execute("VACUUM;");\r
  31         \r
  32         // TODO remove this - temp fix for problem in 0.1.8\r
  33         db->Execute("DELETE FROM tblMessageBoard WHERE MessageID NOT IN (SELECT MessageID FROM tblMessage);");\r
  34 \r
  35         db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
  36                                 Major                           INTEGER,\\r
  37                                 Minor                           INTEGER\\r
  38                                 );");\r
  39 \r
  40         SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");\r
  41         st.Step();\r
  42         if(st.RowReturned())\r
  43         {\r
  44                 int major;\r
  45                 int minor;\r
  46                 st.ResultInt(0,major);\r
  47                 st.ResultInt(1,minor);\r
  48                 st.Finalize();\r
  49                 if(major==1 && minor==0)\r
  50                 {\r
  51                         ConvertDB0100To0101();\r
  52                         major=1;\r
  53                         minor=1;\r
  54                 }\r
  55                 if(major==1 && (minor==1 || minor==2))\r
  56                 {\r
  57                         ConvertDB0101To0103();\r
  58                         major=1;\r
  59                         minor=3;\r
  60                 }\r
  61         }\r
  62         else\r
  63         {\r
  64                 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,1);");\r
  65         }\r
  66 \r
  67         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
  68 \r
  69         db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
  70                                 Option                          TEXT UNIQUE,\\r
  71                                 OptionValue                     TEXT NOT NULL,\\r
  72                                 OptionDescription       TEXT\\r
  73                                 );");\r
  74 \r
  75         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
  76                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
  77                                 Name                                    TEXT,\\r
  78                                 PublicKey                               TEXT UNIQUE,\\r
  79                                 PrivateKey                              TEXT UNIQUE,\\r
  80                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
  81                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
  82                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
  83                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
  84                                 LastInsertedIdentity    DATETIME,\\r
  85                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
  86                                 LastInsertedPuzzle              DATETIME,\\r
  87                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
  88                                 LastInsertedTrustList   DATETIME,\\r
  89                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
  90                                 LastInsertedBoardList   DATETIME,\\r
  91                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
  92                                 LastInsertedMessageList DATETIME\\r
  93                                 );");\r
  94 \r
  95         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r
  96                                 LocalIdentityID         INTEGER,\\r
  97                                 Day                                     DATE,\\r
  98                                 InsertIndex                     INTEGER\\r
  99                                 );");\r
 100 \r
 101         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\\r
 102                                 LocalIdentityID         INTEGER,\\r
 103                                 Day                                     DATE,\\r
 104                                 InsertIndex                     INTEGER\\r
 105                                 );");\r
 106 \r
 107         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\\r
 108                                 IdentityID                      INTEGER,\\r
 109                                 Day                                     DATE,\\r
 110                                 RequestIndex            INTEGER,\\r
 111                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 112                                 );");\r
 113 \r
 114         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
 115                                 UUID                            TEXT UNIQUE,\\r
 116                                 LocalIdentityID         INTEGER,\\r
 117                                 Day                                     DATE,\\r
 118                                 InsertIndex                     INTEGER,\\r
 119                                 Type                            TEXT,\\r
 120                                 MimeType                        TEXT,\\r
 121                                 PuzzleData                      TEXT,\\r
 122                                 PuzzleSolution          TEXT,\\r
 123                                 FoundSolution           BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\\r
 124                                 );");\r
 125 \r
 126         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 127                                 IdentityID                      INTEGER PRIMARY KEY,\\r
 128                                 PublicKey                       TEXT UNIQUE,\\r
 129                                 Name                            TEXT,\\r
 130                                 SingleUse                       BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 131                                 PublishTrustList        BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 132                                 PublishBoardList        BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 133                                 DateAdded                       DATETIME,\\r
 134                                 LastSeen                        DATETIME,\\r
 135                                 LocalMessageTrust       INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 136                                 PeerMessageTrust        INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 137                                 LocalTrustListTrust     INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 138                                 PeerTrustListTrust      INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\\r
 139                                 );");\r
 140 \r
 141         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
 142                                 IdentityID                      INTEGER,\\r
 143                                 Day                                     DATE,\\r
 144                                 RequestIndex            INTEGER,\\r
 145                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 146                                 );");\r
 147 \r
 148         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\\r
 149                                 IdentityID                      INTEGER,\\r
 150                                 Day                                     DATE,\\r
 151                                 RequestIndex            INTEGER,\\r
 152                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 153                                 UUID                            TEXT UNIQUE,\\r
 154                                 Type                            TEXT,\\r
 155                                 MimeType                        TEXT,\\r
 156                                 PuzzleData                      TEXT\\r
 157                                 );");\r
 158 \r
 159         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\\r
 160                                 LocalIdentityID         INTEGER,\\r
 161                                 Day                                     DATE,\\r
 162                                 UUID                            TEXT UNIQUE,\\r
 163                                 Solution                        TEXT,\\r
 164                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 165                                 );");\r
 166 \r
 167         db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
 168                                 IdentityID                      INTEGER,\\r
 169                                 TargetIdentityID        INTEGER,\\r
 170                                 MessageTrust            INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\\r
 171                                 TrustListTrust          INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100)\\r
 172                                 );");\r
 173 \r
 174         db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\\r
 175                                 BoardID                                 INTEGER PRIMARY KEY,\\r
 176                                 BoardName                               TEXT UNIQUE,\\r
 177                                 BoardDescription                TEXT,\\r
 178                                 DateAdded                               DATETIME,\\r
 179                                 SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true'\\r
 180                                 );");\r
 181 \r
 182         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01 12:00:00');");\r
 183         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00');");\r
 184         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('public','Public discussion','2007-12-01 12:00:00');");\r
 185         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('test','Test board','2007-12-01 12:00:00');");\r
 186 \r
 187         db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
 188                                 MessageID                       INTEGER PRIMARY KEY,\\r
 189                                 IdentityID                      INTEGER,\\r
 190                                 FromName                        TEXT,\\r
 191                                 MessageDate                     DATE,\\r
 192                                 MessageTime                     TIME,\\r
 193                                 Subject                         TEXT,\\r
 194                                 MessageUUID                     TEXT UNIQUE,\\r
 195                                 ReplyBoardID            INTEGER,\\r
 196                                 Body                            TEXT\\r
 197                                 );");\r
 198 \r
 199         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
 200                                 MessageID                       INTEGER,\\r
 201                                 ReplyToMessageUUID      TEXT,\\r
 202                                 ReplyOrder                      INTEGER\\r
 203                                 );");\r
 204 \r
 205         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
 206                                 MessageID                       INTEGER,\\r
 207                                 BoardID                         INTEGER\\r
 208                                 );");\r
 209 \r
 210         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\\r
 211                                 IdentityID                      INTEGER,\\r
 212                                 Day                                     DATE,\\r
 213                                 RequestIndex            INTEGER,\\r
 214                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 215                                 );");\r
 216 \r
 217         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\\r
 218                                 IdentityID                      INTEGER,\\r
 219                                 Day                                     DATE,\\r
 220                                 RequestIndex            INTEGER,\\r
 221                                 FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
 222                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 223                                 );");\r
 224 \r
 225         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\\r
 226                                 LocalIdentityID         INTEGER,\\r
 227                                 Day                                     DATE,\\r
 228                                 InsertIndex                     INTEGER,\\r
 229                                 MessageUUID                     TEXT UNIQUE,\\r
 230                                 MessageXML                      TEXT,\\r
 231                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 232                                 );");\r
 233 \r
 234         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
 235                                 LocalIdentityID         INTEGER,\\r
 236                                 Day                                     DATE,\\r
 237                                 InsertIndex                     INTEGER,\\r
 238                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 239                                 );");\r
 240 \r
 241         db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\\r
 242                                 BoardID                                         INTEGER UNIQUE,\\r
 243                                 ModifyLocalMessageTrust         INTEGER,\\r
 244                                 ModifyLocalTrustListTrust       INTEGER\\r
 245                                 );");\r
 246 \r
 247         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\\r
 248                                 LocalIdentityID         INTEGER,\\r
 249                                 Day                                     DATE,\\r
 250                                 InsertIndex                     INTEGER,\\r
 251                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 252                                 );");\r
 253 \r
 254         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\\r
 255                                 IdentityID                      INTEGER,\\r
 256                                 Day                                     DATE,\\r
 257                                 RequestIndex            INTEGER,\\r
 258                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 259                                 );");   \r
 260 \r
 261         // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed\r
 262         db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\\r
 263                                 LocalIdentityID         INTEGER,\\r
 264                                 Date                            DATETIME\\r
 265                                 );");\r
 266 \r
 267         // low / high / message count for each board\r
 268         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 269                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 270                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 271                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 272                                 GROUP BY tblBoard.BoardID;");\r
 273 \r
 274         // calculates peer trust\r
 275         // 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
 276         // need the +1 so that when the values are 0 the result is not 0\r
 277         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 278         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 279                                 SELECT TargetIdentityID, \\r
 280                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 281                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 282                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 283                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 284                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 285                                 GROUP BY TargetIdentityID;");\r
 286 \r
 287         /*\r
 288                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 289                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 290         */\r
 291         // drop existing triggers\r
 292         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 293         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 294         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 295         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 296 /*\r
 297         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 298         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 299                                 FOR EACH ROW \\r
 300                                 BEGIN \\r
 301                                         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
 302                                 END;");\r
 303 \r
 304         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 305         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 306                                 FOR EACH ROW \\r
 307                                 BEGIN \\r
 308                                         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
 309                                 END;");\r
 310 \r
 311         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 312         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 313                                 FOR EACH ROW \\r
 314                                 BEGIN \\r
 315                                         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
 316                                         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
 317                                 END;");\r
 318 \r
 319         // 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
 320         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 321                                 FOR EACH ROW \\r
 322                                 BEGIN \\r
 323                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 324                                 END;");\r
 325 */\r
 326 \r
 327         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 328                                 FOR EACH ROW \\r
 329                                 BEGIN \\r
 330                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 331                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 332                                 END;");\r
 333 \r
 334         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 335                                 FOR EACH ROW \\r
 336                                 BEGIN \\r
 337                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 338                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 339                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 340                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 341                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 342                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 343                                 END;");\r
 344 \r
 345         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 346                                 FOR EACH ROW \\r
 347                                 BEGIN \\r
 348                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 349                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 350                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 351                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 352                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 353                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 354                                 END;");\r
 355 \r
 356         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 357                                 FOR EACH ROW \\r
 358                                 BEGIN \\r
 359                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 360                                 END;");\r
 361 \r
 362         // delete introduction puzzles that were half-way inserted\r
 363         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 364 \r
 365         // delete stale introduction puzzles (2 or more days old)\r
 366         date.SetToGMTime();\r
 367         date.Add(0,0,0,-2);\r
 368         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 369         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 370 \r
 371         date.SetToGMTime();\r
 372         // insert SomeDude's public key\r
 373         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
 374         // insert Shadow Panther's public key\r
 375         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");\r
 376         // insert garfield's public key\r
 377         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");\r
 378 \r
 379 }\r
 380 \r
 381 void ConvertDB0100To0101()\r
 382 {\r
 383         // added unique constraint to public and private key\r
 384         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 385         db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
 386         db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
 387         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 388                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 389                                 Name                                    TEXT,\\r
 390                                 PublicKey                               TEXT UNIQUE,\\r
 391                                 PrivateKey                              TEXT UNIQUE,\\r
 392                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 393                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 394                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 395                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 396                                 LastInsertedIdentity    DATETIME,\\r
 397                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 398                                 LastInsertedPuzzle              DATETIME,\\r
 399                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 400                                 LastInsertedTrustList   DATETIME,\\r
 401                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
 402                                 LastInsertedBoardList   DATETIME,\\r
 403                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
 404                                 LastInsertedMessageList DATETIME\\r
 405                                 );");\r
 406         db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");\r
 407         db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
 408         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");\r
 409 }\r
 410 \r
 411 void ConvertDB0101To0103()\r
 412 {\r
 413         // remove default 50 from trust fields and set default to NULL\r
 414         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 415         db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
 416         db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
 417         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 418                                 IdentityID                      INTEGER PRIMARY KEY,\\r
 419                                 PublicKey                       TEXT UNIQUE,\\r
 420                                 Name                            TEXT,\\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                                 DateAdded                       DATETIME,\\r
 425                                 LastSeen                        DATETIME,\\r
 426                                 LocalMessageTrust       INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 427                                 PeerMessageTrust        INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 428                                 LocalTrustListTrust     INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 429                                 PeerTrustListTrust      INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\\r
 430                                 );");\r
 431         db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");\r
 432         db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
 433 \r
 434         // add SaveReceivedMessages field to tblBoard\r
 435         db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages       BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");\r
 436 \r
 437         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
 438 }\r
 439 \r
 440 void SetupDefaultOptions()\r
 441 {\r
 442         // 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
 443 \r
 444         std::ostringstream tempstr;     // must set tempstr to "" between db inserts\r
 445         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 446         SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");\r
 447 \r
 448         // LogLevel\r
 449         tempstr.str("");\r
 450         tempstr << LogFile::LOGLEVEL_DEBUG;\r
 451         st.Bind(0,"LogLevel");\r
 452         st.Bind(1,tempstr.str());\r
 453         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
 454         st.Step();\r
 455         st.Reset();\r
 456 \r
 457         // NNTPListenPort\r
 458         st.Bind(0,"NNTPListenPort");\r
 459         st.Bind(1,"1119");\r
 460         st.Bind(2,"The port that the NNTP service will listen for incoming connections.");\r
 461         st.Step();\r
 462         st.Reset();\r
 463 \r
 464         // NNTPBindAddresses\r
 465         st.Bind(0,"NNTPBindAddresses");\r
 466         st.Bind(1,"localhost");\r
 467         st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");\r
 468         st.Step();\r
 469         st.Reset();\r
 470 \r
 471         st.Bind(0,"NNTPAllowPost");\r
 472         st.Bind(1,"true");\r
 473         st.Bind(2,"Allow posting messages from NNTP.  Setting to false will make the newsgroups read only.");\r
 474         st.Step();\r
 475         st.Reset();\r
 476 \r
 477         // StartNNTP\r
 478         st.Bind(0,"StartNNTP");\r
 479         st.Bind(1,"true");\r
 480         st.Bind(2,"Start NNTP server.");\r
 481         st.Step();\r
 482         st.Reset();\r
 483 \r
 484         st.Bind(0,"StartHTTP");\r
 485         st.Bind(1,"true");\r
 486         st.Bind(2,"Start HTTP server.");\r
 487         st.Step();\r
 488         st.Reset();\r
 489 \r
 490         st.Bind(0,"HTTPListenPort");\r
 491         st.Bind(1,"8080");\r
 492         st.Bind(2,"Port HTTP server will listen on.");\r
 493         st.Step();\r
 494         st.Reset();\r
 495 \r
 496         // StartFreenetUpdater\r
 497         st.Bind(0,"StartFreenetUpdater");\r
 498         st.Bind(1,"true");\r
 499         st.Bind(2,"Start Freenet Updater thread.");\r
 500         st.Step();\r
 501         st.Reset();\r
 502 \r
 503         // FCPHost\r
 504         st.Bind(0,"FCPHost");\r
 505         st.Bind(1,"127.0.0.1");\r
 506         st.Bind(2,"Host name or address of Freenet node.");\r
 507         st.Step();\r
 508         st.Reset();\r
 509 \r
 510         // FCPPort\r
 511         st.Bind(0,"FCPPort");\r
 512         st.Bind(1,"9481");\r
 513         st.Bind(2,"The port that Freenet is listening for FCP connections on.");\r
 514         st.Step();\r
 515         st.Reset();\r
 516 \r
 517         st.Bind(0,"MessageBase");\r
 518         st.Bind(1,"fms");\r
 519         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
 520         st.Step();\r
 521         st.Reset();\r
 522 \r
 523         st.Bind(0,"MaxIdentityRequests");\r
 524         st.Bind(1,"5");\r
 525         st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");\r
 526         st.Step();\r
 527         st.Reset();\r
 528 \r
 529         st.Bind(0,"MaxIdentityIntroductionRequests");\r
 530         st.Bind(1,"5");\r
 531         st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files.  Each identity may have multiple requests pending.");\r
 532         st.Step();\r
 533         st.Reset();\r
 534 \r
 535         st.Bind(0,"MaxIntroductionPuzzleRequests");\r
 536         st.Bind(1,"5");\r
 537         st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");\r
 538         st.Step();\r
 539         st.Reset();\r
 540 \r
 541         st.Bind(0,"MaxTrustListRequests");\r
 542         st.Bind(1,"5");\r
 543         st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");\r
 544         st.Step();\r
 545         st.Reset();\r
 546 \r
 547         st.Bind(0,"MaxMessageListRequests");\r
 548         st.Bind(1,"5");\r
 549         st.Bind(2,"Maximum number of concurrent requests for new Message Lists");\r
 550         st.Step();\r
 551         st.Reset();\r
 552 \r
 553         st.Bind(0,"MaxMessageRequests");\r
 554         st.Bind(1,"20");\r
 555         st.Bind(2,"Maximum number of concurrent requests for new Messages");\r
 556         st.Step();\r
 557         st.Reset();\r
 558 \r
 559         st.Bind(0,"MinLocalMessageTrust");\r
 560         st.Bind(1,"50");\r
 561         st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");\r
 562         st.Step();\r
 563         st.Reset();\r
 564 \r
 565         st.Bind(0,"MinPeerMessageTrust");\r
 566         st.Bind(1,"30");\r
 567         st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");\r
 568         st.Step();\r
 569         st.Reset();\r
 570 \r
 571         st.Bind(0,"MinLocalTrustListTrust");\r
 572         st.Bind(1,"51");\r
 573         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
 574         st.Step();\r
 575         st.Reset();\r
 576 \r
 577         st.Bind(0,"MinPeerTrustListTrust");\r
 578         st.Bind(1,"30");\r
 579         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
 580         st.Step();\r
 581         st.Reset();\r
 582 \r
 583         st.Bind(0,"MessageDownloadMaxDaysBackward");\r
 584         st.Bind(1,"5");\r
 585         st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");\r
 586         st.Step();\r
 587         st.Reset();\r
 588 \r
 589         st.Bind(0,"MessageListDaysBackward");\r
 590         st.Bind(1,"5");\r
 591         st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");\r
 592         st.Step();\r
 593         st.Reset();\r
 594 \r
 595         st.Bind(0,"MaxPeerMessagesPerDay");\r
 596         st.Bind(1,"200");\r
 597         st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");\r
 598         st.Step();\r
 599         st.Reset();\r
 600 \r
 601         st.Bind(0,"MaxBoardListRequests");\r
 602         st.Bind(1,"5");\r
 603         st.Bind(2,"The maximum number of concurrent requests for new Board Lists.  Set to 0 to disable.");\r
 604         st.Step();\r
 605         st.Reset();\r
 606 \r
 607         st.Bind(0,"MaxBoardsPerMessage");\r
 608         st.Bind(1,"8");\r
 609         st.Bind(2,"The maximum number of boards a received message may be sent to.  Boards over this limit will be ignored.");\r
 610         st.Step();\r
 611         st.Reset();\r
 612 \r
 613         st.Bind(0,"ChangeMessageTrustOnReply");\r
 614         st.Bind(1,"0");\r
 615         st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");\r
 616         st.Step();\r
 617         st.Reset();\r
 618 \r
 619         st.Bind(0,"AddNewPostFromIdentities");\r
 620         st.Bind(1,"false");\r
 621         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
 622         st.Step();\r
 623         st.Reset();\r
 624 \r
 625 }\r
 626 \r
 627 void SetupLogFile()\r
 628 {\r
 629         DateTime date;\r
 630         std::string configval;\r
 631         int loglevel;\r
 632 \r
 633         date.SetToGMTime();\r
 634 \r
 635         LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");\r
 636         LogFile::Instance()->OpenFile();\r
 637         LogFile::Instance()->SetWriteNewLine(true);\r
 638         LogFile::Instance()->SetWriteDate(true);\r
 639         LogFile::Instance()->SetWriteLogLevel(true);\r
 640 \r
 641         if(Option::Instance()->Get("LogLevel",configval)==false)\r
 642         {\r
 643                 configval="4";\r
 644                 Option::Instance()->Set("LogLevel",configval);\r
 645         }\r
 646         if(StringFunctions::Convert(configval,loglevel)==false)\r
 647         {\r
 648                 loglevel=LogFile::LOGLEVEL_DEBUG;\r
 649                 Option::Instance()->Set("LogLevel",loglevel);\r
 650         }\r
 651         LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);\r
 652 }\r
 653 \r
 654 void SetupNetwork()\r
 655 {\r
 656 #ifdef _WIN32\r
 657         WSAData wsadata;\r
 658         WSAStartup(MAKEWORD(2,2),&wsadata);\r
 659 #endif\r
 660 }\r
 661 \r
 662 void Shutdown()\r
 663 {\r
 664         ThreadController::Instance()->ShutdownThreads();\r
 665 \r
 666         ShutdownNetwork();\r
 667 \r
 668         LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");\r
 669         LogFile::Instance()->WriteNewLine();\r
 670 }\r
 671 \r
 672 void ShutdownNetwork()\r
 673 {\r
 674 #ifdef _WIN32\r
 675         WSACleanup();\r
 676 #endif\r
 677 }\r
 678 \r
 679 void SigHandler(int signum)\r
 680 {\r
 681         Shutdown();\r
 682         exit(0);\r
 683 }\r
 684 \r
 685 /*\r
 686 void ShutdownThreads(std::vector<PThread::Thread *> &threads)\r
 687 {\r
 688         std::vector<PThread::Thread *>::iterator i;\r
 689         for(i=threads.begin(); i!=threads.end(); i++)\r
 690         {\r
 691                 (*i)->Cancel();\r
 692         }\r
 693 \r
 694         for(i=threads.begin(); i!=threads.end(); i++)\r
 695         {\r
 696                 LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_DEBUG,"ShutdownThreads waiting for thread to exit.");\r
 697                 //(*i)->wait();\r
 698                 (*i)->Join();\r
 699                 delete (*i);\r
 700         }\r
 701 \r
 702         threads.clear();\r
 703 \r
 704 }\r
 705 \r
 706 void StartThreads(std::vector<PThread::Thread *> &threads)\r
 707 {\r
 708         std::string startfreenet;\r
 709         std::string startnntp;\r
 710         std::string starthttp;\r
 711 \r
 712         if(Option::Instance()->Get("StartFreenetUpdater",startfreenet)==false)\r
 713         {\r
 714                 startfreenet="true";\r
 715                 Option::Instance()->Set("StartFreenetUpdater","true");\r
 716         }\r
 717 \r
 718         if(Option::Instance()->Get("StartNNTP",startnntp)==false)\r
 719         {\r
 720                 startnntp="true";\r
 721                 Option::Instance()->Set("StartNNTP","true");\r
 722         }\r
 723 \r
 724         if(Option::Instance()->Get("StartHTTP",starthttp)==false)\r
 725         {\r
 726                 starthttp="true";\r
 727                 Option::Instance()->Set("StartHTTP","true");\r
 728         }\r
 729 \r
 730         if(startfreenet=="true")\r
 731         {\r
 732                 PThread::Thread *t=new PThread::Thread(new FreenetMasterThread());\r
 733                 threads.push_back(t);\r
 734         }\r
 735 \r
 736         if(startnntp=="true")\r
 737         {\r
 738                 PThread::Thread *t=new PThread::Thread(new NNTPListener());\r
 739                 threads.push_back(t);\r
 740         }\r
 741 \r
 742         if(starthttp=="true")\r
 743         {\r
 744                 PThread::Thread *t=new PThread::Thread(new HTTPThread());\r
 745                 threads.push_back(t);\r
 746         }\r
 747 \r
 748 }\r
 749 */\r