[PYTHON DB-SIG] DbiDate in DBAPI

[Bertil Reinhammar]

!!!
The DBAPI DbiDate definition have caused us a couple of problems.

0) There seem to be different definitions of the meaning of type DATE
   between Oracle and Informix. Possibly also for others.
   Oracle supplies DATE with year-month-day-hour-minute-second.
   Informix supplies DATE as days since 18991231 and
	DATETIME with year-month-day-hour-minute-second-fraction.
	and allows for any contigous subset of that.
   Oracle DATE and Informix DATETIME YEAR TO SECOND is the ANSI standard.
   Informix also have INTERVAL (also ANSI).
1) There is no distinction between DATE and DATETIME.
   Yes, this is an Informix thing but we use Informix, we use Oracle,
   we are likely to start using others and we need to write generic code.
   If other databases have their peculiarities, maybe we can define some
   general method to make the distinctions.
3) There is no way to indicate the column format for a DATETIME.
   Assume that I wish to INSERT a record with a DATETIME and I have
   a dbiDate object with some time info. I then have two options:
   1) Form a string representation of a complete INSERT statement
      and execute. The date info must then be extracted from the
      dbiDate object and transformed into suitable syntax for the
      column at hand. If there is a change in the database schema,
      I have to find all places in my code to make a change.
   2) Tweak the implementation to retrieve the column definition
      from database system tables and form a correct insert value (and
      possibly coercing the time info to fit).

   We have encountered cases where the database is defined by the customer
   and we have been forced to write quite ugly code to work around this
   lack of information and control.
   
   Also, if I wish to write some generic code handling DATETIMES, I'm
   stuck since all I have is time since unix epoch and can't make any
   distinction between the case YEAR TO DAY and the case YEAR TO SECOND and
   the time  happens to be exactly midnight a certain day. In the former case,
   there is little meaning to allow code to add 3 hours and 34 minutes which is
   quite valid in the latter.

3) There is no way to denote a fraction of a second.
   Ok, one could live without it but its defined by ANSI (for INTERVAL) and
   there will surely pop up needs for it so why not try to support it.
   
Yes, I understand that much of this was considered as part of the basis for the
definition as it is. I just feel the definition inadequate and would very
much like to see it improved instead of just supplying extensions for
the Informix module (contradicting DBAPI def) or worse, supply another 
module with its own api. (We have one.)

So that's my observations. How may we improve ?
I can see two ways:
1) Extend dbiDate to take constructor arguments defining type (DATE vs
   DATETIME) and span (YEAR TO SECOND et al). For symmetry we need a
   type attribute and a span attribute to allow us to see how the
   column is defined.
   On top of this we may create wrappers to our liking.
2) Define a new class with fullblown utilities. (Hmm, I remember some
   discussion on a datetime module but that didn't deal with database
   needs or ...)

Extending dbiDate as indicated should be simple enough.
Defining a new class may lead us to eternal discussions before we settle
what we want in it.

---BeRe
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Bertil Reinhammar		IV DocEye AB (Combitech)
phn. +46 13 200606		Teknikringen 9
fax. +46 13 214897		S-58330 Linköping
bertil_reinhammar@ivab.se	Sweden

_______________
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
_______________