sqlite file format, anyone?

[Derek Atkins]

linas at linas.org (Linas Vepstas) writes:

> FWIW, generic object support might (or might not) require ability 
> to handle meta-queries, e.g. 'what are the names of all the tables
> in this db' or 'what are the names and types of all the fields in
> this table', which odbc supports, and it doesn't look like libdbi does.
> (this can be worked around, at least in postgres, which allows things
> like 'select * from pg_tables;')

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.

Besides, libdbi just provides a generic query execution interface; we 
still have to build the tables and queries ourselves.

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

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

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

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

> > But yea,
> > renaming the functions would probably be best, even if it means more
> > work keeping everything in sync.  Or you can just write a PERL script
> > that does the renaming.  That might be easier.
> 
> OK.  Should the prefix be 'qof' or 'gnc'?

I would say qof..  You shouldn't use gnc.

> I propose using the same capitalization and dash-underscore conventions
> as gnome does.  Or do you prefer something different?
> 
> Also, its not to late to chage the 'qof' name if you are interested.

I don't have a strong opinion on it.  The only issue is that the more
you diverge, the harder it will be to keep in sync.

> --linas

-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