python GnuCash interface to SQL backend

[Sébastien de Menten]

On Thursday, November 13, 2014, John Ralls <jralls at ceridwen.us> wrote:

>
> On Nov 12, 2014, at 12:08 PM, Sébastien de Menten <sdementen at gmail.com
> <javascript:_e(%7B%7D,'cvml','sdementen at gmail.com');>> wrote:
>
> On Wednesday, November 12, 2014, John Ralls <jralls at ceridwen.us
> <javascript:_e(%7B%7D,'cvml','jralls at ceridwen.us');>> wrote:
>
>>
>> > On Nov 11, 2014, at 1:10 PM, Sébastien de Menten <sdementen at gmail.com>
>> 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 python interface uses SQLAlchemy (an ORM) only to handle the backend
(retrieve and save objects), all business logic is in the python code. For
instance, when creating a transaction and the related splits, it is the
python code that ensures the business logic (for instance that the sum of
the splits = 0). This is close to what GnuCash does.
Moreover, there are some basic SQL integrity constrains (we cannot remove a
split without removing the related transaction) that are added in the ORM
layer as they do not exist in the SQL backend.

>
>
>>
>> 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 ?
>
> I’m not sure I follow you about calculations when the book is opened that
> aren’t saved.
>
I was thinking about temporary results/cached calculations/etc that are not
saved to the back ends (if there are any).


> 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.
>
> Indeed, if GnuCash has opened the file and is using it (ie there is a lock
in the table gnc_lock), we are almost 100% sure to have the issues you
mentions if we change the file in parallel through SQL.  There is a check
in pyscash that raises an exception in this case (it can be overruled but
at user's own risk.