16 |
|
|
17 |
@EXPORT_OK = qw( |
@EXPORT_OK = qw( |
18 |
&list_tables |
&list_tables |
19 |
|
&get_table_oid |
20 |
|
&explain |
21 |
|
&pg_attribute |
22 |
|
&cols |
23 |
|
&cols_notnull |
24 |
|
&cols_null |
25 |
|
&cols_pk |
26 |
|
&cols_notpk |
27 |
); |
); |
28 |
|
|
29 |
my $debug; |
my $debug; |
96 |
return $row->{oid}; |
return $row->{oid}; |
97 |
} |
} |
98 |
|
|
99 |
sub explain_table { |
sub explain { |
100 |
my $self = shift; |
my $self = shift; |
101 |
my $table = shift; |
my $table = shift; |
102 |
|
|
103 |
my @explain; |
# XXX if explained, return just results |
104 |
|
# return $self->{'explained'}->{$table}->{'pg_attribute'} if ($self->{'explained'}->{$table}->{'pg_attribute'}); |
105 |
|
my @pg_attribute; |
106 |
|
|
107 |
my $oid = $self->get_table_oid($table); |
my $oid = $self->get_table_oid($table); |
108 |
|
|
115 |
ORDER BY a.attnum |
ORDER BY a.attnum |
116 |
"; |
"; |
117 |
|
|
118 |
|
my @cols; # all colums (for insert) |
119 |
|
my @cols_null; # colums compared by a=b or a is null and b is null |
120 |
|
my @cols_notnull;# colums compared by a=b |
121 |
|
|
122 |
my $sth = $self->{'dbh'}->prepare($sql); |
my $sth = $self->{'dbh'}->prepare($sql); |
123 |
$sth->execute($oid) || die; |
$sth->execute($oid) || die; |
124 |
while(my $row = $sth->fetchrow_hashref()) { |
while(my $row = $sth->fetchrow_hashref()) { |
125 |
# attname | format_type | attnotnull | atthasdef | attnum |
# attname | format_type | attnotnull | atthasdef | attnum |
126 |
push @explain, $row; |
push @pg_attribute, $row; |
127 |
|
push @cols,$row->{attname}; |
128 |
|
|
129 |
|
if ($row->{attnotnull}) { |
130 |
|
push @cols_notnull,$row->{attname}; |
131 |
|
} else { |
132 |
|
push @cols_null,$row->{attname}; |
133 |
|
} |
134 |
|
|
135 |
|
} |
136 |
|
|
137 |
|
@{$self->{'explained'}->{$table}->{'pg_attribute'}} = @pg_attribute; |
138 |
|
@{$self->{'explained'}->{$table}->{'cols'}} = @cols; |
139 |
|
@{$self->{'explained'}->{$table}->{'cols_notnull'}} = @cols_notnull; |
140 |
|
@{$self->{'explained'}->{$table}->{'cols_null'}} = @cols_null; |
141 |
|
|
142 |
|
# all, just for safe keeping |
143 |
|
@{$self->{'explained'}->{$table}->{'cols_notpk'}} = @cols; |
144 |
|
|
145 |
|
# now, try to find primary key |
146 |
|
|
147 |
|
my @cols_pk; # columns which are primary key |
148 |
|
my @cols_notpk; |
149 |
|
my %in_pk; |
150 |
|
|
151 |
|
$sql=" |
152 |
|
SELECT |
153 |
|
i.indexrelid as indexrelid, i.indrelid as indrelid, |
154 |
|
count(a.attname) as cols_in_pk |
155 |
|
FROM |
156 |
|
pg_catalog.pg_class c, |
157 |
|
pg_catalog.pg_index i, |
158 |
|
pg_catalog.pg_attribute a |
159 |
|
WHERE |
160 |
|
c.oid = i.indrelid |
161 |
|
and i.indisunique |
162 |
|
and c.relname = '$table' |
163 |
|
and a.attrelid = i.indexrelid |
164 |
|
GROUP BY |
165 |
|
i.indexrelid, i.indrelid, c.relname, i.indisprimary, i.indisunique |
166 |
|
ORDER BY |
167 |
|
cols_in_pk ASC, i.indisprimary DESC, i.indisunique DESC, c.relname DESC |
168 |
|
"; |
169 |
|
print STDERR "DEBUG: $sql\n" if ($debug); |
170 |
|
$sth = $self->{'dbh'}->prepare($sql); |
171 |
|
$sth->execute() || die; |
172 |
|
my $row = $sth->fetchrow_hashref(); |
173 |
|
if ($row) { |
174 |
|
$sql=" |
175 |
|
select a1.attname as attname from pg_attribute a1, pg_attribute a2 where a1.attrelid = ".$row->{indexrelid}." and a2.attrelid=".$row->{indrelid}." and a1.attname = a2.attname and a2.attnotnull"; |
176 |
|
|
177 |
|
my $sth2 = $self->{'dbh'}->prepare($sql); |
178 |
|
print STDERR "DEBUG: $sql\n" if ($debug); |
179 |
|
$sth2->execute() || die; |
180 |
|
while (my $row2 = $sth2->fetchrow_hashref()) { |
181 |
|
push @cols_pk,$row2->{attname}; |
182 |
|
$in_pk{$row2->{attname}}++; |
183 |
|
} |
184 |
|
|
185 |
|
} |
186 |
|
|
187 |
|
foreach my $col (@cols) { |
188 |
|
push @cols_notpk,$col if (! $in_pk{$col}); |
189 |
|
} |
190 |
|
|
191 |
|
@{$self->{'explained'}->{$table}->{cols_pk}} = @cols_pk; |
192 |
|
@{$self->{'explained'}->{$table}->{cols_notpk}} = @cols_notpk; |
193 |
|
|
194 |
|
return @pg_attribute; |
195 |
|
} |
196 |
|
|
197 |
|
# return all rows in PostgreSQL format |
198 |
|
# attname | format_type | attnotnull | atthasdef | attnum |
199 |
|
# |
200 |
|
# somewhat internal function, but still usefull if you want to |
201 |
|
# do tweaking of columns your way |
202 |
|
# |
203 |
|
sub pg_attribute { |
204 |
|
my $self = shift; |
205 |
|
my $table = shift; |
206 |
|
|
207 |
|
if (! $self->{'explained'}->{$table}) { |
208 |
|
$self->explain($table); |
209 |
|
} |
210 |
|
|
211 |
|
return $self->{'explained'}->{$table}->{pg_attribute}; |
212 |
|
} |
213 |
|
|
214 |
|
# return columns in given table |
215 |
|
sub cols { |
216 |
|
my $self = shift; |
217 |
|
my $table = shift; |
218 |
|
|
219 |
|
if (! $self->{'explained'}->{$table}) { |
220 |
|
$self->explain($table); |
221 |
|
} |
222 |
|
|
223 |
|
return $self->{'explained'}->{$table}->{cols}; |
224 |
|
} |
225 |
|
|
226 |
|
# return not null columns in given table |
227 |
|
sub cols_notnull { |
228 |
|
my $self = shift; |
229 |
|
my $table = shift; |
230 |
|
|
231 |
|
if (! $self->{'explained'}->{$table}) { |
232 |
|
$self->explain($table); |
233 |
|
} |
234 |
|
|
235 |
|
return $self->{'explained'}->{$table}->{cols_notnull}; |
236 |
|
} |
237 |
|
|
238 |
|
# return columns which *can* be null in given table |
239 |
|
sub cols_null { |
240 |
|
my $self = shift; |
241 |
|
my $table = shift; |
242 |
|
|
243 |
|
if (! $self->{'explained'}->{$table}) { |
244 |
|
$self->explain($table); |
245 |
|
} |
246 |
|
|
247 |
|
return $self->{'explained'}->{$table}->{cols_null}; |
248 |
|
} |
249 |
|
|
250 |
|
# return primary key columns |
251 |
|
sub cols_pk { |
252 |
|
my $self = shift; |
253 |
|
my $table = shift; |
254 |
|
|
255 |
|
if (! $self->{'explained'}->{$table}) { |
256 |
|
$self->explain($table); |
257 |
|
} |
258 |
|
|
259 |
|
return $self->{'explained'}->{$table}->{cols_pk}; |
260 |
|
} |
261 |
|
|
262 |
|
# return columns not in primary key |
263 |
|
sub cols_notpk { |
264 |
|
my $self = shift; |
265 |
|
my $table = shift; |
266 |
|
|
267 |
|
if (! $self->{'explained'}->{$table}) { |
268 |
|
$self->explain($table); |
269 |
} |
} |
|
$sth->finish(); |
|
270 |
|
|
271 |
return @explain; |
return $self->{'explained'}->{$table}->{cols_notpk}; |
272 |
} |
} |
273 |
1; |
1; |