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