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 # -------------------------------------------------------------------
96 use SQL::Translator::Schema::Constants;
98 use vars qw( $VERSION );
101 my ( @input, $list, $help, $debug );
102 for my $arg ( @ARGV ) {
103 if ( $arg =~ m/^-?-l(ist)?$/ ) {
106 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
109 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
112 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
113 push @input, { file => $1, parser => $2 };
116 pod2usage( msg => "Unknown argument '$arg'" );
120 pod2usage(1) if $help;
121 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
122 pod2usage('No input') if !@input;
124 if ( my $interactive = -t STDIN && -t STDOUT ) {
125 print STDERR join("\n",
126 "sqlt-diff-old is deprecated. Please sqlt-diff, and tell us ",
127 "about any problems or patch SQL::Translator::Diff",
132 my $tr = SQL::Translator->new;
133 my @parsers = $tr->list_parsers;
134 my %valid_parsers = map { $_, 1 } @parsers;
137 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
142 pod2usage( msg => 'Too many file args' ) if @input > 2;
144 my ( $source_schema, $source_db, $target_schema, $target_db );
147 for my $in ( @input ) {
148 my $file = $in->{'file'};
149 my $parser = $in->{'parser'};
151 die "Unable to read file '$file'\n" unless -r $file;
152 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
154 my $t = SQL::Translator->new;
156 $t->parser( $parser ) or die $tr->error;
157 my $out = $t->translate( $file ) or die $tr->error;
158 my $schema = $t->schema;
159 unless ( $schema->name ) {
160 $schema->name( $file );
164 $source_schema = $schema;
165 $source_db = $parser;
168 $target_schema = $schema;
169 $target_db = $parser;
173 my $case_insensitive = $target_db =~ /SQLServer/;
175 my $s1_name = $source_schema->name;
176 my $s2_name = $target_schema->name;
177 my ( @new_tables, @diffs , @diffs_at_end);
178 for my $t1 ( $source_schema->get_tables ) {
179 my $t1_name = $t1->name;
180 my $t2 = $target_schema->get_table( $t1_name, $case_insensitive );
182 warn "TABLE '$s1_name.$t1_name'\n" if $debug;
184 warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
186 if ( $target_db =~ /(SQLServer|Oracle)/ ) {
187 for my $constraint ( $t1->get_constraints ) {
188 next if $constraint->type ne FOREIGN_KEY;
189 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
190 constraint_to_string($constraint, $source_schema).";";
191 $t1->drop_constraint($constraint);
194 push @new_tables, $t1;
198 # Go through our options
199 my $options_different = 0;
202 for my $t1_option_ref ( $t1->options ) {
203 my($key1, $value1) = %{$t1_option_ref};
204 for my $t2_option_ref ( $t2->options ) {
205 my($key2, $value2) = %{$t2_option_ref};
206 if ( $key1 eq $key2 ) {
207 if ( defined $value1 != defined $value2 ) {
208 $options_different = 1;
211 if ( defined $value1 && $value1 ne $value2 ) {
212 $options_different = 1;
215 $checkedOptions{$key1} = 1;
219 $options_different = 1;
222 # Go through the other table's options
223 unless ( $options_different ) {
224 for my $t2_option_ref ( $t2->options ) {
225 my($key, $value) = %{$t2_option_ref};
226 next if $checkedOptions{$key};
227 $options_different = 1;
231 # If there's a difference, just re-set all the options
232 my @diffs_table_options;
233 if ( $options_different ) {
235 foreach my $option_ref ( $t1->options ) {
236 my($key, $value) = %{$option_ref};
237 push(@options, defined $value ? "$key=$value" : $key);
239 my $options = join(' ', @options);
240 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
243 my $t2_name = $t2->name;
244 my(@diffs_table_adds, @diffs_table_changes);
245 for my $t1_field ( $t1->get_fields ) {
246 my $f1_type = $t1_field->data_type;
247 my $f1_size = $t1_field->size;
248 my $f1_name = $t1_field->name;
249 my $f1_nullable = $t1_field->is_nullable;
250 my $f1_default = $t1_field->default_value;
251 my $f1_auto_inc = $t1_field->is_auto_increment;
252 my $t2_field = $t2->get_field( $f1_name, $case_insensitive );
253 my $f1_full_name = "$s1_name.$t1_name.$t1_name";
254 warn "FIELD '$f1_full_name'\n" if $debug;
256 my $f2_full_name = "$s2_name.$t2_name.$f1_name";
258 unless ( $t2_field ) {
259 warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
261 my $temp_default_value = 0;
262 if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
263 # SQL Server doesn't allow adding non-nullable, non-default columns
264 # so we add it with a default value, then remove the default value
265 $temp_default_value = 1;
266 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
267 $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
269 push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
270 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
272 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
273 !defined $f1_default ? ''
274 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
275 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
276 : " DEFAULT '$f1_default'",
277 $f1_nullable ? '' : ' NOT NULL',
278 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
279 $target_db =~ /Oracle/ ? ')' : '',
281 if ( $temp_default_value ) {
283 push @diffs_table_adds, sprintf( <<END
284 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
287 FROM sysobjects so JOIN sysconstraints sc
288 ON so.id = sc.constid
289 WHERE object_name(so.parent_obj) = '%s'
292 (SELECT colid FROM syscolumns
293 WHERE id = object_id('%s') AND
295 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
299 , $t1_name, $t1_name, $f1_name, $t1_name,
305 my $f2_type = $t2_field->data_type;
306 my $f2_size = $t2_field->size || '';
307 my $f2_nullable = $t2_field->is_nullable;
308 my $f2_default = $t2_field->default_value;
309 my $f2_auto_inc = $t2_field->is_auto_increment;
310 if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
311 # SQLServer timestamp fields can't be altered, so we drop and add instead
312 if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
313 push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
314 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
315 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
317 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
318 !defined $f1_default ? ''
319 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
320 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
321 : " DEFAULT '$f1_default'",
322 $f1_nullable ? '' : ' NOT NULL',
323 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
324 $target_db =~ /Oracle/ ? ')' : '',
329 my $changeText = $target_db =~ /SQLServer/ ? 'ALTER COLUMN' :
330 $target_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
331 my $nullText = $f1_nullable ? '' : ' NOT NULL';
332 $nullText = '' if $target_db =~ /Oracle/ && $f1_nullable == $f2_nullable;
333 push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
334 $t1_name, $changeText,
335 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
336 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
338 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
339 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
340 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
341 : " DEFAULT '$f1_default'",
342 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
343 $target_db =~ /Oracle/ ? ')' : '',
345 if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
346 # Adding a column with a default value for SQL Server means adding a
347 # constraint and setting existing NULLs to the default value
348 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
349 $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
350 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
351 : "DEFAULT '$f1_default'", $f1_name,
353 push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
354 $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
355 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
356 : "'$f1_default'", $f1_name,
362 my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
364 for my $i1 ( $t1->get_indices ) {
365 for my $i2 ( $t2->get_indices ) {
366 if ( $i1->equals($i2, $case_insensitive) ) {
367 $checked_indices{$i2} = 1;
371 push @diffs_index_creates, sprintf(
372 "CREATE %sINDEX%s ON %s (%s);",
373 $i1->type eq NORMAL ? '' : $i1->type." ",
374 $i1->name ? " ".$i1->name : '',
376 join(",", $i1->fields),
380 for my $i2 ( $t2->get_indices ) {
381 next if $checked_indices{$i2};
382 for my $i1 ( $t1->get_indices ) {
383 next INDEX2 if $i2->equals($i1, $case_insensitive);
385 $target_db =~ /SQLServer/
386 ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
387 : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
390 my(%checked_constraints, @diffs_constraint_drops);
392 for my $c1 ( $t1->get_constraints ) {
393 next if $source_db =~ /Oracle/ && $c1->type eq UNIQUE && $c1->name =~ /^SYS_/i;
394 for my $c2 ( $t2->get_constraints ) {
395 if ( $c1->equals($c2, $case_insensitive) ) {
396 $checked_constraints{$c2} = 1;
400 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
401 constraint_to_string($c1, $source_schema).";";
404 for my $c2 ( $t2->get_constraints ) {
405 next if $checked_constraints{$c2};
406 for my $c1 ( $t1->get_constraints ) {
407 next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
409 if ( $c2->type eq UNIQUE ) {
410 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
412 } elsif ( $target_db =~ /SQLServer/ ) {
413 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
415 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
416 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
420 push @diffs, @diffs_index_drops, @diffs_constraint_drops,
421 @diffs_table_options, @diffs_table_adds,
422 @diffs_table_changes, @diffs_index_creates;
425 for my $t2 ( $target_schema->get_tables ) {
426 my $t2_name = $t2->name;
427 my $t1 = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
430 if ( $target_db =~ /SQLServer/ ) {
431 for my $constraint ( $t2->get_constraints ) {
432 next if $constraint->type eq PRIMARY_KEY;
433 push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
436 push @diffs_at_end, "DROP TABLE $t2_name;";
440 for my $t2_field ( $t2->get_fields ) {
441 my $f2_name = $t2_field->name;
442 my $t1_field = $t1->get_field( $f2_name );
443 unless ( $t1_field ) {
444 my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
445 push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
451 my $dummy_tr = SQL::Translator->new;
452 $dummy_tr->schema->add_table( $_ ) for @new_tables;
453 my $producer = $dummy_tr->producer( $target_db );
454 unshift @diffs, $producer->( $dummy_tr );
456 push(@diffs, @diffs_at_end);
459 if ( $source_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
460 unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
466 "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
471 print "There were no differences.\n";
474 sub constraint_to_string {
476 my $schema = shift or die "No schema given";
477 my @fields = $c->field_names or return '';
479 if ( $c->type eq PRIMARY_KEY ) {
480 if ( $target_db =~ /Oracle/ ) {
481 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
482 'PRIMARY KEY (' . join(', ', @fields). ')';
484 return 'PRIMARY KEY (' . join(', ', @fields). ')';
487 elsif ( $c->type eq UNIQUE ) {
488 if ( $target_db =~ /Oracle/ ) {
489 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
490 'UNIQUE (' . join(', ', @fields). ')';
493 (defined $c->name ? $c->name.' ' : '').
494 '(' . join(', ', @fields). ')';
497 elsif ( $c->type eq FOREIGN_KEY ) {
499 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
502 $def .= ' (' . join( ', ', @fields ) . ')';
504 $def .= ' REFERENCES ' . $c->reference_table;
506 my @rfields = map { $_ || () } $c->reference_fields;
507 unless ( @rfields ) {
508 my $rtable_name = $c->reference_table;
509 if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
510 push @rfields, $ref_table->primary_key;
513 warn "Can't find reference table '$rtable_name' " .
519 $def .= ' (' . join( ', ', @rfields ) . ')';
522 warn "FK constraint on " . 'some table' . '.' .
523 join('', @fields) . " has no reference fields\n";
526 if ( $c->match_type ) {
528 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
531 if ( $c->on_delete ) {
532 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
535 if ( $c->on_update ) {
536 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
543 # -------------------------------------------------------------------
544 # Bring out number weight & measure in a year of dearth.
546 # -------------------------------------------------------------------
552 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
556 SQL::Translator, L<http://sqlfairy.sourceforge.net>.