Re: [Bug-gnubg] Database interface

[Joern Thyssen]

On Tue, May 25, 2004 at 01:12:56PM +0100, Jon Kinsey wrote

> I've just had a quick look at the new sql database code and have
> managed to get it working under windows.  I've used the public domain
> database engine sqlite, which implements a subset of SQL92.  The main
> thing used that wasn't covered was schemas, so I've changed my code
> here to not use "gnubg." in places.

Maybe we should introduce a variable

schema = "gnubg."

and use it throughout database.py.
> 
> I'm not sure about using python to access the database.  It wasn't a
> problem as there is a pysqlite module, I just don't think it's
> necessary.  To put it another way, it adds a dependency that isn't
> strictly required.  I imagine the code could easily be changed so that
> the database access is in the C layer and the python code changed to
> use this.  Is there any other reason to use python for this?

One of the problems is that each database has it's own C interface, so
we would have a lot of #ifdef MYSQL, #idef POSTGRESQL, #ifdef ACCESS etc
etc. It requires a lot of programming to support a new database. 

Python has a documented DB API, so the changes should be relatively
small from database to database. It's almost trivial to add support for
a new database, in the sense that it doesn't require changes to the C
code (e.g., new builds). The current code support all databases which
has a Python DB API v2 just by doing simple changes to the python code.

An alternative way, is to use DB precompilers available for Postgresql
and embed sql like:

EXEC SQL SELECT blah FROM blahblah WHERE whatever IS NOT NULL;

EXEC SQL INSERT INTO blah VALUES ( 'bla');

Running the precompiler changes this into a postgresql-specific C API
call. The advantage is that we only need one source, one drawback is
that we need a different built for each database and a serious drawback
is that a DB precompiler is AFAIK only available for postgresql, oracle,
and IBM DB2...

Jørn