t/38-mysql-producer.t

   1 #!/usr/bin/perl -w
   2 #
   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.
   6 #
   7
   8 use strict;
   9 use Test::More;
  10 use Test::Exception;
  11
  12 use Data::Dumper;
  13 use FindBin qw/$Bin/;
  14
  15 use Test::Differences;
  16 use SQL::Translator;
  17
  18 my $sqlt;
  19
  20 {
  21 my $yaml_in = <<EOSCHEMA;
  22 ---
  23 schema:
  24   tables:
  25     thing:
  26       name: thing
  27       extra:
  28         mysql_charset: latin1
  29         mysql_collate: latin1_danish_ci
  30       order: 1
  31       fields:
  32         id:
  33           name: id
  34           data_type: unsigned int
  35           is_primary_key: 1
  36           is_auto_increment: 1
  37           order: 0
  38         name:
  39           name: name
  40           data_type: varchar
  41           size:
  42             - 32
  43           order: 1
  44         swedish_name:
  45           name: swedish_name
  46           data_type: varchar
  47           size: 32
  48           extra:
  49             mysql_charset: swe7
  50           order: 2
  51         description:
  52           name: description
  53           data_type: text
  54           extra:
  55             mysql_charset: utf8
  56             mysql_collate: utf8_general_ci
  57           order: 3
  58       constraints:
  59         - type: UNIQUE
  60           fields:
  61             - name
  62           name: idx_unique_name
  63
  64     thing2:
  65       name: some.thing2
  66       extra:
  67       order: 2
  68       fields:
  69         id:
  70           name: id
  71           data_type: int
  72           is_primary_key: 0
  73           order: 0
  74           is_foreign_key: 1
  75         foo:
  76           name: foo
  77           data_type: int
  78           order: 1
  79           is_not_null: 1
  80         foo2:
  81           name: foo2
  82           data_type: int
  83           order: 2
  84           is_not_null: 1
  85         bar_set:
  86           name: bar_set
  87           data_type: set
  88           order: 3
  89           is_not_null: 1
  90           extra:
  91             list:
  92               - foo
  93               - bar
  94               - baz
  95       indices:
  96         - type: NORMAL
  97           fields:
  98             - id
  99           name: index_1
 100         - type: NORMAL
 101           fields:
 102             - id
 103           name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
 104       constraints:
 105         - type: PRIMARY_KEY
 106           fields:
 107             - id
 108             - foo
 109         - reference_table: thing
 110           type: FOREIGN_KEY
 111           fields: foo
 112           name: fk_thing
 113         - reference_table: thing
 114           type: FOREIGN_KEY
 115           fields: foo2
 116           name: fk_thing
 117
 118 EOSCHEMA
 119
 120 my @stmts = (
 121 "SET foreign_key_checks=0",
 122
 123 "DROP TABLE IF EXISTS `thing`",
 124 "CREATE TABLE `thing` (
 125   `id` unsigned int auto_increment,
 126   `name` varchar(32),
 127   `swedish_name` varchar(32) character set swe7,
 128   `description` text character set utf8 collate utf8_general_ci,
 129   PRIMARY KEY (`id`),
 130   UNIQUE `idx_unique_name` (`name`)
 131 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
 132
 133 "DROP TABLE IF EXISTS `some`.`thing2`",
 134 "CREATE TABLE `some`.`thing2` (
 135   `id` integer,
 136   `foo` integer,
 137   `foo2` integer,
 138   `bar_set` set('foo', 'bar', 'baz'),
 139   INDEX index_1 (`id`),
 140   INDEX really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47 (`id`),
 141   INDEX (`foo`),
 142   INDEX (`foo2`),
 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`)
 146 ) ENGINE=InnoDB",
 147
 148 "SET foreign_key_checks=1",
 149
 150 );
 151
 152 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
 153
 154 my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
 155
 156
 157     $sqlt = SQL::Translator->new(
 158         show_warnings  => 1,
 159         no_comments    => 1,
 160 #        debug          => 1,
 161         from           => "YAML",
 162         to             => "MySQL",
 163         quote_table_names => 1,
 164         quote_field_names => 1
 165     );
 166
 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";
 171
 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";
 175
 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;
 179
 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";
 186
 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;
 192
 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";
 195 }
 196
 197 my $producer = $sqlt->_producer;
 198 my $parser = $sqlt->_parser;
 199
 200 ###############################################################################
 201 # New alter/add subs
 202
 203 my $table = SQL::Translator::Object::Table->new( name => 'mytable');
 204
 205 my $field1 = SQL::Translator::Object::Column->new( name => 'myfield',
 206                                                   table => $table,
 207                                                   data_type => 'VARCHAR',
 208                                                   size => 10,
 209                                                   default_value => undef,
 210                                                   is_auto_increment => 0,
 211                                                   is_nullable => 1,
 212                                                   is_foreign_key => 0,
 213                                                   is_unique => 0 );
 214
 215 my $field1_sql = $producer->create_field($field1);
 216
 217 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
 218
 219 my $field2 = SQL::Translator::Object::Column->new( name      => 'myfield',
 220                                                   table => $table,
 221                                                   data_type => 'VARCHAR',
 222                                                   size      => 25,
 223                                                   default_value => undef,
 224                                                   is_auto_increment => 0,
 225                                                   is_nullable => 0,
 226                                                   is_foreign_key => 0,
 227                                                   is_unique => 0 );
 228
 229 my $alter_field = $producer->alter_field($field1,
 230                                                                 $field2);
 231 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
 232
 233 my $add_field = $producer->add_field($field1);
 234
 235 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
 236
 237 my $drop_field = $producer->drop_field($field2);
 238 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
 239
 240 my $field3 = SQL::Translator::Object::Column->new( name      => 'myfield',
 241                                                   table => $table,
 242                                                   data_type => 'boolean',
 243                                                   is_nullable => 0,
 244                                                   is_foreign_key => 0,
 245                                                   is_unique => 0 );
 246
 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');
 253
 254 my $number_sizes = {
 255     '3, 2' => 'double',
 256     12 => 'bigint',
 257     1 => 'tinyint',
 258     4 => 'int',
 259 };
 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",
 264         table => $table,
 265         data_type => 'number',
 266         size => $size,
 267         is_nullable => 1,
 268         is_foreign_key => 0,
 269         is_unique => 0
 270     );
 271
 272     is(
 273         $producer->create_field($number_field),
 274         "numberfield_$expected $expected($size)",
 275         "Use $expected for NUMBER types of size $size"
 276     );
 277 }
 278
 279 my $varchars;
 280 for my $size (qw/255 256 65535 65536/) {
 281     $varchars->{$size} = SQL::Translator::Object::Column->new(
 282         name => "vch_$size",
 283         table => $table,
 284         data_type => 'varchar',
 285         size => $size,
 286         is_nullable => 1,
 287     );
 288 }
 289
 290
 291 is (
 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'
 295 );
 296 is (
 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'
 300 );
 301 is (
 302     $producer->create_field($varchars->{255}),
 303     'vch_255 varchar(255)',
 304     'VARCHAR(255) is not substituted with TEXT when no version specified',
 305 );
 306
 307
 308 is (
 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'
 312 );
 313 is (
 314     $producer->create_field($varchars->{256}, { mysql_version => 5.0 }),
 315     'vch_256 text',
 316     'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
 317 );
 318 is (
 319     $producer->create_field($varchars->{256}),
 320     'vch_256 text',
 321     'VARCHAR(256) is substituted with TEXT when no version specified',
 322 );
 323
 324
 325 is (
 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'
 329 );
 330 is (
 331     $producer->create_field($varchars->{65535}, { mysql_version => 5.0 }),
 332     'vch_65535 text',
 333     'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
 334 );
 335 is (
 336     $producer->create_field($varchars->{65535}),
 337     'vch_65535 text',
 338     'VARCHAR(65535) is substituted with TEXT when no version specified',
 339 );
 340
 341
 342 is (
 343     $producer->create_field($varchars->{65536}, { mysql_version => 5.000003 }),
 344     'vch_65536 text',
 345     'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
 346 );
 347 is (
 348     $producer->create_field($varchars->{65536}, { mysql_version => 5.0 }),
 349     'vch_65536 text',
 350     'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
 351 );
 352 is (
 353     $producer->create_field($varchars->{65536}),
 354     'vch_65536 text',
 355     'VARCHAR(65536) is substituted with TEXT when no version specified',
 356 );
 357
 358
 359 {
 360   my $view1 = SQL::Translator::Object::View->new( name => 'view_foo',
 361                                                   fields => [qw/id name/],
 362                                                   sql => 'SELECT id, name FROM thing',
 363                                                   extra => {
 364                                                     mysql_definer => 'CURRENT_USER',
 365                                                     mysql_algorithm => 'MERGE',
 366                                                     mysql_security => 'DEFINER',
 367                                                   });
 368   my $create_opts = { add_replace_view => 1, no_comments => 1 };
 369   my $view1_sql1 = $producer->create_view($view1, $create_opts);
 370
 371   my $view_sql_replace = "CREATE OR REPLACE
 372    ALGORITHM = MERGE
 373    DEFINER = CURRENT_USER
 374    SQL SECURITY DEFINER
 375   VIEW view_foo ( id, name ) AS (
 376     SELECT id, name FROM thing
 377   )";
 378   is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
 379
 380
 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
 389   )";
 390   is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
 391
 392   {
 393     my %extra = $view1->extra;
 394     is_deeply \%extra,
 395       {
 396         'mysql_algorithm' => 'MERGE',
 397         'mysql_definer'   => 'CURRENT_USER',
 398         'mysql_security'  => 'DEFINER'
 399       },
 400       'Extra attributes';
 401   }
 402
 403   $view1->remove_extra(qw/mysql_definer mysql_security/);
 404   {
 405     my %extra = $view1->extra;
 406     is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
 407   }
 408
 409   $view1->remove_extra();
 410   {
 411     my %extra = $view1->extra;
 412     is_deeply \%extra, {}, 'Extra attributes completely removed';
 413   }
 414 }
 415
 416 {
 417
 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(
 422             name              => "my$type",
 423             table             => $table,
 424             data_type         => $type,
 425             size              => 10,
 426             default_value     => undef,
 427             is_auto_increment => 0,
 428             is_nullable       => 1,
 429             is_foreign_key    => 0,
 430             is_unique         => 0
 431         );
 432         my $sql = $producer->create_field($field);
 433         is($sql, "my$type $type", "Skip length param for type $type");
 434     }
 435 }
 436
 437 done_testing;