src/dbconversions.cpp

   1 #include "../include/dbconversions.h"\r
   2 #include "../include/db/sqlite3db.h"\r
   3 #include "../include/stringfunctions.h"\r
   4 \r
   5 #include <Poco/Timestamp.h>\r
   6 #include <Poco/DateTimeFormatter.h>\r
   7 \r
   8 void ConvertDB0100To0101()\r
   9 {\r
  10         // added unique constraint to public and private key\r
  11         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  12         db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
  13         db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
  14         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
  15                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
  16                                 Name                                    TEXT,\\r
  17                                 PublicKey                               TEXT UNIQUE,\\r
  18                                 PrivateKey                              TEXT UNIQUE,\\r
  19                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
  20                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
  21                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
  22                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
  23                                 LastInsertedIdentity    DATETIME,\\r
  24                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
  25                                 LastInsertedPuzzle              DATETIME,\\r
  26                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
  27                                 LastInsertedTrustList   DATETIME,\\r
  28                                 InsertingBoardList              BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
  29                                 LastInsertedBoardList   DATETIME,\\r
  30                                 InsertingMessageList    BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
  31                                 LastInsertedMessageList DATETIME\\r
  32                                 );");\r
  33         db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");\r
  34         db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
  35         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");\r
  36 }\r
  37 \r
  38 void ConvertDB0101To0103()\r
  39 {\r
  40         // remove default 50 from trust fields and set default to NULL\r
  41         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  42         db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
  43         db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
  44         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
  45                                 IdentityID                      INTEGER PRIMARY KEY,\\r
  46                                 PublicKey                       TEXT UNIQUE,\\r
  47                                 Name                            TEXT,\\r
  48                                 SingleUse                       BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
  49                                 PublishTrustList        BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
  50                                 PublishBoardList        BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
  51                                 DateAdded                       DATETIME,\\r
  52                                 LastSeen                        DATETIME,\\r
  53                                 LocalMessageTrust       INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
  54                                 PeerMessageTrust        INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
  55                                 LocalTrustListTrust     INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
  56                                 PeerTrustListTrust      INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\\r
  57                                 );");\r
  58         db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");\r
  59         db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
  60 \r
  61         // add SaveReceivedMessages field to tblBoard\r
  62         db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages       BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");\r
  63 \r
  64         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
  65 }\r
  66 \r
  67 void ConvertDB0103To0104()\r
  68 {\r
  69         // add MessageIndex to tblMessage\r
  70         Poco::Timestamp date;\r
  71         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  72         db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex     INTEGER;");\r
  73         db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
  74         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");\r
  75         db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");\r
  76         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
  77 }\r
  78 \r
  79 void ConvertDB0104To0105()\r
  80 {\r
  81         // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity\r
  82         // add MessageTrustComment,TrustListTrustComment to tblPeerTrust\r
  83         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  84         db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");\r
  85         db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");\r
  86         db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");\r
  87         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");\r
  88         db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");\r
  89         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");\r
  90 }\r
  91 \r
  92 void ConvertDB0105To0106()\r
  93 {\r
  94         // add Publish Freesite\r
  95         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
  96         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");\r
  97         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");\r
  98         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");\r
  99 }\r
 100 \r
 101 void ConvertDB0106To0107()\r
 102 {\r
 103         // add AddedMethod to tblBoard\r
 104         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 105         db->Execute("ALTER TABLE tblBoard ADD COLUMN AddedMethod TEXT;");\r
 106         db->Execute("ALTER TABLE tblIdentity ADD COLUMN Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false';");\r
 107         db->Execute("UPDATE tblIdentity SET Hidden='false' WHERE Hidden IS NULL;");\r
 108         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");\r
 109 }\r
 110 \r
 111 void ConvertDB0107To0108()\r
 112 {\r
 113         // add FreesiteEdition to tblLocalIdentity and tblIdentity\r
 114         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 115         db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
 116         db->Execute("ALTER TABLE tblIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
 117         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=8;");\r
 118 }\r
 119 \r
 120 void ConvertDB0108To0109()\r
 121 {\r
 122         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 123         db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
 124                         FileInsertID            INTEGER PRIMARY KEY,\\r
 125                         MessageUUID                     TEXT,\\r
 126                         FileName                        TEXT,\\r
 127                         Key                                     TEXT,\\r
 128                         Size                            INTEGER,\\r
 129                         Data                            BLOB\\r
 130                         );");\r
 131         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");\r
 132 }\r
 133 \r
 134 void ConvertDB0109To0110()\r
 135 {\r
 136         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 137         db->Execute("ALTER TABLE tblFileInserts ADD COLUMN MimeType TEXT;");\r
 138         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=10;");\r
 139 }\r
 140 \r
 141 void ConvertDB0110To0111()\r
 142 {\r
 143         /*\r
 144         Drop MessageTrustComment, TrustListTrustComment FROM tblIdentity\r
 145 \r
 146         Drop InsertingMessageList, InsertingBoardList FROM tblLocalIdentity\r
 147         Add MinMessageDelay, MaxMessageDelay to tblLocalIdentity Default 0\r
 148 \r
 149         Add SendDate to tblMessageInserts\r
 150         */\r
 151         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 152 \r
 153         db->Execute("ALTER TABLE tblMessageInserts ADD COLUMN SendDate DATETIME;");\r
 154 \r
 155         db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
 156         db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
 157         db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
 158                                 LocalIdentityID                 INTEGER PRIMARY KEY,\\r
 159                                 Name                                    TEXT,\\r
 160                                 PublicKey                               TEXT UNIQUE,\\r
 161                                 PrivateKey                              TEXT UNIQUE,\\r
 162                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 163                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 164                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 165                                 PublishFreesite                 BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
 166                                 FreesiteEdition                 INTEGER,\\r
 167                                 InsertingIdentity               BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
 168                                 LastInsertedIdentity    DATETIME,\\r
 169                                 InsertingPuzzle                 BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
 170                                 LastInsertedPuzzle              DATETIME,\\r
 171                                 InsertingTrustList              BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
 172                                 LastInsertedTrustList   DATETIME,\\r
 173                                 LastInsertedBoardList   DATETIME,\\r
 174                                 LastInsertedMessageList DATETIME,\\r
 175                                 LastInsertedFreesite    DATETIME,\\r
 176                                 DateCreated                             DATETIME,\\r
 177                                 MinMessageDelay                 INTEGER DEFAULT 0,\\r
 178                                 MaxMessageDelay                 INTEGER DEFAULT 0\\r
 179                                 );");\r
 180         db->Execute("INSERT INTO tblLocalIdentity SELECT LocalIdentityID,Name,PublicKey,PrivateKey,SingleUse,PublishTrustList,PublishBoardList,PublishFreesite,FreesiteEdition,InsertingIdentity,LastInsertedIdentity,InsertingPuzzle,LastInsertedPuzzle,InsertingTrustList,LastInsertedTrustList,LastInsertedBoardList,LastInsertedMessageList,LastInsertedFreesite,DateCreated,0,0 FROM tblLocalIdentityTemp;");\r
 181         db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
 182 \r
 183         db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
 184         db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
 185         db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
 186                                 IdentityID                              INTEGER PRIMARY KEY,\\r
 187                                 PublicKey                               TEXT UNIQUE,\\r
 188                                 Name                                    TEXT,\\r
 189                                 SingleUse                               BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
 190                                 PublishTrustList                BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
 191                                 PublishBoardList                BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
 192                                 FreesiteEdition                 INTEGER,\\r
 193                                 DateAdded                               DATETIME,\\r
 194                                 LastSeen                                DATETIME,\\r
 195                                 LocalMessageTrust               INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 196                                 PeerMessageTrust                INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 197                                 LocalTrustListTrust             INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 198                                 PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
 199                                 AddedMethod                             TEXT,\\r
 200                                 Hidden                                  BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\\r
 201                                 );");\r
 202         db->Execute("INSERT INTO tblIdentity SELECT IdentityID,PublicKey,Name,SingleUse,PublishTrustList,PublishBoardList,FreesiteEdition,DateAdded,LastSeen,LocalMessageTrust,PeerMessageTrust,LocalTrustListTrust,PeerTrustListTrust,AddedMethod,Hidden FROM tblIdentityTemp;");\r
 203         db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
 204 \r
 205         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=11;");\r
 206 }\r
 207 \r
 208 void ConvertDB0111To0112()\r
 209 {\r
 210         /*\r
 211                 Add Section, SortOrder, ValidValues to tblOption\r
 212                 Add PurgeDate to tblIdentity\r
 213         */\r
 214         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 215 \r
 216         db->Execute("ALTER TABLE tblOption ADD COLUMN Section TEXT;");\r
 217         db->Execute("ALTER TABLE tblOption ADD COLUMN SortOrder INTEGER;");\r
 218         db->Execute("ALTER TABLE tblOption ADD COLUMN ValidValues TEXT;");\r
 219 \r
 220         db->Execute("ALTER TABLE tblIdentity ADD COLUMN PurgeDate DATETIME;");\r
 221 \r
 222         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=12;");\r
 223 }\r
 224 \r
 225 void FixCapitalBoardNames()\r
 226 {\r
 227         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 228 \r
 229         SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");\r
 230         SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");\r
 231         SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");\r
 232         SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");\r
 233         SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");\r
 234         SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");\r
 235 \r
 236         st.Step();\r
 237         while(st.RowReturned())\r
 238         {\r
 239                 int boardid=0;\r
 240                 int newboardid=0;\r
 241                 std::string name="";\r
 242                 std::string lowername="";\r
 243 \r
 244                 st.ResultInt(0,boardid);\r
 245                 st.ResultText(1,name);\r
 246 \r
 247                 lowername=name;\r
 248                 StringFunctions::LowerCase(lowername,lowername);\r
 249        \r
 250                 if(name!=lowername)\r
 251                 {\r
 252                         st2.Bind(0,lowername);\r
 253                         st2.Step();\r
 254 \r
 255                         if(st2.RowReturned())\r
 256                         {\r
 257                                 st2.ResultInt(0,newboardid);\r
 258 \r
 259                                 upd2.Bind(0,newboardid);\r
 260                                 upd2.Bind(1,boardid);\r
 261                                 upd2.Step();\r
 262                                 upd2.Reset();\r
 263 \r
 264                                 upd3.Bind(0,newboardid);\r
 265                                 upd3.Bind(1,boardid);\r
 266                                 upd3.Step();\r
 267                                 upd3.Reset();\r
 268 \r
 269                                 del.Bind(0,boardid);\r
 270                                 del.Step();\r
 271                                 del.Reset();\r
 272                         }\r
 273                         else\r
 274                         {\r
 275                                 upd.Bind(0,lowername);\r
 276                                 upd.Bind(1,boardid);\r
 277                                 upd.Step();\r
 278                                 upd.Reset();\r
 279                         }\r
 280 \r
 281                         st2.Reset();\r
 282                 }\r
 283        \r
 284                 st.Step();\r
 285         }\r
 286 \r
 287 }\r