Commit | Line | Data |
d4c630fa |
1 | #!/usr/bin/perl -w |
d4c630fa |
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; |
d4c630fa |
11 | |
12 | use Data::Dumper; |
13 | use FindBin qw/$Bin/; |
14 | |
d4c630fa |
15 | use Test::Differences; |
16 | use SQL::Translator; |
17 | |
27218698 |
18 | my $sqlt; |
19 | |
d4c630fa |
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 | |
d4c630fa |
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) |
27218698 |
173 | or die "Translate error:".$sqlt->error; |
d4c630fa |
174 | is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting"; |
175 | |
d4c630fa |
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) |
27218698 |
189 | or die "Translate error:".$sqlt->error; |
d4c630fa |
190 | $out = $sqlt->translate(data => $yaml_in) |
27218698 |
191 | or die "Translate error:".$sqlt->error; |
d4c630fa |
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 | |
27218698 |
197 | my $producer = $sqlt->_producer; |
198 | my $parser = $sqlt->_parser; |
199 | |
d4c630fa |
200 | ############################################################################### |
201 | # New alter/add subs |
202 | |
27218698 |
203 | my $table = SQL::Translator::Object::Table->new( name => 'mytable'); |
d4c630fa |
204 | |
27218698 |
205 | my $field1 = SQL::Translator::Object::Column->new( name => 'myfield', |
d4c630fa |
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 | |
27218698 |
215 | my $field1_sql = $producer->create_field($field1); |
d4c630fa |
216 | |
217 | is($field1_sql, 'myfield VARCHAR(10)', 'Create field works'); |
218 | |
27218698 |
219 | my $field2 = SQL::Translator::Object::Column->new( name => 'myfield', |
d4c630fa |
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 | |
27218698 |
229 | my $alter_field = $producer->alter_field($field1, |
d4c630fa |
230 | $field2); |
231 | is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works'); |
232 | |
27218698 |
233 | my $add_field = $producer->add_field($field1); |
d4c630fa |
234 | |
235 | is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works'); |
236 | |
27218698 |
237 | my $drop_field = $producer->drop_field($field2); |
d4c630fa |
238 | is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works'); |
239 | |
27218698 |
240 | my $field3 = SQL::Translator::Object::Column->new( name => 'myfield', |
d4c630fa |
241 | table => $table, |
242 | data_type => 'boolean', |
243 | is_nullable => 0, |
244 | is_foreign_key => 0, |
245 | is_unique => 0 ); |
246 | |
27218698 |
247 | my $field3_sql = $producer->create_field($field3, { mysql_version => 4.1 }); |
d4c630fa |
248 | is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type'); |
27218698 |
249 | $field3_sql = $producer->create_field($field3, { mysql_version => 3.22 }); |
d4c630fa |
250 | is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type'); |
27218698 |
251 | $field3_sql = $producer->create_field($field3,); |
d4c630fa |
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}; |
27218698 |
262 | my $number_field = SQL::Translator::Object::Column->new( |
d4c630fa |
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( |
27218698 |
273 | $producer->create_field($number_field), |
d4c630fa |
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/) { |
27218698 |
281 | $varchars->{$size} = SQL::Translator::Object::Column->new( |
d4c630fa |
282 | name => "vch_$size", |
283 | table => $table, |
284 | data_type => 'varchar', |
285 | size => $size, |
286 | is_nullable => 1, |
287 | ); |
288 | } |
289 | |
290 | |
291 | is ( |
27218698 |
292 | $producer->create_field($varchars->{255}, { mysql_version => 5.000003 }), |
d4c630fa |
293 | 'vch_255 varchar(255)', |
294 | 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3' |
295 | ); |
296 | is ( |
27218698 |
297 | $producer->create_field($varchars->{255}, { mysql_version => 5.0 }), |
d4c630fa |
298 | 'vch_255 varchar(255)', |
299 | 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3' |
300 | ); |
301 | is ( |
27218698 |
302 | $producer->create_field($varchars->{255}), |
d4c630fa |
303 | 'vch_255 varchar(255)', |
304 | 'VARCHAR(255) is not substituted with TEXT when no version specified', |
305 | ); |
306 | |
307 | |
308 | is ( |
27218698 |
309 | $producer->create_field($varchars->{256}, { mysql_version => 5.000003 }), |
d4c630fa |
310 | 'vch_256 varchar(256)', |
311 | 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3' |
312 | ); |
313 | is ( |
27218698 |
314 | $producer->create_field($varchars->{256}, { mysql_version => 5.0 }), |
d4c630fa |
315 | 'vch_256 text', |
316 | 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3' |
317 | ); |
318 | is ( |
27218698 |
319 | $producer->create_field($varchars->{256}), |
d4c630fa |
320 | 'vch_256 text', |
321 | 'VARCHAR(256) is substituted with TEXT when no version specified', |
322 | ); |
323 | |
324 | |
325 | is ( |
27218698 |
326 | $producer->create_field($varchars->{65535}, { mysql_version => 5.000003 }), |
d4c630fa |
327 | 'vch_65535 varchar(65535)', |
328 | 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3' |
329 | ); |
330 | is ( |
27218698 |
331 | $producer->create_field($varchars->{65535}, { mysql_version => 5.0 }), |
d4c630fa |
332 | 'vch_65535 text', |
333 | 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3' |
334 | ); |
335 | is ( |
27218698 |
336 | $producer->create_field($varchars->{65535}), |
d4c630fa |
337 | 'vch_65535 text', |
338 | 'VARCHAR(65535) is substituted with TEXT when no version specified', |
339 | ); |
340 | |
341 | |
342 | is ( |
27218698 |
343 | $producer->create_field($varchars->{65536}, { mysql_version => 5.000003 }), |
d4c630fa |
344 | 'vch_65536 text', |
345 | 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3' |
346 | ); |
347 | is ( |
27218698 |
348 | $producer->create_field($varchars->{65536}, { mysql_version => 5.0 }), |
d4c630fa |
349 | 'vch_65536 text', |
350 | 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3' |
351 | ); |
352 | is ( |
27218698 |
353 | $producer->create_field($varchars->{65536}), |
d4c630fa |
354 | 'vch_65536 text', |
355 | 'VARCHAR(65536) is substituted with TEXT when no version specified', |
356 | ); |
357 | |
358 | |
359 | { |
27218698 |
360 | my $view1 = SQL::Translator::Object::View->new( name => 'view_foo', |
d4c630fa |
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 }; |
27218698 |
369 | my $view1_sql1 = $producer->create_view($view1, $create_opts); |
d4c630fa |
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 | |
27218698 |
381 | my $view2 = SQL::Translator::Object::View->new( name => 'view_foo', |
d4c630fa |
382 | fields => [qw/id name/], |
383 | sql => 'SELECT id, name FROM thing',); |
384 | my $create2_opts = { add_replace_view => 0, no_comments => 1 }; |
27218698 |
385 | my $view1_sql2 = $producer->create_view($view2, $create2_opts); |
d4c630fa |
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/) { |
27218698 |
421 | my $field = SQL::Translator::Object::Column->new( |
d4c630fa |
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 | ); |
27218698 |
432 | my $sql = $producer->create_field($field); |
d4c630fa |
433 | is($sql, "my$type $type", "Skip length param for type $type"); |
434 | } |
435 | } |
436 | |
437 | done_testing; |