1 /**********************************************************************************************
2 Copyright (C) 2014 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 "qlgt/CQlb.h"
20 #include "qlgt/CQlgtDb.h"
21 #include "qlgt/CQlgtFolder.h"
22 #include "qlgt/CQlgtRoute.h"
23 #include "qlgt/CQlgtTrack.h"
24 #include "qlgt/CQlgtWpt.h"
25 #include "qlgt/CQmsDb.h"
26 #include "qlgt/IQlgtOverlay.h"
27 #include "tool/CImportDatabase.h"
28
29 #include "gis/db/macros.h"
30 #include "gis/ovl/CGisItemOvlArea.h"
31 #include "gis/rte/CGisItemRte.h"
32 #include "gis/trk/CGisItemTrk.h"
33 #include "gis/wpt/CGisItemWpt.h"
34 #include "helpers/CProgressDialog.h"
35 #include "helpers/CWptIconManager.h"
36
37 #include "CMainWindow.h"
38
39 #include <QtSql>
40 #include <QtWidgets>
41
42 #define DB_QLGT_VERSION 9
43
CQlgtDb(const QString & filename,CImportDatabase * parent)44 CQlgtDb::CQlgtDb(const QString& filename, CImportDatabase* parent)
45 : gui(parent)
46 , nItems(0)
47 , nFolders(0)
48 , nWpt(0)
49 , nTrk(0)
50 , nRte(0)
51 , nOvl(0)
52 , nDiary(0)
53 {
54 db = QSqlDatabase::addDatabase("QSQLITE", "qlandkarte");
55 db.setDatabaseName(filename);
56 db.open();
57
58 QFileInfo fi(filename);
59 path = fi.absoluteDir();
60 name = fi.fileName();
61
62 QSqlQuery query(db);
63
64 if(!query.exec("PRAGMA locking_mode=EXCLUSIVE"))
65 {
66 return;
67 }
68
69 if(!query.exec("PRAGMA synchronous=OFF"))
70 {
71 return;
72 }
73
74 if(!query.exec("PRAGMA temp_store=MEMORY"))
75 {
76 return;
77 }
78
79 if(!query.exec("PRAGMA default_cache_size=1000"))
80 {
81 return;
82 }
83
84 if(!query.exec("PRAGMA page_size=8192"))
85 {
86 return;
87 }
88
89 if(!query.exec("SELECT version FROM versioninfo"))
90 {
91 initDB();
92 }
93 else if(query.next())
94 {
95 int version = query.value(0).toInt();
96 if(version != DB_QLGT_VERSION)
97 {
98 migrateDB(version);
99 }
100 }
101 else
102 {
103 initDB();
104 }
105
106 printStatistic();
107 }
108
~CQlgtDb()109 CQlgtDb::~CQlgtDb()
110 {
111 }
112
initDB()113 void CQlgtDb::initDB()
114 {
115 qDebug() << "void CGeoDB::initDB()";
116 QSqlQuery query(db);
117
118 if(query.exec( "CREATE TABLE versioninfo ( version TEXT )"))
119 {
120 query.prepare( "INSERT INTO versioninfo (version) VALUES(:version)");
121 query.bindValue(":version", DB_QLGT_VERSION);
122 QUERY_EXEC();
123 }
124
125 if(!query.exec( "CREATE TABLE folders ("
126 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
127 "type INTEGER,"
128 "date DATETIME DEFAULT CURRENT_TIMESTAMP,"
129 "icon TEXT NOT NULL,"
130 "name TEXT NOT NULL,"
131 "comment TEXT,"
132 "locked BOOLEAN DEFAULT FALSE"
133 ")"))
134 {
135 qDebug() << query.lastQuery();
136 qDebug() << query.lastError();
137 }
138
139 if(!query.exec( "CREATE TABLE items ("
140 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
141 "type INTEGER,"
142 "key TEXT NOT NULL,"
143 "date DATETIME DEFAULT CURRENT_TIMESTAMP,"
144 "icon TEXT NOT NULL,"
145 "name TEXT NOT NULL,"
146 "comment TEXT,"
147 "data BLOB NOT NULL"
148 ")"))
149 {
150 qDebug() << query.lastQuery();
151 qDebug() << query.lastError();
152 }
153
154 if(!query.exec( "CREATE TABLE workspace ("
155 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
156 "type INTEGER NOT NULL,"
157 "changed BOOLEAN DEFAULT FALSE,"
158 "data BLOB NOT NULL,"
159 "key TEXT NOT NULL"
160 ")"))
161 {
162 qDebug() << query.lastQuery();
163 qDebug() << query.lastError();
164 }
165
166 if(!query.exec("INSERT INTO folders (icon, name, comment) VALUES ('', 'database', '')"))
167 {
168 qDebug() << query.lastQuery();
169 qDebug() << query.lastError();
170 }
171
172 if(!query.exec( "CREATE TABLE folder2folder ("
173 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
174 "parent INTEGER NOT NULL,"
175 "child INTEGER NOT NULL,"
176 "FOREIGN KEY(parent) REFERENCES folders(id),"
177 "FOREIGN KEY(child) REFERENCES folders(id)"
178 ")"))
179 {
180 qDebug() << query.lastQuery();
181 qDebug() << query.lastError();
182 }
183
184 if(!query.exec( "CREATE TABLE folder2item ("
185 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
186 "parent INTEGER NOT NULL,"
187 "child INTEGER NOT NULL,"
188 "FOREIGN KEY(parent) REFERENCES folders(id),"
189 "FOREIGN KEY(child) REFERENCES items(id)"
190 ")"))
191 {
192 qDebug() << query.lastQuery();
193 qDebug() << query.lastError();
194 }
195
196 if(!query.exec( "CREATE TABLE diarys ("
197 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
198 "parent INTEGER NOT NULL,"
199 "key TEXT NOT NULL,"
200 "date DATETIME DEFAULT CURRENT_TIMESTAMP,"
201 "data BLOB NOT NULL,"
202 "FOREIGN KEY(parent) REFERENCES folders(id)"
203 ")"))
204 {
205 qDebug() << query.lastQuery();
206 qDebug() << query.lastError();
207 }
208 }
209
210
migrateDB(int version)211 void CQlgtDb::migrateDB(int version)
212 {
213 qDebug() << "void CGeoDB::migrateDB(int version)" << version;
214 QSqlQuery query(db);
215
216 for(version++; version <= DB_QLGT_VERSION; version++)
217 {
218 switch(version)
219 {
220 case 1:
221 break;
222
223 case 2:
224 {
225 if(!query.exec( "CREATE TABLE workspace ("
226 "id INTEGER PRIMARY KEY NOT NULL,"
227 "changed BOOLEAN DEFAULT FALSE,"
228 "data BLOB NOT NULL"
229 ")"))
230 {
231 qDebug() << query.lastQuery();
232 qDebug() << query.lastError();
233 return;
234 }
235
236 break;
237 }
238
239 case 3:
240 {
241 if(!query.exec("ALTER TABLE workspace ADD COLUMN key TEXT"))
242 {
243 qDebug() << query.lastQuery();
244 qDebug() << query.lastError();
245 return;
246 }
247 if(!query.exec("ALTER TABLE workspace ADD COLUMN type INTEGER"))
248 {
249 qDebug() << query.lastQuery();
250 qDebug() << query.lastError();
251 return;
252 }
253 break;
254 }
255
256 case 4:
257 {
258 if(!query.exec("ALTER TABLE folders ADD COLUMN type INTEGER"))
259 {
260 qDebug() << query.lastQuery();
261 qDebug() << query.lastError();
262 return;
263 }
264
265 query.prepare("UPDATE folders SET type=:type WHERE icon=:icon");
266 query.bindValue("type", eFolder1);
267 query.bindValue("icon", ":/icons/iconFolderBlue16x16.png");
268 if(!query.exec())
269 {
270 qDebug() << query.lastQuery();
271 qDebug() << query.lastError();
272 return;
273 }
274
275 query.prepare("UPDATE folders SET type=:type WHERE icon=:icon");
276 query.bindValue(":type", eFolder2);
277 query.bindValue(":icon", ":/icons/iconFolderGreen16x16.png");
278 if(!query.exec())
279 {
280 qDebug() << query.lastQuery();
281 qDebug() << query.lastError();
282 return;
283 }
284
285 break;
286 }
287
288 case 5:
289 {
290 if (QFile::exists(path.absoluteFilePath("qlgt_save_v4.db")))
291 {
292 QFile::remove(path.absoluteFilePath("qlgt_save_v4.db"));
293 }
294 QFile f(path.absoluteFilePath(name));
295 f.copy(path.absoluteFilePath("qlgt_save_v4.db"));
296
297 QSqlQuery query2(db);
298
299 if(!query.exec("SELECT id, type, icon FROM items"))
300 {
301 qDebug() << query.lastQuery();
302 qDebug() << query.lastError();
303 return;
304 }
305
306 const int total = query.size();
307 quint32 progCnt = 0;
308 PROGRESS_SETUP(tr("Migrating database from version 4 to 5."), 0, total, CMainWindow::getBestWidgetForParent());
309
310 while(query.next())
311 {
312 QPixmap pixmap;
313
314 if(query.value(1).toInt() == eWpt || query.value(1).toInt() == eRte)
315 {
316 QPointF focus;
317 pixmap = CWptIconManager::self().getWptIconByName(query.value(2).toString(), focus);
318 }
319 else if(query.value(1).toInt() == eTrk)
320 {
321 pixmap = QPixmap(16, 16);
322 pixmap.fill(query.value(2).toString());
323 }
324 else
325 {
326 pixmap = QPixmap(query.value(2).toString());
327 }
328
329 QByteArray bytes;
330 QBuffer buffer(&bytes);
331 buffer.open(QIODevice::WriteOnly);
332 pixmap.save(&buffer, "XPM");
333
334 query2.prepare("UPDATE items SET icon=:icon WHERE id=:id");
335 query2.bindValue(":id", query.value(0).toULongLong());
336 query2.bindValue(":icon", bytes);
337 if(!query2.exec())
338 {
339 qDebug() << query.lastQuery();
340 qDebug() << query.lastError();
341 return;
342 }
343
344 PROGRESS(progCnt++, continue);
345 }
346 break;
347 }
348
349 case 6:
350 {
351 QSqlQuery query2(db);
352
353 if(!query.exec("SELECT id, data, type FROM items"))
354 {
355 qDebug() << query.lastQuery();
356 qDebug() << query.lastError();
357 return;
358 }
359
360 const int total = query.size();
361 quint32 progCnt = 0;
362 PROGRESS_SETUP(tr("Migrating database from version 5 to 6."), 0, total, CMainWindow::getBestWidgetForParent());
363
364 while(query.next())
365 {
366 QByteArray array = query.value(1).toByteArray();
367 QBuffer buffer(&array);
368 CQlb qlb(this);
369 qlb.load(&buffer);
370
371 switch(query.value(2).toInt())
372 {
373 case eWpt:
374 array = qlb.waypoints();
375 break;
376
377 case eTrk:
378 array = qlb.tracks();
379 break;
380
381 case eRte:
382 array = qlb.routes();
383 break;
384
385 case eOvl:
386 array = qlb.overlays();
387 break;
388 }
389
390 query2.prepare("UPDATE items SET data=:data WHERE id=:id");
391 query2.bindValue(":data", array);
392 query2.bindValue(":id", query.value(0));
393
394 if(!query2.exec())
395 {
396 qDebug() << query.lastQuery();
397 qDebug() << query.lastError();
398 return;
399 }
400
401 PROGRESS(progCnt++, continue);
402 }
403
404 break;
405 }
406
407 case 7:
408 {
409 QSqlQuery query2(db);
410
411 if(!query.exec("SELECT id, data, type FROM items"))
412 {
413 qDebug() << query.lastQuery();
414 qDebug() << query.lastError();
415 return;
416 }
417
418 const int total = query.size();
419 quint32 progCnt = 0;
420 PROGRESS_SETUP(tr("Migrating database from version 6 to 7."), 0, total, CMainWindow::getBestWidgetForParent());
421
422 while(query.next())
423 {
424 QString comment;
425 QByteArray array = query.value(1).toByteArray();
426 QDataStream stream(&array, QIODevice::ReadOnly);
427 stream.setVersion(QDataStream::Qt_4_5);
428
429 quint64 id = query.value(0).toULongLong();
430
431 switch(query.value(2).toInt())
432 {
433 case eWpt:
434 {
435 CQlgtWpt wpt(id, nullptr);
436 stream >> wpt;
437 comment = wpt.getInfo();
438 }
439 break;
440
441 case eTrk:
442 {
443 CQlgtTrack trk(id, nullptr);
444 stream >> trk;
445 comment = trk.getInfo();
446 }
447 break;
448
449 case eRte:
450 {
451 CQlgtRoute rte(id, nullptr);
452 stream >> rte;
453 comment = rte.getInfo();
454 }
455 break;
456
457 case eOvl:
458 {
459 // IOverlay ovl(0);
460 // stream >> ovl;
461 // comment = ovl.getInfo();
462 continue;
463 }
464 break;
465 }
466
467 query2.prepare("UPDATE items SET comment=:comment WHERE id=:id");
468 query2.bindValue(":comment", comment);
469 query2.bindValue(":id", query.value(0));
470
471 if(!query2.exec())
472 {
473 qDebug() << query.lastQuery();
474 qDebug() << query.lastError();
475 return;
476 }
477
478 PROGRESS(progCnt++, continue);
479 }
480
481 break;
482 }
483
484 case 8:
485 {
486 PROGRESS_SETUP(tr("Migrating database from version 7 to 8."), 0, 1, CMainWindow::getBestWidgetForParent());
487
488 if(!query.exec( "CREATE TABLE diarys ("
489 "id INTEGER PRIMARY KEY AUTOINCREMENT,"
490 "parent INTEGER NOT NULL,"
491 "key TEXT NOT NULL,"
492 "date DATETIME DEFAULT CURRENT_TIMESTAMP,"
493 "data BLOB NOT NULL,"
494 "FOREIGN KEY(parent) REFERENCES folders(id)"
495 ")"))
496 {
497 qDebug() << query.lastQuery();
498 qDebug() << query.lastError();
499 return;
500 }
501
502 PROGRESS(1, return );
503 break;
504 }
505
506 case 9:
507 {
508 if (QFile::exists(path.absoluteFilePath(name)))
509 {
510 QFile::remove(path.absoluteFilePath("qlgt_save_v8.db"));
511 }
512 QFile f(path.absoluteFilePath(name));
513 f.copy(path.absoluteFilePath("qlgt_save_v4.db"));
514
515 PROGRESS_SETUP(tr("Migrating database from version 8 to 9."), 0, 1, CMainWindow::getBestWidgetForParent());
516
517 if(!query.exec("ALTER TABLE folders ADD COLUMN locked BOOLEAN DEFAULT FALSE"))
518 {
519 qDebug() << query.lastQuery();
520 qDebug() << query.lastError();
521 return;
522 }
523
524 PROGRESS(1, return );
525 break;
526 }
527 }
528 }
529 query.prepare( "UPDATE versioninfo set version=:version");
530 query.bindValue(":version", version - 1);
531 QUERY_EXEC();
532 }
533
printStatistic()534 void CQlgtDb::printStatistic()
535 {
536 QSqlQuery query(db);
537
538 gui->stdOut(tr("Open database: %1").arg(db.databaseName()));
539
540 nItems = 0;
541
542 query.prepare("SELECT COUNT() FROM folders");
543 QUERY_EXEC();
544 if(query.next())
545 {
546 nFolders = query.value(0).toInt();
547 gui->stdOut(tr("Folders: %1").arg(nFolders));
548 }
549
550 query.prepare("SELECT COUNT() FROM items WHERE type=:type");
551 query.bindValue(":type", eTrk);
552 QUERY_EXEC();
553 if(query.next())
554 {
555 nItems += query.value(0).toInt();
556 gui->stdOut(tr("Tracks: %1").arg(query.value(0).toInt()));
557 }
558 query.prepare("SELECT COUNT() FROM items WHERE type=:type");
559 query.bindValue(":type", eRte);
560 QUERY_EXEC();
561 if(query.next())
562 {
563 nItems += query.value(0).toInt();
564 gui->stdErr(tr("Routes: %1 (Only the basic route will be copied)").arg(query.value(0).toInt()));
565 }
566 query.prepare("SELECT COUNT() FROM items WHERE type=:type");
567 query.bindValue(":type", eWpt);
568 QUERY_EXEC();
569 if(query.next())
570 {
571 nItems += query.value(0).toInt();
572 gui->stdOut(tr("Waypoints: %1").arg(query.value(0).toInt()));
573 }
574 query.prepare("SELECT COUNT() FROM items WHERE type=:type");
575 query.bindValue(":type", eOvl);
576 QUERY_EXEC();
577 if(query.next())
578 {
579 nItems += query.value(0).toInt();
580 gui->stdErr(tr("Overlays: %1 (areas will be converted as areas, distance lines will be converted to tracks, all other overlay items will be lost)").arg(query.value(0).toInt()));
581 }
582 query.prepare("SELECT COUNT() FROM diarys");
583 query.bindValue(":type", eDry);
584 QUERY_EXEC();
585 if(query.next())
586 {
587 gui->stdOut(tr("Diaries: %1").arg(query.value(0).toInt()));
588 }
589 query.prepare("SELECT COUNT() FROM items WHERE type=:type");
590 query.bindValue(":type", eMap);
591 QUERY_EXEC();
592 if(query.next())
593 {
594 gui->stdErr(tr("Map selections: %1 (can't be converted to QMapShack)").arg(query.value(0).toInt()));
595 }
596 }
597
start(const QString & filename)598 void CQlgtDb::start(const QString& filename)
599 {
600 gui->stdOut(tr("------ Start to convert database to %1------").arg(filename));
601 dbQms = new CQmsDb(filename, gui);
602 if(!dbQms->isValid())
603 {
604 gui->stdErr(tr("Failed to create target database."));
605 gui->stdOut(tr("------ Abort ------"));
606 return;
607 }
608
609
610 xferItems();
611 xferFolders();
612
613 QSqlQuery query(db);
614 query.prepare("Select parent, child FROM folder2folder");
615 QUERY_EXEC(return );
616 while(query.next())
617 {
618 quint64 idParent = query.value(0).toULongLong();
619 quint64 idChild = query.value(1).toULongLong();
620 dbQms->addFolder2FolderRelation(idParent, idChild);
621 }
622
623 query.prepare("Select parent, child FROM folder2item");
624 QUERY_EXEC(return );
625 while(query.next())
626 {
627 quint64 idParent = query.value(0).toULongLong();
628 quint64 idChild = query.value(1).toULongLong();
629 dbQms->addFolder2ItemRelation(idParent, idChild);
630 }
631
632 delete dbQms;
633 gui->stdOut(tr("------ Done ------"));
634 }
635
xferFolders()636 void CQlgtDb::xferFolders()
637 {
638 nDiary = 0;
639
640 quint32 cnt = 1;
641 PROGRESS_SETUP(tr("Restore folders..."), 0, nFolders, gui);
642
643 QSqlQuery query(db);
644 query.prepare("SELECT id FROM folders");
645 QUERY_EXEC(return );
646 while(query.next())
647 {
648 PROGRESS(cnt++, break);
649
650 quint64 idFolder = query.value(0).toULongLong();
651
652 CQlgtFolder folder1(idFolder, db);
653 if(folder1.diary)
654 {
655 nDiary++;
656 }
657
658 dbQms->addFolder(folder1);
659 }
660 progress.setValue(100);
661 gui->stdOut(tr("Imported %1 folders and %2 diaries").arg(nFolders).arg(nDiary));
662 }
663
xferItems()664 void CQlgtDb::xferItems()
665 {
666 quint32 cnt = 1;
667 PROGRESS_SETUP(tr("Copy items..."), 0, nItems, gui);
668
669 nWpt = 0;
670 nTrk = 0;
671 nRte = 0;
672 nOvl = 0;
673
674
675 QSqlQuery query(db);
676 query.prepare("SELECT id FROM items");
677 QUERY_EXEC(return );
678 while(query.next())
679 {
680 PROGRESS(cnt++, break);
681 xferItem(query.value(0).toULongLong());
682 }
683 progress.setValue(100);
684
685 gui->stdOut(tr("Imported %1 tracks, %2 waypoints, %3 routes, %4 areas").arg(nTrk).arg(nWpt).arg(nRte).arg(nOvl));
686 gui->stdOut(tr("Import folders..."));
687
688 query.prepare("SELECT id FROM folders");
689 QUERY_EXEC(return );
690 }
691
xferItem(quint64 id)692 void CQlgtDb::xferItem(quint64 id)
693 {
694 QSqlQuery query(db);
695 query.prepare("SELECT type, data FROM items WHERE id=:id");
696 query.bindValue(":id", id);
697 QUERY_EXEC(return );
698
699 if(query.next())
700 {
701 QByteArray data = query.value(1).toByteArray();
702 QDataStream stream(&data, QIODevice::ReadOnly);
703 stream.setVersion(QDataStream::Qt_4_5);
704
705
706 switch(query.value(0).toInt())
707 {
708 case eWpt:
709 {
710 CQlgtWpt wpt1(id, 0);
711 stream >> wpt1;
712 dbQms->addWpt(wpt1);
713 nWpt++;
714 break;
715 }
716
717 case eTrk:
718 {
719 CQlgtTrack trk1(id, 0);
720 stream >> trk1;
721 dbQms->addTrk(trk1);
722 nTrk++;
723 break;
724 }
725
726 case eRte:
727 {
728 CQlgtRoute rte1(id, 0);
729 stream >> rte1;
730 dbQms->addRte(rte1);
731 nRte++;
732 break;
733 }
734
735 case eOvl:
736 {
737 IQlgtOverlay ovl1(id, 0);
738 stream >> ovl1;
739 if(ovl1.type == "Area")
740 {
741 dbQms->addArea(ovl1);
742 nOvl++;
743 }
744 else if(ovl1.type == "Distance")
745 {
746 dbQms->addTrk(ovl1);
747 nTrk++;
748 }
749 else
750 {
751 gui->stdErr(tr("Overlay of type '%1' cant be converted").arg(ovl1.type));
752 nOvl++;
753 break;
754 }
755
756
757 break;
758 }
759 }
760 }
761 }
762
