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