Commit | Line | Data |
8c4efd11 |
1 | #!/usr/bin/perl |
2 | |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
7 | use Test::Exception; |
8 | use Test::SQL::Translator qw(maybe_plan); |
5f31ed66 |
9 | use SQL::Translator::Schema::Constants; |
8c4efd11 |
10 | use Data::Dumper; |
11 | use FindBin qw/$Bin/; |
12 | |
13 | # Testing 1,2,3,4... |
14 | #============================================================================= |
15 | |
16 | BEGIN { |
1868ddbe |
17 | maybe_plan(undef, |
8c4efd11 |
18 | 'SQL::Translator::Producer::PostgreSQL', |
19 | 'Test::Differences', |
20 | ) |
21 | } |
22 | use Test::Differences; |
23 | use SQL::Translator; |
24 | |
bc8e2aa1 |
25 | my $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field; |
8c4efd11 |
26 | |
2230ed2a |
27 | { |
31bed2c0 |
28 | my $table = SQL::Translator::Schema::Table->new( name => 'foo.bar', |
29 | comments => [ "multi\nline",'single line' ] ); |
2230ed2a |
30 | my $field = SQL::Translator::Schema::Field->new( name => 'baz', |
31bed2c0 |
31 | comments => [ "multi\nline",'single line' ], |
2230ed2a |
32 | table => $table, |
33 | data_type => 'VARCHAR', |
34 | size => 10, |
35 | default_value => 'quux', |
36 | is_auto_increment => 0, |
37 | is_nullable => 0, |
38 | is_foreign_key => 0, |
39 | is_unique => 0 ); |
40 | $table->add_field($field); |
41 | my ($create, $fks) = SQL::Translator::Producer::PostgreSQL::create_table($table, { quote_table_names => q{"} }); |
42 | is($table->name, 'foo.bar'); |
43 | |
31bed2c0 |
44 | my $expected = <<EOESQL; |
45 | -- |
46 | -- Table: foo.bar |
47 | -- |
48 | |
49 | -- Comments: |
50 | -- multi |
51 | -- line |
52 | -- single line |
53 | -- |
54 | CREATE TABLE "foo"."bar" ( |
55 | -- multi |
56 | -- line |
57 | -- single line |
58 | "baz" character varying(10) DEFAULT 'quux' NOT NULL |
59 | ) |
60 | EOESQL |
61 | |
62 | $expected =~ s/\n\z//; |
2230ed2a |
63 | is($create, $expected); |
64 | } |
65 | |
8c4efd11 |
66 | my $table = SQL::Translator::Schema::Table->new( name => 'mytable'); |
67 | |
68 | my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', |
69 | table => $table, |
70 | data_type => 'VARCHAR', |
71 | size => 10, |
72 | default_value => undef, |
73 | is_auto_increment => 0, |
74 | is_nullable => 1, |
75 | is_foreign_key => 0, |
76 | is_unique => 0 ); |
77 | |
78 | my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1); |
79 | |
80 | is($field1_sql, 'myfield character varying(10)', 'Create field works'); |
81 | |
aacb3187 |
82 | my $field_array = SQL::Translator::Schema::Field->new( name => 'myfield', |
83 | table => $table, |
84 | data_type => 'character varying[]', |
85 | size => 10, |
86 | default_value => undef, |
87 | is_auto_increment => 0, |
88 | is_nullable => 1, |
89 | is_foreign_key => 0, |
90 | is_unique => 0 ); |
91 | |
92 | my $field_array_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_array); |
93 | |
94 | is($field_array_sql, 'myfield character varying(10)[]', 'Create field works'); |
95 | |
8c4efd11 |
96 | my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', |
97 | table => $table, |
98 | data_type => 'VARCHAR', |
99 | size => 25, |
100 | default_value => undef, |
101 | is_auto_increment => 0, |
102 | is_nullable => 0, |
103 | is_foreign_key => 0, |
104 | is_unique => 0 ); |
105 | |
c50d1a0a |
106 | my $pk_constraint = SQL::Translator::Schema::Constraint->new( |
107 | table => $table, |
108 | name => 'foo', |
109 | fields => [qw(myfield)], |
110 | type => 'PRIMARY_KEY', |
111 | ); |
112 | |
113 | my ($pk_constraint_def_ref, $pk_constraint_fk_ref ) = SQL::Translator::Producer::PostgreSQL::create_constraint($pk_constraint); |
114 | ok(@{$pk_constraint_def_ref} == 1 && @{$pk_constraint_fk_ref} == 0, 'precheck of create_Primary Key constraint'); |
115 | is($pk_constraint_def_ref->[0], 'CONSTRAINT foo PRIMARY KEY (myfield)', 'Create Primary Key Constraint works'); |
116 | |
117 | my $alter_pk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($pk_constraint); |
118 | is($alter_pk_constraint, 'ALTER TABLE mytable DROP CONSTRAINT foo', 'Alter drop Primary Key constraint works'); |
119 | |
120 | my $table2 = SQL::Translator::Schema::Table->new( name => 'mytable2'); |
121 | |
122 | my $field1_2 = SQL::Translator::Schema::Field->new( name => 'myfield_2', |
123 | table => $table, |
124 | data_type => 'VARCHAR', |
125 | size => 10, |
126 | default_value => undef, |
127 | is_auto_increment => 0, |
128 | is_nullable => 1, |
129 | is_foreign_key => 0, |
130 | is_unique => 0 ); |
131 | |
681dc480 |
132 | # check named, and unnamed foreign keys |
133 | for my $name ( 'foo', undef ) { |
134 | my $fk_constraint = SQL::Translator::Schema::Constraint->new( |
135 | table => $table, |
136 | name => $name, |
137 | fields => [qw(myfield)], |
138 | type => 'FOREIGN_KEY', |
139 | reference_table => $table2, |
140 | reference_fields => [qw(myfield_2)], |
141 | ); |
142 | my $fk_constraint_2 = SQL::Translator::Schema::Constraint->new( |
143 | table => $table, |
144 | name => $name, |
145 | fields => [qw(myfield)], |
146 | type => 'FOREIGN_KEY', |
147 | reference_table => $table2, |
148 | reference_fields => [qw(myfield_2)], |
149 | ); |
150 | |
151 | my ($fk_constraint_def_ref, $fk_constraint_fk_ref ) = SQL::Translator::Producer::PostgreSQL::create_constraint($fk_constraint); |
152 | |
153 | ok(@{$fk_constraint_def_ref} == 0 && @{$fk_constraint_fk_ref} == 1, 'precheck of create_Foreign Key constraint'); |
154 | |
155 | if ( $name ) { |
156 | is($fk_constraint_fk_ref->[0], "ALTER TABLE mytable ADD CONSTRAINT $name FOREIGN KEY (myfield) |
157 | REFERENCES mytable2 (myfield_2) DEFERRABLE", 'Create Foreign Key Constraint works'); |
158 | |
159 | # ToDo: may we should check if the constraint name was valid, or if next |
160 | # unused_name created has choosen a different one |
161 | my $alter_fk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($fk_constraint); |
162 | is($alter_fk_constraint, "ALTER TABLE mytable DROP CONSTRAINT $name", 'Alter drop Foreign Key constraint works'); |
163 | } |
164 | else { |
165 | is($fk_constraint_fk_ref->[0], 'ALTER TABLE mytable ADD FOREIGN KEY (myfield) |
bc9b1c11 |
166 | REFERENCES mytable2 (myfield_2) DEFERRABLE', 'Create un-named Foreign Key Constraint works'); |
681dc480 |
167 | |
168 | my $alter_fk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($fk_constraint); |
bc9b1c11 |
169 | is($alter_fk_constraint, 'ALTER TABLE mytable DROP CONSTRAINT mytable_myfield_fkey', 'Alter drop un-named Foreign Key constraint works'); |
681dc480 |
170 | } |
171 | } |
c50d1a0a |
172 | |
bc9b1c11 |
173 | # check named, and unnamed primary keys |
174 | for my $name ( 'foo', undef ) { |
175 | my $pk_constraint = SQL::Translator::Schema::Constraint->new( |
176 | table => $table, |
177 | name => $name, |
178 | fields => [qw(myfield)], |
179 | type => 'PRIMARY_KEY', |
180 | ); |
181 | my $pk_constraint_2 = SQL::Translator::Schema::Constraint->new( |
182 | table => $table, |
183 | name => $name, |
184 | fields => [qw(myfield)], |
185 | type => 'PRIMARY_KEY', |
186 | ); |
187 | |
188 | my ($pk_constraint_def_ref, $pk_constraint_pk_ref ) = SQL::Translator::Producer::PostgreSQL::create_constraint($pk_constraint); |
189 | |
190 | if ( $name ) { |
191 | is($pk_constraint_def_ref->[0], "CONSTRAINT $name PRIMARY KEY (myfield)", 'Create Primary Key Constraint works'); |
192 | |
193 | # ToDo: may we should check if the constraint name was valid, or if next |
194 | # unused_name created has choosen a different one |
195 | my $alter_pk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($pk_constraint); |
196 | is($alter_pk_constraint, "ALTER TABLE mytable DROP CONSTRAINT $name", 'Alter drop Primary Key constraint works'); |
197 | } |
198 | else { |
199 | is($pk_constraint_def_ref->[0], 'PRIMARY KEY (myfield)', 'Create un-named Primary Key Constraint works'); |
200 | |
201 | my $alter_pk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($pk_constraint); |
202 | is($alter_pk_constraint, 'ALTER TABLE mytable DROP CONSTRAINT mytable_pkey', 'Alter drop un-named Foreign Key constraint works'); |
203 | } |
204 | } |
c50d1a0a |
205 | |
8c4efd11 |
206 | my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1, |
207 | $field2); |
c50d1a0a |
208 | is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL; |
3406fd5b |
209 | ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)], |
8c4efd11 |
210 | 'Alter field works'); |
211 | |
c50d1a0a |
212 | my $field1_complex = SQL::Translator::Schema::Field->new( |
213 | name => 'my_complex_field', |
214 | table => $table, |
215 | data_type => 'VARCHAR', |
216 | size => 10, |
217 | default_value => undef, |
218 | is_auto_increment => 0, |
219 | is_nullable => 1, |
220 | is_foreign_key => 0, |
221 | is_unique => 0 |
222 | ); |
223 | |
224 | my $field2_complex = SQL::Translator::Schema::Field->new( |
225 | name => 'my_altered_field', |
226 | table => $table, |
227 | data_type => 'VARCHAR', |
228 | size => 60, |
229 | default_value => 'whatever', |
230 | is_auto_increment => 0, |
231 | is_nullable => 1, |
232 | is_foreign_key => 0, |
233 | is_unique => 0 |
234 | ); |
235 | |
236 | my $alter_field_complex = SQL::Translator::Producer::PostgreSQL::alter_field($field1_complex, $field2_complex); |
237 | is( |
238 | $alter_field_complex, |
239 | q{ALTER TABLE mytable RENAME COLUMN my_complex_field TO my_altered_field; |
240 | ALTER TABLE mytable ALTER COLUMN my_altered_field TYPE character varying(60); |
241 | ALTER TABLE mytable ALTER COLUMN my_altered_field SET DEFAULT 'whatever'}, |
242 | 'Complex Alter field works' |
243 | ); |
244 | |
8c4efd11 |
245 | $field1->name('field3'); |
246 | my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1); |
247 | |
3406fd5b |
248 | is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10)', 'Add field works'); |
8c4efd11 |
249 | |
250 | my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2); |
3406fd5b |
251 | is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works'); |
8c4efd11 |
252 | |
9d430e09 |
253 | my $field_serial = SQL::Translator::Schema::Field->new( name => 'serial_field', |
254 | table => $table, |
255 | data_type => 'INT', |
256 | is_auto_increment => 1, |
257 | is_nullable => 0 ); |
258 | |
259 | my $field_serial_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_serial); |
260 | |
261 | is($field_serial_sql, 'serial_field serial NOT NULL', 'Create serial field works'); |
262 | |
263 | my $field_bigserial = SQL::Translator::Schema::Field->new( name => 'bigserial_field', |
264 | table => $table, |
265 | data_type => 'BIGINT', |
266 | is_auto_increment => 1, |
267 | is_nullable => 0 ); |
268 | |
269 | my $field_bigserial_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_bigserial); |
270 | |
271 | is($field_bigserial_sql, 'bigserial_field bigserial NOT NULL', 'Create bigserial field works (from bigint type)'); |
272 | |
273 | $field_bigserial = SQL::Translator::Schema::Field->new( name => 'bigserial_field', |
274 | table => $table, |
275 | data_type => 'INT', |
276 | is_auto_increment => 1, |
277 | is_nullable => 0, |
278 | size => 12 ); |
279 | |
280 | $field_bigserial_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_bigserial); |
281 | |
282 | is($field_bigserial_sql, 'bigserial_field bigserial NOT NULL', 'Create bigserial field works (based on size)'); |
283 | |
e56dabb7 |
284 | my $field3 = SQL::Translator::Schema::Field->new( name => 'time_field', |
285 | table => $table, |
286 | data_type => 'TIME', |
287 | default_value => undef, |
288 | is_auto_increment => 0, |
289 | is_nullable => 0, |
290 | is_foreign_key => 0, |
291 | is_unique => 0 ); |
292 | |
293 | my $field3_sql = SQL::Translator::Producer::PostgreSQL::create_field($field3); |
294 | |
295 | is($field3_sql, 'time_field time NOT NULL', 'Create time field works'); |
296 | |
621cb859 |
297 | my $field3_datetime_with_TZ = SQL::Translator::Schema::Field->new( |
298 | name => 'datetime_with_TZ', |
299 | table => $table, |
300 | data_type => 'timestamp with time zone', |
301 | size => 7, |
302 | ); |
303 | |
aee4b66e |
304 | my $field3_datetime_with_TZ_sql = |
621cb859 |
305 | SQL::Translator::Producer::PostgreSQL::create_field( |
306 | $field3_datetime_with_TZ |
307 | ); |
308 | |
309 | is( |
aee4b66e |
310 | $field3_datetime_with_TZ_sql, |
311 | 'datetime_with_TZ timestamp(6) with time zone', |
621cb859 |
312 | 'Create time field with time zone and size, works' |
313 | ); |
314 | |
315 | my $field3_time_without_TZ = SQL::Translator::Schema::Field->new( |
316 | name => 'time_without_TZ', |
317 | table => $table, |
318 | data_type => 'time without time zone', |
319 | size => 2, |
320 | ); |
321 | |
aee4b66e |
322 | my $field3_time_without_TZ_sql |
621cb859 |
323 | = SQL::Translator::Producer::PostgreSQL::create_field( |
324 | $field3_time_without_TZ |
325 | ); |
326 | |
327 | is( |
aee4b66e |
328 | $field3_time_without_TZ_sql, |
329 | 'time_without_TZ time(2) without time zone', |
621cb859 |
330 | 'Create time field without time zone but with size, works' |
331 | ); |
332 | |
c3bddac9 |
333 | my $field_num = SQL::Translator::Schema::Field->new( name => 'num', |
334 | table => $table, |
335 | data_type => 'numeric', |
336 | size => [10,2], |
337 | ); |
338 | my $fieldnum_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_num); |
339 | |
340 | is($fieldnum_sql, 'num numeric(10,2)', 'Create numeric field works'); |
341 | |
342 | |
e56dabb7 |
343 | my $field4 = SQL::Translator::Schema::Field->new( name => 'bytea_field', |
344 | table => $table, |
345 | data_type => 'bytea', |
346 | size => '16777215', |
347 | default_value => undef, |
348 | is_auto_increment => 0, |
349 | is_nullable => 0, |
350 | is_foreign_key => 0, |
351 | is_unique => 0 ); |
352 | |
353 | my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4); |
8c4efd11 |
354 | |
e56dabb7 |
355 | is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works'); |
5342f5c1 |
356 | |
357 | my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field', |
358 | table => $table, |
359 | data_type => 'enum', |
1868ddbe |
360 | extra => { list => [ 'Foo', 'Bar', 'Ba\'z' ] }, |
5342f5c1 |
361 | is_auto_increment => 0, |
362 | is_nullable => 0, |
363 | is_foreign_key => 0, |
364 | is_unique => 0 ); |
365 | |
1868ddbe |
366 | my $field5_types = {}; |
367 | my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field( |
368 | $field5, |
369 | { |
370 | postgres_version => 8.3, |
371 | type_defs => $field5_types, |
372 | } |
373 | ); |
5342f5c1 |
374 | |
375 | is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works'); |
1868ddbe |
376 | is_deeply( |
377 | $field5_types, |
378 | { mytable_enum_field_type => |
379 | "DROP TYPE IF EXISTS mytable_enum_field_type CASCADE;\n" . |
380 | "CREATE TYPE mytable_enum_field_type AS ENUM ('Foo', 'Bar', 'Ba''z')" |
381 | }, |
382 | 'Create real enum type works' |
383 | ); |
90726ffd |
384 | |
385 | my $field6 = SQL::Translator::Schema::Field->new( |
386 | name => 'character', |
387 | table => $table, |
388 | data_type => 'character', |
389 | size => '123', |
390 | default_value => 'foobar', |
391 | is_auto_increment => 0, |
392 | is_nullable => 0, |
393 | is_foreign_key => 0, |
394 | is_unique => 0); |
395 | |
396 | my $field7 = SQL::Translator::Schema::Field->new( |
397 | name => 'character', |
398 | table => $table, |
399 | data_type => 'character', |
400 | size => '123', |
401 | default_value => undef, |
402 | is_auto_increment => 0, |
403 | is_nullable => 0, |
404 | is_foreign_key => 0, |
405 | is_unique => 0); |
406 | |
407 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
408 | $field7); |
409 | |
410 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP DEFAULT), 'DROP DEFAULT'); |
411 | |
412 | $field7->default_value(q(foo'bar')); |
413 | |
414 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
415 | $field7); |
416 | |
417 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT 'foo''bar'''), 'DEFAULT with escaping'); |
418 | |
419 | $field7->default_value(\q(foobar)); |
420 | |
421 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
422 | $field7); |
423 | |
424 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT foobar), 'DEFAULT unescaped if scalarref'); |
425 | |
426 | $field7->is_nullable(1); |
427 | $field7->default_value(q(foobar)); |
428 | |
429 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
430 | $field7); |
431 | |
432 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP NOT NULL), 'DROP NOT NULL'); |
433 | |
ad258776 |
434 | my $field8 = SQL::Translator::Schema::Field->new( name => 'ts_field', |
435 | table => $table, |
436 | data_type => 'timestamp with time zone', |
437 | size => 6, |
438 | is_auto_increment => 0, |
439 | is_nullable => 0, |
440 | is_foreign_key => 0, |
441 | is_unique => 0 ); |
442 | |
443 | my $field8_sql = SQL::Translator::Producer::PostgreSQL::create_field($field8,{ postgres_version => 8.3 }); |
444 | |
445 | is($field8_sql, 'ts_field timestamp(6) with time zone NOT NULL', 'timestamp with precision'); |
446 | |
447 | my $field9 = SQL::Translator::Schema::Field->new( name => 'time_field', |
448 | table => $table, |
449 | data_type => 'time with time zone', |
450 | size => 6, |
451 | is_auto_increment => 0, |
452 | is_nullable => 0, |
453 | is_foreign_key => 0, |
454 | is_unique => 0 ); |
455 | |
456 | my $field9_sql = SQL::Translator::Producer::PostgreSQL::create_field($field9,{ postgres_version => 8.3 }); |
457 | |
458 | is($field9_sql, 'time_field time(6) with time zone NOT NULL', 'time with precision'); |
459 | |
460 | my $field10 = SQL::Translator::Schema::Field->new( name => 'interval_field', |
461 | table => $table, |
462 | data_type => 'interval', |
463 | size => 6, |
464 | is_auto_increment => 0, |
465 | is_nullable => 0, |
466 | is_foreign_key => 0, |
467 | is_unique => 0 ); |
468 | |
469 | my $field10_sql = SQL::Translator::Producer::PostgreSQL::create_field($field10,{ postgres_version => 8.3 }); |
470 | |
471 | is($field10_sql, 'interval_field interval(6) NOT NULL', 'time with precision'); |
472 | |
473 | |
474 | my $field11 = SQL::Translator::Schema::Field->new( name => 'time_field', |
475 | table => $table, |
476 | data_type => 'time without time zone', |
477 | size => 6, |
478 | is_auto_increment => 0, |
479 | is_nullable => 0, |
480 | is_foreign_key => 0, |
481 | is_unique => 0 ); |
482 | |
483 | my $field11_sql = SQL::Translator::Producer::PostgreSQL::create_field($field11,{ postgres_version => 8.3 }); |
484 | |
485 | is($field11_sql, 'time_field time(6) without time zone NOT NULL', 'time with precision'); |
486 | |
487 | |
488 | |
489 | my $field12 = SQL::Translator::Schema::Field->new( name => 'time_field', |
490 | table => $table, |
491 | data_type => 'timestamp', |
492 | is_auto_increment => 0, |
493 | is_nullable => 0, |
494 | is_foreign_key => 0, |
495 | is_unique => 0 ); |
496 | |
497 | my $field12_sql = SQL::Translator::Producer::PostgreSQL::create_field($field12,{ postgres_version => 8.3 }); |
498 | |
499 | is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision'); |
500 | |
79f55d7e |
501 | my $field13 = SQL::Translator::Schema::Field->new( name => 'enum_field_with_type_name', |
502 | table => $table, |
503 | data_type => 'enum', |
1868ddbe |
504 | extra => { list => [ 'Foo', 'Bar', 'Ba\'z' ], |
79f55d7e |
505 | custom_type_name => 'real_enum_type' }, |
506 | is_auto_increment => 0, |
507 | is_nullable => 0, |
508 | is_foreign_key => 0, |
509 | is_unique => 0 ); |
510 | |
1868ddbe |
511 | my $field13_types = {}; |
512 | my $field13_sql = SQL::Translator::Producer::PostgreSQL::create_field( |
513 | $field13, |
514 | { |
515 | postgres_version => 8.3, |
516 | type_defs => $field13_types, |
517 | } |
518 | ); |
79f55d7e |
519 | |
520 | is($field13_sql, 'enum_field_with_type_name real_enum_type NOT NULL', 'Create real enum field works'); |
1868ddbe |
521 | is_deeply( |
522 | $field13_types, |
523 | { real_enum_type => |
524 | "DROP TYPE IF EXISTS real_enum_type CASCADE;\n" . |
525 | "CREATE TYPE real_enum_type AS ENUM ('Foo', 'Bar', 'Ba''z')" |
526 | }, |
527 | 'Create real enum type works' |
528 | ); |
79f55d7e |
529 | |
90726ffd |
530 | |
bc8e2aa1 |
531 | { |
532 | # let's test default values! -- rjbs, 2008-09-30 |
533 | my %field = ( |
534 | table => $table, |
535 | data_type => 'VARCHAR', |
536 | size => 10, |
537 | is_auto_increment => 0, |
538 | is_nullable => 1, |
539 | is_foreign_key => 0, |
540 | is_unique => 0, |
541 | ); |
542 | |
543 | { |
544 | my $simple_default = SQL::Translator::Schema::Field->new( |
545 | %field, |
546 | name => 'str_default', |
547 | default_value => 'foo', |
548 | ); |
549 | |
550 | is( |
551 | $PRODUCER->($simple_default), |
552 | q{str_default character varying(10) DEFAULT 'foo'}, |
553 | 'default str', |
554 | ); |
555 | } |
556 | |
557 | { |
558 | my $null_default = SQL::Translator::Schema::Field->new( |
559 | %field, |
560 | name => 'null_default', |
561 | default_value => \'NULL', |
562 | ); |
563 | |
564 | is( |
565 | $PRODUCER->($null_default), |
566 | q{null_default character varying(10) DEFAULT NULL}, |
567 | 'default null', |
568 | ); |
569 | } |
570 | |
571 | { |
572 | my $null_default = SQL::Translator::Schema::Field->new( |
573 | %field, |
574 | name => 'null_default_2', |
575 | default_value => 'NULL', # XXX: this should go away |
576 | ); |
577 | |
578 | is( |
579 | $PRODUCER->($null_default), |
580 | q{null_default_2 character varying(10) DEFAULT NULL}, |
581 | 'default null from special cased string', |
582 | ); |
583 | } |
584 | |
585 | { |
586 | my $func_default = SQL::Translator::Schema::Field->new( |
587 | %field, |
588 | name => 'func_default', |
589 | default_value => \'func(funky)', |
590 | ); |
591 | |
592 | is( |
593 | $PRODUCER->($func_default), |
594 | q{func_default character varying(10) DEFAULT func(funky)}, |
595 | 'unquoted default from scalar ref', |
596 | ); |
597 | } |
598 | } |
599 | |
600 | |
296c2701 |
601 | my $view1 = SQL::Translator::Schema::View->new( |
602 | name => 'view_foo', |
603 | fields => [qw/id name/], |
604 | sql => 'SELECT id, name FROM thing', |
605 | ); |
606 | my $create_opts = { add_replace_view => 1, no_comments => 1 }; |
607 | my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts); |
608 | |
f59b2c0e |
609 | my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS |
296c2701 |
610 | SELECT id, name FROM thing |
f59b2c0e |
611 | "; |
296c2701 |
612 | is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL'); |
613 | |
614 | my $view2 = SQL::Translator::Schema::View->new( |
615 | name => 'view_foo2', |
616 | sql => 'SELECT id, name FROM thing', |
617 | extra => { |
618 | 'temporary' => '1', |
619 | 'check_option' => 'cascaded', |
620 | }, |
621 | ); |
622 | my $create2_opts = { add_replace_view => 1, no_comments => 1 }; |
623 | my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts); |
624 | |
f59b2c0e |
625 | my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS |
296c2701 |
626 | SELECT id, name FROM thing |
f59b2c0e |
627 | WITH CASCADED CHECK OPTION"; |
296c2701 |
628 | is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2'); |
5f31ed66 |
629 | |
630 | { |
631 | my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => [qw( foo bar )] ); |
f82112a3 |
632 | my $quote = { quote_table_names => '"' }; |
5f31ed66 |
633 | |
634 | { |
635 | my $index = $table->add_index(name => 'myindex', fields => ['foo']); |
636 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
637 | is($def, "CREATE INDEX myindex on foobar (foo)", 'index created'); |
638 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
639 | is($def, 'CREATE INDEX "myindex" on "foobar" ("foo")', 'index created w/ quotes'); |
640 | } |
641 | |
642 | { |
643 | my $index = $table->add_index(name => 'myindex', fields => ['lower(foo)']); |
644 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
645 | is($def, "CREATE INDEX myindex on foobar (lower(foo))", 'index created'); |
646 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
647 | is($def, 'CREATE INDEX "myindex" on "foobar" (lower(foo))', 'index created w/ quotes'); |
648 | } |
649 | |
650 | { |
651 | my $index = $table->add_index(name => 'myindex', fields => ['bar', 'lower(foo)']); |
652 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
653 | is($def, "CREATE INDEX myindex on foobar (bar, lower(foo))", 'index created'); |
654 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
655 | is($def, 'CREATE INDEX "myindex" on "foobar" ("bar", lower(foo))', 'index created w/ quotes'); |
656 | } |
657 | |
658 | { |
659 | my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['foo']); |
660 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr); |
661 | is($def->[0], 'CONSTRAINT constr UNIQUE (foo)', 'constraint created'); |
662 | ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote); |
663 | is($def->[0], 'CONSTRAINT "constr" UNIQUE ("foo")', 'constraint created w/ quotes'); |
664 | } |
665 | |
666 | { |
667 | my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['lower(foo)']); |
668 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr); |
669 | is($def->[0], 'CONSTRAINT constr UNIQUE (lower(foo))', 'constraint created'); |
670 | ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote); |
671 | is($def->[0], 'CONSTRAINT "constr" UNIQUE (lower(foo))', 'constraint created w/ quotes'); |
672 | } |
673 | |
674 | { |
675 | my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['bar', 'lower(foo)']); |
676 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr); |
677 | is($def->[0], 'CONSTRAINT constr UNIQUE (bar, lower(foo))', 'constraint created'); |
678 | ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote); |
679 | is($def->[0], 'CONSTRAINT "constr" UNIQUE ("bar", lower(foo))', 'constraint created w/ quotes'); |
680 | } |
5b36314d |
681 | |
682 | { |
683 | my $index = $table->add_index(name => 'myindex', options => [{using => 'hash'}, {where => "upper(foo) = 'bar' AND bar = 'foo'"}], fields => ['bar', 'lower(foo)']); |
684 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
685 | is($def, "CREATE INDEX myindex on foobar USING hash (bar, lower(foo)) WHERE upper(foo) = 'bar' AND bar = 'foo'", 'index using & where created'); |
686 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
687 | is($def, 'CREATE INDEX "myindex" on "foobar" USING hash ("bar", lower(foo)) WHERE upper(foo) = \'bar\' AND bar = \'foo\'', 'index using & where created w/ quotes'); |
688 | } |
5f31ed66 |
689 | } |
25c74c43 |
690 | |
691 | my $drop_view_opts1 = { add_drop_view => 1, no_comments => 1, postgres_version => 8.001 }; |
692 | my $drop_view_8_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts1); |
693 | |
694 | my $drop_view_8_1_expected = "DROP VIEW view_foo; |
695 | CREATE VIEW view_foo ( id, name ) AS |
696 | SELECT id, name FROM thing |
697 | "; |
aee4b66e |
698 | |
25c74c43 |
699 | is($drop_view_8_1_produced, $drop_view_8_1_expected, "My DROP VIEW statement for 8.1 is correct"); |
700 | |
701 | my $drop_view_opts2 = { add_drop_view => 1, no_comments => 1, postgres_version => 9.001 }; |
702 | my $drop_view_9_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts2); |
703 | |
704 | my $drop_view_9_1_expected = "DROP VIEW IF EXISTS view_foo; |
705 | CREATE VIEW view_foo ( id, name ) AS |
706 | SELECT id, name FROM thing |
707 | "; |
aee4b66e |
708 | |
25c74c43 |
709 | is($drop_view_9_1_produced, $drop_view_9_1_expected, "My DROP VIEW statement for 9.1 is correct"); |
1868ddbe |
710 | |
711 | done_testing; |