bin/sqlt-diff

   1 #!/usr/bin/perl -w
   2 # vim: set ft=perl:
   3
   4 # -------------------------------------------------------------------
   5 # $Id: sqlt-diff,v 1.8 2005-06-28 23:05:47 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.8 $ =~ /(\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     $t->producer( 'YAML' )           or die $tr->error;
 149     my $out = $t->translate( $file ) or die $tr->error;
 150     my $schema = $t->schema;
 151     unless ( $schema->name ) {
 152         $schema->name( $file );
 153     }
 154
 155     if ( $i == 1 ) {
 156         $source_schema = $schema;
 157         $source_db     = $parser;
 158     }
 159     else {
 160         $target_schema = $schema;
 161         $target_db     = $parser;
 162     }
 163     $i--;
 164 }
 165 my $case_insensitive = $target_db =~ /SQLServer/;
 166
 167 my $s1_name  = $source_schema->name;
 168 my $s2_name  = $target_schema->name;
 169 my ( @new_tables, @diffs , @diffs_at_end);
 170 for my $t1 ( $source_schema->get_tables ) {
 171     my $t1_name = $t1->name;
 172     my $t2      = $target_schema->get_table( $t1_name, $case_insensitive );
 173
 174     warn "TABLE '$s1_name.$t1_name'\n" if $debug;
 175     unless ( $t2 ) {
 176         warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
 177             if $debug;
 178         if ( $target_db =~ /SQLServer/ ) {
 179                         for my $constraint ( $t1->get_constraints ) {
 180                                 next if $constraint->type eq PRIMARY_KEY;
 181                                 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
 182                                         constraint_to_string($constraint, $source_schema).";";
 183                                 $t1->drop_constraint($constraint);
 184                         }
 185         }
 186         push @new_tables, $t1;
 187         next;
 188     }
 189
 190     # Go through our options
 191         my $options_different = 0;
 192         my %checkedOptions;
 193 OPTION:
 194         for my $t1_option_ref ( $t1->options ) {
 195                 my($key1, $value1) = %{$t1_option_ref};
 196                 for my $t2_option_ref ( $t2->options ) {
 197                         my($key2, $value2) = %{$t2_option_ref};
 198                         if ( $key1 eq $key2 ) {
 199                                 if ( defined $value1 != defined $value2 ) {
 200                                         $options_different = 1;
 201                                         last OPTION;
 202                                 }
 203                                 if ( defined $value1 && $value1 ne $value2 ) {
 204                                         $options_different = 1;
 205                                         last OPTION;
 206                                 }
 207                                 $checkedOptions{$key1} = 1;
 208                                 next OPTION;
 209                         }
 210                 }
 211                 $options_different = 1;
 212                 last OPTION;
 213         }
 214     # Go through the other table's options
 215     unless ( $options_different ) {
 216             for my $t2_option_ref ( $t2->options ) {
 217                 my($key, $value) = %{$t2_option_ref};
 218                 next if $checkedOptions{$key};
 219                 $options_different = 1;
 220                 last;
 221             }
 222     }
 223     # If there's a difference, just re-set all the options
 224     my @diffs_table_options;
 225     if ( $options_different ) {
 226         my @options = ();
 227         foreach my $option_ref ( $t1->options ) {
 228                 my($key, $value) = %{$option_ref};
 229                 push(@options, defined $value ? "$key=$value" : $key);
 230         }
 231         my $options = join(' ', @options);
 232                 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
 233     }
 234
 235     my $t2_name = $t2->name;
 236     my(@diffs_table_adds, @diffs_table_changes);
 237     for my $t1_field ( $t1->get_fields ) {
 238         my $f1_type      = $t1_field->data_type;
 239         my $f1_size      = $t1_field->size;
 240         my $f1_name      = $t1_field->name;
 241         my $f1_nullable  = $t1_field->is_nullable;
 242         my $f1_default   = $t1_field->default_value;
 243         my $f1_auto_inc  = $t1_field->is_auto_increment;
 244         my $t2_field     = $t2->get_field( $f1_name );
 245         my $f1_full_name = "$s1_name.$t1_name.$t1_name";
 246         warn "FIELD '$f1_full_name'\n" if $debug;
 247
 248         my $f2_full_name = "$s2_name.$t2_name.$f1_name";
 249
 250         unless ( $t2_field ) {
 251             warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
 252                 if $debug;
 253             my $temp_default_value = 0;
 254             if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
 255                 # SQL Server doesn't allow adding non-nullable, non-default columns
 256                 # so we add it with a default value, then remove the default value
 257                 $temp_default_value = 1;
 258                 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
 259                 $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
 260             }
 261             push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s %s%s%s%s%s;",
 262                 $t1_name, $f1_name, $f1_type,
 263                 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 264                 $f1_nullable ? '' : ' NOT NULL',
 265                 !defined $f1_default ? ''
 266                         : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
 267                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
 268                         : " DEFAULT '$f1_default'",
 269                 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
 270             );
 271             if ( $temp_default_value ) {
 272                 undef $f1_default;
 273                     push @diffs_table_adds, sprintf( <<END
 274 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
 275 SET \@defname =
 276 (SELECT name
 277 FROM sysobjects so JOIN sysconstraints sc
 278 ON so.id = sc.constid
 279 WHERE object_name(so.parent_obj) = '%s'
 280 AND so.xtype = 'D'
 281 AND sc.colid =
 282  (SELECT colid FROM syscolumns
 283  WHERE id = object_id('%s') AND
 284  name = '%s'))
 285 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
 286 + \@defname
 287 EXEC(\@cmd)
 288 END
 289                                         , $t1_name, $t1_name, $f1_name, $t1_name,
 290                     );
 291             }
 292             next;
 293         }
 294
 295         my $f2_type = $t2_field->data_type;
 296         my $f2_size = $t2_field->size || '';
 297         my $f2_nullable  = $t2_field->is_nullable;
 298         my $f2_default   = $t2_field->default_value;
 299         my $f2_auto_inc  = $t2_field->is_auto_increment;
 300         if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
 301                 # SQLServer timestamp fields can't be altered, so we drop and add instead
 302                 if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
 303                         push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
 304                     push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s %s%s%s%s%s;",
 305                         $t1_name, $f1_name, $f1_type,
 306                         ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 307                         $f1_nullable ? '' : ' NOT NULL',
 308                         !defined $f1_default ? ''
 309                                 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
 310                                 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
 311                                 : " DEFAULT '$f1_default'",
 312                         $f1_auto_inc ? ' AUTO_INCREMENT' : '',
 313                     );
 314                     next;
 315                 }
 316
 317             push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s;",
 318                 $t1_name, $target_db =~ /SQLServer/ ? "ALTER COLUMN" : "CHANGE",
 319                 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
 320                 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 321                 $f1_nullable ? '' : ' NOT NULL',
 322                 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
 323                         : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
 324                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
 325                         : " DEFAULT '$f1_default'",
 326                 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
 327             );
 328             if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
 329                 # Adding a column with a default value for SQL Server means adding a
 330                 # constraint and setting existing NULLs to the default value
 331                 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
 332                         $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
 333                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
 334                         : "DEFAULT '$f1_default'", $f1_name,
 335                 );
 336                 push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
 337                         $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
 338                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
 339                         : "'$f1_default'", $f1_name,
 340                 );
 341             }
 342         }
 343     }
 344
 345         my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
 346 INDEX:
 347         for my $i1 ( $t1->get_indices ) {
 348                 for my $i2 ( $t2->get_indices ) {
 349                         if ( $i1->equals($i2, $case_insensitive) ) {
 350                                 $checked_indices{$i2} = 1;
 351                                 next INDEX;
 352                         }
 353                 }
 354                 push @diffs_index_creates, sprintf(
 355                 "CREATE %sINDEX%s ON %s (%s);",
 356                 $i1->type eq NORMAL ? '' : $i1->type." ",
 357                 $i1->name ? " ".$i1->name : '',
 358                 $t1_name,
 359                 join(",", $i1->fields),
 360             );
 361         }
 362 INDEX2:
 363         for my $i2 ( $t2->get_indices ) {
 364                 next if $checked_indices{$i2};
 365                 for my $i1 ( $t1->get_indices ) {
 366                         next INDEX2 if $i2->equals($i1, $case_insensitive);
 367                 }
 368                 $target_db =~ /SQLServer/
 369                         ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
 370                         : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
 371         }
 372
 373         my(%checked_constraints, @diffs_constraint_adds, @diffs_constraint_drops);
 374 CONSTRAINT:
 375         for my $c1 ( $t1->get_constraints ) {
 376                 for my $c2 ( $t2->get_constraints ) {
 377                         if ( $c1->equals($c2, $case_insensitive) ) {
 378                                 $checked_constraints{$c2} = 1;
 379                                 next CONSTRAINT;
 380                         }
 381                 }
 382                 push @diffs_constraint_adds, "ALTER TABLE $t1_name ADD ".
 383                         constraint_to_string($c1, $source_schema).";";
 384         }
 385 CONSTRAINT2:
 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);
 390                 }
 391                 if ( $c2->type eq UNIQUE ) {
 392                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
 393                                 $c2->name.";";
 394                 } elsif ( $target_db =~ /SQLServer/ ) {
 395                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
 396                 } else {
 397                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
 398                                 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
 399                 }
 400         }
 401
 402         push @diffs, @diffs_index_drops, @diffs_constraint_drops,
 403                 @diffs_table_options, @diffs_table_adds, @diffs_table_changes,
 404                 @diffs_constraint_adds, @diffs_index_creates;
 405 }
 406
 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/ );
 410
 411     unless ( $t1 ) {
 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.";";
 416                         }
 417         }
 418         push @diffs_at_end, "DROP TABLE $t2_name;";
 419         next;
 420     }
 421
 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;";
 428         }
 429     }
 430 }
 431
 432 if ( @new_tables ) {
 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 );
 437 }
 438 push(@diffs, @diffs_at_end);
 439
 440 if ( @diffs ) {
 441     print join( "\n",
 442         "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, ''
 443     );
 444 }
 445 else {
 446     print "There were no differences.\n";
 447 }
 448
 449 sub constraint_to_string {
 450         my $c = shift;
 451         my $schema = shift or die "No schema given";
 452         my @fields = $c->fields or return '';
 453
 454         if ( $c->type eq PRIMARY_KEY ) {
 455                 return 'PRIMARY KEY (' . join(', ', @fields). ')';
 456         }
 457         elsif ( $c->type eq UNIQUE ) {
 458                 return 'UNIQUE '.
 459                         (defined $c->name ? $c->name.' ' : '').
 460                         '(' . join(', ', @fields). ')';
 461         }
 462         elsif ( $c->type eq FOREIGN_KEY ) {
 463                 my $def = join(' ',
 464                         map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
 465                 );
 466
 467                 $def .= ' (' . join( ', ', @fields ) . ')';
 468
 469                 $def .= ' REFERENCES ' . $c->reference_table;
 470
 471                 my @rfields = map { $_ || () } $c->reference_fields;
 472                 unless ( @rfields ) {
 473                         my $rtable_name = $c->reference_table;
 474                         if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
 475                                 push @rfields, $ref_table->primary_key;
 476                         }
 477                         else {
 478                                 warn "Can't find reference table '$rtable_name' " .
 479                                         "in schema\n";
 480                         }
 481                 }
 482
 483                 if ( @rfields ) {
 484                         $def .= ' (' . join( ', ', @rfields ) . ')';
 485                 }
 486                 else {
 487                         warn "FK constraint on " . 'some table' . '.' .
 488                                 join('', @fields) . " has no reference fields\n";
 489                 }
 490
 491                 if ( $c->match_type ) {
 492                         $def .= ' MATCH ' .
 493                                 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 494                 }
 495
 496                 if ( $c->on_delete ) {
 497                         $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 498                 }
 499
 500                 if ( $c->on_update ) {
 501                         $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 502                 }
 503
 504                 return $def;
 505         }
 506 }
 507
 508 # -------------------------------------------------------------------
 509 # Bring out number weight & measure in a year of dearth.
 510 # William Blake
 511 # -------------------------------------------------------------------
 512
 513 =pod
 514
 515 =head1 AUTHOR
 516
 517 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
 518
 519 =head1 SEE ALSO
 520
 521 SQL::Translator, L<http://sqlfairy.sourceforge.net>.
 522
 523 =cut