Commit | Line | Data |
8793567f |
1 | use strict; |
5347f501 |
2 | use warnings; |
c4a69b87 |
3 | use Test::More; |
4 | use Test::Exception; |
5 | use Try::Tiny; |
6 | use File::Path 'rmtree'; |
7 | use DBIx::Class::Schema::Loader 'make_schema_at'; |
8 | use Scope::Guard (); |
9 | |
8793567f |
10 | use lib qw(t/lib); |
c4a69b87 |
11 | |
8793567f |
12 | use dbixcsl_common_tests; |
c4a69b87 |
13 | use dbixcsl_test_dir '$tdir'; |
14 | |
15 | use constant EXTRA_DUMP_DIR => "$tdir/sqlanywhere_extra_dump"; |
8793567f |
16 | |
17 | # The default max_cursor_count and max_statement_count settings of 50 are too |
18 | # low to run this test. |
8a64178e |
19 | # |
20 | # Setting them to zero is preferred. |
8793567f |
21 | |
637a5785 |
22 | my $dbd_sqlanywhere_dsn = $ENV{DBICTEST_SQLANYWHERE_DSN} || ''; |
23 | my $dbd_sqlanywhere_user = $ENV{DBICTEST_SQLANYWHERE_USER} || ''; |
24 | my $dbd_sqlanywhere_password = $ENV{DBICTEST_SQLANYWHERE_PASS} || ''; |
5347f501 |
25 | |
637a5785 |
26 | my $odbc_dsn = $ENV{DBICTEST_SQLANYWHERE_ODBC_DSN} || ''; |
27 | my $odbc_user = $ENV{DBICTEST_SQLANYWHERE_ODBC_USER} || ''; |
28 | my $odbc_password = $ENV{DBICTEST_SQLANYWHERE_ODBC_PASS} || ''; |
8793567f |
29 | |
c4a69b87 |
30 | my ($schema, $schemas_created); # for cleanup in END for extra tests |
31 | |
8793567f |
32 | my $tester = dbixcsl_common_tests->new( |
33 | vendor => 'SQLAnywhere', |
34 | auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY', |
5347f501 |
35 | connect_info => [ ($dbd_sqlanywhere_dsn ? { |
36 | dsn => $dbd_sqlanywhere_dsn, |
37 | user => $dbd_sqlanywhere_user, |
38 | password => $dbd_sqlanywhere_password, |
39 | } : ()), |
40 | ($odbc_dsn ? { |
41 | dsn => $odbc_dsn, |
42 | user => $odbc_user, |
43 | password => $odbc_password, |
44 | } : ()), |
45 | ], |
dd87d4c4 |
46 | loader_options => { preserve_case => 1 }, |
9dc968df |
47 | data_types => { |
48 | # http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html |
49 | # |
50 | # Numeric types |
51 | 'bit' => { data_type => 'bit' }, |
52 | 'tinyint' => { data_type => 'tinyint' }, |
53 | 'smallint' => { data_type => 'smallint' }, |
54 | 'int' => { data_type => 'integer' }, |
55 | 'integer' => { data_type => 'integer' }, |
56 | 'bigint' => { data_type => 'bigint' }, |
dd87d4c4 |
57 | 'float' => { data_type => 'real' }, |
58 | 'real' => { data_type => 'real' }, |
9dc968df |
59 | 'double' => { data_type => 'double precision' }, |
60 | 'double precision' => |
61 | { data_type => 'double precision' }, |
62 | |
dd87d4c4 |
63 | 'float(2)' => { data_type => 'real' }, |
64 | 'float(24)' => { data_type => 'real' }, |
9dc968df |
65 | 'float(25)' => { data_type => 'double precision' }, |
66 | 'float(53)' => { data_type => 'double precision' }, |
67 | |
68 | # This test only works with the default precision and scale options. |
69 | # |
70 | # They are preserved even for the default values, because the defaults |
71 | # can be changed. |
72 | 'decimal' => { data_type => 'decimal', size => [30,6] }, |
73 | 'dec' => { data_type => 'decimal', size => [30,6] }, |
74 | 'numeric' => { data_type => 'numeric', size => [30,6] }, |
75 | |
76 | 'decimal(3)' => { data_type => 'decimal', size => [3,0] }, |
77 | 'dec(3)' => { data_type => 'decimal', size => [3,0] }, |
78 | 'numeric(3)' => { data_type => 'numeric', size => [3,0] }, |
79 | |
80 | 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] }, |
81 | 'dec(3,3)' => { data_type => 'decimal', size => [3,3] }, |
82 | 'numeric(3,3)' => { data_type => 'numeric', size => [3,3] }, |
83 | |
84 | 'decimal(18,18)' => { data_type => 'decimal', size => [18,18] }, |
85 | 'dec(18,18)' => { data_type => 'decimal', size => [18,18] }, |
86 | 'numeric(18,18)' => { data_type => 'numeric', size => [18,18] }, |
87 | |
88 | # money types |
89 | 'money' => { data_type => 'money' }, |
90 | 'smallmoney' => { data_type => 'smallmoney' }, |
91 | |
92 | # bit arrays |
93 | 'long varbit' => { data_type => 'long varbit' }, |
94 | 'long bit varying' |
95 | => { data_type => 'long varbit' }, |
96 | 'varbit' => { data_type => 'varbit', size => 1 }, |
97 | 'varbit(20)' => { data_type => 'varbit', size => 20 }, |
98 | 'bit varying' => { data_type => 'varbit', size => 1 }, |
99 | 'bit varying(20)' |
100 | => { data_type => 'varbit', size => 20 }, |
101 | |
102 | # Date and Time Types |
103 | 'date' => { data_type => 'date' }, |
104 | 'datetime' => { data_type => 'datetime' }, |
105 | 'smalldatetime' |
106 | => { data_type => 'smalldatetime' }, |
107 | 'timestamp' => { data_type => 'timestamp' }, |
6e566cc4 |
108 | # rewrite 'current timestamp' as 'current_timestamp' |
109 | 'timestamp default current timestamp' |
701cd3e3 |
110 | => { data_type => 'timestamp', default_value => \'current_timestamp', |
111 | original => { default_value => \'current timestamp' } }, |
9dc968df |
112 | 'time' => { data_type => 'time' }, |
113 | |
114 | # String Types |
115 | 'char' => { data_type => 'char', size => 1 }, |
116 | 'char(11)' => { data_type => 'char', size => 11 }, |
117 | 'nchar' => { data_type => 'nchar', size => 1 }, |
118 | 'nchar(11)' => { data_type => 'nchar', size => 11 }, |
119 | 'varchar' => { data_type => 'varchar', size => 1 }, |
120 | 'varchar(20)' => { data_type => 'varchar', size => 20 }, |
121 | 'char varying(20)' |
122 | => { data_type => 'varchar', size => 20 }, |
123 | 'character varying(20)' |
124 | => { data_type => 'varchar', size => 20 }, |
125 | 'nvarchar(20)' => { data_type => 'nvarchar', size => 20 }, |
126 | 'xml' => { data_type => 'xml' }, |
127 | 'uniqueidentifierstr' |
825c42a3 |
128 | => { data_type => 'uniqueidentifierstr' }, |
9dc968df |
129 | |
130 | # Binary types |
131 | 'binary' => { data_type => 'binary', size => 1 }, |
132 | 'binary(20)' => { data_type => 'binary', size => 20 }, |
133 | 'varbinary' => { data_type => 'varbinary', size => 1 }, |
134 | 'varbinary(20)'=> { data_type => 'varbinary', size => 20 }, |
135 | 'uniqueidentifier' |
825c42a3 |
136 | => { data_type => 'uniqueidentifier' }, |
9dc968df |
137 | |
138 | # Blob types |
139 | 'long binary' => { data_type => 'long binary' }, |
140 | 'image' => { data_type => 'image' }, |
141 | 'long varchar' => { data_type => 'long varchar' }, |
142 | 'text' => { data_type => 'text' }, |
143 | 'long nvarchar'=> { data_type => 'long nvarchar' }, |
144 | 'ntext' => { data_type => 'ntext' }, |
145 | }, |
c4a69b87 |
146 | extra => { |
147 | count => 28 * 2, |
148 | run => sub { |
149 | SKIP: { |
150 | $schema = $_[0]; |
151 | my $self = $_[3]; |
152 | |
153 | my $connect_info = [@$self{qw/dsn user password/}]; |
154 | |
155 | my $dbh = $schema->storage->dbh; |
156 | |
157 | try { |
158 | $dbh->do("CREATE USER dbicsl_test1 identified by 'dbicsl'"); |
159 | } |
160 | catch { |
161 | $schemas_created = 0; |
162 | skip "no CREATE USER privileges", 28 * 2; |
163 | }; |
164 | |
165 | $dbh->do(<<"EOF"); |
166 | CREATE TABLE dbicsl_test1.sqlanywhere_loader_test4 ( |
167 | id INT IDENTITY NOT NULL PRIMARY KEY, |
168 | value VARCHAR(100) |
169 | ) |
170 | EOF |
171 | $dbh->do(<<"EOF"); |
172 | CREATE TABLE dbicsl_test1.sqlanywhere_loader_test5 ( |
173 | id INT IDENTITY NOT NULL PRIMARY KEY, |
174 | value VARCHAR(100), |
175 | four_id INTEGER NOT NULL UNIQUE, |
176 | FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id) |
177 | ) |
178 | EOF |
179 | $dbh->do("CREATE USER dbicsl_test2 identified by 'dbicsl'"); |
180 | $dbh->do(<<"EOF"); |
181 | CREATE TABLE dbicsl_test2.sqlanywhere_loader_test6 ( |
182 | id INT IDENTITY NOT NULL PRIMARY KEY, |
183 | value VARCHAR(100), |
184 | sqlanywhere_loader_test4_id INTEGER, |
185 | FOREIGN KEY (sqlanywhere_loader_test4_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id) |
186 | ) |
187 | EOF |
188 | $dbh->do(<<"EOF"); |
189 | CREATE TABLE dbicsl_test2.sqlanywhere_loader_test7 ( |
190 | id INT IDENTITY NOT NULL PRIMARY KEY, |
191 | value VARCHAR(100), |
192 | six_id INTEGER NOT NULL UNIQUE, |
193 | FOREIGN KEY (six_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test6 (id) |
194 | ) |
195 | EOF |
196 | $dbh->do(<<"EOF"); |
197 | CREATE TABLE dbicsl_test1.sqlanywhere_loader_test8 ( |
198 | id INT IDENTITY NOT NULL PRIMARY KEY, |
199 | value VARCHAR(100), |
200 | sqlanywhere_loader_test7_id INTEGER, |
201 | FOREIGN KEY (sqlanywhere_loader_test7_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test7 (id) |
202 | ) |
203 | EOF |
204 | |
205 | $schemas_created = 1; |
206 | |
207 | my $guard = Scope::Guard->new(\&extra_cleanup); |
208 | |
209 | foreach my $db_schema (['dbicsl_test1', 'dbicsl_test2'], '%') { |
210 | lives_and { |
211 | rmtree EXTRA_DUMP_DIR; |
212 | |
213 | my @warns; |
214 | local $SIG{__WARN__} = sub { |
215 | push @warns, $_[0] unless $_[0] =~ /\bcollides\b/; |
216 | }; |
217 | |
218 | make_schema_at( |
219 | 'SQLAnywhereMultiSchema', |
220 | { |
221 | naming => 'current', |
222 | db_schema => $db_schema, |
223 | dump_directory => EXTRA_DUMP_DIR, |
224 | quiet => 1, |
225 | }, |
226 | $connect_info, |
227 | ); |
228 | |
229 | diag join "\n", @warns if @warns; |
230 | |
231 | is @warns, 0; |
232 | } 'dumped schema for dbicsl_test1 and dbicsl_test2 schemas with no warnings'; |
233 | |
234 | my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info); |
235 | |
236 | lives_and { |
237 | ok $test_schema = SQLAnywhereMultiSchema->connect(@$connect_info); |
238 | } 'connected test schema'; |
239 | |
240 | lives_and { |
241 | ok $rsrc = $test_schema->source('SqlanywhereLoaderTest4'); |
242 | } 'got source for table in schema one'; |
243 | |
244 | is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, |
245 | 'column in schema one'; |
246 | |
247 | is try { $rsrc->column_info('value')->{data_type} }, 'varchar', |
248 | 'column in schema one'; |
249 | |
250 | is try { $rsrc->column_info('value')->{size} }, 100, |
251 | 'column in schema one'; |
252 | |
253 | lives_and { |
254 | ok $rs = $test_schema->resultset('SqlanywhereLoaderTest4'); |
255 | } 'got resultset for table in schema one'; |
256 | |
257 | lives_and { |
258 | ok $row = $rs->create({ value => 'foo' }); |
259 | } 'executed SQL on table in schema one'; |
260 | |
261 | $rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test5') }; |
262 | |
263 | is_deeply $rel_info->{cond}, { |
264 | 'foreign.four_id' => 'self.id' |
265 | }, 'relationship in schema one'; |
9dc968df |
266 | |
c4a69b87 |
267 | is $rel_info->{attrs}{accessor}, 'single', |
268 | 'relationship in schema one'; |
269 | |
270 | is $rel_info->{attrs}{join_type}, 'LEFT', |
271 | 'relationship in schema one'; |
272 | |
273 | lives_and { |
274 | ok $rsrc = $test_schema->source('SqlanywhereLoaderTest5'); |
275 | } 'got source for table in schema one'; |
276 | |
277 | %uniqs = try { $rsrc->unique_constraints }; |
278 | |
279 | is keys %uniqs, 2, |
280 | 'got unique and primary constraint in schema one'; |
281 | |
282 | lives_and { |
283 | ok $rsrc = $test_schema->source('SqlanywhereLoaderTest6'); |
284 | } 'got source for table in schema two'; |
285 | |
286 | is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, |
287 | 'column in schema two introspected correctly'; |
288 | |
289 | is try { $rsrc->column_info('value')->{data_type} }, 'varchar', |
290 | 'column in schema two introspected correctly'; |
291 | |
292 | is try { $rsrc->column_info('value')->{size} }, 100, |
293 | 'column in schema two introspected correctly'; |
294 | |
295 | lives_and { |
296 | ok $rs = $test_schema->resultset('SqlanywhereLoaderTest6'); |
297 | } 'got resultset for table in schema two'; |
298 | |
299 | lives_and { |
300 | ok $row = $rs->create({ value => 'foo' }); |
301 | } 'executed SQL on table in schema two'; |
302 | |
303 | $rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test7') }; |
304 | |
305 | is_deeply $rel_info->{cond}, { |
306 | 'foreign.six_id' => 'self.id' |
307 | }, 'relationship in schema two'; |
308 | |
309 | is $rel_info->{attrs}{accessor}, 'single', |
310 | 'relationship in schema two'; |
311 | |
312 | is $rel_info->{attrs}{join_type}, 'LEFT', |
313 | 'relationship in schema two'; |
314 | |
315 | lives_and { |
316 | ok $rsrc = $test_schema->source('SqlanywhereLoaderTest7'); |
317 | } 'got source for table in schema two'; |
318 | |
319 | %uniqs = try { $rsrc->unique_constraints }; |
320 | |
321 | is keys %uniqs, 2, |
322 | 'got unique and primary constraint in schema two'; |
323 | |
324 | lives_and { |
325 | ok $test_schema->source('SqlanywhereLoaderTest6') |
326 | ->has_relationship('sqlanywhere_loader_test4'); |
327 | } 'cross-schema relationship in multi-db_schema'; |
328 | |
329 | lives_and { |
330 | ok $test_schema->source('SqlanywhereLoaderTest4') |
331 | ->has_relationship('sqlanywhere_loader_test6s'); |
332 | } 'cross-schema relationship in multi-db_schema'; |
333 | |
334 | lives_and { |
335 | ok $test_schema->source('SqlanywhereLoaderTest8') |
336 | ->has_relationship('sqlanywhere_loader_test7'); |
337 | } 'cross-schema relationship in multi-db_schema'; |
338 | |
339 | lives_and { |
340 | ok $test_schema->source('SqlanywhereLoaderTest7') |
341 | ->has_relationship('sqlanywhere_loader_test8s'); |
342 | } 'cross-schema relationship in multi-db_schema'; |
343 | } |
344 | } |
345 | }, |
346 | }, |
8793567f |
347 | ); |
348 | |
5347f501 |
349 | if (not ($dbd_sqlanywhere_dsn || $odbc_dsn)) { |
637a5785 |
350 | $tester->skip_tests('You need to set the DBICTEST_SQLANYWHERE_DSN, _USER and _PASS and/or the DBICTEST_SQLANYWHERE_ODBC_DSN, _USER and _PASS environment variables'); |
8793567f |
351 | } |
352 | else { |
353 | $tester->run_tests(); |
354 | } |
c4a69b87 |
355 | |
356 | sub extra_cleanup { |
357 | if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) { |
358 | if ($schemas_created && (my $dbh = try { $schema->storage->dbh })) { |
359 | foreach my $table ('dbicsl_test1.sqlanywhere_loader_test8', |
360 | 'dbicsl_test2.sqlanywhere_loader_test7', |
361 | 'dbicsl_test2.sqlanywhere_loader_test6', |
362 | 'dbicsl_test1.sqlanywhere_loader_test5', |
363 | 'dbicsl_test1.sqlanywhere_loader_test4') { |
364 | try { |
365 | $dbh->do("DROP TABLE $table"); |
366 | } |
367 | catch { |
368 | diag "Error dropping table: $_"; |
369 | }; |
370 | } |
371 | |
372 | foreach my $db_schema (qw/dbicsl_test1 dbicsl_test2/) { |
373 | try { |
374 | $dbh->do("DROP USER $db_schema"); |
375 | } |
376 | catch { |
377 | diag "Error dropping test user $db_schema: $_"; |
378 | }; |
379 | } |
380 | } |
381 | rmtree EXTRA_DUMP_DIR; |
382 | } |
383 | } |
701cd3e3 |
384 | # vim:et sts=4 sw=4 tw=0: |