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 # -------------------------------------------------------------------
46 my ( $tr, $dbh ) = @_;
48 my $schema = $tr->schema;
50 my $column_select = $dbh->prepare(
51 "SELECT a.attname, t.typname, a.attnum,a.atttypmod as length,
52 a.attnotnull, a.atthasdef, d.adsrc
53 FROM pg_type t,pg_attribute a
54 LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum)
55 WHERE a.attrelid=? AND attnum>0
60 my $index_select = $dbh->prepare(
61 "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique,
62 i.indisprimary, pg_get_indexdef(oid) AS create_string
63 FROM pg_class c,pg_index i
64 WHERE c.relnamespace=2200 AND c.relkind='i'
65 AND c.oid=i.indexrelid AND i.indrelid=?"
68 my $table_select = $dbh->prepare(
69 "SELECT oid,relname FROM pg_class WHERE relnamespace IN
70 (SELECT oid FROM pg_namespace WHERE nspname='public')
73 $table_select->execute();
75 while ( my $tablehash = $table_select->fetchrow_hashref ) {
77 my $table_name = $$tablehash{'relname'};
78 my $table_oid = $$tablehash{'oid'};
80 my $table = $schema->add_table(
82 #what is type? type => $table_info->{TABLE_TYPE},
83 ) || die $schema->error;
85 $column_select->execute($table_oid);
87 while (my $columnhash = $column_select->fetchrow_hashref ) {
89 #data_type seems to not be populated; perhaps there needs to
90 #be a mapping of query output to reserved constants in sqlt?
92 my $col = $table->add_field(
93 name => $$columnhash{'attname'},
94 default_value => $$columnhash{'adsrc'},
95 data_type => $$columnhash{'typname'},
96 order => $$columnhash{'attnum'},
97 ) || die $table->error;
99 $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0;
100 $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1;
103 $index_select->execute($table_oid);
105 my @column_names = $table->field_names();
106 while (my $indexhash = $index_select->fetchrow_hashref ) {
107 #don't deal with function indexes at the moment
108 next if ($$indexhash{'indkey'} eq ''
109 or !defined($$indexhash{'indkey'}) );
112 if ($$indexhash{'indisprimary'}) {
113 $type = UNIQUE; #PRIMARY_KEY;
115 #tell sqlt that this is the primary key:
116 my $col_name=$column_names[($$indexhash{'indkey'} - 1)];
117 $table->get_field($col_name)->{is_primary_key}=1;
119 } elsif ($$indexhash{'indisunique'}) {
125 my @column_ids = split /\s+/, $$indexhash{'indkey'};
127 foreach my $col (@column_ids) {
128 push @columns, $column_names[($col - 1)];
132 name => $$indexhash{'relname'},
135 ) || die $table->error;
144 # -------------------------------------------------------------------
145 # Time is a waste of money.
147 # -------------------------------------------------------------------
153 Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
154 Paul Harrington E<lt>harringp@deshaw.comE<gt>.
158 SQL::Translator, DBD::Pg.