Added a semicolon at the end of the create trigger definition because SQLite seems...
[dbsrgits/SQL-Translator.git] / 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(11,
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     thing2:
76       name: thing2
77       extra:
78       order: 2
79       fields:
80         id:
81           name: id
82           data_type: int
83           is_primary_key: 0
84           order: 0
85           is_foreign_key: 1
86         foo:
87           name: foo
88           data_type: int
89           order: 1
90           is_not_null: 1
91       constraints:
92         - type: PRIMARY_KEY
93           fields:
94             - id
95             - foo
96         - reference_table: thing
97           type: FOREIGN_KEY
98           fields: foo
99           name: fk_thing
100
101 EOSCHEMA
102
103 my @stmts = (
104 "SET foreign_key_checks=0;\n\n",
105
106 "DROP TABLE IF EXISTS `thing`;\n",
107 "CREATE TABLE `thing` (
108   `id` unsigned int auto_increment,
109   `name` varchar(32),
110   `swedish_name` varchar(32) CHARACTER SET swe7,
111   `description` text CHARACTER SET utf8 COLLATE utf8_general_ci,
112   INDEX (`id`),
113   INDEX (`name`),
114   PRIMARY KEY (`id`),
115   UNIQUE `idx_unique_name` (`name`)
116 ) Type=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;\n\n",
117
118 "DROP TABLE IF EXISTS `thing2`;\n",
119 "CREATE TABLE `thing2` (
120   `id` integer,
121   `foo` integer,
122   INDEX (`id`),
123   INDEX (`foo`),
124   PRIMARY KEY (`id`, `foo`),
125   CONSTRAINT `thing2_fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`)
126 ) Type=InnoDB;\n\n",
127
128 "SET foreign_key_checks=1;\n\n"
129
130 );
131
132 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
133
134 my $mysql_out = join("", @stmts_no_drop);
135
136
137     my $sqlt;
138     $sqlt = SQL::Translator->new(
139         show_warnings  => 1,
140         no_comments    => 1,
141 #        debug          => 1,
142         from           => "YAML",
143         to             => "MySQL",
144         quote_table_names => 1,
145         quote_field_names => 1
146     );
147
148     my $out = $sqlt->translate(\$yaml_in)
149     or die "Translate error:".$sqlt->error;
150     ok $out ne "",                    "Produced something!";
151     eq_or_diff $out, $mysql_out,      "Scalar output looks right with quoting";
152
153     my @out = $sqlt->translate(\$yaml_in)
154       or die "Translat eerror:".$sqlt->error;
155     is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
156
157
158     @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
159     $out = $sqlt->translate(\$yaml_in)
160       or die "Translat eerror:".$sqlt->error;
161
162     @out = $sqlt->translate(\$yaml_in)
163       or die "Translat eerror:".$sqlt->error;
164     $mysql_out =~ s/`//g;
165     my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
166     eq_or_diff $out, $mysql_out,       "Output looks right without quoting";
167     is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
168
169     @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
170     @out = $sqlt->translate(\$yaml_in)
171       or die "Translat eerror:".$sqlt->error;
172     $out = $sqlt->translate(\$yaml_in)
173       or die "Translat eerror:".$sqlt->error;
174
175     eq_or_diff $out, join("", @stmts), "Output looks right with DROP TABLEs";
176     is_deeply \@out, \@stmts,          "Array output looks right with DROP TABLEs";
177 }
178
179 ###############################################################################
180 # New alter/add subs
181
182 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
183
184 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
185                                                   table => $table,
186                                                   data_type => 'VARCHAR',
187                                                   size => 10,
188                                                   default_value => undef,
189                                                   is_auto_increment => 0,
190                                                   is_nullable => 1,
191                                                   is_foreign_key => 0,
192                                                   is_unique => 0 );
193
194 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
195
196 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
197
198 my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
199                                                   table => $table,
200                                                   data_type => 'VARCHAR',
201                                                   size      => 25,
202                                                   default_value => undef,
203                                                   is_auto_increment => 0,
204                                                   is_nullable => 0,
205                                                   is_foreign_key => 0,
206                                                   is_unique => 0 );
207
208 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
209                                                                 $field2);
210 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
211
212 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
213
214 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
215
216 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
217 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');