1 package SQL::Translator::Diff;
3 ## SQLT schema diffing code
7 use SQL::Translator::Schema::Constants;
9 use base 'Class::Accessor::Fast';
11 use vars qw[ $VERSION ];
15 # Input/option accessors
16 __PACKAGE__->mk_accessors(qw/
17 ignore_index_names ignore_constraint_names ignore_view_sql
18 ignore_proc_sql output_db source_schema target_schema
19 case_insensitive no_batch_alters ignore_missing_methods producer_options
27 my @diff_hash_keys = qw/
40 __PACKAGE__->mk_accessors(@diff_arrays, 'table_diff_hash');
44 ## we are getting instructions on how to turn the source into the target
45 ## source == original, target == new (hmm, if I need to comment this, should I rename the vars again ??)
46 ## _schema isa SQL::Translator::Schema
47 ## _db is the name of the producer/db it came out of/into
48 ## results are formatted to the source preferences
50 my ($source_schema, $source_db, $target_schema, $output_db, $options) = @_;
53 my $obj = SQL::Translator::Diff->new( {
55 source_schema => $source_schema,
56 target_schema => $target_schema,
57 output_db => $output_db
60 $obj->compute_differences->produce_diff_sql;
64 my ($class, $values) = @_;
65 $values->{$_} ||= [] foreach @diff_arrays;
66 $values->{table_diff_hash} = {};
68 $values->{producer_options} ||= {};
69 $values->{output_db} ||= $values->{source_db};
70 return $class->SUPER::new($values);
73 sub compute_differences {
76 my $target_schema = $self->target_schema;
77 my $source_schema = $self->source_schema;
79 my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}";
80 eval "require $producer_class";
83 if (my $preprocess = $producer_class->can('preprocess_schema')) {
84 $preprocess->($source_schema);
85 $preprocess->($target_schema);
88 my %src_tables_checked = ();
89 my @tar_tables = sort { $a->name cmp $b->name } $target_schema->get_tables;
90 ## do original/source tables exist in target?
91 for my $tar_table ( @tar_tables ) {
92 my $tar_table_name = $tar_table->name;
96 $self->table_diff_hash->{$tar_table_name} = {
97 map {$_ => [] } @diff_hash_keys
100 if (my $old_name = $tar_table->extra('renamed_from')) {
101 $src_table = $source_schema->get_table( $old_name, $self->case_insensitive );
103 $self->table_diff_hash->{$tar_table_name}{table_renamed_from} = [ [$src_table, $tar_table] ];
105 delete $tar_table->extra->{renamed_from};
106 warn qq#Renamed table can't find old table "$old_name" for renamed table\n#;
109 $src_table = $source_schema->get_table( $tar_table_name, $self->case_insensitive );
112 unless ( $src_table ) {
114 ## add table(s) later.
115 push @{$self->tables_to_create}, $tar_table;
119 my $src_table_name = $src_table->name;
120 $src_table_name = lc $src_table_name if $self->case_insensitive;
121 $src_tables_checked{$src_table_name} = 1;
124 $self->diff_table_options($src_table, $tar_table);
126 ## Compare fields, their types, defaults, sizes etc etc
127 $self->diff_table_fields($src_table, $tar_table);
129 $self->diff_table_indexes($src_table, $tar_table);
130 $self->diff_table_constraints($src_table, $tar_table);
132 } # end of target_schema->get_tables loop
134 for my $src_table ( $source_schema->get_tables ) {
135 my $src_table_name = $src_table->name;
137 $src_table_name = lc $src_table_name if $self->case_insensitive;
139 push @{ $self->tables_to_drop}, $src_table
140 unless $src_tables_checked{$src_table_name};
146 sub produce_diff_sql {
149 my $target_schema = $self->target_schema;
150 my $source_schema = $self->source_schema;
151 my $tar_name = $target_schema->name;
152 my $src_name = $source_schema->name;
154 my $producer_class = "SQL::Translator::Producer::@{[$self->output_db]}";
155 eval "require $producer_class";
158 # Map of name we store under => producer method name
160 constraints_to_create => 'alter_create_constraint',
161 constraints_to_drop => 'alter_drop_constraint',
162 indexes_to_create => 'alter_create_index',
163 indexes_to_drop => 'alter_drop_index',
164 fields_to_create => 'add_field',
165 fields_to_alter => 'alter_field',
166 fields_to_rename => 'rename_field',
167 fields_to_drop => 'drop_field',
168 table_options => 'alter_table',
169 table_renamed_from => 'rename_table',
173 if (!$self->no_batch_alters &&
174 (my $batch_alter = $producer_class->can('batch_alter_table')) )
176 # Good - Producer supports batch altering of tables.
177 foreach my $table ( sort keys %{$self->table_diff_hash} ) {
178 my $tar_table = $target_schema->get_table($table)
179 || $source_schema->get_table($table);
181 push @diffs, $batch_alter->($tar_table,
183 $func_map{$_} => $self->table_diff_hash->{$table}{$_}
186 $self->producer_options
191 # If we have any table renames we need to do those first;
193 foreach my $table ( sort keys %{$self->table_diff_hash} ) {
194 my $table_diff = $self->table_diff_hash->{$table};
195 for (@diff_hash_keys) {
196 push( @{ $flattened_diffs{ $func_map{$_} } ||= [] }, @{ $table_diff->{$_} } );
201 if (@{ $flattened_diffs{$_} || [] }) {
202 my $meth = $producer_class->can($_);
205 my $sql = $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $self->producer_options );
206 $sql ? ("$sql") : ();
207 } @{ $flattened_diffs{$_} }
208 : $self->ignore_missing_methods
209 ? "-- $producer_class cant $_"
210 : die "$producer_class cant $_";
214 alter_drop_constraint
221 alter_create_constraint
225 if (my @tables = @{ $self->tables_to_create } ) {
226 my $translator = new SQL::Translator(
227 producer_type => $self->output_db,
230 # TODO: sort out options
231 %{ $self->producer_options }
233 $translator->producer_args->{no_transaction} = 1;
234 my $schema = $translator->schema;
236 $schema->add_table($_) for @tables;
239 # Remove begin/commit here, since we wrap everything in one.
240 grep { $_ !~ /^(?:COMMIT|START(?: TRANSACTION)?|BEGIN(?: TRANSACTION)?)/ } $producer_class->can('produce')->($translator);
243 if (my @tables_to_drop = @{ $self->{tables_to_drop} || []} ) {
244 my $meth = $producer_class->can('drop_table');
246 push @diffs, $meth ? ( map { $meth->($_, $self->producer_options) } @tables_to_drop)
247 : $self->ignore_missing_methods
248 ? "-- $producer_class cant drop_table"
249 : die "$producer_class cant drop_table";
253 unshift @diffs, "BEGIN";
254 push @diffs, "\nCOMMIT";
256 @diffs = ("-- No differences found");
260 if ( $self->output_db !~ /^(?:MySQL|SQLite|PostgreSQL)$/ ) {
261 unshift(@diffs, "-- Output database @{[$self->output_db]} is untested/unsupported!!!");
265 map { $_ ? ( $_ =~ /;$/xms ? $_ : "$_;\n\n" ) : "\n" }
266 ("-- Convert schema '$src_name' to '$tar_name':", @diffs);
268 return wantarray ? @return : join('', @return);
274 sub diff_table_indexes {
275 my ($self, $src_table, $tar_table) = @_;
277 my (%checked_indices);
279 for my $i_tar ( $tar_table->get_indices ) {
280 for my $i_src ( $src_table->get_indices ) {
281 if ( $i_tar->equals($i_src, $self->case_insensitive, $self->ignore_index_names) ) {
282 $checked_indices{$i_src} = 1;
286 push @{$self->table_diff_hash->{$tar_table}{indexes_to_create}}, $i_tar;
290 for my $i_src ( $src_table->get_indices ) {
291 next if !$self->ignore_index_names && $checked_indices{$i_src};
292 for my $i_tar ( $tar_table->get_indices ) {
293 next INDEX_DROP if $i_src->equals($i_tar, $self->case_insensitive, $self->ignore_index_names);
295 push @{$self->table_diff_hash->{$tar_table}{indexes_to_drop}}, $i_src;
300 sub diff_table_constraints {
301 my ($self, $src_table, $tar_table) = @_;
303 my(%checked_constraints);
305 for my $c_tar ( $tar_table->get_constraints ) {
306 for my $c_src ( $src_table->get_constraints ) {
308 # This is a bit of a hack - needed for renaming tables to work
309 local $c_src->{table} = $tar_table;
311 if ( $c_tar->equals($c_src, $self->case_insensitive, $self->ignore_constraint_names) ) {
312 $checked_constraints{$c_src} = 1;
313 next CONSTRAINT_CREATE;
316 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_create} }, $c_tar;
321 for my $c_src ( $src_table->get_constraints ) {
323 # This is a bit of a hack - needed for renaming tables to work
324 local $c_src->{table} = $tar_table;
326 next if !$self->ignore_constraint_names && $checked_constraints{$c_src};
327 for my $c_tar ( $tar_table->get_constraints ) {
328 next CONSTRAINT_DROP if $c_src->equals($c_tar, $self->case_insensitive, $self->ignore_constraint_names);
331 push @{ $self->table_diff_hash->{$tar_table}{constraints_to_drop} }, $c_src;
336 sub diff_table_fields {
337 my ($self, $src_table, $tar_table) = @_;
339 # List of ones ew've renamed from so we dont drop them
340 my %renamed_source_fields;
342 for my $tar_table_field ( $tar_table->get_fields ) {
343 my $f_tar_name = $tar_table_field->name;
345 if (my $old_name = $tar_table_field->extra->{renamed_from}) {
346 my $src_table_field = $src_table->get_field( $old_name, $self->case_insensitive );
347 unless ($src_table_field) {
348 warn qq#Renamed column can't find old column "@{[$src_table->name]}.$old_name" for renamed column\n#;
349 delete $tar_table_field->extra->{renamed_from};
351 push @{$self->table_diff_hash->{$tar_table}{fields_to_rename} }, [ $src_table_field, $tar_table_field ];
352 $renamed_source_fields{$old_name} = 1;
357 my $src_table_field = $src_table->get_field( $f_tar_name, $self->case_insensitive );
359 unless ( $src_table_field ) {
360 push @{$self->table_diff_hash->{$tar_table}{fields_to_create}}, $tar_table_field;
364 # field exists, something changed. This is a bit complex. Parsers can
365 # normalize types, but only some of them do, so compare the normalized and
366 # parsed types for each field to each other
367 if ( !$tar_table_field->equals($src_table_field, $self->case_insensitive) &&
368 !$tar_table_field->equals($src_table_field->parsed_field, $self->case_insensitive) &&
369 !$tar_table_field->parsed_field->equals($src_table_field, $self->case_insensitive) &&
370 !$tar_table_field->parsed_field->equals($src_table_field->parsed_field, $self->case_insensitive) ) {
372 # Some producers might need src field to diff against
373 push @{$self->table_diff_hash->{$tar_table}{fields_to_alter}}, [ $src_table_field, $tar_table_field ];
379 # Now check to see if any fields from src_table need to be dropped
380 for my $src_table_field ( $src_table->get_fields ) {
381 my $f_src_name = $src_table_field->name;
382 next if $renamed_source_fields{$f_src_name};
384 my $tar_table_field = $tar_table->get_field( $f_src_name, $self->case_insensitive );
386 unless ( $tar_table_field ) {
387 push @{$self->table_diff_hash->{$tar_table}{fields_to_drop}}, $src_table_field;
393 sub diff_table_options {
394 my ($self, $src_table, $tar_table) = @_;
397 my ($a_name, undef, $b_name, undef) = ( %$a, %$b );
400 # Need to sort the options so we dont get supruious diffs.
401 my (@src_opts, @tar_opts);
402 @src_opts = sort $cmp $src_table->options;
403 @tar_opts = sort $cmp $tar_table->options;
406 # If there's a difference, just re-set all the options
407 push @{ $self->table_diff_hash->{$tar_table}{table_options} }, $tar_table
408 unless $src_table->_compare_objects( \@src_opts, \@tar_opts );
417 SQL::Translator::Diff
421 Takes two input SQL::Translator::Schemas (or SQL files) and produces ALTER
422 statments to make them the same
428 use SQL::Translator::Diff;
429 my $sql = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL', $options_hash)
433 use SQL::Translator::Diff;
434 my $diff = SQL::Translator::Diff->new({
435 output_db => 'MySQL',
436 source_schema => $source_schema,
437 target_schema => $target_schema,
439 })->compute_differences->produce_diff_sql;
445 =item B<ignore_index_names>
447 Match indexes based on types and fields, ignoring name.
449 =item B<ignore_constraint_names>
451 Match constrains based on types, fields and tables, ignoring name.
455 Which producer to use to produce the output.
457 =item B<case_insensitive>
459 Ignore case of table, field, index and constraint names when comparing
461 =item B<no_batch_alters>
463 Produce each alter as a distinct C<ALTER TABLE> statement even if the producer
464 supports the ability to do all alters for a table as one statement.
466 =item B<ignore_missing_methods>
468 If the diff would need a method that is missing from the producer, just emit a
469 comment showing the method is missing, rather than dieing with an error
473 =head1 PRODUCER FUNCTIONS
475 The following producer functions should be implemented for completeness. If
476 any of them are needed for a given diff, but not found, an error will be
481 =item * C<alter_create_constraint($con)>
483 =item * C<alter_drop_constraint($con)>
485 =item * C<alter_create_index($idx)>
487 =item * C<alter_drop_index($idx)>
489 =item * C<add_field($fld)>
491 =item * C<alter_field($old_fld, $new_fld)>
493 =item * C<rename_field($old_fld, $new_fld)>
495 =item * C<drop_field($fld)>
497 =item * C<alter_table($table)>
499 =item * C<drop_table($table)>
501 =item * C<rename_table($old_table, $new_table)> (optional)
503 =item * C<batch_alter_table($table, $hash)> (optional)
505 If the producer supports C<batch_alter_table>, it will be called with the
506 table to alter and a hash, the keys of which will be the method names listed
507 above; values will be arrays of fields or constraints to operate on. In the
508 case of the field functions that take two arguments this will appear as a hash.
510 I.e. the hash might look something like the following:
513 alter_create_constraint => [ $constraint1, $constraint2 ],
514 add_field => [ $field ],
515 alter_field => [ [$old_field, $new_field] ]
519 =item * C<preprocess_schema($class, $schema)> (optional)
521 C<preprocess_schema> is called by the Diff code to allow the producer to
522 normalize any data it needs to first. For example, the MySQL producer uses
523 this method to ensure that FK contraint names are unique.
525 Basicaly any changes that need to be made to produce the SQL file for the
526 schema should be done here, so that a diff between a parsed SQL file and (say)
527 a parsed DBIx::Class::Schema object will be sane.
529 (As an aside, DBIx::Class, for instance, uses the presence of a
530 C<preprocess_schema> function on the producer to know that it can diff between
531 the previous SQL file and its own internal representation. Without this method
532 on th producer it will diff the two SQL files which is slower, but known to
533 work better on old-style producers.)
540 Original Author(s) unknown.
542 Refactor/re-write and more comprehensive tests by Ash Berlin C<< ash@cpan.org >>.
544 Redevelopment sponsored by Takkle Inc.