-
Notifications
You must be signed in to change notification settings - Fork 91
/
Copy path66-postgres-dbi-parser.t
213 lines (170 loc) · 8.72 KB
/
66-postgres-dbi-parser.t
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
#!/usr/bin/perl
# vim: set ft=perl:
use strict;
use Test::More;
use SQL::Translator;
use SQL::Translator::Schema::Constants;
use Test::SQL::Translator qw(maybe_plan table_ok);
maybe_plan(undef, 'SQL::Translator::Parser::DBI::PostgreSQL');
my $pgsql;
my @dsn
= $ENV{DBICTEST_PG_DSN} ? @ENV{ map {"DBICTEST_PG_$_"} qw/DSN USER PASS/ }
: $ENV{DBI_DSN} ? @ENV{ map {"DBI_$_"} qw/DSN USER PASS/ }
: eval { require Test::PostgreSQL and ($pgsql = Test::PostgreSQL->new()) } ? ($pgsql->dsn, '', '')
: plan skip_all => 'Set $ENV{DBICTEST_PG_DSN}, _USER and _PASS to run this test, or install Test::PostgreSQL';
my $dbh = eval { DBI->connect(@dsn, { AutoCommit => 1, RaiseError => 1, PrintError => 1 }); };
if (my $err = ($@ || $DBI::err)) {
chomp $err;
plan skip_all => "No connection to test db. DBI says '$err'";
}
# Cleanly shut down Test::PostgreSQL if it is being used
END { undef $dbh; undef $pgsql; }
ok($dbh, "dbh setup correctly");
$dbh->do('SET client_min_messages=WARNING');
my $sql = q[
drop table if exists sqlt_test2;
drop table if exists sqlt_test1;
drop table if exists sqlt_products_1;
drop type if exists example_enum;
create type example_enum as enum('alpha','beta');
create table sqlt_test1 (
f_serial serial NOT NULL primary key,
f_varchar character varying(255),
f_text text default 'FOO',
f_to_drop integer,
f_text2 text,
f_enum example_enum default 'alpha'
);
comment on table sqlt_test1 is 'this is a comment on the first table';
comment on column sqlt_test1.f_text is 'this is a comment on a field of the first table';
create index sqlt_test1_f_text2_idx on sqlt_test1 (f_text2);
create table sqlt_test2 (
f_id integer NOT NULL,
f_int smallint,
f_fk1 integer NOT NULL references sqlt_test1 (f_serial),
primary key (f_id, f_fk1)
);
CREATE TABLE sqlt_products_1 (
product_no integer,
name text default '['''']',
price numeric(8,4) default 0.0,
created_at timestamp without time zone default now()
);
-- drop a column, to not have a linear id
-- When the table t_test1 is created, f_text2 get id 5 but
-- after this drop, there is only 4 columns.
alter table sqlt_test1 drop column f_to_drop;
];
$| = 1;
$dbh->begin_work;
$dbh->do($sql);
my $t = SQL::Translator->new(
trace => 0,
parser => 'DBI',
parser_args => { dbh => $dbh, deconstruct_enum_types => 1 },
);
$t->translate;
my $schema = $t->schema;
isa_ok($schema, 'SQL::Translator::Schema', 'Schema object');
ok($dbh->ping, 'External handle still connected');
my @tables = $schema->get_tables;
my $t1 = $schema->get_table("sqlt_test1");
is($t1->name, 'sqlt_test1', 'Table sqlt_test1 exists');
is($t1->comments, 'this is a comment on the first table', 'First table has a comment');
my @t1_fields = $t1->get_fields;
is(scalar @t1_fields, 5, '5 fields in sqlt_test1');
my $f1 = shift @t1_fields;
is($f1->name, 'f_serial', 'First field is "f_serial"');
is($f1->data_type, 'integer', 'Field is an integer');
is($f1->is_nullable, 0, 'Field cannot be null');
is(${ $f1->default_value }, "nextval('sqlt_test1_f_serial_seq'::regclass)", 'Default value is nextval()');
is($f1->is_primary_key, 1, 'Field is PK');
#FIXME: not set to auto-increment? maybe we can guess auto-increment behavior by looking at the default_value (i.e. it call function nextval() )
#is( $f1->is_auto_increment, 1, 'Field is auto increment' );
my $f2 = shift @t1_fields;
is($f2->name, 'f_varchar', 'Second field is "f_varchar"');
is($f2->data_type, 'character varying', 'Field is a character varying(255)');
is($f2->is_nullable, 1, 'Field can be null');
is($f2->size, 255, 'Size is "255"');
is($f2->default_value, undef, 'Default value is undefined');
is($f2->is_primary_key, 0, 'Field is not PK');
is($f2->is_auto_increment, 0, 'Field is not auto increment');
is($f2->comments, '', 'There is no comment on the second field');
my $f3 = shift @t1_fields;
is($f3->name, 'f_text', 'Third field is "f_text"');
is($f3->data_type, 'text', 'Field is a text');
is($f3->is_nullable, 1, 'Field can be null');
is($f3->size, 0, 'Size is 0');
is($f3->default_value, 'FOO', 'Default value is "FOO"');
is($f3->is_primary_key, 0, 'Field is not PK');
is($f3->is_auto_increment, 0, 'Field is not auto increment');
is($f3->comments, 'this is a comment on a field of the first table', 'There is a comment on the third field');
my $f4 = shift @t1_fields;
is($f4->name, 'f_text2', 'Fouth field is "f_text2"');
is($f4->data_type, 'text', 'Field is a text');
is($f4->is_nullable, 1, 'Field can be null');
is($f4->size, 0, 'Size is 0');
is($f4->default_value, undef, 'No default value');
is($f4->is_primary_key, 0, 'Field is not PK');
is($f4->is_auto_increment, 0, 'Field is not auto increment');
my $f5 = shift @t1_fields;
is($f5->name, 'f_enum', 'Fifth field is "f_enum"');
is($f5->data_type, 'enum', 'Field is a decomposed enum');
is($f5->default_value, 'alpha', 'Default value "alpha"');
is_deeply(
{ $f5->extra },
{ custom_type_name => 'example_enum', list => [ 'alpha', 'beta' ] },
'Field "extra" enum description'
);
#TODO: no 'NOT NULL' constraint not set
my $t2 = $schema->get_table("sqlt_test2");
is($t2->name, 'sqlt_test2', 'Table sqlt_test2 exists');
is($t2->comments, undef, 'No comment on table sqlt_test2');
my @t2_fields = $t2->get_fields;
is(scalar @t2_fields, 3, '3 fields in sqlt_test2');
my $t2_f1 = shift @t2_fields;
is($t2_f1->name, 'f_id', 'First field is "f_id"');
is($t2_f1->data_type, 'integer', 'Field is an integer');
is($t2_f1->is_nullable, 0, 'Field cannot be null');
is($t2_f1->size, 0, 'Size is "0"');
is($t2_f1->default_value, undef, 'Default value is undefined');
is($t2_f1->is_primary_key, 1, 'Field is PK');
my $t2_f2 = shift @t2_fields;
is($t2_f2->name, 'f_int', 'Third field is "f_int"');
is($t2_f2->data_type, 'smallint', 'Field is an smallint');
is($t2_f2->is_nullable, 1, 'Field can be null');
is($t2_f2->size, 0, 'Size is "0"');
is($t2_f2->default_value, undef, 'Default value is undefined');
is($t2_f2->is_primary_key, 0, 'Field is not PK');
my $t2_f3 = shift @t2_fields;
is($t2_f3->name, 'f_fk1', 'Third field is "f_fk1"');
is($t2_f3->data_type, 'integer', 'Field is an integer');
is($t2_f3->is_nullable, 0, 'Field cannot be null');
is($t2_f3->size, 0, 'Size is "0"');
is($t2_f3->default_value, undef, 'Default value is undefined');
is($t2_f3->is_primary_key, 1, 'Field is PK');
is($t2_f3->is_foreign_key, 1, 'Field is a FK');
my $fk_ref1 = $t2_f3->foreign_key_reference;
isa_ok($fk_ref1, 'SQL::Translator::Schema::Constraint', 'FK');
is($fk_ref1->reference_table, 'sqlt_test1', 'FK is to "sqlt_test1" table');
my $t3 = $schema->get_table("sqlt_products_1");
my $t3_f2 = $t3->get_field('name');
is($t3_f2->data_type, 'text', 'Second field, type "text"');
is($t3_f2->default_value, q{['']}, 'default value is json array of empty string');
my $t3_f3 = $t3->get_field('price');
is($t3_f3->name, 'price', 'Third field is "price"');
is($t3_f3->data_type, 'numeric', 'Third field type "numeric"');
is_deeply([ $t3_f3->size ], [ 8, 4 ], 'Third field size "(8,4)"');
is($t3_f3->default_value, '0.0', 'Third field default "0.0"');
my $t3_f4 = $t3->get_field('created_at');
is($t3_f4->name, 'created_at', 'fourth field is "created_at"');
is($t3_f4->data_type, 'timestamp without time zone', 'type is "timestamp without time zone"');
is($t2_f3->size, 0, 'Size is "0"');
is_deeply($t3_f4->default_value, \"now()", 'default \\"now()"');
my @t2_constraints = $t2->get_constraints;
is(scalar @t2_constraints, 1, "One constraint on table");
my $t2_c1 = shift @t2_constraints;
is($t2_c1->type, FOREIGN_KEY, "Constraint is a FK");
$dbh->rollback;
$dbh->disconnect;
done_testing();