Re: pager

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

--Message-Boundary-2842
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body


I've taken Yuval's suggestion and made the pager easily subclassable. 
An example of a subclass is:

package Class::DBI::Plugin::Pager::LimitOffset;
use base 'Class::DBI::Plugin::Pager';
sub make_limit { "LIMIT $_[2] OFFSET $_[1]" }
1;


Again, any suggestions on additional syntaxes would be great. Current 
version is attached, I'll upload to CPAN as soon as I can write a few 
tests. 

Cheers,

d.


On 20 Oct 2004 at 14:53, David R. Baird wrote:

> 
> 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




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


--Message-Boundary-2842
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'Pager.pm'

package Class::DBI::Plugin::Pager;
use strict;
use warnings;
use Carp;

use Data::Page;
use SQL::Abstract;

use vars '$VERSION';

$VERSION = 0.2;

my $Debug = 0;

sub import {
    my ( $class ) = @_;

    my $caller = caller(); # the CDBI app

    warn "CDBI::P::Pager caller is $caller" if $Debug;

    no strict 'refs';
    *{"$caller\::search_where_paged"}      = \&search_where_paged;
    *{"$caller\::search_where_limitable"}  = \&search_where_limitable;
    *{"$caller\::_make_limit_phrase"}      = $class->can( 'make_limit' );
}

sub make_limit {
    my ( undef, $offset, $rows ) = @_;

    require Class::DBI::Plugin::Pager::LimitOffset;

    return Class::DBI::Plugin::Pager::LimitOffset->make_limit( $offset, $rows );
}

# $where, [$attr], $per_page, [$page]

# or

# where     => $where,
# attr      => $attr,        # optional
# per_page  => $per_page,
# page      => $page         # optional

sub search_where_paged {
    my $class = shift;  # this is the plugged-into-class

    my ( $where, $attr, $per_page, $page );

    if ( ref $_[0] )
    {
        $where    = shift;
        $attr     = shift if ref $_[0];
        $per_page = shift || croak( 'no per_page argument' );
        $page     = shift || 1;
    }
    else
    {
        my %args  = @_;

        $where    = $args{where};
        $attr     = $args{attr};
        $per_page = $args{per_page} || croak( 'no per_page argument' );
        $page     = $args{page}     || 1;
    }

    $attr ||= {};

    my $count = $class->count_search_where( $where );

    my $pager = Data::Page->new( $count, $per_page, $page );

    # make sure the results are sorted by something
    my $order_by = $attr->{order_by} || $attr->{order} || [ $class->primary_columns ];

    my $limit = [ $pager->skipped, $pager->entries_per_page ];

    $attr = { limit    => $limit,
              order_by => $order_by,
              };

    my @results = $class->search_where_limitable( $where, $attr );

    return $pager, @results;
}

# This is CDBI::AbstractSearch::search_where, with extra limitations
sub search_where_limitable {
    my $class = shift;  # this is the plugged-into-class
    my $where = ( ref $_[0] ) ? $_[0] : { @_ };
    my $attr  = ( ref $_[0] ) ? $_[1] : undef;
    my $order = ( $attr ) ? delete( $attr->{order_by} ) : undef;
    my $limit = ( $attr ) ? delete( $attr->{limit} )    : undef;

    if ( $Debug )
    {
        require YAML;
        warn 'limitations: ' . YAML::Dump( $limit );
    }

    # order is deprecated, but still backward compatible
    if ( $attr && exists $attr->{order} )
    {
        $order = delete $attr->{order};
    }

    $class->can( 'retrieve_from_sql' ) or
        croak( "$class should inherit from Class::DBI >= 0.90" );

    my $sql = SQL::Abstract->new( %$attr );

    my ( $phrase, @bind ) = $sql->where( $where, $order );

    $phrase =~ s/^\s*WHERE\s*//i;

    if ( $limit )
    {
        my ( $offset, $rows, $limit_phrase );

        if ( my $input = ref $limit )
        {
            if ( $input eq 'ARRAY' )
            {
                ( $offset, $rows ) = @$limit;
            }
            elsif ( $input eq 'HASH' )
            {
                ( $offset, $rows ) = map { $limit->{ $_ } } qw( offset rows );
            }
            else
            {
                croak( "can't parse $limit for LIMIT params" );
            }

            croak( "undefined offset in $limit" ) unless defined $offset;
            croak( "undefined rows in $limit" )   unless defined $rows;

            $limit_phrase = $class->_make_limit_phrase( $offset, $rows );
        }
        else
        {
            $limit_phrase = $limit;
        }

        $phrase .= ' ' . $limit_phrase;

        warn "phrase: $phrase" if $Debug;
    }

    return $class->retrieve_from_sql( $phrase, @bind );
}

1;

__END__

=head1 NAME

Class::DBI::Plugin::Pager - paged results for CDBI::AbstractSearch

=head1 SYNOPSIS

    package CD;
    use base 'Class::DBI';

    # pager needs this
    use Class::DBI::Plugin::AbstractCount;

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

    # or to use a different LIMIT syntax
    # use Class::DBI::Plugin::Pager::MySQL;

    __PACKAGE__->set_db(...);


    # in a nearby piece of code...

    use CD;

    # see SQL::Abstract and CDBI::AbstractSearch for how to specify the query
    my $where = { ... };

    my $attr => { order_by => 'foo' };

    my ( $pager, @cds ) = CD->search_where_paged( where    => $where,
                                                  attr     => $attr,
                                                  per_page => 10,
                                                  page     => 3,
                                                  );

    # or

    my ( $pager, @cds ) = CD->search_where_paged( $where, $attr, $per_page, $page );

    # $pager is a Data::Page object
    # @cds contains the CDs just for the current page

=head1 EXPORTS

The following methods are placed in the package of the caller (e.g. C<CD>).

=item search_where_paged

Accepts a L<SQL::Abstract|SQL::Abstract> query specification.

=item search_where_limitable

Accepts a L<SQL::Abstract|SQL::Abstract> query specification, and an additional
C<limit> spec.

    $where = { ... };

    $attr  = { order_by => [ 'this', 'that' ],
               limit    => [ $offset, $limit ],
               };

    CD->search_where_limitable( $where, $attr );

The C<limit> clause can be specified as an arrayref: C<[ $offset, $limit ]>,
a hashref: C<{ offset => $offset, rows => $rows }>, or a literal string:
C<LIMIT $rows OFFSET $offset>.

=item _make_limit_phrase

Puts the method C<_make_limit_phrase> into the package of the caller. This is
aliased to the C<make_limit> method of C<Class::DBI::Plugin::Pager> or a
subclass.


=head1 SUBCLASSES

This module works by appending a phrase to the end of the WHERE
clause generated by L<SQL::Abstract|SQL::Abstract>. Alternative syntaxes can
be implemented in subclasses. The default syntax works in PostgreSQL and
recent versions of MySQL, and probably some other databases.

Any comments on whether this actually does work for PostgreSQL
or InterBase would be very helpful, since I only have access to MySQL.

Subclasses should implement a C<make_limit> method. This receives three
arguments: the class of the CDBI package (e.g. 'CD'), and the C<offset> and <rows>
for the limit (or whatever) clause.

The subclasses implement the following LIMIT syntaxes:

=item Class::DBI::Plugin::Pager::LimitOffset

    LIMIT $rows OFFSET $offset

This is the default i.e. you can just say C<use Class::DBI::Plugin::Pager> to
get this syntax.

This should work for PostgreSQL, more recent MySQL, and some others.

=item Class::DBI::Plugin::LimitXY

    LIMIT $offset, $rows

I believe this syntax is only used by older versions of MySQL.

=item Class::DBI::Plugin::RowsTo

    ROWS $offset TO $offset + $rows

InterBase, also FireBird, maybe others?

=head1 TODO

I've only used this on an older version of MySQL. Reports of this thing
working (or not) elsewhere would be useful.

Tests.

=head1 CAVEATS

This class can't implement the subselect mechanism required by some databases
to emulate the LIMIT phrase, because it only has access to the WHERE clause,
not the whole SQL statement.

Each query issues two requests to the database - the first to count the entire
result set, the second to retrieve the required subset of results. If your
tables are small it may be quicker to use L<Class::DBI::Pager|Class::DBI::Pager>.

The C<order_by> clause means the database has to retrieve and sort the
entire results set, before chopping out the requested subset. It's probably a
good idea to have an index on the column(s) used to order the results.

=head1 DEPENDENCIES

L<SQL::Abstract|SQL::Abstract>,
L<Data::Page|Data::Page>,
L<Class::DBI::Plugin::AbstractCount|Class::DBI::Plugin::AbstractCount>,
L<Carp|Carp>.

=head1 SEE ALSO

L<Class::DBI::Pager|Class::DBI::Pager> does a similar job, but retrieves
the entire results set into memory before chopping out the page you want.

=head1 BUGS

Please report all bugs via the CPAN Request Tracker at
L<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Class-DBI-Plugin-Pager>;.

=head1 COPYRIGHT AND LICENSE

Copyright 2004 by David Baird.

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.

=head1 AUTHOR

David Baird, C<cpan@xxxxxxxxxxxxx.xx.xx>






--Message-Boundary-2842
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'RowsTo.pm'

package Class::DBI::Plugin::Pager::RowsTo;
use strict;
use warnings;

use base 'Class::DBI::Plugin::Pager';

sub make_limit {
    my ( undef, $offset, $rows ) = @_;

    my $last = $rows + $offset;

    return "ROWS $offset TO $last";
}

1;


--Message-Boundary-2842
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'LimitOffset.pm'

package Class::DBI::Plugin::Pager::LimitOffset;
use strict;
use warnings;

use base 'Class::DBI::Plugin::Pager';

sub make_limit {
    my ( undef, $offset, $rows ) = @_;

    return "LIMIT $rows OFFSET $offset";
}

1;


--Message-Boundary-2842
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'LimitXY.pm'

package Class::DBI::Plugin::Pager::LimitXY;
use strict;
use warnings;

use base 'Class::DBI::Plugin::Pager';

sub make_limit {
    my ( undef, $offset, $rows ) = @_;

    return "LIMIT $offset, $rows";
}

1;

--Message-Boundary-2842--