4 # -------------------------------------------------------------------
5 # $Id: sqlt-diff,v 1.5 2004-02-11 21:31:45 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.5 $ =~ /(\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 "TABLE '$s1_name.$t1_name'\n" if $debug;
175 warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
177 push @new_tables, $t1;
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";
188 warn "FIELD '$f1_full_name'\n" if $debug;
190 my $f2_full_name = "$s2_name.$t2_name.$t1_name";
192 unless ( $t2_field ) {
193 warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
196 "ALTER TABLE $t1_name ADD $t1_name $t1_type($t1_size);";
200 my $t2_type = $t2_field->data_type;
201 my $t2_size = $t2_field->size;
203 if ( lc $t1_type ne lc $t2_type ||
204 ( defined $t1_size && ( $t1_size ne $t2_size ) )
207 "ALTER TABLE $t1_name CHANGE $t1_name $t1_type($t1_size);";
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 );
219 $indices->{'type'}{ $type }{ $fields } = $name;
222 $indices->{'name'}{ $name } = {
230 for my $type ( keys %{ $t2_indices{'type'} } ) {
231 while ( my ($fields, $iname) = each %{$t2_indices{'type'}{ $type } } ) {
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 ) {
241 elsif ( my $i1 = $t1_indices{'type'}{ $type }{ $fields } ) {
245 push @diffs, "DROP INDEX $iname on $t1_name;";
249 for my $type ( keys %{ $t1_indices{'type'} } ) {
250 while ( my ($fields, $iname) = each %{$t1_indices{'type'}{ $type } } ) {
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 ) {
260 elsif ( my $i2 = $t2_indices{'type'}{ $type }{ $fields } ) {
264 push @diffs, sprintf(
265 "CREATE %sINDEX%s ON %s (%s);",
266 $type eq NORMAL ? '' : "$type ",
267 $iname ? " $iname" : '',
275 for my $t2 ( $target_schema->get_tables ) {
276 my $t2_name = $t2->name;
277 my $t1 = $source_schema->get_table( $t2_name );
280 push @diffs, "DROP TABLE $t2_name;";
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;";
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 );
302 "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, ''
306 print "There were no differences.\n";
309 # -------------------------------------------------------------------
310 # Bring out number weight & measure in a year of dearth.
312 # -------------------------------------------------------------------
318 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
322 SQL::Translator, L<http://sqlfairy.sourceforge.net>.