use DBIC::SqlMakerTest;
my $schema = DBICTest->init_schema;
-$schema->storage_type('::DBI::MSSQL');
-# Trick the sqlite DB to use Top limit emulation
-# We could test all of this via $sq->$op directly,
-# but some conditions need a $rsrc
-delete $schema->storage->_sql_maker->{_cached_syntax};
$schema->storage->_sql_maker->limit_dialect ('RowNumberOver');
-my $rs = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 1, offset => 3 });
+my $rs_selectas_col = $schema->resultset ('BooksInLibrary')->search ({}, {
+ '+select' => ['owner.name'],
+ '+as' => ['owner.name'],
+ join => 'owner',
+ rows => 1,
+});
-sub default_test_order {
- my $order_by = shift;
- is_same_sql_bind(
- $rs->search ({}, {order_by => $order_by})->as_query,
- "(SELECT me__id, me__source, me__owner, me__title, me__price, owner__id, owner__name FROM
- (
- SELECT Q1.*, ROW_NUMBER() OVER( ) AS ROW_NUM FROM (
- SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name
- FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? )
- ) Q1
- ) Q2
- WHERE ROW_NUM BETWEEN 4 and 4
- )",
- [ [ source => 'Library' ] ],
- );
-}
-
-sub test_order {
- my $args = shift;
-
- my $req_order = $args->{order_req}
- ? "ORDER BY $args->{order_req}"
- : ''
- ;
-
- is_same_sql_bind(
- $rs->search ({}, {order_by => $args->{order_by}})->as_query,
- "(SELECT me__id, me__source, me__owner, me__title, me__price, owner__id, owner__name FROM
- (
- SELECT Q1.*, ROW_NUMBER() OVER( ) AS ROW_NUM FROM (
- SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name
- FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? )
- $req_order
- ) Q1
- ) Q2
- WHERE ROW_NUM BETWEEN 4 and 4
- )",
- [ [ source => 'Library' ] ],
- );
-}
-
-my @tests = (
- {
- order_by => \'foo DESC',
- order_req => 'foo DESC',
- order_inner => 'foo DESC',
- order_outer => 'foo ASC'
- },
- {
- order_by => { -asc => 'foo' },
- order_req => 'foo ASC',
- order_inner => 'foo ASC',
- order_outer => 'foo DESC',
- },
- {
- order_by => 'foo',
- order_req => 'foo',
- order_inner => 'foo ASC',
- order_outer => 'foo DESC',
- },
- {
- order_by => [ qw{ foo bar} ],
- order_req => 'foo, bar',
- order_inner => 'foo ASC,bar ASC',
- order_outer => 'foo DESC, bar DESC',
- },
- {
- order_by => { -desc => 'foo' },
- order_req => 'foo DESC',
- order_inner => 'foo DESC',
- order_outer => 'foo ASC',
- },
- {
- order_by => ['foo', { -desc => 'bar' } ],
- order_req => 'foo, bar DESC',
- order_inner => 'foo ASC, bar DESC',
- order_outer => 'foo DESC, bar ASC',
- },
- {
- order_by => { -asc => [qw{ foo bar }] },
- order_req => 'foo ASC, bar ASC',
- order_inner => 'foo ASC, bar ASC',
- order_outer => 'foo DESC, bar DESC',
- },
- {
- order_by => [
- { -asc => 'foo' },
- { -desc => [qw{bar}] },
- { -asc => [qw{hello sensors}]},
- ],
- order_req => 'foo ASC, bar DESC, hello ASC, sensors ASC',
- order_inner => 'foo ASC, bar DESC, hello ASC, sensors ASC',
- order_outer => 'foo DESC, bar ASC, hello DESC, sensors DESC',
- },
+is_same_sql_bind(
+ $rs_selectas_col->as_query,
+ '(
+ SELECT id, source, owner, title, price,
+ owner__name
+ FROM (
+ SELECT id, source, owner, title, price,
+ owner__name,
+ ROW_NUMBER() OVER( ) AS rno__row__index
+ FROM (
+ SELECT me.id, me.source, me.owner, me.title, me.price,
+ owner.name AS owner__name
+ FROM books me
+ JOIN owners owner ON owner.id = me.owner
+ WHERE ( source = ? )
+ ) me
+ ) me
+ WHERE rno__row__index BETWEEN 1 AND 1
+ )',
+ [ [ 'source', 'Library' ] ],
);
-my @default_tests = ( undef, '', {}, [] );
+$schema->storage->_sql_maker->quote_char ([qw/ [ ] /]);
+$schema->storage->_sql_maker->name_sep ('.');
-plan (tests => scalar @tests + scalar @default_tests + 1);
+my $rs_selectas_rel = $schema->resultset ('BooksInLibrary')->search ({}, {
+ '+select' => ['owner.name'],
+ '+as' => ['owner_name'],
+ join => 'owner',
+ rows => 1,
+});
-test_order ($_) for @tests;
-default_test_order ($_) for @default_tests;
-
-
-is_same_sql_bind (
- $rs->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
-'(SELECT
-me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name FROM
- ( SELECT
- id, source, owner, title, price FROM
- ( SELECT
- TOP 1 id, source, owner, title, price FROM
- ( SELECT
- TOP 4 me.id, me.source, me.owner, me.title, me.price FROM
- books me JOIN
- owners owner ON owner.id = me.owner
- WHERE ( source = ? )
- GROUP BY title
- ORDER BY title ASC
- ) me
- ORDER BY title DESC
- ) me
- ORDER BY title
- ) me JOIN
- owners owner ON owner.id = me.owner WHERE
- ( source = ? )
- ORDER BY title)' ,
- [ [ source => 'Library' ], [ source => 'Library' ] ],
+is_same_sql_bind(
+ $rs_selectas_rel->as_query,
+ '(
+ SELECT [id], [source], [owner], [title], [price],
+ [owner_name]
+ FROM (
+ SELECT [id], [source], [owner], [title], [price],
+ [owner_name],
+ ROW_NUMBER() OVER( ) AS [rno__row__index]
+ FROM (
+ SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
+ [owner].[name] AS [owner_name]
+ FROM [books] [me]
+ JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
+ WHERE ( [source] = ? )
+ ) [me]
+ ) [me]
+ WHERE [rno__row__index] BETWEEN 1 AND 1
+ )',
+ [ [ 'source', 'Library' ] ],
);
+
+done_testing;
projects / dbsrgits/DBIx-Class.git / blobdiff