OpenCPN Partial API docs
Loading...
Searching...
No Matches
navobj_db.cpp
Go to the documentation of this file.
1/***************************************************************************
2 * Copyright (C) 2025 by David S. Register *
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 2 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 <https://www.gnu.org/licenses/>. *
16 **************************************************************************/
17
24#include <cmath>
25#include <iomanip>
26#include <memory>
27#include <string>
28#include <vector>
29
30#include <wx/dir.h>
31#include <wx/filename.h>
32
33#include "model/base_platform.h"
34#include "model/comm_appmsg_bus.h"
35#include "model/navobj_db.h"
36#include "model/navutil_base.h"
37#include "model/notification.h"
38#include "model/notification_manager.h"
39#include "model/routeman.h"
40
41static void ReportError(const std::string zmsg); // forward
42
43static bool executeSQL(sqlite3* db, const char* sql) {
44 char* errMsg = nullptr;
45 if (sqlite3_exec(db, sql, nullptr, nullptr, &errMsg) != SQLITE_OK) {
46 wxString msg =
47 wxString::Format(_("navobj database error.") + " %s", errMsg);
48 wxLogMessage(msg);
49 auto& noteman = NotificationManager::GetInstance();
50 noteman.AddNotification(NotificationSeverity::kWarning, msg.ToStdString());
51 sqlite3_free(errMsg);
52 return false;
53 }
54 return true;
55}
56
57static bool executeSQL(sqlite3* db, wxString& sql) {
58 return executeSQL(db, sql.ToStdString().c_str());
59}
60
61bool CreateTables(sqlite3* db) {
62 // Track tables
63 const char* create_tables_sql = R"(
64 CREATE TABLE IF NOT EXISTS tracks (
65 guid TEXT PRIMARY KEY NOT NULL,
66 name TEXT,
67 description TEXT,
68 visibility INTEGER,
69 start_string TEXT,
70 end_string TEXT,
71 width INTEGER,
72 style INTEGER,
73 color TEXT,
74 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
75 );
76
77 CREATE TABLE IF NOT EXISTS trk_points (
78 track_guid TEXT NOT NULL,
79 latitude REAL NOT NULL,
80 longitude REAL NOT NULL,
81 timestamp TEXT NOT NULL,
82 point_order INTEGER,
83 FOREIGN KEY (track_guid) REFERENCES tracks(guid) ON DELETE CASCADE
84 );
85
86
87 CREATE TABLE IF NOT EXISTS track_html_links (
88 guid TEXT PRIMARY KEY,
89 track_guid TEXT NOT NULL,
90 html_link TEXT,
91 html_description TEXT,
92 html_type TEXT,
93 FOREIGN KEY (track_guid) REFERENCES tracks(guid) ON DELETE CASCADE
94 );
95
96
97 CREATE TABLE IF NOT EXISTS routes (
98 guid TEXT PRIMARY KEY NOT NULL,
99 name TEXT,
100 start_string TEXT,
101 end_string TEXT,
102 description TEXT,
103 planned_departure TEXT,
104 plan_speed REAL,
105 time_format TEXT,
106 style INTEGER,
107 width INTEGER,
108 color TEXT,
109 visibility INTEGER,
110 shared_wp_viz INTEGER,
111 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
112 );
113
114
115 CREATE TABLE IF NOT EXISTS routepoints (
116 guid TEXT PRIMARY KEY NOT NULL,
117 lat REAL,
118 lon REAL,
119 Symbol TEXT,
120 Name TEXT,
121 description TEXT,
122 TideStation TEXT,
123 plan_speed REAL,
124 etd INTEGER,
125 Type TEXT,
126 Time TEXT,
127 ArrivalRadius REAL,
128 RangeRingsNumber INTEGER,
129 RangeRingsStep REAL,
130 RangeRingsStepUnits INTEGER,
131 RangeRingsVisible INTEGER,
132 RangeRingsColour TEXT,
133 ScaleMin INTEGER,
134 ScaleMax INTEGER,
135 UseScale INTEGER,
136 visibility INTEGER,
137 viz_name INTEGER,
138 shared INTEGER,
139 isolated INTEGER,
140 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
141 );
142
143 CREATE TABLE IF NOT EXISTS routepoints_link (
144 route_guid TEXT,
145 point_guid TEXT,
146 point_order INTEGER,
147 PRIMARY KEY (route_guid, point_order),
148 FOREIGN KEY (route_guid) REFERENCES routes(guid) ON DELETE CASCADE
149 );
150
151 CREATE TABLE IF NOT EXISTS route_html_links (
152 guid TEXT PRIMARY KEY,
153 route_guid TEXT NOT NULL,
154 html_link TEXT,
155 html_description TEXT,
156 html_type TEXT,
157 FOREIGN KEY (route_guid) REFERENCES routes(guid) ON DELETE CASCADE
158 );
159
160 CREATE TABLE IF NOT EXISTS routepoint_html_links (
161 guid TEXT PRIMARY KEY,
162 routepoint_guid TEXT NOT NULL,
163 html_link TEXT,
164 html_description TEXT,
165 html_type TEXT,
166 FOREIGN KEY (routepoint_guid) REFERENCES routepoints(guid) ON DELETE CASCADE
167 );
168
169 CREATE INDEX IF NOT EXISTS idx_track_points
170 ON trk_points (track_guid);
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
533static void 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 for (RoutePoint* point : *pointlist) {
659 if (point->m_bIsolatedMark) {
660 m_nImportObjects++;
661 m_nimportPoints++;
662 }
663 }
664
665 input_set->LoadAllGPXRouteObjects();
666 for (Route* route_import : *pRouteList) {
667 m_nImportObjects++;
668 m_nimportRoutes++;
669 m_nImportObjects += route_import->GetnPoints();
670 }
671
672 input_set->LoadAllGPXTrackObjects();
673 m_nImportObjects += g_TrackList.size();
674 m_nimportTracks = g_TrackList.size();
675
676 for (Track* track_import : g_TrackList) {
677 m_nImportObjects += track_import->GetnPoints();
678 }
679 }
680 delete input_set;
681}
682
683bool NavObj_dB::ImportLegacyTracks() {
684 std::vector<Track*> tracks_added;
685 // Add all tracks to database
686 int ntrack = 0;
687 for (Track* track_import : g_TrackList) {
688 if (InsertTrack(track_import)) {
689 tracks_added.push_back(track_import);
690 }
691 ntrack++;
692 m_import_progesscount += track_import->GetnPoints() + 1;
693 wxString msg = wxString::Format("Tracks %d/%d", ntrack, m_nimportTracks);
694 m_pImportProgress->Update(m_import_progesscount, msg);
695 m_pImportProgress->Show();
696 }
697
698 // Delete all tracks that were successfully added
699 for (Track* ptrack : tracks_added) {
700 if (ptrack->m_bIsInLayer) continue;
701 g_pRouteMan->DeleteTrack(ptrack);
702 }
703
704 return true;
705}
706
707bool NavObj_dB::ImportLegacyRoutes() {
708 std::vector<Route*> routes_added;
709 // Add all routes to database
710 int nroute = 0;
711 for (Route* route_import : *pRouteList) {
712 if (InsertRoute(route_import)) {
713 routes_added.push_back(route_import);
714 }
715 nroute++;
716 m_import_progesscount += route_import->GetnPoints() + 1;
717 wxString msg = wxString::Format("Routes %d/%d", nroute, m_nimportRoutes);
718 m_pImportProgress->Update(m_import_progesscount, msg);
719 m_pImportProgress->Show();
720 }
721
722 // Delete all routes that were successfully added
723 for (Route* route : routes_added) {
724 g_pRouteMan->DeleteRoute(route);
725 }
726
727 // There may have been some points left as isolated orphans
728 // Delete them too.
729 pWayPointMan->DeleteAllWaypoints(true);
730
731 return true;
732}
733
734bool NavObj_dB::ImportLegacyPoints() {
735 std::vector<RoutePoint*> points_added;
736 // Add all isolated points to database
737 int npoint = 0;
738 int nmod = 1;
739 if (m_nimportPoints > 1000) nmod = 10;
740 if (m_nimportPoints > 10000) nmod = 100;
741
742 for (RoutePoint* point : *pWayPointMan->GetWaypointList()) {
743 if (point->m_bIsolatedMark) {
744 if (InsertRoutePointDB(m_db, point)) {
745 points_added.push_back(point);
746 }
747
748 UpdateDBRoutePointAttributes(point);
749 m_import_progesscount += 1;
750 if ((npoint % nmod) == 0) {
751 wxString msg =
752 wxString::Format("Points %d/%d", npoint, m_nimportPoints);
753 m_pImportProgress->Update(m_import_progesscount, msg);
754 m_pImportProgress->Show();
755 }
756 npoint++;
757 }
758 }
759
760 // Delete all points that were successfully added
761 for (RoutePoint* point : points_added) {
762 pWayPointMan->RemoveRoutePoint(point);
763 delete point;
764 }
765
766 return true;
767}
768
769void NavObj_dB::LoadNavObjects() {
770 LoadAllPoints();
771 LoadAllRoutes();
772 LoadAllTracks();
773}
774
775bool NavObj_dB::InsertTrack(Track* track) {
776 if (TrackExists(m_db, track->m_GUID.ToStdString())) return false;
777
778 bool rv = false;
779 char* errMsg = 0;
780 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
781 if (errMsg) {
782 ReportError("InsertTrack:BEGIN TRANSACTION");
783 return false;
784 }
785
786 // Insert a new track
787 wxString sql = wxString::Format("INSERT INTO tracks (guid) VALUES ('%s')",
788 track->m_GUID.ToStdString().c_str());
789 if (!executeSQL(m_db, sql)) {
790 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
791 return false;
792 }
793
794 UpdateDBTrackAttributes(track);
795
796 // Add any existing trkpoints
797 for (int i = 0; i < track->GetnPoints(); i++) {
798 auto point = track->GetPoint(i);
799 // Add the bare trkpoint
800 InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
801 point->m_lon, point->GetTimeString(), i);
802 }
803
804 // Add HTML links to track
805 int NbrOfLinks = track->m_TrackHyperlinkList->size();
806 if (NbrOfLinks > 0) {
807 auto& list = track->m_TrackHyperlinkList;
808 for (auto it = list->begin(); it != list->end(); ++it) {
809 Hyperlink* link = *it;
810 if (!TrackHtmlLinkExists(m_db, link->GUID)) {
811 InsertTrackHTML(m_db, track->m_GUID.ToStdString(), link->GUID,
812 link->DescrText.ToStdString(), link->Link.ToStdString(),
813 link->LType.ToStdString());
814 }
815 }
816 }
817 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
818 rv = true;
819 if (errMsg) rv = false;
820
821 return rv;
822};
823
824bool NavObj_dB::UpdateTrack(Track* track) {
825 bool rv = false;
826 char* errMsg = 0;
827
828 if (!TrackExists(m_db, track->m_GUID.ToStdString())) return false;
829
830 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
831 if (errMsg) {
832 ReportError("UpdateTrack:BEGIN TRANSACTION");
833 return false;
834 }
835
836 UpdateDBTrackAttributes(track);
837
838 // Delete and re-add track points
839 const char* sql = "DELETE FROM trk_points WHERE track_guid = ?";
840 sqlite3_stmt* stmt;
841 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
842 sqlite3_bind_text(stmt, 1, track->m_GUID.ToStdString().c_str(), -1,
843 SQLITE_TRANSIENT);
844 } else {
845 ReportError("UpdateTrack:prepare");
846 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
847 return false;
848 }
849 if (sqlite3_step(stmt) != SQLITE_DONE) {
850 ReportError("UpdateTrack:step");
851 sqlite3_finalize(stmt);
852 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
853 return false;
854 }
855 sqlite3_finalize(stmt);
856
857 // re-add trackpoints
858 for (int i = 0; i < track->GetnPoints(); i++) {
859 auto point = track->GetPoint(i);
860 // Add the bare point
861 if (point) {
862 InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
863 point->m_lon, point->GetTimeString(), i);
864 }
865 }
866
867 sqlite3_exec(m_db, "COMMIT", 0, 0, nullptr);
868
869 rv = true;
870 if (errMsg) rv = false;
871 return rv;
872};
873
874bool NavObj_dB::UpdateDBTrackAttributes(Track* track) {
875 const char* sql =
876 "UPDATE tracks SET "
877 "name = ?, "
878 "description = ?, "
879 "visibility = ?, "
880 "start_string = ?, "
881 "end_string = ?, "
882 "width = ?, "
883 "style = ?, "
884 "color = ? "
885 "WHERE guid = ?";
886
887 sqlite3_stmt* stmt;
888 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
889 sqlite3_bind_text(stmt, 1, track->GetName().ToStdString().c_str(), -1,
890 SQLITE_TRANSIENT);
891 sqlite3_bind_text(stmt, 2, track->m_TrackDescription.ToStdString().c_str(),
892 -1, SQLITE_TRANSIENT);
893 sqlite3_bind_int(stmt, 3, track->m_bVisible);
894 sqlite3_bind_text(stmt, 4, track->m_TrackStartString.ToStdString().c_str(),
895 -1, SQLITE_TRANSIENT);
896 sqlite3_bind_text(stmt, 5, track->m_TrackEndString.ToStdString().c_str(),
897 -1, SQLITE_TRANSIENT);
898 sqlite3_bind_int(stmt, 6, track->m_width);
899 sqlite3_bind_int(stmt, 7,
900 (int)(track->m_style)); // track->m_style.c_str(),
901 sqlite3_bind_text(stmt, 8, track->m_Colour.ToStdString().c_str(), -1,
902 SQLITE_TRANSIENT);
903 sqlite3_bind_text(stmt, 9, track->m_GUID.c_str(), track->m_GUID.size(),
904 SQLITE_TRANSIENT);
905 } else {
906 return false;
907 }
908
909 if (sqlite3_step(stmt) != SQLITE_DONE) {
910 ReportError("UpdateDBTrackAttributesA:step");
911 sqlite3_finalize(stmt);
912 return false;
913 }
914
915 sqlite3_finalize(stmt);
916
917 // Update the HTML links
918 // The list of links is freshly rebuilt when this method is called
919 // So start by deleting all existing bcomments
920 DeleteAllCommentsForTrack(m_db, track->m_GUID.ToStdString());
921
922 // Now add all the links to db
923 int NbrOfLinks = track->m_TrackHyperlinkList->size();
924 if (NbrOfLinks > 0) {
925 auto& list = track->m_TrackHyperlinkList;
926 for (auto it = list->begin(); it != list->end(); ++it) {
927 Hyperlink* link = *it;
928
929 if (!TrackHtmlLinkExists(m_db, link->GUID)) {
930 InsertTrackHTML(m_db, track->m_GUID.ToStdString(), link->GUID,
931 link->DescrText.ToStdString(), link->Link.ToStdString(),
932 link->LType.ToStdString());
933 } else {
934 const char* sql =
935 "UPDATE track_html_links SET "
936 "html_link = ?, "
937 "html_description = ?, "
938 "html_type = ? "
939 "WHERE guid = ?";
940 sqlite3_stmt* stmt;
941 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
942 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
943 SQLITE_TRANSIENT);
944 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
945 SQLITE_TRANSIENT);
946 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
947 SQLITE_TRANSIENT);
948 }
949 if (sqlite3_step(stmt) != SQLITE_DONE) {
950 ReportError("UpdateDBTRackAttributesB:step");
951 sqlite3_finalize(stmt);
952 return false;
953 }
954 sqlite3_finalize(stmt);
955 }
956 }
957 }
958
959 return true;
960}
961
962bool NavObj_dB::AddTrackPoint(Track* track, TrackPoint* point) {
963 // If track does not yet exist in dB, return
964 if (!TrackExists(m_db, track->m_GUID.ToStdString())) return false;
965
966 // Get next point order
967 int this_point_index = track->GetnPoints();
968
969 // Add the linked point to the dB
970 if (!InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
971 point->m_lon, point->GetTimeString(),
972 this_point_index - 1))
973 return false;
974
975 return true;
976}
977
978bool NavObj_dB::LoadAllTracks() {
979 const char* sql = R"(
980 SELECT guid, name,
981 description, visibility, start_string, end_string,
982 width, style, color,
983 created_at
984 FROM tracks
985 ORDER BY created_at ASC
986 )";
987
988 sqlite3_stmt* stmt;
989 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
990 return false;
991 }
992
993 while (sqlite3_step(stmt) == SQLITE_ROW) {
994 std::string guid =
995 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
996 std::string name =
997 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
998 std::string description =
999 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1000 int visibility = sqlite3_column_int(stmt, 3);
1001 std::string start_string =
1002 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
1003 std::string end_string =
1004 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
1005 int width = sqlite3_column_int(stmt, 6);
1006 int style = sqlite3_column_int(stmt, 7);
1007 std::string color =
1008 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 8));
1009 std::string created =
1010 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 9));
1011
1012 Track* new_trk = NULL;
1013
1014 // Add the trk_points
1015 const char* sql = R"(
1016 SELECT latitude, longitude, timestamp, point_order
1017 FROM trk_points
1018 WHERE track_guid = ?
1019 ORDER BY point_order ASC
1020 )";
1021
1022 sqlite3_stmt* stmtp;
1023 if (sqlite3_prepare_v2(m_db, sql, -1, &stmtp, nullptr) != SQLITE_OK) {
1024 return false;
1025 }
1026
1027 sqlite3_bind_text(stmtp, 1, guid.c_str(), -1, SQLITE_TRANSIENT);
1028
1029 int GPXTrkSeg = 1;
1030 while (sqlite3_step(stmtp) == SQLITE_ROW) {
1031 if (!new_trk) {
1032 new_trk = new Track;
1033 new_trk->m_GUID = guid;
1034
1035 // Set all the track attributes
1036 new_trk->SetVisible(visibility == 1);
1037 new_trk->SetName(name.c_str());
1038 new_trk->m_TrackStartString = start_string.c_str();
1039 new_trk->m_TrackEndString = end_string.c_str();
1040 new_trk->m_width = width;
1041 new_trk->m_style = (wxPenStyle)style;
1042 new_trk->m_Colour = color;
1043 }
1044
1045 double latitude = sqlite3_column_double(stmtp, 0);
1046 double longitude = sqlite3_column_double(stmtp, 1);
1047 std::string timestamp =
1048 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, 2));
1049 int point_order = sqlite3_column_int(stmtp, 3);
1050
1051 auto point = new TrackPoint(latitude, longitude, timestamp);
1052
1053 point->m_GPXTrkSegNo = GPXTrkSeg;
1054 new_trk->AddPoint(point);
1055 }
1056 sqlite3_finalize(stmtp);
1057
1058 if (new_trk) {
1059 new_trk->SetCurrentTrackSeg(GPXTrkSeg);
1060
1061 // Add the HTML links
1062 const char* sqlh = R"(
1063 SELECT guid, html_link, html_description, html_type
1064 FROM track_html_links
1065 WHERE track_guid = ?
1066 ORDER BY html_type ASC
1067 )";
1068
1069 sqlite3_stmt* stmt;
1070
1071 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1072 sqlite3_bind_text(stmt, 1, new_trk->m_GUID.ToStdString().c_str(), -1,
1073 SQLITE_TRANSIENT);
1074
1075 while (sqlite3_step(stmt) == SQLITE_ROW) {
1076 std::string link_guid =
1077 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1078 std::string link_link =
1079 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1080 std::string link_description =
1081 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1082 std::string link_type =
1083 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1084
1085 Hyperlink* h = new Hyperlink();
1086 h->DescrText = link_description;
1087 h->Link = link_link;
1088 h->LType = link_type;
1089
1090 new_trk->m_TrackHyperlinkList->push_back(h);
1091 int yyp = 4;
1092 }
1093
1094 sqlite3_finalize(stmt);
1095
1096 } else {
1097 return false;
1098 }
1099
1100 // Insert the track into the global list
1101 g_TrackList.push_back(new_trk);
1102 // Add the selectable points and segments of the track
1103 pSelect->AddAllSelectableTrackSegments(new_trk);
1104 }
1105 }
1106 return true;
1107}
1108
1109bool NavObj_dB::DeleteTrack(Track* track) {
1110 if (!track) return false;
1111 std::string track_guid = track->m_GUID.ToStdString();
1112 const char* sql = "DELETE FROM tracks WHERE guid = ?";
1113 sqlite3_stmt* stmt;
1114
1115 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1116 sqlite3_bind_text(stmt, 1, track_guid.c_str(), -1, SQLITE_TRANSIENT);
1117 if (sqlite3_step(stmt) != SQLITE_DONE) {
1118 ReportError("DeleteTrack:step");
1119 sqlite3_finalize(stmt);
1120 return false;
1121 }
1122
1123 sqlite3_finalize(stmt);
1124 } else {
1125 return false;
1126 }
1127 return true;
1128}
1129
1130// Route support
1131
1132bool NavObj_dB::InsertRoute(Route* route) {
1133 bool rv = false;
1134 char* errMsg = 0;
1135
1136 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) {
1137 // Insert a new route
1138 wxString sql = wxString::Format("INSERT INTO routes (guid) VALUES ('%s')",
1139 route->m_GUID.ToStdString().c_str());
1140 if (!executeSQL(m_db, sql)) {
1141 return false;
1142 }
1143 UpdateDBRouteAttributes(route);
1144 }
1145
1146 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
1147 if (errMsg) {
1148 ReportError("InsertRoute:BEGIN TRANSACTION");
1149 return false;
1150 }
1151
1152 // insert routepoints
1153 for (int i = 0; i < route->GetnPoints(); i++) {
1154 auto point = route->GetPoint(i + 1);
1155 // Add the bare point
1156 if (point) {
1157 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
1158 InsertRoutePointDB(m_db, point);
1159 UpdateDBRoutePointAttributes(point);
1160 }
1161 }
1162 }
1163
1164 // insert linkages
1165 for (int i = 0; i < route->GetnPoints(); i++) {
1166 auto point = route->GetPoint(i + 1);
1167 // Add the bare point
1168 if (point) {
1169 InsertRoutePointLink(m_db, route, point, i + 1);
1170 }
1171 }
1172
1173 // Add HTML links to route
1174 int NbrOfLinks = route->m_HyperlinkList->size();
1175 if (NbrOfLinks > 0) {
1176 auto& list = *route->m_HyperlinkList;
1177 for (auto it = list.begin(); it != list.end(); ++it) {
1178 Hyperlink* link = *it;
1179 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1180 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1181 link->DescrText.ToStdString(), link->Link.ToStdString(),
1182 link->LType.ToStdString());
1183 }
1184 }
1185 }
1186
1187 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
1188 rv = true;
1189 if (errMsg) {
1190 ReportError("InsertRoute:commit");
1191 rv = false;
1192 }
1193 return rv;
1194};
1195
1196bool NavObj_dB::UpdateRoute(Route* route) {
1197 bool rv = false;
1198 char* errMsg = 0;
1199
1200 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) return false;
1201
1202 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
1203 if (errMsg) {
1204 ReportError("UpdateRoute:BEGIN TRANSACTION");
1205 return false;
1206 }
1207
1208 UpdateDBRouteAttributes(route);
1209
1210 // update routepoints
1211 for (int i = 0; i < route->GetnPoints(); i++) {
1212 auto point = route->GetPoint(i + 1);
1213 // Add the bare point
1214 if (point) {
1215 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
1216 InsertRoutePointDB(m_db, point);
1217 }
1218 UpdateDBRoutePointAttributes(point);
1219 }
1220 }
1221
1222 // Delete and re-add point linkages
1223 const char* sql = "DELETE FROM routepoints_link WHERE route_guid = ?";
1224 sqlite3_stmt* stmt;
1225 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1226 sqlite3_bind_text(stmt, 1, route->m_GUID.ToStdString().c_str(), -1,
1227 SQLITE_TRANSIENT);
1228 } else {
1229 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
1230 return false;
1231 }
1232 if (sqlite3_step(stmt) != SQLITE_DONE) {
1233 ReportError("UpdateRoute:step");
1234 sqlite3_finalize(stmt);
1235 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
1236 return false;
1237 }
1238
1239 sqlite3_finalize(stmt);
1240
1241 for (int i = 0; i < route->GetnPoints(); i++) {
1242 auto point = route->GetPoint(i + 1);
1243 if (point) {
1244 InsertRoutePointLink(m_db, route, point, i + 1);
1245 }
1246 }
1247
1248 // Add HTML links to route
1249 int NbrOfLinks = route->m_HyperlinkList->size();
1250 if (NbrOfLinks > 0) {
1251 auto& list = *route->m_HyperlinkList;
1252 for (auto it = list.begin(); it != list.end(); ++it) {
1253 Hyperlink* link = *it;
1254 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1255 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1256 link->DescrText.ToStdString(), link->Link.ToStdString(),
1257 link->LType.ToStdString());
1258 }
1259 }
1260 }
1261 sqlite3_exec(m_db, "COMMIT", 0, 0, nullptr);
1262
1263 rv = true;
1264 if (errMsg) rv = false;
1265
1266 return rv;
1267};
1268
1269bool NavObj_dB::UpdateRouteViz(Route* route) {
1270 bool rv = false;
1271 char* errMsg = 0;
1272 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) return false;
1273
1274 UpdateDBRouteAttributes(route);
1275 // update routepoints visibility
1276 for (int i = 0; i < route->GetnPoints(); i++) {
1277 auto point = route->GetPoint(i + 1);
1278 // Add the bare point
1279 if (point) {
1280 UpdateDBRoutePointViz(point);
1281 }
1282 }
1283 rv = true;
1284 if (errMsg) rv = false;
1285
1286 return rv;
1287};
1288
1289bool NavObj_dB::UpdateDBRouteAttributes(Route* route) {
1290 const char* sql =
1291 "UPDATE routes SET "
1292 "name = ?, "
1293 "description = ?, "
1294 "start_string = ?, "
1295 "end_string = ?, "
1296 "visibility = ?, "
1297 "shared_wp_viz = ?, "
1298 "planned_departure = ?, "
1299 "plan_speed = ?, "
1300 "time_format = ?, "
1301 "width = ?, "
1302 "style = ?, "
1303 "color = ? "
1304 "WHERE guid = ?";
1305
1306 sqlite3_stmt* stmt;
1307 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1308 sqlite3_bind_text(stmt, 1, route->GetName().ToStdString().c_str(), -1,
1309 SQLITE_TRANSIENT);
1310 sqlite3_bind_text(stmt, 2, route->m_RouteDescription.ToStdString().c_str(),
1311 -1, SQLITE_TRANSIENT);
1312 sqlite3_bind_text(stmt, 3, route->m_RouteStartString.ToStdString().c_str(),
1313 -1, SQLITE_TRANSIENT);
1314 sqlite3_bind_text(stmt, 4, route->m_RouteEndString.ToStdString().c_str(),
1315 -1, SQLITE_TRANSIENT);
1316 sqlite3_bind_int(stmt, 5, route->IsVisible());
1317 sqlite3_bind_int(stmt, 6, route->GetSharedWPViz());
1318 if (route->m_PlannedDeparture.IsValid())
1319 sqlite3_bind_int(stmt, 7, route->m_PlannedDeparture.GetTicks());
1320 sqlite3_bind_double(stmt, 8, route->m_PlannedSpeed);
1321 sqlite3_bind_text(stmt, 9, route->m_TimeDisplayFormat.ToStdString().c_str(),
1322 -1, SQLITE_TRANSIENT);
1323 sqlite3_bind_int(stmt, 10, route->m_width);
1324 sqlite3_bind_int(stmt, 11,
1325 (int)(route->m_style)); // track->m_style.c_str(),
1326 sqlite3_bind_text(stmt, 12, route->m_Colour.ToStdString().c_str(), -1,
1327 SQLITE_TRANSIENT);
1328 sqlite3_bind_text(stmt, 13, route->m_GUID.c_str(), route->m_GUID.size(),
1329 SQLITE_TRANSIENT);
1330 } else {
1331 return false;
1332 }
1333
1334 if (sqlite3_step(stmt) != SQLITE_DONE) {
1335 ReportError("UpdateDBRouteAttributesA:step");
1336 sqlite3_finalize(stmt);
1337 return false;
1338 }
1339
1340 sqlite3_finalize(stmt);
1341
1342 // Update the HTML links
1343 // The list of links is freshly rebuilt when this method is called
1344 // So start by deleting all existing bcomments
1345 DeleteAllCommentsForRoute(m_db, route->m_GUID.ToStdString());
1346
1347 // Now add all the links to db
1348 int NbrOfLinks = route->m_HyperlinkList->size();
1349 if (NbrOfLinks > 0) {
1350 auto& list = route->m_HyperlinkList;
1351 for (auto it = list->begin(); it != list->end(); ++it) {
1352 Hyperlink* link = *it;
1353 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1354 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1355 link->DescrText.ToStdString(), link->Link.ToStdString(),
1356 link->LType.ToStdString());
1357 } else {
1358 const char* sql =
1359 "UPDATE route_html_links SET "
1360 "html_link = ?, "
1361 "html_description = ?, "
1362 "html_type = ? "
1363 "WHERE guid = ?";
1364 sqlite3_stmt* stmt;
1365 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1366 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
1367 SQLITE_TRANSIENT);
1368 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
1369 SQLITE_TRANSIENT);
1370 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
1371 SQLITE_TRANSIENT);
1372 }
1373 if (sqlite3_step(stmt) != SQLITE_DONE) {
1374 return false;
1375 }
1376 if (sqlite3_step(stmt) != SQLITE_DONE) {
1377 ReportError("UpdateDBRouteAttributesB:step");
1378 sqlite3_finalize(stmt);
1379 return false;
1380 }
1381 sqlite3_finalize(stmt);
1382 }
1383 }
1384 }
1385 return true;
1386}
1387
1388bool NavObj_dB::UpdateDBRoutePointAttributes(RoutePoint* point) {
1389 const char* sql =
1390 "UPDATE routepoints SET "
1391 "lat = ?, "
1392 "lon = ?, "
1393 "Symbol = ?, "
1394 "Name = ?, "
1395 "description = ?, "
1396 "TideStation = ?, "
1397 "plan_speed = ?, "
1398 "etd = ?, "
1399 "Type = ?, "
1400 "Time = ?, "
1401 "ArrivalRadius = ?, "
1402 "RangeRingsNumber = ?, "
1403 "RangeRingsStep = ?, "
1404 "RangeRingsStepUnits = ?, "
1405 "RangeRingsVisible = ?, "
1406 "RangeRingsColour = ?, "
1407 "ScaleMin = ?, "
1408 "ScaleMax = ?, "
1409 "UseScale = ?, "
1410 "visibility = ?, "
1411 "viz_name = ?, "
1412 "shared = ?, "
1413 "isolated = ? "
1414 "WHERE guid = ?";
1415
1416 sqlite3_stmt* stmt;
1417 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1418 sqlite3_bind_double(stmt, 1, point->GetLatitude());
1419 sqlite3_bind_double(stmt, 2, point->GetLongitude());
1420 sqlite3_bind_text(stmt, 3, point->GetIconName().ToStdString().c_str(), -1,
1421 SQLITE_TRANSIENT);
1422 sqlite3_bind_text(stmt, 4, point->GetName().ToStdString().c_str(), -1,
1423 SQLITE_TRANSIENT);
1424 sqlite3_bind_text(stmt, 5, point->GetDescription().ToStdString().c_str(),
1425 -1, SQLITE_TRANSIENT);
1426 sqlite3_bind_text(stmt, 6, point->m_TideStation.ToStdString().c_str(), -1,
1427 SQLITE_TRANSIENT);
1428 sqlite3_bind_double(stmt, 7, point->GetPlannedSpeed());
1429 time_t etd = -1;
1430 if (point->GetManualETD().IsValid()) etd = point->GetManualETD().GetTicks();
1431 sqlite3_bind_int(stmt, 8, etd);
1432 sqlite3_bind_text(stmt, 9, "type", -1, SQLITE_TRANSIENT);
1433 std::string timit = point->m_timestring.ToStdString().c_str();
1434 sqlite3_bind_text(stmt, 10, point->m_timestring.ToStdString().c_str(), -1,
1435 SQLITE_TRANSIENT);
1436 sqlite3_bind_double(stmt, 11, point->m_WaypointArrivalRadius);
1437
1438 sqlite3_bind_int(stmt, 12, point->m_iWaypointRangeRingsNumber);
1439 sqlite3_bind_double(stmt, 13, point->m_fWaypointRangeRingsStep);
1440 sqlite3_bind_int(stmt, 14, point->m_iWaypointRangeRingsStepUnits);
1441 sqlite3_bind_int(stmt, 15, point->m_bShowWaypointRangeRings);
1442 sqlite3_bind_text(
1443 stmt, 16,
1444 point->m_wxcWaypointRangeRingsColour.GetAsString(wxC2S_HTML_SYNTAX)
1445 .ToStdString()
1446 .c_str(),
1447 -1, SQLITE_TRANSIENT);
1448
1449 sqlite3_bind_int(stmt, 17, point->GetScaMin());
1450 sqlite3_bind_int(stmt, 18, point->GetScaMax());
1451 sqlite3_bind_int(stmt, 19, point->GetUseSca());
1452
1453 sqlite3_bind_int(stmt, 20, point->IsVisible());
1454 sqlite3_bind_int(stmt, 21, point->IsNameShown());
1455 sqlite3_bind_int(stmt, 22, point->IsShared());
1456 int iso = point->m_bIsolatedMark;
1457 sqlite3_bind_int(stmt, 23, iso); // point->m_bIsolatedMark);
1458
1459 sqlite3_bind_text(stmt, 24, point->m_GUID.ToStdString().c_str(), -1,
1460 SQLITE_TRANSIENT);
1461
1462 } else {
1463 return false;
1464 }
1465
1466 if (sqlite3_step(stmt) != SQLITE_DONE) {
1467 ReportError("UpdateDBRoutePointAttributesA:step");
1468 sqlite3_finalize(stmt);
1469 return false;
1470 }
1471
1472 sqlite3_finalize(stmt);
1473
1474 // Update the HTML links
1475 // The list of links is freshly rebuilt when this method is called
1476 // So start by deleting all existing bcomments
1477 DeleteAllCommentsForRoutePoint(m_db, point->m_GUID.ToStdString());
1478
1479 // Now add all the links to db
1480 int NbrOfLinks = point->m_HyperlinkList->size();
1481 if (NbrOfLinks > 0) {
1482 auto& list = point->m_HyperlinkList;
1483 for (auto it = list->begin(); it != list->end(); ++it) {
1484 Hyperlink* link = *it;
1485 if (!RoutePointHtmlLinkExists(m_db, link->GUID)) {
1486 InsertRoutePointHTML(m_db, point->m_GUID.ToStdString(), link->GUID,
1487 link->DescrText.ToStdString(),
1488 link->Link.ToStdString(),
1489 link->LType.ToStdString());
1490 } else {
1491 const char* sql =
1492 "UPDATE routepoint_html_links SET "
1493 "html_link = ?, "
1494 "html_description = ?, "
1495 "html_type = ? "
1496 "WHERE guid = ?";
1497 sqlite3_stmt* stmt;
1498 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1499 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
1500 SQLITE_TRANSIENT);
1501 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
1502 SQLITE_TRANSIENT);
1503 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
1504 SQLITE_TRANSIENT);
1505 }
1506 if (sqlite3_step(stmt) != SQLITE_DONE) {
1507 return false;
1508 }
1509 if (sqlite3_step(stmt) != SQLITE_DONE) {
1510 ReportError("UpdateDBRoutePointAttributesB:step-h");
1511 sqlite3_finalize(stmt);
1512 return false;
1513 }
1514 sqlite3_finalize(stmt);
1515 }
1516 }
1517 }
1518
1519 return true;
1520}
1521
1522bool NavObj_dB::UpdateDBRoutePointViz(RoutePoint* point) {
1523 const char* sql =
1524 "UPDATE routepoints SET "
1525 "visibility = ? "
1526 "WHERE guid = ?";
1527
1528 sqlite3_stmt* stmt;
1529 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1530 sqlite3_bind_int(stmt, 1, point->IsVisible());
1531 sqlite3_bind_text(stmt, 2, point->m_GUID.ToStdString().c_str(), -1,
1532 SQLITE_TRANSIENT);
1533
1534 } else {
1535 return false;
1536 }
1537
1538 if (sqlite3_step(stmt) != SQLITE_DONE) {
1539 ReportError("UpdateDBRoutePointVizA:step");
1540 sqlite3_finalize(stmt);
1541 return false;
1542 }
1543
1544 sqlite3_finalize(stmt);
1545
1546 return true;
1547}
1548
1549bool NavObj_dB::DeleteRoute(Route* route) {
1550 if (m_importing) return false;
1551 if (!route) return false;
1552 std::string route_guid = route->m_GUID.ToStdString();
1553 const char* sql = "DELETE FROM routes WHERE guid = ?";
1554 sqlite3_stmt* stmt;
1555
1556 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1557 sqlite3_bind_text(stmt, 1, route_guid.c_str(), -1, SQLITE_TRANSIENT);
1558 if (sqlite3_step(stmt) != SQLITE_DONE) {
1559 ReportError("DeleteRoute:step");
1560 sqlite3_finalize(stmt);
1561 return false;
1562 }
1563 sqlite3_finalize(stmt);
1564 } else {
1565 return false;
1566 }
1567 return true;
1568}
1569
1570bool NavObj_dB::LoadAllRoutes() {
1571 const char* sql =
1572 "SELECT "
1573 "guid, "
1574 "name, "
1575 "description, "
1576 "start_string, "
1577 "end_string, "
1578 "visibility, "
1579 "shared_wp_viz, "
1580 "planned_departure, "
1581 "plan_speed, "
1582 "time_format, "
1583 "width, "
1584 "style, "
1585 "color "
1586 "FROM routes "
1587 "ORDER BY created_at ASC";
1588
1589 sqlite3_stmt* stmt;
1590 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
1591 return false;
1592 }
1593
1594 int errcode0 = SQLITE_OK;
1595 while ((errcode0 = sqlite3_step(stmt)) == SQLITE_ROW) {
1596 std::string guid =
1597 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1598 std::string name =
1599 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1600 std::string description =
1601 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1602 std::string start_string =
1603 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1604 std::string end_string =
1605 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
1606 int visibility = sqlite3_column_int(stmt, 5);
1607 int sharewp_viz = sqlite3_column_int(stmt, 6);
1608 time_t planned_departure_ticks = sqlite3_column_int(stmt, 7);
1609 double plan_speed = sqlite3_column_double(stmt, 8);
1610 std::string time_format =
1611 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 9));
1612
1613 int width = sqlite3_column_int(stmt, 10);
1614 int style = sqlite3_column_int(stmt, 11);
1615 std::string color =
1616 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 12));
1617
1618 Route* route = NULL;
1619
1620 // Add the route_points
1621 const char* sql = R"(
1622 SELECT latitude, longitude, timestamp, point_order
1623 FROM trk_points
1624 WHERE track_guid = ?
1625 ORDER BY point_order ASC
1626 )";
1627
1628 const char* sqlp =
1629 "SELECT p.guid, "
1630 "p.lat, "
1631 "p.lon, "
1632 "p.Symbol, "
1633 "p.Name, "
1634 "p.description, "
1635 "p.TideStation, "
1636 "p.plan_speed, "
1637 "p.etd, "
1638 "p.Type, "
1639 "p.Time, "
1640 "p.ArrivalRadius, "
1641 "p.RangeRingsNumber, "
1642 "p.RangeRingsStep, "
1643 "p.RangeRingsStepUnits, "
1644 "p.RangeRingsVisible, "
1645 "p.RangeRingsColour, "
1646 "p.ScaleMin, "
1647 "p.ScaleMax, "
1648 "p.UseScale, "
1649 "p.visibility, "
1650 "p.viz_name, "
1651 "p.shared, "
1652 "p.isolated, "
1653 "p.created_at "
1654 "FROM routepoints_link tp "
1655 "JOIN routepoints p ON p.guid = tp.point_guid "
1656 "WHERE tp.route_guid = ? "
1657 "ORDER BY tp.point_order ASC";
1658
1659 sqlite3_stmt* stmtp;
1660 if (sqlite3_prepare_v2(m_db, sqlp, -1, &stmtp, nullptr) != SQLITE_OK) {
1661 ReportError("LoadAllRoutes-B:prepare");
1662 return false;
1663 }
1664
1665 sqlite3_bind_text(stmtp, 1, guid.c_str(), -1, SQLITE_TRANSIENT);
1666
1667 int GPXSeg = 0;
1668 int errcode = SQLITE_OK;
1669 while ((errcode = sqlite3_step(stmtp)) == SQLITE_ROW) {
1670 if (!route) {
1671 route = new Route;
1672 route->m_GUID = guid;
1673
1674 // Set all the route attributes
1675 route->SetVisible(visibility == 1);
1676 route->m_RouteNameString = name.c_str();
1677 route->m_RouteDescription = description.c_str();
1678 route->m_RouteStartString = start_string.c_str();
1679 route->m_RouteEndString = end_string.c_str();
1680 route->SetVisible(visibility == 1);
1681 route->SetSharedWPViz(sharewp_viz == 1);
1682 route->m_PlannedDeparture.Set((time_t)planned_departure_ticks);
1683 route->m_PlannedSpeed = plan_speed;
1684 route->m_TimeDisplayFormat = time_format.c_str();
1685
1686 route->m_width = width;
1687 route->m_style = (wxPenStyle)style;
1688 route->m_Colour = color;
1689 }
1690
1691 // Grab all the point attributes from the SELECT statement
1692 int col = 0;
1693 std::string point_guid =
1694 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1695 double latitude = sqlite3_column_double(stmtp, col++);
1696 double longitude = sqlite3_column_double(stmtp, col++);
1697 std::string symbol =
1698 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1699 std::string name =
1700 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1701 std::string description =
1702 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1703 std::string tide_station =
1704 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1705 double plan_speed = sqlite3_column_double(stmtp, col++);
1706 time_t etd_epoch = sqlite3_column_int(stmtp, col++);
1707 std::string type =
1708 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1709 std::string time =
1710 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1711 double arrival_radius = sqlite3_column_double(stmtp, col++);
1712
1713 int range_ring_number = sqlite3_column_int(stmtp, col++);
1714 double range_ring_step = sqlite3_column_double(stmtp, col++);
1715 int range_ring_units = sqlite3_column_int(stmtp, col++);
1716 int range_ring_visible = sqlite3_column_int(stmtp, col++);
1717 std::string range_ring_color =
1718 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1719
1720 int scamin = sqlite3_column_int(stmtp, col++);
1721 int scamax = sqlite3_column_int(stmtp, col++);
1722 int use_scaminmax = sqlite3_column_int(stmtp, col++);
1723
1724 int visibility = sqlite3_column_int(stmtp, col++);
1725 int viz_name = sqlite3_column_int(stmtp, col++);
1726 int shared = sqlite3_column_int(stmtp, col++);
1727 int isolated = sqlite3_column_int(stmtp, col++);
1728 std::string point_created_at =
1729 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1730
1731 RoutePoint* point;
1732 // RoutePoint exists already, in another route or isolated??
1733 RoutePoint* existing_point = NULL;
1734 auto containing_route =
1735 g_pRouteMan->FindRouteContainingWaypoint(point_guid);
1736
1737 if (containing_route) { // In a route already?
1738 existing_point = containing_route->GetPoint(point_guid);
1739 }
1740 // Or isolated?
1741 if (!existing_point) {
1742 existing_point = pWayPointMan->FindRoutePointByGUID(point_guid.c_str());
1743 }
1744
1745 if (existing_point) {
1746 point = existing_point;
1747 point->SetShared(true); // by definition
1748 point->m_bIsolatedMark = false;
1749 } else {
1750 point =
1751 new RoutePoint(latitude, longitude, symbol, name, point_guid, true);
1752
1753 point->m_MarkDescription = description;
1754 point->m_TideStation = tide_station;
1755 point->SetPlannedSpeed(plan_speed);
1756
1757 wxDateTime etd;
1758 etd.Set((time_t)etd_epoch);
1759 if (etd.IsValid()) point->SetETD(etd);
1760
1761 point->m_WaypointArrivalRadius = arrival_radius;
1762
1763 point->m_iWaypointRangeRingsNumber = range_ring_number;
1764 point->m_fWaypointRangeRingsStep = range_ring_step;
1765 point->m_iWaypointRangeRingsStepUnits = range_ring_units;
1766 point->SetShowWaypointRangeRings(range_ring_visible == 1);
1767 // TODO
1768 point->m_wxcWaypointRangeRingsColour.Set(range_ring_color);
1769
1770 point->SetScaMin(scamin);
1771 point->SetScaMax(scamax);
1772 point->SetUseSca(use_scaminmax == 1);
1773
1774 point->SetVisible(visibility == 1);
1775 point->SetNameShown(viz_name == 1);
1776 point->SetShared(shared == 1);
1777 point->m_bIsolatedMark = (isolated == 1);
1778
1779 if (point_created_at.size()) {
1780 // Convert from sqLite default date/time format to wxDateTime
1781 // sqLite format uses UTC, so conversion to epoch_time is clear.
1782 std::tm tm = {};
1783 std::istringstream ss(point_created_at);
1784 ss >> std::get_time(&tm, "%Y-%m-%d %H:%M:%S");
1785 time_t epoch_time = mktime(&tm);
1786 point->m_CreateTimeX = epoch_time;
1787 }
1788
1789 // Add the point HTML links
1790 const char* sqlh = R"(
1791 SELECT guid, html_link, html_description, html_type
1792 FROM routepoint_html_links
1793 WHERE routepoint_guid = ?
1794 ORDER BY html_type ASC
1795 )";
1796
1797 sqlite3_stmt* stmt;
1798
1799 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1800 sqlite3_bind_text(stmt, 1, point->m_GUID.ToStdString().c_str(), -1,
1801 SQLITE_TRANSIENT);
1802
1803 while (sqlite3_step(stmt) == SQLITE_ROW) {
1804 std::string link_guid =
1805 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1806 std::string link_link =
1807 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1808 std::string link_description =
1809 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1810 std::string link_type =
1811 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1812
1813 Hyperlink* h = new Hyperlink();
1814 h->DescrText = link_description;
1815 h->Link = link_link;
1816 h->LType = link_type;
1817
1818 point->m_HyperlinkList->push_back(h);
1819 }
1820 }
1821 }
1822
1823 route->AddPoint(point);
1824 } // route points
1825 sqlite3_finalize(stmtp);
1826 if (errcode != SQLITE_DONE) {
1827 ReportError("LoadAllRoutes-A:step");
1828 return false;
1829 }
1830
1831 // Add route html links
1832 if (route) {
1833 // Add the HTML links
1834 const char* sqlh = R"(
1835 SELECT guid, html_link, html_description, html_type
1836 FROM route_html_links
1837 WHERE route_guid = ?
1838 ORDER BY html_type ASC
1839 )";
1840
1841 sqlite3_stmt* stmt;
1842
1843 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1844 sqlite3_bind_text(stmt, 1, route->m_GUID.ToStdString().c_str(), -1,
1845 SQLITE_TRANSIENT);
1846
1847 int errcode2 = SQLITE_OK;
1848 while ((errcode2 = sqlite3_step(stmt)) == SQLITE_ROW) {
1849 std::string link_guid =
1850 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1851 std::string link_link =
1852 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1853 std::string link_description =
1854 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1855 std::string link_type =
1856 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1857
1858 Hyperlink* h = new Hyperlink();
1859 h->DescrText = link_description;
1860 h->Link = link_link;
1861 h->LType = link_type;
1862
1863 route->m_HyperlinkList->push_back(h);
1864 }
1865 if (errcode != SQLITE_DONE) {
1866 ReportError("LoadAllRoutes-B:step");
1867 return false;
1868 }
1869
1870 sqlite3_finalize(stmt);
1871
1872 } else {
1873 ReportError("LoadAllRoutes-B:prepare");
1874 return false;
1875 }
1876 }
1877
1878 // Insert the route into the global list
1879 InsertRouteA(route,
1880 nullptr); // NavObjectChanges::getInstance() //TODO adding
1881 // changes will force the xml file to be updated?
1882
1883 } // routes
1884 if (errcode0 != SQLITE_DONE) {
1885 ReportError("LoadAllRoutes-C:step");
1886 return false;
1887 }
1888
1889 return true;
1890}
1891
1892bool NavObj_dB::LoadAllPoints() {
1893 const char* sqlp =
1894 "SELECT "
1895 "p.guid, "
1896 "p.lat, "
1897 "p.lon, "
1898 "p.Symbol, "
1899 "p.Name, "
1900 "p.description, "
1901 "p.TideStation, "
1902 "p.plan_speed, "
1903 "p.etd, "
1904 "p.Type, "
1905 "p.Time, "
1906 "p.ArrivalRadius, "
1907 "p.RangeRingsNumber, "
1908 "p.RangeRingsStep, "
1909 "p.RangeRingsStepUnits, "
1910 "p.RangeRingsVisible, "
1911 "p.RangeRingsColour, "
1912 "p.ScaleMin, "
1913 "p.ScaleMax, "
1914 "p.UseScale, "
1915 "p.visibility, "
1916 "p.viz_name, "
1917 "p.shared, "
1918 "p.isolated, "
1919 "p.created_at "
1920 "FROM routepoints p ";
1921
1922 RoutePoint* point = nullptr;
1923
1924 sqlite3_stmt* stmtp;
1925 if (sqlite3_prepare_v2(m_db, sqlp, -1, &stmtp, nullptr) != SQLITE_OK) {
1926 return false;
1927 }
1928
1929 while (sqlite3_step(stmtp) == SQLITE_ROW) {
1930 // Grab all the point attributes from the SELECT statement
1931 int col = 0;
1932 std::string point_guid =
1933 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1934 double latitude = sqlite3_column_double(stmtp, col++);
1935 double longitude = sqlite3_column_double(stmtp, col++);
1936 std::string symbol =
1937 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1938 std::string name =
1939 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1940 std::string description =
1941 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1942 std::string tide_station =
1943 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1944 double plan_speed = sqlite3_column_double(stmtp, col++);
1945 time_t etd = sqlite3_column_int(stmtp, col++);
1946 std::string type =
1947 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1948 std::string point_time_string =
1949 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1950 double arrival_radius = sqlite3_column_double(stmtp, col++);
1951
1952 int range_ring_number = sqlite3_column_int(stmtp, col++);
1953 double range_ring_step = sqlite3_column_double(stmtp, col++);
1954 int range_ring_units = sqlite3_column_int(stmtp, col++);
1955 int range_ring_visible = sqlite3_column_int(stmtp, col++);
1956 std::string range_ring_color =
1957 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1958
1959 int scamin = sqlite3_column_int(stmtp, col++);
1960 int scamax = sqlite3_column_int(stmtp, col++);
1961 int use_scaminmax = sqlite3_column_int(stmtp, col++);
1962
1963 int visibility = sqlite3_column_int(stmtp, col++);
1964 int viz_name = sqlite3_column_int(stmtp, col++);
1965 int shared = sqlite3_column_int(stmtp, col++);
1966 int isolated = sqlite3_column_int(stmtp, col++);
1967 std::string point_created_at =
1968 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1969
1970 if (isolated) {
1971 point =
1972 new RoutePoint(latitude, longitude, symbol, name, point_guid, false);
1973
1974 point->m_MarkDescription = description;
1975 point->m_TideStation = tide_station;
1976 point->SetPlannedSpeed(plan_speed);
1977 point->m_WaypointArrivalRadius = arrival_radius;
1978
1979 point->m_iWaypointRangeRingsNumber = range_ring_number;
1980 point->m_fWaypointRangeRingsStep = range_ring_step;
1981 point->m_iWaypointRangeRingsStepUnits = range_ring_units;
1982 point->SetShowWaypointRangeRings(range_ring_visible == 1);
1983
1984 point->m_wxcWaypointRangeRingsColour.Set(range_ring_color);
1985
1986 point->SetScaMin(scamin);
1987 point->SetScaMax(scamax);
1988 point->SetUseSca(use_scaminmax == 1);
1989
1990 point->SetVisible(visibility == 1);
1991 point->SetNameShown(viz_name == 1);
1992 point->SetShared(shared == 1);
1993 point->m_bIsolatedMark = (isolated == 1);
1994
1995 if (point_created_at.size()) {
1996 // Convert from sqLite default date/time format to wxDateTime
1997 // sqLite format uses UTC, so conversion to epoch_time is clear.
1998 std::tm tm = {};
1999 std::istringstream ss(point_created_at);
2000 ss >> std::get_time(&tm, "%Y-%m-%d %H:%M:%S");
2001 time_t epoch_time = mktime(&tm);
2002 point->m_CreateTimeX = epoch_time;
2003 }
2004
2005 // Add it here
2006 pWayPointMan->AddRoutePoint(point);
2007 pSelect->AddSelectableRoutePoint(point->m_lat, point->m_lon, point);
2008 }
2009 } // points
2010 sqlite3_finalize(stmtp);
2011
2012 if (point) {
2013 // Add the point HTML links
2014 const char* sqlh = R"(
2015 SELECT guid, html_link, html_description, html_type
2016 FROM routepoint_html_links
2017 WHERE routepoint_guid = ?
2018 ORDER BY html_type ASC
2019 )";
2020
2021 sqlite3_stmt* stmt;
2022
2023 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
2024 sqlite3_bind_text(stmt, 1, point->m_GUID.ToStdString().c_str(), -1,
2025 SQLITE_TRANSIENT);
2026
2027 while (sqlite3_step(stmt) == SQLITE_ROW) {
2028 std::string link_guid =
2029 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
2030 std::string link_link =
2031 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
2032 std::string link_description =
2033 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
2034 std::string link_type =
2035 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
2036
2037 Hyperlink* h = new Hyperlink();
2038 h->DescrText = link_description;
2039 h->Link = link_link;
2040 h->LType = link_type;
2041
2042 point->m_HyperlinkList->push_back(h);
2043 }
2044
2045 sqlite3_finalize(stmt);
2046
2047 } else {
2048 return false;
2049 }
2050 }
2051 return true;
2052}
2053bool NavObj_dB::InsertRoutePoint(RoutePoint* point) {
2054 bool rv = false;
2055 char* errMsg = 0;
2056
2057 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
2058 // Insert a new route point
2059 wxString sql =
2060 wxString::Format("INSERT INTO routepoints (guid) VALUES ('%s')",
2061 point->m_GUID.ToStdString().c_str());
2062 if (!executeSQL(m_db, sql)) {
2063 return false;
2064 }
2065 }
2066
2067 UpdateDBRoutePointAttributes(point);
2068
2069 // Add HTML links to routepoint
2070 int NbrOfLinks = point->m_HyperlinkList->size();
2071 if (NbrOfLinks > 0) {
2072 auto& list = point->m_HyperlinkList;
2073 for (auto it = list->begin(); it != list->end(); ++it) {
2074 Hyperlink* link = *it;
2075 if (!RoutePointHtmlLinkExists(m_db, link->GUID)) {
2076 InsertRoutePointHTML(m_db, point->m_GUID.ToStdString(), link->GUID,
2077 link->DescrText.ToStdString(),
2078 link->Link.ToStdString(),
2079 link->LType.ToStdString());
2080 }
2081 }
2082 }
2083
2084 return true;
2085}
2086
2087bool NavObj_dB::DeleteRoutePoint(RoutePoint* point) {
2088 if (m_importing) return false;
2089 if (!point) return false;
2090
2091 std::string point_guid = point->m_GUID.ToStdString();
2092
2093 // DeleteAllCommentsForRoutePoint(m_db, point_guid);
2094
2095 const char* sql = "DELETE FROM routepoints WHERE guid = ?";
2096 sqlite3_stmt* stmt;
2097
2098 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
2099 sqlite3_bind_text(stmt, 1, point_guid.c_str(), -1, SQLITE_TRANSIENT);
2100 if (sqlite3_step(stmt) != SQLITE_DONE) {
2101 ReportError("DeleteRoutePoint:step");
2102 sqlite3_finalize(stmt);
2103 return false;
2104 }
2105
2106 sqlite3_finalize(stmt);
2107 } else {
2108 return false;
2109 }
2110 return true;
2111}
2112
2113bool NavObj_dB::UpdateRoutePoint(RoutePoint* point) {
2114 if (m_importing) return false;
2115 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) return false;
2116 UpdateDBRoutePointAttributes(point);
2117 return true;
2118}
2119
2120bool NavObj_dB::Backup(wxString fileName) {
2121 sqlite3_backup* pBackup;
2122 sqlite3* backupDatabase;
2123
2124 if (sqlite3_open(fileName.c_str(), &backupDatabase) == SQLITE_OK) {
2125 pBackup = sqlite3_backup_init(backupDatabase, "main", m_db, "main");
2126 if (pBackup) {
2127 int result = sqlite3_backup_step(pBackup, -1);
2128 if ((result == SQLITE_OK) || (result == SQLITE_DONE)) {
2129 if (sqlite3_backup_finish(pBackup) == SQLITE_OK) {
2130 sqlite3_close_v2(backupDatabase);
2131 return true;
2132 }
2133 }
2134 }
2135 }
2136 wxLogMessage("navobj database backup error: %s", sqlite3_errmsg(m_db));
2137 return false;
2138}
g_BasePlatform
BasePlatform * g_BasePlatform
points to g_platform, handles brain-dead MS linker.
Definition base_platform.cpp:100
base_platform.h
Basic platform specific support utilities without GUI deps.
AbstractPlatform::GetPrivateDataDir
wxString & GetPrivateDataDir()
Return dir path for opencpn.log, etc., respecting -c cli option.
Definition base_platform.cpp:321
NavObj_dB
The navobj SQLite container object, a singleton.
Definition navobj_db.h:35
RoutePoint
Represents a waypoint or mark within the navigation system.
Definition route_point.h:71
RoutePoint::m_HyperlinkList
HyperlinkList * m_HyperlinkList
List of hyperlinks associated with this waypoint.
Definition route_point.h:546
RoutePoint::m_wxcWaypointRangeRingsColour
wxColour m_wxcWaypointRangeRingsColour
Color for the range rings display.
Definition route_point.h:509
RoutePoint::m_MarkDescription
wxString m_MarkDescription
Description text for the waypoint.
Definition route_point.h:420
RoutePoint::m_iWaypointRangeRingsNumber
int m_iWaypointRangeRingsNumber
Number of range rings to display around the waypoint.
Definition route_point.h:496
RoutePoint::m_GUID
wxString m_GUID
Globally Unique Identifier for the waypoint.
Definition route_point.h:424
RoutePoint::m_CreateTimeX
wxDateTime m_CreateTimeX
Creation timestamp for the waypoint, in UTC.
Definition route_point.h:554
RoutePoint::m_bIsolatedMark
bool m_bIsolatedMark
Flag indicating if the waypoint is a standalone mark.
Definition route_point.h:396
RoutePoint::GetManualETD
wxDateTime GetManualETD()
Retrieves the manually set Estimated Time of Departure for this waypoint, in UTC.
Definition route_point.cpp:505
RoutePoint::m_timestring
wxString m_timestring
String representation of the waypoint creation time.
Definition route_point.h:550
RoutePoint::GetPlannedSpeed
double GetPlannedSpeed()
Return the planned speed associated with this waypoint.
Definition route_point.cpp:444
RoutePoint::m_WaypointArrivalRadius
double m_WaypointArrivalRadius
Arrival radius in nautical miles.
Definition route_point.h:542
RoutePoint::m_iWaypointRangeRingsStepUnits
int m_iWaypointRangeRingsStepUnits
Units for the range rings step (0=nm, 1=km).
Definition route_point.h:505
RoutePoint::m_fWaypointRangeRingsStep
float m_fWaypointRangeRingsStep
Distance between consecutive range rings.
Definition route_point.h:501
RoutePoint::m_TideStation
wxString m_TideStation
Associated tide station identifier.
Definition route_point.h:428
RoutePoint::m_bShowWaypointRangeRings
bool m_bShowWaypointRangeRings
Flag indicating if range rings should be shown around the waypoint.
Definition route_point.h:492
RoutePoint::SetETD
void SetETD(const wxDateTime &etd)
Sets the Estimated Time of Departure for this waypoint, in UTC.
Definition route_point.cpp:528
Route
Represents a navigational route in the navigation system.
Definition route.h:99
Route::m_PlannedSpeed
double m_PlannedSpeed
Default planned speed for the route in knots.
Definition route.h:321
Route::m_RouteStartString
wxString m_RouteStartString
Name or description of the route's starting point.
Definition route.h:252
Route::m_RouteDescription
wxString m_RouteDescription
Additional descriptive information about the route.
Definition route.h:262
Route::m_Colour
wxString m_Colour
Color name for rendering the route on the chart.
Definition route.h:346
Route::m_RouteEndString
wxString m_RouteEndString
Name or description of the route's ending point.
Definition route.h:257
Route::m_style
wxPenStyle m_style
Style of the route line when rendered on the chart.
Definition route.h:293
Route::m_TimeDisplayFormat
wxString m_TimeDisplayFormat
Format for displaying times in the UI.
Definition route.h:331
Route::m_width
int m_width
Width of the route line in pixels when rendered on the chart.
Definition route.h:288
Route::m_RouteNameString
wxString m_RouteNameString
User-assigned name for the route.
Definition route.h:247
Route::m_GUID
wxString m_GUID
Globally unique identifier for this route.
Definition route.h:273
Route::m_PlannedDeparture
wxDateTime m_PlannedDeparture
Planned departure time for the route, in UTC.
Definition route.h:326
Route::m_HyperlinkList
HyperlinkList * m_HyperlinkList
List of hyperlinks associated with this route.
Definition route.h:361
Routeman::DeleteRoute
bool DeleteRoute(Route *pRoute)
Definition routeman.cpp:762
TrackPoint
Represents a single point in a track.
Definition track.h:59
Track
Represents a track, which is a series of connected track points.
Definition track.h:117
WayPointman::AddRoutePoint
bool AddRoutePoint(RoutePoint *prp)
Add a point to list which owns it.
Definition routeman.cpp:998
WayPointman::RemoveRoutePoint
bool RemoveRoutePoint(RoutePoint *prp)
Remove a routepoint from list if present, deallocate it all cases.
Definition routeman.cpp:1007
comm_appmsg_bus.h
Decoded messages send/receive support.
ocpn::exists
bool exists(const std::string &name)
Definition ocpn_utils.cpp:76
navobj_db.h
MySQL based storage for routes, tracks, etc.
navutil_base.h
Navigation Utility Functions without GUI dependencies.
notification.h
User notification container.
notification_manager.h
User notifications manager.
g_pRouteMan
Routeman * g_pRouteMan
Global instance.
Definition routeman.cpp:60
pRouteList
RouteList * pRouteList
Global instance.
Definition routeman.cpp:66
routeman.h
Route Manager.
pSelect
Select * pSelect
Global instance.
Definition select.cpp:36
g_TrackList
std::vector< Track * > g_TrackList
Global instance.
Definition track.cpp:96