X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FParser%2FDBI%2FPostgreSQL.pm;h=1cff52913b760b24bc7391cf85d467a2462f54d6;hb=7d8f0a61b9fbd54e85625c0f9857fc000ae11bfd;hp=9fc3359c401f2d43188552fa6c413147c8927843;hpb=80ae061a391be4969df63d3affae16a629276f3b;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm index 9fc3359..1cff529 100644 --- a/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm +++ b/lib/SQL/Translator/Parser/DBI/PostgreSQL.pm @@ -1,9 +1,7 @@ package SQL::Translator::Parser::DBI::PostgreSQL; # ------------------------------------------------------------------- -# $Id: PostgreSQL.pm,v 1.1 2003-10-10 15:24:04 kycl4rk Exp $ -# ------------------------------------------------------------------- -# Copyright (C) 2003 Ken Y. Clark . +# 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 @@ -30,123 +28,112 @@ See SQL::Translator::Parser::DBI. =head1 DESCRIPTION -Uses DBIx::DBSchema. +Uses DBI to query PostgreSQL system tables to determine schema structure. =cut use strict; use DBI; -use DBIx::DBSchema; use Data::Dumper; use SQL::Translator::Schema::Constants; use vars qw[ $DEBUG $VERSION @EXPORT_OK ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/; +$VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; # ------------------------------------------------------------------- sub parse { my ( $tr, $dbh ) = @_; - my $db_schema = DBIx::DBSchema->new_native( $dbh ); - -# warn "DBIx::DBSchema =\n", Dumper( $,b_schema ), "\n"; - my $schema = $tr->schema; - my @table_names = $db_schema->tables; - - for my $table_name ( @table_names ) { - my $db_table = $db_schema->table( $table_name ); - - my $table = $schema->add_table( - name => $table_name, - ) or die $schema->error; - - my @col_names = $db_table->columns; - - for my $col_name ( @col_names ) { - my $db_col = $db_table->column( $col_name ); - my $fname = $db_col->name or next; - my $data_type = $db_col->type or next; - my $size = $db_col->length; - my $is_nullable = $db_col->null eq 'NULL' ? 1 : 0; - my $default = $db_col->default; - - my $field = $table->add_field( - name => $fname, - data_type => $data_type, - size => $size, - default_value => $default, - is_nullable => $is_nullable, - ) or die $table->error; - - my $pk = $db_table->primary_key; - $table->primary_key( $pk ) if $pk; + my $column_select = $dbh->prepare( + "SELECT a.attname, t.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) + 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, + i.indisprimary, pg_get_indexdef(oid) AS create_string + FROM pg_class c,pg_index i + WHERE c.relnamespace=2200 AND c.relkind='i' + AND c.oid=i.indexrelid AND i.indrelid=?" + ); + + my $table_select = $dbh->prepare( + "SELECT oid,relname FROM pg_class WHERE relnamespace IN + (SELECT oid FROM pg_namespace WHERE nspname='public') + AND relkind='r';" + ); + $table_select->execute(); + + while ( my $tablehash = $table_select->fetchrow_hashref ) { + + my $table_name = $$tablehash{'relname'}; + my $table_oid = $$tablehash{'oid'}; + + my $table = $schema->add_table( + name => $table_name, + #what is type? type => $table_info->{TABLE_TYPE}, + ) || die $schema->error; + + $column_select->execute($table_oid); + + while (my $columnhash = $column_select->fetchrow_hashref ) { + + #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( + name => $$columnhash{'attname'}, + default_value => $$columnhash{'adsrc'}, + data_type => $$columnhash{'typname'}, + order => $$columnhash{'attnum'}, + ) || die $table->error; + + $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0; + $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1; } -# my $indices = $dbh->selectall_arrayref( -# "show index from $table_name", -# { Columns => {} }, -# ); -# -# my ( %keys, %constraints, $order ); -# for my $index ( @$indices ) { -# my $table = $index->{'table'}; -# my $non_unique = $index->{'non_unique'}; -# my $key_name = $index->{'key_name'} || ''; -# my $seq_in_index = $index->{'seq_in_index'}; -# my $column_name = $index->{'column_name'}; -# my $collation = $index->{'collation'}; -# my $cardinality = $index->{'cardinality'}; -# my $sub_part = $index->{'sub_part'}; -# my $packed = $index->{'packed'}; -# my $null = $index->{'null'}; -# my $index_type = $index->{'index_type'}; -# my $comment = $index->{'comment'}; -# -# my $is_constraint = $key_name eq 'PRIMARY' || $non_unique == 0; -# -# if ( $is_constraint ) { -# $constraints{ $key_name }{'order'} = ++$order; -# push @{ $constraints{ $key_name }{'fields'} }, $column_name; -# -# if ( $key_name eq 'PRIMARY' ) { -# $constraints{ $key_name }{'type'} = PRIMARY_KEY; -# } -# elsif ( $non_unique == 0 ) { -# $constraints{ $key_name }{'type'} = UNIQUE; -# } -# } -# else { -# $keys{ $key_name }{'order'} = ++$order; -# push @{ $keys{ $key_name }{'fields'} }, $column_name; -# } -# } -# -# for my $key_name ( -# sort { $keys{ $a }{'order'} <=> $keys{ $b }{'order'} } -# keys %keys -# ) { -# my $key = $keys{ $key_name }; -# my $index = $table->add_index( -# name => $key_name, -# type => NORMAL, -# fields => $key->{'fields'}, -# ) or die $table->error; -# } -# -# for my $constraint_name ( -# sort { $constraints{ $a }{'order'} <=> $constraints{ $b }{'order'} } -# keys %constraints -# ) { -# my $def = $constraints{ $constraint_name }; -# my $constraint = $table->add_constraint( -# name => $constraint_name, -# type => $def->{'type'}, -# fields => $def->{'fields'}, -# ) or die $table->error; -# } + $index_select->execute($table_oid); + + 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 '' + or !defined($$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; + + } elsif ($$indexhash{'indisunique'}) { + $type = UNIQUE; + } else { + $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'}, + type => $type, + fields => \@columns, + ) || die $table->error; + } } return 1; @@ -155,18 +142,19 @@ sub parse { 1; # ------------------------------------------------------------------- -# Where man is not nature is barren. -# William Blake +# Time is a waste of money. +# Oscar Wilde # ------------------------------------------------------------------- =pod =head1 AUTHOR -Ken Y. Clark Ekclark@cpan.orgE. +Scott Cain Ecain@cshl.eduE, previous author: +Paul Harrington Eharringp@deshaw.comE. =head1 SEE ALSO -perl(1), Parse::RecDescent, SQL::Translator::Schema. +SQL::Translator, DBD::Pg. =cut