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