OpenCPN Partial API docs
Loading...
Searching...
No Matches
navobj_db.cpp
1/***************************************************************************
2 *
3 * Project: OpenCPN
4 * Purpose: NavObj_dB
5 * Author: David Register
6 *
7 ***************************************************************************
8 * Copyright (C) 2025 by David S. Register *
9 * *
10 * This program is free software; you can redistribute it and/or modify *
11 * it under the terms of the GNU General Public License as published by *
12 * the Free Software Foundation; either version 2 of the License, or *
13 * (at your option) any later version. *
14 * *
15 * This program is distributed in the hope that it will be useful, *
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of *
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
18 * GNU General Public License for more details. *
19 * *
20 * You should have received a copy of the GNU General Public License *
21 * along with this program; if not, write to the *
22 * Free Software Foundation, Inc., *
23 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. *
24 **************************************************************************/
25#include <cmath>
26#include <memory>
27#include <vector>
28#include <string>
29#include <iomanip>
30#include <wx/dir.h>
31
32#include "model/base_platform.h"
33#include "model/navobj_db.h"
34#include "model/navutil_base.h"
35#include "model/notification.h"
36#include "model/notification_manager.h"
37#include "wx/filename.h"
38#include "model/comm_appmsg_bus.h"
39
40extern BasePlatform* g_BasePlatform;
41extern std::shared_ptr<ObservableListener> ack_listener;
42extern RouteList* pRouteList;
43
44void ReportError(const std::string zmsg);
45
46static bool executeSQL(sqlite3* db, const char* sql) {
47 char* errMsg = nullptr;
48 if (sqlite3_exec(db, sql, nullptr, nullptr, &errMsg) != SQLITE_OK) {
49 wxString msg =
50 wxString::Format(_("navobj database error.") + " %s", errMsg);
51 wxLogMessage(msg);
52 auto& noteman = NotificationManager::GetInstance();
53 noteman.AddNotification(NotificationSeverity::kWarning, msg.ToStdString());
54 sqlite3_free(errMsg);
55 return false;
56 }
57 return true;
58}
59
60static bool executeSQL(sqlite3* db, wxString& sql) {
61 return executeSQL(db, sql.ToStdString().c_str());
62}
63
64bool CreateTables(sqlite3* db) {
65 // Track tables
66 const char* create_tables_sql = R"(
67 CREATE TABLE IF NOT EXISTS tracks (
68 guid TEXT PRIMARY KEY NOT NULL,
69 name TEXT,
70 description TEXT,
71 visibility INTEGER,
72 start_string TEXT,
73 end_string TEXT,
74 width INTEGER,
75 style INTEGER,
76 color TEXT,
77 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
78 );
79
80 CREATE TABLE IF NOT EXISTS trk_points (
81 track_guid TEXT NOT NULL,
82 latitude REAL NOT NULL,
83 longitude REAL NOT NULL,
84 timestamp TEXT NOT NULL,
85 point_order INTEGER,
86 FOREIGN KEY (track_guid) REFERENCES tracks(guid) ON DELETE CASCADE
87 );
88
89
90 CREATE TABLE IF NOT EXISTS track_html_links (
91 guid TEXT PRIMARY KEY,
92 track_guid TEXT NOT NULL,
93 html_link TEXT,
94 html_description TEXT,
95 html_type TEXT,
96 FOREIGN KEY (track_guid) REFERENCES tracks(guid) ON DELETE CASCADE
97 );
98
99
100 CREATE TABLE IF NOT EXISTS routes (
101 guid TEXT PRIMARY KEY NOT NULL,
102 name TEXT,
103 start_string TEXT,
104 end_string TEXT,
105 description TEXT,
106 planned_departure TEXT,
107 plan_speed REAL,
108 time_format TEXT,
109 style INTEGER,
110 width INTEGER,
111 color TEXT,
112 visibility INTEGER,
113 shared_wp_viz INTEGER,
114 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
115 );
116
117
118 CREATE TABLE IF NOT EXISTS routepoints (
119 guid TEXT PRIMARY KEY NOT NULL,
120 lat REAL,
121 lon REAL,
122 Symbol TEXT,
123 Name TEXT,
124 description TEXT,
125 TideStation TEXT,
126 plan_speed REAL,
127 etd INTEGER,
128 Type TEXT,
129 Time TEXT,
130 ArrivalRadius REAL,
131 RangeRingsNumber INTEGER,
132 RangeRingsStep REAL,
133 RangeRingsStepUnits INTEGER,
134 RangeRingsVisible INTEGER,
135 RangeRingsColour TEXT,
136 ScaleMin INTEGER,
137 ScaleMax INTEGER,
138 UseScale INTEGER,
139 visibility INTEGER,
140 viz_name INTEGER,
141 shared INTEGER,
142 isolated INTEGER,
143 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
144 );
145
146 CREATE TABLE IF NOT EXISTS routepoints_link (
147 route_guid TEXT,
148 point_guid TEXT,
149 point_order INTEGER,
150 PRIMARY KEY (route_guid, point_order),
151 FOREIGN KEY (route_guid) REFERENCES routes(guid) ON DELETE CASCADE
152 );
153
154 CREATE TABLE IF NOT EXISTS route_html_links (
155 guid TEXT PRIMARY KEY,
156 route_guid TEXT NOT NULL,
157 html_link TEXT,
158 html_description TEXT,
159 html_type TEXT,
160 FOREIGN KEY (route_guid) REFERENCES routes(guid) ON DELETE CASCADE
161 );
162
163 CREATE TABLE IF NOT EXISTS routepoint_html_links (
164 guid TEXT PRIMARY KEY,
165 routepoint_guid TEXT NOT NULL,
166 html_link TEXT,
167 html_description TEXT,
168 html_type TEXT,
169 FOREIGN KEY (routepoint_guid) REFERENCES routepoints(guid) ON DELETE CASCADE
170 );
171
172 )";
173
174 if (!executeSQL(db, create_tables_sql)) return false;
175
176 return true;
177}
178
179bool TrackExists(sqlite3* db, const std::string& track_guid) {
180 const char* sql = "SELECT 1 FROM tracks WHERE guid = ? LIMIT 1";
181 sqlite3_stmt* stmt;
182 bool exists = false;
183
184 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
185 sqlite3_bind_text(stmt, 1, track_guid.c_str(), -1, SQLITE_TRANSIENT);
186
187 if (sqlite3_step(stmt) == SQLITE_ROW) {
188 exists = true; // found a match
189 }
190
191 sqlite3_finalize(stmt);
192 } else {
193 ReportError("TrackExists:prepare");
194 return false;
195 }
196 return exists;
197}
198
199bool TrackHtmlLinkExists(sqlite3* db, const std::string& link_guid) {
200 const char* sql = "SELECT 1 FROM track_html_links WHERE guid = ? LIMIT 1";
201 sqlite3_stmt* stmt;
202 bool exists = false;
203
204 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
205 sqlite3_bind_text(stmt, 1, link_guid.c_str(), -1, SQLITE_TRANSIENT);
206
207 if (sqlite3_step(stmt) == SQLITE_ROW) {
208 exists = true; // found a match
209 }
210
211 sqlite3_finalize(stmt);
212 } else {
213 ReportError("TrackHtmlLinkExists:prepare");
214 return false;
215 }
216 return exists;
217}
218
219bool DeleteAllCommentsForTrack(sqlite3* db, const std::string& track_guid) {
220 const char* sql = "DELETE FROM track_html_links WHERE track_guid = ?";
221
222 sqlite3_stmt* stmt;
223 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
224 sqlite3_bind_text(stmt, 1, track_guid.c_str(), -1, SQLITE_TRANSIENT);
225 if (sqlite3_step(stmt) != SQLITE_DONE) {
226 ReportError("DeleteAllCommentsForTrack:step");
227 return false;
228 }
229
230 sqlite3_finalize(stmt);
231 } else {
232 ReportError("DeleteAllCommentsForTrack:prepare");
233 return false;
234 }
235 return true;
236}
237
238bool InsertTrackPoint(sqlite3* db, const std::string& track_guid, double lat,
239 double lon, const std::string& timestamp, int i_point) {
240 const char* sql = R"(
241 INSERT INTO trk_points (track_guid, latitude, longitude, timestamp, point_order)
242 VALUES (?, ?, ?, ?, ?)
243 )";
244 sqlite3_stmt* stmt;
245
246 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
247 sqlite3_bind_text(stmt, 1, track_guid.c_str(), -1, SQLITE_TRANSIENT);
248 sqlite3_bind_double(stmt, 2, lat);
249 sqlite3_bind_double(stmt, 3, lon);
250 sqlite3_bind_text(stmt, 4, timestamp.c_str(), -1, SQLITE_TRANSIENT);
251 sqlite3_bind_int(stmt, 5, i_point);
252 if (sqlite3_step(stmt) != SQLITE_DONE) {
253 ReportError("InsertTrackPoint:step");
254 sqlite3_finalize(stmt);
255 return false;
256 }
257 sqlite3_finalize(stmt);
258 } else {
259 return false;
260 }
261 return true;
262}
263
264bool InsertTrackHTML(sqlite3* db, const std::string& track_guid,
265 const std::string& link_guid, const std::string& descrText,
266 const std::string& link, const std::string& ltype) {
267 const char* sql = R"(
268 INSERT INTO track_html_links (guid, track_guid, html_link, html_description, html_type)
269 VALUES (?, ?, ?, ?, ?)
270 )";
271 sqlite3_stmt* stmt;
272
273 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
274 sqlite3_bind_text(stmt, 1, link_guid.c_str(), -1, SQLITE_TRANSIENT);
275 sqlite3_bind_text(stmt, 2, track_guid.c_str(), -1, SQLITE_TRANSIENT);
276 sqlite3_bind_text(stmt, 3, link.c_str(), -1, SQLITE_TRANSIENT);
277 sqlite3_bind_text(stmt, 4, descrText.c_str(), -1, SQLITE_TRANSIENT);
278 sqlite3_bind_text(stmt, 5, ltype.c_str(), -1, SQLITE_TRANSIENT);
279 if (sqlite3_step(stmt) != SQLITE_DONE) {
280 ReportError("InsertTrackHTML:step");
281 sqlite3_finalize(stmt);
282 return false;
283 }
284 sqlite3_finalize(stmt);
285 } else {
286 return false;
287 }
288 return true;
289}
290
291//..Routes
292
293bool DeleteAllCommentsForRoute(sqlite3* db, const std::string& route_guid) {
294 const char* sql = R"(
295 DELETE FROM route_html_links WHERE route_guid = ?
296 )";
297 sqlite3_stmt* stmt;
298 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
299 sqlite3_bind_text(stmt, 1, route_guid.c_str(), -1, SQLITE_TRANSIENT);
300 if (sqlite3_step(stmt) != SQLITE_DONE) {
301 ReportError("DeleteAllCommentsForRoute:step");
302 sqlite3_finalize(stmt);
303 return false;
304 }
305 sqlite3_finalize(stmt);
306 } else {
307 return false;
308 }
309 return true;
310}
311
312bool RouteHtmlLinkExists(sqlite3* db, const std::string& link_guid) {
313 const char* sql = "SELECT 1 FROM route_html_links WHERE guid = ? LIMIT 1";
314 sqlite3_stmt* stmt;
315 bool exists = false;
316
317 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
318 sqlite3_bind_text(stmt, 1, link_guid.c_str(), -1, SQLITE_TRANSIENT);
319
320 if (sqlite3_step(stmt) == SQLITE_ROW) {
321 exists = true; // found a match
322 }
323
324 sqlite3_finalize(stmt);
325 } else {
326 return false;
327 }
328 return exists;
329}
330
331bool RoutePointHtmlLinkExists(sqlite3* db, const std::string& link_guid) {
332 const char* sql =
333 "SELECT 1 FROM routepoint_html_links WHERE guid = ? LIMIT 1";
334 sqlite3_stmt* stmt;
335 bool exists = false;
336
337 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
338 sqlite3_bind_text(stmt, 1, link_guid.c_str(), -1, SQLITE_TRANSIENT);
339
340 if (sqlite3_step(stmt) == SQLITE_ROW) {
341 exists = true; // found a match
342 }
343
344 sqlite3_finalize(stmt);
345 } else {
346 return false;
347 }
348 return exists;
349}
350
351bool RouteExistsDB(sqlite3* db, const std::string& route_guid) {
352 const char* sql = "SELECT 1 FROM routes WHERE guid = ? LIMIT 1";
353 sqlite3_stmt* stmt;
354 bool exists = false;
355
356 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
357 sqlite3_bind_text(stmt, 1, route_guid.c_str(), -1, SQLITE_TRANSIENT);
358
359 if (sqlite3_step(stmt) == SQLITE_ROW) {
360 exists = true; // found a match
361 }
362
363 sqlite3_finalize(stmt);
364 } else {
365 return false;
366 }
367 return exists;
368}
369
370bool InsertRouteHTML(sqlite3* db, const std::string& route_guid,
371 const std::string& link_guid, const std::string& descrText,
372 const std::string& link, const std::string& ltype) {
373 const char* sql = R"(
374 INSERT INTO route_html_links (guid, route_guid, html_link, html_description, html_type)
375 VALUES (?, ?, ?, ?, ?)
376 )";
377 sqlite3_stmt* stmt;
378
379 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
380 sqlite3_bind_text(stmt, 1, link_guid.c_str(), -1, SQLITE_TRANSIENT);
381 sqlite3_bind_text(stmt, 2, route_guid.c_str(), -1, SQLITE_TRANSIENT);
382 sqlite3_bind_text(stmt, 3, link.c_str(), -1, SQLITE_TRANSIENT);
383 sqlite3_bind_text(stmt, 4, descrText.c_str(), -1, SQLITE_TRANSIENT);
384 sqlite3_bind_text(stmt, 5, ltype.c_str(), -1, SQLITE_TRANSIENT);
385 if (sqlite3_step(stmt) != SQLITE_DONE) {
386 ReportError("InsertRouteHTML:step");
387 sqlite3_finalize(stmt);
388 return false;
389 }
390 sqlite3_finalize(stmt);
391 } else {
392 return false;
393 }
394 return true;
395}
396
397bool RoutePointExists(sqlite3* db, const std::string& routepoint_guid) {
398 const char* sql = "SELECT 1 FROM routepoints WHERE guid = ? LIMIT 1";
399 sqlite3_stmt* stmt;
400 bool exists = false;
401
402 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
403 sqlite3_bind_text(stmt, 1, routepoint_guid.c_str(), -1, SQLITE_TRANSIENT);
404
405 if (sqlite3_step(stmt) == SQLITE_ROW) {
406 exists = true; // found a match
407 }
408
409 sqlite3_finalize(stmt);
410 } else {
411 return false;
412 }
413 return exists;
414}
415
416bool InsertRoutePointHTML(sqlite3* db, const std::string& point_guid,
417 const std::string& link_guid,
418 const std::string& descrText, const std::string& link,
419 const std::string& ltype) {
420 const char* sql = R"(
421 INSERT INTO routepoint_html_links (guid, routepoint_guid, html_link, html_description, html_type)
422 VALUES (?, ?, ?, ?, ?)
423 )";
424 sqlite3_stmt* stmt;
425
426 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
427 sqlite3_bind_text(stmt, 1, link_guid.c_str(), -1, SQLITE_TRANSIENT);
428 sqlite3_bind_text(stmt, 2, point_guid.c_str(), -1, SQLITE_TRANSIENT);
429 sqlite3_bind_text(stmt, 3, link.c_str(), -1, SQLITE_TRANSIENT);
430 sqlite3_bind_text(stmt, 4, descrText.c_str(), -1, SQLITE_TRANSIENT);
431 sqlite3_bind_text(stmt, 5, ltype.c_str(), -1, SQLITE_TRANSIENT);
432 if (sqlite3_step(stmt) != SQLITE_DONE) {
433 ReportError("InsertRoutePointHTML:step");
434 sqlite3_finalize(stmt);
435 return false;
436 }
437 sqlite3_finalize(stmt);
438 } else {
439 return false;
440 }
441 return true;
442}
443bool DeleteAllCommentsForRoutePoint(sqlite3* db,
444 const std::string& routepoint_guid) {
445 const char* sql =
446 "DELETE FROM routepoint_html_links WHERE routepoint_guid = ?";
447
448 sqlite3_stmt* stmt;
449 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
450 sqlite3_bind_text(stmt, 1, routepoint_guid.c_str(), -1, SQLITE_TRANSIENT);
451 if (sqlite3_step(stmt) != SQLITE_DONE) {
452 ReportError("DeleteAllCommentsForRoutepoint:step");
453 return false;
454 }
455
456 sqlite3_finalize(stmt);
457 } else {
458 ReportError("DeleteAllCommentsForRoutepoint:prepare");
459 return false;
460 }
461 return true;
462}
463
464bool InsertRoutePointDB(sqlite3* db, RoutePoint* point) {
465 const char* sql = R"(
466 INSERT or REPLACE INTO routepoints(guid)
467 VALUES (?)
468 )";
469 sqlite3_stmt* stmt;
470
471 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
472 sqlite3_bind_text(stmt, 1, point->m_GUID.ToStdString().c_str(), -1,
473 SQLITE_TRANSIENT);
474 if (sqlite3_step(stmt) != SQLITE_DONE) {
475 ReportError("InsertRoutePointDB:step");
476 sqlite3_finalize(stmt);
477 return false;
478 }
479 sqlite3_finalize(stmt);
480 } else {
481 return false;
482 }
483 return true;
484}
485
486bool InsertRoutePointLink(sqlite3* db, Route* route, RoutePoint* point,
487 int point_order) {
488 const char* sql = R"(
489 INSERT or IGNORE INTO routepoints_link (route_guid, point_guid, point_order)
490 VALUES (?, ?, ?)
491 )";
492
493 sqlite3_stmt* stmt;
494
495 if (sqlite3_prepare_v2(db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
496 sqlite3_bind_text(stmt, 1, route->m_GUID.ToStdString().c_str(), -1,
497 SQLITE_TRANSIENT);
498 sqlite3_bind_text(stmt, 2, point->m_GUID.ToStdString().c_str(), -1,
499 SQLITE_TRANSIENT);
500 sqlite3_bind_int(stmt, 3, point_order);
501 if (sqlite3_step(stmt) != SQLITE_DONE) {
502 ReportError("InsertTrackPointLink:step");
503 sqlite3_finalize(stmt);
504 return false;
505 }
506 sqlite3_finalize(stmt);
507 } else {
508 return false;
509 }
510 return true;
511}
512
513void DeleteOrphanedRoutepoint(sqlite3* db) {
514 const char* sql = R"(
515 DELETE FROM routepoints
516 WHERE guid NOT IN (SELECT point_guid FROM routepoints_link)
517 )";
518 char* errMsg = nullptr;
519
520 if (sqlite3_exec(db, sql, nullptr, nullptr, &errMsg) != SQLITE_OK) {
521 } else {
522 }
523}
524
525void errorLogCallback(void* pArg, int iErrCode, const char* zMsg) {
526 wxString msg =
527 wxString::Format(_("navobj database error.") + " %d: %s", iErrCode, zMsg);
528 wxLogMessage(msg);
529 auto& noteman = NotificationManager::GetInstance();
530 noteman.AddNotification(NotificationSeverity::kWarning, msg.ToStdString());
531}
532
533void ReportError(const std::string zmsg) {
534 wxString msg =
535 wxString::Format(_("navobj database error.") + " %s", zmsg.c_str());
536 wxLogMessage(msg);
537 auto& noteman = NotificationManager::GetInstance();
538 noteman.AddNotification(NotificationSeverity::kWarning, msg.ToStdString());
539}
540
541NavObj_dB& NavObj_dB::GetInstance() {
542 static NavObj_dB instance;
543 return instance;
544}
545
546NavObj_dB::NavObj_dB() {
547 m_pImportProgress = nullptr;
548
549 // Set SQLite per-process config options
550 int ie = sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, nullptr);
551
552 // Does dB file exist?
553 wxString db_filename = g_BasePlatform->GetPrivateDataDir() +
554 wxFileName::GetPathSeparator() + "navobj.db";
555 if (!wxFileExists(db_filename)) {
556 // Make a safety backup of current navobj.xml
557 wxString xml_filename = g_BasePlatform->GetPrivateDataDir() +
558 wxFileName::GetPathSeparator() + "navobj.xml";
559 if (wxFileExists(xml_filename)) {
560 wxCopyFile(xml_filename, xml_filename + ".backup");
561
562 // Make another safety backup, one time
563 wxString deep_backup_filename = g_BasePlatform->GetPrivateDataDir() +
564 wxFileName::GetPathSeparator() +
565 "navobj.xml.import_backup";
566 if (!wxFileExists(deep_backup_filename)) {
567 wxCopyFile(xml_filename, deep_backup_filename);
568 }
569 }
570
571 // Create the new database file navobj.db
572
573 int create_result = sqlite3_open_v2(
574 db_filename.ToStdString().c_str(),
575 &m_db, // sqlite3 **ppDb, /* OUT: SQLite db handle */
576 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, // int flags, /* Flags */
577 NULL // char *zVfs /* Name of VFS module to use */
578 );
579 if (create_result != SQLITE_OK) {
580 wxLogMessage("Cannot create new navobj.db database file");
581 m_db = nullptr;
582 return;
583 }
584
585 // Create initial database tables
586 CreateTables(m_db);
587
588 // Save/Close the database
589 int close_result = sqlite3_close_v2(m_db);
590 if (close_result != SQLITE_OK) {
591 return;
592 }
593 }
594
595 // Open the existing database file
596 int m_open_result = sqlite3_open_v2(db_filename.ToStdString().c_str(), &m_db,
597 SQLITE_OPEN_READWRITE, NULL);
598 sqlite3_exec(m_db, "PRAGMA foreign_keys = ON;", nullptr, nullptr, nullptr);
599
600 // Add any new tables
601 CreateTables(m_db);
602 sqlite3_close_v2(m_db);
603
604 m_open_result = sqlite3_open_v2(db_filename.ToStdString().c_str(), &m_db,
605 SQLITE_OPEN_READWRITE, NULL);
606 sqlite3_exec(m_db, "PRAGMA foreign_keys = ON;", nullptr, nullptr, nullptr);
607
608 // Init class members
609 m_importing = false;
610}
611
612NavObj_dB::~NavObj_dB() { sqlite3_close_v2(m_db); }
613
614void NavObj_dB::Close() {
615 sqlite3_close_v2(m_db);
616 m_db = nullptr;
617}
618
619bool NavObj_dB::ImportLegacyNavobj(wxFrame* frame) {
620 wxString navobj_filename = g_BasePlatform->GetPrivateDataDir() +
621 wxFileName::GetPathSeparator() + "navobj.xml";
622 bool rv = false;
623 if (::wxFileExists(navobj_filename)) {
624 m_importing = true;
625 CountImportNavObjects();
626 m_pImportProgress = new wxProgressDialog(_("Importing Navobj database"), "",
627 m_nImportObjects, frame);
628 m_import_progesscount = 0;
629
630 rv = ImportLegacyPoints();
631 rv |= ImportLegacyRoutes();
632 rv |= ImportLegacyTracks();
633 m_importing = false;
634 m_pImportProgress->Destroy();
635 }
636
637 // Delete the imported navobj.xml
638 if (::wxFileExists(navobj_filename)) ::wxRemoveFile(navobj_filename);
639
640 return rv;
641}
642
643void NavObj_dB::CountImportNavObjects() {
644 m_nImportObjects = 0;
645 m_nimportPoints = 0;
646 m_nimportRoutes = 0;
647 m_nimportTracks = 0;
648
649 auto input_set = new NavObjectCollection1();
650 wxString navobj_filename = g_BasePlatform->GetPrivateDataDir() +
651 wxFileName::GetPathSeparator() + "navobj.xml";
652
653 if (::wxFileExists(navobj_filename) &&
654 input_set->load_file(navobj_filename.ToStdString().c_str()).status ==
655 pugi::xml_parse_status::status_ok) {
656 input_set->LoadAllGPXPointObjects();
657 auto pointlist = pWayPointMan->GetWaypointList();
658 wxRoutePointListNode* prpnode = pointlist->GetFirst();
659 while (prpnode) {
660 RoutePoint* point = prpnode->GetData();
661 if (point->m_bIsolatedMark) {
662 m_nImportObjects++;
663 m_nimportPoints++;
664 }
665 prpnode = prpnode->GetNext(); // RoutePoint
666 }
667
668 input_set->LoadAllGPXRouteObjects();
669 for (wxRouteListNode* node = pRouteList->GetFirst(); node;
670 node = node->GetNext()) {
671 Route* route_import = node->GetData();
672 m_nImportObjects++;
673 m_nimportRoutes++;
674 m_nImportObjects += route_import->GetnPoints();
675 }
676
677 input_set->LoadAllGPXTrackObjects();
678 m_nImportObjects += g_TrackList.size();
679 m_nimportTracks = g_TrackList.size();
680
681 for (Track* track_import : g_TrackList) {
682 m_nImportObjects += track_import->GetnPoints();
683 }
684 }
685 delete input_set;
686}
687
688bool NavObj_dB::ImportLegacyTracks() {
689 std::vector<Track*> tracks_added;
690 // Add all tracks to database
691 int ntrack = 0;
692 for (Track* track_import : g_TrackList) {
693 if (InsertTrack(track_import)) {
694 tracks_added.push_back(track_import);
695 }
696 ntrack++;
697 m_import_progesscount += track_import->GetnPoints() + 1;
698 wxString msg = wxString::Format("Tracks %d/%d", ntrack, m_nimportTracks);
699 m_pImportProgress->Update(m_import_progesscount, msg);
700 m_pImportProgress->Show();
701 }
702
703 // Delete all tracks that were successfully added
704 for (Track* ptrack : tracks_added) {
705 if (ptrack->m_bIsInLayer) continue;
706 g_pRouteMan->DeleteTrack(ptrack);
707 }
708
709 return true;
710}
711
712bool NavObj_dB::ImportLegacyRoutes() {
713 std::vector<Route*> routes_added;
714 // Add all routes to database
715 int nroute = 0;
716 for (wxRouteListNode* node = pRouteList->GetFirst(); node;
717 node = node->GetNext()) {
718 Route* route_import = node->GetData();
719 if (InsertRoute(route_import)) {
720 routes_added.push_back(route_import);
721 }
722 nroute++;
723 m_import_progesscount += route_import->GetnPoints() + 1;
724 wxString msg = wxString::Format("Routes %d/%d", nroute, m_nimportRoutes);
725 m_pImportProgress->Update(m_import_progesscount, msg);
726 m_pImportProgress->Show();
727 }
728
729 // Delete all routes that were successfully added
730 for (Route* route : routes_added) {
731 g_pRouteMan->DeleteRoute(route);
732 }
733
734 // There may have been some points left as isolated orphans
735 // Delete them too.
736 pWayPointMan->DeleteAllWaypoints(true);
737
738 return true;
739}
740
741bool NavObj_dB::ImportLegacyPoints() {
742 std::vector<RoutePoint*> points_added;
743 // Add all isolated points to database
744 int npoint = 0;
745 int nmod = 1;
746 if (m_nimportPoints > 1000) nmod = 10;
747 if (m_nimportPoints > 10000) nmod = 100;
748
749 auto pointlist = pWayPointMan->GetWaypointList();
750 wxRoutePointListNode* prpnode = pointlist->GetFirst();
751 while (prpnode) {
752 RoutePoint* point = prpnode->GetData();
753 if (point->m_bIsolatedMark) {
754 if (InsertRoutePointDB(m_db, point)) {
755 points_added.push_back(point);
756 }
757
758 UpdateDBRoutePointAttributes(point);
759 m_import_progesscount += 1;
760 if ((npoint % nmod) == 0) {
761 wxString msg =
762 wxString::Format("Points %d/%d", npoint, m_nimportPoints);
763 m_pImportProgress->Update(m_import_progesscount, msg);
764 m_pImportProgress->Show();
765 }
766 npoint++;
767 }
768 prpnode = prpnode->GetNext(); // RoutePoint
769 }
770
771 // Delete all points that were successfully added
772 for (RoutePoint* point : points_added) {
773 pWayPointMan->RemoveRoutePoint(point);
774 delete point;
775 }
776
777 return true;
778}
779
780void NavObj_dB::LoadNavObjects() {
781 LoadAllPoints();
782 LoadAllRoutes();
783 LoadAllTracks();
784}
785
786bool NavObj_dB::InsertTrack(Track* track) {
787 if (TrackExists(m_db, track->m_GUID.ToStdString())) return false;
788
789 bool rv = false;
790 char* errMsg = 0;
791 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
792 if (errMsg) {
793 ReportError("InsertTrack:BEGIN TRANSACTION");
794 return false;
795 }
796
797 // Insert a new track
798 wxString sql = wxString::Format("INSERT INTO tracks (guid) VALUES ('%s')",
799 track->m_GUID.ToStdString().c_str());
800 if (!executeSQL(m_db, sql)) {
801 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
802 return false;
803 }
804
805 UpdateDBTrackAttributes(track);
806
807 // Add any existing trkpoints
808 for (int i = 0; i < track->GetnPoints(); i++) {
809 auto point = track->GetPoint(i);
810 // Add the bare trkpoint
811 InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
812 point->m_lon, point->GetTimeString(), i);
813 }
814
815 // Add HTML links to track
816 int NbrOfLinks = track->m_TrackHyperlinkList->GetCount();
817 if (NbrOfLinks > 0) {
818 wxHyperlinkListNode* linknode = track->m_TrackHyperlinkList->GetFirst();
819 while (linknode) {
820 Hyperlink* link = linknode->GetData();
821
822 if (!TrackHtmlLinkExists(m_db, link->GUID)) {
823 InsertTrackHTML(m_db, track->m_GUID.ToStdString(), link->GUID,
824 link->DescrText.ToStdString(), link->Link.ToStdString(),
825 link->LType.ToStdString());
826 }
827 linknode = linknode->GetNext();
828 }
829 }
830 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
831 rv = true;
832 if (errMsg) rv = false;
833
834 return rv;
835};
836
837bool NavObj_dB::UpdateTrack(Track* track) {
838 bool rv = false;
839 char* errMsg = 0;
840
841 if (!TrackExists(m_db, track->m_GUID.ToStdString())) return false;
842
843 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
844 if (errMsg) {
845 ReportError("UpdateTrack:BEGIN TRANSACTION");
846 return false;
847 }
848
849 UpdateDBTrackAttributes(track);
850
851 // Delete and re-add track points
852 const char* sql = "DELETE FROM trk_points WHERE track_guid = ?";
853 sqlite3_stmt* stmt;
854 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
855 sqlite3_bind_text(stmt, 1, track->m_GUID.ToStdString().c_str(), -1,
856 SQLITE_TRANSIENT);
857 } else {
858 ReportError("UpdateTrack:prepare");
859 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
860 return false;
861 }
862 if (sqlite3_step(stmt) != SQLITE_DONE) {
863 ReportError("UpdateTrack:step");
864 sqlite3_finalize(stmt);
865 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
866 return false;
867 }
868 sqlite3_finalize(stmt);
869
870 // re-add trackpoints
871 for (int i = 0; i < track->GetnPoints(); i++) {
872 auto point = track->GetPoint(i);
873 // Add the bare point
874 if (point) {
875 InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
876 point->m_lon, point->GetTimeString(), i);
877 }
878 }
879
880 sqlite3_exec(m_db, "COMMIT", 0, 0, nullptr);
881
882 rv = true;
883 if (errMsg) rv = false;
884 return rv;
885};
886
887bool NavObj_dB::UpdateDBTrackAttributes(Track* track) {
888 const char* sql =
889 "UPDATE tracks SET "
890 "name = ?, "
891 "description = ?, "
892 "visibility = ?, "
893 "start_string = ?, "
894 "end_string = ?, "
895 "width = ?, "
896 "style = ?, "
897 "color = ? "
898 "WHERE guid = ?";
899
900 sqlite3_stmt* stmt;
901 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
902 sqlite3_bind_text(stmt, 1, track->GetName().ToStdString().c_str(), -1,
903 SQLITE_TRANSIENT);
904 sqlite3_bind_text(stmt, 2, track->m_TrackDescription.ToStdString().c_str(),
905 -1, SQLITE_TRANSIENT);
906 sqlite3_bind_int(stmt, 3, track->m_bVisible);
907 sqlite3_bind_text(stmt, 4, track->m_TrackStartString.ToStdString().c_str(),
908 -1, SQLITE_TRANSIENT);
909 sqlite3_bind_text(stmt, 5, track->m_TrackEndString.ToStdString().c_str(),
910 -1, SQLITE_TRANSIENT);
911 sqlite3_bind_int(stmt, 6, track->m_width);
912 sqlite3_bind_int(stmt, 7,
913 (int)(track->m_style)); // track->m_style.c_str(),
914 sqlite3_bind_text(stmt, 8, track->m_Colour.ToStdString().c_str(), -1,
915 SQLITE_TRANSIENT);
916 sqlite3_bind_text(stmt, 9, track->m_GUID.c_str(), track->m_GUID.size(),
917 SQLITE_TRANSIENT);
918 } else {
919 return false;
920 }
921
922 if (sqlite3_step(stmt) != SQLITE_DONE) {
923 ReportError("UpdateDBTrackAttributesA:step");
924 sqlite3_finalize(stmt);
925 return false;
926 }
927
928 sqlite3_finalize(stmt);
929
930 // Update the HTML links
931 // The list of links is freshly rebuilt when this method is called
932 // So start by deleting all existing bcomments
933 DeleteAllCommentsForTrack(m_db, track->m_GUID.ToStdString());
934
935 // Now add all the links to db
936 int NbrOfLinks = track->m_TrackHyperlinkList->GetCount();
937 if (NbrOfLinks > 0) {
938 wxHyperlinkListNode* linknode = track->m_TrackHyperlinkList->GetFirst();
939 while (linknode) {
940 Hyperlink* link = linknode->GetData();
941
942 if (!TrackHtmlLinkExists(m_db, link->GUID)) {
943 InsertTrackHTML(m_db, track->m_GUID.ToStdString(), link->GUID,
944 link->DescrText.ToStdString(), link->Link.ToStdString(),
945 link->LType.ToStdString());
946 } else {
947 const char* sql =
948 "UPDATE track_html_links SET "
949 "html_link = ?, "
950 "html_description = ?, "
951 "html_type = ? "
952 "WHERE guid = ?";
953 sqlite3_stmt* stmt;
954 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
955 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
956 SQLITE_TRANSIENT);
957 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
958 SQLITE_TRANSIENT);
959 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
960 SQLITE_TRANSIENT);
961 }
962 if (sqlite3_step(stmt) != SQLITE_DONE) {
963 ReportError("UpdateDBTRackAttributesB:step");
964 sqlite3_finalize(stmt);
965 return false;
966 }
967
968 sqlite3_finalize(stmt);
969 }
970
971 linknode = linknode->GetNext();
972 }
973 }
974
975 return true;
976}
977
978bool NavObj_dB::AddTrackPoint(Track* track, TrackPoint* point) {
979 // If track does not yet exist in dB, return
980 if (!TrackExists(m_db, track->m_GUID.ToStdString())) return false;
981
982 // Get next point order
983 int this_point_index = track->GetnPoints();
984
985 // Add the linked point to the dB
986 if (!InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
987 point->m_lon, point->GetTimeString(),
988 this_point_index - 1))
989 return false;
990
991 return true;
992}
993
994bool NavObj_dB::LoadAllTracks() {
995 const char* sql = R"(
996 SELECT guid, name,
997 description, visibility, start_string, end_string,
998 width, style, color,
999 created_at
1000 FROM tracks
1001 ORDER BY created_at ASC
1002 )";
1003
1004 sqlite3_stmt* stmt;
1005 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
1006 return false;
1007 }
1008
1009 while (sqlite3_step(stmt) == SQLITE_ROW) {
1010 std::string guid =
1011 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1012 std::string name =
1013 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1014 std::string description =
1015 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1016 int visibility = sqlite3_column_int(stmt, 3);
1017 std::string start_string =
1018 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
1019 std::string end_string =
1020 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
1021 int width = sqlite3_column_int(stmt, 6);
1022 int style = sqlite3_column_int(stmt, 7);
1023 std::string color =
1024 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 8));
1025 std::string created =
1026 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 9));
1027
1028 Track* new_trk = NULL;
1029
1030 // Add the trk_points
1031 const char* sql = R"(
1032 SELECT latitude, longitude, timestamp, point_order
1033 FROM trk_points
1034 WHERE track_guid = ?
1035 ORDER BY point_order ASC
1036 )";
1037
1038 sqlite3_stmt* stmtp;
1039 if (sqlite3_prepare_v2(m_db, sql, -1, &stmtp, nullptr) != SQLITE_OK) {
1040 return false;
1041 }
1042
1043 sqlite3_bind_text(stmtp, 1, guid.c_str(), -1, SQLITE_TRANSIENT);
1044
1045 int GPXTrkSeg = 1;
1046 while (sqlite3_step(stmtp) == SQLITE_ROW) {
1047 if (!new_trk) {
1048 new_trk = new Track;
1049 new_trk->m_GUID = guid;
1050
1051 // Set all the track attributes
1052 new_trk->SetVisible(visibility == 1);
1053 new_trk->SetName(name.c_str());
1054 new_trk->m_TrackStartString = start_string.c_str();
1055 new_trk->m_TrackEndString = end_string.c_str();
1056 new_trk->m_width = width;
1057 new_trk->m_style = (wxPenStyle)style;
1058 new_trk->m_Colour = color;
1059 }
1060
1061 double latitude = sqlite3_column_double(stmtp, 0);
1062 double longitude = sqlite3_column_double(stmtp, 1);
1063 std::string timestamp =
1064 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, 2));
1065 int point_order = sqlite3_column_int(stmtp, 3);
1066
1067 auto point = new TrackPoint(latitude, longitude, timestamp);
1068
1069 point->m_GPXTrkSegNo = GPXTrkSeg;
1070 new_trk->AddPoint(point);
1071 }
1072 sqlite3_finalize(stmtp);
1073
1074 if (new_trk) {
1075 new_trk->SetCurrentTrackSeg(GPXTrkSeg);
1076
1077 // Add the HTML links
1078 const char* sqlh = R"(
1079 SELECT guid, html_link, html_description, html_type
1080 FROM track_html_links
1081 WHERE track_guid = ?
1082 ORDER BY html_type ASC
1083 )";
1084
1085 sqlite3_stmt* stmt;
1086
1087 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1088 sqlite3_bind_text(stmt, 1, new_trk->m_GUID.ToStdString().c_str(), -1,
1089 SQLITE_TRANSIENT);
1090
1091 while (sqlite3_step(stmt) == SQLITE_ROW) {
1092 std::string link_guid =
1093 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1094 std::string link_link =
1095 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1096 std::string link_description =
1097 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1098 std::string link_type =
1099 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1100
1101 Hyperlink* h = new Hyperlink();
1102 h->DescrText = link_description;
1103 h->Link = link_link;
1104 h->LType = link_type;
1105
1106 new_trk->m_TrackHyperlinkList->Append(h);
1107 int yyp = 4;
1108 }
1109
1110 sqlite3_finalize(stmt);
1111
1112 } else {
1113 return false;
1114 }
1115
1116 // Insert the track into the global list
1117 g_TrackList.push_back(new_trk);
1118 // Add the selectable points and segments of the track
1119 pSelect->AddAllSelectableTrackSegments(new_trk);
1120 }
1121 }
1122 return true;
1123}
1124
1125bool NavObj_dB::DeleteTrack(Track* track) {
1126 if (!track) return false;
1127 std::string track_guid = track->m_GUID.ToStdString();
1128 const char* sql = "DELETE FROM tracks WHERE guid = ?";
1129 sqlite3_stmt* stmt;
1130
1131 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1132 sqlite3_bind_text(stmt, 1, track_guid.c_str(), -1, SQLITE_TRANSIENT);
1133 if (sqlite3_step(stmt) != SQLITE_DONE) {
1134 ReportError("DeleteTrack:step");
1135 sqlite3_finalize(stmt);
1136 return false;
1137 }
1138
1139 sqlite3_finalize(stmt);
1140 } else {
1141 return false;
1142 }
1143 return true;
1144}
1145
1146// Route support
1147
1148bool NavObj_dB::InsertRoute(Route* route) {
1149 bool rv = false;
1150 char* errMsg = 0;
1151
1152 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) {
1153 // Insert a new route
1154 wxString sql = wxString::Format("INSERT INTO routes (guid) VALUES ('%s')",
1155 route->m_GUID.ToStdString().c_str());
1156 if (!executeSQL(m_db, sql)) {
1157 return false;
1158 }
1159 UpdateDBRouteAttributes(route);
1160 }
1161
1162 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
1163 if (errMsg) {
1164 ReportError("InsertRoute:BEGIN TRANSACTION");
1165 return false;
1166 }
1167
1168 // insert routepoints
1169 for (int i = 0; i < route->GetnPoints(); i++) {
1170 auto point = route->GetPoint(i + 1);
1171 // Add the bare point
1172 if (point) {
1173 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
1174 InsertRoutePointDB(m_db, point);
1175 UpdateDBRoutePointAttributes(point);
1176 }
1177 }
1178 }
1179
1180 // insert linkages
1181 for (int i = 0; i < route->GetnPoints(); i++) {
1182 auto point = route->GetPoint(i + 1);
1183 // Add the bare point
1184 if (point) {
1185 InsertRoutePointLink(m_db, route, point, i + 1);
1186 }
1187 }
1188
1189 // Add HTML links to route
1190 int NbrOfLinks = route->m_HyperlinkList->GetCount();
1191 if (NbrOfLinks > 0) {
1192 wxHyperlinkListNode* linknode = route->m_HyperlinkList->GetFirst();
1193 while (linknode) {
1194 Hyperlink* link = linknode->GetData();
1195
1196 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1197 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1198 link->DescrText.ToStdString(), link->Link.ToStdString(),
1199 link->LType.ToStdString());
1200 }
1201 linknode = linknode->GetNext();
1202 }
1203 }
1204
1205 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
1206 rv = true;
1207 if (errMsg) {
1208 ReportError("InsertRoute:commit");
1209 rv = false;
1210 }
1211 return rv;
1212};
1213
1214bool NavObj_dB::UpdateRoute(Route* route) {
1215 bool rv = false;
1216 char* errMsg = 0;
1217
1218 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) return false;
1219
1220 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
1221 if (errMsg) {
1222 ReportError("UpdateRoute:BEGIN TRANSACTION");
1223 return false;
1224 }
1225
1226 UpdateDBRouteAttributes(route);
1227
1228 // update routepoints
1229 for (int i = 0; i < route->GetnPoints(); i++) {
1230 auto point = route->GetPoint(i + 1);
1231 // Add the bare point
1232 if (point) {
1233 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
1234 InsertRoutePointDB(m_db, point);
1235 }
1236 UpdateDBRoutePointAttributes(point);
1237 }
1238 }
1239
1240 // Delete and re-add point linkages
1241 const char* sql = "DELETE FROM routepoints_link WHERE route_guid = ?";
1242 sqlite3_stmt* stmt;
1243 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1244 sqlite3_bind_text(stmt, 1, route->m_GUID.ToStdString().c_str(), -1,
1245 SQLITE_TRANSIENT);
1246 } else {
1247 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
1248 return false;
1249 }
1250 if (sqlite3_step(stmt) != SQLITE_DONE) {
1251 ReportError("UpdateRoute:step");
1252 sqlite3_finalize(stmt);
1253 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
1254 return false;
1255 }
1256
1257 sqlite3_finalize(stmt);
1258
1259 for (int i = 0; i < route->GetnPoints(); i++) {
1260 auto point = route->GetPoint(i + 1);
1261 if (point) {
1262 InsertRoutePointLink(m_db, route, point, i + 1);
1263 }
1264 }
1265
1266 // Add HTML links to route
1267 int NbrOfLinks = route->m_HyperlinkList->GetCount();
1268 if (NbrOfLinks > 0) {
1269 wxHyperlinkListNode* linknode = route->m_HyperlinkList->GetFirst();
1270 while (linknode) {
1271 Hyperlink* link = linknode->GetData();
1272
1273 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1274 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1275 link->DescrText.ToStdString(), link->Link.ToStdString(),
1276 link->LType.ToStdString());
1277 }
1278 linknode = linknode->GetNext();
1279 }
1280 }
1281 sqlite3_exec(m_db, "COMMIT", 0, 0, nullptr);
1282
1283 rv = true;
1284 if (errMsg) rv = false;
1285
1286 return rv;
1287};
1288
1289bool NavObj_dB::UpdateRouteViz(Route* route) {
1290 bool rv = false;
1291 char* errMsg = 0;
1292 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) return false;
1293
1294 UpdateDBRouteAttributes(route);
1295 // update routepoints visibility
1296 for (int i = 0; i < route->GetnPoints(); i++) {
1297 auto point = route->GetPoint(i + 1);
1298 // Add the bare point
1299 if (point) {
1300 UpdateDBRoutePointViz(point);
1301 }
1302 }
1303 rv = true;
1304 if (errMsg) rv = false;
1305
1306 return rv;
1307};
1308
1309bool NavObj_dB::UpdateDBRouteAttributes(Route* route) {
1310 const char* sql =
1311 "UPDATE routes SET "
1312 "name = ?, "
1313 "description = ?, "
1314 "start_string = ?, "
1315 "end_string = ?, "
1316 "visibility = ?, "
1317 "shared_wp_viz = ?, "
1318 "planned_departure = ?, "
1319 "plan_speed = ?, "
1320 "time_format = ?, "
1321 "width = ?, "
1322 "style = ?, "
1323 "color = ? "
1324 "WHERE guid = ?";
1325
1326 sqlite3_stmt* stmt;
1327 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1328 sqlite3_bind_text(stmt, 1, route->GetName().ToStdString().c_str(), -1,
1329 SQLITE_TRANSIENT);
1330 sqlite3_bind_text(stmt, 2, route->m_RouteDescription.ToStdString().c_str(),
1331 -1, SQLITE_TRANSIENT);
1332 sqlite3_bind_text(stmt, 3, route->m_RouteStartString.ToStdString().c_str(),
1333 -1, SQLITE_TRANSIENT);
1334 sqlite3_bind_text(stmt, 4, route->m_RouteEndString.ToStdString().c_str(),
1335 -1, SQLITE_TRANSIENT);
1336 sqlite3_bind_int(stmt, 5, route->IsVisible());
1337 sqlite3_bind_int(stmt, 6, route->GetSharedWPViz());
1338 if (route->m_PlannedDeparture.IsValid())
1339 sqlite3_bind_int(stmt, 7, route->m_PlannedDeparture.GetTicks());
1340 sqlite3_bind_double(stmt, 8, route->m_PlannedSpeed);
1341 sqlite3_bind_text(stmt, 9, route->m_TimeDisplayFormat.ToStdString().c_str(),
1342 -1, SQLITE_TRANSIENT);
1343 sqlite3_bind_int(stmt, 10, route->m_width);
1344 sqlite3_bind_int(stmt, 11,
1345 (int)(route->m_style)); // track->m_style.c_str(),
1346 sqlite3_bind_text(stmt, 12, route->m_Colour.ToStdString().c_str(), -1,
1347 SQLITE_TRANSIENT);
1348 sqlite3_bind_text(stmt, 13, route->m_GUID.c_str(), route->m_GUID.size(),
1349 SQLITE_TRANSIENT);
1350 } else {
1351 return false;
1352 }
1353
1354 if (sqlite3_step(stmt) != SQLITE_DONE) {
1355 ReportError("UpdateDBRouteAttributesA:step");
1356 sqlite3_finalize(stmt);
1357 return false;
1358 }
1359
1360 sqlite3_finalize(stmt);
1361
1362 // Update the HTML links
1363 // The list of links is freshly rebuilt when this method is called
1364 // So start by deleting all existing bcomments
1365 DeleteAllCommentsForRoute(m_db, route->m_GUID.ToStdString());
1366
1367 // Now add all the links to db
1368 int NbrOfLinks = route->m_HyperlinkList->GetCount();
1369 if (NbrOfLinks > 0) {
1370 wxHyperlinkListNode* linknode = route->m_HyperlinkList->GetFirst();
1371 while (linknode) {
1372 Hyperlink* link = linknode->GetData();
1373
1374 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1375 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1376 link->DescrText.ToStdString(), link->Link.ToStdString(),
1377 link->LType.ToStdString());
1378 } else {
1379 const char* sql =
1380 "UPDATE route_html_links SET "
1381 "html_link = ?, "
1382 "html_description = ?, "
1383 "html_type = ? "
1384 "WHERE guid = ?";
1385 sqlite3_stmt* stmt;
1386 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1387 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
1388 SQLITE_TRANSIENT);
1389 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
1390 SQLITE_TRANSIENT);
1391 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
1392 SQLITE_TRANSIENT);
1393 }
1394 if (sqlite3_step(stmt) != SQLITE_DONE) {
1395 return false;
1396 }
1397 if (sqlite3_step(stmt) != SQLITE_DONE) {
1398 ReportError("UpdateDBRouteAttributesB:step");
1399 sqlite3_finalize(stmt);
1400 return false;
1401 }
1402
1403 sqlite3_finalize(stmt);
1404 }
1405
1406 linknode = linknode->GetNext();
1407 }
1408 }
1409 return true;
1410}
1411
1412bool NavObj_dB::UpdateDBRoutePointAttributes(RoutePoint* point) {
1413 const char* sql =
1414 "UPDATE routepoints SET "
1415 "lat = ?, "
1416 "lon = ?, "
1417 "Symbol = ?, "
1418 "Name = ?, "
1419 "description = ?, "
1420 "TideStation = ?, "
1421 "plan_speed = ?, "
1422 "etd = ?, "
1423 "Type = ?, "
1424 "Time = ?, "
1425 "ArrivalRadius = ?, "
1426 "RangeRingsNumber = ?, "
1427 "RangeRingsStep = ?, "
1428 "RangeRingsStepUnits = ?, "
1429 "RangeRingsVisible = ?, "
1430 "RangeRingsColour = ?, "
1431 "ScaleMin = ?, "
1432 "ScaleMax = ?, "
1433 "UseScale = ?, "
1434 "visibility = ?, "
1435 "viz_name = ?, "
1436 "shared = ?, "
1437 "isolated = ? "
1438 "WHERE guid = ?";
1439
1440 sqlite3_stmt* stmt;
1441 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1442 sqlite3_bind_double(stmt, 1, point->GetLatitude());
1443 sqlite3_bind_double(stmt, 2, point->GetLongitude());
1444 sqlite3_bind_text(stmt, 3, point->GetIconName().ToStdString().c_str(), -1,
1445 SQLITE_TRANSIENT);
1446 sqlite3_bind_text(stmt, 4, point->GetName().ToStdString().c_str(), -1,
1447 SQLITE_TRANSIENT);
1448 sqlite3_bind_text(stmt, 5, point->GetDescription().ToStdString().c_str(),
1449 -1, SQLITE_TRANSIENT);
1450 sqlite3_bind_text(stmt, 6, point->m_TideStation.ToStdString().c_str(), -1,
1451 SQLITE_TRANSIENT);
1452 sqlite3_bind_double(stmt, 7, point->GetPlannedSpeed());
1453 time_t etd = -1;
1454 if (point->GetManualETD().IsValid()) etd = point->GetManualETD().GetTicks();
1455 sqlite3_bind_int(stmt, 8, etd);
1456 sqlite3_bind_text(stmt, 9, "type", -1, SQLITE_TRANSIENT);
1457 std::string timit = point->m_timestring.ToStdString().c_str();
1458 sqlite3_bind_text(stmt, 10, point->m_timestring.ToStdString().c_str(), -1,
1459 SQLITE_TRANSIENT);
1460 sqlite3_bind_double(stmt, 11, point->m_WaypointArrivalRadius);
1461
1462 sqlite3_bind_int(stmt, 12, point->m_iWaypointRangeRingsNumber);
1463 sqlite3_bind_double(stmt, 13, point->m_fWaypointRangeRingsStep);
1464 sqlite3_bind_int(stmt, 14, point->m_iWaypointRangeRingsStepUnits);
1465 sqlite3_bind_int(stmt, 15, point->m_bShowWaypointRangeRings);
1466 sqlite3_bind_text(
1467 stmt, 16,
1468 point->m_wxcWaypointRangeRingsColour.GetAsString(wxC2S_HTML_SYNTAX)
1469 .ToStdString()
1470 .c_str(),
1471 -1, SQLITE_TRANSIENT);
1472
1473 sqlite3_bind_int(stmt, 17, point->GetScaMin());
1474 sqlite3_bind_int(stmt, 18, point->GetScaMax());
1475 sqlite3_bind_int(stmt, 19, point->GetUseSca());
1476
1477 sqlite3_bind_int(stmt, 20, point->IsVisible());
1478 sqlite3_bind_int(stmt, 21, point->IsNameShown());
1479 sqlite3_bind_int(stmt, 22, point->IsShared());
1480 int iso = point->m_bIsolatedMark;
1481 sqlite3_bind_int(stmt, 23, iso); // point->m_bIsolatedMark);
1482
1483 sqlite3_bind_text(stmt, 24, point->m_GUID.ToStdString().c_str(), -1,
1484 SQLITE_TRANSIENT);
1485
1486 } else {
1487 return false;
1488 }
1489
1490 if (sqlite3_step(stmt) != SQLITE_DONE) {
1491 ReportError("UpdateDBRoutePointAttributesA:step");
1492 sqlite3_finalize(stmt);
1493 return false;
1494 }
1495
1496 sqlite3_finalize(stmt);
1497
1498 // Update the HTML links
1499 // The list of links is freshly rebuilt when this method is called
1500 // So start by deleting all existing bcomments
1501 DeleteAllCommentsForRoutePoint(m_db, point->m_GUID.ToStdString());
1502
1503 // Now add all the links to db
1504 int NbrOfLinks = point->m_HyperlinkList->GetCount();
1505 if (NbrOfLinks > 0) {
1506 wxHyperlinkListNode* linknode = point->m_HyperlinkList->GetFirst();
1507 while (linknode) {
1508 Hyperlink* link = linknode->GetData();
1509
1510 if (!RoutePointHtmlLinkExists(m_db, link->GUID)) {
1511 InsertRoutePointHTML(m_db, point->m_GUID.ToStdString(), link->GUID,
1512 link->DescrText.ToStdString(),
1513 link->Link.ToStdString(),
1514 link->LType.ToStdString());
1515 } else {
1516 const char* sql =
1517 "UPDATE routepoint_html_links SET "
1518 "html_link = ?, "
1519 "html_description = ?, "
1520 "html_type = ? "
1521 "WHERE guid = ?";
1522 sqlite3_stmt* stmt;
1523 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1524 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
1525 SQLITE_TRANSIENT);
1526 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
1527 SQLITE_TRANSIENT);
1528 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
1529 SQLITE_TRANSIENT);
1530 }
1531 if (sqlite3_step(stmt) != SQLITE_DONE) {
1532 return false;
1533 }
1534 if (sqlite3_step(stmt) != SQLITE_DONE) {
1535 ReportError("UpdateDBRoutePointAttributesB:step-h");
1536 sqlite3_finalize(stmt);
1537 return false;
1538 }
1539
1540 sqlite3_finalize(stmt);
1541 }
1542
1543 linknode = linknode->GetNext();
1544 }
1545 }
1546
1547 return true;
1548}
1549
1550bool NavObj_dB::UpdateDBRoutePointViz(RoutePoint* point) {
1551 const char* sql =
1552 "UPDATE routepoints SET "
1553 "visibility = ? "
1554 "WHERE guid = ?";
1555
1556 sqlite3_stmt* stmt;
1557 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1558 sqlite3_bind_int(stmt, 1, point->IsVisible());
1559 sqlite3_bind_text(stmt, 2, point->m_GUID.ToStdString().c_str(), -1,
1560 SQLITE_TRANSIENT);
1561
1562 } else {
1563 return false;
1564 }
1565
1566 if (sqlite3_step(stmt) != SQLITE_DONE) {
1567 ReportError("UpdateDBRoutePointVizA:step");
1568 sqlite3_finalize(stmt);
1569 return false;
1570 }
1571
1572 sqlite3_finalize(stmt);
1573
1574 return true;
1575}
1576
1577bool NavObj_dB::DeleteRoute(Route* route) {
1578 if (m_importing) return false;
1579 if (!route) return false;
1580 std::string route_guid = route->m_GUID.ToStdString();
1581 const char* sql = "DELETE FROM routes WHERE guid = ?";
1582 sqlite3_stmt* stmt;
1583
1584 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1585 sqlite3_bind_text(stmt, 1, route_guid.c_str(), -1, SQLITE_TRANSIENT);
1586 if (sqlite3_step(stmt) != SQLITE_DONE) {
1587 ReportError("DeleteRoute:step");
1588 sqlite3_finalize(stmt);
1589 return false;
1590 }
1591 sqlite3_finalize(stmt);
1592 } else {
1593 return false;
1594 }
1595 return true;
1596}
1597
1598bool NavObj_dB::LoadAllRoutes() {
1599 const char* sql =
1600 "SELECT "
1601 "guid, "
1602 "name, "
1603 "description, "
1604 "start_string, "
1605 "end_string, "
1606 "visibility, "
1607 "shared_wp_viz, "
1608 "planned_departure, "
1609 "plan_speed, "
1610 "time_format, "
1611 "width, "
1612 "style, "
1613 "color "
1614 "FROM routes "
1615 "ORDER BY created_at ASC";
1616
1617 sqlite3_stmt* stmt;
1618 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
1619 return false;
1620 }
1621
1622 int errcode0 = SQLITE_OK;
1623 while ((errcode0 = sqlite3_step(stmt)) == SQLITE_ROW) {
1624 std::string guid =
1625 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1626 std::string name =
1627 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1628 std::string description =
1629 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1630 std::string start_string =
1631 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1632 std::string end_string =
1633 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
1634 int visibility = sqlite3_column_int(stmt, 5);
1635 int sharewp_viz = sqlite3_column_int(stmt, 6);
1636 time_t planned_departure_ticks = sqlite3_column_int(stmt, 7);
1637 double plan_speed = sqlite3_column_double(stmt, 8);
1638 std::string time_format =
1639 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 9));
1640
1641 int width = sqlite3_column_int(stmt, 10);
1642 int style = sqlite3_column_int(stmt, 11);
1643 std::string color =
1644 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 12));
1645
1646 Route* route = NULL;
1647
1648 // Add the route_points
1649 const char* sql = R"(
1650 SELECT latitude, longitude, timestamp, point_order
1651 FROM trk_points
1652 WHERE track_guid = ?
1653 ORDER BY point_order ASC
1654 )";
1655
1656 const char* sqlp =
1657 "SELECT p.guid, "
1658 "p.lat, "
1659 "p.lon, "
1660 "p.Symbol, "
1661 "p.Name, "
1662 "p.description, "
1663 "p.TideStation, "
1664 "p.plan_speed, "
1665 "p.etd, "
1666 "p.Type, "
1667 "p.Time, "
1668 "p.ArrivalRadius, "
1669 "p.RangeRingsNumber, "
1670 "p.RangeRingsStep, "
1671 "p.RangeRingsStepUnits, "
1672 "p.RangeRingsVisible, "
1673 "p.RangeRingsColour, "
1674 "p.ScaleMin, "
1675 "p.ScaleMax, "
1676 "p.UseScale, "
1677 "p.visibility, "
1678 "p.viz_name, "
1679 "p.shared, "
1680 "p.isolated, "
1681 "p.created_at "
1682 "FROM routepoints_link tp "
1683 "JOIN routepoints p ON p.guid = tp.point_guid "
1684 "WHERE tp.route_guid = ? "
1685 "ORDER BY tp.point_order ASC";
1686
1687 sqlite3_stmt* stmtp;
1688 if (sqlite3_prepare_v2(m_db, sqlp, -1, &stmtp, nullptr) != SQLITE_OK) {
1689 ReportError("LoadAllRoutes-B:prepare");
1690 return false;
1691 }
1692
1693 sqlite3_bind_text(stmtp, 1, guid.c_str(), -1, SQLITE_TRANSIENT);
1694
1695 int GPXSeg = 0;
1696 int errcode = SQLITE_OK;
1697 while ((errcode = sqlite3_step(stmtp)) == SQLITE_ROW) {
1698 if (!route) {
1699 route = new Route;
1700 route->m_GUID = guid;
1701
1702 // Set all the route attributes
1703 route->SetVisible(visibility == 1);
1704 route->m_RouteNameString = name.c_str();
1705 route->m_RouteDescription = description.c_str();
1706 route->m_RouteStartString = start_string.c_str();
1707 route->m_RouteEndString = end_string.c_str();
1708 route->SetVisible(visibility == 1);
1709 route->SetSharedWPViz(sharewp_viz == 1);
1710 route->m_PlannedDeparture.Set((time_t)planned_departure_ticks);
1711 route->m_PlannedSpeed = plan_speed;
1712 route->m_TimeDisplayFormat = time_format.c_str();
1713
1714 route->m_width = width;
1715 route->m_style = (wxPenStyle)style;
1716 route->m_Colour = color;
1717 }
1718
1719 // Grab all the point attributes from the SELECT statement
1720 int col = 0;
1721 std::string point_guid =
1722 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1723 double latitude = sqlite3_column_double(stmtp, col++);
1724 double longitude = sqlite3_column_double(stmtp, col++);
1725 std::string symbol =
1726 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1727 std::string name =
1728 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1729 std::string description =
1730 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1731 std::string tide_station =
1732 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1733 double plan_speed = sqlite3_column_double(stmtp, col++);
1734 time_t etd_epoch = sqlite3_column_int(stmtp, col++);
1735 std::string type =
1736 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1737 std::string time =
1738 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1739 double arrival_radius = sqlite3_column_double(stmtp, col++);
1740
1741 int range_ring_number = sqlite3_column_int(stmtp, col++);
1742 double range_ring_step = sqlite3_column_double(stmtp, col++);
1743 int range_ring_units = sqlite3_column_int(stmtp, col++);
1744 int range_ring_visible = sqlite3_column_int(stmtp, col++);
1745 std::string range_ring_color =
1746 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1747
1748 int scamin = sqlite3_column_int(stmtp, col++);
1749 int scamax = sqlite3_column_int(stmtp, col++);
1750 int use_scaminmax = sqlite3_column_int(stmtp, col++);
1751
1752 int visibility = sqlite3_column_int(stmtp, col++);
1753 int viz_name = sqlite3_column_int(stmtp, col++);
1754 int shared = sqlite3_column_int(stmtp, col++);
1755 int isolated = sqlite3_column_int(stmtp, col++);
1756 std::string point_created_at =
1757 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1758
1759 RoutePoint* point;
1760 // RoutePoint exists already, in another route?
1761 auto containing_route =
1762 g_pRouteMan->FindRouteContainingWaypoint(point_guid);
1763
1764 if (containing_route) {
1765 point = containing_route->GetPoint(point_guid);
1766 } else {
1767 point =
1768 new RoutePoint(latitude, longitude, symbol, name, point_guid, true);
1769
1770 point->m_MarkDescription = description;
1771 point->m_TideStation = tide_station;
1772 point->SetPlannedSpeed(plan_speed);
1773
1774 wxDateTime etd;
1775 etd.Set((time_t)etd_epoch);
1776 if (etd.IsValid()) point->SetETD(etd);
1777
1778 point->m_WaypointArrivalRadius = arrival_radius;
1779
1780 point->m_iWaypointRangeRingsNumber = range_ring_number;
1781 point->m_fWaypointRangeRingsStep = range_ring_step;
1782 point->m_iWaypointRangeRingsStepUnits = range_ring_units;
1783 point->SetShowWaypointRangeRings(range_ring_visible == 1);
1784 // TODO
1785 point->m_wxcWaypointRangeRingsColour.Set(range_ring_color);
1786
1787 point->SetScaMin(scamin);
1788 point->SetScaMax(scamax);
1789 point->SetUseSca(use_scaminmax == 1);
1790
1791 point->SetVisible(visibility == 1);
1792 point->SetNameShown(viz_name == 1);
1793 point->SetShared(shared == 1);
1794 point->m_bIsolatedMark = (isolated == 1);
1795
1796 if (point_created_at.size()) {
1797 // Convert from sqLite default date/time format to wxDateTime
1798 // sqLite format uses UTC, so conversion to epoch_time is clear.
1799 std::tm tm = {};
1800 std::istringstream ss(point_created_at);
1801 ss >> std::get_time(&tm, "%Y-%m-%d %H:%M:%S");
1802 time_t epoch_time = mktime(&tm);
1803 point->m_CreateTimeX = epoch_time;
1804 }
1805
1806 // Add the point HTML links
1807 const char* sqlh = R"(
1808 SELECT guid, html_link, html_description, html_type
1809 FROM routepoint_html_links
1810 WHERE routepoint_guid = ?
1811 ORDER BY html_type ASC
1812 )";
1813
1814 sqlite3_stmt* stmt;
1815
1816 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1817 sqlite3_bind_text(stmt, 1, point->m_GUID.ToStdString().c_str(), -1,
1818 SQLITE_TRANSIENT);
1819
1820 while (sqlite3_step(stmt) == SQLITE_ROW) {
1821 std::string link_guid =
1822 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1823 std::string link_link =
1824 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1825 std::string link_description =
1826 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1827 std::string link_type =
1828 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1829
1830 Hyperlink* h = new Hyperlink();
1831 h->DescrText = link_description;
1832 h->Link = link_link;
1833 h->LType = link_type;
1834
1835 point->m_HyperlinkList->Append(h);
1836 }
1837 }
1838 }
1839
1840 route->AddPoint(point);
1841 } // route points
1842 sqlite3_finalize(stmtp);
1843 if (errcode != SQLITE_DONE) {
1844 ReportError("LoadAllRoutes-A:step");
1845 return false;
1846 }
1847
1848 // Add route html links
1849 if (route) {
1850 // Add the HTML links
1851 const char* sqlh = R"(
1852 SELECT guid, html_link, html_description, html_type
1853 FROM route_html_links
1854 WHERE route_guid = ?
1855 ORDER BY html_type ASC
1856 )";
1857
1858 sqlite3_stmt* stmt;
1859
1860 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1861 sqlite3_bind_text(stmt, 1, route->m_GUID.ToStdString().c_str(), -1,
1862 SQLITE_TRANSIENT);
1863
1864 int errcode2 = SQLITE_OK;
1865 while ((errcode2 = sqlite3_step(stmt)) == SQLITE_ROW) {
1866 std::string link_guid =
1867 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1868 std::string link_link =
1869 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1870 std::string link_description =
1871 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1872 std::string link_type =
1873 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1874
1875 Hyperlink* h = new Hyperlink();
1876 h->DescrText = link_description;
1877 h->Link = link_link;
1878 h->LType = link_type;
1879
1880 route->m_HyperlinkList->Append(h);
1881 }
1882 if (errcode != SQLITE_DONE) {
1883 ReportError("LoadAllRoutes-B:step");
1884 return false;
1885 }
1886
1887 sqlite3_finalize(stmt);
1888
1889 } else {
1890 ReportError("LoadAllRoutes-B:prepare");
1891 return false;
1892 }
1893 }
1894
1895 // Insert the route into the global list
1896 InsertRouteA(route,
1897 nullptr); // NavObjectChanges::getInstance() //TODO adding
1898 // changes will force the xml file to be updated?
1899
1900 } // routes
1901 if (errcode0 != SQLITE_DONE) {
1902 ReportError("LoadAllRoutes-C:step");
1903 return false;
1904 }
1905
1906 return true;
1907}
1908
1909bool NavObj_dB::LoadAllPoints() {
1910 const char* sqlp =
1911 "SELECT "
1912 "p.guid, "
1913 "p.lat, "
1914 "p.lon, "
1915 "p.Symbol, "
1916 "p.Name, "
1917 "p.description, "
1918 "p.TideStation, "
1919 "p.plan_speed, "
1920 "p.etd, "
1921 "p.Type, "
1922 "p.Time, "
1923 "p.ArrivalRadius, "
1924 "p.RangeRingsNumber, "
1925 "p.RangeRingsStep, "
1926 "p.RangeRingsStepUnits, "
1927 "p.RangeRingsVisible, "
1928 "p.RangeRingsColour, "
1929 "p.ScaleMin, "
1930 "p.ScaleMax, "
1931 "p.UseScale, "
1932 "p.visibility, "
1933 "p.viz_name, "
1934 "p.shared, "
1935 "p.isolated, "
1936 "p.created_at "
1937 "FROM routepoints p ";
1938
1939 RoutePoint* point = nullptr;
1940
1941 sqlite3_stmt* stmtp;
1942 if (sqlite3_prepare_v2(m_db, sqlp, -1, &stmtp, nullptr) != SQLITE_OK) {
1943 return false;
1944 }
1945
1946 while (sqlite3_step(stmtp) == SQLITE_ROW) {
1947 // Grab all the point attributes from the SELECT statement
1948 int col = 0;
1949 std::string point_guid =
1950 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1951 double latitude = sqlite3_column_double(stmtp, col++);
1952 double longitude = sqlite3_column_double(stmtp, col++);
1953 std::string symbol =
1954 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1955 std::string name =
1956 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1957 std::string description =
1958 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1959 std::string tide_station =
1960 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1961 double plan_speed = sqlite3_column_double(stmtp, col++);
1962 time_t etd = sqlite3_column_int(stmtp, col++);
1963 std::string type =
1964 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1965 std::string point_time_string =
1966 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1967 double arrival_radius = sqlite3_column_double(stmtp, col++);
1968
1969 int range_ring_number = sqlite3_column_int(stmtp, col++);
1970 double range_ring_step = sqlite3_column_double(stmtp, col++);
1971 int range_ring_units = sqlite3_column_int(stmtp, col++);
1972 int range_ring_visible = sqlite3_column_int(stmtp, col++);
1973 std::string range_ring_color =
1974 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1975
1976 int scamin = sqlite3_column_int(stmtp, col++);
1977 int scamax = sqlite3_column_int(stmtp, col++);
1978 int use_scaminmax = sqlite3_column_int(stmtp, col++);
1979
1980 int visibility = sqlite3_column_int(stmtp, col++);
1981 int viz_name = sqlite3_column_int(stmtp, col++);
1982 int shared = sqlite3_column_int(stmtp, col++);
1983 int isolated = sqlite3_column_int(stmtp, col++);
1984 std::string point_created_at =
1985 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1986
1987 if (isolated) {
1988 point =
1989 new RoutePoint(latitude, longitude, symbol, name, point_guid, false);
1990
1991 point->m_MarkDescription = description;
1992 point->m_TideStation = tide_station;
1993 point->SetPlannedSpeed(plan_speed);
1994 point->m_WaypointArrivalRadius = arrival_radius;
1995
1996 point->m_iWaypointRangeRingsNumber = range_ring_number;
1997 point->m_fWaypointRangeRingsStep = range_ring_step;
1998 point->m_iWaypointRangeRingsStepUnits = range_ring_units;
1999 point->SetShowWaypointRangeRings(range_ring_visible == 1);
2000 // TODO
2001 // point->m_wxcWaypointRangeRingsColour = range_ring_color;
2002
2003 point->SetScaMin(scamin);
2004 point->SetScaMax(scamax);
2005 point->SetUseSca(use_scaminmax == 1);
2006
2007 point->SetVisible(visibility == 1);
2008 point->SetNameShown(viz_name == 1);
2009 point->SetShared(shared == 1);
2010 point->m_bIsolatedMark = (isolated == 1);
2011
2012 if (point_created_at.size()) {
2013 // Convert from sqLite default date/time format to wxDateTime
2014 // sqLite format uses UTC, so conversion to epoch_time is clear.
2015 std::tm tm = {};
2016 std::istringstream ss(point_created_at);
2017 ss >> std::get_time(&tm, "%Y-%m-%d %H:%M:%S");
2018 time_t epoch_time = mktime(&tm);
2019 point->m_CreateTimeX = epoch_time;
2020 }
2021
2022 // Add it here
2023 pWayPointMan->AddRoutePoint(point);
2024 pSelect->AddSelectableRoutePoint(point->m_lat, point->m_lon, point);
2025 }
2026 } // points
2027 sqlite3_finalize(stmtp);
2028
2029 if (point) {
2030 // Add the point HTML links
2031 const char* sqlh = R"(
2032 SELECT guid, html_link, html_description, html_type
2033 FROM routepoint_html_links
2034 WHERE routepoint_guid = ?
2035 ORDER BY html_type ASC
2036 )";
2037
2038 sqlite3_stmt* stmt;
2039
2040 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
2041 sqlite3_bind_text(stmt, 1, point->m_GUID.ToStdString().c_str(), -1,
2042 SQLITE_TRANSIENT);
2043
2044 while (sqlite3_step(stmt) == SQLITE_ROW) {
2045 std::string link_guid =
2046 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
2047 std::string link_link =
2048 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
2049 std::string link_description =
2050 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
2051 std::string link_type =
2052 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
2053
2054 Hyperlink* h = new Hyperlink();
2055 h->DescrText = link_description;
2056 h->Link = link_link;
2057 h->LType = link_type;
2058
2059 point->m_HyperlinkList->Append(h);
2060 }
2061
2062 sqlite3_finalize(stmt);
2063
2064 } else {
2065 return false;
2066 }
2067 }
2068 return true;
2069}
2070bool NavObj_dB::InsertRoutePoint(RoutePoint* point) {
2071 bool rv = false;
2072 char* errMsg = 0;
2073
2074 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
2075 // Insert a new route point
2076 wxString sql =
2077 wxString::Format("INSERT INTO routepoints (guid) VALUES ('%s')",
2078 point->m_GUID.ToStdString().c_str());
2079 if (!executeSQL(m_db, sql)) {
2080 return false;
2081 }
2082 }
2083
2084 UpdateDBRoutePointAttributes(point);
2085
2086 // Add HTML links to routepoint
2087 int NbrOfLinks = point->m_HyperlinkList->GetCount();
2088 if (NbrOfLinks > 0) {
2089 wxHyperlinkListNode* linknode = point->m_HyperlinkList->GetFirst();
2090 while (linknode) {
2091 Hyperlink* link = linknode->GetData();
2092
2093 if (!RoutePointHtmlLinkExists(m_db, link->GUID)) {
2094 InsertRoutePointHTML(m_db, point->m_GUID.ToStdString(), link->GUID,
2095 link->DescrText.ToStdString(),
2096 link->Link.ToStdString(),
2097 link->LType.ToStdString());
2098 }
2099 linknode = linknode->GetNext();
2100 }
2101 }
2102
2103 return true;
2104}
2105
2106bool NavObj_dB::DeleteRoutePoint(RoutePoint* point) {
2107 if (m_importing) return false;
2108 if (!point) return false;
2109
2110 std::string point_guid = point->m_GUID.ToStdString();
2111
2112 // DeleteAllCommentsForRoutePoint(m_db, point_guid);
2113
2114 const char* sql = "DELETE FROM routepoints WHERE guid = ?";
2115 sqlite3_stmt* stmt;
2116
2117 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
2118 sqlite3_bind_text(stmt, 1, point_guid.c_str(), -1, SQLITE_TRANSIENT);
2119 if (sqlite3_step(stmt) != SQLITE_DONE) {
2120 ReportError("DeleteRoutePoint:step");
2121 sqlite3_finalize(stmt);
2122 return false;
2123 }
2124
2125 sqlite3_finalize(stmt);
2126 } else {
2127 return false;
2128 }
2129 return true;
2130}
2131
2132bool NavObj_dB::UpdateRoutePoint(RoutePoint* point) {
2133 if (m_importing) return false;
2134 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) return false;
2135 UpdateDBRoutePointAttributes(point);
2136 return true;
2137}
2138
2139bool NavObj_dB::Backup(wxString fileName) {
2140 sqlite3_backup* pBackup;
2141 sqlite3* backupDatabase;
2142
2143 if (sqlite3_open(fileName.c_str(), &backupDatabase) == SQLITE_OK) {
2144 pBackup = sqlite3_backup_init(backupDatabase, "main", m_db, "main");
2145 if (pBackup) {
2146 int result = sqlite3_backup_step(pBackup, -1);
2147 if ((result == SQLITE_OK) || (result == SQLITE_DONE)) {
2148 if (sqlite3_backup_finish(pBackup) == SQLITE_OK) {
2149 sqlite3_close_v2(backupDatabase);
2150 return true;
2151 }
2152 }
2153 }
2154 }
2155 wxLogMessage("navobj database backup error: %s", sqlite3_errmsg(m_db));
2156 return false;
2157}
AbstractPlatform::GetPrivateDataDir
wxString & GetPrivateDataDir()
Return dir path for opencpn.log, etc., respecting -c cli option.
Definition base_platform.cpp:301
NavObj_dB
The navobj SQLite container object, a singleton.
Definition navobj_db.h:36
RoutePoint
Represents a waypoint or mark within the navigation system.
Definition route_point.h:70
RoutePoint::m_HyperlinkList
HyperlinkList * m_HyperlinkList
List of hyperlinks associated with this waypoint.
Definition route_point.h:545
RoutePoint::m_wxcWaypointRangeRingsColour
wxColour m_wxcWaypointRangeRingsColour
Color for the range rings display.
Definition route_point.h:508
RoutePoint::m_MarkDescription
wxString m_MarkDescription
Description text for the waypoint.
Definition route_point.h:419
RoutePoint::m_iWaypointRangeRingsNumber
int m_iWaypointRangeRingsNumber
Number of range rings to display around the waypoint.
Definition route_point.h:495
RoutePoint::m_GUID
wxString m_GUID
Globally Unique Identifier for the waypoint.
Definition route_point.h:423
RoutePoint::m_CreateTimeX
wxDateTime m_CreateTimeX
Creation timestamp for the waypoint, in UTC.
Definition route_point.h:553
RoutePoint::m_bIsolatedMark
bool m_bIsolatedMark
Flag indicating if the waypoint is a standalone mark.
Definition route_point.h:395
RoutePoint::GetManualETD
wxDateTime GetManualETD()
Retrieves the manually set Estimated Time of Departure for this waypoint, in UTC.
Definition route_point.cpp:508
RoutePoint::m_timestring
wxString m_timestring
String representation of the waypoint creation time.
Definition route_point.h:549
RoutePoint::GetPlannedSpeed
double GetPlannedSpeed()
Return the planned speed associated with this waypoint.
Definition route_point.cpp:446
RoutePoint::m_WaypointArrivalRadius
double m_WaypointArrivalRadius
Arrival radius in nautical miles.
Definition route_point.h:541
RoutePoint::m_iWaypointRangeRingsStepUnits
int m_iWaypointRangeRingsStepUnits
Units for the range rings step (0=nm, 1=km).
Definition route_point.h:504
RoutePoint::m_fWaypointRangeRingsStep
float m_fWaypointRangeRingsStep
Distance between consecutive range rings.
Definition route_point.h:500
RoutePoint::m_TideStation
wxString m_TideStation
Associated tide station identifier.
Definition route_point.h:427
RoutePoint::m_bShowWaypointRangeRings
bool m_bShowWaypointRangeRings
Flag indicating if range rings should be shown around the waypoint.
Definition route_point.h:491
RoutePoint::SetETD
void SetETD(const wxDateTime &etd)
Sets the Estimated Time of Departure for this waypoint, in UTC.
Definition route_point.cpp:531
Route
Represents a navigational route in the navigation system.
Definition route.h:98
Route::m_PlannedSpeed
double m_PlannedSpeed
Default planned speed for the route in knots.
Definition route.h:320
Route::m_RouteStartString
wxString m_RouteStartString
Name or description of the route's starting point.
Definition route.h:251
Route::m_RouteDescription
wxString m_RouteDescription
Additional descriptive information about the route.
Definition route.h:261
Route::m_Colour
wxString m_Colour
Color name for rendering the route on the chart.
Definition route.h:345
Route::m_RouteEndString
wxString m_RouteEndString
Name or description of the route's ending point.
Definition route.h:256
Route::m_style
wxPenStyle m_style
Style of the route line when rendered on the chart.
Definition route.h:292
Route::m_TimeDisplayFormat
wxString m_TimeDisplayFormat
Format for displaying times in the UI.
Definition route.h:330
Route::m_width
int m_width
Width of the route line in pixels when rendered on the chart.
Definition route.h:287
Route::m_RouteNameString
wxString m_RouteNameString
User-assigned name for the route.
Definition route.h:246
Route::m_GUID
wxString m_GUID
Globally unique identifier for this route.
Definition route.h:272
Route::m_PlannedDeparture
wxDateTime m_PlannedDeparture
Planned departure time for the route, in UTC.
Definition route.h:325
Route::m_HyperlinkList
HyperlinkList * m_HyperlinkList
List of hyperlinks associated with this route.
Definition route.h:360
Routeman::DeleteRoute
bool DeleteRoute(Route *pRoute)
Definition routeman.cpp:834
TrackPoint
Represents a single point in a track.
Definition track.h:53
Track
Represents a track, which is a series of connected track points.
Definition track.h:111
WayPointman::AddRoutePoint
bool AddRoutePoint(RoutePoint *prp)
Add a point to list which owns it.
Definition routeman.cpp:1085
WayPointman::RemoveRoutePoint
bool RemoveRoutePoint(RoutePoint *prp)
Remove a routepoint from list if present, deallocate it all cases.
Definition routeman.cpp:1094
ocpn::exists
bool exists(const std::string &name)
Definition ocpn_utils.cpp:75
navobj_db.h
Class NavObj_dB.
notification.h
Class Notification.
notification_manager.h
Class NotificationManager.