dbix::class::sqlmaker(3) [osx man page]

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

NAME

       DBIx::Class::SQLMaker - An SQL::Abstract-based SQL maker class

DESCRIPTION

       This module is a subclass of SQL::Abstract and includes a number of DBIC-specific workarounds, not yet suitable for inclusion into the
       SQL::Abstract core. It also provides all (and more than) the functionality of SQL::Abstract::Limit, see
       DBIx::Class::SQLMaker::LimitDialects for more info.

       Currently the enhancements to SQL::Abstract are:

       o   Support for "JOIN" statements (via extended "table/from" support)

       o   Support of functions in "SELECT" lists

       o   "GROUP BY"/"HAVING" support (via extensions to the order_by parameter)

       o   Support of "...FOR UPDATE" type of select statement modifiers

       o   The "-ident" operator

       o   The "-value" operator

OPERATORS

   -ident
       Used to explicitly specify an SQL identifier. Takes a plain string as value which is then invariably treated as a column name (and is being
       properly quoted if quoting has been requested). Most useful for comparison of two columns:

	   my %where = (
	       priority => { '<', 2 },
	       requestor => { -ident => 'submitter' }
	   );

       which results in:

	   $stmt = 'WHERE "priority" < ? AND "requestor" = "submitter"';
	   @bind = ('2');

   -value
       The -value operator signals that the argument to the right is a raw bind value.	It will be passed straight to DBI, without invoking any of
       the SQL::Abstract condition-parsing logic. This allows you to, for example, pass an array as a column value for databases that support
       array datatypes, e.g.:

	   my %where = (
	       array => { -value => [1, 2, 3] }
	   );

       which results in:

	   $stmt = 'WHERE array = ?';
	   @bind = ([1, 2, 3]);

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(3)

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)