libmysql-diff-perl 0.50-1 / usr / bin / mysql-schema-diff

#!/usr/bin/perl -w

=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

=over 4

=item C<-?,  --help>

show usage

=item C<-A,  --apply>

interactively patch database1 to match database2

=item C<-B,  --batch-apply>

non-interactively patch database1 to match database2

=item C<-d,  --debug[=N]>

enable debugging [level N, default 1]

=item C<-l,  --list-tables>

output the list off all used tables

=item C<-o,  --only-both>

only output changes for tables in both databases

=item C<-k,  --keep-old-tables>

don't output DROP TABLE commands

=item C<-n,  --no-old-defs>

suppress comments describing old definitions

=item C<-t,  --table-re=REGEXP>

restrict comparisons to tables matching REGEXP

=item C<-i,  --tolerant>

ignore DEFAULT, AUTO_INCREMENT, COLLATE, and formatting changes

=item C<-S,  --single-transaction>

perform DB dump in transaction
For more info see: http://dev.mysql.com/doc/refman/en/mysqldump.html#option_mysqldump_single-transaction

=item C<-h,  --host=[hostname]>

connect to host

=item C<-P,  --port=[port]>

use this port for connection

=item C<-u,  --user=[user]>

user for login if not current user

=item C<-p,  --password[=password]>

password to use when connecting to server

=item C<-s,  --socket=...>

socket to use when connecting to server

=back

=head2 For <databaseN> only, where N == 1 or 2

=over 4

=item C<--hostN=[hostN]>

connect to host

=item C<--portN=[portN]>

use this port for connection

=item C<--userN=[userN]>

user for login if not current user

=item C<--passwordN[=passwordN]>

password to use when connecting to server

=item C<--socketN=[socketN]>

socket to use when connecting to server

=back

=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 String::ShellQuote qw(shell_quote);
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", "single-transaction|S", "list-tables|l"
          ) 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]
  -l,  --list-tables         output the list off all used tables
  -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
  -S,  --single-transaction  perform DB dump in transaction

  -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_ref = $md->db1->auth_args;
  unshift @$args_ref, q{mysql};
  push @$args_ref, $db0;
  my $pipe = shell_quote @$args_ref;
  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://adamspiers.org/computing/mysqldiff/>.

=head1 COPYRIGHT AND LICENSE

Copyright (c) 2000-2016 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