From e0b3d4901e644cedac7e308ab977150825051c7a Mon Sep 17 00:00:00 2001
From: Todd Lyons <tlyons@ivenue.com>
Date: Thu, 9 Dec 2010 14:48:53 -0800
Subject: [PATCH] Add foreign key support.
Ignores triggers, future feature.
---
Diff.pm | 65 +++++++++++++++++++++++++++++++++++++++++++++++++++---------
Table.pm | 14 ++++++++++++-
mysqldiff | 17 ++++++++++-----
3 files changed, 79 insertions(+), 17 deletions(-)
diff --git a/Diff.pm b/Diff.pm
index da31629..bcf25b1 100644
--- a/Diff.pm
+++ b/Diff.pm
@@ -23,17 +23,18 @@ sub diff_dbs {
foreach my $table1 ($db[0]->tables()) {
my $name = $table1->name();
- if ($table_re && $name !~ $table_re) {
- debug(2, " table `$name' didn't match /$table_re/; ignoring\n");
+ ( my $clean = $name ) =~ s/\`//g;
+ if ($table_re && $clean !~ $table_re) {
+ debug(2, " table `$clean' didn't match /$table_re/; ignoring\n");
next;
}
- debug(2, " looking at tables called `$name'\n");
+ debug(2, " looking at tables called `$clean'\n");
if (my $table2 = $db[1]->table_by_name($name)) {
- debug(4, " comparing tables called `$name'\n");
+ debug(4, " comparing tables called `$clean'\n");
push @changes, diff_tables($opts, $table1, $table2);
}
else {
- debug(3, " table `$name' dropped\n");
+ debug(3, " table `$clean' dropped\n");
push @changes, "DROP TABLE $name;\n\n"
unless $opts{'only-both'} || $opts{'keep-old-tables'};
}
@@ -41,12 +42,14 @@ sub diff_dbs {
foreach my $table2 ($db[1]->tables()) {
my $name = $table2->name();
- if ($table_re && $name !~ $table_re) {
- debug(2, " table `$name' matched $opts{'table-re'}; ignoring\n");
+ ( my $clean = $name ) =~ s/\`//g;
+ if ($table_re && $clean !~ $table_re) {
+ debug(2, " table `$clean' didn't match $opts{'table-re'}; ignoring\n");
next;
}
+ debug(2, " looking at tables called `$clean'\n");
if (! $db[0]->table_by_name($name)) {
- debug(3, " table `$name' added\n");
+ debug(3, " table `$clean' added\n");
push @changes, $table2->def() . "\n"
unless $opts{'only-both'};
}
@@ -78,8 +81,8 @@ sub diff_banner {
##
## Run on $now
$opt_text##
-## --- $summary1
-## +++ $summary2
+## --- $summary1 => this is the database to be changed
+## +++ $summary2 => make database above be the same as this one
EOF
}
@@ -88,6 +91,7 @@ sub diff_tables {
my @changes = (diff_fields(@_),
diff_indices(@_),
diff_primary_key(@_),
+ diff_foreign_keys(@_),
diff_options(@_));
if (@changes) {
$changes[-1] =~ s/\n*$/\n/;
@@ -275,6 +279,42 @@ sub index_auto_col {
return "ALTER TABLE $name ADD INDEX ($field); # auto columns must always be indexed\n";
}
+sub diff_foreign_keys {
+ my ($opts, $table1, $table2) = @_;
+
+ my %opts = %$opts;
+ my $name1 = $table1->name();
+
+ my @changes = ();
+
+ foreach my $fkey ( $table1->foreign_keys_keys() ) {
+ my $fk1 = $table1->foreign_keys($fkey);
+ my $fk2 = $table2->foreign_keys($fkey);
+
+ if ($fk2) {
+ if (! grep { /^$fkey$/ } $table2->foreign_keys_keys() ) {
+ debug(4, " FOREIGN KEY `$fkey' not present\n");
+ my $change = "ALTER TABLE $name1 DROP FOREIGN KEY $fkey;"
+ unless $opts{'no-old-defs'};
+ $change .= "\n";
+ push @changes, $change;
+ }
+ }
+ }
+
+ foreach my $fkey ( $table2->foreign_keys_keys() ) {
+ my $fk1 = $table1->foreign_keys($fkey);
+ my $fk2 = $table2->foreign_keys($fkey);
+ if (! grep { /^$fkey$/ } $table1->foreign_keys_keys() ) {
+ debug(4, " FOREIGN KEY `$fkey' added\n");
+ push @changes,
+ "ALTER TABLE $name1 ADD CONSTRAINT $fkey FOREIGN KEY ($fk2->[0]) REFERENCES $fk2->[1] ($fk2->[2]) $fk2->[3];\n";
+ }
+ }
+
+ return @changes;
+}
+
sub diff_options {
my ($opts, $table1, $table2) = @_;
@@ -283,6 +323,11 @@ sub diff_options {
my $options2 = $table2->options();
my $name = $table1->name();
+ if ( ! $opts{'autoincrement'} ) {
+ $options1 =~ s/AUTO_INCREMENT=\d+ //;
+ $options2 =~ s/AUTO_INCREMENT=\d+ //;
+ }
+
my @changes = ();
if ($options1 ne $options2) {
diff --git a/Table.pm b/Table.pm
index 4258120..f0468b7 100644
--- a/Table.pm
+++ b/Table.pm
@@ -8,6 +8,7 @@ use Class::MakeMethods::Template::Hash
'scalar' => 'name def source primary_key options',
'array --get_set_ref' => 'lines',
'hash' => 'fields indices unique_index fulltext',
+ 'hash_of_arrays' => 'foreign_keys',
;
use MySQL::Utils qw(debug);
@@ -74,6 +75,15 @@ sub parse {
next;
}
+ if (/^CONSTRAINT\s+(\S+?)\s+FOREIGN\s+KEY\s*\((\S+)\)\s+REFERENCES\s+(\S+)\s+\((\S+)\)\s*(.*)$/ ) {
+ my($fkey, $local_field, $other_table, $other_field, $conditions) = ($1, $2, $3, $4, $5, $6);
+ croak "FOREIGN KEY `$fkey' duplicated in table `$name'\n"
+ if ( grep { /^$fkey$/ } $self->foreign_keys_keys() );
+ $self->foreign_keys_push($fkey, ($local_field, $other_table, $other_field, $conditions) );
+ debug(6, " got FOREIGN KEY `$fkey': ($local_field) to $other_table($other_field)\n");
+ next;
+ }
+
if (/^\)\s*(.*?);$/) { # end of table definition
my $options = $self->options($1);
debug(6, " got table options `$options'\n");
@@ -98,8 +108,10 @@ sub parse {
@lines = grep ! m{^/\*!40000 ALTER TABLE \Q$name\E DISABLE KEYS \*/;},
@lines;
+ # Look for extra lines that exist after end of table definition
+ # was reached, but ignore it if it looks like triggers.
warn "table `$name' had trailing garbage:\n", join '', @lines
- if @lines;
+ if ( @lines && ! grep { /DROP|DELIMITER|TRIGGER/ } @lines );
}
1;
diff --git a/mysqldiff b/mysqldiff
index eba78c0..0ab76fc 100755
--- a/mysqldiff
+++ b/mysqldiff
@@ -25,6 +25,7 @@ use Carp qw(:DEFAULT cluck);
use FindBin qw($RealBin $Script);
use lib $RealBin;
use Getopt::Long;
+use IO::File;
use MySQL::Diff qw(parse_arg diff_dbs);
use MySQL::Utils qw(debug_level);
@@ -32,11 +33,11 @@ use MySQL::Utils qw(debug_level);
my %opts = ();
GetOptions(\%opts, "help|?", "debug|d:i", "apply|A", "batch-apply|B",
"keep-old-tables|k", "no-old-defs|n", "only-both|o", "table-re|t=s",
- "host|h=s", "port|P=s", "user|u=s", "password|p:s",
- "host1|h1=s", "port1|P1=s", "user1|u1=s", "password1|p1:s",
- "host2|h2=s", "port2|P2=s", "user2|u2=s", "password2|p2:s",
+ "host|h=s", "port|P=s", "user|u=s", "password:s",
+ "host1|h1=s", "port1|P1=s", "user1|u1=s", "password1:s",
+ "host2|h2=s", "port2|P2=s", "user2|u2=s", "password2:s",
"socket|s=s", "socket1|s1=s", "socket2|s2=s",
- "tolerant|i"
+ "tolerant|i", "autoincrement!"
);
if (@ARGV != 2 or $opts{help}) {
@@ -57,7 +58,7 @@ for my $num (0, 1) {
$db[$num] = $new_db;
}
-$| = 1;
+STDOUT->autoflush(1);
my $diffs = diff_dbs(\%opts, @db);
print $diffs;
apply($diffs) if $opts{apply} || $opts{'batch-apply'};
@@ -69,7 +70,9 @@ exit 0;
sub usage {
print STDERR @_, "\n" if @_;
die <<EOF;
-Usage: $Script [ options ] <database1> <database2>
+Function: Make target database1 schema look like master database2 schema
+
+Usage: $Script [ options ] <database> <database2>
Options:
-?, --help show this help
@@ -81,6 +84,8 @@ Options:
-n, --no-old-defs suppress comments describing old definitions
-t, --table-re=REGEXP restrict comparisons to tables matching REGEXP
-i, --tolerant ignore DEFAULT and formatting changes
+ --autoincrement use AUTOINCREMENT in table options comparison
+ --no-autoincrement ignore AUTOINCREMENT in table options (default)
-h, --host=... connect to host
-P, --port=... use this port for connection
--
1.7.1
