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