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 skip "no CREATE DATABASE privileges", 28 * 2;
226 CREATE TABLE `dbicsl-test`.mysql_loader_test4 (
227 id INT AUTO_INCREMENT PRIMARY KEY,
232 CREATE TABLE `dbicsl-test`.mysql_loader_test5 (
233 id INT AUTO_INCREMENT PRIMARY KEY,
235 four_id INTEGER UNIQUE,
236 FOREIGN KEY (four_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
239 $dbh->do('CREATE DATABASE `dbicsl.test`');
241 CREATE TABLE `dbicsl.test`.mysql_loader_test6 (
242 id INT AUTO_INCREMENT PRIMARY KEY,
244 mysql_loader_test4_id INTEGER,
245 FOREIGN KEY (mysql_loader_test4_id) REFERENCES `dbicsl-test`.mysql_loader_test4 (id)
249 CREATE TABLE `dbicsl.test`.mysql_loader_test7 (
250 id INT AUTO_INCREMENT PRIMARY KEY,
252 six_id INTEGER UNIQUE,
253 FOREIGN KEY (six_id) REFERENCES `dbicsl.test`.mysql_loader_test6 (id)
257 CREATE TABLE `dbicsl-test`.mysql_loader_test8 (
258 id INT AUTO_INCREMENT PRIMARY KEY,
260 mysql_loader_test7_id INTEGER,
261 FOREIGN KEY (mysql_loader_test7_id) REFERENCES `dbicsl.test`.mysql_loader_test7 (id)
265 $databases_created = 1;
267 SKIP: foreach my $db_schema (['dbicsl-test', 'dbicsl.test'], '%') {
268 if ($db_schema eq '%') {
270 $dbh->selectall_arrayref('SHOW DATABASES');
273 skip 'no SHOW DATABASES privileges', 28;
278 rmtree EXTRA_DUMP_DIR;
281 local $SIG{__WARN__} = sub {
282 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/;
289 db_schema => $db_schema,
290 dump_directory => EXTRA_DUMP_DIR,
293 [ $dsn, $user, $password ],
296 diag join "\n", @warns if @warns;
299 } 'dumped schema for "dbicsl-test" and "dbicsl.test" databases with no warnings';
301 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info);
304 ok $test_schema = MySQLMultiSchema->connect($dsn, $user, $password);
305 } 'connected test schema';
308 ok $rsrc = $test_schema->source('MysqlLoaderTest4');
309 } 'got source for table in database name with dash';
311 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
312 'column in database name with dash';
314 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
315 'column in database name with dash';
317 is try { $rsrc->column_info('value')->{size} }, 100,
318 'column in database name with dash';
321 ok $rs = $test_schema->resultset('MysqlLoaderTest4');
322 } 'got resultset for table in database name with dash';
325 ok $row = $rs->create({ value => 'foo' });
326 } 'executed SQL on table in database name with dash';
329 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
331 $rel_info = try { $rsrc->relationship_info('mysql_loader_test5') };
333 is_deeply $rel_info->{cond}, {
334 'foreign.four_id' => 'self.id'
335 }, 'relationship in database name with dash';
337 is $rel_info->{attrs}{accessor}, 'single',
338 'relationship in database name with dash';
340 is $rel_info->{attrs}{join_type}, 'LEFT',
341 'relationship in database name with dash';
345 ok $rsrc = $test_schema->source('MysqlLoaderTest5');
346 } 'got source for table in database name with dash';
348 %uniqs = try { $rsrc->unique_constraints };
351 'got unique and primary constraint in database name with dash';
354 ok $rsrc = $test_schema->source('MysqlLoaderTest6');
355 } 'got source for table in database name with dot';
357 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1,
358 'column in database name with dot introspected correctly';
360 is try { $rsrc->column_info('value')->{data_type} }, 'varchar',
361 'column in database name with dot introspected correctly';
363 is try { $rsrc->column_info('value')->{size} }, 100,
364 'column in database name with dot introspected correctly';
367 ok $rs = $test_schema->resultset('MysqlLoaderTest6');
368 } 'got resultset for table in database name with dot';
371 ok $row = $rs->create({ value => 'foo' });
372 } 'executed SQL on table in database name with dot';
375 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 3 unless $test_innodb;
377 $rel_info = try { $rsrc->relationship_info('mysql_loader_test7') };
379 is_deeply $rel_info->{cond}, {
380 'foreign.six_id' => 'self.id'
381 }, 'relationship in database name with dot';
383 is $rel_info->{attrs}{accessor}, 'single',
384 'relationship in database name with dot';
386 is $rel_info->{attrs}{join_type}, 'LEFT',
387 'relationship in database name with dot';
391 ok $rsrc = $test_schema->source('MysqlLoaderTest7');
392 } 'got source for table in database name with dot';
394 %uniqs = try { $rsrc->unique_constraints };
397 'got unique and primary constraint in database name with dot';
400 skip 'set the environment variable DBICTEST_MYSQL_INNODB=1 to test relationships', 4 unless $test_innodb;
403 ok $test_schema->source('MysqlLoaderTest6')
404 ->has_relationship('mysql_loader_test4');
405 } 'cross-database relationship in multi-db_schema';
408 ok $test_schema->source('MysqlLoaderTest4')
409 ->has_relationship('mysql_loader_test6s');
410 } 'cross-database relationship in multi-db_schema';
413 ok $test_schema->source('MysqlLoaderTest8')
414 ->has_relationship('mysql_loader_test7');
415 } 'cross-database relationship in multi-db_schema';
418 ok $test_schema->source('MysqlLoaderTest7')
419 ->has_relationship('mysql_loader_test8s');
420 } 'cross-database relationship in multi-db_schema';
428 if( !$dsn || !$user ) {
429 $tester->skip_tests('You need to set the DBICTEST_MYSQL_DSN, DBICTEST_MYSQL_USER, and DBICTEST_MYSQL_PASS environment variables');
432 diag $skip_rels_msg if not $test_innodb;
433 $tester->run_tests();
437 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) {
438 if ($databases_created && (my $dbh = try { $schema->storage->dbh })) {
439 foreach my $table ('`dbicsl-test`.mysql_loader_test8',
440 '`dbicsl.test`.mysql_loader_test7',
441 '`dbicsl.test`.mysql_loader_test6',
442 '`dbicsl-test`.mysql_loader_test5',
443 '`dbicsl-test`.mysql_loader_test4') {
445 $dbh->do("DROP TABLE $table");
448 diag "Error dropping table: $_";
452 foreach my $db (qw/dbicsl-test dbicsl.test/) {
454 $dbh->do("DROP DATABASE `$db`");
457 diag "Error dropping test database $db: $_";
461 rmtree EXTRA_DUMP_DIR;
464 # vim:et sts=4 sw=4 tw=0: