DESCRIPTION

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:

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.

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.