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