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 map { $_ ? "$_" : () } $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $self->producer_args );
252 } @{ $flattened_diffs{$_} }
253 : $self->ignore_missing_methods
254 ? "-- $producer_class cant $_"
255 : die "$producer_class cant $_";
259 alter_drop_constraint
266 alter_create_constraint
270 if (my @tables = @{ $self->tables_to_create } ) {
271 my $translator = SQL::Translator->new(
272 producer_type => $self->output_db,
275 # TODO: sort out options
276 %{ $self->producer_args }
278 $translator->producer_args->{no_transaction} = 1;
279 my $schema = $translator->schema;
281 $schema->add_table($_) for @tables;
284 # Remove begin/commit here, since we wrap everything in one.
285 grep { $_ !~ /^(?:COMMIT|START(?: TRANSACTION)?|BEGIN(?: TRANSACTION)?)/ } $producer_class->can('produce')->($translator);
288 if (my @tables_to_drop = @{ $self->{tables_to_drop} || []} ) {
289 my $meth = $producer_class->can('drop_table');
291 push @diffs, $meth ? ( map { $meth->($_, $self->producer_args) } @tables_to_drop)
292 : $self->ignore_missing_methods
293 ? "-- $producer_class cant drop_table"
294 : die "$producer_class cant drop_table";
298 unshift @diffs, "BEGIN";
299 push @diffs, "\nCOMMIT";
301 @diffs = ("-- No differences found");
305 if ( $self->output_db !~ /^(?:MySQL|SQLite|PostgreSQL)$/ ) {
306 unshift(@diffs, "-- Output database @{[$self->output_db]} is untested/unsupported!!!");
310 map { $_ ? ( $_ =~ /;\s*\z/xms ? $_ : "$_;\n\n" ) : "\n" }
311 ("-- Convert schema '$src_name' to '$tar_name':", @diffs);
313 return wantarray ? @return : join('', @return);
319 sub diff_table_indexes {
320 my ($self, $src_table, $tar_table) = @_;
322 my (%checked_indices);
324 for my $i_tar ( $tar_table->get_indices ) {
325 for my $i_src ( $src_table->get_indices ) {
326 if ( $i_tar->equals($i_src, $self->case_insensitive, $self->ignore_index_names) ) {
327 $checked_indices{$i_src} = 1;
331 push @{$self->table_diff_hash->{$tar_table}{indexes_to_create}}, $i_tar;
335 for my $i_src ( $src_table->get_indices ) {
336 next if !$self->ignore_index_names && $checked_indices{$i_src};
337 for my $i_tar ( $tar_table->get_indices ) {
338 next INDEX_DROP if $i_src->equals($i_tar, $self->case_insensitive, $self->ignore_index_names);
340 push @{$self->table_diff_hash->{$tar_table}{indexes_to_drop}}, $i_src;
345 sub diff_table_constraints {
346 my ($self, $src_table, $tar_table) = @_;
348 my(%checked_constraints);
350 for my $c_tar ( $tar_table->get_constraints ) {
351 for my $c_src ( $src_table->get_constraints ) {
353 # This is a bit of a hack - needed for renaming tables to work
354 local $c_src->{table} = $tar_table;
356 if ( $c_tar->equals($c_src, $self->case_insensitive, $self->ignore_constraint_names) ) {
357 $checked_constraints{$c_src} = 1;
358 next CONSTRAINT_CREATE;
361 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_create} }, $c_tar;
366 for my $c_src ( $src_table->get_constraints ) {
368 # This is a bit of a hack - needed for renaming tables to work
369 local $c_src->{table} = $tar_table;
371 next if !$self->ignore_constraint_names && $checked_constraints{$c_src};
372 for my $c_tar ( $tar_table->get_constraints ) {
373 next CONSTRAINT_DROP if $c_src->equals($c_tar, $self->case_insensitive, $self->ignore_constraint_names);
376 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_drop} }, $c_src;
381 sub diff_table_fields {
382 my ($self, $src_table, $tar_table) = @_;
384 # List of ones we've renamed from so we don't drop them
385 my %renamed_source_fields;
387 for my $tar_table_field ( $tar_table->get_fields ) {
388 my $f_tar_name = $tar_table_field->name;
390 if (my $old_name = $tar_table_field->extra->{renamed_from}) {
391 my $src_table_field = $src_table->get_field( $old_name, $self->case_insensitive );
392 unless ($src_table_field) {
393 carp qq#Renamed column can't find old column "@{[$src_table->name]}.$old_name" for renamed column\n#;
394 delete $tar_table_field->extra->{renamed_from};
396 push @{$self->table_diff_hash->{$tar_table}{fields_to_rename} }, [ $src_table_field, $tar_table_field ];
397 $renamed_source_fields{$old_name} = 1;
402 my $src_table_field = $src_table->get_field( $f_tar_name, $self->case_insensitive );
404 unless ( $src_table_field ) {
405 push @{$self->table_diff_hash->{$tar_table}{fields_to_create}}, $tar_table_field;
409 # field exists, something changed. This is a bit complex. Parsers can
410 # normalize types, but only some of them do, so compare the normalized and
411 # parsed types for each field to each other
412 if ( !$tar_table_field->equals($src_table_field, $self->case_insensitive) &&
413 !$tar_table_field->equals($src_table_field->parsed_field, $self->case_insensitive) &&
414 !$tar_table_field->parsed_field->equals($src_table_field, $self->case_insensitive) &&
415 !$tar_table_field->parsed_field->equals($src_table_field->parsed_field, $self->case_insensitive) ) {
417 # Some producers might need src field to diff against
418 push @{$self->table_diff_hash->{$tar_table}{fields_to_alter}}, [ $src_table_field, $tar_table_field ];
424 # Now check to see if any fields from src_table need to be dropped
425 for my $src_table_field ( $src_table->get_fields ) {
426 my $f_src_name = $src_table_field->name;
427 next if $renamed_source_fields{$f_src_name};
429 my $tar_table_field = $tar_table->get_field( $f_src_name, $self->case_insensitive );
431 unless ( $tar_table_field ) {
432 push @{$self->table_diff_hash->{$tar_table}{fields_to_drop}}, $src_table_field;
438 sub diff_table_options {
439 my ($self, $src_table, $tar_table) = @_;
442 my ($a_name, undef, $b_name, undef) = ( %$a, %$b );
445 # Need to sort the options so we don't get spurious diffs.
446 my (@src_opts, @tar_opts);
447 @src_opts = sort $cmp $src_table->options;
448 @tar_opts = sort $cmp $tar_table->options;
451 # If there's a difference, just re-set all the options
452 push @{ $self->table_diff_hash->{$tar_table}{table_options} }, $tar_table
453 unless $src_table->_compare_objects( \@src_opts, \@tar_opts );
456 # support producer_options as an alias for producer_args for legacy code.
457 sub producer_options {
460 return $self->producer_args( @_ );
469 SQL::Translator::Diff - determine differences between two schemas
473 Takes two input SQL::Translator::Schemas (or SQL files) and produces ALTER
474 statements to make them the same
480 use SQL::Translator::Diff;
481 my $sql = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL', $options_hash)
485 use SQL::Translator::Diff;
486 my $diff = SQL::Translator::Diff->new({
487 output_db => 'MySQL',
488 source_schema => $source_schema,
489 target_schema => $target_schema,
491 })->compute_differences->produce_diff_sql;
497 =item B<ignore_index_names>
499 Match indexes based on types and fields, ignoring name.
501 =item B<ignore_constraint_names>
503 Match constrains based on types, fields and tables, ignoring name.
507 Which producer to use to produce the output.
509 =item B<case_insensitive>
511 Ignore case of table, field, index and constraint names when comparing
513 =item B<no_batch_alters>
515 Produce each alter as a distinct C<ALTER TABLE> statement even if the producer
516 supports the ability to do all alters for a table as one statement.
518 =item B<ignore_missing_methods>
520 If the diff would need a method that is missing from the producer, just emit a
521 comment showing the method is missing, rather than dieing with an error
525 =head1 PRODUCER FUNCTIONS
527 The following producer functions should be implemented for completeness. If
528 any of them are needed for a given diff, but not found, an error will be
533 =item * C<alter_create_constraint($con)>
535 =item * C<alter_drop_constraint($con)>
537 =item * C<alter_create_index($idx)>
539 =item * C<alter_drop_index($idx)>
541 =item * C<add_field($fld)>
543 =item * C<alter_field($old_fld, $new_fld)>
545 =item * C<rename_field($old_fld, $new_fld)>
547 =item * C<drop_field($fld)>
549 =item * C<alter_table($table)>
551 =item * C<drop_table($table)>
553 =item * C<rename_table($old_table, $new_table)> (optional)
555 =item * C<batch_alter_table($table, $hash)> (optional)
557 If the producer supports C<batch_alter_table>, it will be called with the
558 table to alter and a hash, the keys of which will be the method names listed
559 above; values will be arrays of fields or constraints to operate on. In the
560 case of the field functions that take two arguments this will appear as a hash.
562 I.e. the hash might look something like the following:
565 alter_create_constraint => [ $constraint1, $constraint2 ],
566 add_field => [ $field ],
567 alter_field => [ [$old_field, $new_field] ]
571 =item * C<preprocess_schema($class, $schema)> (optional)
573 C<preprocess_schema> is called by the Diff code to allow the producer to
574 normalize any data it needs to first. For example, the MySQL producer uses
575 this method to ensure that FK constraint names are unique.
577 Basicaly any changes that need to be made to produce the SQL file for the
578 schema should be done here, so that a diff between a parsed SQL file and (say)
579 a parsed DBIx::Class::Schema object will be sane.
581 (As an aside, DBIx::Class, for instance, uses the presence of a
582 C<preprocess_schema> function on the producer to know that it can diff between
583 the previous SQL file and its own internal representation. Without this method
584 on th producer it will diff the two SQL files which is slower, but known to
585 work better on old-style producers.)
592 Original Author(s) unknown.
594 Refactor/re-write and more comprehensive tests by Ash Berlin C<< ash@cpan.org >>.
596 Redevelopment sponsored by Takkle Inc.