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
101 - reference_table: thing
105 - reference_table: thing
113 "SET foreign_key_checks=0;\n\n",
115 "DROP TABLE IF EXISTS `thing`;\n",
116 "CREATE TABLE `thing` (
117 `id` unsigned int auto_increment,
119 `swedish_name` varchar(32) CHARACTER SET swe7,
120 `description` text CHARACTER SET utf8 COLLATE utf8_general_ci,
122 UNIQUE `idx_unique_name` (`name`)
123 ) Type=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;\n\n",
125 "DROP TABLE IF EXISTS `thing2`;\n",
126 "CREATE TABLE `thing2` (
132 PRIMARY KEY (`id`, `foo`),
133 CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
134 CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
137 "SET foreign_key_checks=1;\n\n"
141 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
143 my $mysql_out = join("", @stmts_no_drop);
147 $sqlt = SQL::Translator->new(
153 quote_table_names => 1,
154 quote_field_names => 1
157 my $out = $sqlt->translate(\$yaml_in)
158 or die "Translate error:".$sqlt->error;
159 ok $out ne "", "Produced something!";
160 eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting";
162 my @out = $sqlt->translate(\$yaml_in)
163 or die "Translat eerror:".$sqlt->error;
164 is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
167 @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
168 $out = $sqlt->translate(\$yaml_in)
169 or die "Translat eerror:".$sqlt->error;
171 @out = $sqlt->translate(\$yaml_in)
172 or die "Translat eerror:".$sqlt->error;
173 $mysql_out =~ s/`//g;
174 my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
175 eq_or_diff $out, $mysql_out, "Output looks right without quoting";
176 is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
178 @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
179 @out = $sqlt->translate(\$yaml_in)
180 or die "Translat eerror:".$sqlt->error;
181 $out = $sqlt->translate(\$yaml_in)
182 or die "Translat eerror:".$sqlt->error;
184 eq_or_diff $out, join("", @stmts), "Output looks right with DROP TABLEs";
185 is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
188 ###############################################################################
191 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
193 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
195 data_type => 'VARCHAR',
197 default_value => undef,
198 is_auto_increment => 0,
203 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
205 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
207 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
209 data_type => 'VARCHAR',
211 default_value => undef,
212 is_auto_increment => 0,
217 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
219 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
221 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
223 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
225 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
226 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');