sqlite file format, anyone?

[Matthew Vanecek]

On Sat, 2003-06-21 at 16:34, Derek Atkins wrote:
> linas at linas.org (Linas Vepstas) writes:
> 
> Well, I'm not so worried about generic object support per se.  At
> least not COMPLETELY generic..  I'm willing to leave it within the
> "GnuCash SQL framework", whatever that means.  We could easily define
> a "gnc_tables" table to get a list of "table names" and "version
> numbers" of all GnuCash tables.
> 

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.

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

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).  This is certainly a difficult thing to do--if that's
what you want, then an existing library would be better used.  Unless
you really want the punishment of writing the internal transaction
manager yourself....

> > It might be enough to assume that for generic objects, all chaining
> > together is done with guids.  Also, kvp presents some additonal
> > complexity.
> 
> Yes, my plan was to chain by guid.  And yes, kvps are "problematic"
> in terms of databases.  Not sure exactly what to do about that --
> kvps are exactly the wrong idea for SQL based storage systems. :(
> 

Indeed they are.  My plan was to use the GUID as the FK in the various
KVP tables.  The only drawback with that approach is there's no way to
go backwards (i.e., refer back to *whatever* table from the KVP table
based on the GUID).

> > > Multi-user
> > > updates is an issue because of how GnuCash caches data in the engine
> > > instead of relying on the backend all the time.  If GnuCash was
> > > just a SQL app and didn't cache data locally then we wouldn't have
> > > this problem, either -- every "refresh" would pull fresh data
> > > from the database.   Admittedly, I don't know how to do 'events'
> > > generically in a SQL application.
> > 
> > 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?  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.
> 

You just call PQnotify() in Postgresql.  Any registered listener will
receive the notification the next time it queries for events.  It's not
automatic--you actually have to query for any notifies.

> > For the sql backend, I added a version number so that every 'refresh'
> > does *not* go to the db;  it only goes to the db if it received an
> > event and if the version number changed.  Its not hard per se, its
> > just that many more KLOC's to write and debug.
> > 
> > Again, that's why the sql backend got so large ... 
> 
> Well, this is an argument to have an RPC-like interface to the backend
> instead of going directly to a database.  However, that requires
> writing (and running) a "GnuCash server" for multiuser access.  It was
> the route I was going down with the RPC Backend, but I don't know if
> that's something people would be willing to use. 
> 
> Besides, I want an embedded solution to replace XML.
> 

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.

My only objection to libdbi, really, is that it won't let you have the
embedded mysql.  If I work it right, however, Derek ought to be able to
write the mysql code and just tack it on to the back of the SQL
backend--replacing the PG or libdbi code.

I don't know how any of this works into the QOF discussion, though...

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