[dbsrgits/SQL-Translator.git] / bin / sqlt-diff

#!/usr/bin/perl -w
# vim: set ft=perl:

# -------------------------------------------------------------------
# $Id: sqlt-diff,v 1.6 2004-02-27 18:26:38 kycl4rk Exp $
# -------------------------------------------------------------------
# Copyright (C) 2002-4 The SQLFairy Authors
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; version 2.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
# 02111-1307  USA
# -------------------------------------------------------------------

=head1 NAME

sqlt-diff - find the differences b/w two schemas

=head1 SYNOPSIS

For help:

  sqlt-diff -h|--help

For a list of all valid parsers:

  sqlt -l|--list

To diff two schemas:

  sqlt-diff [options] file_name1=parser file_name2=parser

Options:

  -d|--debug   Show debugging info

=head1 DESCRIPTION

sqlt-diff is a utility for creating a file of SQL commands necessary to
transform the first schema provided to the second.  While not yet 
exhaustive in its ability to mutate the entire schema, it will report the 
following

=over

=item * New tables

Using the Producer class of the target (second) schema, any tables missing
in the first schema will be generated in their entirety (fields, constraints,
indices).

=item * Missing/altered fields

Any fields missing or altered between the two schemas will be reported 
as:

  ALTER TABLE <table_name> 
    [DROP <field_name>] 
    [CHANGE <field_name> <datatype> (<size>)] ;

=item * Missing/altered indices

Any indices missing or of a different type or on different fields will be
indicated.  Indices that should be dropped will be reported as such:
 
  DROP INDEX <index_name> ON <table_name> ;

An index of a different type or on different fields will be reported as a 
new index as such:

  CREATE [<index_type>] INDEX [<index_name>] ON <table_name> 
    ( <field_name>[,<field_name>] ) ;

=back

"ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
the Producer, unfortunately, and may require massaging before being passed to
your target database.

=cut

# -------------------------------------------------------------------

use strict;
use Pod::Usage;
use Data::Dumper;
use SQL::Translator;
use SQL::Translator::Schema::Constants;

use vars qw( $VERSION );
$VERSION = sprintf "%d.%02d", q$Revision: 1.6 $ =~ /(\d+)\.(\d+)/;

my ( @input, $list, $help, $debug );
for my $arg ( @ARGV ) {
    if ( $arg =~ m/^-?-l(ist)?$/ ) {
        $list = 1;
    }
    elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
        $help = 1;
    }
    elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
        $debug = 1; 
    }
    elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
        push @input, { file => $1, parser => $2 };
    }
    else {
        pod2usage( msg => "Unknown argument '$arg'" );
    }
}

pod2usage(1) if $help;
pod2usage('Please specify only two schemas to diff') if scalar @input > 2;

my $tr            = SQL::Translator->new;
my @parsers       = $tr->list_parsers;
my %valid_parsers = map { $_, 1 } @parsers;

if ( $list ) {
    print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
    print "\n";
    exit(0);
}

pod2usage( msg => 'Too many file args' ) if @input > 2;

my ( $source_schema, $source_db, $target_schema, $target_db );
my $i = 2;
for my $in ( @input ) {
    my $file   = $in->{'file'};
    my $parser = $in->{'parser'};

    die "Unable to read file '$file'\n" unless -r $file;
    die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };

    my $t = SQL::Translator->new;
    $t->debug( $debug );
    $t->parser( $parser )            or die $tr->error;
    $t->producer( 'YAML' )           or die $tr->error;
    my $out = $t->translate( $file ) or die $tr->error;
    my $schema = $t->schema;
    unless ( $schema->name ) {
        $schema->name( $file );
    }

    if ( $i == 1 ) {
        $source_schema = $schema;
        $source_db     = $parser;
    }
    else {
        $target_schema = $schema;
        $target_db     = $parser;
    }
    $i--;
}

my $s1_name  = $source_schema->name;
my $s2_name  = $target_schema->name;
my ( @new_tables, @diffs );
for my $t1 ( $source_schema->get_tables ) {
    my $t1_name = $t1->name;
    my $t2      = $target_schema->get_table( $t1_name );

    warn "TABLE '$s1_name.$t1_name'\n" if $debug;
    unless ( $t2 ) {
        warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n" 
            if $debug;
        push @new_tables, $t1;
        next;
    }

    my $t2_name = $t2->name;
    for my $t1_field ( $t1->get_fields ) {
        my $f1_type      = $t1_field->data_type;
        my $f1_size      = $t1_field->size;
        my $f1_name      = $t1_field->name;
        my $t2_field     = $t2->get_field( $f1_name );
        my $f1_full_name = "$s1_name.$t1_name.$t1_name";
        warn "FIELD '$f1_full_name'\n" if $debug;

        my $f2_full_name = "$s2_name.$t2_name.$f1_name";

        unless ( $t2_field ) {
            warn "Couldn't find field '$f2_full_name' in '$t2_name'\n" 
                if $debug;
            push @diffs, sprintf( "ALTER TABLE %s ADD %s %s%s;",
                $t1_name, $f1_name, $f1_type,
                $f1_size ? "($f1_size)" : ''
            );
            next;
        }

        my $f2_type = $t2_field->data_type;
        my $f2_size = $t2_field->size;

        if ( lc $f1_type ne lc $f2_type ||
           ( defined $f1_size && ( $f1_size ne $f2_size ) )
        ) {
            push @diffs, sprintf( "ALTER TABLE %s CHANGE %s %s%s;",
                $t1_name, $f1_name, $f1_type,
                $f1_size ? "($f1_size)" : ''
            );
        }
    }

    my ( %t1_indices, %t2_indices );
    for my $rec ( [ $t1, \%t1_indices ], [ $t2, \%t2_indices ] ) {
        my ( $table, $indices ) = @$rec;
        for my $index ( $table->get_indices ) {
            my $name   = $index->name;
            my $type   = $index->type;
            my $fields = join( ',', sort $index->fields );

            $indices->{'type'}{ $type }{ $fields } = $name;

            if ( $name ) {
                $indices->{'name'}{ $name } = { 
                    type   => $type, 
                    fields => $fields,
                };
            }
        }
    }

    for my $type ( keys %{ $t2_indices{'type'} } ) {
        while ( my ($fields, $iname) = each %{$t2_indices{'type'}{ $type } } ) {
            if ( $iname ) {
                if ( my $i1 = $t1_indices{'name'}{ $iname } ) {
                    my $i1_type   = $i1->{'type'};
                    my $i1_fields = $i1->{'fields'};
                    if ( $i1_type eq $type && $i1_fields eq $fields ) {
                        next;
                    }
                }
            }
            elsif ( my $i1 = $t1_indices{'type'}{ $type }{ $fields } ) {
                next; 
            }

            push @diffs, "DROP INDEX $iname on $t1_name;";
        }
    }

    for my $type ( keys %{ $t1_indices{'type'} } ) {
        while ( my ($fields, $iname) = each %{$t1_indices{'type'}{ $type } } ) {
            if ( $iname ) {
                if ( my $i2 = $t2_indices{'name'}{ $iname } ) {
                    my $i2_type   = $i2->{'type'};
                    my $i2_fields = $i2->{'fields'};
                    if ( $i2_type eq $type && $i2_fields eq $fields ) {
                        next;
                    }
                }
            }
            elsif ( my $i2 = $t2_indices{'type'}{ $type }{ $fields } ) {
                next; 
            }

            push @diffs, sprintf(
                "CREATE %sINDEX%s ON %s (%s);",
                $type eq NORMAL ? '' : "$type ",
                $iname ? " $iname" : '',
                $t1_name,
                $fields,
            );
        }
    }
}

for my $t2 ( $target_schema->get_tables ) {
    my $t2_name = $t2->name;
    my $t1      = $source_schema->get_table( $t2_name );

    unless ( $t1 ) {
        push @diffs, "DROP TABLE $t2_name;";
        next;
    }

    for my $t2_field ( $t2->get_fields ) {
        my $f2_name      = $t2_field->name;
        my $t1_field     = $t1->get_field( $f2_name );
        unless ( $t1_field ) {
            push @diffs, "ALTER TABLE $t2_name DROP $f2_name;";
        }
    }
}

if ( @new_tables ) {
    my $dummy_tr = SQL::Translator->new;
    $dummy_tr->schema->add_table( $_ ) for @new_tables;
    my $producer = $dummy_tr->producer( $target_db );
    unshift @diffs, $producer->( $dummy_tr );
}

if ( @diffs ) {
    print join( "\n", 
        "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, '' 
    );
}
else {
    print "There were no differences.\n";
}

# -------------------------------------------------------------------
# Bring out number weight & measure in a year of dearth.
# William Blake
# -------------------------------------------------------------------

=pod

=head1 AUTHOR

Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.

=head1 SEE ALSO

SQL::Translator, L<http://sqlfairy.sourceforge.net>.

=cut
Commit	Line	Data
e12ca55a	1	#!/usr/bin/perl -w
	2	# vim: set ft=perl:
	3
	4	# -------------------------------------------------------------------
afdf6a1c	5	# $Id: sqlt-diff,v 1.6 2004-02-27 18:26:38 kycl4rk Exp $
e12ca55a	6	# -------------------------------------------------------------------
daf4f623	7	# Copyright (C) 2002-4 The SQLFairy Authors
e12ca55a	8	#
	9	# This program is free software; you can redistribute it and/or
	10	# modify it under the terms of the GNU General Public License as
	11	# published by the Free Software Foundation; version 2.
	12	#
	13	# This program is distributed in the hope that it will be useful, but
	14	# WITHOUT ANY WARRANTY; without even the implied warranty of
	15	# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
	16	# General Public License for more details.
	17	#
	18	# You should have received a copy of the GNU General Public License
	19	# along with this program; if not, write to the Free Software
	20	# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
	21	# 02111-1307 USA
	22	# -------------------------------------------------------------------
	23
	24	=head1 NAME
	25
	26	sqlt-diff - find the differences b/w two schemas
	27
	28	=head1 SYNOPSIS
	29
	30	For help:
	31
	32	sqlt-diff -h\|--help
	33
	34	For a list of all valid parsers:
	35
	36	sqlt -l\|--list
	37
	38	To diff two schemas:
	39
21f5bf40	40	sqlt-diff [options] file_name1=parser file_name2=parser
e12ca55a	41
	42	Options:
	43
	44	-d\|--debug Show debugging info
	45
	46	=head1 DESCRIPTION
	47
942485ea	48	sqlt-diff is a utility for creating a file of SQL commands necessary to
	49	transform the first schema provided to the second. While not yet
	50	exhaustive in its ability to mutate the entire schema, it will report the
	51	following
	52
	53	=over
	54
	55	=item * New tables
	56
	57	Using the Producer class of the target (second) schema, any tables missing
	58	in the first schema will be generated in their entirety (fields, constraints,
	59	indices).
	60
	61	=item * Missing/altered fields
	62
	63	Any fields missing or altered between the two schemas will be reported
	64	as:
	65
	66	ALTER TABLE <table_name>
	67	[DROP <field_name>]
	68	[CHANGE <field_name> <datatype> (<size>)] ;
	69
	70	=item * Missing/altered indices
	71
	72	Any indices missing or of a different type or on different fields will be
	73	indicated. Indices that should be dropped will be reported as such:
	74
	75	DROP INDEX <index_name> ON <table_name> ;
	76
	77	An index of a different type or on different fields will be reported as a
	78	new index as such:
	79
	80	CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
	81	( <field_name>[,<field_name>] ) ;
	82
	83	=back
	84
	85	"ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
	86	the Producer, unfortunately, and may require massaging before being passed to
	87	your target database.
e12ca55a	88
	89	=cut
	90
	91	# -------------------------------------------------------------------
	92
	93	use strict;
	94	use Pod::Usage;
	95	use Data::Dumper;
	96	use SQL::Translator;
942485ea	97	use SQL::Translator::Schema::Constants;
e12ca55a	98
e12ca55a	99	use vars qw( $VERSION );
afdf6a1c	100	$VERSION = sprintf "%d.%02d", q$Revision: 1.6 $ =~ /(\d+)\.(\d+)/;
e12ca55a	101
	102	my ( @input, $list, $help, $debug );
	103	for my $arg ( @ARGV ) {
	104	if ( $arg =~ m/^-?-l(ist)?$/ ) {
	105	$list = 1;
	106	}
	107	elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
	108	$help = 1;
	109	}
	110	elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
	111	$debug = 1;
	112	}
	113	elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
	114	push @input, { file => $1, parser => $2 };
	115	}
	116	else {
	117	pod2usage( msg => "Unknown argument '$arg'" );
	118	}
	119	}
	120
	121	pod2usage(1) if $help;
942485ea	122	pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
e12ca55a	123
	124	my $tr = SQL::Translator->new;
	125	my @parsers = $tr->list_parsers;
	126	my %valid_parsers = map { $_, 1 } @parsers;
	127
	128	if ( $list ) {
	129	print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
	130	print "\n";
	131	exit(0);
	132	}
	133
	134	pod2usage( msg => 'Too many file args' ) if @input > 2;
	135
942485ea	136	my ( $source_schema, $source_db, $target_schema, $target_db );
942485ea	137	my $i = 2;
e12ca55a	138	for my $in ( @input ) {
	139	my $file = $in->{'file'};
	140	my $parser = $in->{'parser'};
	141
	142	die "Unable to read file '$file'\n" unless -r $file;
	143	die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
	144
	145	my $t = SQL::Translator->new;
	146	$t->debug( $debug );
	147	$t->parser( $parser ) or die $tr->error;
	148	$t->producer( 'YAML' ) or die $tr->error;
	149	my $out = $t->translate( $file ) or die $tr->error;
	150	my $schema = $t->schema;
	151	unless ( $schema->name ) {
	152	$schema->name( $file );
	153	}
	154
	155	if ( $i == 1 ) {
942485ea	156	$source_schema = $schema;
942485ea	157	$source_db = $parser;
e12ca55a	158	}
e12ca55a	159	else {
942485ea	160	$target_schema = $schema;
942485ea	161	$target_db = $parser;
e12ca55a	162	}
942485ea	163	$i--;
e12ca55a	164	}
e12ca55a	165
942485ea	166	my $s1_name = $source_schema->name;
	167	my $s2_name = $target_schema->name;
	168	my ( @new_tables, @diffs );
	169	for my $t1 ( $source_schema->get_tables ) {
	170	my $t1_name = $t1->name;
	171	my $t2 = $target_schema->get_table( $t1_name );
	172
8b3b2f0c	173	warn "TABLE '$s1_name.$t1_name'\n" if $debug;
942485ea	174	unless ( $t2 ) {
8b3b2f0c	175	warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
8b3b2f0c	176	if $debug;
942485ea	177	push @new_tables, $t1;
	178	next;
	179	}
e12ca55a	180
942485ea	181	my $t2_name = $t2->name;
942485ea	182	for my $t1_field ( $t1->get_fields ) {
afdf6a1c	183	my $f1_type = $t1_field->data_type;
	184	my $f1_size = $t1_field->size;
	185	my $f1_name = $t1_field->name;
	186	my $t2_field = $t2->get_field( $f1_name );
942485ea	187	my $f1_full_name = "$s1_name.$t1_name.$t1_name";
8b3b2f0c	188	warn "FIELD '$f1_full_name'\n" if $debug;
e12ca55a	189
afdf6a1c	190	my $f2_full_name = "$s2_name.$t2_name.$f1_name";
e12ca55a	191
942485ea	192	unless ( $t2_field ) {
8b3b2f0c	193	warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
8b3b2f0c	194	if $debug;
afdf6a1c	195	push @diffs, sprintf( "ALTER TABLE %s ADD %s %s%s;",
	196	$t1_name, $f1_name, $f1_type,
	197	$f1_size ? "($f1_size)" : ''
	198	);
e12ca55a	199	next;
	200	}
	201
afdf6a1c	202	my $f2_type = $t2_field->data_type;
afdf6a1c	203	my $f2_size = $t2_field->size;
e12ca55a	204
afdf6a1c	205	if ( lc $f1_type ne lc $f2_type \|\|
afdf6a1c	206	( defined $f1_size && ( $f1_size ne $f2_size ) )
942485ea	207	) {
afdf6a1c	208	push @diffs, sprintf( "ALTER TABLE %s CHANGE %s %s%s;",
	209	$t1_name, $f1_name, $f1_type,
	210	$f1_size ? "($f1_size)" : ''
	211	);
942485ea	212	}
942485ea	213	}
e12ca55a	214
942485ea	215	my ( %t1_indices, %t2_indices );
	216	for my $rec ( [ $t1, \%t1_indices ], [ $t2, \%t2_indices ] ) {
	217	my ( $table, $indices ) = @$rec;
	218	for my $index ( $table->get_indices ) {
	219	my $name = $index->name;
	220	my $type = $index->type;
	221	my $fields = join( ',', sort $index->fields );
	222
	223	$indices->{'type'}{ $type }{ $fields } = $name;
	224
	225	if ( $name ) {
	226	$indices->{'name'}{ $name } = {
	227	type => $type,
	228	fields => $fields,
	229	};
e12ca55a	230	}
942485ea	231	}
942485ea	232	}
e12ca55a	233
942485ea	234	for my $type ( keys %{ $t2_indices{'type'} } ) {
	235	while ( my ($fields, $iname) = each %{$t2_indices{'type'}{ $type } } ) {
	236	if ( $iname ) {
	237	if ( my $i1 = $t1_indices{'name'}{ $iname } ) {
	238	my $i1_type = $i1->{'type'};
	239	my $i1_fields = $i1->{'fields'};
	240	if ( $i1_type eq $type && $i1_fields eq $fields ) {
	241	next;
	242	}
	243	}
e12ca55a	244	}
942485ea	245	elsif ( my $i1 = $t1_indices{'type'}{ $type }{ $fields } ) {
	246	next;
	247	}
	248
	249	push @diffs, "DROP INDEX $iname on $t1_name;";
e12ca55a	250	}
e12ca55a	251	}
942485ea	252
	253	for my $type ( keys %{ $t1_indices{'type'} } ) {
	254	while ( my ($fields, $iname) = each %{$t1_indices{'type'}{ $type } } ) {
	255	if ( $iname ) {
	256	if ( my $i2 = $t2_indices{'name'}{ $iname } ) {
	257	my $i2_type = $i2->{'type'};
	258	my $i2_fields = $i2->{'fields'};
	259	if ( $i2_type eq $type && $i2_fields eq $fields ) {
	260	next;
	261	}
	262	}
	263	}
	264	elsif ( my $i2 = $t2_indices{'type'}{ $type }{ $fields } ) {
	265	next;
	266	}
	267
	268	push @diffs, sprintf(
	269	"CREATE %sINDEX%s ON %s (%s);",
	270	$type eq NORMAL ? '' : "$type ",
	271	$iname ? " $iname" : '',
	272	$t1_name,
	273	$fields,
	274	);
	275	}
	276	}
	277	}
	278
	279	for my $t2 ( $target_schema->get_tables ) {
	280	my $t2_name = $t2->name;
	281	my $t1 = $source_schema->get_table( $t2_name );
	282
	283	unless ( $t1 ) {
	284	push @diffs, "DROP TABLE $t2_name;";
	285	next;
	286	}
	287
	288	for my $t2_field ( $t2->get_fields ) {
	289	my $f2_name = $t2_field->name;
	290	my $t1_field = $t1->get_field( $f2_name );
	291	unless ( $t1_field ) {
	292	push @diffs, "ALTER TABLE $t2_name DROP $f2_name;";
	293	}
	294	}
	295	}
	296
	297	if ( @new_tables ) {
	298	my $dummy_tr = SQL::Translator->new;
	299	$dummy_tr->schema->add_table( $_ ) for @new_tables;
	300	my $producer = $dummy_tr->producer( $target_db );
	301	unshift @diffs, $producer->( $dummy_tr );
e12ca55a	302	}
	303
	304	if ( @diffs ) {
942485ea	305	print join( "\n",
8b3b2f0c	306	"-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, ''
942485ea	307	);
e12ca55a	308	}
	309	else {
	310	print "There were no differences.\n";
	311	}
	312
	313	# -------------------------------------------------------------------
	314	# Bring out number weight & measure in a year of dearth.
	315	# William Blake
	316	# -------------------------------------------------------------------
	317
	318	=pod
	319
	320	=head1 AUTHOR
	321
	322	Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
	323
	324	=head1 SEE ALSO
	325
	326	SQL::Translator, L<http://sqlfairy.sourceforge.net>.
	327
	328	=cut