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         // low / high / message count for each board\r
 405         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 406                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 407                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 408                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 409                                 GROUP BY tblBoard.BoardID;");\r
 410 \r
 411         // calculates peer trust\r
 412         // 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
 413         // need the +1 so that when the values are 0 the result is not 0\r
 414         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 415         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 416                                 SELECT TargetIdentityID, \\r
 417                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 418                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 419                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 420                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 421                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 422                                 GROUP BY TargetIdentityID;");\r
 423 \r
 424         db->Execute("CREATE VIEW IF NOT EXISTS vwIdentityStats AS \\r
 425                                 SELECT tblIdentity.IdentityID, COUNT(tblMessage.MessageID) AS MessageCount, MIN(tblMessage.MessageDate) AS FirstMessageDate, MAX(tblMessage.MessageDate) AS LastMessageDate \\r
 426                                 FROM tblIdentity LEFT JOIN tblMessage ON tblIdentity.IdentityID=tblMessage.IdentityID \\r
 427                                 GROUP BY tblIdentity.IdentityID;");\r
 428 \r
 429         /*\r
 430                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 431                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 432         */\r
 433         // drop existing triggers\r
 434         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 435         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 436         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 437         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 438 /*\r
 439         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 440         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE 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                                 END;");\r
 445 \r
 446         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 447         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 448                                 FOR EACH ROW \\r
 449                                 BEGIN \\r
 450                                         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
 451                                 END;");\r
 452 \r
 453         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 454         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 455                                 FOR EACH ROW \\r
 456                                 BEGIN \\r
 457                                         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
 458                                         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
 459                                 END;");\r
 460 \r
 461         // 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
 462         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 463                                 FOR EACH ROW \\r
 464                                 BEGIN \\r
 465                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 466                                 END;");\r
 467 */\r
 468 \r
 469         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 470                                 FOR EACH ROW \\r
 471                                 BEGIN \\r
 472                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 473                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 474                                 END;");\r
 475 \r
 476         db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
 477         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 478                                 FOR EACH ROW \\r
 479                                 BEGIN \\r
 480                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 481                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 482                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 483                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 484                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 485                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 486                                         DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
 487                                 END;");\r
 488 \r
 489         db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
 490         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 491                                 FOR EACH ROW \\r
 492                                 BEGIN \\r
 493                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 494                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 495                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 496                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 497                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 498                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 499                                         DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
 500                                 END;");\r
 501 \r
 502         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 503                                 FOR EACH ROW \\r
 504                                 BEGIN \\r
 505                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 506                                 END;");\r
 507 \r
 508         // delete introduction puzzles that were half-way inserted\r
 509         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 510 \r
 511         // delete stale introduction puzzles (2 or more days old)\r
 512         date-=Poco::Timespan(2,0,0,0,0);\r
 513         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 514         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d")+"';");\r
 515 \r
 516         date=Poco::Timestamp();\r
 517         // insert SomeDude's public key\r
 518         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
 519         // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
 520         //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
 521         // insert garfield's public key\r
 522         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
 523         // insert alek's public key\r
 524         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
 525         // insert Luke771's public key\r
 526         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
 527         // insert falafel's public key\r
 528         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
 529         // insert cptn_insano's public key\r
 530         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
 531         // insert Flink's public key\r
 532         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
 533         // insert Kane's public key\r
 534         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
 535         // inserts boardstat's public key\r
 536         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
 537 \r
 538         // TODO remove sometime after 0.1.17\r
 539         FixCapitalBoardNames();\r
 540 \r
 541         // run analyze - may speed up some queries\r
 542         db->Execute("ANALYZE;");\r
 543 \r
 544 }\r