Fix missing true return lost during e5a62c46f
[dbsrgits/DBIx-Class.git] / t / 73oracle.t
1 use strict;
2 use warnings;
3
4 use Test::Exception;
5 use Test::More;
6 use Sub::Name;
7 use Try::Tiny;
8 use DBIx::Class::Optional::Dependencies ();
9
10 use lib qw(t/lib);
11 use DBICTest;
12 use DBIC::SqlMakerTest;
13
14 my ($dsn,  $user,  $pass)  = @ENV{map { "DBICTEST_ORA_${_}" }  qw/DSN USER PASS/};
15
16 # optional:
17 my ($dsn2, $user2, $pass2) = @ENV{map { "DBICTEST_ORA_EXTRAUSER_${_}" } 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 plan skip_all => 'Test needs ' . DBIx::Class::Optional::Dependencies->req_missing_for ('test_rdbms_oracle')
23   unless DBIx::Class::Optional::Dependencies->req_ok_for ('test_rdbms_oracle');
24
25 $ENV{NLS_SORT} = "BINARY";
26 $ENV{NLS_COMP} = "BINARY";
27 $ENV{NLS_LANG} = "AMERICAN";
28
29 {
30   package    # hide from PAUSE
31     DBICTest::Schema::ArtistFQN;
32
33   use base 'DBIx::Class::Core';
34
35   __PACKAGE__->table(
36     $ENV{DBICTEST_ORA_USER}
37       ? (uc $ENV{DBICTEST_ORA_USER}) . '.artist'
38       : '??_no_user_??'
39   );
40   __PACKAGE__->add_columns(
41     'artistid' => {
42       data_type         => 'integer',
43       is_auto_increment => 1,
44     },
45     'name' => {
46       data_type   => 'varchar',
47       size        => 100,
48       is_nullable => 1,
49     },
50     'autoinc_col' => {
51       data_type         => 'integer',
52       is_auto_increment => 1,
53     },
54     'default_value_col' => {
55       data_type           => 'varchar',
56       size                => 100,
57       is_nullable         => 0,
58       retrieve_on_insert  => 1,
59     }
60   );
61   __PACKAGE__->set_primary_key(qw/ artistid autoinc_col /);
62
63   1;
64 }
65
66 DBICTest::Schema->load_classes('ArtistFQN');
67
68 # This is in Core now, but it's here just to test that it doesn't break
69 DBICTest::Schema::Artist->load_components('PK::Auto');
70 # These are compat shims for PK::Auto...
71 DBICTest::Schema::CD->load_components('PK::Auto::Oracle');
72 DBICTest::Schema::Track->load_components('PK::Auto::Oracle');
73
74
75 # check if we indeed do support stuff
76 my $v = do {
77   my $si = DBICTest::Schema->connect($dsn, $user, $pass)->storage->_server_info;
78   $si->{normalized_dbms_version}
79     or die "Unparseable Oracle server version: $si->{dbms_version}\n";
80 };
81
82 my $test_server_supports_only_orajoins = $v < 9;
83
84 # TODO find out which version supports the RETURNING syntax
85 # 8i (8.1) has it and earlier docs are a 404 on oracle.com
86 my $test_server_supports_insert_returning = $v >= 8.001;
87
88 is (
89   DBICTest::Schema->connect($dsn, $user, $pass)->storage->_use_insert_returning,
90   $test_server_supports_insert_returning,
91   'insert returning capability guessed correctly'
92 );
93
94 isa_ok (DBICTest::Schema->connect($dsn, $user, $pass)->storage->sql_maker, 'DBIx::Class::SQLMaker::Oracle');
95
96 # see if determining a driver with bad credentials throws propely
97 throws_ok {
98   DBICTest::Schema->connect($dsn, "BORKED BORKED USER $user", $pass)->storage->sql_maker;
99 } qr/DBI Connection failed/;
100
101 ##########
102 # the recyclebin (new for 10g) sometimes comes in the way
103 my $on_connect_sql = $v >= 10 ? ["ALTER SESSION SET recyclebin = OFF"] : [];
104
105 # iterate all tests on following options
106 my @tryopt = (
107   { on_connect_do => $on_connect_sql },
108   { quote_char => '"', on_connect_do => $on_connect_sql },
109 );
110
111 # keep a database handle open for cleanup
112 my ($dbh, $dbh2);
113
114 my $schema;
115 for my $use_insert_returning ($test_server_supports_insert_returning ? (1,0) : (0) ) {
116   for my $force_ora_joins ($test_server_supports_only_orajoins ? (0) : (0,1) ) {
117
118     no warnings qw/once redefine/;
119     my $old_connection = DBICTest::Schema->can('connection');
120     local *DBICTest::Schema::connection = subname 'DBICTest::Schema::connection' => sub {
121       my $s = shift->$old_connection (@_);
122       $s->storage->_use_insert_returning ($use_insert_returning);
123       $s->storage->sql_maker_class('DBIx::Class::SQLMaker::OracleJoins') if $force_ora_joins;
124       $s;
125     };
126
127     for my $opt (@tryopt) {
128       # clean all cached sequences from previous run
129       for (map { values %{DBICTest::Schema->source($_)->columns_info} } (qw/Artist CD Track/) ) {
130         delete $_->{sequence};
131       }
132
133       my $schema = DBICTest::Schema->connect($dsn, $user, $pass, $opt);
134
135       $dbh = $schema->storage->dbh;
136       my $q = $schema->storage->sql_maker->quote_char || '';
137
138       do_creates($dbh, $q);
139
140       _run_tests($schema, $opt);
141     }
142   }
143 }
144
145 sub _run_tests {
146   my ($schema, $opt) = @_;
147
148   my $q = $schema->storage->sql_maker->quote_char || '';
149
150 # test primary key handling with multiple triggers
151   my ($new, $seq);
152
153   my $new_artist = $schema->resultset('Artist')->create({ name => 'foo' });
154   my $new_cd     = $schema->resultset('CD')->create({ artist => 1, title => 'EP C', year => '2003' });
155
156   SKIP: {
157     skip 'not detecting sequences when using INSERT ... RETURNING', 4
158       if $schema->storage->_use_insert_returning;
159
160     is($new_artist->artistid, 1, "Oracle Auto-PK worked for standard sqlt-like trigger");
161     $seq = $new_artist->result_source->column_info('artistid')->{sequence};
162     $seq = $$seq if ref $seq;
163     like ($seq, qr/\.${q}artist_pk_seq${q}$/, 'Correct PK sequence selected for sqlt-like trigger');
164
165     is($new_cd->cdid, 1, 'Oracle Auto-PK worked - using scalar ref as table name/custom weird trigger');
166     $seq = $new_cd->result_source->column_info('cdid')->{sequence};
167     $seq = $$seq if ref $seq;
168     like ($seq, qr/\.${q}cd_seq${q}$/, 'Correct PK sequence selected for custom trigger');
169   }
170
171 # test PKs again with fully-qualified table name
172   my $artistfqn_rs = $schema->resultset('ArtistFQN');
173   my $artist_rsrc = $artistfqn_rs->result_source;
174
175   delete $artist_rsrc->column_info('artistid')->{sequence};
176   $new = $artistfqn_rs->create( { name => 'bar' } );
177
178   is_deeply( {map { $_ => $new->$_ } $artist_rsrc->primary_columns},
179     { artistid => 2, autoinc_col => 2},
180     "Oracle Multi-Auto-PK worked with fully-qualified tablename" );
181
182
183   delete $artist_rsrc->column_info('artistid')->{sequence};
184   $new = $artistfqn_rs->create( { name => 'bar', autoinc_col => 1000 } );
185
186   is( $new->artistid, 3, "Oracle Auto-PK worked with fully-qualified tablename" );
187   is( $new->autoinc_col, 1000, "Oracle Auto-Inc overruled with fully-qualified tablename");
188
189
190   is( $new->default_value_col, 'default_value', $schema->storage->_use_insert_returning
191     ? 'Check retrieve_on_insert on default_value_col with INSERT ... RETURNING'
192     : 'Check retrieve_on_insert on default_value_col without INSERT ... RETURNING'
193   );
194
195   SKIP: {
196     skip 'not detecting sequences when using INSERT ... RETURNING', 1
197       if $schema->storage->_use_insert_returning;
198
199     $seq = $new->result_source->column_info('artistid')->{sequence};
200     $seq = $$seq if ref $seq;
201     like ($seq, qr/\.${q}artist_pk_seq${q}$/, 'Correct PK sequence selected for sqlt-like trigger');
202   }
203
204
205 # test LIMIT support
206   for (1..6) {
207     $schema->resultset('Artist')->create({ name => 'Artist ' . $_ });
208   }
209   my $it = $schema->resultset('Artist')->search( { name => { -like => 'Artist %' } }, {
210     rows => 3,
211     offset => 4,
212     order_by => 'artistid'
213   });
214
215   is( $it->count, 2, "LIMIT count past end of RS ok" );
216   is( $it->next->name, "Artist 5", "iterator->next ok" );
217   is( $it->next->name, "Artist 6", "iterator->next ok" );
218   is( $it->next, undef, "next past end of resultset ok" );
219
220 # test identifiers over the 30 char limit
221   lives_ok {
222     my @results = $schema->resultset('CD')->search(undef, {
223       prefetch => 'very_long_artist_relationship',
224       rows => 3,
225       offset => 0,
226     })->all;
227     ok( scalar @results > 0, 'limit with long identifiers returned something');
228   } 'limit with long identifiers executed successfully';
229
230
231 # test rel names over the 30 char limit
232   my $query = $schema->resultset('Artist')->search({
233     artistid => 1
234   }, {
235     prefetch => 'cds_very_very_very_long_relationship_name'
236   });
237
238   lives_and {
239     is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
240   } 'query with rel name over 30 chars survived and worked';
241
242 # test rel names over the 30 char limit using group_by and join
243   {
244     my @group_cols = ( 'me.name' );
245     my $query = $schema->resultset('Artist')->search({
246       artistid => 1
247     }, {
248       select => \@group_cols,
249       as => [map { /^\w+\.(\w+)$/ } @group_cols],
250       join => [qw( cds_very_very_very_long_relationship_name )],
251       group_by => \@group_cols,
252     });
253
254     lives_and {
255       my @got = $query->get_column('name')->all();
256       is_deeply \@got, [$new_artist->name];
257     } 'query with rel name over 30 chars worked on join, group_by for me col';
258
259     lives_and {
260       is $query->count(), 1
261     } 'query with rel name over 30 chars worked on join, group_by, count for me col';
262   }
263   {
264     my @group_cols = ( 'cds_very_very_very_long_relationship_name.title' );
265     my $query = $schema->resultset('Artist')->search({
266       artistid => 1
267     }, {
268       select => \@group_cols,
269       as => [map { /^\w+\.(\w+)$/ } @group_cols],
270       join => [qw( cds_very_very_very_long_relationship_name )],
271       group_by => \@group_cols,
272     });
273
274     lives_and {
275       my @got = $query->get_column('title')->all();
276       is_deeply \@got, [$new_cd->title];
277     } 'query with rel name over 30 chars worked on join, group_by for long rel col';
278
279     lives_and {
280       is $query->count(), 1
281     } 'query with rel name over 30 chars worked on join, group_by, count for long rel col';
282   }
283
284   # rel name over 30 char limit with user condition
285   # This requires walking the SQLA data structure.
286   {
287     $query = $schema->resultset('Artist')->search({
288       'cds_very_very_very_long_relationship_name.title' => 'EP C'
289     }, {
290       prefetch => 'cds_very_very_very_long_relationship_name'
291     });
292
293     lives_and {
294       is $query->first->cds_very_very_very_long_relationship_name->first->cdid, 1
295     } 'query with rel name over 30 chars and user condition survived and worked';
296   }
297
298
299 # test join with row count ambiguity
300   my $cd = $schema->resultset('CD')->next;
301   my $track = $cd->create_related('tracks', { position => 1, title => 'Track1'} );
302   my $tjoin = $schema->resultset('Track')->search({ 'me.title' => 'Track1'}, {
303     join => 'cd', rows => 2
304   });
305
306   ok(my $row = $tjoin->next);
307
308   is($row->title, 'Track1', "ambiguous column ok");
309
310
311
312 # check count distinct with multiple columns
313   my $other_track = $schema->resultset('Track')->create({ cd => $cd->cdid, position => 1, title => 'Track2' });
314
315   my $tcount = $schema->resultset('Track')->search(
316     {},
317     {
318       select => [ qw/position title/ ],
319       distinct => 1,
320     }
321   );
322   is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
323
324   $tcount = $schema->resultset('Track')->search(
325     {},
326     {
327       columns => [ qw/position title/ ],
328       distinct => 1,
329     }
330   );
331   is($tcount->count, 2, 'multiple column COUNT DISTINCT ok');
332
333   $tcount = $schema->resultset('Track')->search(
334     {},
335     {
336       group_by => [ qw/position title/ ]
337     }
338   );
339   is($tcount->count, 2, 'multiple column COUNT DISTINCT using column syntax ok');
340
341
342 # check group_by
343   my $g_rs = $schema->resultset('Track')->search( undef, { columns=>[qw/trackid position/], group_by=> [ qw/trackid position/ ] , rows => 2, offset => 1 });
344   is( scalar $g_rs->all, 1, "Group by with limit OK" );
345
346
347 # test with_deferred_fk_checks
348   lives_ok {
349     $schema->storage->with_deferred_fk_checks(sub {
350       $schema->resultset('Track')->create({
351         trackid => 999, cd => 999, position => 1, title => 'deferred FK track'
352       });
353       $schema->resultset('CD')->create({
354         artist => 1, cdid => 999, year => '2003', title => 'deferred FK cd'
355       });
356     });
357   } 'with_deferred_fk_checks code survived';
358
359   is eval { $schema->resultset('Track')->find(999)->title }, 'deferred FK track',
360     'code in with_deferred_fk_checks worked';
361
362   throws_ok {
363     $schema->resultset('Track')->create({
364       trackid => 1, cd => 9999, position => 1, title => 'Track1'
365     });
366   } qr/constraint/i, 'with_deferred_fk_checks is off';
367
368
369 # test auto increment using sequences WITHOUT triggers
370   for (1..5) {
371     my $st = $schema->resultset('SequenceTest')->create({ name => 'foo' });
372     is($st->pkid1, $_, "Oracle Auto-PK without trigger: First primary key");
373     is($st->pkid2, $_ + 9, "Oracle Auto-PK without trigger: Second primary key");
374     is($st->nonpkid, $_ + 19, "Oracle Auto-PK without trigger: Non-primary key");
375   }
376   my $st = $schema->resultset('SequenceTest')->create({ name => 'foo', pkid1 => 55 });
377   is($st->pkid1, 55, "Oracle Auto-PK without trigger: First primary key set manually");
378
379
380 # test populate (identity, success and error handling)
381   my $art_rs = $schema->resultset('Artist');
382
383   my $seq_pos = $art_rs->get_column('artistid')->max;
384   ok($seq_pos, 'Starting with something in the artist table');
385
386
387   my $pop_rs = $schema->resultset('Artist')->search(
388     { name => { -like => 'pop_art_%' } },
389     { order_by => 'artistid' }
390   );
391
392   $art_rs->delete;
393   lives_ok {
394     $pop_rs->populate([
395       map { +{ name => "pop_art_$_" } }
396       (1,2,3)
397     ]);
398
399     is_deeply (
400       [ $pop_rs->get_column('artistid')->all ],
401       [ map { $seq_pos + $_ } (1,2,3) ],
402       'Sequence works after empty-table insertion'
403     );
404   } 'Populate without identity does not throw';
405
406   lives_ok {
407     $pop_rs->populate([
408       map { +{ artistid => $_, name => "pop_art_$_" } }
409       (1,2,3)
410     ]);
411
412     is_deeply (
413       [ $pop_rs->get_column('artistid')->all ],
414       [ 1,2,3, map { $seq_pos + $_ } (1,2,3) ],
415       'Explicit id population works'
416     );
417   } 'Populate with identity does not throw';
418
419   throws_ok {
420     $pop_rs->populate([
421       map { +{ artistid => $_, name => "pop_art_$_" } }
422       (200, 1, 300)
423     ]);
424   } qr/unique constraint.+populate slice.+name => "pop_art_1"/s, 'Partially failed populate throws';
425
426   is_deeply (
427     [ $pop_rs->get_column('artistid')->all ],
428     [ 1,2,3, map { $seq_pos + $_ } (1,2,3) ],
429     'Partially failed populate did not alter table contents'
430   );
431
432 # test complex join (exercise orajoins)
433   lives_ok { is_deeply (
434     $schema->resultset('CD')->search(
435       { 'artist.name' => 'pop_art_1', 'me.cdid' => { '!=', 999} },
436       { join => 'artist', prefetch => 'tracks', rows => 4, order_by => 'tracks.trackid' }
437     )->all_hri,
438     [{
439       artist => 1,
440       cdid => 1,
441       genreid => undef,
442       single_track => undef,
443       title => "EP C",
444       tracks => [
445         {
446           cd => 1,
447           last_updated_at => undef,
448           last_updated_on => undef,
449           position => 1,
450           title => "Track1",
451           trackid => 1
452         },
453         {
454           cd => 1,
455           last_updated_at => undef,
456           last_updated_on => undef,
457           position => 1,
458           title => "Track2",
459           trackid => 2
460         },
461       ],
462       year => 2003
463     }],
464     'Correct set of data prefetched',
465   ) } 'complex prefetch ok';
466
467 # test sequence detection from a different schema
468   SKIP: {
469   TODO: {
470     skip ((join '',
471       'Set DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS to a *DIFFERENT* Oracle user',
472       ' to run the cross-schema sequence detection test.'),
473     1) unless $dsn2 && $user2 && $user2 ne $user;
474
475     skip 'not detecting cross-schema sequence name when using INSERT ... RETURNING', 1
476       if $schema->storage->_use_insert_returning;
477
478     # Oracle8i Reference Release 2 (8.1.6)
479     #   http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch294.htm#993
480     # Oracle Database Reference 10g Release 2 (10.2)
481     #   http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2107.htm#sthref1297
482     todo_skip "On Oracle8i all_triggers view is empty, i don't yet know why...", 1
483       if $schema->storage->_server_info->{normalized_dbms_version} < 9;
484
485     my $schema2 = $schema->connect($dsn2, $user2, $pass2, $opt);
486     my $dbh2 = $schema2->storage->dbh;
487
488     # create identically named tables/sequences in the other schema
489     do_creates($dbh2, $q);
490
491     # grant select privileges to the 2nd user
492     $dbh->do("GRANT INSERT ON ${q}artist${q} TO " . uc $user2);
493     $dbh->do("GRANT SELECT ON ${q}artist${q} TO " . uc $user2);
494     $dbh->do("GRANT SELECT ON ${q}artist_pk_seq${q} TO " . uc $user2);
495     $dbh->do("GRANT SELECT ON ${q}artist_autoinc_seq${q} TO " . uc $user2);
496
497     # test with a fully qualified table (user1/schema prepended)
498     my $rs2 = $schema2->resultset('ArtistFQN');
499     delete $rs2->result_source->column_info('artistid')->{sequence};
500
501     lives_and {
502       my $row = $rs2->create({ name => 'From Different Schema' });
503       ok $row->artistid;
504     } 'used autoinc sequence across schemas';
505
506     # now quote the sequence name (do_creates always uses an lc name)
507     my $q_seq = $q
508       ? '"artist_pk_seq"'
509       : '"ARTIST_PK_SEQ"'
510     ;
511     delete $rs2->result_source->column_info('artistid')->{sequence};
512     $dbh->do(qq{
513       CREATE OR REPLACE TRIGGER ${q}artist_insert_trg_pk${q}
514       BEFORE INSERT ON ${q}artist${q}
515       FOR EACH ROW
516       BEGIN
517         IF :new.${q}artistid${q} IS NULL THEN
518           SELECT $q_seq.nextval
519           INTO :new.${q}artistid${q}
520           FROM DUAL;
521         END IF;
522       END;
523     });
524
525
526     lives_and {
527       my $row = $rs2->create({ name => 'From Different Schema With Quoted Sequence' });
528       ok $row->artistid;
529     } 'used quoted autoinc sequence across schemas';
530
531     is_deeply $rs2->result_source->column_info('artistid')->{sequence},
532       \( (uc $user) . ".$q_seq"),
533       'quoted sequence name correctly extracted';
534
535     # try an insert operation on the default user2 artist
536     my $art1 = $schema->resultset('Artist');
537     my $art2 = $schema2->resultset('Artist');
538     my $art1_count = $art1->count || 0;
539     my $art2_count = $art2->count;
540
541     is( $art2_count, 0, 'No artists created yet in second schema' );
542
543     delete $art2->result_source->column_info('artistid')->{sequence};
544     my $new_art = $art2->create({ name => '2nd best' });
545
546     is ($art1->count, $art1_count, 'No new rows in main schema');
547     is ($art2->count, 1, 'One artist create in 2nd schema');
548
549     is( $new_art->artistid, 1, 'Expected first PK' );
550
551     do_clean ($dbh2);
552   }}
553
554 # test driver determination issues that led to the diagnosis/fix in 37b5ab51
555 # observed side-effect when count-is-first on a fresh env-based connect
556   {
557     local $ENV{DBI_DSN};
558     ($ENV{DBI_DSN}, my @user_pass_args) = @{ $schema->storage->connect_info };
559     my $s2 = DBICTest::Schema->connect( undef, @user_pass_args );
560     ok (! $s2->storage->connected, 'Not connected' );
561     is (ref $s2->storage, 'DBIx::Class::Storage::DBI', 'Undetermined driver' );
562
563     ok (
564       $s2->resultset('Artist')->search({ 'me.name' => { like => '%' } }, { prefetch => 'cds' })->count,
565       'Some artist count'
566     );
567     ok (
568       scalar $s2->resultset('CD')->search({}, { join => 'tracks' } )->all,
569       'Some cds returned'
570     );
571     $s2->storage->disconnect;
572   }
573
574   do_clean ($dbh);
575 }
576
577 done_testing;
578
579 sub do_creates {
580   my ($dbh, $q) = @_;
581
582   do_clean($dbh);
583
584   $dbh->do("CREATE SEQUENCE ${q}artist_autoinc_seq${q} START WITH 1 MAXVALUE 999999 MINVALUE 0");
585   $dbh->do("CREATE SEQUENCE ${q}artist_pk_seq${q} START WITH 1 MAXVALUE 999999 MINVALUE 0");
586   $dbh->do("CREATE SEQUENCE ${q}cd_seq${q} START WITH 1 MAXVALUE 999999 MINVALUE 0");
587   $dbh->do("CREATE SEQUENCE ${q}track_seq${q} START WITH 1 MAXVALUE 999999 MINVALUE 0");
588
589   $dbh->do("CREATE SEQUENCE ${q}nonpkid_seq${q} START WITH 20 MAXVALUE 999999 MINVALUE 0");
590   # this one is always quoted as per manually specified sequence =>
591   $dbh->do('CREATE SEQUENCE "pkid1_seq" START WITH 1 MAXVALUE 999999 MINVALUE 0');
592   # this one is always unquoted as per manually specified sequence =>
593   $dbh->do("CREATE SEQUENCE pkid2_seq START WITH 10 MAXVALUE 999999 MINVALUE 0");
594
595   $dbh->do("CREATE TABLE ${q}artist${q} (${q}artistid${q} NUMBER(12), ${q}name${q} VARCHAR(255),${q}default_value_col${q} VARCHAR(255) DEFAULT 'default_value', ${q}autoinc_col${q} NUMBER(12), ${q}rank${q} NUMBER(38), ${q}charfield${q} VARCHAR2(10))");
596   $dbh->do("ALTER TABLE ${q}artist${q} ADD (CONSTRAINT ${q}artist_pk${q} PRIMARY KEY (${q}artistid${q}))");
597
598   $dbh->do("CREATE TABLE ${q}sequence_test${q} (${q}pkid1${q} NUMBER(12), ${q}pkid2${q} NUMBER(12), ${q}nonpkid${q} NUMBER(12), ${q}name${q} VARCHAR(255))");
599   $dbh->do("ALTER TABLE ${q}sequence_test${q} ADD (CONSTRAINT ${q}sequence_test_constraint${q} PRIMARY KEY (${q}pkid1${q}, ${q}pkid2${q}))");
600
601   # table cd will be unquoted => Oracle will see it as uppercase
602   $dbh->do("CREATE TABLE cd (${q}cdid${q} NUMBER(12), ${q}artist${q} NUMBER(12), ${q}title${q} VARCHAR(255), ${q}year${q} VARCHAR(4), ${q}genreid${q} NUMBER(12), ${q}single_track${q} NUMBER(12))");
603   $dbh->do("ALTER TABLE cd ADD (CONSTRAINT ${q}cd_pk${q} PRIMARY KEY (${q}cdid${q}))");
604
605   $dbh->do("CREATE TABLE ${q}track${q} (${q}trackid${q} NUMBER(12), ${q}cd${q} NUMBER(12) REFERENCES CD(${q}cdid${q}) DEFERRABLE, ${q}position${q} NUMBER(12), ${q}title${q} VARCHAR(255), ${q}last_updated_on${q} DATE, ${q}last_updated_at${q} DATE)");
606   $dbh->do("ALTER TABLE ${q}track${q} ADD (CONSTRAINT ${q}track_pk${q} PRIMARY KEY (${q}trackid${q}))");
607
608   $dbh->do(qq{
609     CREATE OR REPLACE TRIGGER ${q}artist_insert_trg_auto${q}
610     BEFORE INSERT ON ${q}artist${q}
611     FOR EACH ROW
612     BEGIN
613       IF :new.${q}autoinc_col${q} IS NULL THEN
614         SELECT ${q}artist_autoinc_seq${q}.nextval
615         INTO :new.${q}autoinc_col${q}
616         FROM DUAL;
617       END IF;
618     END;
619   });
620
621   $dbh->do(qq{
622     CREATE OR REPLACE TRIGGER ${q}artist_insert_trg_pk${q}
623     BEFORE INSERT ON ${q}artist${q}
624     FOR EACH ROW
625     BEGIN
626       IF :new.${q}artistid${q} IS NULL THEN
627         SELECT ${q}artist_pk_seq${q}.nextval
628         INTO :new.${q}artistid${q}
629         FROM DUAL;
630       END IF;
631     END;
632   });
633
634   $dbh->do(qq{
635     CREATE OR REPLACE TRIGGER ${q}cd_insert_trg${q}
636     BEFORE INSERT OR UPDATE ON cd
637     FOR EACH ROW
638
639     DECLARE
640     tmpVar NUMBER;
641
642     BEGIN
643       tmpVar := 0;
644
645       IF :new.${q}cdid${q} IS NULL THEN
646         SELECT ${q}cd_seq${q}.nextval
647         INTO tmpVar
648         FROM dual;
649
650         :new.${q}cdid${q} := tmpVar;
651       END IF;
652     END;
653   });
654
655   $dbh->do(qq{
656     CREATE OR REPLACE TRIGGER ${q}track_insert_trg${q}
657     BEFORE INSERT ON ${q}track${q}
658     FOR EACH ROW
659     BEGIN
660       IF :new.${q}trackid${q} IS NULL THEN
661         SELECT ${q}track_seq${q}.nextval
662         INTO :new.${q}trackid${q}
663         FROM DUAL;
664       END IF;
665     END;
666   });
667 }
668
669 # clean up our mess
670 sub do_clean {
671
672   my $dbh = shift || return;
673
674   for my $q ('', '"') {
675     my @clean = (
676       "DROP TRIGGER ${q}track_insert_trg${q}",
677       "DROP TRIGGER ${q}cd_insert_trg${q}",
678       "DROP TRIGGER ${q}artist_insert_trg_auto${q}",
679       "DROP TRIGGER ${q}artist_insert_trg_pk${q}",
680       "DROP SEQUENCE ${q}nonpkid_seq${q}",
681       "DROP SEQUENCE ${q}pkid2_seq${q}",
682       "DROP SEQUENCE ${q}pkid1_seq${q}",
683       "DROP SEQUENCE ${q}track_seq${q}",
684       "DROP SEQUENCE ${q}cd_seq${q}",
685       "DROP SEQUENCE ${q}artist_autoinc_seq${q}",
686       "DROP SEQUENCE ${q}artist_pk_seq${q}",
687       "DROP TABLE ${q}bindtype_test${q}",
688       "DROP TABLE ${q}sequence_test${q}",
689       "DROP TABLE ${q}track${q}",
690       "DROP TABLE ${q}cd${q}",
691       "DROP TABLE ${q}artist${q}",
692     );
693     eval { $dbh -> do ($_) } for @clean;
694   }
695 }
696
697 END {
698   for ($dbh, $dbh2) {
699     next unless $_;
700     local $SIG{__WARN__} = sub {};
701     do_clean($_);
702   }
703   undef $dbh;
704   undef $dbh2;
705 }