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