1 package SQL::Translator::Diff;
4 ## SQLT schema diffing code
9 use Carp::Clan qw/^SQL::Translator/;
10 use SQL::Translator::Schema::Constants;
11 use Sub::Quote qw(quote_sub);
14 has ignore_index_names => (
17 has ignore_constraint_names => (
20 has ignore_view_sql => (
23 has ignore_proc_sql => (
29 has source_schema => (
32 has target_schema => (
35 has case_insensitive => (
38 has no_batch_alters => (
41 has ignore_missing_methods => (
44 has producer_args => (
47 default => quote_sub '{}',
49 has tables_to_drop => (
52 default => quote_sub '[]',
54 has tables_to_create => (
57 default => quote_sub '[]',
59 has table_diff_hash => (
62 default => quote_sub '{}',
70 my @diff_hash_keys = qw/
85 ## we are getting instructions on how to turn the source into the target
86 ## source == original, target == new (hmm, if I need to comment this, should I rename the vars again ??)
87 ## _schema isa SQL::Translator::Schema
88 ## _db is the name of the producer/db it came out of/into
89 ## results are formatted to the source preferences
91 my ($source_schema, $source_db, $target_schema, $output_db, $options) = @_;
94 my $obj = SQL::Translator::Diff->new( {
96 source_schema => $source_schema,
97 target_schema => $target_schema,
98 output_db => $output_db
101 $obj->compute_differences->produce_diff_sql;
105 my ($self, $args) = @_;
106 if ($args->{producer_options}) {
107 carp 'producer_options is deprecated. Please use producer_args';
108 $self->producer_args({
109 %{$args->{producer_options}},
110 %{$self->producer_args}
114 if (! $self->output_db) {
115 $self->output_db($args->{source_db})
119 sub compute_differences {
122 my $target_schema = $self->target_schema;
123 my $source_schema = $self->source_schema;
125 my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}";
126 eval "require $producer_class";
129 if (my $preprocess = $producer_class->can('preprocess_schema')) {
130 $preprocess->($source_schema);
131 $preprocess->($target_schema);
134 my %src_tables_checked = ();
135 my @tar_tables = sort { $a->name cmp $b->name } $target_schema->get_tables;
136 ## do original/source tables exist in target?
137 for my $tar_table ( @tar_tables ) {
138 my $tar_table_name = $tar_table->name;
142 $self->table_diff_hash->{$tar_table_name} = {
143 map {$_ => [] } @diff_hash_keys
146 if (my $old_name = $tar_table->extra('renamed_from')) {
147 $src_table = $source_schema->get_table( $old_name, $self->case_insensitive );
149 $self->table_diff_hash->{$tar_table_name}{table_renamed_from} = [ [$src_table, $tar_table] ];
151 delete $tar_table->extra->{renamed_from};
152 carp qq#Renamed table can't find old table "$old_name" for renamed table\n#;
155 $src_table = $source_schema->get_table( $tar_table_name, $self->case_insensitive );
158 unless ( $src_table ) {
160 ## add table(s) later.
161 push @{$self->tables_to_create}, $tar_table;
165 my $src_table_name = $src_table->name;
166 $src_table_name = lc $src_table_name if $self->case_insensitive;
167 $src_tables_checked{$src_table_name} = 1;
170 $self->diff_table_options($src_table, $tar_table);
172 ## Compare fields, their types, defaults, sizes etc etc
173 $self->diff_table_fields($src_table, $tar_table);
175 $self->diff_table_indexes($src_table, $tar_table);
176 $self->diff_table_constraints($src_table, $tar_table);
178 } # end of target_schema->get_tables loop
180 for my $src_table ( $source_schema->get_tables ) {
181 my $src_table_name = $src_table->name;
183 $src_table_name = lc $src_table_name if $self->case_insensitive;
185 push @{ $self->tables_to_drop}, $src_table
186 unless $src_tables_checked{$src_table_name};
192 sub produce_diff_sql {
195 my $target_schema = $self->target_schema;
196 my $source_schema = $self->source_schema;
197 my $tar_name = $target_schema->name;
198 my $src_name = $source_schema->name;
200 my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}";
201 eval "require $producer_class";
204 # Map of name we store under => producer method name
206 constraints_to_create => 'alter_create_constraint',
207 constraints_to_drop => 'alter_drop_constraint',
208 indexes_to_create => 'alter_create_index',
209 indexes_to_drop => 'alter_drop_index',
210 fields_to_create => 'add_field',
211 fields_to_alter => 'alter_field',
212 fields_to_rename => 'rename_field',
213 fields_to_drop => 'drop_field',
214 table_options => 'alter_table',
215 table_renamed_from => 'rename_table',
219 if (!$self->no_batch_alters &&
220 (my $batch_alter = $producer_class->can('batch_alter_table')) )
222 # Good - Producer supports batch altering of tables.
223 foreach my $table ( sort keys %{$self->table_diff_hash} ) {
224 my $tar_table = $target_schema->get_table($table)
225 || $source_schema->get_table($table);
227 push @diffs, $batch_alter->($tar_table,
229 $func_map{$_} => $self->table_diff_hash->{$table}{$_}
237 # If we have any table renames we need to do those first;
239 foreach my $table ( sort keys %{$self->table_diff_hash} ) {
240 my $table_diff = $self->table_diff_hash->{$table};
241 for (@diff_hash_keys) {
242 push( @{ $flattened_diffs{ $func_map{$_} } ||= [] }, @{ $table_diff->{$_} } );
247 if (@{ $flattened_diffs{$_} || [] }) {
248 my $meth = $producer_class->can($_);
251 my $sql = $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $self->producer_args );
252 $sql ? ("$sql") : ();
253 } @{ $flattened_diffs{$_} }
254 : $self->ignore_missing_methods
255 ? "-- $producer_class cant $_"
256 : die "$producer_class cant $_";
260 alter_drop_constraint
267 alter_create_constraint
271 if (my @tables = @{ $self->tables_to_create } ) {
272 my $translator = new SQL::Translator(
273 producer_type => $self->output_db,
276 # TODO: sort out options
277 %{ $self->producer_args }
279 $translator->producer_args->{no_transaction} = 1;
280 my $schema = $translator->schema;
282 $schema->add_table($_) for @tables;
285 # Remove begin/commit here, since we wrap everything in one.
286 grep { $_ !~ /^(?:COMMIT|START(?: TRANSACTION)?|BEGIN(?: TRANSACTION)?)/ } $producer_class->can('produce')->($translator);
289 if (my @tables_to_drop = @{ $self->{tables_to_drop} || []} ) {
290 my $meth = $producer_class->can('drop_table');
292 push @diffs, $meth ? ( map { $meth->($_, $self->producer_args) } @tables_to_drop)
293 : $self->ignore_missing_methods
294 ? "-- $producer_class cant drop_table"
295 : die "$producer_class cant drop_table";
299 unshift @diffs, "BEGIN";
300 push @diffs, "\nCOMMIT";
302 @diffs = ("-- No differences found");
306 if ( $self->output_db !~ /^(?:MySQL|SQLite|PostgreSQL)$/ ) {
307 unshift(@diffs, "-- Output database @{[$self->output_db]} is untested/unsupported!!!");
311 map { $_ ? ( $_ =~ /;$/xms ? $_ : "$_;\n\n" ) : "\n" }
312 ("-- Convert schema '$src_name' to '$tar_name':", @diffs);
314 return wantarray ? @return : join('', @return);
320 sub diff_table_indexes {
321 my ($self, $src_table, $tar_table) = @_;
323 my (%checked_indices);
325 for my $i_tar ( $tar_table->get_indices ) {
326 for my $i_src ( $src_table->get_indices ) {
327 if ( $i_tar->equals($i_src, $self->case_insensitive, $self->ignore_index_names) ) {
328 $checked_indices{$i_src} = 1;
332 push @{$self->table_diff_hash->{$tar_table}{indexes_to_create}}, $i_tar;
336 for my $i_src ( $src_table->get_indices ) {
337 next if !$self->ignore_index_names && $checked_indices{$i_src};
338 for my $i_tar ( $tar_table->get_indices ) {
339 next INDEX_DROP if $i_src->equals($i_tar, $self->case_insensitive, $self->ignore_index_names);
341 push @{$self->table_diff_hash->{$tar_table}{indexes_to_drop}}, $i_src;
346 sub diff_table_constraints {
347 my ($self, $src_table, $tar_table) = @_;
349 my(%checked_constraints);
351 for my $c_tar ( $tar_table->get_constraints ) {
352 for my $c_src ( $src_table->get_constraints ) {
354 # This is a bit of a hack - needed for renaming tables to work
355 local $c_src->{table} = $tar_table;
357 if ( $c_tar->equals($c_src, $self->case_insensitive, $self->ignore_constraint_names) ) {
358 $checked_constraints{$c_src} = 1;
359 next CONSTRAINT_CREATE;
362 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_create} }, $c_tar;
367 for my $c_src ( $src_table->get_constraints ) {
369 # This is a bit of a hack - needed for renaming tables to work
370 local $c_src->{table} = $tar_table;
372 next if !$self->ignore_constraint_names && $checked_constraints{$c_src};
373 for my $c_tar ( $tar_table->get_constraints ) {
374 next CONSTRAINT_DROP if $c_src->equals($c_tar, $self->case_insensitive, $self->ignore_constraint_names);
377 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_drop} }, $c_src;
382 sub diff_table_fields {
383 my ($self, $src_table, $tar_table) = @_;
385 # List of ones ew've renamed from so we dont drop them
386 my %renamed_source_fields;
388 for my $tar_table_field ( $tar_table->get_fields ) {
389 my $f_tar_name = $tar_table_field->name;
391 if (my $old_name = $tar_table_field->extra->{renamed_from}) {
392 my $src_table_field = $src_table->get_field( $old_name, $self->case_insensitive );
393 unless ($src_table_field) {
394 carp qq#Renamed column can't find old column "@{[$src_table->name]}.$old_name" for renamed column\n#;
395 delete $tar_table_field->extra->{renamed_from};
397 push @{$self->table_diff_hash->{$tar_table}{fields_to_rename} }, [ $src_table_field, $tar_table_field ];
398 $renamed_source_fields{$old_name} = 1;
403 my $src_table_field = $src_table->get_field( $f_tar_name, $self->case_insensitive );
405 unless ( $src_table_field ) {
406 push @{$self->table_diff_hash->{$tar_table}{fields_to_create}}, $tar_table_field;
410 # field exists, something changed. This is a bit complex. Parsers can
411 # normalize types, but only some of them do, so compare the normalized and
412 # parsed types for each field to each other
413 if ( !$tar_table_field->equals($src_table_field, $self->case_insensitive) &&
414 !$tar_table_field->equals($src_table_field->parsed_field, $self->case_insensitive) &&
415 !$tar_table_field->parsed_field->equals($src_table_field, $self->case_insensitive) &&
416 !$tar_table_field->parsed_field->equals($src_table_field->parsed_field, $self->case_insensitive) ) {
418 # Some producers might need src field to diff against
419 push @{$self->table_diff_hash->{$tar_table}{fields_to_alter}}, [ $src_table_field, $tar_table_field ];
425 # Now check to see if any fields from src_table need to be dropped
426 for my $src_table_field ( $src_table->get_fields ) {
427 my $f_src_name = $src_table_field->name;
428 next if $renamed_source_fields{$f_src_name};
430 my $tar_table_field = $tar_table->get_field( $f_src_name, $self->case_insensitive );
432 unless ( $tar_table_field ) {
433 push @{$self->table_diff_hash->{$tar_table}{fields_to_drop}}, $src_table_field;
439 sub diff_table_options {
440 my ($self, $src_table, $tar_table) = @_;
443 my ($a_name, undef, $b_name, undef) = ( %$a, %$b );
446 # Need to sort the options so we dont get supruious diffs.
447 my (@src_opts, @tar_opts);
448 @src_opts = sort $cmp $src_table->options;
449 @tar_opts = sort $cmp $tar_table->options;
452 # If there's a difference, just re-set all the options
453 push @{ $self->table_diff_hash->{$tar_table}{table_options} }, $tar_table
454 unless $src_table->_compare_objects( \@src_opts, \@tar_opts );
457 # support producer_options as an alias for producer_args for legacy code.
458 sub producer_options {
461 return $self->producer_args( @_ );
470 SQL::Translator::Diff - determine differences between two schemas
474 Takes two input SQL::Translator::Schemas (or SQL files) and produces ALTER
475 statments to make them the same
481 use SQL::Translator::Diff;
482 my $sql = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL', $options_hash)
486 use SQL::Translator::Diff;
487 my $diff = SQL::Translator::Diff->new({
488 output_db => 'MySQL',
489 source_schema => $source_schema,
490 target_schema => $target_schema,
492 })->compute_differences->produce_diff_sql;
498 =item B<ignore_index_names>
500 Match indexes based on types and fields, ignoring name.
502 =item B<ignore_constraint_names>
504 Match constrains based on types, fields and tables, ignoring name.
508 Which producer to use to produce the output.
510 =item B<case_insensitive>
512 Ignore case of table, field, index and constraint names when comparing
514 =item B<no_batch_alters>
516 Produce each alter as a distinct C<ALTER TABLE> statement even if the producer
517 supports the ability to do all alters for a table as one statement.
519 =item B<ignore_missing_methods>
521 If the diff would need a method that is missing from the producer, just emit a
522 comment showing the method is missing, rather than dieing with an error
526 =head1 PRODUCER FUNCTIONS
528 The following producer functions should be implemented for completeness. If
529 any of them are needed for a given diff, but not found, an error will be
534 =item * C<alter_create_constraint($con)>
536 =item * C<alter_drop_constraint($con)>
538 =item * C<alter_create_index($idx)>
540 =item * C<alter_drop_index($idx)>
542 =item * C<add_field($fld)>
544 =item * C<alter_field($old_fld, $new_fld)>
546 =item * C<rename_field($old_fld, $new_fld)>
548 =item * C<drop_field($fld)>
550 =item * C<alter_table($table)>
552 =item * C<drop_table($table)>
554 =item * C<rename_table($old_table, $new_table)> (optional)
556 =item * C<batch_alter_table($table, $hash)> (optional)
558 If the producer supports C<batch_alter_table>, it will be called with the
559 table to alter and a hash, the keys of which will be the method names listed
560 above; values will be arrays of fields or constraints to operate on. In the
561 case of the field functions that take two arguments this will appear as a hash.
563 I.e. the hash might look something like the following:
566 alter_create_constraint => [ $constraint1, $constraint2 ],
567 add_field => [ $field ],
568 alter_field => [ [$old_field, $new_field] ]
572 =item * C<preprocess_schema($class, $schema)> (optional)
574 C<preprocess_schema> is called by the Diff code to allow the producer to
575 normalize any data it needs to first. For example, the MySQL producer uses
576 this method to ensure that FK contraint names are unique.
578 Basicaly any changes that need to be made to produce the SQL file for the
579 schema should be done here, so that a diff between a parsed SQL file and (say)
580 a parsed DBIx::Class::Schema object will be sane.
582 (As an aside, DBIx::Class, for instance, uses the presence of a
583 C<preprocess_schema> function on the producer to know that it can diff between
584 the previous SQL file and its own internal representation. Without this method
585 on th producer it will diff the two SQL files which is slower, but known to
586 work better on old-style producers.)
593 Original Author(s) unknown.
595 Refactor/re-write and more comprehensive tests by Ash Berlin C<< ash@cpan.org >>.
597 Redevelopment sponsored by Takkle Inc.