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