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   {
 347     my $rs = $schema->resultset('Artist')->search({}, {
 348       select => { count => 'rank', -as => 'cnt' },
 349       start_with => { name => 'root' },
 350       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 351       group_by => ['rank'],
 352       having => \[ 'count(rank) < ?', [ cnt => 2 ] ],
 353     });
 354
 355     is_same_sql_bind (
 356       $rs->as_query,
 357       '(
 358         SELECT COUNT(rank) AS cnt
 359           FROM artist me
 360         START WITH name = ?
 361         CONNECT BY parentid = PRIOR artistid
 362         GROUP BY rank HAVING count(rank) < ?
 363       )',
 364       [ [ name => 'root' ], [ cnt => 2 ] ],
 365     );
 366
 367     is_deeply (
 368       [ $rs->get_column ('cnt')->all ],
 369       [1, 1],
 370       'Group By a Connect By query - correct values'
 371     );
 372   }
 373
 374   # select the whole cycle tree without nocylce
 375   {
 376     my $rs = $schema->resultset('Artist')->search({}, {
 377       start_with => { name => 'cycle-root' },
 378       connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
 379     });
 380
 381     # ORA-01436:  CONNECT BY loop in user data
 382     throws_ok { $rs->get_column ('name')->all } qr/ORA-01436/,
 383       "connect by initify loop detection without nocycle";
 384   }
 385
 386   # select the whole cycle tree with nocylce
 387   SKIP: {
 388     # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023748
 389     skip q{Oracle8i doesn't support connect by nocycle}, 1
 390       if $schema->storage->_server_info->{normalized_dbms_version} < 9;
 391
 392     my $rs = $schema->resultset('Artist')->search({}, {
 393       start_with => { name => 'cycle-root' },
 394       '+select'  => \ 'CONNECT_BY_ISCYCLE',
 395       '+as'      => [ 'connector' ],
 396       connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
 397     });
 398
 399     is_same_sql_bind (
 400       $rs->as_query,
 401       '(
 402         SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
 403           FROM artist me
 404         START WITH name = ?
 405         CONNECT BY NOCYCLE parentid = PRIOR artistid
 406       )',
 407       [ [ name => 'cycle-root'] ],
 408     );
 409     is_deeply (
 410       [ $rs->get_column ('name')->all ],
 411       [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
 412       'got artist tree with nocycle (name)',
 413     );
 414     is_deeply (
 415       [ $rs->get_column ('connector')->all ],
 416       [ qw/1 0 0 0/ ],
 417       'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
 418     );
 419
 420     is_same_sql_bind (
 421       $rs->count_rs->as_query,
 422       '(
 423         SELECT COUNT( * )
 424           FROM artist me
 425         START WITH name = ?
 426         CONNECT BY NOCYCLE parentid = PRIOR artistid
 427       )',
 428       [ [ name => 'cycle-root'] ],
 429     );
 430
 431     is( $rs->count, 4, 'Connect By Nocycle count ok' );
 432   }
 433 }
 434
 435 done_testing;
 436
 437 sub do_creates {
 438   my $dbh = shift;
 439
 440   eval {
 441     $dbh->do("DROP SEQUENCE artist_autoinc_seq");
 442     $dbh->do("DROP SEQUENCE artist_pk_seq");
 443     $dbh->do("DROP SEQUENCE cd_seq");
 444     $dbh->do("DROP SEQUENCE track_seq");
 445     $dbh->do("DROP TABLE artist");
 446     $dbh->do("DROP TABLE track");
 447     $dbh->do("DROP TABLE cd");
 448   };
 449
 450   $dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
 451   $dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
 452   $dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
 453
 454   $dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))");
 455   $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
 456
 457   $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
 458   $dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
 459
 460   $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)");
 461   $dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");
 462
 463   $dbh->do(qq{
 464     CREATE OR REPLACE TRIGGER artist_insert_trg_pk
 465     BEFORE INSERT ON artist
 466     FOR EACH ROW
 467       BEGIN
 468         IF :new.artistid IS NULL THEN
 469           SELECT artist_pk_seq.nextval
 470           INTO :new.artistid
 471           FROM DUAL;
 472         END IF;
 473       END;
 474   });
 475   $dbh->do(qq{
 476     CREATE OR REPLACE TRIGGER cd_insert_trg
 477     BEFORE INSERT OR UPDATE ON cd
 478     FOR EACH ROW
 479
 480       DECLARE
 481       tmpVar NUMBER;
 482
 483       BEGIN
 484         tmpVar := 0;
 485
 486         IF :new.cdid IS NULL THEN
 487           SELECT cd_seq.nextval
 488           INTO tmpVar
 489           FROM dual;
 490
 491           :new.cdid := tmpVar;
 492         END IF;
 493       END;
 494   });
 495   $dbh->do(qq{
 496     CREATE OR REPLACE TRIGGER track_insert_trg
 497     BEFORE INSERT ON track
 498     FOR EACH ROW
 499       BEGIN
 500         IF :new.trackid IS NULL THEN
 501           SELECT track_seq.nextval
 502           INTO :new.trackid
 503           FROM DUAL;
 504         END IF;
 505       END;
 506   });
 507 }
 508
 509 # clean up our mess
 510 END {
 511   eval {
 512     my $dbh = $schema->storage->dbh;
 513     $dbh->do("DROP SEQUENCE artist_pk_seq");
 514     $dbh->do("DROP SEQUENCE cd_seq");
 515     $dbh->do("DROP SEQUENCE track_seq");
 516     $dbh->do("DROP TABLE artist");
 517     $dbh->do("DROP TABLE track");
 518     $dbh->do("DROP TABLE cd");
 519   };
 520 }