script/sqlt-diff-old

   1 #!/usr/bin/env perl
   2 # vim: set ft=perl:
   3
   4 # -------------------------------------------------------------------
   5 # Copyright (C) 2002-2009 The SQLFairy Authors
   6 #
   7 # This program is free software; you can redistribute it and/or
   8 # modify it under the terms of the GNU General Public License as
   9 # published by the Free Software Foundation; version 2.
  10 #
  11 # This program is distributed in the hope that it will be useful, but
  12 # WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14 # General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License
  17 # along with this program; if not, write to the Free Software
  18 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
  19 # 02111-1307  USA
  20 # -------------------------------------------------------------------
  21
  22 =head1 NAME
  23
  24 sqlt-diff - find the differences b/w two schemas
  25
  26 =head1 SYNOPSIS
  27
  28 For help:
  29
  30   sqlt-diff -h|--help
  31
  32 For a list of all valid parsers:
  33
  34   sqlt -l|--list
  35
  36 To diff two schemas:
  37
  38   sqlt-diff [options] file_name1=parser file_name2=parser
  39
  40 Options:
  41
  42   -d|--debug   Show debugging info
  43
  44 =head1 DESCRIPTION
  45
  46 sqlt-diff is a utility for creating a file of SQL commands necessary to
  47 transform the first schema provided to the second.  While not yet
  48 exhaustive in its ability to mutate the entire schema, it will report the
  49 following
  50
  51 =over
  52
  53 =item * New tables
  54
  55 Using the Producer class of the target (second) schema, any tables missing
  56 in the first schema will be generated in their entirety (fields, constraints,
  57 indices).
  58
  59 =item * Missing/altered fields
  60
  61 Any fields missing or altered between the two schemas will be reported
  62 as:
  63
  64   ALTER TABLE <table_name>
  65     [DROP <field_name>]
  66     [CHANGE <field_name> <datatype> (<size>)] ;
  67
  68 =item * Missing/altered indices
  69
  70 Any indices missing or of a different type or on different fields will be
  71 indicated.  Indices that should be dropped will be reported as such:
  72
  73   DROP INDEX <index_name> ON <table_name> ;
  74
  75 An index of a different type or on different fields will be reported as a
  76 new index as such:
  77
  78   CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
  79     ( <field_name>[,<field_name>] ) ;
  80
  81 =back
  82
  83 "ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
  84 the Producer, unfortunately, and may require massaging before being passed to
  85 your target database.
  86
  87 =cut
  88
  89 # -------------------------------------------------------------------
  90
  91 use strict;
  92 use warnings;
  93 use Pod::Usage;
  94 use Data::Dumper;
  95 use SQL::Translator;
  96 use SQL::Translator::Schema::Constants;
  97
  98 use vars qw( $VERSION );
  99 $VERSION = '1.60';
 100
 101 my ( @input, $list, $help, $debug );
 102 for my $arg ( @ARGV ) {
 103     if ( $arg =~ m/^-?-l(ist)?$/ ) {
 104         $list = 1;
 105     }
 106     elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
 107         $help = 1;
 108     }
 109     elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
 110         $debug = 1;
 111     }
 112     elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
 113         push @input, { file => $1, parser => $2 };
 114     }
 115     else {
 116         pod2usage( msg => "Unknown argument '$arg'" );
 117     }
 118 }
 119
 120 pod2usage(1) if $help;
 121 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
 122 pod2usage('No input') if !@input;
 123
 124 if ( my $interactive = -t STDIN && -t STDOUT ) {
 125     print STDERR join("\n",
 126         "sqlt-diff-old is deprecated. Please sqlt-diff, and tell us ",
 127         "about any problems or patch SQL::Translator::Diff",
 128         '',
 129     );
 130 }
 131
 132 my $tr            = SQL::Translator->new;
 133 my @parsers       = $tr->list_parsers;
 134 my %valid_parsers = map { $_, 1 } @parsers;
 135
 136 if ( $list ) {
 137     print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
 138     print "\n";
 139     exit(0);
 140 }
 141
 142 pod2usage( msg => 'Too many file args' ) if @input > 2;
 143
 144 my ( $source_schema, $source_db, $target_schema, $target_db );
 145
 146 my $i = 2;
 147 for my $in ( @input ) {
 148     my $file   = $in->{'file'};
 149     my $parser = $in->{'parser'};
 150
 151     die "Unable to read file '$file'\n" unless -r $file;
 152     die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
 153
 154     my $t = SQL::Translator->new;
 155     $t->debug( $debug );
 156     $t->parser( $parser )            or die $tr->error;
 157     my $out = $t->translate( $file ) or die $tr->error;
 158     my $schema = $t->schema;
 159     unless ( $schema->name ) {
 160         $schema->name( $file );
 161     }
 162
 163     if ( $i == 1 ) {
 164         $source_schema = $schema;
 165         $source_db     = $parser;
 166     }
 167     else {
 168         $target_schema = $schema;
 169         $target_db     = $parser;
 170     }
 171     $i--;
 172 }
 173 my $case_insensitive = $target_db =~ /SQLServer/;
 174
 175 my $s1_name  = $source_schema->name;
 176 my $s2_name  = $target_schema->name;
 177 my ( @new_tables, @diffs , @diffs_at_end);
 178 for my $t1 ( $source_schema->get_tables ) {
 179     my $t1_name = $t1->name;
 180     my $t2      = $target_schema->get_table( $t1_name, $case_insensitive );
 181
 182     warn "TABLE '$s1_name.$t1_name'\n" if $debug;
 183     unless ( $t2 ) {
 184         warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
 185             if $debug;
 186         if ( $target_db =~ /(SQLServer|Oracle)/ ) {
 187                         for my $constraint ( $t1->get_constraints ) {
 188                                 next if $constraint->type ne FOREIGN_KEY;
 189                                 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
 190                                         constraint_to_string($constraint, $source_schema).";";
 191                                 $t1->drop_constraint($constraint);
 192                         }
 193         }
 194         push @new_tables, $t1;
 195         next;
 196     }
 197
 198     # Go through our options
 199         my $options_different = 0;
 200         my %checkedOptions;
 201 OPTION:
 202         for my $t1_option_ref ( $t1->options ) {
 203                 my($key1, $value1) = %{$t1_option_ref};
 204                 for my $t2_option_ref ( $t2->options ) {
 205                         my($key2, $value2) = %{$t2_option_ref};
 206                         if ( $key1 eq $key2 ) {
 207                                 if ( defined $value1 != defined $value2 ) {
 208                                         $options_different = 1;
 209                                         last OPTION;
 210                                 }
 211                                 if ( defined $value1 && $value1 ne $value2 ) {
 212                                         $options_different = 1;
 213                                         last OPTION;
 214                                 }
 215                                 $checkedOptions{$key1} = 1;
 216                                 next OPTION;
 217                         }
 218                 }
 219                 $options_different = 1;
 220                 last OPTION;
 221         }
 222     # Go through the other table's options
 223     unless ( $options_different ) {
 224             for my $t2_option_ref ( $t2->options ) {
 225                 my($key, $value) = %{$t2_option_ref};
 226                 next if $checkedOptions{$key};
 227                 $options_different = 1;
 228                 last;
 229             }
 230     }
 231     # If there's a difference, just re-set all the options
 232     my @diffs_table_options;
 233     if ( $options_different ) {
 234         my @options = ();
 235         foreach my $option_ref ( $t1->options ) {
 236                 my($key, $value) = %{$option_ref};
 237                 push(@options, defined $value ? "$key=$value" : $key);
 238         }
 239         my $options = join(' ', @options);
 240                 @diffs_table_options = ("ALTER TABLE $t1_name $options;");
 241     }
 242
 243     my $t2_name = $t2->name;
 244     my(@diffs_table_adds, @diffs_table_changes);
 245     for my $t1_field ( $t1->get_fields ) {
 246         my $f1_type      = $t1_field->data_type;
 247         my $f1_size      = $t1_field->size;
 248         my $f1_name      = $t1_field->name;
 249         my $f1_nullable  = $t1_field->is_nullable;
 250         my $f1_default   = $t1_field->default_value;
 251         my $f1_auto_inc  = $t1_field->is_auto_increment;
 252         my $t2_field     = $t2->get_field( $f1_name, $case_insensitive );
 253         my $f1_full_name = "$s1_name.$t1_name.$t1_name";
 254         warn "FIELD '$f1_full_name'\n" if $debug;
 255
 256         my $f2_full_name = "$s2_name.$t2_name.$f1_name";
 257
 258         unless ( $t2_field ) {
 259             warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
 260                 if $debug;
 261             my $temp_default_value = 0;
 262             if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
 263                 # SQL Server doesn't allow adding non-nullable, non-default columns
 264                 # so we add it with a default value, then remove the default value
 265                 $temp_default_value = 1;
 266                 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
 267                 $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
 268             }
 269             push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
 270                 $t1_name, $target_db =~ /Oracle/ ? '(' : '',
 271                 $f1_name, $f1_type,
 272                 ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 273                 !defined $f1_default ? ''
 274                         : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
 275                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
 276                         : " DEFAULT '$f1_default'",
 277                 $f1_nullable ? '' : ' NOT NULL',
 278                 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
 279                 $target_db =~ /Oracle/ ? ')' : '',
 280             );
 281             if ( $temp_default_value ) {
 282                 undef $f1_default;
 283                     push @diffs_table_adds, sprintf( <<END
 284 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
 285 SET \@defname =
 286 (SELECT name
 287 FROM sysobjects so JOIN sysconstraints sc
 288 ON so.id = sc.constid
 289 WHERE object_name(so.parent_obj) = '%s'
 290 AND so.xtype = 'D'
 291 AND sc.colid =
 292  (SELECT colid FROM syscolumns
 293  WHERE id = object_id('%s') AND
 294  name = '%s'))
 295 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
 296 + \@defname
 297 EXEC(\@cmd)
 298 END
 299                                         , $t1_name, $t1_name, $f1_name, $t1_name,
 300                     );
 301             }
 302             next;
 303         }
 304
 305         my $f2_type = $t2_field->data_type;
 306         my $f2_size = $t2_field->size || '';
 307         my $f2_nullable  = $t2_field->is_nullable;
 308         my $f2_default   = $t2_field->default_value;
 309         my $f2_auto_inc  = $t2_field->is_auto_increment;
 310         if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
 311                 # SQLServer timestamp fields can't be altered, so we drop and add instead
 312                 if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
 313                         push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
 314                     push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
 315                         $t1_name, $target_db =~ /Oracle/ ? '(' : '',
 316                         $f1_name, $f1_type,
 317                         ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 318                         !defined $f1_default ? ''
 319                                 : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
 320                                 : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
 321                                 : " DEFAULT '$f1_default'",
 322                         $f1_nullable ? '' : ' NOT NULL',
 323                         $f1_auto_inc ? ' AUTO_INCREMENT' : '',
 324                         $target_db =~ /Oracle/ ? ')' : '',
 325                     );
 326                     next;
 327                 }
 328
 329                         my $changeText = $target_db =~ /SQLServer/ ? 'ALTER COLUMN' :
 330                                 $target_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
 331                         my $nullText = $f1_nullable ? '' : ' NOT NULL';
 332                         $nullText = '' if $target_db =~ /Oracle/ && $f1_nullable == $f2_nullable;
 333             push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
 334                 $t1_name, $changeText,
 335                 $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
 336                 $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
 337                 $nullText,
 338                 !defined $f1_default || $target_db =~ /SQLServer/ ? ''
 339                         : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
 340                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
 341                         : " DEFAULT '$f1_default'",
 342                 $f1_auto_inc ? ' AUTO_INCREMENT' : '',
 343                 $target_db =~ /Oracle/ ? ')' : '',
 344             );
 345             if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
 346                 # Adding a column with a default value for SQL Server means adding a
 347                 # constraint and setting existing NULLs to the default value
 348                 push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
 349                         $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
 350                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
 351                         : "DEFAULT '$f1_default'", $f1_name,
 352                 );
 353                 push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
 354                         $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
 355                         : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
 356                         : "'$f1_default'", $f1_name,
 357                 );
 358             }
 359         }
 360     }
 361
 362         my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
 363 INDEX:
 364         for my $i1 ( $t1->get_indices ) {
 365                 for my $i2 ( $t2->get_indices ) {
 366                         if ( $i1->equals($i2, $case_insensitive) ) {
 367                                 $checked_indices{$i2} = 1;
 368                                 next INDEX;
 369                         }
 370                 }
 371                 push @diffs_index_creates, sprintf(
 372                 "CREATE %sINDEX%s ON %s (%s);",
 373                 $i1->type eq NORMAL ? '' : $i1->type." ",
 374                 $i1->name ? " ".$i1->name : '',
 375                 $t1_name,
 376                 join(",", $i1->fields),
 377             );
 378         }
 379 INDEX2:
 380         for my $i2 ( $t2->get_indices ) {
 381                 next if $checked_indices{$i2};
 382                 for my $i1 ( $t1->get_indices ) {
 383                         next INDEX2 if $i2->equals($i1, $case_insensitive);
 384                 }
 385                 $target_db =~ /SQLServer/
 386                         ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
 387                         : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
 388         }
 389
 390         my(%checked_constraints, @diffs_constraint_drops);
 391 CONSTRAINT:
 392         for my $c1 ( $t1->get_constraints ) {
 393                 next if $source_db =~ /Oracle/ && $c1->type eq UNIQUE && $c1->name =~ /^SYS_/i;
 394                 for my $c2 ( $t2->get_constraints ) {
 395                         if ( $c1->equals($c2, $case_insensitive) ) {
 396                                 $checked_constraints{$c2} = 1;
 397                                 next CONSTRAINT;
 398                         }
 399                 }
 400                 push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
 401                         constraint_to_string($c1, $source_schema).";";
 402         }
 403 CONSTRAINT2:
 404         for my $c2 ( $t2->get_constraints ) {
 405                 next if $checked_constraints{$c2};
 406                 for my $c1 ( $t1->get_constraints ) {
 407                         next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
 408                 }
 409                 if ( $c2->type eq UNIQUE ) {
 410                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
 411                                 $c2->name.";";
 412                 } elsif ( $target_db =~ /SQLServer/ ) {
 413                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
 414                 } else {
 415                         push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
 416                                 ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
 417                 }
 418         }
 419
 420         push @diffs, @diffs_index_drops, @diffs_constraint_drops,
 421                 @diffs_table_options, @diffs_table_adds,
 422                 @diffs_table_changes, @diffs_index_creates;
 423 }
 424
 425 for my $t2 ( $target_schema->get_tables ) {
 426     my $t2_name = $t2->name;
 427     my $t1      = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
 428
 429     unless ( $t1 ) {
 430         if ( $target_db =~ /SQLServer/ ) {
 431                         for my $constraint ( $t2->get_constraints ) {
 432                                 next if $constraint->type eq PRIMARY_KEY;
 433                                 push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
 434                         }
 435         }
 436         push @diffs_at_end, "DROP TABLE $t2_name;";
 437         next;
 438     }
 439
 440     for my $t2_field ( $t2->get_fields ) {
 441         my $f2_name      = $t2_field->name;
 442         my $t1_field     = $t1->get_field( $f2_name );
 443         unless ( $t1_field ) {
 444                 my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
 445             push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
 446         }
 447     }
 448 }
 449
 450 if ( @new_tables ) {
 451     my $dummy_tr = SQL::Translator->new;
 452     $dummy_tr->schema->add_table( $_ ) for @new_tables;
 453     my $producer = $dummy_tr->producer( $target_db );
 454     unshift @diffs, $producer->( $dummy_tr );
 455 }
 456 push(@diffs, @diffs_at_end);
 457
 458 if ( @diffs ) {
 459     if ( $source_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
 460         unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
 461     }
 462 }
 463
 464 if ( @diffs ) {
 465     print join( "\n",
 466         "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
 467     );
 468     exit(1);
 469 }
 470 else {
 471     print "There were no differences.\n";
 472 }
 473
 474 sub constraint_to_string {
 475         my $c = shift;
 476         my $schema = shift or die "No schema given";
 477         my @fields = $c->field_names or return '';
 478
 479         if ( $c->type eq PRIMARY_KEY ) {
 480                 if ( $target_db =~ /Oracle/ ) {
 481                         return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
 482                                 'PRIMARY KEY (' . join(', ', @fields). ')';
 483                 } else {
 484                         return 'PRIMARY KEY (' . join(', ', @fields). ')';
 485                 }
 486         }
 487         elsif ( $c->type eq UNIQUE ) {
 488                 if ( $target_db =~ /Oracle/ ) {
 489                         return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
 490                                 'UNIQUE (' . join(', ', @fields). ')';
 491                 } else {
 492                         return 'UNIQUE '.
 493                                 (defined $c->name ? $c->name.' ' : '').
 494                                 '(' . join(', ', @fields). ')';
 495                 }
 496         }
 497         elsif ( $c->type eq FOREIGN_KEY ) {
 498                 my $def = join(' ',
 499                         map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
 500                 );
 501
 502                 $def .= ' (' . join( ', ', @fields ) . ')';
 503
 504                 $def .= ' REFERENCES ' . $c->reference_table;
 505
 506                 my @rfields = map { $_ || () } $c->reference_fields;
 507                 unless ( @rfields ) {
 508                         my $rtable_name = $c->reference_table;
 509                         if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
 510                                 push @rfields, $ref_table->primary_key;
 511                         }
 512                         else {
 513                                 warn "Can't find reference table '$rtable_name' " .
 514                                         "in schema\n";
 515                         }
 516                 }
 517
 518                 if ( @rfields ) {
 519                         $def .= ' (' . join( ', ', @rfields ) . ')';
 520                 }
 521                 else {
 522                         warn "FK constraint on " . 'some table' . '.' .
 523                                 join('', @fields) . " has no reference fields\n";
 524                 }
 525
 526                 if ( $c->match_type ) {
 527                         $def .= ' MATCH ' .
 528                                 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
 529                 }
 530
 531                 if ( $c->on_delete ) {
 532                         $def .= ' ON DELETE '.join( ' ', $c->on_delete );
 533                 }
 534
 535                 if ( $c->on_update ) {
 536                         $def .= ' ON UPDATE '.join( ' ', $c->on_update );
 537                 }
 538
 539                 return $def;
 540         }
 541 }
 542
 543 # -------------------------------------------------------------------
 544 # Bring out number weight & measure in a year of dearth.
 545 # William Blake
 546 # -------------------------------------------------------------------
 547
 548 =pod
 549
 550 =head1 AUTHOR
 551
 552 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
 553
 554 =head1 SEE ALSO
 555
 556 SQL::Translator, L<http://sqlfairy.sourceforge.net>.
 557
 558 =cut