[DB-SIG] RE: column type casting

[Federico Di Gregorio]

sorry for being so late in the answer...

Scavenging the mail folder uncovered brian zimmer's letter:

> We need to be able to specify handling for input and output.  

agreed, but... i was talking about adapting the db api type, not about
rewriting it from scratch. this is way i addressed the problem the way
i did. note also that i am from keepend the two directions (db->python
and python->db) quite separated, because this way is easies to cope
with different dbs.

[snip]
> I presume this is a typo?  Should it not read:
> 
>  c.register_type(PNGIMAGE, 1)
> 
> since the icon is second in the select?

yes, it is a typo.

> > it is a little bit more clear now? note that the default STRING, DATETIME,
> > NUMBER, etc. objects are created by the module at init time but are
> > exactly the same and are registered *only* with the module, not with
> > cursoer or connections. you can override them a module level or by
> > registering your custom objects with the connections and cursors.
> >
> 
> I like the idea of a single callable object as the converter so you can easily mix and match for datatypes, but it needs to go both
> ways.  I'm also partial to my syntax:
> 
> ic.execute("SELECT name, icon FROM icons_table WHERE name LIKE '%.png'", bindings={1, PNGIMAGE})

i *don't* like the idea of a single callable object. in the python-to-db
conversion, the current objects (Date, Binary) work quite well. we only
need to formalize what are the default, required types and what are left
to the implementation.

about your syntax, it has the advantage of being valid for just 1 execute()
call. it is a nice addition to the proposted api and should work as
.register_type() / .execute() / .unregister_type() or something similar.

> so the state can be maintained just during the call to execute.  If you wish to keep it for all calls just pull the dictionary out
> and then include it with each call.

i'd like to keep the register_type() stuff anyway.

> > this confusion derives from the fact that you interpreted my type object
> > as two-ways. they are one-way only, they only convert from the db to
> > python. the old Date, Binary, etc, are still good to convert from python
> > to the db. to make it clearer, BINARY (all uppercase) converts from the
> > db to python and produce a python buffer, Binary convert from python to
> > the db and what it produces is application dependent.
> >
> 
> Even if it's application dependent, why not have a consistent mechanism for converting?

but *we have* a consistent mechanism for converting from python to db, 
it is explained in the db api... you just need to add more types and
document them as an extension (String, Date, Time and Binary are really
basic, maybe we can discuss about adding some more default types, or are
the the common base already?)

[snip]
> I would also like to add support for cursor lifecycles.  This is especially useful for me because the JDBC drivers for each vendor
> offer a number of very convenient methods that simplify work later, such as auto-incremented columns.  Does this hold true for other
> technologies such as ODBC or native libraries?

can you expand i little bit on that, i don't know what a "cursor lifecycle"
is...

Scavenging the mail folder uncovered Dittmar, Daniel's letter:
> 
> > I like the idea of a single callable object as the converter 
> > so you can easily mix and match for datatypes, but it needs to go both
> > ways.  
> 
> How can it be a callable object if it goes both ways? Wouldn't it need two
> methods for db => python and python => db? I know that Python objects can
> have metthods in addition to being callable. But when there are symmetrical
> behaviours, it doesn't make sense to make of them the 'call'.

this is another reason to keep the two directions separated, they
are not perfectly symmetrical.

> > > > A module scope registration will only cause problems (thread-wise
> > > > and design-wise too).
> 
> I agree with Marc there. If someone wants to mess with module variables,
> he/she should do it in his own module.

this one was just to keep the type singletons uniform. remember that in
my propostal, the STRING, ROWID, etc. module variables are just pre-registered
types.

> > > the module scope registration is there to allow you to override the
> > > default type casting object: STRING, DATETIME, etc... so we have:
> 
> Understood, but not agreed

ok ;)

> > > module.register_type(TYPE)  -  convert based on type_code
> > > connection.register_type(TYPE)  -  convert based on the type_code
> > > cursor.register_type(TYPE, column)  -  convert based on column
> 
> Wouldn't it be clearer for the cursor to have separate methods to register
> by type and to register by pos?

maybe yes. this was just a proof of concept. method names can be changed
until we all agree on them.

> How should a cursor behave when there is a new execute? 
> - forget conversion information (fall back to connection default, fall back
> to default at cursor creation time)
> - keep conversion by type

conversion by type should be keeped, maybe even conversion by position,
because you want to set the converion stuff once and then to many queries
inside a loop, right?

> Another idea: conversions that span multiple columns
> - DATE + TIME combined into a timestamp
> - mime type in STRING + BLOB 

hey, this is cool! stuff for dbapi 3.0, i think... :)

> I think that this is too complex to be put into the standard, but how about
> a generic cursor wrapper which shows how to write these kinds of filters?

would be nice. at now i am just interested in wrapping up the standard
to have better drivers, though. btw, how is the conversion to the new
text format going? i am waiting for applying the first set of patches i
sent to this list (no, i won't try to patch with types until we settle
the argument, obviously.)

ciao,
federico

-- 
Federico Di Gregorio
MIXAD LIVE Chief of Research & Technology              fog@mixadlive.com
Debian GNU/Linux Developer & Italian Press Contact        fog@debian.org
                             Best friends are often failed lovers. -- Me