t/38-mysql-producer.t

   1 #!/usr/bin/perl -w
   2 # vim:filetype=perl
   3
   4 #
   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.
   8 #
   9
  10 use strict;
  11 use Test::More;
  12 use Test::Exception;
  13 use Test::SQL::Translator qw(maybe_plan);
  14
  15 use Data::Dumper;
  16 use FindBin qw/$Bin/;
  17
  18 # Testing 1,2,3,4...
  19 #=============================================================================
  20
  21 BEGIN {
  22     maybe_plan(14,
  23         'YAML',
  24         'SQL::Translator::Producer::MySQL',
  25         'Test::Differences',
  26     )
  27 }
  28 use Test::Differences;
  29 use SQL::Translator;
  30
  31 # Main test.
  32 {
  33 my $yaml_in = <<EOSCHEMA;
  34 ---
  35 schema:
  36   tables:
  37     thing:
  38       name: thing
  39       extra:
  40         mysql_charset: latin1
  41         mysql_collate: latin1_danish_ci
  42       order: 1
  43       fields:
  44         id:
  45           name: id
  46           data_type: unsigned int
  47           is_primary_key: 1
  48           is_auto_increment: 1
  49           order: 0
  50         name:
  51           name: name
  52           data_type: varchar
  53           size:
  54             - 32
  55           order: 1
  56         swedish_name:
  57           name: swedish_name
  58           data_type: varchar
  59           size: 32
  60           extra:
  61             mysql_charset: swe7
  62           order: 2
  63         description:
  64           name: description
  65           data_type: text
  66           extra:
  67             mysql_charset: utf8
  68             mysql_collate: utf8_general_ci
  69           order: 3
  70       constraints:
  71         - type: UNIQUE
  72           fields:
  73             - name
  74           name: idx_unique_name
  75
  76     thing2:
  77       name: thing2
  78       extra:
  79       order: 2
  80       fields:
  81         id:
  82           name: id
  83           data_type: int
  84           is_primary_key: 0
  85           order: 0
  86           is_foreign_key: 1
  87         foo:
  88           name: foo
  89           data_type: int
  90           order: 1
  91           is_not_null: 1
  92         foo2:
  93           name: foo2
  94           data_type: int
  95           order: 2
  96           is_not_null: 1
  97       indices:
  98         - type: NORMAL
  99           fields:
 100             - id
 101           name: index_1
 102         - type: NORMAL
 103           fields:
 104             - id
 105           name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
 106       constraints:
 107         - type: PRIMARY_KEY
 108           fields:
 109             - id
 110             - foo
 111         - reference_table: thing
 112           type: FOREIGN_KEY
 113           fields: foo
 114           name: fk_thing
 115         - reference_table: thing
 116           type: FOREIGN_KEY
 117           fields: foo2
 118           name: fk_thing
 119
 120 EOSCHEMA
 121
 122 my @stmts = (
 123 "SET foreign_key_checks=0;\n\n",
 124
 125 "DROP TABLE IF EXISTS `thing`;\n",
 126 "CREATE TABLE `thing` (
 127   `id` unsigned int auto_increment,
 128   `name` varchar(32),
 129   `swedish_name` varchar(32) character set swe7,
 130   `description` text character set utf8 collate utf8_general_ci,
 131   PRIMARY KEY (`id`),
 132   UNIQUE `idx_unique_name` (`name`)
 133 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;\n\n",
 134
 135 "DROP TABLE IF EXISTS `thing2`;\n",
 136 "CREATE TABLE `thing2` (
 137   `id` integer,
 138   `foo` integer,
 139   `foo2` integer,
 140   INDEX index_1 (`id`),
 141   INDEX really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47 (`id`),
 142   INDEX (`foo`),
 143   INDEX (`foo2`),
 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",
 148
 149 "SET foreign_key_checks=1;\n\n"
 150
 151 );
 152
 153 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
 154
 155 my $mysql_out = join("", @stmts_no_drop);
 156
 157
 158     my $sqlt;
 159     $sqlt = SQL::Translator->new(
 160         show_warnings  => 1,
 161         no_comments    => 1,
 162 #        debug          => 1,
 163         from           => "YAML",
 164         to             => "MySQL",
 165         quote_table_names => 1,
 166         quote_field_names => 1
 167     );
 168
 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";
 173
 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";
 177
 178
 179     @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
 180     $out = $sqlt->translate(\$yaml_in)
 181       or die "Translate error:".$sqlt->error;
 182
 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";
 189
 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;
 195
 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";
 198 }
 199
 200 ###############################################################################
 201 # New alter/add subs
 202
 203 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
 204
 205 my $field1 = SQL::Translator::Schema::Field->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 = SQL::Translator::Producer::MySQL::create_field($field1);
 216
 217 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
 218
 219 my $field2 = SQL::Translator::Schema::Field->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 = SQL::Translator::Producer::MySQL::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 = SQL::Translator::Producer::MySQL::add_field($field1);
 234
 235 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
 236
 237 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
 238 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
 239
 240 my $field3 = SQL::Translator::Schema::Field->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 = 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');