GDA: Transactions (was Re: Managing large files)

[Phil Longstaff]

Andrew Sackville-West wrote:
> On Sun, Mar 09, 2008 at 06:35:01PM -0400, Phil Longstaff wrote:
>> Derek Atkins wrote:
>>
>>> GnuCash itself wont use an RDBMS as a real DB, but rather it'll just
>>> use it as a data store, just like any other data store.  IOW, GnuCash
>>> will still want to load all the records into memory every time.  This
>>> is just the way GnuCash/QOF currently works, so comparing it to a "real
>>> DB App" just isn't a fair comparison because, frankly, GnuCash ISNT
>>> a "real DB App".
>> Which brings me to the next topic.  Currently, I'm trying to have the
>> GDA backend load only what is needed on startup, and then load
>> transactions and splits only when they're needed (when a register is
>> opened).  There are a number of issues which might or might not be
>> easily solvable:
>>
>> 1) In the account tree, all balances are 0, even though I try to
>> calculate the balance, cleared balance and reconciled balance and set
>> them in the account using g_object_set().
>> 2) When a register is opened, the running balance is always 0.
>> 3) I haven't tried yet, but I would imagine reports will be wrong
>> because they won't do what is required to force transactions or splits
>> to be loaded.
>>
>> My proposed (temporary?) solution is to modify the gda backend to load
>> all transactions and splits at startup time.  Since all data will be
>> loaded, I will also (temporarily?) disable query processing so that qof
>> queries will operate on in-memory data.
> 
> This mimics what currently happens with the xml file, so I don't think
> that's a problem, though I was looking forward to not having to wait
> through my load times...
> 
> Does that mean that there would essentially be two copies of the
> books? one in memory and one on disk with the disk version being
> updated in sync with the memory version? If that doesn't happen pretty

Yes, that is what it would mean.  When an object (transaction, split,
account, ...) in the books is created or modified, the backend is called
to commit the change.  The gda backend ignores the commit notice, but
the gda backend writes the change to the db.

> quickly then we run into the same problem we currently have with lost
> txns in unsaved books. It also allows the introduction of subtle
> errors in that those two copies could drift apart leading to an
> inconsistent state. 

It does happen quickly.

> Personally, I like the idea of using the db backend as a real db, with
> every action being committed to the db on the fly and storing the data
> on disk except for the currently used queries. At some point, it
> will become a case of needing more devs focusing on getting gda up to
> par with the current implementation. I assume we want to eventually
> migrate over to all gda all the time. I *personally* would be okay
> with a released version with a gda backend that works for basic
> functionality but doesn't support all the bells and whistles (like
> account balances in the account tree) in the hopes of getting more of
> us working on making it feature-complete relative to the xml
> backend. It seems to me that the sorts of far-reaching changes
> required shouldn't be done in a separate branch at that point, but I
> don't know. 

It probably involves quite a few changes, some of which could be hidden
at the expense of some performance.  For example, currently, the only
way that transactions and splits are loaded with the gda backend is via
a query when a register is opened.  The backend grabs all splits for
that register, then gets a list of the parent transactions, then all
splits for those transactions.  This will mean that the specific
account's split list is complete, and other accounts' split lists may
have a few splits on them.  In the register, this query is run over and
over again (possibly a bug in the register?).  We could modify the
xaccAccountGetSplitList() call to automatically run the split query for
the account so that this call would always return the correct list.  In
the future, a real db app, rather than getting the full split list and
scanning through it, might ask for the sum of all amounts for splits in
a certain account between certain dates (and the db backend can do this
work and return a single value).

I don't know what is the optimum development path to get from where
gnucash is to a full db app.  QOF does allow queries to be created and
executed, but I believe those queries only return lists of objects and
can't include functions like SUM (I don't really know).  I also don't
know how much of the QOF query mechanism is exposed to scheme code.
Maybe some of the reports should be re-written so that, for example, the
basic income/expense report could use better qof queries (return the
list of all splits where account = "AccountBeingLookedAt" and tx_date >=
start date and tx_date <= end date) or even better (return SUM(amount)
from all splits where ...).

Phil
of the reports should