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., 51 Franklin Street, Fifth Floor, Boston, MA
  19 # 02110-1301 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.59';
 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