Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

tblSync

by tcf03 (Deacon)
on Sep 06, 2007 at 18:25 UTC ( [id://637496]=sourcecode: print w/replies, xml ) Need Help??
Category: Database Programming
Author/Contact Info Ted Fiedler <fiedlert@gmail.com>
Description: syncs database tables. My main use is to sync Informix tables and MS SQL Server tables.
ini file looks like this:
[configs] ; can we perform deletes in this table? deletes = 0 ; 0 for no / 1 for yes [columns] ;Remote(MSSQL) Local(INFORMIX) IDCode =id_code FirstName =first_name MiddleInitial = mid_init Lastname = last_name Address = address Address2 = address2 Address3 = address3 City = city State = state ZipCode = zip Country = country BirthDate = birth_date [keys] rkey = IDCode lkey = id_code [tables] rtable = Employee_ID ltable = empview [defaults]
#!/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 = \&timestamp;
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;
}
Replies are listed 'Best First'.
Re: tblSync
by tcf03 (Deacon) on Sep 06, 2007 at 18:38 UTC
    use DBI; is not really necessary above. Its pulled in my DB::myDB... Horrible I know, Im working on pulling the db configs in via the INI file, Just have not gotten a round to it yet.

    Some Taint checking will also be in order I guess :)
    Ted
    --
    "That which we persist in doing becomes easier, not that the task itself has become easier, but that our ability to perform it has improved."
      --Ralph Waldo Emerson

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: sourcecode [id://637496]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (3)
As of 2025-04-19 12:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuliAnonymous Monks are no longer allowed to use Super Search, due to an excessive use of this resource by robots.