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`)
183 # 4 through 10 are used for the multi-schema tests
185 create table mysql_loader_test11 (
186 id int auto_increment primary key
190 create table mysql_loader_test12 (
191 id int auto_increment primary key,
193 foreign key (eleven_id) references mysql_loader_test11(id)
194 on delete restrict on update set null
198 pre_drop_ddl => [ 'DROP VIEW mysql_loader_test2', ],
199 drop => [ 'mysql_loader-test1', 'mysql_loader_test3', 'mysql_loader_test11', 'mysql_loader_test12' ],
202 my ($monikers, $classes);
203 ($schema, $monikers, $classes) = @_;
205 is $monikers->{'mysql_loader-test1'}, 'MysqlLoaderTest1',
206 'table with dash correctly monikerized';
208 my $rsrc = $schema->source('MysqlLoaderTest2');
210 is $rsrc->column_info('value')->{data_type}, 'varchar',
211 'view introspected successfully';
213 $rsrc = $schema->source('MysqlLoaderTest3');
215 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'],
216 'hairy enum introspected correctly';
218 my $class = $classes->{'mysql_loader-test1'};
219 my $filename = $schema->loader->get_dump_filename($class);
221 my $code = slurp_file $filename;
223 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
226 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
227 'column comment and attrs';
229 # test on delete/update fk clause introspection
230 ok ((my $rel_info = $schema->source('MysqlLoaderTest12')->relationship_info('eleven')),
233 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
234 'ON DELETE clause introspected correctly';
236 is $rel_info->{attrs}{on_update}, 'SET NULL',
237 'ON UPDATE clause introspected correctly';
239 # multischema tests follow
241 my $dbh = $schema->storage->dbh;
244 $dbh->do('CREATE DATABASE `dbicsl-test`');
247 diag "CREATE DATABASE returned error: '$_'";
248 skip "no CREATE DATABASE privileges", 30 * 2;
252 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
253 id INT AUTO_INCREMENT PRIMARY KEY,
258 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
259 id INT AUTO_INCREMENT PRIMARY KEY,
262 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
263 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
267 $dbh->do('CREATE DATABASE `dbicsl.test`');
269 # Test that keys are correctly cached by naming the primary and
270 # unique keys in this table with the same name as a table in
271 # the `dbicsl-test` schema differently.
273 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
274 pk INT AUTO_INCREMENT PRIMARY KEY,
277 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
278 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
283 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
284 id INT AUTO_INCREMENT PRIMARY KEY,
286 mysql_loader_test4_id INTEGER,
287 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
291 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
292 id INT AUTO_INCREMENT PRIMARY KEY,
294 six_id INTEGER UNIQUE,
295 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
299 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
300 id INT AUTO_INCREMENT PRIMARY KEY,
302 mysql_loader_test7_id INTEGER,
303 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
306 # Test dumping a rel to a table that's not part of the dump.
307 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
309 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
310 id INT AUTO_INCREMENT PRIMARY KEY,
315 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
316 id INT AUTO_INCREMENT PRIMARY KEY,
318 mysql_loader_test9_id INTEGER,
319 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
323 $databases_created = 1;
325 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
326 if ($db_schema eq '%') {
328 $dbh->selectall_arrayref('SHOW DATABASES');
331 skip 'no SHOW DATABASES privileges', 28;
336 rmtree EXTRA_DUMP_DIR;
339 local $SIG{__WARN__} = sub {
340 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
347 db_schema => $db_schema,
348 dump_directory => EXTRA_DUMP_DIR,
351 [ $dsn, $user, $password ],
354 diag join "\n", @warns if @warns;
357 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
359 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
362 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
363 } 'connected test schema';
366 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
367 } 'got source for table in database name with dash';
369 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
370 'column in database name with dash';
372 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
373 'column in database name with dash';
375 is try { $rsrc->column_info('value')->{size} }, 100,
376 'column in database name with dash';
379 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
380 } 'got resultset for table in database name with dash';
383 ok $row = $rs->create({ value => 'foo' });
384 } 'executed SQL on table in database name with dash';
387 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
389 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
391 is_deeply $rel_info->{cond}, {
392 'foreign.four_id' => 'self.id'
393 }, 'relationship in database name with dash';
395 is $rel_info->{attrs}{accessor}, 'single',
396 'relationship in database name with dash';
398 is $rel_info->{attrs}{join_type}, 'LEFT',
399 'relationship in database name with dash';
403 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
404 } 'got source for table in database name with dash';
406 %uniqs = try { $rsrc->unique_constraints };
409 'got unique and primary constraint in database name with dash';
411 delete $uniqs{primary};
413 is_deeply ((values %uniqs)[0], ['four_id'],
414 'unique constraint is correct in database name with dash');
417 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
418 } 'got source for table in database name with dot';
420 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
421 'column in database name with dot introspected correctly';
423 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
424 'column in database name with dot introspected correctly';
426 is try { $rsrc->column_info('value')->{size} }, 100,
427 'column in database name with dot introspected correctly';
430 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
431 } 'got resultset for table in database name with dot';
434 ok $row = $rs->create({ value => 'foo' });
435 } 'executed SQL on table in database name with dot';
438 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
440 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
442 is_deeply $rel_info->{cond}, {
443 'foreign.six_id' => 'self.id'
444 }, 'relationship in database name with dot';
446 is $rel_info->{attrs}{accessor}, 'single',
447 'relationship in database name with dot';
449 is $rel_info->{attrs}{join_type}, 'LEFT',
450 'relationship in database name with dot';
454 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
455 } 'got source for table in database name with dot';
457 %uniqs = try { $rsrc->unique_constraints };
460 'got unique and primary constraint in database name with dot';
462 delete $uniqs{primary};
464 is_deeply ((values %uniqs)[0], ['six_id'],
465 'unique constraint is correct in database name with dot');
468 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
471 ok $test_schema->source('MysqlLoaderTest6')
472 ->has_relationship('mysql_loader_test4');
473 } 'cross-database relationship in multi-db_schema';
476 ok $test_schema->source('MysqlLoaderTest4')
477 ->has_relationship('mysql_loader_test6s');
478 } 'cross-database relationship in multi-db_schema';
481 ok $test_schema->source('MysqlLoaderTest8')
482 ->has_relationship('mysql_loader_test7');
483 } 'cross-database relationship in multi-db_schema';
486 ok $test_schema->source('MysqlLoaderTest7')
487 ->has_relationship('mysql_loader_test8s');
488 } 'cross-database relationship in multi-db_schema';
496 if( !$dsn || !$user ) {
497 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
500 diag $skip_rels_msg if not $test_innodb;
501 $tester->run_tests();
505 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
506 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
507 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
508 'dbicsl_test_ignored.mysql_loader_test9',
509 '`dbicsl-test`.mysql_loader_test8',
510 '`dbicsl.test`.mysql_loader_test7',
511 '`dbicsl.test`.mysql_loader_test6',
512 '`dbicsl.test`.mysql_loader_test5',
513 '`dbicsl-test`.mysql_loader_test5',
514 '`dbicsl-test`.mysql_loader_test4') {
516 $dbh->do("DROP TABLE $table");
519 diag "Error dropping table: $_";
523 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
525 $dbh->do("DROP DATABASE `$db`");
528 diag "Error dropping test database $db: $_";
532 rmtree EXTRA_DUMP_DIR;
535 # vim:et sts=4 sw=4 tw=0: