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