Setting up gnucash to use postgesql back end - close but no cigar ...

[John Ralls]

> On Mar 11, 2015, at 3:54 PM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
> Some feedback from my experience developing piecash
> <https://github.com/sdementen/piecash> - python package to access/change a
> gnucash book (using any SQL backend) independently of gnucash itself, ie
> not going through the gnucash engine:
> - the current data model is a data dump and therefore not a perfectly
> clean DB data model (re normalisation, integrity constrain)
> - however, it was possible to write an ORM layer that "correct" this data
> model issue and present gnucash objects in a more OO way (see piecash)
> - nevertheless, the point about the "business logic in gnucash" is not
> related to the pure data representation but more about the rules to keep
> consistent data or to generate new data. For instance, if you create a SX
> object in the gnucash DB, nothing in the data model/DB can trigger the
> creation of the transactions themselves at the proper time and in the
> proper form (implement this logic in the postgres would be a nightmare i
> guess). You need to implement this "business logic" somewhere. piecash
> implements part of this logic too (but far from complete)
> - now, if one only wants to read data, this can indeed already be done
> today either by accessing directly the SQL data via pgadmin/sqliteman, or
> doing raw SQL queries (WM gave examples some weeks ago on the ML) or using
> something like piecash or the official python bindings from gnucash
> 
> I do not see the multi-user challenge as requiring per se a redesign of the
> data model. In fact, today, you can already work in gnucash on a book and
> at the same time modify this same book with piecash BUT you must
>   a) be sure no one write at the same time at the DB (which is ensured by
> piecash taking a full lock on the DB for the sqlite backend) and
>   b) be sure to reread the data from gnucash when modified externally (by
> piecash) by doing a "File -> Revert"
> 
> To move towards a multi-user system, there is a need for a finer locking
> mechanism (not lock the whole DB when some user is writing to it) as well
> as ensuring that the Gnucash GUI is properly "refreshed" when there is a
> change in the DB ... which could mean important changes to the Gnucash GUI
> or even engine (today, the whole data is kept in memory and then dumped to
> SQL after each change ... to refresh the Gnucash in memory representation,
> we need to use the the "File -> Revert" trick).
> 
> Gnucash core devs, if my prose is pure non-sense/disinformation, do not
> hesitate to shoot :-)

Not pure nonsense, but we can't support writing to the database with piecash because it doesn't use the GnuCash API and doesn't implement most of the integrity checks or features of that API.

I'll also point out that manual synchronization like that is not dependable, but I agree that the key to multi-user access is in fine-grained locking provided by the database engine.

Regards,
John Ralls