4 # -------------------------------------------------------------------
5 # Copyright (C) 2002-2009 The SQLFairy Authors
7 # This program is free software; you can redistribute it and/or
8 # modify it under the terms of the GNU General Public License as
9 # published by the Free Software Foundation; version 2.
11 # This program is distributed in the hope that it will be useful, but
12 # WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 # General Public License for more details.
16 # You should have received a copy of the GNU General Public License
17 # along with this program; if not, write to the Free Software
18 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
20 # -------------------------------------------------------------------
24 sqlt-diff - find the differences b/w two schemas
32 For a list of all valid parsers:
38 sqlt-diff [options] file_name1=parser file_name2=parser
42 -d|--debug Show debugging info
46 sqlt-diff is a utility for creating a file of SQL commands necessary to
47 transform the first schema provided to the second. While not yet
48 exhaustive in its ability to mutate the entire schema, it will report the
55 Using the Producer class of the target (second) schema, any tables missing
56 in the first schema will be generated in their entirety (fields, constraints,
59 =item * Missing/altered fields
61 Any fields missing or altered between the two schemas will be reported
64 ALTER TABLE <table_name>
66 [CHANGE <field_name> <datatype> (<size>)] ;
68 =item * Missing/altered indices
70 Any indices missing or of a different type or on different fields will be
71 indicated. Indices that should be dropped will be reported as such:
73 DROP INDEX <index_name> ON <table_name> ;
75 An index of a different type or on different fields will be reported as a
78 CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
79 ( <field_name>[,<field_name>] ) ;
83 "ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
84 the Producer, unfortunately, and may require massaging before being passed to
89 # -------------------------------------------------------------------
95 use SQL::Translator::Schema::Constants;
97 use vars qw( $VERSION );
100 my ( @input, $list, $help, $debug );
101 for my $arg ( @ARGV ) {
102 if ( $arg =~ m/^-?-l(ist)?$/ ) {
105 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
108 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
111 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
112 push @input, { file => $1, parser => $2 };
115 pod2usage( msg => "Unknown argument '$arg'" );
119 pod2usage(1) if $help;
120 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
122 my $tr = SQL::Translator->new;
123 my @parsers = $tr->list_parsers;
124 my %valid_parsers = map { $_, 1 } @parsers;
127 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
132 pod2usage( msg => 'Too many file args' ) if @input > 2;
134 my ( $source_schema, $source_db, $target_schema, $target_db );
137 for my $in ( @input ) {
138 my $file = $in->{'file'};
139 my $parser = $in->{'parser'};
141 die "Unable to read file '$file'\n" unless -r $file;
142 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
144 my $t = SQL::Translator->new;
146 $t->parser( $parser ) or die $tr->error;
147 my $out = $t->translate( $file ) or die $tr->error;
148 my $schema = $t->schema;
149 unless ( $schema->name ) {
150 $schema->name( $file );
154 $source_schema = $schema;
155 $source_db = $parser;
158 $target_schema = $schema;
159 $target_db = $parser;
163 my $case_insensitive = $target_db =~ /SQLServer/;
165 my $s1_name = $source_schema->name;
166 my $s2_name = $target_schema->name;
167 my ( @new_tables, @diffs , @diffs_at_end);
168 for my $t1 ( $source_schema->get_tables ) {
169 my $t1_name = $t1->name;
170 my $t2 = $target_schema->get_table( $t1_name, $case_insensitive );
172 warn "TABLE '$s1_name.$t1_name'\n" if $debug;
174 warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
176 if ( $target_db =~ /(SQLServer|Oracle)/ ) {
177 for my $constraint ( $t1->get_constraints ) {
178 next if $constraint->type ne FOREIGN_KEY;
179 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
180 constraint_to_string($constraint, $source_schema).";";
181 $t1->drop_constraint($constraint);
184 push @new_tables, $t1;
188 # Go through our options
189 my $options_different = 0;
192 for my $t1_option_ref ( $t1->options ) {
193 my($key1, $value1) = %{$t1_option_ref};
194 for my $t2_option_ref ( $t2->options ) {
195 my($key2, $value2) = %{$t2_option_ref};
196 if ( $key1 eq $key2 ) {
197 if ( defined $value1 != defined $value2 ) {
198 $options_different = 1;
201 if ( defined $value1 && $value1 ne $value2 ) {
202 $options_different = 1;
205 $checkedOptions{$key1} = 1;
209 $options_different = 1;
212 # Go through the other table's options
213 unless ( $options_different ) {
214 for my $t2_option_ref ( $t2->options ) {
215 my($key, $value) = %{$t2_option_ref};
216 next if $checkedOptions{$key};
217 $options_different = 1;
221 # If there's a difference, just re-set all the options
222 my @diffs_table_options;
223 if ( $options_different ) {
225 foreach my $option_ref ( $t1->options ) {
226 my($key, $value) = %{$option_ref};
227 push(@options, defined $value ? "$key=$value" : $key);
229 my $options = join(' ', @options);
230 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
233 my $t2_name = $t2->name;
234 my(@diffs_table_adds, @diffs_table_changes);
235 for my $t1_field ( $t1->get_fields ) {
236 my $f1_type = $t1_field->data_type;
237 my $f1_size = $t1_field->size;
238 my $f1_name = $t1_field->name;
239 my $f1_nullable = $t1_field->is_nullable;
240 my $f1_default = $t1_field->default_value;
241 my $f1_auto_inc = $t1_field->is_auto_increment;
242 my $t2_field = $t2->get_field( $f1_name, $case_insensitive );
243 my $f1_full_name = "$s1_name.$t1_name.$t1_name";
244 warn "FIELD '$f1_full_name'\n" if $debug;
246 my $f2_full_name = "$s2_name.$t2_name.$f1_name";
248 unless ( $t2_field ) {
249 warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
251 my $temp_default_value = 0;
252 if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
253 # SQL Server doesn't allow adding non-nullable, non-default columns
254 # so we add it with a default value, then remove the default value
255 $temp_default_value = 1;
256 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
257 $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
259 push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
260 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
262 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
263 !defined $f1_default ? ''
264 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
265 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
266 : " DEFAULT '$f1_default'",
267 $f1_nullable ? '' : ' NOT NULL',
268 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
269 $target_db =~ /Oracle/ ? ')' : '',
271 if ( $temp_default_value ) {
273 push @diffs_table_adds, sprintf( <<END
274 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
277 FROM sysobjects so JOIN sysconstraints sc
278 ON so.id = sc.constid
279 WHERE object_name(so.parent_obj) = '%s'
282 (SELECT colid FROM syscolumns
283 WHERE id = object_id('%s') AND
285 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
289 , $t1_name, $t1_name, $f1_name, $t1_name,
295 my $f2_type = $t2_field->data_type;
296 my $f2_size = $t2_field->size || '';
297 my $f2_nullable = $t2_field->is_nullable;
298 my $f2_default = $t2_field->default_value;
299 my $f2_auto_inc = $t2_field->is_auto_increment;
300 if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
301 # SQLServer timestamp fields can't be altered, so we drop and add instead
302 if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
303 push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
304 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
305 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
307 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
308 !defined $f1_default ? ''
309 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
310 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
311 : " DEFAULT '$f1_default'",
312 $f1_nullable ? '' : ' NOT NULL',
313 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
314 $target_db =~ /Oracle/ ? ')' : '',
319 my $changeText = $target_db =~ /SQLServer/ ? 'ALTER COLUMN' :
320 $target_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
321 my $nullText = $f1_nullable ? '' : ' NOT NULL';
322 $nullText = '' if $target_db =~ /Oracle/ && $f1_nullable == $f2_nullable;
323 push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
324 $t1_name, $changeText,
325 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
326 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
328 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
329 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
330 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
331 : " DEFAULT '$f1_default'",
332 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
333 $target_db =~ /Oracle/ ? ')' : '',
335 if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
336 # Adding a column with a default value for SQL Server means adding a
337 # constraint and setting existing NULLs to the default value
338 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
339 $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
340 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
341 : "DEFAULT '$f1_default'", $f1_name,
343 push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
344 $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
345 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
346 : "'$f1_default'", $f1_name,
352 my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
354 for my $i1 ( $t1->get_indices ) {
355 for my $i2 ( $t2->get_indices ) {
356 if ( $i1->equals($i2, $case_insensitive) ) {
357 $checked_indices{$i2} = 1;
361 push @diffs_index_creates, sprintf(
362 "CREATE %sINDEX%s ON %s (%s);",
363 $i1->type eq NORMAL ? '' : $i1->type." ",
364 $i1->name ? " ".$i1->name : '',
366 join(",", $i1->fields),
370 for my $i2 ( $t2->get_indices ) {
371 next if $checked_indices{$i2};
372 for my $i1 ( $t1->get_indices ) {
373 next INDEX2 if $i2->equals($i1, $case_insensitive);
375 $target_db =~ /SQLServer/
376 ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
377 : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
380 my(%checked_constraints, @diffs_constraint_drops);
382 for my $c1 ( $t1->get_constraints ) {
383 next if $source_db =~ /Oracle/ && $c1->type eq UNIQUE && $c1->name =~ /^SYS_/i;
384 for my $c2 ( $t2->get_constraints ) {
385 if ( $c1->equals($c2, $case_insensitive) ) {
386 $checked_constraints{$c2} = 1;
390 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
391 constraint_to_string($c1, $source_schema).";";
394 for my $c2 ( $t2->get_constraints ) {
395 next if $checked_constraints{$c2};
396 for my $c1 ( $t1->get_constraints ) {
397 next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
399 if ( $c2->type eq UNIQUE ) {
400 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
402 } elsif ( $target_db =~ /SQLServer/ ) {
403 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
405 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
406 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
410 push @diffs, @diffs_index_drops, @diffs_constraint_drops,
411 @diffs_table_options, @diffs_table_adds,
412 @diffs_table_changes, @diffs_index_creates;
415 for my $t2 ( $target_schema->get_tables ) {
416 my $t2_name = $t2->name;
417 my $t1 = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
420 if ( $target_db =~ /SQLServer/ ) {
421 for my $constraint ( $t2->get_constraints ) {
422 next if $constraint->type eq PRIMARY_KEY;
423 push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
426 push @diffs_at_end, "DROP TABLE $t2_name;";
430 for my $t2_field ( $t2->get_fields ) {
431 my $f2_name = $t2_field->name;
432 my $t1_field = $t1->get_field( $f2_name );
433 unless ( $t1_field ) {
434 my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
435 push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
441 my $dummy_tr = SQL::Translator->new;
442 $dummy_tr->schema->add_table( $_ ) for @new_tables;
443 my $producer = $dummy_tr->producer( $target_db );
444 unshift @diffs, $producer->( $dummy_tr );
446 push(@diffs, @diffs_at_end);
449 if ( $source_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
450 unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
454 print STDERR "sqlt-diff-old is deprecated, please try and use sqlt-diff, and tell us about any problems or patch SQL::Translator::Diff\n";
458 "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
463 print "There were no differences.\n";
466 sub constraint_to_string {
468 my $schema = shift or die "No schema given";
469 my @fields = $c->field_names or return '';
471 if ( $c->type eq PRIMARY_KEY ) {
472 if ( $target_db =~ /Oracle/ ) {
473 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
474 'PRIMARY KEY (' . join(', ', @fields). ')';
476 return 'PRIMARY KEY (' . join(', ', @fields). ')';
479 elsif ( $c->type eq UNIQUE ) {
480 if ( $target_db =~ /Oracle/ ) {
481 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
482 'UNIQUE (' . join(', ', @fields). ')';
485 (defined $c->name ? $c->name.' ' : '').
486 '(' . join(', ', @fields). ')';
489 elsif ( $c->type eq FOREIGN_KEY ) {
491 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
494 $def .= ' (' . join( ', ', @fields ) . ')';
496 $def .= ' REFERENCES ' . $c->reference_table;
498 my @rfields = map { $_ || () } $c->reference_fields;
499 unless ( @rfields ) {
500 my $rtable_name = $c->reference_table;
501 if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
502 push @rfields, $ref_table->primary_key;
505 warn "Can't find reference table '$rtable_name' " .
511 $def .= ' (' . join( ', ', @rfields ) . ')';
514 warn "FK constraint on " . 'some table' . '.' .
515 join('', @fields) . " has no reference fields\n";
518 if ( $c->match_type ) {
520 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
523 if ( $c->on_delete ) {
524 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
527 if ( $c->on_update ) {
528 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
535 # -------------------------------------------------------------------
536 # Bring out number weight & measure in a year of dearth.
538 # -------------------------------------------------------------------
544 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
548 SQL::Translator, L<http://sqlfairy.sourceforge.net>.