Title:   db-drivers and how they interact with GNUe
Status:  Current 
Created: 2005-07-02
Revised: 2005-07-02


This technote describes how backend drivers map to 'our' datatypes and how they
treat dates, times and strings.


I) Mapping and handling of datatypes by databases
=================================================

1. PostgreSQL:
--------------

Type            Native type                             Fractional seconds
------------------------------------------------------------------------------
date            DATE
time            TIME WITHOUT TIMEZONE                   6 digits (rounded)
datetime        TIMESTAMP WITHOUT TIMEZONE              6 digits (rounded)
string          VARCHAR
number          SMALLINT, INTEGER, BIGINT, NUMERIC
boolean         BOOLEAN


2. Interbase/Firebird:
----------------------

Type            Native type                             Fractional seconds
------------------------------------------------------------------------------
date            DATE
time            TIME                                    4 digits (error)
datetime        TIMESTAMP                               4 digits (error)
string          VARCHAR (<= 32K), BLOB *1)
number          SMALLINT, INTEGER, NUMERIC
boolean         BOOLEAN (added domain *2)

*1) Character set character size determines the maximum number of characters
    that can fit in 32Kb 
*2) Boolean Domain: CHECK value IN (0,1) OR value IS NULL


3. MySQL:
---------

Type            Native type                             Fractional seconds
------------------------------------------------------------------------------
date            DATE
time            TIME                                    No
datetime        DATETIME                                No
string          VARCHAR (<= 255), TEXT
number          SMALLINT, INT, BIGINT, DECIMAL
boolean         TINYINT (1) UNSIGNED


4. MaxDB (SAP DB):
------------------

Type            Native type                             Fractional seconds
------------------------------------------------------------------------------
date            DATE
time            TIME                                    No
datetime        TIMESTAMP                               6 digits (error)
string          VARCHAR (< 8000 *1), LONG
number          SMALLINT, INTEGER, FIXED
boolean         BOOLEAN

*1) The available length for VARCHAR columns depends on the character set used.
    For ASCII it's 8000, for UNICODE it's 4000


5. SQLite 2/3
-------------

Type            Native type                             Fractional seconds
------------------------------------------------------------------------------
date            DATE
time            TIME                                    Yes, unlimited
datetime        DATETIME                                Yes, unlimited
string          VARCHAR
number          INTEGER, NUMERIC
boolean         INTEGER



II). Datatypes returned by a DBSIG2 cursor
==============================================================================

F-Read : number of fractional digits for seconds on a 'read'
F-Write: number of fractional digits for seconds on a 'write'

psycopg:
--------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        mx.DateTime
time        mx.DateTimeDelta     6-dig *1  No
datetime    mx.DateTime          6-dig *1  No
boolean     int
string      str

[*1]: Fractional part is parsed using sscanf () which means it will fail if the
      decimal character (as defined by the locale) is different from '.'.


pygresql:
---------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        string                         
time        string               No        2 digits [*2]
datetime    string               No        2 digits [*1]
boolean     bool
string      str

[*1] Make sure the decimal separator is set to "." (locale), otherwise writing
     dates, times or datetimes fails with an exception
[*2] Driver throws an exception if a time is given as built by pygresql.Time ()
     (which creates an mx.DateTimeDelta). Instead use a pygresql.Timestamp ()
     value (which is a mx.DateTime) !


pypgsql:
--------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        mx.DateTime                    
time        mx.DateTimeDelta     2 digits  2 digits [*1]
datetime    mx.DateTime          2 digits  2 digits [*1]
boolean     pgBoolean
string      str

[*1] Make sure the decimal separator is set to "." (locale), otherwise writing
     dates, times or datetimes fails with an exception

kinterbasdb:
------------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        datetime.date                  
time        datetime.time        No        0000
datetime    datetime.datetime    No        0000
boolean     int
string      str


MySQLdb:
--------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        datetime.date                  
time        datetime.timedelta   No        No
datetime    datetime.datetime    No        No
boolean     int
string      unicode


sapdb:
------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        datetime.date                  
time        datetime.timedelta   No        No
datetime    datetime.datetime    6 digits  000000
boolean     bool
string      str


sqlite2 (SQLite 2):
-------------------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        mx.DateTime                    
time        mx.DateTimeDelta     2 digits  2 digits truncated
datetime    mx.DateTime          No        2 digits truncated
boolean     int
string      str


sqlite3 (SQLite 3):
-------------------

Column      Datatype             F-Read    F-Write
------------------------------------------------------------------------------
date        datetime.date                  
time        datetime.time        6 digits  6 digits
datetime    datetime.datetime    6 digits  6 digits
boolean     bool
string      unicode