gnucash, sql, and libre office

[Tommy Trussell]

On Sat, Feb 20, 2016 at 6:25 AM, Kai Truempler <truempler at gmail.com> wrote:

> Thanks. On the macro, it is still available here:
>
> https://github.com/cstim/gnucash/blob/master/contrib/gnuc2ooo.py
>
> However, I don't think it works with newer versions of OO/LO.
>
> On the sql connection:
>
> I got a connection to a gnucash sqlite database with LO 4.4 on MacOS
> 10.11, following the instructions included in the readme files in this
> installer "sqliteodbc-0.9993.dmg" on this page:
>
> http://www.ch-werner.de/sqliteodbc/
>
>
I tinkered with it this afternoon and had some success using the packages
available for Ubuntu. In fact, all you have to do is install the packages
[unixodbc unixodbc-bin libsqliteodbc] then use the ODBCManageDataSourcesQ4
tool as a regular user to register the GnuCash sqlite3 file to ODBC, and
it's completely visible in LibreOffice Base.

The trick is the libsqliteodbc package installs the odbc driver AND
registers it in the system. You don't need to adjust anything; just use the
ODBC tool to make the database file visible.

And now comes the embarrassing part: I'm stuck at "now what"?
>
> Knut's gnuc2ooo macro was nicely designed to get a database that makes
> answering certain questions easy and those were the same questions I have
> (e.g. doing monthly reports).
>
> The sql database in gnucash uses a different schema. I really don't know
> much sql and certainly not enough to get a nice data view that I could use
> to work with in calc. For example gnuc2ooo came up with a database that you
> were able to filter by account level. I have no idea how to extract that
> information from the gnucash sqlite file.
>

Yes the tables are confusing and not joined usefully, and certainly not
presented in a way one might hope for.

At the moment I'm wondering if it would be easier to strip the OO parts
> from the original gnuc2ooo macro and just run it as a python script
> directly. Any thoughts on that?
>

If you dig through the script, it looks like it's parsing items from the
XML file and inserting the data into OpenOffice Org tables (note the SQL
statements). You might be able to re-engineer it as some sort of standalone
Python script but I think in that context it won't have much to offer. I
think if you went that route you'd do better fixing it so it works again in
the current LibreOffice and GnuCash. But I haven't dug into it myself.

Yesterday Wm described procedures for getting at the GnuCash database as a
Windows user, you found a procedure that works for Macintosh, and I found a
procedure that works in Ubuntu. At that point I think we are at the same
spot, looking at the tables as GnuCash saves them to a database store.

I believe the "now what" part... do a few searches for Wm and SQL on the
GnuCash User list, as a year or more back he posted several "tutorial"
messages on using SQL with GnuCash. I think we can probably use the same or
similar statements to set up queries in LibreOffice Base.

For example:

https://lists.gnucash.org/pipermail/gnucash-user/2015-January/057654.html