1 /**********************************************************************************************
2 Copyright (C) 2014-2015 Oliver Eichler <oliver.eichler@gmx.de>
3
4 This program is free software: you can redistribute it and/or modify
5 it under the terms of the GNU General Public License as published by
6 the Free Software Foundation, either version 3 of the License, or
7 (at your option) any later version.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License
15 along with this program. If not, see <http://www.gnu.org/licenses/>.
16
17 **********************************************************************************************/
18
19 #include "CMainWindow.h"
20 #include "gis/db/IDBMysql.h"
21 #include "gis/db/macros.h"
22 #include "gis/ovl/CGisItemOvlArea.h"
23 #include "gis/rte/CGisItemRte.h"
24 #include "gis/trk/CGisItemTrk.h"
25 #include "gis/wpt/CGisItemWpt.h"
26 #include "helpers/CProgressDialog.h"
27
28
29 #include <QtSql>
30 #include <QtWidgets>
31
32
IDBMysql()33 IDBMysql::IDBMysql()
34 {
35 }
36
setupDB(const QString & server,const QString & port,const QString & user,const QString & passwd,bool noPasswd,const QString & name,const QString & connectionName)37 bool IDBMysql::setupDB(const QString& server, const QString& port, const QString& user, const QString& passwd, bool noPasswd, const QString& name, const QString& connectionName)
38 {
39 // this is important!
40 IDB::setup(connectionName);
41
42 if(!QSqlDatabase::contains(connectionName))
43 {
44 db = QSqlDatabase::addDatabase("QMYSQL", connectionName);
45 db.setDatabaseName(name);
46 db.setHostName(server);
47 db.setConnectOptions("MYSQL_OPT_RECONNECT=1");
48
49 if(!port.isEmpty())
50 {
51 quint16 port16 = port.toUInt();
52 db.setPort(port16);
53 }
54
55 db.setUserName(user);
56
57 if(!noPasswd)
58 {
59 if(passwd.isEmpty())
60 {
61 bool ok;
62 QString p = QInputDialog::getText(CMainWindow::self().getBestWidgetForParent(), tr("Password..."), tr("Password for database '%1':").arg(name), QLineEdit::Password, "", &ok);
63 if (!ok || p.isEmpty())
64 {
65 return false;
66 }
67 db.setPassword(p);
68 }
69 else
70 {
71 db.setPassword(passwd);
72 }
73 }
74
75 qDebug() << "open MySQL database" << name << "@" << server << ":" << port << "as user" << user;
76
77 if(!db.open())
78 {
79 qDebug() << "failed to open database" << db.lastError();
80 return false;
81 }
82 }
83 else
84 {
85 db = QSqlDatabase::database(connectionName);
86 }
87
88 QString error;
89 return setupDB(error);
90 }
91
initDB()92 bool IDBMysql::initDB()
93 {
94 QSqlQuery query(db);
95
96 if(query.exec( "CREATE TABLE versioninfo ( version TEXT, type TEXT )"))
97 {
98 query.prepare( "INSERT INTO versioninfo (version, type) VALUES(:version, 'QMapShack')");
99 query.bindValue(":version", DB_VERSION);
100 QUERY_EXEC(return false);
101 }
102
103 QUERY_RUN( "CREATE TABLE folders ("
104 "id INTEGER PRIMARY KEY AUTO_INCREMENT,"
105 "type INTEGER NOT NULL,"
106 "keyqms TEXT,"
107 "date DATETIME DEFAULT CURRENT_TIMESTAMP,"
108 "name TEXT NOT NULL,"
109 "comment TEXT,"
110 "locked BOOLEAN DEFAULT FALSE,"
111 "data LONGBLOB,"
112 "sortmode INTEGER NOT NULL DEFAULT 0"
113 ")", return false);
114
115 QUERY_RUN( "CREATE TABLE items ("
116 "id INTEGER PRIMARY KEY AUTO_INCREMENT,"
117 "type INTEGER,"
118 "keyqms VARCHAR(64) NOT NULL,"
119 "date DATETIME DEFAULT CURRENT_TIMESTAMP,"
120 "icon BLOB NOT NULL,"
121 "name TEXT NOT NULL,"
122 "comment TEXT,"
123 "data LONGBLOB NOT NULL,"
124 "hash TEXT NOT NULL,"
125 "last_user TEXT DEFAULT NULL,"
126 "last_change DATETIME DEFAULT NOW() ON UPDATE NOW(),"
127 "trash DATETIME DEFAULT NULL,"
128 "FULLTEXT INDEX searchindex(comment),"
129 "UNIQUE KEY (keyqms)"
130 ")", return false);
131
132 QUERY_RUN("CREATE TRIGGER items_insert_last_user "
133 "BEFORE INSERT ON items "
134 "FOR EACH ROW SET NEW.last_user = USER();"
135 , return false);
136
137 QUERY_RUN("CREATE TRIGGER items_update_last_user "
138 "BEFORE UPDATE ON items "
139 "FOR EACH ROW SET NEW.last_user = USER();"
140 , return false);
141
142 query.prepare("INSERT INTO folders (type, name, comment) VALUES (2, :name, '')");
143 query.bindValue(":name", db.connectionName());
144 QUERY_EXEC(return false);
145
146 QUERY_RUN( "CREATE TABLE folder2folder ("
147 "id INTEGER PRIMARY KEY AUTO_INCREMENT,"
148 "parent INTEGER NOT NULL,"
149 "child INTEGER NOT NULL,"
150 "FOREIGN KEY(parent) REFERENCES folders(id),"
151 "FOREIGN KEY(child) REFERENCES folders(id)"
152 ")", return false);
153
154 QUERY_RUN( "CREATE TABLE folder2item ("
155 "id INTEGER PRIMARY KEY AUTO_INCREMENT,"
156 "parent INTEGER NOT NULL,"
157 "child INTEGER NOT NULL,"
158 "FOREIGN KEY(parent) REFERENCES folders(id),"
159 "FOREIGN KEY(child) REFERENCES items(id)"
160 ")", return false);
161
162 QUERY_RUN("CREATE TRIGGER folder2item_insert "
163 "BEFORE INSERT ON folder2item "
164 "FOR EACH ROW UPDATE items SET trash=NULL "
165 "WHERE id=NEW.child;"
166 , return false);
167
168 QUERY_RUN("CREATE TRIGGER folder2item_delete "
169 "AFTER DELETE ON folder2item "
170 "FOR EACH ROW UPDATE items SET trash=CURRENT_TIMESTAMP "
171 "WHERE id=OLD.child AND OLD.child NOT IN(SELECT child FROM folder2item);"
172 , return false);
173
174 return true;
175 }
176
migrateDB(int version)177 bool IDBMysql::migrateDB(int version)
178 {
179 QSqlQuery query(db);
180
181 try
182 {
183 if(version < 5)
184 {
185 if(!migrateDB4to5())
186 {
187 throw -1;
188 }
189 }
190
191 if(version < 6)
192 {
193 if(!migrateDB5to6())
194 {
195 throw -1;
196 }
197 }
198 }
199 catch(int i)
200 {
201 if(i == -1)
202 {
203 return false;
204 }
205 }
206
207 query.prepare( "UPDATE versioninfo set version=:version");
208 query.bindValue(":version", DB_VERSION);
209 QUERY_EXEC(return false);
210 return true;
211 }
212
migrateDB4to5()213 bool IDBMysql::migrateDB4to5()
214 {
215 QSqlQuery query(db);
216
217 // id and comment to full text search index
218 QUERY_RUN("ALTER TABLE items ADD FULLTEXT INDEX searchindex (comment)", return false);
219
220
221 // get number of items in the database
222 QUERY_RUN("SELECT Count(*) FROM items", return false);
223 query.next();
224 quint32 N = query.value(0).toUInt();
225
226 // over all items
227 QUERY_RUN("SELECT id, type FROM items", return false);
228 PROGRESS_SETUP(tr("Update to database version 5. Migrate all GIS items."), 0, N, CMainWindow::self().getBestWidgetForParent());
229 progress.enableCancel(false);
230 quint32 cnt = 0;
231 while(query.next())
232 {
233 PROGRESS(cnt++,;
234 );
235
236 quint64 itemId = query.value(0).toULongLong();
237 quint32 itemType = query.value(1).toUInt();
238 IGisItem* item = IGisItem::newGisItem(itemType, itemId, db, nullptr);
239
240 if(nullptr == item)
241 {
242 continue;
243 }
244
245 // get full size info text
246 QString comment = item->getInfo(IGisItem::eFeatureShowName | IGisItem::eFeatureShowFullText);
247
248 // replace comment with full size info text in items table
249 QSqlQuery query2(db);
250 query2.prepare("UPDATE items SET comment=:comment WHERE id=:id");
251 query2.bindValue(":comment", comment);
252 query2.bindValue(":id", itemId);
253 if(!query2.exec())
254 {
255 qWarning() << query2.lastQuery();
256 qWarning() << query2.lastError();
257 }
258
259 delete item;
260 }
261
262 return true;
263 }
264
migrateDB5to6()265 bool IDBMysql::migrateDB5to6()
266 {
267 QSqlQuery query(db);
268
269 QUERY_RUN("ALTER TABLE folders ADD COLUMN sortmode INTEGER NOT NULL DEFAULT 0", return false);
270
271 // get number of items in the database
272 QUERY_RUN("SELECT Count(*) FROM items", return false);
273 query.next();
274 quint32 N = query.value(0).toUInt();
275
276 // over all items
277 QUERY_RUN("SELECT id, type FROM items", return false);
278 PROGRESS_SETUP(("Update to database version 6. Migrate all GIS items."), 0, N, CMainWindow::self().getBestWidgetForParent());
279 progress.enableCancel(false);
280 quint32 cnt = 0;
281 while(query.next())
282 {
283 PROGRESS(cnt++,;
284 );
285
286 quint64 itemId = query.value(0).toULongLong();
287 quint32 itemType = query.value(1).toUInt();
288 IGisItem* item = IGisItem::newGisItem(itemType, itemId, db, nullptr);
289
290
291 if(nullptr == item)
292 {
293 continue;
294 }
295
296 // get full size info text
297 QString comment = item->getInfo(IGisItem::eFeatureShowName | IGisItem::eFeatureShowFullText);
298 QDateTime date = item->getTimestamp();
299
300 // replace comment with full size info text in items table
301 QSqlQuery query2(db);
302 query2.prepare("UPDATE items SET comment=:comment, date=:date WHERE id=:id");
303 query2.bindValue(":comment", comment);
304 query2.bindValue(":date", date);
305 query2.bindValue(":id", itemId);
306 if(!query2.exec())
307 {
308 qWarning() << query2.lastQuery();
309 qWarning() << query2.lastError();
310 }
311
312 delete item;
313 }
314
315
316 return true;
317 }
318
319