#!/usr/bin/env perl
# vim: set ft=perl:

# -------------------------------------------------------------------
# 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
# published by the Free Software Foundation; version 2.
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MAb
# 02110-1301 USA.
# -------------------------------------------------------------------

=head1 NAME

sqlt-diff - find the differences b/w two schemas


For help:

  sqlt-diff -h|--help

For a list of all valid parsers:

  sqlt -l|--list

To diff two schemas:

  sqlt-diff [options] file_name1=parser1 file_name2=parser2


  -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=<Producer>  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.
  --quote=<character>  Use <character> to quote all table and field
                       names in statements


sqlt-diff is a utility for creating a file of SQL commands necessary to
transform the first schema provided to the second.  While not yet
exhaustive in its ability to mutate the entire schema, it will report the


=item * New tables

Using the Producer class of the target (second) schema, any tables missing
in the first schema will be generated in their entirety (fields, constraints,

=item * Missing/altered fields

Any fields missing or altered between the two schemas will be reported

  ALTER TABLE <table_name>
    [DROP <field_name>]
    [CHANGE <field_name> <datatype> (<size>)] ;

=item * Missing/altered indices

Any indices missing or of a different type or on different fields will be
indicated.  Indices that should be dropped will be reported as such:

  DROP INDEX <index_name> ON <table_name> ;

An index of a different type or on different fields will be reported as a
new index as such:

  CREATE [<index_type>] INDEX [<index_name>] ON <table_name>
    ( <field_name>[,<field_name>] ) ;


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.


# -------------------------------------------------------------------

use strict;
use warnings;
use Pod::Usage;
use Data::Dumper;
use Getopt::Long;
use SQL::Translator;
use SQL::Translator::Diff;
use SQL::Translator::Schema::Constants;

use vars qw( $VERSION );
$VERSION = '1.60';

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, $quote

    'l|list' => \$list,
    'h|help' => \$help,
    'd|debug' => \$debug,
    't|trace' => \$trace,
    'c|case-insensitive' => \$caseopt,
    'ignore-index-names' => \$ignore_index_names,
    'ignore-constraint-names' => \$ignore_constraint_names,
    'mysql_parser_version:s' => \$mysql_parser_version,
    'output-db:s' => \$output_db,
    'ignore-view-sql' => \$ignore_view_sql,
    'ignore-proc-sql' => \$ignore_proc_sql,
    'quote:s' => \$quote,
    'no-batch-alters' => \$no_batch_alters,
) or pod2usage(2);

for my $arg ( @ARGV ) {
    if ( $arg =~ m/^([^=]+)=(.+)$/ ) {
        push @input, { file => $1, parser => $2 };

my $tr            = SQL::Translator->new;
my @parsers       = $tr->list_parsers;
my %valid_parsers = map { $_, 1 } @parsers;

if ( $list ) {
    print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
    print "\n";

pod2usage(1) if $help || !@input;
pod2usage(msg => 'Please specify two schemas to diff') if scalar @input != 2;

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(parser_args => {
        mysql_parser_version => $mysql_parser_version
    $t->debug( $debug );
    $t->trace( $trace );
    $t->parser( $parser )            or die $tr->error;
    my $out = $t->translate( $file ) or die $tr->error;
    my $schema = $t->schema;
    unless ( $schema->name ) {
        $schema->name( $file );

    ($schema, $parser);
} @input;

my $result = SQL::Translator::Diff::schema_diff(
    $source_schema, $source_db,
    $target_schema, $target_db,
        caseopt                 => $caseopt || 0,
        ignore_index_names      => $ignore_index_names || 0,
        ignore_constraint_names => $ignore_constraint_names || 0,
        ignore_view_sql         => $ignore_view_sql || 0,
        ignore_proc_sql         => $ignore_proc_sql || 0,
        output_db               => $output_db,
        no_batch_alters         => $no_batch_alters || 0,
        debug                   => $debug || 0,
        trace                   => $trace || 0,
        producer_args => {
            quote_table_names   => $quote || '',
            quote_field_names   => $quote || '',

    print $result;
    print "No differences found.";

# -------------------------------------------------------------------
# Bring out number weight & measure in a year of dearth.
# William Blake
# -------------------------------------------------------------------


=head1 AUTHOR

Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.

=head1 SEE ALSO

SQL::Translator, L<http://sqlfairy.sourceforge.net>.