Commit | Line | Data |
122353c5 |
1 | #!/usr/bin/perl |
2 | # vim: set ft=perl: |
3 | |
4 | use strict; |
5 | use Test::More; |
6 | use SQL::Translator; |
7 | use SQL::Translator::Schema::Constants; |
8 | use Test::SQL::Translator qw(maybe_plan table_ok); |
9 | |
0142de97 |
10 | maybe_plan(undef, 'SQL::Translator::Parser::DBI::PostgreSQL'); |
122353c5 |
11 | |
12 | my @dsn = |
13 | $ENV{DBICTEST_PG_DSN} ? @ENV{ map { "DBICTEST_PG_$_" } qw/DSN USER PASS/ } |
0142de97 |
14 | : $ENV{DBI_DSN} ? @ENV{ map { "DBI_$_" } qw/DSN USER PASS/ } |
15 | : plan skip_all => 'Set $ENV{DBICTEST_PG_DSN}, _USER and _PASS to run this test'; |
122353c5 |
16 | |
17 | my $dbh = eval { |
18 | DBI->connect(@dsn, {AutoCommit => 1, RaiseError=>1,PrintError => 1} ); |
19 | }; |
20 | |
0142de97 |
21 | if (my $err = ($@ || $DBI::err )) { |
22 | chomp $err; |
23 | plan skip_all => "No connection to test db. DBI says '$err'"; |
24 | } |
122353c5 |
25 | |
0142de97 |
26 | ok($dbh, "dbh setup correctly"); |
27 | $dbh->do('SET client_min_messages=WARNING'); |
122353c5 |
28 | |
122353c5 |
29 | my $sql = q[ |
30 | drop table if exists sqlt_test2; |
31 | drop table if exists sqlt_test1; |
32 | drop table if exists sqlt_products_1; |
33 | |
34 | create table sqlt_test1 ( |
35 | f_serial serial NOT NULL primary key, |
a23f9a9d |
36 | f_varchar character varying(255), |
c601ca5d |
37 | f_text text default 'FOO', |
38 | f_to_drop integer, |
39 | f_last text |
122353c5 |
40 | ); |
41 | |
fd52d7dd |
42 | comment on table sqlt_test1 is 'this is a comment on the first table'; |
43 | comment on column sqlt_test1.f_text is 'this is a comment on a field of the first table'; |
44 | |
c601ca5d |
45 | create index sqlt_test1_f_last_idx on sqlt_test1 (f_last); |
122353c5 |
46 | |
47 | create table sqlt_test2 ( |
48 | f_id integer NOT NULL, |
49 | f_int smallint, |
50 | primary key (f_id), |
51 | f_fk1 integer NOT NULL references sqlt_test1 (f_serial) |
52 | ); |
53 | |
54 | CREATE TABLE sqlt_products_1 ( |
55 | product_no integer, |
56 | name text, |
57 | price numeric |
58 | ); |
c601ca5d |
59 | |
aee4b66e |
60 | -- drop a column, to not have a linear id |
c601ca5d |
61 | -- When the table t_test1 is created, f_last get id 5 but |
62 | -- after this drop, there is only 4 columns. |
63 | alter table sqlt_test1 drop column f_to_drop; |
122353c5 |
64 | ]; |
65 | |
66 | $| = 1; |
67 | |
7b1bb658 |
68 | $dbh->begin_work; |
122353c5 |
69 | $dbh->do($sql); |
70 | |
c3919fdd |
71 | my $t = SQL::Translator->new( |
72 | trace => 0, |
73 | parser => 'DBI', |
74 | parser_args => { dbh => $dbh }, |
75 | ); |
76 | $t->translate; |
122353c5 |
77 | my $schema = $t->schema; |
78 | |
79 | isa_ok( $schema, 'SQL::Translator::Schema', 'Schema object' ); |
c3919fdd |
80 | |
81 | ok ($dbh->ping, 'External handle still connected'); |
82 | |
122353c5 |
83 | my @tables = $schema->get_tables; |
84 | |
85 | my $t1 = $schema->get_table("sqlt_test1"); |
86 | is( $t1->name, 'sqlt_test1', 'Table sqlt_test1 exists' ); |
fd52d7dd |
87 | is( $t1->comments, 'this is a comment on the first table', 'First table has a comment'); |
122353c5 |
88 | |
89 | my @t1_fields = $t1->get_fields; |
c601ca5d |
90 | is( scalar @t1_fields, 4, '4 fields in sqlt_test1' ); |
122353c5 |
91 | |
92 | my $f1 = shift @t1_fields; |
93 | is( $f1->name, 'f_serial', 'First field is "f_serial"' ); |
a23f9a9d |
94 | is( $f1->data_type, 'integer', 'Field is an integer' ); |
122353c5 |
95 | is( $f1->is_nullable, 0, 'Field cannot be null' ); |
96 | is( $f1->default_value, "nextval('sqlt_test1_f_serial_seq'::regclass)", 'Default value is nextval()' ); |
97 | is( $f1->is_primary_key, 1, 'Field is PK' ); |
98 | #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() ) |
99 | #is( $f1->is_auto_increment, 1, 'Field is auto increment' ); |
100 | |
101 | my $f2 = shift @t1_fields; |
102 | is( $f2->name, 'f_varchar', 'Second field is "f_varchar"' ); |
a23f9a9d |
103 | is( $f2->data_type, 'character varying(255)', 'Field is a character varying(255)' ); |
122353c5 |
104 | is( $f2->is_nullable, 1, 'Field can be null' ); |
105 | #FIXME: should not be 255? |
106 | is( $f2->size, 259, 'Size is "259"' ); |
107 | is( $f2->default_value, undef, 'Default value is undefined' ); |
108 | is( $f2->is_primary_key, 0, 'Field is not PK' ); |
109 | is( $f2->is_auto_increment, 0, 'Field is not auto increment' ); |
fd52d7dd |
110 | is( $f2->comments, '', 'There is no comment on the second field'); |
122353c5 |
111 | |
112 | my $f3 = shift @t1_fields; |
113 | is( $f3->name, 'f_text', 'Third field is "f_text"' ); |
114 | is( $f3->data_type, 'text', 'Field is a text' ); |
115 | is( $f3->is_nullable, 1, 'Field can be null' ); |
116 | is( $f3->size, 0, 'Size is 0' ); |
117 | is( $f3->default_value, "'FOO'::text", 'Default value is "FOO"' ); |
118 | is( $f3->is_primary_key, 0, 'Field is not PK' ); |
119 | is( $f3->is_auto_increment, 0, 'Field is not auto increment' ); |
fd52d7dd |
120 | is( $f3->comments, 'this is a comment on a field of the first table', 'There is a comment on the third field'); |
122353c5 |
121 | |
c601ca5d |
122 | my $f4 = shift @t1_fields; |
123 | is( $f4->name, 'f_last', 'Fouth field is "f_last"' ); |
124 | is( $f4->data_type, 'text', 'Field is a text' ); |
125 | is( $f4->is_nullable, 1, 'Field can be null' ); |
126 | is( $f4->size, 0, 'Size is 0' ); |
127 | is( $f4->default_value, undef, 'No default value' ); |
128 | is( $f4->is_primary_key, 0, 'Field is not PK' ); |
129 | is( $f4->is_auto_increment, 0, 'Field is not auto increment' ); |
130 | |
122353c5 |
131 | #TODO: no 'NOT NULL' constraint not set |
132 | |
133 | my $t2 = $schema->get_table("sqlt_test2"); |
134 | is( $t2->name, 'sqlt_test2', 'Table sqlt_test2 exists' ); |
fd52d7dd |
135 | is( $t2->comments, undef, 'No comment on table sqlt_test2'); |
122353c5 |
136 | |
137 | my @t2_fields = $t2->get_fields; |
138 | is( scalar @t2_fields, 3, '3 fields in sqlt_test2' ); |
139 | |
140 | my $t2_f1 = shift @t2_fields; |
141 | is( $t2_f1->name, 'f_id', 'First field is "f_id"' ); |
a23f9a9d |
142 | is( $t2_f1->data_type, 'integer', 'Field is an integer' ); |
122353c5 |
143 | is( $t2_f1->is_nullable, 0, 'Field cannot be null' ); |
144 | is( $t2_f1->size, 0, 'Size is "0"' ); |
145 | is( $t2_f1->default_value, undef, 'Default value is undefined' ); |
146 | is( $t2_f1->is_primary_key, 1, 'Field is PK' ); |
147 | |
148 | my $t2_f2= shift @t2_fields; |
149 | is( $t2_f2->name, 'f_int', 'Third field is "f_int"' ); |
a23f9a9d |
150 | is( $t2_f2->data_type, 'smallint', 'Field is an smallint' ); |
122353c5 |
151 | is( $t2_f2->is_nullable, 1, 'Field can be null' ); |
152 | is( $t2_f2->size, 0, 'Size is "0"' ); |
153 | is( $t2_f2->default_value, undef, 'Default value is undefined' ); |
154 | is( $t2_f2->is_primary_key, 0, 'Field is not PK' ); |
155 | |
156 | my $t2_f3 = shift @t2_fields; |
157 | is( $t2_f3->name, 'f_fk1', 'Third field is "f_fk1"' ); |
a23f9a9d |
158 | is( $t2_f3->data_type, 'integer', 'Field is an integer' ); |
122353c5 |
159 | is( $t2_f3->is_nullable, 0, 'Field cannot be null' ); |
160 | is( $t2_f3->size, 0, 'Size is "0"' ); |
161 | is( $t2_f3->default_value, undef, 'Default value is undefined' ); |
162 | is( $t2_f3->is_primary_key, 0, 'Field is not PK' ); |
163 | is( $t2_f3->is_foreign_key, 1, 'Field is a FK' ); |
164 | my $fk_ref1 = $t2_f3->foreign_key_reference; |
165 | isa_ok( $fk_ref1, 'SQL::Translator::Schema::Constraint', 'FK' ); |
166 | is( $fk_ref1->reference_table, 'sqlt_test1', 'FK is to "sqlt_test1" table' ); |
167 | |
168 | my @t2_constraints = $t2->get_constraints; |
169 | is( scalar @t2_constraints, 1, "One constraint on table" ); |
170 | |
171 | my $t2_c1 = shift @t2_constraints; |
172 | is( $t2_c1->type, FOREIGN_KEY, "Constraint is a FK" ); |
173 | |
7b1bb658 |
174 | $dbh->rollback; |
122353c5 |
175 | $dbh->disconnect; |
122353c5 |
176 | |
0142de97 |
177 | done_testing(); |