#!/usr/bin/perl

use DBI;
use IO::File;


use strict;


sub help {

print <<EOF;

$0

Show structural differences between two databases. Requieres vimdiff.

Usage:

$0 [--user=s ] [ --pass=s ] --db1=s --db2=s [ --driver=s ] [ --help ] 

EOF

}
use Getopt::Long;

my $db1;
my $db2;
my $user = getlogin();
my $pass;
my $host= "localhost";
my $driver= "Pg";
my $help;

my $result = GetOptions (
	       "user=s"   => \$user,      # string
	       "pass=s"   => \$pass,      # string
	       "db1=s"   => \$db1,
	       "help"   => \$help,
	       "db2=s"   => \$db2,
	       "driver=s"   => \$driver,
);

if ($help) {
	help();
	exit -2;
}

unless ($db1 && $db2) {
	print "Debe entregar las bases de datos\n";
	help();
	exit -1;
}

my $f1 = "/tmp/dbdiff.file1.$$";
my $f2 = "/tmp/dbdiff.file2.$$";

my $file1 = new IO::File(">$f1");
my $file2 = new IO::File(">$f2");

&process_database($db1, $file1);
&process_database($db2, $file2);

exec "vimdiff", $f1, $f2;

exit 0;


sub process_database {

my $dbname = shift;
my $output = shift;

select $output;

my $dbh = DBI->connect( "dbi:$driver:dbname=$dbname;host=$host", $user, $pass );
die DBI::errstr unless $dbh;

### Create a new statement handle to fetch table information
my $tabsth = $dbh->table_info();

### Iterate through all the tables...
while ( my ( $qual, $owner, $name, $type ) = $tabsth->fetchrow_array() ) {


	next unless $type =~ /^TABLE/;
    ### The table to fetch data for
    my $table = $name;
    
    ### Build the full table name with quoting if required
    $table = qq{"$owner"."$table"} if defined $owner;
    
    ### The SQL statement to fetch the table metadata
    my $statement = "SELECT * FROM $table limit 0";
    
    print "\n";
    print "Table Information\n";
    print "=================\n\n";
    print "Statement:     $statement\n";
    
    ### Prepare and execute the SQL statement
    my $sth = $dbh->prepare( $statement );
    $sth->execute();
    
    my $fields = $sth->{NUM_OF_FIELDS};
    print "NUM_OF_FIELDS: $fields\n\n";
    
    print "Column Name                     Type  Precision  Scale  Nullable?\n";
    print "------------------------------  ----  ---------  -----  ---------\n\n";
    
    ### Iterate through all the fields and dump the field information

	my @fields;
    for ( my $i = 0 ; $i < $fields ; $i++ ) {
    
        my $name = $sth->{NAME}->[$i];
    
        ### Describe the NULLABLE value
        my $nullable = ("No", "Yes", "Unknown")[ $sth->{NULLABLE}->[$i] ];
        ### Tidy the other values, which some drivers don't provide
        my $scale = $sth->{SCALE}->[$i];
        my $prec  = $sth->{PRECISION}->[$i];
        my $type  = $sth->{TYPE}->[$i];
    
        ### Display the field information
	my $field = new TableField({ name => $name, nullable => $nullable, scale => $scale, prec => $prec, type => $type });

	push @fields, $field;
    }

    my @sortedFields = sort { $a->name cmp $b->name } @fields;
    foreach (@sortedFields) {

        printf "%-30s %5d      %4d   %4d   %s\n",
                $_->name, $_->type, $_->prec, $_->scale, $_->nullable;

	} 
    ### Explicitly deallocate the statement resources
    ### because we didn't fetch all the data
    $sth->finish();
}
}


package TableField;

sub new {

my $class = shift;
my $args = shift;

return bless $args, $class;

}

sub name { $_[0]->{name} }
sub type { $_[0]->{type} }
sub prec { $_[0]->{prec} }
sub scale { $_[0]->{scale} }
sub nullable { $_[0]->{nullable} }

1;

