--- /dev/null
+#!/usr/bin/env perl
+# vim: set ft=perl:
+
+# -------------------------------------------------------------------
+# Copyright (C) 2002-2009 The SQLFairy Authors
+#
+# This program is free software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License as
+# published by the Free Software Foundation; version 2.
+#
+# This program is distributed in the hope that it will be useful, but
+# WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+# General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
+# 02111-1307 USA
+# -------------------------------------------------------------------
+
+=head1 NAME
+
+sqlt-diff - find the differences b/w two schemas
+
+=head1 SYNOPSIS
+
+For help:
+
+ sqlt-diff -h|--help
+
+For a list of all valid parsers:
+
+ sqlt -l|--list
+
+To diff two schemas:
+
+ sqlt-diff [options] file_name1=parser file_name2=parser
+
+Options:
+
+ -d|--debug Show debugging info
+
+=head1 DESCRIPTION
+
+sqlt-diff is a utility for creating a file of SQL commands necessary to
+transform the first schema provided to the second. While not yet
+exhaustive in its ability to mutate the entire schema, it will report the
+following
+
+=over
+
+=item * New tables
+
+Using the Producer class of the target (second) schema, any tables missing
+in the first schema will be generated in their entirety (fields, constraints,
+indices).
+
+=item * Missing/altered fields
+
+Any fields missing or altered between the two schemas will be reported
+as:
+
+ ALTER TABLE <table_name>
+ [DROP <field_name>]
+ [CHANGE <field_name> <datatype> (<size>)] ;
+
+=item * Missing/altered indices
+
+Any indices missing or of a different type or on different fields will be
+indicated. Indices that should be dropped will be reported as such:
+
+ DROP INDEX <index_name> ON <table_name> ;
+
+An index of a different type or on different fields will be reported as a
+new index as such:
+
+ CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
+ ( <field_name>[,<field_name>] ) ;
+
+=back
+
+"ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
+the Producer, unfortunately, and may require massaging before being passed to
+your target database.
+
+=cut
+
+# -------------------------------------------------------------------
+
+use strict;
+use warnings;
+use Pod::Usage;
+use Data::Dumper;
+use SQL::Translator;
+use SQL::Translator::Schema::Constants;
+
+use vars qw( $VERSION );
+$VERSION = '1.59';
+
+my ( @input, $list, $help, $debug );
+for my $arg ( @ARGV ) {
+ if ( $arg =~ m/^-?-l(ist)?$/ ) {
+ $list = 1;
+ }
+ elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
+ $help = 1;
+ }
+ elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
+ $debug = 1;
+ }
+ elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
+ push @input, { file => $1, parser => $2 };
+ }
+ else {
+ pod2usage( msg => "Unknown argument '$arg'" );
+ }
+}
+
+pod2usage(1) if $help;
+pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
+pod2usage('No input') if !@input;
+
+if ( my $interactive = -t STDIN && -t STDOUT ) {
+ print STDERR join("\n",
+ "sqlt-diff-old is deprecated. Please sqlt-diff, and tell us ",
+ "about any problems or patch SQL::Translator::Diff",
+ '',
+ );
+}
+
+my $tr = SQL::Translator->new;
+my @parsers = $tr->list_parsers;
+my %valid_parsers = map { $_, 1 } @parsers;
+
+if ( $list ) {
+ print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
+ print "\n";
+ exit(0);
+}
+
+pod2usage( msg => 'Too many file args' ) if @input > 2;
+
+my ( $source_schema, $source_db, $target_schema, $target_db );
+
+my $i = 2;
+for my $in ( @input ) {
+ my $file = $in->{'file'};
+ my $parser = $in->{'parser'};
+
+ die "Unable to read file '$file'\n" unless -r $file;
+ die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
+
+ my $t = SQL::Translator->new;
+ $t->debug( $debug );
+ $t->parser( $parser ) or die $tr->error;
+ my $out = $t->translate( $file ) or die $tr->error;
+ my $schema = $t->schema;
+ unless ( $schema->name ) {
+ $schema->name( $file );
+ }
+
+ if ( $i == 1 ) {
+ $source_schema = $schema;
+ $source_db = $parser;
+ }
+ else {
+ $target_schema = $schema;
+ $target_db = $parser;
+ }
+ $i--;
+}
+my $case_insensitive = $target_db =~ /SQLServer/;
+
+my $s1_name = $source_schema->name;
+my $s2_name = $target_schema->name;
+my ( @new_tables, @diffs , @diffs_at_end);
+for my $t1 ( $source_schema->get_tables ) {
+ my $t1_name = $t1->name;
+ my $t2 = $target_schema->get_table( $t1_name, $case_insensitive );
+
+ warn "TABLE '$s1_name.$t1_name'\n" if $debug;
+ unless ( $t2 ) {
+ warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
+ if $debug;
+ if ( $target_db =~ /(SQLServer|Oracle)/ ) {
+ for my $constraint ( $t1->get_constraints ) {
+ next if $constraint->type ne FOREIGN_KEY;
+ push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
+ constraint_to_string($constraint, $source_schema).";";
+ $t1->drop_constraint($constraint);
+ }
+ }
+ push @new_tables, $t1;
+ next;
+ }
+
+ # Go through our options
+ my $options_different = 0;
+ my %checkedOptions;
+OPTION:
+ for my $t1_option_ref ( $t1->options ) {
+ my($key1, $value1) = %{$t1_option_ref};
+ for my $t2_option_ref ( $t2->options ) {
+ my($key2, $value2) = %{$t2_option_ref};
+ if ( $key1 eq $key2 ) {
+ if ( defined $value1 != defined $value2 ) {
+ $options_different = 1;
+ last OPTION;
+ }
+ if ( defined $value1 && $value1 ne $value2 ) {
+ $options_different = 1;
+ last OPTION;
+ }
+ $checkedOptions{$key1} = 1;
+ next OPTION;
+ }
+ }
+ $options_different = 1;
+ last OPTION;
+ }
+ # Go through the other table's options
+ unless ( $options_different ) {
+ for my $t2_option_ref ( $t2->options ) {
+ my($key, $value) = %{$t2_option_ref};
+ next if $checkedOptions{$key};
+ $options_different = 1;
+ last;
+ }
+ }
+ # If there's a difference, just re-set all the options
+ my @diffs_table_options;
+ if ( $options_different ) {
+ my @options = ();
+ foreach my $option_ref ( $t1->options ) {
+ my($key, $value) = %{$option_ref};
+ push(@options, defined $value ? "$key=$value" : $key);
+ }
+ my $options = join(' ', @options);
+ @diffs_table_options = ("ALTER TABLE $t1_name $options;");
+ }
+
+ my $t2_name = $t2->name;
+ my(@diffs_table_adds, @diffs_table_changes);
+ for my $t1_field ( $t1->get_fields ) {
+ my $f1_type = $t1_field->data_type;
+ my $f1_size = $t1_field->size;
+ my $f1_name = $t1_field->name;
+ my $f1_nullable = $t1_field->is_nullable;
+ my $f1_default = $t1_field->default_value;
+ my $f1_auto_inc = $t1_field->is_auto_increment;
+ my $t2_field = $t2->get_field( $f1_name, $case_insensitive );
+ my $f1_full_name = "$s1_name.$t1_name.$t1_name";
+ warn "FIELD '$f1_full_name'\n" if $debug;
+
+ my $f2_full_name = "$s2_name.$t2_name.$f1_name";
+
+ unless ( $t2_field ) {
+ warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
+ if $debug;
+ my $temp_default_value = 0;
+ if ( $target_db =~ /SQLServer/ && !$f1_nullable && !defined $f1_default ) {
+ # SQL Server doesn't allow adding non-nullable, non-default columns
+ # so we add it with a default value, then remove the default value
+ $temp_default_value = 1;
+ my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
+ $f1_default = grep($_ eq $f1_type, @numeric_types) ? 0 : '';
+ }
+ push @diffs_table_adds, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
+ $t1_name, $target_db =~ /Oracle/ ? '(' : '',
+ $f1_name, $f1_type,
+ ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
+ !defined $f1_default ? ''
+ : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
+ : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
+ : " DEFAULT '$f1_default'",
+ $f1_nullable ? '' : ' NOT NULL',
+ $f1_auto_inc ? ' AUTO_INCREMENT' : '',
+ $target_db =~ /Oracle/ ? ')' : '',
+ );
+ if ( $temp_default_value ) {
+ undef $f1_default;
+ push @diffs_table_adds, sprintf( <<END
+DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
+SET \@defname =
+(SELECT name
+FROM sysobjects so JOIN sysconstraints sc
+ON so.id = sc.constid
+WHERE object_name(so.parent_obj) = '%s'
+AND so.xtype = 'D'
+AND sc.colid =
+ (SELECT colid FROM syscolumns
+ WHERE id = object_id('%s') AND
+ name = '%s'))
+SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
++ \@defname
+EXEC(\@cmd)
+END
+ , $t1_name, $t1_name, $f1_name, $t1_name,
+ );
+ }
+ next;
+ }
+
+ my $f2_type = $t2_field->data_type;
+ my $f2_size = $t2_field->size || '';
+ my $f2_nullable = $t2_field->is_nullable;
+ my $f2_default = $t2_field->default_value;
+ my $f2_auto_inc = $t2_field->is_auto_increment;
+ if ( !$t1_field->equals($t2_field, $case_insensitive) ) {
+ # SQLServer timestamp fields can't be altered, so we drop and add instead
+ if ( $target_db =~ /SQLServer/ && $f2_type eq "timestamp" ) {
+ push @diffs_table_changes, "ALTER TABLE $t1_name DROP COLUMN $f1_name;";
+ push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
+ $t1_name, $target_db =~ /Oracle/ ? '(' : '',
+ $f1_name, $f1_type,
+ ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
+ !defined $f1_default ? ''
+ : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
+ : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
+ : " DEFAULT '$f1_default'",
+ $f1_nullable ? '' : ' NOT NULL',
+ $f1_auto_inc ? ' AUTO_INCREMENT' : '',
+ $target_db =~ /Oracle/ ? ')' : '',
+ );
+ next;
+ }
+
+ my $changeText = $target_db =~ /SQLServer/ ? 'ALTER COLUMN' :
+ $target_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
+ my $nullText = $f1_nullable ? '' : ' NOT NULL';
+ $nullText = '' if $target_db =~ /Oracle/ && $f1_nullable == $f2_nullable;
+ push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
+ $t1_name, $changeText,
+ $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
+ $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
+ $nullText,
+ !defined $f1_default || $target_db =~ /SQLServer/ ? ''
+ : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
+ : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
+ : " DEFAULT '$f1_default'",
+ $f1_auto_inc ? ' AUTO_INCREMENT' : '',
+ $target_db =~ /Oracle/ ? ')' : '',
+ );
+ if ( defined $f1_default && $target_db =~ /SQLServer/ ) {
+ # Adding a column with a default value for SQL Server means adding a
+ # constraint and setting existing NULLs to the default value
+ push @diffs_table_changes, sprintf( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
+ $t1_name, $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'DEFAULT NULL'
+ : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
+ : "DEFAULT '$f1_default'", $f1_name,
+ );
+ push @diffs_table_changes, sprintf( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
+ $t1_name, $f1_name, uc $f1_default eq 'NULL' ? 'NULL'
+ : uc $f1_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
+ : "'$f1_default'", $f1_name,
+ );
+ }
+ }
+ }
+
+ my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
+INDEX:
+ for my $i1 ( $t1->get_indices ) {
+ for my $i2 ( $t2->get_indices ) {
+ if ( $i1->equals($i2, $case_insensitive) ) {
+ $checked_indices{$i2} = 1;
+ next INDEX;
+ }
+ }
+ push @diffs_index_creates, sprintf(
+ "CREATE %sINDEX%s ON %s (%s);",
+ $i1->type eq NORMAL ? '' : $i1->type." ",
+ $i1->name ? " ".$i1->name : '',
+ $t1_name,
+ join(",", $i1->fields),
+ );
+ }
+INDEX2:
+ for my $i2 ( $t2->get_indices ) {
+ next if $checked_indices{$i2};
+ for my $i1 ( $t1->get_indices ) {
+ next INDEX2 if $i2->equals($i1, $case_insensitive);
+ }
+ $target_db =~ /SQLServer/
+ ? push @diffs_index_drops, "DROP INDEX $t1_name.".$i2->name.";"
+ : push @diffs_index_drops, "DROP INDEX ".$i2->name." on $t1_name;";
+ }
+
+ my(%checked_constraints, @diffs_constraint_drops);
+CONSTRAINT:
+ for my $c1 ( $t1->get_constraints ) {
+ next if $source_db =~ /Oracle/ && $c1->type eq UNIQUE && $c1->name =~ /^SYS_/i;
+ for my $c2 ( $t2->get_constraints ) {
+ if ( $c1->equals($c2, $case_insensitive) ) {
+ $checked_constraints{$c2} = 1;
+ next CONSTRAINT;
+ }
+ }
+ push @diffs_at_end, "ALTER TABLE $t1_name ADD ".
+ constraint_to_string($c1, $source_schema).";";
+ }
+CONSTRAINT2:
+ for my $c2 ( $t2->get_constraints ) {
+ next if $checked_constraints{$c2};
+ for my $c1 ( $t1->get_constraints ) {
+ next CONSTRAINT2 if $c2->equals($c1, $case_insensitive);
+ }
+ if ( $c2->type eq UNIQUE ) {
+ push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP INDEX ".
+ $c2->name.";";
+ } elsif ( $target_db =~ /SQLServer/ ) {
+ push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->name.";";
+ } else {
+ push @diffs_constraint_drops, "ALTER TABLE $t1_name DROP ".$c2->type.
+ ($c2->type eq FOREIGN_KEY ? " ".$c2->name : '').";";
+ }
+ }
+
+ push @diffs, @diffs_index_drops, @diffs_constraint_drops,
+ @diffs_table_options, @diffs_table_adds,
+ @diffs_table_changes, @diffs_index_creates;
+}
+
+for my $t2 ( $target_schema->get_tables ) {
+ my $t2_name = $t2->name;
+ my $t1 = $source_schema->get_table( $t2_name, $target_db =~ /SQLServer/ );
+
+ unless ( $t1 ) {
+ if ( $target_db =~ /SQLServer/ ) {
+ for my $constraint ( $t2->get_constraints ) {
+ next if $constraint->type eq PRIMARY_KEY;
+ push @diffs, "ALTER TABLE $t2_name DROP ".$constraint->name.";";
+ }
+ }
+ push @diffs_at_end, "DROP TABLE $t2_name;";
+ next;
+ }
+
+ for my $t2_field ( $t2->get_fields ) {
+ my $f2_name = $t2_field->name;
+ my $t1_field = $t1->get_field( $f2_name );
+ unless ( $t1_field ) {
+ my $modifier = $target_db =~ /SQLServer/ ? "COLUMN " : '';
+ push @diffs, "ALTER TABLE $t2_name DROP $modifier$f2_name;";
+ }
+ }
+}
+
+if ( @new_tables ) {
+ my $dummy_tr = SQL::Translator->new;
+ $dummy_tr->schema->add_table( $_ ) for @new_tables;
+ my $producer = $dummy_tr->producer( $target_db );
+ unshift @diffs, $producer->( $dummy_tr );
+}
+push(@diffs, @diffs_at_end);
+
+if ( @diffs ) {
+ if ( $source_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
+ unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
+ }
+}
+
+if ( @diffs ) {
+ print join( "\n",
+ "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, "\n"
+ );
+ exit(1);
+}
+else {
+ print "There were no differences.\n";
+}
+
+sub constraint_to_string {
+ my $c = shift;
+ my $schema = shift or die "No schema given";
+ my @fields = $c->field_names or return '';
+
+ if ( $c->type eq PRIMARY_KEY ) {
+ if ( $target_db =~ /Oracle/ ) {
+ return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
+ 'PRIMARY KEY (' . join(', ', @fields). ')';
+ } else {
+ return 'PRIMARY KEY (' . join(', ', @fields). ')';
+ }
+ }
+ elsif ( $c->type eq UNIQUE ) {
+ if ( $target_db =~ /Oracle/ ) {
+ return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
+ 'UNIQUE (' . join(', ', @fields). ')';
+ } else {
+ return 'UNIQUE '.
+ (defined $c->name ? $c->name.' ' : '').
+ '(' . join(', ', @fields). ')';
+ }
+ }
+ elsif ( $c->type eq FOREIGN_KEY ) {
+ my $def = join(' ',
+ map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
+ );
+
+ $def .= ' (' . join( ', ', @fields ) . ')';
+
+ $def .= ' REFERENCES ' . $c->reference_table;
+
+ my @rfields = map { $_ || () } $c->reference_fields;
+ unless ( @rfields ) {
+ my $rtable_name = $c->reference_table;
+ if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
+ push @rfields, $ref_table->primary_key;
+ }
+ else {
+ warn "Can't find reference table '$rtable_name' " .
+ "in schema\n";
+ }
+ }
+
+ if ( @rfields ) {
+ $def .= ' (' . join( ', ', @rfields ) . ')';
+ }
+ else {
+ warn "FK constraint on " . 'some table' . '.' .
+ join('', @fields) . " has no reference fields\n";
+ }
+
+ if ( $c->match_type ) {
+ $def .= ' MATCH ' .
+ ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
+ }
+
+ if ( $c->on_delete ) {
+ $def .= ' ON DELETE '.join( ' ', $c->on_delete );
+ }
+
+ if ( $c->on_update ) {
+ $def .= ' ON UPDATE '.join( ' ', $c->on_update );
+ }
+
+ return $def;
+ }
+}
+
+# -------------------------------------------------------------------
+# Bring out number weight & measure in a year of dearth.
+# William Blake
+# -------------------------------------------------------------------
+
+=pod
+
+=head1 AUTHOR
+
+Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
+
+=head1 SEE ALSO
+
+SQL::Translator, L<http://sqlfairy.sourceforge.net>.
+
+=cut
projects / dbsrgits/SQL-Translator.git / blobdiff