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