10 use Test::Differences;
12 use SQL::Translator::Object::Table;
13 use SQL::Translator::Object::Column;
15 my $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field;
17 my $table = SQL::Translator::Object::Table->new( name => 'mytable');
19 my $field1 = SQL::Translator::Object::Column->new( name => 'myfield',
21 data_type => 'VARCHAR',
23 default_value => undef,
24 is_auto_increment => 0,
29 my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1);
31 is($field1_sql, 'myfield character varying(10)', 'Create field works');
33 my $field2 = SQL::Translator::Object::Column->new( name => 'myfield',
35 data_type => 'VARCHAR',
37 default_value => undef,
38 is_auto_increment => 0,
43 my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
45 is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL
46 ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)],
49 $field1->name('field3');
50 my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1);
52 is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10)', 'Add field works');
54 my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2);
55 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
57 my $field3 = SQL::Translator::Object::Column->new( name => 'time_field',
60 default_value => undef,
61 is_auto_increment => 0,
66 my $field3_sql = SQL::Translator::Producer::PostgreSQL::create_field($field3);
68 is($field3_sql, 'time_field time NOT NULL', 'Create time field works');
70 my $field3_datetime_with_TZ = SQL::Translator::Object::Column->new(
71 name => 'datetime_with_TZ',
73 data_type => 'timestamp with time zone',
77 my $field3_datetime_with_TZ_sql =
78 SQL::Translator::Producer::PostgreSQL::create_field(
79 $field3_datetime_with_TZ
83 $field3_datetime_with_TZ_sql,
84 'datetime_with_TZ timestamp(6) with time zone',
85 'Create time field with time zone and size, works'
88 my $field3_time_without_TZ = SQL::Translator::Object::Column->new(
89 name => 'time_without_TZ',
91 data_type => 'time without time zone',
95 my $field3_time_without_TZ_sql
96 = SQL::Translator::Producer::PostgreSQL::create_field(
97 $field3_time_without_TZ
101 $field3_time_without_TZ_sql,
102 'time_without_TZ time(2) without time zone',
103 'Create time field without time zone but with size, works'
106 my $field4 = SQL::Translator::Object::Column->new( name => 'bytea_field',
108 data_type => 'bytea',
110 default_value => undef,
111 is_auto_increment => 0,
116 my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4);
118 is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works');
120 my $field5 = SQL::Translator::Object::Column->new( name => 'enum_field',
123 extra => { list => [ 'Foo', 'Bar' ] },
124 is_auto_increment => 0,
129 my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 });
131 is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works');
136 my $field6 = SQL::Translator::Object::Column->new(
139 data_type => 'character',
141 default_value => 'foobar',
142 is_auto_increment => 0,
147 my $field7 = SQL::Translator::Object::Column->new(
150 data_type => 'character',
152 default_value => undef,
153 is_auto_increment => 0,
158 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
161 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP DEFAULT), 'DROP DEFAULT');
163 $field7->default_value(q(foo'bar'));
165 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
168 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT 'foo''bar'''), 'DEFAULT with escaping');
170 $field7->default_value(\q(foobar));
172 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
175 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT foobar), 'DEFAULT unescaped if scalarref');
177 $field7->is_nullable(1);
178 $field7->default_value(q(foobar));
180 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
183 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP NOT NULL), 'DROP NOT NULL');
185 my $field8 = SQL::Translator::Object::Column->new( name => 'ts_field',
187 data_type => 'timestamp with time zone',
189 is_auto_increment => 0,
194 my $field8_sql = SQL::Translator::Producer::PostgreSQL::create_field($field8,{ postgres_version => 8.3 });
196 is($field8_sql, 'ts_field timestamp(6) with time zone NOT NULL', 'timestamp with precision');
198 my $field9 = SQL::Translator::Object::Column->new( name => 'time_field',
200 data_type => 'time with time zone',
202 is_auto_increment => 0,
207 my $field9_sql = SQL::Translator::Producer::PostgreSQL::create_field($field9,{ postgres_version => 8.3 });
209 is($field9_sql, 'time_field time(6) with time zone NOT NULL', 'time with precision');
211 my $field10 = SQL::Translator::Object::Column->new( name => 'interval_field',
213 data_type => 'interval',
215 is_auto_increment => 0,
220 my $field10_sql = SQL::Translator::Producer::PostgreSQL::create_field($field10,{ postgres_version => 8.3 });
222 is($field10_sql, 'interval_field interval(6) NOT NULL', 'time with precision');
225 my $field11 = SQL::Translator::Object::Column->new( name => 'time_field',
227 data_type => 'time without time zone',
229 is_auto_increment => 0,
234 my $field11_sql = SQL::Translator::Producer::PostgreSQL::create_field($field11,{ postgres_version => 8.3 });
236 is($field11_sql, 'time_field time(6) without time zone NOT NULL', 'time with precision');
240 my $field12 = SQL::Translator::Object::Column->new( name => 'time_field',
242 data_type => 'timestamp',
243 is_auto_increment => 0,
248 my $field12_sql = SQL::Translator::Producer::PostgreSQL::create_field($field12,{ postgres_version => 8.3 });
250 is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision');
254 # let's test default values! -- rjbs, 2008-09-30
257 data_type => 'VARCHAR',
259 is_auto_increment => 0,
266 my $simple_default = SQL::Translator::Object::Column->new(
268 name => 'str_default',
269 default_value => 'foo',
273 $PRODUCER->($simple_default),
274 q{str_default character varying(10) DEFAULT 'foo'},
280 my $null_default = SQL::Translator::Object::Column->new(
282 name => 'null_default',
283 default_value => \'NULL',
287 $PRODUCER->($null_default),
288 q{null_default character varying(10) DEFAULT NULL},
294 my $null_default = SQL::Translator::Object::Column->new(
296 name => 'null_default_2',
297 default_value => 'NULL', # XXX: this should go away
301 $PRODUCER->($null_default),
302 q{null_default_2 character varying(10) DEFAULT NULL},
303 'default null from special cased string',
308 my $func_default = SQL::Translator::Object::Column->new(
310 name => 'func_default',
311 default_value => \'func(funky)',
315 $PRODUCER->($func_default),
316 q{func_default character varying(10) DEFAULT func(funky)},
317 'unquoted default from scalar ref',
323 my $view1 = SQL::Translator::Schema::View->new(
325 fields => [qw/id name/],
326 sql => 'SELECT id, name FROM thing',
328 my $create_opts = { add_replace_view => 1, no_comments => 1 };
329 my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts);
331 my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS
332 SELECT id, name FROM thing
334 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
336 my $view2 = SQL::Translator::Schema::View->new(
338 sql => 'SELECT id, name FROM thing',
341 'check_option' => 'cascaded',
344 my $create2_opts = { add_replace_view => 1, no_comments => 1 };
345 my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts);
347 my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS
348 SELECT id, name FROM thing
349 WITH CASCADED CHECK OPTION";
350 is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2');