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 NOT NULL auto_increment,
191 `name` varchar(32) NULL,
192 `swedish_name` varchar(32) character set swe7 NULL,
193 `description` text character set utf8 collate utf8_general_ci NULL,
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` (
200 `id` integer NOT NULL,
201 `foo` integer NOT NULL,
203 `bar_set` set('foo', 'bar', 'baz') NULL,
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` (
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_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";
257 $sqlt->quote_identifiers(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->quote_identifiers(1);
270 $sqlt->add_drop_table(1);
272 @out = $sqlt->translate(\$yaml_in)
273 or die "Translat eerror:".$sqlt->error;
274 $out = $sqlt->translate(\$yaml_in)
275 or die "Translat eerror:".$sqlt->error;
277 eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
278 is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
281 ###############################################################################
285 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
287 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
289 data_type => 'VARCHAR',
291 default_value => undef,
292 is_auto_increment => 0,
297 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
299 is($field1_sql, 'myfield VARCHAR(10) NULL', 'Create field works');
301 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
303 data_type => 'VARCHAR',
305 default_value => undef,
306 is_auto_increment => 0,
311 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
313 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
315 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
317 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10) NULL', 'Add field works');
319 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
320 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
322 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
324 data_type => 'boolean',
329 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
330 is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
331 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
332 is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
333 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
334 is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
342 for my $size (keys %$number_sizes) {
343 my $expected = $number_sizes->{$size};
344 my $number_field = SQL::Translator::Schema::Field->new(
345 name => "numberfield_$expected",
347 data_type => 'number',
355 SQL::Translator::Producer::MySQL::create_field($number_field),
356 "numberfield_$expected $expected($size) NULL",
357 "Use $expected for NUMBER types of size $size"
362 for my $size (qw/255 256 65535 65536/) {
363 $varchars->{$size} = SQL::Translator::Schema::Field->new(
366 data_type => 'varchar',
374 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }),
375 'vch_255 varchar(255) NULL',
376 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
379 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }),
380 'vch_255 varchar(255) NULL',
381 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
384 SQL::Translator::Producer::MySQL::create_field($varchars->{255}),
385 'vch_255 varchar(255) NULL',
386 'VARCHAR(255) is not substituted with TEXT when no version specified',
391 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }),
392 'vch_256 varchar(256) NULL',
393 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
396 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }),
398 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
401 SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
403 'VARCHAR(256) is substituted with TEXT when no version specified',
408 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }),
409 'vch_65535 varchar(65535) NULL',
410 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
413 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }),
414 'vch_65535 text NULL',
415 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
418 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
419 'vch_65535 text NULL',
420 'VARCHAR(65535) is substituted with TEXT when no version specified',
425 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }),
426 'vch_65536 text NULL',
427 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
430 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }),
431 'vch_65536 text NULL',
432 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
435 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}),
436 'vch_65536 text NULL',
437 'VARCHAR(65536) is substituted with TEXT when no version specified',
442 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
443 fields => [qw/id name/],
444 sql => 'SELECT id, name FROM thing',
446 mysql_definer => 'CURRENT_USER',
447 mysql_algorithm => 'MERGE',
448 mysql_security => 'DEFINER',
450 my $create_opts = { add_replace_view => 1, no_comments => 1 };
451 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
453 my $view_sql_replace = <<'EOV';
456 DEFINER = CURRENT_USER
458 VIEW view_foo ( id, name ) AS
459 SELECT id, name FROM thing
462 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
465 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
466 fields => [qw/id name/],
467 sql => 'SELECT id, name FROM thing',);
468 my $create2_opts = { add_replace_view => 0, no_comments => 1 };
469 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
470 my $view_sql_noreplace = <<'EOV';
472 VIEW view_foo ( id, name ) AS
473 SELECT id, name FROM thing
476 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
479 my %extra = $view1->extra;
482 'mysql_algorithm' => 'MERGE',
483 'mysql_definer' => 'CURRENT_USER',
484 'mysql_security' => 'DEFINER'
489 $view1->remove_extra(qw/mysql_definer mysql_security/);
491 my %extra = $view1->extra;
492 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
495 $view1->remove_extra();
497 my %extra = $view1->extra;
498 is_deeply \%extra, {}, 'Extra attributes completely removed';
504 # certain types do not support a size, see also:
505 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
506 for my $type (qw/date time timestamp datetime year/) {
507 my $field = SQL::Translator::Schema::Field->new(
512 default_value => undef,
513 is_auto_increment => 0,
518 my $sql = SQL::Translator::Producer::MySQL::create_field($field);
519 is($sql, "my$type $type NULL", "Skip length param for type $type");
527 my $table = SQL::Translator::Schema::Table->new( name => 'mydb.mytable');
529 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
531 data_type => 'VARCHAR',
533 default_value => undef,
534 is_auto_increment => 0,
540 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
542 data_type => 'VARCHAR',
544 default_value => undef,
545 is_auto_increment => 0,
550 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
552 data_type => 'boolean',
561 quote_table_names => $qt,
562 quote_field_names => $qf,
566 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, $field2, $options);
567 is($alter_field, 'ALTER TABLE `mydb`.`mytable` CHANGE COLUMN `myfield` `myfield` VARCHAR(25) NOT NULL', 'Alter field works');
569 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1, $options);
571 is($add_field, 'ALTER TABLE `mydb`.`mytable` ADD COLUMN `myfield` VARCHAR(10) NULL', 'Add field works');
573 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2, $options);
574 is($drop_field, 'ALTER TABLE `mydb`.`mytable` DROP COLUMN `myfield`', 'Drop field works');
576 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1, %$options });
577 is($field3_sql, '`myfield` boolean NOT NULL', 'For Mysql >= 4, use boolean type');
578 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22, %$options });
579 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
580 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,$options);
581 is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
589 for my $size (keys %$number_sizes) {
590 my $expected = $number_sizes->{$size};
591 my $number_field = SQL::Translator::Schema::Field->new(
592 name => "numberfield_$expected",
594 data_type => 'number',
602 SQL::Translator::Producer::MySQL::create_field($number_field, $options),
603 "`numberfield_$expected` $expected($size) NULL",
604 "Use $expected for NUMBER types of size $size"
609 for my $size (qw/255 256 65535 65536/) {
610 $varchars->{$size} = SQL::Translator::Schema::Field->new(
613 data_type => 'varchar',
621 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003, %$options }),
622 '`vch_255` varchar(255) NULL',
623 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
626 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0, %$options }),
627 '`vch_255` varchar(255) NULL',
628 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
631 SQL::Translator::Producer::MySQL::create_field($varchars->{255}, $options),
632 '`vch_255` varchar(255) NULL',
633 'VARCHAR(255) is not substituted with TEXT when no version specified',
638 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003, %$options }),
639 '`vch_256` varchar(256) NULL',
640 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
643 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0, %$options }),
644 '`vch_256` text NULL',
645 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
648 SQL::Translator::Producer::MySQL::create_field($varchars->{256}, $options),
649 '`vch_256` text NULL',
650 'VARCHAR(256) is substituted with TEXT when no version specified',
655 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003, %$options }),
656 '`vch_65535` varchar(65535) NULL',
657 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
660 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0, %$options }),
661 '`vch_65535` text NULL',
662 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
665 SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, $options),
666 '`vch_65535` text NULL',
667 'VARCHAR(65535) is substituted with TEXT when no version specified',
672 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003, %$options }),
673 '`vch_65536` text NULL',
674 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
677 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0, %$options }),
678 '`vch_65536` text NULL',
679 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
682 SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, $options),
683 '`vch_65536` text NULL',
684 'VARCHAR(65536) is substituted with TEXT when no version specified',
688 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
689 fields => [qw/id name/],
690 sql => 'SELECT `id`, `name` FROM `my`.`thing`',
692 mysql_definer => 'CURRENT_USER',
693 mysql_algorithm => 'MERGE',
694 mysql_security => 'DEFINER',
696 my $create_opts = { add_replace_view => 1, no_comments => 1, %$options };
697 my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
699 my $view_sql_replace = <<'EOV';
702 DEFINER = CURRENT_USER
704 VIEW `view_foo` ( `id`, `name` ) AS
705 SELECT `id`, `name` FROM `my`.`thing`
708 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
711 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
712 fields => [qw/id name/],
713 sql => 'SELECT `id`, `name` FROM `my`.`thing`',);
714 my $create2_opts = { add_replace_view => 0, no_comments => 1, %$options };
715 my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
716 my $view_sql_noreplace = <<'EOV';
718 VIEW `view_foo` ( `id`, `name` ) AS
719 SELECT `id`, `name` FROM `my`.`thing`
722 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
725 my %extra = $view1->extra;
728 'mysql_algorithm' => 'MERGE',
729 'mysql_definer' => 'CURRENT_USER',
730 'mysql_security' => 'DEFINER'
735 $view1->remove_extra(qw/mysql_definer mysql_security/);
737 my %extra = $view1->extra;
738 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
741 $view1->remove_extra();
743 my %extra = $view1->extra;
744 is_deeply \%extra, {}, 'Extra attributes completely removed';
750 # certain types do not support a size, see also:
751 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
752 for my $type (qw/date time timestamp datetime year/) {
753 my $field = SQL::Translator::Schema::Field->new(
758 default_value => undef,
759 is_auto_increment => 0,
764 my $sql = SQL::Translator::Producer::MySQL::create_field($field, $options);
765 is($sql, "`my$type` $type NULL", "Skip length param for type $type");
771 my $table = SQL::Translator::Schema::Table->new(name => 'table');
773 SQL::Translator::Schema::Field->new( name => 'mypk',
777 default_value => undef,
778 is_auto_increment => 1,
783 my $constraint = $table->add_constraint(fields => ['mypk'], type => 'PRIMARY_KEY');
784 my $options = {quote_table_names => '`'};
785 is(SQL::Translator::Producer::MySQL::alter_drop_constraint($constraint,$options),
786 'ALTER TABLE `table` DROP PRIMARY KEY','valid drop primary key');