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 ConvertDB0112To0113()\r
 226 {\r
 227         // Add Tries and Key (for anonymous messages) to tblMessageRequests     \r
 228         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 229 \r
 230         db->Execute("ALTER TABLE tblMessageRequests ADD COLUMN Tries INTEGER DEFAULT 0;");\r
 231         db->Execute("ALTER TABLE tblMessageRequests ADD COLUMN Key TEXT;");\r
 232 \r
 233         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");\r
 234 }\r
 235 \r
 236 void ConvertDB0113To0114()\r
 237 {\r
 238         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 239 \r
 240         db->Execute("ALTER TABLE tblBoard ADD COLUMN Forum TEXT CHECK(Forum IN ('true','false')) DEFAULT 'false';");\r
 241         db->Execute("ALTER TABLE tblMessage ADD COLUMN Read INTEGER CHECK(Read IN (0,1)) DEFAULT 0;");\r
 242 \r
 243         db->Execute("UPDATE tblDBVersion SET Major=1, Minor=14;");\r
 244 }\r
 245 \r
 246 void FixCapitalBoardNames()\r
 247 {\r
 248         SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
 249 \r
 250         SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");\r
 251         SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");\r
 252         SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");\r
 253         SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");\r
 254         SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");\r
 255         SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");\r
 256 \r
 257         st.Step();\r
 258         while(st.RowReturned())\r
 259         {\r
 260                 int boardid=0;\r
 261                 int newboardid=0;\r
 262                 std::string name="";\r
 263                 std::string lowername="";\r
 264 \r
 265                 st.ResultInt(0,boardid);\r
 266                 st.ResultText(1,name);\r
 267 \r
 268                 lowername=name;\r
 269                 StringFunctions::LowerCase(lowername,lowername);\r
 270        \r
 271                 if(name!=lowername)\r
 272                 {\r
 273                         st2.Bind(0,lowername);\r
 274                         st2.Step();\r
 275 \r
 276                         if(st2.RowReturned())\r
 277                         {\r
 278                                 st2.ResultInt(0,newboardid);\r
 279 \r
 280                                 upd2.Bind(0,newboardid);\r
 281                                 upd2.Bind(1,boardid);\r
 282                                 upd2.Step();\r
 283                                 upd2.Reset();\r
 284 \r
 285                                 upd3.Bind(0,newboardid);\r
 286                                 upd3.Bind(1,boardid);\r
 287                                 upd3.Step();\r
 288                                 upd3.Reset();\r
 289 \r
 290                                 del.Bind(0,boardid);\r
 291                                 del.Step();\r
 292                                 del.Reset();\r
 293                         }\r
 294                         else\r
 295                         {\r
 296                                 upd.Bind(0,lowername);\r
 297                                 upd.Bind(1,boardid);\r
 298                                 upd.Step();\r
 299                                 upd.Reset();\r
 300                         }\r
 301 \r
 302                         st2.Reset();\r
 303                 }\r
 304        \r
 305                 st.Step();\r
 306         }\r
 307 \r
 308 }\r