1 use DBIx::Class::Schema::Loader::Optional::Dependencies
2 -skip_all_without => 'test_rdbms_mysql';
9 use File::Path 'rmtree';
10 use DBIx::Class::Schema::Loader::Utils 'slurp_file';
11 use DBIx::Class::Schema::Loader 'make_schema_at';
15 use dbixcsl_common_tests;
16 use dbixcsl_test_dir '$tdir';
18 use constant EXTRA_DUMP_DIR => "$tdir/mysql_extra_dump";
20 my $dsn = $ENV{DBICTEST_MYSQL_DSN} || '';
21 my $user = $ENV{DBICTEST_MYSQL_USER} || '';
22 my $password = $ENV{DBICTEST_MYSQL_PASS} || '';
23 my $test_innodb = $ENV{DBICTEST_MYSQL_INNODB} || 0;
25 my $skip_rels_msg = 'You need to set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships.';
27 my $innodb = $test_innodb ? q{Engine=InnoDB} : '';
29 my ($schema, $databases_created); # for cleanup in END for extra tests
31 diag $skip_rels_msg if not $test_innodb;
33 dbixcsl_common_tests->new(
35 auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT',
39 password => $password,
40 connect_info_opts => { on_connect_call => 'set_strict_mode' },
41 loader_options => { preserve_case => 1 },
42 skip_rels => $test_innodb ? 0 : $skip_rels_msg,
45 no_implicit_rels => 1,
46 default_on_clause => 'RESTRICT',
48 # http://dev.mysql.com/doc/refman/5.5/en/data-type-overview.html
50 'bit' => { data_type => 'bit', size => 1 },
51 'bit(11)' => { data_type => 'bit', size => 11 },
53 'bool' => { data_type => 'tinyint' },
54 'boolean' => { data_type => 'tinyint' },
55 'tinyint' => { data_type => 'tinyint' },
57 => { data_type => 'tinyint', extra => { unsigned => 1 } },
58 'smallint' => { data_type => 'smallint' },
60 => { data_type => 'smallint', extra => { unsigned => 1 } },
61 'mediumint' => { data_type => 'mediumint' },
63 => { data_type => 'mediumint', extra => { unsigned => 1 } },
64 'int' => { data_type => 'integer' },
66 => { data_type => 'integer', extra => { unsigned => 1 } },
67 'integer' => { data_type => 'integer' },
69 => { data_type => 'integer', extra => { unsigned => 1 } },
71 => { data_type => 'integer' },
72 'bigint' => { data_type => 'bigint' },
74 => { data_type => 'bigint', extra => { unsigned => 1 } },
76 'serial' => { data_type => 'bigint', is_auto_increment => 1, extra => { unsigned => 1 } },
78 'float' => { data_type => 'float' },
80 => { data_type => 'float', extra => { unsigned => 1 } },
81 'double' => { data_type => 'double precision' },
83 => { data_type => 'double precision', extra => { unsigned => 1 } },
85 { data_type => 'double precision' },
86 'double precision unsigned'
87 => { data_type => 'double precision', extra => { unsigned => 1 } },
89 # we skip 'real' because its alias depends on the 'REAL AS FLOAT' setting
91 'float(2)' => { data_type => 'float' },
92 'float(24)' => { data_type => 'float' },
93 'float(25)' => { data_type => 'double precision' },
95 'float(3,3)' => { data_type => 'float', size => [3,3] },
96 'double(3,3)' => { data_type => 'double precision', size => [3,3] },
97 'double precision(3,3)'
98 => { data_type => 'double precision', size => [3,3] },
100 'decimal' => { data_type => 'decimal' },
102 => { data_type => 'decimal', extra => { unsigned => 1 } },
103 'dec' => { data_type => 'decimal' },
104 'numeric' => { data_type => 'decimal' },
105 'fixed' => { data_type => 'decimal' },
107 'decimal(3)' => { data_type => 'decimal', size => [3,0] },
109 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
110 'dec(3,3)' => { data_type => 'decimal', size => [3,3] },
111 'numeric(3,3)' => { data_type => 'decimal', size => [3,3] },
112 'fixed(3,3)' => { data_type => 'decimal', size => [3,3] },
114 # Date and Time Types
115 'date' => { data_type => 'date', datetime_undef_if_invalid => 1 },
116 'datetime' => { data_type => 'datetime', datetime_undef_if_invalid => 1 },
117 'timestamp default current_timestamp'
118 => { data_type => 'timestamp', default_value => \'current_timestamp', datetime_undef_if_invalid => 1 },
119 'time' => { data_type => 'time' },
120 'year' => { data_type => 'year' },
121 'year(4)' => { data_type => 'year' },
124 'char' => { data_type => 'char', size => 1 },
125 'char(11)' => { data_type => 'char', size => 11 },
126 'varchar(20)' => { data_type => 'varchar', size => 20 },
127 'binary' => { data_type => 'binary', size => 1 },
128 'binary(11)' => { data_type => 'binary', size => 11 },
129 'varbinary(20)'=> { data_type => 'varbinary', size => 20 },
131 'tinyblob' => { data_type => 'tinyblob' },
132 'tinytext' => { data_type => 'tinytext' },
133 'blob' => { data_type => 'blob' },
135 # text(M) types will map to the appropriate type, length is not stored
136 'text' => { data_type => 'text' },
138 'mediumblob' => { data_type => 'mediumblob' },
139 'mediumtext' => { data_type => 'mediumtext' },
140 'longblob' => { data_type => 'longblob' },
141 'longtext' => { data_type => 'longtext' },
144 "$_('','foo','bar','baz')"
145 => { data_type => $_, extra => { list => ['', qw/foo bar baz/] } },
146 "$_('foo \\'bar\\' baz', 'foo ''bar'' quux')"
147 => { data_type => $_, extra => { list => [q{foo 'bar' baz}, q{foo 'bar' quux}] } },
148 "$_('''', '''foo', 'bar''')"
149 => { data_type => $_, extra => { list => [qw(' 'foo bar')] } },
150 "$_('\\'', '\\'foo', 'bar\\'')",
151 => { data_type => $_, extra => { list => [qw(' 'foo bar')] } },
156 "enum('11,10 (<500)/0 DUN','4,90 (<120)/0 EUR') NOT NULL default '11,10 (<500)/0 DUN'"
157 => { data_type => 'enum', extra => { list => ['11,10 (<500)/0 DUN', '4,90 (<120)/0 EUR'] }, default_value => '11,10 (<500)/0 DUN' },
158 "set('11_10 (<500)/0 DUN','4_90 (<120)/0 EUR') NOT NULL default '11_10 (<500)/0 DUN'"
159 => { data_type => 'set', extra => { list => ['11_10 (<500)/0 DUN', '4_90 (<120)/0 EUR'] }, default_value => '11_10 (<500)/0 DUN' },
160 "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'"
161 => { 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' },
166 CREATE TABLE `mysql_loader-test1` (
167 id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'The\15\12Column',
169 ) $innodb COMMENT 'The\15\12Table'
172 CREATE VIEW mysql_loader_test2 AS SELECT * FROM `mysql_loader-test1`
176 CREATE TABLE `mysql_loader_test3` (
177 `ISO3_code` char(3) NOT NULL default '',
178 `lang_pref` enum('de','en','fr','nl','dk','es','se') NOT NULL,
179 `vat` decimal(4,2) default '16.00',
180 `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',
181 `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',
182 `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',
183 `pmethod` varchar(255) NOT NULL default 'VISA,MASTER',
184 `delivery_time` varchar(5) default NULL,
185 `express_delivery_time` varchar(5) default NULL,
186 `eu` int(1) default '0',
187 `cod_costs` varchar(12) default NULL,
188 PRIMARY KEY (`ISO3_code`)
191 # 4 through 10 are used for the multi-schema tests
193 create table mysql_loader_test11 (
194 id int auto_increment primary key
198 create table mysql_loader_test12 (
199 id int auto_increment primary key,
201 foreign key (eleven_id) references mysql_loader_test11(id)
202 on delete restrict on update set null
206 pre_drop_ddl => [ 'DROP VIEW mysql_loader_test2', ],
207 drop => [ 'mysql_loader-test1', 'mysql_loader_test3', 'mysql_loader_test11', 'mysql_loader_test12' ],
210 my ($monikers, $classes);
211 ($schema, $monikers, $classes) = @_;
213 is $monikers->{'mysql_loader-test1'}, 'MysqlLoaderTest1',
214 'table with dash correctly monikerized';
216 my $rsrc = $schema->source('MysqlLoaderTest2');
218 is $rsrc->column_info('value')->{data_type}, 'varchar',
219 'view introspected successfully';
221 # test that views are marked as such
222 isa_ok $schema->resultset($monikers->{mysql_loader_test2})->result_source, 'DBIx::Class::ResultSource::View',
223 'views have table_class set correctly';
225 $rsrc = $schema->source('MysqlLoaderTest3');
227 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'],
228 'hairy enum introspected correctly';
230 my $class = $classes->{'mysql_loader-test1'};
231 my $filename = $schema->loader->get_dump_filename($class);
233 my $code = slurp_file $filename;
235 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
238 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
239 'column comment and attrs';
241 # test on delete/update fk clause introspection
242 ok ((my $rel_info = $schema->source('MysqlLoaderTest12')->relationship_info('eleven')),
245 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
246 'ON DELETE clause introspected correctly';
248 is $rel_info->{attrs}{on_update}, 'SET NULL',
249 'ON UPDATE clause introspected correctly';
251 # multischema tests follow
253 my $dbh = $schema->storage->dbh;
256 $dbh->do('CREATE DATABASE `dbicsl-test`');
259 note "CREATE DATABASE returned error: '$_'";
260 skip "no CREATE DATABASE privileges", 30 * 2;
264 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
265 id INT AUTO_INCREMENT PRIMARY KEY,
270 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
271 id INT AUTO_INCREMENT PRIMARY KEY,
274 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
275 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
279 $dbh->do('CREATE DATABASE `dbicsl.test`');
281 # Test that keys are correctly cached by naming the primary and
282 # unique keys in this table with the same name as a table in
283 # the `dbicsl-test` schema differently.
285 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
286 pk INT AUTO_INCREMENT PRIMARY KEY,
289 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
290 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
295 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
296 id INT AUTO_INCREMENT PRIMARY KEY,
298 mysql_loader_test4_id INTEGER,
299 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
303 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
304 id INT AUTO_INCREMENT PRIMARY KEY,
306 six_id INTEGER UNIQUE,
307 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
311 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
312 id INT AUTO_INCREMENT PRIMARY KEY,
314 mysql_loader_test7_id INTEGER,
315 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
318 # Test dumping a rel to a table that's not part of the dump.
319 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
321 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
322 id INT AUTO_INCREMENT PRIMARY KEY,
327 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
328 id INT AUTO_INCREMENT PRIMARY KEY,
330 mysql_loader_test9_id INTEGER,
331 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
335 $databases_created = 1;
337 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
338 if ($db_schema eq '%') {
340 $dbh->selectall_arrayref('SHOW DATABASES');
343 skip 'no SHOW DATABASES privileges', 30;
348 rmtree EXTRA_DUMP_DIR;
351 local $SIG{__WARN__} = sub {
352 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
359 db_schema => $db_schema,
360 dump_directory => EXTRA_DUMP_DIR,
363 [ $dsn, $user, $password ],
366 diag join "\n", @warns if @warns;
369 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
371 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
374 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
375 } 'connected test schema';
378 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
379 } 'got source for table in database name with dash';
381 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
382 'column in database name with dash';
384 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
385 'column in database name with dash';
387 is try { $rsrc->column_info('value')->{size} }, 100,
388 'column in database name with dash';
391 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
392 } 'got resultset for table in database name with dash';
395 ok $row = $rs->create({ value => 'foo' });
396 } 'executed SQL on table in database name with dash';
399 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
401 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
403 is_deeply $rel_info->{cond}, {
404 'foreign.four_id' => 'self.id'
405 }, 'relationship in database name with dash';
407 is $rel_info->{attrs}{accessor}, 'single',
408 'relationship in database name with dash';
410 is $rel_info->{attrs}{join_type}, 'LEFT',
411 'relationship in database name with dash';
415 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
416 } 'got source for table in database name with dash';
418 %uniqs = try { $rsrc->unique_constraints };
421 'got unique and primary constraint in database name with dash';
423 delete $uniqs{primary};
425 is_deeply ((values %uniqs)[0], ['four_id'],
426 'unique constraint is correct in database name with dash');
429 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
430 } 'got source for table in database name with dot';
432 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
433 'column in database name with dot introspected correctly';
435 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
436 'column in database name with dot introspected correctly';
438 is try { $rsrc->column_info('value')->{size} }, 100,
439 'column in database name with dot introspected correctly';
442 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
443 } 'got resultset for table in database name with dot';
446 ok $row = $rs->create({ value => 'foo' });
447 } 'executed SQL on table in database name with dot';
450 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
452 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
454 is_deeply $rel_info->{cond}, {
455 'foreign.six_id' => 'self.id'
456 }, 'relationship in database name with dot';
458 is $rel_info->{attrs}{accessor}, 'single',
459 'relationship in database name with dot';
461 is $rel_info->{attrs}{join_type}, 'LEFT',
462 'relationship in database name with dot';
466 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
467 } 'got source for table in database name with dot';
469 %uniqs = try { $rsrc->unique_constraints };
472 'got unique and primary constraint in database name with dot';
474 delete $uniqs{primary};
476 is_deeply ((values %uniqs)[0], ['six_id'],
477 'unique constraint is correct in database name with dot');
480 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
483 ok $test_schema->source('MysqlLoaderTest6')
484 ->has_relationship('mysql_loader_test4');
485 } 'cross-database relationship in multi-db_schema';
488 ok $test_schema->source('MysqlLoaderTest4')
489 ->has_relationship('mysql_loader_test6s');
490 } 'cross-database relationship in multi-db_schema';
493 ok $test_schema->source('MysqlLoaderTest8')
494 ->has_relationship('mysql_loader_test7');
495 } 'cross-database relationship in multi-db_schema';
498 ok $test_schema->source('MysqlLoaderTest7')
499 ->has_relationship('mysql_loader_test8s');
500 } 'cross-database relationship in multi-db_schema';
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: