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

[Mike C. Fletcher]

Jonathan Franz wrote:
...

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

PyTable also does minimal rewriting of queries to support different
parameter formats.  Again, though, not a true portability layer, it
assumes you are writing any given piece of SQL against the current
database, it just lets you use the rich pyformat operator everywhere. 
The idea being that the ORM provides the bulk of the code you want to be
cross-database, and the sql queries are for when you want to get
down-and-dirty to get past an impedance mismatch (of course, it being a
limited ORM, it's quite frequent that you wind up needing to do that :) ).

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

PyTable allows for this via its schema objects (generally written in
Python code to allow extra annotations, but they can also be derived
from the internal database structures), but that's part of the ORM from
my point of view (you tell a result record to delete itself/update
itself/refresh itself).

> 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!)

PyTable has a limited ORM as well.

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

In PyTable these are spelled:

    DBDriver.listDatabases( cursor/connection )
    DBDriver.listTables( cursor/connection )
    DBDriver.listIndices( cursor/connection )
    DBDriver.tableStructure( cursor/connection, tableName )

as well as (on PostgreSQL):

    DBDriver.listNamespaces( cursor/connection )
    DBDriver.listNamespaceTables( cursor/connection ) (retrieves all
    tables in non-root namespaces)

I'd suggest making them function calls rather than properties, these are
things you don't want to be hiding from the user if they are going to
take a significant amount of time.

Have fun,
Mike

-- 
________________________________________________
  Mike C. Fletcher
  Designer, VR Plumber, Coder
  http://www.vrplumber.com
  http://blog.vrplumber.com