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