sqlite file format, anyone?

[Derek Atkins]

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

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

Is this?  I would think we could just:

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;

Isn't that good enough?  I would think that this would be
sufficient, right?

The only thing I don't know is if this needs to be passed as a single
query or multiple queries are "good enough".

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

I'm trying to figure out when we need to go backwards....

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

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?

I know that SQLite doesn't have this.  I don't know if MySQL does.

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

Good.  I'm hoping for this, too.

> My only objection to libdbi, really, is that it won't let you have the
> embedded mysql.  

Well, it would -- we'd just need to build the libdbi backend for
embedded-mysql.  It shouldn't be too hard to do it based on the
existing mysql backend that's already written.

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

That's my hope...  I'm just getting anxious to start in... ;)

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

*shrugs*

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available