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,
41 default_on_clause => 'RESTRICT',
43 # http://dev.mysql.com/doc/refman/5.5/en/data-type-overview.html
45 'bit' => { data_type => 'bit', size => 1 },
46 'bit(11)' => { data_type => 'bit', size => 11 },
48 'bool' => { data_type => 'tinyint' },
49 'boolean' => { data_type => 'tinyint' },
50 'tinyint' => { data_type => 'tinyint' },
52 => { data_type => 'tinyint', extra => { unsigned => 1 } },
53 'smallint' => { data_type => 'smallint' },
55 => { data_type => 'smallint', extra => { unsigned => 1 } },
56 'mediumint' => { data_type => 'mediumint' },
58 => { data_type => 'mediumint', extra => { unsigned => 1 } },
59 'int' => { data_type => 'integer' },
61 => { data_type => 'integer', extra => { unsigned => 1 } },
62 'integer' => { data_type => 'integer' },
64 => { data_type => 'integer', extra => { unsigned => 1 } },
66 => { data_type => 'integer' },
67 'bigint' => { data_type => 'bigint' },
69 => { data_type => 'bigint', extra => { unsigned => 1 } },
71 'serial' => { data_type => 'bigint', is_auto_increment => 1, extra => { unsigned => 1 } },
73 'float' => { data_type => 'float' },
75 => { data_type => 'float', extra => { unsigned => 1 } },
76 'double' => { data_type => 'double precision' },
78 => { data_type => 'double precision', extra => { unsigned => 1 } },
80 { data_type => 'double precision' },
81 'double precision unsigned'
82 => { data_type => 'double precision', extra => { unsigned => 1 } },
84 # we skip 'real' because its alias depends on the 'REAL AS FLOAT' setting
86 'float(2)' => { data_type => 'float' },
87 'float(24)' => { data_type => 'float' },
88 'float(25)' => { data_type => 'double precision' },
90 'float(3,3)' => { data_type => 'float', size => [3,3] },
91 'double(3,3)' => { data_type => 'double precision', size => [3,3] },
92 'double precision(3,3)'
93 => { data_type => 'double precision', size => [3,3] },
95 'decimal' => { data_type => 'decimal' },
97 => { data_type => 'decimal', extra => { unsigned => 1 } },
98 'dec' => { data_type => 'decimal' },
99 'numeric' => { data_type => 'decimal' },
100 'fixed' => { data_type => 'decimal' },
102 'decimal(3)' => { data_type => 'decimal', size => [3,0] },
104 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
105 'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
106 'numeric(3,3)' => { data_type => 'decimal', size => [3,3] },
107 'fixed(3,3)' => { data_type => 'decimal', size => [3,3] },
109 # Date and Time Types
110 'date' => { data_type => 'date', datetime_undef_if_invalid => 1 },
111 'datetime' => { data_type => 'datetime', datetime_undef_if_invalid => 1 },
112 'timestamp default current_timestamp'
113 => { data_type => 'timestamp', default_value => \'current_timestamp', datetime_undef_if_invalid => 1 },
114 'time' => { data_type => 'time' },
115 'year' => { data_type => 'year' },
116 'year(4)' => { data_type => 'year' },
117 'year(2)' => { data_type => 'year', size => 2 },
120 'char' => { data_type => 'char', size => 1 },
121 'char(11)' => { data_type => 'char', size => 11 },
122 'varchar(20)' => { data_type => 'varchar', size => 20 },
123 'binary' => { data_type => 'binary', size => 1 },
124 'binary(11)' => { data_type => 'binary', size => 11 },
125 'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
127 'tinyblob' => { data_type => 'tinyblob' },
128 'tinytext' => { data_type => 'tinytext' },
129 'blob' => { data_type => 'blob' },
131 # text(M) types will map to the appropriate type, length is not stored
132 'text' => { data_type => 'text' },
134 'mediumblob' => { data_type => 'mediumblob' },
135 'mediumtext' => { data_type => 'mediumtext' },
136 'longblob' => { data_type => 'longblob' },
137 'longtext' => { data_type => 'longtext' },
139 "enum('foo','bar','baz')"
140 => { data_type => 'enum', extra => { list => [qw/foo bar baz/] } },
141 "enum('foo \\'bar\\' baz', 'foo ''bar'' quux')"
142 => { data_type => 'enum', extra => { list => [q{foo 'bar' baz}, q{foo 'bar' quux}] } },
143 "set('foo \\'bar\\' baz', 'foo ''bar'' quux')"
144 => { data_type => 'set', extra => { list => [q{foo 'bar' baz}, q{foo 'bar' quux}] } },
145 "set('foo','bar','baz')"
146 => { data_type => 'set', extra => { list => [qw/foo bar baz/] } },
149 "enum('11,10 (<500)/0 DUN','4,90 (<120)/0 EUR') NOT NULL default '11,10 (<500)/0 DUN'"
150 => { data_type => 'enum', extra => { list => ['11,10 (<500)/0 DUN', '4,90 (<120)/0 EUR'] }, default_value => '11,10 (<500)/0 DUN' },
151 "set('11_10 (<500)/0 DUN','4_90 (<120)/0 EUR') NOT NULL default '11_10 (<500)/0 DUN'"
152 => { data_type => 'set', extra => { list => ['11_10 (<500)/0 DUN', '4_90 (<120)/0 EUR'] }, default_value => '11_10 (<500)/0 DUN' },
153 "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'"
154 => { 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' },
159 CREATE TABLE `mysql_loader-test1` (
160 id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'The\15\12Column',
162 ) $innodb COMMENT 'The\15\12Table'
165 CREATE VIEW mysql_loader_test2 AS SELECT * FROM `mysql_loader-test1`
169 CREATE TABLE `mysql_loader_test3` (
170 `ISO3_code` char(3) NOT NULL default '',
171 `lang_pref` enum('de','en','fr','nl','dk','es','se') NOT NULL,
172 `vat` decimal(4,2) default '16.00',
173 `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',
174 `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',
175 `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',
176 `pmethod` varchar(255) NOT NULL default 'VISA,MASTER',
177 `delivery_time` varchar(5) default NULL,
178 `express_delivery_time` varchar(5) default NULL,
179 `eu` int(1) default '0',
180 `cod_costs` varchar(12) default NULL,
181 PRIMARY KEY (`ISO3_code`)
184 # 4 through 10 are used for the multi-schema tests
186 create table mysql_loader_test11 (
187 id int auto_increment primary key
191 create table mysql_loader_test12 (
192 id int auto_increment primary key,
194 foreign key (eleven_id) references mysql_loader_test11(id)
195 on delete restrict on update set null
199 pre_drop_ddl => [ 'DROP VIEW mysql_loader_test2', ],
200 drop => [ 'mysql_loader-test1', 'mysql_loader_test3', 'mysql_loader_test11', 'mysql_loader_test12' ],
203 my ($monikers, $classes);
204 ($schema, $monikers, $classes) = @_;
206 is $monikers->{'mysql_loader-test1'}, 'MysqlLoaderTest1',
207 'table with dash correctly monikerized';
209 my $rsrc = $schema->source('MysqlLoaderTest2');
211 is $rsrc->column_info('value')->{data_type}, 'varchar',
212 'view introspected successfully';
214 $rsrc = $schema->source('MysqlLoaderTest3');
216 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'],
217 'hairy enum introspected correctly';
219 my $class = $classes->{'mysql_loader-test1'};
220 my $filename = $schema->loader->get_dump_filename($class);
222 my $code = slurp_file $filename;
224 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
227 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
228 'column comment and attrs';
230 # test on delete/update fk clause introspection
231 ok ((my $rel_info = $schema->source('MysqlLoaderTest12')->relationship_info('eleven')),
234 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
235 'ON DELETE clause introspected correctly';
237 is $rel_info->{attrs}{on_update}, 'SET NULL',
238 'ON UPDATE clause introspected correctly';
240 # multischema tests follow
242 my $dbh = $schema->storage->dbh;
245 $dbh->do('CREATE DATABASE `dbicsl-test`');
248 diag "CREATE DATABASE returned error: '$_'";
249 skip "no CREATE DATABASE privileges", 30 * 2;
253 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
254 id INT AUTO_INCREMENT PRIMARY KEY,
259 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
260 id INT AUTO_INCREMENT PRIMARY KEY,
263 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
264 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
268 $dbh->do('CREATE DATABASE `dbicsl.test`');
270 # Test that keys are correctly cached by naming the primary and
271 # unique keys in this table with the same name as a table in
272 # the `dbicsl-test` schema differently.
274 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
275 pk INT AUTO_INCREMENT PRIMARY KEY,
278 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
279 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
284 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
285 id INT AUTO_INCREMENT PRIMARY KEY,
287 mysql_loader_test4_id INTEGER,
288 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
292 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
293 id INT AUTO_INCREMENT PRIMARY KEY,
295 six_id INTEGER UNIQUE,
296 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
300 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
301 id INT AUTO_INCREMENT PRIMARY KEY,
303 mysql_loader_test7_id INTEGER,
304 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
307 # Test dumping a rel to a table that's not part of the dump.
308 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
310 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
311 id INT AUTO_INCREMENT PRIMARY KEY,
316 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
317 id INT AUTO_INCREMENT PRIMARY KEY,
319 mysql_loader_test9_id INTEGER,
320 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
324 $databases_created = 1;
326 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
327 if ($db_schema eq '%') {
329 $dbh->selectall_arrayref('SHOW DATABASES');
332 skip 'no SHOW DATABASES privileges', 28;
337 rmtree EXTRA_DUMP_DIR;
340 local $SIG{__WARN__} = sub {
341 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
348 db_schema => $db_schema,
349 dump_directory => EXTRA_DUMP_DIR,
352 [ $dsn, $user, $password ],
355 diag join "\n", @warns if @warns;
358 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
360 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
363 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
364 } 'connected test schema';
367 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
368 } 'got source for table in database name with dash';
370 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
371 'column in database name with dash';
373 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
374 'column in database name with dash';
376 is try { $rsrc->column_info('value')->{size} }, 100,
377 'column in database name with dash';
380 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
381 } 'got resultset for table in database name with dash';
384 ok $row = $rs->create({ value => 'foo' });
385 } 'executed SQL on table in database name with dash';
388 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
390 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
392 is_deeply $rel_info->{cond}, {
393 'foreign.four_id' => 'self.id'
394 }, 'relationship in database name with dash';
396 is $rel_info->{attrs}{accessor}, 'single',
397 'relationship in database name with dash';
399 is $rel_info->{attrs}{join_type}, 'LEFT',
400 'relationship in database name with dash';
404 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
405 } 'got source for table in database name with dash';
407 %uniqs = try { $rsrc->unique_constraints };
410 'got unique and primary constraint in database name with dash';
412 delete $uniqs{primary};
414 is_deeply ((values %uniqs)[0], ['four_id'],
415 'unique constraint is correct in database name with dash');
418 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
419 } 'got source for table in database name with dot';
421 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
422 'column in database name with dot introspected correctly';
424 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
425 'column in database name with dot introspected correctly';
427 is try { $rsrc->column_info('value')->{size} }, 100,
428 'column in database name with dot introspected correctly';
431 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
432 } 'got resultset for table in database name with dot';
435 ok $row = $rs->create({ value => 'foo' });
436 } 'executed SQL on table in database name with dot';
439 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
441 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
443 is_deeply $rel_info->{cond}, {
444 'foreign.six_id' => 'self.id'
445 }, 'relationship in database name with dot';
447 is $rel_info->{attrs}{accessor}, 'single',
448 'relationship in database name with dot';
450 is $rel_info->{attrs}{join_type}, 'LEFT',
451 'relationship in database name with dot';
455 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
456 } 'got source for table in database name with dot';
458 %uniqs = try { $rsrc->unique_constraints };
461 'got unique and primary constraint in database name with dot';
463 delete $uniqs{primary};
465 is_deeply ((values %uniqs)[0], ['six_id'],
466 'unique constraint is correct in database name with dot');
469 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
472 ok $test_schema->source('MysqlLoaderTest6')
473 ->has_relationship('mysql_loader_test4');
474 } 'cross-database relationship in multi-db_schema';
477 ok $test_schema->source('MysqlLoaderTest4')
478 ->has_relationship('mysql_loader_test6s');
479 } 'cross-database relationship in multi-db_schema';
482 ok $test_schema->source('MysqlLoaderTest8')
483 ->has_relationship('mysql_loader_test7');
484 } 'cross-database relationship in multi-db_schema';
487 ok $test_schema->source('MysqlLoaderTest7')
488 ->has_relationship('mysql_loader_test8s');
489 } 'cross-database relationship in multi-db_schema';
497 if( !$dsn || !$user ) {
498 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
501 diag $skip_rels_msg if not $test_innodb;
502 $tester->run_tests();
506 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
507 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
508 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
509 'dbicsl_test_ignored.mysql_loader_test9',
510 '`dbicsl-test`.mysql_loader_test8',
511 '`dbicsl.test`.mysql_loader_test7',
512 '`dbicsl.test`.mysql_loader_test6',
513 '`dbicsl.test`.mysql_loader_test5',
514 '`dbicsl-test`.mysql_loader_test5',
515 '`dbicsl-test`.mysql_loader_test4') {
517 $dbh->do("DROP TABLE $table");
520 diag "Error dropping table: $_";
524 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
526 $dbh->do("DROP DATABASE `$db`");
529 diag "Error dropping test database $db: $_";
533 rmtree EXTRA_DUMP_DIR;
536 # vim:et sts=4 sw=4 tw=0: