package SQL::Abstract::Limit;
use strict;
use warnings;
use Carp();
use DBI::Const::GetInfoType ();
use SQL::Abstract 1.20;
use base 'SQL::Abstract';
=head1 NAME
SQL::Abstract::Limit - portable LIMIT emulation
=cut
our $VERSION = '0.143';
# additions / error reports welcome !
our %SyntaxMap = ( mssql => 'Top',
access => 'Top',
sybase => 'GenericSubQ',
oracle => 'RowNum',
db2 => 'FetchFirst',
ingres => '',
adabasd => '',
informix => 'Skip',
# asany => '',
# more recent MySQL versions support LimitOffset as well
mysql => 'LimitXY',
mysqlpp => 'LimitXY',
maxdb => 'LimitXY', # MySQL
pg => 'LimitOffset',
pgpp => 'LimitOffset',
sqlite => 'LimitOffset',
sqlite2 => 'LimitOffset',
interbase => 'RowsTo',
unify => '',
primebase => '',
mimer => '',
# anything that uses SQL::Statement can use LimitXY, I think
sprite => 'LimitXY',
wtsprite => 'LimitXY',
anydata => 'LimitXY',
csv => 'LimitXY',
ram => 'LimitXY',
dbm => 'LimitXY',
excel => 'LimitXY',
google => 'LimitXY',
);
=head1 SYNOPSIS
use SQL::Abstract::Limit;
my $sql = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );;
# or autodetect from a DBI $dbh:
my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );
# or from a Class::DBI class:
my $sql = SQL::Abstract::Limit->new( limit_dialect => 'My::CDBI::App' );
# or object:
my $obj = My::CDBI::App->retrieve( $id );
my $sql = SQL::Abstract::Limit->new( limit_dialect => $obj );
# generate SQL:
my ( $stmt, @bind ) = $sql->select( $table, \@fields, \%where, \@order, $limit, $offset );
# Then, use these in your DBI statements
my $sth = $dbh->prepare( $stmt );
$sth->execute( @bind );
# Just generate the WHERE clause (only available for some syntaxes)
my ( $stmt, @bind ) = $sql->where( \%where, \@order, $limit, $offset );
=head1 DESCRIPTION
Portability layer for LIMIT emulation.
=over 4
=item new( case => 'lower', cmp => 'like', logic => 'and', convert => 'upper', limit_dialect => 'Top' )
All settings are optional.
=over 8
=item limit_dialect
Sets the default syntax model to use for emulating a C<LIMIT $rows OFFSET $offset>
clause. Default setting is C<GenericSubQ>. You can still pass other syntax
settings in method calls, this just sets the default. Possible values are:
LimitOffset PostgreSQL, SQLite
LimitXY MySQL, MaxDB, anything that uses SQL::Statement
LimitYX SQLite (optional)
RowsTo InterBase/FireBird
Top SQL/Server, MS Access
RowNum Oracle
FetchFirst DB2
Skip Informix
GenericSubQ Sybase, plus any databases not recognised by this module
$dbh a DBI database handle
CDBI subclass
CDBI object
other DBI-based thing
The first group are implemented by appending a short clause to the end of the
statement. The second group require more intricate wrapping of the original
statement in subselects.
You can pass a L<DBI|DBI> database handle, and the module will figure out which
dialect to use.
You can pass a L<Class::DBI|Class::DBI> subclass or object, and the module will
find the C<$dbh> and use it to find the dialect.
Anything else based on L<DBI|DBI> can be easily added by locating the C<$dbh>.
Patches or suggestions welcome.
=back
Other options are described in L<SQL::Abstract|SQL::Abstract>.
=item select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] )
Same as C<SQL::Abstract::select>, but accepts additional C<$rows>, C<$offset>
and C<$dialect> parameters.
The C<$order> parameter is required if C<$rows> is specified.
The C<$fields> parameter is required, but can be set to C<undef>, C<''> or
C<'*'> (all these get set to C<'*'>).
The C<$where> parameter is also required. It can be a hashref
or an arrayref, or C<undef>.
=cut
sub select {
my $self = shift;
my $table = shift;
my $fields = shift;
my $where = shift; # if ref( $_[0] ) eq 'HASH';
my ( $order, $rows, $offset, $syntax ) = $self->_get_args( @_ );
$fields ||= '*'; # in case someone supplies '' or undef
# with no LIMIT parameters, defer to SQL::Abstract
return $self->SUPER::select( $table, $fields, $where, $order ) unless $rows;
# with LIMIT parameters, get the basic SQL without the ORDER BY clause
my ( $sql, @bind ) = $self->SUPER::select( $table, $fields, $where );
my $syntax_name = $self->_find_syntax( $syntax );
$sql = $self->_emulate_limit( $syntax_name, $sql, $order, $rows, $offset );
return wantarray ? ( $sql, @bind ) : $sql;
}
=item where( [ $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] ] )
Same as C<SQL::Abstract::where>, but accepts additional C<$rows>, C<$offset>
and C<$dialect> parameters.
Some SQL dialects support syntaxes that can be applied as simple phrases
tacked on to the end of the WHERE clause. These are:
LimitOffset
LimitXY
LimitYX
RowsTo
This method returns a modified WHERE clause, if the limit syntax is set to one
of these options (either in the call to C<where> or in the constructor), and
if C<$rows> is passed in.
Dies via C<croak> if you try to use it for other syntaxes.
C<$order> is required if C<$rows> is set.
C<$where> is required if any other parameters are specified. It can be a hashref
or an arrayref, or C<undef>.
Returns a regular C<WHERE> clause if no limits are set.
=cut
sub where
{
my $self = shift;
my $where = shift; # if ref( $_[0] ) eq 'HASH';
my ( $order, $rows, $offset, $syntax ) = $self->_get_args( @_ );
my ( $sql, @bind );
if ( defined $rows )
{
( $sql, @bind ) = $self->SUPER::where( $where );
my $syntax_name = $self->_find_syntax( $syntax );
Carp::croak( "can't build a stand-alone WHERE clause for $syntax_name" )
unless $syntax_name =~ /(?:LimitOffset|LimitXY|LimitYX|RowsTo)/i;
$sql = $self->_emulate_limit( $syntax_name, $sql, $order, $rows, $offset );
}
else
{
#
( $sql, @bind ) = $self->SUPER::where( $where, $order );
}
return wantarray ? ( $sql, @bind ) : $sql;
}
sub _get_args {
my $self = shift;
my $order = shift;
my $rows = shift;
my $offset = shift if ( defined $_[0] && $_[0] =~ /^\d+$/ );
my $syntax = shift || $self->_default_limit_syntax;
return $order, $rows, $offset, $syntax;
}
=item insert
=item update
=item delete
=item values
=item generate
See L<SQL::Abstract|SQL::Abstract> for these methods.
C<update> and C<delete> are not provided with any C<LIMIT> emulation in this
release, and no support is planned at the moment. But patches would be welcome.
=back
=cut
sub _default_limit_syntax { $_[0]->{limit_dialect} || 'GenericSubQ' }
sub _emulate_limit {
my ( $self, $syntax, $sql, $order, $rows, $offset ) = @_;
$offset ||= 0;
Carp::croak( "rows must be a number (got $rows)" ) unless $rows =~ /^\d+$/;
Carp::croak( "offset must be a number (got $offset)" ) unless $offset =~ /^\d+$/;
my $method = $self->can( 'emulate_limit' ) || "_$syntax";
$sql = $self->$method( $sql, $order, $rows, $offset );
return $sql;
}
sub _find_syntax
{
my ($self, $syntax) = @_;
# $syntax is a dialect name, database name, $dbh, or CDBI class or object
Carp::croak('no syntax') unless $syntax;
my $db;
# note: tests arranged so that the eval isn't run against a scalar $syntax
# see rt #15000
if (ref $syntax) # a $dbh or a CDBI object
{
if ( UNIVERSAL::isa($syntax => 'Class::DBI') )
{
$db = $self->_find_database_from_cdbi($syntax);
}
elsif ( eval { $syntax->{Driver}->{Name} } ) # or use isa DBI::db ?
{
$db = $self->_find_database_from_dbh($syntax);
}
}
else # string - CDBI class, db name, or dialect name
{
if (exists $SyntaxMap{lc $syntax})
{
# the name of a database
$db = $syntax;
}
elsif (UNIVERSAL::isa($syntax => 'Class::DBI'))
{
# a CDBI class
$db = $self->_find_database_from_cdbi($syntax);
}
else
{
# or it's already a syntax dialect
return $syntax;
}
}
return $self->_find_syntax_from_database($db) if $db;
# if you get here, you might like to provide a patch to determine the
# syntax model for your object or ref e.g. by getting at the $dbh stored in it
warn "can't determine syntax model for $syntax - using default";
return $self->_default_limit_syntax;
}
# most of this code modified from DBIx::AnyDBD::rebless
sub _find_database_from_dbh {
my ( $self, $dbh ) = @_;
my $driver = ucfirst( $dbh->{Driver}->{Name} ) || Carp::croak( "no driver in $dbh" );
if ( $driver eq 'Proxy' )
{
# Looking into the internals of DBD::Proxy is maybe a little questionable
( $driver ) = $dbh->{proxy_client}->{application} =~ /^DBI:(.+?):/;
}
# what about DBD::JDBC ?
my ( $odbc, $ado ) = ( $driver eq 'ODBC', $driver eq 'ADO' );
if ( $odbc || $ado )
{
my $name;
# $name = $dbh->func( 17, 'GetInfo' ) if $odbc;
$name = $dbh->get_info( $DBI::Const::GetInfoType::GetInfoType{SQL_DBMS_NAME} ) if $odbc;
$name = $dbh->{ado_conn}->Properties->Item( 'DBMS Name' )->Value if $ado;
die "can't determine driver name for ODBC or ADO handle: $dbh" unless $name;
CASE: {
$driver = 'MSSQL', last CASE if $name eq 'Microsoft SQL Server';
$driver = 'Sybase', last CASE if $name eq 'SQL Server';
$driver = 'Oracle', last CASE if $name =~ /Oracle/;
$driver = 'ASAny', last CASE if $name eq 'Adaptive Server Anywhere';
$driver = 'AdabasD', last CASE if $name eq 'ADABAS D';
# this should catch Access (ACCESS) and Informix (Informix)
$driver = lc( $name );
$driver =~ s/\b(\w)/uc($1)/eg;
$driver =~ s/\s+/_/g;
}
}
die "couldn't find DBD driver in $dbh" unless $driver;
# $driver now holds a string identifying the database server - in the future,
# it might return an object with extra information e.g. version
return $driver;
}
# $cdbi can be a class or object
sub _find_database_from_cdbi
{
my ($self, $cdbi) = @_;
# inherits from Ima::DBI
my ($dbh) = $cdbi->db_handles;
Carp::croak "no \$dbh in $cdbi" unless $dbh;
return $self->_find_database_from_dbh($dbh);
}
# currently expects a string (database moniker), but this may become an object
# with e.g. version string etc.
sub _find_syntax_from_database {
my ( $self, $db ) = @_;
my $syntax = $SyntaxMap{ lc( $db ) };
return $syntax if $syntax;
my $msg = defined $syntax ?
"no dialect known for $db - using GenericSubQ dialect" :
"unknown database $db - using GenericSubQ dialect";
warn $msg;
return 'GenericSubQ';
}
# DBIx::SearchBuilder LIMIT emulation:
# Oracle - RowNum
# Pg - LimitOffset
# Sybase - doesn't emulate
# Informix - First - but can only retrieve 1st page
# SQLite - default
# MySQL - default
# default - LIMIT $offset, $rows
# or LIMIT $rows
# if $offset == 0
# DBIx::Compat also tries, but only for the easy ones
# ---------------------------------
# LIMIT emulation routines
# utility for some emulations
sub _order_directions {
my ( $self, $order ) = @_;
return unless $order;
my $ref = ref $order;
my @order;
CASE: {
@order = @$order, last CASE if $ref eq 'ARRAY';
@order = ( $order ), last CASE unless $ref;
@order = ( $$order ), last CASE if $ref eq 'SCALAR';
Carp::croak __PACKAGE__ . ": Unsupported data struct $ref for ORDER BY";
}
my ( $order_by_up, $order_by_down );
foreach my $spec ( @order )
{
my @spec = split ' ', $spec;
Carp::croak( "bad column order spec: $spec" ) if @spec > 2;
push( @spec, 'ASC' ) unless @spec == 2;
my ( $col, $up ) = @spec; # or maybe down
$up = uc( $up );
Carp::croak( "bad direction: $up" ) unless $up =~ /^(?:ASC|DESC)$/;
$order_by_up .= ", $col $up";
my $down = $up eq 'ASC' ? 'DESC' : 'ASC';
$order_by_down .= ", $col $down";
}
s/^,/ORDER BY/ for ( $order_by_up, $order_by_down );
return $order_by_up, $order_by_down;
}
# From http://phplens.com/lens/adodb/tips_portable_sql.htm
# When writing SQL to retrieve the first 10 rows for paging, you could write...
# Database SQL Syntax
# DB2 select * from table fetch first 10 rows only
# Informix select first 10 * from table
# Microsoft SQL Server and Access select top 10 * from table
# MySQL and PostgreSQL select * from table limit 10
# Oracle 8i select * from (select * from table) where rownum <= 10
=head2 Limit emulation
The following dialects are available for emulating the LIMIT clause. In each
case, C<$sql> represents the SQL statement generated by C<SQL::Abstract::select>,
minus the ORDER BY clause, e.g.
SELECT foo, bar FROM my_table WHERE some_conditions
C<$sql_after_select> represents C<$sql> with the leading C<SELECT> keyword
removed.
C<order_cols_up> represents the sort column(s) and direction(s) specified in
the C<order> parameter.
C<order_cols_down> represents the opposite sort.
C<$last = $rows + $offset>
=over 4
=item LimitOffset
=over 8
=item Syntax
$sql ORDER BY order_cols_up LIMIT $rows OFFSET $offset
or
$sql ORDER BY order_cols_up LIMIT $rows
if C<$offset == 0>.
=item Databases
PostgreSQL
SQLite
=back
=cut
sub _LimitOffset {
my ( $self, $sql, $order, $rows, $offset ) = @_;
$sql .= $self->_order_by( $order ) . " LIMIT $rows";
$sql .= " OFFSET $offset" if +$offset;
return $sql;
}
=item LimitXY
=over 8
=item Syntax
$sql ORDER BY order_cols_up LIMIT $offset, $rows
or
$sql ORDER BY order_cols_up LIMIT $rows
if C<$offset == 0>.
=item Databases
MySQL
=back
=cut
sub _LimitXY {
my ( $self, $sql, $order, $rows, $offset ) = @_;
$sql .= $self->_order_by( $order ) . " LIMIT ";
$sql .= "$offset, " if +$offset;
$sql .= $rows;
return $sql;
}
=item LimitYX
=over 8
=item Syntax
$sql ORDER BY order_cols_up LIMIT $rows, $offset
or
$sql ORDER BY order_cols_up LIMIT $rows
if C<$offset == 0>.
=item Databases
SQLite understands this syntax, or LimitOffset. If autodetecting the
dialect, it will be set to LimitOffset.
=back
=cut
sub _LimitYX {
my ( $self, $sql, $order, $rows, $offset ) = @_;
$sql .= $self->_order_by( $order ) . " LIMIT $rows";
$sql .= " $offset" if +$offset;
return $sql;
}
=item RowsTo
=over 8
=item Syntax
$sql ORDER BY order_cols_up ROWS $offset TO $last
=item Databases
InterBase
FireBird
=back
=cut
# InterBase/FireBird
sub _RowsTo {
my ( $self, $sql, $order, $rows, $offset ) = @_;
my $last = $rows + $offset;
$sql .= $self->_order_by( $order ) . " ROWS $offset TO $last";
return $sql;
}
=item Top
=over 8
=item Syntax
SELECT * FROM
(
SELECT TOP $rows * FROM
(
SELECT TOP $last $sql_after_select
ORDER BY order_cols_up
) AS foo
ORDER BY order_cols_down
) AS bar
ORDER BY order_cols_up
=item Databases
SQL/Server
MS Access
=back
=cut
sub _Top {
my ( $self, $sql, $order, $rows, $offset ) = @_;
my $last = $rows + $offset;
my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order );
$sql =~ s/^\s*(SELECT|select)//;
$sql = <<"";
SELECT * FROM
(
SELECT TOP $rows * FROM
(
SELECT TOP $last $sql $order_by_up
) AS foo
$order_by_down
) AS bar
$order_by_up
return $sql;
}
=item RowNum
=over 8
=item Syntax
Oracle numbers rows from 1, not zero, so here $offset has been incremented by 1.
SELECT * FROM
(
SELECT A.*, ROWNUM r FROM
(
$sql ORDER BY order_cols_up
) A
WHERE ROWNUM <= $last
) B
WHERE r >= $offset
=item Databases
Oracle
=back
=cut
sub _RowNum {
my ( $self, $sql, $order, $rows, $offset ) = @_;
# Oracle orders from 1 not zero
$offset++;
my $last = $rows + $offset;
my $order_by = $self->_order_by( $order );
$sql = <<"";
SELECT * FROM
(
SELECT A.*, ROWNUM r FROM
(
$sql $order_by
) A
WHERE ROWNUM < $last
) B
WHERE r >= $offset
return $sql;
}
# DBIx::SearchBuilder::Handle::Oracle does this:
# Transform an SQL query from:
#
# SELECT main.*
# FROM Tickets main
# WHERE ((main.EffectiveId = main.id))
# AND ((main.Type = 'ticket'))
# AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
# AND ( (main.Queue = '1') ) )
#
# to:
#
# SELECT * FROM (
# SELECT limitquery.*,rownum limitrownum FROM (
# SELECT main.*
# FROM Tickets main
# WHERE ((main.EffectiveId = main.id))
# AND ((main.Type = 'ticket'))
# AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
# AND ( (main.Queue = '1') ) )
# ) limitquery WHERE rownum <= 50
# ) WHERE limitrownum >= 1
#
#if ($per_page) {
# # Oracle orders from 1 not zero
# $first++;
# # Make current query a sub select
# $$statementref = "SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( $$statementref ) limitquery WHERE rownum <= " . ($first + $per_page - 1) . " ) WHERE limitrownum >= " . $first;
#}
# DBIx::SQLEngine::Driver::Oracle does this:
#sub sql_limit {
# my $self = shift;
# my ( $limit, $offset, $sql, @params ) = @_;
#
# # remove tablealiases and group-functions from outer query properties
# my ($properties) = ($sql =~ /^\s*SELECT\s(.*?)\sFROM\s/i);
# $properties =~ s/[^\s]+\s*as\s*//ig;
# $properties =~ s/\w+\.//g;
#
# $offset ||= 0;
# my $position = ( $offset + $limit );
#
# $sql = <<"";
#SELECT $properties FROM (
# SELECT $properties, ROWNUM AS sqle_position FROM (
# $sql
# )
#)
#WHERE sqle_position > $offset AND sqle_position <= $position
#
# return ($sql, @params);
#}
=item FetchFirst
=over 8
=item Syntax
SELECT * FROM (
SELECT * FROM (
$sql
ORDER BY order_cols_up
FETCH FIRST $last ROWS ONLY
) foo
ORDER BY order_cols_down
FETCH FIRST $rows ROWS ONLY
) bar
ORDER BY order_cols_up
=item Databases
IBM DB2
=back
=cut
sub _FetchFirst {
my ( $self, $sql, $order, $rows, $offset ) = @_;
my $last = $rows + $offset;
my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order );
$sql = <<"";
SELECT * FROM (
SELECT * FROM (
$sql
$order_by_up
FETCH FIRST $last ROWS ONLY
) foo
$order_by_down
FETCH FIRST $rows ROWS ONLY
) bar
$order_by_up
return $sql;
}
=item GenericSubQ
When all else fails, this should work for many databases, but it is probably
fairly slow.
This method relies on having a column with unique values as the first column in
the C<SELECT> clause (i.e. the first column in the C<\@fields> parameter). The
results will be sorted by that unique column, so any C<$order> parameter is
ignored, unless it matches the unique column, in which case the direction of
the sort is honored.
=over 8
=item Syntax
SELECT field_list FROM $table X WHERE where_clause AND
(
SELECT COUNT(*) FROM $table WHERE $pk > X.$pk
)
BETWEEN $offset AND $last
ORDER BY $pk $asc_desc
C<$pk> is the first column in C<field_list>.
C<$asc_desc> is the opposite direction to that specified in the method call. So
if you want the final results sorted C<ASC>, say so, and it gets flipped
internally, but the results come out as you'd expect. I think.
The C<BETWEEN $offset AND $last> clause is replaced with C<E<lt> $rows> if
<$offset == 0>.
=item Databases
Sybase
Anything not otherwise known to this module.
=back
=cut
sub _GenericSubQ {
my ( $self, $sql, $order, $rows, $offset ) = @_;
my $last = $rows + $offset;
my $order_by = $self->_order_by( $order );
my ( $pk, $table ) = $sql =~ /^\s*SELECT\s+(\w+),?.*\sFROM\s+([\w]+)/i;
#warn "pk: $pk";
#warn "table: $table";
# get specified sort order and swap it to get the expected output (I think?)
my ( $asc_desc ) = $order_by =~ /\b$pk\s+(ASC|DESC)\s*/i;
$asc_desc = 'ASC' unless defined $asc_desc;
$asc_desc = uc( $asc_desc );
$asc_desc = $asc_desc eq 'ASC' ? 'DESC' : 'ASC';
$sql =~ s/FROM $table /FROM $table X /;
my $limit = $offset ? "BETWEEN $offset AND $last" : "< $rows";
$sql = <<"";
$sql AND
(
SELECT COUNT(*) FROM $table WHERE $pk > X.$pk
)
$limit
ORDER BY $pk $asc_desc
return $sql;
}
=begin notes
1st page:
SELECT id, field1, fieldn
FROM table_xyz X
WHERE
(
SELECT COUNT(*) FROM table_xyz WHERE id > X.id
)
< 100
ORDER BY id DESC
Next page:
SELECT id, field1, fieldn
FROM table_xyz X
WHERE
(
SELECT COUNT(*) FROM table_xyz WHERE id > X.id
)
BETWEEN 100 AND 199
ORDER BY id DESC
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,,sid63_gci978197,00.html
We can adapt the generic Top N query to this task. I would not use the generic
method when TOP or LIMIT is available, but you're right, the previous answer
is incomplete without this.
Using the same table and column names, the top 100 ids are given by:
SELECT id, field1, fieldn FROM table_xyz X
WHERE ( SELECT COUNT(*)
FROM table_xyz
WHERE id > X.id ) < 100
ORDER BY id DESC
The subquery is correlated, which means that it will be evaluated for each row
of the outer query. The subquery says "count the number of rows that have an
id that is greater than this id." Note that the sort order is descending, so
we are looking for ids that are greater, i.e. higher up in the result set. If
that number is less than 100, then this row must be one of the top 100. Simple,
eh? Unfortunately, it runs quite slowly. Furthermore, it takes ties into
consideration, which is good, but this means that the number of rows returned
isn't always going to be exactly 100 -- there will be extra rows if there are
ties extending across the 100th place.
Next, we need the second set of 100:
select id
, field1
, fieldn
from table_xyz X
where ( select count(*)
from table_xyz
where id > X.id ) between 100 and 199
order by id desc
See the pattern? Note that the same caveat applies about ties that extend
across 200th place.
=end notes
=begin notes
=item First
=over 8
=item Syntax
Looks to be identical to C<Top>, e.g. C<SELECT FIRST 10 * FROM table>. Can
probably be implemented in a very similar way, but not done yet.
=item Databases
Informix
=back
sub _First {
my ( $self, $sql, $order, $rows, $offset ) = @_;
die 'FIRST not implemented';
# fetch first 20 rows
# might need to add to regex in 'where' method
}
=end notes
=cut
=item Skip
=over 8
=item Syntax
select skip 5 limit 5 * from customer
which will take rows 6 through 10 in the select.
=item Databases
Informix
=back
=cut
sub _Skip {
my ( $self, $sql, $order, $rows, $offset ) = @_;
my $last = $rows + $offset;
my ( $order_by_up, $order_by_down ) = $self->_order_directions( $order );
$sql =~ s/^\s*(SELECT|select)//;
$sql = "select skip $offset limit $rows ".$sql." ".$self->_order_by( $order );
return $sql;
}
1;
__END__
=back
=head1 SUBCLASSING
You can create your own syntax by making a subclass that provides an
C<emulate_limit> method. This might be useful if you are using stored procedures
to provide more efficient paging.
=over 4
=item emulate_limit( $self, $sql, $order, $rows, $offset )
=over 4
=item $sql
This is the SQL statement built by L<SQL::Abstract|SQL::Abstract>, but without
the ORDER BY clause, e.g.
SELECT foo, bar FROM my_table WHERE conditions
or just
WHERE conditions
if calling C<where> instead of C<select>.
=item $order
The C<order> parameter passed to the C<select> or C<where> call. You can get
an C<ORDER BY> clause from this by calling
my $order_by = $self->_order_by( $order );
You can get a pair of C<ORDER BY> clauses that sort in opposite directions by
saying
my ( $up, $down ) = $self->_order_directions( $order );
=back
The method should return a suitably modified SQL statement.
=back
=head1 AUTO-DETECTING THE DIALECT
The C<$dialect> parameter that can be passed to the constructor or to the
C<select> and C<where> methods can be a number of things. The module will
attempt to determine the appropriate syntax to use.
Supported C<$dialect> things are:
dialect name (e.g. LimitOffset, RowsTo, Top etc.)
database moniker (e.g. Oracle, SQLite etc.)
DBI database handle
Class::DBI subclass or object
=head1 CAVEATS
Paging results sets is a complicated undertaking, with several competing factors
to take into account. This module does B<not> magically give you the optimum
paging solution for your situation. It gives you a solution that may be good
enough in many situations. But if your tables are large, the SQL generated here
will often not be efficient. Or if your queries involve joins or other
complications, you will probably need to look elsewhere.
But if your tables aren't too huge, and your queries straightforward, you can
just plug this module in and move on to your next task.
=head1 ACKNOWLEDGEMENTS
Thanks to Aaron Johnson for the Top syntax model (SQL/Server and MS Access).
Thanks to Emanuele Zeppieri for the IBM DB2 syntax model.
Thanks to Paul Falbe for the Informix implementation.
=head1 TODO
Find more syntaxes to implement.
Test the syntaxes against real databases. I only have access to MySQL. Reports
of success or failure would be great.
=head1 DEPENDENCIES
L<SQL::Abstract|SQL::Abstract>,
L<DBI::Const::GetInfoType|DBI::Const::GetInfoType>,
L<Carp|Carp>.
=head1 SEE ALSO
L<DBIx::SQLEngine|DBIx::SQLEngine>,
L<DBIx::SearchBuilder|DBIx::SearchBuilder>,
L<DBIx::RecordSet|DBIx::RecordSet>.
=head1 BUGS
Please report all bugs (patches welcome) via GitHub
at L<https://github.com/asb-capfan/SQL-Abstract-Limit>
or via the CPAN Request Tracker at
L<http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.
=head1 AUTHOR, COPYRIGHT AND LICENSE
Copyright 2004-2020 by David Baird.
Currently maintained by Alexander Becker.
This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.
=head1 HOW IS IT DONE ELSEWHERE
A few CPAN modules do this for a few databases, but the most comprehensive
seem to be DBIx::SQLEngine, DBIx::SearchBuilder and DBIx::RecordSet.
Have a look in the source code for my notes on how these modules tackle
similar problems.
=begin notes
=over 4
=item DBIx::SearchBuilder::Handle::Oracle
Transform an SQL query from:
SELECT main.*
FROM Tickets main
WHERE ((main.EffectiveId = main.id))
AND ((main.Type = 'ticket'))
AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
AND ( (main.Queue = '1') ) )
to:
SELECT * FROM (
SELECT limitquery.*,rownum limitrownum FROM (
SELECT main.*
FROM Tickets main
WHERE ((main.EffectiveId = main.id))
AND ((main.Type = 'ticket'))
AND ( ( (main.Status = 'new')OR(main.Status = 'open') )
AND ( (main.Queue = '1') ) )
) limitquery WHERE rownum <= 50
) WHERE limitrownum >= 1
if ($per_page) {
# Oracle orders from 1 not zero
$first++;
# Make current query a sub select
$$statementref = "SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( $$statementref ) limitquery WHERE rownum <= " . ($first + $per_page - 1) . " ) WHERE limitrownum >= " . $first;
}
=item DBIx::SQLEngine::Driver
sub sql_limit {
my $self = shift;
my ( $limit, $offset, $sql, @params ) = @_;
$sql .= " limit $limit" if $limit;
$sql .= " offset $offset" if $offset;
return ($sql, @params);
}
=item DBIx::SQLEngine::Driver::AnyData
Also:
DBIx::SQLEngine::Driver::CSV
Adds support for SQL select limit clause.
TODO: Needs workaround to support offset.
sub sql_limit {
my $self = shift;
my ( $limit, $offset, $sql, @params ) = @_;
# You can't apply "limit" to non-table fetches
$sql .= " limit $limit" if ( $sql =~ / from / );
return ($sql, @params);
}
=item DBIx::SQLEngine::Driver::Informix - Support DBD::Informix and DBD::ODBC/Informix
=item sql_limit()
Not yet supported. Perhaps we should use "first $maxrows" and throw out the first $offset?
=back
=cut
sub sql_limit {
confess("Not yet supported")
}
=item DBIx::SQLEngine::Driver::MSSQL - Support DBD::ODBC with Microsoft SQL Server
=item sql_limit()
Adds support for SQL select limit clause.
=back
=cut
sub sql_limit {
my $self = shift;
my ( $limit, $offset, $sql, @params ) = @_;
# You can't apply "limit" to non-table fetches like "select LAST_INSERT_ID"
if ( $sql =~ /\bfrom\b/ and defined $limit or defined $offset) {
$sql .= " limit $limit" if $limit;
$sql .= " offset $offset" if $offset;
}
return ($sql, @params);
}
=item DBIx::SQLEngine::Driver::Mysql - Support DBD::mysql
=item sql_limit()
Adds support for SQL select limit clause.
=back
=cut
sub sql_limit {
my $self = shift;
my ( $limit, $offset, $sql, @params ) = @_;
# You can't apply "limit" to non-table fetches like "select LAST_INSERT_ID"
if ( $sql =~ /\bfrom\b/ and $limit or $offset) {
$limit ||= 1_000_000; # MySQL select with offset requires a limit
$sql .= " limit " . ( $offset ? "$offset," : '' ) . $limit;
}
return ($sql, @params);
}
=item DBIx::SQLEngine::Driver::Oracle - Support DBD::Oracle and DBD::ODBC/Oracle
=item sql_limit()
Adds support for SQL select limit clause.
Implemented as a subselect with ROWNUM.
=back
=cut
sub sql_limit {
my $self = shift;
my ( $limit, $offset, $sql, @params ) = @_;
# remove tablealiases and group-functions from outer query properties
my ($properties) = ($sql =~ /^\s*SELECT\s(.*?)\sFROM\s/i);
$properties =~ s/[^\s]+\s*as\s*//ig;
$properties =~ s/\w+\.//g;
$offset ||= 0;
my $position = ( $offset + $limit );
$sql = <<"";
SELECT $properties FROM (
SELECT $properties, ROWNUM AS sqle_position FROM (
$sql
)
)
WHERE sqle_position > $offset AND sqle_position <= $position
return ($sql, @params);
}
=item DBIx::SQLEngine::Driver::Pg - Support DBD::Pg
=head2 sql_limit
$sqldb->sql_limit( $limit, $offset, $sql, @params ) : $sql, @params
Adds support for SQL select limit clause.
=cut
sub sql_limit {
my $self = shift;
my ( $limit, $offset, $sql, @params ) = @_;
# You can't apply "limit" to non-table fetches like "select LAST_INSERT_ID"
if ( $sql =~ /\bfrom\b/ and defined $limit or defined $offset) {
$sql .= " limit $limit" if $limit;
$sql .= " offset $offset" if $offset;
}
return ($sql, @params);
}
=item DBIx::SQLEngine::Driver::SQLite - Support DBD::SQLite driver
=head2 sql_limit
Adds support for SQL select limit clause.
=cut
sub sql_limit {
my $self = shift;
my ( $limit, $offset, $sql, @params ) = @_;
# You can't apply "limit" to non-table fetches like "select LAST_INSERT_ID"
if ( $sql =~ /\bfrom\b/ and defined $limit or defined $offset) {
$sql .= " limit $limit" if $limit;
$sql .= " offset $offset" if $offset;
}
return ($sql, @params);
}
=item DBIx::SQLEngine::Driver::Sybase - Extends SQLEngine for DBMS Idiosyncrasies
=item sql_limit()
Not yet supported.
See http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12
=back
=cut
sub sql_limit {
confess("Not yet supported")
}
=item DBIx::SQLEngine::Driver::Sybase::MSSQL - Support DBD::Sybase with Microsoft SQL
Nothing.
=back
=cut
=end notes