Re: GnuCash page on GO site

[linas linas org (Linas Vepstas)]

On Mon, Mar 01, 2004 at 06:23:26PM +0100, Rodrigo Moya was heard to remark:
> On Mon, 2004-03-01 at 10:06 -0600, Linas Vepstas wrote:
> 
> > On Mon, Mar 01, 2004 at 10:37:03AM +0100, Rodrigo Moya was heard to remark:
> > > >  For the gnucash engine, we implemented
> > > > 'qof' to solve that need.  Actually, qof is older than gnomedb, 
> > > > Derek's re-write of qof is now the 3rd rewrite of the query engine.  
> > > > I don't think libgda even *has* a query engine.  
> > > > 
> > > what do you mean by a 'query engine'?
> > 
> > To generate reports, we need the ability to perform queries
> > over objects.  For example, we need to find all objects of type
> > "split" between a start date and an end date, which belong to 
> > account XYZ;  this query is used to generate the checkbook register.
> > 
> > Another example: we need to find all objects of type "split",
> > for any date, that are not marked reconciled, and belong to 
> > account XYZ.  We use this to populate the reconcile window.
> > 
> > The query engine is *not* SQL, and it doesn't go to a database;
> > rather, it traverses a set of C language objects in system RAM. 
> > There is a hello-world example included with the source code
> > in http://qof.sourceforge.net/ Oddly, that example is not on
> > the website :-(
> > 
> right, that's what GdaSelect does. It does use SQL, but that's just for
> convenience, since all the parsing and selection of data is done on the
> client side, without calling the db server.  That is, you create a
> GdaSelect object, add to it different data models obtained from the data
> source (one for each table, for instance), and then, on the client side,
> you can do SQL queries over the data. All data is in memory, and on the
> client side.

Hah! Well, this is indeed going in the right direction.
The only 'problem' is the langage: we don't have "tables" and 
static data, we have objects with dynamic data and getters and setters.

> We need a better support for the SQL parsing code in GdaSelect though,
> but that shouldn't be too hard. Thus, we could have a special syntax in
> that SQL to allow for callback functions to be called, for instance, so

Why would you need a special syntax?  

"SELECT balance FROM Account WHERE Account.name = "ABC Bank"; 

can trivially mean "Account" is an object class, the select
is an implicit search over all of the class instances, where
the name() and balance() methods are called:

So, in C:

typedef struct { int private_stuff[100]; } Account;
char * AccountGetName (Account *);
int AccountGetBalance (Account *);
GList * ListOfAllAccounts (void);

GList *al;
for (al=ListOfAllAccounts(); al; al=al->next)  {
      Account *a = al->data;
      if (!strcmp (AccountName(a),"ABC Bank")) return AccountGetBalance(a);
}

> that you can do the calculations you talk about. Or even add special
> fields to the data models to allow that. Or, as I said before, do all

Well, I don't see that one needs special fields; rather, one needs
a way of having an object class register itself with libgda.  The
registration process just maps "tables" to "object classes", and
"field names" to "object methods".

Note, however, my example above is over-simplified:  

GList * ListOfAllAccounts (void);

should really be replaced by a binary tree of some sort, and/or a hash
table.   And the "for (al=ListOfAllAccounts(); al; al=al->next)"
should be replaced by a binary/hash search.  And etc. 
 
--------------------------------------------------
Note: one more special requirement, and this one might actually be hard:

The result of calling "AccountGetName()" might (for example) cause a
new account to be added.  Thus, in such a case, the intial search
needs to be aborted and restarted.   There needs to be a way of 
signalling that the search needs to be restarted. 

In real-life, i.e. gnucash today, this might happen because gnucash
has just started, and there are no accounts yet, or maybe only 1 or 2.
A query for a balance may cause gnucash to go to the database and to
pull in additional accounts from there first, before running the 
query over the objects.  

Actually, the way we do this today is to run the sql query on the
backend first, and then run it locally.  So that, for example:

when QOF gets the following:
"SELECT balance FROM Account WHERE Account.name = "ABC Bank"; 

it contacts the "real" sql db (postgres in our case), and does a

"SELECT * FROM Account WHERE Account.name = "ABC Bank"; 

and uses the results from this query to create an instance of 
the C-langague account object.  Only then does it perform the 
query over the C-langauge objects.

---------------------------------------------------------
Note also: because the SQL backend is multi-user, one user
might be creating accounts while another is querying balances.
One thus has traditional "cache coherency" issues.  In gnucash,
we maintain a "cache" of local C Objects for performance, so
that we don't have to issue a new SQL query every time.  We
implement a "cache invalidate" with the postgres SQL NOTIFY
statement. 

--linas

-- 
pub  1024D/01045933 2001-02-01 Linas Vepstas (Labas!) <linas linas org>
PGP Key fingerprint = 8305 2521 6000 0B5E 8984  3F54 64A9 9A82 0104 5933