/lib/CXGN/Tools/PgCatalog.pm
Perl | 160 lines | 139 code | 15 blank | 6 comment | 4 complexity | 14238c52e672df19cb68439888bdbc77 MD5 | raw file
- package CXGN::Tools::PgCatalog;
- use strict;
- use warnings;
- use Carp;
- BEGIN {
- our @EXPORT_OK = qw/ table_info /;
- }
- our @EXPORT_OK;
- use base qw/Exporter/;
- =head1 NAME
- CXGN::Tools::PgCatalog - tools for getting information out of the
- Postgres pg_catalog schema.
- =head1 DESCRIPTION
- Tools for getting information from the Postgres pg_catalog schema,
- which holds everything you ever wanted to know about the structure
- of the database itself.
- =head1 FUNCTIONS
- All functions listed below are EXPORT_OK.
- =head2 table_info
- Usage: my %info = table_info($dbc,'genomic','blast_hit');
- Desc :
- Ret : hash-style list as:
- ( primary => ['primary key col','primary key col',...],
- columns => ['column name', 'column name', ...],
- sequence => 'genomic.my_crazy_seq',
- )
- Args : L<CXGN::DB::Connection> object, schema name, table name
- Side Effects:
- Example:
- =cut
- #adapted by Rob from set_up_table in Class::DBI::Pg
- sub table_info {
- my ( $dbh, $schema, $table ) = @_;
- print "getting info for $schema.$table\n";
- #convert the schema into the base table schema name
- my $schema_bt = $dbh->qualify_schema($schema,1);
- # find primary key
- my $sth = $dbh->prepare_cached(<<SQL);
- SELECT indkey
- FROM pg_catalog.pg_index
- WHERE indisprimary=true
- AND indrelid=( SELECT c.oid
- FROM pg_catalog.pg_class as c
- JOIN pg_catalog.pg_namespace as n ON (c.relnamespace=n.oid)
- WHERE n.nspname = ?
- AND c.relname = ?
- )
- SQL
- $sth->execute($schema_bt,$table);
- my %prinum = map { $_ => 1 } split ' ', ($sth->fetchrow_array || (''));
- $sth->finish;
- # find all columns
- $sth = $dbh->prepare_cached(<<SQL);
- SELECT a.attname,
- a.attnum
- FROM pg_catalog.pg_class as c,
- pg_catalog.pg_attribute as a,
- pg_catalog.pg_namespace as n
- WHERE n.nspname = ?
- AND a.attnum > 0
- AND a.attrelid = c.oid
- AND n.oid = c.relnamespace
- AND c.relname = ?
- ORDER BY a.attnum
- SQL
- $sth->execute($schema, $table);
- my $columns = $sth->fetchall_arrayref;
- $sth->finish;
- # find SERIAL type.
- # nextval('"table_id_seq"'::text)
- $sth = $dbh->prepare_cached(<<SQL);
- SELECT adsrc
- FROM pg_catalog.pg_attrdef
- WHERE adrelid=( SELECT c.oid
- FROM pg_catalog.pg_class as c
- JOIN pg_catalog.pg_namespace as n ON (c.relnamespace=n.oid)
- WHERE n.nspname = ?
- AND c.relname = ?
- )
- SQL
- $sth->execute($schema_bt, $table);
- my ($nextval_str) = $sth->fetchrow_array;
- $sth->finish;
- my ($sequence) =
- $nextval_str ? $nextval_str =~ m/^nextval\('"?([^"']+)"?'::text\)/ : '';
- # ($sequence) = (split /\./,$sequence)[-1]; #un-qualify the sequence name
- my ( @cols, @primary );
- foreach my $col (@$columns) {
- # skip dropped column.
- next if $col->[0] =~ /^\.+pg\.dropped\.\d+\.+$/;
- push @cols, $col->[0];
- next unless $prinum{ $col->[1] };
- push @primary, $col->[0];
- }
- warn("$schema.$table has no primary key") unless @primary;
- warn("$schema.$table has a composite primary key") if @primary > 1;
- return ( primary => \@primary,
- columns => \@cols,
- sequence => $sequence,
- );
- }
- =head2 is_valid_column
- #Example
- unless(CXGN::DB::Tools::is_valid_column($dbh,$table_name,$column_name))
- {
- CXGN::Apache::Error::notify('found invalid parameter',"Someone sent in '$column_name' as a parameter. Wacky.");
- $sortby='';
- }
- =cut
- sub is_valid_column
- {
- my($dbh,$table_name,$column_name)=@_;
- my $test=$dbh->prepare
- ("
- select
- count(*)
- from
- pg_class
- inner join pg_attribute on (pg_attribute.attrelid=pg_class.oid)
- where
- relname=?
- and attname=?
- and relkind='r'
- ");
- $test->execute($table_name,$column_name);
- my($found)=$test->fetchrow_array();
- return $found;
- }
- =head1 AUTHOR
- Robert Buels and John Binns <zombieite@gmail.com>
- =cut
- ###
- 1;#do not remove
- ###