package SQL::Translator::Parser::DBI::PostgreSQL;
# -------------------------------------------------------------------
-# $Id: PostgreSQL.pm,v 1.1 2003-10-10 15:24:04 kycl4rk Exp $
+# $Id: PostgreSQL.pm,v 1.9 2005-10-07 16:26:41 scottcain Exp $
# -------------------------------------------------------------------
-# Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>.
+# Copyright (C) 2002-4 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
=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 = sprintf "%d.%02d", q$Revision: 1.9 $ =~ /(\d+)\.(\d+)/;
$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{'typename'},
+ order => $$columnhash{'attnum'},
+ ) || die $table->error;
+
+ $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0;
+ $col->{is_nullable} = 1 unless $$columnhash{'attnotnull'};
}
-# 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;
1;
# -------------------------------------------------------------------
-# Where man is not nature is barren.
-# William Blake
+# Time is a waste of money.
+# Oscar Wilde
# -------------------------------------------------------------------
=pod
=head1 AUTHOR
-Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
+Scott Cain E<lt>cain@cshl.eduE<gt>, previous author:
+Paul Harrington E<lt>harringp@deshaw.comE<gt>.
=head1 SEE ALSO
-perl(1), Parse::RecDescent, SQL::Translator::Schema.
+SQL::Translator, DBD::Pg.
=cut
projects / dbsrgits/SQL-Translator.git / blobdiff