sub to automatically set up relations for postgres

[prev] [thread] [next] [Date index for 2004/06/29]

Hi,

since in Postgres you can define references in the tables like

CREATE TABLE my_table
  ( id serial PRIMARY KEY,
    ref integer REFERENCES other_table
  );

I wrote a littel sub that looks up those references in the databse and
set the has_a / has_many references. Maybe it would be worth including
in CDBI::Pg?

here it is:

# Create has_a realtion defined for $table in $namespace as
# $namespace::$table->has_a( $column => $namespace::$foreign );
#
# and has_many relation as
# $namespace::$foreign->has_many( "$table_referencing_$column" =>
#						$namespace::$table );

sub set_up_references{
  my $self = shift;
  my $table = shift;
  my $namespace = shift;

  my $package = $namespace ."::".
	Class::DBI::AutoLoader::table2class($table);

  my $dbh = $package->db_Main;
  my $catalog = "";
  $catalog = 'pg_catalog.' if ($package->pg_version >= 7.3);

  #find oid of table
  my $sth = $dbh->
    prepare("SELECT oid FROM ${catalog}pg_class WHERE relname = ?");
  $sth->execute($table);
  my ($oid) = $sth->fetchrow_array;
  $sth->finish;

  #find all foreign_key constraints
  $sth = $dbh->prepare(<<"SQL");
SELECT conkey, confrelid
FROM ${catalog}pg_constraint
WHERE conrelid = ${oid} AND contype = 'f'
SQL
  $sth->execute();
  my $reflist = $sth->fetchall_arrayref;
  $sth->finish;

  $sth = $dbh->prepare(<<"SQL");
SELECT a.attname, c.relname
FROM ${catalog}pg_attribute a, ${catalog}pg_class c
WHERE a.attnum = ? AND a.attrelid = ${oid} AND c.oid = ?
SQL

  foreach my $ref (@$reflist) {
    $$ref[0] =~ /(\d+)/;
    $$ref[0] = $1;
    $sth->execute( @$ref );
    my ( $c, $r ) = $sth->fetchrow_array;
    my $foreign = $namespace ."::". Class::DBI::AutoLoader::table2class($r);
    my $desc = $table.'_referencing_'.$c;
    eval { $package->has_a ( $c => $foreign ); };
    eval { $foreign->has_many ( $desc => $package, $c ); };
  }
}
#--------

Grüße,

Andreas Fromm