4 # -------------------------------------------------------------------
5 # $Id: sqlt-diff,v 1.4 2004-02-06 17:48:16 kycl4rk Exp $
6 # -------------------------------------------------------------------
7 # Copyright (C) 2002-4 The SQLFairy Authors
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.
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.
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
22 # -------------------------------------------------------------------
26 sqlt-diff - find the differences b/w two schemas
34 For a list of all valid parsers:
40 sqlt-diff [options] file_name1=parser file_name2=parser
44 -d|--debug Show debugging info
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
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,
61 =item * Missing/altered fields
63 Any fields missing or altered between the two schemas will be reported
66 ALTER TABLE <table_name>
68 [CHANGE <field_name> <datatype> (<size>)] ;
70 =item * Missing/altered indices
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:
75 DROP INDEX <index_name> ON <table_name> ;
77 An index of a different type or on different fields will be reported as a
80 CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
81 ( <field_name>[,<field_name>] ) ;
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
91 # -------------------------------------------------------------------
97 use SQL::Translator::Schema::Constants;
99 use vars qw( $VERSION );
100 $VERSION = sprintf "%d.%02d", q$Revision: 1.4 $ =~ /(\d+)\.(\d+)/;
102 my ( @input, $list, $help, $debug );
103 for my $arg ( @ARGV ) {
104 if ( $arg =~ m/^-?-l(ist)?$/ ) {
107 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
110 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
113 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
114 push @input, { file => $1, parser => $2 };
117 pod2usage( msg => "Unknown argument '$arg'" );
121 pod2usage(1) if $help;
122 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
124 my $tr = SQL::Translator->new;
125 my @parsers = $tr->list_parsers;
126 my %valid_parsers = map { $_, 1 } @parsers;
129 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
134 pod2usage( msg => 'Too many file args' ) if @input > 2;
136 my ( $source_schema, $source_db, $target_schema, $target_db );
138 for my $in ( @input ) {
139 my $file = $in->{'file'};
140 my $parser = $in->{'parser'};
142 die "Unable to read file '$file'\n" unless -r $file;
143 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
145 my $t = SQL::Translator->new;
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 );
156 $source_schema = $schema;
157 $source_db = $parser;
160 $target_schema = $schema;
161 $target_db = $parser;
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 );
173 warn "Checking '$s1_name' table '$t1_name'\n" if $debug;
175 push @new_tables, $t1;
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;
188 my $f2_full_name = "$s2_name.$t2_name.$t1_name";
190 unless ( $t2_field ) {
192 "ALTER TABLE $t1_name ADD $t1_name $t1_type($t1_size);";
196 my $t2_type = $t2_field->data_type;
197 my $t2_size = $t2_field->size;
199 if ( $t1_type ne $t2_type ||
200 ( defined $t1_size && ( $t1_size ne $t2_size ) )
203 "ALTER TABLE $t1_name CHANGE $t1_name $t1_type($t1_size);";
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 );
215 $indices->{'type'}{ $type }{ $fields } = $name;
218 $indices->{'name'}{ $name } = {
226 for my $type ( keys %{ $t2_indices{'type'} } ) {
227 while ( my ($fields, $iname) = each %{$t2_indices{'type'}{ $type } } ) {
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 ) {
237 elsif ( my $i1 = $t1_indices{'type'}{ $type }{ $fields } ) {
241 push @diffs, "DROP INDEX $iname on $t1_name;";
245 for my $type ( keys %{ $t1_indices{'type'} } ) {
246 while ( my ($fields, $iname) = each %{$t1_indices{'type'}{ $type } } ) {
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 ) {
256 elsif ( my $i2 = $t2_indices{'type'}{ $type }{ $fields } ) {
260 push @diffs, sprintf(
261 "CREATE %sINDEX%s ON %s (%s);",
262 $type eq NORMAL ? '' : "$type ",
263 $iname ? " $iname" : '',
271 for my $t2 ( $target_schema->get_tables ) {
272 my $t2_name = $t2->name;
273 my $t1 = $source_schema->get_table( $t2_name );
276 push @diffs, "DROP TABLE $t2_name;";
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;";
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 );
298 "-- Convert schema '$s2_name' to '$s1_name':", @diffs, ''
302 print "There were no differences.\n";
305 # -------------------------------------------------------------------
306 # Bring out number weight & measure in a year of dearth.
308 # -------------------------------------------------------------------
314 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
318 SQL::Translator, L<http://sqlfairy.sourceforge.net>.