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