Provided by: libsql-abstract-limit-perl_0.14.1-4_all bug

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 "LIMIT $rows OFFSET
                   $offset" clause. Default setting is "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 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
                   $dbh and use it to find the dialect.

                   Anything else based on DBI can be easily added by locating the $dbh.  Patches
                   or suggestions welcome.

           Other options are described in SQL::Abstract.

       select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] )
           Same as "SQL::Abstract::select", but accepts additional $rows, $offset and $dialect
           parameters.

           The $order parameter is required if $rows is specified.

           The $fields parameter is required, but can be set to "undef", '' or '*' (all these get
           set to '*').

           The $where parameter is also required. It can be a hashref or an arrayref, or "undef".

       where( [ $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] ] )
           Same as "SQL::Abstract::where", but accepts additional $rows, $offset and $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 "where" or in the constructor), and if $rows is
           passed in.

           Dies via "croak" if you try to use it for other syntaxes.

           $order is required if $rows is set.

           $where is required if any other parameters are specified. It can be a hashref or an
           arrayref, or "undef".

           Returns a regular "WHERE" clause if no limits are set.

       insert
       update
       delete
       values
       generate
           See SQL::Abstract for these methods.

           "update" and "delete" are not provided with any "LIMIT" 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, $sql
       represents the SQL statement generated by "SQL::Abstract::select", minus the ORDER BY
       clause, e.g.

           SELECT foo, bar FROM my_table WHERE some_conditions

       $sql_after_select represents $sql with the leading "SELECT" keyword removed.

       "order_cols_up" represents the sort column(s) and direction(s) specified in the "order"
       parameter.

       "order_cols_down" represents the opposite sort.

       "$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 "$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 "$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 "$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 $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
           "SELECT" clause (i.e. the first column in the "\@fields" parameter). The results will
           be sorted by that unique column, so any $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

                   $pk is the first column in "field_list".

                   $asc_desc is the opposite direction to that specified in the method call. So
                   if you want the final results sorted "ASC", say so, and it gets flipped
                   internally, but the results come out as you'd expect. I think.

                   The "BETWEEN $offset AND $last" clause is replaced with "< $rows" if <$offset
                   == 0>.

           Databases
                   Sybase Anything not otherwise known to this module.

       Skip
           Syntax
                     select skip 5 limit 5 * from customer

                   which will take rows 6 through 10 in the select.

           Databases
                   Informix

SUBCLASSING

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

       emulate_limit( $self, $sql, $order, $rows, $offset )
           $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 "where" instead of "select".

           $order
               The "order" parameter passed to the "select" or "where" call. You can get an
               "ORDER BY" clause from this by calling

                   my $order_by = $self->_order_by( $order );

               You can get a pair of "ORDER 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 $dialect parameter that can be passed to the constructor or to the "select" and
       "where" methods can be a number of things. The module will attempt to determine the
       appropriate syntax to use.

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

       Thanks to Paul Falbe for the Informix implementation.

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
       <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, "cpan@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.