1 package SQL::Translator::Diff;
2 ## SQLT schema diffing code
6 use SQL::Translator::Schema::Constants;
11 ## we are getting instructions on how to turn the source into the target
12 ## source == original, target == new (hmm, if I need to comment this, should I rename the vars again ??)
13 ## _schema isa SQL::Translator::Schema
14 ## _db is the name of the producer/db it came out of/into
15 ## results are formatted to the source preferences
17 my ($source_schema, $source_db, $target_schema, $target_db, $options) = @_;
18 # print Data::Dumper::Dumper($target_schema);
20 my $producer_class = "SQL::Translator::Producer::$source_db";
21 eval "require $producer_class";
23 my $case_insensitive = $options->{caseopt} || 0;
24 my $debug = $options->{debug} || 0;
25 my $trace = $options->{trace} || 0;
26 my $ignore_index_names = $options->{ignore_index_names} || 0;
27 my $ignore_constraint_names = $options->{ignore_constraint_names} || 0;
28 my $ignore_view_sql = $options->{ignore_view_sql} || 0;
29 my $ignore_proc_sql = $options->{ignore_proc_sql} || 0;
30 my $output_db = $options->{output_db} || $source_db;
32 my $tar_name = $target_schema->name;
33 my $src_name = $source_schema->name;
35 my ( @diffs_new_tables, @diffs_at_end, @new_tables, @diffs_index_drops, @diffs_constraint_drops, @diffs_table_drops, @diffs_table_adds, @diffs_index_creates, @diffs_constraint_creates, @diffs_table_options );
36 ## do original/source tables exist in target?
37 for my $tar_table ( $target_schema->get_tables ) {
38 my $tar_table_name = $tar_table->name;
39 my $src_table = $source_schema->get_table( $tar_table_name, $case_insensitive );
41 warn "TABLE '$tar_name.$tar_table_name'\n" if $debug;
42 unless ( $src_table ) {
43 warn "Couldn't find table '$tar_name.$tar_table_name' in '$src_name'\n"
46 ## add table(s) later.
47 my $cr_table = $producer_class->can('create_table') || die "$producer_class does not support create_table";
48 my $new_table_sql = $cr_table->($tar_table, { leave_name => 1 });
49 push (@diffs_new_tables, $new_table_sql);
50 push (@new_tables, $tar_table);
54 # Go through our options
55 my $options_different = 0;
58 for my $tar_table_option_ref ( $tar_table->options ) {
59 my($key_tar, $value_tar) = %{$tar_table_option_ref};
60 for my $src_table_option_ref ( $src_table->options ) {
61 my($key_src, $value_src) = %{$src_table_option_ref};
62 if ( $key_tar eq $key_src ) {
63 if ( defined $value_tar != defined $value_src ) {
64 $options_different = 1;
67 if ( defined $value_tar && $value_tar ne $value_src ) {
68 $options_different = 1;
71 $checkedOptions{$key_tar} = 1;
75 $options_different = 1;
78 # Go through the other table's options
79 unless ( $options_different ) {
80 for my $src_table_option_ref ( $src_table->options ) {
81 my($key, $value) = %{$src_table_option_ref};
82 next if $checkedOptions{$key};
83 $options_different = 1;
87 # If there's a difference, just re-set all the options
88 if ( $options_different ) {
89 my $al_table = $producer_class->can('alter_table') || die "$producer_class does not support alter_table";
90 my $alter_sql = $al_table->( $tar_table ) . ';';
91 @diffs_table_options = ("$alter_sql");
94 my $src_table_name = $src_table->name;
95 ## Compare fields, their types, defaults, sizes etc etc
96 for my $tar_table_field ( $tar_table->get_fields ) {
97 my $f_tar_type = $tar_table_field->data_type;
98 my $f_tar_size = $tar_table_field->size;
99 my $f_tar_name = $tar_table_field->name;
100 my $f_tar_nullable = $tar_table_field->is_nullable;
101 my $f_tar_default = $tar_table_field->default_value;
102 my $f_tar_auto_inc = $tar_table_field->is_auto_increment;
103 my $src_table_field = $src_table->get_field( $f_tar_name, $case_insensitive );
104 my $f_tar_full_name = "$tar_name.$tar_table_name.$f_tar_name";
105 warn "FIELD '$f_tar_full_name'\n" if $debug;
107 my $f_src_full_name = "$src_name.$src_table_name.$f_tar_name";
109 unless ( $src_table_field ) {
110 warn "Couldn't find field '$f_src_full_name' in '$src_table_name'\n"
113 my $add_field = $producer_class->can('add_field') || die "$producer_class does not support add_field";
114 my $alter_add_sql = $add_field->( $tar_table_field ) . ';';
115 push (@diffs_table_adds, $alter_add_sql);
119 ## field exists, so what changed?
120 ## (do we care? just call equals to see IF)
121 if ( !$tar_table_field->equals($src_table_field, $case_insensitive) ) {
122 ## throw all this junk away and call producer->alter_field
123 ## check output same, etc etc
125 my $al_field = $producer_class->can('alter_field') || die "$producer_class does not support alter_field";
126 my $alter_field_sql = $al_field->( $src_table_field, $tar_table_field ) . ';';
127 push (@diffs_table_adds, $alter_field_sql);
132 for my $src_table_field ( $src_table->get_fields ) {
133 my $f_src_name = $src_table_field->name;
134 my $tar_table_field = $tar_table->get_field( $f_src_name, $case_insensitive );
135 my $f_src_full_name = "$tar_name.$tar_table_name.$f_src_name";
137 unless ( $tar_table_field ) {
138 warn "Couldn't find field '$f_src_full_name' in '$src_table_name'\n"
141 my $dr_field = $producer_class->can('drop_field') || die "$producer_class does not support drop_field";
142 my $alter_drop_sql = $dr_field->( $src_table_field ) . ';';
143 push (@diffs_table_drops, $alter_drop_sql);
148 my (%checked_indices);
150 for my $i_tar ( $tar_table->get_indices ) {
151 for my $i_src ( $src_table->get_indices ) {
152 if ( $i_tar->equals($i_src, $case_insensitive, $ignore_index_names) ) {
153 $checked_indices{$i_src} = 1;
157 my $al_cr_index = $producer_class->can('alter_create_index') || die "$producer_class does not support alter_create_index";
158 my $create_index_sql = $al_cr_index->( $i_tar ) . ';';
159 push ( @diffs_index_creates, $create_index_sql );
162 for my $i_src ( $src_table->get_indices ) {
163 next if !$ignore_index_names && $checked_indices{$i_src};
164 for my $i_tar ( $tar_table->get_indices ) {
165 next INDEX_DROP if $i_src->equals($i_tar, $case_insensitive, $ignore_index_names);
167 my $al_dr_index = $producer_class->can('alter_drop_index') || die "$producer_class does not support alter_drop_index";
168 my $drop_index_sql = $al_dr_index->( $i_src ) . ';';
169 push ( @diffs_index_drops, $drop_index_sql );
172 my(%checked_constraints);
174 for my $c_tar ( $tar_table->get_constraints ) {
175 for my $c_src ( $src_table->get_constraints ) {
176 if ( $c_tar->equals($c_src, $case_insensitive, $ignore_constraint_names) ) {
177 $checked_constraints{$c_src} = 1;
178 next CONSTRAINT_CREATE;
181 my $al_cr_const = $producer_class->can('alter_create_constraint') || die "$producer_class does not support alter_create_constraint";
182 my $create_constraint_sql = $al_cr_const->( $c_tar, { leave_name => 1 }) . ';';
183 push ( @diffs_constraint_creates, $create_constraint_sql );
187 for my $c_src ( $src_table->get_constraints ) {
188 next if !$ignore_constraint_names && $checked_constraints{$c_src};
189 for my $c_tar ( $tar_table->get_constraints ) {
190 next CONSTRAINT_DROP if $c_src->equals($c_tar, $case_insensitive, $ignore_constraint_names);
193 my $al_dr_const = $producer_class->can('alter_drop_constraint') || die "$producer_class does not support alter_drop_constraint";
194 my $drop_constraint_sql = $al_dr_const->( $c_src ) . ';';
195 push ( @diffs_constraint_drops, $drop_constraint_sql );
199 my @diffs_dropped_tables;
200 for my $src_table ( $source_schema->get_tables ) {
201 my $src_table_name = $src_table->name;
202 my $tar_table = $target_schema->get_table( $src_table_name, $case_insensitive );
204 unless ( $tar_table ) {
205 for my $c_src ( $src_table->get_constraints ) {
206 my $al_dr_const = $producer_class->can('alter_drop_constraint') || die "$producer_class does not support alter_drop_constraint";
207 my $drop_constraint_sql = $al_dr_const->( $c_src ) . ';';
208 push ( @diffs_constraint_drops, $drop_constraint_sql );
211 push @diffs_dropped_tables, "DROP TABLE $src_table_name;";
217 push ( @diffs, @diffs_constraint_drops, @diffs_index_drops, @diffs_table_drops, @diffs_table_adds, @diffs_index_creates, @diffs_constraint_creates, @diffs_table_options );
218 unshift (@diffs, "SET foreign_key_checks=0;\n\n", @diffs_new_tables, "SET foreign_key_checks=1;\n\n" );
219 push (@diffs, @diffs_dropped_tables);
221 if(@diffs_constraint_drops+@diffs_index_drops+@diffs_table_drops+@diffs_table_adds+@diffs_index_creates+@diffs_constraint_creates+@diffs_table_options+@diffs_new_tables+@diffs_dropped_tables == 0 )
223 @diffs = ('No differences found');
227 # if ( $target_db !~ /^(MySQL|SQLServer|Oracle)$/ ) {
228 if ( $target_db !~ /^(MySQL)$/ ) {
229 unshift(@diffs, "-- Target database $target_db is untested/unsupported!!!");
232 "-- Convert schema '$src_name' to '$tar_name':\n", @diffs, "\n"