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
74 name: index_on_prefix_1
83 name: index_on_prefix_2
133 name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
139 - reference_table: thing
143 - reference_table: thing
187 name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
193 - reference_table: some.thing2
197 - reference_table: some.thing2
204 "SET foreign_key_checks=0",
206 "DROP TABLE IF EXISTS `thing`",
207 "CREATE TABLE `thing` (
208 `id` unsigned int NOT NULL auto_increment,
209 `name` varchar(32) NULL,
210 `swedish_name` varchar(32) character set swe7 NULL,
211 `description` text character set utf8 collate utf8_general_ci NULL,
212 INDEX `index_on_prefix_1` (`name`(10)),
213 INDEX `index_on_prefix_2` (`id`, `name`(10), `swedish_name`(20)),
215 UNIQUE `idx_unique_name` (`name`)
216 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
218 "DROP TABLE IF EXISTS `some`.`thing2`",
219 "CREATE TABLE `some`.`thing2` (
220 `id` integer NOT NULL,
221 `foo` integer NOT NULL,
223 `bar_set` set('foo', 'bar', 'baz') NULL,
224 INDEX `index_1` (`id`),
225 INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
228 PRIMARY KEY (`id`, `foo`),
229 CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
230 CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
233 "DROP TABLE IF EXISTS `some`.`thing3`",
234 "CREATE TABLE `some`.`thing3` (
235 `id` integer NOT NULL,
236 `foo` integer NOT NULL,
238 `bar_set` set('foo', 'bar', 'baz') NULL,
239 INDEX `index_1` (`id`),
240 INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
243 PRIMARY KEY (`id`, `foo`),
244 CONSTRAINT `fk_thing_2` FOREIGN KEY (`foo`) REFERENCES `some`.`thing2` (`id`, `foo`),
245 CONSTRAINT `fk_thing_3` FOREIGN KEY (`foo2`) REFERENCES `some`.`thing2` (`id`, `foo`)
248 "SET foreign_key_checks=1",
252 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
254 my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
258 $sqlt = SQL::Translator->new(
264 quote_table_names => 1,
265 quote_field_names => 1
268 my $out = $sqlt->translate(\$yaml_in)
269 or die "Translate error:".$sqlt->error;
270 ok $out ne "", "Produced something!";
271 eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting";
273 my @out = $sqlt->translate(\$yaml_in)
274 or die "Translat eerror:".$sqlt->error;
275 is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
277 $sqlt->quote_identifiers(0);
279 $out = $sqlt->translate(\$yaml_in)
280 or die "Translate error:".$sqlt->error;
282 @out = $sqlt->translate(\$yaml_in)
283 or die "Translate error:".$sqlt->error;
284 $mysql_out =~ s/`//g;
285 my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
286 eq_or_diff $out, $mysql_out, "Output looks right without quoting";
287 is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
289 $sqlt->quote_identifiers(1);
290 $sqlt->add_drop_table(1);
292 @out = $sqlt->translate(\$yaml_in)
293 or die "Translat eerror:".$sqlt->error;
294 $out = $sqlt->translate(\$yaml_in)
295 or die "Translat eerror:".$sqlt->error;
297 eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
298 is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
301 ###############################################################################
305 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
307 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
309 data_type => 'VARCHAR',
311 default_value => undef,
312 is_auto_increment => 0,
317 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
319 is($field1_sql, 'myfield VARCHAR(10) NULL', 'Create field works');
321 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
323 data_type => 'VARCHAR',
325 default_value => undef,
326 is_auto_increment => 0,
331 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
333 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
335 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
337 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10) NULL', 'Add field works');
339 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
340 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
342 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
344 data_type => 'boolean',
349 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
350 is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
351 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
352 is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
353 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
354 is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
362 for my $size (keys %$number_sizes) {
363 my $expected = $number_sizes->{$size};
364 my $number_field = SQL::Translator::Schema::Field->new(
365 name => "numberfield_$expected",
367 data_type => 'number',
375 SQL::Translator::Producer::MySQL::create_field($number_field),
376 "numberfield_$expected $expected($size) NULL",
377 "Use $expected for NUMBER types of size $size"
382 for my $size (qw/255 256 65535 65536/) {
383 $varchars->{$size} = SQL::Translator::Schema::Field->new(
386 data_type => 'varchar',
394 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }),
395 'vch_255 varchar(255) NULL',
396 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
399 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }),
400 'vch_255 varchar(255) NULL',
401 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
404 SQL::Translator::Producer::MySQL::create_field($varchars->{255}),
405 'vch_255 varchar(255) NULL',
406 'VARCHAR(255) is not substituted with TEXT when no version specified',
411 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }),
412 'vch_256 varchar(256) NULL',
413 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
416 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }),
418 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
421 SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
423 'VARCHAR(256) is substituted with TEXT when no version specified',
428 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }),
429 'vch_65535 varchar(65535) NULL',
430 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
433 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }),
434 'vch_65535 text NULL',
435 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
438 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
439 'vch_65535 text NULL',
440 'VARCHAR(65535) is substituted with TEXT when no version specified',
445 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }),
446 'vch_65536 text NULL',
447 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
450 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }),
451 'vch_65536 text NULL',
452 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
455 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}),
456 'vch_65536 text NULL',
457 'VARCHAR(65536) is substituted with TEXT when no version specified',
462 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
463 fields => [qw/id name/],
464 sql => 'SELECT id, name FROM thing',
466 mysql_definer => 'CURRENT_USER',
467 mysql_algorithm => 'MERGE',
468 mysql_security => 'DEFINER',
470 my $create_opts = { add_replace_view => 1, no_comments => 1 };
471 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
473 my $view_sql_replace = <<'EOV';
476 DEFINER = CURRENT_USER
478 VIEW view_foo ( id, name ) AS
479 SELECT id, name FROM thing
482 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
485 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
486 fields => [qw/id name/],
487 sql => 'SELECT id, name FROM thing',);
488 my $create2_opts = { add_replace_view => 0, no_comments => 1 };
489 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
490 my $view_sql_noreplace = <<'EOV';
492 VIEW view_foo ( id, name ) AS
493 SELECT id, name FROM thing
496 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
499 my %extra = $view1->extra;
502 'mysql_algorithm' => 'MERGE',
503 'mysql_definer' => 'CURRENT_USER',
504 'mysql_security' => 'DEFINER'
509 $view1->remove_extra(qw/mysql_definer mysql_security/);
511 my %extra = $view1->extra;
512 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
515 $view1->remove_extra();
517 my %extra = $view1->extra;
518 is_deeply \%extra, {}, 'Extra attributes completely removed';
524 # certain types do not support a size, see also:
525 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
526 for my $type (qw/date time timestamp datetime year/) {
527 my $field = SQL::Translator::Schema::Field->new(
532 default_value => undef,
533 is_auto_increment => 0,
538 my $sql = SQL::Translator::Producer::MySQL::create_field($field);
539 is($sql, "my$type $type NULL", "Skip length param for type $type");
547 my $table = SQL::Translator::Schema::Table->new( name => 'mydb.mytable');
549 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
551 data_type => 'VARCHAR',
553 default_value => undef,
554 is_auto_increment => 0,
560 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
562 data_type => 'VARCHAR',
564 default_value => undef,
565 is_auto_increment => 0,
570 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
572 data_type => 'boolean',
581 quote_table_names => $qt,
582 quote_field_names => $qf,
586 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, $field2, $options);
587 is($alter_field, 'ALTER TABLE `mydb`.`mytable` CHANGE COLUMN `myfield` `myfield` VARCHAR(25) NOT NULL', 'Alter field works');
589 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1, $options);
591 is($add_field, 'ALTER TABLE `mydb`.`mytable` ADD COLUMN `myfield` VARCHAR(10) NULL', 'Add field works');
593 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2, $options);
594 is($drop_field, 'ALTER TABLE `mydb`.`mytable` DROP COLUMN `myfield`', 'Drop field works');
596 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1, %$options });
597 is($field3_sql, '`myfield` boolean NOT NULL', 'For Mysql >= 4, use boolean type');
598 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22, %$options });
599 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
600 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,$options);
601 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
609 for my $size (keys %$number_sizes) {
610 my $expected = $number_sizes->{$size};
611 my $number_field = SQL::Translator::Schema::Field->new(
612 name => "numberfield_$expected",
614 data_type => 'number',
622 SQL::Translator::Producer::MySQL::create_field($number_field, $options),
623 "`numberfield_$expected` $expected($size) NULL",
624 "Use $expected for NUMBER types of size $size"
629 for my $size (qw/255 256 65535 65536/) {
630 $varchars->{$size} = SQL::Translator::Schema::Field->new(
633 data_type => 'varchar',
641 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003, %$options }),
642 '`vch_255` varchar(255) NULL',
643 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
646 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0, %$options }),
647 '`vch_255` varchar(255) NULL',
648 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
651 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, $options),
652 '`vch_255` varchar(255) NULL',
653 'VARCHAR(255) is not substituted with TEXT when no version specified',
658 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003, %$options }),
659 '`vch_256` varchar(256) NULL',
660 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
663 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0, %$options }),
664 '`vch_256` text NULL',
665 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
668 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, $options),
669 '`vch_256` text NULL',
670 'VARCHAR(256) is substituted with TEXT when no version specified',
675 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003, %$options }),
676 '`vch_65535` varchar(65535) NULL',
677 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
680 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0, %$options }),
681 '`vch_65535` text NULL',
682 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
685 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, $options),
686 '`vch_65535` text NULL',
687 'VARCHAR(65535) is substituted with TEXT when no version specified',
692 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003, %$options }),
693 '`vch_65536` text NULL',
694 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
697 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0, %$options }),
698 '`vch_65536` text NULL',
699 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
702 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, $options),
703 '`vch_65536` text NULL',
704 'VARCHAR(65536) is substituted with TEXT when no version specified',
708 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
709 fields => [qw/id name/],
710 sql => 'SELECT `id`, `name` FROM `my`.`thing`',
712 mysql_definer => 'CURRENT_USER',
713 mysql_algorithm => 'MERGE',
714 mysql_security => 'DEFINER',
716 my $create_opts = { add_replace_view => 1, no_comments => 1, %$options };
717 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
719 my $view_sql_replace = <<'EOV';
722 DEFINER = CURRENT_USER
724 VIEW `view_foo` ( `id`, `name` ) AS
725 SELECT `id`, `name` FROM `my`.`thing`
728 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
731 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
732 fields => [qw/id name/],
733 sql => 'SELECT `id`, `name` FROM `my`.`thing`',);
734 my $create2_opts = { add_replace_view => 0, no_comments => 1, %$options };
735 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
736 my $view_sql_noreplace = <<'EOV';
738 VIEW `view_foo` ( `id`, `name` ) AS
739 SELECT `id`, `name` FROM `my`.`thing`
742 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
745 my %extra = $view1->extra;
748 'mysql_algorithm' => 'MERGE',
749 'mysql_definer' => 'CURRENT_USER',
750 'mysql_security' => 'DEFINER'
755 $view1->remove_extra(qw/mysql_definer mysql_security/);
757 my %extra = $view1->extra;
758 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
761 $view1->remove_extra();
763 my %extra = $view1->extra;
764 is_deeply \%extra, {}, 'Extra attributes completely removed';
770 # certain types do not support a size, see also:
771 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
772 for my $type (qw/date time timestamp datetime year/) {
773 my $field = SQL::Translator::Schema::Field->new(
778 default_value => undef,
779 is_auto_increment => 0,
784 my $sql = SQL::Translator::Producer::MySQL::create_field($field, $options);
785 is($sql, "`my$type` $type NULL", "Skip length param for type $type");
791 my $table = SQL::Translator::Schema::Table->new(name => 'table');
793 SQL::Translator::Schema::Field->new( name => 'mypk',
797 default_value => undef,
798 is_auto_increment => 1,
803 my $constraint = $table->add_constraint(fields => ['mypk'], type => 'PRIMARY_KEY');
804 my $options = {quote_table_names => '`'};
805 is(SQL::Translator::Producer::MySQL::alter_drop_constraint($constraint,$options),
806 'ALTER TABLE `table` DROP PRIMARY KEY','valid drop primary key');