1 package SQL::Translator::Parser::DBI::PostgreSQL;
5 SQL::Translator::Parser::DBI::PostgreSQL - parser for DBD::Pg
9 See SQL::Translator::Parser::DBI.
13 Uses DBI to query PostgreSQL system tables to determine schema structure.
21 use SQL::Translator::Schema::Constants;
23 our ( $DEBUG, @EXPORT_OK );
24 our $VERSION = '1.59_01';
25 $DEBUG = 0 unless defined $DEBUG;
27 my $actions = {c => 'cascade',
35 my ( $tr, $dbh ) = @_;
37 my $schema = $tr->schema;
39 my $column_select = $dbh->prepare(
40 "SELECT a.attname, format_type(t.oid, a.atttypmod) as typname, a.attnum,
41 a.atttypmod as length, a.attnotnull, a.atthasdef, ad.adsrc,
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)
46 WHERE a.attrelid=? AND attnum>0
51 my $index_select = $dbh->prepare(
52 "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
53 i.indisprimary, pg_get_indexdef(oid) AS create_string
54 FROM pg_class c,pg_index i
55 WHERE c.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname='public') AND c.relkind='i'
56 AND c.oid=i.indexrelid AND i.indrelid=?"
59 my $table_select = $dbh->prepare(
60 "SELECT c.oid, c.relname, d.description
62 LEFT JOIN pg_description d ON c.oid=d.objoid AND d.objsubid=0
64 (SELECT oid FROM pg_namespace WHERE nspname='public')
68 my $fk_select = $dbh->prepare(
72 d.relname AS frelname,
74 ARRAY(SELECT column_name::varchar
75 FROM information_schema.columns
76 WHERE ordinal_position = ANY (r.conkey)
77 AND table_schema = n.nspname
78 AND table_name = c.relname ) AS fields,
80 ARRAY(SELECT column_name::varchar
81 FROM information_schema.columns
82 WHERE ordinal_position = ANY (r.confkey)
83 AND table_schema = n.nspname
84 AND table_name = d.relname ) AS reference_fields,
89 FROM pg_catalog.pg_constraint r
91 JOIN pg_catalog.pg_class c
95 JOIN pg_catalog.pg_class d
96 ON d.oid = r.confrelid
98 JOIN pg_catalog.pg_namespace n
99 ON n.oid = c.relnamespace
101 WHERE pg_catalog.pg_table_is_visible(c.oid)
105 /) or die "Can't prepare: $@";
107 $table_select->execute();
109 while ( my $tablehash = $table_select->fetchrow_hashref ) {
111 my $table_name = $$tablehash{'relname'};
112 my $table_oid = $$tablehash{'oid'};
113 my $table = $schema->add_table(
115 #what is type? type => $table_info->{TABLE_TYPE},
116 ) || die $schema->error;
118 $table->comments($$tablehash{'description'}) if $$tablehash{'description'};
120 $column_select->execute($table_oid);
122 while (my $columnhash = $column_select->fetchrow_hashref ) {
124 #data_type seems to not be populated; perhaps there needs to
125 #be a mapping of query output to reserved constants in sqlt?
127 my $col = $table->add_field(
128 name => $$columnhash{'attname'},
129 default_value => $$columnhash{'adsrc'},
130 data_type => $$columnhash{'typname'},
131 order => $$columnhash{'attnum'},
132 ) || die $table->error;
134 $col->{size} = [$$columnhash{'length'}]
135 if $$columnhash{'length'}>0 && $$columnhash{'length'}<=0xFFFF;
136 $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1;
137 $col->comments($$columnhash{'description'}) if $$columnhash{'description'};
140 $index_select->execute($table_oid);
142 my @column_names = $table->field_names();
143 while (my $indexhash = $index_select->fetchrow_hashref ) {
144 #don't deal with function indexes at the moment
145 next if ($$indexhash{'indkey'} eq ''
146 or !defined($$indexhash{'indkey'}) );
148 my @columns = map $column_names[$_ - 1], split /\s+/, $$indexhash{'indkey'};
151 if ($$indexhash{'indisprimary'}) {
152 $type = UNIQUE; #PRIMARY_KEY;
154 #tell sqlt that this is the primary key:
155 for my $column (@columns) {
156 $table->get_field($column)->{is_primary_key}=1;
159 } elsif ($$indexhash{'indisunique'}) {
167 name => $$indexhash{'relname'},
170 ) || die $table->error;
173 $fk_select->execute('public',$table_name) or die "Can't execute: $@";
174 my $fkeys = $fk_select->fetchall_arrayref({});
175 $DEBUG and print Dumper $fkeys;
176 for my $con (@$fkeys){
177 my $con_name = $con->{conname};
178 my $fields = $con->{fields};
179 my $reference_fields = $con->{reference_fields};
180 my $reference_table = $con->{frelname};
181 my $on_upd = $con->{confupdtype};
182 my $on_del = $con->{confdeltype};
183 $table->add_constraint(
185 type => 'foreign_key',
187 reference_fields => $reference_fields,
188 reference_table => $reference_table,
189 on_delete => $actions->{$on_upd},
190 on_update => $actions->{$on_del},
201 # -------------------------------------------------------------------
202 # Time is a waste of money.
204 # -------------------------------------------------------------------
210 Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
211 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
215 SQL::Translator, DBD::Pg.