libmysql-diff-perl 0.43-2 / usr / bin / mysql-schema-diff

#!/usr/bin/perl -w

eval 'exec /usr/bin/perl -w -S $0 ${1+"$@"}'
    if 0; # not running under some shell

=head1 NAME

mysql-schema-diff - compare MySQL database schemas

=head1 SYNOPSIS

  mysql-schema-diff [B<options>] B<database1> B<database2>

  mysql-schema-diff --help

=head1 DESCRIPTION

F<mysql-schema-diff> is a Perl script front-end to the
L<CPAN|http://www.perl.com/CPAN> module
L<MySQL::Diff|http://search.cpan.org/search?module=MySQL::Diff> which
compares the data structures (i.e. schema / table definitions) of two
L<MySQL|http://www.mysql.com/> databases, and returns the differences
as a sequence of MySQL commands suitable for piping into F<mysql>
which will transform the structure of the first database to be
identical to that of the second (I<c.f.> F<diff> and F<patch>).

Database structures can be compared whether they are files containing
table definitions or existing databases, local or remote.

B<N.B.> The program makes I<no> attempt to compare any of the data
which may be stored in the databases.  It is purely for comparing the
table definitions.  I have no plans to implement data comparison; it
is a complex problem and I have no need of such functionality anyway.
However there is another program
L<coldiff|http://rossbeyer.net/software/mysql_coldiff/>
which does this, and is based on an older program called
F<datadiff> which seems to have vanished off the 'net.

For PostgreSQL there are similar tools such as
L<pgdiff|http://pgdiff.sourceforge.net/> and
L<apgdiff|http://apgdiff.startnet.biz/>.

=head1 EXAMPLES

  # compare table definitions in two files
  mysql-schema-diff db1.mysql db2.mysql

  # compare table definitions in a file 'db1.mysql' with a database 'db2'
  mysql-schema-diff db1.mysql db2

  # interactively upgrade schema of database 'db1' to be like the
  # schema described in the file 'db2.mysql'
  mysql-schema-diff -A db1 db2.mysql

  # compare table definitions in two databases on a remote machine
  mysql-schema-diff --host=remote.host.com --user=myaccount db1 db2

  # compare table definitions in a local database 'foo' with a
  # database 'bar' on a remote machine, when a file foo already
  # exists in the current directory
  mysql-schema-diff --host2=remote.host.com --password=secret db:foo bar

=head1 OPTIONS

=for comment FIXME - add option docs here

More details to come; for now run C<mysql-schema-diff --help>.

=head1 INTERNALS

For both of the database structures being compared, the following
happens:

=over 4

=item

If the argument is a valid filename, the file is used to create a
temporary database which C<mysqldump -d> is run on to obtain the table
definitions in canonicalised form.  The temporary database is then
dropped.  (The temporary database is named
C<test_mysqldiff_temp_something> because default MySQL permissions
allow anyone to create databases beginning with the prefix C<test_>.)

=item

If the argument is a database, C<mysqldump -d> is run directly on it.

=item

Where authentication is required, the hostname, username, and password
given by the corresponding options are used (type C<mysql-schema-diff --help>
for more information).

=item

Each set of table definitions is now parsed into tables, and fields
and index keys within those tables; these are compared, and the
differences outputted in the form of MySQL statements.

=back

=cut

use strict;

use 5.006; # due to 'our' and qr//

use FindBin qw($RealBin $Script);
use lib $RealBin;
use Getopt::Long qw(:config no_ignore_case);
use IO::File;

use MySQL::Diff;

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", "socket|s=s", "user|u=s", "password|p:s",
           "host1=s",  "port1=s",  "socket1=s",  "user1=s",  "password1:s",
           "host2=s",  "port2=s",  "socket2=s",  "user2=s",  "password2:s",
           "tolerant|i"
          ) or usage();

usage() if (@ARGV != 2 or $opts{help});

$opts{debug} ||= 0;

my $md = MySQL::Diff->new(%opts);

for my $num (1, 2) {
  my $new_db = $md->register_db($ARGV[$num-1], $num);
  usage($new_db) unless ref $new_db;
}

$| = 1;
my $diffs = $md->diff();
print $diffs;
apply($md, $diffs) if $opts{apply} || $opts{'batch-apply'};

exit 0;

##############################################################################

sub usage {
  print STDERR @_, "\n" if @_;
  die <<EOF;
Usage: $Script [ options ] <database1> <database2>

Options:
  -?,  --help             show this help
  -A,  --apply            interactively patch database1 to match database2
  -B,  --batch-apply      non-interactively patch database1 to match database2
  -d,  --debug[=N]        enable debugging [level N, default 1]
  -o,  --only-both        only output changes for tables in both databases
  -k,  --keep-old-tables  don't output DROP TABLE commands
  -n,  --no-old-defs      suppress comments describing old definitions
  -t,  --table-re=REGEXP  restrict comparisons to tables matching REGEXP
  -i,  --tolerant         ignore DEFAULT, AUTO_INCREMENT, COLLATE, and formatting changes

  -h,  --host=...         connect to host
  -P,  --port=...         use this port for connection
  -u,  --user=...         user for login if not current user
  -p,  --password[=...]   password to use when connecting to server
  -s,  --socket=...       socket to use when connecting to server

for <databaseN> only, where N == 1 or 2,
       --hostN=...        connect to host
       --portN=...        use this port for connection
       --userN=...        user for login if not current user
       --passwordN[=...]  password to use when connecting to server
       --socketN=...      socket to use when connecting to server

Databases can be either files or database names.
If there is an ambiguity, the file will be preferred;
to prevent this prefix the database argument with `db:'.
EOF
}

sub apply {
  my ($md, $diffs) = @_;

  if (! $diffs) {
    print "No differences to apply.\n";
    return;
  }

  my $db0 = $md->db1->name;
  if ($md->db1->source_type ne 'db') {
    die "$db0 is not a database; cannot apply changes.\n";
  }

  unless ($opts{'batch-apply'}) {
    print "\nApply above changes to $db0 [y/N] ? ";
    print "\n(CAUTION! Changes contain DROP TABLE commands.) "
      if $diffs =~ /\bDROP TABLE\b/i;
    my $reply = <STDIN>;
    return unless $reply =~ /^y(es)?$/i;
  }

  print "Applying changes ... ";
  my $args = $md->db1->auth_args;
  my $pipe = "mysql$args $db0";
  my $fh = IO::File->new("|$pipe") or die "Couldn't open pipe to '$pipe': $!\n";
  print $fh $diffs;
  $fh->close or die "Couldn't close pipe: $!\n";
  print "done.\n";
}

=head1 BUGS, DEVELOPMENT, CONTRIBUTING

See L<http://software.adamspiers.org/wiki/mysqldiff>.

=head1 COPYRIGHT AND LICENSE

Copyright (c) 2000-2011 Adam Spiers. All rights reserved. This
program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=head1 SEE ALSO

L<MySQL::Diff>, L<MySQL::Diff::Database>, L<MySQL::Diff::Table>, L<MySQL::Diff::Utils>,
L<mysql>, L<mysqldump>, L<mysqlshow>

=head1 AUTHOR

Adam Spiers <mysqldiff@adamspiers.org>

=cut