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