NAME

DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality for DBIx::Class::SQLMaker

DESCRIPTION

This module replicates a lot of the functionality originally found in SQL::Abstract::Limit. While simple limits would work as-is, the more complex dialects that require e.g. subqueries could not be reliably implemented without taking full advantage of the metadata locked within DBIx::Class::ResultSource classes. After reimplementation of close to 80% of the SQL::Abstract::Limit functionality it was deemed more practical to simply make an independent DBIx::Class-specific limit-dialect provider.

SQL LIMIT DIALECTS

Note that the actual implementations listed below never use * literally. Instead proper re-aliasing of selectors and order criteria is done, so that the limit dialect are safe to use on joined resultsets with clashing column names.

Currently the provided dialects are:

LimitOffset

 SELECT ... LIMIT $limit OFFSET $offset

Supported by PostgreSQL and SQLite

LimitXY

 SELECT ... LIMIT $offset $limit

Supported by MySQL and any SQL::Statement based DBD

RowNumberOver

 SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER( ORDER BY ... ) AS RNO__ROW__INDEX FROM (
   SELECT ...
  )
 ) WHERE RNO__ROW__INDEX BETWEEN ($offset+1) AND ($limit+$offset)

ANSI standard Limit/Offset implementation. Supported by DB2 and MSSQL >= 2005.

SkipFirst

 SELECT SKIP $offset FIRST $limit * FROM ...

Suported by Informix, almost like LimitOffset. According to SQL::Abstract::Limit ... SKIP $offset LIMIT $limit ... is also supported.

FirstSkip

 SELECT FIRST $limit SKIP $offset * FROM ...

Supported by Firebird/Interbase, reverse of SkipFirst. According to SQL::Abstract::Limit ... ROWS $limit TO $offset ... is also supported.

RowNum

Depending on the resultset attributes one of:

 SELECT * FROM (
  SELECT *, ROWNUM rownum__index FROM (
   SELECT ...
  ) WHERE ROWNUM <= ($limit+$offset)
 ) WHERE rownum__index >= ($offset+1)

or

 SELECT * FROM (
  SELECT *, ROWNUM rownum__index FROM (
    SELECT ...
  )
 ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)

or

 SELECT * FROM (
    SELECT ...
  ) WHERE ROWNUM <= ($limit+1)

Supported by Oracle.

Top

 SELECT * FROM

 SELECT TOP $limit FROM (
  SELECT TOP $limit FROM (
   SELECT TOP ($limit+$offset) ...
  ) ORDER BY $reversed_original_order
 ) ORDER BY $original_order

Unreliable Top-based implementation, supported by MSSQL < 2005.

CAVEAT

Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.

FetchFirst

 SELECT * FROM
 (
 SELECT * FROM (
  SELECT * FROM (
   SELECT * FROM ...
  ) ORDER BY $reversed_original_order
    FETCH FIRST $limit ROWS ONLY
 ) ORDER BY $original_order
   FETCH FIRST $limit ROWS ONLY
 )

Unreliable FetchFirst-based implementation, supported by IBM DB2 <= V5R3.

CAVEAT

Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.

RowCountOrGenericSubQ

This is not exactly a limit dialect, but more of a proxy for Sybase ASE. If no $offset is supplied the limit is simply performed as:

 SET ROWCOUNT $limit
 SELECT ...
 SET ROWCOUNT 0

Otherwise we fall back to "GenericSubQ"

GenericSubQ

 SELECT * FROM (
  SELECT ...
 )
 WHERE (
  SELECT COUNT(*) FROM $original_table cnt WHERE cnt.id < $original_table.id
 ) BETWEEN $offset AND ($offset+$rows-1)

This is the most evil limit "dialect" (more of a hack) for really stupid databases. It works by ordering the set by some unique column, and calculating the amount of rows that have a less-er value (thus emulating a "RowNum"-like index). Of course this implies the set can only be ordered by a single unique column.

Also note that this technique can be and often is excruciatingly slow. You may have much better luck using "software_limit" in DBIx::Class::ResultSet instead.

Currently used by Sybase ASE, due to lack of any other option.

AUTHORS

See "CONTRIBUTORS" in DBIx::Class.

LICENSE

You may distribute this code under the same terms as Perl itself.

cpanm DBIx::Class

perl -MCPAN -e shell
install DBIx::Class