Hi folks,
Well, I did warn you.. I managed to find someone courageous enough to
poke big holes in the diffing code, and replace the bits that actually
output SQL, to use the producers instead.
This means that some incompatible changes have been made, because Diff
used to do some things that are not in the respective producers.
Please would people who need this code look at the new changes (enclosed
as patches, will apply them to cvs unless people yell loudly enough ;),
and update the relevant producers to be able to cope with the needed
changes? Should be fairly simple. The MySQL producer diff is also attached
as a working example.
Thanks Luke!!
Index: lib/SQL/Translator/Producer/MySQL.pm
===================================================================
RCS file: /cvsroot/sqlfairy/sqlfairy/lib/SQL/Translator/Producer/MySQL.pm,v
retrieving revision 1.52
diff -u -r1.52 MySQL.pm
--- lib/SQL/Translator/Producer/MySQL.pm 27 Nov 2006 19:28:04 -0000
1.52
+++ lib/SQL/Translator/Producer/MySQL.pm 1 Aug 2007 10:12:26 -0000
@@ -241,6 +241,17 @@
# Footer
#
$create .= "\n)";
+ $create .= generate_table_options($table) || "";
+ $create .= ";\n\n";
+
+ return $drop ? ($drop,$create) : $create;
+}
+
+sub generate_table_options
+{
+ my ($table) = @_;
+ my $create;
+
my $table_type_defined = 0;
for my $t1_option_ref ( $table->options ) {
my($key, $value) = %{$t1_option_ref};
@@ -263,9 +274,7 @@
$create .= " DEFAULT CHARACTER SET $charset" if $charset;
$create .= " COLLATE $collate" if $collate;
$create .= qq[ comment='$comments'] if $comments;
- $create .= ";\n\n";
-
- return $drop ? ($drop,$create) : $create;
+ return $create;
}
sub create_field
@@ -376,6 +385,21 @@
return $field_def;
}
+sub alter_create_index
+{
+ my ($index, $options) = @_;
+
+ my $qt = $options->{quote_table_names} || '';
+ my $qf = $options->{quote_field_names} || '';
+
+ return join( ' ',
+ 'ALTER TABLE',
+ $qt.$index->table->name.$qt,
+ 'ADD',
+ create_index(@_)
+ );
+}
+
sub create_index
{
my ($index, $options) = @_;
@@ -383,19 +407,64 @@
my $qf = $options->{quote_field_names} || '';
return join( ' ',
- lc $index->type eq 'normal' ? 'INDEX' : $index->type,
+ lc $index->type eq 'normal' ? 'INDEX' : $index->type . '
INDEX',
$index->name,
'(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
);
}
+sub alter_drop_index
+{
+ my ($index, $options) = @_;
+
+ my $qt = $options->{quote_table_names} || '';
+ my $qf = $options->{quote_field_names} || '';
+
+ return join( ' ',
+ 'ALTER TABLE',
+ $qt.$index->table->name.$qt,
+ 'DROP',
+ 'INDEX',
+ $index->name || $index->fields
+ );
+
+}
+
+sub alter_drop_constraint
+{
+ my ($c, $options) = @_;
+
+ my $qt = $options->{quote_table_names} || '';
+ my $qc = $options->{quote_constraint_names} || '';
+
+ my $out = sprintf('ALTER TABLE %s DROP %s %s',
+ $c->table->name,
+ $c->type,
+ $qc . $c->name . $qc );
+
+ return $out;
+}
+
+sub alter_create_constraint
+{
+ my ($index, $options) = @_;
+
+ my $qt = $options->{quote_table_names} || '';
+ return join( ' ',
+ 'ALTER TABLE',
+ $qt.$index->table->name.$qt,
+ 'ADD',
+ create_constraint(@_) );
+}
+
sub create_constraint
{
my ($c, $options) = @_;
my $qf = $options->{quote_field_names} || '';
my $qt = $options->{quote_table_names} || '';
+ my $leave_name = $options->{leave_name} || undef;
my $counter = ($options->{fk_name_counter} ||= {});
my @fields = $c->fields or next;
@@ -414,12 +483,12 @@
# Make sure FK field is indexed or MySQL complains.
#
+ my $c_name = ($leave_name) ? $c->name : $c->table . '_' . $c->name;
$counter->{$c->table} ||= {};
my $def = join(' ',
map { $_ || () }
'CONSTRAINT',
- $qt . join('_', $c->table,
- $c->name,
+ $qt . join('_', $c_name,
($counter->{$c->table}{$c->name}++ ||
())
) . $qt,
'FOREIGN KEY'
@@ -468,6 +537,20 @@
return undef;
}
+sub alter_table
+{
+ my ($to_table, $options) = @_;
+
+ my $qt = $options->{quote_table_name} || '';
+
+ my $table_options = generate_table_options($to_table);
+ my $out = sprintf('ALTER TABLE %s%s',
+ $qt . $to_table->name . $qt,
+ $table_options);
+
+ return $out;
+}
+
sub alter_field
{
my ($from_field, $to_field, $options) = @_;Index: lib/SQL/Translator/Diff.pm
===================================================================
RCS file: /cvsroot/sqlfairy/sqlfairy/lib/SQL/Translator/Diff.pm,v
retrieving revision 1.9
diff -u -r1.9 Diff.pm
--- lib/SQL/Translator/Diff.pm 21 Mar 2007 15:20:49 -0000 1.9
+++ lib/SQL/Translator/Diff.pm 9 Aug 2007 10:27:58 -0000
@@ -2,13 +2,25 @@
## SQLT schema diffing code
use strict;
use warnings;
+use Data::Dumper;
use SQL::Translator::Schema::Constants;
sub schema_diff
-{
-# use Data::Dumper;
+ {
+ my @diffs;
+ # 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, $target_db, $options) = @_;
-# print Data::Dumper::Dumper($target_schema);
+ # print Data::Dumper::Dumper($target_schema);
+
+ my $producer_class = "SQL::Translator::Producer::$source_db";
+ use SQL::Translator::Producer::MySQL;
+
my $case_insensitive = $options->{caseopt} || 0;
my $debug = $options->{debug} || 0;
my $trace = $options->{trace} || 0;
@@ -20,420 +32,191 @@
my $tar_name = $target_schema->name;
my $src_name = $source_schema->name;
- my ( @new_tables, @diffs , @diffs_at_end);
+
+ my ( @diffs_new_tables, @diffs_at_end, @new_tables, @diffs_index_drops,
@diffs_constraint_drops, @diffs_table_drops, @diffs_table_adds,
@diffs_index_creates, @diffs_constraint_creates, @diffs_table_options );
+ ## do original/source tables exist in target?
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 );
+ 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;
- }
+ 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;
+ ## table is new
+ ## add table(s) later.
+ my $new_table_sql =
SQL::Translator::Producer::MySQL::create_table($tar_table, { leave_name => 1
});
+ push (@diffs_new_tables, $new_table_sql);
+ push (@new_tables, $tar_table);
+ next;
+ }
- # 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;
- }
+ # 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;
}
- $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 ( defined $value_tar && $value_tar ne $value_src ) {
+ $options_different = 1;
+ last OPTION;
}
+ $checkedOptions{$key_tar} = 1;
+ next OPTION;
+ }
}
- # 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;");
+ $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
+ if ( $options_different ) {
+ my $alter_sql = SQL::Translator::Producer::MySQL::alter_table(
$tar_table ) . ';';
+ @diffs_table_options = ("$alter_sql");
+ }
+
+ my $src_table_name = $src_table->name;
+ ## Compare fields, their types, defaults, sizes etc etc
+ 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 $alter_add_sql = SQL::Translator::Producer::MySQL::add_field(
$tar_table_field ) . ';';
+ push (@diffs_table_adds, $alter_add_sql);
+ next;
+ }
+
+ ## field exists, so what changed?
+ ## (do we care? just call equals to see IF)
+ if ( !$tar_table_field->equals($src_table_field, $case_insensitive) ) {
+ ## throw all this junk away and call producer->alter_field
+ ## check output same, etc etc
+
+ my $alter_field_sql = SQL::Translator::Producer::MySQL::alter_field(
$src_table_field, $tar_table_field ) . ';';
+ push (@diffs_table_adds, $alter_field_sql);
+ next;
}
+ }
- 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 [EMAIL PROTECTED] VARCHAR(100), [EMAIL PROTECTED] VARCHAR(1000)
-SET [EMAIL PROTECTED] =
-(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 [EMAIL PROTECTED] = 'ALTER TABLE %s DROP CONSTRAINT '
-+ [EMAIL PROTECTED]
-EXEC([EMAIL PROTECTED])
-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,
- );
- }
- }
- }
+ 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 );
+ my $f_src_full_name = "$tar_name.$tar_table_name.$f_src_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),
- );
+ unless ( $tar_table_field ) {
+ warn "Couldn't find field '$f_src_full_name' in '$src_table_name'\n"
+ if $debug;
+
+ my $alter_drop_sql = SQL::Translator::Producer::MySQL::drop_field(
$src_table_field ) . ';';
+ push (@diffs_table_drops, $alter_drop_sql);
+ next;
}
- INDEX2:
+ }
+
+ my (%checked_indices);
+ INDEX_CREATE:
+ for my $i_tar ( $tar_table->get_indices ) {
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);
+ if ( $i_tar->equals($i_src, $case_insensitive, $ignore_index_names)
) {
+ $checked_indices{$i_src} = 1;
+ next INDEX_CREATE;
}
- $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 $create_index_sql =
SQL::Translator::Producer::MySQL::alter_create_index( $i_tar ) . ';';
+ push ( @diffs_index_creates, $create_index_sql );
+ }
+ INDEX_DROP:
+ 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 INDEX_DROP if $i_src->equals($i_tar, $case_insensitive,
$ignore_index_names);
+ }
+ my $drop_index_sql =
SQL::Translator::Producer::MySQL::alter_drop_index( $i_src ) . ';';
+ push ( @diffs_index_drops, $drop_index_sql );
+ }
- my(%checked_constraints, @diffs_constraint_drops);
- CONSTRAINT:
+ my(%checked_constraints);
+ CONSTRAINT_CREATE:
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;
+ next CONSTRAINT_CREATE;
}
}
- push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ".
- constraint_to_string($c_tar, $output_db,
$target_schema).";";
+ my $create_constraint_sql =
SQL::Translator::Producer::MySQL::alter_create_constraint( $c_tar, { leave_name
=> 1 }) . ';';
+ push ( @diffs_constraint_creates, $create_constraint_sql );
}
- CONSTRAINT2:
+
+ CONSTRAINT_DROP:
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);
+ next CONSTRAINT_DROP 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 : '').";";
- }
- }
- push @diffs, @diffs_index_drops, @diffs_constraint_drops,
- @diffs_table_options, @diffs_table_adds,
- @diffs_table_changes, @diffs_index_creates;
- }
+ my $drop_constraint_sql =
SQL::Translator::Producer::MySQL::alter_drop_constraint( $c_src ) . ';';
+ push ( @diffs_constraint_drops, $drop_constraint_sql );
+ }
+ }
+ my @diffs_dropped_tables;
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;
- }
-
- 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;";
+ for my $c_src ( $src_table->get_constraints ) {
+ my $drop_constraint_sql =
SQL::Translator::Producer::MySQL::alter_drop_constraint( $c_src ) . ';';
+ push ( @diffs_constraint_drops, $drop_constraint_sql );
}
- }
- }
- 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, @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,
$ignore_proc_sql) ) {
- $checked_procs{$p_src} = 1;
- next PROC;
- }
- }
- push @diffs_proc_creates, $p_tar->sql;
- }
- 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,
$ignore_proc_sql);
- }
- 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";
- }
-
- # 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,
$ignore_view_sql) ) {
- $checked_views{$v_src} = 1;
- next VIEW;
- }
- }
- push @diffs_view_creates, $v_tar->sql;
- }
- 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,
$ignore_view_sql);
+ push @diffs_dropped_tables, "DROP TABLE $src_table_name;";
+ next;
}
- 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;
+ push ( @diffs, @diffs_constraint_drops, @diffs_index_drops,
@diffs_table_drops, @diffs_table_adds, @diffs_index_creates,
@diffs_constraint_creates, @diffs_table_options );
+ unshift (@diffs, "SET foreign_key_checks=0;\n\n", @diffs_new_tables, "SET
foreign_key_checks=1;\n\n" );
+ push (@diffs, @diffs_dropped_tables);
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 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 '';
-
- 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). ')';
- }
- }
- 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). ')';
- }
- }
- 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 ( $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 ( @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;
+ return undef;
}
-}
1;
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
--
sqlfairy-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
