X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=bin%2Fsqlt-diff;h=d3548018cc583faa7cc0f154e8f91eb8570f33a9;hb=abf315bb9c2c78e40da9af6519e5daae76d60f08;hp=4351f1ed5d5486f606d7f92991e2a77848e19996;hpb=e12ca55a563b9e53e18e97784b9ac2e72f779c66;p=dbsrgits%2FSQL-Translator.git diff --git a/bin/sqlt-diff b/bin/sqlt-diff index 4351f1e..d354801 100755 --- a/bin/sqlt-diff +++ b/bin/sqlt-diff @@ -2,9 +2,9 @@ # vim: set ft=perl: # ------------------------------------------------------------------- -# $Id: sqlt-diff,v 1.1 2003-10-17 16:42:14 kycl4rk Exp $ +# $Id: sqlt-diff,v 1.6 2004-02-27 18:26:38 kycl4rk Exp $ # ------------------------------------------------------------------- -# Copyright (C) 2002 The SQLFairy Authors +# 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 @@ -37,7 +37,7 @@ For a list of all valid parsers: To diff two schemas: - sqlt-diff [options] file1=parser file2=parser + sqlt-diff [options] file_name1=parser file_name2=parser Options: @@ -45,8 +45,46 @@ Options: =head1 DESCRIPTION -This script is part of the SQL Fairy project. It will find the -differences between two schemas. +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 + [DROP ] + [CHANGE ()] ; + +=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 ON ; + +An index of a different type or on different fields will be reported as a +new index as such: + + CREATE [] INDEX [] ON + ( [,] ) ; + +=back + +"ALTER/DROP TABLE" and "CREATE INDEX" statements B generated by +the Producer, unfortunately, and may require massaging before being passed to +your target database. =cut @@ -56,9 +94,10 @@ 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.1 $ =~ /(\d+)\.(\d+)/; +$VERSION = sprintf "%d.%02d", q$Revision: 1.6 $ =~ /(\d+)\.(\d+)/; my ( @input, $list, $help, $debug ); for my $arg ( @ARGV ) { @@ -80,6 +119,7 @@ for my $arg ( @ARGV ) { } 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; @@ -93,8 +133,8 @@ if ( $list ) { pod2usage( msg => 'Too many file args' ) if @input > 2; -my ( $schema1, $schema2 ); -my $i = 1; +my ( $source_schema, $source_db, $target_schema, $target_db ); +my $i = 2; for my $in ( @input ) { my $file = $in->{'file'}; my $parser = $in->{'parser'}; @@ -113,70 +153,158 @@ for my $in ( @input ) { } if ( $i == 1 ) { - $schema1 = $schema; + $source_schema = $schema; + $source_db = $parser; } else { - $schema2 = $schema; + $target_schema = $schema; + $target_db = $parser; } - $i++; + $i--; } -#print "Schemas =\n", Dumper( \@schemas ), "\n" if $debug; +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 @matrix = ( [ $schema1, $schema2 ], [ $schema2, $schema1 ] ); + 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 @diffs; -for my $rec ( @matrix ) { - my $s1 = $rec->[0]; - my $s2 = $rec->[1]; - my $s1_name = $s1->name; - my $s2_name = $s2->name; - print "Schema1 = '$s1_name', schema2 = '$s2_name'\n" if $debug; - for my $t1 ( $s1->get_tables ) { - my $t1_name = $t1->name; - my $t2 = $s2->get_table( $t1_name ); + my $f2_type = $t2_field->data_type; + my $f2_size = $t2_field->size; - print "Checking '$s1_name' table '$t1_name'\n" if $debug; - unless ( $t2 ) { - push @diffs, "Schema '$s2_name' is missing table '$t1_name'"; - next; + 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 $t2_name = $t2->name; - for my $t1_field ( $t1->get_fields ) { - my $fname = $t1_field->name; - my $t2_field = $t2->get_field( $fname ); - my $f1_full_name = "$s1_name.$t1_name.$fname"; - print "Checking '$f1_full_name'\n" if $debug; - - unless ( $t2_field ) { - push @diffs, - "Table '$s2_name.$t2_name' is missing field '$fname'"; - next; + 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, + }; } + } + } - my $f2_full_name = "$s2_name.$t2_name.$fname"; - my $t1_type = $t1_field->data_type; - my $t1_size = $t1_field->size; - my $t2_type = $t2_field->data_type; - my $t2_size = $t2_field->size; - - if ( $t1_type ne $t2_type ) { - push @diffs, "'$f1_full_name' type = '$t1_type' and ". - "'$f2_full_name' type = '$t2_type'"; + 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; } - if ( defined $t1_size && ( $t1_size ne $t2_size ) ) { - push @diffs, "'$f1_full_name' size = '$t1_size' and ". - "'$f2_full_name' size = '$t2_size'"; + 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 "Diffs\n-----\n"; - print join( "\n", @diffs, '' ); + print join( "\n", + "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, '' + ); } else { print "There were no differences.\n";