SQLite

[hackoon]

SQLite database support would greatly improve speed and extensions for opencpn (see discussion about data-sharing) Too many tracks and routes make opencpn quite slow already with SQL support it could improve speed while adding advanced features for data exchange and synchronization. XML is a nice format for storing some local data and for exporting waypoints etc but it gets too slow with to much data.
It would add a dependency to a statical linked libsqlite but than should not be a great problem for portability (firefox uses sqlite too)
The chart-database would also be improved with sqlite, it would also be a good starting point to try out sqlite support.

[Psyches]

Originally Posted by hackoon

SQLite database support would greatly improve speed and extensions for opencpn (see discussion about data-sharing) Too many tracks and routes make opencpn quite slow already with SQL support it could improve speed while adding advanced features for data exchange and synchronization.

Hackoon, is this based on a performance analysis that you've done? If so, I'd like to see the results and how you got them. There are some perf problems I've come across, but they can be solved easily without a database. It's possible we'll need to tweak route, track, or waypoint storage to accommodate performance issues if folks start running into them. But if you haven't actually seen problems: before we just assume that SQLite is an important addition and OpenCPN is too slow without it - let's find parameters or a real world problem case. As I mentioned on another thread, I think SQLite is currently overkill, and until it isn't, we have higher priorities.

XML is a nice format for storing some local data and for exporting waypoints etc but it gets too slow with to much data.
It would add a dependency to a statical linked libsqlite but than should not be a great problem for portability (firefox uses sqlite too)
The chart-database would also be improved with sqlite, it would also be a good starting point to try out sqlite support.

I agree, we need to be careful with using XML as a normal persistence path. However, there are other ways to persist data that don't require SQL or a relational store. The current chart metadata file is one example. The SENC S57 directory is another. So again, I'm not convinced yet that it's a high priority to add a relational store here...maybe someday.

Mark

[philocat]

As much as I appreciate Hackoons help re AIS I'd second Marks view and vote for the KISS principle even if not as scalable and robust as SQlite is. The more scalable we get the bigger the memory footprint and the risk of broken linkages out at sea.

My preference would be to use structured text based files which are human readable and resonably compact, eg 1 record per line with key description/identifier at beginining of each line or a group. Failing that probably XML given its common useage incl. gpx format. However I lament the verboseness of the data structure itself with redundant!? end tags for example. Keep in mind that I foresee waypoints routes etc in the future being sent between boat users much more frequently than actual chart data itself probably on bandwidth limited radio comms. If stored in a file rather than a database then it is more easily simply accessed outside the application.

Originally Posted by Psyches

I agree, we need to be careful with using XML as a normal persistence path. However, there are other ways to persist data that don't require SQL or a relational store. The current chart metadata file is one example. The SENC S57 directory is another. So again, I'm not convinced yet that it's a high priority to add a relational store here...maybe someday.

Mark

[Strygaldwir]

Bite my tongue, bite my tongue. I am not at work, I am not at work!

[idpnd]

Originally Posted by otya

Failing that probably XML given its common useage incl. gpx format. However I lament the verboseness of the data structure itself

I concur, a database may be sci-fi at the moment, but a standard file structure for all data (e.g. gpx or kml) would be of great advantage to (manual) data sharing schemes, such as this one.

Thanks for your attention.

[sredna]

openCPN is already able to write GPX, moving the location data out of the config file could be a nice first step

IMHO, KML is more overkill than using sqlite btw, we would have to implement a small selection of it, leading to much possible grief. And it is actually not well suited for waypoints and routes. If you want to convert use gpsbabel or sites that understands gpx

[Psyches]

Originally Posted by sredna

openCPN is already able to write GPX, moving the location data out of the config file could be a nice first step

Anders, I completely agree. That's a good near-term step.

IMHO, KML is more overkill than using sqlite btw, we would have to implement a small selection of it, leading to much possible grief. And it is actually not well suited for waypoints and routes. If you want to convert use gpsbabel or sites that understands gpx

Also agree KML is big and a subset is what we'd go after. A big diff between KML support & SQLite is: SQLite proper integration will affect many areas of the code at low-level, and to do it best, force revisiting and rewriting all persistence interfaces as well as their corresponding data access components. Caching strategy may need to be re-thought too as a side-effect.

KML support will force better higher-level abstractions to allow arbitrary layer read/write of display data (styled points, lines, bitmaps with associated callbacks for hover, selection, action - context menu or dialog) and should be possible to implement as a side-effect and separable task to plug-in support. That is, once plug-in support works, KML can be done and/or iteratively improved.

Anyway, that's the way I see it but I'm open for other views or clarifications...

Mark

[hackoon]

Originally Posted by Psyches

Hackoon, is this based on a performance analysis that you've done? If so, I'd like to see the results and how you got them. There are some perf problems I've come across, but they can be solved easily without a database. It's possible we'll need to tweak route, track, or waypoint storage to accommodate performance issues if folks start running into them. But if you haven't actually seen problems: before we just assume that SQLite is an important addition and OpenCPN is too slow without it - let's find parameters or a real world problem case. As I mentioned on another thread, I think SQLite is currently overkill, and until it isn't, we have higher priorities.

SQL(ite) is as far I know of the fastest disc-based data-management. As for my experience, a long track for example will slow opencpn, with sqlite you would only load area relevant data into the memory. That would actually speed up opencpn and lower memory footprint. sqlite is a quite small lib and as it would free some redundant code from the chart-database the difference would be insignificant.
Anyway I would be happy to hear of alternatives to sqlite! I'm a fan of KISS too but still think that offline based data-sharing would be an essential feature in todays online world. I would prefer to eliminate GRIB and tide support (which are quite redundant by xtide and zyGrib anyway).

[Netsurfer]

Originally Posted by hackoon

SQL(ite) is as far I know of the fastest disc-based data-management. As for my experience, a long track for example will slow opencpn, with sqlite you would only load area relevant data into the memory. That would actually speed up opencpn and lower memory footprint. sqlite is a quite small lib and as it would free some redundant code from the chart-database the difference would be insignificant.

Another point to keep in mind for further developement is that OCPN might not only be used on "high-end" Notebooks (just think about Netbooks).
In my opinion the option to use OCPN on small Netbooks and "low-end" NB should have the/ one of the highest prioritie(s).

Anyway I would be happy to hear of alternatives to sqlite! I'm a fan of KISS too but still think that offline based data-sharing would be an essential feature in todays online world.

I agree to your last sentence. Mainly because if you are online you don't have any problems at all. Then you can google and search thousands of Websites for your desired information.

Even though more and more marinas offer W-LAN access it's all over when you are just half a mile out to sea. Then the only available information is that which you can reach offline.

I would prefer to eliminate GRIB and tide support (which are quite redundant by xtide and zyGrib anyway).

I disapprove of doing so.
One of the main goals a navigation software should achieve is to make it possible to see all relevant information on demand in one (application) window together with the geographical info (which is of course essential).
And not to forget about the "ease of use" - not every skipper using e-navigation is a computer specialist (believe me - I know 3 of that kind ).

I'm also not seeing the problem, due every of this additional 'features' is optional. So they do not consume any resources until you use them!

[kevingy]

Originally Posted by Psyches

There are some perf problems I've come across, but they can be solved easily without a database. ... As I mentioned on another thread, I think SQLite is currently overkill, and until it isn't, we have higher priorities.

SQLite is overkill?!? I beg to differ. SQL Server is overkill. SQLite is, well..., "lite". SQLite is likely smaller and faster than the XML parser being used. Then, when you compare the low signal to noise ratio of data stored in XML, data files converted from XML to SQLite are likely to be tremendously smaller. Databases define the schema once in binary. XML defines the schema continuously in plain text.

Originally Posted by Psyches

I agree, we need to be careful with using XML as a normal persistence path. However, there are other ways to persist data that don't require SQL or a relational store.

Agreed. XML is dreadfully inefficient as a data storage medium. In order to achieve maximum flexibility, data storage efficiency was sacrificed. XML was never meant to be a data storage mechanism as much as a data transmission mechanism. It's plain text, supports Unicode, and arbitrarily formed. It was designed to, for example, allow someone in Japan running an AIX system to transmit small Unicode documents in Japanese via the Internet to someone in Canada running a Windows system. It was never envisioned to store more than a few dozen kilobytes of data. As with many things, the real-life usage has far exceeded the design-time intent.

Agreed. There are other ways to persist data, but not many. If not XML and databases are deemed evil, the only alternative that comes to mind is writing a custom file format. That has its own set of pitfalls. (You have to write the interface to those files. The files are proprietary. Unless the data file can be indexed, random access to the data is difficult. If you don't get random access, you get painfully slow, unless the data is typically read sequentially from beginning to end.)

Databases, in general, are designed specifically to store data. You are not required to use the relational data features if you don't need them. Why not use the right tool for the job? How much data are we talking here?

[Psyches]

Originally Posted by kevingy

SQLite is overkill?!? I beg to differ. SQL Server is overkill. SQLite is, well..., "lite". SQLite is likely smaller and faster than the XML parser being used. Then, when you compare the low signal to noise ratio of data stored in XML, data files converted from XML to SQLite are likely to be tremendously smaller. Databases define the schema once in binary. XML defines the schema continuously in plain text.

I'd be surprised if it's smaller and faster than the XML parser. But even if it is, that's not really the point. Speed of the XML parser isn't an issue - not that I'm aware of...nor is its size. SQLite is overkill in the sense that it supports way more than we need (but you have a point, if you don't need the features, don't use those features). But this is combined with my concern that SQLite would not add significant value now without much development pain. "Adds no visible current value and great migration pain"...this is the category that I place SQLite in today. And regardless, we need an XML parser.

Agreed. There are other ways to persist data, but not many. If not XML and databases are deemed evil, the only alternative that comes to mind is writing a custom file format. That has its own set of pitfalls. (You have to write the interface to those files. The files are proprietary. Unless the data file can be indexed, random access to the data is difficult. If you don't get random access, you get painfully slow, unless the data is typically read sequentially from beginning to end.)

Not evil, just unnecessary today (or more accurately, more pain than the current value-add). Yes, good point about custom file format w/it's own pitfalls. But just as XML has been and is being overused, so are relational databases. They have their own pitfalls too.

Databases, in general, are designed specifically to store data. You are not required to use the relational data features if you don't need them. Why not use the right tool for the job? How much data are we talking here?

The vast majority of the data is charts that need to remain as files, or would need to be stored in blobs (or worse, broken into chunks and stored in varbinary or encoded varchar as I've seen some do). And big parts of OpenCPN would need to change. Beyond chart data, there is a few K to a few Mb of data, normally. Some of this other data, and future data like it, should eventually be stored in a database.

I'm rather aware of what databases are designed for - I've worked inside them and just above them for some decades. I've been fortunate to work on the same relational engine code base as Roger Bamford and other luminaries. So I know how useful databases can be. And right now I think it is not the right time to move OpenCPN to SQLite...maybe someday.

However if someone strongly disagreed and wanted to take the code base and show us how easy and useful it would be to move to SQLite - I'd say "go for it!", and I'll back you 100%. And then we'd have to work out how to merge the code effectively with other work going on.

Mark

[Viking Sailor]

I have to agree with Mark that a database is not needed for the type and amount of data we are looking at. Considering the simplicity of the data that needs to be handled there is no need for the added complexity that SQLite will require. All we are taking about here is a few thousand data files that apply to specific geographical locations. This is not much different than storing the complete set of NOAA charts. As with charts a simple directory structure would be all that is required to store the GPX data files.

Paul

[hackoon]

I disagree, opencpn has already performance problems. I run at 600Mhz centrino for navigation under linux. tides, currents and gribs make opencpn very slow.
A long track gives it the rest. To implement datasharing it would require quite a lot of data indexed by its position. You are right that opencpn would not gain much in it current form but without a common database it will grow big, ugly and slow.

[Psyches]

Originally Posted by hackoon

I disagree, opencpn has already performance problems. I run at 600Mhz centrino for navigation under linux. tides, currents and gribs make opencpn very slow.
A long track gives it the rest.

Hackoon, you've already been a valuable contributor to these forums, so I take a keen interest in what you have to say. You seem pretty convinced that you know exactly where the problems are. That's good. As I said in my very first reply, please show us quantifiable data and convince me and everyone else. A good gprof run should do the trick.

You also seem convinced that you know exactly what the best solution is. That's good too. But first, I want to be convinced where the problems are. Still, if you want to really take the bull by the horns, as I said in my last post, feel free to prototype a solution, to prove to all that whatever problems you're seeing are solved by your suggested solution. It would say a lot to have clearly identified a problem with quantifiable data, and then show a solution that solves that problem.

To implement datasharing it would require quite a lot of data indexed by its position. You are right that opencpn would not gain much in it current form but without a common database it will grow big, ugly and slow.

I think you do foresee a real potential problem, one we'll want to be careful to address one way or another in a timely manner - not too late, and not too early.

Mark

[sredna]

OpenCPN currently stores ALL your marks, routes and track data in memory, so there is lots of room for improvements. I believe using sqlite for data storage would be very efficient, it will be much faster than the current data lookup for these datas and probably tide/current data as well, and possibly more. The memory footprint is not scary, it is a few hundred kb, depending on how it is loaded. It can run embedded in mobile phone apps, so it can also run within openCPN!