1 package SQL::Translator::Diff;
2 ## SQLT schema diffing code
5 use SQL::Translator::Schema::Constants;
10 my ($source_schema, $source_db, $target_schema, $target_db, $options) = @_;
11 # print Data::Dumper::Dumper($target_schema);
12 my $caseopt = $options->{caseopt} || 0;
13 my $debug = $options->{debug} || 0;
14 my $trace = $options->{trace} || 0;
15 my $ignore_index_names = $options->{ignore_index_names} || 0;
16 my $ignore_constraint_names = $options->{ignore_constraint_names} || 0;
18 my $case_insensitive = $source_db =~ /SQLServer/ || $caseopt;
20 my $tar_name = $target_schema->name;
21 my $src_name = $source_schema->name;
22 my ( @new_tables, @diffs , @diffs_at_end);
23 for my $tar_table ( $target_schema->get_tables ) {
24 my $tar_table_name = $tar_table->name;
25 my $src_table = $source_schema->get_table( $tar_table_name, $case_insensitive );
27 warn "TABLE '$tar_name.$tar_table_name'\n" if $debug;
28 unless ( $src_table ) {
29 warn "Couldn't find table '$tar_name.$tar_table_name' in '$src_name'\n"
31 if ( $source_db =~ /(SQLServer|Oracle)/ ) {
32 for my $constraint ( $tar_table->get_constraints ) {
33 next if $constraint->type ne FOREIGN_KEY;
34 push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ".
35 constraint_to_string($constraint, $source_db, $target_schema).";";
36 $tar_table->drop_constraint($constraint);
39 push @new_tables, $tar_table;
43 # Go through our options
44 my $options_different = 0;
47 for my $tar_table_option_ref ( $tar_table->options ) {
48 my($key_tar, $value_tar) = %{$tar_table_option_ref};
49 for my $src_table_option_ref ( $src_table->options ) {
50 my($key_src, $value_src) = %{$src_table_option_ref};
51 if ( $key_tar eq $key_src ) {
52 if ( defined $value_tar != defined $value_src ) {
53 $options_different = 1;
56 if ( defined $value_tar && $value_tar ne $value_src ) {
57 $options_different = 1;
60 $checkedOptions{$key_tar} = 1;
64 $options_different = 1;
67 # Go through the other table's options
68 unless ( $options_different ) {
69 for my $src_table_option_ref ( $src_table->options ) {
70 my($key, $value) = %{$src_table_option_ref};
71 next if $checkedOptions{$key};
72 $options_different = 1;
76 # If there's a difference, just re-set all the options
77 my @diffs_table_options;
78 if ( $options_different ) {
80 foreach my $option_ref ( $tar_table->options ) {
81 my($key, $value) = %{$option_ref};
82 push(@options, defined $value ? "$key=$value" : $key);
84 my $options = join(' ', @options);
85 @diffs_table_options = ("ALTER TABLE $tar_table_name $options;");
88 my $src_table_name = $src_table->name;
89 my(@diffs_table_adds, @diffs_table_changes);
90 for my $tar_table_field ( $tar_table->get_fields ) {
91 my $f_tar_type = $tar_table_field->data_type;
92 my $f_tar_size = $tar_table_field->size;
93 my $f_tar_name = $tar_table_field->name;
94 my $f_tar_nullable = $tar_table_field->is_nullable;
95 my $f_tar_default = $tar_table_field->default_value;
96 my $f_tar_auto_inc = $tar_table_field->is_auto_increment;
97 my $src_table_field = $src_table->get_field( $f_tar_name, $case_insensitive );
98 my $f_tar_full_name = "$tar_name.$tar_table_name.$tar_table_name";
99 warn "FIELD '$f_tar_full_name'\n" if $debug;
101 my $f_src_full_name = "$src_name.$src_table_name.$f_tar_name";
103 unless ( $src_table_field ) {
104 warn "Couldn't find field '$f_src_full_name' in '$src_table_name'\n"
106 my $temp_default_value = 0;
107 if ( $source_db =~ /SQLServer/ &&
109 !defined $f_tar_default ) {
110 # SQL Server doesn't allow adding non-nullable, non-default columns
111 # so we add it with a default value, then remove the default value
112 $temp_default_value = 1;
113 my(@numeric_types) = qw(decimal numeric float real int bigint smallint tinyint);
114 $f_tar_default = grep($_ eq $f_tar_type, @numeric_types) ? 0 : '';
116 push @diffs_table_adds, sprintf
117 ( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
118 $tar_table_name, $source_db =~ /Oracle/ ? '(' : '',
119 $f_tar_name, $f_tar_type,
120 ($f_tar_size && $f_tar_type !~ /(blob|text)$/) ? "($f_tar_size)" : '',
121 !defined $f_tar_default ? ''
122 : uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL'
123 : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
124 : " DEFAULT '$f_tar_default'",
125 $f_tar_nullable ? '' : ' NOT NULL',
126 $f_tar_auto_inc ? ' AUTO_INCREMENT' : '',
127 $source_db =~ /Oracle/ ? ')' : '',
129 if ( $temp_default_value ) {
130 undef $f_tar_default;
131 push @diffs_table_adds, sprintf
133 DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000)
136 FROM sysobjects so JOIN sysconstraints sc
137 ON so.id = sc.constid
138 WHERE object_name(so.parent_obj) = '%s'
141 (SELECT colid FROM syscolumns
142 WHERE id = object_id('%s') AND
144 SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT '
148 , $tar_table_name, $tar_table_name, $f_tar_name, $tar_table_name,
154 my $f_src_type = $src_table_field->data_type;
155 my $f_src_size = $src_table_field->size || '';
156 my $f_src_nullable = $src_table_field->is_nullable;
157 my $f_src_default = $src_table_field->default_value;
158 my $f_src_auto_inc = $src_table_field->is_auto_increment;
159 if ( !$tar_table_field->equals($src_table_field, $case_insensitive) ) {
160 # SQLServer timestamp fields can't be altered, so we drop and add instead
161 if ( $source_db =~ /SQLServer/ && $f_src_type eq "timestamp" ) {
162 push @diffs_table_changes, "ALTER TABLE $tar_table_name DROP COLUMN $f_tar_name;";
163 push @diffs_table_changes, sprintf
164 ( "ALTER TABLE %s ADD %s%s %s%s%s%s%s%s;",
165 $tar_table_name, $source_db =~ /Oracle/ ? '(' : '',
166 $f_tar_name, $f_tar_type,
167 ($f_tar_size && $f_tar_type !~ /(blob|text)$/) ? "($f_tar_size)" : '',
168 !defined $f_tar_default ? ''
169 : uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL'
170 : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
171 : " DEFAULT '$f_tar_default'",
172 $f_tar_nullable ? '' : ' NOT NULL',
173 $f_tar_auto_inc ? ' AUTO_INCREMENT' : '',
174 $source_db =~ /Oracle/ ? ')' : '',
179 my $changeText = $source_db =~ /SQLServer/ ? 'ALTER COLUMN' :
180 $source_db =~ /Oracle/ ? 'MODIFY (' : 'CHANGE';
181 my $nullText = $f_tar_nullable ? '' : ' NOT NULL';
182 $nullText = '' if $source_db =~ /Oracle/ && $f_tar_nullable == $f_src_nullable;
183 push @diffs_table_changes, sprintf
184 ( "ALTER TABLE %s %s %s%s %s%s%s%s%s%s;",
185 $tar_table_name, $changeText,
186 $f_tar_name, $source_db =~ /MySQL/ ? " $f_tar_name" : '',
187 $f_tar_type, ($f_tar_size && $f_tar_type !~ /(blob|text)$/) ? "($f_tar_size)" : '',
189 !defined $f_tar_default || $source_db =~ /SQLServer/ ? ''
190 : uc $f_tar_default eq 'NULL' ? ' DEFAULT NULL'
191 : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? ' DEFAULT CURRENT_TIMESTAMP'
192 : " DEFAULT '$f_tar_default'",
193 $f_tar_auto_inc ? ' AUTO_INCREMENT' : '',
194 $source_db =~ /Oracle/ ? ')' : '',
196 if ( defined $f_tar_default && $source_db =~ /SQLServer/ ) {
197 # Adding a column with a default value for SQL Server means adding a
198 # constraint and setting existing NULLs to the default value
199 push @diffs_table_changes, sprintf
200 ( "ALTER TABLE %s ADD CONSTRAINT DF_%s_%s %s FOR %s;",
201 $tar_table_name, $tar_table_name, $f_tar_name, uc $f_tar_default eq 'NULL' ? 'DEFAULT NULL'
202 : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? 'DEFAULT CURRENT_TIMESTAMP'
203 : "DEFAULT '$f_tar_default'", $f_tar_name,
205 push @diffs_table_changes, sprintf
206 ( "UPDATE %s SET %s = %s WHERE %s IS NULL;",
207 $tar_table_name, $f_tar_name, uc $f_tar_default eq 'NULL' ? 'NULL'
208 : uc $f_tar_default eq 'CURRENT_TIMESTAMP' ? 'CURRENT_TIMESTAMP'
209 : "'$f_tar_default'", $f_tar_name,
215 my(%checked_indices, @diffs_index_creates, @diffs_index_drops);
217 for my $i_tar ( $tar_table->get_indices ) {
218 for my $i_src ( $src_table->get_indices ) {
219 if ( $i_tar->equals($i_src, $case_insensitive, $ignore_index_names) ) {
220 $checked_indices{$i_src} = 1;
224 push @diffs_index_creates, sprintf
225 ( "CREATE %sINDEX%s ON %s (%s);",
226 $i_tar->type eq NORMAL ? '' : $i_tar->type." ",
227 $i_tar->name ? " ".$i_tar->name : '',
229 join(",", $i_tar->fields),
233 for my $i_src ( $src_table->get_indices ) {
234 next if $checked_indices{$i_src};
235 for my $i_tar ( $tar_table->get_indices ) {
236 next INDEX2 if $i_src->equals($i_tar, $case_insensitive, $ignore_index_names);
238 $source_db =~ /SQLServer/
239 ? push @diffs_index_drops, "DROP INDEX $tar_table_name.".$i_src->name.";"
240 : push @diffs_index_drops, "DROP INDEX ".$i_src->name." on $tar_table_name;";
243 my(%checked_constraints, @diffs_constraint_drops);
245 for my $c_tar ( $tar_table->get_constraints ) {
246 next if $target_db =~ /Oracle/ &&
247 $c_tar->type eq UNIQUE && $c_tar->name =~ /^SYS_/i; # Ignore Oracle SYS_ constraints hack
248 for my $c_src ( $src_table->get_constraints ) {
249 if ( $c_tar->equals($c_src, $case_insensitive, $ignore_constraint_names) ) {
250 $checked_constraints{$c_src} = 1;
254 push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ".
255 constraint_to_string($c_tar, $source_db, $target_schema).";";
258 for my $c_src ( $src_table->get_constraints ) {
259 next if $source_db =~ /Oracle/ &&
260 $c_src->type eq UNIQUE && $c_src->name =~ /^SYS_/i; # Ignore Oracle SYS_ constraints hack
261 next if $checked_constraints{$c_src};
262 for my $c_tar ( $tar_table->get_constraints ) {
263 next CONSTRAINT2 if $c_src->equals($c_tar, $case_insensitive, $ignore_constraint_names);
265 if ( $c_src->type eq UNIQUE ) {
266 push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP INDEX ".
268 } elsif ( $source_db =~ /SQLServer/ ) {
269 push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->name.";";
271 push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->type.
272 ($c_src->type eq FOREIGN_KEY ? " ".$c_src->name : '').";";
276 push @diffs, @diffs_index_drops, @diffs_constraint_drops,
277 @diffs_table_options, @diffs_table_adds,
278 @diffs_table_changes, @diffs_index_creates;
281 for my $src_table ( $source_schema->get_tables ) {
282 my $src_table_name = $src_table->name;
283 my $tar_table = $target_schema->get_table( $src_table_name, $source_db =~ /SQLServer/ );
285 unless ( $tar_table ) {
286 if ( $source_db =~ /SQLServer/ ) {
287 for my $constraint ( $src_table->get_constraints ) {
288 next if $constraint->type eq PRIMARY_KEY;
289 push @diffs, "ALTER TABLE $src_table_name DROP ".$constraint->name.";";
292 push @diffs_at_end, "DROP TABLE $src_table_name;";
296 for my $src_table_field ( $src_table->get_fields ) {
297 my $f_src_name = $src_table_field->name;
298 my $tar_table_field = $tar_table->get_field( $f_src_name );
299 unless ( $tar_table_field ) {
300 my $modifier = $source_db =~ /SQLServer/ ? "COLUMN " : '';
301 push @diffs, "ALTER TABLE $src_table_name DROP $modifier$f_src_name;";
307 my $dummytr = SQL::Translator->new;
308 $dummytr->schema->add_table( $_ ) for @new_tables;
309 my $producer = $dummytr->producer( $source_db );
310 unshift @diffs, $producer->( $dummytr );
312 push(@diffs, @diffs_at_end);
315 if ( $target_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
316 unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
319 "-- Convert schema '$src_name' to '$tar_name':\n", @diffs, "\n"
325 sub constraint_to_string {
327 my $source_db = shift;
328 my $schema = shift or die "No schema given";
329 my @fields = $c->field_names or return '';
331 if ( $c->type eq PRIMARY_KEY ) {
332 if ( $source_db =~ /Oracle/ ) {
333 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
334 'PRIMARY KEY (' . join(', ', @fields). ')';
336 return 'PRIMARY KEY (' . join(', ', @fields). ')';
339 elsif ( $c->type eq UNIQUE ) {
340 if ( $source_db =~ /Oracle/ ) {
341 return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') .
342 'UNIQUE (' . join(', ', @fields). ')';
345 (defined $c->name ? $c->name.' ' : '').
346 '(' . join(', ', @fields). ')';
349 elsif ( $c->type eq FOREIGN_KEY ) {
351 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
354 $def .= ' (' . join( ', ', @fields ) . ')';
356 $def .= ' REFERENCES ' . $c->reference_table;
358 my @rfields = map { $_ || () } $c->reference_fields;
359 unless ( @rfields ) {
360 my $rtable_name = $c->reference_table;
361 if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
362 push @rfields, $ref_table->primary_key;
365 warn "Can't find reference table '$rtable_name' " .
371 $def .= ' (' . join( ', ', @rfields ) . ')';
374 warn "FK constraint on " . 'some table' . '.' .
375 join('', @fields) . " has no reference fields\n";
378 if ( $c->match_type ) {
380 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
383 if ( $c->on_delete ) {
384 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
387 if ( $c->on_update ) {
388 $def .= ' ON UPDATE '.join( ' ', $c->on_update );