view release on metacpan or  search on metacpan

lib/DBIx/Class/SQLMaker/LimitDialects.pm  view on Meta::CPAN

# because of how loose dep specs are, we need to keep squatting
# on the CPAN face - FOREVER.
package DBIx::Class::SQLMaker::LimitDialects;

use warnings;
use strict;

##
## Compat in case someone is using these in the wild...
##

my $sigh = sub {
  require DBIx::Class::_Util;
  require DBIx::Class::SQLMaker;

  my( $meth ) = (caller(1))[3] =~ /([^:]+)$/;

  DBIx::Class::_Util::emit_loud_diag(
    skip_frames => 1,
    msg => "The $meth() constant is now provided by DBIx::Class::SQLMaker::ClassicExtensions: please adjust your code"
  );

  DBIx::Class::SQLMaker::ClassicExtensions->$meth;
};

sub __rows_bindtype { $sigh->() }
sub __offset_bindtype { $sigh->() }
sub __total_bindtype { $sigh->() }

1;

__END__

=head1 NAME

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

=head1 DESCRIPTION

DBIC's SQLMaker stack replicates and surpasses all of the functionality
originally found in L<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 L<DBIx::Class::ResultSource> classes. After reimplementation
of close to 80% of the L<SQL::Abstract::Limit> functionality it was deemed
more practical to simply make an independent DBIx::Class-specific
limit-dialect provider.

=head1 SQL LIMIT DIALECTS

Note that the actual implementations listed below never use C<*> 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:

=head2 LimitOffset

 SELECT ... LIMIT $limit OFFSET $offset

Supported by B<PostgreSQL> and B<SQLite>

=head2 LimitXY

 SELECT ... LIMIT $offset, $limit

Supported by B<MySQL> and any L<SQL::Statement> based DBD

=head2 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 B<DB2> and
B<< MSSQL >= 2005 >>.

=head2 SkipFirst

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

Supported by B<Informix>, almost like LimitOffset. According to
L<SQL::Abstract::Limit> C<... SKIP $offset LIMIT $limit ...> is also supported.

=head2 FirstSkip

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

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

=head2 RowNum

Depending on the resultset attributes one of:

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