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 # test that views are marked as such
218 isa_ok $schema->resultset($monikers->{mysql_loader_test2})->result_source, 'DBIx::Class::ResultSource::View',
219 'views have table_class set correctly';
221 $rsrc = $schema->source('MysqlLoaderTest3');
223 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'],
224 'hairy enum introspected correctly';
226 my $class = $classes->{'mysql_loader-test1'};
227 my $filename = $schema->loader->get_dump_filename($class);
229 my $code = slurp_file $filename;
231 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
234 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
235 'column comment and attrs';
237 # test on delete/update fk clause introspection
238 ok ((my $rel_info = $schema->source('MysqlLoaderTest12')->relationship_info('eleven')),
241 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
242 'ON DELETE clause introspected correctly';
244 is $rel_info->{attrs}{on_update}, 'SET NULL',
245 'ON UPDATE clause introspected correctly';
247 # multischema tests follow
249 my $dbh = $schema->storage->dbh;
252 $dbh->do('CREATE DATABASE `dbicsl-test`');
255 note "CREATE DATABASE returned error: '$_'";
256 skip "no CREATE DATABASE privileges", 30 * 2;
260 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
261 id INT AUTO_INCREMENT PRIMARY KEY,
266 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
267 id INT AUTO_INCREMENT PRIMARY KEY,
270 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
271 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
275 $dbh->do('CREATE DATABASE `dbicsl.test`');
277 # Test that keys are correctly cached by naming the primary and
278 # unique keys in this table with the same name as a table in
279 # the `dbicsl-test` schema differently.
281 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
282 pk INT AUTO_INCREMENT PRIMARY KEY,
285 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
286 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
291 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
292 id INT AUTO_INCREMENT PRIMARY KEY,
294 mysql_loader_test4_id INTEGER,
295 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
299 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
300 id INT AUTO_INCREMENT PRIMARY KEY,
302 six_id INTEGER UNIQUE,
303 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
307 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
308 id INT AUTO_INCREMENT PRIMARY KEY,
310 mysql_loader_test7_id INTEGER,
311 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
314 # Test dumping a rel to a table that's not part of the dump.
315 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
317 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
318 id INT AUTO_INCREMENT PRIMARY KEY,
323 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
324 id INT AUTO_INCREMENT PRIMARY KEY,
326 mysql_loader_test9_id INTEGER,
327 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
331 $databases_created = 1;
333 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
334 if ($db_schema eq '%') {
336 $dbh->selectall_arrayref('SHOW DATABASES');
339 skip 'no SHOW DATABASES privileges', 28;
344 rmtree EXTRA_DUMP_DIR;
347 local $SIG{__WARN__} = sub {
348 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
355 db_schema => $db_schema,
356 dump_directory => EXTRA_DUMP_DIR,
359 [ $dsn, $user, $password ],
362 diag join "\n", @warns if @warns;
365 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
367 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
370 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
371 } 'connected test schema';
374 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
375 } 'got source for table in database name with dash';
377 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
378 'column in database name with dash';
380 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
381 'column in database name with dash';
383 is try { $rsrc->column_info('value')->{size} }, 100,
384 'column in database name with dash';
387 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
388 } 'got resultset for table in database name with dash';
391 ok $row = $rs->create({ value => 'foo' });
392 } 'executed SQL on table in database name with dash';
395 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
397 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
399 is_deeply $rel_info->{cond}, {
400 'foreign.four_id' => 'self.id'
401 }, 'relationship in database name with dash';
403 is $rel_info->{attrs}{accessor}, 'single',
404 'relationship in database name with dash';
406 is $rel_info->{attrs}{join_type}, 'LEFT',
407 'relationship in database name with dash';
411 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
412 } 'got source for table in database name with dash';
414 %uniqs = try { $rsrc->unique_constraints };
417 'got unique and primary constraint in database name with dash';
419 delete $uniqs{primary};
421 is_deeply ((values %uniqs)[0], ['four_id'],
422 'unique constraint is correct in database name with dash');
425 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
426 } 'got source for table in database name with dot';
428 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
429 'column in database name with dot introspected correctly';
431 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
432 'column in database name with dot introspected correctly';
434 is try { $rsrc->column_info('value')->{size} }, 100,
435 'column in database name with dot introspected correctly';
438 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
439 } 'got resultset for table in database name with dot';
442 ok $row = $rs->create({ value => 'foo' });
443 } 'executed SQL on table in database name with dot';
446 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
448 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
450 is_deeply $rel_info->{cond}, {
451 'foreign.six_id' => 'self.id'
452 }, 'relationship in database name with dot';
454 is $rel_info->{attrs}{accessor}, 'single',
455 'relationship in database name with dot';
457 is $rel_info->{attrs}{join_type}, 'LEFT',
458 'relationship in database name with dot';
462 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
463 } 'got source for table in database name with dot';
465 %uniqs = try { $rsrc->unique_constraints };
468 'got unique and primary constraint in database name with dot';
470 delete $uniqs{primary};
472 is_deeply ((values %uniqs)[0], ['six_id'],
473 'unique constraint is correct in database name with dot');
476 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
479 ok $test_schema->source('MysqlLoaderTest6')
480 ->has_relationship('mysql_loader_test4');
481 } 'cross-database relationship in multi-db_schema';
484 ok $test_schema->source('MysqlLoaderTest4')
485 ->has_relationship('mysql_loader_test6s');
486 } 'cross-database relationship in multi-db_schema';
489 ok $test_schema->source('MysqlLoaderTest8')
490 ->has_relationship('mysql_loader_test7');
491 } 'cross-database relationship in multi-db_schema';
494 ok $test_schema->source('MysqlLoaderTest7')
495 ->has_relationship('mysql_loader_test8s');
496 } 'cross-database relationship in multi-db_schema';
504 if( !$dsn || !$user ) {
505 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
508 diag $skip_rels_msg if not $test_innodb;
509 $tester->run_tests();
513 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
514 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
515 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
516 'dbicsl_test_ignored.mysql_loader_test9',
517 '`dbicsl-test`.mysql_loader_test8',
518 '`dbicsl.test`.mysql_loader_test7',
519 '`dbicsl.test`.mysql_loader_test6',
520 '`dbicsl.test`.mysql_loader_test5',
521 '`dbicsl-test`.mysql_loader_test5',
522 '`dbicsl-test`.mysql_loader_test4') {
524 $dbh->do("DROP TABLE $table");
527 diag "Error dropping table: $_";
531 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
533 $dbh->do("DROP DATABASE `$db`");
536 diag "Error dropping test database $db: $_";
540 rmtree EXTRA_DUMP_DIR;
543 # vim:et sts=4 sw=4 tw=0: