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