On Thursday, November 13, 2014, John Ralls <jra...@ceridwen.us> wrote:
> > On Nov 12, 2014, at 12:08 PM, Sébastien de Menten <sdemen...@gmail.com > <javascript:_e(%7B%7D,'cvml','sdemen...@gmail.com');>> wrote: > > On Wednesday, November 12, 2014, John Ralls <jra...@ceridwen.us > <javascript:_e(%7B%7D,'cvml','jra...@ceridwen.us');>> wrote: > >> >> > On Nov 11, 2014, at 1:10 PM, Sébastien de Menten <sdemen...@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. _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
