Commit | Line | Data |
969049ba |
1 | #!/usr/bin/env perl |
e12ca55a |
2 | # vim: set ft=perl: |
3 | |
44659089 |
4 | # ------------------------------------------------------------------- |
5 | # Copyright (C) 2002-2009 The SQLFairy Authors |
6 | # |
7 | # This program is free software; you can redistribute it and/or |
8 | # modify it under the terms of the GNU General Public License as |
9 | # published by the Free Software Foundation; version 2. |
10 | # |
11 | # This program is distributed in the hope that it will be useful, but |
12 | # WITHOUT ANY WARRANTY; without even the implied warranty of |
13 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
14 | # General Public License for more details. |
15 | # |
16 | # You should have received a copy of the GNU General Public License |
17 | # along with this program; if not, write to the Free Software |
18 | # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA |
19 | # 02111-1307 USA |
20 | # ------------------------------------------------------------------- |
21 | |
e12ca55a |
22 | =head1 NAME |
23 | |
24 | sqlt-diff - find the differences b/w two schemas |
25 | |
26 | =head1 SYNOPSIS |
27 | |
28 | For help: |
29 | |
30 | sqlt-diff -h|--help |
31 | |
32 | For a list of all valid parsers: |
33 | |
34 | sqlt -l|--list |
35 | |
36 | To diff two schemas: |
37 | |
fb5ab406 |
38 | sqlt-diff [options] file_name1=parser1 file_name2=parser2 |
e12ca55a |
39 | |
40 | Options: |
41 | |
42 | -d|--debug Show debugging info |
51ffe5ee |
43 | -t|--trace Turn on tracing for Parse::RecDescent |
0b1b149e |
44 | -c|--case-insensitive Compare tables/columns case-insensitively |
d990d84b |
45 | --ignore-index-names Ignore index name differences |
46 | --ignore-constraint-names Ignore constraint name differences |
0b1b149e |
47 | --mysql_parser_version=<#####> Specify a target MySQL parser version |
48 | for dealing with /*! comments |
49 | --output-db=<Producer> This Producer will be used instead of one |
50 | corresponding to parser1 to format output |
51 | for new tables |
d1a895ce |
52 | --ignore-view-sql Ignore view SQL differences |
53 | --ignore-proc-sql Ignore procedure SQL differences |
54b6e490 |
54 | --no-batch-alters Do not clump multile alters to the same table into a |
55 | single ALTER TABLE statement where possible. |
d4c5bb75 |
56 | --quote=<character> Use <character> to quote all table and field |
57 | names in statements |
e12ca55a |
58 | |
59 | =head1 DESCRIPTION |
60 | |
942485ea |
61 | sqlt-diff is a utility for creating a file of SQL commands necessary to |
aee4b66e |
62 | transform the first schema provided to the second. While not yet |
63 | exhaustive in its ability to mutate the entire schema, it will report the |
942485ea |
64 | following |
65 | |
66 | =over |
67 | |
68 | =item * New tables |
69 | |
70 | Using the Producer class of the target (second) schema, any tables missing |
71 | in the first schema will be generated in their entirety (fields, constraints, |
72 | indices). |
73 | |
74 | =item * Missing/altered fields |
75 | |
aee4b66e |
76 | Any fields missing or altered between the two schemas will be reported |
942485ea |
77 | as: |
78 | |
aee4b66e |
79 | ALTER TABLE <table_name> |
80 | [DROP <field_name>] |
942485ea |
81 | [CHANGE <field_name> <datatype> (<size>)] ; |
82 | |
83 | =item * Missing/altered indices |
84 | |
85 | Any indices missing or of a different type or on different fields will be |
86 | indicated. Indices that should be dropped will be reported as such: |
aee4b66e |
87 | |
942485ea |
88 | DROP INDEX <index_name> ON <table_name> ; |
89 | |
aee4b66e |
90 | An index of a different type or on different fields will be reported as a |
942485ea |
91 | new index as such: |
92 | |
aee4b66e |
93 | CREATE [<index_type>] INDEX [<index_name>] ON <table_name> |
942485ea |
94 | ( <field_name>[,<field_name>] ) ; |
95 | |
96 | =back |
97 | |
da5a1bae |
98 | ALTER, CREATE, DROP statements are created by |
99 | SQL::Translator::Producer::*, see there for support/problems. |
100 | |
101 | Currently (v0.0900), only MySQL is supported by this code. |
e12ca55a |
102 | |
103 | =cut |
104 | |
105 | # ------------------------------------------------------------------- |
106 | |
107 | use strict; |
969049ba |
108 | use warnings; |
e12ca55a |
109 | use Pod::Usage; |
110 | use Data::Dumper; |
a3899970 |
111 | use Getopt::Long; |
e12ca55a |
112 | use SQL::Translator; |
51ffe5ee |
113 | use SQL::Translator::Diff; |
942485ea |
114 | use SQL::Translator::Schema::Constants; |
e12ca55a |
115 | |
da06ac74 |
116 | use vars qw( $VERSION ); |
11ad2df9 |
117 | $VERSION = '1.59'; |
da06ac74 |
118 | |
aee4b66e |
119 | my ( @input, $list, $help, $debug, $trace, $caseopt, $ignore_index_names, |
120 | $ignore_constraint_names, $output_db, $mysql_parser_version, |
d4c5bb75 |
121 | $ignore_view_sql, $ignore_proc_sql, $no_batch_alters, $quote |
122 | ); |
a3899970 |
123 | |
124 | GetOptions( |
125 | 'l|list' => \$list, |
126 | 'h|help' => \$help, |
127 | 'd|debug' => \$debug, |
128 | 't|trace' => \$trace, |
129 | 'c|case-insensitive' => \$caseopt, |
130 | 'ignore-index-names' => \$ignore_index_names, |
131 | 'ignore-constraint-names' => \$ignore_constraint_names, |
132 | 'mysql_parser_version:s' => \$mysql_parser_version, |
133 | 'output-db:s' => \$output_db, |
134 | 'ignore-view-sql' => \$ignore_view_sql, |
135 | 'ignore-proc-sql' => \$ignore_proc_sql, |
136 | 'quote:s' => \$quote, |
137 | 'no-batch-alters' => \$no_batch_alters, |
138 | ) or pod2usage(2); |
139 | |
e12ca55a |
140 | for my $arg ( @ARGV ) { |
a3899970 |
141 | if ( $arg =~ m/^([^=]+)=(.+)$/ ) { |
e12ca55a |
142 | push @input, { file => $1, parser => $2 }; |
143 | } |
e12ca55a |
144 | } |
145 | |
d4c5bb75 |
146 | print STDERR <<'EOM' unless $ENV{SQLT_NEWDIFF_NOWARN}; |
aee4b66e |
147 | This code is experimental, currently the new code only supports MySQL or |
54b6e490 |
148 | SQLite diffing. To add support for other databases, please patch the relevant |
149 | SQL::Translator::Producer:: module. If you need compatibility with the old |
150 | sqlt-diff, please use sqlt-diff-old, and look into helping us make this one |
151 | work for you |
152 | EOM |
da5a1bae |
153 | |
e12ca55a |
154 | my $tr = SQL::Translator->new; |
155 | my @parsers = $tr->list_parsers; |
156 | my %valid_parsers = map { $_, 1 } @parsers; |
157 | |
a3899970 |
158 | |
e12ca55a |
159 | if ( $list ) { |
160 | print "\nParsers:\n", map { "\t$_\n" } sort @parsers; |
161 | print "\n"; |
162 | exit(0); |
163 | } |
164 | |
a3899970 |
165 | pod2usage(1) if $help || !@input; |
166 | pod2usage(msg => 'Please specify two schemas to diff') if scalar @input != 2; |
e12ca55a |
167 | |
51ffe5ee |
168 | my ( $source_schema, $source_db, $target_schema, $target_db ) = map { |
169 | my $file = $_->{'file'}; |
170 | my $parser = $_->{'parser'}; |
e12ca55a |
171 | |
172 | die "Unable to read file '$file'\n" unless -r $file; |
173 | die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser }; |
174 | |
a3899970 |
175 | my $t = SQL::Translator->new(parser_args => { |
176 | mysql_parser_version => $mysql_parser_version |
177 | }); |
e12ca55a |
178 | $t->debug( $debug ); |
51ffe5ee |
179 | $t->trace( $trace ); |
e12ca55a |
180 | $t->parser( $parser ) or die $tr->error; |
e12ca55a |
181 | my $out = $t->translate( $file ) or die $tr->error; |
182 | my $schema = $t->schema; |
183 | unless ( $schema->name ) { |
184 | $schema->name( $file ); |
185 | } |
186 | |
51ffe5ee |
187 | ($schema, $parser); |
188 | } @input; |
189 | |
a3899970 |
190 | my $result = SQL::Translator::Diff::schema_diff( |
191 | $source_schema, $source_db, |
192 | $target_schema, $target_db, |
193 | { |
194 | caseopt => $caseopt || 0, |
195 | ignore_index_names => $ignore_index_names || 0, |
196 | ignore_constraint_names => $ignore_constraint_names || 0, |
197 | ignore_view_sql => $ignore_view_sql || 0, |
198 | ignore_proc_sql => $ignore_proc_sql || 0, |
199 | output_db => $output_db, |
200 | no_batch_alters => $no_batch_alters || 0, |
201 | debug => $debug || 0, |
202 | trace => $trace || 0, |
203 | producer_args => { |
204 | quote_table_names => $quote || '', |
205 | quote_field_names => $quote || '', |
206 | }, |
207 | } |
208 | ); |
209 | |
51ffe5ee |
210 | if($result) |
211 | { |
212 | print $result; |
e12ca55a |
213 | } |
51ffe5ee |
214 | else |
215 | { |
216 | print "No differences found."; |
e12ca55a |
217 | } |
218 | |
219 | # ------------------------------------------------------------------- |
220 | # Bring out number weight & measure in a year of dearth. |
221 | # William Blake |
222 | # ------------------------------------------------------------------- |
223 | |
224 | =pod |
225 | |
226 | =head1 AUTHOR |
227 | |
969049ba |
228 | Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>. |
e12ca55a |
229 | |
230 | =head1 SEE ALSO |
231 | |
232 | SQL::Translator, L<http://sqlfairy.sourceforge.net>. |
233 | |
234 | =cut |