Commit | Line | Data |
819fe9ef |
1 | #!/usr/bin/perl -w |
1ded8513 |
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 { |
5d666b31 |
22 | maybe_plan(32, |
1ded8513 |
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: |
8c4efd11 |
44 | id: |
45 | name: id |
46 | data_type: unsigned int |
47 | is_primary_key: 1 |
48 | is_auto_increment: 1 |
49 | order: 0 |
1ded8513 |
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 |
fe0f47d0 |
70 | constraints: |
71 | - type: UNIQUE |
72 | fields: |
73 | - name |
74 | name: idx_unique_name |
4d438549 |
75 | |
8c4efd11 |
76 | thing2: |
8d693a85 |
77 | name: some.thing2 |
8c4efd11 |
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 |
fb149f81 |
92 | foo2: |
93 | name: foo2 |
94 | data_type: int |
95 | order: 2 |
96 | is_not_null: 1 |
7c1aae02 |
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 |
4d438549 |
107 | indices: |
108 | - type: NORMAL |
7c1aae02 |
109 | fields: |
4d438549 |
110 | - id |
111 | name: index_1 |
112 | - type: NORMAL |
7c1aae02 |
113 | fields: |
4d438549 |
114 | - id |
f5405d47 |
115 | name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa |
8c4efd11 |
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 |
fb149f81 |
125 | - reference_table: thing |
126 | type: FOREIGN_KEY |
127 | fields: foo2 |
128 | name: fk_thing |
8c4efd11 |
129 | |
1ded8513 |
130 | EOSCHEMA |
131 | |
f6af58ae |
132 | my @stmts = ( |
24d9fe69 |
133 | "SET foreign_key_checks=0", |
1ded8513 |
134 | |
24d9fe69 |
135 | "DROP TABLE IF EXISTS `thing`", |
f6af58ae |
136 | "CREATE TABLE `thing` ( |
fe0f47d0 |
137 | `id` unsigned int auto_increment, |
138 | `name` varchar(32), |
f5405d47 |
139 | `swedish_name` varchar(32) character set swe7, |
140 | `description` text character set utf8 collate utf8_general_ci, |
fe0f47d0 |
141 | PRIMARY KEY (`id`), |
142 | UNIQUE `idx_unique_name` (`name`) |
24d9fe69 |
143 | ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci", |
1ded8513 |
144 | |
8d693a85 |
145 | "DROP TABLE IF EXISTS `some`.`thing2`", |
146 | "CREATE TABLE `some`.`thing2` ( |
fe0f47d0 |
147 | `id` integer, |
148 | `foo` integer, |
fb149f81 |
149 | `foo2` integer, |
7c1aae02 |
150 | `bar_set` set('foo', 'bar', 'baz'), |
4d438549 |
151 | INDEX index_1 (`id`), |
f5405d47 |
152 | INDEX really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47 (`id`), |
fe0f47d0 |
153 | INDEX (`foo`), |
fb149f81 |
154 | INDEX (`foo2`), |
fe0f47d0 |
155 | PRIMARY KEY (`id`, `foo`), |
da5a1bae |
156 | CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`), |
157 | CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`) |
24d9fe69 |
158 | ) ENGINE=InnoDB", |
8c4efd11 |
159 | |
24d9fe69 |
160 | "SET foreign_key_checks=1", |
f6af58ae |
161 | |
162 | ); |
163 | |
164 | my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts; |
165 | |
24d9fe69 |
166 | my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n"; |
8c4efd11 |
167 | |
1ded8513 |
168 | |
169 | my $sqlt; |
170 | $sqlt = SQL::Translator->new( |
171 | show_warnings => 1, |
172 | no_comments => 1, |
8c4efd11 |
173 | # debug => 1, |
1ded8513 |
174 | from => "YAML", |
175 | to => "MySQL", |
fe0f47d0 |
176 | quote_table_names => 1, |
177 | quote_field_names => 1 |
1ded8513 |
178 | ); |
179 | |
819fe9ef |
180 | my $out = $sqlt->translate(\$yaml_in) |
181 | or die "Translate error:".$sqlt->error; |
f6af58ae |
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"; |
fe0f47d0 |
188 | |
fe0f47d0 |
189 | |
f6af58ae |
190 | @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0); |
fe0f47d0 |
191 | $out = $sqlt->translate(\$yaml_in) |
4d438549 |
192 | or die "Translate error:".$sqlt->error; |
f6af58ae |
193 | |
194 | @out = $sqlt->translate(\$yaml_in) |
4d438549 |
195 | or die "Translate error:".$sqlt->error; |
fe0f47d0 |
196 | $mysql_out =~ s/`//g; |
f6af58ae |
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 | |
24d9fe69 |
207 | eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs"; |
f6af58ae |
208 | is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs"; |
1ded8513 |
209 | } |
8db4bd9d |
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'); |
ca1f9923 |
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'); |
d28afa66 |
264 | |
5d666b31 |
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( |
75b8fbe8 |
274 | name => "numberfield_$expected", |
5d666b31 |
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), |
75b8fbe8 |
285 | "numberfield_$expected $expected($size)", |
5d666b31 |
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 | |
d28afa66 |
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 |
24d9fe69 |
388 | )"; |
d28afa66 |
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 |
24d9fe69 |
400 | )"; |
d28afa66 |
401 | is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL'); |
402 | } |