3 # Note that the bulk of the testing for the mysql producer is in
4 # 08postgres-to-mysql.t. This test is for additional stuff that can't be tested
5 # using an Oracle schema as source e.g. extra attributes.
15 use Test::Differences;
21 my $yaml_in = <<EOSCHEMA;
29 mysql_collate: latin1_danish_ci
34 data_type: unsigned int
56 mysql_collate: utf8_general_ci
103 name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
109 - reference_table: thing
113 - reference_table: thing
121 "SET foreign_key_checks=0",
123 "DROP TABLE IF EXISTS `thing`",
124 "CREATE TABLE `thing` (
125 `id` unsigned int auto_increment,
127 `swedish_name` varchar(32) character set swe7,
128 `description` text character set utf8 collate utf8_general_ci,
130 UNIQUE `idx_unique_name` (`name`)
131 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
133 "DROP TABLE IF EXISTS `some`.`thing2`",
134 "CREATE TABLE `some`.`thing2` (
138 `bar_set` set('foo', 'bar', 'baz'),
139 INDEX index_1 (`id`),
140 INDEX really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47 (`id`),
143 PRIMARY KEY (`id`, `foo`),
144 CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
145 CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
148 "SET foreign_key_checks=1",
152 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
154 my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
157 $sqlt = SQL::Translator->new(
163 quote_table_names => 1,
164 quote_field_names => 1
167 my $out = $sqlt->translate(data => $yaml_in)
168 or die "Translate error:".$sqlt->error;
169 ok $out ne "", "Produced something!";
170 eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting";
172 my @out = $sqlt->translate(data => $yaml_in)
173 or die "Translate error:".$sqlt->error;
174 is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
176 @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
177 $out = $sqlt->translate(data => $yaml_in)
178 or die "Translate error:".$sqlt->error;
180 @out = $sqlt->translate(data => $yaml_in)
181 or die "Translate error:".$sqlt->error;
182 $mysql_out =~ s/`//g;
183 my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
184 eq_or_diff $out, $mysql_out, "Output looks right without quoting";
185 is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
187 @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
188 @out = $sqlt->translate(data => $yaml_in)
189 or die "Translate error:".$sqlt->error;
190 $out = $sqlt->translate(data => $yaml_in)
191 or die "Translate error:".$sqlt->error;
193 eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
194 is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
197 my $producer = $sqlt->_producer;
198 my $parser = $sqlt->_parser;
200 ###############################################################################
203 my $table = SQL::Translator::Object::Table->new( name => 'mytable');
205 my $field1 = SQL::Translator::Object::Column->new( name => 'myfield',
207 data_type => 'VARCHAR',
209 default_value => undef,
210 is_auto_increment => 0,
215 my $field1_sql = $producer->create_field($field1);
217 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
219 my $field2 = SQL::Translator::Object::Column->new( name => 'myfield',
221 data_type => 'VARCHAR',
223 default_value => undef,
224 is_auto_increment => 0,
229 my $alter_field = $producer->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 = $producer->add_field($field1);
235 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
237 my $drop_field = $producer->drop_field($field2);
238 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
240 my $field3 = SQL::Translator::Object::Column->new( name => 'myfield',
242 data_type => 'boolean',
247 my $field3_sql = $producer->create_field($field3, { mysql_version => 4.1 });
248 is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
249 $field3_sql = $producer->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 = $producer->create_field($field3,);
252 is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
260 for my $size (keys %$number_sizes) {
261 my $expected = $number_sizes->{$size};
262 my $number_field = SQL::Translator::Object::Column->new(
263 name => "numberfield_$expected",
265 data_type => 'number',
273 $producer->create_field($number_field),
274 "numberfield_$expected $expected($size)",
275 "Use $expected for NUMBER types of size $size"
280 for my $size (qw/255 256 65535 65536/) {
281 $varchars->{$size} = SQL::Translator::Object::Column->new(
284 data_type => 'varchar',
292 $producer->create_field($varchars->{255}, { mysql_version => 5.000003 }),
293 'vch_255 varchar(255)',
294 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
297 $producer->create_field($varchars->{255}, { mysql_version => 5.0 }),
298 'vch_255 varchar(255)',
299 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
302 $producer->create_field($varchars->{255}),
303 'vch_255 varchar(255)',
304 'VARCHAR(255) is not substituted with TEXT when no version specified',
309 $producer->create_field($varchars->{256}, { mysql_version => 5.000003 }),
310 'vch_256 varchar(256)',
311 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
314 $producer->create_field($varchars->{256}, { mysql_version => 5.0 }),
316 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
319 $producer->create_field($varchars->{256}),
321 'VARCHAR(256) is substituted with TEXT when no version specified',
326 $producer->create_field($varchars->{65535}, { mysql_version => 5.000003 }),
327 'vch_65535 varchar(65535)',
328 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
331 $producer->create_field($varchars->{65535}, { mysql_version => 5.0 }),
333 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
336 $producer->create_field($varchars->{65535}),
338 'VARCHAR(65535) is substituted with TEXT when no version specified',
343 $producer->create_field($varchars->{65536}, { mysql_version => 5.000003 }),
345 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
348 $producer->create_field($varchars->{65536}, { mysql_version => 5.0 }),
350 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
353 $producer->create_field($varchars->{65536}),
355 'VARCHAR(65536) is substituted with TEXT when no version specified',
360 my $view1 = SQL::Translator::Object::View->new( name => 'view_foo',
361 fields => [qw/id name/],
362 sql => 'SELECT id, name FROM thing',
364 mysql_definer => 'CURRENT_USER',
365 mysql_algorithm => 'MERGE',
366 mysql_security => 'DEFINER',
368 my $create_opts = { add_replace_view => 1, no_comments => 1 };
369 my $view1_sql1 = $producer->create_view($view1, $create_opts);
371 my $view_sql_replace = "CREATE OR REPLACE
373 DEFINER = CURRENT_USER
375 VIEW view_foo ( id, name ) AS (
376 SELECT id, name FROM thing
378 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
381 my $view2 = SQL::Translator::Object::View->new( name => 'view_foo',
382 fields => [qw/id name/],
383 sql => 'SELECT id, name FROM thing',);
384 my $create2_opts = { add_replace_view => 0, no_comments => 1 };
385 my $view1_sql2 = $producer->create_view($view2, $create2_opts);
386 my $view_sql_noreplace = "CREATE
387 VIEW view_foo ( id, name ) AS (
388 SELECT id, name FROM thing
390 is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
393 my %extra = $view1->extra;
396 'mysql_algorithm' => 'MERGE',
397 'mysql_definer' => 'CURRENT_USER',
398 'mysql_security' => 'DEFINER'
403 $view1->remove_extra(qw/mysql_definer mysql_security/);
405 my %extra = $view1->extra;
406 is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
409 $view1->remove_extra();
411 my %extra = $view1->extra;
412 is_deeply \%extra, {}, 'Extra attributes completely removed';
418 # certain types do not support a size, see also:
419 # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
420 for my $type (qw/date time timestamp datetime year/) {
421 my $field = SQL::Translator::Object::Column->new(
426 default_value => undef,
427 is_auto_increment => 0,
432 my $sql = $producer->create_field($field);
433 is($sql, "my$type $type", "Skip length param for type $type");