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(6,
  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     thing2:
  71       name: thing2
  72       extra:
  73       order: 2
  74       fields:
  75         id:
  76           name: id
  77           data_type: int
  78           is_primary_key: 0
  79           order: 0
  80           is_foreign_key: 1
  81         foo:
  82           name: foo
  83           data_type: int
  84           order: 1
  85           is_not_null: 1
  86       constraints:
  87         - type: PRIMARY_KEY
  88           fields:
  89             - id
  90             - foo
  91         - reference_table: thing
  92           type: FOREIGN_KEY
  93           fields: foo
  94           name: fk_thing
  95
  96 EOSCHEMA
  97
  98 my $mysql_out = <<EOSQL;
  99 SET foreign_key_checks=0;
 100
 101 CREATE TABLE thing (
 102   id unsigned int auto_increment,
 103   name varchar(32),
 104   swedish_name varchar(32) CHARACTER SET swe7,
 105   description text CHARACTER SET utf8 COLLATE utf8_general_ci,
 106   INDEX (id),
 107   PRIMARY KEY (id)
 108 ) Type=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
 109
 110 CREATE TABLE thing2 (
 111   id integer,
 112   foo integer,
 113   INDEX (id),
 114   INDEX (foo),
 115   PRIMARY KEY (id, foo),
 116   CONSTRAINT thing2_fk_thing FOREIGN KEY (foo) REFERENCES thing (id)
 117 ) Type=InnoDB;
 118
 119 SET foreign_key_checks=1;
 120
 121 EOSQL
 122
 123     my $sqlt;
 124     $sqlt = SQL::Translator->new(
 125         show_warnings  => 1,
 126         no_comments    => 1,
 127 #        debug          => 1,
 128         from           => "YAML",
 129         to             => "MySQL",
 130     );
 131
 132     my $out = $sqlt->translate(\$yaml_in)
 133     or die "Translate error:".$sqlt->error;
 134     ok $out ne ""                 ,"Produced something!";
 135     eq_or_diff $out, $mysql_out   ,"Output looks right";
 136 }
 137
 138 ###############################################################################
 139 # New alter/add subs
 140
 141 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
 142
 143 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
 144                                                   table => $table,
 145                                                   data_type => 'VARCHAR',
 146                                                   size => 10,
 147                                                   default_value => undef,
 148                                                   is_auto_increment => 0,
 149                                                   is_nullable => 1,
 150                                                   is_foreign_key => 0,
 151                                                   is_unique => 0 );
 152
 153 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
 154
 155 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
 156
 157 my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
 158                                                   table => $table,
 159                                                   data_type => 'VARCHAR',
 160                                                   size      => 25,
 161                                                   default_value => undef,
 162                                                   is_auto_increment => 0,
 163                                                   is_nullable => 0,
 164                                                   is_foreign_key => 0,
 165                                                   is_unique => 0 );
 166
 167 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
 168                                                                 $field2);
 169 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
 170
 171 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
 172
 173 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
 174
 175 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
 176 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');