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 _______________________________________________ Commit-gnue mailing list [email protected] http://lists.gnu.org/mailman/listinfo/commit-gnue
