src/dbsetup.cpp

   1 #include "../include/dbsetup.h"\r
   2 #include "../include/dbconversions.h"\r
   3 #include "../include/option.h"\r
   4 #include "../include/db/sqlite3db.h"\r
   5 \r
   6 #include <Poco/DateTime.h>\r
   7 #include <Poco/Timespan.h>\r
   8 #include <Poco/DateTimeFormatter.h>\r
   9 \r
  10 void SetupDB(SQLite3DB::DB *db)\r
  11 {\r
  12 \r
  13         Poco::DateTime date;\r
  14         std::string tempval="";\r
  15 \r
  16         db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
  17                                 Major                           INTEGER,\\r
  18                                 Minor                           INTEGER\\r
  19                                 );");\r
  20 \r
  21         SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");\r
  22         st.Step();\r
  23         if(st.RowReturned())\r
  24         {\r
  25                 int major;\r
  26                 int minor;\r
  27                 st.ResultInt(0,major);\r
  28                 st.ResultInt(1,minor);\r
  29                 st.Finalize();\r
  30                 if(major==1 && minor==0)\r
  31                 {\r
  32                         ConvertDB0100To0101(db);\r
  33                         major=1;\r
  34                         minor=1;\r
  35                 }\r
  36                 if(major==1 && (minor==1 || minor==2))\r
  37                 {\r
  38                         ConvertDB0101To0103(db);\r
  39                         major=1;\r
  40                         minor=3;\r
  41                 }\r
  42                 if(major==1 && minor==3)\r
  43                 {\r
  44                         ConvertDB0103To0104(db);\r
  45                         major=1;\r
  46                         minor=4;\r
  47                 }\r
  48                 if(major==1 && minor==4)\r
  49                 {\r
  50                         ConvertDB0104To0105(db);\r
  51                         major=1;\r
  52                         minor=5;\r
  53                 }\r
  54                 if(major==1 && minor==5)\r
  55                 {\r
  56                         ConvertDB0105To0106(db);\r
  57                         major=1;\r
  58                         minor=6;\r
  59                 }\r
  60                 if(major==1 && minor==6)\r
  61                 {\r
  62                         ConvertDB0106To0107(db);\r
  63                         major=1;\r
  64                         minor=7;\r
  65                 }\r
  66                 if(major==1 && minor==7)\r
  67                 {\r
  68                         ConvertDB0107To0108(db);\r
  69                         major=1;\r
  70                         minor=8;\r
  71                 }\r
  72                 if(major==1 && minor==8)\r
  73                 {\r
  74                         ConvertDB0108To0109(db);\r
  75                         major=1;\r
  76                         minor=9;\r
  77                 }\r
  78                 if(major==1 && minor==9)\r
  79                 {\r
  80                         ConvertDB0109To0110(db);\r
  81                         major=1;\r
  82                         minor=10;\r
  83                 }\r
  84                 if(major==1 && minor==10)\r
  85                 {\r
  86                         ConvertDB0110To0111(db);\r
  87                         major=1;\r
  88                         minor=11;\r
  89                 }\r
  90                 if(major==1 && minor==11)\r
  91                 {\r
  92                         ConvertDB0111To0112(db);\r
  93                         major=1;\r
  94                         minor=12;\r
  95                 }\r
  96                 if(major==1 && minor==12)\r
  97                 {\r
  98                         ConvertDB0112To0113(db);\r
  99                         major=1;\r
 100                         minor=13;\r
 101                 }\r
 102                 if(major==1 && minor==13)\r
 103                 {\r
 104                         ConvertDB0113To0114(db);\r
 105                         major=1;\r
 106                         minor=14;\r
 107                 }\r
 108                 if(major==1 && minor==14)\r
 109                 {\r
 110                         ConvertDB0114To0115(db);\r
 111                         major=1;\r
 112                         minor=15;\r
 113                 }\r
 114                 if(major==1 && minor==15)\r
 115                 {\r
 116                         ConvertDB0115To0116(db);\r
 117                         major=1;\r
 118                         minor=16;\r
 119                 }\r
 120                 if(major==1 && minor==16)\r
 121                 {\r
 122                         ConvertDB0116To0117(db);\r
 123                         major=1;\r
 124                         minor=17;\r
 125                 }\r
 126         }\r
 127         else\r
 128         {\r
 129                 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,17);");\r
 130         }\r
 131 \r
 132         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=17;");\r
 133 \r
 134         db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\\r
 135                                 Major                           INTEGER,\\r
 136                                 Minor                           INTEGER,\\r
 137                                 Release                         INTEGER,\\r
 138                                 Notes                           TEXT,\\r
 139                                 Changes                         TEXT,\\r
 140                                 PageKey                         TEXT,\\r
 141                                 SourceKey                       TEXT\\r
 142                                 );");\r
 143 \r
 144         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");\r
 145 \r
 146         db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
 147                                 Option                          TEXT UNIQUE,\\r
 148                                 OptionValue                     TEXT NOT NULL,\\r
 149                                 OptionDescription       TEXT,\\r
 150                                 Section                         TEXT,\\r
 151                                 SortOrder                       INTEGER,\\r
 152                                 ValidValues                     TEXT,\\r
 153                                 DisplayType                     TEXT CHECK (DisplayType IN ('textbox','textarea','select','multiselect')) DEFAULT 'textbox',\\r
 154                                 DisplayParam1           TEXT,\\r
 155                                 DisplayParam2           TEXT,\\r
 156                                 Mode                            TEXT CHECK (Mode IN ('simple','advanced')) DEFAULT 'simple'\\r
 157                                 );");\r
 158 \r
 159         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 160                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 161                                 Name                                    TEXT,\\r
 162                                 PublicKey                               TEXT UNIQUE,\\r
 163                                 PrivateKey                              TEXT UNIQUE,\\r
 164                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 165                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 166                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 167                                 PublishFreesite                 BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
 168                                 FreesiteEdition                 INTEGER,\\r
 169                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 170                                 LastInsertedIdentity    DATETIME,\\r
 171                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 172                                 LastInsertedPuzzle              DATETIME,\\r
 173                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 174                                 LastInsertedTrustList   DATETIME,\\r
 175                                 LastInsertedBoardList   DATETIME,\\r
 176                                 LastInsertedMessageList DATETIME,\\r
 177                                 LastInsertedFreesite    DATETIME,\\r
 178                                 DateCreated                             DATETIME,\\r
 179                                 MinMessageDelay                 INTEGER DEFAULT 0,\\r
 180                                 MaxMessageDelay                 INTEGER DEFAULT 0\\r
 181                                 );");\r
 182 \r
 183         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r
 184                                 LocalIdentityID         INTEGER,\\r
 185                                 Day                                     DATE,\\r
 186                                 InsertIndex                     INTEGER\\r
 187                                 );");\r
 188 \r
 189         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\\r
 190                                 LocalIdentityID         INTEGER,\\r
 191                                 Day                                     DATE,\\r
 192                                 InsertIndex                     INTEGER\\r
 193                                 );");\r
 194 \r
 195         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\\r
 196                                 IdentityID                      INTEGER,\\r
 197                                 Day                                     DATE,\\r
 198                                 RequestIndex            INTEGER,\\r
 199                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 200                                 );");\r
 201 \r
 202         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
 203                                 UUID                            TEXT UNIQUE,\\r
 204                                 LocalIdentityID         INTEGER,\\r
 205                                 Day                                     DATE,\\r
 206                                 InsertIndex                     INTEGER,\\r
 207                                 Type                            TEXT,\\r
 208                                 MimeType                        TEXT,\\r
 209                                 PuzzleData                      TEXT,\\r
 210                                 PuzzleSolution          TEXT,\\r
 211                                 FoundSolution           BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\\r
 212                                 );");\r
 213 \r
 214         /*\r
 215                 PurgeDate is not used yet\r
 216         */\r
 217         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 218                                 IdentityID                              INTEGER PRIMARY KEY,\\r
 219                                 PublicKey                               TEXT UNIQUE,\\r
 220                                 Name                                    TEXT,\\r
 221                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 222                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 223                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 224                                 FreesiteEdition                 INTEGER,\\r
 225                                 DateAdded                               DATETIME,\\r
 226                                 LastSeen                                DATETIME,\\r
 227                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 228                                 PeerMessageTrust                INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 229                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 230                                 PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 231                                 AddedMethod                             TEXT,\\r
 232                                 Hidden                                  BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\\r
 233                                 PurgeDate                               DATETIME,\\r
 234                                 FailureCount                    INTEGER CHECK(FailureCount>=0) DEFAULT 0\\r
 235                                 );");\r
 236 \r
 237         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
 238                                 IdentityID                      INTEGER,\\r
 239                                 Day                                     DATE,\\r
 240                                 RequestIndex            INTEGER,\\r
 241                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 242                                 );");\r
 243 \r
 244         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\\r
 245                                 IdentityID                      INTEGER,\\r
 246                                 Day                                     DATE,\\r
 247                                 RequestIndex            INTEGER,\\r
 248                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 249                                 UUID                            TEXT UNIQUE,\\r
 250                                 Type                            TEXT,\\r
 251                                 MimeType                        TEXT,\\r
 252                                 PuzzleData                      TEXT\\r
 253                                 );");\r
 254 \r
 255         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\\r
 256                                 LocalIdentityID         INTEGER,\\r
 257                                 Day                                     DATE,\\r
 258                                 UUID                            TEXT UNIQUE,\\r
 259                                 Solution                        TEXT,\\r
 260                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 261                                 );");\r
 262 \r
 263         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\\r
 264                                 LocalIdentityID                 INTEGER,\\r
 265                                 IdentityID                              INTEGER,\\r
 266                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 267                                 MessageTrustComment             TEXT,\\r
 268                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 269                                 TrustListTrustComment   TEXT\\r
 270                                 );");\r
 271 \r
 272         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");\r
 273 \r
 274         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \\r
 275                                 FOR EACH ROW \\r
 276                                 BEGIN \\r
 277                                         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
 278                                 END;");\r
 279 \r
 280         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \\r
 281                                 FOR EACH ROW \\r
 282                                 BEGIN \\r
 283                                         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
 284                                 END;");\r
 285 \r
 286         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \\r
 287                                 FOR EACH ROW \\r
 288                                 BEGIN \\r
 289                                         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
 290                                 END;");\r
 291 \r
 292         db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
 293                                 IdentityID                              INTEGER,\\r
 294                                 TargetIdentityID                INTEGER,\\r
 295                                 MessageTrust                    INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\\r
 296                                 TrustListTrust                  INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\\r
 297                                 MessageTrustComment             TEXT,\\r
 298                                 TrustListTrustComment   TEXT\\r
 299                                 );");\r
 300 \r
 301         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");\r
 302         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");\r
 303 \r
 304         db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\\r
 305                                 BoardID                                 INTEGER PRIMARY KEY,\\r
 306                                 BoardName                               TEXT UNIQUE,\\r
 307                                 BoardDescription                TEXT,\\r
 308                                 DateAdded                               DATETIME,\\r
 309                                 SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\\r
 310                                 AddedMethod                             TEXT,\\r
 311                                 Forum                                   TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\\r
 312                                 );");\r
 313 \r
 314         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Seed Board','true');");\r
 315         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Seed Board','true');");\r
 316         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Seed Board','true');");\r
 317         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Seed Board','true');");\r
 318 \r
 319         db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
 320                                 MessageID                       INTEGER PRIMARY KEY,\\r
 321                                 IdentityID                      INTEGER,\\r
 322                                 FromName                        TEXT,\\r
 323                                 MessageDate                     DATE,\\r
 324                                 MessageTime                     TIME,\\r
 325                                 Subject                         TEXT,\\r
 326                                 MessageUUID                     TEXT UNIQUE,\\r
 327                                 ReplyBoardID            INTEGER,\\r
 328                                 Body                            TEXT,\\r
 329                                 InsertDate                      DATE,\\r
 330                                 MessageIndex            INTEGER,\\r
 331                                 Read                            INTEGER CHECK(Read IN(0,1)) DEFAULT 0\\r
 332                                 );");\r
 333 \r
 334         db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");\r
 335 \r
 336         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
 337                                 MessageID                       INTEGER,\\r
 338                                 ReplyToMessageUUID      TEXT,\\r
 339                                 ReplyOrder                      INTEGER\\r
 340                                 );");\r
 341 \r
 342         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\r
 343         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);");\r
 344 \r
 345         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
 346                                 MessageID                       INTEGER,\\r
 347                                 BoardID                         INTEGER\\r
 348                                 );");\r
 349 \r
 350         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");\r
 351         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");\r
 352 \r
 353         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\\r
 354                                 IdentityID                      INTEGER,\\r
 355                                 Day                                     DATE,\\r
 356                                 RequestIndex            INTEGER,\\r
 357                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 358                                 );");\r
 359 \r
 360         /*\r
 361                 Key is for anonymous messages (future)\r
 362         */\r
 363         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\\r
 364                                 IdentityID                      INTEGER,\\r
 365                                 Day                                     DATE,\\r
 366                                 RequestIndex            INTEGER,\\r
 367                                 FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
 368                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 369                                 Tries                           INTEGER DEFAULT 0,\\r
 370                                 Key                                     TEXT,\\r
 371                                 FromIdentityID          INTEGER\\r
 372                                 );");\r
 373 \r
 374         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 375 \r
 376         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\\r
 377                                 LocalIdentityID         INTEGER,\\r
 378                                 Day                                     DATE,\\r
 379                                 InsertIndex                     INTEGER,\\r
 380                                 MessageUUID                     TEXT UNIQUE,\\r
 381                                 MessageXML                      TEXT,\\r
 382                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\\r
 383                                 SendDate                        DATETIME\\r
 384                                 );");\r
 385 \r
 386         db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
 387                                 FileInsertID            INTEGER PRIMARY KEY,\\r
 388                                 MessageUUID                     TEXT,\\r
 389                                 FileName                        TEXT,\\r
 390                                 Key                                     TEXT,\\r
 391                                 Size                            INTEGER,\\r
 392                                 MimeType                        TEXT,\\r
 393                                 Data                            BLOB\\r
 394                                 );");\r
 395 \r
 396         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
 397                                 LocalIdentityID         INTEGER,\\r
 398                                 Day                                     DATE,\\r
 399                                 InsertIndex                     INTEGER,\\r
 400                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 401                                 );");\r
 402 \r
 403         db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\\r
 404                                 BoardID                                         INTEGER UNIQUE,\\r
 405                                 ModifyLocalMessageTrust         INTEGER,\\r
 406                                 ModifyLocalTrustListTrust       INTEGER\\r
 407                                 );");\r
 408 \r
 409         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\\r
 410                                 LocalIdentityID         INTEGER,\\r
 411                                 Day                                     DATE,\\r
 412                                 InsertIndex                     INTEGER,\\r
 413                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 414                                 );");\r
 415 \r
 416         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\\r
 417                                 IdentityID                      INTEGER,\\r
 418                                 Day                                     DATE,\\r
 419                                 RequestIndex            INTEGER,\\r
 420                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 421                                 );");\r
 422 \r
 423 #ifdef FROST_SUPPORT\r
 424 \r
 425         db->Execute("CREATE TABLE IF NOT EXISTS tblFrostMessageRequests(\\r
 426                                 BoardID                         INTEGER,\\r
 427                                 Day                                     DATE,\\r
 428                                 RequestIndex            INTEGER,\\r
 429                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 430                                 Tries                           INTEGER DEFAULT 0\\r
 431                                 );");\r
 432 \r
 433         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFrostMessageRequest ON tblFrostMessageRequests(BoardID,Day,RequestIndex);");\r
 434 \r
 435 #endif\r
 436 \r
 437         // begin thread db schema\r
 438         db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\\r
 439                                 ThreadID                INTEGER PRIMARY KEY,\\r
 440                                 BoardID                 INTEGER,\\r
 441                                 FirstMessageID  INTEGER,\\r
 442                                 LastMessageID   INTEGER\\r
 443                                 );");\r
 444 \r
 445         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");\r
 446         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");\r
 447         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");\r
 448 \r
 449         db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\\r
 450                                 ThreadID                INTEGER,\\r
 451                                 MessageID               INTEGER,\\r
 452                                 PostOrder               INTEGER\\r
 453                                 );");\r
 454 \r
 455         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxThreadPost_ThreadMessage ON tblThreadPost(ThreadID,MessageID);");\r
 456         db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");\r
 457 \r
 458         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\\r
 459                                 FOR EACH ROW\\r
 460                                 BEGIN\\r
 461                                         DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\\r
 462                                 END;");\r
 463         // end thread db schema\r
 464 \r
 465         // low / high / message count for each board\r
 466         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 467                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 468                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 469                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 470                                 GROUP BY tblBoard.BoardID;");\r
 471 \r
 472         // calculates peer trust\r
 473         // 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
 474         // need the +1 so that when the values are 0 the result is not 0\r
 475         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 476         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 477                                 SELECT TargetIdentityID, \\r
 478                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 479                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 480                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 481                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 482                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 483                                 GROUP BY TargetIdentityID;");\r
 484 \r
 485         db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \\r
 486                                 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \\r
 487                                 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \\r
 488                                 GROUP BY tblIdentity.IdentityID;");\r
 489 \r
 490         /*\r
 491                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 492                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 493         */\r
 494         // drop existing triggers\r
 495         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 496         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 497         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 498         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 499 /*\r
 500         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 501         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 502                                 FOR EACH ROW \\r
 503                                 BEGIN \\r
 504                                         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
 505                                 END;");\r
 506 \r
 507         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 508         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 509                                 FOR EACH ROW \\r
 510                                 BEGIN \\r
 511                                         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
 512                                 END;");\r
 513 \r
 514         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 515         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 516                                 FOR EACH ROW \\r
 517                                 BEGIN \\r
 518                                         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
 519                                         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
 520                                 END;");\r
 521 \r
 522         // 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
 523         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 524                                 FOR EACH ROW \\r
 525                                 BEGIN \\r
 526                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 527                                 END;");\r
 528 */\r
 529 \r
 530         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 531                                 FOR EACH ROW \\r
 532                                 BEGIN \\r
 533                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 534                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 535                                 END;");\r
 536 \r
 537         db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
 538         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 539                                 FOR EACH ROW \\r
 540                                 BEGIN \\r
 541                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 542                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 543                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 544                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 545                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 546                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 547                                         DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
 548                                 END;");\r
 549 \r
 550         db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
 551         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 552                                 FOR EACH ROW \\r
 553                                 BEGIN \\r
 554                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 555                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 556                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 557                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 558                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 559                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 560                                         DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
 561                                 END;");\r
 562 \r
 563         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 564                                 FOR EACH ROW \\r
 565                                 BEGIN \\r
 566                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 567                                 END;");\r
 568 \r
 569         // delete introduction puzzles that were half-way inserted\r
 570         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 571 \r
 572         // delete stale introduction puzzles (2 or more days old)\r
 573         date-=Poco::Timespan(2,0,0,0,0);\r
 574         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 575         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 576 \r
 577         date=Poco::Timestamp();\r
 578         // insert SomeDude's public key\r
 579         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust,AddedMethod) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"',50,'Seed Identity');");\r
 580         // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
 581         //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 582         // insert garfield's public key -haven't seen in a while - disabling for now\r
 583         //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 584         // insert alek's public key - haven't seen in a while - disabling for now\r
 585         //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@lTjeI6V0lQsktXqaqJ6Iwk4TdsHduQI54rdUpHfhGbg,0oTYfrxxx8OmdU1~60gqpf3781qzEicM4Sz97mJsBM4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 586         // insert Luke771's public key\r
 587         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@mdXK~ZVlfTZhF1SLBrvZ--i0vOsOpa~w9wv~~psQ-04,gXonsXKc7aexKSO8Gt8Fwre4Qgmmbt2WueO7VzxNKkk,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 588         // insert falafel's public key\r
 589         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 590         // insert cptn_insano's public key\r
 591         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@bloE1LJ~qzSYUkU2nt7sB9kq060D4HTQC66pk5Q8NpA,DOOASUnp0kj6tOdhZJ-h5Tk7Ka50FSrUgsH7tCG1usU,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 592         // insert Flink's public key - haven't seen in a while - disabling for now\r
 593         //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@q2TtkNBOuuniyJ56~8NSopCs3ttwe5KlB31ugZtWmXA,6~PzIupS8YK7L6oFNpXGKJmHT2kBMDfwTg73nHdNur8,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 594         // insert Kane's public key\r
 595         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@Ofm~yZivDJ5Z2fSzZbMiLEUUQaIc0KHRdZMBTaPLO6I,WLm4s4hNbOOurJ6ijfOq4odz7-dN7uTUvYxJRwWnlMI,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 596         // inserts boardstat's public key\r
 597         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@aYWBb6zo2AM13XCNhsmmRKMANEx6PG~C15CWjdZziKA,X1pAG4EIqR1gAiyGFVZ1iiw-uTlh460~rFACJ7ZHQXk,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 598 \r
 599         // TODO remove sometime after 0.1.17\r
 600         FixCapitalBoardNames(db);\r
 601 \r
 602         // run analyze - may speed up some queries\r
 603         db->Execute("ANALYZE;");\r
 604 \r
 605 }\r
 606 \r
 607 const bool VerifyDB(SQLite3DB::DB *db)\r
 608 {\r
 609         SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
 610         st.Step();\r
 611         if(st.RowReturned())\r
 612         {\r
 613                 std::string res="";\r
 614                 st.ResultText(0,res);\r
 615                 if(res=="ok")\r
 616                 {\r
 617                         return true;\r
 618                 }\r
 619                 else\r
 620                 {\r
 621                         // try to reindex and vacuum database in case of index corruption\r
 622                         st=db->Prepare("REINDEX;");\r
 623                         st.Step();\r
 624                         st=db->Prepare("VACUUM;");\r
 625                         st.Step();\r
 626 \r
 627                         // check integrity again\r
 628                         st=db->Prepare("PRAGMA integrity_check;");\r
 629                         st.Step();\r
 630                         st.ResultText(0,res);\r
 631                         if(res=="ok")\r
 632                         {\r
 633                                 return true;\r
 634                         }\r
 635                         else\r
 636                         {\r
 637                                 return false;\r
 638                         }\r
 639                 }\r
 640         }\r
 641         else\r
 642         {\r
 643                 return false;\r
 644         }\r
 645 }\r
 646 \r
 647 const std::string TestDBIntegrity(SQLite3DB::DB *db)\r
 648 {\r
 649         std::string result="";\r
 650 \r
 651         SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
 652         st.Step();\r
 653         while(st.RowReturned())\r
 654         {\r
 655                 std::string text="";\r
 656                 st.ResultText(0,text);\r
 657                 result+=text;\r
 658                 st.Step();\r
 659         }\r
 660         return result;\r
 661 }\r