Added some debugging comments.
[dbsrgits/SQL-Translator.git] / bin / sqlt-diff
CommitLineData
e12ca55a 1#!/usr/bin/perl -w
2# vim: set ft=perl:
3
4# -------------------------------------------------------------------
8b3b2f0c 5# $Id: sqlt-diff,v 1.5 2004-02-11 21:31:45 kycl4rk Exp $
e12ca55a 6# -------------------------------------------------------------------
daf4f623 7# Copyright (C) 2002-4 The SQLFairy Authors
e12ca55a 8#
9# This program is free software; you can redistribute it and/or
10# modify it under the terms of the GNU General Public License as
11# published by the Free Software Foundation; version 2.
12#
13# This program is distributed in the hope that it will be useful, but
14# WITHOUT ANY WARRANTY; without even the implied warranty of
15# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16# General Public License for more details.
17#
18# You should have received a copy of the GNU General Public License
19# along with this program; if not, write to the Free Software
20# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21# 02111-1307 USA
22# -------------------------------------------------------------------
23
24=head1 NAME
25
26sqlt-diff - find the differences b/w two schemas
27
28=head1 SYNOPSIS
29
30For help:
31
32 sqlt-diff -h|--help
33
34For a list of all valid parsers:
35
36 sqlt -l|--list
37
38To diff two schemas:
39
21f5bf40 40 sqlt-diff [options] file_name1=parser file_name2=parser
e12ca55a 41
42Options:
43
44 -d|--debug Show debugging info
45
46=head1 DESCRIPTION
47
942485ea 48sqlt-diff is a utility for creating a file of SQL commands necessary to
49transform the first schema provided to the second. While not yet
50exhaustive in its ability to mutate the entire schema, it will report the
51following
52
53=over
54
55=item * New tables
56
57Using the Producer class of the target (second) schema, any tables missing
58in the first schema will be generated in their entirety (fields, constraints,
59indices).
60
61=item * Missing/altered fields
62
63Any fields missing or altered between the two schemas will be reported
64as:
65
66 ALTER TABLE <table_name>
67 [DROP <field_name>]
68 [CHANGE <field_name> <datatype> (<size>)] ;
69
70=item * Missing/altered indices
71
72Any indices missing or of a different type or on different fields will be
73indicated. Indices that should be dropped will be reported as such:
74
75 DROP INDEX <index_name> ON <table_name> ;
76
77An index of a different type or on different fields will be reported as a
78new index as such:
79
80 CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
81 ( <field_name>[,<field_name>] ) ;
82
83=back
84
85"ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
86the Producer, unfortunately, and may require massaging before being passed to
87your target database.
e12ca55a 88
89=cut
90
91# -------------------------------------------------------------------
92
93use strict;
94use Pod::Usage;
95use Data::Dumper;
96use SQL::Translator;
942485ea 97use SQL::Translator::Schema::Constants;
e12ca55a 98
99use vars qw( $VERSION );
8b3b2f0c 100$VERSION = sprintf "%d.%02d", q$Revision: 1.5 $ =~ /(\d+)\.(\d+)/;
e12ca55a 101
102my ( @input, $list, $help, $debug );
103for my $arg ( @ARGV ) {
104 if ( $arg =~ m/^-?-l(ist)?$/ ) {
105 $list = 1;
106 }
107 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
108 $help = 1;
109 }
110 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
111 $debug = 1;
112 }
113 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
114 push @input, { file => $1, parser => $2 };
115 }
116 else {
117 pod2usage( msg => "Unknown argument '$arg'" );
118 }
119}
120
121pod2usage(1) if $help;
942485ea 122pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
e12ca55a 123
124my $tr = SQL::Translator->new;
125my @parsers = $tr->list_parsers;
126my %valid_parsers = map { $_, 1 } @parsers;
127
128if ( $list ) {
129 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
130 print "\n";
131 exit(0);
132}
133
134pod2usage( msg => 'Too many file args' ) if @input > 2;
135
942485ea 136my ( $source_schema, $source_db, $target_schema, $target_db );
137my $i = 2;
e12ca55a 138for my $in ( @input ) {
139 my $file = $in->{'file'};
140 my $parser = $in->{'parser'};
141
142 die "Unable to read file '$file'\n" unless -r $file;
143 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
144
145 my $t = SQL::Translator->new;
146 $t->debug( $debug );
147 $t->parser( $parser ) or die $tr->error;
148 $t->producer( 'YAML' ) or die $tr->error;
149 my $out = $t->translate( $file ) or die $tr->error;
150 my $schema = $t->schema;
151 unless ( $schema->name ) {
152 $schema->name( $file );
153 }
154
155 if ( $i == 1 ) {
942485ea 156 $source_schema = $schema;
157 $source_db = $parser;
e12ca55a 158 }
159 else {
942485ea 160 $target_schema = $schema;
161 $target_db = $parser;
e12ca55a 162 }
942485ea 163 $i--;
e12ca55a 164}
165
942485ea 166my $s1_name = $source_schema->name;
167my $s2_name = $target_schema->name;
168my ( @new_tables, @diffs );
169for my $t1 ( $source_schema->get_tables ) {
170 my $t1_name = $t1->name;
171 my $t2 = $target_schema->get_table( $t1_name );
172
8b3b2f0c 173 warn "TABLE '$s1_name.$t1_name'\n" if $debug;
942485ea 174 unless ( $t2 ) {
8b3b2f0c 175 warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n"
176 if $debug;
942485ea 177 push @new_tables, $t1;
178 next;
179 }
e12ca55a 180
942485ea 181 my $t2_name = $t2->name;
182 for my $t1_field ( $t1->get_fields ) {
183 my $t1_type = $t1_field->data_type;
184 my $t1_size = $t1_field->size;
185 my $t1_name = $t1_field->name;
186 my $t2_field = $t2->get_field( $t1_name );
187 my $f1_full_name = "$s1_name.$t1_name.$t1_name";
8b3b2f0c 188 warn "FIELD '$f1_full_name'\n" if $debug;
e12ca55a 189
942485ea 190 my $f2_full_name = "$s2_name.$t2_name.$t1_name";
e12ca55a 191
942485ea 192 unless ( $t2_field ) {
8b3b2f0c 193 warn "Couldn't find field '$f2_full_name' in '$t2_name'\n"
194 if $debug;
942485ea 195 push @diffs,
196 "ALTER TABLE $t1_name ADD $t1_name $t1_type($t1_size);";
e12ca55a 197 next;
198 }
199
942485ea 200 my $t2_type = $t2_field->data_type;
201 my $t2_size = $t2_field->size;
e12ca55a 202
8b3b2f0c 203 if ( lc $t1_type ne lc $t2_type ||
942485ea 204 ( defined $t1_size && ( $t1_size ne $t2_size ) )
205 ) {
206 push @diffs,
207 "ALTER TABLE $t1_name CHANGE $t1_name $t1_type($t1_size);";
208 }
209 }
e12ca55a 210
942485ea 211 my ( %t1_indices, %t2_indices );
212 for my $rec ( [ $t1, \%t1_indices ], [ $t2, \%t2_indices ] ) {
213 my ( $table, $indices ) = @$rec;
214 for my $index ( $table->get_indices ) {
215 my $name = $index->name;
216 my $type = $index->type;
217 my $fields = join( ',', sort $index->fields );
218
219 $indices->{'type'}{ $type }{ $fields } = $name;
220
221 if ( $name ) {
222 $indices->{'name'}{ $name } = {
223 type => $type,
224 fields => $fields,
225 };
e12ca55a 226 }
942485ea 227 }
228 }
e12ca55a 229
942485ea 230 for my $type ( keys %{ $t2_indices{'type'} } ) {
231 while ( my ($fields, $iname) = each %{$t2_indices{'type'}{ $type } } ) {
232 if ( $iname ) {
233 if ( my $i1 = $t1_indices{'name'}{ $iname } ) {
234 my $i1_type = $i1->{'type'};
235 my $i1_fields = $i1->{'fields'};
236 if ( $i1_type eq $type && $i1_fields eq $fields ) {
237 next;
238 }
239 }
e12ca55a 240 }
942485ea 241 elsif ( my $i1 = $t1_indices{'type'}{ $type }{ $fields } ) {
242 next;
243 }
244
245 push @diffs, "DROP INDEX $iname on $t1_name;";
e12ca55a 246 }
247 }
942485ea 248
249 for my $type ( keys %{ $t1_indices{'type'} } ) {
250 while ( my ($fields, $iname) = each %{$t1_indices{'type'}{ $type } } ) {
251 if ( $iname ) {
252 if ( my $i2 = $t2_indices{'name'}{ $iname } ) {
253 my $i2_type = $i2->{'type'};
254 my $i2_fields = $i2->{'fields'};
255 if ( $i2_type eq $type && $i2_fields eq $fields ) {
256 next;
257 }
258 }
259 }
260 elsif ( my $i2 = $t2_indices{'type'}{ $type }{ $fields } ) {
261 next;
262 }
263
264 push @diffs, sprintf(
265 "CREATE %sINDEX%s ON %s (%s);",
266 $type eq NORMAL ? '' : "$type ",
267 $iname ? " $iname" : '',
268 $t1_name,
269 $fields,
270 );
271 }
272 }
273}
274
275for my $t2 ( $target_schema->get_tables ) {
276 my $t2_name = $t2->name;
277 my $t1 = $source_schema->get_table( $t2_name );
278
279 unless ( $t1 ) {
280 push @diffs, "DROP TABLE $t2_name;";
281 next;
282 }
283
284 for my $t2_field ( $t2->get_fields ) {
285 my $f2_name = $t2_field->name;
286 my $t1_field = $t1->get_field( $f2_name );
287 unless ( $t1_field ) {
288 push @diffs, "ALTER TABLE $t2_name DROP $f2_name;";
289 }
290 }
291}
292
293if ( @new_tables ) {
294 my $dummy_tr = SQL::Translator->new;
295 $dummy_tr->schema->add_table( $_ ) for @new_tables;
296 my $producer = $dummy_tr->producer( $target_db );
297 unshift @diffs, $producer->( $dummy_tr );
e12ca55a 298}
299
300if ( @diffs ) {
942485ea 301 print join( "\n",
8b3b2f0c 302 "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, ''
942485ea 303 );
e12ca55a 304}
305else {
306 print "There were no differences.\n";
307}
308
309# -------------------------------------------------------------------
310# Bring out number weight & measure in a year of dearth.
311# William Blake
312# -------------------------------------------------------------------
313
314=pod
315
316=head1 AUTHOR
317
318Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
319
320=head1 SEE ALSO
321
322SQL::Translator, L<http://sqlfairy.sourceforge.net>.
323
324=cut