[DB-SIG] Extending DB-API

[M.-A. Lemburg]

Ian Bicking wrote:
> I probably won't have the time to really follow this through, but since 
> there has been a little discussion of this stuff lately I'd like to 
> throw out an idea for where I think Python database standards should go. 
>   This mostly builds on the dbapi rather than extending it directly (I 
> think).

I think that we should add a few of the standard extensions
to the main spec and possibly add some useful extra attributes
that make life easier for people writing wrappers, ORM, etc.
on top of the DB-API module APIs.

Some comments:

> The major things I think we can standardize:
> 
> * There's no common way to configure databases.  I'd like to see a 
> single URI syntax that everyone can use.  This should be modestly 
> extensible via query string parameters.

There has been some discussion about this. I believe that this
is something a wrapper would have to implement since part of the
URI is mostly going to be the name of the database module to use.

Also, I'm not sure whether a URI syntax would be of any benefit,
since the parameters required by various backends are rather
diverse.

ODBC uses something called a data source definition string
which is a simple string format of "<key>=<value>;" pairs.

Only a few of the keys are standardized. Many depend on the
backend being used and often vary between ODBC drivers.

I doubt that you could convert a URI into such a string in
a sensible way. In the end, you'd probably have to use
the query part of the URI to pass in all the parameters
that are non-standard in the URI syntax.

This wouldn't really help anyone, since it's just another
way of writing things, but doesn't make things easier for
the user.

This is different at the application level, since an
application will typically only support a handful of
backends. In this case a simple URI would suffice,
since all the other details would be added to the
connection parameters at lower level of the application,
e.g. in the database abstraction layer.

> * Given a database connection there should be a well-documented strategy 
> for discovering the type of the connection (including the server's 
> version) and loading up code specific to that module and some interface. 

In mxODBC we have these connection attributes:

.dbms_name
	String identifying the database manager system.
.dbms_version
	String identifying the database manager system version.

.driver_name
	String identifying the ODBC driver.
.driver_version
	String identifying the ODBC driver version.

They have proven to be quite useful, esp. when it comes to
coding against specific backends.

>   This allows compatibility code to be developed separately from the 
> database connection modules, and separately from consumers like ORMs. 
> This would also give a clear place to build database introspection 
> tools, without tying that work to the release schedules or development 
> process of the current database modules.  Realistically those existing 
> modules are developed fairly slowly and conservatively, and require 
> skill in things like writing C extensions.  Compatibility layers have 
> none of these qualities.
> 
> * Unified exceptions.  This can be done currently with monkeypatching 
> the inheritance hierarchy, but they aren't unified to anything in 
> particular that you can rely on.

We already have a standard way for this: all exceptions should be
exposed on the connection object as attributes.

This makes writing polymorphic code easy.

> * Figure out a strategy for parameter styles.  Maybe this just means a 
> reasonable way to handle SQL in a more abstract way than as strings with 
> markers (that way no update is required to the dbapi).  Or maybe 
> something more sophisticated.  Or we could even be lazy and use 
> %s/pyformat, which is the only marker that can be easily translated to 
> other markers.

See past discussions: pyformat is probably the worst of all
parameter styles.

Note that any change in this respect will break *a lot* of existing
and working code.

Perhaps we ought to make the parameter style a connection
parameter that's writeable and then agree on a very limited
set of required styles - perhaps just the qmark and the
numeric style since these are easy to implement and can be
mapped to all other styles.

> * Maybe some work on database connection pooling strategies.  Maybe this 
> can just be library code.  I think we need a little more data on the 
> threading restrictions than dbapi gives (sqlite in particular doesn't 
> fit into any of the current levels).

Connection pooling is something which higher level interfaces
have to manage and provide, since database drivers can't
possibly know which connections to pool and in what way to suit
the application needs.

Note that simply placing connections into a dictionary
is not good enough, since each connection keeps state and
thus may not be reusable.

>  From there I see some other useful database standards:
> 
> * A standard transaction container.  The Zope transaction container is a 
> reasonable and straight-forward implementation (though it needs to be 
> better extracted from Zope).

I don't understand this one.

The industry standards for transaction management are
 * X/Open DTP (XA)
 * MS DTC

See http://docs.openlinksw.com/mt/xamt.html for a good
overview of how XA works. The MS DTC is described here:
http://msdn2.microsoft.com/en-US/library/ms191440.aspx

Both interfaces are C level interfaces.

> * A standard way to retrieve database configuration and connections. 
> This way database library/framework code can be written in a reasonably 
> abstract way without worrying about deployment concerns.

Not sure what you mean here.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 16 2006)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::