DBIx::Class::SQLMaker::LimitDialects(3) 		User Contributed Perl Documentation		   DBIx::Class::SQLMaker::LimitDialects(3)

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.

perl v5.16.2							    2012-08-23				   DBIx::Class::SQLMaker::LimitDialects(3)