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