6 sqlt-diff - find the differences b/w two schemas
14 For a list of all valid parsers:
20 sqlt-diff [options] file_name1=parser1 file_name2=parser2
24 -d|--debug Show debugging info
25 -t|--trace Turn on tracing for Parse::RecDescent
26 -c|--case-insensitive Compare tables/columns case-insensitively
27 --ignore-index-names Ignore index name differences
28 --ignore-constraint-names Ignore constraint name differences
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
34 --ignore-view-sql Ignore view SQL differences
35 --ignore-proc-sql Ignore procedure SQL differences
36 --no-batch-alters Do not clump multile alters to the same table into a
37 single ALTER TABLE statement where possible.
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
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,
54 =item * Missing/altered fields
56 Any fields missing or altered between the two schemas will be reported
59 ALTER TABLE <table_name>
61 [CHANGE <field_name> <datatype> (<size>)] ;
63 =item * Missing/altered indices
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:
68 DROP INDEX <index_name> ON <table_name> ;
70 An index of a different type or on different fields will be reported as a
73 CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
74 ( <field_name>[,<field_name>] ) ;
78 ALTER, CREATE, DROP statements are created by
79 SQL::Translator::Producer::*, see there for support/problems.
81 Currently (v0.0900), only MySQL is supported by this code.
85 # -------------------------------------------------------------------
92 use SQL::Translator::Diff;
93 use SQL::Translator::Schema::Constants;
95 use vars qw( $VERSION );
98 my ( @input, $list, $help, $debug, $trace, $caseopt, $ignore_index_names,
99 $ignore_constraint_names, $output_db, $mysql_parser_version,
100 $ignore_view_sql, $ignore_proc_sql, $no_batch_alters );
101 for my $arg ( @ARGV ) {
102 if ( $arg =~ m/^-?-l(ist)?$/ ) {
105 elsif ( $arg =~ m/^-?-h(elp)?$/ ) {
108 elsif ( $arg =~ m/^-?-d(ebug)?$/ ) {
111 elsif ( $arg =~ m/^-?-t(race)?$/ ) {
114 elsif ( $arg =~ m/^-?-c(ase-insensitive)?$/ ) {
117 elsif ( $arg =~ m/^--ignore-index-names$/ ) {
118 $ignore_index_names = 1;
120 elsif ( $arg =~ m/^--ignore-constraint-names$/ ) {
121 $ignore_constraint_names = 1;
123 elsif ( $arg =~ m/^--mysql-parser-version=(.+)$/ ) {
124 $mysql_parser_version = $1;
126 elsif ( $arg =~ m/^--output-db=(.+)$/ ) {
129 elsif ( $arg =~ m/^--ignore-view-sql$/ ) {
130 $ignore_view_sql = 1;
132 elsif ( $arg =~ m/^--ignore-proc-sql$/ ) {
133 $ignore_proc_sql = 1;
135 elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) {
136 push @input, { file => $1, parser => $2 };
138 elsif ( $arg =~ m/^--no-batch-alters$/ ) {
139 $no_batch_alters = 1;
142 pod2usage( msg => "Unknown argument '$arg'" );
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
154 pod2usage(1) if $help || !@ARGV;
155 pod2usage('Please specify only two schemas to diff') if scalar @input > 2;
157 my $tr = SQL::Translator->new;
158 my @parsers = $tr->list_parsers;
159 my %valid_parsers = map { $_, 1 } @parsers;
162 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
167 pod2usage( msg => 'Too many file args' ) if @input > 2;
169 my ( $source_schema, $source_db, $target_schema, $target_db ) = map {
170 my $file = $_->{'file'};
171 my $parser = $_->{'parser'};
173 die "Unable to read file '$file'\n" unless -r $file;
174 die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser };
176 my $t = SQL::Translator->new(parser_args => {mysql_parser_version => $mysql_parser_version});
179 $t->parser( $parser ) or die $tr->error;
180 my $out = $t->translate( $file ) or die $tr->error;
181 my $schema = $t->schema;
182 unless ( $schema->name ) {
183 $schema->name( $file );
189 my $result = SQL::Translator::Diff::schema_diff($source_schema, $source_db,
190 $target_schema, $target_db,
191 { caseopt => $caseopt,
192 ignore_index_names => $ignore_index_names,
193 ignore_constraint_names => $ignore_constraint_names,
194 ignore_view_sql => $ignore_view_sql,
195 ignore_proc_sql => $ignore_proc_sql,
196 output_db => $output_db,
197 no_batch_alters => $no_batch_alters,
206 print "No differences found.";
209 # -------------------------------------------------------------------
210 # Bring out number weight & measure in a year of dearth.
212 # -------------------------------------------------------------------
218 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
222 SQL::Translator, L<http://sqlfairy.sourceforge.net>.