> On Sep 24, 2021, at 9:58 PM, flywire <[email protected]> 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 <[email protected] > <mailto:[email protected]>> 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 _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
