X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=bin%2Fsqlt-diff;h=61c29a9378532d7679acf4f18d73346af4448265;hb=782b5a43519d2713171767f74a544fe9892542ea;hp=d3548018cc583faa7cc0f154e8f91eb8570f33a9;hpb=afdf6a1cdd5a2820da4869aef101f2712ac4c6c1;p=dbsrgits%2FSQL-Translator.git diff --git a/bin/sqlt-diff b/bin/sqlt-diff index d354801..61c29a9 100755 --- a/bin/sqlt-diff +++ b/bin/sqlt-diff @@ -2,9 +2,9 @@ # vim: set ft=perl: # ------------------------------------------------------------------- -# $Id: sqlt-diff,v 1.6 2004-02-27 18:26:38 kycl4rk Exp $ +# $Id$ # ------------------------------------------------------------------- -# Copyright (C) 2002-4 The SQLFairy Authors +# Copyright (C) 2002-2009 The SQLFairy Authors # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License as @@ -37,11 +37,24 @@ For a list of all valid parsers: To diff two schemas: - sqlt-diff [options] file_name1=parser file_name2=parser + sqlt-diff [options] file_name1=parser1 file_name2=parser2 Options: -d|--debug Show debugging info + -t|--trace Turn on tracing for Parse::RecDescent + -c|--case-insensitive Compare tables/columns case-insensitively + --ignore-index-names Ignore index name differences + --ignore-constraint-names Ignore constraint name differences + --mysql_parser_version=<#####> Specify a target MySQL parser version + for dealing with /*! comments + --output-db= This Producer will be used instead of one + corresponding to parser1 to format output + for new tables + --ignore-view-sql Ignore view SQL differences + --ignore-proc-sql Ignore procedure SQL differences + --no-batch-alters Do not clump multile alters to the same table into a + single ALTER TABLE statement where possible. =head1 DESCRIPTION @@ -82,9 +95,10 @@ new index as such: =back -"ALTER/DROP TABLE" and "CREATE INDEX" statements B generated by -the Producer, unfortunately, and may require massaging before being passed to -your target database. +ALTER, CREATE, DROP statements are created by +SQL::Translator::Producer::*, see there for support/problems. + +Currently (v0.0900), only MySQL is supported by this code. =cut @@ -94,12 +108,15 @@ use strict; use Pod::Usage; use Data::Dumper; use SQL::Translator; +use SQL::Translator::Diff; use SQL::Translator::Schema::Constants; use vars qw( $VERSION ); -$VERSION = sprintf "%d.%02d", q$Revision: 1.6 $ =~ /(\d+)\.(\d+)/; +$VERSION = '1.99'; -my ( @input, $list, $help, $debug ); +my ( @input, $list, $help, $debug, $trace, $caseopt, $ignore_index_names, + $ignore_constraint_names, $output_db, $mysql_parser_version, + $ignore_view_sql, $ignore_proc_sql, $no_batch_alters ); for my $arg ( @ARGV ) { if ( $arg =~ m/^-?-l(ist)?$/ ) { $list = 1; @@ -110,15 +127,50 @@ for my $arg ( @ARGV ) { elsif ( $arg =~ m/^-?-d(ebug)?$/ ) { $debug = 1; } + elsif ( $arg =~ m/^-?-t(race)?$/ ) { + $trace = 1; + } + elsif ( $arg =~ m/^-?-c(ase-insensitive)?$/ ) { + $caseopt = 1; + } + elsif ( $arg =~ m/^--ignore-index-names$/ ) { + $ignore_index_names = 1; + } + elsif ( $arg =~ m/^--ignore-constraint-names$/ ) { + $ignore_constraint_names = 1; + } + elsif ( $arg =~ m/^--mysql-parser-version=(.+)$/ ) { + $mysql_parser_version = $1; + } + elsif ( $arg =~ m/^--output-db=(.+)$/ ) { + $output_db = $1; + } + elsif ( $arg =~ m/^--ignore-view-sql$/ ) { + $ignore_view_sql = 1; + } + elsif ( $arg =~ m/^--ignore-proc-sql$/ ) { + $ignore_proc_sql = 1; + } elsif ( $arg =~ m/^([^=]+)=(.+)$/ ) { push @input, { file => $1, parser => $2 }; } + elsif ( $arg =~ m/^--no-batch-alters$/ ) { + $no_batch_alters = 1; + } else { pod2usage( msg => "Unknown argument '$arg'" ); } } -pod2usage(1) if $help; +print STDERR <<'EOM'; +This code is experimental, currently the new code only supports MySQL or +SQLite diffing. To add support for other databases, please patch the relevant +SQL::Translator::Producer:: module. If you need compatibility with the old +sqlt-diff, please use sqlt-diff-old, and look into helping us make this one +work for you +EOM + +pod2usage(1) if $help || !@ARGV; pod2usage('Please specify only two schemas to diff') if scalar @input > 2; my $tr = SQL::Translator->new; @@ -133,181 +185,44 @@ if ( $list ) { pod2usage( msg => 'Too many file args' ) if @input > 2; -my ( $source_schema, $source_db, $target_schema, $target_db ); -my $i = 2; -for my $in ( @input ) { - my $file = $in->{'file'}; - my $parser = $in->{'parser'}; +my ( $source_schema, $source_db, $target_schema, $target_db ) = map { + my $file = $_->{'file'}; + my $parser = $_->{'parser'}; die "Unable to read file '$file'\n" unless -r $file; die "'$parser' is an invalid parser\n" unless $valid_parsers{ $parser }; - my $t = SQL::Translator->new; + my $t = SQL::Translator->new(parser_args => {mysql_parser_version => $mysql_parser_version}); $t->debug( $debug ); + $t->trace( $trace ); $t->parser( $parser ) or die $tr->error; - $t->producer( 'YAML' ) or die $tr->error; my $out = $t->translate( $file ) or die $tr->error; my $schema = $t->schema; unless ( $schema->name ) { $schema->name( $file ); } - if ( $i == 1 ) { - $source_schema = $schema; - $source_db = $parser; - } - else { - $target_schema = $schema; - $target_db = $parser; - } - $i--; -} - -my $s1_name = $source_schema->name; -my $s2_name = $target_schema->name; -my ( @new_tables, @diffs ); -for my $t1 ( $source_schema->get_tables ) { - my $t1_name = $t1->name; - my $t2 = $target_schema->get_table( $t1_name ); - - warn "TABLE '$s1_name.$t1_name'\n" if $debug; - unless ( $t2 ) { - warn "Couldn't find table '$s1_name.$t1_name' in '$s2_name'\n" - if $debug; - push @new_tables, $t1; - next; - } - - my $t2_name = $t2->name; - for my $t1_field ( $t1->get_fields ) { - my $f1_type = $t1_field->data_type; - my $f1_size = $t1_field->size; - my $f1_name = $t1_field->name; - my $t2_field = $t2->get_field( $f1_name ); - my $f1_full_name = "$s1_name.$t1_name.$t1_name"; - warn "FIELD '$f1_full_name'\n" if $debug; - - my $f2_full_name = "$s2_name.$t2_name.$f1_name"; - - unless ( $t2_field ) { - warn "Couldn't find field '$f2_full_name' in '$t2_name'\n" - if $debug; - push @diffs, sprintf( "ALTER TABLE %s ADD %s %s%s;", - $t1_name, $f1_name, $f1_type, - $f1_size ? "($f1_size)" : '' - ); - next; - } - - my $f2_type = $t2_field->data_type; - my $f2_size = $t2_field->size; - - if ( lc $f1_type ne lc $f2_type || - ( defined $f1_size && ( $f1_size ne $f2_size ) ) - ) { - push @diffs, sprintf( "ALTER TABLE %s CHANGE %s %s%s;", - $t1_name, $f1_name, $f1_type, - $f1_size ? "($f1_size)" : '' - ); - } - } - - my ( %t1_indices, %t2_indices ); - for my $rec ( [ $t1, \%t1_indices ], [ $t2, \%t2_indices ] ) { - my ( $table, $indices ) = @$rec; - for my $index ( $table->get_indices ) { - my $name = $index->name; - my $type = $index->type; - my $fields = join( ',', sort $index->fields ); - - $indices->{'type'}{ $type }{ $fields } = $name; - - if ( $name ) { - $indices->{'name'}{ $name } = { - type => $type, - fields => $fields, - }; - } - } - } - - for my $type ( keys %{ $t2_indices{'type'} } ) { - while ( my ($fields, $iname) = each %{$t2_indices{'type'}{ $type } } ) { - if ( $iname ) { - if ( my $i1 = $t1_indices{'name'}{ $iname } ) { - my $i1_type = $i1->{'type'}; - my $i1_fields = $i1->{'fields'}; - if ( $i1_type eq $type && $i1_fields eq $fields ) { - next; - } - } - } - elsif ( my $i1 = $t1_indices{'type'}{ $type }{ $fields } ) { - next; - } - - push @diffs, "DROP INDEX $iname on $t1_name;"; - } - } - - for my $type ( keys %{ $t1_indices{'type'} } ) { - while ( my ($fields, $iname) = each %{$t1_indices{'type'}{ $type } } ) { - if ( $iname ) { - if ( my $i2 = $t2_indices{'name'}{ $iname } ) { - my $i2_type = $i2->{'type'}; - my $i2_fields = $i2->{'fields'}; - if ( $i2_type eq $type && $i2_fields eq $fields ) { - next; - } - } - } - elsif ( my $i2 = $t2_indices{'type'}{ $type }{ $fields } ) { - next; - } - - push @diffs, sprintf( - "CREATE %sINDEX%s ON %s (%s);", - $type eq NORMAL ? '' : "$type ", - $iname ? " $iname" : '', - $t1_name, - $fields, - ); - } - } -} - -for my $t2 ( $target_schema->get_tables ) { - my $t2_name = $t2->name; - my $t1 = $source_schema->get_table( $t2_name ); - - unless ( $t1 ) { - push @diffs, "DROP TABLE $t2_name;"; - next; - } - - for my $t2_field ( $t2->get_fields ) { - my $f2_name = $t2_field->name; - my $t1_field = $t1->get_field( $f2_name ); - unless ( $t1_field ) { - push @diffs, "ALTER TABLE $t2_name DROP $f2_name;"; - } - } -} - -if ( @new_tables ) { - my $dummy_tr = SQL::Translator->new; - $dummy_tr->schema->add_table( $_ ) for @new_tables; - my $producer = $dummy_tr->producer( $target_db ); - unshift @diffs, $producer->( $dummy_tr ); -} - -if ( @diffs ) { - print join( "\n", - "-- Convert schema '$s2_name' to '$s1_name':\n", @diffs, '' - ); + ($schema, $parser); +} @input; + +my $result = SQL::Translator::Diff::schema_diff($source_schema, $source_db, + $target_schema, $target_db, + { caseopt => $caseopt, + ignore_index_names => $ignore_index_names, + ignore_constraint_names => $ignore_constraint_names, + ignore_view_sql => $ignore_view_sql, + ignore_proc_sql => $ignore_proc_sql, + output_db => $output_db, + no_batch_alters => $no_batch_alters, + debug => $debug, + trace => $trace }); +if($result) +{ + print $result; } -else { - print "There were no differences.\n"; +else +{ + print "No differences found."; } # -------------------------------------------------------------------