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 | |
e56dabb7 |
253 | my $field3 = SQL::Translator::Schema::Field->new( name => 'time_field', |
254 | table => $table, |
255 | data_type => 'TIME', |
256 | default_value => undef, |
257 | is_auto_increment => 0, |
258 | is_nullable => 0, |
259 | is_foreign_key => 0, |
260 | is_unique => 0 ); |
261 | |
262 | my $field3_sql = SQL::Translator::Producer::PostgreSQL::create_field($field3); |
263 | |
264 | is($field3_sql, 'time_field time NOT NULL', 'Create time field works'); |
265 | |
621cb859 |
266 | my $field3_datetime_with_TZ = SQL::Translator::Schema::Field->new( |
267 | name => 'datetime_with_TZ', |
268 | table => $table, |
269 | data_type => 'timestamp with time zone', |
270 | size => 7, |
271 | ); |
272 | |
aee4b66e |
273 | my $field3_datetime_with_TZ_sql = |
621cb859 |
274 | SQL::Translator::Producer::PostgreSQL::create_field( |
275 | $field3_datetime_with_TZ |
276 | ); |
277 | |
278 | is( |
aee4b66e |
279 | $field3_datetime_with_TZ_sql, |
280 | 'datetime_with_TZ timestamp(6) with time zone', |
621cb859 |
281 | 'Create time field with time zone and size, works' |
282 | ); |
283 | |
284 | my $field3_time_without_TZ = SQL::Translator::Schema::Field->new( |
285 | name => 'time_without_TZ', |
286 | table => $table, |
287 | data_type => 'time without time zone', |
288 | size => 2, |
289 | ); |
290 | |
aee4b66e |
291 | my $field3_time_without_TZ_sql |
621cb859 |
292 | = SQL::Translator::Producer::PostgreSQL::create_field( |
293 | $field3_time_without_TZ |
294 | ); |
295 | |
296 | is( |
aee4b66e |
297 | $field3_time_without_TZ_sql, |
298 | 'time_without_TZ time(2) without time zone', |
621cb859 |
299 | 'Create time field without time zone but with size, works' |
300 | ); |
301 | |
c3bddac9 |
302 | my $field_num = SQL::Translator::Schema::Field->new( name => 'num', |
303 | table => $table, |
304 | data_type => 'numeric', |
305 | size => [10,2], |
306 | ); |
307 | my $fieldnum_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_num); |
308 | |
309 | is($fieldnum_sql, 'num numeric(10,2)', 'Create numeric field works'); |
310 | |
311 | |
e56dabb7 |
312 | my $field4 = SQL::Translator::Schema::Field->new( name => 'bytea_field', |
313 | table => $table, |
314 | data_type => 'bytea', |
315 | size => '16777215', |
316 | default_value => undef, |
317 | is_auto_increment => 0, |
318 | is_nullable => 0, |
319 | is_foreign_key => 0, |
320 | is_unique => 0 ); |
321 | |
322 | my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4); |
8c4efd11 |
323 | |
e56dabb7 |
324 | is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works'); |
5342f5c1 |
325 | |
326 | my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field', |
327 | table => $table, |
328 | data_type => 'enum', |
1868ddbe |
329 | extra => { list => [ 'Foo', 'Bar', 'Ba\'z' ] }, |
5342f5c1 |
330 | is_auto_increment => 0, |
331 | is_nullable => 0, |
332 | is_foreign_key => 0, |
333 | is_unique => 0 ); |
334 | |
1868ddbe |
335 | my $field5_types = {}; |
336 | my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field( |
337 | $field5, |
338 | { |
339 | postgres_version => 8.3, |
340 | type_defs => $field5_types, |
341 | } |
342 | ); |
5342f5c1 |
343 | |
344 | is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works'); |
1868ddbe |
345 | is_deeply( |
346 | $field5_types, |
347 | { mytable_enum_field_type => |
348 | "DROP TYPE IF EXISTS mytable_enum_field_type CASCADE;\n" . |
349 | "CREATE TYPE mytable_enum_field_type AS ENUM ('Foo', 'Bar', 'Ba''z')" |
350 | }, |
351 | 'Create real enum type works' |
352 | ); |
90726ffd |
353 | |
354 | my $field6 = SQL::Translator::Schema::Field->new( |
355 | name => 'character', |
356 | table => $table, |
357 | data_type => 'character', |
358 | size => '123', |
359 | default_value => 'foobar', |
360 | is_auto_increment => 0, |
361 | is_nullable => 0, |
362 | is_foreign_key => 0, |
363 | is_unique => 0); |
364 | |
365 | my $field7 = SQL::Translator::Schema::Field->new( |
366 | name => 'character', |
367 | table => $table, |
368 | data_type => 'character', |
369 | size => '123', |
370 | default_value => undef, |
371 | is_auto_increment => 0, |
372 | is_nullable => 0, |
373 | is_foreign_key => 0, |
374 | is_unique => 0); |
375 | |
376 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
377 | $field7); |
378 | |
379 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP DEFAULT), 'DROP DEFAULT'); |
380 | |
381 | $field7->default_value(q(foo'bar')); |
382 | |
383 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
384 | $field7); |
385 | |
386 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT 'foo''bar'''), 'DEFAULT with escaping'); |
387 | |
388 | $field7->default_value(\q(foobar)); |
389 | |
390 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
391 | $field7); |
392 | |
393 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT foobar), 'DEFAULT unescaped if scalarref'); |
394 | |
395 | $field7->is_nullable(1); |
396 | $field7->default_value(q(foobar)); |
397 | |
398 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
399 | $field7); |
400 | |
401 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP NOT NULL), 'DROP NOT NULL'); |
402 | |
ad258776 |
403 | my $field8 = SQL::Translator::Schema::Field->new( name => 'ts_field', |
404 | table => $table, |
405 | data_type => 'timestamp with time zone', |
406 | size => 6, |
407 | is_auto_increment => 0, |
408 | is_nullable => 0, |
409 | is_foreign_key => 0, |
410 | is_unique => 0 ); |
411 | |
412 | my $field8_sql = SQL::Translator::Producer::PostgreSQL::create_field($field8,{ postgres_version => 8.3 }); |
413 | |
414 | is($field8_sql, 'ts_field timestamp(6) with time zone NOT NULL', 'timestamp with precision'); |
415 | |
416 | my $field9 = SQL::Translator::Schema::Field->new( name => 'time_field', |
417 | table => $table, |
418 | data_type => 'time with time zone', |
419 | size => 6, |
420 | is_auto_increment => 0, |
421 | is_nullable => 0, |
422 | is_foreign_key => 0, |
423 | is_unique => 0 ); |
424 | |
425 | my $field9_sql = SQL::Translator::Producer::PostgreSQL::create_field($field9,{ postgres_version => 8.3 }); |
426 | |
427 | is($field9_sql, 'time_field time(6) with time zone NOT NULL', 'time with precision'); |
428 | |
429 | my $field10 = SQL::Translator::Schema::Field->new( name => 'interval_field', |
430 | table => $table, |
431 | data_type => 'interval', |
432 | size => 6, |
433 | is_auto_increment => 0, |
434 | is_nullable => 0, |
435 | is_foreign_key => 0, |
436 | is_unique => 0 ); |
437 | |
438 | my $field10_sql = SQL::Translator::Producer::PostgreSQL::create_field($field10,{ postgres_version => 8.3 }); |
439 | |
440 | is($field10_sql, 'interval_field interval(6) NOT NULL', 'time with precision'); |
441 | |
442 | |
443 | my $field11 = SQL::Translator::Schema::Field->new( name => 'time_field', |
444 | table => $table, |
445 | data_type => 'time without time zone', |
446 | size => 6, |
447 | is_auto_increment => 0, |
448 | is_nullable => 0, |
449 | is_foreign_key => 0, |
450 | is_unique => 0 ); |
451 | |
452 | my $field11_sql = SQL::Translator::Producer::PostgreSQL::create_field($field11,{ postgres_version => 8.3 }); |
453 | |
454 | is($field11_sql, 'time_field time(6) without time zone NOT NULL', 'time with precision'); |
455 | |
456 | |
457 | |
458 | my $field12 = SQL::Translator::Schema::Field->new( name => 'time_field', |
459 | table => $table, |
460 | data_type => 'timestamp', |
461 | is_auto_increment => 0, |
462 | is_nullable => 0, |
463 | is_foreign_key => 0, |
464 | is_unique => 0 ); |
465 | |
466 | my $field12_sql = SQL::Translator::Producer::PostgreSQL::create_field($field12,{ postgres_version => 8.3 }); |
467 | |
468 | is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision'); |
469 | |
79f55d7e |
470 | my $field13 = SQL::Translator::Schema::Field->new( name => 'enum_field_with_type_name', |
471 | table => $table, |
472 | data_type => 'enum', |
1868ddbe |
473 | extra => { list => [ 'Foo', 'Bar', 'Ba\'z' ], |
79f55d7e |
474 | custom_type_name => 'real_enum_type' }, |
475 | is_auto_increment => 0, |
476 | is_nullable => 0, |
477 | is_foreign_key => 0, |
478 | is_unique => 0 ); |
479 | |
1868ddbe |
480 | my $field13_types = {}; |
481 | my $field13_sql = SQL::Translator::Producer::PostgreSQL::create_field( |
482 | $field13, |
483 | { |
484 | postgres_version => 8.3, |
485 | type_defs => $field13_types, |
486 | } |
487 | ); |
79f55d7e |
488 | |
489 | is($field13_sql, 'enum_field_with_type_name real_enum_type NOT NULL', 'Create real enum field works'); |
1868ddbe |
490 | is_deeply( |
491 | $field13_types, |
492 | { real_enum_type => |
493 | "DROP TYPE IF EXISTS real_enum_type CASCADE;\n" . |
494 | "CREATE TYPE real_enum_type AS ENUM ('Foo', 'Bar', 'Ba''z')" |
495 | }, |
496 | 'Create real enum type works' |
497 | ); |
79f55d7e |
498 | |
90726ffd |
499 | |
bc8e2aa1 |
500 | { |
501 | # let's test default values! -- rjbs, 2008-09-30 |
502 | my %field = ( |
503 | table => $table, |
504 | data_type => 'VARCHAR', |
505 | size => 10, |
506 | is_auto_increment => 0, |
507 | is_nullable => 1, |
508 | is_foreign_key => 0, |
509 | is_unique => 0, |
510 | ); |
511 | |
512 | { |
513 | my $simple_default = SQL::Translator::Schema::Field->new( |
514 | %field, |
515 | name => 'str_default', |
516 | default_value => 'foo', |
517 | ); |
518 | |
519 | is( |
520 | $PRODUCER->($simple_default), |
521 | q{str_default character varying(10) DEFAULT 'foo'}, |
522 | 'default str', |
523 | ); |
524 | } |
525 | |
526 | { |
527 | my $null_default = SQL::Translator::Schema::Field->new( |
528 | %field, |
529 | name => 'null_default', |
530 | default_value => \'NULL', |
531 | ); |
532 | |
533 | is( |
534 | $PRODUCER->($null_default), |
535 | q{null_default character varying(10) DEFAULT NULL}, |
536 | 'default null', |
537 | ); |
538 | } |
539 | |
540 | { |
541 | my $null_default = SQL::Translator::Schema::Field->new( |
542 | %field, |
543 | name => 'null_default_2', |
544 | default_value => 'NULL', # XXX: this should go away |
545 | ); |
546 | |
547 | is( |
548 | $PRODUCER->($null_default), |
549 | q{null_default_2 character varying(10) DEFAULT NULL}, |
550 | 'default null from special cased string', |
551 | ); |
552 | } |
553 | |
554 | { |
555 | my $func_default = SQL::Translator::Schema::Field->new( |
556 | %field, |
557 | name => 'func_default', |
558 | default_value => \'func(funky)', |
559 | ); |
560 | |
561 | is( |
562 | $PRODUCER->($func_default), |
563 | q{func_default character varying(10) DEFAULT func(funky)}, |
564 | 'unquoted default from scalar ref', |
565 | ); |
566 | } |
567 | } |
568 | |
569 | |
296c2701 |
570 | my $view1 = SQL::Translator::Schema::View->new( |
571 | name => 'view_foo', |
572 | fields => [qw/id name/], |
573 | sql => 'SELECT id, name FROM thing', |
574 | ); |
575 | my $create_opts = { add_replace_view => 1, no_comments => 1 }; |
576 | my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts); |
577 | |
f59b2c0e |
578 | my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS |
296c2701 |
579 | SELECT id, name FROM thing |
f59b2c0e |
580 | "; |
296c2701 |
581 | is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL'); |
582 | |
583 | my $view2 = SQL::Translator::Schema::View->new( |
584 | name => 'view_foo2', |
585 | sql => 'SELECT id, name FROM thing', |
586 | extra => { |
587 | 'temporary' => '1', |
588 | 'check_option' => 'cascaded', |
589 | }, |
590 | ); |
591 | my $create2_opts = { add_replace_view => 1, no_comments => 1 }; |
592 | my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts); |
593 | |
f59b2c0e |
594 | my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS |
296c2701 |
595 | SELECT id, name FROM thing |
f59b2c0e |
596 | WITH CASCADED CHECK OPTION"; |
296c2701 |
597 | is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2'); |
5f31ed66 |
598 | |
599 | { |
600 | my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => [qw( foo bar )] ); |
f82112a3 |
601 | my $quote = { quote_table_names => '"' }; |
5f31ed66 |
602 | |
603 | { |
604 | my $index = $table->add_index(name => 'myindex', fields => ['foo']); |
605 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
606 | is($def, "CREATE INDEX myindex on foobar (foo)", 'index created'); |
607 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
608 | is($def, 'CREATE INDEX "myindex" on "foobar" ("foo")', 'index created w/ quotes'); |
609 | } |
610 | |
611 | { |
612 | my $index = $table->add_index(name => 'myindex', fields => ['lower(foo)']); |
613 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
614 | is($def, "CREATE INDEX myindex on foobar (lower(foo))", 'index created'); |
615 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
616 | is($def, 'CREATE INDEX "myindex" on "foobar" (lower(foo))', 'index created w/ quotes'); |
617 | } |
618 | |
619 | { |
620 | my $index = $table->add_index(name => 'myindex', fields => ['bar', 'lower(foo)']); |
621 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
622 | is($def, "CREATE INDEX myindex on foobar (bar, lower(foo))", 'index created'); |
623 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
624 | is($def, 'CREATE INDEX "myindex" on "foobar" ("bar", lower(foo))', 'index created w/ quotes'); |
625 | } |
626 | |
627 | { |
628 | my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['foo']); |
629 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr); |
630 | is($def->[0], 'CONSTRAINT constr UNIQUE (foo)', 'constraint created'); |
631 | ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote); |
632 | is($def->[0], 'CONSTRAINT "constr" UNIQUE ("foo")', 'constraint created w/ quotes'); |
633 | } |
634 | |
635 | { |
636 | my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['lower(foo)']); |
637 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr); |
638 | is($def->[0], 'CONSTRAINT constr UNIQUE (lower(foo))', 'constraint created'); |
639 | ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote); |
640 | is($def->[0], 'CONSTRAINT "constr" UNIQUE (lower(foo))', 'constraint created w/ quotes'); |
641 | } |
642 | |
643 | { |
644 | my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['bar', 'lower(foo)']); |
645 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr); |
646 | is($def->[0], 'CONSTRAINT constr UNIQUE (bar, lower(foo))', 'constraint created'); |
647 | ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote); |
648 | is($def->[0], 'CONSTRAINT "constr" UNIQUE ("bar", lower(foo))', 'constraint created w/ quotes'); |
649 | } |
5b36314d |
650 | |
651 | { |
652 | my $index = $table->add_index(name => 'myindex', options => [{using => 'hash'}, {where => "upper(foo) = 'bar' AND bar = 'foo'"}], fields => ['bar', 'lower(foo)']); |
653 | my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index); |
654 | is($def, "CREATE INDEX myindex on foobar USING hash (bar, lower(foo)) WHERE upper(foo) = 'bar' AND bar = 'foo'", 'index using & where created'); |
655 | ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote); |
656 | 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'); |
657 | } |
5f31ed66 |
658 | } |
25c74c43 |
659 | |
660 | my $drop_view_opts1 = { add_drop_view => 1, no_comments => 1, postgres_version => 8.001 }; |
661 | my $drop_view_8_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts1); |
662 | |
663 | my $drop_view_8_1_expected = "DROP VIEW view_foo; |
664 | CREATE VIEW view_foo ( id, name ) AS |
665 | SELECT id, name FROM thing |
666 | "; |
aee4b66e |
667 | |
25c74c43 |
668 | is($drop_view_8_1_produced, $drop_view_8_1_expected, "My DROP VIEW statement for 8.1 is correct"); |
669 | |
670 | my $drop_view_opts2 = { add_drop_view => 1, no_comments => 1, postgres_version => 9.001 }; |
671 | my $drop_view_9_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts2); |
672 | |
673 | my $drop_view_9_1_expected = "DROP VIEW IF EXISTS view_foo; |
674 | CREATE VIEW view_foo ( id, name ) AS |
675 | SELECT id, name FROM thing |
676 | "; |
aee4b66e |
677 | |
25c74c43 |
678 | is($drop_view_9_1_produced, $drop_view_9_1_expected, "My DROP VIEW statement for 9.1 is correct"); |
1868ddbe |
679 | |
680 | done_testing; |