[GNC-dev] GUI SQL Reports

[flywire]

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> 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.
>