6 use File::Path 'rmtree';
7 use DBIx::Class::Optional::Dependencies;
8 use DBIx::Class::Schema::Loader::Utils 'slurp_file';
9 use DBIx::Class::Schema::Loader 'make_schema_at';
13 use dbixcsl_common_tests;
14 use dbixcsl_test_dir '$tdir';
16 use constant EXTRA_DUMP_DIR => "$tdir/mysql_extra_dump";
18 my $dsn = $ENV{DBICTEST_MYSQL_DSN} || '';
19 my $user = $ENV{DBICTEST_MYSQL_USER} || '';
20 my $password = $ENV{DBICTEST_MYSQL_PASS} || '';
21 my $test_innodb = $ENV{DBICTEST_MYSQL_INNODB} || 0;
23 my $skip_rels_msg = 'You need to set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships.';
25 my $innodb = $test_innodb ? q{Engine=InnoDB} : '';
27 my ($schema, $databases_created); # for cleanup in END for extra tests
29 my $tester = dbixcsl_common_tests->new(
31 auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT',
35 password => $password,
36 connect_info_opts => { on_connect_call => 'set_strict_mode' },
37 loader_options => { preserve_case => 1 },
38 skip_rels => $test_innodb ? 0 : $skip_rels_msg,
41 no_implicit_rels => 1,
42 default_on_clause => 'RESTRICT',
44 # http://dev.mysql.com/doc/refman/5.5/en/data-type-overview.html
46 'bit' => { data_type => 'bit', size => 1 },
47 'bit(11)' => { data_type => 'bit', size => 11 },
49 'bool' => { data_type => 'tinyint' },
50 'boolean' => { data_type => 'tinyint' },
51 'tinyint' => { data_type => 'tinyint' },
53 => { data_type => 'tinyint', extra => { unsigned => 1 } },
54 'smallint' => { data_type => 'smallint' },
56 => { data_type => 'smallint', extra => { unsigned => 1 } },
57 'mediumint' => { data_type => 'mediumint' },
59 => { data_type => 'mediumint', extra => { unsigned => 1 } },
60 'int' => { data_type => 'integer' },
62 => { data_type => 'integer', extra => { unsigned => 1 } },
63 'integer' => { data_type => 'integer' },
65 => { data_type => 'integer', extra => { unsigned => 1 } },
67 => { data_type => 'integer' },
68 'bigint' => { data_type => 'bigint' },
70 => { data_type => 'bigint', extra => { unsigned => 1 } },
72 'serial' => { data_type => 'bigint', is_auto_increment => 1, extra => { unsigned => 1 } },
74 'float' => { data_type => 'float' },
76 => { data_type => 'float', extra => { unsigned => 1 } },
77 'double' => { data_type => 'double precision' },
79 => { data_type => 'double precision', extra => { unsigned => 1 } },
81 { data_type => 'double precision' },
82 'double precision unsigned'
83 => { data_type => 'double precision', extra => { unsigned => 1 } },
85 # we skip 'real' because its alias depends on the 'REAL AS FLOAT' setting
87 'float(2)' => { data_type => 'float' },
88 'float(24)' => { data_type => 'float' },
89 'float(25)' => { data_type => 'double precision' },
91 'float(3,3)' => { data_type => 'float', size => [3,3] },
92 'double(3,3)' => { data_type => 'double precision', size => [3,3] },
93 'double precision(3,3)'
94 => { data_type => 'double precision', size => [3,3] },
96 'decimal' => { data_type => 'decimal' },
98 => { data_type => 'decimal', extra => { unsigned => 1 } },
99 'dec' => { data_type => 'decimal' },
100 'numeric' => { data_type => 'decimal' },
101 'fixed' => { data_type => 'decimal' },
103 'decimal(3)' => { data_type => 'decimal', size => [3,0] },
105 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
106 'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
107 'numeric(3,3)' => { data_type => 'decimal', size => [3,3] },
108 'fixed(3,3)' => { data_type => 'decimal', size => [3,3] },
110 # Date and Time Types
111 'date' => { data_type => 'date', datetime_undef_if_invalid => 1 },
112 'datetime' => { data_type => 'datetime', datetime_undef_if_invalid => 1 },
113 'timestamp default current_timestamp'
114 => { data_type => 'timestamp', default_value => \'current_timestamp', datetime_undef_if_invalid => 1 },
115 'time' => { data_type => 'time' },
116 'year' => { data_type => 'year' },
117 'year(4)' => { data_type => 'year' },
118 'year(2)' => { data_type => 'year', size => 2 },
121 'char' => { data_type => 'char', size => 1 },
122 'char(11)' => { data_type => 'char', size => 11 },
123 'varchar(20)' => { data_type => 'varchar', size => 20 },
124 'binary' => { data_type => 'binary', size => 1 },
125 'binary(11)' => { data_type => 'binary', size => 11 },
126 'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
128 'tinyblob' => { data_type => 'tinyblob' },
129 'tinytext' => { data_type => 'tinytext' },
130 'blob' => { data_type => 'blob' },
132 # text(M) types will map to the appropriate type, length is not stored
133 'text' => { data_type => 'text' },
135 'mediumblob' => { data_type => 'mediumblob' },
136 'mediumtext' => { data_type => 'mediumtext' },
137 'longblob' => { data_type => 'longblob' },
138 'longtext' => { data_type => 'longtext' },
141 "$_('','foo','bar','baz')"
142 => { data_type => $_, extra => { list => ['', qw/foo bar baz/] } },
143 "$_('foo \\'bar\\' baz', 'foo ''bar'' quux')"
144 => { data_type => $_, extra => { list => [q{foo 'bar' baz}, q{foo 'bar' quux}] } },
145 "$_('''', '''foo', 'bar''')"
146 => { data_type => $_, extra => { list => [qw(' 'foo bar')] } },
147 "$_('\\'', '\\'foo', 'bar\\'')",
148 => { data_type => $_, extra => { list => [qw(' 'foo bar')] } },
153 "enum('11,10 (<500)/0 DUN','4,90 (<120)/0 EUR') NOT NULL default '11,10 (<500)/0 DUN'"
154 => { data_type => 'enum', extra => { list => ['11,10 (<500)/0 DUN', '4,90 (<120)/0 EUR'] }, default_value => '11,10 (<500)/0 DUN' },
155 "set('11_10 (<500)/0 DUN','4_90 (<120)/0 EUR') NOT NULL default '11_10 (<500)/0 DUN'"
156 => { data_type => 'set', extra => { list => ['11_10 (<500)/0 DUN', '4_90 (<120)/0 EUR'] }, default_value => '11_10 (<500)/0 DUN' },
157 "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'"
158 => { 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' },
163 CREATE TABLE `mysql_loader-test1` (
164 id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'The\15\12Column',
166 ) $innodb COMMENT 'The\15\12Table'
169 CREATE VIEW mysql_loader_test2 AS SELECT * FROM `mysql_loader-test1`
173 CREATE TABLE `mysql_loader_test3` (
174 `ISO3_code` char(3) NOT NULL default '',
175 `lang_pref` enum('de','en','fr','nl','dk','es','se') NOT NULL,
176 `vat` decimal(4,2) default '16.00',
177 `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',
178 `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',
179 `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',
180 `pmethod` varchar(255) NOT NULL default 'VISA,MASTER',
181 `delivery_time` varchar(5) default NULL,
182 `express_delivery_time` varchar(5) default NULL,
183 `eu` int(1) default '0',
184 `cod_costs` varchar(12) default NULL,
185 PRIMARY KEY (`ISO3_code`)
188 # 4 through 10 are used for the multi-schema tests
190 create table mysql_loader_test11 (
191 id int auto_increment primary key
195 create table mysql_loader_test12 (
196 id int auto_increment primary key,
198 foreign key (eleven_id) references mysql_loader_test11(id)
199 on delete restrict on update set null
203 pre_drop_ddl => [ 'DROP VIEW mysql_loader_test2', ],
204 drop => [ 'mysql_loader-test1', 'mysql_loader_test3', 'mysql_loader_test11', 'mysql_loader_test12' ],
207 my ($monikers, $classes);
208 ($schema, $monikers, $classes) = @_;
210 is $monikers->{'mysql_loader-test1'}, 'MysqlLoaderTest1',
211 'table with dash correctly monikerized';
213 my $rsrc = $schema->source('MysqlLoaderTest2');
215 is $rsrc->column_info('value')->{data_type}, 'varchar',
216 'view introspected successfully';
218 # test that views are marked as such
219 isa_ok $schema->resultset($monikers->{mysql_loader_test2})->result_source, 'DBIx::Class::ResultSource::View',
220 'views have table_class set correctly';
222 $rsrc = $schema->source('MysqlLoaderTest3');
224 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'],
225 'hairy enum introspected correctly';
227 my $class = $classes->{'mysql_loader-test1'};
228 my $filename = $schema->loader->get_dump_filename($class);
230 my $code = slurp_file $filename;
232 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
235 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
236 'column comment and attrs';
238 # test on delete/update fk clause introspection
239 ok ((my $rel_info = $schema->source('MysqlLoaderTest12')->relationship_info('eleven')),
242 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
243 'ON DELETE clause introspected correctly';
245 is $rel_info->{attrs}{on_update}, 'SET NULL',
246 'ON UPDATE clause introspected correctly';
248 # multischema tests follow
250 my $dbh = $schema->storage->dbh;
253 $dbh->do('CREATE DATABASE `dbicsl-test`');
256 note "CREATE DATABASE returned error: '$_'";
257 skip "no CREATE DATABASE privileges", 30 * 2;
261 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
262 id INT AUTO_INCREMENT PRIMARY KEY,
267 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
268 id INT AUTO_INCREMENT PRIMARY KEY,
271 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
272 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
276 $dbh->do('CREATE DATABASE `dbicsl.test`');
278 # Test that keys are correctly cached by naming the primary and
279 # unique keys in this table with the same name as a table in
280 # the `dbicsl-test` schema differently.
282 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
283 pk INT AUTO_INCREMENT PRIMARY KEY,
286 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
287 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
292 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
293 id INT AUTO_INCREMENT PRIMARY KEY,
295 mysql_loader_test4_id INTEGER,
296 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
300 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
301 id INT AUTO_INCREMENT PRIMARY KEY,
303 six_id INTEGER UNIQUE,
304 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
308 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
309 id INT AUTO_INCREMENT PRIMARY KEY,
311 mysql_loader_test7_id INTEGER,
312 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
315 # Test dumping a rel to a table that's not part of the dump.
316 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
318 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
319 id INT AUTO_INCREMENT PRIMARY KEY,
324 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
325 id INT AUTO_INCREMENT PRIMARY KEY,
327 mysql_loader_test9_id INTEGER,
328 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
332 $databases_created = 1;
334 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
335 if ($db_schema eq '%') {
337 $dbh->selectall_arrayref('SHOW DATABASES');
340 skip 'no SHOW DATABASES privileges', 30;
345 rmtree EXTRA_DUMP_DIR;
348 local $SIG{__WARN__} = sub {
349 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
356 db_schema => $db_schema,
357 dump_directory => EXTRA_DUMP_DIR,
360 [ $dsn, $user, $password ],
363 diag join "\n", @warns if @warns;
366 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
368 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
371 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
372 } 'connected test schema';
375 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
376 } 'got source for table in database name with dash';
378 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
379 'column in database name with dash';
381 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
382 'column in database name with dash';
384 is try { $rsrc->column_info('value')->{size} }, 100,
385 'column in database name with dash';
388 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
389 } 'got resultset for table in database name with dash';
392 ok $row = $rs->create({ value => 'foo' });
393 } 'executed SQL on table in database name with dash';
396 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
398 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
400 is_deeply $rel_info->{cond}, {
401 'foreign.four_id' => 'self.id'
402 }, 'relationship in database name with dash';
404 is $rel_info->{attrs}{accessor}, 'single',
405 'relationship in database name with dash';
407 is $rel_info->{attrs}{join_type}, 'LEFT',
408 'relationship in database name with dash';
412 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
413 } 'got source for table in database name with dash';
415 %uniqs = try { $rsrc->unique_constraints };
418 'got unique and primary constraint in database name with dash';
420 delete $uniqs{primary};
422 is_deeply ((values %uniqs)[0], ['four_id'],
423 'unique constraint is correct in database name with dash');
426 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
427 } 'got source for table in database name with dot';
429 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
430 'column in database name with dot introspected correctly';
432 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
433 'column in database name with dot introspected correctly';
435 is try { $rsrc->column_info('value')->{size} }, 100,
436 'column in database name with dot introspected correctly';
439 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
440 } 'got resultset for table in database name with dot';
443 ok $row = $rs->create({ value => 'foo' });
444 } 'executed SQL on table in database name with dot';
447 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
449 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
451 is_deeply $rel_info->{cond}, {
452 'foreign.six_id' => 'self.id'
453 }, 'relationship in database name with dot';
455 is $rel_info->{attrs}{accessor}, 'single',
456 'relationship in database name with dot';
458 is $rel_info->{attrs}{join_type}, 'LEFT',
459 'relationship in database name with dot';
463 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
464 } 'got source for table in database name with dot';
466 %uniqs = try { $rsrc->unique_constraints };
469 'got unique and primary constraint in database name with dot';
471 delete $uniqs{primary};
473 is_deeply ((values %uniqs)[0], ['six_id'],
474 'unique constraint is correct in database name with dot');
477 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
480 ok $test_schema->source('MysqlLoaderTest6')
481 ->has_relationship('mysql_loader_test4');
482 } 'cross-database relationship in multi-db_schema';
485 ok $test_schema->source('MysqlLoaderTest4')
486 ->has_relationship('mysql_loader_test6s');
487 } 'cross-database relationship in multi-db_schema';
490 ok $test_schema->source('MysqlLoaderTest8')
491 ->has_relationship('mysql_loader_test7');
492 } 'cross-database relationship in multi-db_schema';
495 ok $test_schema->source('MysqlLoaderTest7')
496 ->has_relationship('mysql_loader_test8s');
497 } 'cross-database relationship in multi-db_schema';
505 if( !$dsn || !$user ) {
506 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
508 elsif (!DBIx::Class::Optional::Dependencies->req_ok_for ('rdbms_mysql')) {
509 $tester->skip_tests('You need to install ' . DBIx::Class::Optional::Dependencies->req_missing_for ('rdbms_mysql'));
512 diag $skip_rels_msg if not $test_innodb;
513 $tester->run_tests();
517 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
518 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
519 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
520 'dbicsl_test_ignored.mysql_loader_test9',
521 '`dbicsl-test`.mysql_loader_test8',
522 '`dbicsl.test`.mysql_loader_test7',
523 '`dbicsl.test`.mysql_loader_test6',
524 '`dbicsl.test`.mysql_loader_test5',
525 '`dbicsl-test`.mysql_loader_test5',
526 '`dbicsl-test`.mysql_loader_test4') {
528 $dbh->do("DROP TABLE $table");
531 diag "Error dropping table: $_";
535 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
537 $dbh->do("DROP DATABASE `$db`");
540 diag "Error dropping test database $db: $_";
544 rmtree EXTRA_DUMP_DIR;
547 # vim:et sts=4 sw=4 tw=0: