This test in fact requires DT::F::SQLite
[dbsrgits/DBIx-Class-Historic.git] / t / 73oracle.t
1 {
2   package    # hide from PAUSE
3     DBICTest::Schema::ArtistFQN;
4
5   use base 'DBIx::Class::Core';
6
7   __PACKAGE__->table(
8       defined $ENV{DBICTEST_ORA_USER}
9       ? $ENV{DBICTEST_ORA_USER} . '.artist'
10       : 'artist'
11   );
12   __PACKAGE__->add_columns(
13       'artistid' => {
14           data_type         => 'integer',
15           is_auto_increment => 1,
16       },
17       'name' => {
18           data_type   => 'varchar',
19           size        => 100,
20           is_nullable => 1,
21       },
22       'autoinc_col' => {
23           data_type         => 'integer',
24           is_auto_increment => 1,
25       },
26   );
27   __PACKAGE__->set_primary_key('artistid');
28
29   1;
30 }
31
32 use strict;
33 use warnings;
34
35 use Test::Exception;
36 use Test::More;
37
38 use lib qw(t/lib);
39 use DBICTest;
40 use DBIC::SqlMakerTest;
41
42 my ($dsn,  $user,  $pass)  = @ENV{map { "DBICTEST_ORA_${_}" }  qw/DSN USER PASS/};
43
44 # optional:
45 my ($dsn2, $user2, $pass2) = @ENV{map { "DBICTEST_ORA_EXTRAUSER_${_}" } qw/DSN USER PASS/};
46
47 plan skip_all => 'Set $ENV{DBICTEST_ORA_DSN}, _USER and _PASS to run this test. ' .
48   'Warning: This test drops and creates tables called \'artist\', \'cd\', \'track\' and \'sequence_test\''.
49   ' as well as following sequences: \'pkid1_seq\', \'pkid2_seq\' and \'nonpkid_seq\''
50   unless ($dsn && $user && $pass);
51
52 DBICTest::Schema->load_classes('ArtistFQN');
53 my $schema = DBICTest::Schema->connect($dsn, $user, $pass);
54
55 note "Oracle Version: " . $schema->storage->_server_info->{dbms_version};
56
57 my $dbh = $schema->storage->dbh;
58
59 do_creates($dbh);
60
61 {
62     # Swiped from t/bindtype_columns.t to avoid creating my own Resultset.
63
64     local $SIG{__WARN__} = sub {};
65     eval { $dbh->do('DROP TABLE bindtype_test') };
66
67     $dbh->do(qq[
68         CREATE TABLE bindtype_test
69         (
70             id              integer      NOT NULL   PRIMARY KEY,
71             bytea           integer      NULL,
72             blob            blob         NULL,
73             clob            clob         NULL
74         )
75     ],{ RaiseError => 1, PrintError => 1 });
76 }
77
78 # This is in Core now, but it's here just to test that it doesn't break
79 $schema->class('Artist')->load_components('PK::Auto');
80 # These are compat shims for PK::Auto...
81 $schema->class('CD')->load_components('PK::Auto::Oracle');
82 $schema->class('Track')->load_components('PK::Auto::Oracle');
83
84
85 # test primary key handling with multiple triggers
86 my $new = $schema->resultset('Artist')->create({ name => 'foo' });
87 is($new->artistid, 1, "Oracle Auto-PK worked");
88
89 like ($new->result_source->column_info('artistid')->{sequence}, qr/\.artist_pk_seq$/, 'Correct PK sequence selected');
90
91 # test again with fully-qualified table name
92 my $artistfqn_rs = $schema->resultset('ArtistFQN');
93 my $artist_rsrc = $artistfqn_rs->result_source;
94
95 delete $artist_rsrc->column_info('artistid')->{sequence};
96
97 $new = $artistfqn_rs->create( { name => 'bar' } );
98 is( $new->artistid, 2, "Oracle Auto-PK worked with fully-qualified tablename" );
99
100 delete $artist_rsrc->column_info('artistid')->{sequence};
101
102 $new = $artistfqn_rs->create( { name => 'bar', autoinc_col => 1000 } );
103 is( $new->artistid, 3, "Oracle Auto-PK worked with fully-qualified tablename" );
104 is( $new->autoinc_col, 1000, "Oracle Auto-Inc overruled with fully-qualified tablename");
105
106 like ($artist_rsrc->column_info('artistid')->{sequence}, qr/\.artist_pk_seq$/, 'Still correct PK sequence');
107
108 # test LIMIT support
109 for (1..6) {
110     $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
111 }
112 my $it = $schema->resultset('Artist')->search( { name => { -like => 'Artist %' }},
113     { rows => 3,
114       offset => 4,
115       order_by => 'artistid' }
116 );
117 is( $it->count, 2, "LIMIT count past end of RS ok" );
118 is( $it->next->name, "Artist 5", "iterator->next ok" );
119 is( $it->next->name, "Artist 6", "iterator->next ok" );
120 is( $it->next, undef, "next past end of resultset ok" );
121
122 my $cd = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
123 is($cd->cdid, 1, "Oracle Auto-PK worked - using scalar ref as table name");
124
125 # test rel names over the 30 char limit
126 {
127   my $query = $schema->resultset('Artist')->search({
128     artistid => 1
129   }, {
130     prefetch => 'cds_very_very_very_long_relationship_name'
131   });
132
133   lives_and {
134     is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
135   } 'query with rel name over 30 chars survived and worked';
136
137   # rel name over 30 char limit with user condition
138   # This requires walking the SQLA data structure.
139   {
140     local $TODO = 'user condition on rel longer than 30 chars';
141
142     $query = $schema->resultset('Artist')->search({
143       'cds_very_very_very_long_relationship_name.title' => 'EP C'
144     }, {
145       prefetch => 'cds_very_very_very_long_relationship_name'
146     });
147
148     lives_and {
149       is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
150     } 'query with rel name over 30 chars and user condition survived and worked';
151   }
152 }
153
154 # test join with row count ambiguity
155
156 my $track = $schema->resultset('Track')->create({ cd => $cd->cdid,
157     position => 1, title => 'Track1' });
158 my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'},
159         { join => 'cd',
160           rows => 2 }
161 );
162
163 ok(my $row = $tjoin->next);
164
165 is($row->title, 'Track1', "ambiguous column ok");
166
167 # check count distinct with multiple columns
168 my $other_track = $schema->resultset('Track')->create({ cd => $cd->cdid, position => 1, title => 'Track2' });
169
170 my $tcount = $schema->resultset('Track')->search(
171   {},
172   {
173     select => [ qw/position title/ ],
174     distinct => 1,
175   }
176 );
177 is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
178
179 $tcount = $schema->resultset('Track')->search(
180   {},
181   {
182     columns => [ qw/position title/ ],
183     distinct => 1,
184   }
185 );
186 is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
187
188 $tcount = $schema->resultset('Track')->search(
189   {},
190   {
191      group_by => [ qw/position title/ ]
192   }
193 );
194 is($tcount->count, 2, 'multiple column COUNT DISTINCT using column syntax ok');
195
196 {
197   my $rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset=>1 });
198   my @results = $rs->all;
199   is( scalar @results, 1, "Group by with limit OK" );
200 }
201
202 # test identifiers over the 30 char limit
203 {
204   lives_ok {
205     my @results = $schema->resultset('CD')->search(undef, {
206       prefetch => 'very_long_artist_relationship',
207       rows => 3,
208       offset => 0,
209     })->all;
210     ok( scalar @results > 0, 'limit with long identifiers returned something');
211   } 'limit with long identifiers executed successfully';
212 }
213
214 # test with_deferred_fk_checks
215 lives_ok {
216   $schema->storage->with_deferred_fk_checks(sub {
217     $schema->resultset('Track')->create({
218       trackid => 999, cd => 999, position => 1, title => 'deferred FK track'
219     });
220     $schema->resultset('CD')->create({
221       artist => 1, cdid => 999, year => '2003', title => 'deferred FK cd'
222     });
223   });
224 } 'with_deferred_fk_checks code survived';
225
226 is eval { $schema->resultset('Track')->find(999)->title }, 'deferred FK track',
227    'code in with_deferred_fk_checks worked'; 
228
229 throws_ok {
230   $schema->resultset('Track')->create({
231     trackid => 1, cd => 9999, position => 1, title => 'Track1'
232   });
233 } qr/constraint/i, 'with_deferred_fk_checks is off';
234
235 # test auto increment using sequences WITHOUT triggers
236 for (1..5) {
237     my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
238     is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key");
239     is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key");
240     is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key");
241 }
242 my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
243 is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually");
244
245 SKIP: {
246   my %binstr = ( 'small' => join('', map { chr($_) } ( 1 .. 127 )) );
247   $binstr{'large'} = $binstr{'small'} x 1024;
248
249   my $maxloblen = length $binstr{'large'};
250   note "Localizing LongReadLen to $maxloblen to avoid truncation of test data";
251   local $dbh->{'LongReadLen'} = $maxloblen;
252
253   my $rs = $schema->resultset('BindType');
254   my $id = 0;
255
256   if ($DBD::Oracle::VERSION eq '1.23') {
257     throws_ok { $rs->create({ id => 1, blob => $binstr{large} }) }
258       qr/broken/,
259       'throws on blob insert with DBD::Oracle == 1.23';
260
261     skip 'buggy BLOB support in DBD::Oracle 1.23', 7;
262   }
263
264   # disable BLOB mega-output
265   my $orig_debug = $schema->storage->debug;
266   $schema->storage->debug (0);
267
268   foreach my $type (qw( blob clob )) {
269     foreach my $size (qw( small large )) {
270       $id++;
271
272       lives_ok { $rs->create( { 'id' => $id, $type => $binstr{$size} } ) }
273       "inserted $size $type without dying";
274
275       ok($rs->find($id)->$type eq $binstr{$size}, "verified inserted $size $type" );
276     }
277   }
278
279   $schema->storage->debug ($orig_debug);
280 }
281
282
283 ### test hierarchical queries
284 if ( $schema->storage->isa('DBIx::Class::Storage::DBI::Oracle::Generic') ) {
285     my $source = $schema->source('Artist');
286
287     $source->add_column( 'parentid' );
288
289     $source->add_relationship('children', 'DBICTest::Schema::Artist',
290         { 'foreign.parentid' => 'self.artistid' },
291         {
292             accessor => 'multi',
293             join_type => 'LEFT',
294             cascade_delete => 1,
295             cascade_copy => 1,
296         } );
297     $source->add_relationship('parent', 'DBICTest::Schema::Artist',
298         { 'foreign.artistid' => 'self.parentid' },
299         { accessor => 'single' } );
300     DBICTest::Schema::Artist->add_column( 'parentid' );
301     DBICTest::Schema::Artist->has_many(
302         children => 'DBICTest::Schema::Artist',
303         { 'foreign.parentid' => 'self.artistid' }
304     );
305     DBICTest::Schema::Artist->belongs_to(
306         parent => 'DBICTest::Schema::Artist',
307         { 'foreign.artistid' => 'self.parentid' }
308     );
309
310     $schema->resultset('Artist')->create ({
311         name => 'root',
312         rank => 1,
313         cds => [],
314         children => [
315             {
316                 name => 'child1',
317                 rank => 2,
318                 children => [
319                     {
320                         name => 'grandchild',
321                         rank => 3,
322                         cds => [
323                             {
324                                 title => "grandchilds's cd" ,
325                                 year => '2008',
326                                 tracks => [
327                                     {
328                                         position => 1,
329                                         title => 'Track 1 grandchild',
330                                     }
331                                 ],
332                             }
333                         ],
334                         children => [
335                             {
336                                 name => 'greatgrandchild',
337                                 rank => 3,
338                             }
339                         ],
340                     }
341                 ],
342             },
343             {
344                 name => 'child2',
345                 rank => 3,
346             },
347         ],
348     });
349
350     $schema->resultset('Artist')->create(
351         {
352             name     => 'cycle-root',
353             children => [
354                 {
355                     name     => 'cycle-child1',
356                     children => [ { name => 'cycle-grandchild' } ],
357                 },
358                 { name => 'cycle-child2' },
359             ],
360         }
361     );
362
363     $schema->resultset('Artist')->find({ name => 'cycle-root' })
364       ->update({ parentid => { -ident => 'artistid' } });
365
366     # select the whole tree
367     {
368       my $rs = $schema->resultset('Artist')->search({}, {
369         start_with => { name => 'root' },
370         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
371       });
372
373       is_same_sql_bind (
374         $rs->as_query,
375         '(
376           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
377             FROM artist me
378           START WITH name = ?
379           CONNECT BY parentid = PRIOR artistid 
380         )',
381         [ [ name => 'root'] ],
382       );
383       is_deeply (
384         [ $rs->get_column ('name')->all ],
385         [ qw/root child1 grandchild greatgrandchild child2/ ],
386         'got artist tree',
387       );
388
389
390       is_same_sql_bind (
391         $rs->count_rs->as_query,
392         '(
393           SELECT COUNT( * )
394             FROM artist me
395           START WITH name = ?
396           CONNECT BY parentid = PRIOR artistid 
397         )',
398         [ [ name => 'root'] ],
399       );
400
401       is( $rs->count, 5, 'Connect By count ok' );
402     }
403
404     # use order siblings by statement
405     SKIP: {
406       # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
407       skip q{Oracle8i doesn't support ORDER SIBLINGS BY}, 1
408         if $schema->storage->_server_info->{normalized_dbms_version} < 9;
409
410       my $rs = $schema->resultset('Artist')->search({}, {
411         start_with => { name => 'root' },
412         connect_by => { parentid => { -prior => { -ident =>  'artistid' } } },
413         order_siblings_by => { -desc => 'name' },
414       });
415
416       is_same_sql_bind (
417         $rs->as_query,
418         '(
419           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
420             FROM artist me
421           START WITH name = ?
422           CONNECT BY parentid = PRIOR artistid 
423           ORDER SIBLINGS BY name DESC
424         )',
425         [ [ name => 'root'] ],
426       );
427
428       is_deeply (
429         [ $rs->get_column ('name')->all ],
430         [ qw/root child2 child1 grandchild greatgrandchild/ ],
431         'Order Siblings By ok',
432       );
433     }
434
435     # get the root node
436     {
437       my $rs = $schema->resultset('Artist')->search({ parentid => undef }, {
438         start_with => { name => 'root' },
439         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
440       });
441
442       is_same_sql_bind (
443         $rs->as_query,
444         '(
445           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
446             FROM artist me
447           WHERE ( parentid IS NULL )
448           START WITH name = ?
449           CONNECT BY parentid = PRIOR artistid 
450         )',
451         [ [ name => 'root'] ],
452       );
453
454       is_deeply(
455         [ $rs->get_column('name')->all ],
456         [ 'root' ],
457         'found root node',
458       );
459     }
460
461     # combine a connect by with a join
462     SKIP: {
463       # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state21b.htm#2066123
464       skip q{Oracle8i doesn't support connect by with join}, 1
465         if $schema->storage->_server_info->{normalized_dbms_version} < 9;
466
467       my $rs = $schema->resultset('Artist')->search(
468         {'cds.title' => { -like => '%cd'} },
469         {
470           join => 'cds',
471           start_with => { 'me.name' => 'root' },
472           connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
473         }
474       );
475
476       is_same_sql_bind (
477         $rs->as_query,
478         '(
479           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
480             FROM artist me
481             LEFT JOIN cd cds ON cds.artist = me.artistid
482           WHERE ( cds.title LIKE ? )
483           START WITH me.name = ?
484           CONNECT BY parentid = PRIOR artistid 
485         )',
486         [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
487       );
488
489       is_deeply(
490         [ $rs->get_column('name')->all ],
491         [ 'grandchild' ],
492         'Connect By with a join result name ok'
493       );
494
495
496       is_same_sql_bind (
497         $rs->count_rs->as_query,
498         '(
499           SELECT COUNT( * )
500             FROM artist me
501             LEFT JOIN cd cds ON cds.artist = me.artistid
502           WHERE ( cds.title LIKE ? )
503           START WITH me.name = ?
504           CONNECT BY parentid = PRIOR artistid 
505         )',
506         [ [ 'cds.title' => '%cd' ], [ 'me.name' => 'root' ] ],
507       );
508
509       is( $rs->count, 1, 'Connect By with a join; count ok' );
510     }
511
512     # combine a connect by with order_by
513     {
514       my $rs = $schema->resultset('Artist')->search({}, {
515         start_with => { name => 'root' },
516         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
517         order_by => { -asc => [ 'LEVEL', 'name' ] },
518       });
519
520       is_same_sql_bind (
521         $rs->as_query,
522         '(
523           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid
524           FROM artist me
525           START WITH name = ?
526           CONNECT BY parentid = PRIOR artistid 
527           ORDER BY LEVEL ASC, name ASC
528         )',
529         [ [ name => 'root' ] ],
530       );
531
532
533       # Don't use "$rs->get_column ('name')->all" they build a query arround the $rs.
534       #   If $rs has a order by, the order by is in the subquery and this doesn't work with Oracle 8i.
535       # TODO: write extra test and fix order by handling on Oracle 8i
536       is_deeply (
537         [ map { $_->[1] } $rs->cursor->all ],
538         [ qw/root child1 child2 grandchild greatgrandchild/ ],
539         'Connect By with a order_by - result name ok (without get_column)'
540       );
541
542       SKIP: {
543           skip q{Connect By with a order_by - result name ok (with get_column), Oracle8i doesn't support order by in a subquery},1
544             if $schema->storage->_server_info->{normalized_dbms_version} < 9;
545           is_deeply (
546             [  $rs->get_column ('name')->all ],
547             [ qw/root child1 child2 grandchild greatgrandchild/ ],
548             'Connect By with a order_by - result name ok (with get_column)'
549           );
550       }
551     }
552
553
554     # limit a connect by
555     SKIP: {
556       skip q{Oracle8i doesn't support order by in a subquery}, 1
557         if $schema->storage->_server_info->{normalized_dbms_version} < 9;
558
559       my $rs = $schema->resultset('Artist')->search({}, {
560         start_with => { name => 'root' },
561         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
562         order_by => { -asc => 'name' },
563         rows => 2,
564       });
565
566       is_same_sql_bind (
567         $rs->as_query,
568         '(
569             SELECT artistid, name, rank, charfield, parentid FROM (
570               SELECT
571                   me.artistid,
572                   me.name,
573                   me.rank,
574                   me.charfield,
575                   me.parentid
576                 FROM artist me
577               START WITH name = ?
578               CONNECT BY parentid = PRIOR artistid
579               ORDER BY name ASC 
580             ) me
581             WHERE ROWNUM <= 2
582         )',
583         [ [ name => 'root' ] ],
584       );
585
586       is_deeply (
587         [ $rs->get_column ('name')->all ],
588         [qw/child1 child2/],
589         'LIMIT a Connect By query - correct names'
590       );
591
592       is_same_sql_bind (
593         $rs->count_rs->as_query,
594         '(
595           SELECT COUNT( * ) FROM (
596             SELECT artistid
597               FROM (
598                 SELECT
599                   me.artistid
600                 FROM artist me 
601                 START WITH name = ? 
602                 CONNECT BY parentid = PRIOR artistid
603               ) me
604             WHERE ROWNUM <= 2
605           ) me
606         )',
607         [ [ name => 'root' ] ],
608       );
609
610       is( $rs->count, 2, 'Connect By; LIMIT count ok' );
611     }
612
613     # combine a connect_by with group_by and having
614     {
615       my $rs = $schema->resultset('Artist')->search({}, {
616         select => ['count(rank)'],
617         start_with => { name => 'root' },
618         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
619         group_by => ['rank'],
620         having => { 'count(rank)' => { '<', 2 } },
621       });
622
623       is_same_sql_bind (
624         $rs->as_query,
625         '(
626             SELECT count(rank)
627             FROM artist me
628             START WITH name = ?
629             CONNECT BY parentid = PRIOR artistid
630             GROUP BY rank HAVING count(rank) < ?
631         )',
632         [ [ name => 'root' ], [ 'count(rank)' => 2 ] ],
633       );
634
635       is_deeply (
636         [ $rs->get_column ('count(rank)')->all ],
637         [1, 1],
638         'Group By a Connect By query - correct values'
639       );
640     }
641
642
643     # select the whole cycle tree without nocylce
644     {
645       my $rs = $schema->resultset('Artist')->search({}, {
646         start_with => { name => 'cycle-root' },
647         connect_by => { parentid => { -prior => { -ident => 'artistid' } } },
648       });
649       eval { $rs->get_column ('name')->all };
650       if ( $@ =~ /ORA-01436/ ){ # ORA-01436:  CONNECT BY loop in user data
651         pass "connect by initify loop detection without nocycle";
652       }else{
653         fail "connect by initify loop detection without nocycle, not detected by oracle";
654       }
655     }
656
657     # select the whole cycle tree with nocylce
658     SKIP: {
659       # http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1023748
660       skip q{Oracle8i doesn't support connect by nocycle}, 1
661         if $schema->storage->_server_info->{normalized_dbms_version} < 9;
662
663       my $rs = $schema->resultset('Artist')->search({}, {
664         start_with => { name => 'cycle-root' },
665         '+select'  => [ \ 'CONNECT_BY_ISCYCLE' ],
666         connect_by_nocycle => { parentid => { -prior => { -ident => 'artistid' } } },
667       });
668
669       is_same_sql_bind (
670         $rs->as_query,
671         '(
672           SELECT me.artistid, me.name, me.rank, me.charfield, me.parentid, CONNECT_BY_ISCYCLE
673             FROM artist me
674           START WITH name = ?
675           CONNECT BY NOCYCLE parentid = PRIOR artistid 
676         )',
677         [ [ name => 'cycle-root'] ],
678       );
679       is_deeply (
680         [ $rs->get_column ('name')->all ],
681         [ qw/cycle-root cycle-child1 cycle-grandchild cycle-child2/ ],
682         'got artist tree with nocycle (name)',
683       );
684       is_deeply (
685         [ $rs->get_column ('CONNECT_BY_ISCYCLE')->all ],
686         [ qw/1 0 0 0/ ],
687         'got artist tree with nocycle (CONNECT_BY_ISCYCLE)',
688       );
689
690
691       is_same_sql_bind (
692         $rs->count_rs->as_query,
693         '(
694           SELECT COUNT( * )
695             FROM artist me
696           START WITH name = ?
697           CONNECT BY NOCYCLE parentid = PRIOR artistid 
698         )',
699         [ [ name => 'cycle-root'] ],
700       );
701
702       is( $rs->count, 4, 'Connect By Nocycle count ok' );
703     }
704 }
705
706 my $schema2;
707
708 # test sequence detection from a different schema
709 SKIP: {
710 TODO: {
711   skip ((join '',
712 'Set DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS to a *DIFFERENT* Oracle user',
713 ' to run the cross-schema autoincrement test.'),
714     1) unless $dsn2 && $user2 && $user2 ne $user;
715
716   # Oracle8i Reference Release 2 (8.1.6) 
717   #   http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch294.htm#993
718   # Oracle Database Reference 10g Release 2 (10.2)
719   #   http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2107.htm#sthref1297
720   local $TODO = "On Oracle8i all_triggers view is empty, i don't yet know why..."
721     if $schema->storage->_server_info->{normalized_dbms_version} < 9;
722
723   $schema2 = DBICTest::Schema->connect($dsn2, $user2, $pass2);
724
725   my $schema1_dbh  = $schema->storage->dbh;
726
727   $schema1_dbh->do("GRANT INSERT ON artist TO $user2");
728   $schema1_dbh->do("GRANT SELECT ON artist_pk_seq TO $user2");
729
730   my $rs = $schema2->resultset('ArtistFQN');
731
732   # first test with unquoted (default) sequence name in trigger body
733
734   lives_and {
735     my $row = $rs->create({ name => 'From Different Schema' });
736     ok $row->artistid;
737   } 'used autoinc sequence across schemas';
738
739   # now quote the sequence name
740   $schema1_dbh->do(qq{
741     CREATE OR REPLACE TRIGGER artist_insert_trg_pk
742     BEFORE INSERT ON artist
743     FOR EACH ROW
744     BEGIN
745       IF :new.artistid IS NULL THEN
746         SELECT "ARTIST_PK_SEQ".nextval
747         INTO :new.artistid
748         FROM DUAL;
749       END IF;
750     END;
751   });
752
753   # sequence is cached in the rsrc
754   delete $rs->result_source->column_info('artistid')->{sequence};
755
756   lives_and {
757     my $row = $rs->create({ name => 'From Different Schema With Quoted Sequence' });
758     ok $row->artistid;
759   } 'used quoted autoinc sequence across schemas';
760
761   my $schema_name = uc $user;
762
763   is $rs->result_source->column_info('artistid')->{sequence},
764     qq[${schema_name}."ARTIST_PK_SEQ"],
765     'quoted sequence name correctly extracted';
766 } }
767
768 done_testing;
769
770 sub do_creates {
771   my $dbh = shift;
772
773   eval {
774     $dbh->do("DROP SEQUENCE artist_autoinc_seq");
775     $dbh->do("DROP SEQUENCE artist_pk_seq");
776     $dbh->do("DROP SEQUENCE cd_seq");
777     $dbh->do("DROP SEQUENCE track_seq");
778     $dbh->do("DROP SEQUENCE pkid1_seq");
779     $dbh->do("DROP SEQUENCE pkid2_seq");
780     $dbh->do("DROP SEQUENCE nonpkid_seq");
781     $dbh->do("DROP TABLE artist");
782     $dbh->do("DROP TABLE sequence_test");
783     $dbh->do("DROP TABLE track");
784     $dbh->do("DROP TABLE cd");
785   };
786   $dbh->do("CREATE SEQUENCE artist_autoinc_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
787   $dbh->do("CREATE SEQUENCE artist_pk_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
788   $dbh->do("CREATE SEQUENCE cd_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
789   $dbh->do("CREATE SEQUENCE track_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
790   $dbh->do("CREATE SEQUENCE pkid1_seq START WITH 1 MAXVALUE 999999 MINVALUE 0");
791   $dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0");
792   $dbh->do("CREATE SEQUENCE nonpkid_seq START WITH 20 MAXVALUE 999999 MINVALUE 0");
793
794   $dbh->do("CREATE TABLE artist (artistid NUMBER(12), parentid NUMBER(12), name VARCHAR(255), autoinc_col NUMBER(12), rank NUMBER(38), charfield VARCHAR2(10))");
795   $dbh->do("ALTER TABLE artist ADD (CONSTRAINT artist_pk PRIMARY KEY (artistid))");
796
797   $dbh->do("CREATE TABLE sequence_test (pkid1 NUMBER(12), pkid2 NUMBER(12), nonpkid NUMBER(12), name VARCHAR(255))");
798   $dbh->do("ALTER TABLE sequence_test ADD (CONSTRAINT sequence_test_constraint PRIMARY KEY (pkid1, pkid2))");
799
800   $dbh->do("CREATE TABLE cd (cdid NUMBER(12), artist NUMBER(12), title VARCHAR(255), year VARCHAR(4), genreid NUMBER(12), single_track NUMBER(12))");
801   $dbh->do("ALTER TABLE cd ADD (CONSTRAINT cd_pk PRIMARY KEY (cdid))");
802
803   $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)");
804   $dbh->do("ALTER TABLE track ADD (CONSTRAINT track_pk PRIMARY KEY (trackid))");
805
806   $dbh->do(qq{
807     CREATE OR REPLACE TRIGGER artist_insert_trg_auto
808     BEFORE INSERT ON artist
809     FOR EACH ROW
810     BEGIN
811       IF :new.autoinc_col IS NULL THEN
812         SELECT artist_autoinc_seq.nextval
813         INTO :new.autoinc_col
814         FROM DUAL;
815       END IF;
816     END;
817   });
818   $dbh->do(qq{
819     CREATE OR REPLACE TRIGGER artist_insert_trg_pk
820     BEFORE INSERT ON artist
821     FOR EACH ROW
822     BEGIN
823       IF :new.artistid IS NULL THEN
824         SELECT artist_pk_seq.nextval
825         INTO :new.artistid
826         FROM DUAL;
827       END IF;
828     END;
829   });
830   $dbh->do(qq{
831     CREATE OR REPLACE TRIGGER cd_insert_trg
832     BEFORE INSERT OR UPDATE ON cd
833     FOR EACH ROW
834     BEGIN
835       IF :new.cdid IS NULL THEN
836         SELECT cd_seq.nextval
837         INTO :new.cdid
838         FROM DUAL;
839       END IF;
840     END;
841   });
842   $dbh->do(qq{
843     CREATE OR REPLACE TRIGGER cd_insert_trg
844     BEFORE INSERT ON cd
845     FOR EACH ROW
846     BEGIN
847       IF :new.cdid IS NULL THEN
848         SELECT cd_seq.nextval
849         INTO :new.cdid
850         FROM DUAL;
851       END IF;
852     END;
853   });
854   $dbh->do(qq{
855     CREATE OR REPLACE TRIGGER track_insert_trg
856     BEFORE INSERT ON track
857     FOR EACH ROW
858     BEGIN
859       IF :new.trackid IS NULL THEN
860         SELECT track_seq.nextval
861         INTO :new.trackid
862         FROM DUAL;
863       END IF;
864     END;
865   });
866 }
867
868 # clean up our mess
869 END {
870   for my $dbh (map $_->storage->dbh, grep $_, ($schema, $schema2)) {
871     eval {
872       $dbh->do("DROP SEQUENCE artist_autoinc_seq");
873       $dbh->do("DROP SEQUENCE artist_pk_seq");
874       $dbh->do("DROP SEQUENCE cd_seq");
875       $dbh->do("DROP SEQUENCE track_seq");
876       $dbh->do("DROP SEQUENCE pkid1_seq");
877       $dbh->do("DROP SEQUENCE pkid2_seq");
878       $dbh->do("DROP SEQUENCE nonpkid_seq");
879       $dbh->do("DROP TABLE artist");
880       $dbh->do("DROP TABLE sequence_test");
881       $dbh->do("DROP TABLE track");
882       $dbh->do("DROP TABLE cd");
883       $dbh->do("DROP TABLE bindtype_test");
884     };
885   }
886 }