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' ],
208 count => 10 + 30 * 2, # regular + multi-schema * 2
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 my $view_source = $schema->resultset($monikers->{mysql_loader_test2})->result_source;
223 isa_ok $view_source, 'DBIx::Class::ResultSource::View',
224 'view result source';
226 like $view_source->view_definition,
227 qr/\A \s* select \b .* \b from \s+ `.*?` \. `mysql_loader-test1` \s* \z/imsx,
230 $rsrc = $schema->source('MysqlLoaderTest3');
232 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'],
233 'hairy enum introspected correctly';
235 my $class = $classes->{'mysql_loader-test1'};
236 my $filename = $schema->loader->get_dump_filename($class);
238 my $code = slurp_file $filename;
240 like $code, qr/^=head1 NAME\n\n^$class - The\nTable\n\n^=cut\n/m,
243 like $code, qr/^=head2 id\n\n(.+:.+\n)+\nThe\nColumn\n\n/m,
244 'column comment and attrs';
246 # test on delete/update fk clause introspection
247 ok ((my $rel_info = $schema->source('MysqlLoaderTest12')->relationship_info('eleven')),
250 is $rel_info->{attrs}{on_delete}, 'RESTRICT',
251 'ON DELETE clause introspected correctly';
253 is $rel_info->{attrs}{on_update}, 'SET NULL',
254 'ON UPDATE clause introspected correctly';
256 # multischema tests follow
258 my $dbh = $schema->storage->dbh;
261 $dbh->do('CREATE DATABASE `dbicsl-test`');
264 note "CREATE DATABASE returned error: '$_'";
265 skip "no CREATE DATABASE privileges", 30 * 2;
269 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
270 id INT AUTO_INCREMENT PRIMARY KEY,
275 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
276 id INT AUTO_INCREMENT PRIMARY KEY,
279 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
280 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
284 $dbh->do('CREATE DATABASE `dbicsl.test`');
286 # Test that keys are correctly cached by naming the primary and
287 # unique keys in this table with the same name as a table in
288 # the `dbicsl-test` schema differently.
290 CREATE TABLE `dbicsl.test`.mysql_loader_test5 (
291 pk INT AUTO_INCREMENT PRIMARY KEY,
294 CONSTRAINT loader_test5_uniq UNIQUE (four_id),
295 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
300 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
301 id INT AUTO_INCREMENT PRIMARY KEY,
303 mysql_loader_test4_id INTEGER,
304 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
308 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
309 id INT AUTO_INCREMENT PRIMARY KEY,
311 six_id INTEGER UNIQUE,
312 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
316 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
317 id INT AUTO_INCREMENT PRIMARY KEY,
319 mysql_loader_test7_id INTEGER,
320 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
323 # Test dumping a rel to a table that's not part of the dump.
324 $dbh->do('CREATE DATABASE `dbicsl_test_ignored`');
326 CREATE TABLE `dbicsl_test_ignored`.mysql_loader_test9 (
327 id INT AUTO_INCREMENT PRIMARY KEY,
332 CREATE TABLE `dbicsl-test`.mysql_loader_test10 (
333 id INT AUTO_INCREMENT PRIMARY KEY,
335 mysql_loader_test9_id INTEGER,
336 FOREIGN KEY (mysql_loader_test9_id) REFERENCES `dbicsl_test_ignored`.mysql_loader_test9 (id)
340 $databases_created = 1;
342 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
343 if ($db_schema eq '%') {
345 $dbh->selectall_arrayref('SHOW DATABASES');
348 skip 'no SHOW DATABASES privileges', 30;
353 rmtree EXTRA_DUMP_DIR;
356 local $SIG{__WARN__} = sub {
357 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
364 db_schema => $db_schema,
365 dump_directory => EXTRA_DUMP_DIR,
368 [ $dsn, $user, $password ],
371 diag join "\n", @warns if @warns;
374 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
376 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
379 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
380 } 'connected test schema';
383 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
384 } 'got source for table in database name with dash';
386 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
387 'column in database name with dash';
389 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
390 'column in database name with dash';
392 is try { $rsrc->column_info('value')->{size} }, 100,
393 'column in database name with dash';
396 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
397 } 'got resultset for table in database name with dash';
400 ok $row = $rs->create({ value => 'foo' });
401 } 'executed SQL on table in database name with dash';
404 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
406 $rel_info = try { $rsrc->relationship_info('dbicsl_dash_test_mysql_loader_test5') };
408 is_deeply $rel_info->{cond}, {
409 'foreign.four_id' => 'self.id'
410 }, 'relationship in database name with dash';
412 is $rel_info->{attrs}{accessor}, 'single',
413 'relationship in database name with dash';
415 is $rel_info->{attrs}{join_type}, 'LEFT',
416 'relationship in database name with dash';
420 ok $rsrc = $test_schema->source('DbicslDashTestMysqlLoaderTest5');
421 } 'got source for table in database name with dash';
423 %uniqs = try { $rsrc->unique_constraints };
426 'got unique and primary constraint in database name with dash';
428 delete $uniqs{primary};
430 is_deeply ((values %uniqs)[0], ['four_id'],
431 'unique constraint is correct in database name with dash');
434 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
435 } 'got source for table in database name with dot';
437 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
438 'column in database name with dot introspected correctly';
440 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
441 'column in database name with dot introspected correctly';
443 is try { $rsrc->column_info('value')->{size} }, 100,
444 'column in database name with dot introspected correctly';
447 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
448 } 'got resultset for table in database name with dot';
451 ok $row = $rs->create({ value => 'foo' });
452 } 'executed SQL on table in database name with dot';
455 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
457 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
459 is_deeply $rel_info->{cond}, {
460 'foreign.six_id' => 'self.id'
461 }, 'relationship in database name with dot';
463 is $rel_info->{attrs}{accessor}, 'single',
464 'relationship in database name with dot';
466 is $rel_info->{attrs}{join_type}, 'LEFT',
467 'relationship in database name with dot';
471 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
472 } 'got source for table in database name with dot';
474 %uniqs = try { $rsrc->unique_constraints };
477 'got unique and primary constraint in database name with dot';
479 delete $uniqs{primary};
481 is_deeply ((values %uniqs)[0], ['six_id'],
482 'unique constraint is correct in database name with dot');
485 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
488 ok $test_schema->source('MysqlLoaderTest6')
489 ->has_relationship('mysql_loader_test4');
490 } 'cross-database relationship in multi-db_schema';
493 ok $test_schema->source('MysqlLoaderTest4')
494 ->has_relationship('mysql_loader_test6s');
495 } 'cross-database relationship in multi-db_schema';
498 ok $test_schema->source('MysqlLoaderTest8')
499 ->has_relationship('mysql_loader_test7');
500 } 'cross-database relationship in multi-db_schema';
503 ok $test_schema->source('MysqlLoaderTest7')
504 ->has_relationship('mysql_loader_test8s');
505 } 'cross-database relationship in multi-db_schema';
514 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
515 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
516 foreach my $table ('`dbicsl-test`.mysql_loader_test10',
517 'dbicsl_test_ignored.mysql_loader_test9',
518 '`dbicsl-test`.mysql_loader_test8',
519 '`dbicsl.test`.mysql_loader_test7',
520 '`dbicsl.test`.mysql_loader_test6',
521 '`dbicsl.test`.mysql_loader_test5',
522 '`dbicsl-test`.mysql_loader_test5',
523 '`dbicsl-test`.mysql_loader_test4') {
525 $dbh->do("DROP TABLE $table");
528 diag "Error dropping table: $_";
532 foreach my $db (qw/dbicsl-test dbicsl.test dbicsl_test_ignored/) {
534 $dbh->do("DROP DATABASE `$db`");
537 diag "Error dropping test database $db: $_";
541 rmtree EXTRA_DUMP_DIR;
544 # vim:et sts=4 sw=4 tw=0: