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         /*\r
 419                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 420                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 421         */\r
 422         // drop existing triggers\r
 423         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 424         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 425         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 426         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 427 /*\r
 428         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 429         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 430                                 FOR EACH ROW \\r
 431                                 BEGIN \\r
 432                                         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
 433                                 END;");\r
 434 \r
 435         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 436         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 437                                 FOR EACH ROW \\r
 438                                 BEGIN \\r
 439                                         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
 440                                 END;");\r
 441 \r
 442         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 443         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 444                                 FOR EACH ROW \\r
 445                                 BEGIN \\r
 446                                         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
 447                                         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
 448                                 END;");\r
 449 \r
 450         // 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
 451         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 452                                 FOR EACH ROW \\r
 453                                 BEGIN \\r
 454                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 455                                 END;");\r
 456 */\r
 457 \r
 458         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 459                                 FOR EACH ROW \\r
 460                                 BEGIN \\r
 461                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 462                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 463                                 END;");\r
 464 \r
 465         db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
 466         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 467                                 FOR EACH ROW \\r
 468                                 BEGIN \\r
 469                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 470                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 471                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 472                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 473                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 474                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 475                                         DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
 476                                 END;");\r
 477 \r
 478         db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
 479         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 480                                 FOR EACH ROW \\r
 481                                 BEGIN \\r
 482                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 483                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 484                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 485                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 486                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 487                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 488                                         DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
 489                                 END;");\r
 490 \r
 491         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 492                                 FOR EACH ROW \\r
 493                                 BEGIN \\r
 494                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 495                                 END;");\r
 496 \r
 497         // delete introduction puzzles that were half-way inserted\r
 498         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 499 \r
 500         // delete stale introduction puzzles (2 or more days old)\r
 501         date.SetToGMTime();\r
 502         date.Add(0,0,0,-2);\r
 503         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 504         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 505 \r
 506         date.SetToGMTime();\r
 507         // insert SomeDude's public key\r
 508         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
 509         // insert Shadow Panther's public key\r
 510         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
 511         // insert garfield's public key\r
 512         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
 513         // insert alek's public key\r
 514         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
 515         // insert Luke771's public key\r
 516         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
 517         // insert falafel's public key\r
 518         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
 519         // insert cptn_insano's public key\r
 520         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
 521         // insert Flink's public key\r
 522         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
 523         // insert Kane's public key\r
 524         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
 525         // inserts boardstat's public key\r
 526         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
 527 \r
 528         // TODO remove sometime after 0.1.17\r
 529         FixCapitalBoardNames();\r
 530 \r
 531         // run analyze - may speed up some queries\r
 532         db->Execute("ANALYZE;");\r
 533 \r
 534 }\r
 535 \r
 536 void ConvertDB0100To0101()\r
 537 {\r
 538         // added unique constraint to public and private key\r
 539         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 540         db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
 541         db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
 542         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 543                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 544                                 Name                                    TEXT,\\r
 545                                 PublicKey                               TEXT UNIQUE,\\r
 546                                 PrivateKey                              TEXT UNIQUE,\\r
 547                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 548                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 549                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 550                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 551                                 LastInsertedIdentity    DATETIME,\\r
 552                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 553                                 LastInsertedPuzzle              DATETIME,\\r
 554                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 555                                 LastInsertedTrustList   DATETIME,\\r
 556                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
 557                                 LastInsertedBoardList   DATETIME,\\r
 558                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
 559                                 LastInsertedMessageList DATETIME\\r
 560                                 );");\r
 561         db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");\r
 562         db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
 563         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");\r
 564 }\r
 565 \r
 566 void ConvertDB0101To0103()\r
 567 {\r
 568         // remove default 50 from trust fields and set default to NULL\r
 569         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 570         db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
 571         db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
 572         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 573                                 IdentityID                      INTEGER PRIMARY KEY,\\r
 574                                 PublicKey                       TEXT UNIQUE,\\r
 575                                 Name                            TEXT,\\r
 576                                 SingleUse                       BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 577                                 PublishTrustList        BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 578                                 PublishBoardList        BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 579                                 DateAdded                       DATETIME,\\r
 580                                 LastSeen                        DATETIME,\\r
 581                                 LocalMessageTrust       INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 582                                 PeerMessageTrust        INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 583                                 LocalTrustListTrust     INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 584                                 PeerTrustListTrust      INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\\r
 585                                 );");\r
 586         db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");\r
 587         db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
 588 \r
 589         // add SaveReceivedMessages field to tblBoard\r
 590         db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages       BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");\r
 591 \r
 592         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
 593 }\r
 594 \r
 595 void ConvertDB0103To0104()\r
 596 {\r
 597         // add MessageIndex to tblMessage\r
 598         DateTime date;\r
 599         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 600         db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex     INTEGER;");\r
 601         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 602         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");\r
 603         date.SetToGMTime();\r
 604         db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");\r
 605         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
 606 }\r
 607 \r
 608 void ConvertDB0104To0105()\r
 609 {\r
 610         // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity\r
 611         // add MessageTrustComment,TrustListTrustComment to tblPeerTrust\r
 612         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 613         db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");\r
 614         db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");\r
 615         db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");\r
 616         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");\r
 617         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");\r
 618         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");\r
 619 }\r
 620 \r
 621 void ConvertDB0105To0106()\r
 622 {\r
 623         // add Publish Freesite\r
 624         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 625         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");\r
 626         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");\r
 627         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");\r
 628 }\r
 629 \r
 630 void ConvertDB0106To0107()\r
 631 {\r
 632         // add AddedMethod to tblBoard\r
 633         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 634         db->Execute("ALTER TABLE tblBoard ADD COLUMN AddedMethod TEXT;");\r
 635         db->Execute("ALTER TABLE tblIdentity ADD COLUMN Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false';");\r
 636         db->Execute("UPDATE tblIdentity SET Hidden='false' WHERE Hidden IS NULL;");\r
 637         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");\r
 638 }\r
 639 \r
 640 void ConvertDB0107To0108()\r
 641 {\r
 642         // add FreesiteEdition to tblLocalIdentity and tblIdentity\r
 643         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 644         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
 645         db->Execute("ALTER TABLE tblIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
 646         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=8;");\r
 647 }\r
 648 \r
 649 void ConvertDB0108To0109()\r
 650 {\r
 651         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 652         db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
 653                         FileInsertID            INTEGER PRIMARY KEY,\\r
 654                         MessageUUID                     TEXT,\\r
 655                         FileName                        TEXT,\\r
 656                         Key                                     TEXT,\\r
 657                         Size                            INTEGER,\\r
 658                         Data                            BLOB\\r
 659                         );");\r
 660         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");\r
 661 }\r
 662 \r
 663 void ConvertDB0109To0110()\r
 664 {\r
 665         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 666         db->Execute("ALTER TABLE tblFileInserts ADD COLUMN MimeType TEXT;");\r
 667         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=10;");\r
 668 }\r
 669 \r
 670 void ConvertDB0110To0111()\r
 671 {\r
 672         /*\r
 673         Drop MessageTrustComment, TrustListTrustComment FROM tblIdentity\r
 674 \r
 675         Drop InsertingMessageList, InsertingBoardList FROM tblLocalIdentity\r
 676         Add MinMessageDelay, MaxMessageDelay to tblLocalIdentity Default 0\r
 677 \r
 678         Add SendDate to tblMessageInserts\r
 679         */\r
 680         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 681 \r
 682         db->Execute("ALTER TABLE tblMessageInserts ADD COLUMN SendDate DATETIME;");\r
 683 \r
 684         db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
 685         db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
 686         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 687                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 688                                 Name                                    TEXT,\\r
 689                                 PublicKey                               TEXT UNIQUE,\\r
 690                                 PrivateKey                              TEXT UNIQUE,\\r
 691                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 692                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 693                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 694                                 PublishFreesite                 BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
 695                                 FreesiteEdition                 INTEGER,\\r
 696                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 697                                 LastInsertedIdentity    DATETIME,\\r
 698                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 699                                 LastInsertedPuzzle              DATETIME,\\r
 700                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 701                                 LastInsertedTrustList   DATETIME,\\r
 702                                 LastInsertedBoardList   DATETIME,\\r
 703                                 LastInsertedMessageList DATETIME,\\r
 704                                 LastInsertedFreesite    DATETIME,\\r
 705                                 DateCreated                             DATETIME,\\r
 706                                 MinMessageDelay                 INTEGER DEFAULT 0,\\r
 707                                 MaxMessageDelay                 INTEGER DEFAULT 0\\r
 708                                 );");\r
 709         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
 710         db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
 711 \r
 712         db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
 713         db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
 714         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 715                                 IdentityID                              INTEGER PRIMARY KEY,\\r
 716                                 PublicKey                               TEXT UNIQUE,\\r
 717                                 Name                                    TEXT,\\r
 718                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 719                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 720                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 721                                 FreesiteEdition                 INTEGER,\\r
 722                                 DateAdded                               DATETIME,\\r
 723                                 LastSeen                                DATETIME,\\r
 724                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 725                                 PeerMessageTrust                INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 726                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 727                                 PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 728                                 AddedMethod                             TEXT,\\r
 729                                 Hidden                                  BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\\r
 730                                 );");\r
 731         db->Execute("INSERT INTO tblIdentity SELECT IdentityID,PublicKey,Name,SingleUse,PublishTrustList,PublishBoardList,FreesiteEdition,DateAdded,LastSeen,LocalMessageTrust,PeerMessageTrust,LocalTrustListTrust,PeerTrustListTrust,AddedMethod,Hidden FROM tblIdentityTemp;");\r
 732         db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
 733 \r
 734         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=11;");\r
 735 }\r
 736 \r
 737 void SetupDefaultOptions()\r
 738 {\r
 739         // 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
 740 \r
 741         std::ostringstream tempstr;     // must set tempstr to "" between db inserts\r
 742         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 743         SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");\r
 744 \r
 745         // LogLevel\r
 746         tempstr.str("");\r
 747         tempstr << LogFile::LOGLEVEL_DEBUG;\r
 748         st.Bind(0,"LogLevel");\r
 749         st.Bind(1,tempstr.str());\r
 750         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
 751         st.Step();\r
 752         st.Reset();\r
 753 \r
 754         // NNTPListenPort\r
 755         st.Bind(0,"NNTPListenPort");\r
 756         st.Bind(1,"1119");\r
 757         st.Bind(2,"The port that the NNTP service will listen for incoming connections.");\r
 758         st.Step();\r
 759         st.Reset();\r
 760 \r
 761         // NNTPBindAddresses\r
 762         st.Bind(0,"NNTPBindAddresses");\r
 763         st.Bind(1,"localhost");\r
 764         st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");\r
 765         st.Step();\r
 766         st.Reset();\r
 767 \r
 768         st.Bind(0,"NNTPAllowPost");\r
 769         st.Bind(1,"true");\r
 770         st.Bind(2,"Allow posting messages from NNTP.  Setting to false will make the newsgroups read only.");\r
 771         st.Step();\r
 772         st.Reset();\r
 773 \r
 774         // StartNNTP\r
 775         st.Bind(0,"StartNNTP");\r
 776         st.Bind(1,"true");\r
 777         st.Bind(2,"Start NNTP server.");\r
 778         st.Step();\r
 779         st.Reset();\r
 780 \r
 781         st.Bind(0,"StartHTTP");\r
 782         st.Bind(1,"true");\r
 783         st.Bind(2,"Start HTTP server.  WARNING: If you turn this off, you won't be able to access the administration pages.");\r
 784         st.Step();\r
 785         st.Reset();\r
 786 \r
 787         st.Bind(0,"HTTPListenPort");\r
 788         st.Bind(1,"8080");\r
 789         st.Bind(2,"Port HTTP server will listen on.");\r
 790         st.Step();\r
 791         st.Reset();\r
 792 \r
 793         st.Bind(0,"HTTPAccessControl");\r
 794         st.Bind(1,"-0.0.0.0/0,+127.0.0.1");\r
 795         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
 796         st.Step();\r
 797         st.Reset();\r
 798 \r
 799         // StartFreenetUpdater\r
 800         st.Bind(0,"StartFreenetUpdater");\r
 801         st.Bind(1,"true");\r
 802         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
 803         st.Step();\r
 804         st.Reset();\r
 805 \r
 806         // FCPHost\r
 807         st.Bind(0,"FCPHost");\r
 808         st.Bind(1,"127.0.0.1");\r
 809         st.Bind(2,"Host name or address of Freenet node.");\r
 810         st.Step();\r
 811         st.Reset();\r
 812 \r
 813         // FCPPort\r
 814         st.Bind(0,"FCPPort");\r
 815         st.Bind(1,"9481");\r
 816         st.Bind(2,"The port that Freenet is listening for FCP connections on.");\r
 817         st.Step();\r
 818         st.Reset();\r
 819 \r
 820         st.Bind(0,"FProxyPort");\r
 821         st.Bind(1,"8888");\r
 822         st.Bind(2,"The port that Freenet is listening for http connections on.");\r
 823         st.Step();\r
 824         st.Reset();\r
 825 \r
 826         st.Bind(0,"MessageBase");\r
 827         st.Bind(1,"fms");\r
 828         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
 829         st.Step();\r
 830         st.Reset();\r
 831 \r
 832         st.Bind(0,"MaxIdentityRequests");\r
 833         st.Bind(1,"5");\r
 834         st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");\r
 835         st.Step();\r
 836         st.Reset();\r
 837 \r
 838         st.Bind(0,"MaxIdentityIntroductionRequests");\r
 839         st.Bind(1,"5");\r
 840         st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files.  Each identity may have multiple requests pending.");\r
 841         st.Step();\r
 842         st.Reset();\r
 843 \r
 844         st.Bind(0,"MaxIntroductionPuzzleRequests");\r
 845         st.Bind(1,"5");\r
 846         st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");\r
 847         st.Step();\r
 848         st.Reset();\r
 849 \r
 850         st.Bind(0,"MaxTrustListRequests");\r
 851         st.Bind(1,"5");\r
 852         st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");\r
 853         st.Step();\r
 854         st.Reset();\r
 855 \r
 856         st.Bind(0,"MaxMessageListRequests");\r
 857         st.Bind(1,"5");\r
 858         st.Bind(2,"Maximum number of concurrent requests for new Message Lists");\r
 859         st.Step();\r
 860         st.Reset();\r
 861 \r
 862         st.Bind(0,"MaxMessageRequests");\r
 863         st.Bind(1,"20");\r
 864         st.Bind(2,"Maximum number of concurrent requests for new Messages");\r
 865         st.Step();\r
 866         st.Reset();\r
 867 \r
 868         st.Bind(0,"MinLocalMessageTrust");\r
 869         st.Bind(1,"50");\r
 870         st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");\r
 871         st.Step();\r
 872         st.Reset();\r
 873 \r
 874         st.Bind(0,"MinPeerMessageTrust");\r
 875         st.Bind(1,"30");\r
 876         st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");\r
 877         st.Step();\r
 878         st.Reset();\r
 879 \r
 880         st.Bind(0,"MinLocalTrustListTrust");\r
 881         st.Bind(1,"50");\r
 882         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
 883         st.Step();\r
 884         st.Reset();\r
 885 \r
 886         st.Bind(0,"MinPeerTrustListTrust");\r
 887         st.Bind(1,"30");\r
 888         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
 889         st.Step();\r
 890         st.Reset();\r
 891 \r
 892         st.Bind(0,"LocalTrustOverridesPeerTrust");\r
 893         st.Bind(1,"false");\r
 894         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
 895         st.Step();\r
 896         st.Reset();\r
 897 \r
 898         st.Bind(0,"MessageDownloadMaxDaysBackward");\r
 899         st.Bind(1,"5");\r
 900         st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");\r
 901         st.Step();\r
 902         st.Reset();\r
 903 \r
 904         st.Bind(0,"MessageListDaysBackward");\r
 905         st.Bind(1,"5");\r
 906         st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");\r
 907         st.Step();\r
 908         st.Reset();\r
 909 \r
 910         st.Bind(0,"MaxPeerMessagesPerDay");\r
 911         st.Bind(1,"200");\r
 912         st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");\r
 913         st.Step();\r
 914         st.Reset();\r
 915 \r
 916         st.Bind(0,"MaxBoardListRequests");\r
 917         st.Bind(1,"5");\r
 918         st.Bind(2,"The maximum number of concurrent requests for new Board Lists.  Set to 0 to disable.");\r
 919         st.Step();\r
 920         st.Reset();\r
 921 \r
 922         st.Bind(0,"MaxBoardsPerMessage");\r
 923         st.Bind(1,"8");\r
 924         st.Bind(2,"The maximum number of boards a received message may be sent to.  Boards over this limit will be ignored.");\r
 925         st.Step();\r
 926         st.Reset();\r
 927 \r
 928         st.Bind(0,"SaveMessagesFromNewBoards");\r
 929         st.Bind(1,"true");\r
 930         st.Bind(2,"Set to true to automatically save messages posted to new boards.  Set to false to ignore messages to new boards.");\r
 931         st.Step();\r
 932         st.Reset();\r
 933 \r
 934         st.Bind(0,"ChangeMessageTrustOnReply");\r
 935         st.Bind(1,"0");\r
 936         st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");\r
 937         st.Step();\r
 938         st.Reset();\r
 939 \r
 940         st.Bind(0,"AddNewPostFromIdentities");\r
 941         st.Bind(1,"false");\r
 942         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
 943         st.Step();\r
 944         st.Reset();\r
 945 \r
 946         st.Bind(0,"DeleteMessagesOlderThan");\r
 947         st.Bind(1,"180");\r
 948         st.Bind(2,"Automatically delete messages older than this many days.");\r
 949         st.Step();\r
 950         st.Reset();\r
 951 \r
 952         st.Bind(0,"VacuumOnStartup");\r
 953         st.Bind(1,"false");\r
 954         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
 955         st.Step();\r
 956         st.Reset();\r
 957 \r
 958 }\r
 959 \r
 960 void SetupLogFile()\r
 961 {\r
 962         DateTime date;\r
 963         std::string configval;\r
 964         int loglevel;\r
 965 \r
 966         date.SetToGMTime();\r
 967 \r
 968         LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");\r
 969         LogFile::Instance()->OpenFile();\r
 970         LogFile::Instance()->SetWriteNewLine(true);\r
 971         LogFile::Instance()->SetWriteDate(true);\r
 972         LogFile::Instance()->SetWriteLogLevel(true);\r
 973 \r
 974         if(Option::Instance()->Get("LogLevel",configval)==false)\r
 975         {\r
 976                 configval="4";\r
 977                 Option::Instance()->Set("LogLevel",configval);\r
 978         }\r
 979         if(StringFunctions::Convert(configval,loglevel)==false)\r
 980         {\r
 981                 loglevel=LogFile::LOGLEVEL_DEBUG;\r
 982                 Option::Instance()->Set("LogLevel",loglevel);\r
 983         }\r
 984         LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);\r
 985 }\r
 986 \r
 987 void SetupNetwork()\r
 988 {\r
 989 #ifdef _WIN32\r
 990         WSAData wsadata;\r
 991         WSAStartup(MAKEWORD(2,2),&wsadata);\r
 992 #endif\r
 993 }\r
 994 \r
 995 void Shutdown()\r
 996 {\r
 997 \r
 998         LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_DEBUG,"FMS starting shutdown");\r
 999 \r
1000         ThreadController::Instance()->ShutdownThreads();\r
1001 \r
1002         ShutdownNetwork();\r
1003 \r
1004         LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");\r
1005         LogFile::Instance()->WriteNewLine();\r
1006 }\r
1007 \r
1008 void ShutdownNetwork()\r
1009 {\r
1010 #ifdef _WIN32\r
1011         WSACleanup();\r
1012 #endif\r
1013 }\r
1014 \r
1015 void SigHandler(int signum)\r
1016 {\r
1017         Shutdown();\r
1018         exit(0);\r
1019 }\r
1020 \r
1021 void FixCapitalBoardNames()\r
1022 {\r
1023         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
1024 \r
1025         SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");\r
1026         SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");\r
1027         SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");\r
1028         SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");\r
1029         SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");\r
1030         SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");\r
1031 \r
1032         st.Step();\r
1033         while(st.RowReturned())\r
1034         {\r
1035                 int boardid=0;\r
1036                 int newboardid=0;\r
1037                 std::string name="";\r
1038                 std::string lowername="";\r
1039 \r
1040                 st.ResultInt(0,boardid);\r
1041                 st.ResultText(1,name);\r
1042 \r
1043                 lowername=name;\r
1044                 StringFunctions::LowerCase(lowername,lowername);\r
1045        \r
1046                 if(name!=lowername)\r
1047                 {\r
1048                         st2.Bind(0,lowername);\r
1049                         st2.Step();\r
1050 \r
1051                         if(st2.RowReturned())\r
1052                         {\r
1053                                 st2.ResultInt(0,newboardid);\r
1054 \r
1055                                 upd2.Bind(0,newboardid);\r
1056                                 upd2.Bind(1,boardid);\r
1057                                 upd2.Step();\r
1058                                 upd2.Reset();\r
1059 \r
1060                                 upd3.Bind(0,newboardid);\r
1061                                 upd3.Bind(1,boardid);\r
1062                                 upd3.Step();\r
1063                                 upd3.Reset();\r
1064 \r
1065                                 del.Bind(0,boardid);\r
1066                                 del.Step();\r
1067                                 del.Reset();\r
1068                         }\r
1069                         else\r
1070                         {\r
1071                                 upd.Bind(0,lowername);\r
1072                                 upd.Bind(1,boardid);\r
1073                                 upd.Step();\r
1074                                 upd.Reset();\r
1075                         }\r
1076 \r
1077                         st2.Reset();\r
1078                 }\r
1079        \r
1080                 st.Step();\r
1081         }\r
1082 \r
1083 }\r