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); |
9 | |
10 | use Data::Dumper; |
11 | use FindBin qw/$Bin/; |
12 | |
13 | # Testing 1,2,3,4... |
14 | #============================================================================= |
15 | |
16 | BEGIN { |
621cb859 |
17 | maybe_plan(24, |
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 | |
27 | my $table = SQL::Translator::Schema::Table->new( name => 'mytable'); |
28 | |
29 | my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', |
30 | table => $table, |
31 | data_type => 'VARCHAR', |
32 | size => 10, |
33 | default_value => undef, |
34 | is_auto_increment => 0, |
35 | is_nullable => 1, |
36 | is_foreign_key => 0, |
37 | is_unique => 0 ); |
38 | |
39 | my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1); |
40 | |
41 | is($field1_sql, 'myfield character varying(10)', 'Create field works'); |
42 | |
43 | my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', |
44 | table => $table, |
45 | data_type => 'VARCHAR', |
46 | size => 25, |
47 | default_value => undef, |
48 | is_auto_increment => 0, |
49 | is_nullable => 0, |
50 | is_foreign_key => 0, |
51 | is_unique => 0 ); |
52 | |
53 | my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1, |
54 | $field2); |
3406fd5b |
55 | is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL |
56 | ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)], |
8c4efd11 |
57 | 'Alter field works'); |
58 | |
59 | $field1->name('field3'); |
60 | my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1); |
61 | |
3406fd5b |
62 | is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10)', 'Add field works'); |
8c4efd11 |
63 | |
64 | my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2); |
3406fd5b |
65 | is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works'); |
8c4efd11 |
66 | |
e56dabb7 |
67 | my $field3 = SQL::Translator::Schema::Field->new( name => 'time_field', |
68 | table => $table, |
69 | data_type => 'TIME', |
70 | default_value => undef, |
71 | is_auto_increment => 0, |
72 | is_nullable => 0, |
73 | is_foreign_key => 0, |
74 | is_unique => 0 ); |
75 | |
76 | my $field3_sql = SQL::Translator::Producer::PostgreSQL::create_field($field3); |
77 | |
78 | is($field3_sql, 'time_field time NOT NULL', 'Create time field works'); |
79 | |
621cb859 |
80 | my $field3_datetime_with_TZ = SQL::Translator::Schema::Field->new( |
81 | name => 'datetime_with_TZ', |
82 | table => $table, |
83 | data_type => 'timestamp with time zone', |
84 | size => 7, |
85 | ); |
86 | |
87 | my $field3_datetime_with_TZ_sql = |
88 | SQL::Translator::Producer::PostgreSQL::create_field( |
89 | $field3_datetime_with_TZ |
90 | ); |
91 | |
92 | is( |
93 | $field3_datetime_with_TZ_sql, |
94 | 'datetime_with_TZ timestamp(6) with time zone', |
95 | 'Create time field with time zone and size, works' |
96 | ); |
97 | |
98 | my $field3_time_without_TZ = SQL::Translator::Schema::Field->new( |
99 | name => 'time_without_TZ', |
100 | table => $table, |
101 | data_type => 'time without time zone', |
102 | size => 2, |
103 | ); |
104 | |
105 | my $field3_time_without_TZ_sql |
106 | = SQL::Translator::Producer::PostgreSQL::create_field( |
107 | $field3_time_without_TZ |
108 | ); |
109 | |
110 | is( |
111 | $field3_time_without_TZ_sql, |
112 | 'time_without_TZ time(2) without time zone', |
113 | 'Create time field without time zone but with size, works' |
114 | ); |
115 | |
e56dabb7 |
116 | my $field4 = SQL::Translator::Schema::Field->new( name => 'bytea_field', |
117 | table => $table, |
118 | data_type => 'bytea', |
119 | size => '16777215', |
120 | default_value => undef, |
121 | is_auto_increment => 0, |
122 | is_nullable => 0, |
123 | is_foreign_key => 0, |
124 | is_unique => 0 ); |
125 | |
126 | my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4); |
8c4efd11 |
127 | |
e56dabb7 |
128 | is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works'); |
5342f5c1 |
129 | |
130 | my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field', |
131 | table => $table, |
132 | data_type => 'enum', |
133 | extra => { list => [ 'Foo', 'Bar' ] }, |
134 | is_auto_increment => 0, |
135 | is_nullable => 0, |
136 | is_foreign_key => 0, |
137 | is_unique => 0 ); |
138 | |
139 | my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 }); |
140 | |
141 | is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works'); |
142 | |
90726ffd |
143 | |
144 | |
145 | |
146 | my $field6 = SQL::Translator::Schema::Field->new( |
147 | name => 'character', |
148 | table => $table, |
149 | data_type => 'character', |
150 | size => '123', |
151 | default_value => 'foobar', |
152 | is_auto_increment => 0, |
153 | is_nullable => 0, |
154 | is_foreign_key => 0, |
155 | is_unique => 0); |
156 | |
157 | my $field7 = SQL::Translator::Schema::Field->new( |
158 | name => 'character', |
159 | table => $table, |
160 | data_type => 'character', |
161 | size => '123', |
162 | default_value => undef, |
163 | is_auto_increment => 0, |
164 | is_nullable => 0, |
165 | is_foreign_key => 0, |
166 | is_unique => 0); |
167 | |
168 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
169 | $field7); |
170 | |
171 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP DEFAULT), 'DROP DEFAULT'); |
172 | |
173 | $field7->default_value(q(foo'bar')); |
174 | |
175 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
176 | $field7); |
177 | |
178 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT 'foo''bar'''), 'DEFAULT with escaping'); |
179 | |
180 | $field7->default_value(\q(foobar)); |
181 | |
182 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
183 | $field7); |
184 | |
185 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT foobar), 'DEFAULT unescaped if scalarref'); |
186 | |
187 | $field7->is_nullable(1); |
188 | $field7->default_value(q(foobar)); |
189 | |
190 | $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6, |
191 | $field7); |
192 | |
193 | is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP NOT NULL), 'DROP NOT NULL'); |
194 | |
ad258776 |
195 | my $field8 = SQL::Translator::Schema::Field->new( name => 'ts_field', |
196 | table => $table, |
197 | data_type => 'timestamp with time zone', |
198 | size => 6, |
199 | is_auto_increment => 0, |
200 | is_nullable => 0, |
201 | is_foreign_key => 0, |
202 | is_unique => 0 ); |
203 | |
204 | my $field8_sql = SQL::Translator::Producer::PostgreSQL::create_field($field8,{ postgres_version => 8.3 }); |
205 | |
206 | is($field8_sql, 'ts_field timestamp(6) with time zone NOT NULL', 'timestamp with precision'); |
207 | |
208 | my $field9 = SQL::Translator::Schema::Field->new( name => 'time_field', |
209 | table => $table, |
210 | data_type => 'time with time zone', |
211 | size => 6, |
212 | is_auto_increment => 0, |
213 | is_nullable => 0, |
214 | is_foreign_key => 0, |
215 | is_unique => 0 ); |
216 | |
217 | my $field9_sql = SQL::Translator::Producer::PostgreSQL::create_field($field9,{ postgres_version => 8.3 }); |
218 | |
219 | is($field9_sql, 'time_field time(6) with time zone NOT NULL', 'time with precision'); |
220 | |
221 | my $field10 = SQL::Translator::Schema::Field->new( name => 'interval_field', |
222 | table => $table, |
223 | data_type => 'interval', |
224 | size => 6, |
225 | is_auto_increment => 0, |
226 | is_nullable => 0, |
227 | is_foreign_key => 0, |
228 | is_unique => 0 ); |
229 | |
230 | my $field10_sql = SQL::Translator::Producer::PostgreSQL::create_field($field10,{ postgres_version => 8.3 }); |
231 | |
232 | is($field10_sql, 'interval_field interval(6) NOT NULL', 'time with precision'); |
233 | |
234 | |
235 | my $field11 = SQL::Translator::Schema::Field->new( name => 'time_field', |
236 | table => $table, |
237 | data_type => 'time without time zone', |
238 | size => 6, |
239 | is_auto_increment => 0, |
240 | is_nullable => 0, |
241 | is_foreign_key => 0, |
242 | is_unique => 0 ); |
243 | |
244 | my $field11_sql = SQL::Translator::Producer::PostgreSQL::create_field($field11,{ postgres_version => 8.3 }); |
245 | |
246 | is($field11_sql, 'time_field time(6) without time zone NOT NULL', 'time with precision'); |
247 | |
248 | |
249 | |
250 | my $field12 = SQL::Translator::Schema::Field->new( name => 'time_field', |
251 | table => $table, |
252 | data_type => 'timestamp', |
253 | is_auto_increment => 0, |
254 | is_nullable => 0, |
255 | is_foreign_key => 0, |
256 | is_unique => 0 ); |
257 | |
258 | my $field12_sql = SQL::Translator::Producer::PostgreSQL::create_field($field12,{ postgres_version => 8.3 }); |
259 | |
260 | is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision'); |
261 | |
90726ffd |
262 | |
bc8e2aa1 |
263 | { |
264 | # let's test default values! -- rjbs, 2008-09-30 |
265 | my %field = ( |
266 | table => $table, |
267 | data_type => 'VARCHAR', |
268 | size => 10, |
269 | is_auto_increment => 0, |
270 | is_nullable => 1, |
271 | is_foreign_key => 0, |
272 | is_unique => 0, |
273 | ); |
274 | |
275 | { |
276 | my $simple_default = SQL::Translator::Schema::Field->new( |
277 | %field, |
278 | name => 'str_default', |
279 | default_value => 'foo', |
280 | ); |
281 | |
282 | is( |
283 | $PRODUCER->($simple_default), |
284 | q{str_default character varying(10) DEFAULT 'foo'}, |
285 | 'default str', |
286 | ); |
287 | } |
288 | |
289 | { |
290 | my $null_default = SQL::Translator::Schema::Field->new( |
291 | %field, |
292 | name => 'null_default', |
293 | default_value => \'NULL', |
294 | ); |
295 | |
296 | is( |
297 | $PRODUCER->($null_default), |
298 | q{null_default character varying(10) DEFAULT NULL}, |
299 | 'default null', |
300 | ); |
301 | } |
302 | |
303 | { |
304 | my $null_default = SQL::Translator::Schema::Field->new( |
305 | %field, |
306 | name => 'null_default_2', |
307 | default_value => 'NULL', # XXX: this should go away |
308 | ); |
309 | |
310 | is( |
311 | $PRODUCER->($null_default), |
312 | q{null_default_2 character varying(10) DEFAULT NULL}, |
313 | 'default null from special cased string', |
314 | ); |
315 | } |
316 | |
317 | { |
318 | my $func_default = SQL::Translator::Schema::Field->new( |
319 | %field, |
320 | name => 'func_default', |
321 | default_value => \'func(funky)', |
322 | ); |
323 | |
324 | is( |
325 | $PRODUCER->($func_default), |
326 | q{func_default character varying(10) DEFAULT func(funky)}, |
327 | 'unquoted default from scalar ref', |
328 | ); |
329 | } |
330 | } |
331 | |
332 | |
296c2701 |
333 | my $view1 = SQL::Translator::Schema::View->new( |
334 | name => 'view_foo', |
335 | fields => [qw/id name/], |
336 | sql => 'SELECT id, name FROM thing', |
337 | ); |
338 | my $create_opts = { add_replace_view => 1, no_comments => 1 }; |
339 | my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts); |
340 | |
f59b2c0e |
341 | my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS |
296c2701 |
342 | SELECT id, name FROM thing |
f59b2c0e |
343 | "; |
296c2701 |
344 | is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL'); |
345 | |
346 | my $view2 = SQL::Translator::Schema::View->new( |
347 | name => 'view_foo2', |
348 | sql => 'SELECT id, name FROM thing', |
349 | extra => { |
350 | 'temporary' => '1', |
351 | 'check_option' => 'cascaded', |
352 | }, |
353 | ); |
354 | my $create2_opts = { add_replace_view => 1, no_comments => 1 }; |
355 | my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts); |
356 | |
f59b2c0e |
357 | my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS |
296c2701 |
358 | SELECT id, name FROM thing |
f59b2c0e |
359 | WITH CASCADED CHECK OPTION"; |
296c2701 |
360 | is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2'); |