1 package SQL::Translator::Parser::DBI::PostgreSQL;
3 # -------------------------------------------------------------------
4 # Copyright (C) 2002-2009 SQLFairy Authors
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.
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.
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
19 # -------------------------------------------------------------------
23 SQL::Translator::Parser::DBI::PostgreSQL - parser for DBD::Pg
27 See SQL::Translator::Parser::DBI.
31 Uses DBI to query PostgreSQL system tables to determine schema structure.
38 use SQL::Translator::Schema::Constants;
40 use vars qw[ $DEBUG $VERSION @EXPORT_OK ];
42 $DEBUG = 0 unless defined $DEBUG;
44 my $actions = {c => 'cascade',
51 # -------------------------------------------------------------------
53 my ( $tr, $dbh ) = @_;
55 my $schema = $tr->schema;
57 my $column_select = $dbh->prepare(
58 "SELECT a.attname, format_type(t.oid, a.atttypmod) as typname, a.attnum,
59 a.atttypmod as length, 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
67 my $index_select = $dbh->prepare(
68 "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
69 ARRAY(SELECT a.attname
71 WHERE a.attrelid=i.indrelid AND a.attnum = ANY(i.indkey)
73 i.indisprimary, pg_get_indexdef(oid) AS create_string
74 FROM pg_class c,pg_index i
75 WHERE c.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname='public') AND c.relkind='i'
76 AND c.oid=i.indexrelid AND i.indrelid=?"
79 my $table_select = $dbh->prepare(
80 "SELECT oid,relname FROM pg_class WHERE relnamespace IN
81 (SELECT oid FROM pg_namespace WHERE nspname='public')
85 my $fk_select = $dbh->prepare(
89 d.relname AS frelname,
91 ARRAY(SELECT column_name::varchar
92 FROM information_schema.columns
93 WHERE ordinal_position = ANY (r.conkey)
94 AND table_schema = n.nspname
95 AND table_name = c.relname ) AS fields,
97 ARRAY(SELECT column_name::varchar
98 FROM information_schema.columns
99 WHERE ordinal_position = ANY (r.confkey)
100 AND table_schema = n.nspname
101 AND table_name = d.relname ) AS reference_fields,
106 FROM pg_catalog.pg_constraint r
108 JOIN pg_catalog.pg_class c
109 ON c.oid = r.conrelid
112 JOIN pg_catalog.pg_class d
113 ON d.oid = r.confrelid
115 JOIN pg_catalog.pg_namespace n
116 ON n.oid = c.relnamespace
118 WHERE pg_catalog.pg_table_is_visible(c.oid)
122 /) or die "Can't prepare: $@";
124 $table_select->execute();
126 while ( my $tablehash = $table_select->fetchrow_hashref ) {
128 my $table_name = $$tablehash{'relname'};
129 my $table_oid = $$tablehash{'oid'};
131 my $table = $schema->add_table(
133 #what is type? type => $table_info->{TABLE_TYPE},
134 ) || die $schema->error;
136 $column_select->execute($table_oid);
138 while (my $columnhash = $column_select->fetchrow_hashref ) {
140 #data_type seems to not be populated; perhaps there needs to
141 #be a mapping of query output to reserved constants in sqlt?
143 my $col = $table->add_field(
144 name => $$columnhash{'attname'},
145 default_value => $$columnhash{'adsrc'},
146 data_type => $$columnhash{'typname'},
147 order => $$columnhash{'attnum'},
148 ) || die $table->error;
150 $col->{size} = [$$columnhash{'length'}]
151 if $$columnhash{'length'}>0 && $$columnhash{'length'}<=0xFFFF;
152 $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1;
155 $index_select->execute($table_oid);
157 my @column_names = $table->field_names();
158 while (my $indexhash = $index_select->fetchrow_hashref ) {
159 #don't deal with function indexes at the moment
160 next if ($$indexhash{'indkey'} eq ''
161 or !defined($$indexhash{'indkey'}) );
164 if ($$indexhash{'indisprimary'}) {
165 $type = UNIQUE; #PRIMARY_KEY;
167 #tell sqlt that this is the primary key:
168 my $col_name=$column_names[($$indexhash{'indkey'} - 1)];
169 $table->get_field($col_name)->{is_primary_key}=1;
171 } elsif ($$indexhash{'indisunique'}) {
178 my @column_ids = split /\s+/, $$indexhash{'indkey'};
179 my @columns = split /\s+/, $$indexhash{'attname'};
182 name => $$indexhash{'relname'},
185 ) || die $table->error;
188 $fk_select->execute('public',$table_name) or die "Can't execute: $@";
189 my $fkeys = $fk_select->fetchall_arrayref({});
190 $DEBUG and print Dumper $fkeys;
191 for my $con (@$fkeys){
192 my $con_name = $con->{conname};
193 my $fields = $con->{fields};
194 my $reference_fields = $con->{reference_fields};
195 my $reference_table = $con->{frelname};
196 my $on_upd = $con->{confupdtype};
197 my $on_del = $con->{confdeltype};
198 $table->add_constraint(
200 type => 'foreign_key',
202 reference_fields => $reference_fields,
203 reference_table => $reference_table,
204 on_delete => $actions->{$on_upd},
205 on_update => $actions->{$on_del},
216 # -------------------------------------------------------------------
217 # Time is a waste of money.
219 # -------------------------------------------------------------------
225 Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
226 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
230 SQL::Translator, DBD::Pg.