DA weaknesses (was: Re: [Zope-DB] Re: Re: Test for equality to Missing.Value (empty field)?)

M.-A. Lemburg mal@lemburg.com
Fri, 31 Jan 2003 12:27:44 +0100

This has been sitting in my Inbox for a while. I've had a look
on dev.zope.org today and could not find any project related
to the standardization of the Zope DA interfaces.

How about we start a PEP for this ? (I am not familiar with the
Zope dev process, but do know a lot about the Python process
and its PEPs :-)

The PEP should address all the interfaces and specifications
of the data types involved; ideal would be to use as much of the
Python DB-API spec as we can.

Thoughts ?

Marc-Andre Lemburg
CEO eGenix.com Software GmbH
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/

Dieter Maurer wrote:
> M.-A. Lemburg writes:
>  > ....
>  > Some other things which I found while writing the DA:
>  > 
>  > * floats returned from the database are automagically converted
>  >    to integers in the ZODBCDA if the scale is 0 -- I wonder whether
>  >    that's a good idea, because integers can overflow (the mxODBC
>  >    Zope DA uses longs for these cases).
> A long int might be a better approximation than a float in this case.
>   This may break old Zope versions (though that still use Python 1.5.2)
>   because there "str(long_int)" appends 'l' which many applications
>   may not expect.
> I would probably try to convert "scale 0" floats and also
> SQL integer to Python "int" and when this fails I would
> convert to Python "long".
>  > * there's no query interface which allows usage of bound
>  >    parameters (as defined in the DB-API); the result is that
>  >    data in query strings is restricted in size to what the database
>  >    interfaces can handle, e.g. Oracle has a 4k byte for SQL
>  >    literals.
> This is a well known weakness.
> It has probably not been tackled because nobody felt I
> has an excellent idea how to use it via Z SQL Methods.
>  >    I would welcome a standard interface of the form
>  >    .execute(sql, paramtuple, maxrows=None)
> I would like that very much.
>  > * there's no support for providing an offset into the result set;
>  >    this makes browsing large query sets very slow and memory
>  >    intense
> I would like that too - mostly on aesthetical grounds.
> I think, its practical value may be limited: few use cases
> require browsing a result set of several thousand records.
> However, it would make programmatic handling of such
> result sets possible directly in Zope. Nowadays, they
> usually have to be made external to Zope or done in
> an External Method.
>  > * the de-facto "standards" for .tables() and .columns() have
>  >    inconsistent interfaces
> ... and seems to be re-implemented by each DA.
> I am almost sure, there could be some common code whith only
> the base access to the database scheme being DA specific.
>  > Are there any activities to solve these problems ?
> If there is something, it is probably in the form
> of a proposal on <http://dev.zope.org>.
> But, I fear, there are no activate projects on any of these
> issues.
> Dieter