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_guid),
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
792 // Insert a new track
793 wxString sql = wxString::Format("INSERT INTO tracks (guid) VALUES ('%s')",
794 track->m_GUID.ToStdString().c_str());
795 if (!executeSQL(m_db, sql)) {
796 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
797 return false;
798 }
799
800 UpdateDBTrackAttributes(track);
801
802 // Add any existing trkpoints
803 for (int i = 0; i < track->GetnPoints(); i++) {
804 auto point = track->GetPoint(i);
805 // Add the bare trkpoint
806 InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
807 point->m_lon, point->GetTimeString(), i);
808 }
809
810 // Add HTML links to track
811 int NbrOfLinks = track->m_TrackHyperlinkList->GetCount();
812 if (NbrOfLinks > 0) {
813 wxHyperlinkListNode* linknode = track->m_TrackHyperlinkList->GetFirst();
814 while (linknode) {
815 Hyperlink* link = linknode->GetData();
816
817 if (!TrackHtmlLinkExists(m_db, link->GUID)) {
818 InsertTrackHTML(m_db, track->m_GUID.ToStdString(), link->GUID,
819 link->DescrText.ToStdString(), link->Link.ToStdString(),
820 link->LType.ToStdString());
821 }
822 linknode = linknode->GetNext();
823 }
824 }
825 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
826 rv = true;
827 if (errMsg) rv = false;
828
829 return rv;
830};
831
832bool NavObj_dB::UpdateDBTrackAttributes(Track* track) {
833 const char* sql =
834 "UPDATE tracks SET "
835 "name = ?, "
836 "description = ?, "
837 "visibility = ?, "
838 "start_string = ?, "
839 "end_string = ?, "
840 "width = ?, "
841 "style = ?, "
842 "color = ? "
843 "WHERE guid = ?";
844
845 sqlite3_stmt* stmt;
846 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
847 sqlite3_bind_text(stmt, 1, track->GetName().ToStdString().c_str(), -1,
848 SQLITE_TRANSIENT);
849 sqlite3_bind_text(stmt, 2, track->m_TrackDescription.ToStdString().c_str(),
850 -1, SQLITE_TRANSIENT);
851 sqlite3_bind_int(stmt, 3, track->m_bVisible);
852 sqlite3_bind_text(stmt, 4, track->m_TrackStartString.ToStdString().c_str(),
853 -1, SQLITE_TRANSIENT);
854 sqlite3_bind_text(stmt, 5, track->m_TrackEndString.ToStdString().c_str(),
855 -1, SQLITE_TRANSIENT);
856 sqlite3_bind_int(stmt, 6, track->m_width);
857 sqlite3_bind_int(stmt, 7,
858 (int)(track->m_style)); // track->m_style.c_str(),
859 sqlite3_bind_text(stmt, 8, track->m_Colour.ToStdString().c_str(), -1,
860 SQLITE_TRANSIENT);
861 sqlite3_bind_text(stmt, 9, track->m_GUID.c_str(), track->m_GUID.size(),
862 SQLITE_TRANSIENT);
863 } else {
864 return false;
865 }
866
867 if (sqlite3_step(stmt) != SQLITE_DONE) {
868 ReportError("UpdateDBTrackAttributesA:step");
869 sqlite3_finalize(stmt);
870 return false;
871 }
872
873 sqlite3_finalize(stmt);
874
875 // Update the HTML links
876 // The list of links is freshly rebuilt when this method is called
877 // So start by deleting all existing bcomments
878 DeleteAllCommentsForTrack(m_db, track->m_GUID.ToStdString());
879
880 // Now add all the links to db
881 int NbrOfLinks = track->m_TrackHyperlinkList->GetCount();
882 if (NbrOfLinks > 0) {
883 wxHyperlinkListNode* linknode = track->m_TrackHyperlinkList->GetFirst();
884 while (linknode) {
885 Hyperlink* link = linknode->GetData();
886
887 if (!TrackHtmlLinkExists(m_db, link->GUID)) {
888 InsertTrackHTML(m_db, track->m_GUID.ToStdString(), link->GUID,
889 link->DescrText.ToStdString(), link->Link.ToStdString(),
890 link->LType.ToStdString());
891 } else {
892 const char* sql =
893 "UPDATE track_html_links SET "
894 "html_link = ?, "
895 "html_description = ?, "
896 "html_type = ? "
897 "WHERE guid = ?";
898 sqlite3_stmt* stmt;
899 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
900 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
901 SQLITE_TRANSIENT);
902 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
903 SQLITE_TRANSIENT);
904 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
905 SQLITE_TRANSIENT);
906 }
907 if (sqlite3_step(stmt) != SQLITE_DONE) {
908 ReportError("UpdateDBTRackAttributesB:step");
909 sqlite3_finalize(stmt);
910 return false;
911 }
912
913 sqlite3_finalize(stmt);
914 }
915
916 linknode = linknode->GetNext();
917 }
918 }
919
920 return true;
921}
922
923bool NavObj_dB::AddTrackPoint(Track* track, TrackPoint* point) {
924 // If track does not yet exist in dB, return
925 if (!TrackExists(m_db, track->m_GUID.ToStdString())) return false;
926
927 // Get next point order
928 int this_point_index = track->GetnPoints();
929
930 // Add the linked point to the dB
931 if (!InsertTrackPoint(m_db, track->m_GUID.ToStdString(), point->m_lat,
932 point->m_lon, point->GetTimeString(),
933 this_point_index - 1))
934 return false;
935
936 return true;
937}
938
939bool NavObj_dB::LoadAllTracks() {
940 const char* sql = R"(
941 SELECT guid, name,
942 description, visibility, start_string, end_string,
943 width, style, color,
944 created_at
945 FROM tracks
946 ORDER BY created_at ASC
947 )";
948
949 sqlite3_stmt* stmt;
950 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
951 return false;
952 }
953
954 while (sqlite3_step(stmt) == SQLITE_ROW) {
955 std::string guid =
956 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
957 std::string name =
958 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
959 std::string description =
960 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
961 int visibility = sqlite3_column_int(stmt, 3);
962 std::string start_string =
963 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
964 std::string end_string =
965 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));
966 int width = sqlite3_column_int(stmt, 6);
967 int style = sqlite3_column_int(stmt, 7);
968 std::string color =
969 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 8));
970 std::string created =
971 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 9));
972
973 Track* new_trk = NULL;
974
975 // Add the trk_points
976 const char* sql = R"(
977 SELECT latitude, longitude, timestamp, point_order
978 FROM trk_points
979 WHERE track_guid = ?
980 ORDER BY point_order ASC
981 )";
982
983 sqlite3_stmt* stmtp;
984 if (sqlite3_prepare_v2(m_db, sql, -1, &stmtp, nullptr) != SQLITE_OK) {
985 return false;
986 }
987
988 sqlite3_bind_text(stmtp, 1, guid.c_str(), -1, SQLITE_TRANSIENT);
989
990 int GPXSeg = 0;
991 while (sqlite3_step(stmtp) == SQLITE_ROW) {
992 if (!new_trk) {
993 new_trk = new Track;
994 new_trk->m_GUID = guid;
995
996 // Set all the track attributes
997 new_trk->SetVisible(visibility == 1);
998 new_trk->SetName(name.c_str());
999 new_trk->m_TrackStartString = start_string.c_str();
1000 new_trk->m_TrackEndString = end_string.c_str();
1001 new_trk->m_width = width;
1002 new_trk->m_style = (wxPenStyle)style;
1003 new_trk->m_Colour = color;
1004 }
1005
1006 GPXSeg += 1;
1007
1008 double latitude = sqlite3_column_double(stmtp, 0);
1009 double longitude = sqlite3_column_double(stmtp, 1);
1010 std::string timestamp =
1011 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, 2));
1012 int point_order = sqlite3_column_int(stmtp, 3);
1013
1014 auto point = new TrackPoint(latitude, longitude, timestamp);
1015
1016 point->m_GPXTrkSegNo = GPXSeg;
1017 new_trk->AddPoint(point);
1018 }
1019 sqlite3_finalize(stmtp);
1020
1021 if (new_trk) {
1022 new_trk->SetCurrentTrackSeg(GPXSeg);
1023
1024 // Add the HTML links
1025 const char* sqlh = R"(
1026 SELECT guid, html_link, html_description, html_type
1027 FROM track_html_links
1028 WHERE track_guid = ?
1029 ORDER BY html_type ASC
1030 )";
1031
1032 sqlite3_stmt* stmt;
1033
1034 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1035 sqlite3_bind_text(stmt, 1, new_trk->m_GUID.ToStdString().c_str(), -1,
1036 SQLITE_TRANSIENT);
1037
1038 while (sqlite3_step(stmt) == SQLITE_ROW) {
1039 std::string link_guid =
1040 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1041 std::string link_link =
1042 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1043 std::string link_description =
1044 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1045 std::string link_type =
1046 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1047
1048 Hyperlink* h = new Hyperlink();
1049 h->DescrText = link_description;
1050 h->Link = link_link;
1051 h->LType = link_type;
1052
1053 new_trk->m_TrackHyperlinkList->Append(h);
1054 int yyp = 4;
1055 }
1056
1057 sqlite3_finalize(stmt);
1058
1059 } else {
1060 return false;
1061 }
1062
1063 // Insert the track into the global list
1064 g_TrackList.push_back(new_trk);
1065 // Add the selectable points and segments of the track
1066 pSelect->AddAllSelectableTrackSegments(new_trk);
1067 }
1068 }
1069 return true;
1070}
1071
1072bool NavObj_dB::DeleteTrack(Track* track) {
1073 if (!track) return false;
1074 std::string track_guid = track->m_GUID.ToStdString();
1075 const char* sql = "DELETE FROM tracks WHERE guid = ?";
1076 sqlite3_stmt* stmt;
1077
1078 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1079 sqlite3_bind_text(stmt, 1, track_guid.c_str(), -1, SQLITE_TRANSIENT);
1080 if (sqlite3_step(stmt) != SQLITE_DONE) {
1081 ReportError("DeleteTrack:step");
1082 sqlite3_finalize(stmt);
1083 return false;
1084 }
1085
1086 sqlite3_finalize(stmt);
1087 } else {
1088 return false;
1089 }
1090 return true;
1091}
1092
1093// Route support
1094
1095bool NavObj_dB::InsertRoute(Route* route) {
1096 bool rv = false;
1097 char* errMsg = 0;
1098
1099 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) {
1100 // Insert a new route
1101 wxString sql = wxString::Format("INSERT INTO routes (guid) VALUES ('%s')",
1102 route->m_GUID.ToStdString().c_str());
1103 if (!executeSQL(m_db, sql)) {
1104 return false;
1105 }
1106 UpdateDBRouteAttributes(route);
1107 }
1108
1109 sqlite3_exec(m_db, "BEGIN TRANSACTION", 0, 0, &errMsg);
1110 if (errMsg) {
1111 ReportError("InsertRoute:transaction");
1112 return false;
1113 }
1114
1115 // insert routepoints
1116 for (int i = 0; i < route->GetnPoints(); i++) {
1117 auto point = route->GetPoint(i + 1);
1118 // Add the bare point
1119 if (point) {
1120 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
1121 InsertRoutePointDB(m_db, point);
1122 UpdateDBRoutePointAttributes(point);
1123 }
1124 }
1125 }
1126
1127 // insert linkages
1128 for (int i = 0; i < route->GetnPoints(); i++) {
1129 auto point = route->GetPoint(i + 1);
1130 // Add the bare point
1131 if (point) {
1132 InsertRoutePointLink(m_db, route, point, i + 1);
1133 }
1134 }
1135
1136 // Add HTML links to route
1137 int NbrOfLinks = route->m_HyperlinkList->GetCount();
1138 if (NbrOfLinks > 0) {
1139 wxHyperlinkListNode* linknode = route->m_HyperlinkList->GetFirst();
1140 while (linknode) {
1141 Hyperlink* link = linknode->GetData();
1142
1143 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1144 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1145 link->DescrText.ToStdString(), link->Link.ToStdString(),
1146 link->LType.ToStdString());
1147 }
1148 linknode = linknode->GetNext();
1149 }
1150 }
1151
1152 sqlite3_exec(m_db, "COMMIT", 0, 0, &errMsg);
1153 rv = true;
1154 if (errMsg) {
1155 ReportError("InsertRoute:commit");
1156 rv = false;
1157 }
1158 return rv;
1159};
1160
1161bool NavObj_dB::UpdateRoute(Route* route) {
1162 bool rv = false;
1163 char* errMsg = 0;
1164
1165 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) return false;
1166
1167 UpdateDBRouteAttributes(route);
1168
1169 // update routepoints
1170 for (int i = 0; i < route->GetnPoints(); i++) {
1171 auto point = route->GetPoint(i + 1);
1172 // Add the bare point
1173 if (point) {
1174 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
1175 InsertRoutePointDB(m_db, point);
1176 }
1177 UpdateDBRoutePointAttributes(point);
1178 }
1179 }
1180
1181 // Delete and re-add point linkages
1182 const char* sql = "DELETE FROM routepoints_link WHERE route_guid = ?";
1183 sqlite3_stmt* stmt;
1184 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1185 sqlite3_bind_text(stmt, 1, route->m_GUID.ToStdString().c_str(), -1,
1186 SQLITE_TRANSIENT);
1187 } else {
1188 return false;
1189 }
1190 if (sqlite3_step(stmt) != SQLITE_DONE) {
1191 ReportError("UpdateRoute:step");
1192 sqlite3_finalize(stmt);
1193 return false;
1194 }
1195
1196 sqlite3_finalize(stmt);
1197
1198 for (int i = 0; i < route->GetnPoints(); i++) {
1199 auto point = route->GetPoint(i + 1);
1200 if (point) {
1201 InsertRoutePointLink(m_db, route, point, i + 1);
1202 }
1203 }
1204
1205 // Add HTML links to route
1206 int NbrOfLinks = route->m_HyperlinkList->GetCount();
1207 if (NbrOfLinks > 0) {
1208 wxHyperlinkListNode* linknode = route->m_HyperlinkList->GetFirst();
1209 while (linknode) {
1210 Hyperlink* link = linknode->GetData();
1211
1212 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1213 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1214 link->DescrText.ToStdString(), link->Link.ToStdString(),
1215 link->LType.ToStdString());
1216 }
1217 linknode = linknode->GetNext();
1218 }
1219 }
1220
1221 rv = true;
1222 if (errMsg) rv = false;
1223
1224 return rv;
1225};
1226
1227bool NavObj_dB::UpdateRouteViz(Route* route) {
1228 bool rv = false;
1229 char* errMsg = 0;
1230 if (!RouteExistsDB(m_db, route->m_GUID.ToStdString())) return false;
1231
1232 UpdateDBRouteAttributes(route);
1233 // update routepoints visibility
1234 for (int i = 0; i < route->GetnPoints(); i++) {
1235 auto point = route->GetPoint(i + 1);
1236 // Add the bare point
1237 if (point) {
1238 UpdateDBRoutePointViz(point);
1239 }
1240 }
1241 rv = true;
1242 if (errMsg) rv = false;
1243
1244 return rv;
1245};
1246
1247bool NavObj_dB::UpdateDBRouteAttributes(Route* route) {
1248 const char* sql =
1249 "UPDATE routes SET "
1250 "name = ?, "
1251 "description = ?, "
1252 "start_string = ?, "
1253 "end_string = ?, "
1254 "visibility = ?, "
1255 "shared_wp_viz = ?, "
1256 "planned_departure = ?, "
1257 "plan_speed = ?, "
1258 "time_format = ?, "
1259 "width = ?, "
1260 "style = ?, "
1261 "color = ? "
1262 "WHERE guid = ?";
1263
1264 sqlite3_stmt* stmt;
1265 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1266 sqlite3_bind_text(stmt, 1, route->GetName().ToStdString().c_str(), -1,
1267 SQLITE_TRANSIENT);
1268 sqlite3_bind_text(stmt, 2, route->m_RouteDescription.ToStdString().c_str(),
1269 -1, SQLITE_TRANSIENT);
1270 sqlite3_bind_text(stmt, 3, route->m_RouteStartString.ToStdString().c_str(),
1271 -1, SQLITE_TRANSIENT);
1272 sqlite3_bind_text(stmt, 4, route->m_RouteEndString.ToStdString().c_str(),
1273 -1, SQLITE_TRANSIENT);
1274 sqlite3_bind_int(stmt, 5, route->IsVisible());
1275 sqlite3_bind_int(stmt, 6, route->GetSharedWPViz());
1276 sqlite3_bind_int(stmt, 7, route->m_PlannedDeparture.GetTicks());
1277 sqlite3_bind_double(stmt, 8, route->m_PlannedSpeed);
1278 sqlite3_bind_text(stmt, 9, route->m_TimeDisplayFormat.ToStdString().c_str(),
1279 -1, SQLITE_TRANSIENT);
1280 sqlite3_bind_int(stmt, 10, route->m_width);
1281 sqlite3_bind_int(stmt, 11,
1282 (int)(route->m_style)); // track->m_style.c_str(),
1283 sqlite3_bind_text(stmt, 12, route->m_Colour.ToStdString().c_str(), -1,
1284 SQLITE_TRANSIENT);
1285 sqlite3_bind_text(stmt, 13, route->m_GUID.c_str(), route->m_GUID.size(),
1286 SQLITE_TRANSIENT);
1287 } else {
1288 return false;
1289 }
1290
1291 if (sqlite3_step(stmt) != SQLITE_DONE) {
1292 ReportError("UpdateDBRouteAttributesA:step");
1293 sqlite3_finalize(stmt);
1294 return false;
1295 }
1296
1297 sqlite3_finalize(stmt);
1298
1299 // Update the HTML links
1300 // The list of links is freshly rebuilt when this method is called
1301 // So start by deleting all existing bcomments
1302 DeleteAllCommentsForRoute(m_db, route->m_GUID.ToStdString());
1303
1304 // Now add all the links to db
1305 int NbrOfLinks = route->m_HyperlinkList->GetCount();
1306 if (NbrOfLinks > 0) {
1307 wxHyperlinkListNode* linknode = route->m_HyperlinkList->GetFirst();
1308 while (linknode) {
1309 Hyperlink* link = linknode->GetData();
1310
1311 if (!RouteHtmlLinkExists(m_db, link->GUID)) {
1312 InsertRouteHTML(m_db, route->m_GUID.ToStdString(), link->GUID,
1313 link->DescrText.ToStdString(), link->Link.ToStdString(),
1314 link->LType.ToStdString());
1315 } else {
1316 const char* sql =
1317 "UPDATE route_html_links SET "
1318 "html_link = ?, "
1319 "html_description = ?, "
1320 "html_type = ? "
1321 "WHERE guid = ?";
1322 sqlite3_stmt* stmt;
1323 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1324 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
1325 SQLITE_TRANSIENT);
1326 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
1327 SQLITE_TRANSIENT);
1328 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
1329 SQLITE_TRANSIENT);
1330 }
1331 if (sqlite3_step(stmt) != SQLITE_DONE) {
1332 return false;
1333 }
1334 if (sqlite3_step(stmt) != SQLITE_DONE) {
1335 ReportError("UpdateDBRouteAttributesB:step");
1336 sqlite3_finalize(stmt);
1337 return false;
1338 }
1339
1340 sqlite3_finalize(stmt);
1341 }
1342
1343 linknode = linknode->GetNext();
1344 }
1345 }
1346 return true;
1347}
1348
1349bool NavObj_dB::UpdateDBRoutePointAttributes(RoutePoint* point) {
1350 const char* sql =
1351 "UPDATE routepoints SET "
1352 "lat = ?, "
1353 "lon = ?, "
1354 "Symbol = ?, "
1355 "Name = ?, "
1356 "description = ?, "
1357 "TideStation = ?, "
1358 "plan_speed = ?, "
1359 "etd = ?, "
1360 "Type = ?, "
1361 "Time = ?, "
1362 "ArrivalRadius = ?, "
1363 "RangeRingsNumber = ?, "
1364 "RangeRingsStep = ?, "
1365 "RangeRingsStepUnits = ?, "
1366 "RangeRingsVisible = ?, "
1367 "RangeRingsColour = ?, "
1368 "ScaleMin = ?, "
1369 "ScaleMax = ?, "
1370 "UseScale = ?, "
1371 "visibility = ?, "
1372 "viz_name = ?, "
1373 "shared = ?, "
1374 "isolated = ? "
1375 "WHERE guid = ?";
1376
1377 sqlite3_stmt* stmt;
1378 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1379 sqlite3_bind_double(stmt, 1, point->GetLatitude());
1380 sqlite3_bind_double(stmt, 2, point->GetLongitude());
1381 sqlite3_bind_text(stmt, 3, point->GetIconName().ToStdString().c_str(), -1,
1382 SQLITE_TRANSIENT);
1383 sqlite3_bind_text(stmt, 4, point->GetName().ToStdString().c_str(), -1,
1384 SQLITE_TRANSIENT);
1385 sqlite3_bind_text(stmt, 5, point->GetDescription().ToStdString().c_str(),
1386 -1, SQLITE_TRANSIENT);
1387 sqlite3_bind_text(stmt, 6, point->m_TideStation.ToStdString().c_str(), -1,
1388 SQLITE_TRANSIENT);
1389 sqlite3_bind_double(stmt, 7, point->GetPlannedSpeed());
1390 time_t etd = -1;
1391 if (point->GetETD().IsValid()) etd = point->GetETD().GetTicks();
1392 sqlite3_bind_int(stmt, 8, etd);
1393 sqlite3_bind_text(stmt, 9, "type", -1, SQLITE_TRANSIENT);
1394 sqlite3_bind_text(stmt, 10, point->m_timestring.ToStdString().c_str(), -1,
1395 SQLITE_TRANSIENT);
1396 sqlite3_bind_double(stmt, 11, point->m_WaypointArrivalRadius);
1397
1398 sqlite3_bind_int(stmt, 12, point->m_iWaypointRangeRingsNumber);
1399 sqlite3_bind_double(stmt, 13, point->m_fWaypointRangeRingsStep);
1400 sqlite3_bind_int(stmt, 14, point->m_iWaypointRangeRingsStepUnits);
1401 sqlite3_bind_int(stmt, 15, point->m_bShowWaypointRangeRings);
1402 sqlite3_bind_text(
1403 stmt, 16,
1404 point->m_wxcWaypointRangeRingsColour.GetAsString(wxC2S_HTML_SYNTAX)
1405 .ToStdString()
1406 .c_str(),
1407 -1, SQLITE_TRANSIENT);
1408
1409 sqlite3_bind_int(stmt, 17, point->GetScaMin());
1410 sqlite3_bind_int(stmt, 18, point->GetScaMax());
1411 sqlite3_bind_int(stmt, 19, point->GetUseSca());
1412
1413 sqlite3_bind_int(stmt, 20, point->IsVisible());
1414 sqlite3_bind_int(stmt, 21, point->IsNameShown());
1415 sqlite3_bind_int(stmt, 22, point->IsShared());
1416 int iso = point->m_bIsolatedMark;
1417 sqlite3_bind_int(stmt, 23, iso); // point->m_bIsolatedMark);
1418
1419 sqlite3_bind_text(stmt, 24, point->m_GUID.ToStdString().c_str(), -1,
1420 SQLITE_TRANSIENT);
1421
1422 } else {
1423 return false;
1424 }
1425
1426 if (sqlite3_step(stmt) != SQLITE_DONE) {
1427 ReportError("UpdateDBRoutePointAttributesA:step");
1428 sqlite3_finalize(stmt);
1429 return false;
1430 }
1431
1432 sqlite3_finalize(stmt);
1433
1434 // Update the HTML links
1435 // The list of links is freshly rebuilt when this method is called
1436 // So start by deleting all existing bcomments
1437 DeleteAllCommentsForRoutePoint(m_db, point->m_GUID.ToStdString());
1438
1439 // Now add all the links to db
1440 int NbrOfLinks = point->m_HyperlinkList->GetCount();
1441 if (NbrOfLinks > 0) {
1442 wxHyperlinkListNode* linknode = point->m_HyperlinkList->GetFirst();
1443 while (linknode) {
1444 Hyperlink* link = linknode->GetData();
1445
1446 if (!RoutePointHtmlLinkExists(m_db, link->GUID)) {
1447 InsertRoutePointHTML(m_db, point->m_GUID.ToStdString(), link->GUID,
1448 link->DescrText.ToStdString(),
1449 link->Link.ToStdString(),
1450 link->LType.ToStdString());
1451 } else {
1452 const char* sql =
1453 "UPDATE routepoint_html_links SET "
1454 "html_link = ?, "
1455 "html_description = ?, "
1456 "html_type = ? "
1457 "WHERE guid = ?";
1458 sqlite3_stmt* stmt;
1459 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1460 sqlite3_bind_text(stmt, 3, link->Link.ToStdString().c_str(), -1,
1461 SQLITE_TRANSIENT);
1462 sqlite3_bind_text(stmt, 4, link->DescrText.ToStdString().c_str(), -1,
1463 SQLITE_TRANSIENT);
1464 sqlite3_bind_text(stmt, 5, link->LType.ToStdString().c_str(), -1,
1465 SQLITE_TRANSIENT);
1466 }
1467 if (sqlite3_step(stmt) != SQLITE_DONE) {
1468 return false;
1469 }
1470 if (sqlite3_step(stmt) != SQLITE_DONE) {
1471 ReportError("UpdateDBRoutePointAttributesB:step-h");
1472 sqlite3_finalize(stmt);
1473 return false;
1474 }
1475
1476 sqlite3_finalize(stmt);
1477 }
1478
1479 linknode = linknode->GetNext();
1480 }
1481 }
1482
1483 return true;
1484}
1485
1486bool NavObj_dB::UpdateDBRoutePointViz(RoutePoint* point) {
1487 const char* sql =
1488 "UPDATE routepoints SET "
1489 "visibility = ? "
1490 "WHERE guid = ?";
1491
1492 sqlite3_stmt* stmt;
1493 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1494 sqlite3_bind_int(stmt, 1, point->IsVisible());
1495 sqlite3_bind_text(stmt, 2, point->m_GUID.ToStdString().c_str(), -1,
1496 SQLITE_TRANSIENT);
1497
1498 } else {
1499 return false;
1500 }
1501
1502 if (sqlite3_step(stmt) != SQLITE_DONE) {
1503 ReportError("UpdateDBRoutePointVizA:step");
1504 sqlite3_finalize(stmt);
1505 return false;
1506 }
1507
1508 sqlite3_finalize(stmt);
1509
1510 return true;
1511}
1512
1513bool NavObj_dB::DeleteRoute(Route* route) {
1514 if (m_importing) return false;
1515 if (!route) return false;
1516 std::string route_guid = route->m_GUID.ToStdString();
1517 const char* sql = "DELETE FROM routes WHERE guid = ?";
1518 sqlite3_stmt* stmt;
1519
1520 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
1521 sqlite3_bind_text(stmt, 1, route_guid.c_str(), -1, SQLITE_TRANSIENT);
1522 if (sqlite3_step(stmt) != SQLITE_DONE) {
1523 ReportError("DeleteRoute:step");
1524 sqlite3_finalize(stmt);
1525 return false;
1526 }
1527 sqlite3_finalize(stmt);
1528 } else {
1529 return false;
1530 }
1531 return true;
1532}
1533
1534bool NavObj_dB::LoadAllRoutes() {
1535 const char* sql =
1536 "SELECT "
1537 "guid, "
1538 "name, "
1539 "description, "
1540 "start_string, "
1541 "end_string, "
1542 "visibility, "
1543 "shared_wp_viz, "
1544 "planned_departure, "
1545 "plan_speed, "
1546 "time_format, "
1547 "width, "
1548 "style, "
1549 "color "
1550 "FROM routes "
1551 "ORDER BY created_at ASC";
1552
1553 sqlite3_stmt* stmt;
1554 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) != SQLITE_OK) {
1555 return false;
1556 }
1557
1558 int errcode0 = SQLITE_OK;
1559 while ((errcode0 = sqlite3_step(stmt)) == SQLITE_ROW) {
1560 std::string guid =
1561 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1562 std::string name =
1563 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1564 std::string description =
1565 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1566 std::string start_string =
1567 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1568 std::string end_string =
1569 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
1570 int visibility = sqlite3_column_int(stmt, 5);
1571 int sharewp_viz = sqlite3_column_int(stmt, 6);
1572 time_t planned_departure_ticks = sqlite3_column_int(stmt, 7);
1573 double plan_speed = sqlite3_column_double(stmt, 8);
1574 std::string time_format =
1575 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 9));
1576
1577 int width = sqlite3_column_int(stmt, 10);
1578 int style = sqlite3_column_int(stmt, 11);
1579 std::string color =
1580 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 12));
1581
1582 Route* route = NULL;
1583
1584 // Add the route_points
1585 const char* sql = R"(
1586 SELECT latitude, longitude, timestamp, point_order
1587 FROM trk_points
1588 WHERE track_guid = ?
1589 ORDER BY point_order ASC
1590 )";
1591
1592 const char* sqlp =
1593 "SELECT p.guid, "
1594 "p.lat, "
1595 "p.lon, "
1596 "p.Symbol, "
1597 "p.Name, "
1598 "p.description, "
1599 "p.TideStation, "
1600 "p.plan_speed, "
1601 "p.etd, "
1602 "p.Type, "
1603 "p.Time, "
1604 "p.ArrivalRadius, "
1605 "p.RangeRingsNumber, "
1606 "p.RangeRingsStep, "
1607 "p.RangeRingsStepUnits, "
1608 "p.RangeRingsVisible, "
1609 "p.RangeRingsColour, "
1610 "p.ScaleMin, "
1611 "p.ScaleMax, "
1612 "p.UseScale, "
1613 "p.visibility, "
1614 "p.viz_name, "
1615 "p.shared, "
1616 "p.isolated "
1617 "FROM routepoints_link tp "
1618 "JOIN routepoints p ON p.guid = tp.point_guid "
1619 "WHERE tp.route_guid = ? "
1620 "ORDER BY tp.point_order ASC";
1621
1622 sqlite3_stmt* stmtp;
1623 if (sqlite3_prepare_v2(m_db, sqlp, -1, &stmtp, nullptr) != SQLITE_OK) {
1624 ReportError("LoadAllRoutes-B:prepare");
1625 return false;
1626 }
1627
1628 sqlite3_bind_text(stmtp, 1, guid.c_str(), -1, SQLITE_TRANSIENT);
1629
1630 int GPXSeg = 0;
1631 int errcode = SQLITE_OK;
1632 while ((errcode = sqlite3_step(stmtp)) == SQLITE_ROW) {
1633 if (!route) {
1634 route = new Route;
1635 route->m_GUID = guid;
1636
1637 // Set all the route attributes
1638 route->SetVisible(visibility == 1);
1639 route->m_RouteNameString = name.c_str();
1640 route->m_RouteDescription = description.c_str();
1641 route->m_RouteStartString = start_string.c_str();
1642 route->m_RouteEndString = end_string.c_str();
1643 route->SetVisible(visibility == 1);
1644 route->SetSharedWPViz(sharewp_viz == 1);
1645 route->m_PlannedDeparture.Set((time_t)planned_departure_ticks);
1646 route->m_PlannedSpeed = plan_speed;
1647 route->m_TimeDisplayFormat = time_format.c_str();
1648
1649 route->m_width = width;
1650 route->m_style = (wxPenStyle)style;
1651 route->m_Colour = color;
1652 }
1653
1654 // Grab all the point attributes from the SELECT statement
1655 int col = 0;
1656 std::string point_guid =
1657 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1658 double latitude = sqlite3_column_double(stmtp, col++);
1659 double longitude = sqlite3_column_double(stmtp, col++);
1660 std::string symbol =
1661 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1662 std::string name =
1663 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1664 std::string description =
1665 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1666 std::string tide_station =
1667 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1668 double plan_speed = sqlite3_column_double(stmtp, col++);
1669 time_t etd_epoch = sqlite3_column_int(stmtp, col++);
1670 std::string type =
1671 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1672 std::string time =
1673 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1674 double arrival_radius = sqlite3_column_double(stmtp, col++);
1675
1676 int range_ring_number = sqlite3_column_int(stmtp, col++);
1677 double range_ring_step = sqlite3_column_double(stmtp, col++);
1678 int range_ring_units = sqlite3_column_int(stmtp, col++);
1679 int range_ring_visible = sqlite3_column_int(stmtp, col++);
1680 std::string range_ring_color =
1681 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1682
1683 int scamin = sqlite3_column_int(stmtp, col++);
1684 int scamax = sqlite3_column_int(stmtp, col++);
1685 int use_scaminmax = sqlite3_column_int(stmtp, col++);
1686
1687 int visibility = sqlite3_column_int(stmtp, col++);
1688 int viz_name = sqlite3_column_int(stmtp, col++);
1689 int shared = sqlite3_column_int(stmtp, col++);
1690 int isolated = sqlite3_column_int(stmtp, col++);
1691
1692 RoutePoint* point;
1693 // RoutePoint exists already, in another route?
1694 auto containing_route =
1695 g_pRouteMan->FindRouteContainingWaypoint(point_guid);
1696
1697 if (containing_route) {
1698 point = containing_route->GetPoint(point_guid);
1699 } else {
1700 point =
1701 new RoutePoint(latitude, longitude, symbol, name, point_guid, true);
1702
1703 point->m_MarkDescription = description;
1704 point->m_TideStation = tide_station;
1705 point->SetPlannedSpeed(plan_speed);
1706
1707 wxDateTime etd;
1708 etd.Set((time_t)etd_epoch);
1709 if (etd.IsValid()) point->SetETD(etd);
1710
1711 point->m_WaypointArrivalRadius = arrival_radius;
1712
1713 point->m_iWaypointRangeRingsNumber = range_ring_number;
1714 point->m_fWaypointRangeRingsStep = range_ring_step;
1715 point->m_iWaypointRangeRingsStepUnits = range_ring_units;
1716 point->SetShowWaypointRangeRings(range_ring_visible == 1);
1717 // TODO
1718 point->m_wxcWaypointRangeRingsColour.Set(range_ring_color);
1719
1720 point->SetScaMin(scamin);
1721 point->SetScaMax(scamax);
1722 point->SetUseSca(use_scaminmax == 1);
1723
1724 point->SetVisible(visibility == 1);
1725 point->SetNameShown(viz_name == 1);
1726 point->SetShared(shared == 1);
1727 point->m_bIsolatedMark = (isolated == 1);
1728
1729 // Add the point HTML links
1730 const char* sqlh = R"(
1731 SELECT guid, html_link, html_description, html_type
1732 FROM routepoint_html_links
1733 WHERE routepoint_guid = ?
1734 ORDER BY html_type ASC
1735 )";
1736
1737 sqlite3_stmt* stmt;
1738
1739 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1740 sqlite3_bind_text(stmt, 1, point->m_GUID.ToStdString().c_str(), -1,
1741 SQLITE_TRANSIENT);
1742
1743 while (sqlite3_step(stmt) == SQLITE_ROW) {
1744 std::string link_guid =
1745 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1746 std::string link_link =
1747 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1748 std::string link_description =
1749 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1750 std::string link_type =
1751 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1752
1753 Hyperlink* h = new Hyperlink();
1754 h->DescrText = link_description;
1755 h->Link = link_link;
1756 h->LType = link_type;
1757
1758 point->m_HyperlinkList->Append(h);
1759 }
1760 }
1761 }
1762
1763 route->AddPoint(point);
1764 } // route points
1765 sqlite3_finalize(stmtp);
1766 if (errcode != SQLITE_DONE) {
1767 ReportError("LoadAllRoutes-A:step");
1768 return false;
1769 }
1770
1771 // Add route html links
1772 if (route) {
1773 // Add the HTML links
1774 const char* sqlh = R"(
1775 SELECT guid, html_link, html_description, html_type
1776 FROM route_html_links
1777 WHERE route_guid = ?
1778 ORDER BY html_type ASC
1779 )";
1780
1781 sqlite3_stmt* stmt;
1782
1783 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1784 sqlite3_bind_text(stmt, 1, route->m_GUID.ToStdString().c_str(), -1,
1785 SQLITE_TRANSIENT);
1786
1787 int errcode2 = SQLITE_OK;
1788 while ((errcode2 = sqlite3_step(stmt)) == SQLITE_ROW) {
1789 std::string link_guid =
1790 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1791 std::string link_link =
1792 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1793 std::string link_description =
1794 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1795 std::string link_type =
1796 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1797
1798 Hyperlink* h = new Hyperlink();
1799 h->DescrText = link_description;
1800 h->Link = link_link;
1801 h->LType = link_type;
1802
1803 route->m_HyperlinkList->Append(h);
1804 }
1805 if (errcode != SQLITE_DONE) {
1806 ReportError("LoadAllRoutes-B:step");
1807 return false;
1808 }
1809
1810 sqlite3_finalize(stmt);
1811
1812 } else {
1813 ReportError("LoadAllRoutes-B:prepare");
1814 return false;
1815 }
1816 }
1817
1818 // Insert the route into the global list
1819 InsertRouteA(route,
1820 nullptr); // NavObjectChanges::getInstance() //TODO adding
1821 // changes will force the xml file to be updated?
1822
1823 } // routes
1824 if (errcode0 != SQLITE_DONE) {
1825 ReportError("LoadAllRoutes-C:step");
1826 return false;
1827 }
1828
1829 return true;
1830}
1831
1832bool NavObj_dB::LoadAllPoints() {
1833 const char* sqlp =
1834 "SELECT "
1835 "p.guid, "
1836 "p.lat, "
1837 "p.lon, "
1838 "p.Symbol, "
1839 "p.Name, "
1840 "p.description, "
1841 "p.TideStation, "
1842 "p.plan_speed, "
1843 "p.etd, "
1844 "p.Type, "
1845 "p.Time, "
1846 "p.ArrivalRadius, "
1847 "p.RangeRingsNumber, "
1848 "p.RangeRingsStep, "
1849 "p.RangeRingsStepUnits, "
1850 "p.RangeRingsVisible, "
1851 "p.RangeRingsColour, "
1852 "p.ScaleMin, "
1853 "p.ScaleMax, "
1854 "p.UseScale, "
1855 "p.visibility, "
1856 "p.viz_name, "
1857 "p.shared, "
1858 "p.isolated "
1859 "FROM routepoints p ";
1860
1861 RoutePoint* point = nullptr;
1862
1863 sqlite3_stmt* stmtp;
1864 if (sqlite3_prepare_v2(m_db, sqlp, -1, &stmtp, nullptr) != SQLITE_OK) {
1865 return false;
1866 }
1867
1868 while (sqlite3_step(stmtp) == SQLITE_ROW) {
1869 // Grab all the point attributes from the SELECT statement
1870 int col = 0;
1871 std::string point_guid =
1872 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1873 double latitude = sqlite3_column_double(stmtp, col++);
1874 double longitude = sqlite3_column_double(stmtp, col++);
1875 std::string symbol =
1876 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1877 std::string name =
1878 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1879 std::string description =
1880 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1881 std::string tide_station =
1882 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1883 double plan_speed = sqlite3_column_double(stmtp, col++);
1884 time_t etd = sqlite3_column_int(stmtp, col++);
1885 std::string type =
1886 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1887 std::string point_time_string =
1888 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1889 double arrival_radius = sqlite3_column_double(stmtp, col++);
1890
1891 int range_ring_number = sqlite3_column_int(stmtp, col++);
1892 double range_ring_step = sqlite3_column_double(stmtp, col++);
1893 int range_ring_units = sqlite3_column_int(stmtp, col++);
1894 int range_ring_visible = sqlite3_column_int(stmtp, col++);
1895 std::string range_ring_color =
1896 reinterpret_cast<const char*>(sqlite3_column_text(stmtp, col++));
1897
1898 int scamin = sqlite3_column_int(stmtp, col++);
1899 int scamax = sqlite3_column_int(stmtp, col++);
1900 int use_scaminmax = sqlite3_column_int(stmtp, col++);
1901
1902 int visibility = sqlite3_column_int(stmtp, col++);
1903 int viz_name = sqlite3_column_int(stmtp, col++);
1904 int shared = sqlite3_column_int(stmtp, col++);
1905 int isolated = sqlite3_column_int(stmtp, col++);
1906
1907 if (isolated) {
1908 point =
1909 new RoutePoint(latitude, longitude, symbol, name, point_guid, false);
1910
1911 point->m_MarkDescription = description;
1912 point->m_TideStation = tide_station;
1913 point->SetPlannedSpeed(plan_speed);
1914 point->m_WaypointArrivalRadius = arrival_radius;
1915
1916 point->m_iWaypointRangeRingsNumber = range_ring_number;
1917 point->m_fWaypointRangeRingsStep = range_ring_step;
1918 point->m_iWaypointRangeRingsStepUnits = range_ring_units;
1919 point->SetShowWaypointRangeRings(range_ring_visible == 1);
1920 // TODO
1921 // point->m_wxcWaypointRangeRingsColour = range_ring_color;
1922
1923 point->SetScaMin(scamin);
1924 point->SetScaMax(scamax);
1925 point->SetUseSca(use_scaminmax == 1);
1926
1927 point->SetVisible(visibility == 1);
1928 point->SetNameShown(viz_name == 1);
1929 point->SetShared(shared == 1);
1930 point->m_bIsolatedMark = (isolated == 1);
1931
1932 if (point_time_string.size()) {
1933 wxString sdt(point_time_string.c_str());
1934 point->m_timestring = sdt;
1935 ParseGPXDateTime(point->m_CreateTimeX, sdt);
1936 }
1937 // Add it here
1938 pWayPointMan->AddRoutePoint(point);
1939 pSelect->AddSelectableRoutePoint(point->m_lat, point->m_lon, point);
1940 }
1941 } // points
1942 sqlite3_finalize(stmtp);
1943
1944 if (point) {
1945 // Add the point HTML links
1946 const char* sqlh = R"(
1947 SELECT guid, html_link, html_description, html_type
1948 FROM routepoint_html_links
1949 WHERE routepoint_guid = ?
1950 ORDER BY html_type ASC
1951 )";
1952
1953 sqlite3_stmt* stmt;
1954
1955 if (sqlite3_prepare_v2(m_db, sqlh, -1, &stmt, nullptr) == SQLITE_OK) {
1956 sqlite3_bind_text(stmt, 1, point->m_GUID.ToStdString().c_str(), -1,
1957 SQLITE_TRANSIENT);
1958
1959 while (sqlite3_step(stmt) == SQLITE_ROW) {
1960 std::string link_guid =
1961 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
1962 std::string link_link =
1963 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
1964 std::string link_description =
1965 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
1966 std::string link_type =
1967 reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
1968
1969 Hyperlink* h = new Hyperlink();
1970 h->DescrText = link_description;
1971 h->Link = link_link;
1972 h->LType = link_type;
1973
1974 point->m_HyperlinkList->Append(h);
1975 }
1976
1977 sqlite3_finalize(stmt);
1978
1979 } else {
1980 return false;
1981 }
1982 }
1983 return true;
1984}
1985bool NavObj_dB::InsertRoutePoint(RoutePoint* point) {
1986 bool rv = false;
1987 char* errMsg = 0;
1988
1989 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) {
1990 // Insert a new route point
1991 wxString sql =
1992 wxString::Format("INSERT INTO routepoints (guid) VALUES ('%s')",
1993 point->m_GUID.ToStdString().c_str());
1994 if (!executeSQL(m_db, sql)) {
1995 return false;
1996 }
1997 }
1998
1999 UpdateDBRoutePointAttributes(point);
2000
2001 // Add HTML links to routepoint
2002 int NbrOfLinks = point->m_HyperlinkList->GetCount();
2003 if (NbrOfLinks > 0) {
2004 wxHyperlinkListNode* linknode = point->m_HyperlinkList->GetFirst();
2005 while (linknode) {
2006 Hyperlink* link = linknode->GetData();
2007
2008 if (!RoutePointHtmlLinkExists(m_db, link->GUID)) {
2009 InsertRoutePointHTML(m_db, point->m_GUID.ToStdString(), link->GUID,
2010 link->DescrText.ToStdString(),
2011 link->Link.ToStdString(),
2012 link->LType.ToStdString());
2013 }
2014 linknode = linknode->GetNext();
2015 }
2016 }
2017
2018 return true;
2019}
2020
2021bool NavObj_dB::DeleteRoutePoint(RoutePoint* point) {
2022 if (m_importing) return false;
2023 if (!point) return false;
2024
2025 std::string point_guid = point->m_GUID.ToStdString();
2026
2027 // DeleteAllCommentsForRoutePoint(m_db, point_guid);
2028
2029 const char* sql = "DELETE FROM routepoints WHERE guid = ?";
2030 sqlite3_stmt* stmt;
2031
2032 if (sqlite3_prepare_v2(m_db, sql, -1, &stmt, nullptr) == SQLITE_OK) {
2033 sqlite3_bind_text(stmt, 1, point_guid.c_str(), -1, SQLITE_TRANSIENT);
2034 if (sqlite3_step(stmt) != SQLITE_DONE) {
2035 ReportError("DeleteRoutePoint:step");
2036 sqlite3_finalize(stmt);
2037 return false;
2038 }
2039
2040 sqlite3_finalize(stmt);
2041 } else {
2042 return false;
2043 }
2044 return true;
2045}
2046
2047bool NavObj_dB::UpdateRoutePoint(RoutePoint* point) {
2048 if (m_importing) return false;
2049 if (!RoutePointExists(m_db, point->m_GUID.ToStdString())) return false;
2050 UpdateDBRoutePointAttributes(point);
2051 return true;
2052}
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::GetETD
wxDateTime GetETD()
Retrieves the Estimated Time of Departure for this waypoint, in UTC.
Definition route_point.cpp:463
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::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.