X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FDiff.pm;h=4218fc3e91557d70bb44ffaf54d345b56b0021bc;hb=f741008213392307f73bd65e270c42b40a71e282;hp=8d893f73ef5aa1e170e7fec63ced9270b5dce030;hpb=03739eb3d4372c1dd36d350143bce670c7654894;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Diff.pm b/lib/SQL/Translator/Diff.pm index 8d893f7..4218fc3 100644 --- a/lib/SQL/Translator/Diff.pm +++ b/lib/SQL/Translator/Diff.pm @@ -1,395 +1,604 @@ package SQL::Translator::Diff; + + ## SQLT schema diffing code use strict; use warnings; + +use Data::Dumper; +use Carp::Clan qw/^SQL::Translator/; use SQL::Translator::Schema::Constants; +use Sub::Quote qw(quote_sub); +use Moo; -sub schema_diff -{ -# use Data::Dumper; - my ($source_schema, $source_db, $target_schema, $target_db, $options) = @_; -# print Data::Dumper::Dumper($target_schema); - my $caseopt = $options->{caseopt} || 0; - my $debug = $options->{debug} || 0; - my $trace = $options->{trace} || 0; - my $ignore_index_names = $options->{ignore_index_names} || 0; - my $ignore_constraint_names = $options->{ignore_constraint_names} || 0; +has ignore_index_names => ( + is => 'rw', +); +has ignore_constraint_names => ( + is => 'rw', +); +has ignore_view_sql => ( + is => 'rw', +); +has ignore_proc_sql => ( + is => 'rw', +); +has output_db => ( + is => 'rw', +); +has source_schema => ( + is => 'rw', +); +has target_schema => ( + is => 'rw', +); +has case_insensitive => ( + is => 'rw', +); +has no_batch_alters => ( + is => 'rw', +); +has ignore_missing_methods => ( + is => 'rw', +); +has producer_args => ( + is => 'rw', + lazy => 1, + default => quote_sub '{}', +); +has tables_to_drop => ( + is => 'rw', + lazy => 1, + default => quote_sub '[]', +); +has tables_to_create => ( + is => 'rw', + lazy => 1, + default => quote_sub '[]', +); +has table_diff_hash => ( + is => 'rw', + lazy => 1, + default => quote_sub '{}', +); - my $case_insensitive = $source_db =~ /SQLServer/ || $caseopt; +my @diff_arrays = qw/ + tables_to_drop + tables_to_create +/; - my $tar_name = $target_schema->name; - my $src_name = $source_schema->name; - my ( @new_tables, @diffs , @diffs_at_end); - for my $tar_table ( $target_schema->get_tables ) { - my $tar_table_name = $tar_table->name; - my $src_table = $source_schema->get_table( $tar_table_name, $case_insensitive ); - - warn "TABLE '$tar_name.$tar_table_name'\n" if $debug; - unless ( $src_table ) { - warn "Couldn't find table '$tar_name.$tar_table_name' in '$src_name'\n" - if $debug; - if ( $source_db =~ /(SQLServer|Oracle)/ ) { - for my $constraint ( $tar_table->get_constraints ) { - next if $constraint->type ne FOREIGN_KEY; - push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ". - constraint_to_string($constraint, $source_db, $target_schema).";"; - $tar_table->drop_constraint($constraint); - } - } - push @new_tables, $tar_table; - next; - } +my @diff_hash_keys = qw/ + constraints_to_create + constraints_to_drop + indexes_to_create + indexes_to_drop + fields_to_create + fields_to_alter + fields_to_rename + fields_to_drop + table_options + table_renamed_from +/; - # Go through our options - my $options_different = 0; - my %checkedOptions; - OPTION: - for my $tar_table_option_ref ( $tar_table->options ) { - my($key_tar, $value_tar) = %{$tar_table_option_ref}; - for my $src_table_option_ref ( $src_table->options ) { - my($key_src, $value_src) = %{$src_table_option_ref}; - if ( $key_tar eq $key_src ) { - if ( defined $value_tar != defined $value_src ) { - $options_different = 1; - last OPTION; - } - if ( defined $value_tar && $value_tar ne $value_src ) { - $options_different = 1; - last OPTION; - } - $checkedOptions{$key_tar} = 1; - next OPTION; - } - } - $options_different = 1; - last OPTION; - } - # Go through the other table's options - unless ( $options_different ) { - for my $src_table_option_ref ( $src_table->options ) { - my($key, $value) = %{$src_table_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 ( $tar_table->options ) { - my($key, $value) = %{$option_ref}; - push(@options, defined $value ? "$key=$value" : $key); - } - my $options = join(' ', @options); - @diffs_table_options = ("ALTER TABLE $tar_table_name $options;"); - } +sub schema_diff { + # use Data::Dumper; + ## we are getting instructions on how to turn the source into the target + ## source == original, target == new (hmm, if I need to comment this, should I rename the vars again ??) + ## _schema isa SQL::Translator::Schema + ## _db is the name of the producer/db it came out of/into + ## results are formatted to the source preferences - my $src_table_name = $src_table->name; - my(@diffs_table_adds, @diffs_table_changes); - for my $tar_table_field ( $tar_table->get_fields ) { - my $f_tar_type = $tar_table_field->data_type; - my $f_tar_size = $tar_table_field->size; - my $f_tar_name = $tar_table_field->name; - my $f_tar_nullable = $tar_table_field->is_nullable; - my $f_tar_default = $tar_table_field->default_value; - my $f_tar_auto_inc = $tar_table_field->is_auto_increment; - my $src_table_field = $src_table->get_field( $f_tar_name, $case_insensitive ); - my $f_tar_full_name = "$tar_name.$tar_table_name.$tar_table_name"; - warn "FIELD '$f_tar_full_name'\n" if $debug; - - my $f_src_full_name = "$src_name.$src_table_name.$f_tar_name"; - - unless ( $src_table_field ) { - warn "Couldn't find field '$f_src_full_name' in '$src_table_name'\n" - if $debug; - my $temp_default_value = 0; - if ( $source_db =~ /SQLServer/ && - !$f_tar_nullable && - !defined $f_tar_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); - $f_tar_default = grep($_ eq $f_tar_type, @numeric_types) ? 0 : ''; - } - push @diffs_table_adds, sprintf - ( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;", - $tar_table_name, $source_db =~ /Oracle/ ? '(' : '', - $f_tar_name, $f_tar_type, - ($f_tar_size && $f_tar_type !~ /(blob|text)$/) ? "($f_tar_size)" : '', - !defined $f_tar_default ? '' - : uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL' - : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP' - : " DEFAULT '$f_tar_default'", - $f_tar_nullable ? '' : ' NOT NULL', - $f_tar_auto_inc ? ' AUTO_INCREMENT' : '', - $source_db =~ /Oracle/ ? ')' : '', - ); - if ( $temp_default_value ) { - undef $f_tar_default; - push @diffs_table_adds, sprintf - ( <data_type; - my $f_src_size = $src_table_field->size || ''; - my $f_src_nullable = $src_table_field->is_nullable; - my $f_src_default = $src_table_field->default_value; - my $f_src_auto_inc = $src_table_field->is_auto_increment; - if ( !$tar_table_field->equals($src_table_field, $case_insensitive) ) { - # SQLServer timestamp fields can't be altered, so we drop and add instead - if ( $source_db =~ /SQLServer/ && $f_src_type eq "timestamp" ) { - push @diffs_table_changes, "ALTER TABLE $tar_table_name DROP COLUMN $f_tar_name;"; - push @diffs_table_changes, sprintf - ( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;", - $tar_table_name, $source_db =~ /Oracle/ ? '(' : '', - $f_tar_name, $f_tar_type, - ($f_tar_size && $f_tar_type !~ /(blob|text)$/) ? "($f_tar_size)" : '', - !defined $f_tar_default ? '' - : uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL' - : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP' - : " DEFAULT '$f_tar_default'", - $f_tar_nullable ? '' : ' NOT NULL', - $f_tar_auto_inc ? ' AUTO_INCREMENT' : '', - $source_db =~ /Oracle/ ? ')' : '', - ); - next; - } - - my $changeText = $source_db =~ /SQLServer/ ? 'ALTER COLUMN' : - $source_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE'; - my $nullText = $f_tar_nullable ? '' : ' NOT NULL'; - $nullText = '' if $source_db =~ /Oracle/ && $f_tar_nullable == $f_src_nullable; - push @diffs_table_changes, sprintf - ( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;", - $tar_table_name, $changeText, - $f_tar_name, $source_db =~ /MySQL/ ? " $f_tar_name" : '', - $f_tar_type, ($f_tar_size && $f_tar_type !~ /(blob|text)$/) ? "($f_tar_size)" : '', - $nullText, - !defined $f_tar_default || $source_db =~ /SQLServer/ ? '' - : uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL' - : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP' - : " DEFAULT '$f_tar_default'", - $f_tar_auto_inc ? ' AUTO_INCREMENT' : '', - $source_db =~ /Oracle/ ? ')' : '', - ); - if ( defined $f_tar_default && $source_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;", - $tar_table_name, $tar_table_name, $f_tar_name, uc $f_tar_default eq 'NULL' ? 'DEFAULT NULL' - : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP' - : "DEFAULT '$f_tar_default'", $f_tar_name, - ); - push @diffs_table_changes, sprintf - ( "UPDATE %s SET %s = %s WHERE %s IS NULL;", - $tar_table_name, $f_tar_name, uc $f_tar_default eq 'NULL' ? 'NULL' - : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP' - : "'$f_tar_default'", $f_tar_name, - ); - } - } - } - - my(%checked_indices, @diffs_index_creates, @diffs_index_drops); - INDEX: - for my $i_tar ( $tar_table->get_indices ) { - for my $i_src ( $src_table->get_indices ) { - if ( $i_tar->equals($i_src, $case_insensitive, $ignore_index_names) ) { - $checked_indices{$i_src} = 1; - next INDEX; - } - } - push @diffs_index_creates, sprintf - ( "CREATE %sINDEX%s ON %s (%s);", - $i_tar->type eq NORMAL ? '' : $i_tar->type." ", - $i_tar->name ? " ".$i_tar->name : '', - $tar_table_name, - join(",", $i_tar->fields), - ); - } - INDEX2: - for my $i_src ( $src_table->get_indices ) { - next if !$ignore_index_names && $checked_indices{$i_src}; - for my $i_tar ( $tar_table->get_indices ) { - next INDEX2 if $i_src->equals($i_tar, $case_insensitive, $ignore_index_names); - } - $source_db =~ /SQLServer/ - ? push @diffs_index_drops, "DROP INDEX $tar_table_name.".$i_src->name.";" - : push @diffs_index_drops, "DROP INDEX ".$i_src->name." on $tar_table_name;"; - } + my ($source_schema, $source_db, $target_schema, $output_db, $options) = @_; + $options ||= {}; - my(%checked_constraints, @diffs_constraint_drops); - CONSTRAINT: - for my $c_tar ( $tar_table->get_constraints ) { - next if $target_db =~ /Oracle/ && - $c_tar->type eq UNIQUE && $c_tar->name =~ /^SYS_/i; # Ignore Oracle SYS_ constraints hack - for my $c_src ( $src_table->get_constraints ) { - if ( $c_tar->equals($c_src, $case_insensitive, $ignore_constraint_names) ) { - $checked_constraints{$c_src} = 1; - next CONSTRAINT; - } - } - push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ". - constraint_to_string($c_tar, $source_db, $target_schema).";"; - } - CONSTRAINT2: - for my $c_src ( $src_table->get_constraints ) { - next if $source_db =~ /Oracle/ && - $c_src->type eq UNIQUE && $c_src->name =~ /^SYS_/i; # Ignore Oracle SYS_ constraints hack - next if !$ignore_constraint_names && $checked_constraints{$c_src}; - for my $c_tar ( $tar_table->get_constraints ) { - next CONSTRAINT2 if $c_src->equals($c_tar, $case_insensitive, $ignore_constraint_names); - } - if ( $c_src->type eq UNIQUE ) { - push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP INDEX ". - $c_src->name.";"; - } elsif ( $source_db =~ /SQLServer/ ) { - push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->name.";"; - } else { - push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->type. - ($c_src->type eq FOREIGN_KEY ? " ".$c_src->name : '').";"; - } + my $obj = SQL::Translator::Diff->new( { + %$options, + source_schema => $source_schema, + target_schema => $target_schema, + output_db => $output_db + } ); + + $obj->compute_differences->produce_diff_sql; +} + +sub BUILD { + my ($self, $args) = @_; + if ($args->{producer_options}) { + carp 'producer_options is deprecated. Please use producer_args'; + $self->producer_args({ + %{$args->{producer_options}}, + %{$self->producer_args} + }); + } + + if (! $self->output_db) { + $self->output_db($args->{source_db}) + } +} + +sub compute_differences { + my ($self) = @_; + + my $target_schema = $self->target_schema; + my $source_schema = $self->source_schema; + + my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}"; + eval "require $producer_class"; + die $@ if $@; + + if (my $preprocess = $producer_class->can('preprocess_schema')) { + $preprocess->($source_schema); + $preprocess->($target_schema); + } + + my %src_tables_checked = (); + my @tar_tables = sort { $a->name cmp $b->name } $target_schema->get_tables; + ## do original/source tables exist in target? + for my $tar_table ( @tar_tables ) { + my $tar_table_name = $tar_table->name; + + my $src_table; + + $self->table_diff_hash->{$tar_table_name} = { + map {$_ => [] } @diff_hash_keys + }; + + if (my $old_name = $tar_table->extra('renamed_from')) { + $src_table = $source_schema->get_table( $old_name, $self->case_insensitive ); + if ($src_table) { + $self->table_diff_hash->{$tar_table_name}{table_renamed_from} = [ [$src_table, $tar_table] ]; + } else { + delete $tar_table->extra->{renamed_from}; + carp qq#Renamed table can't find old table "$old_name" for renamed table\n#; } + } else { + $src_table = $source_schema->get_table( $tar_table_name, $self->case_insensitive ); + } - push @diffs, @diffs_index_drops, @diffs_constraint_drops, - @diffs_table_options, @diffs_table_adds, - @diffs_table_changes, @diffs_index_creates; + unless ( $src_table ) { + ## table is new + ## add table(s) later. + push @{$self->tables_to_create}, $tar_table; + next; } + my $src_table_name = $src_table->name; + $src_table_name = lc $src_table_name if $self->case_insensitive; + $src_tables_checked{$src_table_name} = 1; + + + $self->diff_table_options($src_table, $tar_table); + + ## Compare fields, their types, defaults, sizes etc etc + $self->diff_table_fields($src_table, $tar_table); + + $self->diff_table_indexes($src_table, $tar_table); + $self->diff_table_constraints($src_table, $tar_table); + + } # end of target_schema->get_tables loop + for my $src_table ( $source_schema->get_tables ) { my $src_table_name = $src_table->name; - my $tar_table = $target_schema->get_table( $src_table_name, $source_db =~ /SQLServer/ ); - - unless ( $tar_table ) { - if ( $source_db =~ /SQLServer/ ) { - for my $constraint ( $src_table->get_constraints ) { - next if $constraint->type eq PRIMARY_KEY; - push @diffs, "ALTER TABLE $src_table_name DROP ".$constraint->name.";"; - } - } - push @diffs_at_end, "DROP TABLE $src_table_name;"; - next; + + $src_table_name = lc $src_table_name if $self->case_insensitive; + + push @{ $self->tables_to_drop}, $src_table + unless $src_tables_checked{$src_table_name}; + } + + return $self; +} + +sub produce_diff_sql { + my ($self) = @_; + + my $target_schema = $self->target_schema; + my $source_schema = $self->source_schema; + my $tar_name = $target_schema->name; + my $src_name = $source_schema->name; + + my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}"; + eval "require $producer_class"; + die $@ if $@; + + # Map of name we store under => producer method name + my %func_map = ( + constraints_to_create => 'alter_create_constraint', + constraints_to_drop => 'alter_drop_constraint', + indexes_to_create => 'alter_create_index', + indexes_to_drop => 'alter_drop_index', + fields_to_create => 'add_field', + fields_to_alter => 'alter_field', + fields_to_rename => 'rename_field', + fields_to_drop => 'drop_field', + table_options => 'alter_table', + table_renamed_from => 'rename_table', + ); + my @diffs; + + if (!$self->no_batch_alters && + (my $batch_alter = $producer_class->can('batch_alter_table')) ) + { + # Good - Producer supports batch altering of tables. + foreach my $table ( sort keys %{$self->table_diff_hash} ) { + my $tar_table = $target_schema->get_table($table) + || $source_schema->get_table($table); + + push @diffs, $batch_alter->($tar_table, + { map { + $func_map{$_} => $self->table_diff_hash->{$table}{$_} + } keys %func_map + }, + $self->producer_args + ); } + } else { - for my $src_table_field ( $src_table->get_fields ) { - my $f_src_name = $src_table_field->name; - my $tar_table_field = $tar_table->get_field( $f_src_name ); - unless ( $tar_table_field ) { - my $modifier = $source_db =~ /SQLServer/ ? "COLUMN " : ''; - push @diffs, "ALTER TABLE $src_table_name DROP $modifier$f_src_name;"; + # If we have any table renames we need to do those first; + my %flattened_diffs; + foreach my $table ( sort keys %{$self->table_diff_hash} ) { + my $table_diff = $self->table_diff_hash->{$table}; + for (@diff_hash_keys) { + push( @{ $flattened_diffs{ $func_map{$_} } ||= [] }, @{ $table_diff->{$_} } ); } } + + push @diffs, map( { + if (@{ $flattened_diffs{$_} || [] }) { + my $meth = $producer_class->can($_); + + $meth ? map { + map { $_ ? "$_" : () } $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $self->producer_args ); + } @{ $flattened_diffs{$_} } + : $self->ignore_missing_methods + ? "-- $producer_class cant $_" + : die "$producer_class cant $_"; + } else { () } + + } qw/rename_table + alter_drop_constraint + alter_drop_index + drop_field + add_field + alter_field + rename_field + alter_create_index + alter_create_constraint + alter_table/), + } + + if (my @tables = @{ $self->tables_to_create } ) { + my $translator = SQL::Translator->new( + producer_type => $self->output_db, + add_drop_table => 0, + no_comments => 1, + # TODO: sort out options + %{ $self->producer_args } + ); + $translator->producer_args->{no_transaction} = 1; + my $schema = $translator->schema; + + $schema->add_table($_) for @tables; + + unshift @diffs, + # Remove begin/commit here, since we wrap everything in one. + grep { $_ !~ /^(?:COMMIT|START(?: TRANSACTION)?|BEGIN(?: TRANSACTION)?)/ } $producer_class->can('produce')->($translator); + } + + if (my @tables_to_drop = @{ $self->{tables_to_drop} || []} ) { + my $meth = $producer_class->can('drop_table'); + + push @diffs, $meth ? ( map { $meth->($_, $self->producer_args) } @tables_to_drop) + : $self->ignore_missing_methods + ? "-- $producer_class cant drop_table" + : die "$producer_class cant drop_table"; } - if ( @new_tables ) { - my $dummytr = SQL::Translator->new; - $dummytr->schema->add_table( $_ ) for @new_tables; - my $producer = $dummytr->producer( $source_db ); - unshift @diffs, $producer->( $dummytr ); + if (@diffs) { + unshift @diffs, "BEGIN"; + push @diffs, "\nCOMMIT"; + } else { + @diffs = ("-- No differences found"); } - push(@diffs, @diffs_at_end); if ( @diffs ) { - if ( $target_db !~ /^(MySQL|SQLServer|Oracle)$/ ) { - unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!"); - } - return join( "\n", - "-- Convert schema '$src_name' to '$tar_name':\n", @diffs, "\n" - ); + if ( $self->output_db !~ /^(?:MySQL|SQLite|PostgreSQL)$/ ) { + unshift(@diffs, "-- Output database @{[$self->output_db]} is untested/unsupported!!!"); + } + + my @return = + map { $_ ? ( $_ =~ /;\s*\z/xms ? $_ : "$_;\n\n" ) : "\n" } + ("-- Convert schema '$src_name' to '$tar_name':", @diffs); + + return wantarray ? @return : join('', @return); } return undef; + } -sub constraint_to_string { - my $c = shift; - my $source_db = shift; - my $schema = shift or die "No schema given"; - my @fields = $c->field_names or return ''; +sub diff_table_indexes { + my ($self, $src_table, $tar_table) = @_; - if ( $c->type eq PRIMARY_KEY ) { - if ( $source_db =~ /Oracle/ ) { - return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') . - 'PRIMARY KEY (' . join(', ', @fields). ')'; - } else { - return 'PRIMARY KEY (' . join(', ', @fields). ')'; + my (%checked_indices); + INDEX_CREATE: + for my $i_tar ( $tar_table->get_indices ) { + for my $i_src ( $src_table->get_indices ) { + if ( $i_tar->equals($i_src, $self->case_insensitive, $self->ignore_index_names) ) { + $checked_indices{$i_src} = 1; + next INDEX_CREATE; + } } + push @{$self->table_diff_hash->{$tar_table}{indexes_to_create}}, $i_tar; } - elsif ( $c->type eq UNIQUE ) { - if ( $source_db =~ /Oracle/ ) { - return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') . - 'UNIQUE (' . join(', ', @fields). ')'; - } else { - return 'UNIQUE '. - (defined $c->name ? $c->name.' ' : ''). - '(' . join(', ', @fields). ')'; + + INDEX_DROP: + for my $i_src ( $src_table->get_indices ) { + next if !$self->ignore_index_names && $checked_indices{$i_src}; + for my $i_tar ( $tar_table->get_indices ) { + next INDEX_DROP if $i_src->equals($i_tar, $self->case_insensitive, $self->ignore_index_names); } + push @{$self->table_diff_hash->{$tar_table}{indexes_to_drop}}, $i_src; } - elsif ( $c->type eq FOREIGN_KEY ) { - my $def = join(' ', - map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY' - ); +} - $def .= ' (' . join( ', ', @fields ) . ')'; - $def .= ' REFERENCES ' . $c->reference_table; +sub diff_table_constraints { + my ($self, $src_table, $tar_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"; + my(%checked_constraints); + CONSTRAINT_CREATE: + for my $c_tar ( $tar_table->get_constraints ) { + for my $c_src ( $src_table->get_constraints ) { + + # This is a bit of a hack - needed for renaming tables to work + local $c_src->{table} = $tar_table; + + if ( $c_tar->equals($c_src, $self->case_insensitive, $self->ignore_constraint_names) ) { + $checked_constraints{$c_src} = 1; + next CONSTRAINT_CREATE; } } + push @{ $self->table_diff_hash->{$tar_table}{constraints_to_create} }, $c_tar; + } - if ( @rfields ) { - $def .= ' (' . join( ', ', @rfields ) . ')'; - } - else { - warn "FK constraint on " . 'some table' . '.' . - join('', @fields) . " has no reference fields\n"; + + CONSTRAINT_DROP: + for my $c_src ( $src_table->get_constraints ) { + + # This is a bit of a hack - needed for renaming tables to work + local $c_src->{table} = $tar_table; + + next if !$self->ignore_constraint_names && $checked_constraints{$c_src}; + for my $c_tar ( $tar_table->get_constraints ) { + next CONSTRAINT_DROP if $c_src->equals($c_tar, $self->case_insensitive, $self->ignore_constraint_names); } - if ( $c->match_type ) { - $def .= ' MATCH ' . - ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL'; + push @{ $self->table_diff_hash->{$tar_table}{constraints_to_drop} }, $c_src; + } + +} + +sub diff_table_fields { + my ($self, $src_table, $tar_table) = @_; + + # List of ones we've renamed from so we don't drop them + my %renamed_source_fields; + + for my $tar_table_field ( $tar_table->get_fields ) { + my $f_tar_name = $tar_table_field->name; + + if (my $old_name = $tar_table_field->extra->{renamed_from}) { + my $src_table_field = $src_table->get_field( $old_name, $self->case_insensitive ); + unless ($src_table_field) { + carp qq#Renamed column can't find old column "@{[$src_table->name]}.$old_name" for renamed column\n#; + delete $tar_table_field->extra->{renamed_from}; + } else { + push @{$self->table_diff_hash->{$tar_table}{fields_to_rename} }, [ $src_table_field, $tar_table_field ]; + $renamed_source_fields{$old_name} = 1; + next; + } } - if ( $c->on_delete ) { - $def .= ' ON DELETE '.join( ' ', $c->on_delete ); + my $src_table_field = $src_table->get_field( $f_tar_name, $self->case_insensitive ); + + unless ( $src_table_field ) { + push @{$self->table_diff_hash->{$tar_table}{fields_to_create}}, $tar_table_field; + next; } - if ( $c->on_update ) { - $def .= ' ON UPDATE '.join( ' ', $c->on_update ); + # field exists, something changed. This is a bit complex. Parsers can + # normalize types, but only some of them do, so compare the normalized and + # parsed types for each field to each other + if ( !$tar_table_field->equals($src_table_field, $self->case_insensitive) && + !$tar_table_field->equals($src_table_field->parsed_field, $self->case_insensitive) && + !$tar_table_field->parsed_field->equals($src_table_field, $self->case_insensitive) && + !$tar_table_field->parsed_field->equals($src_table_field->parsed_field, $self->case_insensitive) ) { + + # Some producers might need src field to diff against + push @{$self->table_diff_hash->{$tar_table}{fields_to_alter}}, [ $src_table_field, $tar_table_field ]; + next; } + } + + + # Now check to see if any fields from src_table need to be dropped + for my $src_table_field ( $src_table->get_fields ) { + my $f_src_name = $src_table_field->name; + next if $renamed_source_fields{$f_src_name}; - return $def; + my $tar_table_field = $tar_table->get_field( $f_src_name, $self->case_insensitive ); + + unless ( $tar_table_field ) { + push @{$self->table_diff_hash->{$tar_table}{fields_to_drop}}, $src_table_field; + next; + } } } +sub diff_table_options { + my ($self, $src_table, $tar_table) = @_; + + my $cmp = sub { + my ($a_name, undef, $b_name, undef) = ( %$a, %$b ); + $a_name cmp $b_name; + }; + # Need to sort the options so we don't get spurious diffs. + my (@src_opts, @tar_opts); + @src_opts = sort $cmp $src_table->options; + @tar_opts = sort $cmp $tar_table->options; + + + # If there's a difference, just re-set all the options + push @{ $self->table_diff_hash->{$tar_table}{table_options} }, $tar_table + unless $src_table->_compare_objects( \@src_opts, \@tar_opts ); +} + +# support producer_options as an alias for producer_args for legacy code. +sub producer_options { + my $self = shift; + + return $self->producer_args( @_ ); +} + 1; + +__END__ + +=head1 NAME + +SQL::Translator::Diff - determine differences between two schemas + +=head1 DESCRIPTION + +Takes two input SQL::Translator::Schemas (or SQL files) and produces ALTER +statements to make them the same + +=head1 SNYOPSIS + +Simplest usage: + + use SQL::Translator::Diff; + my $sql = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL', $options_hash) + +OO usage: + + use SQL::Translator::Diff; + my $diff = SQL::Translator::Diff->new({ + output_db => 'MySQL', + source_schema => $source_schema, + target_schema => $target_schema, + %$options_hash, + })->compute_differences->produce_diff_sql; + +=head1 OPTIONS + +=over + +=item B + +Match indexes based on types and fields, ignoring name. + +=item B + +Match constrains based on types, fields and tables, ignoring name. + +=item B + +Which producer to use to produce the output. + +=item B + +Ignore case of table, field, index and constraint names when comparing + +=item B + +Produce each alter as a distinct C statement even if the producer +supports the ability to do all alters for a table as one statement. + +=item B + +If the diff would need a method that is missing from the producer, just emit a +comment showing the method is missing, rather than dieing with an error + +=item B + +Hash of extra arguments passed to L and the below +L. + +=back + +=head1 PRODUCER FUNCTIONS + +The following producer functions should be implemented for completeness. If +any of them are needed for a given diff, but not found, an error will be +thrown. + +=over + +=item * C + +=item * C + +=item * C + +=item * C + +=item * C + +=item * C + +=item * C + +=item * C + +=item * C + +=item * C + +=item * C (optional) + +=item * C (optional) + +If the producer supports C, it will be called with the +table to alter and a hash, the keys of which will be the method names listed +above; values will be arrays of fields or constraints to operate on. In the +case of the field functions that take two arguments this will appear as an +array reference. + +I.e. the hash might look something like the following: + + { + alter_create_constraint => [ $constraint1, $constraint2 ], + add_field => [ $field ], + alter_field => [ [$old_field, $new_field] ] + } + + +=item * C (optional) + +C is called by the Diff code to allow the producer to +normalize any data it needs to first. For example, the MySQL producer uses +this method to ensure that FK constraint names are unique. + +Basicaly any changes that need to be made to produce the SQL file for the +schema should be done here, so that a diff between a parsed SQL file and (say) +a parsed DBIx::Class::Schema object will be sane. + +(As an aside, DBIx::Class, for instance, uses the presence of a +C function on the producer to know that it can diff between +the previous SQL file and its own internal representation. Without this method +on th producer it will diff the two SQL files which is slower, but known to +work better on old-style producers.) + +=back + + +=head1 AUTHOR + +Original Author(s) unknown. + +Refactor/re-write and more comprehensive tests by Ash Berlin C<< ash@cpan.org >>. + +Redevelopment sponsored by Takkle Inc. + +=cut