bin/sqlt-diff

   1 #!/usr/bin/perl -w
   2 # vim: set ft=perl:
   3
   4 # -------------------------------------------------------------------
   5 # $Id: sqlt-diff,v 1.10 2005-07-15 23:36:13 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.10 $ =~ /(\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/ ) {
 178                         for my $constraint ( $t1->get_constraints ) {
 179                                 next if $constraint->type eq PRIMARY_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;",
 261                 $t1_name, $f1_name, $f1_type,
 262                 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 263                 $f1_nullable ? '' : ' NOT NULL',
 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_auto_inc ? ' AUTO_INCREMENT' : '',
 269             );
 270             if ( $temp_default_value ) {
 271                 undef $f1_default;
 272                     push @diffs_table_adds, sprintf( <<END
 273 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
 274 SET \@defname =
 275 (SELECT name
 276 FROM sysobjects so JOIN sysconstraints sc
 277 ON so.id = sc.constid
 278 WHERE object_name(so.parent_obj) = '%s'
 279 AND so.xtype = 'D'
 280 AND sc.colid =
 281  (SELECT colid FROM syscolumns
 282  WHERE id = object_id('%s') AND
 283  name = '%s'))
 284 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
 285 + \@defname
 286 EXEC(\@cmd)
 287 END
 288                                         , $t1_name, $t1_name, $f1_name, $t1_name,
 289                     );
 290             }
 291             next;
 292         }
 293
 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;",
 304                         $t1_name, $f1_name, $f1_type,
 305                         ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 306                         $f1_nullable ? '' : ' NOT NULL',
 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_auto_inc ? ' AUTO_INCREMENT' : '',
 312                     );
 313                     next;
 314                 }
 315
 316             push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s;",
 317                 $t1_name, $target_db =~ /SQLServer/ ? "ALTER COLUMN" : "CHANGE",
 318                 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
 319                 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 320                 $f1_nullable ? '' : ' NOT NULL',
 321                 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
 322                         : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
 323                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
 324                         : " DEFAULT '$f1_default'",
 325                 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
 326             );
 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,
 334                 );
 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,
 339                 );
 340             }
 341         }
 342     }
 343
 344         my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
 345 INDEX:
 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;
 350                                 next INDEX;
 351                         }
 352                 }
 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 : '',
 357                 $t1_name,
 358                 join(",", $i1->fields),
 359             );
 360         }
 361 INDEX2:
 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);
 366                 }
 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;";
 370         }
 371
 372         my(%checked_constraints, @diffs_constraint_adds, @diffs_constraint_drops);
 373 CONSTRAINT:
 374         for my $c1 ( $t1->get_constraints ) {
 375                 for my $c2 ( $t2->get_constraints ) {
 376                         if ( $c1->equals($c2, $case_insensitive) ) {
 377                                 $checked_constraints{$c2} = 1;
 378                                 next CONSTRAINT;
 379                         }
 380                 }
 381                 push @diffs_constraint_adds, "ALTER TABLE $t1_name ADD ".
 382                         constraint_to_string($c1, $source_schema).";";
 383         }
 384 CONSTRAINT2:
 385         for my $c2 ( $t2->get_constraints ) {
 386                 next if $checked_constraints{$c2};
 387                 for my $c1 ( $t1->get_constraints ) {
 388                         next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
 389                 }
 390                 if ( $c2->type eq UNIQUE ) {
 391                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
 392                                 $c2->name.";";
 393                 } elsif ( $target_db =~ /SQLServer/ ) {
 394                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
 395                 } else {
 396                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
 397                                 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
 398                 }
 399         }
 400
 401         push @diffs, @diffs_index_drops, @diffs_constraint_drops,
 402                 @diffs_table_options, @diffs_table_adds, @diffs_table_changes,
 403                 @diffs_constraint_adds, @diffs_index_creates;
 404 }
 405
 406 for my $t2 ( $target_schema->get_tables ) {
 407     my $t2_name = $t2->name;
 408     my $t1      = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
 409
 410     unless ( $t1 ) {
 411         if ( $target_db =~ /SQLServer/ ) {
 412                         for my $constraint ( $t2->get_constraints ) {
 413                                 next if $constraint->type eq PRIMARY_KEY;
 414                                 push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
 415                         }
 416         }
 417         push @diffs_at_end, "DROP TABLE $t2_name;";
 418         next;
 419     }
 420
 421     for my $t2_field ( $t2->get_fields ) {
 422         my $f2_name      = $t2_field->name;
 423         my $t1_field     = $t1->get_field( $f2_name );
 424         unless ( $t1_field ) {
 425                 my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
 426             push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
 427         }
 428     }
 429 }
 430
 431 if ( @new_tables ) {
 432     my $dummy_tr = SQL::Translator->new;
 433     $dummy_tr->schema->add_table( $_ ) for @new_tables;
 434     my $producer = $dummy_tr->producer( $target_db );
 435     unshift @diffs, $producer->( $dummy_tr );
 436 }
 437 push(@diffs, @diffs_at_end);
 438
 439 if ( @diffs ) {
 440     print join( "\n",
 441         "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, ''
 442     );
 443 }
 444 else {
 445     print "There were no differences.\n";
 446 }
 447
 448 sub constraint_to_string {
 449         my $c = shift;
 450         my $schema = shift or die "No schema given";
 451         my @fields = $c->fields or return '';
 452
 453         if ( $c->type eq PRIMARY_KEY ) {
 454                 return 'PRIMARY KEY (' . join(', ', @fields). ')';
 455         }
 456         elsif ( $c->type eq UNIQUE ) {
 457                 return 'UNIQUE '.
 458                         (defined $c->name ? $c->name.' ' : '').
 459                         '(' . join(', ', @fields). ')';
 460         }
 461         elsif ( $c->type eq FOREIGN_KEY ) {
 462                 my $def = join(' ',
 463                         map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
 464                 );
 465
 466                 $def .= ' (' . join( ', ', @fields ) . ')';
 467
 468                 $def .= ' REFERENCES ' . $c->reference_table;
 469
 470                 my @rfields = map { $_ || () } $c->reference_fields;
 471                 unless ( @rfields ) {
 472                         my $rtable_name = $c->reference_table;
 473                         if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
 474                                 push @rfields, $ref_table->primary_key;
 475                         }
 476                         else {
 477                                 warn "Can't find reference table '$rtable_name' " .
 478                                         "in schema\n";
 479                         }
 480                 }
 481
 482                 if ( @rfields ) {
 483                         $def .= ' (' . join( ', ', @rfields ) . ')';
 484                 }
 485                 else {
 486                         warn "FK constraint on " . 'some table' . '.' .
 487                                 join('', @fields) . " has no reference fields\n";
 488                 }
 489
 490                 if ( $c->match_type ) {
 491                         $def .= ' MATCH ' .
 492                                 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 493                 }
 494
 495                 if ( $c->on_delete ) {
 496                         $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 497                 }
 498
 499                 if ( $c->on_update ) {
 500                         $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 501                 }
 502
 503                 return $def;
 504         }
 505 }
 506
 507 # -------------------------------------------------------------------
 508 # Bring out number weight & measure in a year of dearth.
 509 # William Blake
 510 # -------------------------------------------------------------------
 511
 512 =pod
 513
 514 =head1 AUTHOR
 515
 516 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
 517
 518 =head1 SEE ALSO
 519
 520 SQL::Translator, L<http://sqlfairy.sourceforge.net>.
 521
 522 =cut