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