sqlite file format, anyone?

[Linas Vepstas]

On Sat, Jun 21, 2003 at 10:07:18PM -0400, Derek Atkins was heard to remark:
> Matthew Vanecek <mevanecek at yahoo.com> writes:
> 
> > I was going to use gncVersion for that...  Linas is referring, however,
> > to system tables vs. Gnucash tables.  Each DBMS is a little
> > different--DB2 puts stuff in SYSIBM.*, Postgresql uses the pg_* tables,
> > etc.  That's what would be difficult about a generic object interface
> > when trying to access system information.
> 
> Ok, call me stupid, but why would one need to access "system tables"?

If you wanted to build a database browser, of course! 
(Not that we need on in gnucash, or in QOF (yet) ...

> > 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)?

> > > > in postgres, you can broadcast an 'event' which can be any string 
> > > > (typically a table name), and anyone 'listening' would get it.
> > > 
> > > Hmm, ok..  How do you set this up?  

see src/backend/postgres/events.c
The listener does a "LISTEN gncPrice;"
The sender does 'NOTIFY gncPrice;'

That code also uses the 'audit trail', and this is important: if one
client has deleted a specific split, one has the potential tricky 
situation that the other side will conclude that its copy of the split 
is "new", and so it adds it right back in.  I avoid this by having
the other client check the audit trail before assuming that it has a handle
to a new, rather than a deleted split.

> > >I dont know if there is a good
> > > way to go this generically.  I don't know if MySQL or SQLite have
> > > events, or if they do I have no clue how to set them up.

They're only needed for multi-user.  The embedded mysql backend is
single-user.

> Ok, what do you register for?  Do you register for a particular row
> in a table?  Or register for a table?  Or register for something else?

If I remember correctly, you can register for any string, although typically
the string is a table name.  The listener must know the string in advance.
You can't really pass any data this way.  You can only make 'event x' happen;
if you want to pass data, you pass it in a table.

> > I'm hoping my version of the PG backend is pluggable enough that you can
> > just plug-n-play the db access code.  The bulk of the work is in the
> > Query decoding and Engine loading.  

I'm going to strongly disagree with this. Unless I misunderstand the
statement. I did the 'obvious generalizations' as m4 macros (maybe not
a good technology choice, but it was worth a shot).  But there's a 
huge amount of code that's not m4 macros.  Its very specific, and its
really needded to make things work correctly.

I initially thought that 95% of the code would be generic m4 macros,
just 'query decoding and engine loading' but that turned out be not 
the case; maybe only 25% is m4 macros.

I vastly underestimated how easy the sql backend would be; the complexity
there was necessity, not frivolity.  I'm concerned that you'll trip
over this too ...  See? you haven't even started, and already I'm saying
'I told you so' ... :-)

> > Simple things like submitting
> > queries and parsing results are rather easily coded.

builder.c is already completely generic I beleive.  Some things like events.c
can probably be chopped up and made generic.

--linas

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