1 package SQL::Translator::Parser::DBI::PostgreSQL;
3 # -------------------------------------------------------------------
4 # $Id: PostgreSQL.pm 1440 2009-01-17 16:31:57Z jawnsy $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-2009 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 @EXPORT_OK ];
43 $DEBUG = 0 unless defined $DEBUG;
45 # -------------------------------------------------------------------
47 my ( $tr, $dbh ) = @_;
49 my $schema = $tr->schema;
51 my $column_select = $dbh->prepare(
52 "SELECT a.attname, t.typname, a.attnum,a.atttypmod as length,
53 a.attnotnull, a.atthasdef, d.adsrc
54 FROM pg_type t,pg_attribute a
55 LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum)
56 WHERE a.attrelid=? AND attnum>0
61 my $index_select = $dbh->prepare(
62 "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
63 i.indisprimary, pg_get_indexdef(oid) AS create_string
64 FROM pg_class c,pg_index i
65 WHERE c.relnamespace=2200 AND c.relkind='i'
66 AND c.oid=i.indexrelid AND i.indrelid=?"
69 my $table_select = $dbh->prepare(
70 "SELECT oid,relname FROM pg_class WHERE relnamespace IN
71 (SELECT oid FROM pg_namespace WHERE nspname='public')
74 $table_select->execute();
76 while ( my $tablehash = $table_select->fetchrow_hashref ) {
78 my $table_name = $$tablehash{'relname'};
79 my $table_oid = $$tablehash{'oid'};
81 my $table = $schema->add_table(
83 #what is type? type => $table_info->{TABLE_TYPE},
84 ) || die $schema->error;
86 $column_select->execute($table_oid);
88 while (my $columnhash = $column_select->fetchrow_hashref ) {
90 #data_type seems to not be populated; perhaps there needs to
91 #be a mapping of query output to reserved constants in sqlt?
93 my $col = $table->add_field(
94 name => $$columnhash{'attname'},
95 default_value => $$columnhash{'adsrc'},
96 data_type => $$columnhash{'typname'},
97 order => $$columnhash{'attnum'},
98 ) || die $table->error;
100 $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0;
101 $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1;
104 $index_select->execute($table_oid);
106 my @column_names = $table->field_names();
107 while (my $indexhash = $index_select->fetchrow_hashref ) {
108 #don't deal with function indexes at the moment
109 next if ($$indexhash{'indkey'} eq ''
110 or !defined($$indexhash{'indkey'}) );
113 if ($$indexhash{'indisprimary'}) {
114 $type = UNIQUE; #PRIMARY_KEY;
116 #tell sqlt that this is the primary key:
117 my $col_name=$column_names[($$indexhash{'indkey'} - 1)];
118 $table->get_field($col_name)->{is_primary_key}=1;
120 } elsif ($$indexhash{'indisunique'}) {
126 my @column_ids = split /\s+/, $$indexhash{'indkey'};
128 foreach my $col (@column_ids) {
129 push @columns, $column_names[($col - 1)];
133 name => $$indexhash{'relname'},
136 ) || die $table->error;
145 # -------------------------------------------------------------------
146 # Time is a waste of money.
148 # -------------------------------------------------------------------
154 Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
155 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
159 SQL::Translator, DBD::Pg.