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
72 default_value: !!perl/ref
129 name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
135 - reference_table: thing
139 - reference_table: thing
183 name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
189 - reference_table: some.thing2
193 - reference_table: some.thing2
200 "SET foreign_key_checks=0",
202 "DROP TABLE IF EXISTS `thing`",
203 "CREATE TABLE `thing` (
204 `id` unsigned int NOT NULL auto_increment,
205 `name` varchar(32) NULL,
206 `swedish_name` varchar(32) character set swe7 NULL,
207 `description` text character set utf8 collate utf8_general_ci NULL,
208 `timestamp` timestamp on update CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
210 UNIQUE `idx_unique_name` (`name`)
211 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
213 "DROP TABLE IF EXISTS `some`.`thing2`",
214 "CREATE TABLE `some`.`thing2` (
215 `id` integer NOT NULL,
216 `foo` integer NOT NULL,
218 `bar_set` set('foo', 'bar', 'baz') NULL,
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` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
225 CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
228 "DROP TABLE IF EXISTS `some`.`thing3`",
229 "CREATE TABLE `some`.`thing3` (
230 `id` integer NOT NULL,
231 `foo` integer NOT NULL,
233 `bar_set` set('foo', 'bar', 'baz') NULL,
234 INDEX `index_1` (`id`),
235 INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
238 PRIMARY KEY (`id`, `foo`),
239 CONSTRAINT `fk_thing_2` FOREIGN KEY (`foo`) REFERENCES `some`.`thing2` (`id`, `foo`),
240 CONSTRAINT `fk_thing_3` FOREIGN KEY (`foo2`) REFERENCES `some`.`thing2` (`id`, `foo`)
243 "SET foreign_key_checks=1",
247 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
249 my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
253 $sqlt = SQL::Translator->new(
259 quote_table_names => 1,
260 quote_field_names => 1
263 my $out = $sqlt->translate(\$yaml_in)
264 or die "Translate error:".$sqlt->error;
265 ok $out ne "", "Produced something!";
266 eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting";
268 my @out = $sqlt->translate(\$yaml_in)
269 or die "Translat eerror:".$sqlt->error;
270 is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
272 $sqlt->quote_identifiers(0);
274 $out = $sqlt->translate(\$yaml_in)
275 or die "Translate error:".$sqlt->error;
277 @out = $sqlt->translate(\$yaml_in)
278 or die "Translate error:".$sqlt->error;
279 $mysql_out =~ s/`//g;
280 my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
281 eq_or_diff $out, $mysql_out, "Output looks right without quoting";
282 is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
284 $sqlt->quote_identifiers(1);
285 $sqlt->add_drop_table(1);
287 @out = $sqlt->translate(\$yaml_in)
288 or die "Translat eerror:".$sqlt->error;
289 $out = $sqlt->translate(\$yaml_in)
290 or die "Translat eerror:".$sqlt->error;
292 eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
293 is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
296 ###############################################################################
300 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
302 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
304 data_type => 'VARCHAR',
306 default_value => undef,
307 is_auto_increment => 0,
312 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
314 is($field1_sql, 'myfield VARCHAR(10) NULL', 'Create field works');
316 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
318 data_type => 'VARCHAR',
320 default_value => undef,
321 is_auto_increment => 0,
326 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
328 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
330 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
332 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10) NULL', 'Add field works');
334 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
335 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
337 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
339 data_type => 'boolean',
344 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
345 is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
346 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
347 is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
348 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
349 is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
357 for my $size (keys %$number_sizes) {
358 my $expected = $number_sizes->{$size};
359 my $number_field = SQL::Translator::Schema::Field->new(
360 name => "numberfield_$expected",
362 data_type => 'number',
370 SQL::Translator::Producer::MySQL::create_field($number_field),
371 "numberfield_$expected $expected($size) NULL",
372 "Use $expected for NUMBER types of size $size"
377 for my $size (qw/255 256 65535 65536/) {
378 $varchars->{$size} = SQL::Translator::Schema::Field->new(
381 data_type => 'varchar',
389 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }),
390 'vch_255 varchar(255) NULL',
391 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
394 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }),
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}),
400 'vch_255 varchar(255) NULL',
401 'VARCHAR(255) is not substituted with TEXT when no version specified',
406 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }),
407 'vch_256 varchar(256) NULL',
408 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
411 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }),
413 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
416 SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
418 'VARCHAR(256) is substituted with TEXT when no version specified',
423 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }),
424 'vch_65535 varchar(65535) NULL',
425 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
428 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }),
429 'vch_65535 text NULL',
430 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
433 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
434 'vch_65535 text NULL',
435 'VARCHAR(65535) is substituted with TEXT when no version specified',
440 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }),
441 'vch_65536 text NULL',
442 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
445 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }),
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}),
451 'vch_65536 text NULL',
452 'VARCHAR(65536) is substituted with TEXT when no version specified',
457 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
458 fields => [qw/id name/],
459 sql => 'SELECT id, name FROM thing',
461 mysql_definer => 'CURRENT_USER',
462 mysql_algorithm => 'MERGE',
463 mysql_security => 'DEFINER',
465 my $create_opts = { add_replace_view => 1, no_comments => 1 };
466 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
468 my $view_sql_replace = <<'EOV';
471 DEFINER = CURRENT_USER
473 VIEW view_foo ( id, name ) AS
474 SELECT id, name FROM thing
477 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
480 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
481 fields => [qw/id name/],
482 sql => 'SELECT id, name FROM thing',);
483 my $create2_opts = { add_replace_view => 0, no_comments => 1 };
484 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
485 my $view_sql_noreplace = <<'EOV';
487 VIEW view_foo ( id, name ) AS
488 SELECT id, name FROM thing
491 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
494 my %extra = $view1->extra;
497 'mysql_algorithm' => 'MERGE',
498 'mysql_definer' => 'CURRENT_USER',
499 'mysql_security' => 'DEFINER'
504 $view1->remove_extra(qw/mysql_definer mysql_security/);
506 my %extra = $view1->extra;
507 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
510 $view1->remove_extra();
512 my %extra = $view1->extra;
513 is_deeply \%extra, {}, 'Extra attributes completely removed';
519 # certain types do not support a size, see also:
520 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
521 for my $type (qw/date time timestamp datetime year/) {
522 my $field = SQL::Translator::Schema::Field->new(
527 default_value => undef,
528 is_auto_increment => 0,
533 my $sql = SQL::Translator::Producer::MySQL::create_field($field);
534 is($sql, "my$type $type NULL", "Skip length param for type $type");
542 my $table = SQL::Translator::Schema::Table->new( name => 'mydb.mytable');
544 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
546 data_type => 'VARCHAR',
548 default_value => undef,
549 is_auto_increment => 0,
555 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
557 data_type => 'VARCHAR',
559 default_value => undef,
560 is_auto_increment => 0,
565 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
567 data_type => 'boolean',
576 quote_table_names => $qt,
577 quote_field_names => $qf,
581 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, $field2, $options);
582 is($alter_field, 'ALTER TABLE `mydb`.`mytable` CHANGE COLUMN `myfield` `myfield` VARCHAR(25) NOT NULL', 'Alter field works');
584 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1, $options);
586 is($add_field, 'ALTER TABLE `mydb`.`mytable` ADD COLUMN `myfield` VARCHAR(10) NULL', 'Add field works');
588 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2, $options);
589 is($drop_field, 'ALTER TABLE `mydb`.`mytable` DROP COLUMN `myfield`', 'Drop field works');
591 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1, %$options });
592 is($field3_sql, '`myfield` boolean NOT NULL', 'For Mysql >= 4, use boolean type');
593 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22, %$options });
594 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
595 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,$options);
596 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
604 for my $size (keys %$number_sizes) {
605 my $expected = $number_sizes->{$size};
606 my $number_field = SQL::Translator::Schema::Field->new(
607 name => "numberfield_$expected",
609 data_type => 'number',
617 SQL::Translator::Producer::MySQL::create_field($number_field, $options),
618 "`numberfield_$expected` $expected($size) NULL",
619 "Use $expected for NUMBER types of size $size"
624 for my $size (qw/255 256 65535 65536/) {
625 $varchars->{$size} = SQL::Translator::Schema::Field->new(
628 data_type => 'varchar',
636 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003, %$options }),
637 '`vch_255` varchar(255) NULL',
638 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
641 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0, %$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}, $options),
647 '`vch_255` varchar(255) NULL',
648 'VARCHAR(255) is not substituted with TEXT when no version specified',
653 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003, %$options }),
654 '`vch_256` varchar(256) NULL',
655 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
658 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0, %$options }),
659 '`vch_256` text NULL',
660 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
663 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, $options),
664 '`vch_256` text NULL',
665 'VARCHAR(256) is substituted with TEXT when no version specified',
670 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003, %$options }),
671 '`vch_65535` varchar(65535) NULL',
672 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
675 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0, %$options }),
676 '`vch_65535` text NULL',
677 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
680 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, $options),
681 '`vch_65535` text NULL',
682 'VARCHAR(65535) is substituted with TEXT when no version specified',
687 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003, %$options }),
688 '`vch_65536` text NULL',
689 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
692 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0, %$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}, $options),
698 '`vch_65536` text NULL',
699 'VARCHAR(65536) is substituted with TEXT when no version specified',
703 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
704 fields => [qw/id name/],
705 sql => 'SELECT `id`, `name` FROM `my`.`thing`',
707 mysql_definer => 'CURRENT_USER',
708 mysql_algorithm => 'MERGE',
709 mysql_security => 'DEFINER',
711 my $create_opts = { add_replace_view => 1, no_comments => 1, %$options };
712 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
714 my $view_sql_replace = <<'EOV';
717 DEFINER = CURRENT_USER
719 VIEW `view_foo` ( `id`, `name` ) AS
720 SELECT `id`, `name` FROM `my`.`thing`
723 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
726 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
727 fields => [qw/id name/],
728 sql => 'SELECT `id`, `name` FROM `my`.`thing`',);
729 my $create2_opts = { add_replace_view => 0, no_comments => 1, %$options };
730 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
731 my $view_sql_noreplace = <<'EOV';
733 VIEW `view_foo` ( `id`, `name` ) AS
734 SELECT `id`, `name` FROM `my`.`thing`
737 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
740 my %extra = $view1->extra;
743 'mysql_algorithm' => 'MERGE',
744 'mysql_definer' => 'CURRENT_USER',
745 'mysql_security' => 'DEFINER'
750 $view1->remove_extra(qw/mysql_definer mysql_security/);
752 my %extra = $view1->extra;
753 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
756 $view1->remove_extra();
758 my %extra = $view1->extra;
759 is_deeply \%extra, {}, 'Extra attributes completely removed';
765 # certain types do not support a size, see also:
766 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
767 for my $type (qw/date time timestamp datetime year/) {
768 my $field = SQL::Translator::Schema::Field->new(
773 default_value => undef,
774 is_auto_increment => 0,
779 my $sql = SQL::Translator::Producer::MySQL::create_field($field, $options);
780 is($sql, "`my$type` $type NULL", "Skip length param for type $type");
786 my $table = SQL::Translator::Schema::Table->new(name => 'table');
788 SQL::Translator::Schema::Field->new( name => 'mypk',
792 default_value => undef,
793 is_auto_increment => 1,
798 my $constraint = $table->add_constraint(fields => ['mypk'], type => 'PRIMARY_KEY');
799 my $options = {quote_table_names => '`'};
800 is(SQL::Translator::Producer::MySQL::alter_drop_constraint($constraint,$options),
801 'ALTER TABLE `table` DROP PRIMARY KEY','valid drop primary key');