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