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()
\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
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
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
38 void ConvertDB0101To0103()
\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
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
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
58 db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");
\r
59 db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");
\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
64 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");
\r
67 void ConvertDB0103To0104()
\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
79 void ConvertDB0104To0105()
\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
92 void ConvertDB0105To0106()
\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
101 void ConvertDB0106To0107()
\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
111 void ConvertDB0107To0108()
\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
120 void ConvertDB0108To0109()
\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
131 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");
\r
134 void ConvertDB0109To0110()
\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
141 void ConvertDB0110To0111()
\r
144 Drop MessageTrustComment, TrustListTrustComment FROM tblIdentity
\r
146 Drop InsertingMessageList, InsertingBoardList FROM tblLocalIdentity
\r
147 Add MinMessageDelay, MaxMessageDelay to tblLocalIdentity Default 0
\r
149 Add SendDate to tblMessageInserts
\r
151 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\r
153 db->Execute("ALTER TABLE tblMessageInserts ADD COLUMN SendDate DATETIME;");
\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
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
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
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
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
200 Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\
\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
205 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=11;");
\r
208 void ConvertDB0111To0112()
\r
211 Add Section, SortOrder, ValidValues to tblOption
\r
212 Add PurgeDate to tblIdentity
\r
214 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\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
220 db->Execute("ALTER TABLE tblIdentity ADD COLUMN PurgeDate DATETIME;");
\r
222 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=12;");
\r
225 void ConvertDB0112To0113()
\r
227 // Add Tries and Key (for anonymous messages) to tblMessageRequests
\r
228 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\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
233 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");
\r
236 void ConvertDB0113To0114()
\r
238 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\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
243 db->Execute("UPDATE tblDBVersion SET Major=1, Minor=14;");
\r
246 void FixCapitalBoardNames()
\r
248 SQLite3DB::DB *db=SQLite3DB::DB::Instance();
\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
258 while(st.RowReturned())
\r
262 std::string name="";
\r
263 std::string lowername="";
\r
265 st.ResultInt(0,boardid);
\r
266 st.ResultText(1,name);
\r
269 StringFunctions::LowerCase(lowername,lowername);
\r
271 if(name!=lowername)
\r
273 st2.Bind(0,lowername);
\r
276 if(st2.RowReturned())
\r
278 st2.ResultInt(0,newboardid);
\r
280 upd2.Bind(0,newboardid);
\r
281 upd2.Bind(1,boardid);
\r
285 upd3.Bind(0,newboardid);
\r
286 upd3.Bind(1,boardid);
\r
290 del.Bind(0,boardid);
\r
296 upd.Bind(0,lowername);
\r
297 upd.Bind(1,boardid);
\r