delete_some_fkeys.pl

#!/usr/bin/perl
use DBI;

####
####  this will list and delete foreign keys
####

%trgcnt;
%fkeytab;

$^ = "HDNG_TOP";

until (defined($host)) {
print "Please enter host or hit enter for default: ";
chomp($host = <STDIN>);
}

until (defined($db)&& $db) {
print "Please enter dbname: ";
chomp($db = <STDIN>);
}
print "Database = $db\n";

until (defined($tab) && $tab) {
print "Please enter tablename: ";
chomp($tab = <STDIN>);
}
print "tablename = $tab\n";

print "\n";

if (defined($host) && $host) 
   { $dsn = "DBI:Pg:dbname=$db;host=$host";
     print "Host is $host\n";
}else {
     $dsn = "DBI:Pg:dbname=$db";
     $host = `hostname`;
     $host =~ s/\.spectrumtelecorp\.com//;
     print "Host is $host\n";
   }
$dbh = DBI->connect($dsn,undef,undef,{PrintError => 0}) || die
"Can't connect to database [$dsn]\n";

open (OUTPUT ,">cre_" . $tab . "_foreign_keys.sql") || die " Can't create cre_" . $tab . "_foreign_keys.sql\n"; 

$sql = <<END_OF_SQL;
select tgargs from pg_trigger where tgrelid in 
 (select oid from pg_class where relname = '$tab') 
and tgname like 'RI_ConstraintTrigger%'
END_OF_SQL

###warn $sql;

$sth = $dbh->prepare($sql) || die $DBI::errstr." sth \n";
###$sth->execute() || die $DBI::errstr." sth \n";
$status = $sth->execute() || die $DBI::errstr." sth \n";

if ($status eq '0E0') {
   print "There are no foreign keys associated with $tab. \n";
   print "Exiting program...\n";
exit;
}

while($row = $sth->fetchrow_hashref())
{ 
   $$row{tgargs} =~ s/000//g;
   ($fkey,$fromtab,$totab,$x,$fromcol,$tocol) = split(/\\/, $$row{tgargs});
    if ($fromtab eq $tab) {
        $fromtab = ' ' . $fromtab;
     } 
   $fkeytab{$fromtab}{$totab}{$fkey}{$fromcol}{$tocol} +=1;
}  ### end of while 

$sth->finish();

show_rows();
delete_entries();
update_pgclass();

print "\n";
print "A file cre_" . $tab . "_foreign_keys.sql has been generated to restore fkeys! \n";
print "\n";


###===============================================================
### show the foreign keys associated with table ==================
###===============================================================

sub show_rows() {

$i =0;
for $key1 (sort keys %fkeytab) {
          $keyx = $key1;
          $keyx =~ s?\s??;
    for $key2 (sort keys %{$fkeytab{$key1}} ) {
        for $key3 (sort keys %{$fkeytab{$key1}{$key2}} ) {
            for $key4 (sort keys %{$fkeytab{$key1}{$key2}{$key3}} ) {
                for $key5 (sort keys %{$fkeytab{$key1}{$key2}{$key3}{$key4}} ) {
                    $i +=1;
                    $item1 = $keyx . " (" . $key4 . ")";
                    $item2 = $key2 . " (" . $key5 . ")";
                    write_line();
                }
            }
        }
    }
}

}  ### end of sub show_rows()

###===============================================================
###  delete those selected keys...or all of them =================
###===============================================================

sub delete_entries() {

print "\n Please select row numbers comma separated (1,2,3) to delete ";
print "\n or hit enter to delete all entries: ";
chomp($delin = <STDIN>);
$delin =~ s/ /,/;

@dbsel = split(/,/,$delin);
$m == 0;

for $keya (sort keys %fkeytab) {
    for $keyb (sort keys %{$fkeytab{$keya}} ) {
        for $keyc (sort keys %{$fkeytab{$keya}{$keyb}} ) {
            $m +=1; 
            if ($#dbsel > 0) {
               foreach $x(0..$#dbsel) {
                    if ($m == $dbsel[$x]) {
                       for $keyd (sort keys %{$fkeytab{$keya}{$keyb}{$keyc}} ) {
                           for $keye (sort keys %{$fkeytab{$keya}{$keyb}{$keyc}{$keyd}} ) {
                               print OUTPUT "Alter table $keya add constraint $keyc foreign key ($keyd) ";
                               print OUTPUT "references $keyb($keye); \n";
                               count_fkeys();
                               delete_fkeys();
                       }  ### end for keyd
                           }  ### end for keye
                    }  ### end if
                }  ### end foreach
           }else{
                for $keyd (sort keys %{$fkeytab{$keya}{$keyb}{$keyc}} ) {
                    for $keye (sort keys %{$fkeytab{$keya}{$keyb}{$keyc}{$keyd}} ) {
                        print OUTPUT "Alter table $keya add constraint $keyc foreign key ($keyd) ";
                        print OUTPUT "references $keyb($keye); \n";
                        count_fkeys();
                        delete_fkeys();
                }  ### end for keyd
                   }  ### end for keye
           }  ### end if
        }  ### end for keyc
    }  ### end for keyb
}  ### end for keya

}  ### end delete_entries

###===============================================================
### count the entries in by table for each FKEY entry ============
###===============================================================

sub count_fkeys() {

$sql1 = <<END_OF_SQL;
    select tgrelid, count(*) as counter from pg_trigger 
     where tgconstrname = '$keyc' group by tgrelid
END_OF_SQL

$sth1 = $dbh->prepare($sql1) || die $DBI::errstr." sth1 \n";
$sth1->execute() || die $DBI::errstr." sth1 \n";

while($row1 = $sth1->fetchrow_hashref())
     { $trgcnt{$$row1{tgrelid}} += $$row1{counter};
     }  ### end of while    

$sth1->finish();

}  ### end of sub

###===============================================================

sub delete_fkeys() {

$sql2 = <<END_OF_SQL;
delete from pg_trigger where tgconstrname = '$keyc'
END_OF_SQL

$sth2 = $dbh->do($sql2) || die $DBI::errstr." sth2 \n";

}  ### end of sub

###===============================================================
### adjust the pg_class row for each table to reflect the new ====
### reltriggers number.  =========================================
###===============================================================

sub update_pgclass(){

$sql3 = <<END_OF_SQL;
update pg_class set reltriggers = reltriggers - ? where oid = ?
END_OF_SQL

$sth3 = $dbh->prepare($sql3) || die $DBI::errstr." sth3 \n";

for $key1 (sort keys %trgcnt) {
    $adjust = $trgcnt{$key1};
    $x =  $sth3->execute($trgcnt{$key1},$key1) || die $DBI::errstr." sth3 \n"; 

}  ### end for

$sth3->finish();

}  ### end sub

$dbh->disconnect if $dbh;

###===============================================================

sub write_line {
    $~ = "PRINTFORMAT";
    write;
}

format PRINTFORMAT =
@### @<<<<<<<<<<<<<<<<<<<<   @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$i $key3 $item1 $item2
.

###===============================================================

sub heading {
    $~ = "HDNG_TOP";
    write;
}

format HDNG_TOP =

NO   foreign key             from table                      to table  
=======================================================================================================
.
__END__