Added options to sqlt-diff to ignore index and/or constraint name differences
[dbsrgits/SQL-Translator.git] / bin / sqlt-diff
CommitLineData
e12ca55a 1#!/usr/bin/perl -w
2# vim: set ft=perl:
3
4# -------------------------------------------------------------------
d990d84b 5# $Id: sqlt-diff,v 1.14 2007-03-01 22:16:00 duality72 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
51ffe5ee 45 -t|--trace Turn on tracing for Parse::RecDescent
46 -c|--case-insensitive Compare tables/columns case-insenstiviely
d990d84b 47 --ignore-index-names Ignore index name differences
48 --ignore-constraint-names Ignore constraint name differences
e12ca55a 49
50=head1 DESCRIPTION
51
942485ea 52sqlt-diff is a utility for creating a file of SQL commands necessary to
53transform the first schema provided to the second. While not yet
54exhaustive in its ability to mutate the entire schema, it will report the
55following
56
57=over
58
59=item * New tables
60
61Using the Producer class of the target (second) schema, any tables missing
62in the first schema will be generated in their entirety (fields, constraints,
63indices).
64
65=item * Missing/altered fields
66
67Any fields missing or altered between the two schemas will be reported
68as:
69
70 ALTER TABLE <table_name>
71 [DROP <field_name>]
72 [CHANGE <field_name> <datatype> (<size>)] ;
73
74=item * Missing/altered indices
75
76Any indices missing or of a different type or on different fields will be
77indicated. Indices that should be dropped will be reported as such:
78
79 DROP INDEX <index_name> ON <table_name> ;
80
81An index of a different type or on different fields will be reported as a
82new index as such:
83
84 CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
85 ( <field_name>[,<field_name>] ) ;
86
87=back
88
89"ALTER/DROP TABLE" and "CREATE INDEX" statements B<are not> generated by
90the Producer, unfortunately, and may require massaging before being passed to
91your target database.
e12ca55a 92
93=cut
94
95# -------------------------------------------------------------------
96
97use strict;
98use Pod::Usage;
99use Data::Dumper;
100use SQL::Translator;
51ffe5ee 101use SQL::Translator::Diff;
942485ea 102use SQL::Translator::Schema::Constants;
e12ca55a 103
104use vars qw( $VERSION );
d990d84b 105$VERSION = sprintf "%d.%02d", q$Revision: 1.14 $ =~ /(\d+)\.(\d+)/;
e12ca55a 106
d990d84b 107my ( @input, $list, $help, $debug, $trace, $caseopt , $ignore_index_names, $ignore_constraint_names );
e12ca55a 108for my $arg ( @ARGV ) {
109 if ( $arg =~ m/^-?-l(ist)?$/ ) {
110 $list = 1;
111 }
112 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
113 $help = 1;
114 }
115 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
116 $debug = 1;
117 }
51ffe5ee 118 elsif ( $arg =~ m/^-?-t(race)?$/ ) {
119 $trace = 1;
120 }
d990d84b 121 elsif ( $arg =~ m/^-?-c(ase-insensitive)?$/ ) {
51ffe5ee 122 $caseopt = 1;
123 }
d990d84b 124 elsif ( $arg =~ m/^--ignore-index-names$/ ) {
125 $ignore_index_names = 1;
126 }
127 elsif ( $arg =~ m/^--ignore-constraint-names?$/ ) {
128 $ignore_constraint_names = 1;
129 }
e12ca55a 130 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
131 push @input, { file => $1, parser => $2 };
132 }
133 else {
134 pod2usage( msg => "Unknown argument '$arg'" );
135 }
136}
137
51ffe5ee 138pod2usage(1) if $help || !@ARGV;
942485ea 139pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
e12ca55a 140
141my $tr = SQL::Translator->new;
142my @parsers = $tr->list_parsers;
143my %valid_parsers = map { $_, 1 } @parsers;
144
145if ( $list ) {
146 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
147 print "\n";
148 exit(0);
149}
150
151pod2usage( msg => 'Too many file args' ) if @input > 2;
152
51ffe5ee 153my ( $source_schema, $source_db, $target_schema, $target_db ) = map {
154 my $file = $_->{'file'};
155 my $parser = $_->{'parser'};
e12ca55a 156
157 die "Unable to read file '$file'\n" unless -r $file;
158 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
159
160 my $t = SQL::Translator->new;
161 $t->debug( $debug );
51ffe5ee 162 $t->trace( $trace );
e12ca55a 163 $t->parser( $parser ) or die $tr->error;
e12ca55a 164 my $out = $t->translate( $file ) or die $tr->error;
165 my $schema = $t->schema;
166 unless ( $schema->name ) {
167 $schema->name( $file );
168 }
169
51ffe5ee 170 ($schema, $parser);
171} @input;
172
173my $result = SQL::Translator::Diff::schema_diff($source_schema, $source_db,
174 $target_schema, $target_db,
d990d84b 175 { caseopt => $caseopt,
176 ignore_index_names => $ignore_index_names,
177 ignore_constraint_names => $ignore_constraint_names,
178 debug => $debug,
179 trace => $trace });
51ffe5ee 180if($result)
181{
182 print $result;
e12ca55a 183}
51ffe5ee 184else
185{
186 print "No differences found.";
e12ca55a 187}
188
189# -------------------------------------------------------------------
190# Bring out number weight & measure in a year of dearth.
191# William Blake
192# -------------------------------------------------------------------
193
194=pod
195
196=head1 AUTHOR
197
198Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
199
200=head1 SEE ALSO
201
202SQL::Translator, L<http://sqlfairy.sourceforge.net>.
203
204=cut