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

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

# -------------------------------------------------------------------
# $Id: sqlt-diff,v 1.4 2004-02-06 17:48:16 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.4 $ =~ /(\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 "Checking '$s1_name' table '$t1_name'\n" if $debug;
    unless ( $t2 ) {
        push @new_tables, $t1;
        next;
    }

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

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

        unless ( $t2_field ) {
            push @diffs, 
                "ALTER TABLE $t1_name ADD $t1_name $t1_type($t1_size);";
            next;
        }

        my $t2_type = $t2_field->data_type;
        my $t2_size = $t2_field->size;

        if ( $t1_type ne $t2_type ||
           ( defined $t1_size && ( $t1_size ne $t2_size ) )
        ) {
            push @diffs, 
                "ALTER TABLE $t1_name CHANGE $t1_name $t1_type($t1_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':", @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	# -------------------------------------------------------------------
daf4f623	5	# $Id: sqlt-diff,v 1.4 2004-02-06 17:48:16 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 );
daf4f623	100	$VERSION = sprintf "%d.%02d", q$Revision: 1.4 $ =~ /(\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
	173	warn "Checking '$s1_name' table '$t1_name'\n" if $debug;
	174	unless ( $t2 ) {
	175	push @new_tables, $t1;
	176	next;
	177	}
e12ca55a	178
942485ea	179	my $t2_name = $t2->name;
	180	for my $t1_field ( $t1->get_fields ) {
	181	my $t1_type = $t1_field->data_type;
	182	my $t1_size = $t1_field->size;
	183	my $t1_name = $t1_field->name;
	184	my $t2_field = $t2->get_field( $t1_name );
	185	my $f1_full_name = "$s1_name.$t1_name.$t1_name";
	186	warn "Checking '$f1_full_name'\n" if $debug;
e12ca55a	187
942485ea	188	my $f2_full_name = "$s2_name.$t2_name.$t1_name";
e12ca55a	189
942485ea	190	unless ( $t2_field ) {
	191	push @diffs,
	192	"ALTER TABLE $t1_name ADD $t1_name $t1_type($t1_size);";
e12ca55a	193	next;
	194	}
	195
942485ea	196	my $t2_type = $t2_field->data_type;
942485ea	197	my $t2_size = $t2_field->size;
e12ca55a	198
942485ea	199	if ( $t1_type ne $t2_type \|\|
	200	( defined $t1_size && ( $t1_size ne $t2_size ) )
	201	) {
	202	push @diffs,
	203	"ALTER TABLE $t1_name CHANGE $t1_name $t1_type($t1_size);";
	204	}
	205	}
e12ca55a	206
942485ea	207	my ( %t1_indices, %t2_indices );
	208	for my $rec ( [ $t1, \%t1_indices ], [ $t2, \%t2_indices ] ) {
	209	my ( $table, $indices ) = @$rec;
	210	for my $index ( $table->get_indices ) {
	211	my $name = $index->name;
	212	my $type = $index->type;
	213	my $fields = join( ',', sort $index->fields );
	214
	215	$indices->{'type'}{ $type }{ $fields } = $name;
	216
	217	if ( $name ) {
	218	$indices->{'name'}{ $name } = {
	219	type => $type,
	220	fields => $fields,
	221	};
e12ca55a	222	}
942485ea	223	}
942485ea	224	}
e12ca55a	225
942485ea	226	for my $type ( keys %{ $t2_indices{'type'} } ) {
	227	while ( my ($fields, $iname) = each %{$t2_indices{'type'}{ $type } } ) {
	228	if ( $iname ) {
	229	if ( my $i1 = $t1_indices{'name'}{ $iname } ) {
	230	my $i1_type = $i1->{'type'};
	231	my $i1_fields = $i1->{'fields'};
	232	if ( $i1_type eq $type && $i1_fields eq $fields ) {
	233	next;
	234	}
	235	}
e12ca55a	236	}
942485ea	237	elsif ( my $i1 = $t1_indices{'type'}{ $type }{ $fields } ) {
	238	next;
	239	}
	240
	241	push @diffs, "DROP INDEX $iname on $t1_name;";
e12ca55a	242	}
e12ca55a	243	}
942485ea	244
	245	for my $type ( keys %{ $t1_indices{'type'} } ) {
	246	while ( my ($fields, $iname) = each %{$t1_indices{'type'}{ $type } } ) {
	247	if ( $iname ) {
	248	if ( my $i2 = $t2_indices{'name'}{ $iname } ) {
	249	my $i2_type = $i2->{'type'};
	250	my $i2_fields = $i2->{'fields'};
	251	if ( $i2_type eq $type && $i2_fields eq $fields ) {
	252	next;
	253	}
	254	}
	255	}
	256	elsif ( my $i2 = $t2_indices{'type'}{ $type }{ $fields } ) {
	257	next;
	258	}
	259
	260	push @diffs, sprintf(
	261	"CREATE %sINDEX%s ON %s (%s);",
	262	$type eq NORMAL ? '' : "$type ",
	263	$iname ? " $iname" : '',
	264	$t1_name,
	265	$fields,
	266	);
	267	}
	268	}
	269	}
	270
	271	for my $t2 ( $target_schema->get_tables ) {
	272	my $t2_name = $t2->name;
	273	my $t1 = $source_schema->get_table( $t2_name );
	274
	275	unless ( $t1 ) {
	276	push @diffs, "DROP TABLE $t2_name;";
	277	next;
	278	}
	279
	280	for my $t2_field ( $t2->get_fields ) {
	281	my $f2_name = $t2_field->name;
	282	my $t1_field = $t1->get_field( $f2_name );
	283	unless ( $t1_field ) {
	284	push @diffs, "ALTER TABLE $t2_name DROP $f2_name;";
	285	}
	286	}
	287	}
	288
	289	if ( @new_tables ) {
	290	my $dummy_tr = SQL::Translator->new;
	291	$dummy_tr->schema->add_table( $_ ) for @new_tables;
	292	my $producer = $dummy_tr->producer( $target_db );
	293	unshift @diffs, $producer->( $dummy_tr );
e12ca55a	294	}
	295
	296	if ( @diffs ) {
942485ea	297	print join( "\n",
	298	"-- Convert schema '$s2_name' to '$s1_name':", @diffs, ''
	299	);
e12ca55a	300	}
	301	else {
	302	print "There were no differences.\n";
	303	}
	304
	305	# -------------------------------------------------------------------
	306	# Bring out number weight & measure in a year of dearth.
	307	# William Blake
	308	# -------------------------------------------------------------------
	309
	310	=pod
	311
	312	=head1 AUTHOR
	313
	314	Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
	315
	316	=head1 SEE ALSO
	317
	318	SQL::Translator, L<http://sqlfairy.sourceforge.net>.
	319
	320	=cut