Commit | Line | Data |
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 | |
26 | sqlt-diff - find the differences b/w two schemas |
27 | |
28 | =head1 SYNOPSIS |
29 | |
30 | For help: |
31 | |
32 | sqlt-diff -h|--help |
33 | |
34 | For a list of all valid parsers: |
35 | |
36 | sqlt -l|--list |
37 | |
38 | To diff two schemas: |
39 | |
21f5bf40 |
40 | sqlt-diff [options] file_name1=parser file_name2=parser |
e12ca55a |
41 | |
42 | Options: |
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 |
52 | sqlt-diff is a utility for creating a file of SQL commands necessary to |
53 | transform the first schema provided to the second. While not yet |
54 | exhaustive in its ability to mutate the entire schema, it will report the |
55 | following |
56 | |
57 | =over |
58 | |
59 | =item * New tables |
60 | |
61 | Using the Producer class of the target (second) schema, any tables missing |
62 | in the first schema will be generated in their entirety (fields, constraints, |
63 | indices). |
64 | |
65 | =item * Missing/altered fields |
66 | |
67 | Any fields missing or altered between the two schemas will be reported |
68 | as: |
69 | |
70 | ALTER TABLE <table_name> |
71 | [DROP <field_name>] |
72 | [CHANGE <field_name> <datatype> (<size>)] ; |
73 | |
74 | =item * Missing/altered indices |
75 | |
76 | Any indices missing or of a different type or on different fields will be |
77 | indicated. Indices that should be dropped will be reported as such: |
78 | |
79 | DROP INDEX <index_name> ON <table_name> ; |
80 | |
81 | An index of a different type or on different fields will be reported as a |
82 | new 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 |
90 | the Producer, unfortunately, and may require massaging before being passed to |
91 | your target database. |
e12ca55a |
92 | |
93 | =cut |
94 | |
95 | # ------------------------------------------------------------------- |
96 | |
97 | use strict; |
98 | use Pod::Usage; |
99 | use Data::Dumper; |
100 | use SQL::Translator; |
51ffe5ee |
101 | use SQL::Translator::Diff; |
942485ea |
102 | use SQL::Translator::Schema::Constants; |
e12ca55a |
103 | |
104 | use vars qw( $VERSION ); |
d990d84b |
105 | $VERSION = sprintf "%d.%02d", q$Revision: 1.14 $ =~ /(\d+)\.(\d+)/; |
e12ca55a |
106 | |
d990d84b |
107 | my ( @input, $list, $help, $debug, $trace, $caseopt , $ignore_index_names, $ignore_constraint_names ); |
e12ca55a |
108 | for 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 |
138 | pod2usage(1) if $help || !@ARGV; |
942485ea |
139 | pod2usage('Please specify only two schemas to diff') if scalar @input > 2; |
e12ca55a |
140 | |
141 | my $tr = SQL::Translator->new; |
142 | my @parsers = $tr->list_parsers; |
143 | my %valid_parsers = map { $_, 1 } @parsers; |
144 | |
145 | if ( $list ) { |
146 | print "\nParsers:\n", map { "\t$_\n" } sort @parsers; |
147 | print "\n"; |
148 | exit(0); |
149 | } |
150 | |
151 | pod2usage( msg => 'Too many file args' ) if @input > 2; |
152 | |
51ffe5ee |
153 | my ( $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 | |
173 | my $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 |
180 | if($result) |
181 | { |
182 | print $result; |
e12ca55a |
183 | } |
51ffe5ee |
184 | else |
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 | |
198 | Ken Y. Clark E<lt>kclark@cpan.orgE<gt>. |
199 | |
200 | =head1 SEE ALSO |
201 | |
202 | SQL::Translator, L<http://sqlfairy.sourceforge.net>. |
203 | |
204 | =cut |