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