Wanted: Navigational Database

[ron.dunn]

I want a database that has both SQL and navigational support. By "navigational" I mean functions like get row, next row, previous row, etc; as well as SQL query capability.

It needs to support Linux (Intel) and Raspberry Pi, as well as (obviously) Free Pascal. Commercial offerings are fine.

ElevateDb looked like a great fit but they're completely unresponsive to all attempts at contact, so I think they must have fallen by the wayside. Actian's Zen Core might have worked, but they don't support Pi.

Any other ideas?

[TRon]

Single or multi user ?

First thing that pops to mind is simply sqlite.

[ron.dunn]

Sqlite will be my fallback position if I can't get what I want, but it lacks the navigational capability I'm after.

I do need multi-user, but sqlite is fine with the expected workload.

[TRon]

Sqlite will be my fallback position if I can't get what I want, but it lacks the navigational capability I'm after.

I have no idea what you are talking about. A query descents from a dataset which has provision for that kind of functionality.

[ron.dunn]

You're thinking about how the dataset shows up in your code, I'm thinking about its impact on the DBMS.

A relational implementation is about sets, navigational is about individual rows. https://en.wikipedia.org/wiki/Navigational_database

There are a few databases which combine both, two of which I referenced in my first post. I happen to have a workload that could benefit from this approach.

[TRon]

You're thinking about how the dataset shows up in your code, I'm thinking about its impact on the DBMS.

Indeed I was/am. My apologies.

In case it was not clear already from my answer, I am not familiar with the concept (though I understand what I've read from your link). I am mostly familiar with relational databases.

So in that regards and unfortunately, I can't add anything else that might be useful.

[Thaddy]

Have you considered Sqlite in combination with a TBufDataset?
TBufDataset can provide the navigational part.
One word of warning: Navigational access to a large database tables can soon become very slow.

In My view this should be an ideal combination of the two you need provided the navigational part uses filters to fetch its data or the database tables are not very large, say under 1000 records.

[ron.dunn]

Thank you for the suggestion, it's how current solutions to the problem work, but I'm trying to do something better.

The scenario is a very large number of records with multiple pathways between them.

In a navigational scenario I can position a record pointer anywhere in that table, step through rows by one path (eg date), then switch to another path (eg customer).

In a relational scenario I'd need to consider the data volume, construct LIMIT/OFFSET clauses, and repeat the query/fetch for each change in path. A solution based on sqlite3 or duckdb is where I'll go if I can't find a viable alternative.

[Thaddy]

Mind my warning about navigational access. For a local single user database it usually does not matter too much.

[ginoo]

Have you considered Sqlite in combination with a TBufDataset?

Hi Thaddy,
 what do you mean by sqlite in combination with TBufDataset? Can you give an example?