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         // begin thread db schema\r
 424         db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\\r
 425                                 ThreadID                INTEGER PRIMARY KEY,\\r
 426                                 BoardID                 INTEGER,\\r
 427                                 FirstMessageID  INTEGER,\\r
 428                                 LastMessageID   INTEGER\\r
 429                                 );");\r
 430 \r
 431         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");\r
 432         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");\r
 433         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");\r
 434 \r
 435         db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\\r
 436                                 ThreadID                INTEGER,\\r
 437                                 MessageID               INTEGER,\\r
 438                                 PostOrder               INTEGER\\r
 439                                 );");\r
 440 \r
 441         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxThreadPost_ThreadMessage ON tblThreadPost(ThreadID,MessageID);");\r
 442         db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");\r
 443 \r
 444         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\\r
 445                                 FOR EACH ROW\\r
 446                                 BEGIN\\r
 447                                         DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\\r
 448                                 END;");\r
 449         // end thread db schema\r
 450 \r
 451         // low / high / message count for each board\r
 452         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 453                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 454                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 455                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 456                                 GROUP BY tblBoard.BoardID;");\r
 457 \r
 458         // calculates peer trust\r
 459         // 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
 460         // need the +1 so that when the values are 0 the result is not 0\r
 461         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 462         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 463                                 SELECT TargetIdentityID, \\r
 464                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 465                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 466                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 467                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 468                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 469                                 GROUP BY TargetIdentityID;");\r
 470 \r
 471         db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \\r
 472                                 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \\r
 473                                 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \\r
 474                                 GROUP BY tblIdentity.IdentityID;");\r
 475 \r
 476         /*\r
 477                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 478                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 479         */\r
 480         // drop existing triggers\r
 481         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 482         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 483         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 484         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 485 /*\r
 486         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 487         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 488                                 FOR EACH ROW \\r
 489                                 BEGIN \\r
 490                                         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
 491                                 END;");\r
 492 \r
 493         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 494         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 495                                 FOR EACH ROW \\r
 496                                 BEGIN \\r
 497                                         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
 498                                 END;");\r
 499 \r
 500         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 501         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE 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                                         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
 506                                 END;");\r
 507 \r
 508         // 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
 509         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 510                                 FOR EACH ROW \\r
 511                                 BEGIN \\r
 512                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 513                                 END;");\r
 514 */\r
 515 \r
 516         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 517                                 FOR EACH ROW \\r
 518                                 BEGIN \\r
 519                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 520                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 521                                 END;");\r
 522 \r
 523         db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
 524         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 525                                 FOR EACH ROW \\r
 526                                 BEGIN \\r
 527                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 528                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 529                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 530                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 531                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 532                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 533                                         DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
 534                                 END;");\r
 535 \r
 536         db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
 537         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 538                                 FOR EACH ROW \\r
 539                                 BEGIN \\r
 540                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 541                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 542                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 543                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 544                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 545                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 546                                         DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
 547                                 END;");\r
 548 \r
 549         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 550                                 FOR EACH ROW \\r
 551                                 BEGIN \\r
 552                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 553                                 END;");\r
 554 \r
 555         // delete introduction puzzles that were half-way inserted\r
 556         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 557 \r
 558         // delete stale introduction puzzles (2 or more days old)\r
 559         date-=Poco::Timespan(2,0,0,0,0);\r
 560         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 561         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 562 \r
 563         date=Poco::Timestamp();\r
 564         // insert SomeDude's public key\r
 565         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
 566         // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
 567         //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
 568         // insert garfield's public key -haven't seen in a while - disabling for now\r
 569         //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
 570         // insert alek's public key - haven't seen in a while - disabling for now\r
 571         //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
 572         // insert Luke771's public key\r
 573         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
 574         // insert falafel's public key\r
 575         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
 576         // insert cptn_insano's public key\r
 577         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
 578         // insert Flink's public key - haven't seen in a while - disabling for now\r
 579         //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
 580         // insert Kane's public key\r
 581         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
 582         // inserts boardstat's public key\r
 583         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
 584 \r
 585         // TODO remove sometime after 0.1.17\r
 586         FixCapitalBoardNames(db);\r
 587 \r
 588         // run analyze - may speed up some queries\r
 589         db->Execute("ANALYZE;");\r
 590 \r
 591 }\r
 592 \r
 593 const bool VerifyDB(SQLite3DB::DB *db)\r
 594 {\r
 595         SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
 596         st.Step();\r
 597         if(st.RowReturned())\r
 598         {\r
 599                 std::string res="";\r
 600                 st.ResultText(0,res);\r
 601                 if(res=="ok")\r
 602                 {\r
 603                         return true;\r
 604                 }\r
 605                 else\r
 606                 {\r
 607                         // try to reindex and vacuum database in case of index corruption\r
 608                         st=db->Prepare("REINDEX;");\r
 609                         st.Step();\r
 610                         st=db->Prepare("VACUUM;");\r
 611                         st.Step();\r
 612 \r
 613                         // check integrity again\r
 614                         st=db->Prepare("PRAGMA integrity_check;");\r
 615                         st.Step();\r
 616                         st.ResultText(0,res);\r
 617                         if(res=="ok")\r
 618                         {\r
 619                                 return true;\r
 620                         }\r
 621                         else\r
 622                         {\r
 623                                 return false;\r
 624                         }\r
 625                 }\r
 626         }\r
 627         else\r
 628         {\r
 629                 return false;\r
 630         }\r
 631 }\r
 632 \r
 633 const std::string TestDBIntegrity(SQLite3DB::DB *db)\r
 634 {\r
 635         std::string result="";\r
 636 \r
 637         SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
 638         st.Step();\r
 639         while(st.RowReturned())\r
 640         {\r
 641                 std::string text="";\r
 642                 st.ResultText(0,text);\r
 643                 result+=text;\r
 644                 st.Step();\r
 645         }\r
 646         return result;\r
 647 }\r