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