4 # -------------------------------------------------------------------
5 # $Id: sqlt-diff,v 1.12 2005-08-31 15:42:17 duality72 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.12 $ =~ /(\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 my $out = $t->translate( $file ) or die $tr->error;
149 my $schema = $t->schema;
150 unless ( $schema->name ) {
151 $schema->name( $file );
155 $source_schema = $schema;
156 $source_db = $parser;
159 $target_schema = $schema;
160 $target_db = $parser;
164 my $case_insensitive = $target_db =~ /SQLServer/;
166 my $s1_name = $source_schema->name;
167 my $s2_name = $target_schema->name;
168 my ( @new_tables, @diffs , @diffs_at_end);
169 for my $t1 ( $source_schema->get_tables ) {
170 my $t1_name = $t1->name;
171 my $t2 = $target_schema->get_table( $t1_name, $case_insensitive );
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 if ( $target_db =~ /(SQLServer|Oracle)/ ) {
178 for my $constraint ( $t1->get_constraints ) {
179 next if $constraint->type ne FOREIGN_KEY;
180 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
181 constraint_to_string($constraint, $source_schema).";";
182 $t1->drop_constraint($constraint);
185 push @new_tables, $t1;
189 # Go through our options
190 my $options_different = 0;
193 for my $t1_option_ref ( $t1->options ) {
194 my($key1, $value1) = %{$t1_option_ref};
195 for my $t2_option_ref ( $t2->options ) {
196 my($key2, $value2) = %{$t2_option_ref};
197 if ( $key1 eq $key2 ) {
198 if ( defined $value1 != defined $value2 ) {
199 $options_different = 1;
202 if ( defined $value1 && $value1 ne $value2 ) {
203 $options_different = 1;
206 $checkedOptions{$key1} = 1;
210 $options_different = 1;
213 # Go through the other table's options
214 unless ( $options_different ) {
215 for my $t2_option_ref ( $t2->options ) {
216 my($key, $value) = %{$t2_option_ref};
217 next if $checkedOptions{$key};
218 $options_different = 1;
222 # If there's a difference, just re-set all the options
223 my @diffs_table_options;
224 if ( $options_different ) {
226 foreach my $option_ref ( $t1->options ) {
227 my($key, $value) = %{$option_ref};
228 push(@options, defined $value ? "$key=$value" : $key);
230 my $options = join(' ', @options);
231 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
234 my $t2_name = $t2->name;
235 my(@diffs_table_adds, @diffs_table_changes);
236 for my $t1_field ( $t1->get_fields ) {
237 my $f1_type = $t1_field->data_type;
238 my $f1_size = $t1_field->size;
239 my $f1_name = $t1_field->name;
240 my $f1_nullable = $t1_field->is_nullable;
241 my $f1_default = $t1_field->default_value;
242 my $f1_auto_inc = $t1_field->is_auto_increment;
243 my $t2_field = $t2->get_field( $f1_name, $case_insensitive );
244 my $f1_full_name = "$s1_name.$t1_name.$t1_name";
245 warn "FIELD '$f1_full_name'\n" if $debug;
247 my $f2_full_name = "$s2_name.$t2_name.$f1_name";
249 unless ( $t2_field ) {
250 warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
252 my $temp_default_value = 0;
253 if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
254 # SQL Server doesn't allow adding non-nullable, non-default columns
255 # so we add it with a default value, then remove the default value
256 $temp_default_value = 1;
257 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
258 $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
260 push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
261 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
263 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
264 !defined $f1_default ? ''
265 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
266 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
267 : " DEFAULT '$f1_default'",
268 $f1_nullable ? '' : ' NOT NULL',
269 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
270 $target_db =~ /Oracle/ ? ')' : '',
272 if ( $temp_default_value ) {
274 push @diffs_table_adds, sprintf( <<END
275 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
278 FROM sysobjects so JOIN sysconstraints sc
279 ON so.id = sc.constid
280 WHERE object_name(so.parent_obj) = '%s'
283 (SELECT colid FROM syscolumns
284 WHERE id = object_id('%s') AND
286 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
290 , $t1_name, $t1_name, $f1_name, $t1_name,
296 my $f2_type = $t2_field->data_type;
297 my $f2_size = $t2_field->size || '';
298 my $f2_nullable = $t2_field->is_nullable;
299 my $f2_default = $t2_field->default_value;
300 my $f2_auto_inc = $t2_field->is_auto_increment;
301 if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
302 # SQLServer timestamp fields can't be altered, so we drop and add instead
303 if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
304 push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
305 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
306 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
308 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
309 !defined $f1_default ? ''
310 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
311 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
312 : " DEFAULT '$f1_default'",
313 $f1_nullable ? '' : ' NOT NULL',
314 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
315 $target_db =~ /Oracle/ ? ')' : '',
320 my $changeText = $target_db =~ /SQLServer/ ? 'ALTER COLUMN' :
321 $target_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
322 my $nullText = $f1_nullable ? '' : ' NOT NULL';
323 $nullText = '' if $target_db =~ /Oracle/ && $f1_nullable == $f2_nullable;
324 push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
325 $t1_name, $changeText,
326 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
327 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
329 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
330 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
331 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
332 : " DEFAULT '$f1_default'",
333 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
334 $target_db =~ /Oracle/ ? ')' : '',
336 if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
337 # Adding a column with a default value for SQL Server means adding a
338 # constraint and setting existing NULLs to the default value
339 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
340 $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
341 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
342 : "DEFAULT '$f1_default'", $f1_name,
344 push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
345 $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
346 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
347 : "'$f1_default'", $f1_name,
353 my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
355 for my $i1 ( $t1->get_indices ) {
356 for my $i2 ( $t2->get_indices ) {
357 if ( $i1->equals($i2, $case_insensitive) ) {
358 $checked_indices{$i2} = 1;
362 push @diffs_index_creates, sprintf(
363 "CREATE %sINDEX%s ON %s (%s);",
364 $i1->type eq NORMAL ? '' : $i1->type." ",
365 $i1->name ? " ".$i1->name : '',
367 join(",", $i1->fields),
371 for my $i2 ( $t2->get_indices ) {
372 next if $checked_indices{$i2};
373 for my $i1 ( $t1->get_indices ) {
374 next INDEX2 if $i2->equals($i1, $case_insensitive);
376 $target_db =~ /SQLServer/
377 ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
378 : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
381 my(%checked_constraints, @diffs_constraint_drops);
383 for my $c1 ( $t1->get_constraints ) {
384 next if $source_db =~ /Oracle/ && $c1->type eq UNIQUE && $c1->name =~ /^SYS_/i;
385 for my $c2 ( $t2->get_constraints ) {
386 if ( $c1->equals($c2, $case_insensitive) ) {
387 $checked_constraints{$c2} = 1;
391 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
392 constraint_to_string($c1, $source_schema).";";
395 for my $c2 ( $t2->get_constraints ) {
396 next if $checked_constraints{$c2};
397 for my $c1 ( $t1->get_constraints ) {
398 next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
400 if ( $c2->type eq UNIQUE ) {
401 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
403 } elsif ( $target_db =~ /SQLServer/ ) {
404 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
406 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
407 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
411 push @diffs, @diffs_index_drops, @diffs_constraint_drops,
412 @diffs_table_options, @diffs_table_adds,
413 @diffs_table_changes, @diffs_index_creates;
416 for my $t2 ( $target_schema->get_tables ) {
417 my $t2_name = $t2->name;
418 my $t1 = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
421 if ( $target_db =~ /SQLServer/ ) {
422 for my $constraint ( $t2->get_constraints ) {
423 next if $constraint->type eq PRIMARY_KEY;
424 push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
427 push @diffs_at_end, "DROP TABLE $t2_name;";
431 for my $t2_field ( $t2->get_fields ) {
432 my $f2_name = $t2_field->name;
433 my $t1_field = $t1->get_field( $f2_name );
434 unless ( $t1_field ) {
435 my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
436 push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
442 my $dummy_tr = SQL::Translator->new;
443 $dummy_tr->schema->add_table( $_ ) for @new_tables;
444 my $producer = $dummy_tr->producer( $target_db );
445 unshift @diffs, $producer->( $dummy_tr );
447 push(@diffs, @diffs_at_end);
451 "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
456 print "There were no differences.\n";
459 sub constraint_to_string {
461 my $schema = shift or die "No schema given";
462 my @fields = $c->field_names or return '';
464 if ( $c->type eq PRIMARY_KEY ) {
465 if ( $target_db =~ /Oracle/ ) {
466 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
467 'PRIMARY KEY (' . join(', ', @fields). ')';
469 return 'PRIMARY KEY (' . join(', ', @fields). ')';
472 elsif ( $c->type eq UNIQUE ) {
473 if ( $target_db =~ /Oracle/ ) {
474 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
475 'UNIQUE (' . join(', ', @fields). ')';
478 (defined $c->name ? $c->name.' ' : '').
479 '(' . join(', ', @fields). ')';
482 elsif ( $c->type eq FOREIGN_KEY ) {
484 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
487 $def .= ' (' . join( ', ', @fields ) . ')';
489 $def .= ' REFERENCES ' . $c->reference_table;
491 my @rfields = map { $_ || () } $c->reference_fields;
492 unless ( @rfields ) {
493 my $rtable_name = $c->reference_table;
494 if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
495 push @rfields, $ref_table->primary_key;
498 warn "Can't find reference table '$rtable_name' " .
504 $def .= ' (' . join( ', ', @rfields ) . ')';
507 warn "FK constraint on " . 'some table' . '.' .
508 join('', @fields) . " has no reference fields\n";
511 if ( $c->match_type ) {
513 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
516 if ( $c->on_delete ) {
517 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
520 if ( $c->on_update ) {
521 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
528 # -------------------------------------------------------------------
529 # Bring out number weight & measure in a year of dearth.
531 # -------------------------------------------------------------------
537 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
541 SQL::Translator, L<http://sqlfairy.sourceforge.net>.