#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Storable;
use Getopt::Long;
use Config::INI::Reader;
## For quick sanity checks.
#use diagnostics;
#use Data::Dumper;
#use YAML;
# db connections
# This can be done differently - this
# is just how I do it...
unshift @INC, '/usr/local/perl/lib';
require mydb::DB;
# Can we perform deletes - lets always start off w/ a NO
# and explicitly trigger a yes...
# 0 for NO
# 1 for Yes
my $deletes = 0;
# some sub refs
my $fetch = \&fetch;
my $dprint = \&debug;
my $timestamp = \×tamp;
my $sendmail = \&sendmail;
#Process comand line options.
my ( $config, $store, $logprefix, $debug );
my $opts = GetOptions("config=s" => \$config,
"store" => \$store,
"logprefix=s" => \$logprefix,
"debug" => \$debug);
#warn $INC{'DBI.pm'} if $debug;
## start the log
my $LOG_DIR = '/tmp';
my $LOG = ( $logprefix ) ?
$logprefix . "_$config-" . $timestamp->('log') :
"$config-" . $timestamp->('log');
open my $LF, '>', "$LOG_DIR/$LOG" or
die "unable to open $LOG_DIR/$LOG: $!\n";
# Start an exceptions file as well
open my $EF, '>', "$LOG_DIR/$LOG.err" or
die "unable to open $LOG_DIR/$LOG.err: $!\n";
print $LF scalar(localtime), "\n\n";
# Process table load ini file
my $inidir = 'Configs/';
my ( @rmaporder, @lmaporder, $rkey, $lkey, $rtable, $ltable, %defaults
+ );
my ( $source, $target );
my $colcount = 0;
if ( $config )
{
my $configuration = Config::INI::Reader->read_file("$inidir/$confi
+g");
# Valid Items are keys columns defaults and tables
$rkey = $configuration->{'keys'}->{'rkey'};
$lkey = $configuration->{'keys'}->{'lkey'};
$rtable = $configuration->{'tables'}->{'rtable'};
$ltable = $configuration->{'tables'}->{'ltable'};
# Be careful here if its not set lets re-set this to 0
$deletes = $configuration->{'configs'}->{'deletes'} || 0;
for ( keys %{ $configuration->{ 'columns' } } )
{
push @rmaporder, $_;
push @lmaporder, $configuration->{'columns'}->{$_};
$colcount++;
}
for ( keys %{ $configuration->{'default'} } )
{
$defaults{$_} = $configuration->{'default'}->{$_};
}
}
else
{
die "usage: $0 --config=config.ini OPTIONAL: --logprefix=test -s -
+d \n";
}
## End of ini file processing
$dprint->(qq{\@rmaporder = @rmaporder});
$dprint->(qq{\@lmaporder = @lmaporder});
print $LF "remote table => $rtable\nremote key => $rkey\n";
print $LF "local table => $ltable\nlocal key => $lkey\n";
# recylce $config for logfile naming
# minus the .ini
$config =~ s!\..*!! ;
# Hold the errors for reporting...
my @ERRS;
$dprint->(qq{initializing DB connections});
## Local - Informix Server
my $ldbh = myDB->Iproduction();
$ldbh->{ChopBlanks} = "true";
$ldbh->{PrintError} = 0;
$ldbh->{RaiseError} = 0;
# End on Local DB connection
## REMOTE - SQL server
my $rdbh = myDB->Sstage();
$rdbh->{ChopBlanks} = "true";
$rdbh->{PrintError} = 0;
$rdbh->{RaiseError} = 0;
$rdbh->{AutoCommit} = 1;
# End of Remote DB connection
# remote query
my $rfieldlist = join",", @rmaporder;
my $rquery = qq{select $rfieldlist from $rtable};
# local query
my $lfieldlist = join",", @lmaporder;
my $lquery = qq{select $lfieldlist from $ltable};
$dprint->(qq{Remote query = $rquery});
$dprint->(qq{Local query = $lquery});
# prepare and execute our sql query on the
# local DB
$dprint->(qq{prepare});
my $lsth = $ldbh->prepare($lquery) or errpt('FATAL', $ldbh->errstr(),
+'NA', $lquery);
$lsth->execute() or errpt ( 'FATAL', $ldbh->errstr(), 'NA', $lquery );
# prepare and execute our sql query on the
# remote DB
$dprint->(qq{execute});
my $rsth = $rdbh->prepare($rquery) or errpt('FATAL', $rdbh->errstr(),
+'NA', $rquery);
$rsth->execute() or errpt ( 'FATAL', $rdbh->errstr(), 'NA', $rquery );
my %RemoteDB;
my %LocalDB;
# hashes to hold the initial db slurp
my %rdb;
my %ldb;
my $counter = 0;
$dprint->(qq{Binding row data locally});
# Bind columns to hashes
for ( @lmaporder )
{
$counter++;
$ldb{$_} = undef;
$lsth->bind_col( $counter, \$ldb{$_} );
}
$dprint->(qq{local counter = $counter});
$dprint->(qq{Binding row data remotely});
$counter = 0;
for ( @rmaporder )
{
$counter++;
$rdb{$_} = undef;
$rsth->bind_col( $counter, \$rdb{$_} );
}
$dprint->(qq{remote counter = $counter});
# Fetch the data and hold in these hashes
$dprint->(qq{Fetching remote data});
%RemoteDB = $fetch->($rsth, \%rdb, $rkey);
$dprint->(qq{fetching local data});
%LocalDB = $fetch->($lsth, \%ldb, $lkey);
store [\%RemoteDB, %rdb, \%LocalDB, \%ldb], "$LOG_DIR/$LOG.loc"
if $store;
my @rinsert_keys;
my @rinspect_keys;
my @rdelete_keys;
# Populate arrays with keys
#
# @rinsert_keys are keys that exist locally
# and DO NOT exist remotely. These will always
# be inserts
for ( keys %LocalDB )
{
push @rinsert_keys, $_ if ( ! exists $RemoteDB{$_} );
}
# @rinspect_keys represent rows of data
# where the keys are the same remotely
# and locally - this is a dumping
# ground which we will further inspect
# the data later on
for ( keys %LocalDB )
{
push @rinspect_keys, $_ if ( exists $RemoteDB{$_} );
}
# @rdelete_keys represent rows of data where
# the key exists remotely but not locally
# these just get deleted.
for ( keys %RemoteDB )
{
push @rdelete_keys, $_ if ( ! exists $LocalDB{$_} );
}
# Insert Routines
print $LF "\nINFO: +++ There are ", scalar(@rinsert_keys), " to insert
+\n";
for my $keys (@rinsert_keys)
{
my @VALUES;
for my $count ( 0 .. $#lmaporder )
{
push @VALUES, $LocalDB{$keys}{$lmaporder[$count]};
}
my $values = join',',@VALUES;
my $query = "INSERT into $rtable ($rfieldlist) VALUES ($values)";
print $LF "INSERT: $keys QUERY: $query\n";
my $rsth = $rdbh->prepare($query) or
errpt("INSERT", $rdbh->errstr(), $keys, $query);
$rsth->execute() or errpt("INSERT", $rdbh->errstr(), $keys, $query
+);
}
# Inspect/Update Routines
print $LF "\nINFO: +++ There are ", scalar(@rinspect_keys), " to INSPE
+CT\n";
for my $keys (@rinspect_keys)
{
my @UPDATESET;
my @WHERE;
my $UPDATE;
# Inspect the actual column data, if its different push it onto
# @UPDATESET array for later processing
for my $count ( 0 .. $#lmaporder )
{
print $LF "INSPECT $keys: Local $lmaporder[$count] = $LocalDB{
+$keys}{$lmaporder[$count]}\n";
print $LF "INSPECT $keys: Remote $rmaporder[$count] = $RemoteD
+B{$keys}{$rmaporder[$count]}\n";
push @UPDATESET, "$rmaporder[$count] = $LocalDB{$keys}{$lmapor
+der[$count]}"
unless ( $LocalDB{$keys}{$lmaporder[$count]} eq $RemoteDB{$k
+eys}{$rmaporder[$count]} );
}
$UPDATE = join', ', @UPDATESET;
# If there is anything here, goto work...
if ( scalar(@UPDATESET) > 0 )
{
my $query = "UPDATE $rtable SET $UPDATE WHERE $rkey = $RemoteD
+B{$keys}{$rkey}";
print $LF "UPDATE: $keys QUERY=$query\n";
my $rsth = $rdbh->prepare($query) or
errpt ("UPDATE", $rdbh->errstr(), $keys, $query);
$rsth->execute() or errpt ("UPDATE", $rdbh->errstr(), $keys, $
+query);
}
else
{
print $LF "INSPECT: $keys Nothing to do\n";
next;
}
}
# Delete Routines
print $LF "\nINFO: +++ There are ", scalar(@rdelete_keys), " to delete
+\n";
for my $keys ( @rdelete_keys )
{
my $delstat = ( $deletes == 0 ) ? 'off' : 'on';
print $LF "DELETE: deletes are set to $delstat\n";
last if ( $deletes == 0 );
my $query = "DELETE FROM $rtable WHERE $rkey = $RemoteDB{$keys}{$r
+key}";
print $LF "DELETE: $keys\nDELETE: $query\n";
my $rsth=$rdbh->prepare($query) or errpt("DELETE", $rdbh->errstr()
+, $keys, $query);
$rsth->execute() or errpt("DELETE", $rdbh->errstr(), $keys, $query
+);
}
unlink ("$LOG_DIR/$LOG.err") if -s "$LOG_DIR/$LOG.err";
#### Local Subs ####
sub fetch
{
my $sth = shift;
my $dbhash = shift;
my $key = shift;
my %DB;
while ( $sth->fetchrow_hashref )
{
# Ensure that ALL data is properly quoted
# using the dbi->quote method
$DB{$dbhash->{$key}}{$_}
= $rdbh->quote($dbhash->{$_}) for keys %$dbhash
}
return %DB or errpt ( 'FATAL', 'Unable to return data from fetch',
+ 'NA', 'NA' );
}
sub debug
{
warn "@_\n" if $debug
}
sub errpt
{
my $TYPE = shift;
my $DB_ERR = shift;
my $KEY = shift;
my $QUERY = shift;
my $ERR = "$TYPE key $KEY: $DB_ERR";
push @ERRS, $ERR;
print $LF "$TYPE FAILURE: key = $KEY $DB_ERR\n$QUERY\n";
return 0 unless ($TYPE eq 'FATAL');
return 1;
}
sub timestamp
{
$_ = shift || "timestamp";
my ( $sec, $min, $hour,
$mday, $mon, $year,
$wday, $yday ) = localtime();
$mon++;
$year += 1900;
my $log = sprintf "%4i%02i%02i%04i", $year,$mon,$mday, $$;
my $timestamp = sprintf "%4i-%02i-%02i %02i:%02i:%02i ",
$year, $mon, $mday, $hour, $min, $sec;
my $return = ( $_ eq "timestamp" ) ? $timestamp : $log;
return $return;
}
|
