#!/usr/bin/env perl use strict; use warnings;

@ARGV == 2 or die 'Usage: mysqldiff dbname1 dbname2';

my @tables = map { my $scheme; if (-e $) { # file my $create_table = slurp($); my $tmp_dbname = join '', 'tmp', time(); system(qw/mysqladmin -uroot create/, $tmp_dbname); system('mysql', '-uroot', $tmp_dbname, '-e', $create_table); my @cmd = (qw/mysqldump -uroot --no-data=true/, $tmp_dbname); $scheme = @cmd; system(qw/mysql -uroot/, $tmp_dbname, '-e', "drop database $tmp_dbname"); } elsif (/ /) { # remote db my @cmd = (qw/mysqldump --no-data=true/, split /\s+/, $); $scheme = @cmd; my $tmp_dbname = join '', 'tmp', time(); system(qw/mysqladmin -uroot create/, $tmp_dbname); system('mysql', '-uroot', $tmp_dbname, '-e', $scheme); @cmd = (qw/mysqldump -uroot --no-data=true/, $tmp_dbname); $scheme = @cmd; system(qw/mysql -uroot/, $tmp_dbname, '-e', "drop database $tmp_dbname"); } else { # local db my $dbname = $; my @cmd = (qw/mysqldump -uroot --no-data=true/, $dbname); $scheme = @cmd; } parse($scheme); } @ARGV;

print diff(@tables);

sub slurp { my $filename = shift; open my $fh, '<', $filename or die; my $res = do { local $/; <$fh> }; close $fh; $res; }

sub parse { my $tables = []; my $scheme = shift or return $tables; for ($scheme =~ /(CREATE TABLE .? ENGINE[^;]);/smg) { my $content = $_; $content =~ /(.*?)/ or next; my $table_name = $1; my ($columns, $unique_keys, $keys, $primary_keys); for my $line (split /\n/, $content) { $line =~ /^CREATE/ and next; $line =~ /^)/ and next; if ($line =~ /^\sPRIMARY KEY\s+((.))/) { push @$primary_keys, $1; } elsif ($line =~ /^\sUNIQUE KEY\s+(.*)\s+((.))/) { push @$unique_keys, { name => $1, column => $2, }; } elsif ($line =~ /^\sKEY\s+(.*)\s+((.))/) { push @$keys, { name => $1, column => $2, }; } elsif ($line =~ /^\s*(.*?)\s+(.+?)[\n,]?$/) { push @$columns, { column => $1, definition => $2, } } } push @$tables, { table_name => $table_name, primary_keys => $primary_keys || [], unique_keys => $unique_keys || [], keys => $keys || [], columns => $columns || [], content => $content, }; } return $tables; }

sub diff { my ($old, $new) = @; my $diff = ''; my @old_table_names = sort map { $->{table_name} } @$old; my @new_table_names = sort map { $->{table_name} } @$new; my (%old_hash, %new_hash); for (@$old) { $old_hash{$->{table_name}} = $; } for (@$new) { $new_hash{$->{table_name}} = $_; } for my $name (@new_table_names) { if ($old_hash{$name}) { my $old_hash = $old_hash{$name}; my $new_hash = $new_hash{$name}; $diff .= table_diff($name, $old_hash, $new_hash); } else { $diff .= $new_hash{$name}->{content} . ";\n\n"; } }

return $diff;

}

sub table_diff { my ($name, $old, $new) = @; my @change = grep {$} ( column_diff($old, $new), key_diff($old, $new), ); @change or return ''; return sprintf( "ALTER TABLE %s %s;\n\n", $name, join(', ', @change), ); }

sub column_diff { my ($old, $new) = @; my $old_columns = $old->{columns}; my $new_columns = $new->{columns}; my (@change, %old_hash, %new_hash, %all_hash); for (@$old_columns) { $old_hash{$->{column}} = $; $all_hash{$->{column}} = $; } for (@$new_columns) { $new_hash{$->{column}} = $; $all_hash{$->{column}} = $_; } for my $column (sort keys %all_hash) { my $old_definition = $old_hash{$column}->{definition}; my $new_definition = $new_hash{$column}->{definition}; if (!$old_definition) { push @change, "ADD $column $new_definition"; } elsif (!$new_definition) { push @change, "DROP $column"; } elsif ($old_definition ne $new_definition) { push @change, "MODIFY $column $new_definition"; } } return @change; }

sub key_diff { my ($old, $new) = @_;

my @change;
{# key
    my $old_keys = $old->{keys};
    my $new_keys = $new->{keys};
    my (%old_hash, %new_hash);
    for (@$old_keys) {
        $old_hash{$_->{column}} = 1;
    }
    for (@$new_keys) {
        $new_hash{$_->{column}} = 1;
    }
    # add key
    for my $key (@$new_keys) {
        $old_hash{$key->{column}} and next;
        my $name = join '_', map { s{[`()]}{}g; $_ } split /,/, $key->{column};
        push @change, "ADD INDEX `$name` ($key->{column})";
    }
    # drop key
    for my $key (@$old_keys) {
        $new_hash{$key->{column}} and next;
        push @change, "DROP INDEX `$key->{name}`";
    }
};

{# unique_key
    my $old_keys = $old->{unique_keys};
    my $new_keys = $new->{unique_keys};
    my (%old_hash, %new_hash);
    for (@$old_keys) {
        $old_hash{$_->{column}} = 1;
    }
    for (@$new_keys) {
        $new_hash{$_->{column}} = 1;
    }
    # add unique_key
    for my $key (@$new_keys) {
        $old_hash{$key->{column}} and next;
        my $name = join '_', map { s{[`()]}{}g; $_ } split /,/, $key->{column};
        push @change, "ADD UNIQUE INDEX `$name` ($key->{column})";
    }
    # drop unique_key
    for my $key (@$old_keys) {
        $new_hash{$key->{column}} and next;
        push @change, "DROP INDEX `$key->{name}`";
    }
};

return @change;

}

END

=head1 NAME

mysqldiff - diff for mysql

=head1 SYNOPSIS

mysqldiff dbname1 dbname2 mysqldiff '-uroot -hlocalhost dbname1' '-uroot -hlocalhost dbname2' mysqldiff createtable1.sql createtable2.sql

=cut

Mysqldiff

Install / Use

README