Commit | Line | Data |
046e344c |
1 | use strict; |
05d322c8 |
2 | use warnings; |
3a89a69f |
3 | use Test::More; |
4 | use Test::Exception; |
1ad8e8c3 |
5 | use DBIx::Class::Schema::Loader::Utils 'warnings_exist_silent'; |
c4a69b87 |
6 | use Try::Tiny; |
7 | use File::Path 'rmtree'; |
8 | use DBIx::Class::Schema::Loader 'make_schema_at'; |
1ad8e8c3 |
9 | use namespace::clean; |
05d322c8 |
10 | |
11 | # use this if you keep a copy of DBD::Sybase linked to FreeTDS somewhere else |
12 | BEGIN { |
13 | if (my $lib_dirs = $ENV{DBICTEST_MSSQL_PERL5LIB}) { |
14 | unshift @INC, $_ for split /:/, $lib_dirs; |
15 | } |
16 | } |
17 | |
c4a69b87 |
18 | use lib qw(t/lib); |
19 | |
20 | use dbixcsl_common_tests (); |
21 | use dbixcsl_test_dir '$tdir'; |
22 | |
23 | use constant EXTRA_DUMP_DIR => "$tdir/mssql_extra_dump"; |
24 | |
25 | # for cleanup in END for extra tests |
26 | my ($schema, $schemas_created, $databases_created); |
27 | |
8dcf4292 |
28 | my ($dsns, $common_version); |
c4a69b87 |
29 | |
afcd3c32 |
30 | for (qw/MSSQL MSSQL_ODBC MSSQL_ADO/) { |
8dcf4292 |
31 | next unless $ENV{"DBICTEST_${_}_DSN"}; |
32 | |
33 | $dsns->{$_}{dsn} = $ENV{"DBICTEST_${_}_DSN"}; |
34 | $dsns->{$_}{user} = $ENV{"DBICTEST_${_}_USER"}; |
35 | $dsns->{$_}{password} = $ENV{"DBICTEST_${_}_PASS"}; |
36 | |
37 | require DBI; |
38 | my $dbh = DBI->connect (@{$dsns->{$_}}{qw/dsn user password/}, { RaiseError => 1, PrintError => 0} ); |
39 | my $srv_ver = eval { |
40 | $dbh->get_info(18) |
41 | || |
42 | $dbh->selectrow_hashref('master.dbo.xp_msver ProductVersion')->{Character_Value} |
43 | } || 0; |
44 | |
45 | my ($maj_srv_ver) = $srv_ver =~ /^(\d+)/; |
46 | |
47 | if (! defined $common_version or $common_version > $maj_srv_ver ) { |
48 | $common_version = $maj_srv_ver; |
49 | } |
50 | } |
51 | |
52 | plan skip_all => 'You need to set the DBICTEST_MSSQL_DSN, _USER and _PASS and/or the DBICTEST_MSSQL_ODBC_DSN, _USER and _PASS environment variables' |
53 | unless $dsns; |
54 | |
8dcf4292 |
55 | my $mssql_2008_new_data_types = { |
56 | date => { data_type => 'date' }, |
57 | time => { data_type => 'time' }, |
58 | 'time(0)'=> { data_type => 'time', size => 0 }, |
59 | 'time(1)'=> { data_type => 'time', size => 1 }, |
60 | 'time(2)'=> { data_type => 'time', size => 2 }, |
61 | 'time(3)'=> { data_type => 'time', size => 3 }, |
62 | 'time(4)'=> { data_type => 'time', size => 4 }, |
63 | 'time(5)'=> { data_type => 'time', size => 5 }, |
64 | 'time(6)'=> { data_type => 'time', size => 6 }, |
65 | 'time(7)'=> { data_type => 'time' }, |
66 | datetimeoffset => { data_type => 'datetimeoffset' }, |
67 | 'datetimeoffset(0)' => { data_type => 'datetimeoffset', size => 0 }, |
68 | 'datetimeoffset(1)' => { data_type => 'datetimeoffset', size => 1 }, |
69 | 'datetimeoffset(2)' => { data_type => 'datetimeoffset', size => 2 }, |
70 | 'datetimeoffset(3)' => { data_type => 'datetimeoffset', size => 3 }, |
71 | 'datetimeoffset(4)' => { data_type => 'datetimeoffset', size => 4 }, |
72 | 'datetimeoffset(5)' => { data_type => 'datetimeoffset', size => 5 }, |
73 | 'datetimeoffset(6)' => { data_type => 'datetimeoffset', size => 6 }, |
74 | 'datetimeoffset(7)' => { data_type => 'datetimeoffset' }, |
75 | datetime2 => { data_type => 'datetime2' }, |
76 | 'datetime2(0)' => { data_type => 'datetime2', size => 0 }, |
77 | 'datetime2(1)' => { data_type => 'datetime2', size => 1 }, |
78 | 'datetime2(2)' => { data_type => 'datetime2', size => 2 }, |
79 | 'datetime2(3)' => { data_type => 'datetime2', size => 3 }, |
80 | 'datetime2(4)' => { data_type => 'datetime2', size => 4 }, |
81 | 'datetime2(5)' => { data_type => 'datetime2', size => 5 }, |
82 | 'datetime2(6)' => { data_type => 'datetime2', size => 6 }, |
83 | 'datetime2(7)' => { data_type => 'datetime2' }, |
3a89a69f |
84 | |
8dcf4292 |
85 | hierarchyid => { data_type => 'hierarchyid' }, |
86 | }; |
046e344c |
87 | |
88 | my $tester = dbixcsl_common_tests->new( |
b1e43108 |
89 | vendor => 'mssql', |
046e344c |
90 | auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY', |
41968729 |
91 | default_function_def => 'DATETIME DEFAULT getdate()', |
8dcf4292 |
92 | connect_info => [values %$dsns], |
b1d11550 |
93 | preserve_case_mode_is_exclusive => 1, |
94 | quote_char => [ qw/[ ]/ ], |
8dcf4292 |
95 | basic_date_datatype => ($common_version >= 10) ? 'DATE' : 'SMALLDATETIME', |
deedd576 |
96 | data_types => { |
81ade4d9 |
97 | # http://msdn.microsoft.com/en-us/library/ms187752.aspx |
98 | |
99 | # numeric types |
100 | 'int identity' => { data_type => 'integer', is_auto_increment => 1 }, |
101 | bigint => { data_type => 'bigint' }, |
102 | int => { data_type => 'integer' }, |
103 | integer => { data_type => 'integer' }, |
104 | smallint => { data_type => 'smallint' }, |
105 | tinyint => { data_type => 'tinyint' }, |
106 | money => { data_type => 'money' }, |
107 | smallmoney => { data_type => 'smallmoney' }, |
108 | bit => { data_type => 'bit' }, |
8c41c3ce |
109 | real => { data_type => 'real' }, |
110 | 'float(14)' => { data_type => 'real' }, |
111 | 'float(24)' => { data_type => 'real' }, |
81ade4d9 |
112 | 'float(25)' => { data_type => 'double precision' }, |
113 | 'float(53)' => { data_type => 'double precision' }, |
114 | float => { data_type => 'double precision' }, |
115 | 'double precision' |
116 | => { data_type => 'double precision' }, |
afcd3c32 |
117 | 'numeric(6)' => { data_type => 'numeric', size => [6,0] }, |
81ade4d9 |
118 | 'numeric(6,3)' => { data_type => 'numeric', size => [6,3] }, |
afcd3c32 |
119 | 'decimal(6)' => { data_type => 'decimal', size => [6,0] }, |
81ade4d9 |
120 | 'decimal(6,3)' => { data_type => 'decimal', size => [6,3] }, |
121 | 'dec(6,3)' => { data_type => 'decimal', size => [6,3] }, |
122 | numeric => { data_type => 'numeric' }, |
123 | decimal => { data_type => 'decimal' }, |
124 | dec => { data_type => 'decimal' }, |
125 | |
126 | # datetime types |
81ade4d9 |
127 | datetime => { data_type => 'datetime' }, |
6e566cc4 |
128 | # test rewriting getdate() to current_timestamp |
129 | 'datetime default getdate()' |
701cd3e3 |
130 | => { data_type => 'datetime', default_value => \'current_timestamp', |
131 | original => { default_value => \'getdate()' } }, |
81ade4d9 |
132 | smalldatetime => { data_type => 'smalldatetime' }, |
8dcf4292 |
133 | |
134 | ($common_version >= 10) ? %$mssql_2008_new_data_types : (), |
81ade4d9 |
135 | |
136 | # string types |
137 | char => { data_type => 'char', size => 1 }, |
138 | 'char(2)' => { data_type => 'char', size => 2 }, |
c7e6dc1f |
139 | character => { data_type => 'char', size => 1 }, |
140 | 'character(2)' => { data_type => 'char', size => 2 }, |
81ade4d9 |
141 | 'varchar(2)' => { data_type => 'varchar', size => 2 }, |
b48f3f80 |
142 | |
81ade4d9 |
143 | nchar => { data_type => 'nchar', size => 1 }, |
144 | 'nchar(2)' => { data_type => 'nchar', size => 2 }, |
145 | 'nvarchar(2)' => { data_type => 'nvarchar', size => 2 }, |
146 | |
147 | # binary types |
8c41c3ce |
148 | 'binary' => { data_type => 'binary', size => 1 }, |
149 | 'binary(2)' => { data_type => 'binary', size => 2 }, |
150 | 'varbinary(2)' => { data_type => 'varbinary', size => 2 }, |
81ade4d9 |
151 | |
152 | # blob types |
153 | 'varchar(max)' => { data_type => 'text' }, |
154 | text => { data_type => 'text' }, |
b48f3f80 |
155 | |
81ade4d9 |
156 | 'nvarchar(max)' => { data_type => 'ntext' }, |
157 | ntext => { data_type => 'ntext' }, |
b48f3f80 |
158 | |
81ade4d9 |
159 | 'varbinary(max)' => { data_type => 'image' }, |
160 | image => { data_type => 'image' }, |
161 | |
162 | # other types |
163 | timestamp => { data_type => 'timestamp', inflate_datetime => 0 }, |
16773d6d |
164 | rowversion => { data_type => 'rowversion' }, |
825c42a3 |
165 | uniqueidentifier => { data_type => 'uniqueidentifier' }, |
81ade4d9 |
166 | sql_variant => { data_type => 'sql_variant' }, |
167 | xml => { data_type => 'xml' }, |
deedd576 |
168 | }, |
3a89a69f |
169 | extra => { |
170 | create => [ |
171 | q{ |
172 | CREATE TABLE [mssql_loader_test1.dot] ( |
173 | id INT IDENTITY NOT NULL PRIMARY KEY, |
174 | dat VARCHAR(8) |
175 | ) |
176 | }, |
177 | q{ |
178 | CREATE TABLE mssql_loader_test3 ( |
179 | id INT IDENTITY NOT NULL PRIMARY KEY |
180 | ) |
181 | }, |
182 | q{ |
183 | CREATE VIEW mssql_loader_test4 AS |
184 | SELECT * FROM mssql_loader_test3 |
185 | }, |
020f3c3a |
186 | # test capitalization of cols in unique constraints and rels |
ccce0e82 |
187 | q{ SET QUOTED_IDENTIFIER ON }, |
188 | q{ SET ANSI_NULLS ON }, |
deedd576 |
189 | q{ |
020f3c3a |
190 | CREATE TABLE [MSSQL_Loader_Test5] ( |
191 | [Id] INT IDENTITY NOT NULL PRIMARY KEY, |
ccce0e82 |
192 | [FooCol] INT NOT NULL, |
193 | [BarCol] INT NOT NULL, |
194 | UNIQUE ([FooCol], [BarCol]) |
deedd576 |
195 | ) |
196 | }, |
020f3c3a |
197 | q{ |
198 | CREATE TABLE [MSSQL_Loader_Test6] ( |
199 | [Five_Id] INT REFERENCES [MSSQL_Loader_Test5] ([Id]) |
200 | ) |
201 | }, |
3a89a69f |
202 | ], |
203 | pre_drop_ddl => [ |
204 | 'CREATE TABLE mssql_loader_test3 (id INT IDENTITY NOT NULL PRIMARY KEY)', |
205 | 'DROP VIEW mssql_loader_test4', |
206 | ], |
207 | drop => [ |
208 | '[mssql_loader_test1.dot]', |
deedd576 |
209 | 'mssql_loader_test3', |
060f5ecd |
210 | 'MSSQL_Loader_Test6', |
211 | 'MSSQL_Loader_Test5', |
3a89a69f |
212 | ], |
c4a69b87 |
213 | count => 10 + 28 * 2 + 24, |
3a89a69f |
214 | run => sub { |
c4a69b87 |
215 | my ($monikers, $classes, $self); |
216 | ($schema, $monikers, $classes, $self) = @_; |
217 | |
218 | my $connect_info = [@$self{qw/dsn user password/}]; |
3a89a69f |
219 | |
220 | # Test that the table above (with '.' in name) gets loaded correctly. |
221 | ok((my $rs = eval { |
c4a69b87 |
222 | $schema->resultset('MssqlLoaderTest1Dot') }), |
3a89a69f |
223 | 'got a resultset for table with dot in name'); |
224 | |
225 | ok((my $from = eval { $rs->result_source->from }), |
226 | 'got an $rsrc->from for table with dot in name'); |
227 | |
228 | is ref($from), 'SCALAR', '->table with dot in name is a scalar ref'; |
229 | |
230 | is eval { $$from }, "[mssql_loader_test1.dot]", |
231 | '->table with dot in name has correct name'; |
232 | |
deedd576 |
233 | # Test capitalization of columns and unique constraints |
234 | ok ((my $rsrc = $schema->resultset($monikers->{mssql_loader_test5})->result_source), |
235 | 'got result_source'); |
236 | |
c4a69b87 |
237 | if ($schema->loader->preserve_case) { |
060f5ecd |
238 | is_deeply [ $rsrc->columns ], [qw/Id FooCol BarCol/], |
239 | 'column name case is preserved with case-sensitive collation'; |
020f3c3a |
240 | |
060f5ecd |
241 | my %uniqs = $rsrc->unique_constraints; |
242 | delete $uniqs{primary}; |
3a89a69f |
243 | |
060f5ecd |
244 | is_deeply ((values %uniqs)[0], [qw/FooCol BarCol/], |
245 | 'column name case is preserved in unique constraint with case-sensitive collation'); |
246 | } |
247 | else { |
248 | is_deeply [ $rsrc->columns ], [qw/id foocol barcol/], |
249 | 'column names are lowercased for case-insensitive collation'; |
250 | |
251 | my %uniqs = $rsrc->unique_constraints; |
252 | delete $uniqs{primary}; |
3a89a69f |
253 | |
060f5ecd |
254 | is_deeply ((values %uniqs)[0], [qw/foocol barcol/], |
255 | 'columns in unique constraint lowercased for case-insensitive collation'); |
256 | } |
3a89a69f |
257 | |
020f3c3a |
258 | lives_and { |
060f5ecd |
259 | my $five_row = $schema->resultset($monikers->{mssql_loader_test5})->new_result({}); |
16773d6d |
260 | |
c4a69b87 |
261 | if ($schema->loader->preserve_case) { |
16773d6d |
262 | $five_row->foo_col(1); |
263 | $five_row->bar_col(2); |
264 | } |
265 | else { |
266 | $five_row->foocol(1); |
267 | $five_row->barcol(2); |
268 | } |
060f5ecd |
269 | $five_row->insert; |
270 | |
16773d6d |
271 | my $six_row = $five_row->create_related('mssql_loader_test6s', {}); |
020f3c3a |
272 | |
273 | is $six_row->five->id, 1; |
274 | } 'relationships for mixed-case tables/columns detected'; |
275 | |
3a89a69f |
276 | # Test that a bad view (where underlying table is gone) is ignored. |
277 | my $dbh = $schema->storage->dbh; |
278 | $dbh->do("DROP TABLE mssql_loader_test3"); |
279 | |
1ad8e8c3 |
280 | warnings_exist_silent { $schema->rescan } |
c38ec663 |
281 | qr/^Bad table or view 'mssql_loader_test4'/, 'bad view ignored'; |
3a89a69f |
282 | |
283 | throws_ok { |
284 | $schema->resultset($monikers->{mssql_loader_test4}) |
285 | } qr/Can't find source/, |
286 | 'no source registered for bad view'; |
c4a69b87 |
287 | |
288 | SKIP: { |
289 | my $dbh = $schema->storage->dbh; |
290 | |
291 | try { |
292 | $dbh->do('CREATE SCHEMA "dbicsl-test"'); |
293 | } |
294 | catch { |
295 | $schemas_created = 0; |
296 | skip "no CREATE SCHEMA privileges", 28 * 2; |
297 | }; |
298 | |
299 | $dbh->do(<<"EOF"); |
300 | CREATE TABLE [dbicsl-test].mssql_loader_test8 ( |
301 | id INT IDENTITY PRIMARY KEY, |
302 | value VARCHAR(100) |
303 | ) |
304 | EOF |
305 | $dbh->do(<<"EOF"); |
306 | CREATE TABLE [dbicsl-test].mssql_loader_test9 ( |
307 | id INT IDENTITY PRIMARY KEY, |
308 | value VARCHAR(100), |
309 | eight_id INTEGER NOT NULL UNIQUE, |
310 | FOREIGN KEY (eight_id) REFERENCES [dbicsl-test].mssql_loader_test8 (id) |
311 | ) |
312 | EOF |
313 | $dbh->do('CREATE SCHEMA [dbicsl.test]'); |
314 | $dbh->do(<<"EOF"); |
315 | CREATE TABLE [dbicsl.test].mssql_loader_test10 ( |
316 | id INT IDENTITY PRIMARY KEY, |
317 | value VARCHAR(100), |
318 | mssql_loader_test8_id INTEGER, |
319 | FOREIGN KEY (mssql_loader_test8_id) REFERENCES [dbicsl-test].mssql_loader_test8 (id) |
320 | ) |
321 | EOF |
322 | $dbh->do(<<"EOF"); |
323 | CREATE TABLE [dbicsl.test].mssql_loader_test11 ( |
324 | id INT IDENTITY PRIMARY KEY, |
325 | value VARCHAR(100), |
326 | ten_id INTEGER NOT NULL UNIQUE, |
327 | FOREIGN KEY (ten_id) REFERENCES [dbicsl.test].mssql_loader_test10 (id) |
328 | ) |
329 | EOF |
330 | $dbh->do(<<"EOF"); |
331 | CREATE TABLE [dbicsl-test].mssql_loader_test12 ( |
332 | id INT IDENTITY PRIMARY KEY, |
333 | value VARCHAR(100), |
334 | mssql_loader_test11_id INTEGER, |
335 | FOREIGN KEY (mssql_loader_test11_id) REFERENCES [dbicsl.test].mssql_loader_test11 (id) |
336 | ) |
337 | EOF |
338 | |
339 | $schemas_created = 1; |
340 | |
341 | foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') { |
342 | lives_and { |
343 | rmtree EXTRA_DUMP_DIR; |
344 | |
345 | my @warns; |
346 | local $SIG{__WARN__} = sub { |
347 | push @warns, $_[0] unless $_[0] =~ /\bcollides\b/; |
348 | }; |
349 | |
350 | make_schema_at( |
351 | 'MSSQLMultiSchema', |
352 | { |
353 | naming => 'current', |
354 | db_schema => $db_schema, |
355 | dump_directory => EXTRA_DUMP_DIR, |
356 | quiet => 1, |
357 | }, |
358 | $connect_info, |
359 | ); |
360 | |
361 | diag join "\n", @warns if @warns; |
362 | |
363 | is @warns, 0; |
364 | } 'dumped schema for "dbicsl-test" and "dbicsl.test" schemas with no warnings'; |
365 | |
366 | my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info); |
367 | |
368 | lives_and { |
369 | ok $test_schema = MSSQLMultiSchema->connect(@$connect_info); |
370 | } 'connected test schema'; |
371 | |
372 | lives_and { |
373 | ok $rsrc = $test_schema->source('MssqlLoaderTest8'); |
374 | } 'got source for table in schema name with dash'; |
375 | |
376 | is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, |
377 | 'column in schema name with dash'; |
378 | |
379 | is try { $rsrc->column_info('value')->{data_type} }, 'varchar', |
380 | 'column in schema name with dash'; |
381 | |
382 | is try { $rsrc->column_info('value')->{size} }, 100, |
383 | 'column in schema name with dash'; |
384 | |
385 | lives_and { |
386 | ok $rs = $test_schema->resultset('MssqlLoaderTest8'); |
387 | } 'got resultset for table in schema name with dash'; |
388 | |
389 | lives_and { |
390 | ok $row = $rs->create({ value => 'foo' }); |
391 | } 'executed SQL on table in schema name with dash'; |
392 | |
393 | $rel_info = try { $rsrc->relationship_info('mssql_loader_test9') }; |
394 | |
395 | is_deeply $rel_info->{cond}, { |
396 | 'foreign.eight_id' => 'self.id' |
397 | }, 'relationship in schema name with dash'; |
398 | |
399 | is $rel_info->{attrs}{accessor}, 'single', |
400 | 'relationship in schema name with dash'; |
401 | |
402 | is $rel_info->{attrs}{join_type}, 'LEFT', |
403 | 'relationship in schema name with dash'; |
404 | |
405 | lives_and { |
406 | ok $rsrc = $test_schema->source('MssqlLoaderTest9'); |
407 | } 'got source for table in schema name with dash'; |
408 | |
409 | %uniqs = try { $rsrc->unique_constraints }; |
410 | |
411 | is keys %uniqs, 2, |
412 | 'got unique and primary constraint in schema name with dash'; |
413 | |
414 | lives_and { |
415 | ok $rsrc = $test_schema->source('MssqlLoaderTest10'); |
416 | } 'got source for table in schema name with dot'; |
417 | |
418 | is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, |
419 | 'column in schema name with dot introspected correctly'; |
420 | |
421 | is try { $rsrc->column_info('value')->{data_type} }, 'varchar', |
422 | 'column in schema name with dot introspected correctly'; |
423 | |
424 | is try { $rsrc->column_info('value')->{size} }, 100, |
425 | 'column in schema name with dot introspected correctly'; |
426 | |
427 | lives_and { |
428 | ok $rs = $test_schema->resultset('MssqlLoaderTest10'); |
429 | } 'got resultset for table in schema name with dot'; |
430 | |
431 | lives_and { |
432 | ok $row = $rs->create({ value => 'foo' }); |
433 | } 'executed SQL on table in schema name with dot'; |
434 | |
435 | $rel_info = try { $rsrc->relationship_info('mssql_loader_test11') }; |
436 | |
437 | is_deeply $rel_info->{cond}, { |
438 | 'foreign.ten_id' => 'self.id' |
439 | }, 'relationship in schema name with dot'; |
440 | |
441 | is $rel_info->{attrs}{accessor}, 'single', |
442 | 'relationship in schema name with dot'; |
443 | |
444 | is $rel_info->{attrs}{join_type}, 'LEFT', |
445 | 'relationship in schema name with dot'; |
446 | |
447 | lives_and { |
448 | ok $rsrc = $test_schema->source('MssqlLoaderTest11'); |
449 | } 'got source for table in schema name with dot'; |
450 | |
451 | %uniqs = try { $rsrc->unique_constraints }; |
452 | |
453 | is keys %uniqs, 2, |
454 | 'got unique and primary constraint in schema name with dot'; |
455 | |
456 | lives_and { |
457 | ok $test_schema->source('MssqlLoaderTest10') |
458 | ->has_relationship('mssql_loader_test8'); |
459 | } 'cross-schema relationship in multi-db_schema'; |
460 | |
461 | lives_and { |
462 | ok $test_schema->source('MssqlLoaderTest8') |
463 | ->has_relationship('mssql_loader_test10s'); |
464 | } 'cross-schema relationship in multi-db_schema'; |
465 | |
466 | lives_and { |
467 | ok $test_schema->source('MssqlLoaderTest12') |
468 | ->has_relationship('mssql_loader_test11'); |
469 | } 'cross-schema relationship in multi-db_schema'; |
470 | |
471 | lives_and { |
472 | ok $test_schema->source('MssqlLoaderTest11') |
473 | ->has_relationship('mssql_loader_test12s'); |
474 | } 'cross-schema relationship in multi-db_schema'; |
475 | } |
476 | } |
477 | |
478 | SKIP: { |
479 | my $dbh = $schema->storage->dbh; |
480 | |
481 | try { |
482 | $dbh->do('USE master'); |
483 | $dbh->do('CREATE DATABASE dbicsl_test1'); |
484 | } |
485 | catch { |
486 | skip "no CREATE DATABASE privileges", 24; |
487 | }; |
488 | |
489 | $dbh->do('CREATE DATABASE dbicsl_test2'); |
490 | |
491 | $dbh->do('USE dbicsl_test1'); |
492 | |
493 | $dbh->do(<<'EOF'); |
494 | CREATE TABLE mssql_loader_test13 ( |
495 | id INT IDENTITY PRIMARY KEY, |
496 | value VARCHAR(100) |
497 | ) |
498 | EOF |
499 | $dbh->do(<<'EOF'); |
500 | CREATE TABLE mssql_loader_test14 ( |
501 | id INT IDENTITY PRIMARY KEY, |
502 | value VARCHAR(100), |
503 | thirteen_id INTEGER UNIQUE REFERENCES mssql_loader_test13 (id) |
504 | ) |
505 | EOF |
506 | |
507 | $dbh->do('USE master'); |
508 | $dbh->do('USE dbicsl_test2'); |
509 | |
510 | $dbh->do(<<"EOF"); |
511 | CREATE TABLE mssql_loader_test15 ( |
512 | id INT IDENTITY PRIMARY KEY, |
513 | value VARCHAR(100) |
514 | ) |
515 | EOF |
516 | $dbh->do(<<"EOF"); |
517 | CREATE TABLE mssql_loader_test16 ( |
518 | id INT IDENTITY PRIMARY KEY, |
519 | value VARCHAR(100), |
520 | fifteen_id INTEGER UNIQUE REFERENCES mssql_loader_test15 (id) |
521 | ) |
522 | EOF |
523 | |
524 | $databases_created = 1; |
525 | |
526 | lives_and { |
527 | my @warns; |
528 | local $SIG{__WARN__} = sub { |
529 | push @warns, $_[0] unless $_[0] =~ /\bcollides\b/; |
530 | }; |
531 | |
532 | make_schema_at( |
533 | 'MSSQLMultiDatabase', |
534 | { |
535 | naming => 'current', |
536 | db_schema => { '%' => '%' }, |
537 | dump_directory => EXTRA_DUMP_DIR, |
538 | quiet => 1, |
539 | }, |
540 | $connect_info, |
541 | ); |
542 | |
543 | diag join "\n", @warns if @warns; |
544 | |
545 | is @warns, 0; |
546 | } 'dumped schema for all databases with no warnings'; |
547 | |
548 | my $test_schema; |
549 | |
550 | lives_and { |
551 | ok $test_schema = MSSQLMultiDatabase->connect(@$connect_info); |
552 | } 'connected test schema'; |
553 | |
554 | my ($rsrc, $rs, $row, $rel_info, %uniqs); |
555 | |
556 | lives_and { |
557 | ok $rsrc = $test_schema->source('MssqlLoaderTest13'); |
558 | } 'got source for table in database one'; |
559 | |
560 | is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, |
561 | 'column in database one'; |
562 | |
563 | is try { $rsrc->column_info('value')->{data_type} }, 'varchar', |
564 | 'column in database one'; |
565 | |
566 | is try { $rsrc->column_info('value')->{size} }, 100, |
567 | 'column in database one'; |
568 | |
569 | lives_and { |
570 | ok $rs = $test_schema->resultset('MssqlLoaderTest13'); |
571 | } 'got resultset for table in database one'; |
572 | |
573 | lives_and { |
574 | ok $row = $rs->create({ value => 'foo' }); |
575 | } 'executed SQL on table in database one'; |
576 | |
577 | $rel_info = try { $rsrc->relationship_info('mssql_loader_test14') }; |
578 | |
579 | is_deeply $rel_info->{cond}, { |
580 | 'foreign.thirteen_id' => 'self.id' |
581 | }, 'relationship in database one'; |
582 | |
583 | is $rel_info->{attrs}{accessor}, 'single', |
584 | 'relationship in database one'; |
585 | |
586 | is $rel_info->{attrs}{join_type}, 'LEFT', |
587 | 'relationship in database one'; |
588 | |
589 | lives_and { |
590 | ok $rsrc = $test_schema->source('MssqlLoaderTest14'); |
591 | } 'got source for table in database one'; |
592 | |
593 | %uniqs = try { $rsrc->unique_constraints }; |
594 | |
595 | is keys %uniqs, 2, |
596 | 'got unique and primary constraint in database one'; |
597 | |
598 | lives_and { |
599 | ok $rsrc = $test_schema->source('MssqlLoaderTest15'); |
600 | } 'got source for table in database two'; |
601 | |
602 | is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, |
603 | 'column in database two introspected correctly'; |
604 | |
605 | is try { $rsrc->column_info('value')->{data_type} }, 'varchar', |
606 | 'column in database two introspected correctly'; |
607 | |
608 | is try { $rsrc->column_info('value')->{size} }, 100, |
609 | 'column in database two introspected correctly'; |
610 | |
611 | lives_and { |
612 | ok $rs = $test_schema->resultset('MssqlLoaderTest15'); |
613 | } 'got resultset for table in database two'; |
614 | |
615 | lives_and { |
616 | ok $row = $rs->create({ value => 'foo' }); |
617 | } 'executed SQL on table in database two'; |
618 | |
619 | $rel_info = try { $rsrc->relationship_info('mssql_loader_test16') }; |
620 | |
621 | is_deeply $rel_info->{cond}, { |
622 | 'foreign.fifteen_id' => 'self.id' |
623 | }, 'relationship in database two'; |
624 | |
625 | is $rel_info->{attrs}{accessor}, 'single', |
626 | 'relationship in database two'; |
627 | |
628 | is $rel_info->{attrs}{join_type}, 'LEFT', |
629 | 'relationship in database two'; |
630 | |
631 | lives_and { |
632 | ok $rsrc = $test_schema->source('MssqlLoaderTest16'); |
633 | } 'got source for table in database two'; |
634 | |
635 | %uniqs = try { $rsrc->unique_constraints }; |
636 | |
637 | is keys %uniqs, 2, |
638 | 'got unique and primary constraint in database two'; |
639 | } |
3a89a69f |
640 | }, |
641 | }, |
046e344c |
642 | ); |
643 | |
8dcf4292 |
644 | $tester->run_tests(); |
645 | |
c4a69b87 |
646 | END { |
647 | if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) { |
648 | if ($schema) { |
649 | # switch back to default database |
650 | $schema->storage->disconnect; |
651 | my $dbh = $schema->storage->dbh; |
652 | |
653 | if ($schemas_created) { |
654 | foreach my $table ('[dbicsl-test].mssql_loader_test12', |
655 | '[dbicsl.test].mssql_loader_test11', |
656 | '[dbicsl.test].mssql_loader_test10', |
657 | '[dbicsl-test].mssql_loader_test9', |
658 | '[dbicsl-test].mssql_loader_test8') { |
659 | try { |
660 | $dbh->do("DROP TABLE $table"); |
661 | } |
662 | catch { |
663 | diag "Error dropping table: $_"; |
664 | }; |
665 | } |
666 | |
667 | foreach my $db_schema (qw/dbicsl-test dbicsl.test/) { |
668 | try { |
669 | $dbh->do(qq{DROP SCHEMA [$db_schema]}); |
670 | } |
671 | catch { |
672 | diag "Error dropping test schema $db_schema: $_"; |
673 | }; |
674 | } |
675 | } |
676 | |
677 | if ($databases_created) { |
678 | $dbh->do('USE dbicsl_test1'); |
679 | |
680 | foreach my $table ('mssql_loader_test14', |
681 | 'mssql_loader_test13') { |
682 | try { |
683 | $dbh->do("DROP TABLE $table"); |
684 | } |
685 | catch { |
686 | diag "Error dropping table: $_"; |
687 | }; |
688 | } |
689 | |
690 | $dbh->do('USE dbicsl_test2'); |
691 | |
692 | foreach my $table ('mssql_loader_test16', |
693 | 'mssql_loader_test15') { |
694 | try { |
695 | $dbh->do("DROP TABLE $table"); |
696 | } |
697 | catch { |
698 | diag "Error dropping table: $_"; |
699 | }; |
700 | } |
701 | |
702 | $dbh->do('USE master'); |
703 | |
704 | foreach my $database (qw/dbicsl_test1 dbicsl_test2/) { |
705 | try { |
706 | $dbh->do(qq{DROP DATABASE $database}); |
707 | } |
708 | catch { |
709 | diag "Error dropping test database '$database': $_"; |
710 | }; |
711 | } |
712 | } |
713 | |
714 | rmtree EXTRA_DUMP_DIR; |
715 | } |
716 | } |
717 | } |
060f5ecd |
718 | # vim:et sts=4 sw=4 tw=0: |