man SQL::Abstract::Limit () - portable LIMIT emulation

NAME

SQL::Abstract::Limit - portable LIMIT emulation

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

DESCRIPTION

Portability layer for LIMIT emulation.

new( case => 'lower', cmp => 'like', logic => 'and', convert => 'upper', limit_dialect => 'Top' )
All settings are optional.
limit_dialect
Sets the default syntax model to use for emulating a CWLIMIT $rows OFFSET $offset clause. Default setting is CWGenericSubQ. 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
    First           Informix    # not implemented yet
    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 DBI database handle, and the module will figure out which dialect to use. You can pass a Class::DBI subclass or object, and the module will find the CW$dbh and use it to find the dialect. Anything else based on DBI can be easily added by locating the CW$dbh. Patches or suggestions welcome. Other options are described in SQL::Abstract. Same as CWSQL::Abstract::select, but accepts additional CW$rows, CW$offset and CW$dialect parameters. The CW$order parameter is required if CW$rows is specified. The CW$fields parameter is required, but can be set to CWundef, CW'' or CW'*' (all these get set to CW'*'). The CW$where parameter is also required. It can be a hashref or an arrayref, or CWundef. Same as CWSQL::Abstract::where, but accepts additional CW$rows, CW$offset and CW$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 CWwhere or in the constructor), and if CW$rows is passed in. Dies via CWcroak if you try to use it for other syntaxes. CW$order is required if CW$rows is set. CW$where is required if any other parameters are specified. It can be a hashref or an arrayref, or CWundef. Returns a regular CWWHERE clause if no limits are set.
insert
update
delete
values
generate
See SQL::Abstract for these methods. CWupdate and CWdelete are not provided with any CWLIMIT emulation in this release, and no support is planned at the moment. But patches would be welcome.

Limit emulation

The following dialects are available for emulating the LIMIT clause. In each case, CW$sql represents the SQL statement generated by CWSQL::Abstract::select, minus the ORDER BY clause, e.g.

    SELECT foo, bar FROM my_table WHERE some_conditions

CW$sql_after_select represents CW$sql with the leading CWSELECT keyword removed.

CWorder_cols_up represents the sort column(s) and direction(s) specified in the CWorder parameter.

CWorder_cols_down represents the opposite sort.

CW$last = $rows + $offset

LimitOffset
Syntax
    $sql ORDER BY order_cols_up LIMIT $rows OFFSET $offset
or
    $sql ORDER BY order_cols_up LIMIT $rows
if CW$offset == 0.
Databases
    PostgreSQL
    SQLite
LimitXY
Syntax
    $sql ORDER BY order_cols_up LIMIT $offset, $rows
or
     $sql ORDER BY order_cols_up LIMIT $rows
if CW$offset == 0.
Databases
    MySQL
LimitYX
Syntax
    $sql ORDER BY order_cols_up LIMIT $rows, $offset
or
    $sql ORDER BY order_cols_up LIMIT $rows
if CW$offset == 0.
Databases
    SQLite understands this syntax, or LimitOffset. If autodetecting the
           dialect, it will be set to LimitOffset.
RowsTo
Syntax
    $sql ORDER BY order_cols_up ROWS $offset TO $last
Databases
    InterBase
    FireBird
Top
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
Databases
    SQL/Server
    MS Access
RowNum
Syntax
Oracle numbers rows from 1, not zero, so here CW$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
Databases
    Oracle
FetchFirst
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
Databases
IBM DB2
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 CWSELECT clause (i.e. the first column in the CW\@fields parameter). The results will be sorted by that unique column, so any CW$order parameter is ignored, unless it matches the unique column, in which case the direction of the sort is honoured.
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
CW$pk is the first column in CWfield_list. CW$asc_desc is the opposite direction to that specified in the method call. So if you want the final results sorted CWASC, say so, and it gets flipped internally, but the results come out as you'd expect. I think. The CWBETWEEN $offset AND $last clause is replaced with CW< $rows if <$offset == 0>.
Databases
Sybase Anything not otherwise known to this module.
First
Syntax
Looks to be identical to CWTop, e.g. CWSELECT FIRST 10 * FROM table. Can probably be implemented in a very similar way, but not done yet.
Databases
Informix

SUBCLASSING

You can create your own syntax by making a subclass that provides an CWemulate_limit method. This might be useful if you are using stored procedures to provide more efficient paging.

$sql
This is the SQL statement built by SQL::Abstract, but without the ORDER BY clause, e.g.
    SELECT foo, bar FROM my_table WHERE conditions
or just
    WHERE conditions
if calling CWwhere instead of CWselect.
$order
The CWorder parameter passed to the CWselect or CWwhere call. You can get an CWORDER BY clause from this by calling
    my $order_by = $self->_order_by( $order );
You can get a pair of CWORDER BY clauses that sort in opposite directions by saying
    my ( $up, $down ) = $self->_order_directions( $order );
The method should return a suitably modified SQL statement.

AUTO-DETECTING THE DIALECT

The CW$dialect parameter that can be passed to the constructor or to the CWselect and CWwhere methods can be a number of things. The module will attempt to determine the appropriate syntax to use.

Supported CW$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

CAVEATS

Paging results sets is a complicated undertaking, with several competing factors to take into account. This module does 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.

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.

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.

DEPENDENCIES

SQL::Abstract, DBI::Const::GetInfoType, Carp.

SEE ALSO

DBIx::SQLEngine, DBIx::SearchBuilder, DBIx::RecordSet.

BUGS

Please report all bugs via the CPAN Request Tracker at <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.

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.

AUTHOR

David Baird, CWcpan@riverside-cms.co.uk

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.