5 use DBIx::Class::Schema::Loader 'make_schema_at';
6 use DBIx::Class::Schema::Loader::Utils qw/slurp_file split_name/;
8 use File::Path 'rmtree';
9 use String::ToIdentifier::EN::Unicode 'to_identifier';
13 use dbixcsl_common_tests ();
14 use dbixcsl_test_dir '$tdir';
16 use constant EXTRA_DUMP_DIR => "$tdir/ora_extra_dump";
18 my $dsn = $ENV{DBICTEST_ORA_DSN} || '';
19 my $user = $ENV{DBICTEST_ORA_USER} || '';
20 my $password = $ENV{DBICTEST_ORA_PASS} || '';
22 my ($schema, $extra_schema); # for cleanup in END for extra tests
24 my $auto_inc_cb = sub {
25 my ($table, $col) = @_;
27 qq{ CREATE SEQUENCE ${table}_${col}_seq START WITH 1 INCREMENT BY 1},
29 CREATE OR REPLACE TRIGGER ${table}_${col}_trigger
30 BEFORE INSERT ON ${table}
33 SELECT ${table}_${col}_seq.nextval INTO :NEW.${col} FROM dual;
39 my $auto_inc_drop_cb = sub {
40 my ($table, $col) = @_;
41 return qq{ DROP SEQUENCE ${table}_${col}_seq };
44 my $tester = dbixcsl_common_tests->new(
46 auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY',
47 auto_inc_cb => $auto_inc_cb,
48 auto_inc_drop_cb => $auto_inc_drop_cb,
49 preserve_case_mode_is_exclusive => 1,
53 password => $password,
56 # http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330
58 # These tests require at least Oracle 9.2, because of the VARCHAR to
62 'char' => { data_type => 'char', size => 1 },
63 'char(11)' => { data_type => 'char', size => 11 },
64 'nchar' => { data_type => 'nchar', size => 1 },
66 => { data_type => 'nchar', size => 1 },
67 'nchar(11)' => { data_type => 'nchar', size => 11 },
68 'national character(11)'
69 => { data_type => 'nchar', size => 11 },
70 'varchar(20)' => { data_type => 'varchar2', size => 20 },
71 'varchar2(20)' => { data_type => 'varchar2', size => 20 },
72 'nvarchar2(20)'=> { data_type => 'nvarchar2', size => 20 },
73 'national character varying(20)'
74 => { data_type => 'nvarchar2', size => 20 },
78 # integer/decimal/numeric is alised to NUMBER
80 'integer' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
81 'int' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
82 'smallint' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
84 # very long DEFAULT throws an ORA-24345
85 "number(15) DEFAULT to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))" => {
86 data_type => 'numeric', size => [15,0], original => { data_type => 'number' },
87 default_value => \"to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))"
90 'decimal' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
91 'dec' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
92 'numeric' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
94 'decimal(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
95 'dec(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
96 'numeric(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
98 'decimal(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
99 'dec(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
100 'numeric(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
102 'binary_float' => { data_type => 'real', original => { data_type => 'binary_float' } },
103 'binary_double' => { data_type => 'double precision', original => { data_type => 'binary_double' } },
105 # these are not mentioned in the summary chart, must be aliased
106 real => { data_type => 'real', original => { data_type => 'float', size => 63 } },
107 'float(63)' => { data_type => 'real', original => { data_type => 'float', size => 63 } },
108 'float(64)' => { data_type => 'double precision', original => { data_type => 'float', size => 64 } },
109 'float(126)' => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
110 float => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
113 'raw(50)' => { data_type => 'raw', size => 50 },
114 'clob' => { data_type => 'clob' },
115 'nclob' => { data_type => 'nclob' },
116 'blob' => { data_type => 'blob' },
117 'bfile' => { data_type => 'bfile' },
118 'long' => { data_type => 'long' },
119 'long raw' => { data_type => 'long raw' },
122 'date' => { data_type => 'datetime', original => { data_type => 'date' } },
123 'date default sysdate'
124 => { data_type => 'datetime', default_value => \'current_timestamp',
125 original => { data_type => 'date', default_value => \'sysdate' } },
126 'timestamp' => { data_type => 'timestamp' },
127 'timestamp default current_timestamp'
128 => { data_type => 'timestamp', default_value => \'current_timestamp' },
129 'timestamp(3)' => { data_type => 'timestamp', size => 3 },
130 'timestamp with time zone'
131 => { data_type => 'timestamp with time zone' },
132 'timestamp(3) with time zone'
133 => { data_type => 'timestamp with time zone', size => 3 },
134 'timestamp with local time zone'
135 => { data_type => 'timestamp with local time zone' },
136 'timestamp(3) with local time zone'
137 => { data_type => 'timestamp with local time zone', size => 3 },
138 'interval year to month'
139 => { data_type => 'interval year to month' },
140 'interval year(3) to month'
141 => { data_type => 'interval year to month', size => 3 },
142 'interval day to second'
143 => { data_type => 'interval day to second' },
144 'interval day(3) to second'
145 => { data_type => 'interval day to second', size => [3,6] },
146 'interval day to second(3)'
147 => { data_type => 'interval day to second', size => [2,3] },
148 'interval day(3) to second(3)'
149 => { data_type => 'interval day to second', size => [3,3] },
152 'rowid' => { data_type => 'rowid' },
153 'urowid' => { data_type => 'urowid' },
154 'urowid(3333)' => { data_type => 'urowid', size => 3333 },
159 CREATE TABLE oracle_loader_test1 (
164 q{ COMMENT ON TABLE oracle_loader_test1 IS 'oracle_loader_test1 table comment' },
165 q{ COMMENT ON COLUMN oracle_loader_test1.value IS 'oracle_loader_test1.value column comment' },
167 drop => [qw/oracle_loader_test1/],
170 my ($monikers, $classes);
171 ($schema, $monikers, $classes) = @_;
174 if (my $source = $monikers->{loader_test1s}) {
175 is $schema->source($source)->column_info('id')->{sequence},
176 'loader_test1s_id_seq',
177 'Oracle sequence detection';
180 skip 'not running common tests', 1;
184 my $class = $classes->{oracle_loader_test1};
186 my $filename = $schema->loader->get_dump_filename($class);
187 my $code = slurp_file $filename;
189 like $code, qr/^=head1 NAME\n\n^$class - oracle_loader_test1 table comment\n\n^=cut\n/m,
192 like $code, qr/^=head2 value\n\n(.+:.+\n)+\noracle_loader_test1\.value column comment\n\n/m,
193 'column comment and attrs';
196 skip 'Set the DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS environment variables to run the cross-schema relationship tests', 6 * 2
197 unless $ENV{DBICTEST_ORA_EXTRAUSER_DSN};
199 $extra_schema = $schema->clone;
200 $extra_schema->connection(@ENV{map "DBICTEST_ORA_EXTRAUSER_$_",
204 my $dbh1 = $schema->storage->dbh;
205 my $dbh2 = $extra_schema->storage->dbh;
207 my ($schema1) = $dbh1->selectrow_array('SELECT USER FROM DUAL');
208 my ($schema2) = $dbh2->selectrow_array('SELECT USER FROM DUAL');
211 CREATE TABLE oracle_loader_test4 (
212 id INT NOT NULL PRIMARY KEY,
217 $dbh1->do($_) for $auto_inc_cb->('oracle_loader_test4', 'id');
219 $dbh1->do("GRANT ALL ON oracle_loader_test4 TO $schema2");
220 $dbh1->do("GRANT ALL ON oracle_loader_test4_id_seq TO $schema2");
223 CREATE TABLE oracle_loader_test5 (
224 id INT NOT NULL PRIMARY KEY,
226 four_id INT REFERENCES ${schema1}.oracle_loader_test4 (id),
227 CONSTRAINT ora_loader5_uniq UNIQUE (four_id)
230 $dbh1->do($_) for $auto_inc_cb->('oracle_loader_test5', 'id');
231 $dbh1->do("GRANT ALL ON oracle_loader_test5 TO $schema2");
232 $dbh1->do("GRANT ALL ON oracle_loader_test5_id_seq TO $schema2");
235 CREATE TABLE oracle_loader_test5 (
236 pk INT NOT NULL PRIMARY KEY,
238 four_id INT REFERENCES ${schema1}.oracle_loader_test4 (id),
239 CONSTRAINT ora_loader5_uniq UNIQUE (four_id)
242 $dbh2->do($_) for $auto_inc_cb->('oracle_loader_test5', 'pk');
243 $dbh2->do("GRANT ALL ON oracle_loader_test5 TO $schema1");
244 $dbh2->do("GRANT ALL ON oracle_loader_test5_pk_seq TO $schema1");
247 CREATE TABLE oracle_loader_test6 (
248 id INT NOT NULL PRIMARY KEY,
250 oracle_loader_test4_id INT REFERENCES ${schema1}.oracle_loader_test4 (id)
253 $dbh2->do($_) for $auto_inc_cb->('oracle_loader_test6', 'id');
254 $dbh2->do("GRANT ALL ON oracle_loader_test6 to $schema1");
255 $dbh2->do("GRANT ALL ON oracle_loader_test6_id_seq TO $schema1");
258 CREATE TABLE oracle_loader_test7 (
259 id INT NOT NULL PRIMARY KEY,
261 six_id INT UNIQUE REFERENCES ${schema2}.oracle_loader_test6 (id)
264 $dbh2->do($_) for $auto_inc_cb->('oracle_loader_test7', 'id');
265 $dbh2->do("GRANT ALL ON oracle_loader_test7 to $schema1");
266 $dbh2->do("GRANT ALL ON oracle_loader_test7_id_seq TO $schema1");
269 CREATE TABLE oracle_loader_test8 (
270 id INT NOT NULL PRIMARY KEY,
272 oracle_loader_test7_id INT REFERENCES ${schema2}.oracle_loader_test7 (id)
275 $dbh1->do($_) for $auto_inc_cb->('oracle_loader_test8', 'id');
276 $dbh1->do("GRANT ALL ON oracle_loader_test8 to $schema2");
277 $dbh1->do("GRANT ALL ON oracle_loader_test8_id_seq TO $schema2");
279 # We add schema to moniker_parts, so make a monikers hash for
280 # the tests, of the form schemanum.tablenum
281 my $schema1_moniker = join '', map ucfirst lc, split_name to_identifier $schema1;
282 my $schema2_moniker = join '', map ucfirst lc, split_name to_identifier $schema2;
285 $monikers{'1.5'} = $schema1_moniker . 'OracleLoaderTest5';
286 $monikers{'2.5'} = $schema2_moniker . 'OracleLoaderTest5';
288 foreach my $db_schema ([$schema1, $schema2], '%') {
290 rmtree EXTRA_DUMP_DIR;
293 local $SIG{__WARN__} = sub {
294 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
301 db_schema => $db_schema,
302 dump_directory => EXTRA_DUMP_DIR,
305 [ $dsn, $user, $password ],
308 diag join "\n", @warns if @warns;
311 } qq{dumped schema for "$schema1" and "$schema2" schemas with no warnings};
313 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
316 ok $test_schema = OracleMultiSchema->connect($dsn, $user, $password);
317 } 'connected test schema';
320 ok $rsrc = $test_schema->source('OracleLoaderTest4');
321 } 'got source for table in schema1';
323 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
326 is try { $rsrc->column_info('value')->{data_type} }, 'varchar2',
329 is try { $rsrc->column_info('value')->{size} }, 100,
333 ok $rs = $test_schema->resultset('OracleLoaderTest4');
334 } 'got resultset for table in schema1';
337 ok $row = $rs->create({ value => 'foo' });
338 } 'executed SQL on table in schema1';
340 my $schema1_identifier = join '_', map lc, split_name to_identifier $schema1;
342 $rel_info = try { $rsrc->relationship_info(
343 $schema1_identifier . '_oracle_loader_test5'
346 is_deeply $rel_info->{cond}, {
347 'foreign.four_id' => 'self.id'
348 }, 'relationship in schema1';
350 is $rel_info->{attrs}{accessor}, 'single',
351 'relationship in schema1';
353 is $rel_info->{attrs}{join_type}, 'LEFT',
354 'relationship in schema1';
357 ok $rsrc = $test_schema->source($monikers{'1.5'});
358 } 'got source for table in schema1';
360 %uniqs = try { $rsrc->unique_constraints };
363 'got unique and primary constraint in schema1';
365 delete $uniqs{primary};
367 is_deeply ((values %uniqs)[0], ['four_id'],
368 'correct unique constraint in schema1');
371 ok $rsrc = $test_schema->source('OracleLoaderTest6');
372 } 'got source for table in schema2';
374 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
375 'column in schema2 introspected correctly';
377 is try { $rsrc->column_info('value')->{data_type} }, 'varchar2',
378 'column in schema2 introspected correctly';
380 is try { $rsrc->column_info('value')->{size} }, 100,
381 'column in schema2 introspected correctly';
384 ok $rs = $test_schema->resultset('OracleLoaderTest6');
385 } 'got resultset for table in schema2';
388 ok $row = $rs->create({ value => 'foo' });
389 } 'executed SQL on table in schema2';
391 $rel_info = try { $rsrc->relationship_info('oracle_loader_test7') };
393 is_deeply $rel_info->{cond}, {
394 'foreign.six_id' => 'self.id'
395 }, 'relationship in schema2';
397 is $rel_info->{attrs}{accessor}, 'single',
398 'relationship in schema2';
400 is $rel_info->{attrs}{join_type}, 'LEFT',
401 'relationship in schema2';
404 ok $rsrc = $test_schema->source('OracleLoaderTest7');
405 } 'got source for table in schema2';
407 %uniqs = try { $rsrc->unique_constraints };
410 'got unique and primary constraint in schema2';
412 delete $uniqs{primary};
414 is_deeply ((values %uniqs)[0], ['six_id'],
415 'correct unique constraint in schema2');
418 ok $test_schema->source('OracleLoaderTest6')
419 ->has_relationship('oracle_loader_test4');
420 } 'cross-schema relationship in multi-db_schema';
423 ok $test_schema->source('OracleLoaderTest4')
424 ->has_relationship('oracle_loader_test6s');
425 } 'cross-schema relationship in multi-db_schema';
428 ok $test_schema->source('OracleLoaderTest8')
429 ->has_relationship('oracle_loader_test7');
430 } 'cross-schema relationship in multi-db_schema';
433 ok $test_schema->source('OracleLoaderTest7')
434 ->has_relationship('oracle_loader_test8s');
435 } 'cross-schema relationship in multi-db_schema';
442 if( !$dsn || !$user ) {
443 $tester->skip_tests('You need to set the DBICTEST_ORA_DSN, _USER, and _PASS environment variables');
446 $tester->run_tests();
450 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
451 if (my $dbh2 = try { $extra_schema->storage->dbh }) {
452 my $dbh1 = $schema->storage->dbh;
455 $dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test8', 'id');
456 $dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test7', 'id');
457 $dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test6', 'id');
458 $dbh2->do($_) for $auto_inc_drop_cb->('oracle_loader_test5', 'pk');
459 $dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test5', 'id');
460 $dbh1->do($_) for $auto_inc_drop_cb->('oracle_loader_test4', 'id');
463 die "Error dropping sequences for cross-schema test tables: $_";
467 $dbh1->do('DROP TABLE oracle_loader_test8');
468 $dbh2->do('DROP TABLE oracle_loader_test7');
469 $dbh2->do('DROP TABLE oracle_loader_test6');
470 $dbh2->do('DROP TABLE oracle_loader_test5');
471 $dbh1->do('DROP TABLE oracle_loader_test5');
472 $dbh1->do('DROP TABLE oracle_loader_test4');
475 die "Error dropping cross-schema test tables: $_";
479 rmtree EXTRA_DUMP_DIR;
482 # vim:et sw=4 sts=4 tw=0: