1 package SQL::Translator::Parser::DBI::PostgreSQL;
3 # -------------------------------------------------------------------
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-4 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
25 SQL::Translator::Parser::DBI::PostgreSQL - parser for DBD::Pg
29 See SQL::Translator::Parser::DBI.
33 Uses DBI to query PostgreSQL system tables to determine schema structure.
40 use SQL::Translator::Schema::Constants;
42 use vars qw[ $DEBUG $VERSION @EXPORT_OK ];
43 $VERSION = sprintf "%d.%02d", q$Revision$ =~ /(\d+)\.(\d+)/;
44 $DEBUG = 0 unless defined $DEBUG;
46 # -------------------------------------------------------------------
48 my ( $tr, $dbh ) = @_;
50 my $schema = $tr->schema;
52 my $column_select = $dbh->prepare(
53 "SELECT a.attname, t.typname, a.attnum,a.atttypmod as length,
54 a.attnotnull, a.atthasdef, d.adsrc
55 FROM pg_type t,pg_attribute a
56 LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum)
57 WHERE a.attrelid=? AND attnum>0
62 my $index_select = $dbh->prepare(
63 "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
64 i.indisprimary, pg_get_indexdef(oid) AS create_string
65 FROM pg_class c,pg_index i
66 WHERE c.relnamespace=2200 AND c.relkind='i'
67 AND c.oid=i.indexrelid AND i.indrelid=?"
70 my $table_select = $dbh->prepare(
71 "SELECT oid,relname FROM pg_class WHERE relnamespace IN
72 (SELECT oid FROM pg_namespace WHERE nspname='public')
75 $table_select->execute();
77 while ( my $tablehash = $table_select->fetchrow_hashref ) {
79 my $table_name = $$tablehash{'relname'};
80 my $table_oid = $$tablehash{'oid'};
82 my $table = $schema->add_table(
84 #what is type? type => $table_info->{TABLE_TYPE},
85 ) || die $schema->error;
87 $column_select->execute($table_oid);
89 while (my $columnhash = $column_select->fetchrow_hashref ) {
91 #data_type seems to not be populated; perhaps there needs to
92 #be a mapping of query output to reserved constants in sqlt?
94 my $col = $table->add_field(
95 name => $$columnhash{'attname'},
96 default_value => $$columnhash{'adsrc'},
97 data_type => $$columnhash{'typname'},
98 order => $$columnhash{'attnum'},
99 ) || die $table->error;
101 $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0;
102 $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1;
105 $index_select->execute($table_oid);
107 my @column_names = $table->field_names();
108 while (my $indexhash = $index_select->fetchrow_hashref ) {
109 #don't deal with function indexes at the moment
110 next if ($$indexhash{'indkey'} eq ''
111 or !defined($$indexhash{'indkey'}) );
114 if ($$indexhash{'indisprimary'}) {
115 $type = UNIQUE; #PRIMARY_KEY;
117 #tell sqlt that this is the primary key:
118 my $col_name=$column_names[($$indexhash{'indkey'} - 1)];
119 $table->get_field($col_name)->{is_primary_key}=1;
121 } elsif ($$indexhash{'indisunique'}) {
127 my @column_ids = split /\s+/, $$indexhash{'indkey'};
129 foreach my $col (@column_ids) {
130 push @columns, $column_names[($col - 1)];
134 name => $$indexhash{'relname'},
137 ) || die $table->error;
146 # -------------------------------------------------------------------
147 # Time is a waste of money.
149 # -------------------------------------------------------------------
155 Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
156 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
160 SQL::Translator, DBD::Pg.