python GnuCash interface to SQL backend

John Ralls jralls at ceridwen.us
Wed Nov 12 20:31:08 EST 2014


> On Nov 12, 2014, at 12:08 PM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
> On Wednesday, November 12, 2014, John Ralls <jralls at ceridwen.us <mailto:jralls at ceridwen.us>> wrote:
> 
> > On Nov 11, 2014, at 1:10 PM, Sébastien de Menten <sdementen at gmail.com <javascript:;>> wrote:
> > ....
> >
> > I would be genuinely interested to have more specific documentation on the
> > risks of going the SQL way.
> 
> There's nothing wrong with reading the database to generate reports. That is indeed easier for many people via SQL query than writing custom report plugins in Scheme.
> 
> It may also be easier to go with the SQL than with the std python binding for reporting but also to change the GnuCash book.

It might be, but I doubt it. You won’t be able to implement the business logic in SQL alone.

>  
> The risk of writing to the database outside of GnuCash, whether in SQL or XML, is that unless you are very careful and have a deep understanding of how GnuCash works that you will irretrievably corrupt your accounting data. There is no business logic encoded in the SQL database, so your code must replicate the GnuCsah engine code to ensure that all required fields are computed and stored correctly. Much of GnuCash is neither straightforward nor obvious and some critical data are stored outside of the primary tables, usually to preserve backward compatibility with previous versions.
> 
> Regards,
> John Ralls
> 
> 
> 
> I have mainly used the basic objects from GnuCash required for basic personal finance (so no invoice, no budget, ...) and did not found any issues while handling lot of accounts/transactions/splits and stock prices. I had the impression that GnuCash does indeed calculations when the book is opened but that it does not save them in the SQL backend. Hence, if we access the book when it's not opened by GnuCash at the same time, risks are quite reduced, would this be a "wrong" impression ?
> 
> Where could I find detailed documentation on the GnuCash engine (and the constrains/invariants GnuCash enforces) ? Or would there be some code/program to check a GnuCash file is "sane/consistent” ?

The developer documentation is in the sources in doxygen format; the current master documentation is compiled nightly and served at http://code.gnucash.org/docs/HEAD/ <http://code.gnucash.org/docs/HEAD/>.

GnuCash has a check and repair facility built in, most of which is run at file load. It does some sanity checking but won’t necessarily correct every possible error that an external program could make.

I’m not sure I follow you about calculations when the book is opened that aren’t saved. With the SQL backend, everything is written back to the database when a change is committed, so the *results* of the calculations are immediately saved. What GnuCash doesn’t do is *read* the database after the initial load, nor does it use any database concurrency control, so there are two potential ways to screw things up with two programs (even two instances of GnuCash) using the same database: A change made by one instance could be overwritten by a change made in the other or, much worse, the two instances could try writing the same records at the same time, corrupting those records.

Regards,
John Ralls



More information about the gnucash-devel mailing list