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