Re: [SQLObject] ADO DB-API support?

[Ian B. <ia...@co...>]

Since this is all more DBAPI-related than SQLObject-related, and quoting
is popping up (yet again :) on the DB-SIG list, maybe this discussion
should be moved there.

Of course, a lingering prerequesite for this all is a SQL parser to
handle the ?'s, the existence of which makes quoting hooks more
generally interesting (since placeholders aren't currently portable,
making the quoting portable is less interesting).

On Tue, 2003-06-03 at 04:38, Magnus Lyck=E5 wrote:
> At 02:36 2003-06-03 -0500, Ian Bicking wrote:
> >If you want class X to act differently for two different databases, th=
en
> >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.
>=20
> __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__ =3D> DATE to be able
> to convert from a common format to the backend specific.
>=20
> 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).
>=20
> 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.
>=20
> I'm beginning to feel that maybe we should have a __sql_value__
> rather than __sql_repr__ ... Something like in this example:
>=20
> 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
>=20
> Then execute for PostgreSQL could do something like...
>      ...
>      elif value.__sql_literal__(self) =3D=3D literal.BOOL:
>          return ['FALSE', 'TRUE'][value.__sql_value__()]
>      ...
>=20
> >For instance, lets say datetime defines its own __sqlrepr__ that outpu=
ts
> >a string with an ISO date as its contents.  But now you, not the autho=
r
> >of datetime, finds out Access doesn't like that, so you override quote
> >and do a special check to fix up this specific case.
>=20
> 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.
>=20
> 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.
>=20
> 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.
>=20
> A catalog could perhaps start like this:
>=20
> __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...)?
>=20
>=20
>=20
> --
> Magnus Lycka (It's really Lyckå), ma...@th...
> Thinkware AB, Sweden, www.thinkware.se
> I code Python ~ The Agile Programming Language=20