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 diag "CREATE DATABASE returned error: '$_'";
223 skip "no CREATE DATABASE privileges", 30 * 2;
227 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
228 id INT AUTO_INCREMENT PRIMARY KEY,
233 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
234 id INT AUTO_INCREMENT PRIMARY KEY,
237 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
238 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
242 $dbh->do('CREATE DATABASE `dbicsl.test`');
244 # Test that keys are correctly cached by naming the primary and
245 # unique keys in this table with the same name as a table in
246 # the `dbicsl-test` schema differently.
248 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
249 pk INT AUTO_INCREMENT PRIMARY KEY,
252 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
253 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
258 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
259 id INT AUTO_INCREMENT PRIMARY KEY,
261 mysql_loader_test4_id INTEGER,
262 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
266 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
267 id INT AUTO_INCREMENT PRIMARY KEY,
269 six_id INTEGER UNIQUE,
270 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
274 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
275 id INT AUTO_INCREMENT PRIMARY KEY,
277 mysql_loader_test7_id INTEGER,
278 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
281 # Test dumping a rel to a table that's not part of the dump.
282 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
284 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
285 id INT AUTO_INCREMENT PRIMARY KEY,
290 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
291 id INT AUTO_INCREMENT PRIMARY KEY,
293 mysql_loader_test9_id INTEGER,
294 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
298 $databases_created = 1;
300 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
301 if ($db_schema eq '%') {
303 $dbh->selectall_arrayref('SHOW DATABASES');
306 skip 'no SHOW DATABASES privileges', 28;
311 rmtree EXTRA_DUMP_DIR;
314 local $SIG{__WARN__} = sub {
315 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
322 db_schema => $db_schema,
323 dump_directory => EXTRA_DUMP_DIR,
326 [ $dsn, $user, $password ],
329 diag join "\n", @warns if @warns;
332 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
334 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
337 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
338 } 'connected test schema';
341 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
342 } 'got source for table in database name with dash';
344 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
345 'column in database name with dash';
347 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
348 'column in database name with dash';
350 is try { $rsrc->column_info('value')->{size} }, 100,
351 'column in database name with dash';
354 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
355 } 'got resultset for table in database name with dash';
358 ok $row = $rs->create({ value => 'foo' });
359 } 'executed SQL on table in database name with dash';
362 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
364 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
366 is_deeply $rel_info->{cond}, {
367 'foreign.four_id' => 'self.id'
368 }, 'relationship in database name with dash';
370 is $rel_info->{attrs}{accessor}, 'single',
371 'relationship in database name with dash';
373 is $rel_info->{attrs}{join_type}, 'LEFT',
374 'relationship in database name with dash';
378 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
379 } 'got source for table in database name with dash';
381 %uniqs = try { $rsrc->unique_constraints };
384 'got unique and primary constraint in database name with dash';
386 delete $uniqs{primary};
388 is_deeply ((values %uniqs)[0], ['four_id'],
389 'unique constraint is correct in database name with dash');
392 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
393 } 'got source for table in database name with dot';
395 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
396 'column in database name with dot introspected correctly';
398 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
399 'column in database name with dot introspected correctly';
401 is try { $rsrc->column_info('value')->{size} }, 100,
402 'column in database name with dot introspected correctly';
405 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
406 } 'got resultset for table in database name with dot';
409 ok $row = $rs->create({ value => 'foo' });
410 } 'executed SQL on table in database name with dot';
413 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
415 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
417 is_deeply $rel_info->{cond}, {
418 'foreign.six_id' => 'self.id'
419 }, 'relationship in database name with dot';
421 is $rel_info->{attrs}{accessor}, 'single',
422 'relationship in database name with dot';
424 is $rel_info->{attrs}{join_type}, 'LEFT',
425 'relationship in database name with dot';
429 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
430 } 'got source for table in database name with dot';
432 %uniqs = try { $rsrc->unique_constraints };
435 'got unique and primary constraint in database name with dot';
437 delete $uniqs{primary};
439 is_deeply ((values %uniqs)[0], ['six_id'],
440 'unique constraint is correct in database name with dot');
443 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
446 ok $test_schema->source('MysqlLoaderTest6')
447 ->has_relationship('mysql_loader_test4');
448 } 'cross-database relationship in multi-db_schema';
451 ok $test_schema->source('MysqlLoaderTest4')
452 ->has_relationship('mysql_loader_test6s');
453 } 'cross-database relationship in multi-db_schema';
456 ok $test_schema->source('MysqlLoaderTest8')
457 ->has_relationship('mysql_loader_test7');
458 } 'cross-database relationship in multi-db_schema';
461 ok $test_schema->source('MysqlLoaderTest7')
462 ->has_relationship('mysql_loader_test8s');
463 } 'cross-database relationship in multi-db_schema';
471 if( !$dsn || !$user ) {
472 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
475 diag $skip_rels_msg if not $test_innodb;
476 $tester->run_tests();
480 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
481 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
482 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
483 'dbicsl_test_ignored.mysql_loader_test9',
484 '`dbicsl-test`.mysql_loader_test8',
485 '`dbicsl.test`.mysql_loader_test7',
486 '`dbicsl.test`.mysql_loader_test6',
487 '`dbicsl.test`.mysql_loader_test5',
488 '`dbicsl-test`.mysql_loader_test5',
489 '`dbicsl-test`.mysql_loader_test4') {
491 $dbh->do("DROP TABLE $table");
494 diag "Error dropping table: $_";
498 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
500 $dbh->do("DROP DATABASE `$db`");
503 diag "Error dropping test database $db: $_";
507 rmtree EXTRA_DUMP_DIR;
510 # vim:et sts=4 sw=4 tw=0: