Listing 2 make_shadow_table.pl: Creates the auxiliary structure for remote comparison

#!/usr/bin/perl -w
# make_shadow_table
# (C) Giuseppe Maxia 2003
#
# Creates a shadow table with a CRC signature and 
# the corresponding primary key.
# 
use strict;
use DBI;

my $skip_timestamp = 1;

my ($host, $db, $table) = @ARGV ;
$table or die
        "syntax: make_signatures host database table\n";

my @signature_queries = (
        
  ['drop signatures',   qq{
    DROP TABLE IF exists shadow_table
    }],
  ['create signatures', qq{
CREATE TABLE shadow_table ( 
    signature CHAR(32) NOT NULL PRIMARY KEY,
    span BIGINT UNSIGNED NOT NULL,
    PK !PK_TYPE! NOT NULL,
    UNIQUE KEY PK(PK),
    KEY span (span)
    )}],
   ['insert signatures', qq{
INSERT INTO 
    shadow_table (signature, PK, span) 
SELECT 
    MD5(CONCAT_WS('/#/',!COLUMN_NAMES!)),
    !PK_NAME!,
    !PK_NAME! / 512
FROM 
    !TABLE_NAME! 
}]
);

sub make_signatures {
    my $dbh = shift;
    my $table = shift;
    my $key_pos = 3;
    my $description = $dbh->selectall_arrayref(qq{describe $table});
    my ($version) =  @{$dbh->selectcol_arrayref(qq{select version()})};
    if ($version =~ /4\.1/) {
            $key_pos = 4;
    }
    my @pkeys = grep {$_->[$key_pos] eq "PRI"} @$description;
    die "can't make signatures. Table $table has no primary key.\n" 
        unless scalar @pkeys ;
    my ($primary, $pk_type) =();
    if (scalar @pkeys > 1) { # composite PK
        $primary = join "," , map  {"`$_->[0]`"} @pkeys;
        $primary = "MD5(CONCAT_WS('/#/'," . $primary . "))";
        $pk_type = "CHAR(40)";
    }
    else {
        ($primary, $pk_type) = map {'`' . $_->[0] . '`', $_->[1]} @pkeys;
    }
    my $columns ='';
    for (@$description) {
        next if $skip_timestamp && ($_->[1] =~ /timestamp/i);
        my $column = "`$_->[0]`";
        $columns .= ',' if $columns;
        if (($_->[2] eq 'YES') && 
            ((not $_->[$key_pos]) || (($_->[$key_pos]) && \

              ($_->[$key_pos]) ne 'PRI'))) {
            $column = "COALESCE($column, '#NULL#')"
        }
        $columns .= $column;
    }
    
    for (@signature_queries) 
    {
        my $query = $_->[1];
        $query =~ s/!PK_TYPE!/$pk_type/;
        $query =~ s/!PK_NAME!/$primary/g;
        $query =~ s/!TABLE_NAME!/$table/;
        $query =~ s/!COLUMN_NAMES!/$columns/;
        print "#$_->[0] $query\n"; 
        $dbh->do ($query);
    }
}

my $dbh = DBI->connect("DBI:mysql:$db;host=$host", "myuser","secret",{RaiseError=>1});

make_signatures ( $dbh, $table);