If you run a select on every table ordered by the primary key you can compare the rows without loading the full tables in memory.
For instance, supposing the primary key my_key is of some string type:
my $sql = <<EOSQL;
select my_key, foo1, foo2, ...
from bar
order by my_key
EOSQL
my $sth1 = $dbh1->prepare($sql);
my $sth2 = $dbh2->prepare($sql);
unless ($sth1->execute and $sth2->execute) {
die ...;
}
my $row1 = $sth1->fetchrow_arrayref;
my $row2 = $sth1->fetchrow_arrayref;
while ($row1 and $row2) {
if ($row1->[0] lt $row2->[0]) {
print "$row1->[0] only in db 1\n"
$row1 = $sth1->fetchrow_arrayref;
}
elsif ($row1->[0] gt $row2->[0]) {
print "$row2->[0] only in db 2\n"
$row2 = $sth2->fetchrow_arrayref;
}
else {
# a better equality check for rows could be needed
# if the db software is different or if the db
# schemas are not identical:
if ("@$row1" eq "@$row2") {
print "row $row1->[0] differ\n"
}
$row1 = $sth1->fetchrow_arrayref;
$row2 = $sth2->fetchrow_arrayref;
}
}
while ($row1) {
print "$row1->[0] only in db 1\n"
$row1 = $sth1->fetchrow_arrayref;
}
while ($row2) {
print "$row2->[0] only in db 2\n"
$row2 = $sth2->fetchrow_arrayref;
}
|