t/sqlahacks/limit_dialects/rownumberover.t

   1 use strict;
   2 use warnings;
   3
   4 use Test::More;
   5 use lib qw(t/lib);
   6 use DBICTest;
   7 use DBIC::SqlMakerTest;
   8
   9 my $schema = DBICTest->init_schema;
  10
  11 delete $schema->storage->_sql_maker->{_cached_syntax};
  12 $schema->storage->_sql_maker->limit_dialect ('RowNumberOver');
  13
  14 my $rs_selectas_col = $schema->resultset ('BooksInLibrary')->search ({}, { '+select' => ['owner.name'], '+as' => ['owner.name'], join => 'owner', rows => 1 });
  15
  16 is_same_sql_bind( $rs_selectas_col->search({})->as_query,
  17                   "(SELECT
  18                       me.id, me.source, me.owner, me.title, me.price,
  19                       owner.name
  20                     FROM
  21                       (SELECT me.*,
  22                        ROW_NUMBER() OVER( ) AS rno__row__index
  23                        FROM
  24                          (SELECT me.id, me.source, me.owner, me.title, me.price, owner.name
  25                           FROM books me
  26                           JOIN owners owner ON owner.id = me.owner
  27                           WHERE ( source = ? )
  28                          ) me
  29                        ) me
  30                     JOIN owners owner ON owner.id = me.owner
  31                     WHERE rno__row__index BETWEEN 1 AND 1 )",
  32                   [  [ 'source', 'Library' ] ],
  33                 );
  34
  35
  36 my $rs_selectas_rel = $schema->resultset ('BooksInLibrary')->search ({}, { '+select' => ['owner.name'], '+as' => ['owner_name'], join => 'owner', rows => 1 });
  37
  38 is_same_sql_bind( $rs_selectas_rel->search({})->as_query,
  39                   "(SELECT
  40                       me.id, me.source, me.owner, me.title, me.price,
  41                       owner.name
  42                     FROM
  43                       (SELECT me.*,
  44                        ROW_NUMBER() OVER( ) AS rno__row__index
  45                        FROM
  46                          (SELECT me.id, me.source, me.owner, me.title, me.price, owner.name
  47                           FROM books me
  48                           JOIN owners owner ON owner.id = me.owner
  49                           WHERE ( source = ? )
  50                          ) me
  51                        ) me
  52                     JOIN owners owner ON owner.id = me.owner
  53                     WHERE rno__row__index BETWEEN 1 AND 1 )",
  54                   [  [ 'source', 'Library' ] ],
  55                 );
  56
  57
  58 done_testing;