-
Notifications
You must be signed in to change notification settings - Fork 9
onishi/mysqldiff
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.
Folders and files
Name | Name | Last commit message | Last commit date | |
---|---|---|---|---|
Repository files navigation
#!/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 =~ /^\s*PRIMARY KEY\s+\((.*)\)/) { push @$primary_keys, $1; } elsif ($line =~ /^\s*UNIQUE KEY\s+`(.*)`\s+\((.*)\)/) { push @$unique_keys, { name => $1, column => $2, }; } elsif ($line =~ /^\s*KEY\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
About
mysqldiff - mysql scheme diff
Resources
Stars
Watchers
Forks
Releases
No releases published
Packages 0
No packages published