[DB-SIG] Type code mappings: expanding the type objects

[M.-A. Lemburg]

Kevin Jacobs wrote:
> On Thu, 8 Jan 2004, M.-A. Lemburg wrote:
> 
>>Kevin Jacobs wrote:
>>
>>>On Thu, 8 Jan 2004, Federico Di Gregorio wrote:
>>>
>>>> 3/ BOOLEAN is under NUMBER (this will probably give some problems to
>>>>    postgresql and other db using 't' and 'f' but there is really no
>>>>    simple solution and Python *do* use 1 and 0 for True and False (at
>>>>    least untill 2.3)
>>>
>>>SQL 92 and 99 are very clear that booleans and integers are not
>>>interchangeable.  The Python semantics should not be the driving factor
>>>here.
>>
>>The question here is what you want to do with the inheritence
>>information. Most (if not all) database modules return booleans/bits
>>as 1/0 or True/False, so NUMBER would make sense if you're interested
>>in what the application will see.
>>
>>SQL would make them a subclass of STRING.
> 
> 
> I've in the process of moving to a different city, so I don't have my SQL99
> and SQL200x drafts handy, 

Here's a useful URL:

	http://www.wiscorp.com/SQLStandards.html

> but SQL seems to be moving rapidly in the
> direction of having boolean types distinct from both STRING and NUMBER. 
> e.g., the "official" boolean literals are TRUE and FALSE, though various
> backends implement varying degrees of backward compatibility with other
> representations like 't' and 'f' for PostgreSQL.  However, use of BOOLEAN
> columns can only increase as more database vendors are enhancing their
> products to be more standards compliant.

In that case, perhaps BOOLEAN should be at top-level ?!

>>>I'm somewhat sceptical about the WCHAR versions of STRING types.  Python has
>>>native support for unicode strings, so why maintain this artificial
>>>distinction?  The better solution would be to augment the column description
>>>format to include character encoding information.
>>
>>Unicode and strings *are* different in Python and also handled
>>differently in databases, so the distinction makes a lot of sense,
>>e.g. strings are often subject to an encoding specified by the
>>database, while Unicode does not have these deficiencies.
> 
> I agree, so long as we add language that stipulates that CHAR and VARCHAR
> are always returned as undecoded str types with encoding information
> available (unless we go farther and add support for string output encodings
> too). 

Sounds good.

> Several "unicode-enabled" drivers, if I remember correctly, will
> return unicode strings automagically if the default data encoding is not
> ASCII.

mxODBC let's you set a flag to one of these values:
* always return 8-bit strings (in the database encoding)
* allow the database to decide whether or not to send Unicode
* always return Unicode (with 8-bit strings decoded according
   to an encoding you set on the connection; unfortunately,
   there's no easy way to read the database default encoding)

>>Add fields to .description is problematic. Applications tend
>>to use tuple unpacking to access the tuples in that list and
>>adding fields would break this.
>>
>>In general, using tuples for things can may be extended in
>>the future is a bad idea. For DB API 3.0 we should probably
>>switch to a list of ColumnDescription objects instead.
> 
> In the mean time, maybe we should support an extended_description
> dictionary?  A list seems unnecessarily -- well... -- linear.

Another possibility would be to add a method which returns
whatever information you need depending on the parameters
(this is how ODBC works most of the time and they happily
add new query parameter constants in each new revision).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jan 06 2004)
 >>> 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 ! ::::