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