Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
SQL::Abstract::Limit(3User Contributed Perl DocumentatiSQL::Abstract::Limit(3)

NAME
       SQL::Abstract::Limit - portable LIMIT emulation

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
       Portability layer for LIMIT emulation.

       new( case => 'lower', cmp => 'like', logic => 'and', convert =>
       'upper',	limit_dialect => 'Top' )
	   All settings	are optional.

	   limit_dialect
		   Sets	the default syntax model to use	for emulating a	"LIMIT
		   $rows OFFSET	$offset" clause. Default setting is
		   "GenericSubQ". You can still	pass other syntax settings in
		   method calls, this just sets	the default. Possible values
		   are:

		       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.

		   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 are described in SQL::Abstract.

       select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [
       $dialect	] ] ] )
	   Same	as "SQL::Abstract::select", but	accepts	additional $rows,
	   $offset and $dialect	parameters.

	   The $order parameter	is required if $rows is	specified.

	   The $fields parameter is required, but can be set to	"undef", '' or
	   '*' (all these get set to '*').

	   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 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.

	   Dies	via "croak" if you try to use it for other syntaxes.

	   $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.

	   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 honoured.

	   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.

       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.

       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
       Paging results sets is a	complicated undertaking, with several
       competing factors to take into account. This module does	not magically
       give you	the optimum paging solution for	your situation.	It gives you a
       solution	that may be good enough	in many	situations. But	if your	tables
       are large, the SQL generated here will often not	be efficient. Or if
       your queries involve joins or other complications, you will probably
       need to look elsewhere.

       But if your tables aren't too huge, and your queries straightforward,
       you can just plug this module in	and move on to your next task.

ACKNOWLEDGEMENTS
       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
       Find more syntaxes to implement.

       Test the	syntaxes against real databases. I only	have access to MySQL.
       Reports of success or failure would be great.

DEPENDENCIES
       SQL::Abstract, DBI::Const::GetInfoType, Carp.

SEE ALSO
       DBIx::SQLEngine,	DBIx::SearchBuilder, DBIx::RecordSet.

BUGS
       Please report all bugs via the CPAN Request Tracker at
       <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.

COPYRIGHT AND LICENSE
       Copyright 2004 by David Baird.

       This library is free software; you can redistribute it and/or modify it
       under the same terms as Perl itself.

AUTHOR
       David Baird, "cpan@riverside-cms.co.uk"

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.

perl v5.24.1			  2017-07-02	       SQL::Abstract::Limit(3)

NAME | SYNOPSIS | DESCRIPTION | SUBCLASSING | AUTO-DETECTING THE DIALECT | CAVEATS | ACKNOWLEDGEMENTS | TODO | DEPENDENCIES | SEE ALSO | BUGS | COPYRIGHT AND LICENSE | AUTHOR | HOW IS IT DONE ELSEWHERE

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=SQL::Abstract::Limit&sektion=3&manpath=FreeBSD+12.0-RELEASE+and+Ports>

home | help