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); |
12 | my $caseopt = $options->{caseopt} || 0; |
13 | my $debug = $options->{debug} || 0; |
14 | my $trace = $options->{trace} || 0; |
d990d84b |
15 | my $ignore_index_names = $options->{ignore_index_names} || 0; |
16 | my $ignore_constraint_names = $options->{ignore_constraint_names} || 0; |
51ffe5ee |
17 | |
18 | my $case_insensitive = $source_db =~ /SQLServer/ || $caseopt; |
19 | |
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 ); |
26 | |
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" |
30 | if $debug; |
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); |
37 | } |
38 | } |
39 | push @new_tables, $tar_table; |
40 | next; |
41 | } |
42 | |
43 | # Go through our options |
44 | my $options_different = 0; |
45 | my %checkedOptions; |
46 | OPTION: |
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; |
54 | last OPTION; |
55 | } |
56 | if ( defined $value_tar && $value_tar ne $value_src ) { |
57 | $options_different = 1; |
58 | last OPTION; |
59 | } |
60 | $checkedOptions{$key_tar} = 1; |
61 | next OPTION; |
62 | } |
63 | } |
64 | $options_different = 1; |
65 | last OPTION; |
66 | } |
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; |
73 | last; |
74 | } |
75 | } |
76 | # If there's a difference, just re-set all the options |
77 | my @diffs_table_options; |
78 | if ( $options_different ) { |
79 | my @options = (); |
80 | foreach my $option_ref ( $tar_table->options ) { |
81 | my($key, $value) = %{$option_ref}; |
82 | push(@options, defined $value ? "$key=$value" : $key); |
83 | } |
84 | my $options = join(' ', @options); |
85 | @diffs_table_options = ("ALTER TABLE $tar_table_name $options;"); |
86 | } |
87 | |
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; |
100 | |
101 | my $f_src_full_name = "$src_name.$src_table_name.$f_tar_name"; |
102 | |
103 | unless ( $src_table_field ) { |
104 | warn "Couldn't find field '$f_src_full_name' in '$src_table_name'\n" |
105 | if $debug; |
106 | my $temp_default_value = 0; |
107 | if ( $source_db =~ /SQLServer/ && |
108 | !$f_tar_nullable && |
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 : ''; |
115 | } |
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/ ? ')' : '', |
128 | ); |
129 | if ( $temp_default_value ) { |
130 | undef $f_tar_default; |
131 | push @diffs_table_adds, sprintf |
132 | ( <<END |
133 | DECLARE \@defname VARCHAR(100), \@cmd VARCHAR(1000) |
134 | SET \@defname = |
135 | (SELECT name |
136 | FROM sysobjects so JOIN sysconstraints sc |
137 | ON so.id = sc.constid |
138 | WHERE object_name(so.parent_obj) = '%s' |
139 | AND so.xtype = 'D' |
140 | AND sc.colid = |
141 | (SELECT colid FROM syscolumns |
142 | WHERE id = object_id('%s') AND |
143 | name = '%s')) |
144 | SET \@cmd = 'ALTER TABLE %s DROP CONSTRAINT ' |
145 | + \@defname |
146 | EXEC(\@cmd) |
147 | END |
148 | , $tar_table_name, $tar_table_name, $f_tar_name, $tar_table_name, |
149 | ); |
150 | } |
151 | next; |
152 | } |
153 | |
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/ ? ')' : '', |
175 | ); |
176 | next; |
177 | } |
178 | |
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)" : '', |
188 | $nullText, |
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/ ? ')' : '', |
195 | ); |
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, |
204 | ); |
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, |
210 | ); |
211 | } |
212 | } |
213 | } |
214 | |
215 | my(%checked_indices, @diffs_index_creates, @diffs_index_drops); |
216 | INDEX: |
217 | for my $i_tar ( $tar_table->get_indices ) { |
218 | for my $i_src ( $src_table->get_indices ) { |
d990d84b |
219 | if ( $i_tar->equals($i_src, $case_insensitive, $ignore_index_names) ) { |
51ffe5ee |
220 | $checked_indices{$i_src} = 1; |
221 | next INDEX; |
222 | } |
223 | } |
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 : '', |
228 | $tar_table_name, |
229 | join(",", $i_tar->fields), |
230 | ); |
231 | } |
232 | INDEX2: |
233 | for my $i_src ( $src_table->get_indices ) { |
03739eb3 |
234 | next if !$ignore_index_names && $checked_indices{$i_src}; |
51ffe5ee |
235 | for my $i_tar ( $tar_table->get_indices ) { |
d990d84b |
236 | next INDEX2 if $i_src->equals($i_tar, $case_insensitive, $ignore_index_names); |
51ffe5ee |
237 | } |
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;"; |
241 | } |
242 | |
243 | my(%checked_constraints, @diffs_constraint_drops); |
244 | CONSTRAINT: |
245 | for my $c_tar ( $tar_table->get_constraints ) { |
246 | next if $target_db =~ /Oracle/ && |
d990d84b |
247 | $c_tar->type eq UNIQUE && $c_tar->name =~ /^SYS_/i; # Ignore Oracle SYS_ constraints hack |
51ffe5ee |
248 | for my $c_src ( $src_table->get_constraints ) { |
d990d84b |
249 | if ( $c_tar->equals($c_src, $case_insensitive, $ignore_constraint_names) ) { |
51ffe5ee |
250 | $checked_constraints{$c_src} = 1; |
251 | next CONSTRAINT; |
252 | } |
253 | } |
254 | push @diffs_at_end, "ALTER TABLE $tar_table_name ADD ". |
255 | constraint_to_string($c_tar, $source_db, $target_schema).";"; |
256 | } |
257 | CONSTRAINT2: |
258 | for my $c_src ( $src_table->get_constraints ) { |
99b0dca6 |
259 | next if $source_db =~ /Oracle/ && |
d990d84b |
260 | $c_src->type eq UNIQUE && $c_src->name =~ /^SYS_/i; # Ignore Oracle SYS_ constraints hack |
03739eb3 |
261 | next if !$ignore_constraint_names && $checked_constraints{$c_src}; |
51ffe5ee |
262 | for my $c_tar ( $tar_table->get_constraints ) { |
d990d84b |
263 | next CONSTRAINT2 if $c_src->equals($c_tar, $case_insensitive, $ignore_constraint_names); |
51ffe5ee |
264 | } |
265 | if ( $c_src->type eq UNIQUE ) { |
266 | push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP INDEX ". |
267 | $c_src->name.";"; |
268 | } elsif ( $source_db =~ /SQLServer/ ) { |
269 | push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->name.";"; |
270 | } else { |
271 | push @diffs_constraint_drops, "ALTER TABLE $tar_table_name DROP ".$c_src->type. |
272 | ($c_src->type eq FOREIGN_KEY ? " ".$c_src->name : '').";"; |
273 | } |
274 | } |
275 | |
276 | push @diffs, @diffs_index_drops, @diffs_constraint_drops, |
277 | @diffs_table_options, @diffs_table_adds, |
278 | @diffs_table_changes, @diffs_index_creates; |
279 | } |
280 | |
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/ ); |
284 | |
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.";"; |
290 | } |
291 | } |
292 | push @diffs_at_end, "DROP TABLE $src_table_name;"; |
293 | next; |
294 | } |
295 | |
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;"; |
302 | } |
303 | } |
304 | } |
305 | |
306 | if ( @new_tables ) { |
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 ); |
311 | } |
312 | push(@diffs, @diffs_at_end); |
313 | |
314 | if ( @diffs ) { |
bf459ae6 |
315 | if ( $target_db !~ /^(MySQL|SQLServer|Oracle)$/ ) { |
316 | unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!"); |
317 | } |
51ffe5ee |
318 | return join( "\n", |
319 | "-- Convert schema '$src_name' to '$tar_name':\n", @diffs, "\n" |
320 | ); |
321 | } |
322 | return undef; |
323 | } |
324 | |
325 | sub constraint_to_string { |
326 | my $c = shift; |
327 | my $source_db = shift; |
328 | my $schema = shift or die "No schema given"; |
329 | my @fields = $c->field_names or return ''; |
330 | |
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). ')'; |
335 | } else { |
336 | return 'PRIMARY KEY (' . join(', ', @fields). ')'; |
337 | } |
338 | } |
339 | elsif ( $c->type eq UNIQUE ) { |
340 | if ( $source_db =~ /Oracle/ ) { |
341 | return (defined $c->name ? 'CONSTRAINT '.$c->name.' ' : '') . |
342 | 'UNIQUE (' . join(', ', @fields). ')'; |
343 | } else { |
344 | return 'UNIQUE '. |
345 | (defined $c->name ? $c->name.' ' : ''). |
346 | '(' . join(', ', @fields). ')'; |
347 | } |
348 | } |
349 | elsif ( $c->type eq FOREIGN_KEY ) { |
350 | my $def = join(' ', |
351 | map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY' |
352 | ); |
353 | |
354 | $def .= ' (' . join( ', ', @fields ) . ')'; |
355 | |
356 | $def .= ' REFERENCES ' . $c->reference_table; |
357 | |
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; |
363 | } |
364 | else { |
365 | warn "Can't find reference table '$rtable_name' " . |
366 | "in schema\n"; |
367 | } |
368 | } |
369 | |
370 | if ( @rfields ) { |
371 | $def .= ' (' . join( ', ', @rfields ) . ')'; |
372 | } |
373 | else { |
374 | warn "FK constraint on " . 'some table' . '.' . |
375 | join('', @fields) . " has no reference fields\n"; |
376 | } |
377 | |
378 | if ( $c->match_type ) { |
379 | $def .= ' MATCH ' . |
380 | ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL'; |
381 | } |
382 | |
383 | if ( $c->on_delete ) { |
384 | $def .= ' ON DELETE '.join( ' ', $c->on_delete ); |
385 | } |
386 | |
387 | if ( $c->on_update ) { |
388 | $def .= ' ON UPDATE '.join( ' ', $c->on_update ); |
389 | } |
390 | |
391 | return $def; |
392 | } |
393 | } |
394 | |
395 | 1; |