[DB-SIG] Controlling return types for DB APIs

[Carsten Haese]

On Sat, 2007-04-21 at 13:33 -0400, Michael Bayer wrote:
> On Apr 21, 2007, at 12:18 PM, Carsten Haese wrote:
> > Essentially, the proposed input translation could change to
> >
> > if hasattr(in_param, "ToDB"):
> >     in_param = in_param.ToDB()
> >
> 
> OK, duck typing is much better and more analgous to JDBC's usage of  
> an interface.   this solves the module-importing issue, but not  
> necessarily the "different db's might require different ToDB()  
> implementations" problem - it still binds my application-level value  
> objects to an assumption about their storage...and if my application  
> suddenly had to support two different databases, or even to persist  
> the same collections of objects in both of those DBs (there are  
> definitely apps that do this), now my program design has to create  
> copies of values to handle the discrepancy.   the same issue exists  
> for an application value that is stored in multiple places within the  
> same database, but in different ways; such as a Date type that is  
> stored both in some legacy table with a particuilar string-format  
> style of storage and some newer table with a decimal-based storage  
> format (or a different string format).
> 
> a behind-the-scenes registry of converters mapped to my application's  
> types solves the multiple-databases problem, and bind/column-mapped  
> converters solve the multiple-tables problem.
> 
> the non-class-bound approach, using registered converters, looks like:
> 
> converter = cursor.type_mappings.get(type(in_param), None)
> if converter is not None:
>      in_param = converter.ToDB(in_param)
> 
> that removes all interface responsibilities from in_param's class.

Okay, here we have reached the heart of the matter: Persisting an
application object in a database requires cooperation between the object
and the database. Either the object needs to know about the database, or
the database needs to know about the object.

The former can be done by the object having a ToDB method that is given
information about how the database that it'll be stored in, and react
appropriately. The latter can be done in the way you propose, using an
inbound typemap.

I'll concede that using an inbound typemap has a beautiful symmetry to
using an outbound typemap, and it's way less kludgy than making the
object aware of every single database that might want to store it.
However, the adapter lookup needs to be done in a way that doesn't
suddenly fail if the application object is subclassed! Doing this is
just a bit more involved:

for tp in type(in_param).__mro__:
  converter = cursor.input_typemap.get(tp, None)
  if converter is not None: break
if converter is not None:
  in_param = converter(in_param)

Note that it's enough if the converter is simply any callable object
that returns the converted object.

> > To handle this [column specific mapping], the cursor could grow a coltypemap attribute, which  
> > is a
> > mapping of typemaps, keyed on the column number or, maybe more
> > conveniently, column name.
> 
> probably both.

Yeah, I actually meant both :)

> >
> > In summary, I am open to making the following revisions:
> > * The SQLData class would become optional or be eliminated. Inbound  
> > type
> > conversions between Python objects and the database will be  
> > performed by
> > a well-defined ToDB method that the object may implement regardless of
> > its inheritance tree. If an inbound Python object doesn't define a  
> > ToDB
> > method, it'll be mapped by the canonical mapping for the particular
> > database.
> 
> yeah thats more or less what i was saying above.

In the meantime you've made me see the light that the SQLData base class
and the ToDB interface can be completely eliminated if we use an inbound
typemap for handling the translation from the application to the
database.

In light of this development, I propose the following changes to my
proposal:
* The SQLData class and the ToDB interface will be eliminated.
* The typemap attribute will be renamed to output_typemap.
* An analogous input_typemap will be added.

> > * The outbound conversion call will receive additional parameters,  
> > such
> > as the cursor.description tuple, that will allow the adapter to  
> > make the
> > resulting object stateful with respect to all of its database type
> > properties.
> 
> its possible that cursor.description doesnt have all the information  
> we need; such as, a string column that represents dates, and we need  
> to decide what string format is represented in the column.

And who or what, other than the programmer who can handle the situation
with a column-specific typemap, *would* have all the information that's
needed in that case?

> > * Add an optional coltypemap attribute to the cursor for defining a
> > column-specific typemap.
> 
> yeah, just having various maps of typing information to me seems to  
> represent the one method that is of general use for all cases.

I'm glad we're beginning to agree. Maybe down this road, consensus can
be found.

-Carsten