1 #include "../include/dbconversions.h"
\r
2 #include "../include/db/sqlite3db.h"
\r
3 #include "../include/stringfunctions.h"
\r
5 #include <Poco/Timestamp.h>
\r
6 #include <Poco/DateTimeFormatter.h>
\r
8 void ConvertDB0100To0101(SQLite3DB::DB *db)
\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
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
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
37 void ConvertDB0101To0103(SQLite3DB::DB *db)
\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
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
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
56 db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");
\r
57 db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");
\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
62 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");
\r
65 void ConvertDB0103To0104(SQLite3DB::DB *db)
\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
76 void ConvertDB0104To0105(SQLite3DB::DB *db)
\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
88 void ConvertDB0105To0106(SQLite3DB::DB *db)
\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
96 void ConvertDB0106To0107(SQLite3DB::DB *db)
\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
105 void ConvertDB0107To0108(SQLite3DB::DB *db)
\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
113 void ConvertDB0108To0109(SQLite3DB::DB *db)
\r
115 db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\
\r
116 FileInsertID INTEGER PRIMARY KEY,\
\r
123 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");
\r
126 void ConvertDB0109To0110(SQLite3DB::DB *db)
\r
128 db->Execute("ALTER TABLE tblFileInserts ADD COLUMN MimeType TEXT;");
\r
129 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=10;");
\r
132 void ConvertDB0110To0111(SQLite3DB::DB *db)
\r
135 Drop MessageTrustComment, TrustListTrustComment FROM tblIdentity
\r
137 Drop InsertingMessageList, InsertingBoardList FROM tblLocalIdentity
\r
138 Add MinMessageDelay, MaxMessageDelay to tblLocalIdentity Default 0
\r
140 Add SendDate to tblMessageInserts
\r
143 db->Execute("ALTER TABLE tblMessageInserts ADD COLUMN SendDate DATETIME;");
\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
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
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
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
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
190 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\
\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
195 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=11;");
\r
198 void ConvertDB0111To0112(SQLite3DB::DB *db)
\r
201 Add Section, SortOrder, ValidValues to tblOption
\r
202 Add PurgeDate to tblIdentity
\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
209 db->Execute("ALTER TABLE tblIdentity ADD COLUMN PurgeDate DATETIME;");
\r
211 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=12;");
\r
214 void ConvertDB0112To0113(SQLite3DB::DB *db)
\r
216 // Add Tries and Key (for anonymous messages) to tblMessageRequests
\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
221 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");
\r
224 void ConvertDB0113To0114(SQLite3DB::DB *db)
\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
230 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=14;");
\r
233 void ConvertDB0114To0115(SQLite3DB::DB *db)
\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
241 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=15;");
\r
244 void ConvertDB0115To0116(SQLite3DB::DB *db)
\r
246 // Add FromIdentityID to tblMessageRequests so we know who we got this index from
\r
247 // Add FailureCount
\r
249 db->Execute("ALTER TABLE tblMessageRequests ADD COLUMN FromIdentityID INTEGER;");
\r
250 db->Execute("ALTER TABLE tblIdentity ADD COLUMN FailureCount INTEGER CHECK(FailureCount>=0) DEFAULT 0;");
\r
252 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=16;");
\r
255 void ConvertDB0116To0117(SQLite3DB::DB *db)
\r
257 // Add InsertDate to tblMessage
\r
259 db->Execute("ALTER TABLE tblMessage ADD COLUMN InsertDate DATE;");
\r
260 db->Execute("DROP INDEX IF EXISTS idxThreadPost_ThreadID;");
\r
262 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=17;");
\r
265 void FixCapitalBoardNames(SQLite3DB::DB *db)
\r
268 SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);");
\r
269 SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;");
\r
270 SQLite3DB::Statement del=db->Prepare("DELETE FROM tblBoard WHERE BoardID=?;");
\r
271 SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;");
\r
272 SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;");
\r
273 SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;");
\r
276 while(st.RowReturned())
\r
280 std::string name="";
\r
281 std::string lowername="";
\r
283 st.ResultInt(0,boardid);
\r
284 st.ResultText(1,name);
\r
287 StringFunctions::LowerCase(lowername,lowername);
\r
289 if(name!=lowername)
\r
291 st2.Bind(0,lowername);
\r
294 if(st2.RowReturned())
\r
296 st2.ResultInt(0,newboardid);
\r
298 upd2.Bind(0,newboardid);
\r
299 upd2.Bind(1,boardid);
\r
303 upd3.Bind(0,newboardid);
\r
304 upd3.Bind(1,boardid);
\r
308 del.Bind(0,boardid);
\r
314 upd.Bind(0,lowername);
\r
315 upd.Bind(1,boardid);
\r