Listing 3 find_differences.pl: Implementation of the range search algorithm

#!/usr/bin/perl -w
# find_differences
# (C) Giuseppe Maxia 2003
# 
# Compare two tables in two remote databases, looking for different records.
# Requires a "shadow table", created by make_shadow_table
# 
use strict;
use DBI;

# This script is for educational purposes only.
# Parameters are embedded in the script itself.
# For production, it woyld be better to use either
# a config file or any Getopt::XX modules
my $db1 = "xcompany";
my $db2 = "xcompany";
my $host1 = "localhost";
my $host2 ="192.168.59.9";
my ( $user1, $password1 ) = ( 'myuser', 'secret' );
my ( $user2, $password2 ) = ( 'myuser', 'secret' );

my $dbh1 =  DBI->connect("DBI:mysql:$db1;host=$host1",
    $user1, $password1, 
    {RaiseError => 1, PrintError =>0}) 
    or die "can't connect to $host1 : $DBI::errstr";

my $dbh2 =  DBI->connect("DBI:mysql:$db2;host=$host2",
    $user2, $password2, 
    {RaiseError => 1, PrintError =>0}) 
    or die "can't connect to $host2 : $DBI::errstr";

$dbh1->do(qq{set SQL_LOG_OFF=1 }); # prevents writing on the general log file
$dbh2->do(qq{set SQL_LOG_OFF=1 }); # which can become quite big if you 

                                   # have many records

my @probes =();

my $initialprobe = qq{
  SELECT 
    span, COUNT(*) AS cnt, 
    CONCAT(SUM(CONV(SUBSTRING(signature,1,8),16,10)), 
    SUM(CONV(SUBSTRING(signature,9,8),16,10)),
    SUM(CONV(SUBSTRING(signature,17,8),16,10)),
    SUM(CONV(SUBSTRING(signature,25,8),16,10))) AS sig 
  FROM 
    shadow_table 
  GROUP BY 
    span };

my $probe1 = $dbh1->selectall_arrayref($initialprobe);
my $probe2 = $dbh2->selectall_arrayref($initialprobe);
my @totest = ();
my $maxdiff = 0;

for (0.. @{$probe1} -1) {
    if ($probe1->[$_]->[2] ne $probe2->[$_]->[2]) {
        push @totest, $probe1->[$_]->[0];
        $maxdiff += $probe1->[$_]->[1];
    }
}
my $get_probe_sig = qq{SELECT signature from shadow_table where span = ?};
my $sth1 = $dbh1->prepare($get_probe_sig);

for (@totest) {
    $sth1->execute($_);
    my $columns = $sth1->fetchall_arrayref();
    my $query = qq{SELECT PK FROM shadow_table WHERE span = $_ AND
    signature NOT IN (}
    . (join(",", map {"'$_->[0]'"} @$columns ))
    . ")";
    my $sth2 = $dbh2->prepare($query);
    $sth2->execute();
    while (my $different = $sth2->fetchrow_arrayref()) {
        print "$different->[0]\t";
    }
}

print "\n";