src/global.cpp

   1 #include "../include/global.h"\r
   2 #include "../include/datetime.h"\r
   3 #include "../include/logfile.h"\r
   4 #include "../include/option.h"\r
   5 #include "../include/stringfunctions.h"\r
   6 #include "../include/db/sqlite3db.h"\r
   7 #include "../include/freenet/freenetmasterthread.h"\r
   8 #include "../include/nntp/nntplistener.h"\r
   9 #include "../include/http/httpthread.h"\r
  10 #include "../include/threadcontroller.h"\r
  11 \r
  12 #ifdef _WIN32\r
  13         #include <winsock2.h>\r
  14 #endif\r
  15 \r
  16 #ifdef XMEM\r
  17         #include <xmem.h>\r
  18 #endif\r
  19 \r
  20 bool wantshutdown=false;\r
  21 \r
  22 void SetupDB()\r
  23 {\r
  24 \r
  25         DateTime date;\r
  26         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  27 \r
  28         db->Open("fms.db3");\r
  29         db->SetBusyTimeout(10000);              // set timeout to 10 seconds\r
  30         db->Execute("VACUUM;");\r
  31 \r
  32         db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
  33                                 Major                           INTEGER,\\r
  34                                 Minor                           INTEGER\\r
  35                                 );");\r
  36 \r
  37         SQLite3DB::Statement st=db->Prepare("SELECT Major,Minor FROM tblDBVersion;");\r
  38         st.Step();\r
  39         if(st.RowReturned())\r
  40         {\r
  41                 int major;\r
  42                 int minor;\r
  43                 st.ResultInt(0,major);\r
  44                 st.ResultInt(1,minor);\r
  45                 st.Finalize();\r
  46                 if(major==1 && minor==0)\r
  47                 {\r
  48                         ConvertDB0100To0101();\r
  49                         major=1;\r
  50                         minor=1;\r
  51                 }\r
  52                 if(major==1 && (minor==1 || minor==2))\r
  53                 {\r
  54                         ConvertDB0101To0103();\r
  55                         major=1;\r
  56                         minor=3;\r
  57                 }\r
  58                 if(major==1 && minor==3)\r
  59                 {\r
  60                         ConvertDB0103To0104();\r
  61                         major=1;\r
  62                         minor=4;\r
  63                 }\r
  64                 if(major==1 && minor==4)\r
  65                 {\r
  66                         ConvertDB0104To0105();\r
  67                         major=1;\r
  68                         minor=5;\r
  69                 }\r
  70         }\r
  71         else\r
  72         {\r
  73                 db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,5);");\r
  74         }\r
  75 \r
  76         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
  77 \r
  78         db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
  79                                 Option                          TEXT UNIQUE,\\r
  80                                 OptionValue                     TEXT NOT NULL,\\r
  81                                 OptionDescription       TEXT\\r
  82                                 );");\r
  83 \r
  84         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
  85                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
  86                                 Name                                    TEXT,\\r
  87                                 PublicKey                               TEXT UNIQUE,\\r
  88                                 PrivateKey                              TEXT UNIQUE,\\r
  89                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
  90                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
  91                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
  92                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
  93                                 LastInsertedIdentity    DATETIME,\\r
  94                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
  95                                 LastInsertedPuzzle              DATETIME,\\r
  96                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
  97                                 LastInsertedTrustList   DATETIME,\\r
  98                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
  99                                 LastInsertedBoardList   DATETIME,\\r
 100                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
 101                                 LastInsertedMessageList DATETIME,\\r
 102                                 DateCreated                             DATETIME\\r
 103                                 );");\r
 104 \r
 105         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\\r
 106                                 LocalIdentityID         INTEGER,\\r
 107                                 Day                                     DATE,\\r
 108                                 InsertIndex                     INTEGER\\r
 109                                 );");\r
 110 \r
 111         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListInserts(\\r
 112                                 LocalIdentityID         INTEGER,\\r
 113                                 Day                                     DATE,\\r
 114                                 InsertIndex                     INTEGER\\r
 115                                 );");\r
 116 \r
 117         db->Execute("CREATE TABLE IF NOT EXISTS tblTrustListRequests(\\r
 118                                 IdentityID                      INTEGER,\\r
 119                                 Day                                     DATE,\\r
 120                                 RequestIndex            INTEGER,\\r
 121                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 122                                 );");\r
 123 \r
 124         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleInserts(\\r
 125                                 UUID                            TEXT UNIQUE,\\r
 126                                 LocalIdentityID         INTEGER,\\r
 127                                 Day                                     DATE,\\r
 128                                 InsertIndex                     INTEGER,\\r
 129                                 Type                            TEXT,\\r
 130                                 MimeType                        TEXT,\\r
 131                                 PuzzleData                      TEXT,\\r
 132                                 PuzzleSolution          TEXT,\\r
 133                                 FoundSolution           BOOL CHECK(FoundSolution IN('true','false')) DEFAULT 'false'\\r
 134                                 );");\r
 135 \r
 136         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 137                                 IdentityID                              INTEGER PRIMARY KEY,\\r
 138                                 PublicKey                               TEXT UNIQUE,\\r
 139                                 Name                                    TEXT,\\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                                 DateAdded                               DATETIME,\\r
 144                                 LastSeen                                DATETIME,\\r
 145                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 146                                 PeerMessageTrust                INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 147                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 148                                 PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 149                                 AddedMethod                             TEXT,\\r
 150                                 MessageTrustComment             TEXT,\\r
 151                                 TrustListTrustComment   TEXT\\r
 152                                 );");\r
 153 \r
 154         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
 155                                 IdentityID                      INTEGER,\\r
 156                                 Day                                     DATE,\\r
 157                                 RequestIndex            INTEGER,\\r
 158                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 159                                 );");\r
 160 \r
 161         db->Execute("CREATE TABLE IF NOT EXISTS tblIntroductionPuzzleRequests(\\r
 162                                 IdentityID                      INTEGER,\\r
 163                                 Day                                     DATE,\\r
 164                                 RequestIndex            INTEGER,\\r
 165                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
 166                                 UUID                            TEXT UNIQUE,\\r
 167                                 Type                            TEXT,\\r
 168                                 MimeType                        TEXT,\\r
 169                                 PuzzleData                      TEXT\\r
 170                                 );");\r
 171 \r
 172         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityIntroductionInserts(\\r
 173                                 LocalIdentityID         INTEGER,\\r
 174                                 Day                                     DATE,\\r
 175                                 UUID                            TEXT UNIQUE,\\r
 176                                 Solution                        TEXT,\\r
 177                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 178                                 );");\r
 179 \r
 180         db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
 181                                 IdentityID                              INTEGER,\\r
 182                                 TargetIdentityID                INTEGER,\\r
 183                                 MessageTrust                    INTEGER CHECK(MessageTrust BETWEEN 0 AND 100),\\r
 184                                 TrustListTrust                  INTEGER CHECK(TrustListTrust BETWEEN 0 AND 100),\\r
 185                                 MessageTrustComment             TEXT,\\r
 186                                 TrustListTrustComment   TEXT\\r
 187                                 );");\r
 188 \r
 189         db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\\r
 190                                 BoardID                                 INTEGER PRIMARY KEY,\\r
 191                                 BoardName                               TEXT UNIQUE,\\r
 192                                 BoardDescription                TEXT,\\r
 193                                 DateAdded                               DATETIME,\\r
 194                                 SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true'\\r
 195                                 );");\r
 196 \r
 197         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01 12:00:00');");\r
 198         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00');");\r
 199         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('public','Public discussion','2007-12-01 12:00:00');");\r
 200         db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('test','Test board','2007-12-01 12:00:00');");\r
 201 \r
 202         db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
 203                                 MessageID                       INTEGER PRIMARY KEY,\\r
 204                                 IdentityID                      INTEGER,\\r
 205                                 FromName                        TEXT,\\r
 206                                 MessageDate                     DATE,\\r
 207                                 MessageTime                     TIME,\\r
 208                                 Subject                         TEXT,\\r
 209                                 MessageUUID                     TEXT UNIQUE,\\r
 210                                 ReplyBoardID            INTEGER,\\r
 211                                 Body                            TEXT,\\r
 212                                 MessageIndex            INTEGER\\r
 213                                 );");\r
 214 \r
 215         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\\r
 216                                 MessageID                       INTEGER,\\r
 217                                 ReplyToMessageUUID      TEXT,\\r
 218                                 ReplyOrder                      INTEGER\\r
 219                                 );");\r
 220 \r
 221         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
 222                                 MessageID                       INTEGER,\\r
 223                                 BoardID                         INTEGER\\r
 224                                 );");\r
 225 \r
 226         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListRequests(\\r
 227                                 IdentityID                      INTEGER,\\r
 228                                 Day                                     DATE,\\r
 229                                 RequestIndex            INTEGER,\\r
 230                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 231                                 );");\r
 232 \r
 233         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageRequests(\\r
 234                                 IdentityID                      INTEGER,\\r
 235                                 Day                                     DATE,\\r
 236                                 RequestIndex            INTEGER,\\r
 237                                 FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
 238                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 239                                 );");\r
 240 \r
 241         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 242 \r
 243         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\\r
 244                                 LocalIdentityID         INTEGER,\\r
 245                                 Day                                     DATE,\\r
 246                                 InsertIndex                     INTEGER,\\r
 247                                 MessageUUID                     TEXT UNIQUE,\\r
 248                                 MessageXML                      TEXT,\\r
 249                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 250                                 );");\r
 251 \r
 252         db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
 253                                 LocalIdentityID         INTEGER,\\r
 254                                 Day                                     DATE,\\r
 255                                 InsertIndex                     INTEGER,\\r
 256                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 257                                 );");\r
 258 \r
 259         db->Execute("CREATE TABLE IF NOT EXISTS tblAdministrationBoard(\\r
 260                                 BoardID                                         INTEGER UNIQUE,\\r
 261                                 ModifyLocalMessageTrust         INTEGER,\\r
 262                                 ModifyLocalTrustListTrust       INTEGER\\r
 263                                 );");\r
 264 \r
 265         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListInserts(\\r
 266                                 LocalIdentityID         INTEGER,\\r
 267                                 Day                                     DATE,\\r
 268                                 InsertIndex                     INTEGER,\\r
 269                                 Inserted                        BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
 270                                 );");\r
 271 \r
 272         db->Execute("CREATE TABLE IF NOT EXISTS tblBoardListRequests(\\r
 273                                 IdentityID                      INTEGER,\\r
 274                                 Day                                     DATE,\\r
 275                                 RequestIndex            INTEGER,\\r
 276                                 Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
 277                                 );");   \r
 278 \r
 279         // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed\r
 280         db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\\r
 281                                 LocalIdentityID         INTEGER,\\r
 282                                 Date                            DATETIME\\r
 283                                 );");\r
 284 \r
 285         // low / high / message count for each board\r
 286         db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
 287                                 SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
 288                                 FROM tblBoard LEFT JOIN tblMessageBoard ON tblBoard.BoardID=tblMessageBoard.BoardID \\r
 289                                 WHERE MessageID>=0 OR MessageID IS NULL \\r
 290                                 GROUP BY tblBoard.BoardID;");\r
 291 \r
 292         // calculates peer trust\r
 293         // do the (MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1)/100.0 - so it MessageTrust or TrustListTrust is NULL, the calc will be NULL and it won't be included at all in the average\r
 294         // need the +1 so that when the values are 0 the result is not 0\r
 295         db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;");\r
 296         db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \\r
 297                                 SELECT TargetIdentityID, \\r
 298                                 ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \\r
 299                                 ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \\r
 300                                 FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \\r
 301                                 WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \\r
 302                                 AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \\r
 303                                 GROUP BY TargetIdentityID;");\r
 304 \r
 305         /*\r
 306                 These peer trust calculations are too CPU intensive to be triggers - they were called every time a new trust list was processed\r
 307                 All trust levels will now be recalculated every hour in the PeriodicDBMaintenance class\r
 308         */\r
 309         // drop existing triggers\r
 310         db->Execute("DROP TRIGGER IF EXISTS trgDeleteOntblPeerTrust;");\r
 311         db->Execute("DROP TRIGGER IF EXISTS trgInsertOntblPeerTrust;");\r
 312         db->Execute("DROP TRIGGER IF EXISTS trgUpdateOntblPeerTrust;");\r
 313         db->Execute("DROP TRIGGER IF EXISTS trgUpdateLocalTrustLevels;");\r
 314 /*\r
 315         // update PeerTrustLevel when deleting a record from tblPeerTrust\r
 316         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOntblPeerTrust AFTER DELETE ON tblPeerTrust \\r
 317                                 FOR EACH ROW \\r
 318                                 BEGIN \\r
 319                                         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
 320                                 END;");\r
 321 \r
 322         // update PeerTrustLevel when inserting a record into tblPeerTrust\r
 323         db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOntblPeerTrust AFTER INSERT ON tblPeerTrust \\r
 324                                 FOR EACH ROW \\r
 325                                 BEGIN \\r
 326                                         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
 327                                 END;");\r
 328 \r
 329         // update PeerTrustLevel when updating a record in tblPeerTrust\r
 330         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOntblPeerTrust AFTER UPDATE ON tblPeerTrust \\r
 331                                 FOR EACH ROW \\r
 332                                 BEGIN \\r
 333                                         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
 334                                         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
 335                                 END;");\r
 336 \r
 337         // 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
 338         db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateLocalTrustLevels AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentity \\r
 339                                 FOR EACH ROW \\r
 340                                 BEGIN \\r
 341                                         UPDATE tblIdentity SET PeerMessageTrust=(SELECT PeerMessageTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID), PeerTrustListTrust=(SELECT PeerTrustListTrust FROM vwCalculatedPeerTrust WHERE TargetIdentityID=IdentityID);\\r
 342                                 END;");\r
 343 */\r
 344 \r
 345         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteMessage AFTER DELETE ON tblMessage \\r
 346                                 FOR EACH ROW \\r
 347                                 BEGIN \\r
 348                                         DELETE FROM tblMessageBoard WHERE tblMessageBoard.MessageID=old.MessageID;\\r
 349                                         DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
 350                                 END;");\r
 351 \r
 352         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
 353                                 FOR EACH ROW \\r
 354                                 BEGIN \\r
 355                                         DELETE FROM tblIdentityRequests WHERE IdentityID=old.IdentityID;\\r
 356                                         DELETE FROM tblIntroductionPuzzleRequests WHERE IdentityID=old.IdentityID;\\r
 357                                         DELETE FROM tblMessageListRequests WHERE IdentityID=old.IdentityID;\\r
 358                                         DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
 359                                         DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
 360                                         DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
 361                                 END;");\r
 362 \r
 363         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
 364                                 FOR EACH ROW \\r
 365                                 BEGIN \\r
 366                                         DELETE FROM tblIdentityIntroductionInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 367                                         DELETE FROM tblIntroductionPuzzleInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 368                                         DELETE FROM tblLocalIdentityInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 369                                         DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 370                                         DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 371                                         DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
 372                                 END;");\r
 373 \r
 374         db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\r
 375                                 FOR EACH ROW \\r
 376                                 BEGIN \\r
 377                                         DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\\r
 378                                 END;");\r
 379 \r
 380         // delete introduction puzzles that were half-way inserted\r
 381         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;");\r
 382 \r
 383         // delete stale introduction puzzles (2 or more days old)\r
 384         date.SetToGMTime();\r
 385         date.Add(0,0,0,-2);\r
 386         db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 387         db->Execute("DELETE FROM tblIntroductionPuzzleRequests WHERE Day<='"+date.Format("%Y-%m-%d")+"';");\r
 388 \r
 389         date.SetToGMTime();\r
 390         // insert SomeDude's public key\r
 391         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"',51);");\r
 392         // insert Shadow Panther's public key\r
 393         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");\r
 394         // insert garfield's public key\r
 395         db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');");\r
 396 \r
 397         // TODO remove sometime after 0.1.17\r
 398         FixCapitalBoardNames();\r
 399 \r
 400 }\r
 401 \r
 402 void ConvertDB0100To0101()\r
 403 {\r
 404         // added unique constraint to public and private key\r
 405         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 406         db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
 407         db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
 408         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 409                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 410                                 Name                                    TEXT,\\r
 411                                 PublicKey                               TEXT UNIQUE,\\r
 412                                 PrivateKey                              TEXT UNIQUE,\\r
 413                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 414                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 415                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 416                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 417                                 LastInsertedIdentity    DATETIME,\\r
 418                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 419                                 LastInsertedPuzzle              DATETIME,\\r
 420                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 421                                 LastInsertedTrustList   DATETIME,\\r
 422                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
 423                                 LastInsertedBoardList   DATETIME,\\r
 424                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
 425                                 LastInsertedMessageList DATETIME\\r
 426                                 );");\r
 427         db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");\r
 428         db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
 429         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");\r
 430 }\r
 431 \r
 432 void ConvertDB0101To0103()\r
 433 {\r
 434         // remove default 50 from trust fields and set default to NULL\r
 435         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 436         db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
 437         db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
 438         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 439                                 IdentityID                      INTEGER PRIMARY KEY,\\r
 440                                 PublicKey                       TEXT UNIQUE,\\r
 441                                 Name                            TEXT,\\r
 442                                 SingleUse                       BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 443                                 PublishTrustList        BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 444                                 PublishBoardList        BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 445                                 DateAdded                       DATETIME,\\r
 446                                 LastSeen                        DATETIME,\\r
 447                                 LocalMessageTrust       INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 448                                 PeerMessageTrust        INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 449                                 LocalTrustListTrust     INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 450                                 PeerTrustListTrust      INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\\r
 451                                 );");\r
 452         db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");\r
 453         db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
 454 \r
 455         // add SaveReceivedMessages field to tblBoard\r
 456         db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages       BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");\r
 457 \r
 458         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
 459 }\r
 460 \r
 461 void ConvertDB0103To0104()\r
 462 {\r
 463         // add MessageIndex to tblMessage\r
 464         DateTime date;\r
 465         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 466         db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex     INTEGER;");\r
 467         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
 468         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");\r
 469         date.SetToGMTime();\r
 470         db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");\r
 471         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
 472 }\r
 473 \r
 474 void ConvertDB0104To0105()\r
 475 {\r
 476         // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity\r
 477         // add MessageTrustComment,TrustListTrustComment to tblPeerTrust\r
 478         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 479         db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");\r
 480         db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");\r
 481         db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");\r
 482         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");\r
 483         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");\r
 484         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");\r
 485 }\r
 486 \r
 487 void SetupDefaultOptions()\r
 488 {\r
 489         // OptionValue should always be inserted as a string, even if the option really isn't a string - just to keep the field data type consistent\r
 490 \r
 491         std::ostringstream tempstr;     // must set tempstr to "" between db inserts\r
 492         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 493         SQLite3DB::Statement st=db->Prepare("INSERT INTO tblOption(Option,OptionValue,OptionDescription) VALUES(?,?,?);");\r
 494 \r
 495         // LogLevel\r
 496         tempstr.str("");\r
 497         tempstr << LogFile::LOGLEVEL_DEBUG;\r
 498         st.Bind(0,"LogLevel");\r
 499         st.Bind(1,tempstr.str());\r
 500         st.Bind(2,"The maximum logging level that will be written to file.  0=Fatal Errors, 1=Errors, 2=Warnings, 3=Informational Messages, 4=Debug Messages.  Higher levels will include all messages from the previous levels.");\r
 501         st.Step();\r
 502         st.Reset();\r
 503 \r
 504         // NNTPListenPort\r
 505         st.Bind(0,"NNTPListenPort");\r
 506         st.Bind(1,"1119");\r
 507         st.Bind(2,"The port that the NNTP service will listen for incoming connections.");\r
 508         st.Step();\r
 509         st.Reset();\r
 510 \r
 511         // NNTPBindAddresses\r
 512         st.Bind(0,"NNTPBindAddresses");\r
 513         st.Bind(1,"localhost");\r
 514         st.Bind(2,"A comma separated list of valid IPv4 or IPv6 addresses/hostnames that the NNTP service will try to bind to.");\r
 515         st.Step();\r
 516         st.Reset();\r
 517 \r
 518         st.Bind(0,"NNTPAllowPost");\r
 519         st.Bind(1,"true");\r
 520         st.Bind(2,"Allow posting messages from NNTP.  Setting to false will make the newsgroups read only.");\r
 521         st.Step();\r
 522         st.Reset();\r
 523 \r
 524         // StartNNTP\r
 525         st.Bind(0,"StartNNTP");\r
 526         st.Bind(1,"true");\r
 527         st.Bind(2,"Start NNTP server.");\r
 528         st.Step();\r
 529         st.Reset();\r
 530 \r
 531         st.Bind(0,"StartHTTP");\r
 532         st.Bind(1,"true");\r
 533         st.Bind(2,"Start HTTP server.");\r
 534         st.Step();\r
 535         st.Reset();\r
 536 \r
 537         st.Bind(0,"HTTPListenPort");\r
 538         st.Bind(1,"8080");\r
 539         st.Bind(2,"Port HTTP server will listen on.");\r
 540         st.Step();\r
 541         st.Reset();\r
 542 \r
 543         st.Bind(0,"HTTPAccessControl");\r
 544         st.Bind(1,"-0.0.0.0/0,+127.0.0.1");\r
 545         st.Bind(2,"Comma separated list of addresses and/or subnet masks that are allowed access to the administration pages.  Default is localhost only. + allows a host, - denies as host.");\r
 546         st.Step();\r
 547         st.Reset();\r
 548 \r
 549         // StartFreenetUpdater\r
 550         st.Bind(0,"StartFreenetUpdater");\r
 551         st.Bind(1,"true");\r
 552         st.Bind(2,"Start Freenet Updater thread.");\r
 553         st.Step();\r
 554         st.Reset();\r
 555 \r
 556         // FCPHost\r
 557         st.Bind(0,"FCPHost");\r
 558         st.Bind(1,"127.0.0.1");\r
 559         st.Bind(2,"Host name or address of Freenet node.");\r
 560         st.Step();\r
 561         st.Reset();\r
 562 \r
 563         // FCPPort\r
 564         st.Bind(0,"FCPPort");\r
 565         st.Bind(1,"9481");\r
 566         st.Bind(2,"The port that Freenet is listening for FCP connections on.");\r
 567         st.Step();\r
 568         st.Reset();\r
 569 \r
 570         st.Bind(0,"MessageBase");\r
 571         st.Bind(1,"fms");\r
 572         st.Bind(2,"A unique string shared by all clients who want to communicate with each other.  This should not be changed unless you want to create your own separate communications network.");\r
 573         st.Step();\r
 574         st.Reset();\r
 575 \r
 576         st.Bind(0,"MaxIdentityRequests");\r
 577         st.Bind(1,"5");\r
 578         st.Bind(2,"Maximum number of concurrent requests for new Identity xml files");\r
 579         st.Step();\r
 580         st.Reset();\r
 581 \r
 582         st.Bind(0,"MaxIdentityIntroductionRequests");\r
 583         st.Bind(1,"5");\r
 584         st.Bind(2,"Maximum number of concurrent identities requesting IdentityIntroduction xml files.  Each identity may have multiple requests pending.");\r
 585         st.Step();\r
 586         st.Reset();\r
 587 \r
 588         st.Bind(0,"MaxIntroductionPuzzleRequests");\r
 589         st.Bind(1,"5");\r
 590         st.Bind(2,"Maximum number of concurrent requests for new IntroductionPuzzle xml files");\r
 591         st.Step();\r
 592         st.Reset();\r
 593 \r
 594         st.Bind(0,"MaxTrustListRequests");\r
 595         st.Bind(1,"5");\r
 596         st.Bind(2,"Maximum number of concurrent requests for new Trust Lists");\r
 597         st.Step();\r
 598         st.Reset();\r
 599 \r
 600         st.Bind(0,"MaxMessageListRequests");\r
 601         st.Bind(1,"5");\r
 602         st.Bind(2,"Maximum number of concurrent requests for new Message Lists");\r
 603         st.Step();\r
 604         st.Reset();\r
 605 \r
 606         st.Bind(0,"MaxMessageRequests");\r
 607         st.Bind(1,"20");\r
 608         st.Bind(2,"Maximum number of concurrent requests for new Messages");\r
 609         st.Step();\r
 610         st.Reset();\r
 611 \r
 612         st.Bind(0,"MinLocalMessageTrust");\r
 613         st.Bind(1,"50");\r
 614         st.Bind(2,"Specifies a local message trust level that a peer must have before its messages will be downloaded.");\r
 615         st.Step();\r
 616         st.Reset();\r
 617 \r
 618         st.Bind(0,"MinPeerMessageTrust");\r
 619         st.Bind(1,"30");\r
 620         st.Bind(2,"Specifies a peer message trust level that a peer must have before its messages will be downloaded.");\r
 621         st.Step();\r
 622         st.Reset();\r
 623 \r
 624         st.Bind(0,"MinLocalTrustListTrust");\r
 625         st.Bind(1,"51");\r
 626         st.Bind(2,"Specifies a local trust list trust level that a peer must have before its trust list will be included in the weighted average.  Any peers below this number will be excluded from the results.");\r
 627         st.Step();\r
 628         st.Reset();\r
 629 \r
 630         st.Bind(0,"MinPeerTrustListTrust");\r
 631         st.Bind(1,"30");\r
 632         st.Bind(2,"Specifies a peer trust list trust level that a peer must have before its trust list will be included in the weighted average.  Any peers below this number will be excluded from the results.");\r
 633         st.Step();\r
 634         st.Reset();\r
 635 \r
 636         st.Bind(0,"MessageDownloadMaxDaysBackward");\r
 637         st.Bind(1,"5");\r
 638         st.Bind(2,"The maximum number of days backward that messages will be downloaded from each identity");\r
 639         st.Step();\r
 640         st.Reset();\r
 641 \r
 642         st.Bind(0,"MessageListDaysBackward");\r
 643         st.Bind(1,"5");\r
 644         st.Bind(2,"The number of days backward that messages you have inserted will appear in your MessageLists");\r
 645         st.Step();\r
 646         st.Reset();\r
 647 \r
 648         st.Bind(0,"MaxPeerMessagesPerDay");\r
 649         st.Bind(1,"200");\r
 650         st.Bind(2,"The maximum number of messages you will download from each peer on a given day.");\r
 651         st.Step();\r
 652         st.Reset();\r
 653 \r
 654         st.Bind(0,"MaxBoardListRequests");\r
 655         st.Bind(1,"5");\r
 656         st.Bind(2,"The maximum number of concurrent requests for new Board Lists.  Set to 0 to disable.");\r
 657         st.Step();\r
 658         st.Reset();\r
 659 \r
 660         st.Bind(0,"MaxBoardsPerMessage");\r
 661         st.Bind(1,"8");\r
 662         st.Bind(2,"The maximum number of boards a received message may be sent to.  Boards over this limit will be ignored.");\r
 663         st.Step();\r
 664         st.Reset();\r
 665 \r
 666         st.Bind(0,"ChangeMessageTrustOnReply");\r
 667         st.Bind(1,"0");\r
 668         st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages.");\r
 669         st.Step();\r
 670         st.Reset();\r
 671 \r
 672         st.Bind(0,"AddNewPostFromIdentities");\r
 673         st.Bind(1,"false");\r
 674         st.Bind(2,"Set to true to automatically create new identities when you send a message using a new name.  If you set this to false, posting messages will fail until you manually create the identity.");\r
 675         st.Step();\r
 676         st.Reset();\r
 677 \r
 678         st.Bind(0,"DeleteMessagesOlderThan");\r
 679         st.Bind(1,"180");\r
 680         st.Bind(2,"Automatically delete messages older than this many days.");\r
 681         st.Step();\r
 682         st.Reset();\r
 683 \r
 684 }\r
 685 \r
 686 void SetupLogFile()\r
 687 {\r
 688         DateTime date;\r
 689         std::string configval;\r
 690         int loglevel;\r
 691 \r
 692         date.SetToGMTime();\r
 693 \r
 694         LogFile::Instance()->SetFileName("fms-"+date.Format("%Y-%m-%d")+".log");\r
 695         LogFile::Instance()->OpenFile();\r
 696         LogFile::Instance()->SetWriteNewLine(true);\r
 697         LogFile::Instance()->SetWriteDate(true);\r
 698         LogFile::Instance()->SetWriteLogLevel(true);\r
 699 \r
 700         if(Option::Instance()->Get("LogLevel",configval)==false)\r
 701         {\r
 702                 configval="4";\r
 703                 Option::Instance()->Set("LogLevel",configval);\r
 704         }\r
 705         if(StringFunctions::Convert(configval,loglevel)==false)\r
 706         {\r
 707                 loglevel=LogFile::LOGLEVEL_DEBUG;\r
 708                 Option::Instance()->Set("LogLevel",loglevel);\r
 709         }\r
 710         LogFile::Instance()->SetLogLevel((LogFile::LogLevel)loglevel);\r
 711 }\r
 712 \r
 713 void SetupNetwork()\r
 714 {\r
 715 #ifdef _WIN32\r
 716         WSAData wsadata;\r
 717         WSAStartup(MAKEWORD(2,2),&wsadata);\r
 718 #endif\r
 719 }\r
 720 \r
 721 void Shutdown()\r
 722 {\r
 723         ThreadController::Instance()->ShutdownThreads();\r
 724 \r
 725         ShutdownNetwork();\r
 726 \r
 727         LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_INFO,"FMS shutdown");\r
 728         LogFile::Instance()->WriteNewLine();\r
 729 }\r
 730 \r
 731 void ShutdownNetwork()\r
 732 {\r
 733 #ifdef _WIN32\r
 734         WSACleanup();\r
 735 #endif\r
 736 }\r
 737 \r
 738 void SigHandler(int signum)\r
 739 {\r
 740         Shutdown();\r
 741         exit(0);\r
 742 }\r
 743 \r
 744 /*\r
 745 void ShutdownThreads(std::vector<PThread::Thread *> &threads)\r
 746 {\r
 747         std::vector<PThread::Thread *>::iterator i;\r
 748         for(i=threads.begin(); i!=threads.end(); i++)\r
 749         {\r
 750                 (*i)->Cancel();\r
 751         }\r
 752 \r
 753         for(i=threads.begin(); i!=threads.end(); i++)\r
 754         {\r
 755                 LogFile::Instance()->WriteLog(LogFile::LOGLEVEL_DEBUG,"ShutdownThreads waiting for thread to exit.");\r
 756                 //(*i)->wait();\r
 757                 (*i)->Join();\r
 758                 delete (*i);\r
 759         }\r
 760 \r
 761         threads.clear();\r
 762 \r
 763 }\r
 764 \r
 765 void StartThreads(std::vector<PThread::Thread *> &threads)\r
 766 {\r
 767         std::string startfreenet;\r
 768         std::string startnntp;\r
 769         std::string starthttp;\r
 770 \r
 771         if(Option::Instance()->Get("StartFreenetUpdater",startfreenet)==false)\r
 772         {\r
 773                 startfreenet="true";\r
 774                 Option::Instance()->Set("StartFreenetUpdater","true");\r
 775         }\r
 776 \r
 777         if(Option::Instance()->Get("StartNNTP",startnntp)==false)\r
 778         {\r
 779                 startnntp="true";\r
 780                 Option::Instance()->Set("StartNNTP","true");\r
 781         }\r
 782 \r
 783         if(Option::Instance()->Get("StartHTTP",starthttp)==false)\r
 784         {\r
 785                 starthttp="true";\r
 786                 Option::Instance()->Set("StartHTTP","true");\r
 787         }\r
 788 \r
 789         if(startfreenet=="true")\r
 790         {\r
 791                 PThread::Thread *t=new PThread::Thread(new FreenetMasterThread());\r
 792                 threads.push_back(t);\r
 793         }\r
 794 \r
 795         if(startnntp=="true")\r
 796         {\r
 797                 PThread::Thread *t=new PThread::Thread(new NNTPListener());\r
 798                 threads.push_back(t);\r
 799         }\r
 800 \r
 801         if(starthttp=="true")\r
 802         {\r
 803                 PThread::Thread *t=new PThread::Thread(new HTTPThread());\r
 804                 threads.push_back(t);\r
 805         }\r
 806 \r
 807 }\r
 808 */\r
 809 \r
 810 void FixCapitalBoardNames()\r
 811 {\r
 812         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 813 \r
 814         SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");\r
 815         SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");\r
 816         SQLite3DB::Statement del=db->Prepare("DELTE FROM tblBoard WHERE BoardID=?;");\r
 817         SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");\r
 818         SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");\r
 819         SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");\r
 820 \r
 821         st.Step();\r
 822         while(st.RowReturned())\r
 823         {\r
 824                 int boardid=0;\r
 825                 int newboardid=0;\r
 826                 std::string name="";\r
 827                 std::string lowername="";\r
 828 \r
 829                 st.ResultInt(0,boardid);\r
 830                 st.ResultText(1,name);\r
 831 \r
 832                 lowername=name;\r
 833                 StringFunctions::LowerCase(lowername,lowername);\r
 834        \r
 835                 if(name!=lowername)\r
 836                 {\r
 837                         st2.Bind(0,lowername);\r
 838                         st2.Step();\r
 839 \r
 840                         if(st2.RowReturned())\r
 841                         {\r
 842                                 st2.ResultInt(0,newboardid);\r
 843 \r
 844                                 upd2.Bind(0,newboardid);\r
 845                                 upd2.Bind(1,boardid);\r
 846                                 upd2.Step();\r
 847                                 upd2.Reset();\r
 848 \r
 849                                 upd3.Bind(0,newboardid);\r
 850                                 upd3.Bind(1,boardid);\r
 851                                 upd3.Step();\r
 852                                 upd3.Reset();\r
 853 \r
 854                                 del.Bind(0,boardid);\r
 855                                 del.Step();\r
 856                                 del.Reset();\r
 857                         }\r
 858                         else\r
 859                         {\r
 860                                 upd.Bind(0,lowername);\r
 861                                 upd.Bind(1,boardid);\r
 862                                 upd.Step();\r
 863                                 upd.Reset();\r
 864                         }\r
 865 \r
 866                         st2.Reset();\r
 867                 }\r
 868        \r
 869                 st.Step();\r
 870         }\r
 871 \r
 872 }\r