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

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

# -------------------------------------------------------------------
# $Id: sqlt-diff,v 1.5 2004-02-11 21:31:45 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.5 $ =~ /(\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 $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 "FIELD '$f1_full_name'\n" if $debug;

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

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