1 package SQL::Translator::Diff;
4 ## SQLT schema diffing code
9 use Carp::Clan qw/^SQL::Translator/;
10 use SQL::Translator::Schema::Constants;
12 use base 'Class::Accessor::Fast';
14 # Input/option accessors
15 __PACKAGE__->mk_accessors(qw/
16 ignore_index_names ignore_constraint_names ignore_view_sql
17 ignore_proc_sql output_db source_schema target_schema
18 case_insensitive no_batch_alters ignore_missing_methods producer_args
26 my @diff_hash_keys = qw/
39 __PACKAGE__->mk_accessors(@diff_arrays, 'table_diff_hash');
43 ## we are getting instructions on how to turn the source into the target
44 ## source == original, target == new (hmm, if I need to comment this, should I rename the vars again ??)
45 ## _schema isa SQL::Translator::Schema
46 ## _db is the name of the producer/db it came out of/into
47 ## results are formatted to the source preferences
49 my ($source_schema, $source_db, $target_schema, $output_db, $options) = @_;
52 my $obj = SQL::Translator::Diff->new( {
54 source_schema => $source_schema,
55 target_schema => $target_schema,
56 output_db => $output_db
59 $obj->compute_differences->produce_diff_sql;
63 my ($class, $values) = @_;
64 $values->{$_} ||= [] foreach @diff_arrays;
65 $values->{table_diff_hash} = {};
67 $values->{producer_args} ||= {};
68 if ($values->{producer_options}) {
69 carp 'producer_options is deprecated. Please use producer_args';
70 $values->{producer_args} = { %{$values->{producer_options}}, %{$values->{producer_args}} };
72 $values->{output_db} ||= $values->{source_db};
73 return $class->SUPER::new($values);
76 sub compute_differences {
79 my $target_schema = $self->target_schema;
80 my $source_schema = $self->source_schema;
82 my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}";
83 eval "require $producer_class";
86 if (my $preprocess = $producer_class->can('preprocess_schema')) {
87 $preprocess->($source_schema);
88 $preprocess->($target_schema);
91 my %src_tables_checked = ();
92 my @tar_tables = sort { $a->name cmp $b->name } $target_schema->get_tables;
93 ## do original/source tables exist in target?
94 for my $tar_table ( @tar_tables ) {
95 my $tar_table_name = $tar_table->name;
99 $self->table_diff_hash->{$tar_table_name} = {
100 map {$_ => [] } @diff_hash_keys
103 if (my $old_name = $tar_table->extra('renamed_from')) {
104 $src_table = $source_schema->get_table( $old_name, $self->case_insensitive );
106 $self->table_diff_hash->{$tar_table_name}{table_renamed_from} = [ [$src_table, $tar_table] ];
108 delete $tar_table->extra->{renamed_from};
109 carp qq#Renamed table can't find old table "$old_name" for renamed table\n#;
112 $src_table = $source_schema->get_table( $tar_table_name, $self->case_insensitive );
115 unless ( $src_table ) {
117 ## add table(s) later.
118 push @{$self->tables_to_create}, $tar_table;
122 my $src_table_name = $src_table->name;
123 $src_table_name = lc $src_table_name if $self->case_insensitive;
124 $src_tables_checked{$src_table_name} = 1;
127 $self->diff_table_options($src_table, $tar_table);
129 ## Compare fields, their types, defaults, sizes etc etc
130 $self->diff_table_fields($src_table, $tar_table);
132 $self->diff_table_indexes($src_table, $tar_table);
133 $self->diff_table_constraints($src_table, $tar_table);
135 } # end of target_schema->get_tables loop
137 for my $src_table ( $source_schema->get_tables ) {
138 my $src_table_name = $src_table->name;
140 $src_table_name = lc $src_table_name if $self->case_insensitive;
142 push @{ $self->tables_to_drop}, $src_table
143 unless $src_tables_checked{$src_table_name};
149 sub produce_diff_sql {
152 my $target_schema = $self->target_schema;
153 my $source_schema = $self->source_schema;
154 my $tar_name = $target_schema->name;
155 my $src_name = $source_schema->name;
157 my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}";
158 eval "require $producer_class";
161 # Map of name we store under => producer method name
163 constraints_to_create => 'alter_create_constraint',
164 constraints_to_drop => 'alter_drop_constraint',
165 indexes_to_create => 'alter_create_index',
166 indexes_to_drop => 'alter_drop_index',
167 fields_to_create => 'add_field',
168 fields_to_alter => 'alter_field',
169 fields_to_rename => 'rename_field',
170 fields_to_drop => 'drop_field',
171 table_options => 'alter_table',
172 table_renamed_from => 'rename_table',
176 if (!$self->no_batch_alters &&
177 (my $batch_alter = $producer_class->can('batch_alter_table')) )
179 # Good - Producer supports batch altering of tables.
180 foreach my $table ( sort keys %{$self->table_diff_hash} ) {
181 my $tar_table = $target_schema->get_table($table)
182 || $source_schema->get_table($table);
184 push @diffs, $batch_alter->($tar_table,
186 $func_map{$_} => $self->table_diff_hash->{$table}{$_}
194 # If we have any table renames we need to do those first;
196 foreach my $table ( sort keys %{$self->table_diff_hash} ) {
197 my $table_diff = $self->table_diff_hash->{$table};
198 for (@diff_hash_keys) {
199 push( @{ $flattened_diffs{ $func_map{$_} } ||= [] }, @{ $table_diff->{$_} } );
204 if (@{ $flattened_diffs{$_} || [] }) {
205 my $meth = $producer_class->can($_);
208 my $sql = $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $self->producer_args );
209 $sql ? ("$sql") : ();
210 } @{ $flattened_diffs{$_} }
211 : $self->ignore_missing_methods
212 ? "-- $producer_class cant $_"
213 : die "$producer_class cant $_";
217 alter_drop_constraint
224 alter_create_constraint
228 if (my @tables = @{ $self->tables_to_create } ) {
229 my $translator = new SQL::Translator(
230 producer_type => $self->output_db,
233 # TODO: sort out options
234 %{ $self->producer_args }
236 $translator->producer_args->{no_transaction} = 1;
237 my $schema = $translator->schema;
239 $schema->add_table($_) for @tables;
242 # Remove begin/commit here, since we wrap everything in one.
243 grep { $_ !~ /^(?:COMMIT|START(?: TRANSACTION)?|BEGIN(?: TRANSACTION)?)/ } $producer_class->can('produce')->($translator);
246 if (my @tables_to_drop = @{ $self->{tables_to_drop} || []} ) {
247 my $meth = $producer_class->can('drop_table');
249 push @diffs, $meth ? ( map { $meth->($_, $self->producer_args) } @tables_to_drop)
250 : $self->ignore_missing_methods
251 ? "-- $producer_class cant drop_table"
252 : die "$producer_class cant drop_table";
256 unshift @diffs, "BEGIN";
257 push @diffs, "\nCOMMIT";
259 @diffs = ("-- No differences found");
263 if ( $self->output_db !~ /^(?:MySQL|SQLite|PostgreSQL)$/ ) {
264 unshift(@diffs, "-- Output database @{[$self->output_db]} is untested/unsupported!!!");
268 map { $_ ? ( $_ =~ /;$/xms ? $_ : "$_;\n\n" ) : "\n" }
269 ("-- Convert schema '$src_name' to '$tar_name':", @diffs);
271 return wantarray ? @return : join('', @return);
277 sub diff_table_indexes {
278 my ($self, $src_table, $tar_table) = @_;
280 my (%checked_indices);
282 for my $i_tar ( $tar_table->get_indices ) {
283 for my $i_src ( $src_table->get_indices ) {
284 if ( $i_tar->equals($i_src, $self->case_insensitive, $self->ignore_index_names) ) {
285 $checked_indices{$i_src} = 1;
289 push @{$self->table_diff_hash->{$tar_table}{indexes_to_create}}, $i_tar;
293 for my $i_src ( $src_table->get_indices ) {
294 next if !$self->ignore_index_names && $checked_indices{$i_src};
295 for my $i_tar ( $tar_table->get_indices ) {
296 next INDEX_DROP if $i_src->equals($i_tar, $self->case_insensitive, $self->ignore_index_names);
298 push @{$self->table_diff_hash->{$tar_table}{indexes_to_drop}}, $i_src;
303 sub diff_table_constraints {
304 my ($self, $src_table, $tar_table) = @_;
306 my(%checked_constraints);
308 for my $c_tar ( $tar_table->get_constraints ) {
309 for my $c_src ( $src_table->get_constraints ) {
311 # This is a bit of a hack - needed for renaming tables to work
312 local $c_src->{table} = $tar_table;
314 if ( $c_tar->equals($c_src, $self->case_insensitive, $self->ignore_constraint_names) ) {
315 $checked_constraints{$c_src} = 1;
316 next CONSTRAINT_CREATE;
319 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_create} }, $c_tar;
324 for my $c_src ( $src_table->get_constraints ) {
326 # This is a bit of a hack - needed for renaming tables to work
327 local $c_src->{table} = $tar_table;
329 next if !$self->ignore_constraint_names && $checked_constraints{$c_src};
330 for my $c_tar ( $tar_table->get_constraints ) {
331 next CONSTRAINT_DROP if $c_src->equals($c_tar, $self->case_insensitive, $self->ignore_constraint_names);
334 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_drop} }, $c_src;
339 sub diff_table_fields {
340 my ($self, $src_table, $tar_table) = @_;
342 # List of ones ew've renamed from so we dont drop them
343 my %renamed_source_fields;
345 for my $tar_table_field ( $tar_table->get_fields ) {
346 my $f_tar_name = $tar_table_field->name;
348 if (my $old_name = $tar_table_field->extra->{renamed_from}) {
349 my $src_table_field = $src_table->get_field( $old_name, $self->case_insensitive );
350 unless ($src_table_field) {
351 carp qq#Renamed column can't find old column "@{[$src_table->name]}.$old_name" for renamed column\n#;
352 delete $tar_table_field->extra->{renamed_from};
354 push @{$self->table_diff_hash->{$tar_table}{fields_to_rename} }, [ $src_table_field, $tar_table_field ];
355 $renamed_source_fields{$old_name} = 1;
360 my $src_table_field = $src_table->get_field( $f_tar_name, $self->case_insensitive );
362 unless ( $src_table_field ) {
363 push @{$self->table_diff_hash->{$tar_table}{fields_to_create}}, $tar_table_field;
367 # field exists, something changed. This is a bit complex. Parsers can
368 # normalize types, but only some of them do, so compare the normalized and
369 # parsed types for each field to each other
370 if ( !$tar_table_field->equals($src_table_field, $self->case_insensitive) &&
371 !$tar_table_field->equals($src_table_field->parsed_field, $self->case_insensitive) &&
372 !$tar_table_field->parsed_field->equals($src_table_field, $self->case_insensitive) &&
373 !$tar_table_field->parsed_field->equals($src_table_field->parsed_field, $self->case_insensitive) ) {
375 # Some producers might need src field to diff against
376 push @{$self->table_diff_hash->{$tar_table}{fields_to_alter}}, [ $src_table_field, $tar_table_field ];
382 # Now check to see if any fields from src_table need to be dropped
383 for my $src_table_field ( $src_table->get_fields ) {
384 my $f_src_name = $src_table_field->name;
385 next if $renamed_source_fields{$f_src_name};
387 my $tar_table_field = $tar_table->get_field( $f_src_name, $self->case_insensitive );
389 unless ( $tar_table_field ) {
390 push @{$self->table_diff_hash->{$tar_table}{fields_to_drop}}, $src_table_field;
396 sub diff_table_options {
397 my ($self, $src_table, $tar_table) = @_;
400 my ($a_name, undef, $b_name, undef) = ( %$a, %$b );
403 # Need to sort the options so we dont get supruious diffs.
404 my (@src_opts, @tar_opts);
405 @src_opts = sort $cmp $src_table->options;
406 @tar_opts = sort $cmp $tar_table->options;
409 # If there's a difference, just re-set all the options
410 push @{ $self->table_diff_hash->{$tar_table}{table_options} }, $tar_table
411 unless $src_table->_compare_objects( \@src_opts, \@tar_opts );
414 # support producer_options as an alias for producer_args for legacy code.
415 sub producer_options {
418 return $self->producer_args( @_ );
427 SQL::Translator::Diff - determine differences between two schemas
431 Takes two input SQL::Translator::Schemas (or SQL files) and produces ALTER
432 statments to make them the same
438 use SQL::Translator::Diff;
439 my $sql = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL', $options_hash)
443 use SQL::Translator::Diff;
444 my $diff = SQL::Translator::Diff->new({
445 output_db => 'MySQL',
446 source_schema => $source_schema,
447 target_schema => $target_schema,
449 })->compute_differences->produce_diff_sql;
455 =item B<ignore_index_names>
457 Match indexes based on types and fields, ignoring name.
459 =item B<ignore_constraint_names>
461 Match constrains based on types, fields and tables, ignoring name.
465 Which producer to use to produce the output.
467 =item B<case_insensitive>
469 Ignore case of table, field, index and constraint names when comparing
471 =item B<no_batch_alters>
473 Produce each alter as a distinct C<ALTER TABLE> statement even if the producer
474 supports the ability to do all alters for a table as one statement.
476 =item B<ignore_missing_methods>
478 If the diff would need a method that is missing from the producer, just emit a
479 comment showing the method is missing, rather than dieing with an error
483 =head1 PRODUCER FUNCTIONS
485 The following producer functions should be implemented for completeness. If
486 any of them are needed for a given diff, but not found, an error will be
491 =item * C<alter_create_constraint($con)>
493 =item * C<alter_drop_constraint($con)>
495 =item * C<alter_create_index($idx)>
497 =item * C<alter_drop_index($idx)>
499 =item * C<add_field($fld)>
501 =item * C<alter_field($old_fld, $new_fld)>
503 =item * C<rename_field($old_fld, $new_fld)>
505 =item * C<drop_field($fld)>
507 =item * C<alter_table($table)>
509 =item * C<drop_table($table)>
511 =item * C<rename_table($old_table, $new_table)> (optional)
513 =item * C<batch_alter_table($table, $hash)> (optional)
515 If the producer supports C<batch_alter_table>, it will be called with the
516 table to alter and a hash, the keys of which will be the method names listed
517 above; values will be arrays of fields or constraints to operate on. In the
518 case of the field functions that take two arguments this will appear as a hash.
520 I.e. the hash might look something like the following:
523 alter_create_constraint => [ $constraint1, $constraint2 ],
524 add_field => [ $field ],
525 alter_field => [ [$old_field, $new_field] ]
529 =item * C<preprocess_schema($class, $schema)> (optional)
531 C<preprocess_schema> is called by the Diff code to allow the producer to
532 normalize any data it needs to first. For example, the MySQL producer uses
533 this method to ensure that FK contraint names are unique.
535 Basicaly any changes that need to be made to produce the SQL file for the
536 schema should be done here, so that a diff between a parsed SQL file and (say)
537 a parsed DBIx::Class::Schema object will be sane.
539 (As an aside, DBIx::Class, for instance, uses the presence of a
540 C<preprocess_schema> function on the producer to know that it can diff between
541 the previous SQL file and its own internal representation. Without this method
542 on th producer it will diff the two SQL files which is slower, but known to
543 work better on old-style producers.)
550 Original Author(s) unknown.
552 Refactor/re-write and more comprehensive tests by Ash Berlin C<< ash@cpan.org >>.
554 Redevelopment sponsored by Takkle Inc.