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 { |
b62fa492 |
22 | maybe_plan(73, |
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: |
aee4b66e |
40 | mysql_charset: latin1 |
41 | mysql_collate: latin1_danish_ci |
1ded8513 |
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 |
807290c3 |
49 | order: 1 |
1ded8513 |
50 | name: |
51 | name: name |
52 | data_type: varchar |
53 | size: |
54 | - 32 |
807290c3 |
55 | order: 2 |
1ded8513 |
56 | swedish_name: |
57 | name: swedish_name |
58 | data_type: varchar |
59 | size: 32 |
60 | extra: |
61 | mysql_charset: swe7 |
807290c3 |
62 | order: 3 |
1ded8513 |
63 | description: |
64 | name: description |
65 | data_type: text |
66 | extra: |
67 | mysql_charset: utf8 |
68 | mysql_collate: utf8_general_ci |
807290c3 |
69 | order: 4 |
c86d295b |
70 | indices: |
71 | - type: NORMAL |
72 | fields: |
73 | - name |
74 | name: index_on_prefix_1 |
75 | options: |
76 | prefix_length: |
77 | name: 10 |
78 | - type: NORMAL |
79 | fields: |
80 | - id |
81 | - name |
82 | - swedish_name |
83 | name: index_on_prefix_2 |
84 | options: |
85 | prefix_length: |
86 | name: 10 |
87 | swedish_name: 20 |
fe0f47d0 |
88 | constraints: |
89 | - type: UNIQUE |
90 | fields: |
91 | - name |
92 | name: idx_unique_name |
4d438549 |
93 | |
8c4efd11 |
94 | thing2: |
8d693a85 |
95 | name: some.thing2 |
8c4efd11 |
96 | extra: |
97 | order: 2 |
98 | fields: |
99 | id: |
100 | name: id |
101 | data_type: int |
102 | is_primary_key: 0 |
807290c3 |
103 | order: 1 |
8c4efd11 |
104 | is_foreign_key: 1 |
105 | foo: |
106 | name: foo |
107 | data_type: int |
807290c3 |
108 | order: 2 |
8c4efd11 |
109 | is_not_null: 1 |
fb149f81 |
110 | foo2: |
111 | name: foo2 |
112 | data_type: int |
807290c3 |
113 | order: 3 |
fb149f81 |
114 | is_not_null: 1 |
7c1aae02 |
115 | bar_set: |
116 | name: bar_set |
117 | data_type: set |
807290c3 |
118 | order: 4 |
7c1aae02 |
119 | is_not_null: 1 |
120 | extra: |
121 | list: |
122 | - foo |
123 | - bar |
124 | - baz |
4d438549 |
125 | indices: |
126 | - type: NORMAL |
7c1aae02 |
127 | fields: |
4d438549 |
128 | - id |
129 | name: index_1 |
130 | - type: NORMAL |
7c1aae02 |
131 | fields: |
4d438549 |
132 | - id |
f5405d47 |
133 | name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa |
8c4efd11 |
134 | constraints: |
135 | - type: PRIMARY_KEY |
136 | fields: |
137 | - id |
138 | - foo |
139 | - reference_table: thing |
140 | type: FOREIGN_KEY |
141 | fields: foo |
142 | name: fk_thing |
fb149f81 |
143 | - reference_table: thing |
144 | type: FOREIGN_KEY |
145 | fields: foo2 |
146 | name: fk_thing |
8c4efd11 |
147 | |
2c098ea5 |
148 | thing3: |
149 | name: some.thing3 |
150 | extra: |
6a12468d |
151 | order: 3 |
2c098ea5 |
152 | fields: |
153 | id: |
154 | name: id |
155 | data_type: int |
156 | is_primary_key: 0 |
807290c3 |
157 | order: 1 |
2c098ea5 |
158 | is_foreign_key: 1 |
159 | foo: |
160 | name: foo |
161 | data_type: int |
807290c3 |
162 | order: 2 |
2c098ea5 |
163 | is_not_null: 1 |
164 | foo2: |
165 | name: foo2 |
166 | data_type: int |
807290c3 |
167 | order: 3 |
2c098ea5 |
168 | is_not_null: 1 |
169 | bar_set: |
170 | name: bar_set |
171 | data_type: set |
807290c3 |
172 | order: 4 |
2c098ea5 |
173 | is_not_null: 1 |
174 | extra: |
175 | list: |
176 | - foo |
177 | - bar |
178 | - baz |
179 | indices: |
180 | - type: NORMAL |
181 | fields: |
182 | - id |
183 | name: index_1 |
184 | - type: NORMAL |
185 | fields: |
186 | - id |
187 | name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa |
188 | constraints: |
189 | - type: PRIMARY_KEY |
190 | fields: |
191 | - id |
192 | - foo |
193 | - reference_table: some.thing2 |
194 | type: FOREIGN_KEY |
195 | fields: foo |
196 | name: fk_thing |
197 | - reference_table: some.thing2 |
198 | type: FOREIGN_KEY |
199 | fields: foo2 |
200 | name: fk_thing |
1ded8513 |
201 | EOSCHEMA |
202 | |
f6af58ae |
203 | my @stmts = ( |
24d9fe69 |
204 | "SET foreign_key_checks=0", |
1ded8513 |
205 | |
24d9fe69 |
206 | "DROP TABLE IF EXISTS `thing`", |
f6af58ae |
207 | "CREATE TABLE `thing` ( |
a14ab50e |
208 | `id` unsigned int NOT NULL auto_increment, |
ad071409 |
209 | `name` varchar(32) NULL, |
210 | `swedish_name` varchar(32) character set swe7 NULL, |
211 | `description` text character set utf8 collate utf8_general_ci NULL, |
c86d295b |
212 | INDEX `index_on_prefix_1` (`name`(10)), |
213 | INDEX `index_on_prefix_2` (`id`, `name`(10), `swedish_name`(20)), |
fe0f47d0 |
214 | PRIMARY KEY (`id`), |
215 | UNIQUE `idx_unique_name` (`name`) |
24d9fe69 |
216 | ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci", |
1ded8513 |
217 | |
8d693a85 |
218 | "DROP TABLE IF EXISTS `some`.`thing2`", |
219 | "CREATE TABLE `some`.`thing2` ( |
a14ab50e |
220 | `id` integer NOT NULL, |
221 | `foo` integer NOT NULL, |
ad071409 |
222 | `foo2` integer NULL, |
223 | `bar_set` set('foo', 'bar', 'baz') NULL, |
20476859 |
224 | INDEX `index_1` (`id`), |
225 | INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`), |
fe0f47d0 |
226 | INDEX (`foo`), |
fb149f81 |
227 | INDEX (`foo2`), |
fe0f47d0 |
228 | PRIMARY KEY (`id`, `foo`), |
da5a1bae |
229 | CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`), |
230 | CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`) |
24d9fe69 |
231 | ) ENGINE=InnoDB", |
8c4efd11 |
232 | |
2c098ea5 |
233 | "DROP TABLE IF EXISTS `some`.`thing3`", |
234 | "CREATE TABLE `some`.`thing3` ( |
a14ab50e |
235 | `id` integer NOT NULL, |
236 | `foo` integer NOT NULL, |
ad071409 |
237 | `foo2` integer NULL, |
238 | `bar_set` set('foo', 'bar', 'baz') NULL, |
2c098ea5 |
239 | INDEX `index_1` (`id`), |
240 | INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`), |
241 | INDEX (`foo`), |
242 | INDEX (`foo2`), |
243 | PRIMARY KEY (`id`, `foo`), |
244 | CONSTRAINT `fk_thing_2` FOREIGN KEY (`foo`) REFERENCES `some`.`thing2` (`id`, `foo`), |
245 | CONSTRAINT `fk_thing_3` FOREIGN KEY (`foo2`) REFERENCES `some`.`thing2` (`id`, `foo`) |
246 | ) ENGINE=InnoDB", |
247 | |
24d9fe69 |
248 | "SET foreign_key_checks=1", |
f6af58ae |
249 | |
250 | ); |
251 | |
252 | my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts; |
253 | |
24d9fe69 |
254 | my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n"; |
8c4efd11 |
255 | |
1ded8513 |
256 | |
257 | my $sqlt; |
258 | $sqlt = SQL::Translator->new( |
259 | show_warnings => 1, |
260 | no_comments => 1, |
8c4efd11 |
261 | # debug => 1, |
1ded8513 |
262 | from => "YAML", |
263 | to => "MySQL", |
fe0f47d0 |
264 | quote_table_names => 1, |
265 | quote_field_names => 1 |
1ded8513 |
266 | ); |
267 | |
819fe9ef |
268 | my $out = $sqlt->translate(\$yaml_in) |
269 | or die "Translate error:".$sqlt->error; |
f6af58ae |
270 | ok $out ne "", "Produced something!"; |
271 | eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting"; |
272 | |
273 | my @out = $sqlt->translate(\$yaml_in) |
274 | or die "Translat eerror:".$sqlt->error; |
275 | is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting"; |
fe0f47d0 |
276 | |
9683e26b |
277 | $sqlt->quote_identifiers(0); |
fe0f47d0 |
278 | |
279 | $out = $sqlt->translate(\$yaml_in) |
4d438549 |
280 | or die "Translate error:".$sqlt->error; |
f6af58ae |
281 | |
282 | @out = $sqlt->translate(\$yaml_in) |
4d438549 |
283 | or die "Translate error:".$sqlt->error; |
fe0f47d0 |
284 | $mysql_out =~ s/`//g; |
f6af58ae |
285 | my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop; |
286 | eq_or_diff $out, $mysql_out, "Output looks right without quoting"; |
287 | is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting"; |
288 | |
9683e26b |
289 | $sqlt->quote_identifiers(1); |
290 | $sqlt->add_drop_table(1); |
291 | |
f6af58ae |
292 | @out = $sqlt->translate(\$yaml_in) |
293 | or die "Translat eerror:".$sqlt->error; |
294 | $out = $sqlt->translate(\$yaml_in) |
295 | or die "Translat eerror:".$sqlt->error; |
296 | |
24d9fe69 |
297 | eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs"; |
f6af58ae |
298 | is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs"; |
1ded8513 |
299 | } |
8db4bd9d |
300 | |
301 | ############################################################################### |
302 | # New alter/add subs |
303 | |
ab15e426 |
304 | { |
8db4bd9d |
305 | my $table = SQL::Translator::Schema::Table->new( name => 'mytable'); |
306 | |
307 | my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', |
308 | table => $table, |
309 | data_type => 'VARCHAR', |
310 | size => 10, |
311 | default_value => undef, |
312 | is_auto_increment => 0, |
313 | is_nullable => 1, |
314 | is_foreign_key => 0, |
315 | is_unique => 0 ); |
316 | |
317 | my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1); |
318 | |
ad071409 |
319 | is($field1_sql, 'myfield VARCHAR(10) NULL', 'Create field works'); |
8db4bd9d |
320 | |
321 | my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', |
322 | table => $table, |
323 | data_type => 'VARCHAR', |
324 | size => 25, |
325 | default_value => undef, |
326 | is_auto_increment => 0, |
327 | is_nullable => 0, |
328 | is_foreign_key => 0, |
329 | is_unique => 0 ); |
330 | |
331 | my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, |
332 | $field2); |
333 | is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works'); |
334 | |
335 | my $add_field = SQL::Translator::Producer::MySQL::add_field($field1); |
336 | |
ad071409 |
337 | is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10) NULL', 'Add field works'); |
8db4bd9d |
338 | |
339 | my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2); |
340 | is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works'); |
ca1f9923 |
341 | |
342 | my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield', |
343 | table => $table, |
344 | data_type => 'boolean', |
345 | is_nullable => 0, |
346 | is_foreign_key => 0, |
347 | is_unique => 0 ); |
348 | |
349 | my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 }); |
350 | is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type'); |
351 | $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 }); |
352 | is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type'); |
353 | $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,); |
354 | is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type'); |
d28afa66 |
355 | |
5d666b31 |
356 | my $number_sizes = { |
357 | '3, 2' => 'double', |
358 | 12 => 'bigint', |
359 | 1 => 'tinyint', |
360 | 4 => 'int', |
361 | }; |
362 | for my $size (keys %$number_sizes) { |
363 | my $expected = $number_sizes->{$size}; |
aee4b66e |
364 | my $number_field = SQL::Translator::Schema::Field->new( |
75b8fbe8 |
365 | name => "numberfield_$expected", |
5d666b31 |
366 | table => $table, |
367 | data_type => 'number', |
368 | size => $size, |
369 | is_nullable => 1, |
370 | is_foreign_key => 0, |
aee4b66e |
371 | is_unique => 0 |
5d666b31 |
372 | ); |
373 | |
374 | is( |
375 | SQL::Translator::Producer::MySQL::create_field($number_field), |
ad071409 |
376 | "numberfield_$expected $expected($size) NULL", |
5d666b31 |
377 | "Use $expected for NUMBER types of size $size" |
378 | ); |
379 | } |
380 | |
381 | my $varchars; |
382 | for my $size (qw/255 256 65535 65536/) { |
aee4b66e |
383 | $varchars->{$size} = SQL::Translator::Schema::Field->new( |
5d666b31 |
384 | name => "vch_$size", |
385 | table => $table, |
386 | data_type => 'varchar', |
387 | size => $size, |
388 | is_nullable => 1, |
389 | ); |
390 | } |
391 | |
392 | |
393 | is ( |
394 | SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }), |
ad071409 |
395 | 'vch_255 varchar(255) NULL', |
5d666b31 |
396 | 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3' |
397 | ); |
398 | is ( |
399 | SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }), |
ad071409 |
400 | 'vch_255 varchar(255) NULL', |
5d666b31 |
401 | 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3' |
402 | ); |
403 | is ( |
404 | SQL::Translator::Producer::MySQL::create_field($varchars->{255}), |
ad071409 |
405 | 'vch_255 varchar(255) NULL', |
5d666b31 |
406 | 'VARCHAR(255) is not substituted with TEXT when no version specified', |
407 | ); |
408 | |
409 | |
410 | is ( |
411 | SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }), |
ad071409 |
412 | 'vch_256 varchar(256) NULL', |
5d666b31 |
413 | 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3' |
414 | ); |
415 | is ( |
416 | SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }), |
ad071409 |
417 | 'vch_256 text NULL', |
5d666b31 |
418 | 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3' |
419 | ); |
420 | is ( |
421 | SQL::Translator::Producer::MySQL::create_field($varchars->{256}), |
ad071409 |
422 | 'vch_256 text NULL', |
5d666b31 |
423 | 'VARCHAR(256) is substituted with TEXT when no version specified', |
424 | ); |
425 | |
426 | |
427 | is ( |
428 | SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }), |
ad071409 |
429 | 'vch_65535 varchar(65535) NULL', |
5d666b31 |
430 | 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3' |
431 | ); |
432 | is ( |
433 | SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }), |
ad071409 |
434 | 'vch_65535 text NULL', |
5d666b31 |
435 | 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3' |
436 | ); |
437 | is ( |
438 | SQL::Translator::Producer::MySQL::create_field($varchars->{65535}), |
ad071409 |
439 | 'vch_65535 text NULL', |
5d666b31 |
440 | 'VARCHAR(65535) is substituted with TEXT when no version specified', |
441 | ); |
442 | |
443 | |
444 | is ( |
445 | SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }), |
ad071409 |
446 | 'vch_65536 text NULL', |
5d666b31 |
447 | 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3' |
448 | ); |
449 | is ( |
450 | SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }), |
ad071409 |
451 | 'vch_65536 text NULL', |
5d666b31 |
452 | 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3' |
453 | ); |
454 | is ( |
455 | SQL::Translator::Producer::MySQL::create_field($varchars->{65536}), |
ad071409 |
456 | 'vch_65536 text NULL', |
5d666b31 |
457 | 'VARCHAR(65536) is substituted with TEXT when no version specified', |
458 | ); |
459 | |
460 | |
d28afa66 |
461 | { |
462 | my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo', |
463 | fields => [qw/id name/], |
464 | sql => 'SELECT id, name FROM thing', |
465 | extra => { |
466 | mysql_definer => 'CURRENT_USER', |
467 | mysql_algorithm => 'MERGE', |
468 | mysql_security => 'DEFINER', |
469 | }); |
470 | my $create_opts = { add_replace_view => 1, no_comments => 1 }; |
471 | my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts); |
472 | |
1c8ec56e |
473 | my $view_sql_replace = <<'EOV'; |
474 | CREATE OR REPLACE |
d28afa66 |
475 | ALGORITHM = MERGE |
476 | DEFINER = CURRENT_USER |
477 | SQL SECURITY DEFINER |
1c8ec56e |
478 | VIEW view_foo ( id, name ) AS |
d28afa66 |
479 | SELECT id, name FROM thing |
1c8ec56e |
480 | EOV |
481 | |
d28afa66 |
482 | is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL'); |
483 | |
484 | |
485 | my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo', |
486 | fields => [qw/id name/], |
487 | sql => 'SELECT id, name FROM thing',); |
488 | my $create2_opts = { add_replace_view => 0, no_comments => 1 }; |
489 | my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts); |
1c8ec56e |
490 | my $view_sql_noreplace = <<'EOV'; |
491 | CREATE |
492 | VIEW view_foo ( id, name ) AS |
d28afa66 |
493 | SELECT id, name FROM thing |
1c8ec56e |
494 | EOV |
495 | |
d28afa66 |
496 | is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL'); |
1c8ec56e |
497 | |
12018c09 |
498 | { |
499 | my %extra = $view1->extra; |
500 | is_deeply \%extra, |
501 | { |
502 | 'mysql_algorithm' => 'MERGE', |
503 | 'mysql_definer' => 'CURRENT_USER', |
504 | 'mysql_security' => 'DEFINER' |
505 | }, |
506 | 'Extra attributes'; |
507 | } |
508 | |
509 | $view1->remove_extra(qw/mysql_definer mysql_security/); |
510 | { |
511 | my %extra = $view1->extra; |
512 | is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call'; |
513 | } |
514 | |
515 | $view1->remove_extra(); |
516 | { |
517 | my %extra = $view1->extra; |
518 | is_deeply \%extra, {}, 'Extra attributes completely removed'; |
519 | } |
d28afa66 |
520 | } |
de176728 |
521 | |
522 | { |
523 | |
524 | # certain types do not support a size, see also: |
525 | # http://dev.mysql.com/doc/refman/5.1/de/create-table.html |
526 | for my $type (qw/date time timestamp datetime year/) { |
527 | my $field = SQL::Translator::Schema::Field->new( |
528 | name => "my$type", |
529 | table => $table, |
530 | data_type => $type, |
531 | size => 10, |
532 | default_value => undef, |
533 | is_auto_increment => 0, |
534 | is_nullable => 1, |
535 | is_foreign_key => 0, |
536 | is_unique => 0 |
537 | ); |
538 | my $sql = SQL::Translator::Producer::MySQL::create_field($field); |
ad071409 |
539 | is($sql, "my$type $type NULL", "Skip length param for type $type"); |
de176728 |
540 | } |
541 | } |
ab15e426 |
542 | |
543 | } #non quoted test |
544 | |
545 | { |
546 | #Quoted test |
547 | my $table = SQL::Translator::Schema::Table->new( name => 'mydb.mytable'); |
548 | |
549 | my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', |
550 | table => $table, |
551 | data_type => 'VARCHAR', |
552 | size => 10, |
553 | default_value => undef, |
554 | is_auto_increment => 0, |
555 | is_nullable => 1, |
556 | is_foreign_key => 0, |
557 | is_unique => 0 ); |
558 | |
559 | |
560 | my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', |
561 | table => $table, |
562 | data_type => 'VARCHAR', |
563 | size => 25, |
564 | default_value => undef, |
565 | is_auto_increment => 0, |
566 | is_nullable => 0, |
567 | is_foreign_key => 0, |
568 | is_unique => 0 ); |
569 | |
570 | my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield', |
571 | table => $table, |
572 | data_type => 'boolean', |
573 | is_nullable => 0, |
574 | is_foreign_key => 0, |
575 | is_unique => 0 ); |
576 | |
577 | |
578 | my $qt = '`'; |
579 | my $qf = '`'; |
580 | my $options = { |
581 | quote_table_names => $qt, |
582 | quote_field_names => $qf, |
583 | }; |
584 | |
aee4b66e |
585 | |
ab15e426 |
586 | my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, $field2, $options); |
587 | is($alter_field, 'ALTER TABLE `mydb`.`mytable` CHANGE COLUMN `myfield` `myfield` VARCHAR(25) NOT NULL', 'Alter field works'); |
588 | |
589 | my $add_field = SQL::Translator::Producer::MySQL::add_field($field1, $options); |
590 | |
ad071409 |
591 | is($add_field, 'ALTER TABLE `mydb`.`mytable` ADD COLUMN `myfield` VARCHAR(10) NULL', 'Add field works'); |
ab15e426 |
592 | |
593 | my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2, $options); |
594 | is($drop_field, 'ALTER TABLE `mydb`.`mytable` DROP COLUMN `myfield`', 'Drop field works'); |
595 | |
596 | my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1, %$options }); |
597 | is($field3_sql, '`myfield` boolean NOT NULL', 'For Mysql >= 4, use boolean type'); |
598 | $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22, %$options }); |
599 | is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type'); |
600 | $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,$options); |
601 | is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type'); |
602 | |
603 | my $number_sizes = { |
604 | '3, 2' => 'double', |
605 | 12 => 'bigint', |
606 | 1 => 'tinyint', |
607 | 4 => 'int', |
608 | }; |
609 | for my $size (keys %$number_sizes) { |
610 | my $expected = $number_sizes->{$size}; |
aee4b66e |
611 | my $number_field = SQL::Translator::Schema::Field->new( |
ab15e426 |
612 | name => "numberfield_$expected", |
613 | table => $table, |
614 | data_type => 'number', |
615 | size => $size, |
616 | is_nullable => 1, |
617 | is_foreign_key => 0, |
aee4b66e |
618 | is_unique => 0 |
ab15e426 |
619 | ); |
620 | |
621 | is( |
622 | SQL::Translator::Producer::MySQL::create_field($number_field, $options), |
ad071409 |
623 | "`numberfield_$expected` $expected($size) NULL", |
ab15e426 |
624 | "Use $expected for NUMBER types of size $size" |
625 | ); |
626 | } |
627 | |
628 | my $varchars; |
629 | for my $size (qw/255 256 65535 65536/) { |
aee4b66e |
630 | $varchars->{$size} = SQL::Translator::Schema::Field->new( |
ab15e426 |
631 | name => "vch_$size", |
632 | table => $table, |
633 | data_type => 'varchar', |
634 | size => $size, |
635 | is_nullable => 1, |
636 | ); |
637 | } |
638 | |
639 | |
640 | is ( |
641 | SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003, %$options }), |
ad071409 |
642 | '`vch_255` varchar(255) NULL', |
ab15e426 |
643 | 'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3' |
644 | ); |
645 | is ( |
646 | SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0, %$options }), |
ad071409 |
647 | '`vch_255` varchar(255) NULL', |
ab15e426 |
648 | 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3' |
649 | ); |
650 | is ( |
651 | SQL::Translator::Producer::MySQL::create_field($varchars->{255}, $options), |
ad071409 |
652 | '`vch_255` varchar(255) NULL', |
ab15e426 |
653 | 'VARCHAR(255) is not substituted with TEXT when no version specified', |
654 | ); |
655 | |
656 | |
657 | is ( |
658 | SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003, %$options }), |
ad071409 |
659 | '`vch_256` varchar(256) NULL', |
ab15e426 |
660 | 'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3' |
661 | ); |
662 | is ( |
663 | SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0, %$options }), |
ad071409 |
664 | '`vch_256` text NULL', |
ab15e426 |
665 | 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3' |
666 | ); |
667 | is ( |
668 | SQL::Translator::Producer::MySQL::create_field($varchars->{256}, $options), |
ad071409 |
669 | '`vch_256` text NULL', |
ab15e426 |
670 | 'VARCHAR(256) is substituted with TEXT when no version specified', |
671 | ); |
672 | |
673 | |
674 | is ( |
675 | SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003, %$options }), |
ad071409 |
676 | '`vch_65535` varchar(65535) NULL', |
ab15e426 |
677 | 'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3' |
678 | ); |
679 | is ( |
680 | SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0, %$options }), |
ad071409 |
681 | '`vch_65535` text NULL', |
ab15e426 |
682 | 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3' |
683 | ); |
684 | is ( |
685 | SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, $options), |
ad071409 |
686 | '`vch_65535` text NULL', |
ab15e426 |
687 | 'VARCHAR(65535) is substituted with TEXT when no version specified', |
688 | ); |
689 | |
690 | |
691 | is ( |
692 | SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003, %$options }), |
ad071409 |
693 | '`vch_65536` text NULL', |
ab15e426 |
694 | 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3' |
695 | ); |
696 | is ( |
697 | SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0, %$options }), |
ad071409 |
698 | '`vch_65536` text NULL', |
ab15e426 |
699 | 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3' |
700 | ); |
701 | is ( |
702 | SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, $options), |
ad071409 |
703 | '`vch_65536` text NULL', |
ab15e426 |
704 | 'VARCHAR(65536) is substituted with TEXT when no version specified', |
705 | ); |
706 | |
707 | { |
708 | my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo', |
709 | fields => [qw/id name/], |
710 | sql => 'SELECT `id`, `name` FROM `my`.`thing`', |
711 | extra => { |
712 | mysql_definer => 'CURRENT_USER', |
713 | mysql_algorithm => 'MERGE', |
714 | mysql_security => 'DEFINER', |
715 | }); |
716 | my $create_opts = { add_replace_view => 1, no_comments => 1, %$options }; |
717 | my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts); |
718 | |
719 | my $view_sql_replace = <<'EOV'; |
720 | CREATE OR REPLACE |
721 | ALGORITHM = MERGE |
722 | DEFINER = CURRENT_USER |
723 | SQL SECURITY DEFINER |
724 | VIEW `view_foo` ( `id`, `name` ) AS |
725 | SELECT `id`, `name` FROM `my`.`thing` |
726 | EOV |
727 | |
728 | is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL'); |
729 | |
730 | |
731 | my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo', |
732 | fields => [qw/id name/], |
733 | sql => 'SELECT `id`, `name` FROM `my`.`thing`',); |
734 | my $create2_opts = { add_replace_view => 0, no_comments => 1, %$options }; |
735 | my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts); |
736 | my $view_sql_noreplace = <<'EOV'; |
737 | CREATE |
738 | VIEW `view_foo` ( `id`, `name` ) AS |
739 | SELECT `id`, `name` FROM `my`.`thing` |
740 | EOV |
741 | |
742 | is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL'); |
743 | |
744 | { |
745 | my %extra = $view1->extra; |
746 | is_deeply \%extra, |
747 | { |
748 | 'mysql_algorithm' => 'MERGE', |
749 | 'mysql_definer' => 'CURRENT_USER', |
750 | 'mysql_security' => 'DEFINER' |
751 | }, |
752 | 'Extra attributes'; |
753 | } |
754 | |
755 | $view1->remove_extra(qw/mysql_definer mysql_security/); |
756 | { |
757 | my %extra = $view1->extra; |
758 | is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call'; |
759 | } |
760 | |
761 | $view1->remove_extra(); |
762 | { |
763 | my %extra = $view1->extra; |
764 | is_deeply \%extra, {}, 'Extra attributes completely removed'; |
765 | } |
766 | } |
767 | |
768 | { |
769 | |
770 | # certain types do not support a size, see also: |
771 | # http://dev.mysql.com/doc/refman/5.1/de/create-table.html |
772 | for my $type (qw/date time timestamp datetime year/) { |
773 | my $field = SQL::Translator::Schema::Field->new( |
774 | name => "my$type", |
775 | table => $table, |
776 | data_type => $type, |
777 | size => 10, |
778 | default_value => undef, |
779 | is_auto_increment => 0, |
780 | is_nullable => 1, |
781 | is_foreign_key => 0, |
782 | is_unique => 0 |
783 | ); |
784 | my $sql = SQL::Translator::Producer::MySQL::create_field($field, $options); |
ad071409 |
785 | is($sql, "`my$type` $type NULL", "Skip length param for type $type"); |
ab15e426 |
786 | } |
787 | } |
788 | } |
b62fa492 |
789 | |
790 | { # test for rt62250 |
791 | my $table = SQL::Translator::Schema::Table->new(name => 'table'); |
792 | $table->add_field( |
793 | SQL::Translator::Schema::Field->new( name => 'mypk', |
794 | table => $table, |
795 | data_type => 'INT', |
796 | size => 10, |
797 | default_value => undef, |
798 | is_auto_increment => 1, |
799 | is_nullable => 0, |
800 | is_foreign_key => 0, |
801 | is_unique => 1 )); |
802 | |
803 | my $constraint = $table->add_constraint(fields => ['mypk'], type => 'PRIMARY_KEY'); |
804 | my $options = {quote_table_names => '`'}; |
805 | is(SQL::Translator::Producer::MySQL::alter_drop_constraint($constraint,$options), |
806 | 'ALTER TABLE `table` DROP PRIMARY KEY','valid drop primary key'); |
807 | } |