4 # -------------------------------------------------------------------
5 # $Id: sqlt-diff-old 1440 2009-01-17 16:31:57Z jawnsy $
6 # -------------------------------------------------------------------
7 # Copyright (C) 2002-2009 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 my ( @input, $list, $help, $debug );
100 for my $arg ( @ARGV ) {
101 if ( $arg =~ m/^-?-l(ist)?$/ ) {
104 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
107 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
110 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
111 push @input, { file => $1, parser => $2 };
114 pod2usage( msg => "Unknown argument '$arg'" );
118 pod2usage(1) if $help;
119 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
121 my $tr = SQL::Translator->new;
122 my @parsers = $tr->list_parsers;
123 my %valid_parsers = map { $_, 1 } @parsers;
126 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
131 pod2usage( msg => 'Too many file args' ) if @input > 2;
133 my ( $source_schema, $source_db, $target_schema, $target_db );
136 for my $in ( @input ) {
137 my $file = $in->{'file'};
138 my $parser = $in->{'parser'};
140 die "Unable to read file '$file'\n" unless -r $file;
141 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
143 my $t = SQL::Translator->new;
145 $t->parser( $parser ) or die $tr->error;
146 my $out = $t->translate( $file ) or die $tr->error;
147 my $schema = $t->schema;
148 unless ( $schema->name ) {
149 $schema->name( $file );
153 $source_schema = $schema;
154 $source_db = $parser;
157 $target_schema = $schema;
158 $target_db = $parser;
162 my $case_insensitive = $target_db =~ /SQLServer/;
164 my $s1_name = $source_schema->name;
165 my $s2_name = $target_schema->name;
166 my ( @new_tables, @diffs , @diffs_at_end);
167 for my $t1 ( $source_schema->get_tables ) {
168 my $t1_name = $t1->name;
169 my $t2 = $target_schema->get_table( $t1_name, $case_insensitive );
171 warn "TABLE '$s1_name.$t1_name'\n" if $debug;
173 warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
175 if ( $target_db =~ /(SQLServer|Oracle)/ ) {
176 for my $constraint ( $t1->get_constraints ) {
177 next if $constraint->type ne FOREIGN_KEY;
178 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
179 constraint_to_string($constraint, $source_schema).";";
180 $t1->drop_constraint($constraint);
183 push @new_tables, $t1;
187 # Go through our options
188 my $options_different = 0;
191 for my $t1_option_ref ( $t1->options ) {
192 my($key1, $value1) = %{$t1_option_ref};
193 for my $t2_option_ref ( $t2->options ) {
194 my($key2, $value2) = %{$t2_option_ref};
195 if ( $key1 eq $key2 ) {
196 if ( defined $value1 != defined $value2 ) {
197 $options_different = 1;
200 if ( defined $value1 && $value1 ne $value2 ) {
201 $options_different = 1;
204 $checkedOptions{$key1} = 1;
208 $options_different = 1;
211 # Go through the other table's options
212 unless ( $options_different ) {
213 for my $t2_option_ref ( $t2->options ) {
214 my($key, $value) = %{$t2_option_ref};
215 next if $checkedOptions{$key};
216 $options_different = 1;
220 # If there's a difference, just re-set all the options
221 my @diffs_table_options;
222 if ( $options_different ) {
224 foreach my $option_ref ( $t1->options ) {
225 my($key, $value) = %{$option_ref};
226 push(@options, defined $value ? "$key=$value" : $key);
228 my $options = join(' ', @options);
229 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
232 my $t2_name = $t2->name;
233 my(@diffs_table_adds, @diffs_table_changes);
234 for my $t1_field ( $t1->get_fields ) {
235 my $f1_type = $t1_field->data_type;
236 my $f1_size = $t1_field->size;
237 my $f1_name = $t1_field->name;
238 my $f1_nullable = $t1_field->is_nullable;
239 my $f1_default = $t1_field->default_value;
240 my $f1_auto_inc = $t1_field->is_auto_increment;
241 my $t2_field = $t2->get_field( $f1_name, $case_insensitive );
242 my $f1_full_name = "$s1_name.$t1_name.$t1_name";
243 warn "FIELD '$f1_full_name'\n" if $debug;
245 my $f2_full_name = "$s2_name.$t2_name.$f1_name";
247 unless ( $t2_field ) {
248 warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
250 my $temp_default_value = 0;
251 if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
252 # SQL Server doesn't allow adding non-nullable, non-default columns
253 # so we add it with a default value, then remove the default value
254 $temp_default_value = 1;
255 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
256 $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
258 push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
259 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
261 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
262 !defined $f1_default ? ''
263 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
264 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
265 : " DEFAULT '$f1_default'",
266 $f1_nullable ? '' : ' NOT NULL',
267 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
268 $target_db =~ /Oracle/ ? ')' : '',
270 if ( $temp_default_value ) {
272 push @diffs_table_adds, sprintf( <<END
273 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
276 FROM sysobjects so JOIN sysconstraints sc
277 ON so.id = sc.constid
278 WHERE object_name(so.parent_obj) = '%s'
281 (SELECT colid FROM syscolumns
282 WHERE id = object_id('%s') AND
284 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
288 , $t1_name, $t1_name, $f1_name, $t1_name,
294 my $f2_type = $t2_field->data_type;
295 my $f2_size = $t2_field->size || '';
296 my $f2_nullable = $t2_field->is_nullable;
297 my $f2_default = $t2_field->default_value;
298 my $f2_auto_inc = $t2_field->is_auto_increment;
299 if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
300 # SQLServer timestamp fields can't be altered, so we drop and add instead
301 if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
302 push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
303 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
304 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
306 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
307 !defined $f1_default ? ''
308 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
309 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
310 : " DEFAULT '$f1_default'",
311 $f1_nullable ? '' : ' NOT NULL',
312 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
313 $target_db =~ /Oracle/ ? ')' : '',
318 my $changeText = $target_db =~ /SQLServer/ ? 'ALTER COLUMN' :
319 $target_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
320 my $nullText = $f1_nullable ? '' : ' NOT NULL';
321 $nullText = '' if $target_db =~ /Oracle/ && $f1_nullable == $f2_nullable;
322 push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
323 $t1_name, $changeText,
324 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
325 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
327 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
328 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
329 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
330 : " DEFAULT '$f1_default'",
331 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
332 $target_db =~ /Oracle/ ? ')' : '',
334 if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
335 # Adding a column with a default value for SQL Server means adding a
336 # constraint and setting existing NULLs to the default value
337 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
338 $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
339 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
340 : "DEFAULT '$f1_default'", $f1_name,
342 push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
343 $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
344 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
345 : "'$f1_default'", $f1_name,
351 my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
353 for my $i1 ( $t1->get_indices ) {
354 for my $i2 ( $t2->get_indices ) {
355 if ( $i1->equals($i2, $case_insensitive) ) {
356 $checked_indices{$i2} = 1;
360 push @diffs_index_creates, sprintf(
361 "CREATE %sINDEX%s ON %s (%s);",
362 $i1->type eq NORMAL ? '' : $i1->type." ",
363 $i1->name ? " ".$i1->name : '',
365 join(",", $i1->fields),
369 for my $i2 ( $t2->get_indices ) {
370 next if $checked_indices{$i2};
371 for my $i1 ( $t1->get_indices ) {
372 next INDEX2 if $i2->equals($i1, $case_insensitive);
374 $target_db =~ /SQLServer/
375 ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
376 : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
379 my(%checked_constraints, @diffs_constraint_drops);
381 for my $c1 ( $t1->get_constraints ) {
382 next if $source_db =~ /Oracle/ && $c1->type eq UNIQUE && $c1->name =~ /^SYS_/i;
383 for my $c2 ( $t2->get_constraints ) {
384 if ( $c1->equals($c2, $case_insensitive) ) {
385 $checked_constraints{$c2} = 1;
389 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
390 constraint_to_string($c1, $source_schema).";";
393 for my $c2 ( $t2->get_constraints ) {
394 next if $checked_constraints{$c2};
395 for my $c1 ( $t1->get_constraints ) {
396 next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
398 if ( $c2->type eq UNIQUE ) {
399 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
401 } elsif ( $target_db =~ /SQLServer/ ) {
402 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
404 push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
405 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
409 push @diffs, @diffs_index_drops, @diffs_constraint_drops,
410 @diffs_table_options, @diffs_table_adds,
411 @diffs_table_changes, @diffs_index_creates;
414 for my $t2 ( $target_schema->get_tables ) {
415 my $t2_name = $t2->name;
416 my $t1 = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
419 if ( $target_db =~ /SQLServer/ ) {
420 for my $constraint ( $t2->get_constraints ) {
421 next if $constraint->type eq PRIMARY_KEY;
422 push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
425 push @diffs_at_end, "DROP TABLE $t2_name;";
429 for my $t2_field ( $t2->get_fields ) {
430 my $f2_name = $t2_field->name;
431 my $t1_field = $t1->get_field( $f2_name );
432 unless ( $t1_field ) {
433 my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
434 push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
440 my $dummy_tr = SQL::Translator->new;
441 $dummy_tr->schema->add_table( $_ ) for @new_tables;
442 my $producer = $dummy_tr->producer( $target_db );
443 unshift @diffs, $producer->( $dummy_tr );
445 push(@diffs, @diffs_at_end);
448 if ( $source_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
449 unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
453 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";
457 "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
462 print "There were no differences.\n";
465 sub constraint_to_string {
467 my $schema = shift or die "No schema given";
468 my @fields = $c->field_names or return '';
470 if ( $c->type eq PRIMARY_KEY ) {
471 if ( $target_db =~ /Oracle/ ) {
472 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
473 'PRIMARY KEY (' . join(', ', @fields). ')';
475 return 'PRIMARY KEY (' . join(', ', @fields). ')';
478 elsif ( $c->type eq UNIQUE ) {
479 if ( $target_db =~ /Oracle/ ) {
480 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
481 'UNIQUE (' . join(', ', @fields). ')';
484 (defined $c->name ? $c->name.' ' : '').
485 '(' . join(', ', @fields). ')';
488 elsif ( $c->type eq FOREIGN_KEY ) {
490 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
493 $def .= ' (' . join( ', ', @fields ) . ')';
495 $def .= ' REFERENCES ' . $c->reference_table;
497 my @rfields = map { $_ || () } $c->reference_fields;
498 unless ( @rfields ) {
499 my $rtable_name = $c->reference_table;
500 if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
501 push @rfields, $ref_table->primary_key;
504 warn "Can't find reference table '$rtable_name' " .
510 $def .= ' (' . join( ', ', @rfields ) . ')';
513 warn "FK constraint on " . 'some table' . '.' .
514 join('', @fields) . " has no reference fields\n";
517 if ( $c->match_type ) {
519 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
522 if ( $c->on_delete ) {
523 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
526 if ( $c->on_update ) {
527 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
534 # -------------------------------------------------------------------
535 # Bring out number weight & measure in a year of dearth.
537 # -------------------------------------------------------------------
543 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
547 SQL::Translator, L<http://sqlfairy.sourceforge.net>.