package SQL::Translator::Diff;
+
+
## SQLT schema diffing code
use strict;
use warnings;
+
+use Data::Dumper;
use SQL::Translator::Schema::Constants;
-sub schema_diff
-{
-# use Data::Dumper;
- my ($source_schema, $source_db, $target_schema, $target_db, $options) = @_;
-# print Data::Dumper::Dumper($target_schema);
- my $case_insensitive = $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;
- my $output_db = $options->{output_db} || $source_db;
+use base 'Class::Accessor::Fast';
- 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 ( $output_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, $output_db, $target_schema).";";
- $tar_table->drop_constraint($constraint);
- }
- }
- push @new_tables, $tar_table;
- next;
- }
+# Input/option accessors
+__PACKAGE__->mk_accessors(qw/
+ ignore_index_names ignore_constraint_names ignore_view_sql
+ ignore_proc_sql output_db source_schema target_schema
+ case_insensitive no_batch_alters ignore_missing_methods producer_options
+/);
- # 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;");
- }
+my @diff_arrays = qw/
+ tables_to_drop
+ tables_to_create
+/;
- 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.$f_tar_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 ( $output_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, $output_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' : '',
- $output_db =~ /Oracle/ ? ')' : '',
- );
- if ( $temp_default_value ) {
- undef $f_tar_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
- , $tar_table_name, $tar_table_name, $f_tar_name, $tar_table_name,
- );
- }
- next;
- }
-
- my $f_src_type = $src_table_field->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 ( $output_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, $output_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' : '',
- $output_db =~ /Oracle/ ? ')' : '',
- );
- next;
- }
-
- my $changeText = $output_db =~ /SQLServer/ ? 'ALTER COLUMN' :
- $output_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
- my $nullText = $f_tar_nullable ? '' : ' NOT NULL';
- $nullText = '' if $output_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, $output_db =~ /MySQL/ ? " $f_tar_name" : '',
- $f_tar_type, ($f_tar_size && $f_tar_type !~ /(blob|text)$/) ? "($f_tar_size)" : '',
- $nullText,
- !defined $f_tar_default || $output_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' : '',
- $output_db =~ /Oracle/ ? ')' : '',
- );
- if ( defined $f_tar_default && $output_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);
- }
- $output_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 @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
+/;
- my(%checked_constraints, @diffs_constraint_drops);
- CONSTRAINT:
- for my $c_tar ( $tar_table->get_constraints ) {
- 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, $output_db, $target_schema).";";
- }
- CONSTRAINT2:
- for my $c_src ( $src_table->get_constraints ) {
- 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 ( $output_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 : '').";";
- }
+__PACKAGE__->mk_accessors(@diff_arrays, 'table_diff_hash');
+
+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 ($source_schema, $source_db, $target_schema, $output_db, $options) = @_;
+ $options ||= {};
+
+ 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 new {
+ my ($class, $values) = @_;
+ $values->{$_} ||= [] foreach @diff_arrays;
+ $values->{table_diff_hash} = {};
+
+ $values->{producer_options} ||= {};
+ $values->{output_db} ||= $values->{source_db};
+ return $class->SUPER::new($values);
+}
+
+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};
+ warn 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, $case_insensitive );
-
- unless ( $tar_table ) {
- if ( $output_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_options
+ );
}
+ } 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, $case_insensitive );
- unless ( $tar_table_field ) {
- my $modifier = $output_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->{$_} } );
}
}
- }
- if ( @new_tables ) {
- my $dummytr = SQL::Translator->new;
- $dummytr->schema->add_table( $_ ) for @new_tables;
- my $producer = $dummytr->producer( $output_db );
- unshift @diffs, $producer->( $dummytr );
+ push @diffs, map( {
+ if (@{ $flattened_diffs{$_} || [] }) {
+ my $meth = $producer_class->can($_);
+
+ $meth ? map {
+ my $sql = $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $self->producer_options );
+ $sql ? ("$sql") : ();
+ } @{ $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/),
}
- push(@diffs, @diffs_at_end);
-
- # Procedures
- my(%checked_procs, @diffs_proc_creates, @diffs_proc_drops);
- PROC:
- for my $p_tar ( $target_schema->get_procedures ) {
- for my $p_src ( $source_schema->get_procedures ) {
- if ( $p_tar->equals($p_src, $case_insensitive) ) {
- $checked_procs{$p_src} = 1;
- next PROC;
- }
- }
- push @diffs_proc_creates, $p_tar->sql;
+
+ if (my @tables = @{ $self->tables_to_create } ) {
+ my $translator = new SQL::Translator(
+ producer_type => $self->output_db,
+ add_drop_table => 0,
+ no_comments => 1,
+ # TODO: sort out options
+ %{ $self->producer_options }
+ );
+ $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);
}
- PROC2:
- for my $p_src ( $source_schema->get_procedures ) {
- next if $checked_procs{$p_src};
- for my $p_tar ( $target_schema->get_procedures ) {
- next PROC2 if $p_src->equals($p_tar, $case_insensitive);
- }
- my $proc_ident = $p_src->owner ? sprintf("[%s].%s", $p_src->owner, $p_src->name) : $p_src->name;
- push @diffs_proc_drops, "DROP PROCEDURE $proc_ident;\nGO\n";
+
+ if (my @tables_to_drop = @{ $self->{tables_to_drop} || []} ) {
+ my $meth = $producer_class->can('drop_table');
+
+ push @diffs, $meth ? ( map { $meth->($_, $self->producer_options) } @tables_to_drop)
+ : $self->ignore_missing_methods
+ ? "-- $producer_class cant drop_table"
+ : die "$producer_class cant drop_table";
}
- # Views
- my(%checked_views, @diffs_view_creates, @diffs_view_drops);
- VIEW:
- for my $v_tar ( $target_schema->get_views ) {
- for my $v_src ( $source_schema->get_views ) {
- if ( $v_tar->equals($v_src, $case_insensitive) ) {
- $checked_views{$v_src} = 1;
- next VIEW;
- }
- }
- push @diffs_view_creates, $v_tar->sql;
+ if (@diffs) {
+ unshift @diffs, "BEGIN";
+ push @diffs, "\nCOMMIT";
+ } else {
+ @diffs = ("-- No differences found");
}
- VIEW2:
- for my $v_src ( $source_schema->get_views ) {
- next if $checked_views{$v_src};
- for my $v_tar ( $target_schema->get_views ) {
- next VIEW2 if $v_src->equals($v_tar, $case_insensitive);
+
+ if ( @diffs ) {
+ if ( $self->output_db !~ /^(?:MySQL|SQLite|PostgreSQL)$/ ) {
+ unshift(@diffs, "-- Output database @{[$self->output_db]} is untested/unsupported!!!");
}
- my $view_ident = $v_src->name;
- push @diffs_view_drops, "DROP VIEW $view_ident;\nGO\n";
- }
- push @diffs, @diffs_view_drops, @diffs_proc_drops,
- @diffs_view_creates, @diffs_proc_creates;
+ my @return =
+ map { $_ ? ( $_ =~ /;$/xms ? $_ : "$_;\n\n" ) : "\n" }
+ ("-- Convert schema '$src_name' to '$tar_name':", @diffs);
- 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"
- );
+ 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 ew've renamed from so we dont 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) {
+ warn 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;
}
+ }
+
- return $def;
+ # 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};
+
+ 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 dont get supruious 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 );
+}
+
1;
+
+__END__
+
+=head1 NAME
+
+SQL::Translator::Diff
+
+=head1 DESCRIPTION
+
+Takes two input SQL::Translator::Schemas (or SQL files) and produces ALTER
+statments 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<ignore_index_names>
+
+Match indexes based on types and fields, ignoring name.
+
+=item B<ignore_constraint_names>
+
+Match constrains based on types, fields and tables, ignoring name.
+
+=item B<output_db>
+
+Which producer to use to produce the output.
+
+=item B<case_insensitive>
+
+Ignore case of table, field, index and constraint names when comparing
+
+=item B<no_batch_alters>
+
+Produce each alter as a distinct C<ALTER TABLE> statement even if the producer
+supports the ability to do all alters for a table as one statement.
+
+=item B<ignore_missing_methods>
+
+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
+
+=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<alter_create_constraint($con)>
+
+=item * C<alter_drop_constraint($con)>
+
+=item * C<alter_create_index($idx)>
+
+=item * C<alter_drop_index($idx)>
+
+=item * C<add_field($fld)>
+
+=item * C<alter_field($old_fld, $new_fld)>
+
+=item * C<rename_field($old_fld, $new_fld)>
+
+=item * C<drop_field($fld)>
+
+=item * C<alter_table($table)>
+
+=item * C<drop_table($table)>
+
+=item * C<rename_table($old_table, $new_table)> (optional)
+
+=item * C<batch_alter_table($table, $hash)> (optional)
+
+If the producer supports C<batch_alter_table>, 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 a hash.
+
+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<preprocess_schema($class, $schema)> (optional)
+
+C<preprocess_schema> 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 contraint 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<preprocess_schema> 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
projects / dbsrgits/SQL-Translator.git / blobdiff