Cdbi pagers
From ClassDBI
Short version: Don't use Class::DBI::Pager. Use Class::DBI::Plugin::Pager (or better Class::DBI::Sweet) instead.
The current version of Class::DBI::Plugin::Pager uses LIMIT, etc., where supported by the database, so the discussion below is obsolete in some places.
See also Class::DBI::Search::LIMITed on CPAN for how to do this using the latest Class::DBI.
Class::DBI::Sweet also has pager capability, among other extensions to Class::DBI.
Different databases use different syntaxes to implement LIMIT-like functionality. CDBI::Plugin::Pager supports a few. Specifically, it should work for these: pg, mysql, sqlite, interbase, firebird.
CDBI::Pager will work everywhere, even with databases that don't support LIMIT.
SQL::Abstract::Limit will work almost everywhere, but it hasn't been integrated with either plugin.
Also, see the CAVEATS section of CDBI-P-Pager's docs, for situations where you might not want to use it.
--dbaird
Pagers solve a common problem in database applications - displaying big datasets page by page. Unfortunately the filtering in Class::DBI::Pager is done in Perl code, not using the LIMIT and OFFSET or ROWNUM database-specific SQL syntax. With every page the whole dataset is fetched into memory. In configurations where the database is on another computer, the data is sent over network. It's not an appropriae solution for big datasets.
I hope the authors of Class::DBI::Pager will someday fulfill their promise from the TODO and make an implementation using the proper database mechanisms. -- ZbigniewLukasiak
Unfortunately there is no generic way to do this, so it will need to be database specific.
You can always add your own plugin to do this. The code is relatively easy.
I did not see the Class::DBI::Pager code, but I've tried to use LIMIT,OFFSET in Class::DBI directly but I did not succeded. The problem probably was that the parameter feeded into the placeholder was quoted and while normaly the database did an atoi conversion on such strings when number was needed, not in the case of the LIMIT clause. I did not find any way to provide a parameter type for placeholders in Cdbi. Like in:
$sth->bind_param(1, $value, SQL_INTEGER);
This probably isn't the most appropriate place for it, but for Oracle, a good way to page would be, given a startrow and endrow:
select b.*
from (
select rownum as therownum, a.*
from (
$originalquery
) a
where rownum <= $rowEnd
) b
where b.therownum >= $rowStart
What's better is to change the $originalquery so that it only queries id numbers and not the rest of the columns, and then instead get those other columns in the outer query.
I, too, was appalled to see Class::DBI::Pager doesn't support LIMIT at all.
What I've found is virtually no support for LIMIT in ANY of the database-related perl modules! It's not supported by Class::DBI::Pager. Nor is it supported by DBIx::Abstract. Or SQL::Abstract (what Class::DBI::Abstract uses) or DBIx::Recordset!
So now I find myself wondering: am I the only person using limits? Do we really not need to use limits, and rather just fetch the rows used from the queries?
But some simple benchmarks tell me that's not at all the case. (Mysql is the only server I'm testing against though. Perhaps others work better)?
Any idea what I'm missing here? Why do no perl database modules support the use of limits in select queries?
Over the last few years I've optimized a heck of a lot of web-based code to the T, and much of that has involved proper use of LIMIT. Now I'm boggled wondering what everyone else does.
I think most of the databases are just not big enough to make it visible, so people just don't care. But then it becomes a kind of trap when the database grows - that's why I would never recommend using those tools. -- ZbigniewLukasiak
If anyone is in need of a quick hack, there is a way to add it, at least for MySql and Postgresql. It requires overriding a private CDBI method ([=_do_search]), so I don't recommend it in general. Maybe it can be done in a more appropriate way, but this was what I got to work, so I stuck with it.
Add the following line as the next to last of [=_do_search]:
$frag .= " LIMIT $search_opts->{limit}" if $search_opts->{limit};
It allows you to do:
CDB->search( ..., { limit => '10,15' });
# or
CDB->search( ..., { limit => '10 OFFSET 15' });
Note: I made the new _do_search method part of my base class. Don't edit CDBI directly ;^)
-- RhesaRozendaal
(I just set this up in one of my base classes and it seems to work really well. Thank you! --apv)
(It's great to see that my rant has finally some positive outcome. -- ZbigniewLukasiak)
Uhhh. DBIx::Recordset does support limiting the number of rows to be returned from a generated query:
$set1->Search({'$start'=>0,'$max'=>2, '$next'=>1, '$order'=>'id'}) or die "not ok ($DBI::errstr)" ;
Read the DBIx::Recordset docs and DBIx::Recordset::Playground docs for more information and alternative ways to do this.
Regards, Terrence Brannon
See SQL::Abstract::Limit for transparent cross-db LIMIT emulation.
CDBI::Plugin::Pager uses LIMITs and emulations for a few dbs, it'll be using SQL::Abstract::Limit pretty soon for more comprehensive coverage.
-- David Baird
I have a couple of alternatives to the above 'quick hack' posted by RhesaRozendaal. The first is similar except it breaks the limit and offset arguments up in _do_search:
$frag .= " ORDER BY $search_opts->{order_by}" if $search_opts->{order_by};
$frag .= " LIMIT $search_opts->{limit}" if $search_opts->{limit};
if (defined($search_opts->{offset}) and $search_opts->{offset} =~ /\d+/) {
defined($search_opts->{limit})
|| $class->_croak("missing LIMIT for OFFSET");
$frag .= " OFFSET $search_opts->{offset}";
}
This means that you don't have to create strings like '10 OFFSET 15', but can drop your limit and offset variables straight into the method call like so:
$class->search(..., {limit => $limit, offset => $offset});
My second alternative is to create the following constructor method in my base class, which does not quote the bind variables:
sub add_constructor_noquote {
my ($class, $method, $fragment) = @_;
return $class->_croak("constructors needs a name") unless $method;
no strict 'refs';
my $meth = "$class\::$method";
return $class->_carp("$method already exists in $class")
if *$meth{CODE};
*$meth = sub {
my $self = shift;
for (@_) {
$fragment =~ s/\?/$_/;
}
$self->sth_to_objects($self->sql_Retrieve($fragment));
};
}
which you can call like this:
__PACKAGE__->add_constructor_noquote(page => qq{
epoch > 0
ORDER BY epoch
LIMIT ? OFFSET ?
});
The problem being of course that you can't mix and match quoted with unquoted variables.
If I ever need to mix quoted and unquoted variables then I'll rework this function to do so... and post it here of course.
-- Mark Lawrence, Wed Aug 24 08:36:01 UTC 2005
