t/73oracle_hq.t

   1 use strict;
   2 use warnings;
   3
   4 use Test::Exception;
   5 use Test::More;
   6
   7 use lib qw(t/lib);
   8 use DBIC::SqlMakerTest;
   9
  10 $ENV{NLS_SORT} = "BINARY";
  11 $ENV{NLS_COMP} = "BINARY";
  12 $ENV{NLS_LANG} = "AMERICAN";
  13
  14 my ($dsn,  $user,  $pass)  = @ENV{map { "DBICTEST_ORA_${_}" }  qw/DSN USER PASS/};
  15
  16 plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test.'
  17  unless ($dsn && $user && $pass);
  18
  19 use DBICTest::Schema::Artist;
  20 BEGIN {
  21   DBICTest::Schema::Artist->add_column('parentid');
  22
  23   DBICTest::Schema::Artist->has_many(
  24     children => 'DBICTest::Schema::Artist',
  25     { 'foreign.parentid' => 'self.artistid' }
  26   );
  27
  28   DBICTest::Schema::Artist->belongs_to(
  29     parent => 'DBICTest::Schema::Artist',
  30     { 'foreign.artistid' => 'self.parentid' }
  31   );
  32 }
  33
  34 use DBICTest::Schema;
  35
  36 my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
  37
  38 note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};
  39
  40 my $dbh = $schema->storage->dbh;
  41 do_creates($dbh);
  42
  43 ### test hierarchical queries
  44 {
  45   $schema->resultset('Artist')->create ({
  46     name => 'root',
  47     rank => 1,
  48     cds => [],
  49     children => [
  50       {
  51         name => 'child1',
  52         rank => 2,
  53         children => [
  54           {
  55             name => 'grandchild',
  56             rank => 3,
  57             cds => [
  58               {
  59                 title => "grandchilds's cd" ,
  60                 year => '2008',
  61                 tracks => [
  62                   {
  63                     position => 1,
  64                     title => 'Track 1 grandchild',
  65                   }
  66                 ],
  67               }
  68             ],
  69             children => [
  70               {
  71                 name => 'greatgrandchild',
  72                 rank => 3,
  73               }
  74             ],
  75           }
  76         ],
  77       },
  78       {
  79         name => 'child2',
  80         rank => 3,
  81       },
  82     ],
  83   });
  84
  85   $schema->resultset('Artist')->create({
  86     name => 'cycle-root',
  87     children => [
  88       {
  89         name => 'cycle-child1',
  90         children => [ { name => 'cycle-grandchild' } ],
  91       },
  92       {
  93         name => 'cycle-child2'
  94       },
  95     ],
  96   });
  97
  98   $schema->resultset('Artist')->find({ name => 'cycle-root' })
  99     ->update({ parentid => { -ident => 'artistid' } });
 100
 101   # select the whole tree
 102   {
 103     my $rs = $schema->resultset('Artist')->search({}, {
 104       start_with => { name => 'root' },
 105       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 106     });
 107
 108     is_same_sql_bind (
 109       $rs->as_query,
 110       '(
 111         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
 112           FROM artist me
 113         START WITH name = ?
 114         CONNECT BY parentid = PRIOR artistid
 115       )',
 116       [ [ name => 'root'] ],
 117     );
 118     is_deeply (
 119       [ $rs->get_column ('name')->all ],
 120       [ qw/root child1 grandchild greatgrandchild child2/ ],
 121       'got artist tree',
 122     );
 123
 124     is_same_sql_bind (
 125       $rs->count_rs->as_query,
 126       '(
 127         SELECT COUNT( * )
 128           FROM artist me
 129         START WITH name = ?
 130         CONNECT BY parentid = PRIOR artistid
 131       )',
 132       [ [ name => 'root'] ],
 133     );
 134
 135     is( $rs->count, 5, 'Connect By count ok' );
 136   }
 137
 138   # use order siblings by statement
 139   SKIP: {
 140     # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
 141     skip q{Oracle8i doesn't support ORDER SIBLINGS BY}, 1
 142       if $schema->storage->_server_info->{normalized_dbms_version} < 9;
 143
 144     my $rs = $schema->resultset('Artist')->search({}, {
 145       start_with => { name => 'root' },
 146       connect_by => { parentid => { -prior => { -ident =>  'artistid' } } },
 147       order_siblings_by => { -desc => 'name' },
 148     });
 149
 150     is_same_sql_bind (
 151       $rs->as_query,
 152       '(
 153         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
 154           FROM artist me
 155         START WITH name = ?
 156         CONNECT BY parentid = PRIOR artistid
 157         ORDER SIBLINGS BY name DESC
 158       )',
 159       [ [ name => 'root'] ],
 160     );
 161
 162     is_deeply (
 163       [ $rs->get_column ('name')->all ],
 164       [ qw/root child2 child1 grandchild greatgrandchild/ ],
 165       'Order Siblings By ok',
 166     );
 167   }
 168
 169   # get the root node
 170   {
 171     my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
 172       start_with => { name => 'root' },
 173       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 174     });
 175
 176     is_same_sql_bind (
 177       $rs->as_query,
 178       '(
 179         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
 180           FROM artist me
 181         WHERE ( parentid IS NULL )
 182         START WITH name = ?
 183         CONNECT BY parentid = PRIOR artistid
 184       )',
 185       [ [ name => 'root'] ],
 186     );
 187
 188     is_deeply(
 189       [ $rs->get_column('name')->all ],
 190       [ 'root' ],
 191       'found root node',
 192     );
 193   }
 194
 195   # combine a connect by with a join
 196   SKIP: {
 197     # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
 198     skip q{Oracle8i doesn't support connect by with join}, 1
 199       if $schema->storage->_server_info->{normalized_dbms_version} < 9;
 200
 201     my $rs = $schema->resultset('Artist')->search(
 202       {'cds.title' => { -like => '%cd'} },
 203       {
 204         join => 'cds',
 205         start_with => { 'me.name' => 'root' },
 206         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 207       }
 208     );
 209
 210     is_same_sql_bind (
 211       $rs->as_query,
 212       '(
 213         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
 214           FROM artist me
 215           LEFT JOIN cd cds ON cds.artist = me.artistid
 216         WHERE ( cds.title LIKE ? )
 217         START WITH me.name = ?
 218         CONNECT BY parentid = PRIOR artistid
 219       )',
 220       [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
 221     );
 222
 223     is_deeply(
 224       [ $rs->get_column('name')->all ],
 225       [ 'grandchild' ],
 226       'Connect By with a join result name ok'
 227     );
 228
 229     is_same_sql_bind (
 230       $rs->count_rs->as_query,
 231       '(
 232         SELECT COUNT( * )
 233           FROM artist me
 234           LEFT JOIN cd cds ON cds.artist = me.artistid
 235         WHERE ( cds.title LIKE ? )
 236         START WITH me.name = ?
 237         CONNECT BY parentid = PRIOR artistid
 238       )',
 239       [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
 240     );
 241
 242     is( $rs->count, 1, 'Connect By with a join; count ok' );
 243   }
 244
 245   # combine a connect by with order_by
 246   {
 247     my $rs = $schema->resultset('Artist')->search({}, {
 248       start_with => { name => 'root' },
 249       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 250       order_by => { -asc => [ 'LEVEL', 'name' ] },
 251     });
 252
 253     is_same_sql_bind (
 254       $rs->as_query,
 255       '(
 256         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
 257           FROM artist me
 258         START WITH name = ?
 259         CONNECT BY parentid = PRIOR artistid
 260         ORDER BY LEVEL ASC, name ASC
 261       )',
 262       [ [ name => 'root' ] ],
 263     );
 264
 265
 266     # Don't use "$rs->get_column ('name')->all" they build a query arround the $rs.
 267     #   If $rs has a order by, the order by is in the subquery and this doesn't work with Oracle 8i.
 268     # TODO: write extra test and fix order by handling on Oracle 8i
 269     is_deeply (
 270       [ map { $_->[1] } $rs->cursor->all ],
 271       [ qw/root child1 child2 grandchild greatgrandchild/ ],
 272       'Connect By with a order_by - result name ok (without get_column)'
 273     );
 274
 275     SKIP: {
 276       skip q{Connect By with a order_by - result name ok (with get_column), Oracle8i doesn't support order by in a subquery},1
 277         if $schema->storage->_server_info->{normalized_dbms_version} < 9;
 278       is_deeply (
 279         [  $rs->get_column ('name')->all ],
 280         [ qw/root child1 child2 grandchild greatgrandchild/ ],
 281         'Connect By with a order_by - result name ok (with get_column)'
 282       );
 283     }
 284   }
 285
 286
 287   # limit a connect by
 288   SKIP: {
 289     skip q{Oracle8i doesn't support order by in a subquery}, 1
 290       if $schema->storage->_server_info->{normalized_dbms_version} < 9;
 291
 292     my $rs = $schema->resultset('Artist')->search({}, {
 293       start_with => { name => 'root' },
 294       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 295       order_by => { -asc => 'name' },
 296       rows => 2,
 297     });
 298
 299     is_same_sql_bind (
 300       $rs->as_query,
 301       '(
 302         SELECT artistid, name, rank, charfield, parentid
 303           FROM (
 304             SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
 305               FROM artist me
 306             START WITH name = ?
 307             CONNECT BY parentid = PRIOR artistid
 308             ORDER BY name ASC
 309           ) me
 310         WHERE ROWNUM <= 2
 311       )',
 312       [ [ name => 'root' ] ],
 313     );
 314
 315     is_deeply (
 316       [ $rs->get_column ('name')->all ],
 317       [qw/child1 child2/],
 318       'LIMIT a Connect By query - correct names'
 319     );
 320
 321     is_same_sql_bind (
 322       $rs->count_rs->as_query,
 323       '(
 324         SELECT COUNT( * )
 325           FROM (
 326             SELECT artistid
 327               FROM (
 328                 SELECT me.artistid
 329                   FROM artist me
 330                 START WITH name = ?
 331                 CONNECT BY parentid = PRIOR artistid
 332               ) me
 333             WHERE ROWNUM <= 2
 334           ) me
 335       )',
 336       [ [ name => 'root' ] ],
 337     );
 338
 339     is( $rs->count, 2, 'Connect By; LIMIT count ok' );
 340   }
 341
 342   # combine a connect_by with group_by and having
 343   {
 344     my $rs = $schema->resultset('Artist')->search({}, {
 345       select => { count => 'rank', -as => 'cnt' },
 346       start_with => { name => 'root' },
 347       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 348       group_by => ['rank'],
 349       having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
 350     });
 351
 352     is_same_sql_bind (
 353       $rs->as_query,
 354       '(
 355         SELECT COUNT(rank) AS cnt
 356           FROM artist me
 357         START WITH name = ?
 358         CONNECT BY parentid = PRIOR artistid
 359         GROUP BY rank HAVING count(rank) < ?
 360       )',
 361       [ [ name => 'root' ], [ cnt => 2 ] ],
 362     );
 363
 364     is_deeply (
 365       [ $rs->get_column ('cnt')->all ],
 366       [1, 1],
 367       'Group By a Connect By query - correct values'
 368     );
 369   }
 370
 371   # select the whole cycle tree without nocylce
 372   {
 373     my $rs = $schema->resultset('Artist')->search({}, {
 374       start_with => { name => 'cycle-root' },
 375       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 376     });
 377
 378     # ORA-01436:  CONNECT BY loop in user data
 379     throws_ok { $rs->get_column ('name')->all } qr/ORA-01436/,
 380       "connect by initify loop detection without nocycle";
 381   }
 382
 383   # select the whole cycle tree with nocylce
 384   SKIP: {
 385     # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023748
 386     skip q{Oracle8i doesn't support connect by nocycle}, 1
 387       if $schema->storage->_server_info->{normalized_dbms_version} < 9;
 388
 389     my $rs = $schema->resultset('Artist')->search({}, {
 390       start_with => { name => 'cycle-root' },
 391       '+select'  => \ 'CONNECT_BY_ISCYCLE',
 392       '+as'      => [ 'connector' ],
 393       connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
 394     });
 395
 396     is_same_sql_bind (
 397       $rs->as_query,
 398       '(
 399         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
 400           FROM artist me
 401         START WITH name = ?
 402         CONNECT BY NOCYCLE parentid = PRIOR artistid
 403       )',
 404       [ [ name => 'cycle-root'] ],
 405     );
 406     is_deeply (
 407       [ $rs->get_column ('name')->all ],
 408       [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
 409       'got artist tree with nocycle (name)',
 410     );
 411     is_deeply (
 412       [ $rs->get_column ('connector')->all ],
 413       [ qw/1 0 0 0/ ],
 414       'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
 415     );
 416
 417     is_same_sql_bind (
 418       $rs->count_rs->as_query,
 419       '(
 420         SELECT COUNT( * )
 421           FROM artist me
 422         START WITH name = ?
 423         CONNECT BY NOCYCLE parentid = PRIOR artistid
 424       )',
 425       [ [ name => 'cycle-root'] ],
 426     );
 427
 428     is( $rs->count, 4, 'Connect By Nocycle count ok' );
 429   }
 430 }
 431
 432 done_testing;
 433
 434 sub do_creates {
 435   my $dbh = shift;
 436
 437   eval {
 438     $dbh->do("DROP SEQUENCE artist_autoinc_seq");
 439     $dbh->do("DROP SEQUENCE artist_pk_seq");
 440     $dbh->do("DROP SEQUENCE cd_seq");
 441     $dbh->do("DROP SEQUENCE track_seq");
 442     $dbh->do("DROP TABLE artist");
 443     $dbh->do("DROP TABLE track");
 444     $dbh->do("DROP TABLE cd");
 445   };
 446
 447   $dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
 448   $dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
 449   $dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
 450
 451   $dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))");
 452   $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
 453
 454   $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
 455   $dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
 456
 457   $dbh->do("CREATE TABLE track (trackid NUMBER(12), cd NUMBER(12) REFERENCES cd(cdid) DEFERRABLE, position NUMBER(12), title VARCHAR(255), last_updated_on DATE, last_updated_at DATE, small_dt DATE)");
 458   $dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");
 459
 460   $dbh->do(qq{
 461     CREATE OR REPLACE TRIGGER artist_insert_trg_pk
 462     BEFORE INSERT ON artist
 463     FOR EACH ROW
 464       BEGIN
 465         IF :new.artistid IS NULL THEN
 466           SELECT artist_pk_seq.nextval
 467           INTO :new.artistid
 468           FROM DUAL;
 469         END IF;
 470       END;
 471   });
 472   $dbh->do(qq{
 473     CREATE OR REPLACE TRIGGER cd_insert_trg
 474     BEFORE INSERT OR UPDATE ON cd
 475     FOR EACH ROW
 476
 477       DECLARE
 478       tmpVar NUMBER;
 479
 480       BEGIN
 481         tmpVar := 0;
 482
 483         IF :new.cdid IS NULL THEN
 484           SELECT cd_seq.nextval
 485           INTO tmpVar
 486           FROM dual;
 487
 488           :new.cdid := tmpVar;
 489         END IF;
 490       END;
 491   });
 492   $dbh->do(qq{
 493     CREATE OR REPLACE TRIGGER track_insert_trg
 494     BEFORE INSERT ON track
 495     FOR EACH ROW
 496       BEGIN
 497         IF :new.trackid IS NULL THEN
 498           SELECT track_seq.nextval
 499           INTO :new.trackid
 500           FROM DUAL;
 501         END IF;
 502       END;
 503   });
 504 }
 505
 506 # clean up our mess
 507 END {
 508   eval {
 509     my $dbh = $schema->storage->dbh;
 510     $dbh->do("DROP SEQUENCE artist_pk_seq");
 511     $dbh->do("DROP SEQUENCE cd_seq");
 512     $dbh->do("DROP SEQUENCE track_seq");
 513     $dbh->do("DROP TABLE artist");
 514     $dbh->do("DROP TABLE track");
 515     $dbh->do("DROP TABLE cd");
 516   };
 517 }