16151f5681dd4246dcee5f0a46c2ba0583d50e65
[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(35,
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: some.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         bar_set:
98           name: bar_set
99           data_type: set
100           order: 3
101           is_not_null: 1
102           extra:
103             list:
104               - foo
105               - bar
106               - baz
107       indices:
108         - type: NORMAL
109           fields:
110             - id
111           name: index_1
112         - type: NORMAL
113           fields:
114             - id
115           name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
116       constraints:
117         - type: PRIMARY_KEY
118           fields:
119             - id
120             - foo
121         - reference_table: thing
122           type: FOREIGN_KEY
123           fields: foo
124           name: fk_thing
125         - reference_table: thing
126           type: FOREIGN_KEY
127           fields: foo2
128           name: fk_thing
129
130 EOSCHEMA
131
132 my @stmts = (
133 "SET foreign_key_checks=0",
134
135 "DROP TABLE IF EXISTS `thing`",
136 "CREATE TABLE `thing` (
137   `id` unsigned int auto_increment,
138   `name` varchar(32),
139   `swedish_name` varchar(32) character set swe7,
140   `description` text character set utf8 collate utf8_general_ci,
141   PRIMARY KEY (`id`),
142   UNIQUE `idx_unique_name` (`name`)
143 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
144
145 "DROP TABLE IF EXISTS `some`.`thing2`",
146 "CREATE TABLE `some`.`thing2` (
147   `id` integer,
148   `foo` integer,
149   `foo2` integer,
150   `bar_set` set('foo', 'bar', 'baz'),
151   INDEX index_1 (`id`),
152   INDEX really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47 (`id`),
153   INDEX (`foo`),
154   INDEX (`foo2`),
155   PRIMARY KEY (`id`, `foo`),
156   CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
157   CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
158 ) ENGINE=InnoDB",
159
160 "SET foreign_key_checks=1",
161
162 );
163
164 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
165
166 my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
167
168
169     my $sqlt;
170     $sqlt = SQL::Translator->new(
171         show_warnings  => 1,
172         no_comments    => 1,
173 #        debug          => 1,
174         from           => "YAML",
175         to             => "MySQL",
176         quote_table_names => 1,
177         quote_field_names => 1
178     );
179
180     my $out = $sqlt->translate(\$yaml_in)
181     or die "Translate error:".$sqlt->error;
182     ok $out ne "",                    "Produced something!";
183     eq_or_diff $out, $mysql_out,      "Scalar output looks right with quoting";
184
185     my @out = $sqlt->translate(\$yaml_in)
186       or die "Translat eerror:".$sqlt->error;
187     is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
188
189
190     @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
191     $out = $sqlt->translate(\$yaml_in)
192       or die "Translate error:".$sqlt->error;
193
194     @out = $sqlt->translate(\$yaml_in)
195       or die "Translate error:".$sqlt->error;
196     $mysql_out =~ s/`//g;
197     my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
198     eq_or_diff $out, $mysql_out,       "Output looks right without quoting";
199     is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
200
201     @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
202     @out = $sqlt->translate(\$yaml_in)
203       or die "Translat eerror:".$sqlt->error;
204     $out = $sqlt->translate(\$yaml_in)
205       or die "Translat eerror:".$sqlt->error;
206
207     eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
208     is_deeply \@out, \@stmts,          "Array output looks right with DROP TABLEs";
209 }
210
211 ###############################################################################
212 # New alter/add subs
213
214 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
215
216 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
217                                                   table => $table,
218                                                   data_type => 'VARCHAR',
219                                                   size => 10,
220                                                   default_value => undef,
221                                                   is_auto_increment => 0,
222                                                   is_nullable => 1,
223                                                   is_foreign_key => 0,
224                                                   is_unique => 0 );
225
226 my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
227
228 is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
229
230 my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
231                                                   table => $table,
232                                                   data_type => 'VARCHAR',
233                                                   size      => 25,
234                                                   default_value => undef,
235                                                   is_auto_increment => 0,
236                                                   is_nullable => 0,
237                                                   is_foreign_key => 0,
238                                                   is_unique => 0 );
239
240 my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
241                                                                 $field2);
242 is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
243
244 my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
245
246 is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
247
248 my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
249 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
250
251 my $field3 = SQL::Translator::Schema::Field->new( name      => 'myfield',
252                                                   table => $table,
253                                                   data_type => 'boolean',
254                                                   is_nullable => 0,
255                                                   is_foreign_key => 0,
256                                                   is_unique => 0 );
257
258 my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
259 is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
260 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
261 is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
262 $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
263 is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
264
265 my $number_sizes = {
266     '3, 2' => 'double',
267     12 => 'bigint',
268     1 => 'tinyint',
269     4 => 'int',
270 };
271 for my $size (keys %$number_sizes) {
272     my $expected = $number_sizes->{$size};
273     my $number_field = SQL::Translator::Schema::Field->new( 
274         name => "numberfield_$expected",
275         table => $table,
276         data_type => 'number',
277         size => $size,
278         is_nullable => 1,
279         is_foreign_key => 0,
280         is_unique => 0 
281     );
282
283     is(
284         SQL::Translator::Producer::MySQL::create_field($number_field),
285         "numberfield_$expected $expected($size)",
286         "Use $expected for NUMBER types of size $size"
287     );
288 }
289
290 my $varchars;
291 for my $size (qw/255 256 65535 65536/) {
292     $varchars->{$size} = SQL::Translator::Schema::Field->new( 
293         name => "vch_$size",
294         table => $table,
295         data_type => 'varchar',
296         size => $size,
297         is_nullable => 1,
298     );
299 }
300
301
302 is (
303     SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }),
304     'vch_255 varchar(255)', 
305     'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
306 );
307 is (
308     SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }),
309     'vch_255 varchar(255)', 
310     'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
311 );
312 is (
313     SQL::Translator::Producer::MySQL::create_field($varchars->{255}),
314     'vch_255 varchar(255)', 
315     'VARCHAR(255) is not substituted with TEXT when no version specified',
316 );
317
318
319 is (
320     SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }),
321     'vch_256 varchar(256)', 
322     'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
323 );
324 is (
325     SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }),
326     'vch_256 text', 
327     'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
328 );
329 is (
330     SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
331     'vch_256 text', 
332     'VARCHAR(256) is substituted with TEXT when no version specified',
333 );
334
335
336 is (
337     SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }),
338     'vch_65535 varchar(65535)', 
339     'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
340 );
341 is (
342     SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }),
343     'vch_65535 text', 
344     'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
345 );
346 is (
347     SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
348     'vch_65535 text', 
349     'VARCHAR(65535) is substituted with TEXT when no version specified',
350 );
351
352
353 is (
354     SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }),
355     'vch_65536 text', 
356     'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
357 );
358 is (
359     SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }),
360     'vch_65536 text', 
361     'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
362 );
363 is (
364     SQL::Translator::Producer::MySQL::create_field($varchars->{65536}),
365     'vch_65536 text', 
366     'VARCHAR(65536) is substituted with TEXT when no version specified',
367 );
368
369
370 {
371   my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
372                                                   fields => [qw/id name/],
373                                                   sql => 'SELECT id, name FROM thing',
374                                                   extra => {
375                                                     mysql_definer => 'CURRENT_USER',
376                                                     mysql_algorithm => 'MERGE',
377                                                     mysql_security => 'DEFINER',
378                                                   });
379   my $create_opts = { add_replace_view => 1, no_comments => 1 };
380   my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
381
382   my $view_sql_replace = "CREATE OR REPLACE
383    ALGORITHM = MERGE
384    DEFINER = CURRENT_USER
385    SQL SECURITY DEFINER
386   VIEW view_foo ( id, name ) AS (
387     SELECT id, name FROM thing
388   )";
389   is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
390
391
392   my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
393                                                   fields => [qw/id name/],
394                                                   sql => 'SELECT id, name FROM thing',);
395   my $create2_opts = { add_replace_view => 0, no_comments => 1 };
396   my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
397   my $view_sql_noreplace = "CREATE
398   VIEW view_foo ( id, name ) AS (
399     SELECT id, name FROM thing
400   )";
401   is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
402   
403   {
404     my %extra = $view1->extra;
405     is_deeply \%extra,
406       {
407         'mysql_algorithm' => 'MERGE',
408         'mysql_definer'   => 'CURRENT_USER',
409         'mysql_security'  => 'DEFINER'
410       },
411       'Extra attributes';
412   }
413
414   $view1->remove_extra(qw/mysql_definer mysql_security/);
415   {
416     my %extra = $view1->extra;
417     is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
418   }
419
420   $view1->remove_extra();
421   {
422     my %extra = $view1->extra;
423     is_deeply \%extra, {}, 'Extra attributes completely removed';
424   }
425 }