t/sqlmaker/hierarchical/oracle.t

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