[gnue] r7674 - trunk/gnue-common/doc/technotes

[johannes]

Author: johannes
Date: 2005-07-02 11:57:58 -0500 (Sat, 02 Jul 2005)
New Revision: 7674

Added:
   trunk/gnue-common/doc/technotes/00016.txt
Log:
Added technote about db-drivers


Added: trunk/gnue-common/doc/technotes/00016.txt
===================================================================
--- trunk/gnue-common/doc/technotes/00016.txt   2005-07-02 16:51:57 UTC (rev 
7673)
+++ trunk/gnue-common/doc/technotes/00016.txt   2005-07-02 16:57:58 UTC (rev 
7674)
@@ -0,0 +1,175 @@
+Title:   db-drivers and how they interact with GNUe
+Status:  Current 
+Created: 2005-07-02
+Revised: 2005-07-02
+
+
+This technote describes how backend driver map to 'our' datatypes, how they
+treat dates and times.
+
+
+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
+-----------
+
+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     No        No
+datetime    mx.DateTime          No        No
+boolean     int
+
+
+pygresql:
+---------
+
+Column      Datatype             F-Read    F-Write
+------------------------------------------------------------------------------
+date        string                         
+time        string               No        2 digits [*2]
+datetime    string               No        2 digits [*1]
+boolean     bool
+
+[*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
+
+[*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        mx.DateTime                    
+time        mx.DateTimeDelta     No        0000
+datetime    mx.DateTime          No        0000
+boolean     int
+
+
+MySQLdb:
+--------
+
+Column      Datatype             F-Read    F-Write
+------------------------------------------------------------------------------
+date        datetime.date                  
+time        datetime.timedelta   No        No
+datetime    datetime.datetime    No        No
+boolean     int
+
+
+sapdb:
+------
+
+Column      Datatype             F-Read    F-Write
+------------------------------------------------------------------------------
+date        datetime.date                  
+time        datetime.timedelta   No        No
+datetime    datetime.datetime    6 digits  000000
+boolean     bool
+
+
+sqlite:
+-------
+
+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