X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FParser%2FDBI%2FPostgreSQL.pm;h=28017a6b958308493d6efacc3f3bc8a45570df72;hb=db195df8af1efc7fb983ec6041fcb064dada7124;hp=a6dc3e8f2e00848e774612bbbfbe11ee25f92d09;hpb=a23f9a9db103d35a5f953033e5ab18ad65aa1fb2;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm index a6dc3e8..28017a6 100644 --- a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm +++ b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm @@ -1,23 +1,5 @@ package SQL::Translator::Parser::DBI::PostgreSQL; -# ------------------------------------------------------------------- -# Copyright (C) 2002-2009 SQLFairy Authors -# -# This program is free software; you can redistribute it and/or -# modify it under the terms of the GNU General Public License as -# published by the Free Software Foundation; version 2. -# -# This program is distributed in the hope that it will be useful, but -# WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU -# General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program; if not, write to the Free Software -# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA -# 02111-1307 USA -# ------------------------------------------------------------------- - =head1 NAME SQL::Translator::Parser::DBI::PostgreSQL - parser for DBD::Pg @@ -28,17 +10,18 @@ See SQL::Translator::Parser::DBI. =head1 DESCRIPTION -Uses DBI to query PostgreSQL system tables to determine schema structure. +Uses DBI to query PostgreSQL system tables to determine schema structure. =cut use strict; +use warnings; use DBI; use Data::Dumper; use SQL::Translator::Schema::Constants; -use vars qw[ $DEBUG $VERSION @EXPORT_OK ]; -$VERSION = '1.59'; +our ( $DEBUG, @EXPORT_OK ); +our $VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; my $actions = {c => 'cascade', @@ -48,7 +31,6 @@ my $actions = {c => 'cascade', d => 'set default', }; -# ------------------------------------------------------------------- sub parse { my ( $tr, $dbh ) = @_; @@ -56,13 +38,15 @@ sub parse { my $column_select = $dbh->prepare( "SELECT a.attname, format_type(t.oid, a.atttypmod) as typname, a.attnum, - a.atttypmod as length, a.attnotnull, a.atthasdef, d.adsrc - FROM pg_type t,pg_attribute a - LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum) + a.atttypmod as length, a.attnotnull, a.atthasdef, ad.adsrc, + d.description + FROM pg_type t, pg_attribute a + LEFT JOIN pg_attrdef ad ON (ad.adrelid = a.attrelid AND a.attnum = ad.adnum) + LEFT JOIN pg_description d ON (a.attrelid=d.objoid AND a.attnum=d.objsubid) WHERE a.attrelid=? AND attnum>0 AND a.atttypid=t.oid ORDER BY a.attnum" - ); + ); my $index_select = $dbh->prepare( "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique, @@ -73,7 +57,10 @@ sub parse { ); my $table_select = $dbh->prepare( - "SELECT oid,relname FROM pg_class WHERE relnamespace IN + "SELECT c.oid, c.relname, d.description + FROM pg_class c + LEFT JOIN pg_description d ON c.oid=d.objoid AND d.objsubid=0 + WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname='public') AND relkind='r';" ); @@ -116,24 +103,25 @@ WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relname = ? ORDER BY 1; /) or die "Can't prepare: $@"; - + $table_select->execute(); while ( my $tablehash = $table_select->fetchrow_hashref ) { my $table_name = $$tablehash{'relname'}; - my $table_oid = $$tablehash{'oid'}; - + my $table_oid = $$tablehash{'oid'}; my $table = $schema->add_table( name => $table_name, #what is type? type => $table_info->{TABLE_TYPE}, ) || die $schema->error; + $table->comments($$tablehash{'description'}) if $$tablehash{'description'}; + $column_select->execute($table_oid); while (my $columnhash = $column_select->fetchrow_hashref ) { - #data_type seems to not be populated; perhaps there needs to + #data_type seems to not be populated; perhaps there needs to #be a mapping of query output to reserved constants in sqlt? my $col = $table->add_field( @@ -146,6 +134,7 @@ ORDER BY 1; $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0 && $$columnhash{'length'}<=0xFFFF; $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1; + $col->comments($$columnhash{'description'}) if $$columnhash{'description'}; } $index_select->execute($table_oid); @@ -153,16 +142,19 @@ ORDER BY 1; my @column_names = $table->field_names(); while (my $indexhash = $index_select->fetchrow_hashref ) { #don't deal with function indexes at the moment - next if ($$indexhash{'indkey'} eq '' + next if ($$indexhash{'indkey'} eq '' or !defined($$indexhash{'indkey'}) ); + my @columns = map $column_names[$_ - 1], split /\s+/, $$indexhash{'indkey'}; + my $type; if ($$indexhash{'indisprimary'}) { $type = UNIQUE; #PRIMARY_KEY; #tell sqlt that this is the primary key: - my $col_name=$column_names[($$indexhash{'indkey'} - 1)]; - $table->get_field($col_name)->{is_primary_key}=1; + for my $column (@columns) { + $table->get_field($column)->{is_primary_key}=1; + } } elsif ($$indexhash{'indisunique'}) { $type = UNIQUE; @@ -170,11 +162,6 @@ ORDER BY 1; $type = NORMAL; } - my @column_ids = split /\s+/, $$indexhash{'indkey'}; - my @columns; - foreach my $col (@column_ids) { - push @columns, $column_names[($col - 1)]; - } $table->add_index( name => $$indexhash{'relname'}, @@ -182,7 +169,7 @@ ORDER BY 1; fields => \@columns, ) || die $table->error; } - + $fk_select->execute('public',$table_name) or die "Can't execute: $@"; my $fkeys = $fk_select->fetchall_arrayref({}); $DEBUG and print Dumper $fkeys; @@ -204,7 +191,7 @@ ORDER BY 1; ); } } - + return 1; } @@ -220,7 +207,7 @@ ORDER BY 1; =head1 AUTHOR -Scott Cain Ecain@cshl.eduE, previous author: +Scott Cain Ecain@cshl.eduE, previous author: Paul Harrington Eharringp@deshaw.comE. =head1 SEE ALSO