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         }\r
 121         else\r
 122         {\r
 123                 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,16);");\r
 124         }\r
 125 \r
 126         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=16;");\r
 127 \r
 128         db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\\r
 129                                 Major                           INTEGER,\\r
 130                                 Minor                           INTEGER,\\r
 131                                 Release                         INTEGER,\\r
 132                                 Notes                           TEXT,\\r
 133                                 Changes                         TEXT,\\r
 134                                 PageKey                         TEXT,\\r
 135                                 SourceKey                       TEXT\\r
 136                                 );");\r
 137 \r
 138         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");\r
 139 \r
 140         db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
 141                                 Option                          TEXT UNIQUE,\\r
 142                                 OptionValue                     TEXT NOT NULL,\\r
 143                                 OptionDescription       TEXT,\\r
 144                                 Section                         TEXT,\\r
 145                                 SortOrder                       INTEGER,\\r
 146                                 ValidValues                     TEXT,\\r
 147                                 DisplayType                     TEXT CHECK (DisplayType IN ('textbox','textarea','select','multiselect')) DEFAULT 'textbox',\\r
 148                                 DisplayParam1           TEXT,\\r
 149                                 DisplayParam2           TEXT,\\r
 150                                 Mode                            TEXT CHECK (Mode IN ('simple','advanced')) DEFAULT 'simple'\\r
 151                                 );");\r
 152 \r
 153         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 154                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 155                                 Name                                    TEXT,\\r
 156                                 PublicKey                               TEXT UNIQUE,\\r
 157                                 PrivateKey                              TEXT UNIQUE,\\r
 158                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 159                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 160                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 161                                 PublishFreesite                 BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
 162                                 FreesiteEdition                 INTEGER,\\r
 163                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 164                                 LastInsertedIdentity    DATETIME,\\r
 165                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 166                                 LastInsertedPuzzle              DATETIME,\\r
 167                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 168                                 LastInsertedTrustList   DATETIME,\\r
 169                                 LastInsertedBoardList   DATETIME,\\r
 170                                 LastInsertedMessageList DATETIME,\\r
 171                                 LastInsertedFreesite    DATETIME,\\r
 172                                 DateCreated                             DATETIME,\\r
 173                                 MinMessageDelay                 INTEGER DEFAULT 0,\\r
 174                                 MaxMessageDelay                 INTEGER DEFAULT 0\\r
 175                                 );");\r
 176 \r
 177         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r
 178                                 LocalIdentityID         INTEGER,\\r
 179                                 Day                                     DATE,\\r
 180                                 InsertIndex                     INTEGER\\r
 181                                 );");\r
 182 \r
 183         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\\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 tblTrustListRequests(\\r
 190                                 IdentityID                      INTEGER,\\r
 191                                 Day                                     DATE,\\r
 192                                 RequestIndex            INTEGER,\\r
 193                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 194                                 );");\r
 195 \r
 196         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
 197                                 UUID                            TEXT UNIQUE,\\r
 198                                 LocalIdentityID         INTEGER,\\r
 199                                 Day                                     DATE,\\r
 200                                 InsertIndex                     INTEGER,\\r
 201                                 Type                            TEXT,\\r
 202                                 MimeType                        TEXT,\\r
 203                                 PuzzleData                      TEXT,\\r
 204                                 PuzzleSolution          TEXT,\\r
 205                                 FoundSolution           BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\\r
 206                                 );");\r
 207 \r
 208         /*\r
 209                 PurgeDate is not used yet\r
 210         */\r
 211         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 212                                 IdentityID                              INTEGER PRIMARY KEY,\\r
 213                                 PublicKey                               TEXT UNIQUE,\\r
 214                                 Name                                    TEXT,\\r
 215                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 216                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 217                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 218                                 FreesiteEdition                 INTEGER,\\r
 219                                 DateAdded                               DATETIME,\\r
 220                                 LastSeen                                DATETIME,\\r
 221                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 222                                 PeerMessageTrust                INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 223                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 224                                 PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 225                                 AddedMethod                             TEXT,\\r
 226                                 Hidden                                  BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\\r
 227                                 PurgeDate                               DATETIME,\\r
 228                                 FailureCount                    INTEGER CHECK(FailureCount>=0) DEFAULT 0\\r
 229                                 );");\r
 230 \r
 231         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
 232                                 IdentityID                      INTEGER,\\r
 233                                 Day                                     DATE,\\r
 234                                 RequestIndex            INTEGER,\\r
 235                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 236                                 );");\r
 237 \r
 238         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\\r
 239                                 IdentityID                      INTEGER,\\r
 240                                 Day                                     DATE,\\r
 241                                 RequestIndex            INTEGER,\\r
 242                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 243                                 UUID                            TEXT UNIQUE,\\r
 244                                 Type                            TEXT,\\r
 245                                 MimeType                        TEXT,\\r
 246                                 PuzzleData                      TEXT\\r
 247                                 );");\r
 248 \r
 249         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\\r
 250                                 LocalIdentityID         INTEGER,\\r
 251                                 Day                                     DATE,\\r
 252                                 UUID                            TEXT UNIQUE,\\r
 253                                 Solution                        TEXT,\\r
 254                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 255                                 );");\r
 256 \r
 257         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\\r
 258                                 LocalIdentityID                 INTEGER,\\r
 259                                 IdentityID                              INTEGER,\\r
 260                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 261                                 MessageTrustComment             TEXT,\\r
 262                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 263                                 TrustListTrustComment   TEXT\\r
 264                                 );");\r
 265 \r
 266         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");\r
 267 \r
 268         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \\r
 269                                 FOR EACH ROW \\r
 270                                 BEGIN \\r
 271                                         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
 272                                 END;");\r
 273 \r
 274         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust 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 trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \\r
 281                                 FOR EACH ROW \\r
 282                                 BEGIN \\r
 283                                         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
 284                                 END;");\r
 285 \r
 286         db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
 287                                 IdentityID                              INTEGER,\\r
 288                                 TargetIdentityID                INTEGER,\\r
 289                                 MessageTrust                    INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\\r
 290                                 TrustListTrust                  INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\\r
 291                                 MessageTrustComment             TEXT,\\r
 292                                 TrustListTrustComment   TEXT\\r
 293                                 );");\r
 294 \r
 295         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");\r
 296         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");\r
 297 \r
 298         db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\\r
 299                                 BoardID                                 INTEGER PRIMARY KEY,\\r
 300                                 BoardName                               TEXT UNIQUE,\\r
 301                                 BoardDescription                TEXT,\\r
 302                                 DateAdded                               DATETIME,\\r
 303                                 SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\\r
 304                                 AddedMethod                             TEXT,\\r
 305                                 Forum                                   TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\\r
 306                                 );");\r
 307 \r
 308         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
 309         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
 310         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Seed Board','true');");\r
 311         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Seed Board','true');");\r
 312 \r
 313         db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
 314                                 MessageID                       INTEGER PRIMARY KEY,\\r
 315                                 IdentityID                      INTEGER,\\r
 316                                 FromName                        TEXT,\\r
 317                                 MessageDate                     DATE,\\r
 318                                 MessageTime                     TIME,\\r
 319                                 Subject                         TEXT,\\r
 320                                 MessageUUID                     TEXT UNIQUE,\\r
 321                                 ReplyBoardID            INTEGER,\\r
 322                                 Body                            TEXT,\\r
 323                                 MessageIndex            INTEGER,\\r
 324                                 Read                            INTEGER CHECK(Read IN(0,1)) DEFAULT 0\\r
 325                                 );");\r
 326 \r
 327         db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");\r
 328 \r
 329         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
 330                                 MessageID                       INTEGER,\\r
 331                                 ReplyToMessageUUID      TEXT,\\r
 332                                 ReplyOrder                      INTEGER\\r
 333                                 );");\r
 334 \r
 335         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\r
 336         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);");\r
 337 \r
 338         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
 339                                 MessageID                       INTEGER,\\r
 340                                 BoardID                         INTEGER\\r
 341                                 );");\r
 342 \r
 343         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");\r
 344         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");\r
 345 \r
 346         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\\r
 347                                 IdentityID                      INTEGER,\\r
 348                                 Day                                     DATE,\\r
 349                                 RequestIndex            INTEGER,\\r
 350                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 351                                 );");\r
 352 \r
 353         /*\r
 354                 Key is for anonymous messages (future)\r
 355         */\r
 356         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\\r
 357                                 IdentityID                      INTEGER,\\r
 358                                 Day                                     DATE,\\r
 359                                 RequestIndex            INTEGER,\\r
 360                                 FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
 361                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 362                                 Tries                           INTEGER DEFAULT 0,\\r
 363                                 Key                                     TEXT,\\r
 364                                 FromIdentityID          INTEGER\\r
 365                                 );");\r
 366 \r
 367         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 368 \r
 369         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\\r
 370                                 LocalIdentityID         INTEGER,\\r
 371                                 Day                                     DATE,\\r
 372                                 InsertIndex                     INTEGER,\\r
 373                                 MessageUUID                     TEXT UNIQUE,\\r
 374                                 MessageXML                      TEXT,\\r
 375                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\\r
 376                                 SendDate                        DATETIME\\r
 377                                 );");\r
 378 \r
 379         db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
 380                                 FileInsertID            INTEGER PRIMARY KEY,\\r
 381                                 MessageUUID                     TEXT,\\r
 382                                 FileName                        TEXT,\\r
 383                                 Key                                     TEXT,\\r
 384                                 Size                            INTEGER,\\r
 385                                 MimeType                        TEXT,\\r
 386                                 Data                            BLOB\\r
 387                                 );");\r
 388 \r
 389         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
 390                                 LocalIdentityID         INTEGER,\\r
 391                                 Day                                     DATE,\\r
 392                                 InsertIndex                     INTEGER,\\r
 393                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 394                                 );");\r
 395 \r
 396         db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\\r
 397                                 BoardID                                         INTEGER UNIQUE,\\r
 398                                 ModifyLocalMessageTrust         INTEGER,\\r
 399                                 ModifyLocalTrustListTrust       INTEGER\\r
 400                                 );");\r
 401 \r
 402         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\\r
 403                                 LocalIdentityID         INTEGER,\\r
 404                                 Day                                     DATE,\\r
 405                                 InsertIndex                     INTEGER,\\r
 406                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 407                                 );");\r
 408 \r
 409         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\\r
 410                                 IdentityID                      INTEGER,\\r
 411                                 Day                                     DATE,\\r
 412                                 RequestIndex            INTEGER,\\r
 413                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 414                                 );");\r
 415 \r
 416         // begin thread db schema\r
 417         db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\\r
 418                                 ThreadID                INTEGER PRIMARY KEY,\\r
 419                                 BoardID                 INTEGER,\\r
 420                                 FirstMessageID  INTEGER,\\r
 421                                 LastMessageID   INTEGER\\r
 422                                 );");\r
 423 \r
 424         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");\r
 425         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");\r
 426         db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");\r
 427 \r
 428         db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\\r
 429                                 ThreadID                INTEGER,\\r
 430                                 MessageID               INTEGER,\\r
 431                                 PostOrder               INTEGER\\r
 432                                 );");\r
 433 \r
 434         db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_ThreadID ON tblThreadPost(ThreadID);");\r
 435         db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");\r
 436 \r
 437         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\\r
 438                                 FOR EACH ROW\\r
 439                                 BEGIN\\r
 440                                         DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\\r
 441                                 END;");\r
 442         // end thread db schema\r
 443 \r
 444         // low / high / message count for each board\r
 445         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 446                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 447                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 448                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 449                                 GROUP BY tblBoard.BoardID;");\r
 450 \r
 451         // calculates peer trust\r
 452         // 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
 453         // need the +1 so that when the values are 0 the result is not 0\r
 454         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 455         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 456                                 SELECT TargetIdentityID, \\r
 457                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 458                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 459                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 460                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 461                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 462                                 GROUP BY TargetIdentityID;");\r
 463 \r
 464         db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \\r
 465                                 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \\r
 466                                 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \\r
 467                                 GROUP BY tblIdentity.IdentityID;");\r
 468 \r
 469         /*\r
 470                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 471                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 472         */\r
 473         // drop existing triggers\r
 474         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 475         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 476         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 477         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 478 /*\r
 479         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 480         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 481                                 FOR EACH ROW \\r
 482                                 BEGIN \\r
 483                                         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
 484                                 END;");\r
 485 \r
 486         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 487         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 488                                 FOR EACH ROW \\r
 489                                 BEGIN \\r
 490                                         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
 491                                 END;");\r
 492 \r
 493         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 494         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 495                                 FOR EACH ROW \\r
 496                                 BEGIN \\r
 497                                         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
 498                                         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
 499                                 END;");\r
 500 \r
 501         // 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
 502         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 503                                 FOR EACH ROW \\r
 504                                 BEGIN \\r
 505                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 506                                 END;");\r
 507 */\r
 508 \r
 509         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 510                                 FOR EACH ROW \\r
 511                                 BEGIN \\r
 512                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 513                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 514                                 END;");\r
 515 \r
 516         db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
 517         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 518                                 FOR EACH ROW \\r
 519                                 BEGIN \\r
 520                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 521                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 522                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 523                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 524                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 525                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 526                                         DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
 527                                 END;");\r
 528 \r
 529         db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
 530         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 531                                 FOR EACH ROW \\r
 532                                 BEGIN \\r
 533                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 534                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 535                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 536                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 537                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 538                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 539                                         DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
 540                                 END;");\r
 541 \r
 542         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 543                                 FOR EACH ROW \\r
 544                                 BEGIN \\r
 545                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 546                                 END;");\r
 547 \r
 548         // delete introduction puzzles that were half-way inserted\r
 549         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 550 \r
 551         // delete stale introduction puzzles (2 or more days old)\r
 552         date-=Poco::Timespan(2,0,0,0,0);\r
 553         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 554         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 555 \r
 556         date=Poco::Timestamp();\r
 557         // insert SomeDude's public key\r
 558         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
 559         // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
 560         //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
 561         // insert garfield's public key -haven't seen in a while - disabling for now\r
 562         //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
 563         // insert alek's public key - haven't seen in a while - disabling for now\r
 564         //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
 565         // insert Luke771's public key\r
 566         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
 567         // insert falafel's public key\r
 568         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
 569         // insert cptn_insano's public key\r
 570         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
 571         // insert Flink's public key - haven't seen in a while - disabling for now\r
 572         //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
 573         // insert Kane's public key\r
 574         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
 575         // inserts boardstat's public key\r
 576         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
 577 \r
 578         // TODO remove sometime after 0.1.17\r
 579         FixCapitalBoardNames(db);\r
 580 \r
 581         // run analyze - may speed up some queries\r
 582         db->Execute("ANALYZE;");\r
 583 \r
 584 }\r
 585 \r
 586 const bool VerifyDB(SQLite3DB::DB *db)\r
 587 {\r
 588         SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
 589         st.Step();\r
 590         if(st.RowReturned())\r
 591         {\r
 592                 std::string res="";\r
 593                 st.ResultText(0,res);\r
 594                 if(res=="ok")\r
 595                 {\r
 596                         return true;\r
 597                 }\r
 598                 else\r
 599                 {\r
 600                         // try to reindex and vacuum database in case of index corruption\r
 601                         st=db->Prepare("REINDEX;");\r
 602                         st.Step();\r
 603                         st=db->Prepare("VACUUM;");\r
 604                         st.Step();\r
 605 \r
 606                         // check integrity again\r
 607                         st=db->Prepare("PRAGMA integrity_check;");\r
 608                         st.Step();\r
 609                         st.ResultText(0,res);\r
 610                         if(res=="ok")\r
 611                         {\r
 612                                 return true;\r
 613                         }\r
 614                         else\r
 615                         {\r
 616                                 return false;\r
 617                         }\r
 618                 }\r
 619         }\r
 620         else\r
 621         {\r
 622                 return false;\r
 623         }\r
 624 }\r
 625 \r
 626 const std::string TestDBIntegrity(SQLite3DB::DB *db)\r
 627 {\r
 628         std::string result="";\r
 629 \r
 630         SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
 631         st.Step();\r
 632         while(st.RowReturned())\r
 633         {\r
 634                 std::string text="";\r
 635                 st.ResultText(0,text);\r
 636                 result+=text;\r
 637                 st.Step();\r
 638         }\r
 639         return result;\r
 640 }\r