[DB-SIG] Controlling return types for DB APIs

[Federico Di Gregorio]

Il giorno mar, 17/04/2007 alle 10.35 -0700, Robert Brewer ha scritto:
[...]
> This is where Geniusql is headed. I'm not for a moment saying the DBAPI
> should go that far, but there needs to be a clear understanding of
> exactly how far the DBAPI is going to go down this rabbit hole (because
> however far you go, your user base will forever pester you for the next
> level of flexibility ;).

Rewriting the SQL is, imho, not in the scope of the DBAPI. When I talk
about casting types from SQL to Python and back I mean exactly that, not
some (probably very useful) way to automatically generate SQL to patch
the shortcomings of the backend/previous programmer.

> 
> > Let's suppose that a driver "knows" the type of a DB
> > column, then we can ask it for an abstract "dbtype":
> > 
> > dbtype = connection_object.getdbtype("SELECT 1 AS foo")
> > 
> > where the query _must_ return a scalar from which the driver 
> > infers the type. Then the type can be used as a key in the
> > registry.
> 
> Given the extremely small number of datatypes that each commercial
> database exposes, this seems to be both more work and less accurate
> results than simply modeling each concrete dbtype directly. All
> SQL92-compliant types can be fully described with a handful of
> attributes (bytes, precision, scale, whether each of those is
> user-specifiable, and if so the maximum allowed value for each, whether
> a numeric type is signed or unsigned, and finally the CHAR vs VARCHAR
> distinction). [1] 

Yes, but there should be a way to obtain the "type" from the backend.
Especially for backends like PostgreSQL that allow for user defined
types of any complexity.

> There should be some provision for custom converters (which forces you
> to stick the converters on each column object instead of in a >   registry,
> since there can be several different converters for e.g.
> datetime.date-to-CHAR).
>
> But even if you decide not to go that far, the registry of default
> converters will need to be keyed by (pytype, backend-specific dbtype).
> For example, Postgres has a hard time comparing FLOAT4 and FLOAT8 [2],
> not to mention that the concrete precision of SQL92 REAL and DOUBLE are
> "implementation defined". It's not entirely hopeless; some base classes
> for converters can be constructed [3].

You're talking about two different things here. I am not interested at
all at solving at the Python level problems that inerently live at the
backend level, like the FLOAT4/FLOAT8 problem.

Imho all that the DBAPI need is a way to specify type-casting functions
(at global, connection and maybe column level) that allow the programmer
to obtain exactly the Python type they need, given a backend type.

federico

-- 
Federico Di Gregorio                         http://people.initd.org/fog
Debian GNU/Linux Developer                                fog at debian.org
INIT.D Developer                                           fog at initd.org
 - Ma cos'ha il tuo pesce rosso, l'orchite?
 - Si, ha un occhio solo, la voce roca e mangia gli altri pesci.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Questa =?ISO-8859-1?Q?=E8?= una parte del messaggio
	firmata digitalmente
Url : http://mail.python.org/pipermail/db-sig/attachments/20070417/4244ba3a/attachment-0001.pgp