[GNC-dev] GUI SQL Reports

[john]

> On Sep 24, 2021, at 9:58 PM, flywire <flywire0 at gmail.com> wrote:
> 
> I've looked around at free GUI SQL report writers and haven't come up with an alternative to Base. It's barely acknowledged as part of the suite, had little development, fugly, with suspect reliability, eg formatting on view columns isn't saved. SQL dialects vary a bit but the GnuCash accounts tree in SQL moves it out of basic SQL.
> 
> 1. Any comments about storing foreign keys in the database?
> 2. Any guidance on how to use dates stored as text fields? By default, Base only recognises the characters.
> 
> E:\BOOKS>\sqlite\sqlite3 empty.gnucash
> SQLite version 3.36.0 2021-06-18 18:36:39
> Enter ".help" for usage hints.
> sqlite> .tables
> accounts          customers         lots              splits
> billterms         employees         orders            taxtable_entries
> books             entries           prices            taxtables
> budget_amounts    gnclock           recurrences       transactions
> budgets           invoices          schedxactions     vendors
> commodities       jobs              slots             versions
> sqlite> .schema transactions
> CREATE TABLE transactions(guid text(32) PRIMARY KEY NOT NULL, currency_guid text(32) NOT NULL, num text(2048) NOT NULL, post_date text(19), enter_date text(19), description text(2048));
> CREATE INDEX tx_post_date_index ON transactions(post_date);
> 
> 
> On Thu, Jun 3, 2021 at 10:47 AM John Ralls <jralls at ceridwen.us <mailto:jralls at ceridwen.us>> wrote:
> For playing around: It's a bit of a lash-up but you can use Microsoft Access or Open/LibreOffice Base with SQLite3 via an ODBC driver. Both have QBE, though Microsofts is (as usual) a lot more polished.

What about storing foreign keys? There are lots of them, they're just not marked as such in the table definitions because we don't yet use the database that way.

It's probably that SQLite3 doesn't have a date type, it stores them as a sort of ISO8601 strings with no punctuation. MySQL/MariaDB and Postgresql have proper date types. If Base doesn't have a built-in parser you'll have to write one. ISTR that there are Python and Java bindings for LibreOffice.

Regards,
John Ralls