[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Diff.pm

package SQL::Translator::Diff;
## SQLT schema diffing code
use strict;
use warnings;
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 $ignore_view_sql = $options->{ignore_view_sql} || 0;
    my $ignore_proc_sql = $options->{ignore_proc_sql} || 0;
    my $output_db = $options->{output_db} || $source_db;

    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;
        }

        # 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 $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(%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 : '').";";
          }
        }

        push @diffs, @diffs_index_drops, @diffs_constraint_drops,
          @diffs_table_options, @diffs_table_adds,
            @diffs_table_changes, @diffs_index_creates;
      }

    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;";
        }
      }
    }

    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);
      }
      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;

    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 ( @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;
  }
}

1;
Commit	Line	Data
51ffe5ee	1	package SQL::Translator::Diff;
	2	## SQLT schema diffing code
	3	use strict;
	4	use warnings;
	5	use SQL::Translator::Schema::Constants;
	6
	7	sub schema_diff
	8	{
	9	# use Data::Dumper;
	10	my ($source_schema, $source_db, $target_schema, $target_db, $options) = @_;
	11	# print Data::Dumper::Dumper($target_schema);
8b5b4c99	12	my $case_insensitive = $options->{caseopt} \|\| 0;
51ffe5ee	13	my $debug = $options->{debug} \|\| 0;
51ffe5ee	14	my $trace = $options->{trace} \|\| 0;
d990d84b	15	my $ignore_index_names = $options->{ignore_index_names} \|\| 0;
d990d84b	16	my $ignore_constraint_names = $options->{ignore_constraint_names} \|\| 0;
d1a895ce	17	my $ignore_view_sql = $options->{ignore_view_sql} \|\| 0;
d1a895ce	18	my $ignore_proc_sql = $options->{ignore_proc_sql} \|\| 0;
8b5b4c99	19	my $output_db = $options->{output_db} \|\| $source_db;
51ffe5ee	20
	21	my $tar_name = $target_schema->name;
	22	my $src_name = $source_schema->name;
	23	my ( @new_tables, @diffs , @diffs_at_end);
	24	for my $tar_table ( $target_schema->get_tables ) {
	25	my $tar_table_name = $tar_table->name;
	26	my $src_table = $source_schema->get_table( $tar_table_name, $case_insensitive );
	27
	28	warn "TABLE '$tar_name.$tar_table_name'\n" if $debug;
	29	unless ( $src_table ) {
	30	warn "Couldn't find table '$tar_name.$tar_table_name' in '$src_name'\n"
	31	if $debug;
8b5b4c99	32	if ( $output_db =~ /(SQLServer\|Oracle)/ ) {
51ffe5ee	33	for my $constraint ( $tar_table->get_constraints ) {
	34	next if $constraint->type ne FOREIGN_KEY;
	35	push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ".
8b5b4c99	36	constraint_to_string($constraint, $output_db, $target_schema).";";
51ffe5ee	37	$tar_table->drop_constraint($constraint);
	38	}
	39	}
	40	push @new_tables, $tar_table;
	41	next;
	42	}
	43
	44	# Go through our options
	45	my $options_different = 0;
	46	my %checkedOptions;
	47	OPTION:
	48	for my $tar_table_option_ref ( $tar_table->options ) {
	49	my($key_tar, $value_tar) = %{$tar_table_option_ref};
	50	for my $src_table_option_ref ( $src_table->options ) {
	51	my($key_src, $value_src) = %{$src_table_option_ref};
	52	if ( $key_tar eq $key_src ) {
	53	if ( defined $value_tar != defined $value_src ) {
	54	$options_different = 1;
	55	last OPTION;
	56	}
	57	if ( defined $value_tar && $value_tar ne $value_src ) {
	58	$options_different = 1;
	59	last OPTION;
	60	}
	61	$checkedOptions{$key_tar} = 1;
	62	next OPTION;
	63	}
	64	}
	65	$options_different = 1;
	66	last OPTION;
	67	}
	68	# Go through the other table's options
	69	unless ( $options_different ) {
	70	for my $src_table_option_ref ( $src_table->options ) {
	71	my($key, $value) = %{$src_table_option_ref};
	72	next if $checkedOptions{$key};
	73	$options_different = 1;
	74	last;
	75	}
	76	}
	77	# If there's a difference, just re-set all the options
	78	my @diffs_table_options;
	79	if ( $options_different ) {
	80	my @options = ();
	81	foreach my $option_ref ( $tar_table->options ) {
	82	my($key, $value) = %{$option_ref};
	83	push(@options, defined $value ? "$key=$value" : $key);
	84	}
	85	my $options = join(' ', @options);
	86	@diffs_table_options = ("ALTER TABLE $tar_table_name $options;");
	87	}
	88
	89	my $src_table_name = $src_table->name;
	90	my(@diffs_table_adds, @diffs_table_changes);
	91	for my $tar_table_field ( $tar_table->get_fields ) {
	92	my $f_tar_type = $tar_table_field->data_type;
	93	my $f_tar_size = $tar_table_field->size;
	94	my $f_tar_name = $tar_table_field->name;
	95	my $f_tar_nullable = $tar_table_field->is_nullable;
	96	my $f_tar_default = $tar_table_field->default_value;
	97	my $f_tar_auto_inc = $tar_table_field->is_auto_increment;
	98	my $src_table_field = $src_table->get_field( $f_tar_name, $case_insensitive );
8b5b4c99	99	my $f_tar_full_name = "$tar_name.$tar_table_name.$f_tar_name";
51ffe5ee	100	warn "FIELD '$f_tar_full_name'\n" if $debug;
	101
	102	my $f_src_full_name = "$src_name.$src_table_name.$f_tar_name";
	103
	104	unless ( $src_table_field ) {
	105	warn "Couldn't find field '$f_src_full_name' in '$src_table_name'\n"
	106	if $debug;
	107	my $temp_default_value = 0;
8b5b4c99	108	if ( $output_db =~ /SQLServer/ &&
51ffe5ee	109	!$f_tar_nullable &&
	110	!defined $f_tar_default ) {
	111	# SQL Server doesn't allow adding non-nullable, non-default columns
	112	# so we add it with a default value, then remove the default value
	113	$temp_default_value = 1;
	114	my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
	115	$f_tar_default = grep($_ eq $f_tar_type, @numeric_types) ? 0 : '';
	116	}
	117	push @diffs_table_adds, sprintf
	118	( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
8b5b4c99	119	$tar_table_name, $output_db =~ /Oracle/ ? '(' : '',
51ffe5ee	120	$f_tar_name, $f_tar_type,
	121	($f_tar_size && $f_tar_type !~ /(blob\|text)$/) ? "($f_tar_size)" : '',
	122	!defined $f_tar_default ? ''
	123	: uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL'
	124	: uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
	125	: " DEFAULT '$f_tar_default'",
	126	$f_tar_nullable ? '' : ' NOT NULL',
	127	$f_tar_auto_inc ? ' AUTO_INCREMENT' : '',
8b5b4c99	128	$output_db =~ /Oracle/ ? ')' : '',
51ffe5ee	129	);
	130	if ( $temp_default_value ) {
	131	undef $f_tar_default;
	132	push @diffs_table_adds, sprintf
	133	( <<END
	134	DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
	135	SET \@defname =
	136	(SELECT name
	137	FROM sysobjects so JOIN sysconstraints sc
	138	ON so.id = sc.constid
	139	WHERE object_name(so.parent_obj) = '%s'
	140	AND so.xtype = 'D'
	141	AND sc.colid =
	142	(SELECT colid FROM syscolumns
	143	WHERE id = object_id('%s') AND
	144	name = '%s'))
	145	SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
	146	+ \@defname
	147	EXEC(\@cmd)
	148	END
	149	, $tar_table_name, $tar_table_name, $f_tar_name, $tar_table_name,
	150	);
	151	}
	152	next;
	153	}
	154
	155	my $f_src_type = $src_table_field->data_type;
	156	my $f_src_size = $src_table_field->size \|\| '';
	157	my $f_src_nullable = $src_table_field->is_nullable;
	158	my $f_src_default = $src_table_field->default_value;
	159	my $f_src_auto_inc = $src_table_field->is_auto_increment;
	160	if ( !$tar_table_field->equals($src_table_field, $case_insensitive) ) {
	161	# SQLServer timestamp fields can't be altered, so we drop and add instead
8b5b4c99	162	if ( $output_db =~ /SQLServer/ && $f_src_type eq "timestamp" ) {
51ffe5ee	163	push @diffs_table_changes, "ALTER TABLE $tar_table_name DROP COLUMN $f_tar_name;";
	164	push @diffs_table_changes, sprintf
	165	( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
8b5b4c99	166	$tar_table_name, $output_db =~ /Oracle/ ? '(' : '',
51ffe5ee	167	$f_tar_name, $f_tar_type,
	168	($f_tar_size && $f_tar_type !~ /(blob\|text)$/) ? "($f_tar_size)" : '',
	169	!defined $f_tar_default ? ''
	170	: uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL'
	171	: uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
	172	: " DEFAULT '$f_tar_default'",
	173	$f_tar_nullable ? '' : ' NOT NULL',
	174	$f_tar_auto_inc ? ' AUTO_INCREMENT' : '',
8b5b4c99	175	$output_db =~ /Oracle/ ? ')' : '',
51ffe5ee	176	);
	177	next;
	178	}
	179
8b5b4c99	180	my $changeText = $output_db =~ /SQLServer/ ? 'ALTER COLUMN' :
8b5b4c99	181	$output_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
51ffe5ee	182	my $nullText = $f_tar_nullable ? '' : ' NOT NULL';
8b5b4c99	183	$nullText = '' if $output_db =~ /Oracle/ && $f_tar_nullable == $f_src_nullable;
51ffe5ee	184	push @diffs_table_changes, sprintf
	185	( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
	186	$tar_table_name, $changeText,
8b5b4c99	187	$f_tar_name, $output_db =~ /MySQL/ ? " $f_tar_name" : '',
51ffe5ee	188	$f_tar_type, ($f_tar_size && $f_tar_type !~ /(blob\|text)$/) ? "($f_tar_size)" : '',
51ffe5ee	189	$nullText,
8b5b4c99	190	!defined $f_tar_default \|\| $output_db =~ /SQLServer/ ? ''
51ffe5ee	191	: uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL'
	192	: uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
	193	: " DEFAULT '$f_tar_default'",
	194	$f_tar_auto_inc ? ' AUTO_INCREMENT' : '',
8b5b4c99	195	$output_db =~ /Oracle/ ? ')' : '',
51ffe5ee	196	);
8b5b4c99	197	if ( defined $f_tar_default && $output_db =~ /SQLServer/ ) {
51ffe5ee	198	# Adding a column with a default value for SQL Server means adding a
	199	# constraint and setting existing NULLs to the default value
	200	push @diffs_table_changes, sprintf
	201	( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
	202	$tar_table_name, $tar_table_name, $f_tar_name, uc $f_tar_default eq 'NULL' ? 'DEFAULT NULL'
	203	: uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
	204	: "DEFAULT '$f_tar_default'", $f_tar_name,
	205	);
	206	push @diffs_table_changes, sprintf
	207	( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
	208	$tar_table_name, $f_tar_name, uc $f_tar_default eq 'NULL' ? 'NULL'
	209	: uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
	210	: "'$f_tar_default'", $f_tar_name,
	211	);
	212	}
	213	}
	214	}
	215
	216	my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
	217	INDEX:
	218	for my $i_tar ( $tar_table->get_indices ) {
	219	for my $i_src ( $src_table->get_indices ) {
d990d84b	220	if ( $i_tar->equals($i_src, $case_insensitive, $ignore_index_names) ) {
51ffe5ee	221	$checked_indices{$i_src} = 1;
	222	next INDEX;
	223	}
	224	}
	225	push @diffs_index_creates, sprintf
	226	( "CREATE %sINDEX%s ON %s (%s);",
	227	$i_tar->type eq NORMAL ? '' : $i_tar->type." ",
	228	$i_tar->name ? " ".$i_tar->name : '',
	229	$tar_table_name,
	230	join(",", $i_tar->fields),
	231	);
	232	}
	233	INDEX2:
	234	for my $i_src ( $src_table->get_indices ) {
03739eb3	235	next if !$ignore_index_names && $checked_indices{$i_src};
51ffe5ee	236	for my $i_tar ( $tar_table->get_indices ) {
d990d84b	237	next INDEX2 if $i_src->equals($i_tar, $case_insensitive, $ignore_index_names);
51ffe5ee	238	}
8b5b4c99	239	$output_db =~ /SQLServer/
51ffe5ee	240	? push @diffs_index_drops, "DROP INDEX $tar_table_name.".$i_src->name.";"
	241	: push @diffs_index_drops, "DROP INDEX ".$i_src->name." on $tar_table_name;";
	242	}
	243
	244	my(%checked_constraints, @diffs_constraint_drops);
	245	CONSTRAINT:
	246	for my $c_tar ( $tar_table->get_constraints ) {
51ffe5ee	247	for my $c_src ( $src_table->get_constraints ) {
d990d84b	248	if ( $c_tar->equals($c_src, $case_insensitive, $ignore_constraint_names) ) {
51ffe5ee	249	$checked_constraints{$c_src} = 1;
	250	next CONSTRAINT;
	251	}
	252	}
	253	push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ".
8b5b4c99	254	constraint_to_string($c_tar, $output_db, $target_schema).";";
51ffe5ee	255	}
	256	CONSTRAINT2:
	257	for my $c_src ( $src_table->get_constraints ) {
03739eb3	258	next if !$ignore_constraint_names && $checked_constraints{$c_src};
51ffe5ee	259	for my $c_tar ( $tar_table->get_constraints ) {
d990d84b	260	next CONSTRAINT2 if $c_src->equals($c_tar, $case_insensitive, $ignore_constraint_names);
51ffe5ee	261	}
	262	if ( $c_src->type eq UNIQUE ) {
	263	push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP INDEX ".
	264	$c_src->name.";";
8b5b4c99	265	} elsif ( $output_db =~ /SQLServer/ ) {
51ffe5ee	266	push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->name.";";
	267	} else {
	268	push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->type.
	269	($c_src->type eq FOREIGN_KEY ? " ".$c_src->name : '').";";
	270	}
	271	}
	272
	273	push @diffs, @diffs_index_drops, @diffs_constraint_drops,
	274	@diffs_table_options, @diffs_table_adds,
	275	@diffs_table_changes, @diffs_index_creates;
	276	}
	277
	278	for my $src_table ( $source_schema->get_tables ) {
	279	my $src_table_name = $src_table->name;
8b5b4c99	280	my $tar_table = $target_schema->get_table( $src_table_name, $case_insensitive );
51ffe5ee	281
51ffe5ee	282	unless ( $tar_table ) {
8b5b4c99	283	if ( $output_db =~ /SQLServer/ ) {
51ffe5ee	284	for my $constraint ( $src_table->get_constraints ) {
	285	next if $constraint->type eq PRIMARY_KEY;
	286	push @diffs, "ALTER TABLE $src_table_name DROP ".$constraint->name.";";
	287	}
	288	}
	289	push @diffs_at_end, "DROP TABLE $src_table_name;";
	290	next;
	291	}
	292
	293	for my $src_table_field ( $src_table->get_fields ) {
	294	my $f_src_name = $src_table_field->name;
8b5b4c99	295	my $tar_table_field = $tar_table->get_field( $f_src_name, $case_insensitive );
51ffe5ee	296	unless ( $tar_table_field ) {
8b5b4c99	297	my $modifier = $output_db =~ /SQLServer/ ? "COLUMN " : '';
51ffe5ee	298	push @diffs, "ALTER TABLE $src_table_name DROP $modifier$f_src_name;";
	299	}
	300	}
	301	}
	302
	303	if ( @new_tables ) {
	304	my $dummytr = SQL::Translator->new;
	305	$dummytr->schema->add_table( $_ ) for @new_tables;
8b5b4c99	306	my $producer = $dummytr->producer( $output_db );
51ffe5ee	307	unshift @diffs, $producer->( $dummytr );
	308	}
	309	push(@diffs, @diffs_at_end);
	310
7ac784ff	311	# Procedures
	312	my(%checked_procs, @diffs_proc_creates, @diffs_proc_drops);
	313	PROC:
	314	for my $p_tar ( $target_schema->get_procedures ) {
	315	for my $p_src ( $source_schema->get_procedures ) {
d1a895ce	316	if ( $p_tar->equals($p_src, $case_insensitive, $ignore_proc_sql) ) {
7ac784ff	317	$checked_procs{$p_src} = 1;
	318	next PROC;
	319	}
	320	}
	321	push @diffs_proc_creates, $p_tar->sql;
	322	}
	323	PROC2:
	324	for my $p_src ( $source_schema->get_procedures ) {
	325	next if $checked_procs{$p_src};
	326	for my $p_tar ( $target_schema->get_procedures ) {
d1a895ce	327	next PROC2 if $p_src->equals($p_tar, $case_insensitive, $ignore_proc_sql);
7ac784ff	328	}
	329	my $proc_ident = $p_src->owner ? sprintf("[%s].%s", $p_src->owner, $p_src->name) : $p_src->name;
	330	push @diffs_proc_drops, "DROP PROCEDURE $proc_ident;\nGO\n";
	331	}
	332
	333	# Views
	334	my(%checked_views, @diffs_view_creates, @diffs_view_drops);
	335	VIEW:
	336	for my $v_tar ( $target_schema->get_views ) {
	337	for my $v_src ( $source_schema->get_views ) {
d1a895ce	338	if ( $v_tar->equals($v_src, $case_insensitive, $ignore_view_sql) ) {
7ac784ff	339	$checked_views{$v_src} = 1;
	340	next VIEW;
	341	}
	342	}
	343	push @diffs_view_creates, $v_tar->sql;
	344	}
	345	VIEW2:
	346	for my $v_src ( $source_schema->get_views ) {
	347	next if $checked_views{$v_src};
	348	for my $v_tar ( $target_schema->get_views ) {
d1a895ce	349	next VIEW2 if $v_src->equals($v_tar, $case_insensitive, $ignore_view_sql);
7ac784ff	350	}
	351	my $view_ident = $v_src->name;
	352	push @diffs_view_drops, "DROP VIEW $view_ident;\nGO\n";
	353	}
	354
ac0cd217	355	push @diffs, @diffs_view_drops, @diffs_proc_drops,
ac0cd217	356	@diffs_view_creates, @diffs_proc_creates;
7ac784ff	357
51ffe5ee	358	if ( @diffs ) {
bf459ae6	359	if ( $target_db !~ /^(MySQL\|SQLServer\|Oracle)$/ ) {
	360	unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
	361	}
51ffe5ee	362	return join( "\n",
	363	"-- Convert schema '$src_name' to '$tar_name':\n", @diffs, "\n"
	364	);
	365	}
	366	return undef;
	367	}
	368
	369	sub constraint_to_string {
	370	my $c = shift;
	371	my $source_db = shift;
	372	my $schema = shift or die "No schema given";
	373	my @fields = $c->field_names or return '';
	374
	375	if ( $c->type eq PRIMARY_KEY ) {
	376	if ( $source_db =~ /Oracle/ ) {
	377	return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
	378	'PRIMARY KEY (' . join(', ', @fields). ')';
	379	} else {
	380	return 'PRIMARY KEY (' . join(', ', @fields). ')';
	381	}
	382	}
	383	elsif ( $c->type eq UNIQUE ) {
	384	if ( $source_db =~ /Oracle/ ) {
	385	return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
	386	'UNIQUE (' . join(', ', @fields). ')';
	387	} else {
	388	return 'UNIQUE '.
	389	(defined $c->name ? $c->name.' ' : '').
	390	'(' . join(', ', @fields). ')';
	391	}
	392	}
	393	elsif ( $c->type eq FOREIGN_KEY ) {
	394	my $def = join(' ',
	395	map { $_ \|\| () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
	396	);
	397
	398	$def .= ' (' . join( ', ', @fields ) . ')';
	399
	400	$def .= ' REFERENCES ' . $c->reference_table;
	401
	402	my @rfields = map { $_ \|\| () } $c->reference_fields;
	403	unless ( @rfields ) {
	404	my $rtable_name = $c->reference_table;
	405	if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
	406	push @rfields, $ref_table->primary_key;
	407	}
	408	else {
	409	warn "Can't find reference table '$rtable_name' " .
	410	"in schema\n";
	411	}
	412	}
	413
	414	if ( @rfields ) {
	415	$def .= ' (' . join( ', ', @rfields ) . ')';
	416	}
	417	else {
	418	warn "FK constraint on " . 'some table' . '.' .
	419	join('', @fields) . " has no reference fields\n";
	420	}
	421
	422	if ( $c->match_type ) {
	423	$def .= ' MATCH ' .
	424	( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
	425	}
426
427	if ( $c->on_delete ) {
428	$def .= ' ON DELETE '.join( ' ', $c->on_delete );
429	}
430
431	if ( $c->on_update ) {
432	$def .= ' ON UPDATE '.join( ' ', $c->on_update );
433	}
434
435	return $def;
436	}
437	}
438
439	1;