[DB-SIG] Controlling return types for DB APIs

[Michael Bayer]

On Apr 21, 2007, at 3:09 AM, Carsten Haese wrote:

> Hi All,
>
> I have taken the time to write out my type mapping proposal in a
> slightly more structured form, with some hopefully enlightening  
> examples
> of how this proposal might be useful.
>
> Please see http://www.uniqsys.com/~carsten/typemap.html
>
> Any comments are welcome, and I'll do my best to incorporate
> constructive criticism into future revisions of this proposal.
>

heres some thoughts:

- using a class-level approach, i.e. SQLData, makes it inconvenient  
to establish custom types that are independent of a particular DBAPI,  
since the SQLData class itself like everything else in DBAPI only  
exists within implementations.   its impossible to define the class  
until you've imported some DBAPI.  SQLData's origins in JDBC dont  
have this issue since SQLData is part of the abstract JDBC api and  
classes can be built against it independently of any database driver  
being available.

- because SQLData's state is the data itself, SQLData is not really a  
"type" at all, its a value object which includes its own database  
translation function.  That greatly limits what kinds of types  
SQLData can be realistically used for, and in fact it can only be  
used for datatypes that are explicitly aware that they are being  
stored in a database - and only a specific DBAPI too.

For example, its impossible to use SQLData to directly represent  
Decimal instances or datetime instances; neither of them subclass  
<mydbapi>.SQLData.    If the answer is that we'd just use typemaps  
for those, then what would we use SQLData for ?  I can use a typemap  
for my SpatialData objects just as easily, without my SpatialData  
object being welded to a specific persistence scheme and specific DBAPI.

Also because SQLData is not stateful with regards to its type, its  
not possible for a single SQLData class to represent variants of a  
particular type, such as strings that should be truncated to length  
50 versus strings that are truncted to length 100; youd have to use  
more subclassing.

- per-column typemaps:  here is a common use case.  I am receiving a  
row which contains two BLOB coluimns.  one BLOB is image data  
representing a JPEG image, one BLOB is a pickled instance of a Python  
class.  I would like to register type converters so that the second  
column is run through the pickle.loads() function but not the  
first.   If we are registering various type-handling callables at the  
cursor level, it should be easy enough to add an optional integer  
parameter which will bind that type converter to only a specific  
column position in a result set.  the use case is more obvious in the  
bind parameter direction.