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         }\r
 108         else\r
 109         {\r
 110                 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,13);");\r
 111         }\r
 112 \r
 113         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");\r
 114 \r
 115         db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\\r
 116                                 Major                           INTEGER,\\r
 117                                 Minor                           INTEGER,\\r
 118                                 Release                         INTEGER,\\r
 119                                 Notes                           TEXT,\\r
 120                                 Changes                         TEXT,\\r
 121                                 PageKey                         TEXT,\\r
 122                                 SourceKey                       TEXT\\r
 123                                 );");\r
 124 \r
 125         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFMSVersion_Version ON tblFMSVersion(Major,Minor,Release);");\r
 126 \r
 127         db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
 128                                 Option                          TEXT UNIQUE,\\r
 129                                 OptionValue                     TEXT NOT NULL,\\r
 130                                 OptionDescription       TEXT,\\r
 131                                 Section                         TEXT,\\r
 132                                 SortOrder                       INTEGER,\\r
 133                                 ValidValues                     TEXT\\r
 134                                 );");\r
 135 \r
 136         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 137                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 138                                 Name                                    TEXT,\\r
 139                                 PublicKey                               TEXT UNIQUE,\\r
 140                                 PrivateKey                              TEXT UNIQUE,\\r
 141                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 142                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 143                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 144                                 PublishFreesite                 BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
 145                                 FreesiteEdition                 INTEGER,\\r
 146                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 147                                 LastInsertedIdentity    DATETIME,\\r
 148                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 149                                 LastInsertedPuzzle              DATETIME,\\r
 150                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 151                                 LastInsertedTrustList   DATETIME,\\r
 152                                 LastInsertedBoardList   DATETIME,\\r
 153                                 LastInsertedMessageList DATETIME,\\r
 154                                 LastInsertedFreesite    DATETIME,\\r
 155                                 DateCreated                             DATETIME,\\r
 156                                 MinMessageDelay                 INTEGER DEFAULT 0,\\r
 157                                 MaxMessageDelay                 INTEGER DEFAULT 0\\r
 158                                 );");\r
 159 \r
 160         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r
 161                                 LocalIdentityID         INTEGER,\\r
 162                                 Day                                     DATE,\\r
 163                                 InsertIndex                     INTEGER\\r
 164                                 );");\r
 165 \r
 166         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\\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 tblTrustListRequests(\\r
 173                                 IdentityID                      INTEGER,\\r
 174                                 Day                                     DATE,\\r
 175                                 RequestIndex            INTEGER,\\r
 176                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 177                                 );");\r
 178 \r
 179         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
 180                                 UUID                            TEXT UNIQUE,\\r
 181                                 LocalIdentityID         INTEGER,\\r
 182                                 Day                                     DATE,\\r
 183                                 InsertIndex                     INTEGER,\\r
 184                                 Type                            TEXT,\\r
 185                                 MimeType                        TEXT,\\r
 186                                 PuzzleData                      TEXT,\\r
 187                                 PuzzleSolution          TEXT,\\r
 188                                 FoundSolution           BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\\r
 189                                 );");\r
 190 \r
 191         /*\r
 192                 PurgeDate is not used yet\r
 193         */\r
 194         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 195                                 IdentityID                              INTEGER PRIMARY KEY,\\r
 196                                 PublicKey                               TEXT UNIQUE,\\r
 197                                 Name                                    TEXT,\\r
 198                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 199                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 200                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 201                                 FreesiteEdition                 INTEGER,\\r
 202                                 DateAdded                               DATETIME,\\r
 203                                 LastSeen                                DATETIME,\\r
 204                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 205                                 PeerMessageTrust                INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 206                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 207                                 PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 208                                 AddedMethod                             TEXT,\\r
 209                                 Hidden                                  BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\\r
 210                                 PurgeDate                               DATETIME\\r
 211                                 );");\r
 212 \r
 213         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
 214                                 IdentityID                      INTEGER,\\r
 215                                 Day                                     DATE,\\r
 216                                 RequestIndex            INTEGER,\\r
 217                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 218                                 );");\r
 219 \r
 220         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\\r
 221                                 IdentityID                      INTEGER,\\r
 222                                 Day                                     DATE,\\r
 223                                 RequestIndex            INTEGER,\\r
 224                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 225                                 UUID                            TEXT UNIQUE,\\r
 226                                 Type                            TEXT,\\r
 227                                 MimeType                        TEXT,\\r
 228                                 PuzzleData                      TEXT\\r
 229                                 );");\r
 230 \r
 231         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\\r
 232                                 LocalIdentityID         INTEGER,\\r
 233                                 Day                                     DATE,\\r
 234                                 UUID                            TEXT UNIQUE,\\r
 235                                 Solution                        TEXT,\\r
 236                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 237                                 );");\r
 238 \r
 239         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\\r
 240                                 LocalIdentityID                 INTEGER,\\r
 241                                 IdentityID                              INTEGER,\\r
 242                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 243                                 MessageTrustComment             TEXT,\\r
 244                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 245                                 TrustListTrustComment   TEXT\\r
 246                                 );");\r
 247 \r
 248         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");\r
 249 \r
 250         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \\r
 251                                 FOR EACH ROW \\r
 252                                 BEGIN \\r
 253                                         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
 254                                 END;");\r
 255 \r
 256         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust 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 trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \\r
 263                                 FOR EACH ROW \\r
 264                                 BEGIN \\r
 265                                         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
 266                                 END;");\r
 267 \r
 268         db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
 269                                 IdentityID                              INTEGER,\\r
 270                                 TargetIdentityID                INTEGER,\\r
 271                                 MessageTrust                    INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\\r
 272                                 TrustListTrust                  INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\\r
 273                                 MessageTrustComment             TEXT,\\r
 274                                 TrustListTrustComment   TEXT\\r
 275                                 );");\r
 276 \r
 277         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_IdentityID ON tblPeerTrust (IdentityID);");\r
 278         db->Execute("CREATE INDEX IF NOT EXISTS idxPeerTrust_TargetIdentityID ON tblPeerTrust (TargetIdentityID);");\r
 279 \r
 280         db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\\r
 281                                 BoardID                                 INTEGER PRIMARY KEY,\\r
 282                                 BoardName                               TEXT UNIQUE,\\r
 283                                 BoardDescription                TEXT,\\r
 284                                 DateAdded                               DATETIME,\\r
 285                                 SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\\r
 286                                 AddedMethod                             TEXT\\r
 287                                 );");\r
 288 \r
 289         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");\r
 290         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");\r
 291         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");\r
 292         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");\r
 293 \r
 294         db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
 295                                 MessageID                       INTEGER PRIMARY KEY,\\r
 296                                 IdentityID                      INTEGER,\\r
 297                                 FromName                        TEXT,\\r
 298                                 MessageDate                     DATE,\\r
 299                                 MessageTime                     TIME,\\r
 300                                 Subject                         TEXT,\\r
 301                                 MessageUUID                     TEXT UNIQUE,\\r
 302                                 ReplyBoardID            INTEGER,\\r
 303                                 Body                            TEXT,\\r
 304                                 MessageIndex            INTEGER\\r
 305                                 );");\r
 306 \r
 307         db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");\r
 308 \r
 309         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
 310                                 MessageID                       INTEGER,\\r
 311                                 ReplyToMessageUUID      TEXT,\\r
 312                                 ReplyOrder                      INTEGER\\r
 313                                 );");\r
 314 \r
 315         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\r
 316 \r
 317         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
 318                                 MessageID                       INTEGER,\\r
 319                                 BoardID                         INTEGER\\r
 320                                 );");\r
 321 \r
 322         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_MessageID ON tblMessageBoard (MessageID);");\r
 323         db->Execute("CREATE INDEX IF NOT EXISTS idxMessageBoard_BoardID ON tblMessageBoard (BoardID);");\r
 324 \r
 325         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\\r
 326                                 IdentityID                      INTEGER,\\r
 327                                 Day                                     DATE,\\r
 328                                 RequestIndex            INTEGER,\\r
 329                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 330                                 );");\r
 331 \r
 332         /*\r
 333                 Key is for anonymous messages (future)\r
 334         */\r
 335         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\\r
 336                                 IdentityID                      INTEGER,\\r
 337                                 Day                                     DATE,\\r
 338                                 RequestIndex            INTEGER,\\r
 339                                 FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
 340                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 341                                 Tries                           INTEGER DEFAULT 0,\\r
 342                                 Key                                     TEXT\\r
 343                                 );");\r
 344 \r
 345         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 346 \r
 347         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\\r
 348                                 LocalIdentityID         INTEGER,\\r
 349                                 Day                                     DATE,\\r
 350                                 InsertIndex                     INTEGER,\\r
 351                                 MessageUUID                     TEXT UNIQUE,\\r
 352                                 MessageXML                      TEXT,\\r
 353                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false',\\r
 354                                 SendDate                        DATETIME\\r
 355                                 );");\r
 356 \r
 357         db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
 358                                 FileInsertID            INTEGER PRIMARY KEY,\\r
 359                                 MessageUUID                     TEXT,\\r
 360                                 FileName                        TEXT,\\r
 361                                 Key                                     TEXT,\\r
 362                                 Size                            INTEGER,\\r
 363                                 MimeType                        TEXT,\\r
 364                                 Data                            BLOB\\r
 365                                 );");\r
 366 \r
 367         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
 368                                 LocalIdentityID         INTEGER,\\r
 369                                 Day                                     DATE,\\r
 370                                 InsertIndex                     INTEGER,\\r
 371                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 372                                 );");\r
 373 \r
 374         db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\\r
 375                                 BoardID                                         INTEGER UNIQUE,\\r
 376                                 ModifyLocalMessageTrust         INTEGER,\\r
 377                                 ModifyLocalTrustListTrust       INTEGER\\r
 378                                 );");\r
 379 \r
 380         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\\r
 381                                 LocalIdentityID         INTEGER,\\r
 382                                 Day                                     DATE,\\r
 383                                 InsertIndex                     INTEGER,\\r
 384                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 385                                 );");\r
 386 \r
 387         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\\r
 388                                 IdentityID                      INTEGER,\\r
 389                                 Day                                     DATE,\\r
 390                                 RequestIndex            INTEGER,\\r
 391                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 392                                 );");   \r
 393 \r
 394         // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed\r
 395         db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\\r
 396                                 LocalIdentityID         INTEGER,\\r
 397                                 Date                            DATETIME\\r
 398                                 );");\r
 399 \r
 400         // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page\r
 401         db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\\r
 402                                 LocalIdentityID         INTEGER\\r
 403                                 );");\r
 404 \r
 405         // Temporary table for form passwords\r
 406         db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpFormPassword(\\r
 407                                 Date                    DATETIME,\\r
 408                                 Password                TEXT\\r
 409                                 );");\r
 410 \r
 411         // low / high / message count for each board\r
 412         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 413                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 414                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 415                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 416                                 GROUP BY tblBoard.BoardID;");\r
 417 \r
 418         // calculates peer trust\r
 419         // 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
 420         // need the +1 so that when the values are 0 the result is not 0\r
 421         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 422         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 423                                 SELECT TargetIdentityID, \\r
 424                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 425                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 426                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 427                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 428                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 429                                 GROUP BY TargetIdentityID;");\r
 430 \r
 431         db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \\r
 432                                 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \\r
 433                                 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \\r
 434                                 GROUP BY tblIdentity.IdentityID;");\r
 435 \r
 436         /*\r
 437                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 438                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 439         */\r
 440         // drop existing triggers\r
 441         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 442         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 443         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 444         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 445 /*\r
 446         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 447         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 448                                 FOR EACH ROW \\r
 449                                 BEGIN \\r
 450                                         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
 451                                 END;");\r
 452 \r
 453         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 454         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 455                                 FOR EACH ROW \\r
 456                                 BEGIN \\r
 457                                         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
 458                                 END;");\r
 459 \r
 460         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 461         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 462                                 FOR EACH ROW \\r
 463                                 BEGIN \\r
 464                                         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
 465                                         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
 466                                 END;");\r
 467 \r
 468         // 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
 469         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 470                                 FOR EACH ROW \\r
 471                                 BEGIN \\r
 472                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 473                                 END;");\r
 474 */\r
 475 \r
 476         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 477                                 FOR EACH ROW \\r
 478                                 BEGIN \\r
 479                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 480                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 481                                 END;");\r
 482 \r
 483         db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
 484         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 485                                 FOR EACH ROW \\r
 486                                 BEGIN \\r
 487                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 488                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 489                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 490                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 491                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 492                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 493                                         DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
 494                                 END;");\r
 495 \r
 496         db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
 497         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 498                                 FOR EACH ROW \\r
 499                                 BEGIN \\r
 500                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 501                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 502                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 503                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 504                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 505                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 506                                         DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
 507                                 END;");\r
 508 \r
 509         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 510                                 FOR EACH ROW \\r
 511                                 BEGIN \\r
 512                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 513                                 END;");\r
 514 \r
 515         // delete introduction puzzles that were half-way inserted\r
 516         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 517 \r
 518         // delete stale introduction puzzles (2 or more days old)\r
 519         date-=Poco::Timespan(2,0,0,0,0);\r
 520         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 521         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 522 \r
 523         date=Poco::Timestamp();\r
 524         // insert SomeDude's public key\r
 525         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
 526         // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
 527         //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
 528         // insert garfield's public key\r
 529         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
 530         // insert alek's public key\r
 531         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
 532         // insert Luke771's public key\r
 533         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
 534         // insert falafel's public key\r
 535         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
 536         // insert cptn_insano's public key\r
 537         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
 538         // insert Flink's public key\r
 539         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
 540         // insert Kane's public key\r
 541         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
 542         // inserts boardstat's public key\r
 543         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
 544 \r
 545         // TODO remove sometime after 0.1.17\r
 546         FixCapitalBoardNames();\r
 547 \r
 548         // run analyze - may speed up some queries\r
 549         db->Execute("ANALYZE;");\r
 550 \r
 551 }\r