Relational database?

[alensiljak]

Is a relational database easier to work with for reporting?

Technically, it would be possible to use an in-memory sqlite database, populated with the data that the parser produces. The text file would be the source and the database only a temporary store.
A temp database file could be used if too much memory is consumed by the in-memory option.

Resources:

• https://www.sqlite.org/fasterthanfs.html

[alensiljak]

The db file might serve as cache for REPL mode.

[rcaputo]

Intriguing idea. A ready-made data store with a flexible query language would be nicer than writing one specific for the project.

If the plan is to leverage SQL syntax, then whether SQLite will work might depend on its math precision.

If a parser is written first, it can be used to populate and test SQLite and other options.

[alensiljak]

Interesting point. Some reference material on the topic:
https://www.sqlite.org/floatingpoint.html

[dantuck]

I actually didn't think of using any database for this. I know rust has many key-value pair libraries which might be beneficial but I want to see the rational for using a database before complicating the parsing and increasing time to render. What I really like about ledger-cli is it's speed and I think we should strive to beat it if possible.

[alensiljak]

What a coincidence, I just ran into such a situation! See here

[alensiljak]

I assume the implication is that reading the data from a database is slower than reading and parsing the text files? This is probably something that would be worth an experiment on its own.
Going back to @rcaputo's question, obviously there are two approaches with the database:
a) using just the SQL syntax for the queries, and
b) parsing the records into an internal structure / model
Perhaps these could also be mixed, depending on the report.

[alensiljak]

What a coincidence, I just ran into such a situation! See here

Funny enough, Tacklr has exactly this situation in the examples.

[alensiljak]

It seems that hledger has such integration already implemented and it helps immensely with more complicated reports. Just became aware of this from Simon's answer about specific transfers from account A to account B.
I see no way to get from Ledger such a seemingly simple query result.

https://ledger-rs.github.io/cookbook/transactions-reports.html#specific-transfers

[alensiljak]

It is interesting to see the Google Chrome team deprecating the WebSQL and transitioning to SQLite WASM.
That really makes me wonder how a relational storage compares. Some queries are definitely easier in a relational database.

[alensiljak]

https://www.sqlite.org/fasterthanfs.html

"35% Faster Than The Filesystem"