6 use File::Path 'rmtree';
7 use DBIx::Class::Schema::Loader::Utils 'slurp_file';
8 use DBIx::Class::Schema::Loader 'make_schema_at';
12 use dbixcsl_common_tests;
13 use dbixcsl_test_dir '$tdir';
15 use constant EXTRA_DUMP_DIR => "$tdir/mysql_extra_dump";
17 my $dsn = $ENV{DBICTEST_MYSQL_DSN} || '';
18 my $user = $ENV{DBICTEST_MYSQL_USER} || '';
19 my $password = $ENV{DBICTEST_MYSQL_PASS} || '';
20 my $test_innodb = $ENV{DBICTEST_MYSQL_INNODB} || 0;
22 my $skip_rels_msg = 'You need to set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships.';
24 my $innodb = $test_innodb ? q{Engine=InnoDB} : '';
26 my ($schema, $databases_created); # for cleanup in END for extra tests
28 my $tester = dbixcsl_common_tests->new(
30 auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT',
34 password => $password,
35 connect_info_opts=> { on_connect_call => 'set_strict_mode' },
36 loader_options => { preserve_case => 1 },
37 skip_rels => $test_innodb ? 0 : $skip_rels_msg,
40 no_implicit_rels => 1,
42 # http://dev.mysql.com/doc/refman/5.5/en/data-type-overview.html
44 'bit' => { data_type => 'bit', size => 1 },
45 'bit(11)' => { data_type => 'bit', size => 11 },
47 'bool' => { data_type => 'tinyint' },
48 'boolean' => { data_type => 'tinyint' },
49 'tinyint' => { data_type => 'tinyint' },
51 => { data_type => 'tinyint', extra => { unsigned => 1 } },
52 'smallint' => { data_type => 'smallint' },
54 => { data_type => 'smallint', extra => { unsigned => 1 } },
55 'mediumint' => { data_type => 'mediumint' },
57 => { data_type => 'mediumint', extra => { unsigned => 1 } },
58 'int' => { data_type => 'integer' },
60 => { data_type => 'integer', extra => { unsigned => 1 } },
61 'integer' => { data_type => 'integer' },
63 => { data_type => 'integer', extra => { unsigned => 1 } },
65 => { data_type => 'integer' },
66 'bigint' => { data_type => 'bigint' },
68 => { data_type => 'bigint', extra => { unsigned => 1 } },
70 'serial' => { data_type => 'bigint', is_auto_increment => 1, extra => { unsigned => 1 } },
72 'float' => { data_type => 'float' },
74 => { data_type => 'float', extra => { unsigned => 1 } },
75 'double' => { data_type => 'double precision' },
77 => { data_type => 'double precision', extra => { unsigned => 1 } },
79 { data_type => 'double precision' },
80 'double precision unsigned'
81 => { data_type => 'double precision', extra => { unsigned => 1 } },
83 # we skip 'real' because its alias depends on the 'REAL AS FLOAT' setting
85 'float(2)' => { data_type => 'float' },
86 'float(24)' => { data_type => 'float' },
87 'float(25)' => { data_type => 'double precision' },
89 'float(3,3)' => { data_type => 'float', size => [3,3] },
90 'double(3,3)' => { data_type => 'double precision', size => [3,3] },
91 'double precision(3,3)'
92 => { data_type => 'double precision', size => [3,3] },
94 'decimal' => { data_type => 'decimal' },
96 => { data_type => 'decimal', extra => { unsigned => 1 } },
97 'dec' => { data_type => 'decimal' },
98 'numeric' => { data_type => 'decimal' },
99 'fixed' => { data_type => 'decimal' },
101 'decimal(3)' => { data_type => 'decimal', size => [3,0] },
103 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
104 'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
105 'numeric(3,3)' => { data_type => 'decimal', size => [3,3] },
106 'fixed(3,3)' => { data_type => 'decimal', size => [3,3] },
108 # Date and Time Types
109 'date' => { data_type => 'date', datetime_undef_if_invalid => 1 },
110 'datetime' => { data_type => 'datetime', datetime_undef_if_invalid => 1 },
111 'timestamp default current_timestamp'
112 => { data_type => 'timestamp', default_value => \'current_timestamp', datetime_undef_if_invalid => 1 },
113 'time' => { data_type => 'time' },
114 'year' => { data_type => 'year' },
115 'year(4)' => { data_type => 'year' },
116 'year(2)' => { data_type => 'year', size => 2 },
119 'char' => { data_type => 'char', size => 1 },
120 'char(11)' => { data_type => 'char', size => 11 },
121 'varchar(20)' => { data_type => 'varchar', size => 20 },
122 'binary' => { data_type => 'binary', size => 1 },
123 'binary(11)' => { data_type => 'binary', size => 11 },
124 'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
126 'tinyblob' => { data_type => 'tinyblob' },
127 'tinytext' => { data_type => 'tinytext' },
128 'blob' => { data_type => 'blob' },
130 # text(M) types will map to the appropriate type, length is not stored
131 'text' => { data_type => 'text' },
133 'mediumblob' => { data_type => 'mediumblob' },
134 'mediumtext' => { data_type => 'mediumtext' },
135 'longblob' => { data_type => 'longblob' },
136 'longtext' => { data_type => 'longtext' },
138 "enum('foo','bar','baz')"
139 => { data_type => 'enum', extra => { list => [qw/foo bar baz/] } },
140 "enum('foo \\'bar\\' baz', 'foo ''bar'' quux')"
141 => { data_type => 'enum', extra => { list => [q{foo 'bar' baz}, q{foo 'bar' quux}] } },
142 "set('foo \\'bar\\' baz', 'foo ''bar'' quux')"
143 => { data_type => 'set', extra => { list => [q{foo 'bar' baz}, q{foo 'bar' quux}] } },
144 "set('foo','bar','baz')"
145 => { data_type => 'set', extra => { list => [qw/foo bar baz/] } },
148 "enum('11,10 (<500)/0 DUN','4,90 (<120)/0 EUR') NOT NULL default '11,10 (<500)/0 DUN'"
149 => { data_type => 'enum', extra => { list => ['11,10 (<500)/0 DUN', '4,90 (<120)/0 EUR'] }, default_value => '11,10 (<500)/0 DUN' },
150 "set('11_10 (<500)/0 DUN','4_90 (<120)/0 EUR') NOT NULL default '11_10 (<500)/0 DUN'"
151 => { data_type => 'set', extra => { list => ['11_10 (<500)/0 DUN', '4_90 (<120)/0 EUR'] }, default_value => '11_10 (<500)/0 DUN' },
152 "enum('19,90 (<500)/0 EUR','4,90 (<120)/0 EUR','7,90 (<200)/0 CHF','300 (<6000)/0 CZK','4,90 (<100)/0 EUR','39 (<900)/0 DKK','299 (<5000)/0 EEK','9,90 (<250)/0 EUR','3,90 (<100)/0 GBP','3000 (<70000)/0 HUF','4000 (<70000)/0 JPY','13,90 (<200)/0 LVL','99 (<2500)/0 NOK','39 (<1000)/0 PLN','1000 (<20000)/0 RUB','49 (<2500)/0 SEK','29 (<600)/0 USD','19,90 (<600)/0 EUR','0 EUR','0 CHF') NOT NULL default '19,90 (<500)/0 EUR'"
153 => { data_type => 'enum', extra => { list => ['19,90 (<500)/0 EUR','4,90 (<120)/0 EUR','7,90 (<200)/0 CHF','300 (<6000)/0 CZK','4,90 (<100)/0 EUR','39 (<900)/0 DKK','299 (<5000)/0 EEK','9,90 (<250)/0 EUR','3,90 (<100)/0 GBP','3000 (<70000)/0 HUF','4000 (<70000)/0 JPY','13,90 (<200)/0 LVL','99 (<2500)/0 NOK','39 (<1000)/0 PLN','1000 (<20000)/0 RUB','49 (<2500)/0 SEK','29 (<600)/0 USD','19,90 (<600)/0 EUR','0 EUR','0 CHF'] }, default_value => '19,90 (<500)/0 EUR' },
158 CREATE TABLE `mysql_loader-test1` (
159 id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'The\15\12Column',
161 ) $innodb COMMENT 'The\15\12Table'
164 CREATE VIEW mysql_loader_test2 AS SELECT * FROM `mysql_loader-test1`
168 CREATE TABLE `mysql_loader_test3` (
169 `ISO3_code` char(3) NOT NULL default '',
170 `lang_pref` enum('de','en','fr','nl','dk','es','se') NOT NULL,
171 `vat` decimal(4,2) default '16.00',
172 `price_group` enum('EUR_DEFAULT','GBP_GBR','EUR_AUT_BEL_FRA_IRL_NLD','EUR_DNK_SWE','EUR_AUT','EUR_BEL','EUR_FIN','EUR_FRA','EUR_IRL','EUR_NLD','EUR_DNK','EUR_POL','EUR_PRT','EUR_SWE','CHF_CHE','DKK_DNK','SEK_SWE','NOK_NOR','USD_USA','CZK_CZE','PLN_POL','RUB_RUS','HUF_HUN','SKK_SVK','JPY_JPN','LVL_LVA','ROL_ROU','EEK_EST') NOT NULL default 'EUR_DEFAULT',
173 `del_group` enum('19,90 (<500)/0 EUR','4,90 (<120)/0 EUR','7,90 (<200)/0 CHF','300 (<6000)/0 CZK','4,90 (<100)/0 EUR','39 (<900)/0 DKK','299 (<5000)/0 EEK','9,90 (<250)/0 EUR','3,90 (<100)/0 GBP','3000 (<70000)/0 HUF','4000 (<70000)/0 JPY','13,90 (<200)/0 LVL','99 (<2500)/0 NOK','39 (<1000)/0 PLN','1000 (<20000)/0 RUB','49 (<2500)/0 SEK','29 (<600)/0 USD','19,90 (<600)/0 EUR','0 EUR','0 CHF') NOT NULL default '19,90 (<500)/0 EUR',
174 `express_del_group` enum('NO','39 EUR (EXPRESS)','59 EUR (EXPRESS)','79 CHF (EXPRESS)','49 EUR (EXPRESS)','990 CZK (EXPRESS)','19,9 EUR (EXPRESS)','290 DKK (EXPRESS)','990 EEK (EXPRESS)','39 GBP (EXPRESS)','14000 HUF (EXPRESS)','49 LVL (EXPRESS)','590 NOK (EXPRESS)','250 PLN (EXPRESS)','490 SEK (EXPRESS)') NOT NULL default 'NO',
175 `pmethod` varchar(255) NOT NULL default 'VISA,MASTER',
176 `delivery_time` varchar(5) default NULL,
177 `express_delivery_time` varchar(5) default NULL,
178 `eu` int(1) default '0',
179 `cod_costs` varchar(12) default NULL,
180 PRIMARY KEY (`ISO3_code`)
184 pre_drop_ddl => [ 'DROP VIEW mysql_loader_test2', ],
185 drop => [ 'mysql_loader-test1', 'mysql_loader_test3' ],
188 my ($monikers, $classes);
189 ($schema, $monikers, $classes) = @_;
191 is $monikers->{'mysql_loader-test1'}, 'MysqlLoaderTest1',
192 'table with dash correctly monikerized';
194 my $rsrc = $schema->source('MysqlLoaderTest2');
196 is $rsrc->column_info('value')->{data_type}, 'varchar',
197 'view introspected successfully';
199 $rsrc = $schema->source('MysqlLoaderTest3');
201 is_deeply $rsrc->column_info('del_group')->{extra}{list}, ['19,90 (<500)/0 EUR','4,90 (<120)/0 EUR','7,90 (<200)/0 CHF','300 (<6000)/0 CZK','4,90 (<100)/0 EUR','39 (<900)/0 DKK','299 (<5000)/0 EEK','9,90 (<250)/0 EUR','3,90 (<100)/0 GBP','3000 (<70000)/0 HUF','4000 (<70000)/0 JPY','13,90 (<200)/0 LVL','99 (<2500)/0 NOK','39 (<1000)/0 PLN','1000 (<20000)/0 RUB','49 (<2500)/0 SEK','29 (<600)/0 USD','19,90 (<600)/0 EUR','0 EUR','0 CHF'],
202 'hairy enum introspected correctly';
204 my $class = $classes->{'mysql_loader-test1'};
205 my $filename = $schema->loader->get_dump_filename($class);
207 my $code = slurp_file $filename;
209 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
212 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
213 'column comment and attrs';
216 my $dbh = $schema->storage->dbh;
219 $dbh->do('CREATE DATABASE `dbicsl-test`');
222 skip "no CREATE DATABASE privileges", 28 * 2;
226 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
227 id INT AUTO_INCREMENT PRIMARY KEY,
232 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
233 id INT AUTO_INCREMENT PRIMARY KEY,
235 four_id INTEGER UNIQUE,
236 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
239 $dbh->do('CREATE DATABASE `dbicsl.test`');
241 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
242 id INT AUTO_INCREMENT PRIMARY KEY,
244 mysql_loader_test4_id INTEGER,
245 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
249 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
250 id INT AUTO_INCREMENT PRIMARY KEY,
252 six_id INTEGER UNIQUE,
253 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
257 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
258 id INT AUTO_INCREMENT PRIMARY KEY,
260 mysql_loader_test7_id INTEGER,
261 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
264 # Test dumping a rel to a table that's not part of the dump.
265 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
267 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
268 id INT AUTO_INCREMENT PRIMARY KEY,
273 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
274 id INT AUTO_INCREMENT PRIMARY KEY,
276 mysql_loader_test9_id INTEGER,
277 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
281 $databases_created = 1;
283 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
284 if ($db_schema eq '%') {
286 $dbh->selectall_arrayref('SHOW DATABASES');
289 skip 'no SHOW DATABASES privileges', 28;
294 rmtree EXTRA_DUMP_DIR;
297 local $SIG{__WARN__} = sub {
298 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
305 db_schema => $db_schema,
306 dump_directory => EXTRA_DUMP_DIR,
309 [ $dsn, $user, $password ],
312 diag join "\n", @warns if @warns;
315 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
317 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
320 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
321 } 'connected test schema';
324 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
325 } 'got source for table in database name with dash';
327 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
328 'column in database name with dash';
330 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
331 'column in database name with dash';
333 is try { $rsrc->column_info('value')->{size} }, 100,
334 'column in database name with dash';
337 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
338 } 'got resultset for table in database name with dash';
341 ok $row = $rs->create({ value => 'foo' });
342 } 'executed SQL on table in database name with dash';
345 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
347 $rel_info = try { $rsrc->relationship_info('mysql_loader_test5') };
349 is_deeply $rel_info->{cond}, {
350 'foreign.four_id' => 'self.id'
351 }, 'relationship in database name with dash';
353 is $rel_info->{attrs}{accessor}, 'single',
354 'relationship in database name with dash';
356 is $rel_info->{attrs}{join_type}, 'LEFT',
357 'relationship in database name with dash';
361 ok $rsrc = $test_schema->source('MysqlLoaderTest5');
362 } 'got source for table in database name with dash';
364 %uniqs = try { $rsrc->unique_constraints };
367 'got unique and primary constraint in database name with dash';
370 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
371 } 'got source for table in database name with dot';
373 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
374 'column in database name with dot introspected correctly';
376 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
377 'column in database name with dot introspected correctly';
379 is try { $rsrc->column_info('value')->{size} }, 100,
380 'column in database name with dot introspected correctly';
383 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
384 } 'got resultset for table in database name with dot';
387 ok $row = $rs->create({ value => 'foo' });
388 } 'executed SQL on table in database name with dot';
391 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
393 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
395 is_deeply $rel_info->{cond}, {
396 'foreign.six_id' => 'self.id'
397 }, 'relationship in database name with dot';
399 is $rel_info->{attrs}{accessor}, 'single',
400 'relationship in database name with dot';
402 is $rel_info->{attrs}{join_type}, 'LEFT',
403 'relationship in database name with dot';
407 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
408 } 'got source for table in database name with dot';
410 %uniqs = try { $rsrc->unique_constraints };
413 'got unique and primary constraint in database name with dot';
416 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
419 ok $test_schema->source('MysqlLoaderTest6')
420 ->has_relationship('mysql_loader_test4');
421 } 'cross-database relationship in multi-db_schema';
424 ok $test_schema->source('MysqlLoaderTest4')
425 ->has_relationship('mysql_loader_test6s');
426 } 'cross-database relationship in multi-db_schema';
429 ok $test_schema->source('MysqlLoaderTest8')
430 ->has_relationship('mysql_loader_test7');
431 } 'cross-database relationship in multi-db_schema';
434 ok $test_schema->source('MysqlLoaderTest7')
435 ->has_relationship('mysql_loader_test8s');
436 } 'cross-database relationship in multi-db_schema';
444 if( !$dsn || !$user ) {
445 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
448 diag $skip_rels_msg if not $test_innodb;
449 $tester->run_tests();
453 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
454 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
455 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
456 'dbicsl_test_ignored.mysql_loader_test9',
457 '`dbicsl-test`.mysql_loader_test8',
458 '`dbicsl.test`.mysql_loader_test7',
459 '`dbicsl.test`.mysql_loader_test6',
460 '`dbicsl-test`.mysql_loader_test5',
461 '`dbicsl-test`.mysql_loader_test4') {
463 $dbh->do("DROP TABLE $table");
466 diag "Error dropping table: $_";
470 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
472 $dbh->do("DROP DATABASE `$db`");
475 diag "Error dropping test database $db: $_";
479 rmtree EXTRA_DUMP_DIR;
482 # vim:et sts=4 sw=4 tw=0: