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
111 - reference_table: thing
115 - reference_table: thing
123 "SET foreign_key_checks=0;\n\n",
125 "DROP TABLE IF EXISTS `thing`;\n",
126 "CREATE TABLE `thing` (
127 `id` unsigned int auto_increment,
129 `swedish_name` varchar(32) CHARACTER SET swe7,
130 `description` text CHARACTER SET utf8 COLLATE utf8_general_ci,
132 UNIQUE `idx_unique_name` (`name`)
133 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;\n\n",
135 "DROP TABLE IF EXISTS `thing2`;\n",
136 "CREATE TABLE `thing2` (
140 INDEX index_1 (`id`),
141 INDEX index_2 (`id`),
144 PRIMARY KEY (`id`, `foo`),
145 CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
146 CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
147 ) ENGINE=InnoDB;\n\n",
149 "SET foreign_key_checks=1;\n\n"
153 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
155 my $mysql_out = join("", @stmts_no_drop);
159 $sqlt = SQL::Translator->new(
165 quote_table_names => 1,
166 quote_field_names => 1
169 my $out = $sqlt->translate(\$yaml_in)
170 or die "Translate error:".$sqlt->error;
171 ok $out ne "", "Produced something!";
172 eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting";
174 my @out = $sqlt->translate(\$yaml_in)
175 or die "Translat eerror:".$sqlt->error;
176 is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
179 @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
180 $out = $sqlt->translate(\$yaml_in)
181 or die "Translate error:".$sqlt->error;
183 @out = $sqlt->translate(\$yaml_in)
184 or die "Translate error:".$sqlt->error;
185 $mysql_out =~ s/`//g;
186 my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
187 eq_or_diff $out, $mysql_out, "Output looks right without quoting";
188 is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
190 @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
191 @out = $sqlt->translate(\$yaml_in)
192 or die "Translat eerror:".$sqlt->error;
193 $out = $sqlt->translate(\$yaml_in)
194 or die "Translat eerror:".$sqlt->error;
196 eq_or_diff $out, join("", @stmts), "Output looks right with DROP TABLEs";
197 is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
200 ###############################################################################
203 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
205 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
207 data_type => 'VARCHAR',
209 default_value => undef,
210 is_auto_increment => 0,
215 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
217 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
219 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
221 data_type => 'VARCHAR',
223 default_value => undef,
224 is_auto_increment => 0,
229 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
231 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
233 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
235 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
237 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
238 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
240 my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
242 data_type => 'boolean',
247 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
248 is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
249 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
250 is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
251 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
252 is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');