#!/usr/bin/perl

print STDERR "Depricated.  Use /app/tools/dbadmin/bin/diff_dbs.pl\n";
exit 1;

#
# This script compares two RPS databases, and produces a report describing how
# they differ.
#
# The report can be in two different formats:
#  -r : produces a 'regular', english report
#  -a : produces a set of SQL statements that, if executed, would transform the second
#       database's schema to match the first database.
#

use strict;
use Data::Dumper;
use Getopt::Std;

my @alters;
my $gReport     = 0;
my $gAlter      = 1;
my $gDebug      = 0;
my $gDropTables = 0;

my %opt;
getopts( 'r:a:d', \%opt );

if ( defined $opt{r} ) {
    $gReport = $opt{r};
}

if ( defined $opt{a} ) {
    $gAlter = $opt{a};
}

if ( defined $opt{d} ) {
    $gDebug = 1;
}

if ( defined $opt{t} ) {
    $gDropTables = $opt{t};
}

my $db1Name = $ARGV[0];
my $db2Name = $ARGV[1];
if ( !$db1Name || !$db2Name ) {
    _usage();
}

my $schema1 = _getSchema($db1Name);
my $schema2 = _getSchema($db2Name);

_diffSchemas( $schema1, $schema2 );

if ($gAlter) {
    print "--\n";
    print "-- ALTERS to bring $db2Name in sync with $db1Name\n";
    print "--\n\n";
    print "USE $db2Name ;\n\n";
    foreach my $alter (@alters) {
        print $alter . "\n";
    }
}

# ----------------------------------------

sub _usage {
    print "This program compares the schemas of two databases.\n";
    print "USAGE:\n";
    print "$0 DATABASE1 DATABASE2 [-r] [-a] [-t] [-d]\n";
    print " (note - the '-' switches are boolean flags. Pass '1' to enable, '0' to disable)\n";
    print " -a : output SQL statements necessary to make DATABASE2 look like DATABASE1 (default)\n";
    print " -r : print a report detailing the differences\n";
    print " -t : If set to '1', output DROP TABLE statements in the Alter report\n";
    print " -d : produce debugging output: verbose and messy\n";

    exit(1);
}

sub _getSchema {
    my ($name) = @_;

    # See if this is a filename (rather than a database name)
    #
    my $lines;
    if ( $name =~ m/FILE:\/\/(.*)/ ) {
        $lines = _getLinesFromFile($1);
    } else {
        $lines = _getLinesFromDB($name);
    }

    my %schema;
    $schema{_dbName} = $name;

    # This parser uses a little 'state machine'.
    # We start out in the 'looking for table' state.
    # Once we find the start of a table definition, we transition into
    # the 'reading table' state.  Then back to 'looking for table' once
    # we hit the end of the table definition block.
    #
    my $kLookingForTable = 0;
    my $kReadingTable    = 1;

    my $colIndex = 0;
    my $i        = 0;
    my $state    = $kLookingForTable;
    my $currentTable;
    for ( my $i = 0 ; $i < scalar @$lines ; $i++ ) {
        my $line = $lines->[$i];
        chomp $line;

        if ( ',' eq substr( $line, -1 ) ) {
            substr( $line, -1 ) = '';
        }

        if ( $kLookingForTable == $state ) {
            next unless $line =~ m/CREATE TABLE/;

            $line =~ m/\`(.*)\`/;
            $currentTable = $1;
            $state        = $kReadingTable;
            $colIndex     = 0;
            next;
        }

        if ( $kReadingTable eq $state ) {
            if ( $line =~ m/\)(.*)\;/ ) {
                $schema{$currentTable}{_settings} = $1;
                $state = $kLookingForTable;
                next;
            }

            if ( $line =~ m/PRIMARY KEY/ ) {
                $line =~ m/\((.*)\)/;
                $schema{$currentTable}{_keys}{_primaryKey} = $1;
                next;
            }

            if ( $line =~ m/KEY/ ) {
                $line =~ m/\`(.*)\` \((.*)\)/;
                $schema{$currentTable}{_keys}{$1} = $2;
                next;
            }

            $line =~ m/\`(.*)\`(.*)/;
            $schema{$currentTable}{$1} = $2;
            $schema{$currentTable}{_order}{$colIndex} = $1;
            $colIndex++;

            next;
        }
    }

    return \%schema;
}

sub _getLinesFromFile {
    my ($path) = @_;
    die "ERROR : $path does not exist\n"                   unless -e $path;
    die "ERROR : $path does not point to a regular file\n" unless -f $path;

    my @lines;
    open SCHEMA, $path or die "ERROR : could not open file $path for reading: $!\n";
    while ( my $line = <SCHEMA> ) {
        chomp $line;
        push @lines, _cleanLine($line);
    }

    return \@lines;
}

sub _getLinesFromDB {
    my ($name) = @_;

    my $dump = `mysqldump -uroot -d $name`;

    if ($gDebug) {
        print "$dump\n";
    }

    my @nonCleanLines = split( "\n", $dump );
    my @lines;
    foreach my $line (@nonCleanLines) {
        push @lines, _cleanLine($line);
    }

    return \@lines;
}

sub _cleanLine {
    my ($inLine) = @_;

    my $kReadingChar         = 0;
    my $kReadingQuotedPhrase = 1;
    my $kReadingWhiteSpace   = 2;

    # I want to get rid of extraneous spaces, so that trivial differences in
    # white space won't make a difference.
    #
    my @chars = split( //, $inLine );
    my @newChars;
    my $state = $kReadingChar;
    for ( my $i = 0 ; $i < scalar @chars ; $i++ ) {
        if ( $kReadingWhiteSpace == $state ) {

            # skip over extra white space
            #
            if ( _isWhiteSpace( $chars[$i] ) ) {
                next;
            }
            $state = $kReadingChar;
        }

        if ( $kReadingChar == $state ) {
            push @newChars, $chars[$i];
            if ( _isQuote( $chars[$i] ) ) {
                $state = $kReadingQuotedPhrase;
            } elsif ( _isWhiteSpace( $chars[$i] ) ) {
                $state = $kReadingWhiteSpace;
            } elsif ( ',' eq $chars[$i] ) {
                $state = $kReadingWhiteSpace;
            }
        } elsif ( $kReadingQuotedPhrase == $state ) {
            push @newChars, $chars[$i];

            # assuming no nested quotes...
            #
            if ( _isQuote( $chars[$i] ) ) {
                $state = $kReadingChar;
            }
        }
    }

    my $outLine = join( "", @newChars );
    return $outLine;
}

sub _isWhiteSpace {
    my ($c) = @_;
    return 1 if ( $c eq ' ' );
    return 0;
}

sub _isQuote {
    my ($c) = @_;
    return 1 if $c eq '\`';
    return 1 if $c eq '\'';
    return 1 if $c eq '\"';
    return 0;
}

sub _diffSchemas {
    my ( $schema1, $schema2 ) = @_;

    # First, see if either schema contains a table the other does not.
    #
    my @commonTables = _findCommonTables( $schema1, $schema2 );

    # Now compare the columns and keys in each common table.
    #
    foreach my $tableName (@commonTables) {
        _compareTable( $tableName, $schema1, $schema2 );
    }
}

sub _findCommonTables {
    my ( $schema1, $schema2 ) = @_;

    my %inBoth;
    foreach my $tableName ( sort keys %$schema1 ) {
        next if ( '_' eq substr( $tableName, 0, 1 ) );

        if ( exists $schema2->{$tableName} ) {
            $inBoth{$tableName} = 1;
        } else {
            _report( $schema2->{_dbName} . " missing table $tableName\n" );
            _addTable( $schema1, $tableName );
        }
    }
    foreach my $tableName ( sort keys %$schema2 ) {
        next if ( '_' eq substr( $tableName, 0, 1 ) );

        if ( exists $schema1->{$tableName} ) {
            $inBoth{$tableName} = 1;
        } else {
            _report( $schema1->{_dbName} . " missing table $tableName\n" );

            if ($gDropTables) {
                _alter("DROP TABLE $tableName ;");
            }
        }
    }

    return sort keys %inBoth;
}

sub _compareTable {
    my ( $tableName, $schema1, $schema2 ) = @_;

    _report("\n-------- $tableName --------\n");

    my $padding = _max( length( $schema1->{_dbName} ), length( $schema2->{_dbName} ) ) + 1;

    my $table1 = $schema1->{$tableName};
    my $table2 = $schema2->{$tableName};

    # Iterate over the fields in the first table, and compare with the second.
    # We are looking for:
    # - fields that have been added
    # - fields that have been deleted
    # - fields that have been moved
    # - fields that have been renamed
    # So, the basic strategy is to first detect when there is a difference.
    # If there is a difference, is it simply a rename?
    #  If not, then is it a moved field?
    #    If not, then is this a new field?
    #      If not, then should we delete the old field?
    #
    # What if a field is renamed _and_ moved?  That seems rather hard to unambiguously detect.
    # - but, it may be possible.  If a field is missing, and there is a field with the same
    #   definition in the second table that does not exist in the first, we can perhaps assume this
    #   has been renamed and moved.
    #
    # If a field in a particular position has a diferrent name and a different definition, and none of
    # the other conditions are satisfied, then we can just alter 1 into the other, and let the database
    # do its best to convert the data.
    #

    # I am going to want to keep track of columns that I have already dealt with.
    # (by index).
    #
    my %touchedColumns;

    my $orderHash1 = $table1->{_order};
    my $orderHash2 = $table2->{_order};

    my $numColsTable1 = scalar keys %$orderHash1;

    my $i;
    for ( $i = 0 ; $i < $numColsTable1 ; $i++ ) {
        _debugPrintHash( "orderHash1: ", $orderHash1 );
        _debugPrintHash( "orderHash2: ", $orderHash2 );

        my $col1 = $orderHash1->{$i};
        my $col2 = $orderHash2->{$i};

        _report("!!! i = $i  col1 = $col1  col2 = $col2\n");

        # If table2 does not even have a column in this slot (we've run off the end),
        # then just add it.
        #
        if ( !defined $col2 ) {
            _report( $schema2->{_dbName} . " missing column $col1\n" );
            _alter( "ALTER TABLE $tableName ADD COLUMN $col1 " . $table1->{$col1} . " ;" );

            # !!! I don't really need to modify my schema structure anymore - from this point
            # !!! forward, any columns in table 1 will be brand new to table 2.
            #
            next;
        }

        # Do they have different names?
        #
        if ( $col1 ne $col2 ) {

            #  Does this column exist elsewhere in table2?
            #
            if ( exists $table2->{$col1} ) {
                _moveColumn( $i, $tableName, $schema1, $schema2, $table1, $table2, $orderHash1, $orderHash2, $col1, $col2 );
                next;
            }

            # Does the column in table2 exist elsewhere in table1?
            #
            if ( exists $table1->{$col2} ) {
                _insertColumn( $i, $tableName, $schema1, $schema2, $table1, $table2, $orderHash1, $orderHash2, $col1, $col2 );
                next;
            }

            # This is a rename/redefine.
            #
            _redefineColumn( $i, $tableName, $schema1, $schema2, $table1, $table2, $orderHash1, $orderHash2, $col1, $col2 );
            next;
        }

        # Do they have different definitions?
        #
        if ( $table1->{$col1} ne $table2->{$col1} ) {
            _redefineColumn( $i, $tableName, $schema1, $schema2, $table1, $table2, $orderHash1, $orderHash2, $col1, $col2 );
            next;
        }

        # They are identical. Nothing to do.
        #
    }

    # Any remaining columns in table2 should be deleted.
    #

    my $numColsTable2 = scalar keys %$orderHash2;

    for ( my $j = $i ; $j < $numColsTable2 ; $j++ ) {
        my $col2 = $orderHash2->{$j};
        _deleteColumn( $i, $tableName, $schema2, $table2, $orderHash2, $col2 );
    }

    # Now compare the keys.
    #
    my %sharedKeys;
    foreach my $key ( sort keys %{ $table1->{_keys} } ) {
        if ( exists $table2->{_keys}{$key} ) {
            $sharedKeys{$key} = 1;
        } else {
            _report( $schema2->{_dbName} . " missing key $key\n" );

            if ( '_primaryKey' eq $key ) {
                _alter( "ALTER TABLE $tableName ADD PRIMARY KEY (" . $table1->{_keys}{$key} . ") ;" );
            } else {
                _alter( "ALTER TABLE $tableName ADD KEY \`$key\` (" . $table1->{_keys}{$key} . ") ;" );
            }
        }
    }

    foreach my $key ( sort keys %{ $table2->{_keys} } ) {
        if ( exists $table1->{_keys}{$key} ) {
            $sharedKeys{$key} = 1;
        } else {
            _report( $schema1->{_dbName} . " missing key $key\n" );
            if ( '_primaryKey' eq $key ) {
                _alter("ALTER TABLE $tableName DROP PRIMARY KEY ;");
            } else {
                _alter("ALTER TABLE $tableName DROP KEY \`$key\` ;");
            }
        }
    }

    foreach my $key ( sort keys %sharedKeys ) {
        if ( $table1->{_keys}{$key} ne $table2->{_keys}{$key} ) {
            if ( '_primaryKey' eq $key ) {
                _report("PRIMARY KEY:\n");
                _alter("ALTER TABLE $tableName DROP PRIMARY KEY ;");
                _alter( "ALTER TABLE $tableName ADD PRIMARY KEY (" . $table1->{_keys}{$key} . ") ;" );
            } else {
                _report("KEY $key\n");
                _alter("ALTER TABLE $tableName DROP KEY \`$key\` ;");
                _alter( "ALTER TABLE $tableName ADD KEY \`$key\` (" . $table1->{_keys}{$key} . ") ;" );
            }
            _report("\t"
                  . _paddedString( $schema1->{_dbName}, $padding ) . ":"
                  . $table1->{_keys}{$key} . "\n\t"
                  . _paddedString( $schema2->{_dbName}, $padding ) . ":"
                  . $table2->{_keys}{$key}
                  . "\n" );
        }
    }

    # Now see if the table settings (i.e. the last line in the table definition) match.
    # !!! Since we can't actually modify this with an alter in a consistant way, let's not bother.
    #
    #    if ($table1->{_settings} ne $table2->{_settings})
    #    {
    #        _report("TABLE SETTINGS: " . $table1->{_settings} . " : " . $table2->{_settings});
    #        _alter("ALTER TABLE $tableName " . $table1->{_settings} . " ;");
    #    }
}

sub _paddedString {
    my ( $string, $size ) = @_;

    my $outString = $string . ( ' ' x ( $size - length($string) ) );
    return $outString;
}

sub _max {
    my ( $val1, $val2 ) = @_;

    return ( $val1 > $val2 ? $val1 : $val2 );
}

sub _report {
    my ($string) = @_;

    if ($gReport) {
        print $string;
    }
}

sub _alter {
    my ($alter) = @_;

    if ($gAlter) {
        push @alters, $alter;
    }
}

sub _addTable {
    my ( $schema, $tableName ) = @_;

    _alter("CREATE TABLE \`$tableName\` (");

    my $table = $schema->{$tableName};

    # columns first
    #
    foreach my $column ( sort keys %$table ) {
        next if ( '_' eq substr( $column, 0, 1 ) );
        my $colDef = $table->{$column};
        my $alter  = "\`$column\` $colDef,";
        _alter($alter);
    }

    # Then the keys.
    #
    my @keys;
    foreach my $key ( keys %{ $table->{_keys} } ) {
        my $def = $table->{_keys}{$key};
        if ( '_primaryKey' eq $key ) {
            push @keys, "PRIMARY KEY ($def)";
        } else {
            push @keys, "KEY \`$key\` ($def)";
        }
    }

    for ( my $i = 0 ; $i < scalar @keys ; $i++ ) {
        my $alter = $keys[$i];

        if ( $i < ( ( scalar @keys ) - 1 ) ) {
            $alter .= ',';
        }
        _alter($alter);
    }

    _alter(") ENGINE=MyISAM DEFAULT CHARSET=latin1 ;\n");
}

sub _moveColumn {
    my ( $i, $tableName, $schema1, $schema2, $table1, $table2, $orderHash1, $orderHash2, $col1, $col2 ) = @_;

    # We want to _move_ this column, possibly re-defining it as well.
    #
    my $moveTo;
    if ( 0 == $i ) {
        $moveTo = 'FIRST';
    } else {
        my $lastIndex = $i - 1;
        $moveTo = 'AFTER ' . $orderHash1->{$lastIndex};
    }
    _report( $schema2->{_dbName} . " moving column $col1 to $moveTo\n" );
    _alter( "ALTER TABLE $tableName MODIFY COLUMN $col1 " . $table1->{$col1} . " $moveTo ;" );

    # Update our internal data structure to reflect this change.
    #
    $table2->{$col1} = $table1->{$col1};

    # Need to adjust table 2's order hash as well.
    # I need to shift everything above the column's old position down 1 slot.
    #
    # So, first find the old position.
    my $oldIndex;
    my $t2Size = scalar keys %$orderHash2;
    for ( my $i2 = $i + 1 ; $i2 < $t2Size ; $i2++ ) {
        if ( $orderHash2->{$i2} eq $col1 ) {
            $oldIndex = $i2;
            last;
        }
    }

    # Now shift
    #
    for ( my $j = $oldIndex ; $j >= $i ; $j-- ) {
        my $prev = $j - 1;
        $orderHash2->{$j} = $orderHash2->{$prev};
    }

    $orderHash2->{$i} = $col1;
}

sub _insertColumn {
    my ( $i, $tableName, $schema1, $schema2, $table1, $table2, $orderHash1, $orderHash2, $col1, $col2 ) = @_;

    # Figure out where this column is getting inserted.
    #
    my $insertAt;
    if ( 0 == $i ) {
        $insertAt = 'FIRST';
    } else {
        my $lastIndex = $i - 1;
        $insertAt = 'AFTER ' . $orderHash1->{$lastIndex};
    }
    _report( $schema2->{_dbName} . " inserting column $col1 at $insertAt\n" );
    _alter( "ALTER TABLE $tableName ADD COLUMN $col1 " . $table1->{$col1} . " $insertAt ;" );

    # Now we need to adjust our internal representation.
    #
    # Shift everything from $i down 1
    #
    my $t2Size = scalar keys %$orderHash2;
    for ( my $j = $t2Size ; $j >= $i ; $j-- ) {
        my $prev = $j - 1;
        $orderHash2->{$j} = $orderHash2->{$prev};
    }

    # Now insert in the order hash, and update the table2 model.
    #
    $orderHash2->{$i} = $col1;
    $table2->{$col1}  = $table1->{$col1};
}

sub _redefineColumn {
    my ( $i, $tableName, $schema1, $schema2, $table1, $table2, $orderHash1, $orderHash2, $col1, $col2 ) = @_;

    my $newDef = "$col1 " . $table1->{$col1};
    _report( $schema2->{_dbName} . " modifying column $col2 to $newDef\n" );
    _alter("ALTER TABLE $tableName CHANGE COLUMN $col2 $newDef ;");

    # Update the internal representation.
    #
    delete $table2->{$col2};
    $table2->{$col1}  = $table1->{$col1};
    $orderHash2->{$i} = $col1;
}

sub _deleteColumn {
    my ( $i, $tableName, $schema2, $table2, $orderHash2, $col2 ) = @_;

    _report( $schema2->{_dbName} . " deleting column $col2\n" );
    _alter("ALTER TABLE $tableName DROP COLUMN $col2 ;");
}

sub _debugPrintHash {
    my ( $string, $hash ) = @_;
    _report( $string . "\n" );

    foreach my $key ( sort { $a <=> $b } keys %$hash ) {
        _report( "$key => " . $hash->{$key} . "\n" );
    }
}