[DB-SIG] Experiences with DB-API2.0

[Kevin Jacobs]

Hi Horst;

I've been down the same road you have, and despite some early
disappointments with DB-API, I've had very good results in the long term.
Our solution was to develop an abstraction/compatibility layer over the
various native DB-API drivers.  This allows is to smooth over many of the
problems, without complicating our application code.

 On Thu, 20 Jun 2002, Horst Herb wrote:
> We thought DB-API was a great idea. That is, until we tried....:
> 
> 1.) Connection strings incompatible between the 3 "most popular" Postgres 
> adatpters - easy enough to fix, just a few "if....then..else"
> However, becomes tedious if you have hundreds of connections :-((

We put a connection configuration system in place that could generate the
various connection strings/arguments for the various adapters.  All we
specify is an opaque "DSN name" and the appropriate connection is returned.
That way, all of the compatbility logic is nicely partitioned and can be
updated easily.  Plus, you don't have to look at millions of
"if....then..else" statements everywhere.

> 2.) Returned date formats incompatible despite all of them using mxDateTime

We've not this particular problems with dates (and we use a great deal of
them).  Can you give me an example?

> 3.) Frustrated, we gave up further tests for now.
> 
> Are we doing something wrong or are the specifications that ambiguous that 
> not even these simple issues can remain compatible?
> Am I rightfully disappointed or are changes already on the way?

I hate to say it, but there is a huge amount of (possibly) unnecessary
implementation freedom in DB-API 2.0, and a great deal of variation in the
quality of implementation of DB-API drivers.  After the frustration wears
off, most just put on their engineer's hat and trudge forward.  Here are the
things we thought were important and are built into our DB-API driver
abstraction layer:

  1) String and identifier quoting and normalization.  This is _really_
     important, since every backend does something different.  Some
     case-normalize identifiers to upper-case, some to lower-case, some
     don't need any case-normalization for quoted identifiers.  Once you
     know how to normalize identifiers, you can then implement the wildly
     variable identifier quoting rules.

  2) Generic date and time formatting.  The DB-API mandated conversion
     functions to/from unix "seconds since epoch" is a waste of time.  We
     deal with dates that fall before and after the epoch, so we need to
     supply our own constructors.  (Also, mxDateTime and most DB-API drivers
     ignore time-zone information, which is something that I think _is_ a
     problem with DB-API -- since when is data-loss in a driver acceptable?!.)

  3) Unified exception framework -- you want to be able to catch DB-API
     exceptions in code that does not know which driver will be generating
     them.  Do not underestimate the importance of this!  There are many
     approaches to solving this problem -- we have one, though it is a
     somewhat sticky problem.

  4) SQL dialect translation -- this is not for casual applications, but
     we've implemented an SQL source-to-source translator that can parse
     several SQL dialects and output them to other SQL dialects.  This
     process is far from perfected, but it is extensible and has made a huge
     difference in allowing us to write generic queries.

These are just the issues that come to mind at the moment.  We (i.e., my
company) is preparing much of our framework for an open-source release.  It
is slow going, since we have a lot of code -- some we can release, some we
can't -- and many lawyers to deal with.  Hopefully we'll have something
within the next 6 months to a year.

Good luck,
-Kevin

--
Kevin Jacobs
The OPAL Group - Enterprise Systems Architect
Voice: (216) 986-0710 x 19         E-mail: jacobs@theopalgroup.com
Fax:   (216) 986-0714              WWW:    http://www.theopalgroup.com