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 volatile bool wantshutdown=false;\r
  21 \r
  22 std::string CreateShortIdentityName(const std::string &name, const std::string &publickey)\r
  23 {\r
  24         std::string result="";\r
  25         std::vector<std::string> keyparts;\r
  26 \r
  27         StringFunctions::SplitMultiple(publickey,"@,",keyparts);\r
  28 \r
  29         result+=name;\r
  30         if(keyparts.size()>1 && keyparts[1].size()>8)\r
  31         {\r
  32                 result+="@"+keyparts[1].substr(0,4)+"...";\r
  33         }\r
  34 \r
  35         return result;\r
  36 }\r
  37 \r
  38 void SetupDB()\r
  39 {\r
  40 \r
  41         DateTime date;\r
  42         std::string tempval="";\r
  43         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  44 \r
  45         db->Open("fms.db3");\r
  46         db->SetBusyTimeout(10000);              // set timeout to 10 seconds\r
  47 \r
  48         tempval="";\r
  49         Option::Instance()->Get("VacuumOnStartup",tempval);\r
  50         if(tempval=="true")\r
  51         {\r
  52                 db->Execute("VACUUM;");\r
  53         }\r
  54 \r
  55         db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
  56                                 Major                           INTEGER,\\r
  57                                 Minor                           INTEGER\\r
  58                                 );");\r
  59 \r
  60         SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");\r
  61         st.Step();\r
  62         if(st.RowReturned())\r
  63         {\r
  64                 int major;\r
  65                 int minor;\r
  66                 st.ResultInt(0,major);\r
  67                 st.ResultInt(1,minor);\r
  68                 st.Finalize();\r
  69                 if(major==1 && minor==0)\r
  70                 {\r
  71                         ConvertDB0100To0101();\r
  72                         major=1;\r
  73                         minor=1;\r
  74                 }\r
  75                 if(major==1 && (minor==1 || minor==2))\r
  76                 {\r
  77                         ConvertDB0101To0103();\r
  78                         major=1;\r
  79                         minor=3;\r
  80                 }\r
  81                 if(major==1 && minor==3)\r
  82                 {\r
  83                         ConvertDB0103To0104();\r
  84                         major=1;\r
  85                         minor=4;\r
  86                 }\r
  87                 if(major==1 && minor==4)\r
  88                 {\r
  89                         ConvertDB0104To0105();\r
  90                         major=1;\r
  91                         minor=5;\r
  92                 }\r
  93                 if(major==1 && minor==5)\r
  94                 {\r
  95                         ConvertDB0105To0106();\r
  96                         major=1;\r
  97                         minor=6;\r
  98                 }\r
  99                 if(major==1 && minor==6)\r
 100                 {\r
 101                         ConvertDB0106To0107();\r
 102                         major=1;\r
 103                         minor=7;\r
 104                 }\r
 105                 if(major==1 && minor==7)\r
 106                 {\r
 107                         ConvertDB0107To0108();\r
 108                         major=1;\r
 109                         minor=8;\r
 110                 }\r
 111                 if(major==1 && minor==8)\r
 112                 {\r
 113                         ConvertDB0108To0109();\r
 114                         major=1;\r
 115                         minor=9;\r
 116                 }\r
 117                 if(major==1 && minor==9)\r
 118                 {\r
 119                         ConvertDB0109To0110();\r
 120                         major=1;\r
 121                         minor=10;\r
 122                 }\r
 123         }\r
 124         else\r
 125         {\r
 126                 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,10);");\r
 127         }\r
 128 \r
 129         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=10;");\r
 130 \r
 131         db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
 132                                 Option                          TEXT UNIQUE,\\r
 133                                 OptionValue                     TEXT NOT NULL,\\r
 134                                 OptionDescription       TEXT\\r
 135                                 );");\r
 136 \r
 137         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 138                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 139                                 Name                                    TEXT,\\r
 140                                 PublicKey                               TEXT UNIQUE,\\r
 141                                 PrivateKey                              TEXT UNIQUE,\\r
 142                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 143                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 144                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 145                                 PublishFreesite                 BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
 146                                 FreesiteEdition                 INTEGER,\\r
 147                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 148                                 LastInsertedIdentity    DATETIME,\\r
 149                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 150                                 LastInsertedPuzzle              DATETIME,\\r
 151                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 152                                 LastInsertedTrustList   DATETIME,\\r
 153                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
 154                                 LastInsertedBoardList   DATETIME,\\r
 155                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
 156                                 LastInsertedMessageList DATETIME,\\r
 157                                 LastInsertedFreesite    DATETIME,\\r
 158                                 DateCreated                             DATETIME\\r
 159                                 );");\r
 160 \r
 161         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r
 162                                 LocalIdentityID         INTEGER,\\r
 163                                 Day                                     DATE,\\r
 164                                 InsertIndex                     INTEGER\\r
 165                                 );");\r
 166 \r
 167         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\\r
 168                                 LocalIdentityID         INTEGER,\\r
 169                                 Day                                     DATE,\\r
 170                                 InsertIndex                     INTEGER\\r
 171                                 );");\r
 172 \r
 173         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\\r
 174                                 IdentityID                      INTEGER,\\r
 175                                 Day                                     DATE,\\r
 176                                 RequestIndex            INTEGER,\\r
 177                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 178                                 );");\r
 179 \r
 180         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
 181                                 UUID                            TEXT UNIQUE,\\r
 182                                 LocalIdentityID         INTEGER,\\r
 183                                 Day                                     DATE,\\r
 184                                 InsertIndex                     INTEGER,\\r
 185                                 Type                            TEXT,\\r
 186                                 MimeType                        TEXT,\\r
 187                                 PuzzleData                      TEXT,\\r
 188                                 PuzzleSolution          TEXT,\\r
 189                                 FoundSolution           BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\\r
 190                                 );");\r
 191 \r
 192         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 193                                 IdentityID                              INTEGER PRIMARY KEY,\\r
 194                                 PublicKey                               TEXT UNIQUE,\\r
 195                                 Name                                    TEXT,\\r
 196                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 197                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 198                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 199                                 FreesiteEdition                 INTEGER,\\r
 200                                 DateAdded                               DATETIME,\\r
 201                                 LastSeen                                DATETIME,\\r
 202                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 203                                 PeerMessageTrust                INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 204                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 205                                 PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 206                                 AddedMethod                             TEXT,\\r
 207                                 MessageTrustComment             TEXT,\\r
 208                                 TrustListTrustComment   TEXT,\\r
 209                                 Hidden                                  BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\\r
 210                                 );");\r
 211 \r
 212         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
 213                                 IdentityID                      INTEGER,\\r
 214                                 Day                                     DATE,\\r
 215                                 RequestIndex            INTEGER,\\r
 216                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 217                                 );");\r
 218 \r
 219         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\\r
 220                                 IdentityID                      INTEGER,\\r
 221                                 Day                                     DATE,\\r
 222                                 RequestIndex            INTEGER,\\r
 223                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 224                                 UUID                            TEXT UNIQUE,\\r
 225                                 Type                            TEXT,\\r
 226                                 MimeType                        TEXT,\\r
 227                                 PuzzleData                      TEXT\\r
 228                                 );");\r
 229 \r
 230         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\\r
 231                                 LocalIdentityID         INTEGER,\\r
 232                                 Day                                     DATE,\\r
 233                                 UUID                            TEXT UNIQUE,\\r
 234                                 Solution                        TEXT,\\r
 235                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 236                                 );");\r
 237 \r
 238         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\\r
 239                                 LocalIdentityID                 INTEGER,\\r
 240                                 IdentityID                              INTEGER,\\r
 241                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 242                                 MessageTrustComment             TEXT,\\r
 243                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 244                                 TrustListTrustComment   TEXT\\r
 245                                 );");\r
 246 \r
 247         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");\r
 248 \r
 249         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \\r
 250                                 FOR EACH ROW \\r
 251                                 BEGIN \\r
 252                                         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
 253                                 END;");\r
 254 \r
 255         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \\r
 256                                 FOR EACH ROW \\r
 257                                 BEGIN \\r
 258                                         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
 259                                 END;");\r
 260 \r
 261         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \\r
 262                                 FOR EACH ROW \\r
 263                                 BEGIN \\r
 264                                         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
 265                                 END;");\r
 266 \r
 267         db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
 268                                 IdentityID                              INTEGER,\\r
 269                                 TargetIdentityID                INTEGER,\\r
 270                                 MessageTrust                    INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\\r
 271                                 TrustListTrust                  INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\\r
 272                                 MessageTrustComment             TEXT,\\r
 273                                 TrustListTrustComment   TEXT\\r
 274                                 );");\r
 275 \r
 276         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");\r
 277         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");\r
 278 \r
 279         db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\\r
 280                                 BoardID                                 INTEGER PRIMARY KEY,\\r
 281                                 BoardName                               TEXT UNIQUE,\\r
 282                                 BoardDescription                TEXT,\\r
 283                                 DateAdded                               DATETIME,\\r
 284                                 SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\\r
 285                                 AddedMethod                             TEXT\\r
 286                                 );");\r
 287 \r
 288         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");\r
 289         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initialt Board');");\r
 290         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");\r
 291         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");\r
 292 \r
 293         db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
 294                                 MessageID                       INTEGER PRIMARY KEY,\\r
 295                                 IdentityID                      INTEGER,\\r
 296                                 FromName                        TEXT,\\r
 297                                 MessageDate                     DATE,\\r
 298                                 MessageTime                     TIME,\\r
 299                                 Subject                         TEXT,\\r
 300                                 MessageUUID                     TEXT UNIQUE,\\r
 301                                 ReplyBoardID            INTEGER,\\r
 302                                 Body                            TEXT,\\r
 303                                 MessageIndex            INTEGER\\r
 304                                 );");\r
 305 \r
 306         db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");\r
 307 \r
 308         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
 309                                 MessageID                       INTEGER,\\r
 310                                 ReplyToMessageUUID      TEXT,\\r
 311                                 ReplyOrder                      INTEGER\\r
 312                                 );");\r
 313 \r
 314         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\r
 315 \r
 316         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
 317                                 MessageID                       INTEGER,\\r
 318                                 BoardID                         INTEGER\\r
 319                                 );");\r
 320 \r
 321         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");\r
 322         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");\r
 323 \r
 324         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\\r
 325                                 IdentityID                      INTEGER,\\r
 326                                 Day                                     DATE,\\r
 327                                 RequestIndex            INTEGER,\\r
 328                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 329                                 );");\r
 330 \r
 331         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\\r
 332                                 IdentityID                      INTEGER,\\r
 333                                 Day                                     DATE,\\r
 334                                 RequestIndex            INTEGER,\\r
 335                                 FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
 336                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 337                                 );");\r
 338 \r
 339         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 340 \r
 341         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\\r
 342                                 LocalIdentityID         INTEGER,\\r
 343                                 Day                                     DATE,\\r
 344                                 InsertIndex                     INTEGER,\\r
 345                                 MessageUUID                     TEXT UNIQUE,\\r
 346                                 MessageXML                      TEXT,\\r
 347                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 348                                 );");\r
 349 \r
 350         db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
 351                                 FileInsertID            INTEGER PRIMARY KEY,\\r
 352                                 MessageUUID                     TEXT,\\r
 353                                 FileName                        TEXT,\\r
 354                                 Key                                     TEXT,\\r
 355                                 Size                            INTEGER,\\r
 356                                 MimeType                        TEXT,\\r
 357                                 Data                            BLOB\\r
 358                                 );");\r
 359 \r
 360         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
 361                                 LocalIdentityID         INTEGER,\\r
 362                                 Day                                     DATE,\\r
 363                                 InsertIndex                     INTEGER,\\r
 364                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 365                                 );");\r
 366 \r
 367         db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\\r
 368                                 BoardID                                         INTEGER UNIQUE,\\r
 369                                 ModifyLocalMessageTrust         INTEGER,\\r
 370                                 ModifyLocalTrustListTrust       INTEGER\\r
 371                                 );");\r
 372 \r
 373         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\\r
 374                                 LocalIdentityID         INTEGER,\\r
 375                                 Day                                     DATE,\\r
 376                                 InsertIndex                     INTEGER,\\r
 377                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 378                                 );");\r
 379 \r
 380         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\\r
 381                                 IdentityID                      INTEGER,\\r
 382                                 Day                                     DATE,\\r
 383                                 RequestIndex            INTEGER,\\r
 384                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 385                                 );");   \r
 386 \r
 387         // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed\r
 388         db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\\r
 389                                 LocalIdentityID         INTEGER,\\r
 390                                 Date                            DATETIME\\r
 391                                 );");\r
 392 \r
 393         // low / high / message count for each board\r
 394         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 395                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 396                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 397                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 398                                 GROUP BY tblBoard.BoardID;");\r
 399 \r
 400         // calculates peer trust\r
 401         // 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
 402         // need the +1 so that when the values are 0 the result is not 0\r
 403         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 404         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 405                                 SELECT TargetIdentityID, \\r
 406                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 407                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 408                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 409                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 410                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 411                                 GROUP BY TargetIdentityID;");\r
 412 \r
 413         /*\r
 414                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 415                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 416         */\r
 417         // drop existing triggers\r
 418         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 419         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 420         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 421         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 422 /*\r
 423         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 424         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 425                                 FOR EACH ROW \\r
 426                                 BEGIN \\r
 427                                         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
 428                                 END;");\r
 429 \r
 430         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 431         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 432                                 FOR EACH ROW \\r
 433                                 BEGIN \\r
 434                                         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
 435                                 END;");\r
 436 \r
 437         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 438         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 439                                 FOR EACH ROW \\r
 440                                 BEGIN \\r
 441                                         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
 442                                         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
 443                                 END;");\r
 444 \r
 445         // 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
 446         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 447                                 FOR EACH ROW \\r
 448                                 BEGIN \\r
 449                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 450                                 END;");\r
 451 */\r
 452 \r
 453         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 454                                 FOR EACH ROW \\r
 455                                 BEGIN \\r
 456                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 457                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 458                                 END;");\r
 459 \r
 460         db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
 461         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 462                                 FOR EACH ROW \\r
 463                                 BEGIN \\r
 464                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 465                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 466                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 467                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 468                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 469                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 470                                         DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
 471                                 END;");\r
 472 \r
 473         db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
 474         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 475                                 FOR EACH ROW \\r
 476                                 BEGIN \\r
 477                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 478                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 479                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 480                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 481                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 482                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 483                                         DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
 484                                 END;");\r
 485 \r
 486         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 487                                 FOR EACH ROW \\r
 488                                 BEGIN \\r
 489                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 490                                 END;");\r
 491 \r
 492         // delete introduction puzzles that were half-way inserted\r
 493         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 494 \r
 495         // delete stale introduction puzzles (2 or more days old)\r
 496         date.SetToGMTime();\r
 497         date.Add(0,0,0,-2);\r
 498         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 499         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 500 \r
 501         date.SetToGMTime();\r
 502         // insert SomeDude's public key\r
 503         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
 504         // insert Shadow Panther's public key\r
 505         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
 506         // insert garfield's public key\r
 507         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
 508         // insert alek's public key\r
 509         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@lTjeI6V0lQsktXqaqJ6Iwk4TdsHduQI54rdUpHfhGbg,0oTYfrxxx8OmdU1~60gqpf3781qzEicM4Sz97mJsBM4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 510         // insert Luke771's public key\r
 511         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@mdXK~ZVlfTZhF1SLBrvZ--i0vOsOpa~w9wv~~psQ-04,gXonsXKc7aexKSO8Gt8Fwre4Qgmmbt2WueO7VzxNKkk,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 512         // insert falafel's public key\r
 513         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 514         // insert cptn_insano's public key\r
 515         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@bloE1LJ~qzSYUkU2nt7sB9kq060D4HTQC66pk5Q8NpA,DOOASUnp0kj6tOdhZJ-h5Tk7Ka50FSrUgsH7tCG1usU,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 516         // insert Flink's public key\r
 517         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@q2TtkNBOuuniyJ56~8NSopCs3ttwe5KlB31ugZtWmXA,6~PzIupS8YK7L6oFNpXGKJmHT2kBMDfwTg73nHdNur8,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 518         // insert Kane's public key\r
 519         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@Ofm~yZivDJ5Z2fSzZbMiLEUUQaIc0KHRdZMBTaPLO6I,WLm4s4hNbOOurJ6ijfOq4odz7-dN7uTUvYxJRwWnlMI,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 520         // inserts boardstat's public key\r
 521         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@aYWBb6zo2AM13XCNhsmmRKMANEx6PG~C15CWjdZziKA,X1pAG4EIqR1gAiyGFVZ1iiw-uTlh460~rFACJ7ZHQXk,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 522 \r
 523         // TODO remove sometime after 0.1.17\r
 524         FixCapitalBoardNames();\r
 525 \r
 526         // run analyze - may speed up some queries\r
 527         db->Execute("ANALYZE;");\r
 528 \r
 529 }\r
 530 \r
 531 void ConvertDB0100To0101()\r
 532 {\r
 533         // added unique constraint to public and private key\r
 534         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 535         db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
 536         db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
 537         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 538                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 539                                 Name                                    TEXT,\\r
 540                                 PublicKey                               TEXT UNIQUE,\\r
 541                                 PrivateKey                              TEXT UNIQUE,\\r
 542                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 543                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 544                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 545                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 546                                 LastInsertedIdentity    DATETIME,\\r
 547                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 548                                 LastInsertedPuzzle              DATETIME,\\r
 549                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 550                                 LastInsertedTrustList   DATETIME,\\r
 551                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
 552                                 LastInsertedBoardList   DATETIME,\\r
 553                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
 554                                 LastInsertedMessageList DATETIME\\r
 555                                 );");\r
 556         db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");\r
 557         db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
 558         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");\r
 559 }\r
 560 \r
 561 void ConvertDB0101To0103()\r
 562 {\r
 563         // remove default 50 from trust fields and set default to NULL\r
 564         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 565         db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
 566         db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
 567         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 568                                 IdentityID                      INTEGER PRIMARY KEY,\\r
 569                                 PublicKey                       TEXT UNIQUE,\\r
 570                                 Name                            TEXT,\\r
 571                                 SingleUse                       BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 572                                 PublishTrustList        BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 573                                 PublishBoardList        BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 574                                 DateAdded                       DATETIME,\\r
 575                                 LastSeen                        DATETIME,\\r
 576                                 LocalMessageTrust       INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 577                                 PeerMessageTrust        INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 578                                 LocalTrustListTrust     INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 579                                 PeerTrustListTrust      INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\\r
 580                                 );");\r
 581         db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");\r
 582         db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
 583 \r
 584         // add SaveReceivedMessages field to tblBoard\r
 585         db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages       BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");\r
 586 \r
 587         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
 588 }\r
 589 \r
 590 void ConvertDB0103To0104()\r
 591 {\r
 592         // add MessageIndex to tblMessage\r
 593         DateTime date;\r
 594         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 595         db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex     INTEGER;");\r
 596         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 597         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");\r
 598         date.SetToGMTime();\r
 599         db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");\r
 600         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
 601 }\r
 602 \r
 603 void ConvertDB0104To0105()\r
 604 {\r
 605         // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity\r
 606         // add MessageTrustComment,TrustListTrustComment to tblPeerTrust\r
 607         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 608         db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");\r
 609         db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");\r
 610         db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");\r
 611         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");\r
 612         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");\r
 613         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");\r
 614 }\r
 615 \r
 616 void ConvertDB0105To0106()\r
 617 {\r
 618         // add Publish Freesite\r
 619         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 620         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");\r
 621         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");\r
 622         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");\r
 623 }\r
 624 \r
 625 void ConvertDB0106To0107()\r
 626 {\r
 627         // add AddedMethod to tblBoard\r
 628         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 629         db->Execute("ALTER TABLE tblBoard ADD COLUMN AddedMethod TEXT;");\r
 630         db->Execute("ALTER TABLE tblIdentity ADD COLUMN Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false';");\r
 631         db->Execute("UPDATE tblIdentity SET Hidden='false' WHERE Hidden IS NULL;");\r
 632         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");\r
 633 }\r
 634 \r
 635 void ConvertDB0107To0108()\r
 636 {\r
 637         // add FreesiteEdition to tblLocalIdentity and tblIdentity\r
 638         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 639         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
 640         db->Execute("ALTER TABLE tblIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
 641         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=8;");\r
 642 }\r
 643 \r
 644 void ConvertDB0108To0109()\r
 645 {\r
 646         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 647         db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
 648                         FileInsertID            INTEGER PRIMARY KEY,\\r
 649                         MessageUUID                     TEXT,\\r
 650                         FileName                        TEXT,\\r
 651                         Key                                     TEXT,\\r
 652                         Size                            INTEGER,\\r
 653                         Data                            BLOB\\r
 654                         );");\r
 655         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");\r
 656 }\r
 657 \r
 658 void ConvertDB0109To0110()\r
 659 {\r
 660         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 661         db->Execute("ALTER TABLE tblFileInserts ADD COLUMN MimeType TEXT;");\r
 662         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=10;");\r
 663 }\r
 664 \r
 665 void SetupDefaultOptions()\r
 666 {\r
 667         // 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
 668 \r
 669         std::ostringstream tempstr;     // must set tempstr to "" between db inserts\r
 670         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 671         SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");\r
 672 \r
 673         // LogLevel\r
 674         tempstr.str("");\r
 675         tempstr << LogFile::LOGLEVEL_DEBUG;\r
 676         st.Bind(0,"LogLevel");\r
 677         st.Bind(1,tempstr.str());\r
 678         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
 679         st.Step();\r
 680         st.Reset();\r
 681 \r
 682         // NNTPListenPort\r
 683         st.Bind(0,"NNTPListenPort");\r
 684         st.Bind(1,"1119");\r
 685         st.Bind(2,"The port that the NNTP service will listen for incoming connections.");\r
 686         st.Step();\r
 687         st.Reset();\r
 688 \r
 689         // NNTPBindAddresses\r
 690         st.Bind(0,"NNTPBindAddresses");\r
 691         st.Bind(1,"localhost");\r
 692         st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");\r
 693         st.Step();\r
 694         st.Reset();\r
 695 \r
 696         st.Bind(0,"NNTPAllowPost");\r
 697         st.Bind(1,"true");\r
 698         st.Bind(2,"Allow posting messages from NNTP.  Setting to false will make the newsgroups read only.");\r
 699         st.Step();\r
 700         st.Reset();\r
 701 \r
 702         // StartNNTP\r
 703         st.Bind(0,"StartNNTP");\r
 704         st.Bind(1,"true");\r
 705         st.Bind(2,"Start NNTP server.");\r
 706         st.Step();\r
 707         st.Reset();\r
 708 \r
 709         st.Bind(0,"StartHTTP");\r
 710         st.Bind(1,"true");\r
 711         st.Bind(2,"Start HTTP server.  WARNING: If you turn this off, you won't be able to access the administration pages.");\r
 712         st.Step();\r
 713         st.Reset();\r
 714 \r
 715         st.Bind(0,"HTTPListenPort");\r
 716         st.Bind(1,"8080");\r
 717         st.Bind(2,"Port HTTP server will listen on.");\r
 718         st.Step();\r
 719         st.Reset();\r
 720 \r
 721         st.Bind(0,"HTTPAccessControl");\r
 722         st.Bind(1,"-0.0.0.0/0,+127.0.0.1");\r
 723         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 a host.");\r
 724         st.Step();\r
 725         st.Reset();\r
 726 \r
 727         // StartFreenetUpdater\r
 728         st.Bind(0,"StartFreenetUpdater");\r
 729         st.Bind(1,"true");\r
 730         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
 731         st.Step();\r
 732         st.Reset();\r
 733 \r
 734         // FCPHost\r
 735         st.Bind(0,"FCPHost");\r
 736         st.Bind(1,"127.0.0.1");\r
 737         st.Bind(2,"Host name or address of Freenet node.");\r
 738         st.Step();\r
 739         st.Reset();\r
 740 \r
 741         // FCPPort\r
 742         st.Bind(0,"FCPPort");\r
 743         st.Bind(1,"9481");\r
 744         st.Bind(2,"The port that Freenet is listening for FCP connections on.");\r
 745         st.Step();\r
 746         st.Reset();\r
 747 \r
 748         st.Bind(0,"FProxyPort");\r
 749         st.Bind(1,"8888");\r
 750         st.Bind(2,"The port that Freenet is listening for http connections on.");\r
 751         st.Step();\r
 752         st.Reset();\r
 753 \r
 754         st.Bind(0,"MessageBase");\r
 755         st.Bind(1,"fms");\r
 756         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
 757         st.Step();\r
 758         st.Reset();\r
 759 \r
 760         st.Bind(0,"MaxIdentityRequests");\r
 761         st.Bind(1,"5");\r
 762         st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");\r
 763         st.Step();\r
 764         st.Reset();\r
 765 \r
 766         st.Bind(0,"MaxIdentityIntroductionRequests");\r
 767         st.Bind(1,"5");\r
 768         st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files.  Each identity may have multiple requests pending.");\r
 769         st.Step();\r
 770         st.Reset();\r
 771 \r
 772         st.Bind(0,"MaxIntroductionPuzzleRequests");\r
 773         st.Bind(1,"5");\r
 774         st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");\r
 775         st.Step();\r
 776         st.Reset();\r
 777 \r
 778         st.Bind(0,"MaxTrustListRequests");\r
 779         st.Bind(1,"5");\r
 780         st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");\r
 781         st.Step();\r
 782         st.Reset();\r
 783 \r
 784         st.Bind(0,"MaxMessageListRequests");\r
 785         st.Bind(1,"5");\r
 786         st.Bind(2,"Maximum number of concurrent requests for new Message Lists");\r
 787         st.Step();\r
 788         st.Reset();\r
 789 \r
 790         st.Bind(0,"MaxMessageRequests");\r
 791         st.Bind(1,"20");\r
 792         st.Bind(2,"Maximum number of concurrent requests for new Messages");\r
 793         st.Step();\r
 794         st.Reset();\r
 795 \r
 796         st.Bind(0,"MinLocalMessageTrust");\r
 797         st.Bind(1,"50");\r
 798         st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");\r
 799         st.Step();\r
 800         st.Reset();\r
 801 \r
 802         st.Bind(0,"MinPeerMessageTrust");\r
 803         st.Bind(1,"30");\r
 804         st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");\r
 805         st.Step();\r
 806         st.Reset();\r
 807 \r
 808         st.Bind(0,"MinLocalTrustListTrust");\r
 809         st.Bind(1,"50");\r
 810         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
 811         st.Step();\r
 812         st.Reset();\r
 813 \r
 814         st.Bind(0,"MinPeerTrustListTrust");\r
 815         st.Bind(1,"30");\r
 816         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
 817         st.Step();\r
 818         st.Reset();\r
 819 \r
 820         st.Bind(0,"MessageDownloadMaxDaysBackward");\r
 821         st.Bind(1,"5");\r
 822         st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");\r
 823         st.Step();\r
 824         st.Reset();\r
 825 \r
 826         st.Bind(0,"MessageListDaysBackward");\r
 827         st.Bind(1,"5");\r
 828         st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");\r
 829         st.Step();\r
 830         st.Reset();\r
 831 \r
 832         st.Bind(0,"MaxPeerMessagesPerDay");\r
 833         st.Bind(1,"200");\r
 834         st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");\r
 835         st.Step();\r
 836         st.Reset();\r
 837 \r
 838         st.Bind(0,"MaxBoardListRequests");\r
 839         st.Bind(1,"5");\r
 840         st.Bind(2,"The maximum number of concurrent requests for new Board Lists.  Set to 0 to disable.");\r
 841         st.Step();\r
 842         st.Reset();\r
 843 \r
 844         st.Bind(0,"MaxBoardsPerMessage");\r
 845         st.Bind(1,"8");\r
 846         st.Bind(2,"The maximum number of boards a received message may be sent to.  Boards over this limit will be ignored.");\r
 847         st.Step();\r
 848         st.Reset();\r
 849 \r
 850         st.Bind(0,"SaveMessagesFromNewBoards");\r
 851         st.Bind(1,"true");\r
 852         st.Bind(2,"Set to true to automatically save messages posted to new boards.  Set to false to ignore messages to new boards.");\r
 853         st.Step();\r
 854         st.Reset();\r
 855 \r
 856         st.Bind(0,"ChangeMessageTrustOnReply");\r
 857         st.Bind(1,"0");\r
 858         st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");\r
 859         st.Step();\r
 860         st.Reset();\r
 861 \r
 862         st.Bind(0,"AddNewPostFromIdentities");\r
 863         st.Bind(1,"false");\r
 864         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
 865         st.Step();\r
 866         st.Reset();\r
 867 \r
 868         st.Bind(0,"DeleteMessagesOlderThan");\r
 869         st.Bind(1,"180");\r
 870         st.Bind(2,"Automatically delete messages older than this many days.");\r
 871         st.Step();\r
 872         st.Reset();\r
 873 \r
 874         st.Bind(0,"VacuumOnStartup");\r
 875         st.Bind(1,"false");\r
 876         st.Bind(2,"VACUUM the database every time FMS starts.  This will defragment the free space in the database and create a smaller database file.  Vacuuming the database can be CPU and disk intensive.");\r
 877         st.Step();\r
 878         st.Reset();\r
 879 \r
 880 }\r
 881 \r
 882 void SetupLogFile()\r
 883 {\r
 884         DateTime date;\r
 885         std::string configval;\r
 886         int loglevel;\r
 887 \r
 888         date.SetToGMTime();\r
 889 \r
 890         LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");\r
 891         LogFile::Instance()->OpenFile();\r
 892         LogFile::Instance()->SetWriteNewLine(true);\r
 893         LogFile::Instance()->SetWriteDate(true);\r
 894         LogFile::Instance()->SetWriteLogLevel(true);\r
 895 \r
 896         if(Option::Instance()->Get("LogLevel",configval)==false)\r
 897         {\r
 898                 configval="4";\r
 899                 Option::Instance()->Set("LogLevel",configval);\r
 900         }\r
 901         if(StringFunctions::Convert(configval,loglevel)==false)\r
 902         {\r
 903                 loglevel=LogFile::LOGLEVEL_DEBUG;\r
 904                 Option::Instance()->Set("LogLevel",loglevel);\r
 905         }\r
 906         LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);\r
 907 }\r
 908 \r
 909 void SetupNetwork()\r
 910 {\r
 911 #ifdef _WIN32\r
 912         WSAData wsadata;\r
 913         WSAStartup(MAKEWORD(2,2),&wsadata);\r
 914 #endif\r
 915 }\r
 916 \r
 917 void Shutdown()\r
 918 {\r
 919 \r
 920         LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_DEBUG,"FMS starting shutdown");\r
 921 \r
 922         ThreadController::Instance()->ShutdownThreads();\r
 923 \r
 924         ShutdownNetwork();\r
 925 \r
 926         LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");\r
 927         LogFile::Instance()->WriteNewLine();\r
 928 }\r
 929 \r
 930 void ShutdownNetwork()\r
 931 {\r
 932 #ifdef _WIN32\r
 933         WSACleanup();\r
 934 #endif\r
 935 }\r
 936 \r
 937 void SigHandler(int signum)\r
 938 {\r
 939         Shutdown();\r
 940         exit(0);\r
 941 }\r
 942 \r
 943 void FixCapitalBoardNames()\r
 944 {\r
 945         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 946 \r
 947         SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");\r
 948         SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");\r
 949         SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");\r
 950         SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");\r
 951         SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");\r
 952         SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");\r
 953 \r
 954         st.Step();\r
 955         while(st.RowReturned())\r
 956         {\r
 957                 int boardid=0;\r
 958                 int newboardid=0;\r
 959                 std::string name="";\r
 960                 std::string lowername="";\r
 961 \r
 962                 st.ResultInt(0,boardid);\r
 963                 st.ResultText(1,name);\r
 964 \r
 965                 lowername=name;\r
 966                 StringFunctions::LowerCase(lowername,lowername);\r
 967        \r
 968                 if(name!=lowername)\r
 969                 {\r
 970                         st2.Bind(0,lowername);\r
 971                         st2.Step();\r
 972 \r
 973                         if(st2.RowReturned())\r
 974                         {\r
 975                                 st2.ResultInt(0,newboardid);\r
 976 \r
 977                                 upd2.Bind(0,newboardid);\r
 978                                 upd2.Bind(1,boardid);\r
 979                                 upd2.Step();\r
 980                                 upd2.Reset();\r
 981 \r
 982                                 upd3.Bind(0,newboardid);\r
 983                                 upd3.Bind(1,boardid);\r
 984                                 upd3.Step();\r
 985                                 upd3.Reset();\r
 986 \r
 987                                 del.Bind(0,boardid);\r
 988                                 del.Step();\r
 989                                 del.Reset();\r
 990                         }\r
 991                         else\r
 992                         {\r
 993                                 upd.Bind(0,lowername);\r
 994                                 upd.Bind(1,boardid);\r
 995                                 upd.Step();\r
 996                                 upd.Reset();\r
 997                         }\r
 998 \r
 999                         st2.Reset();\r
1000                 }\r
1001        \r
1002                 st.Step();\r
1003         }\r
1004 \r
1005 }\r