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