Re: pager

[prev] [thread] [next] [Date index for 2004/10/20]

On 20 Oct 2004 at 12:41, Yuval Kogman wrote:

> On Wed, Oct 20, 2004 at 10:48:45 +0100, David R. Baird wrote:
> Content-Description: Mail message body
> > 
> > Hi,
> > 
> > I'd be grateful for comments from the list on a pager module I've 
> > written (attached to this email). It uses LIMIT so that only the 
> > required subset of results are loaded into memory. I've tried to code 
> > for different dialects, but I only run MySQL so I haven't tested the 
> > others - any pointers would be great. 
> 
> With PG you do it just the same, with limit and offset. 

PostgreSQL and MySQL both use OFFSET and LIMIT, but the MySQL syntax 
is different from PostgreSQL, at least in the older version of MySQL 
I'm using. I'll check up on more recent versions. Seems like the 
Postgre syntax has also changed between versions. 

> You should
> note that ORDER BY should be considered mandatory, though:

Good point. Which also made me see I'd blocked users from specifying 
their own order_by. I've changed it to accept an order_by clause in 
the method call, and if there is none, to use the primary key 
column(s). 

> With Oracle you have to use subqueries. Here is some info:

Subqueries can't be handled by this module, because it only has 
access to the WHERE clause, not the whole SQL statement. 

> Please make it subclassable, so that you have a subclass per driver,
> and then a subclass per method. Drivers are selcted by set_db
> calling __driver, i guess you can get the data some how.
> 
> Then you basically have MySQL and Pg (and maybe others) inherit from
> LimitOffset.pm, and others implementing their own.
> 
> A bit like Class::DBI::Loader...
> 
> It sounds a little overkill to do it this way but it's the most
> flexible to extend, update, and reuse.

No, it's a great idea, I'm working on it. I'll send an updated 
version to the list ASAP. I'm not clear on whether you see this 
working at runtime or compile time? I'm thinking compile time like 
this:

use Class::DBI::Plugin::Pager::mysql;

I'm no CDBI expert, what you say suggests that a CDBI app can have 
several drivers, and these can be hooked up to different databases, 
and the pager should be identifying the database and adapting to it? 

d.



        -- 
        Dr. David R. Baird
Riverside Content Management Systems
http://www.riverside-cms.co.uk