Once upon a time a friend of mine and I wrote a document that I believe is
still on techdocs: "Compensating for Unimplemented Features in PostgreSQL
7.1".
Therein was included a perl script (that really *DID* work when we published
it) that was intended to script out the foreign key constraints of all
tables in a database, or a table you could specify.
At some point it stopped working (even for us). Over the past 1 1/2 years
we've had many emails asking about the breakage.
I finally had some time today and (I believe) have fixed it. In fact, I not
only fixed it, but I improved to (the original version was not smart enough
to script out ON DELETE and ON UPDATE behavior, nor did it care about
DEFERRABLE | NOT DEFERRABLE and INITIALLY DEFERRED | IMMEDIATE; this new
version does).
I don't have a wide variety of postgresql database to test with, so I was
wondering if a few of you would be willing to put this thing through it's
paces?
It's pretty well documented, just redirect the output to a file (it creates
SQL statements) and validate.
Here's the script:
#!/usr/bin/perl -w
################################################################
## 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; either version 2 of the License, or
## (at your option) any later version.
##
## 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 Library 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.
################################################################
## genConstraints.pl
##
## Input:
## databaseName - name of the database you wish to process
## user - valid user name in postgres
## password - user's password (or "" for nothing)
## tableName(s) - optional. One or more tables in the
## database. Omitted, it will process all
## tables.
##
## Output:
## SQL script to regenerate foreign key constraints on the
## tables in the tableName list (or, if no tableName list
## all foreign key constraints). Output goes to standard
## output - redirect to file if you want to save it.
##
## Description:
## If you drop and rebuild a table that has foreign key
## references from other tables, those references will be
## lost. This script allows you to create the SQL
## statements to add those foreign key constraints back
## once you have recreated and repopulated the parent
## table.
##
## Requirements:
## This program must be run before you drop the table.
## Once you drop the table, the constraints are lost.
##
## When you try to add the constraints back, the rules of
## referential integrity will be enforced. This means that
## there must be an entry in the parent table (the one you
## dropped and re-added) for every reference in the child
## table.
##
################################################################
############
## Modification History
############
## 2000.08.16 - Genesis
## By John Pagakis, Toolsmythe Software Services, Inc. for
## DevelopOnline, Inc.
################################################################
## 2002.01.04
## By John Pagakis, Toolsmythe Software Services, Inc.
##
## Somehow the comments got out of sync with the code (zounds!)
##
## Also implelmented a suggestion by Mark Stosberg in 01:
## $host = $ENV{PGHOST} || "localhost";
## Which somewhat addresses the problem of running this against
## a non-local database. I'll make it a commandline arg when
## I have time and resources to test.
##
## Added the caveat to the general notes above.
################################################################
################################################################
## 2003.11.06
## By John Pagakis, Toolsmythe Software Services, Inc.
##
## Resolved the problem 04.00 where the arguments were not
## null delimited and the split ... wouldn't. This eventually
## caused a use of uninitialized variable error.
##
## Added scripting out of ON DELETE and ON UPDATE behaviour.
##
## Added DEFERRABLE | NOT DEFERRABLE and
## INITIALLY DEFERRED | IMMEDIATE logic.
##
################################################################
use DBI;
################################################################
## main
##
## Input:
## See program input in general comments above
##
## Output:
## See program output in general comments above
##
## Description:
## 00) Check for valid command line args. If wrong number of
## args, show usage.
##
## 01) Attempt to connect to the database. If successful,
## select all non-postgres-internal triggers from
## pg_trigger.
##
## 02) Initialization of control variables.
##
## 03) Database Access. Connect to the database, get the triggers.
##
## 04) For each trigger ....
## 00) Parse out the trigger arguments.
## Postgres 7.x stores constraints as triggers.
## The relationship between the parent and child
## table is held by postgres in the tgargs column
## of the pg_trigger table. The targs column is
## a byte array, the arguments are separeted by
## the string "\000". For a foreign key constraint
## there are six arguments:
## 0) Trigger name (or if no trigger
## name was defined.
## 1) Referencing table name.
## 2) Referenced table name.
## 3) Constraint type (or "UNSPECIFIED" if none).
## 4) Referencing column name.
## 5) Referenced column name.
## We are going to use this information to generate
## the ALTER TABLE ADD CONSTRAINT syntax needed to
## put the constraint back after it has been dropped.
## This step parses out the arguments and holds them
## in the @splitArgs array.
##
## 01) Now that we have the arguments pasred out, we need
## to see if this is a constraint we need to generate
## SQL for. The answer is yes if no table list was
## included in the command line args, or if the
## referenced table name (@splitArgs[2]) is in the
## command line table list. If either of these
## conditions is met ....
##
## 00) Have we hit a control break?
## There will be duplicate triggers in the
## trigger table. This is because constraints
## must be checked on inserts, mods, and
## deletes. Each one of these is a different
## trigger, but the information (arguments ) is
the same,
## so we don't want to process what for our
## purposes are duplicates. On a control
## break ....
##
## 00) Save off the new control info.
##
## 01) Generate the first part of the
## SQL statement.
##
## 02) If the trigger is unnamed, generate
## a name using the following format:
##
fk__referencingTableName__referencedTableName
## If a name exists, use it.
##
## 03) Generate the next part of the SQL
## statement.
##
## 04) If a constraint type is specified, use
it,
## otherwise do nothing.
##
## 05) Script out ON DELETE behaviour.
##
## 06) Script out ON UPDATE behaviour.
##
## 07) Script out DEFERRABLE attribute.
##
## 07) Script out INITIALLY attribute.
##
## 09) Finsh off the SQL statement.
##
## 04) Finish the result set.
##
## 05) Close the database connection
##
##
## Side effects:
## Any unnamed constraint will be given a name matching
## fk__referencingTableName__referencedTableName
################################################################
######
# 00 #
if ( @ARGV < 3 )
{
print "USAGE ....\n\tgenConstraints dbName user password [table1
[table2 ...]]\n";
exit( 0 );
}
######
# 01 #
my $dbase = shift( @ARGV );
my $user = shift( @ARGV );
my $password = shift( @ARGV );
$host = $ENV{PGHOST} || "localhost";
######
# 02 #
$saveReferedTable = "x#";
$saveReferingTable = "x#";
$saveReferedKey = "x#";
$saveReferingKey = "x#";
$numberOfArgs = @ARGV;
######
# 03 #
my $driver = "dbi:Pg:dbname=" . $dbase . ";host=" . $host;
my $dbh = DBI->connect( $driver, $user, $password ) || die "\nError
($DBI::err):$DBI::errstr\n";
#my $targResultSet = $dbh->prepare( "SELECT tgname, tgnargs, tgargs,
tgdeferrable, tginitdeferred FROM pg_trigger WHERE tgisconstraint = TRUE AND
tgtype = 21;" );
my $targResultSet = $dbh->prepare( "select t.tgname, t.tgnargs, t.tgargs,
t.tgdeferrable, t.tginitdeferred, c.confupdtype, c.confdeltype from
pg_constraint AS c JOIN pg_trigger AS t ON t.tgrelid = c.conrelid AND
t.tgconstrrelid = c.confrelid AND t.tgconstrname = c.conname WHERE
c.contype = 'f'" );
$targResultSet->execute;
######
# 04 #
while ( @targs = $targResultSet->fetchrow_array() )
{
#########
# 04.00 #
my @splitArgs = split /\000/, $targs[ 2 ];
my $key_cols = $splitArgs[ 4 ];
my $ref_cols = $splitArgs[ 5 ];
if ( ( $targs[ 1 ] > 6 )
&& ( $targs[ 1 ] - 6 ) % 2 == 0 )
{
while ( $lcolumn_name = pop( @junk ) and $fcolumn_name = pop(
@junk ) )
{
$key_cols .= ", $lcolumn_name";
$ref_cols .= ", $fcolumn_name";
}
}
#########
# 04.01 #
if ( $numberOfArgs == 0
|| isInTableList( $splitArgs[ 2 ], @ARGV ) )
{
############
# 04.01.00 #
if ( $saveReferedTable ne $splitArgs[ 2 ]
|| $saveReferingTable ne $splitArgs[ 1 ]
|| $saveReferedKey ne $key_cols
|| $saveReferingKey ne $splitArgs[ 3 ] )
{
###############
# 04.01.00.00 #
$saveReferingTable = $splitArgs[ 1 ];
$saveReferedTable = $splitArgs[ 2 ];
$saveReferingKey = $splitArgs[ 3 ];
$saveReferedKey = $key_cols;
###############
# 04.01.00.01 #
print "ALTER TABLE\n\t$splitArgs[ 1 ]\nADD
CONSTRAINT\n\t\"";
###############
# 04.01.00.02 #
if ( $splitArgs[ 0 ] eq "" )
{
print "fk__";
print $splitArgs[ 1 ];
print "__";
print $splitArgs[ 2 ];
}
else
{
print "\n\t$splitArgs[ 0 ]";
}
print "\"";
###############
# 04.01.00.03 #
print "\nFOREIGN KEY\n\t(
$key_cols )\nREFERENCES\n\t$splitArgs[ 2 ]( $ref_cols )";
###############
# 04.01.00.04 #
if ( $splitArgs[ 3 ] ne "UNSPECIFIED" )
{
print "\nMATCH $splitArgs[ 3 ]";
}
###############
# 04.01.00.05 #
print "\n";
if ( $targs[ 6 ] eq 'c' )
{
print "ON DELETE\n\tCASCADE\n";
}
elsif ( $targs[ 6 ] eq 'r' )
{
print "ON DELETE\n\tRESTRICT\n";
}
elsif ( $targs[ 6 ] eq 'n' )
{
print "ON DELETE\n\tSET NULL\n";
}
elsif ( $targs[ 6 ] eq 'a' )
{
print "ON DELETE\n\tNO ACTION\n";
}
elsif ( $targs[ 6 ] eq 'd' )
{
print "ON DELETE\n\tSET DEFAULT\n";
}
###############
# 04.01.00.06 #
if ( $targs[ 5 ] eq 'c' )
{
print "ON UPDATE\n\tCASCADE\n";
}
elsif ( $targs[ 5 ] eq 'r' )
{
print "ON UPDATE\n\tRESTRICT\n";
}
elsif ( $targs[ 5 ] eq 'n' )
{
print "ON UPDATE\n\tSET NULL\n";
}
elsif ( $targs[ 5 ] eq 'a' )
{
print "ON UPDATE\n\tNO ACTION\n";
}
elsif ( $targs[ 5 ] eq 'd' )
{
print "ON UPDATE\n\tSET DEFAULT\n";
}
###############
# 04.01.00.07 #
if ( ! $targs[ 3 ] )
{
print "NOT ";
}
print "DEFERRABLE";
###############
# 04.01.00.08 #
print "\nINITIALLY";
if ( $targs[ 4 ] )
{
print " DEFERRED";
}
else
{
print " IMMEDIATE";
}
###############
# 04.01.00.09 #
print ";\n\n\n";
}
}
}
######
# 05 #
$targResultSet->finish;
######
# 06 #
$dbh->disconnect();
######################## end of main ############################
sub isInTableList
################################################################
## isInTableList
##
## Input:
## $candidate - This is the table name that we are testing
## to see if there is a matching entry in the
## table array.
##
## @tableArray - This is a list of tables (from @ARGV) that
## we must generate SQL statements for.
##
## Output:
## True ( 1 ) if $candidate is in @tableArray.
## False ( 0 ) if $candidate is not in @tableArray.
##
## Description:
## See output.
##
## Side effects:
## None
################################################################
{
my $candidate = $_[ 0 ];
my @tableArray = $_[ 1 ];
foreach $tableName ( @tableArray )
{
if ( $tableName eq $candidate )
{
return ( 1 )
}
}
return ( 0 );
}
__________________________________________________________________
John Pagakis, President
Toolsmythe Software Services, Inc.
Email: thebfh@toolsmythe.com
"Chance favors the prepared mind."
-- Louis Pasteur
This signature generated by
... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc.
www.spazmodicfrog.com
