[Zope-DB] Z SQL equivelant of MySQL's SQL_CALC_FOUND_ROWS?

[Charlie Clark]

On 2005-12-06 at 10:48:29 [+0100], Chris Withers <chris at simplistix.co.uk> 
wrote:
> Charlie Clark wrote:
> >>>>I wonder how the two methods compare efficiency-wise?
> >>
> >>Yes, well, we're talking about MySQL specifically ;-)
> > 
> > Well, with reference to Zope we shouldn't be...
> 
> Huh? the original poster was talking about MySQL, why should he care
> about any other rdb?

Well, he was talking about using ZSQL and cited by MySQL and MS SQL 
statements. ie., he was interested in a generic approach. At least that's 
how I read it.

> >>Not so. Think of batches:
> >>
> >>"Now showing Results 5 - 10 of 25"
> >>
> >>But why not just return all the rows and only show 5 of them?
> >>
> >>"Now showing Results 5 - 10 of 250000"
> > 
> > Yes, let's talk about batches - ZSQL doesn't implement batching so it
> > collects the whole set of results available so len() is available.
> 
> How would you implement batching in a way that's agnostic of the rdb used?

Currently I do this with a script but the LCD solution would be to use a 
LIMITs based on parameters passed in. Essentially, however, the RDBMS has 
to support the feature and the Python DB API should be cover it. Having had 
to work with both MySQL and SQLite recently the compromises required to 
support "lesser" systems can be really annoying.
 
> > Result sets aren't supported directly
> 
> by what?

By ZopeDA's and ZSQL
 
> > so unless the DA provides a method to
> > query on the connection or cursor you will also have to run a separate 
> > query
> > with count().
> 
> *sigh*

I've not discovered this to be a real problem yet although my result sets 
are rarely more than several thousand items long when ZPTs rendering speed 
becomes the problem. One could imagine RDBMs support in Zope3 being so much 
better if only anyone could be really bothered about it ;-)

Charlie