Volunteers Needed

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