[Fwd: Re: [DB-SIG] Abstraction layer confusion, ULA (was Re: Database Abstraction in Python)]

[Jonathan Franz]

> Hi All,
> 
> In developing PDBC (the web.database module) I decided there were 5 
> levels of database abstraction so perhaps we can agree on naming them 
> like this:
> 
> Driver
> DB-API
> User Level API
> Portability Layer
> Object Relational Mapper

I'd imagine these inverted, just so that the highest-level 
abstraction/utility is at the top, and lowest at the bottom :)

DB-API is a driver specification, so I would think it should be more 
like this:

ORM
Portability Layer
ULA
Driver (DBAPI)

> The driver is whatever libraries are used to connect to the database at 
> the lowest level. The DB-API 2.0 wraps the driver functionality to allow 
> low level database access for full control via SQL (perhaps with some 
> sensible type conversions). 

Almost, dbapi is the interface a driver must provide, not a wrapper.
(though some dbapi modules may be wrappers of existing drivers, that is 
an implementation choice, not a requirement.)


> The user level API wraps up the DB-API 
> functionality into an easier to use form but doesn't abstract the 
> database functionality, only the API. The portability layer provides 
> database abstraction in both SQL and type conversions so that code works 
> identically on all supported databases. The object relational mapper 
> maps Python objects to SQL relationships so that no SQL is necessarily 
> needed.
> 
> To the best of my knowledge PDO, PyDAL, adodb are what I would describe 
> as user level APIs that is they simply wrap the DB-API methods in a more 
> convenient and standard form.

adodb does a little bit more - like standard ways to limit the # of 
results ("SELECT TOP n" on MSSQL, "SELECT ... LIMIT n" on MySQL and 
others) - but it is far from a true portability layer.

PDO will hopefully, eventually (if we get round trip typing) support 
editable result sets (would require the results come from a single 
table, of course), but the SQL generation capabilities needed to do such 
a thing are very simple compared to a true portability layer.  I have 
experimental versions of this working - but I had to hack together a 
type-inspection system that is nasty and unreliable: Types that aren't 
defined in the spec aren't used when determining the type of a column 
for updates and inserts.

> SQLObject is an Object Relational Mapper. 
> The only portability layer I know of is my own. That is, an API that 
> behaves EXACTLY the same with EXACTLY the same SQL commands on all 
> supported databases by making a compromise between features and 
> portability (regardless of whether we agree this is actually the best 
> approach!)

It is a neat approach, but you'll need JOINs, subselects, LIMIT and 
other pieces to really make it useful for the majority of people - 
otherwise the portability layer will fall into the trap of being too 
limited in functionality to be popular.

> It sounds like what is needed then is a DB-API with slightly less 
> flexibility left to the implementor which takes into account any changes 
> like the adoption of the datetime module and perhaps support for a 
> reduced number of parameter types, tighter use of connection parameters 
> etc. 

Some sort of limited schema inspection might be useful as well - Right 
now, for example, the primary key has to be passed to the constructor 
for an editable resultset in xPDO (The experimental version mentioned 
above).  This may not be an easy feature for driver maintainers to 
implement though...

Easier to implement but still useful: how about dbapi drivers expose the 
SQL inspection commands for the current connection as properties?
(I would just hard code these, but for modules such as mxODBC, the 
inspection commands change based upon the ODBC driver used).

Such as:
Connection.listTables
Connection.listDBs
Connection.inspectTable

(names subject to change)

> Are we also agreeing we need a user level API, perhaps similar to 
> PDO to be included as part of the standard library?? 

+1

> If so, would we 
> also include the database drivers themselves to provide database 
> functionality out of the box? 

I'm of two minds on this one:
A) It is a good idea, and works with the batteries-included philosophy.
B) Including 3rd party drivers in the stdlib would be hairy from a 
licensing standpoint, as well as a stdlib-bloat standpoint.  Google for 
the debate on including SQLLite in the stdlib.


> Is there no general desire to work on a 
> portability layer or object relational mapper to be included in the 
> standard library?

I think there may be desire.  Is there enough to get something into the 
stdlib?  Unknown.