python GnuCash interface to SQL backend

[John Ralls]

> On Nov 12, 2014, at 10:12 PM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
> On Thursday, November 13, 2014, John Ralls <jralls at ceridwen.us <mailto:jralls at ceridwen.us>> wrote:
> 
>> 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 <>> 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.  


What’s your goal here? I don’t think that reimplementing GnuCash in Python with GnuCash’s SQL schema is a particularly good approach: It’s not exactly the most efficient design. Rather, it’s designed to mirror the XML schema. You’ll have a better design if you relegate GnuCash SQL to import/export.

An aside about the name: Pyscash is likely to be pronounced by English speakers with a short “i” sound where the y goes.  That conveys a rather unfortunate meaning.

Regards,
John Ralls