6 sqlt-diff - find the differences b/w two schemas
14 For a list of all valid parsers:
20 sqlt-diff [options] file_name1=parser file_name2=parser
24 -d|--debug Show debugging info
28 sqlt-diff is a utility for creating a file of SQL commands necessary to
29 transform the first schema provided to the second. While not yet
30 exhaustive in its ability to mutate the entire schema, it will report the
37 Using the Producer class of the target (second) schema, any tables missing
38 in the first schema will be generated in their entirety (fields, constraints,
41 =item * Missing/altered fields
43 Any fields missing or altered between the two schemas will be reported
46 ALTER TABLE <table_name>
48 [CHANGE <field_name> <datatype> (<size>)] ;
50 =item * Missing/altered indices
52 Any indices missing or of a different type or on different fields will be
53 indicated. Indices that should be dropped will be reported as such:
55 DROP INDEX <index_name> ON <table_name> ;
57 An index of a different type or on different fields will be reported as a
60 CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
61 ( <field_name>[,<field_name>] ) ;
65 "ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
66 the Producer, unfortunately, and may require massaging before being passed to
71 # -------------------------------------------------------------------
78 use SQL::Translator::Schema::Constants;
80 use vars qw( $VERSION );
83 my ( @input, $list, $help, $debug );
84 for my $arg ( @ARGV ) {
85 if ( $arg =~ m/^-?-l(ist)?$/ ) {
88 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
91 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
94 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
95 push @input, { file => $1, parser => $2 };
98 pod2usage( msg => "Unknown argument '$arg'" );
102 pod2usage(1) if $help;
103 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
104 pod2usage('No input') if !@input;
106 if ( my $interactive = -t STDIN && -t STDOUT ) {
107 print STDERR join("\n",
108 "sqlt-diff-old is deprecated. Please sqlt-diff, and tell us ",
109 "about any problems or patch SQL::Translator::Diff",
114 my $tr = SQL::Translator->new;
115 my @parsers = $tr->list_parsers;
116 my %valid_parsers = map { $_, 1 } @parsers;
119 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
124 pod2usage( msg => 'Too many file args' ) if @input > 2;
126 my ( $source_schema, $source_db, $target_schema, $target_db );
129 for my $in ( @input ) {
130 my $file = $in->{'file'};
131 my $parser = $in->{'parser'};
133 die "Unable to read file '$file'\n" unless -r $file;
134 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
136 my $t = SQL::Translator->new;
138 $t->parser( $parser ) or die $tr->error;
139 my $out = $t->translate( $file ) or die $tr->error;
140 my $schema = $t->schema;
141 unless ( $schema->name ) {
142 $schema->name( $file );
146 $source_schema = $schema;
147 $source_db = $parser;
150 $target_schema = $schema;
151 $target_db = $parser;
155 my $case_insensitive = $target_db =~ /SQLServer/;
157 my $s1_name = $source_schema->name;
158 my $s2_name = $target_schema->name;
159 my ( @new_tables, @diffs , @diffs_at_end);
160 for my $t1 ( $source_schema->get_tables ) {
161 my $t1_name = $t1->name;
162 my $t2 = $target_schema->get_table( $t1_name, $case_insensitive );
164 warn "TABLE '$s1_name.$t1_name'\n" if $debug;
166 warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
168 if ( $target_db =~ /(SQLServer|Oracle)/ ) {
169 for my $constraint ( $t1->get_constraints ) {
170 next if $constraint->type ne FOREIGN_KEY;
171 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
172 constraint_to_string($constraint, $source_schema).";";
173 $t1->drop_constraint($constraint);
176 push @new_tables, $t1;
180 # Go through our options
181 my $options_different = 0;
184 for my $t1_option_ref ( $t1->options ) {
185 my($key1, $value1) = %{$t1_option_ref};
186 for my $t2_option_ref ( $t2->options ) {
187 my($key2, $value2) = %{$t2_option_ref};
188 if ( $key1 eq $key2 ) {
189 if ( defined $value1 != defined $value2 ) {
190 $options_different = 1;
193 if ( defined $value1 && $value1 ne $value2 ) {
194 $options_different = 1;
197 $checkedOptions{$key1} = 1;
201 $options_different = 1;
204 # Go through the other table's options
205 unless ( $options_different ) {
206 for my $t2_option_ref ( $t2->options ) {
207 my($key, $value) = %{$t2_option_ref};
208 next if $checkedOptions{$key};
209 $options_different = 1;
213 # If there's a difference, just re-set all the options
214 my @diffs_table_options;
215 if ( $options_different ) {
217 foreach my $option_ref ( $t1->options ) {
218 my($key, $value) = %{$option_ref};
219 push(@options, defined $value ? "$key=$value" : $key);
221 my $options = join(' ', @options);
222 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
225 my $t2_name = $t2->name;
226 my(@diffs_table_adds, @diffs_table_changes);
227 for my $t1_field ( $t1->get_fields ) {
228 my $f1_type = $t1_field->data_type;
229 my $f1_size = $t1_field->size;
230 my $f1_name = $t1_field->name;
231 my $f1_nullable = $t1_field->is_nullable;
232 my $f1_default = $t1_field->default_value;
233 my $f1_auto_inc = $t1_field->is_auto_increment;
234 my $t2_field = $t2->get_field( $f1_name, $case_insensitive );
235 my $f1_full_name = "$s1_name.$t1_name.$t1_name";
236 warn "FIELD '$f1_full_name'\n" if $debug;
238 my $f2_full_name = "$s2_name.$t2_name.$f1_name";
240 unless ( $t2_field ) {
241 warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
243 my $temp_default_value = 0;
244 if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
245 # SQL Server doesn't allow adding non-nullable, non-default columns
246 # so we add it with a default value, then remove the default value
247 $temp_default_value = 1;
248 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
249 $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
251 push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
252 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
254 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
255 !defined $f1_default ? ''
256 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
257 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
258 : " DEFAULT '$f1_default'",
259 $f1_nullable ? '' : ' NOT NULL',
260 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
261 $target_db =~ /Oracle/ ? ')' : '',
263 if ( $temp_default_value ) {
265 push @diffs_table_adds, sprintf( <<END
266 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
269 FROM sysobjects so JOIN sysconstraints sc
270 ON so.id = sc.constid
271 WHERE object_name(so.parent_obj) = '%s'
274 (SELECT colid FROM syscolumns
275 WHERE id = object_id('%s') AND
277 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
281 , $t1_name, $t1_name, $f1_name, $t1_name,
287 my $f2_type = $t2_field->data_type;
288 my $f2_size = $t2_field->size || '';
289 my $f2_nullable = $t2_field->is_nullable;
290 my $f2_default = $t2_field->default_value;
291 my $f2_auto_inc = $t2_field->is_auto_increment;
292 if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
293 # SQLServer timestamp fields can't be altered, so we drop and add instead
294 if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
295 push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
296 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
297 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
299 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
300 !defined $f1_default ? ''
301 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
302 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
303 : " DEFAULT '$f1_default'",
304 $f1_nullable ? '' : ' NOT NULL',
305 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
306 $target_db =~ /Oracle/ ? ')' : '',
311 my $changeText = $target_db =~ /SQLServer/ ? 'ALTER COLUMN' :
312 $target_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
313 my $nullText = $f1_nullable ? '' : ' NOT NULL';
314 $nullText = '' if $target_db =~ /Oracle/ && $f1_nullable == $f2_nullable;
315 push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
316 $t1_name, $changeText,
317 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
318 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
320 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
321 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
322 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
323 : " DEFAULT '$f1_default'",
324 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
325 $target_db =~ /Oracle/ ? ')' : '',
327 if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
328 # Adding a column with a default value for SQL Server means adding a
329 # constraint and setting existing NULLs to the default value
330 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
331 $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
332 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
333 : "DEFAULT '$f1_default'", $f1_name,
335 push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
336 $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
337 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
338 : "'$f1_default'", $f1_name,
344 my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
346 for my $i1 ( $t1->get_indices ) {
347 for my $i2 ( $t2->get_indices ) {
348 if ( $i1->equals($i2, $case_insensitive) ) {
349 $checked_indices{$i2} = 1;
353 push @diffs_index_creates, sprintf(
354 "CREATE %sINDEX%s ON %s (%s);",
355 $i1->type eq NORMAL ? '' : $i1->type." ",
356 $i1->name ? " ".$i1->name : '',
358 join(",", $i1->fields),
362 for my $i2 ( $t2->get_indices ) {
363 next if $checked_indices{$i2};
364 for my $i1 ( $t1->get_indices ) {
365 next INDEX2 if $i2->equals($i1, $case_insensitive);
367 $target_db =~ /SQLServer/
368 ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
369 : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
372 my(%checked_constraints, @diffs_constraint_drops);
374 for my $c1 ( $t1->get_constraints ) {
375 next if $source_db =~ /Oracle/ && $c1->type eq UNIQUE && $c1->name =~ /^SYS_/i;
376 for my $c2 ( $t2->get_constraints ) {
377 if ( $c1->equals($c2, $case_insensitive) ) {
378 $checked_constraints{$c2} = 1;
382 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
383 constraint_to_string($c1, $source_schema).";";
386 for my $c2 ( $t2->get_constraints ) {
387 next if $checked_constraints{$c2};
388 for my $c1 ( $t1->get_constraints ) {
389 next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
391 if ( $c2->type eq UNIQUE ) {
392 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
394 } elsif ( $target_db =~ /SQLServer/ ) {
395 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
397 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
398 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
402 push @diffs, @diffs_index_drops, @diffs_constraint_drops,
403 @diffs_table_options, @diffs_table_adds,
404 @diffs_table_changes, @diffs_index_creates;
407 for my $t2 ( $target_schema->get_tables ) {
408 my $t2_name = $t2->name;
409 my $t1 = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
412 if ( $target_db =~ /SQLServer/ ) {
413 for my $constraint ( $t2->get_constraints ) {
414 next if $constraint->type eq PRIMARY_KEY;
415 push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
418 push @diffs_at_end, "DROP TABLE $t2_name;";
422 for my $t2_field ( $t2->get_fields ) {
423 my $f2_name = $t2_field->name;
424 my $t1_field = $t1->get_field( $f2_name );
425 unless ( $t1_field ) {
426 my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
427 push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
433 my $dummy_tr = SQL::Translator->new;
434 $dummy_tr->schema->add_table( $_ ) for @new_tables;
435 my $producer = $dummy_tr->producer( $target_db );
436 unshift @diffs, $producer->( $dummy_tr );
438 push(@diffs, @diffs_at_end);
441 if ( $source_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
442 unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
448 "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
453 print "There were no differences.\n";
456 sub constraint_to_string {
458 my $schema = shift or die "No schema given";
459 my @fields = $c->field_names or return '';
461 if ( $c->type eq PRIMARY_KEY ) {
462 if ( $target_db =~ /Oracle/ ) {
463 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
464 'PRIMARY KEY (' . join(', ', @fields). ')';
466 return 'PRIMARY KEY (' . join(', ', @fields). ')';
469 elsif ( $c->type eq UNIQUE ) {
470 if ( $target_db =~ /Oracle/ ) {
471 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
472 'UNIQUE (' . join(', ', @fields). ')';
475 (defined $c->name ? $c->name.' ' : '').
476 '(' . join(', ', @fields). ')';
479 elsif ( $c->type eq FOREIGN_KEY ) {
481 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
484 $def .= ' (' . join( ', ', @fields ) . ')';
486 $def .= ' REFERENCES ' . $c->reference_table;
488 my @rfields = map { $_ || () } $c->reference_fields;
489 unless ( @rfields ) {
490 my $rtable_name = $c->reference_table;
491 if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
492 push @rfields, $ref_table->primary_key;
495 warn "Can't find reference table '$rtable_name' " .
501 $def .= ' (' . join( ', ', @rfields ) . ')';
504 warn "FK constraint on " . 'some table' . '.' .
505 join('', @fields) . " has no reference fields\n";
508 if ( $c->match_type ) {
510 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
513 if ( $c->on_delete ) {
514 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
517 if ( $c->on_update ) {
518 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
525 # -------------------------------------------------------------------
526 # Bring out number weight & measure in a year of dearth.
528 # -------------------------------------------------------------------
534 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
538 SQL::Translator, L<http://sqlfairy.sourceforge.net>.