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' },
140 "$_('','foo','bar','baz')"
141 => { data_type => $_, extra => { list => ['', qw/foo bar baz/] } },
142 "$_('foo \\'bar\\' baz', 'foo ''bar'' quux')"
143 => { data_type => $_, extra => { list => [q{foo 'bar' baz}, q{foo 'bar' quux}] } },
144 "$_('''', '''foo', 'bar''')"
145 => { data_type => $_, extra => { list => [qw(' 'foo bar')] } },
146 "$_('\\'', '\\'foo', 'bar\\'')",
147 => { data_type => $_, extra => { list => [qw(' 'foo bar')] } },
152 "enum('11,10 (<500)/0 DUN','4,90 (<120)/0 EUR') NOT NULL default '11,10 (<500)/0 DUN'"
153 => { data_type => 'enum', extra => { list => ['11,10 (<500)/0 DUN', '4,90 (<120)/0 EUR'] }, default_value => '11,10 (<500)/0 DUN' },
154 "set('11_10 (<500)/0 DUN','4_90 (<120)/0 EUR') NOT NULL default '11_10 (<500)/0 DUN'"
155 => { data_type => 'set', extra => { list => ['11_10 (<500)/0 DUN', '4_90 (<120)/0 EUR'] }, default_value => '11_10 (<500)/0 DUN' },
156 "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'"
157 => { 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' },
162 CREATE TABLE `mysql_loader-test1` (
163 id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'The\15\12Column',
165 ) $innodb COMMENT 'The\15\12Table'
168 CREATE VIEW mysql_loader_test2 AS SELECT * FROM `mysql_loader-test1`
172 CREATE TABLE `mysql_loader_test3` (
173 `ISO3_code` char(3) NOT NULL default '',
174 `lang_pref` enum('de','en','fr','nl','dk','es','se') NOT NULL,
175 `vat` decimal(4,2) default '16.00',
176 `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',
177 `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',
178 `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',
179 `pmethod` varchar(255) NOT NULL default 'VISA,MASTER',
180 `delivery_time` varchar(5) default NULL,
181 `express_delivery_time` varchar(5) default NULL,
182 `eu` int(1) default '0',
183 `cod_costs` varchar(12) default NULL,
184 PRIMARY KEY (`ISO3_code`)
187 # 4 through 10 are used for the multi-schema tests
189 create table mysql_loader_test11 (
190 id int auto_increment primary key
194 create table mysql_loader_test12 (
195 id int auto_increment primary key,
197 foreign key (eleven_id) references mysql_loader_test11(id)
198 on delete restrict on update set null
202 pre_drop_ddl => [ 'DROP VIEW mysql_loader_test2', ],
203 drop => [ 'mysql_loader-test1', 'mysql_loader_test3', 'mysql_loader_test11', 'mysql_loader_test12' ],
206 my ($monikers, $classes);
207 ($schema, $monikers, $classes) = @_;
209 is $monikers->{'mysql_loader-test1'}, 'MysqlLoaderTest1',
210 'table with dash correctly monikerized';
212 my $rsrc = $schema->source('MysqlLoaderTest2');
214 is $rsrc->column_info('value')->{data_type}, 'varchar',
215 'view introspected successfully';
217 $rsrc = $schema->source('MysqlLoaderTest3');
219 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'],
220 'hairy enum introspected correctly';
222 my $class = $classes->{'mysql_loader-test1'};
223 my $filename = $schema->loader->get_dump_filename($class);
225 my $code = slurp_file $filename;
227 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
230 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
231 'column comment and attrs';
233 # test on delete/update fk clause introspection
234 ok ((my $rel_info = $schema->source('MysqlLoaderTest12')->relationship_info('eleven')),
237 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
238 'ON DELETE clause introspected correctly';
240 is $rel_info->{attrs}{on_update}, 'SET NULL',
241 'ON UPDATE clause introspected correctly';
243 # multischema tests follow
245 my $dbh = $schema->storage->dbh;
248 $dbh->do('CREATE DATABASE `dbicsl-test`');
251 note "CREATE DATABASE returned error: '$_'";
252 skip "no CREATE DATABASE privileges", 30 * 2;
256 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
257 id INT AUTO_INCREMENT PRIMARY KEY,
262 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
263 id INT AUTO_INCREMENT PRIMARY KEY,
266 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
267 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
271 $dbh->do('CREATE DATABASE `dbicsl.test`');
273 # Test that keys are correctly cached by naming the primary and
274 # unique keys in this table with the same name as a table in
275 # the `dbicsl-test` schema differently.
277 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
278 pk INT AUTO_INCREMENT PRIMARY KEY,
281 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
282 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
287 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
288 id INT AUTO_INCREMENT PRIMARY KEY,
290 mysql_loader_test4_id INTEGER,
291 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
295 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
296 id INT AUTO_INCREMENT PRIMARY KEY,
298 six_id INTEGER UNIQUE,
299 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
303 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
304 id INT AUTO_INCREMENT PRIMARY KEY,
306 mysql_loader_test7_id INTEGER,
307 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
310 # Test dumping a rel to a table that's not part of the dump.
311 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
313 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
314 id INT AUTO_INCREMENT PRIMARY KEY,
319 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
320 id INT AUTO_INCREMENT PRIMARY KEY,
322 mysql_loader_test9_id INTEGER,
323 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
327 $databases_created = 1;
329 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
330 if ($db_schema eq '%') {
332 $dbh->selectall_arrayref('SHOW DATABASES');
335 skip 'no SHOW DATABASES privileges', 28;
340 rmtree EXTRA_DUMP_DIR;
343 local $SIG{__WARN__} = sub {
344 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
351 db_schema => $db_schema,
352 dump_directory => EXTRA_DUMP_DIR,
355 [ $dsn, $user, $password ],
358 diag join "\n", @warns if @warns;
361 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
363 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
366 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
367 } 'connected test schema';
370 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
371 } 'got source for table in database name with dash';
373 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
374 'column in database name with dash';
376 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
377 'column in database name with dash';
379 is try { $rsrc->column_info('value')->{size} }, 100,
380 'column in database name with dash';
383 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
384 } 'got resultset for table in database name with dash';
387 ok $row = $rs->create({ value => 'foo' });
388 } 'executed SQL on table in database name with dash';
391 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
393 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
395 is_deeply $rel_info->{cond}, {
396 'foreign.four_id' => 'self.id'
397 }, 'relationship in database name with dash';
399 is $rel_info->{attrs}{accessor}, 'single',
400 'relationship in database name with dash';
402 is $rel_info->{attrs}{join_type}, 'LEFT',
403 'relationship in database name with dash';
407 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
408 } 'got source for table in database name with dash';
410 %uniqs = try { $rsrc->unique_constraints };
413 'got unique and primary constraint in database name with dash';
415 delete $uniqs{primary};
417 is_deeply ((values %uniqs)[0], ['four_id'],
418 'unique constraint is correct in database name with dash');
421 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
422 } 'got source for table in database name with dot';
424 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
425 'column in database name with dot introspected correctly';
427 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
428 'column in database name with dot introspected correctly';
430 is try { $rsrc->column_info('value')->{size} }, 100,
431 'column in database name with dot introspected correctly';
434 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
435 } 'got resultset for table in database name with dot';
438 ok $row = $rs->create({ value => 'foo' });
439 } 'executed SQL on table in database name with dot';
442 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
444 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
446 is_deeply $rel_info->{cond}, {
447 'foreign.six_id' => 'self.id'
448 }, 'relationship in database name with dot';
450 is $rel_info->{attrs}{accessor}, 'single',
451 'relationship in database name with dot';
453 is $rel_info->{attrs}{join_type}, 'LEFT',
454 'relationship in database name with dot';
458 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
459 } 'got source for table in database name with dot';
461 %uniqs = try { $rsrc->unique_constraints };
464 'got unique and primary constraint in database name with dot';
466 delete $uniqs{primary};
468 is_deeply ((values %uniqs)[0], ['six_id'],
469 'unique constraint is correct in database name with dot');
472 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
475 ok $test_schema->source('MysqlLoaderTest6')
476 ->has_relationship('mysql_loader_test4');
477 } 'cross-database relationship in multi-db_schema';
480 ok $test_schema->source('MysqlLoaderTest4')
481 ->has_relationship('mysql_loader_test6s');
482 } 'cross-database relationship in multi-db_schema';
485 ok $test_schema->source('MysqlLoaderTest8')
486 ->has_relationship('mysql_loader_test7');
487 } 'cross-database relationship in multi-db_schema';
490 ok $test_schema->source('MysqlLoaderTest7')
491 ->has_relationship('mysql_loader_test8s');
492 } 'cross-database relationship in multi-db_schema';
500 if( !$dsn || !$user ) {
501 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
504 diag $skip_rels_msg if not $test_innodb;
505 $tester->run_tests();
509 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
510 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
511 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
512 'dbicsl_test_ignored.mysql_loader_test9',
513 '`dbicsl-test`.mysql_loader_test8',
514 '`dbicsl.test`.mysql_loader_test7',
515 '`dbicsl.test`.mysql_loader_test6',
516 '`dbicsl.test`.mysql_loader_test5',
517 '`dbicsl-test`.mysql_loader_test5',
518 '`dbicsl-test`.mysql_loader_test4') {
520 $dbh->do("DROP TABLE $table");
523 diag "Error dropping table: $_";
527 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
529 $dbh->do("DROP DATABASE `$db`");
532 diag "Error dropping test database $db: $_";
536 rmtree EXTRA_DUMP_DIR;
539 # vim:et sts=4 sw=4 tw=0: