#!/usr/perl/bin/perl -w

use strict;

{
 my $table;
 my @tables;
 my @all_refs;
 my %table_refs;
 my $debug = 0;

 while (<>) {
   if (/CREATE\s+TABLE\s+(\w+)/i) {
     $table = $1;     
     print "$table\n" if ($debug);
     push @tables, $table;
   }

   if (/references\s+(\w+)/) {
     my $table_ref = $1;
     print "\t$table_ref\n" if ($debug);

     if (! ($table_ref eq "dps_user") ) {	     
     	# A table which references itself is A-OK.
    	if (! ($table_ref eq $table) ) {
      	 $table_refs{$table_ref}{$table} = 1;
     	}

    	# Push it into a a straight table so we can do some sanity checking.
     	push @all_refs, $table_ref;
     } else {
 	die "Urg.  Found dps_user.";
     }
   }
 }

 # Hokay.  We have the list, we just need to sort things out.

 # Do some sanity checking so we can at least make sure that we're referencing
 # tables which exist in this schema.

 # Find anything in @all_refs that is not in @tables.

 if ($debug) {
   print '-' x 70; print "\n";
   foreach my $t ( keys %table_refs ) {
     print "$t referenced by ";   
     print "(";

     my %el_hash = %{ $table_refs{$t} };
     foreach my $i (keys %el_hash) {
       print "$i";
       print " ";
     }

     print ")\n";
   }
   print '-' x 70;
   print "\n";
 }

 # Make sure the tables actually exist.
 my %table_hash;
 foreach (@tables) { $table_hash{$_} = 1; }
 foreach my $tbl (@all_refs) {
   unless ($table_hash{$tbl})
   {
   	die "Reference to non-existant table \"$tbl\"";
   }
 }

 # Anything which doesn't appear in our map but appears in table is
 # a table with no references to it, so we can add it first.

 my @table_order;
 foreach (@tables) { 
   if (! defined($table_refs{$_})) {
     push @table_order, $_;
   }
 }

 do {
   foreach my $t (keys %table_refs) {

     # See if the table shows up in a reference.  If it does, remove it.
     my %el_hash = %{ $table_refs{$t} };

     # Run through the tables already there.  
     foreach (@table_order) {
       if (defined($el_hash{$_})) {
	 print "\t$t: $_\n" if ($debug);
	 delete $el_hash{$_};
       }
     }

     # If there are no more elements in the hash, add $t.
     if ((keys %el_hash) == 0) {
       print "\tadding $t\n" if ($debug);
       push @table_order, $t;
       delete $table_refs{$t};
     }

   }
   # if you're in table order, undef the ref to you in table_refs.

 } while ((keys %table_refs) > 0);

 for (@table_order) { print "DROP TABLE $_;\n"; }
 print "COMMIT WORK;\n";

} # end of main

1;

__END__