Re: [SQLObject] ADO DB-API support?

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

On Thu, 2003-05-29 at 11:27, Magnus Lyck=E5 wrote:
> At 22:31 2003-05-28 -0500, Ian Bicking wrote:
> >I think __sqltype__ seems a little awkward.  You have to agree on the
> >types (and type names) that the backend accepts, and that gets into a
> >whole discussion that seems rather endless ;)
>=20
> Most of the time, you only need to tell the DB-API whether it might
> need to escape/quote the data or not. In other words, is this a number
> or a string? If the class above the DB-API is to handle escape/quote,
> it would need to supply "that's" from __sqlrepr__ when it works with i.=
e.
> mxODBC or cx_Oracle, and "'that''s'" when it works with MySQLdb or a
> PostgreSQL driver. If it can just tell the DB-API whether it's a string
> or a number, it can always supply "that's", or "5" or "314159E-5", and
> the driver can handle it just like it does for ints, floats and strings
> etc today. This covers the overwhelming majority of used literals.
>=20
> Actually, it's not really the data type in the database that matters,
> but what kind of *literal* we are providing. Maybe the method should
> be called __sqlliteral__ or __sql_literal__ rather than __sqltype__
> (and the other method __sql_repr__ if we use __sql_literal__)?
>=20
> The SQL/92 standard supports four kinds of literals:
>=20
> character string    ('that''s')
> bit string          (B'01010101' or X'f5a1')
> exact numeric       (4 or +365.2425)
> approximate numeric (4E3 or -.15E-15)

The problem is that those are the literals that don't matter -- since
SQL92 defines them, most all SQL databases handle them just fine.  It's
other literals -- like dates -- that are likely to have problems. =20

> In addition to returning any of these, I think __sql_literal__ should
> be able to return 'raw' to inticate that the driver should just pass
> the value in as it is. It's then up to the application programmer to
> provide the right data. If she has to support many tricky backends and
> don't have very high performance requirements, she could put logic into
> the __sql_repr__ method that will check what backend it is and act base=
d
> on that.
>=20
> Obviously, MS Access is in clear violation with the SQL spec here, as i=
n
> a number of other cases :( so *this* would not solve the #1900-01-01#
> problem.
>=20
> I'd like to suggest the following: Each db driver has to supply a
> CONSTANT for each kind of literal it supports. At least, it should
> support the following constants:
> .literal.CHAR            Action for such values is typically
>                           "'%s'" % x.replace("'", "''")
> .literal.BBIT (or BITS?) Action is typically "B'%s'" % x
> .literal.XBIT (or HEX?)  Action is typically "X'%s'" % x
> .literal.EXACT           Action is typically to use as is
> .literal.APPROX          Action is typically to use as is
> .literal.RAW             Action is always to us as is.
>=20
> It could optionally support other literal types such as
> .literal.DATE  which could provide custom handling of date
>                 strings for some peculiar backend,
> .literal.MONEY if this is ever needed...
> .literal.BIN   to let people pass in binary strings and not need to
>                 convert them to B'0101' or X'ffff'. This could then
>                 pass the binary data as it is if the backend supports
>                 that, or do "X'%s'" % x.encode('hex') as a convenience
>                 for the application programmer. I don't know if there i=
s
>                 a desire to be able to provide a stream instead of a st=
ring
>                 for binary data? How do people handle BLOBs today? But
>                 maybe I'm confused here. Backends might not handle bit
>                 fields and BLOBs etc in the same way.

It's only these non-standard ones that seem useful (besides RAW).  Which
is why this seems problematic.

[snip]
> Something like this...
>=20
> # mydate.py
>=20
> def init_module(dbdriver):
>      global sql_literal
>      try: sql_literal =3D dbdriver.literal.DATE
>      except: sql_literal =3D dbdriver.literal.CHAR

That init_module is a killer -- it's the same problem you get with
database exceptions right now, where you can't be database agnostic in
isolated parts of code.  The optional stuff will be a pain too -- maybe
if there's a common DBI module it'd be more workable.

[snip quote function example]
> What if dbdriver is written in c? (I guess you can always have
> a thin wrapper.)

Mmm... no assigning the function in that case, I suppose.  Well, you
could instead have all the drivers use a setQuoter() function, which
could just reassign quote(), or do something else if that's not
possible.  The concept is really just about overriding a quoting
function in some manner.

> >The quote function is potentially database specific.  After thinking
> >about it, this function should have the chance to get at the raw value
> >before __sqlrepr__ is called.
>=20
> I don't see why. If you want class X to behave differently in some
> situation, subclass it and put the different behaviour in __sqlrepr__
> of the subclass. These functions seem redundant to me. One is more
> OO, the other more procedural.

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.

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.

If you want __sqlrepr_oracle__, you could also implement that in the
quote function.  It's a general hook, and one which is tied to the
database, not the objects being quoted.

> >Anyway, people talked serious about including Gadfly in the standard
> >distribution, so there's interest in supporting database stuff.  The
> >only weird part about the module is that it would be useless on its ow=
n.
>=20
> If Gadfly was included and used this module, it would be better. I
> would rather see sqlite in the standard library though. I don't see
> why we can have bsddb and not sqlite. SQLite is much more like a typica=
l
> SQL database than Gadfly is.

Yes, I like SQLite more as well, but it's not so much Gadfly in
particular -- rather, there are people interested in putting database
stuff into the main Python distribution.  So it doesn't seem impossible
that a common DBI module could also be included.

  Ian