python GnuCash interface to SQL backend

[John Ralls]

On Nov 13, 2014, at 9:31 AM, Sébastien de Menten <sdementen at gmail.com> wrote:

> Indeed, it may be worth to explain what are the goals (and the limits).
> 
> I have tried to use the official python bindings and had the following issues:
> - need swig + compilations to make them work => pyscash is pure python and has only sqlalchemy as main dependency (which is rather supported/standard package)
> - python binding is a mapping if C API with a thin layer python friendly layer => I do not find the resulting python scripts very pythonic
> 
> Hence, instead of writing (part of the example to create accounts):
> 
> s = Session("bookname", is_new=False)
> 
> book = s.book
> root = book.get_root_account()
> commod_table = book.get_table()
> CAD = commod_table.lookup('CURRENCY', 'CAD')
> 
> a = Account(book)
> root.append_child(a)
> a.SetName('Assets')
> a.SetType(ACCT_TYPE_ASSET)
> a.SetCommodity(CAD)
> 
> a2 = Account(book)
> a.append_child(a2)
> a2.SetName('Receivables')
> a2.SetType(ACCT_TYPE_RECEIVABLE)
> a2.SetCommodity(CAD)
> 
> a3 = Account(book)
> root.append_child(a3)
> a3.SetName('Income')
> a3.SetType(ACCT_TYPE_INCOME)
> a3.SetCommodity(CAD)
> 
> i can now write (reusing standard SQL Alchemy patterns):
> 
> s = Session("bookname")           # pyscash cannot create a Book from scratch. It relies on gnucash to do this
> 
> # query from the session the Book and the CAD currency
> book = s.query(Book).one()        
> CAD = s.query(Commodity).filter_by(name="CAD").one()
> 
> root = book.root_account
> 
> # possibility to create an account in a pythonic way
> acc = Account(name="Assets", type=ACCT_TYPE_ASSET, commodity=CAD, parent=root_account) 
> 
> # possibility to create accounts directly with subaccounts
> acc_inc = Account(name="Income", type=ACCT_TYPE_ASSET, commodity=CAD,
>                   parent=root,
>                   children=[
>                       Account(name="Income subacc 1", type=ACCT_TYPE_INCOME, commodity=CAD),
>                       Account(name="Income subacc 2", type=ACCT_TYPE_INCOME, commodity=CAD),
>                       Account(name="Income subacc 3", type=ACCT_TYPE_INCOME, commodity=CAD),
> )
> 
> # and then either one of the command
> s.rollback()   # to undo changes
> s.commit()    # to save/commit changes
> 
> So, to sum up, goals of pyscash :
> - easy installation as pure python module (no compilation, no swig, ...)
> - easy to contribute as pure python and based on de facto standard ORM for SQL in python (sql alchemy)
> - pythonic implementation by leveraging SQL Alchemy features (transparent handling of guid, free commit/rollback/transaction support, automatic parent-children relation handling, etc)
> - pythonic interface for CRUD operations on a GnuCash Book
> 
> The last point is important as the goal is not at all to reimplement the whole GnuCash engine but only to be able to manipulate (CRUD operations) the different GnuCash entities (book, account, currency, etc) in an easy pythonic way.
> For instance, I do not plan to reimplement functions like GetBalance, GetReconciledBalance, GetPresentBalanceInCurrency, GetAccountBalance, etc
> 
> Is the goal of the project clearer ?
> Do you see complexities in reimplementing the pure CRUD operations (except the basic complexities of leaving a GnuCash book in a consistent status) ?
> 
> On the name issue, you are definitely right ! I had in mind a pie-ess-cash pronounciation (the S for SQL) as pygnucash was already taken. Any suggestion ? pysacash (python sqlalchemy gnucash interface) ? or is it even worse :-) ?
> 
> kind regards,
> 
> Sebastien
> 
> ps: and thank you for clarifying the announce on the user mailing list ! I should have thought about that myself to avoid confusion…

Sébastien,

Please remember to copy the list on all replies.

The goal of the project is clearer but is in my mind severely misguided. Object persistence is always a side effect of a non-trivial program, and GnuCash is a non-trivial program.

If your complaint about the python bindings is that they’re not pythonic I think that your efforts would be better spent improving that. It should become easier as we progress through the C++ rewrite which will produce a much more object-oriented architecture where that’s appropriate. That said, it’s also worth noting that C++ is a far more versatile language than either C or Python in that it supports generic and functional programming as well as the structured and OO paradigms that Python supports. In moving away from GObject we’ll also be enforcing type safety much more rigorously. Since that’s a notorious weakness (though casual hackers think it’s a feature) of Python, having thin Python wrappers around C++ objects will provide for much safer Python add ons than does the current code base.

It’s also very dangerous to attempt to select what parts of GnuCash’s internal functions to reimplement. To take your example of balances, it’s an integral part of GnuCash’s internal checks to compute and compare balances. Leaving that out greatly increases the risk of introducing corrupt data.

As for the name, how about PieCash? It doesn’t get the SQL element in there, but it avoids mispronunciation. 

Regards,
John Ralls


> 
> 
> On Thu, Nov 13, 2014 at 4:28 PM, John Ralls <jralls at ceridwen.us> wrote:
> 
>> 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> 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
> 
>