Re: [SQLObject] ADO DB-API support?

[Magnus <ma...@th...>]

At 02:36 2003-06-03 -0500, Ian Bicking wrote:
>If you want class X to act differently for two different databases, then
>you either need something more than __sqlrepr__ (like __sqlrepr_pg__,
>__sqlrepr_oracle__), or maybe __sqlrepr__ gets called with the driver
>name, or you implement your own thing in the quote function.

__sql_type__ or __sql_literal__ would need to be combined with a
unified way of representing the date in question. Then it's up
to the db driver that supports __sql_literal__ => DATE to be able
to convert from a common format to the backend specific.

I suggest that we simply catalog literal formats beyond the ones
described in the SQL standard. Obviously BOOL is needed for instance.
But remember: We don't need to keep track of every *TYPE*, only every
kind of *LITERAL*. There are a whole bunch of numeric types for
instance, but only two types of numeric literals, EXACT (e.g. -0.2)
and APPROXIMATE (e.g. 31.4159E-1).

If as a certain kind of literal is only useful for one particular
driver/backend, we might as well use RAW, but as soon as more than
one driver/backend supports a literal, we should have a standard
way of describing that literal.

I'm beginning to feel that maybe we should have a __sql_value__
rather than __sql_repr__ ... Something like in this example:

class Bool(int):
     def __new__(cls, val):
         return int.__new__(cls, val and 1 or 0)
     def __sql_literal__(self):
         return db.literal.BOOL
     def __sql_value__(self):
         return self

Then execute for PostgreSQL could do something like...
     ...
     elif value.__sql_literal__(self) == literal.BOOL:
         return ['FALSE', 'TRUE'][value.__sql_value__()]
     ...

>For instance, lets say datetime defines its own __sqlrepr__ that outputs
>a string with an ISO date as its contents.  But now you, not the author
>of datetime, finds out Access doesn't like that, so you override quote
>and do a special check to fix up this specific case.

In a case like that, you can always use RAW. This means that your class
will have to give different results on calls to __sql_literal__ and
__sql_repr__ on different backends, but that's doable, isn't it? I
guess passing in the dbmodule or it's name might make in easier, but
I think this should be an exceptional thing. For the exceptional
cases you could build explicit SQL strings.

I don't want it to become a norm that the applications contain a lot
if backend specific conversion code. After all, that's what parameter
passing should handle for us.

What I want to provide is a method to inform the db interface how it
should handle an object, when this object is not a type or class that
it knows.

A catalog could perhaps start like this:

__sql_literal__  __sql_value__
RAW              string, not to be quoted or escaped.
CHAR             string
NCHAR            unicode string
NUMBER           float, long or int
BOOL             1 (True) or 0 (False)
DATE             mx.DateTime.DateTime (or the new datetime)
TIMESTAMP        mx.DateTime.DateTime
TIME             mx.DateTime.DateTimeDelta
INTERVAL         mx.DateTime.DateTimeDelta
BIT              string containing only [01]
HEX              string containg only [0-9A-Fa-f]
NULL             None
BLOB             binary string or stream object (StringIO, File...)?



--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language