> On Nov 12, 2014, at 10:12 PM, Sébastien de Menten <sdemen...@gmail.com> wrote: > > On Thursday, November 13, 2014, John Ralls <jra...@ceridwen.us > <mailto:jra...@ceridwen.us>> wrote: > >> On Nov 12, 2014, at 12:08 PM, Sébastien de Menten <sdemen...@gmail.com <>> >> wrote: >> >> On Wednesday, November 12, 2014, John Ralls <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.
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 _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
