t/sqlmaker/hierarchical/oracle.t

   1 use strict;
   2 use warnings;
   3
   4 use Test::More;
   5 use lib qw(t/lib);
   6
   7 use DBIx::Class::Optional::Dependencies;
   8 plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('id_shortener')
   9   unless DBIx::Class::Optional::Dependencies->req_ok_for ('id_shortener');
  10
  11 use DBICTest::Schema::Artist;
  12 BEGIN {
  13   DBICTest::Schema::Artist->add_column('parentid');
  14
  15   DBICTest::Schema::Artist->has_many(
  16     children => 'DBICTest::Schema::Artist',
  17     { 'foreign.parentid' => 'self.artistid' }
  18   );
  19
  20   DBICTest::Schema::Artist->belongs_to(
  21     parent => 'DBICTest::Schema::Artist',
  22     { 'foreign.artistid' => 'self.parentid' }
  23   );
  24 }
  25
  26 use DBICTest;
  27 use DBICTest::Schema;
  28 use DBIC::SqlMakerTest;
  29
  30 use DBIx::Class::SQLMaker::LimitDialects;
  31 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
  32 my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
  33
  34 for my $q ( '', '"' ) {
  35
  36   my $schema = DBICTest->init_schema(
  37     storage_type => 'DBIx::Class::Storage::DBI::Oracle::Generic',
  38     no_deploy => 1,
  39     quote_char => $q,
  40   );
  41
  42   # select the whole tree
  43   {
  44     my $rs = $schema->resultset('Artist')->search({}, {
  45       start_with => { name => 'root' },
  46       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
  47     });
  48
  49     is_same_sql_bind (
  50       $rs->as_query,
  51       "(
  52         SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}
  53           FROM ${q}artist${q} ${q}me${q}
  54         START WITH ${q}name${q} = ?
  55         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
  56       )",
  57       [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
  58             => 'root'] ],
  59     );
  60
  61     is_same_sql_bind (
  62       $rs->count_rs->as_query,
  63       "(
  64         SELECT COUNT( * )
  65           FROM ${q}artist${q} ${q}me${q}
  66         START WITH ${q}name${q} = ?
  67         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
  68       )",
  69       [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
  70             => 'root'] ],
  71     );
  72   }
  73
  74   # use order siblings by statement
  75   {
  76     my $rs = $schema->resultset('Artist')->search({}, {
  77       start_with => { name => 'root' },
  78       connect_by => { parentid => { -prior => { -ident =>  'artistid' } } },
  79       order_siblings_by => { -desc => 'name' },
  80     });
  81
  82     is_same_sql_bind (
  83       $rs->as_query,
  84       "(
  85         SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}
  86           FROM ${q}artist${q} ${q}me${q}
  87         START WITH ${q}name${q} = ?
  88         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
  89         ORDER SIBLINGS BY ${q}name${q} DESC
  90       )",
  91       [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
  92             => 'root'] ],
  93     );
  94   }
  95
  96   # get the root node
  97   {
  98     my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
  99       start_with => { name => 'root' },
 100       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 101     });
 102
 103     is_same_sql_bind (
 104       $rs->as_query,
 105       "(
 106         SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}
 107           FROM ${q}artist${q} ${q}me${q}
 108         WHERE ( ${q}parentid${q} IS NULL )
 109         START WITH ${q}name${q} = ?
 110         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
 111       )",
 112       [ [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
 113             => 'root'] ],
 114     );
 115   }
 116
 117   # combine a connect by with a join
 118   {
 119     my $rs = $schema->resultset('Artist')->search(
 120       {'cds.title' => { -like => '%cd'} },
 121       {
 122         join => 'cds',
 123         start_with => { 'me.name' => 'root' },
 124         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 125       }
 126     );
 127
 128     is_same_sql_bind (
 129       $rs->as_query,
 130       "(
 131         SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}
 132           FROM ${q}artist${q} ${q}me${q}
 133           LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q}
 134         WHERE ( ${q}cds${q}.${q}title${q} LIKE ? )
 135         START WITH ${q}me${q}.${q}name${q} = ?
 136         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
 137       )",
 138       [
 139         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
 140             => '%cd'],
 141         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
 142             => 'root'],
 143       ],
 144     );
 145
 146     is_same_sql_bind (
 147       $rs->count_rs->as_query,
 148       "(
 149         SELECT COUNT( * )
 150           FROM ${q}artist${q} ${q}me${q}
 151           LEFT JOIN cd ${q}cds${q} ON ${q}cds${q}.${q}artist${q} = ${q}me${q}.${q}artistid${q}
 152         WHERE ( ${q}cds${q}.${q}title${q} LIKE ? )
 153         START WITH ${q}me${q}.${q}name${q} = ?
 154         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
 155       )",
 156       [
 157         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'cds.title', 'sqlt_size' => 100 }
 158             => '%cd'],
 159         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'me.name', 'sqlt_size' => 100 }
 160               => 'root'],
 161       ],
 162     );
 163   }
 164
 165   # combine a connect by with order_by
 166   {
 167     my $rs = $schema->resultset('Artist')->search({}, {
 168       start_with => { name => 'root' },
 169       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 170       order_by => { -asc => [ 'LEVEL', 'name' ] },
 171     });
 172
 173     is_same_sql_bind (
 174       $rs->as_query,
 175       "(
 176         SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}
 177           FROM ${q}artist${q} ${q}me${q}
 178         START WITH ${q}name${q} = ?
 179         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
 180         ORDER BY ${q}LEVEL${q} ASC, ${q}name${q} ASC
 181       )",
 182       [
 183         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
 184             => 'root'],
 185       ],
 186     );
 187   }
 188
 189   # limit a connect by
 190   {
 191     my $rs = $schema->resultset('Artist')->search({}, {
 192       start_with => { name => 'root' },
 193       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 194       order_by => [ { -asc => 'name' }, {  -desc => 'artistid' } ],
 195       rows => 2,
 196     });
 197
 198     is_same_sql_bind (
 199       $rs->as_query,
 200       "(
 201         SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}
 202           FROM (
 203             SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}
 204               FROM ${q}artist${q} ${q}me${q}
 205             START WITH ${q}name${q} = ?
 206             CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
 207             ORDER BY ${q}name${q} ASC, ${q}artistid${q} DESC
 208           ) ${q}me${q}
 209         WHERE ROWNUM <= ?
 210       )",
 211       [
 212         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
 213             => 'root'], [ $ROWS => 2 ],
 214       ],
 215     );
 216
 217     is_same_sql_bind (
 218       $rs->count_rs->as_query,
 219       "(
 220         SELECT COUNT( * )
 221           FROM (
 222             SELECT ${q}me${q}.${q}artistid${q}
 223               FROM (
 224                 SELECT ${q}me${q}.${q}artistid${q}
 225                   FROM ${q}artist${q} ${q}me${q}
 226                 START WITH ${q}name${q} = ?
 227                 CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
 228               ) ${q}me${q}
 229             WHERE ROWNUM <= ?
 230           ) ${q}me${q}
 231       )",
 232       [
 233         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
 234             => 'root'],
 235         [ $ROWS => 2 ],
 236       ],
 237     );
 238   }
 239
 240   # combine a connect_by with group_by and having
 241   # add some bindvals to make sure things still work
 242   {
 243     my $rs = $schema->resultset('Artist')->search({}, {
 244       select => \[ 'COUNT(rank) + ?', [ __cbind => 3 ] ],
 245       as => 'cnt',
 246       start_with => { name => 'root' },
 247       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 248       group_by => \[ 'rank + ? ', [ __gbind =>  1] ],
 249       having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
 250     });
 251
 252     is_same_sql_bind (
 253       $rs->as_query,
 254       "(
 255         SELECT COUNT(rank) + ?
 256           FROM ${q}artist${q} ${q}me${q}
 257         START WITH ${q}name${q} = ?
 258         CONNECT BY ${q}parentid${q} = PRIOR ${q}artistid${q}
 259         GROUP BY( rank + ? )
 260         HAVING count(rank) < ?
 261       )",
 262       [
 263         [ { dbic_colname => '__cbind' }
 264             => 3 ],
 265         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
 266             => 'root'],
 267         [ { dbic_colname => '__gbind' }
 268             => 1 ],
 269         [ { dbic_colname => 'cnt' }
 270             => 2 ],
 271       ],
 272     );
 273   }
 274
 275   # select the whole cycle tree with nocylce
 276   {
 277     my $rs = $schema->resultset('Artist')->search({}, {
 278       start_with => { name => 'cycle-root' },
 279       '+select'  => \ 'CONNECT_BY_ISCYCLE',
 280       '+as'      => [ 'connector' ],
 281       connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
 282     });
 283
 284     is_same_sql_bind (
 285       $rs->as_query,
 286       "(
 287         SELECT ${q}me${q}.${q}artistid${q}, ${q}me${q}.${q}name${q}, ${q}me${q}.${q}rank${q}, ${q}me${q}.${q}charfield${q}, ${q}me${q}.${q}parentid${q}, CONNECT_BY_ISCYCLE
 288           FROM ${q}artist${q} ${q}me${q}
 289         START WITH ${q}name${q} = ?
 290         CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q}
 291       )",
 292       [
 293         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
 294             => 'cycle-root'],
 295       ],
 296     );
 297
 298     is_same_sql_bind (
 299       $rs->count_rs->as_query,
 300       "(
 301         SELECT COUNT( * )
 302           FROM ${q}artist${q} ${q}me${q}
 303         START WITH ${q}name${q} = ?
 304         CONNECT BY NOCYCLE ${q}parentid${q} = PRIOR ${q}artistid${q}
 305       )",
 306       [
 307         [ { 'sqlt_datatype' => 'varchar', 'dbic_colname' => 'name', 'sqlt_size' => 100 }
 308             => 'cycle-root'],
 309       ],
 310     );
 311   }
 312 }
 313
 314 done_testing;