X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=bin%2Fsqlt-diff;h=08ed420a524e5ad5e7b2a50fcdaf1c1de4d98929;hb=da06ac74ada30aacf656943306679a28605ad5c8;hp=07fea000ca9a64903f19dd3897cbd24a605383da;hpb=ef45b16bef113d0ad742b44e3a14991b8b002838;p=dbsrgits%2FSQL-Translator.git diff --git a/bin/sqlt-diff b/bin/sqlt-diff index 07fea00..08ed420 100755 --- a/bin/sqlt-diff +++ b/bin/sqlt-diff @@ -2,9 +2,9 @@ # vim: set ft=perl: # ------------------------------------------------------------------- -# $Id: sqlt-diff,v 1.12 2005-08-31 15:42:17 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 @@ -37,11 +37,24 @@ For a list of all valid parsers: 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= 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 @@ -82,9 +95,10 @@ new index as such: =back -"ALTER/DROP TABLE" and "CREATE INDEX" statements B 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 @@ -94,12 +108,15 @@ use strict; 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.12 $ =~ /(\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; @@ -110,15 +127,50 @@ for my $arg ( @ARGV ) { 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; @@ -133,17 +185,16 @@ if ( $list ) { 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; @@ -151,380 +202,29 @@ for my $in ( @input ) { $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|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( <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 ); +else +{ + print "No differences found."; } -push(@diffs, @diffs_at_end); -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