sqlite file format, anyone?

[Linas Vepstas]

On Sat, Jun 21, 2003 at 07:01:42PM -0500, Matthew Vanecek was heard to remark:
> On Sat, 2003-06-21 at 16:34, Derek Atkins wrote:
> > linas at linas.org (Linas Vepstas) writes:
> > > Derek Atkins wrote:
> > > > Updating multiple tables at once just requires transaction support,
> > > > no?  What else besides transaction support do you need?  
> > > 
> > > well, for starters, you have to specify *which* tables to update
> > > at the same time: e.g. transaction and split but not account or price or
> > > commodity.   So, from the generic-object point of view, this counts
> > > as 'additional complexity'.   Also, you need to specify how the 
> > > tables are connected, i.e. you have to match guids: 
> > > 'update gncTrans, gncSplit where trans.guid=split.guid' so again,
> > > some additional complexity. 
> > 
> > Umm... that's just a matter of generating the right SQL string.
> > Nothing special about that.  Certainly nothing that isn't generic,
> > provided there is a clear object<->table mapping.

Well, that's what I thought too, about 3-4 years ago :-)

To write out a gncTransaction, you have to compare the splits in the local
cache to those in the database.  Some are new, some are changed, some are
deleted.  You have to decide which is which, and this is not entirely 
trivial.  Its not just a few dozen lines of code, or even a few hundred.

Doing real, true multi-user adds to the complexity.  Someone may have
changed the transaction the same time you did.  So before you even start
writing to the DB, you have to read from it first, and compare to what you
had.  If what you had is old, you have to rollback the GUI, and tell
the poor user to try again.  Oh, and get the new stuff from the DB. 
(Which may be new, modified and/or deleted splits). 

I suppose we can argue if this complexity is needed. I did it this way
because I wanted every transaction in the DB to *always* balance at
all times.  If you think that its OK to allow breif imbalances, then
you can avoid most of this complexity.  On the other hand, if you allow
imbalances, and the client crashes during a write, then you have to
do a psychological equivalent of fsck on the tables: scrub them for 
imbalances.  This is potentially ugly for large DB's.  

(I like tht analogy: its like a journalluing FS: added complexity in
exchange for eliminating fsck ..)

> You will always, at some point, need to specify what tables to update. 
> It sounds like what you're looking for/talking about is either a
> transaction manager, or a transactional generic database interface (JDBC
> comes to mind).  

I always assumed JDBC is a glorified ODBC for java. Is that not the case?

> This is certainly a difficult thing to do--if that's
> what you want, then an existing library would be better used.  

Like what? Name one.  I don't know what this thing is that you are talking
of.  (There are neat things in propreitary s/w, but nothing terribly cool
thats free.)

> Unless
> you really want the punishment of writing the internal transaction
> manager yourself....

Explain?

> > Besides, I want an embedded solution to replace XML.

Yes, lets not loose sight of that.

> 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.  Simple things like submitting
> queries and parsing results are rather easily coded.
>
> I don't know how any of this works into the QOF discussion, though...

I'm hoping that your version of the backend will fit hand-n-glove with the
qof code, i.e. not make any explicit references to any of the accounting
things (i.e. no mention of splits, accounts, prices, etc), and just work 
at the gncObject level.  

At some layer, you'll have to deal with the multi-user balancing transaction
thing I describe above.   Speaking of balancing, you'll also need to 
implement the balance checkpoints, or copy that code over. (Uhh, can
we talk about 'stored proceedures'?)  But I'm hoping the code to 
convert from object to table and back is 'generic qof code' that can 
work for any object.

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