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.

   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.