sqlite file format, anyone?

[Matthew Vanecek]

On Sat, 2003-06-21 at 23:51, Linas Vepstas wrote:
> On Sat, Jun 21, 2003 at 11:35:44PM -0500, Matthew Vanecek was heard to remark:
> > On Sat, 2003-06-21 at 22:54, Linas Vepstas wrote:
> > > On Sat, Jun 21, 2003 at 10:07:18PM -0400, Derek Atkins was heard to remark:
> > > > Matthew Vanecek <mevanecek at yahoo.com> writes:
> > > > 
> > > > > KVP tables.  
> > > 
> > > I'm going to play dumb for a moment.  What's wrong with storing the 
> > > triplet (keypath,value,guid-of-parent) where 'keypath' is the 
> > > slash-delimited list of keys? 
> > > 
> > > > > go backwards (i.e., refer back to *whatever* table from the KVP table
> > > > > based on the GUID).
> > > 
> > > Wouldn't this be solved by (keypath,value,guid-of-parent,type-of-parent)?
> > 
> > Not really, unless you used table-name for type-of-parent.  In
> 
> Yes, table name. Or maybe gncOject->name which should map 1-1 to a table
> name.  Or maybe we add a 'table-name' field to gnc-object?

> > Anyhow, a lot of the queries can be standardized on the SELECT/FROM
> > side--it's mainly the WHERE and ORDER BYs that are truly dynamic.
> 
> If you haven't looked at 'builder.c' then *please* review at least that.
> If you haven't already duplicated this code. 
> 

I've looked at it a bit, already.  It's pretty nifty.  I'm not working
on that portion of the framework yet, but I will keep your concerns in
mind.  Not that I'm ignoring it.  I'm just trying to be pretty organized
with the way I go about this.

One of the main things that needs to change is to migrate to the glib
data types--GString, etc.  Postgresql no longer has a limit on the
length of a query, and Gnucash can put together some pretty long
queries.  I don't feel like re-writing a dynamically sizing string
library, since glib is already there...

I may (have to) end up being entirely dynamic on the SQL
generation--it's just that, for certain things, the queries just won't
change from execution to execution (e.g., loading the accounts into the
engine).  I want as much as possible to be static.

> If you haven't looked at qof yet, then *please* visit qof.sourceforge.net
> and just scan the names of the files in the cvs tree. They are *identical*
> to the gnucash files.  qof consists of gncobject, gncbook and gncsession
> and gncbackend and kvp and date/time. that's it. Only the generic stuff.
> 

I'll take a close look at it this week.  With the way I'm designing the
SQL backend, I don't think it will make too much difference which Query
framework is used.  Plug in QOF, plug in the Engine Query--if I design
it correctly, the differences will be transparent to the rest of the
backend wrt which Query framework is used to build the SQL from.  There
will be other issues to work out (e.g., how do you know which framework
is currently in use?), but nothing that's a major obstacle, I don't
think.

> I am hoping that 90% of what you do, especially in the early stages,
> can be copied directly into qof.  The idea being to have a generic
> sql backend for generic objects.  (There still needs to be a layer that
> knows explicitly about account balances, etc. but that's a different 
> matter).
> 

Yes, I will look.  I'm mainly working on the framework and high-level
stuff, as well as the database access routines.  I've not started with
the middleware, yet.  I consider Query creation and parsing as
middleware.  Thus, that part is still malleable, pretty much.  I have
some routines I wrote that decode a Query and print out what it's
doing--Derek knows about those.  I was hoping to use that in part of the
Query parsing portion.

I'm going for a 3-tier approach, really, or maybe 4-tier.  You've got
the backend->engine interface, the backend->middleware interface, and
the middleware->data access interface.  My thought is that if I keep the
separate functionalities separate and distinct, it becomes much easier
to plug in a different library to provide that functionality.  E.g.,
neither the backend nor the middleware should care what database access
functions the data layer uses--the data layer could use libpq, libdbi,
mysql calls, etc., without affecting the other layers.  I think I said
this somewhere else, but let me reassure you I'm taking the time to make
sure everything will gel together very nicely.


-- 
Matthew Vanecek
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
********************************************************************************
For 93 million miles, there is nothing between the sun and my shadow except me.
I'm always getting in the way of something...