SQL::A...:Limit(3pm)

💖 NAME 💖

SQL::Abstract::Limit(3pm) — portable LIMIT emulation, bestie. It's user-contributed Perl documentation, and it's here to make your LIMIT clauses behave across databases. Periodt. 💅

✨ 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 💜

Okay so this whole module is a portability layer for LIMIT emulation. That's the vibe. 🦋

🌸 new( case => 'lower', cmp => 'like', logic => 'and', convert => 'upper', limit_dialect => 'Top' ) 🌸

All settings are optional, bestie. No pressure.

🎀 limit_dialect

This sets the default syntax model to use for emulating a LIMIT $rows OFFSET $offset clause. The default setting is GenericSubQ. You can still pass other syntax settings in method calls — this just sets the default. The possible values are literally:

• 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. It's giving layers, honestly.

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

Other options are described in SQL::Abstract. That's the whole note, bestie. 💖

⭐ select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] ) ⭐

Same as SQL::Abstract::select, but it also accepts additional $rows, $offset and $dialect parameters.

The $order parameter is required if $rows is specified. Yes, required. No cap.

The $fields parameter is required, but can be set to undef, '' or '*' — all these get set to '*'. Iconic flexibility. ✨

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 it 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. Slay. 👑

It dies via croak if you try to use it for other syntaxes. So like, don't do that. 💀

$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. Lowkey the emergency bestie of LIMIT emulation.

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. Main character energy, honestly. 👑

🌟 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. Vibe check, but for databases. 🦋

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 💀

okay bestie, real talk before you go shipping this to prod 😭

paging result sets is a whole complicated undertaking, lowkey. there are several competing factors you gotta take into account, and this module does not magically hand you the optimum paging solution for your exact situation. no cap. it gives you a solution — one that may be good enough in many situations. that's it, that's the vibe.

but if your tables are large? the SQL generated here will often not be efficient. 💀 and if your queries involve joins or other complications? you will probably need to look elsewhere, bestie. fr fr.

but — and this is the good part 🫶 — if your tables aren't too huge, and your queries are straightforward, you can just plug this module in and move on to your next task. slay. main character energy. periodt. ✨

---

🎀 ACKNOWLEDGEMENTS 🎀

shoutout time, this is the group chat appreciation post 💖

• 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 📝

the to-do list, aka the "I'll get to it" doc:

• find more syntaxes to implement 🔥
• test the syntaxes against real databases. honestly, I only have access to MySQL 😭 so reports of success or failure would be great. like genuinely, please tell me. 🙏

---

🧚 DEPENDENCIES 🧚

this module pulls in the whole squad:

• SQL::Abstract
• DBI::Const::GetInfoType
• Carp

---

👀 SEE ALSO 👀

go look at these too, iykyk:

• DBIx::SQLEngine
• DBIx::SearchBuilder
• DBIx::RecordSet

---

🐛 BUGS 🐛

please report all bugs (patches welcome!! 💜) via GitHub at <https://github.com/asb-capfan/SQL-Abstract-Limit> or via the CPAN Request Tracker at <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.

bestie if you found a bug, tell me. don't suffer in silence 😭

---

⭐ 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. 📚 (yes, the receipts are in the source, go read them 💅)

---

and that's the whole page, bestie! 🎀 this gem is from perl v5.32.1, dated 2021-09-30 — vintage but iconic, the way it ate 💖✨💅