bin/sqlt-diff

   1 #!/usr/bin/perl -w
   2 # vim: set ft=perl:
   3
   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
   8 #
   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.
  12 #
  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.
  17 #
  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
  21 # 02111-1307  USA
  22 # -------------------------------------------------------------------
  23
  24 =head1 NAME
  25
  26 sqlt-diff - find the differences b/w two schemas
  27
  28 =head1 SYNOPSIS
  29
  30 For help:
  31
  32   sqlt-diff -h|--help
  33
  34 For a list of all valid parsers:
  35
  36   sqlt -l|--list
  37
  38 To diff two schemas:
  39
  40   sqlt-diff [options] file_name1=parser file_name2=parser
  41
  42 Options:
  43
  44   -d|--debug   Show debugging info
  45
  46 =head1 DESCRIPTION
  47
  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
  51 following
  52
  53 =over
  54
  55 =item * New tables
  56
  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,
  59 indices).
  60
  61 =item * Missing/altered fields
  62
  63 Any fields missing or altered between the two schemas will be reported
  64 as:
  65
  66   ALTER TABLE <table_name>
  67     [DROP <field_name>]
  68     [CHANGE <field_name> <datatype> (<size>)] ;
  69
  70 =item * Missing/altered indices
  71
  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:
  74
  75   DROP INDEX <index_name> ON <table_name> ;
  76
  77 An index of a different type or on different fields will be reported as a
  78 new index as such:
  79
  80   CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
  81     ( <field_name>[,<field_name>] ) ;
  82
  83 =back
  84
  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
  87 your target database.
  88
  89 =cut
  90
  91 # -------------------------------------------------------------------
  92
  93 use strict;
  94 use Pod::Usage;
  95 use Data::Dumper;
  96 use SQL::Translator;
  97 use SQL::Translator::Schema::Constants;
  98
  99 use vars qw( $VERSION );
 100 $VERSION = sprintf "%d.%02d", q$Revision: 1.12 $ =~ /(\d+)\.(\d+)/;
 101
 102 my ( @input, $list, $help, $debug );
 103 for my $arg ( @ARGV ) {
 104     if ( $arg =~ m/^-?-l(ist)?$/ ) {
 105         $list = 1;
 106     }
 107     elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
 108         $help = 1;
 109     }
 110     elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
 111         $debug = 1;
 112     }
 113     elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
 114         push @input, { file => $1, parser => $2 };
 115     }
 116     else {
 117         pod2usage( msg => "Unknown argument '$arg'" );
 118     }
 119 }
 120
 121 pod2usage(1) if $help;
 122 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
 123
 124 my $tr            = SQL::Translator->new;
 125 my @parsers       = $tr->list_parsers;
 126 my %valid_parsers = map { $_, 1 } @parsers;
 127
 128 if ( $list ) {
 129     print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
 130     print "\n";
 131     exit(0);
 132 }
 133
 134 pod2usage( msg => 'Too many file args' ) if @input > 2;
 135
 136 my ( $source_schema, $source_db, $target_schema, $target_db );
 137 my $i = 2;
 138 for my $in ( @input ) {
 139     my $file   = $in->{'file'};
 140     my $parser = $in->{'parser'};
 141
 142     die "Unable to read file '$file'\n" unless -r $file;
 143     die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
 144
 145     my $t = SQL::Translator->new;
 146     $t->debug( $debug );
 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 );
 152     }
 153
 154     if ( $i == 1 ) {
 155         $source_schema = $schema;
 156         $source_db     = $parser;
 157     }
 158     else {
 159         $target_schema = $schema;
 160         $target_db     = $parser;
 161     }
 162     $i--;
 163 }
 164 my $case_insensitive = $target_db =~ /SQLServer/;
 165
 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 );
 172
 173     warn "TABLE '$s1_name.$t1_name'\n" if $debug;
 174     unless ( $t2 ) {
 175         warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
 176             if $debug;
 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);
 183                         }
 184         }
 185         push @new_tables, $t1;
 186         next;
 187     }
 188
 189     # Go through our options
 190         my $options_different = 0;
 191         my %checkedOptions;
 192 OPTION:
 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;
 200                                         last OPTION;
 201                                 }
 202                                 if ( defined $value1 && $value1 ne $value2 ) {
 203                                         $options_different = 1;
 204                                         last OPTION;
 205                                 }
 206                                 $checkedOptions{$key1} = 1;
 207                                 next OPTION;
 208                         }
 209                 }
 210                 $options_different = 1;
 211                 last OPTION;
 212         }
 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;
 219                 last;
 220             }
 221     }
 222     # If there's a difference, just re-set all the options
 223     my @diffs_table_options;
 224     if ( $options_different ) {
 225         my @options = ();
 226         foreach my $option_ref ( $t1->options ) {
 227                 my($key, $value) = %{$option_ref};
 228                 push(@options, defined $value ? "$key=$value" : $key);
 229         }
 230         my $options = join(' ', @options);
 231                 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
 232     }
 233
 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;
 246
 247         my $f2_full_name = "$s2_name.$t2_name.$f1_name";
 248
 249         unless ( $t2_field ) {
 250             warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
 251                 if $debug;
 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 : '';
 259             }
 260             push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
 261                 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
 262                 $f1_name, $f1_type,
 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/ ? ')' : '',
 271             );
 272             if ( $temp_default_value ) {
 273                 undef $f1_default;
 274                     push @diffs_table_adds, sprintf( <<END
 275 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
 276 SET \@defname =
 277 (SELECT name
 278 FROM sysobjects so JOIN sysconstraints sc
 279 ON so.id = sc.constid
 280 WHERE object_name(so.parent_obj) = '%s'
 281 AND so.xtype = 'D'
 282 AND sc.colid =
 283  (SELECT colid FROM syscolumns
 284  WHERE id = object_id('%s') AND
 285  name = '%s'))
 286 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
 287 + \@defname
 288 EXEC(\@cmd)
 289 END
 290                                         , $t1_name, $t1_name, $f1_name, $t1_name,
 291                     );
 292             }
 293             next;
 294         }
 295
 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/ ? '(' : '',
 307                         $f1_name, $f1_type,
 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/ ? ')' : '',
 316                     );
 317                     next;
 318                 }
 319
 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)" : '',
 328                 $nullText,
 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/ ? ')' : '',
 335             );
 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,
 343                 );
 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,
 348                 );
 349             }
 350         }
 351     }
 352
 353         my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
 354 INDEX:
 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;
 359                                 next INDEX;
 360                         }
 361                 }
 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 : '',
 366                 $t1_name,
 367                 join(",", $i1->fields),
 368             );
 369         }
 370 INDEX2:
 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);
 375                 }
 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;";
 379         }
 380
 381         my(%checked_constraints, @diffs_constraint_drops);
 382 CONSTRAINT:
 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;
 388                                 next CONSTRAINT;
 389                         }
 390                 }
 391                 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
 392                         constraint_to_string($c1, $source_schema).";";
 393         }
 394 CONSTRAINT2:
 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);
 399                 }
 400                 if ( $c2->type eq UNIQUE ) {
 401                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
 402                                 $c2->name.";";
 403                 } elsif ( $target_db =~ /SQLServer/ ) {
 404                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
 405                 } else {
 406                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
 407                                 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
 408                 }
 409         }
 410
 411         push @diffs, @diffs_index_drops, @diffs_constraint_drops,
 412                 @diffs_table_options, @diffs_table_adds,
 413                 @diffs_table_changes, @diffs_index_creates;
 414 }
 415
 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/ );
 419
 420     unless ( $t1 ) {
 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.";";
 425                         }
 426         }
 427         push @diffs_at_end, "DROP TABLE $t2_name;";
 428         next;
 429     }
 430
 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;";
 437         }
 438     }
 439 }
 440
 441 if ( @new_tables ) {
 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 );
 446 }
 447 push(@diffs, @diffs_at_end);
 448
 449 if ( @diffs ) {
 450     print join( "\n",
 451         "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
 452     );
 453     exit(1);
 454 }
 455 else {
 456     print "There were no differences.\n";
 457 }
 458
 459 sub constraint_to_string {
 460         my $c = shift;
 461         my $schema = shift or die "No schema given";
 462         my @fields = $c->field_names or return '';
 463
 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). ')';
 468                 } else {
 469                         return 'PRIMARY KEY (' . join(', ', @fields). ')';
 470                 }
 471         }
 472         elsif ( $c->type eq UNIQUE ) {
 473                 if ( $target_db =~ /Oracle/ ) {
 474                         return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
 475                                 'UNIQUE (' . join(', ', @fields). ')';
 476                 } else {
 477                         return 'UNIQUE '.
 478                                 (defined $c->name ? $c->name.' ' : '').
 479                                 '(' . join(', ', @fields). ')';
 480                 }
 481         }
 482         elsif ( $c->type eq FOREIGN_KEY ) {
 483                 my $def = join(' ',
 484                         map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
 485                 );
 486
 487                 $def .= ' (' . join( ', ', @fields ) . ')';
 488
 489                 $def .= ' REFERENCES ' . $c->reference_table;
 490
 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;
 496                         }
 497                         else {
 498                                 warn "Can't find reference table '$rtable_name' " .
 499                                         "in schema\n";
 500                         }
 501                 }
 502
 503                 if ( @rfields ) {
 504                         $def .= ' (' . join( ', ', @rfields ) . ')';
 505                 }
 506                 else {
 507                         warn "FK constraint on " . 'some table' . '.' .
 508                                 join('', @fields) . " has no reference fields\n";
 509                 }
 510
 511                 if ( $c->match_type ) {
 512                         $def .= ' MATCH ' .
 513                                 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 514                 }
 515
 516                 if ( $c->on_delete ) {
 517                         $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 518                 }
 519
 520                 if ( $c->on_update ) {
 521                         $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 522                 }
 523
 524                 return $def;
 525         }
 526 }
 527
 528 # -------------------------------------------------------------------
 529 # Bring out number weight & measure in a year of dearth.
 530 # William Blake
 531 # -------------------------------------------------------------------
 532
 533 =pod
 534
 535 =head1 AUTHOR
 536
 537 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
 538
 539 =head1 SEE ALSO
 540
 541 SQL::Translator, L<http://sqlfairy.sourceforge.net>.
 542
 543 =cut