Commit | Line | Data |
80ae061a |
1 | package SQL::Translator::Parser::DBI::PostgreSQL; |
2 | |
3 | # ------------------------------------------------------------------- |
478f608d |
4 | # Copyright (C) 2002-2009 SQLFairy Authors |
80ae061a |
5 | # |
6 | # This program is free software; you can redistribute it and/or |
7 | # modify it under the terms of the GNU General Public License as |
8 | # published by the Free Software Foundation; version 2. |
9 | # |
10 | # This program is distributed in the hope that it will be useful, but |
11 | # WITHOUT ANY WARRANTY; without even the implied warranty of |
12 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
13 | # General Public License for more details. |
14 | # |
15 | # You should have received a copy of the GNU General Public License |
16 | # along with this program; if not, write to the Free Software |
17 | # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA |
18 | # 02111-1307 USA |
19 | # ------------------------------------------------------------------- |
20 | |
21 | =head1 NAME |
22 | |
23 | SQL::Translator::Parser::DBI::PostgreSQL - parser for DBD::Pg |
24 | |
25 | =head1 SYNOPSIS |
26 | |
27 | See SQL::Translator::Parser::DBI. |
28 | |
29 | =head1 DESCRIPTION |
30 | |
d2522b19 |
31 | Uses DBI to query PostgreSQL system tables to determine schema structure. |
80ae061a |
32 | |
33 | =cut |
34 | |
35 | use strict; |
36 | use DBI; |
80ae061a |
37 | use Data::Dumper; |
38 | use SQL::Translator::Schema::Constants; |
39 | |
da06ac74 |
40 | use vars qw[ $DEBUG $VERSION @EXPORT_OK ]; |
ba506e52 |
41 | $VERSION = '1.60'; |
80ae061a |
42 | $DEBUG = 0 unless defined $DEBUG; |
43 | |
cfeaa28f |
44 | my $actions = {c => 'cascade', |
45 | r => 'restrict', |
46 | a => 'no action', |
47 | n => 'set null', |
48 | d => 'set default', |
49 | }; |
50 | |
80ae061a |
51 | # ------------------------------------------------------------------- |
52 | sub parse { |
53 | my ( $tr, $dbh ) = @_; |
54 | |
638e82c2 |
55 | my $schema = $tr->schema; |
80ae061a |
56 | |
d2522b19 |
57 | my $column_select = $dbh->prepare( |
58 | "SELECT a.attname, t.typname, a.attnum,a.atttypmod as length, |
59 | a.attnotnull, a.atthasdef, d.adsrc |
60 | FROM pg_type t,pg_attribute a |
61 | LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum) |
62 | WHERE a.attrelid=? AND attnum>0 |
63 | AND a.atttypid=t.oid |
64 | ORDER BY a.attnum" |
65 | ); |
66 | |
67 | my $index_select = $dbh->prepare( |
68 | "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique, |
69 | i.indisprimary, pg_get_indexdef(oid) AS create_string |
70 | FROM pg_class c,pg_index i |
71 | WHERE c.relnamespace=2200 AND c.relkind='i' |
72 | AND c.oid=i.indexrelid AND i.indrelid=?" |
73 | ); |
74 | |
75 | my $table_select = $dbh->prepare( |
76 | "SELECT oid,relname FROM pg_class WHERE relnamespace IN |
77 | (SELECT oid FROM pg_namespace WHERE nspname='public') |
78 | AND relkind='r';" |
79 | ); |
cfeaa28f |
80 | |
81 | my $fk_select = $dbh->prepare( |
82 | q/ |
83 | SELECT r.conname, |
84 | c.relname, |
85 | d.relname AS frelname, |
86 | r.conkey, |
87 | ARRAY(SELECT column_name::varchar |
88 | FROM information_schema.columns |
89 | WHERE ordinal_position = ANY (r.conkey) |
90 | AND table_schema = n.nspname |
91 | AND table_name = c.relname ) AS fields, |
92 | r.confkey, |
93 | ARRAY(SELECT column_name::varchar |
94 | FROM information_schema.columns |
95 | WHERE ordinal_position = ANY (r.confkey) |
96 | AND table_schema = n.nspname |
97 | AND table_name = d.relname ) AS reference_fields, |
98 | r.confupdtype, |
99 | r.confdeltype, |
100 | r.confmatchtype |
101 | |
102 | FROM pg_catalog.pg_constraint r |
103 | |
104 | JOIN pg_catalog.pg_class c |
105 | ON c.oid = r.conrelid |
106 | AND r.contype = 'f' |
107 | |
108 | JOIN pg_catalog.pg_class d |
109 | ON d.oid = r.confrelid |
110 | |
111 | JOIN pg_catalog.pg_namespace n |
112 | ON n.oid = c.relnamespace |
113 | |
114 | WHERE pg_catalog.pg_table_is_visible(c.oid) |
115 | AND n.nspname = ? |
116 | AND c.relname = ? |
117 | ORDER BY 1; |
118 | /) or die "Can't prepare: $@"; |
119 | |
d2522b19 |
120 | $table_select->execute(); |
121 | |
122 | while ( my $tablehash = $table_select->fetchrow_hashref ) { |
123 | |
124 | my $table_name = $$tablehash{'relname'}; |
125 | my $table_oid = $$tablehash{'oid'}; |
126 | |
127 | my $table = $schema->add_table( |
128 | name => $table_name, |
129 | #what is type? type => $table_info->{TABLE_TYPE}, |
638e82c2 |
130 | ) || die $schema->error; |
80ae061a |
131 | |
d2522b19 |
132 | $column_select->execute($table_oid); |
133 | |
134 | while (my $columnhash = $column_select->fetchrow_hashref ) { |
638e82c2 |
135 | |
31f10179 |
136 | #data_type seems to not be populated; perhaps there needs to |
137 | #be a mapping of query output to reserved constants in sqlt? |
138 | |
139 | my $col = $table->add_field( |
d2522b19 |
140 | name => $$columnhash{'attname'}, |
141 | default_value => $$columnhash{'adsrc'}, |
7ed7402c |
142 | data_type => $$columnhash{'typname'}, |
d2522b19 |
143 | order => $$columnhash{'attnum'}, |
d2522b19 |
144 | ) || die $table->error; |
31f10179 |
145 | |
146 | $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0; |
7ed7402c |
147 | $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1; |
d2522b19 |
148 | } |
638e82c2 |
149 | |
d2522b19 |
150 | $index_select->execute($table_oid); |
151 | |
152 | my @column_names = $table->field_names(); |
153 | while (my $indexhash = $index_select->fetchrow_hashref ) { |
154 | #don't deal with function indexes at the moment |
155 | next if ($$indexhash{'indkey'} eq '' |
156 | or !defined($$indexhash{'indkey'}) ); |
157 | |
158 | my $type; |
31f10179 |
159 | if ($$indexhash{'indisprimary'}) { |
160 | $type = UNIQUE; #PRIMARY_KEY; |
161 | |
162 | #tell sqlt that this is the primary key: |
163 | my $col_name=$column_names[($$indexhash{'indkey'} - 1)]; |
164 | $table->get_field($col_name)->{is_primary_key}=1; |
165 | |
166 | } elsif ($$indexhash{'indisunique'}) { |
cfeaa28f |
167 | $type = UNIQUE; |
d2522b19 |
168 | } else { |
169 | $type = NORMAL; |
170 | } |
638e82c2 |
171 | |
d2522b19 |
172 | my @column_ids = split /\s+/, $$indexhash{'indkey'}; |
173 | my @columns; |
174 | foreach my $col (@column_ids) { |
175 | push @columns, $column_names[($col - 1)]; |
638e82c2 |
176 | } |
d2522b19 |
177 | |
178 | $table->add_index( |
179 | name => $$indexhash{'relname'}, |
180 | type => $type, |
31f10179 |
181 | fields => \@columns, |
d2522b19 |
182 | ) || die $table->error; |
638e82c2 |
183 | } |
cfeaa28f |
184 | |
185 | $fk_select->execute('public',$table_name) or die "Can't execute: $@"; |
186 | my $fkeys = $fk_select->fetchall_arrayref({}); |
187 | print Dumper $fkeys; |
188 | for my $con (@$fkeys){ |
189 | my $con_name = $con->{conname}; |
190 | my $fields = $con->{fields}; |
191 | my $reference_fields = $con->{reference_fields}; |
192 | my $reference_table = $con->{frelname}; |
193 | my $on_upd = $con->{confupdtype}; |
194 | my $on_del = $con->{confdeltype}; |
195 | $table->add_constraint( |
196 | name => $con_name, |
197 | type => 'foreign_key', |
198 | fields => $fields, |
199 | reference_fields => $reference_fields, |
200 | reference_table => $reference_table, |
201 | on_delete => $actions->{$on_upd}, |
202 | on_update => $actions->{$on_del}, |
203 | ); |
204 | } |
80ae061a |
205 | } |
cfeaa28f |
206 | |
80ae061a |
207 | |
208 | return 1; |
209 | } |
210 | |
211 | 1; |
212 | |
213 | # ------------------------------------------------------------------- |
13b0b70f |
214 | # Time is a waste of money. |
215 | # Oscar Wilde |
80ae061a |
216 | # ------------------------------------------------------------------- |
217 | |
218 | =pod |
219 | |
220 | =head1 AUTHOR |
221 | |
d2522b19 |
222 | Scott Cain E<lt>cain@cshl.eduE<gt>, previous author: |
13b0b70f |
223 | Paul Harrington E<lt>harringp@deshaw.comE<gt>. |
80ae061a |
224 | |
225 | =head1 SEE ALSO |
226 | |
13b0b70f |
227 | SQL::Translator, DBD::Pg. |
80ae061a |
228 | |
229 | =cut |