# vim: set ft=perl:
# -------------------------------------------------------------------
-# $Id: sqlt-diff,v 1.10 2005-07-15 23:36:13 duality72 Exp $
+# $Id: sqlt-diff 1440 2009-01-17 16:31:57Z jawnsy $
# -------------------------------------------------------------------
-# Copyright (C) 2002-4 The SQLFairy Authors
+# 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
To diff two schemas:
- sqlt-diff [options] file_name1=parser file_name2=parser
+ sqlt-diff [options] file_name1=parser1 file_name2=parser2
Options:
-d|--debug Show debugging info
+ -t|--trace Turn on tracing for Parse::RecDescent
+ -c|--case-insensitive Compare tables/columns case-insensitively
+ --ignore-index-names Ignore index name differences
+ --ignore-constraint-names Ignore constraint name differences
+ --mysql_parser_version=<#####> Specify a target MySQL parser version
+ for dealing with /*! comments
+ --output-db=<Producer> This Producer will be used instead of one
+ corresponding to parser1 to format output
+ for new tables
+ --ignore-view-sql Ignore view SQL differences
+ --ignore-proc-sql Ignore procedure SQL differences
+ --no-batch-alters Do not clump multile alters to the same table into a
+ single ALTER TABLE statement where possible.
=head1 DESCRIPTION
=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.
+ALTER, CREATE, DROP statements are created by
+SQL::Translator::Producer::*, see there for support/problems.
+
+Currently (v0.0900), only MySQL is supported by this code.
=cut
use Pod::Usage;
use Data::Dumper;
use SQL::Translator;
+use SQL::Translator::Diff;
use SQL::Translator::Schema::Constants;
use vars qw( $VERSION );
-$VERSION = sprintf "%d.%02d", q$Revision: 1.10 $ =~ /(\d+)\.(\d+)/;
+$VERSION = '1.99';
-my ( @input, $list, $help, $debug );
+my ( @input, $list, $help, $debug, $trace, $caseopt, $ignore_index_names,
+ $ignore_constraint_names, $output_db, $mysql_parser_version,
+ $ignore_view_sql, $ignore_proc_sql, $no_batch_alters );
for my $arg ( @ARGV ) {
if ( $arg =~ m/^-?-l(ist)?$/ ) {
$list = 1;
elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
$debug = 1;
}
+ elsif ( $arg =~ m/^-?-t(race)?$/ ) {
+ $trace = 1;
+ }
+ elsif ( $arg =~ m/^-?-c(ase-insensitive)?$/ ) {
+ $caseopt = 1;
+ }
+ elsif ( $arg =~ m/^--ignore-index-names$/ ) {
+ $ignore_index_names = 1;
+ }
+ elsif ( $arg =~ m/^--ignore-constraint-names$/ ) {
+ $ignore_constraint_names = 1;
+ }
+ elsif ( $arg =~ m/^--mysql-parser-version=(.+)$/ ) {
+ $mysql_parser_version = $1;
+ }
+ elsif ( $arg =~ m/^--output-db=(.+)$/ ) {
+ $output_db = $1;
+ }
+ elsif ( $arg =~ m/^--ignore-view-sql$/ ) {
+ $ignore_view_sql = 1;
+ }
+ elsif ( $arg =~ m/^--ignore-proc-sql$/ ) {
+ $ignore_proc_sql = 1;
+ }
elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
push @input, { file => $1, parser => $2 };
}
+ elsif ( $arg =~ m/^--no-batch-alters$/ ) {
+ $no_batch_alters = 1;
+ }
else {
pod2usage( msg => "Unknown argument '$arg'" );
}
}
-pod2usage(1) if $help;
+print STDERR <<'EOM';
+This code is experimental, currently the new code only supports MySQL or
+SQLite diffing. To add support for other databases, please patch the relevant
+SQL::Translator::Producer:: module. If you need compatibility with the old
+sqlt-diff, please use sqlt-diff-old, and look into helping us make this one
+work for you
+EOM
+
+pod2usage(1) if $help || !@ARGV;
pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
my $tr = SQL::Translator->new;
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'};
+my ( $source_schema, $source_db, $target_schema, $target_db ) = map {
+ my $file = $_->{'file'};
+ my $parser = $_->{'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;
+ my $t = SQL::Translator->new(parser_args => {mysql_parser_version => $mysql_parser_version});
$t->debug( $debug );
+ $t->trace( $trace );
$t->parser( $parser ) or die $tr->error;
my $out = $t->translate( $file ) or die $tr->error;
my $schema = $t->schema;
$schema->name( $file );
}
- if ( $i == 1 ) {
- $source_schema = $schema;
- $source_db = $parser;
- }
- else {
- $target_schema = $schema;
- $target_db = $parser;
- }
- $i--;
+ ($schema, $parser);
+} @input;
+
+my $result = SQL::Translator::Diff::schema_diff($source_schema, $source_db,
+ $target_schema, $target_db,
+ { caseopt => $caseopt,
+ ignore_index_names => $ignore_index_names,
+ ignore_constraint_names => $ignore_constraint_names,
+ ignore_view_sql => $ignore_view_sql,
+ ignore_proc_sql => $ignore_proc_sql,
+ output_db => $output_db,
+ no_batch_alters => $no_batch_alters,
+ debug => $debug,
+ trace => $trace });
+if($result)
+{
+ print $result;
}
-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/ ) {
- for my $constraint ( $t1->get_constraints ) {
- next if $constraint->type eq PRIMARY_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;",
- $t1_name, $f1_name, $f1_type,
- ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
- $f1_nullable ? '' : ' NOT NULL',
- !defined $f1_default ? ''
- : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
- : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
- : " DEFAULT '$f1_default'",
- $f1_auto_inc ? ' AUTO_INCREMENT' : '',
- );
- 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;",
- $t1_name, $f1_name, $f1_type,
- ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
- $f1_nullable ? '' : ' NOT NULL',
- !defined $f1_default ? ''
- : uc $f1_default eq 'NULL' ? ' DEFAULT NULL'
- : uc $f1_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
- : " DEFAULT '$f1_default'",
- $f1_auto_inc ? ' AUTO_INCREMENT' : '',
- );
- next;
- }
-
- push @diffs_table_changes, sprintf( "ALTER TABLE %s %s %s%s %s%s%s%s%s;",
- $t1_name, $target_db =~ /SQLServer/ ? "ALTER COLUMN" : "CHANGE",
- $f1_name, $target_db =~ /MySQL/ ? " $f1_name" : '',
- $f1_type, ($f1_size && $f1_type !~ /(blob|text)$/) ? "($f1_size)" : '',
- $f1_nullable ? '' : ' NOT NULL',
- !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' : '',
- );
- 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_adds, @diffs_constraint_drops);
-CONSTRAINT:
- for my $c1 ( $t1->get_constraints ) {
- for my $c2 ( $t2->get_constraints ) {
- if ( $c1->equals($c2, $case_insensitive) ) {
- $checked_constraints{$c2} = 1;
- next CONSTRAINT;
- }
- }
- push @diffs_constraint_adds, "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_constraint_adds, @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 );
+else
+{
+ print "No differences found.";
}
-push(@diffs, @diffs_at_end);
-if ( @diffs ) {
- print join( "\n",
- "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, ''
- );
-}
-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->fields or return '';
-
- if ( $c->type eq PRIMARY_KEY ) {
- return 'PRIMARY KEY (' . join(', ', @fields). ')';
- }
- elsif ( $c->type eq UNIQUE ) {
- 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
projects / dbsrgits/SQL-Translator.git / blobdiff