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

[M.-A. Lemburg]

Federico Di Gregorio wrote:
> Il mar, 2004-01-06 alle 11:51, M.-A. Lemburg ha scritto:
> [snip]
> 
>>plus a few more exotic ones that are ODBC cruft. The WCHAR ones
>>are Unicode string columns.
>>
>>Most of these are SQL92 names for the types, perhaps we should
>>stick to them ?!
> 
> 
> i think sticking to SQL92 names would be the right thing. here is a
> reorganized tree. note that i don't have an SQL reference at hand so
> some of the type names can be wrong (just copied marc's).
> 
> STRING
> |__CHAR
> |__VARCHAR
> |__LONGVARCHAR
> |__TEXT
> |__WCHAR
> |__WVARCHAR
> |__WLONGVARCHAR
> BINARY
> |__VARBINARY
> |__LONGVARBINARY
> NUMBER
> |__BIT
> |__INTEGER
> |__SMALLINT
> |__BIGINT
> |__FLOAT
> |__DOUBLE
> |__REAL
> |__DECIMAL
> |__MONEY
> |__COMPLEX
> |__BOOLEAN
> DATETIME
> |__DATE
> |__TIME
> |__TIMESTAMP
> |__INTERVAL
> |__TZTIME
> |__TZTIMESTAMP
> ROWID
> 
> some open questions:
> 
>   1/ CHAR is under STRING or under NUMBER?

STRING

>   2/ there are real differences between VARCHAR and LONGVARCHAR?
>      (i never found a db that has this difference and we don't want
>      to include types just because they are defined in SQL92 right?)

The maximum length of the string makes the difference.
VARCHAR is often restricted to about 4000 characters,
while LONGVARCHAR extends to 2GB of character data.

>   3/ same for VARBINARY and LONGVARBINARY

Dito.

>   4/ are MONEY and DECIMAL the same thing? (both are fixed-point, 
>      right?)

Not sure. AFAIK, MONEY is a MS (or maybe Sybase) invention.
It is not defined in SQL92. I suppose that MONEY also carries
currency information ?!

>   5/ in spite of SQL92 i renamed WCHAR_XXX -> WXXX, the other name
>      is terrible :)

True.

>   6/ where do we put BOOLEAN for compatibility under NUMBER; but
>      IMHO it should have a top-level place in its own.

I'd put it under NUMBER. ODBC doesn't have boolean, just
BIT and that's grouped under numbers as well (even though
SQL92 puts it in the same bin as strings).

>   7/ is TEXT just LONGVARCHAR?

Yes. Dito for BLOB and LONGVARBINARY. Some databases don't make
a difference between binary and text data, others do and complain.

-- 
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 ! ::::