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