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

[Kevin Jacobs]

On Thu, 8 Jan 2004, Federico Di Gregorio wrote:

> another list, amended with marc's comments. if everybody agrees on this
> list i'll go on and write the new paragraph for the PEP document.
> 
> changes:
> 
>   1/ removed TEXT in favor of LONGVARCHAR;

Just call it TEXT -- it really isn't a VARCHAR in most regards.

>   2/ removed MONEY (DECIMAL is the name for fixed point), backends
>      providing currency information will define a sub-type of decimal,
>      right?

I'd keep it for now, even if it is vestigial.  There are a lot of other
backends that honor the difference.

>   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.

> i tought about using three or four levels:
> 
> STRING
> |__VARCHAR
>    |__LONGVARCHAR
> 
> but i really don't see any improvement. the two-level hierarchy is there
> for _backward compatibility_, not for organizing the tree. does this
> make sense?

The more compelling reason for a deeper hierarchy is so that type code
matching can take place at several levels.  i.e., some applications are
satisfied to know that a datum is a string, while others will wish to decode
the type information more deeply, but maybe not to the most derived type. 
e.g., many apps may be satisfied to know that a datum is a DECIMAL type, but
not care enough to check if it is a MONEY type.

> here is the list:
> 
>  STRING
>  |__CHAR
>  |__VARCHAR
>  |__LONGVARCHAR
>  |__TEXT
>  |__WCHAR
>  |__WVARCHAR
>  |__WLONGVARCHAR

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.

>  BINARY
>  |__VARBINARY
>  |__LONGVARBINARY
>  NUMBER
>  |__BIT
>  |__INTEGER
>  |__SMALLINT
>  |__BIGINT
>  |__FLOAT
>  |__DOUBLE
>  |__REAL
>  |__DECIMAL
>  |__COMPLEX
>  |__BOOLEAN
>  DATETIME
>  |__DATE
>  |__TIME
>  |__TIMESTAMP
>  |__INTERVAL
>  |__TZTIME
>  |__TZTIMESTAMP

Recent SQL standards put the TZ at the end, last I checked.

-Kevin

-- 
--
Kevin Jacobs
The OPAL Group - Enterprise Systems Architect
Voice: (440) 871-6725 x 19         E-mail: jacobs at theopalgroup.com
Fax:   (440) 871-6722              WWW:    http://www.theopalgroup.com/