* New Features / Changes
- Add quote_names connection option. When set to true automatically
sets quote_char and name_sep appropriate for your RDBMS
+ - All limit dialects (except for the older Top and FetchFirst) are
+ now using bind parameters for the limits/offsets, making DBI's
+ prepare_cached useful across paged resutsets
- Support for MS Access databases via DBD::ODBC and DBD::ADO (only
Win32 support currently tested)
- IC::DateTime support for MSSQL over DBD::ADO
}
# the "oh noes offset/top without limit" constant
-# limited to 32 bits for sanity (and consistency,
-# since it is ultimately handed to sprintf %u)
+# limited to 31 bits for sanity (and consistency,
+# since it may be handed to the like of sprintf %u)
+#
+# Also *some* builds of SQLite fail the test
+# some_column BETWEEN ? AND ?: 1, 4294967295
+# with the proper integer bind attrs
+#
# Implemented as a method, since ::Storage::DBI also
# refers to it (i.e. for the case of software_limit or
# as the value to abuse with MSSQL ordered subqueries)
-sub __max_int { 0xFFFFFFFF };
+sub __max_int () { 0x7FFFFFFF };
sub new {
my $self = shift->next::method(@_);
sub _assemble_binds {
my $self = shift;
- return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/select from where group having order/);
+ return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/select from where group having order limit/);
}
my $for_syntax = {
use List::Util 'first';
use namespace::clean;
+# constants are used not only here, but also in comparison tests
+sub __rows_bindtype () {
+ +{ sqlt_datatype => 'integer' }
+}
+sub __offset_bindtype () {
+ +{ sqlt_datatype => 'integer' }
+}
+sub __total_bindtype () {
+ +{ sqlt_datatype => 'integer' }
+}
+
=head1 NAME
DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality for DBIx::Class::SQLMaker
Currently the provided dialects are:
-=cut
-
=head2 LimitOffset
SELECT ... LIMIT $limit OFFSET $offset
=cut
sub _LimitOffset {
- my ( $self, $sql, $order, $rows, $offset ) = @_;
- $sql .= $self->_order_by( $order ) . " LIMIT $rows";
- $sql .= " OFFSET $offset" if +$offset;
+ my ( $self, $sql, $rs_attrs, $rows, $offset ) = @_;
+ $sql .= $self->_parse_rs_attrs( $rs_attrs ) . " LIMIT ?";
+ push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+ if ($offset) {
+ $sql .= " OFFSET ?";
+ push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset ];
+ }
return $sql;
}
=cut
sub _LimitXY {
- my ( $self, $sql, $order, $rows, $offset ) = @_;
- $sql .= $self->_order_by( $order ) . " LIMIT ";
- $sql .= "$offset, " if +$offset;
- $sql .= $rows;
+ my ( $self, $sql, $rs_attrs, $rows, $offset ) = @_;
+ $sql .= $self->_parse_rs_attrs( $rs_attrs ) . " LIMIT ";
+ if ($offset) {
+ $sql .= '?, ';
+ push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset ];
+ }
+ $sql .= '?';
+ push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+
return $sql;
}
my $qalias = $self->_quote ($rs_attrs->{alias});
my $idx_name = $self->_quote ('rno__row__index');
- $sql = sprintf (<<EOS, $offset + 1, $offset + $rows, );
+ $sql = <<EOS;
SELECT $out_sel FROM (
SELECT $mid_sel, ROW_NUMBER() OVER( $rno_ord ) AS $idx_name FROM (
SELECT $in_sel ${sql}${group_having}
) $qalias
-) $qalias WHERE $idx_name BETWEEN %u AND %u
+) $qalias WHERE $idx_name >= ? AND $idx_name <= ?
EOS
+ push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset + 1], [ $self->__total_bindtype => $offset + $rows ];
return $sql;
}
return sprintf ('SELECT %s%s%s%s',
$offset
- ? sprintf ('SKIP %u ', $offset)
+ ? do {
+ push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset];
+ 'SKIP ? '
+ }
: ''
,
- sprintf ('FIRST %u ', $rows),
+ do {
+ push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+ 'FIRST ? '
+ },
$sql,
$self->_parse_rs_attrs ($rs_attrs),
);
or $self->throw_exception("Unrecognizable SELECT: $sql");
return sprintf ('SELECT %s%s%s%s',
- sprintf ('FIRST %u ', $rows),
+ do {
+ push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+ 'FIRST ? '
+ },
$offset
- ? sprintf ('SKIP %u ', $offset)
+ ? do {
+ push @{$self->{limit_bind}}, [ $self->__offset_bindtype => $offset];
+ 'SKIP ? '
+ }
: ''
,
$sql,
if ($offset) {
- $sql = sprintf (<<EOS, $offset + $rows, $offset + 1 );
-
+ push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ];
+ $sql =<<"EOS";
SELECT $outsel FROM (
SELECT $outsel, ROWNUM $idx_name FROM (
SELECT $insel ${sql}${order_group_having}
- ) $qalias WHERE ROWNUM <= %u
-) $qalias WHERE $idx_name >= %u
-
+ ) $qalias WHERE ROWNUM <= ?
+) $qalias WHERE $idx_name >= ?
EOS
}
else {
- $sql = sprintf (<<EOS, $rows );
-
+ push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+ $sql =<<"EOS";
SELECT $outsel FROM (
SELECT $insel ${sql}${order_group_having}
- ) $qalias WHERE ROWNUM <= %u
-
+ ) $qalias WHERE ROWNUM <= ?
EOS
}
$order_by,
)),
$offset
- ? sprintf ('BETWEEN %u AND %u', $offset, $offset + $rows - 1)
- : sprintf ('< %u', $rows )
+ ? do {
+ push @{$self->{limit_bind}},
+ [ $self->__offset_bindtype => $offset ], [ $self->__total_bindtype => $offset + $rows - 1];
+ 'BETWEEN ? AND ?';
+ }
+ : do {
+ push @{$self->{limit_bind}}, [ $self->__rows_bindtype => $rows ];
+ '< ?';
+ }
,
);
sub _assemble_binds {
my $self = shift;
- return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/select from where oracle_connect_by group having order/);
+ return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/select from where oracle_connect_by group having order limit/);
}
use base 'DBIx::Class::Storage::DBI';
use mro 'c3';
-=head1 NAME
+use DBIx::Class::SQLMaker::LimitDialects;
+use List::Util qw/first/;
+
+use namespace::clean;
+
+=head1 NAME
DBIx::Class::Storage::DBI::NoBindVars - Sometime DBDs have poor to no support for bind variables
value is interpreted as: true - do not quote, false - do quote. You should
override this in you Storage::DBI::<database> subclass, if your RDBMS
does not like quotes around certain datatypes (e.g. Sybase and integer
-columns). The default method always returns false (do quote).
+columns). The default method returns false, except for integer datatypes
+paired with values containing nothing but digits.
WARNING!!!
sub interpolate_unquoted {
#my ($self, $datatype, $value) = @_;
+
+ return 1 if (
+ defined $_[2]
+ and
+ $_[1]
+ and
+ $_[2] !~ /\D/
+ and
+ $_[1] =~ /int(?:eger)? | (?:tiny|small|medium|big)int/ix
+ );
+
return 0;
}
use lib qw(t/lib);
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
+
$ENV{NLS_SORT} = "BINARY";
$ENV{NLS_COMP} = "BINARY";
$ENV{NLS_LANG} = "AMERICAN";
SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
FROM artist me
START WITH name = ?
- CONNECT BY parentid = PRIOR artistid
+ CONNECT BY parentid = PRIOR artistid
)',
[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
=> 'root'] ],
SELECT COUNT( * )
FROM artist me
START WITH name = ?
- CONNECT BY parentid = PRIOR artistid
+ CONNECT BY parentid = PRIOR artistid
)',
[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
=> 'root'] ],
SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
FROM artist me
START WITH name = ?
- CONNECT BY parentid = PRIOR artistid
+ CONNECT BY parentid = PRIOR artistid
ORDER SIBLINGS BY name DESC
)',
[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
FROM artist me
WHERE ( parentid IS NULL )
START WITH name = ?
- CONNECT BY parentid = PRIOR artistid
+ CONNECT BY parentid = PRIOR artistid
)',
[ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
=> 'root'] ],
LEFT JOIN cd cds ON cds.artist = me.artistid
WHERE ( cds.title LIKE ? )
START WITH me.name = ?
- CONNECT BY parentid = PRIOR artistid
+ CONNECT BY parentid = PRIOR artistid
)',
[
[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
LEFT JOIN cd cds ON cds.artist = me.artistid
WHERE ( cds.title LIKE ? )
START WITH me.name = ?
- CONNECT BY parentid = PRIOR artistid
+ CONNECT BY parentid = PRIOR artistid
)',
[
[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
FROM artist me
START WITH name = ?
- CONNECT BY parentid = PRIOR artistid
+ CONNECT BY parentid = PRIOR artistid
ORDER BY LEVEL ASC, name ASC
)',
[
CONNECT BY parentid = PRIOR artistid
ORDER BY name ASC
) me
- WHERE ROWNUM <= 2
+ WHERE ROWNUM <= ?
)',
[
[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
- => 'root'],
+ => 'root'], [ $ROWS => 2 ],
],
);
START WITH name = ?
CONNECT BY parentid = PRIOR artistid
) me
- WHERE ROWNUM <= 2
+ WHERE ROWNUM <= ?
) me
)',
[
[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
- => 'root'],
+ => 'root'], [ $ROWS => 2 ] ,
],
);
SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
FROM artist me
START WITH name = ?
- CONNECT BY NOCYCLE parentid = PRIOR artistid
+ CONNECT BY NOCYCLE parentid = PRIOR artistid
)',
[
[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
SELECT COUNT( * )
FROM artist me
START WITH name = ?
- CONNECT BY NOCYCLE parentid = PRIOR artistid
+ CONNECT BY NOCYCLE parentid = PRIOR artistid
)',
[
[ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
use DBIC::SqlMakerTest;
use Try::Tiny;
+use DBIx::Class::SQLMaker::LimitDialects;
+my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
+my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
+
my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
plan skip_all => 'Set $ENV{DBICTEST_MSSQL_ODBC_DSN}, _USER and _PASS to run this test'
is_same_bind (
\@bind,
[
- $dialect eq 'Top' ? [ { dbic_colname => 'test' } => 'xxx' ] : (), # the extra re-order bind
- (map {
- [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
- => 'somebogusstring' ],
- [ { dbic_colname => 'test' }
- => 'xxx' ],
- } (1,2)), # double because of the prefetch subq
+ ($dialect eq 'Top' ? [ { dbic_colname => 'test' } => 'xxx' ] : ()), # the extra re-order bind
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
+ => 'somebogusstring' ],
+ [ { dbic_colname => 'test' }
+ => 'xxx' ],
+ ($dialect ne 'Top' ? ( [ $OFFSET => 7 ], [ $TOTAL => 9 ] ) : ()), # parameterised RNO
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
+ => 'somebogusstring' ],
+ [ { dbic_colname => 'test' }
+ => 'xxx' ],
],
);
[ { dbic_colname => 'test' }
=> '1' ],
+ # rno(?)
+ $dialect ne 'Top' ? ( [ $OFFSET => 5 ], [ $TOTAL => 6 ] ) : (),
# outer
[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
=> 'wiggle' ],
use DBICTest;
use DBIC::SqlMakerTest;
use DBIC::DebugObj;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my ($ROWS, $OFFSET) = (
+ DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
my $schema = DBICTest->init_schema();
JOIN track tracks ON tracks.cd = me.cdid
JOIN cd disc ON disc.cdid = tracks.cd
WHERE ( ( position = ? OR position = ? ) )
- LIMIT 3 OFFSET 8
+ LIMIT ? OFFSET ?
) tracks
)',
[
=> 1 ],
[ { sqlt_datatype => 'int', dbic_colname => 'position' }
=> 2 ],
+ [$ROWS => 3],
+ [$OFFSET => 8],
],
'count_rs db-side limit applied',
);
JOIN artist artist ON artist.artistid = cds.artist
WHERE tracks.position = ? OR tracks.position = ?
GROUP BY cds.cdid
- LIMIT 3 OFFSET 4
+ LIMIT ? OFFSET ?
) cds
)',
[
=> 1 ],
[ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' }
=> 2 ],
+ [ $ROWS => 3],
+ [$OFFSET => 4],
],
'count_rs db-side limit applied',
);
}
-# count with a having clause
+# count with a having clause
{
my $rs = $schema->resultset("Artist")->search(
{},
'(SELECT COUNT( * )
FROM (
SELECT me.artistid, MAX( cds.year ) AS newest_cd_year,
- FROM artist me
- LEFT JOIN cd cds ON cds.artist = me.artistid
- GROUP BY me.artistid
+ FROM artist me
+ LEFT JOIN cd cds ON cds.artist = me.artistid
+ GROUP BY me.artistid
HAVING newest_cd_year = ?
) me
)',
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
my $schema = DBICTest->init_schema();
my $sdebug = $schema->storage->debug;
FROM cd me
WHERE ( me.cdid IS NOT NULL )
GROUP BY me.cdid
- LIMIT 2
+ LIMIT ?
) me
)',
- [],
+ [[$ROWS => 2]],
'count() query generated expected SQL',
);
WHERE ( me.cdid IS NOT NULL )
GROUP BY me.cdid
ORDER BY track_count DESC, maxtr ASC
- LIMIT 2
+ LIMIT ?
) me
LEFT JOIN track tracks ON tracks.cd = me.cdid
LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
WHERE ( me.cdid IS NOT NULL )
ORDER BY track_count DESC, maxtr ASC, tracks.cd
)',
- [],
+ [[$ROWS => 2]],
'next() query generated expected SQL',
);
FROM (SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, COUNT( tags.tag ) AS test_count
FROM cd me LEFT JOIN tags tags ON tags.cd = me.cdid
GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, tags.tag
- ORDER BY tags.tag ASC LIMIT 1)
+ ORDER BY tags.tag ASC LIMIT ?)
me
LEFT JOIN tags tags ON tags.cd = me.cdid
ORDER BY tags.tag ASC, tags.cd, tags.tag
)
- }, []);
+ }, [[$ROWS => 1]]);
}
done_testing;
use lib qw(t/lib);
use DBIC::SqlMakerTest;
use DBICTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my ($ROWS, $OFFSET) = (
+ DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
my $schema = DBICTest->init_schema();
ON cds_unordered.artist = me.artistid
WHERE ( me.rank = ? )
ORDER BY me.name ASC, me.artistid DESC
- LIMIT 3
- OFFSET 3
+ LIMIT ?
+ OFFSET ?
) cds_unordered
ON cds_unordered.artist = me.artistid
LEFT JOIN track tracks
WHERE ( me.rank = ? )
ORDER BY me.name ASC, me.artistid DESC, tracks.cd
)},
- [ map { [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' }
- => 13 ] } (1,2)
+ [
+ [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ],
+ [ $ROWS => 3 ],
+ [ $OFFSET => 3 ],
+ [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ],
],
'correct SQL on limited prefetch over search_related ordered by root',
);
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
my $schema = DBICTest->init_schema();
WHERE artwork.cd_id IS NULL
OR tracks.title != ?
GROUP BY me.artistid, me.name, me.artistid + ?
- ORDER BY name DESC LIMIT 3
+ ORDER BY name DESC LIMIT ?
) me
LEFT JOIN cd cds
ON cds.artist = me.artistid
$bind_int_resolved->(), # inner select
$bind_vc_resolved->(), # inner where
$bind_int_resolved->(), # inner group_by
+ [ $ROWS => 3 ],
$bind_vc_resolved->(), # outer where
$bind_int_resolved->(), # outer group_by
],
FROM cd me
JOIN artist artist ON artist.artistid = me.artist
WHERE ( ( artist.name = ? AND me.year = ? ) )
- LIMIT 15
+ LIMIT ?
) me
LEFT JOIN track tracks
ON tracks.cd = me.cdid
WHERE ( ( artist.name = ? AND me.year = ? ) )
ORDER BY tracks.cd
)',
- [ map {
- [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' }
- => 'foo' ],
- [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' }
- => 2010 ],
- } (1,2)],
+ [
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
+ [ $ROWS => 15 ],
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
+ ],
'No grouping of non-multiplying resultsets',
);
use lib qw(t/lib);
use DBIC::SqlMakerTest;
use DBICTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
my $schema = DBICTest->init_schema();
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
FROM cd me
JOIN artist artist ON artist.artistid = me.artist
- LEFT JOIN track tracks ON tracks.cd = me.cdid
+ LEFT JOIN track tracks ON tracks.cd = me.cdid
WHERE ( tracks.trackid != ? )
- LIMIT 2
+ LIMIT ?
) me
JOIN artist artist ON artist.artistid = me.artist
JOIN tags tags ON tags.cd = me.cdid
GROUP BY tags.tagid, tags.cd, tags.tag
)',
- [ [ { sqlt_datatype => 'integer', dbic_colname => 'tracks.trackid' }
- => 666 ]
+ [
+ [ { sqlt_datatype => 'integer', dbic_colname => 'tracks.trackid' } => 666 ],
+ [ $ROWS => 2 ]
],
'Prefetch spec successfully stripped on search_related'
);
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
my $schema = DBICTest->init_schema();
my $art_rs = $schema->resultset('Artist');
search => \[ "title = ? AND year LIKE ?", [ title => 'buahaha' ], [ year => '20%' ] ],
attrs => { rows => 5 },
sqlbind => \[
- "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE (title = ? AND year LIKE ?) LIMIT 5)",
- [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'title' }
- => 'buahaha' ],
- [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'year' }
- => '20%' ],
+ "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE (title = ? AND year LIKE ?) LIMIT ?)",
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'title' } => 'buahaha' ],
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'year' } => '20%' ],
+ [ $ROWS => 5 ],
],
},
artistid => { 'in' => $art_rs->search({}, { rows => 1 })->get_column( 'artistid' )->as_query },
},
sqlbind => \[
- "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE artistid IN ( SELECT me.artistid FROM artist me LIMIT 1 ) )",
+ "( SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track FROM cd me WHERE artistid IN ( SELECT me.artistid FROM artist me LIMIT ? ) )",
+ [ $ROWS => 1 ],
],
},
],
},
sqlbind => \[
- "( SELECT (SELECT me.id FROM cd me LIMIT 1) FROM artist me )",
+ "( SELECT (SELECT me.id FROM cd me LIMIT ?) FROM artist me )",
+ [ $ROWS => 1 ],
],
},
],
},
sqlbind => \[
- "( SELECT me.artistid, me.name, me.rank, me.charfield, (SELECT me.id FROM cd me LIMIT 1) FROM artist me )",
+ "( SELECT me.artistid, me.name, me.rank, me.charfield, (SELECT me.id FROM cd me LIMIT ?) FROM artist me )",
+ [ $ROWS => 1 ],
],
},
{ 'me' => 'artist' },
[
{ 'cds' => $cdrs->search({}, { 'select' => [\'me.artist as cds_artist' ]})->as_query },
- { 'me.artistid' => 'cds_artist' }
+ { 'me.artistid' => 'cds_artist' }
]
]
},
alias => 'cd2',
from => [
{ cd2 => $cdrs->search(
- { artist => { '>' => 20 } },
- {
+ { artist => { '>' => 20 } },
+ {
alias => 'cd3',
- from => [
+ from => [
{ cd3 => $cdrs->search( { artist => { '<' => 40 } } )->as_query }
],
}, )->as_query },
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my ($ROWS, $OFFSET) = (
+ DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
my $schema = DBICTest->init_schema();
GROUP BY me.cdid, me.artist - ?
HAVING me.artist < ?
ORDER BY me.artist * ?
- LIMIT 1 OFFSET 2
+ LIMIT ? OFFSET ?
)',
[
- [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
- => 666 ],
+ [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } => 666 ],
[ { dbic_colname => '_ne' } => 'bar' ],
[ { dbic_colname => '_add' } => 1 ],
- [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' }
- => 666 ],
+ [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } => 666 ],
[ { dbic_colname => '_ne' } => 'bar' ],
[ { dbic_colname => '_sub' } => 2 ],
[ { dbic_colname => '_lt' } => 3 ],
[ { dbic_colname => '_mu' } => 4 ],
+ [ $ROWS => 1 ],
+ [ $OFFSET => 2 ],
],
'Correct crazy sql',
);
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+my ($ROWS, $TOTAL, $OFFSET) = (
+ DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
+
my $schema = DBICTest->init_schema;
SELECT COUNT(*)
FROM books rownum__emulation
WHERE rownum__emulation.title < me.title
- ) < 2
+ ) < ?
ORDER BY me.title
)',
- [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
- => 'Library' ] ],
+ [
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+ [ $ROWS => 2 ],
+ ],
);
is_deeply (
SELECT COUNT(*)
FROM "books" "rownum__emulation"
WHERE "rownum__emulation"."title" > "me"."title"
- ) BETWEEN 1 AND 3
+ ) BETWEEN ? AND ?
ORDER BY "title" DESC
)',
- [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
- => 'Library' ] ],
+ [
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+ [ $OFFSET => 1 ],
+ [ $TOTAL => 3 ],
+ ],
);
is_deeply (
SELECT COUNT(*)
FROM "books" "rownum__emulation"
WHERE "rownum__emulation"."title" < "me"."title"
- ) BETWEEN 1 AND 4294967295
+ ) BETWEEN ? AND ?
ORDER BY "title"
)',
- [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
- => 'Library' ] ],
+ [
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+ [ $OFFSET => 1 ],
+ [ $TOTAL => 2147483647 ],
+ ],
);
is_deeply (
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my ($TOTAL, $OFFSET) = (
+ DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
my $schema = DBICTest->init_schema;
WHERE ( source = ? )
) me
) me
- WHERE rno__row__index BETWEEN 1 AND 1
+ WHERE rno__row__index >= ? AND rno__row__index <= ?
)',
- [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
- => 'Library' ] ],
+ [
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+ [ $OFFSET => 1 ],
+ [ $TOTAL => 1 ],
+ ],
);
$schema->storage->_sql_maker->quote_char ([qw/ [ ] /]);
WHERE ( [source] = ? )
) [me]
) [me]
- WHERE [rno__row__index] BETWEEN 1 AND 1
+ WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
)',
- [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
- => 'Library' ] ],
+ [
+ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
+ [ $OFFSET => 1 ],
+ [ $TOTAL => 1 ],
+ ],
);
{
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
+use DBIx::Class::SQLMaker::LimitDialects;
+
+my ($TOTAL, $OFFSET) = (
+ DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
+ DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
+);
my $s = DBICTest->init_schema (no_deploy => 1, );
$s->storage->sql_maker->limit_dialect ('RowNum');
SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh
FROM cd me
) me
- WHERE ROWNUM <= 4
+ WHERE ROWNUM <= ?
) me
- WHERE rownum__index >= 4
+ WHERE rownum__index >= ?
)',
- [],
+ [
+ [ $TOTAL => 4 ],
+ [ $OFFSET => 4 ],
+ ],
'Rownum subsel aliasing works correctly'
);
SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id
FROM cd me
) me
- WHERE ROWNUM <= 5
+ WHERE ROWNUM <= ?
) me
- WHERE rownum__index >= 4
+ WHERE rownum__index >= ?
)',
- [],
+ [
+ [ $TOTAL => 5 ],
+ [ $OFFSET => 4 ],
+ ],
'Rownum subsel aliasing works correctly'
);
projects / dbsrgits/DBIx-Class.git / commitdiff