sqlite file format, anyone?

[Matthew Vanecek]

On Sat, 2003-06-21 at 21:07, Derek Atkins wrote:
> 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"?
> 

To find out, for instance, if a table named "gncVersion" exists in the
current database.  They're most useful for front ends such as pgaccess,
but do come in handy in other situations (e.g., checking if your apps
tables exist in the current database prior to trying to access them).

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

Not sure I understand your question.  The above is pretty much the model
that Postgresql enforces.  Are you saying to have the QOF thing control
that?  If you pass in Query objects to QOF, then I would think QOF
should control the transactions.  I don't know enough about what Linas
is doing to present an educated opinion, though.  However, the above
model is pretty much what the existing PG back end does.

I would think, if you're passing in an update query, you'd want to pass
it in as a single query.  That provides simplicity to the front
end--transaction control is pushed to the QOF, and the front end only
has to worry about formatting a coherent query object.

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

It's just a referential integrity thing ingrained in most database
programmers...

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

Any application registers by executing the LISTEN SQL command on a given
"name".  An updating application would execute the 'NOTIFY "name"' SQL. 
The listener receives the event via the PQconsumeInput()/PQnotifies()
method.  A robust application would probably have a separate thread
waiting for events using the select() or pselect() (man 2) function. 
That way events could be continuously received and appropriate signals
dispatched, etc.

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

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