5 use DBIx::Class::Schema::Loader 'make_schema_at';
6 use DBIx::Class::Schema::Loader::Utils 'slurp_file';
8 use File::Path 'rmtree';
12 use dbixcsl_common_tests ();
13 use dbixcsl_test_dir '$tdir';
15 use constant EXTRA_DUMP_DIR => "$tdir/ora_extra_dump";
17 my $dsn = $ENV{DBICTEST_ORA_DSN} || '';
18 my $user = $ENV{DBICTEST_ORA_USER} || '';
19 my $password = $ENV{DBICTEST_ORA_PASS} || '';
21 my ($schema, $extra_schema); # for cleanup in END for extra tests
23 my $tester = dbixcsl_common_tests->new(
25 auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY',
27 my ($table, $col) = @_;
29 qq{ CREATE SEQUENCE ${table}_${col}_seq START WITH 1 INCREMENT BY 1},
31 CREATE OR REPLACE TRIGGER ${table}_${col}_trigger
32 BEFORE INSERT ON ${table}
35 SELECT ${table}_${col}_seq.nextval INTO :NEW.${col} FROM dual;
40 auto_inc_drop_cb => sub {
41 my ($table, $col) = @_;
42 return qq{ DROP SEQUENCE ${table}_${col}_seq };
44 preserve_case_mode_is_exclusive => 1,
48 password => $password,
51 # http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330
53 # These tests require at least Oracle 9.2, because of the VARCHAR to
57 'char' => { data_type => 'char', size => 1 },
58 'char(11)' => { data_type => 'char', size => 11 },
59 'nchar' => { data_type => 'nchar', size => 1 },
61 => { data_type => 'nchar', size => 1 },
62 'nchar(11)' => { data_type => 'nchar', size => 11 },
63 'national character(11)'
64 => { data_type => 'nchar', size => 11 },
65 'varchar(20)' => { data_type => 'varchar2', size => 20 },
66 'varchar2(20)' => { data_type => 'varchar2', size => 20 },
67 'nvarchar2(20)'=> { data_type => 'nvarchar2', size => 20 },
68 'national character varying(20)'
69 => { data_type => 'nvarchar2', size => 20 },
73 # integer/decimal/numeric is alised to NUMBER
75 'integer' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
76 'int' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
77 'smallint' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
79 'decimal' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
80 'dec' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
81 'numeric' => { data_type => 'integer', original => { data_type => 'number', size => [38,0] } },
83 'decimal(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
84 'dec(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
85 'numeric(3)' => { data_type => 'numeric', size => [3,0], original => { data_type => 'number' } },
87 'decimal(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
88 'dec(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
89 'numeric(3,3)' => { data_type => 'numeric', size => [3,3], original => { data_type => 'number' } },
91 'binary_float' => { data_type => 'real', original => { data_type => 'binary_float' } },
92 'binary_double' => { data_type => 'double precision', original => { data_type => 'binary_double' } },
94 # these are not mentioned in the summary chart, must be aliased
95 real => { data_type => 'real', original => { data_type => 'float', size => 63 } },
96 'float(63)' => { data_type => 'real', original => { data_type => 'float', size => 63 } },
97 'float(64)' => { data_type => 'double precision', original => { data_type => 'float', size => 64 } },
98 'float(126)' => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
99 float => { data_type => 'double precision', original => { data_type => 'float', size => 126 } },
102 'raw(50)' => { data_type => 'raw', size => 50 },
103 'clob' => { data_type => 'clob' },
104 'nclob' => { data_type => 'nclob' },
105 'blob' => { data_type => 'blob' },
106 'bfile' => { data_type => 'bfile' },
107 'long' => { data_type => 'long' },
108 'long raw' => { data_type => 'long raw' },
111 'date' => { data_type => 'datetime', original => { data_type => 'date' } },
112 'date default sysdate'
113 => { data_type => 'datetime', default_value => \'current_timestamp',
114 original => { data_type => 'date', default_value => \'sysdate' } },
115 'timestamp' => { data_type => 'timestamp' },
116 'timestamp default current_timestamp'
117 => { data_type => 'timestamp', default_value => \'current_timestamp' },
118 'timestamp(3)' => { data_type => 'timestamp', size => 3 },
119 'timestamp with time zone'
120 => { data_type => 'timestamp with time zone' },
121 'timestamp(3) with time zone'
122 => { data_type => 'timestamp with time zone', size => 3 },
123 'timestamp with local time zone'
124 => { data_type => 'timestamp with local time zone' },
125 'timestamp(3) with local time zone'
126 => { data_type => 'timestamp with local time zone', size => 3 },
127 'interval year to month'
128 => { data_type => 'interval year to month' },
129 'interval year(3) to month'
130 => { data_type => 'interval year to month', size => 3 },
131 'interval day to second'
132 => { data_type => 'interval day to second' },
133 'interval day(3) to second'
134 => { data_type => 'interval day to second', size => [3,6] },
135 'interval day to second(3)'
136 => { data_type => 'interval day to second', size => [2,3] },
137 'interval day(3) to second(3)'
138 => { data_type => 'interval day to second', size => [3,3] },
141 'rowid' => { data_type => 'rowid' },
142 'urowid' => { data_type => 'urowid' },
143 'urowid(3333)' => { data_type => 'urowid', size => 3333 },
148 CREATE TABLE oracle_loader_test1 (
153 q{ COMMENT ON TABLE oracle_loader_test1 IS 'oracle_loader_test1 table comment' },
154 q{ COMMENT ON COLUMN oracle_loader_test1.value IS 'oracle_loader_test1.value column comment' },
156 drop => [qw/oracle_loader_test1/],
159 my ($monikers, $classes);
160 ($schema, $monikers, $classes) = @_;
163 if (my $source = $monikers->{loader_test1s}) {
164 is $schema->source($source)->column_info('id')->{sequence},
165 'loader_test1s_id_seq',
166 'Oracle sequence detection';
169 skip 'not running common tests', 1;
173 my $class = $classes->{oracle_loader_test1};
175 my $filename = $schema->loader->get_dump_filename($class);
176 my $code = slurp_file $filename;
178 like $code, qr/^=head1 NAME\n\n^$class - oracle_loader_test1 table comment\n\n^=cut\n/m,
181 like $code, qr/^=head2 value\n\n(.+:.+\n)+\noracle_loader_test1\.value column comment\n\n/m,
182 'column comment and attrs';
185 skip 'Set the DBICTEST_ORA_EXTRAUSER_DSN, _USER and _PASS environment variables to run the cross-schema relationship tests', 6 * 2
186 unless $ENV{DBICTEST_ORA_EXTRAUSER_DSN};
188 $extra_schema = $schema->clone;
189 $extra_schema->connection(@ENV{map "DBICTEST_ORA_EXTRAUSER_$_",
193 my $dbh1 = $schema->storage->dbh;
194 my $dbh2 = $extra_schema->storage->dbh;
196 my ($schema1) = $dbh1->selectrow_array('SELECT USER FROM DUAL');
197 my ($schema2) = $dbh2->selectrow_array('SELECT USER FROM DUAL');
200 CREATE TABLE oracle_loader_test4 (
201 id INT NOT NULL PRIMARY KEY,
205 $dbh1->do("GRANT ALL ON oracle_loader_test4 TO $schema2");
207 CREATE TABLE oracle_loader_test6 (
208 id INT NOT NULL PRIMARY KEY,
210 oracle_loader_test4_id INT REFERENCES ${schema1}.oracle_loader_test4 (id)
213 $dbh2->do("GRANT ALL ON oracle_loader_test6 to $schema1");
215 CREATE TABLE oracle_loader_test7 (
216 id INT NOT NULL PRIMARY KEY,
220 $dbh2->do("GRANT ALL ON oracle_loader_test7 to $schema1");
222 CREATE TABLE oracle_loader_test8 (
223 id INT NOT NULL PRIMARY KEY,
225 oracle_loader_test7_id INT REFERENCES ${schema2}.oracle_loader_test7 (id)
229 foreach my $db_schema ([$schema1, $schema2], '%') {
231 rmtree EXTRA_DUMP_DIR;
234 local $SIG{__WARN__} = sub {
235 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
242 db_schema => $db_schema,
244 dump_directory => EXTRA_DUMP_DIR,
247 [ $dsn, $user, $password ],
250 diag join "\n", @warns if @warns;
253 } qq{dumped schema for "$schema1" and "$schema2" schemas with no warnings};
258 ok $test_schema = OracleMultiSchema->connect($dsn, $user, $password);
259 } 'connected test schema';
262 ok $test_schema->source('OracleLoaderTest6')
263 ->has_relationship('oracle_loader_test4');
264 } 'cross-schema relationship in multi-db_schema';
267 ok $test_schema->source('OracleLoaderTest4')
268 ->has_relationship('oracle_loader_test6s');
269 } 'cross-schema relationship in multi-db_schema';
272 ok $test_schema->source('OracleLoaderTest8')
273 ->has_relationship('oracle_loader_test7');
274 } 'cross-schema relationship in multi-db_schema';
277 ok $test_schema->source('OracleLoaderTest7')
278 ->has_relationship('oracle_loader_test8s');
279 } 'cross-schema relationship in multi-db_schema';
286 if( !$dsn || !$user ) {
287 $tester->skip_tests('You need to set the DBICTEST_ORA_DSN, _USER, and _PASS environment variables');
290 $tester->run_tests();
294 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
295 if (my $dbh2 = try { $extra_schema->storage->dbh }) {
296 my $dbh1 = $schema->storage->dbh;
299 $dbh2->do('DROP TABLE oracle_loader_test6');
300 $dbh1->do('DROP TABLE oracle_loader_test4');
301 $dbh1->do('DROP TABLE oracle_loader_test8');
302 $dbh2->do('DROP TABLE oracle_loader_test7');
305 die "Error dropping cross-schema test tables: $_";
309 rmtree EXTRA_DUMP_DIR;
312 # vim:et sw=4 sts=4 tw=0: