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