5 # Note that the bulk of the testing for the mysql producer is in
6 # 08postgres-to-mysql.t. This test is for additional stuff that can't be tested
7 # using an Oracle schema as source e.g. extra attributes.
13 use Test::SQL::Translator qw(maybe_plan);
19 #=============================================================================
24 'SQL::Translator::Producer::MySQL',
28 use Test::Differences;
33 my $yaml_in = <<EOSCHEMA;
41 mysql_collate: latin1_danish_ci
46 data_type: unsigned int
68 mysql_collate: utf8_general_ci
115 name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
121 - reference_table: thing
125 - reference_table: thing
169 name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
175 - reference_table: some.thing2
179 - reference_table: some.thing2
186 "SET foreign_key_checks=0",
188 "DROP TABLE IF EXISTS `thing`",
189 "CREATE TABLE `thing` (
190 `id` unsigned int auto_increment,
192 `swedish_name` varchar(32) character set swe7,
193 `description` text character set utf8 collate utf8_general_ci,
195 UNIQUE `idx_unique_name` (`name`)
196 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
198 "DROP TABLE IF EXISTS `some`.`thing2`",
199 "CREATE TABLE `some`.`thing2` (
203 `bar_set` set('foo', 'bar', 'baz'),
204 INDEX `index_1` (`id`),
205 INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
208 PRIMARY KEY (`id`, `foo`),
209 CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
210 CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
213 "DROP TABLE IF EXISTS `some`.`thing3`",
214 "CREATE TABLE `some`.`thing3` (
218 `bar_set` set('foo', 'bar', 'baz'),
219 INDEX `index_1` (`id`),
220 INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
223 PRIMARY KEY (`id`, `foo`),
224 CONSTRAINT `fk_thing_2` FOREIGN KEY (`foo`) REFERENCES `some`.`thing2` (`id`, `foo`),
225 CONSTRAINT `fk_thing_3` FOREIGN KEY (`foo2`) REFERENCES `some`.`thing2` (`id`, `foo`)
228 "SET foreign_key_checks=1",
232 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
234 my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
238 $sqlt = SQL::Translator->new(
244 quote_table_names => 1,
245 quote_field_names => 1
248 my $out = $sqlt->translate(\$yaml_in)
249 or die "Translate error:".$sqlt->error;
250 ok $out ne "", "Produced something!";
251 eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting";
253 my @out = $sqlt->translate(\$yaml_in)
254 or die "Translat eerror:".$sqlt->error;
255 is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
258 @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
259 $out = $sqlt->translate(\$yaml_in)
260 or die "Translate error:".$sqlt->error;
262 @out = $sqlt->translate(\$yaml_in)
263 or die "Translate error:".$sqlt->error;
264 $mysql_out =~ s/`//g;
265 my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
266 eq_or_diff $out, $mysql_out, "Output looks right without quoting";
267 is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
269 @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
270 @out = $sqlt->translate(\$yaml_in)
271 or die "Translat eerror:".$sqlt->error;
272 $out = $sqlt->translate(\$yaml_in)
273 or die "Translat eerror:".$sqlt->error;
275 eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
276 is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
279 ###############################################################################
283 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
285 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
287 data_type => 'VARCHAR',
289 default_value => undef,
290 is_auto_increment => 0,
295 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
297 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
299 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
301 data_type => 'VARCHAR',
303 default_value => undef,
304 is_auto_increment => 0,
309 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
311 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
313 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
315 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
317 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
318 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
320 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
322 data_type => 'boolean',
327 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
328 is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
329 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
330 is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
331 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
332 is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
340 for my $size (keys %$number_sizes) {
341 my $expected = $number_sizes->{$size};
342 my $number_field = SQL::Translator::Schema::Field->new(
343 name => "numberfield_$expected",
345 data_type => 'number',
353 SQL::Translator::Producer::MySQL::create_field($number_field),
354 "numberfield_$expected $expected($size)",
355 "Use $expected for NUMBER types of size $size"
360 for my $size (qw/255 256 65535 65536/) {
361 $varchars->{$size} = SQL::Translator::Schema::Field->new(
364 data_type => 'varchar',
372 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }),
373 'vch_255 varchar(255)',
374 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
377 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }),
378 'vch_255 varchar(255)',
379 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
382 SQL::Translator::Producer::MySQL::create_field($varchars->{255}),
383 'vch_255 varchar(255)',
384 'VARCHAR(255) is not substituted with TEXT when no version specified',
389 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }),
390 'vch_256 varchar(256)',
391 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
394 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }),
396 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
399 SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
401 'VARCHAR(256) is substituted with TEXT when no version specified',
406 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }),
407 'vch_65535 varchar(65535)',
408 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
411 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }),
413 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
416 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
418 'VARCHAR(65535) is substituted with TEXT when no version specified',
423 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }),
425 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
428 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }),
430 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
433 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}),
435 'VARCHAR(65536) is substituted with TEXT when no version specified',
440 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
441 fields => [qw/id name/],
442 sql => 'SELECT id, name FROM thing',
444 mysql_definer => 'CURRENT_USER',
445 mysql_algorithm => 'MERGE',
446 mysql_security => 'DEFINER',
448 my $create_opts = { add_replace_view => 1, no_comments => 1 };
449 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
451 my $view_sql_replace = <<'EOV';
454 DEFINER = CURRENT_USER
456 VIEW view_foo ( id, name ) AS
457 SELECT id, name FROM thing
460 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
463 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
464 fields => [qw/id name/],
465 sql => 'SELECT id, name FROM thing',);
466 my $create2_opts = { add_replace_view => 0, no_comments => 1 };
467 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
468 my $view_sql_noreplace = <<'EOV';
470 VIEW view_foo ( id, name ) AS
471 SELECT id, name FROM thing
474 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
477 my %extra = $view1->extra;
480 'mysql_algorithm' => 'MERGE',
481 'mysql_definer' => 'CURRENT_USER',
482 'mysql_security' => 'DEFINER'
487 $view1->remove_extra(qw/mysql_definer mysql_security/);
489 my %extra = $view1->extra;
490 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
493 $view1->remove_extra();
495 my %extra = $view1->extra;
496 is_deeply \%extra, {}, 'Extra attributes completely removed';
502 # certain types do not support a size, see also:
503 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
504 for my $type (qw/date time timestamp datetime year/) {
505 my $field = SQL::Translator::Schema::Field->new(
510 default_value => undef,
511 is_auto_increment => 0,
516 my $sql = SQL::Translator::Producer::MySQL::create_field($field);
517 is($sql, "my$type $type", "Skip length param for type $type");
525 my $table = SQL::Translator::Schema::Table->new( name => 'mydb.mytable');
527 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
529 data_type => 'VARCHAR',
531 default_value => undef,
532 is_auto_increment => 0,
538 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
540 data_type => 'VARCHAR',
542 default_value => undef,
543 is_auto_increment => 0,
548 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
550 data_type => 'boolean',
559 quote_table_names => $qt,
560 quote_field_names => $qf,
564 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, $field2, $options);
565 is($alter_field, 'ALTER TABLE `mydb`.`mytable` CHANGE COLUMN `myfield` `myfield` VARCHAR(25) NOT NULL', 'Alter field works');
567 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1, $options);
569 is($add_field, 'ALTER TABLE `mydb`.`mytable` ADD COLUMN `myfield` VARCHAR(10)', 'Add field works');
571 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2, $options);
572 is($drop_field, 'ALTER TABLE `mydb`.`mytable` DROP COLUMN `myfield`', 'Drop field works');
574 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1, %$options });
575 is($field3_sql, '`myfield` boolean NOT NULL', 'For Mysql >= 4, use boolean type');
576 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22, %$options });
577 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
578 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,$options);
579 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
587 for my $size (keys %$number_sizes) {
588 my $expected = $number_sizes->{$size};
589 my $number_field = SQL::Translator::Schema::Field->new(
590 name => "numberfield_$expected",
592 data_type => 'number',
600 SQL::Translator::Producer::MySQL::create_field($number_field, $options),
601 "`numberfield_$expected` $expected($size)",
602 "Use $expected for NUMBER types of size $size"
607 for my $size (qw/255 256 65535 65536/) {
608 $varchars->{$size} = SQL::Translator::Schema::Field->new(
611 data_type => 'varchar',
619 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003, %$options }),
620 '`vch_255` varchar(255)',
621 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
624 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0, %$options }),
625 '`vch_255` varchar(255)',
626 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
629 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, $options),
630 '`vch_255` varchar(255)',
631 'VARCHAR(255) is not substituted with TEXT when no version specified',
636 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003, %$options }),
637 '`vch_256` varchar(256)',
638 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
641 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0, %$options }),
643 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
646 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, $options),
648 'VARCHAR(256) is substituted with TEXT when no version specified',
653 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003, %$options }),
654 '`vch_65535` varchar(65535)',
655 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
658 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0, %$options }),
660 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
663 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, $options),
665 'VARCHAR(65535) is substituted with TEXT when no version specified',
670 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003, %$options }),
672 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
675 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0, %$options }),
677 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
680 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, $options),
682 'VARCHAR(65536) is substituted with TEXT when no version specified',
686 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
687 fields => [qw/id name/],
688 sql => 'SELECT `id`, `name` FROM `my`.`thing`',
690 mysql_definer => 'CURRENT_USER',
691 mysql_algorithm => 'MERGE',
692 mysql_security => 'DEFINER',
694 my $create_opts = { add_replace_view => 1, no_comments => 1, %$options };
695 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
697 my $view_sql_replace = <<'EOV';
700 DEFINER = CURRENT_USER
702 VIEW `view_foo` ( `id`, `name` ) AS
703 SELECT `id`, `name` FROM `my`.`thing`
706 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
709 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
710 fields => [qw/id name/],
711 sql => 'SELECT `id`, `name` FROM `my`.`thing`',);
712 my $create2_opts = { add_replace_view => 0, no_comments => 1, %$options };
713 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
714 my $view_sql_noreplace = <<'EOV';
716 VIEW `view_foo` ( `id`, `name` ) AS
717 SELECT `id`, `name` FROM `my`.`thing`
720 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
723 my %extra = $view1->extra;
726 'mysql_algorithm' => 'MERGE',
727 'mysql_definer' => 'CURRENT_USER',
728 'mysql_security' => 'DEFINER'
733 $view1->remove_extra(qw/mysql_definer mysql_security/);
735 my %extra = $view1->extra;
736 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
739 $view1->remove_extra();
741 my %extra = $view1->extra;
742 is_deeply \%extra, {}, 'Extra attributes completely removed';
748 # certain types do not support a size, see also:
749 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
750 for my $type (qw/date time timestamp datetime year/) {
751 my $field = SQL::Translator::Schema::Field->new(
756 default_value => undef,
757 is_auto_increment => 0,
762 my $sql = SQL::Translator::Producer::MySQL::create_field($field, $options);
763 is($sql, "`my$type` $type", "Skip length param for type $type");
769 my $table = SQL::Translator::Schema::Table->new(name => 'table');
771 SQL::Translator::Schema::Field->new( name => 'mypk',
775 default_value => undef,
776 is_auto_increment => 1,
781 my $constraint = $table->add_constraint(fields => ['mypk'], type => 'PRIMARY_KEY');
782 my $options = {quote_table_names => '`'};
783 is(SQL::Translator::Producer::MySQL::alter_drop_constraint($constraint,$options),
784 'ALTER TABLE `table` DROP PRIMARY KEY','valid drop primary key');