--- name: "SQL::Abstract::Limit" section: "3pm" platform: "linux" package: "libsql-abstract-limit-perl" version: "2:0.14.3-1" release: "Debian 13 (Trixie)" platforms: ["linux"] url: "https://new.carta.tech/SQL::Abstract::Limit" --- # SQL::Abstract::Limit(3pm) 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 `"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 honored. - 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 (patches welcome) via GitHub at \ or via the CPAN Request Tracker at \. ## 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. ## 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.