Commit | Line | Data |
e7262300 |
1 | use strict; |
fcf328c7 |
2 | use warnings; |
6b0e47fc |
3 | use Test::More; |
4 | use Test::Exception; |
c4a69b87 |
5 | use DBIx::Class::Schema::Loader 'make_schema_at'; |
fcf328c7 |
6 | use DBIx::Class::Schema::Loader::Utils 'slurp_file'; |
c4a69b87 |
7 | use Try::Tiny; |
8 | use File::Path 'rmtree'; |
fcf328c7 |
9 | use namespace::clean; |
c4a69b87 |
10 | |
fcf328c7 |
11 | use lib qw(t/lib); |
c4a69b87 |
12 | use dbixcsl_common_tests (); |
13 | use dbixcsl_test_dir '$tdir'; |
14 | |
15 | use constant EXTRA_DUMP_DIR => "$tdir/ora_extra_dump"; |
e7262300 |
16 | |
17 | my $dsn = $ENV{DBICTEST_ORA_DSN} || ''; |
18 | my $user = $ENV{DBICTEST_ORA_USER} || ''; |
19 | my $password = $ENV{DBICTEST_ORA_PASS} || ''; |
20 | |
c4a69b87 |
21 | my ($schema, $extra_schema); # for cleanup in END for extra tests |
22 | |
e7262300 |
23 | my $tester = dbixcsl_common_tests->new( |
24 | vendor => 'Oracle', |
c1ac681d |
25 | auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY', |
26 | auto_inc_cb => sub { |
27 | my ($table, $col) = @_; |
28 | return ( |
29 | qq{ CREATE SEQUENCE ${table}_${col}_seq START WITH 1 INCREMENT BY 1}, |
30 | qq{ |
31 | CREATE OR REPLACE TRIGGER ${table}_${col}_trigger |
32 | BEFORE INSERT ON ${table} |
33 | FOR EACH ROW |
34 | BEGIN |
35 | SELECT ${table}_${col}_seq.nextval INTO :NEW.${col} FROM dual; |
36 | END; |
37 | } |
38 | ); |
39 | }, |
40 | auto_inc_drop_cb => sub { |
41 | my ($table, $col) = @_; |
42 | return qq{ DROP SEQUENCE ${table}_${col}_seq }; |
43 | }, |
b511f36e |
44 | preserve_case_mode_is_exclusive => 1, |
45 | quote_char => '"', |
e7262300 |
46 | dsn => $dsn, |
47 | user => $user, |
48 | password => $password, |
760fd65c |
49 | data_types => { |
50 | # From: |
51 | # http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330 |
52 | # |
53 | # These tests require at least Oracle 9.2, because of the VARCHAR to |
54 | # VARCHAR2 casting. |
55 | # |
56 | # Character Types |
57 | 'char' => { data_type => 'char', size => 1 }, |
58 | 'char(11)' => { data_type => 'char', size => 11 }, |
59 | 'nchar' => { data_type => 'nchar', size => 1 }, |
bc319c63 |
60 | 'national character' |
61 | => { data_type => 'nchar', size => 1 }, |
760fd65c |
62 | 'nchar(11)' => { data_type => 'nchar', size => 11 }, |
bc319c63 |
63 | 'national character(11)' |
64 | => { data_type => 'nchar', size => 11 }, |
760fd65c |
65 | 'varchar(20)' => { data_type => 'varchar2', size => 20 }, |
66 | 'varchar2(20)' => { data_type => 'varchar2', size => 20 }, |
67 | 'nvarchar2(20)'=> { data_type => 'nvarchar2', size => 20 }, |
bc319c63 |
68 | 'national character varying(20)' |
69 | => { data_type => 'nvarchar2', size => 20 }, |
760fd65c |
70 | |
71 | # Numeric Types |
72 | # |
1c22571b |
73 | # integer/decimal/numeric is alised to NUMBER |
760fd65c |
74 | # |
4ea15dfe |
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] } }, |
760fd65c |
78 | |
4ea15dfe |
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] } }, |
760fd65c |
82 | |
4ea15dfe |
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' } }, |
760fd65c |
86 | |
4ea15dfe |
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' } }, |
760fd65c |
90 | |
1c22571b |
91 | 'binary_float' => { data_type => 'real', original => { data_type => 'binary_float' } }, |
92 | 'binary_double' => { data_type => 'double precision', original => { data_type => 'binary_double' } }, |
93 | |
94 | # these are not mentioned in the summary chart, must be aliased |
ceb009d3 |
95 | real => { data_type => 'real', original => { data_type => 'float', size => 63 } }, |
1c22571b |
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 } }, |
760fd65c |
100 | |
101 | # Blob Types |
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' }, |
3b29d0ac |
107 | 'long' => { data_type => 'long' }, |
760fd65c |
108 | 'long raw' => { data_type => 'long raw' }, |
109 | |
110 | # Datetime Types |
3785bc2e |
111 | 'date' => { data_type => 'datetime', original => { data_type => 'date' } }, |
760fd65c |
112 | 'date default sysdate' |
3785bc2e |
113 | => { data_type => 'datetime', default_value => \'current_timestamp', |
114 | original => { data_type => 'date', default_value => \'sysdate' } }, |
760fd65c |
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] }, |
139 | |
140 | # Other Types |
141 | 'rowid' => { data_type => 'rowid' }, |
142 | 'urowid' => { data_type => 'urowid' }, |
143 | 'urowid(3333)' => { data_type => 'urowid', size => 3333 }, |
144 | }, |
5cd600fa |
145 | extra => { |
4cd5155b |
146 | create => [ |
147 | q{ |
148 | CREATE TABLE oracle_loader_test1 ( |
149 | id NUMBER(11), |
150 | value VARCHAR2(100) |
151 | ) |
152 | }, |
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' }, |
155 | ], |
156 | drop => [qw/oracle_loader_test1/], |
c4a69b87 |
157 | count => 3 + 6 * 2, |
5cd600fa |
158 | run => sub { |
c4a69b87 |
159 | my ($monikers, $classes); |
160 | ($schema, $monikers, $classes) = @_; |
5cd600fa |
161 | |
162 | SKIP: { |
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'; |
167 | } |
168 | else { |
7640ef4b |
169 | skip 'not running common tests', 1; |
5cd600fa |
170 | } |
171 | } |
4cd5155b |
172 | |
61d1cca1 |
173 | my $class = $classes->{oracle_loader_test1}; |
c4a69b87 |
174 | |
175 | my $filename = $schema->loader->get_dump_filename($class); |
fcf328c7 |
176 | my $code = slurp_file $filename; |
4cd5155b |
177 | |
61d1cca1 |
178 | like $code, qr/^=head1 NAME\n\n^$class - oracle_loader_test1 table comment\n\n^=cut\n/m, |
179 | 'table comment'; |
4cd5155b |
180 | |
61d1cca1 |
181 | like $code, qr/^=head2 value\n\n(.+:.+\n)+\noracle_loader_test1\.value column comment\n\n/m, |
182 | 'column comment and attrs'; |
c4a69b87 |
183 | |
184 | SKIP: { |
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}; |
187 | |
188 | $extra_schema = $schema->clone; |
189 | $extra_schema->connection(@ENV{map "DBICTEST_ORA_EXTRAUSER_$_", |
190 | qw/DSN USER PASS/ |
191 | }); |
192 | |
193 | my $dbh1 = $schema->storage->dbh; |
194 | my $dbh2 = $extra_schema->storage->dbh; |
195 | |
196 | my ($schema1) = $dbh1->selectrow_array('SELECT USER FROM DUAL'); |
197 | my ($schema2) = $dbh2->selectrow_array('SELECT USER FROM DUAL'); |
198 | |
199 | $dbh1->do(<<'EOF'); |
200 | CREATE TABLE oracle_loader_test4 ( |
201 | id INT NOT NULL PRIMARY KEY, |
202 | value VARCHAR(100) |
203 | ) |
204 | EOF |
205 | $dbh1->do("GRANT ALL ON oracle_loader_test4 TO $schema2"); |
206 | $dbh2->do(<<"EOF"); |
207 | CREATE TABLE oracle_loader_test6 ( |
208 | id INT NOT NULL PRIMARY KEY, |
209 | value VARCHAR(100), |
210 | oracle_loader_test4_id INT REFERENCES ${schema1}.oracle_loader_test4 (id) |
211 | ) |
212 | EOF |
213 | $dbh2->do("GRANT ALL ON oracle_loader_test6 to $schema1"); |
214 | $dbh2->do(<<"EOF"); |
215 | CREATE TABLE oracle_loader_test7 ( |
216 | id INT NOT NULL PRIMARY KEY, |
217 | value VARCHAR(100) |
218 | ) |
219 | EOF |
220 | $dbh2->do("GRANT ALL ON oracle_loader_test7 to $schema1"); |
221 | $dbh1->do(<<"EOF"); |
222 | CREATE TABLE oracle_loader_test8 ( |
223 | id INT NOT NULL PRIMARY KEY, |
224 | value VARCHAR(100), |
225 | oracle_loader_test7_id INT REFERENCES ${schema2}.oracle_loader_test7 (id) |
226 | ) |
227 | EOF |
228 | |
229 | foreach my $db_schema ([$schema1, $schema2], '%') { |
230 | lives_and { |
231 | rmtree EXTRA_DUMP_DIR; |
232 | |
233 | my @warns; |
234 | local $SIG{__WARN__} = sub { |
235 | push @warns, $_[0] unless $_[0] =~ /\bcollides\b/; |
236 | }; |
237 | |
238 | make_schema_at( |
239 | 'OracleMultiSchema', |
240 | { |
241 | naming => 'current', |
242 | db_schema => $db_schema, |
243 | preserve_case => 1, |
244 | dump_directory => EXTRA_DUMP_DIR, |
245 | quiet => 1, |
246 | }, |
247 | [ $dsn, $user, $password ], |
248 | ); |
249 | |
250 | diag join "\n", @warns if @warns; |
251 | |
252 | is @warns, 0; |
253 | } qq{dumped schema for "$schema1" and "$schema2" schemas with no warnings}; |
254 | |
255 | my $test_schema; |
256 | |
257 | lives_and { |
258 | ok $test_schema = OracleMultiSchema->connect($dsn, $user, $password); |
259 | } 'connected test schema'; |
260 | |
261 | lives_and { |
262 | ok $test_schema->source('OracleLoaderTest6') |
263 | ->has_relationship('oracle_loader_test4'); |
264 | } 'cross-schema relationship in multi-db_schema'; |
265 | |
266 | lives_and { |
267 | ok $test_schema->source('OracleLoaderTest4') |
268 | ->has_relationship('oracle_loader_test6s'); |
269 | } 'cross-schema relationship in multi-db_schema'; |
270 | |
271 | lives_and { |
272 | ok $test_schema->source('OracleLoaderTest8') |
273 | ->has_relationship('oracle_loader_test7'); |
274 | } 'cross-schema relationship in multi-db_schema'; |
275 | |
276 | lives_and { |
277 | ok $test_schema->source('OracleLoaderTest7') |
278 | ->has_relationship('oracle_loader_test8s'); |
279 | } 'cross-schema relationship in multi-db_schema'; |
280 | } |
281 | } |
5cd600fa |
282 | }, |
283 | }, |
e7262300 |
284 | ); |
285 | |
286 | if( !$dsn || !$user ) { |
287 | $tester->skip_tests('You need to set the DBICTEST_ORA_DSN, _USER, and _PASS environment variables'); |
288 | } |
289 | else { |
290 | $tester->run_tests(); |
291 | } |
c4a69b87 |
292 | |
293 | END { |
294 | if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) { |
295 | if (my $dbh2 = try { $extra_schema->storage->dbh }) { |
296 | my $dbh1 = $schema->storage->dbh; |
297 | |
298 | try { |
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'); |
303 | } |
304 | catch { |
305 | die "Error dropping cross-schema test tables: $_"; |
306 | }; |
307 | } |
308 | |
309 | rmtree EXTRA_DUMP_DIR; |
310 | } |
311 | } |
760fd65c |
312 | # vim:et sw=4 sts=4 tw=0: |