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

[Sébastien de Menten]

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 :-)

kr

sebastien

On Wed, Mar 11, 2015 at 4:33 AM, Plutocrat <plutocrat at gmail.com> wrote:

> Jan Steinman wrote on Wednesday, 11 March, 2015 01:04 AM:
>
>> I*really*  want an inter-operable SQL back-end. I can almost taste it.
>>
>
> Just a thought in case you weren't aware ... even the SQLite backend is
> accessible from a GUI, or by the command line. So if its just a case of
> wanting to run your own commands using SQL, then you could do that.
>
> Here's one for example: http://sqlitebrowser.org/
>
> P.
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>