==API sync scripts==

These were used to migrate existing API data from the .125 server to the .124 server.

apisync.pl
%%(perl)
#! /usr/bin/perl

use strict vars;
use DBI;
use Time::ParseDate;

### Config params ###
my $curDbName = 'pvsadmin';
my $curDbHost = '209.18.106.124';
my $curDbUser = 'web';
my $curDbPass = 'XXXXXXX';
my $curDbPort = '5432';

my $oldDbName = 'pvsadmin';
my $oldDbHost = '209.18.106.125';
my $oldDbUser = 'web';
my $oldDbPass = 'XXXXXXX';
my $oldDbPort = '5432';
### End config ###

my $curdbh = DBI->connect("dbi:Pg:dbname=$curDbName;
host=$curDbHost;
port=$curDbPort",
$curDbUser,
$curDbPass,
{ AutoCommit => 1 }) or die $DBI::errstr;

my $olddbh = DBI->connect("dbi:Pg:dbname=$oldDbName;
host=$oldDbHost;
port=$oldDbPort",
$oldDbUser,
$oldDbPass,
{ AutoCommit => 0 }) or die $DBI::errstr;

# Check for API keys that are in old but NOT in current DB (these will need
# to be added)

my $getKeys = "SELECT * FROM misc_userapi WHERE key <> ''";
my $curkeys = $curdbh->selectall_hashref($getKeys, 'key')
or die $DBI::errstr;
=pod
foreach my $key(keys %$curkeys) {
print "$key\t$curkeys->{$key}->{'last_payment'}\n";
}
=cut

my $oldkeys = $olddbh->selectall_hashref($getKeys, 'key')
or die $DBI::errstr;
=pod
foreach my $key(keys %$oldkeys) {
print "$key\t$oldkeys->{$key}->{'last_payment'}\n";
}
=cut

print "Keys in old DB that aren't in current DB:\n";
my $missingKeys = {};
foreach my $key(keys %$oldkeys) {
if(!exists $curkeys->{$key}) {
$missingKeys->{$key} = 1;
print "$key\t$oldkeys->{$key}->{'last_payment'}\n";
}
}
exit if(keys %$missingKeys <= 0);

print "Fetching data from old DB:\n";
my $sth1 = $olddbh->prepare("SELECT * FROM misc_userapi WHERE key = ?");
my $sth2 = $olddbh->prepare("SELECT * FROM misc_userprofile WHERE user_api_id = ?");
my $sth3 = $olddbh->prepare("SELECT * FROM auth_user WHERE id = ?");

my $ith3 = $curdbh->prepare("INSERT INTO auth_user VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?) RETURNING id");
my $ith1 = $curdbh->prepare("INSERT INTO misc_userapi VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?) RETURNING user_api_id");
my $ith2 = $curdbh->prepare("INSERT INTO misc_userprofile VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) RETURNING id");

foreach my $key(keys %$missingKeys) {
$| = 1;
# Manual processing here...
if($key =~ /^ca8e386|65a5717|f5d978e|bd6030c|7645dd5|0cca24d|f2efa0|562b937|8e91361|a43db30|4716a4b/)
{
print "Manually processing $key...\n";

# Get old records
$sth1->execute($key) or die $DBI::errstr;
my $sth1ref = $sth1->fetch();
$sth2->execute($sth1ref->[0]) or die $DBI::errstr;
my $sth2ref = $sth2->fetch();
$sth3->execute($sth2ref->[1]) or die $DBI::errstr;
my $sth3ref = $sth3->fetch();
my $email = $sth3ref->[4];
print "email: $email...";

# Insert new API record, get that new user_api_id
my $ith1 = $curdbh->prepare("INSERT INTO misc_userapi VALUES(DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?) RETURNING user_api_id");
my @ith1_bind_vals = @{$sth1ref}[1..scalar @{$sth1ref}-1];
$ith1->execute(@ith1_bind_vals) or die $DBI::errstr;
my $user_api_id = $ith1->fetch()->[0];

# Get user_id on current db
my $sth3cur = $curdbh->prepare("SELECT id FROM auth_user WHERE email = ?");
$sth3cur->execute($email);
my $user_id = $sth3cur->fetch()->[0];

# Update misc_userprofile with new user_api_id using
# user_id as the lookup key
my $ith2cur = $curdbh->prepare("UPDATE misc_userprofile SET user_api_id = ? WHERE user_id = ?");
$ith2cur->execute($user_api_id, $user_id);
print "done!\n";
next;
}

# Go ahead and copy records over, no existing records
print "Inserting record for key: $key...";
$sth1->execute($key) or die $DBI::errstr;
my $sth1ref = $sth1->fetch();
$sth2->execute($sth1ref->[0]) or die $DBI::errstr;
my $sth2ref = $sth2->fetch();
$sth3->execute($sth2ref->[1]) or die $DBI::errstr;
my $sth3ref = $sth3->fetch();

my @ith3_bind_vals = @{$sth3ref}[1..scalar @{$sth3ref}-1];
$ith3->execute(@ith3_bind_vals) or die $DBI::errstr;
my $user_id = $ith3->fetch()->[0];
print "new user_id: $user_id...";
my @ith1_bind_vals = @{$sth1ref}[1..scalar @{$sth1ref}-1];
$ith1->execute(@ith1_bind_vals) or die $DBI::errstr;
my $user_api_id = $ith1->fetch()->[0];
print "new user_api_id: $user_api_id...";
$sth2ref->[1] = $user_id;
$sth2ref->[11] = $user_api_id;
my @ith2_bind_vals = @{$sth2ref}[1..scalar @{$sth2ref}-1];
$ith2->execute(@ith2_bind_vals);'
print "done!\n";
}

END {
$curdbh->disconnect();
$olddbh->disconnect();
}
%%

apiUpdateLastPayment.pl
%%(perl)
#! /usr/bin/perl

use strict;
use DBI;
use Time::ParseDate;

### Config params ###
my $curDbName = 'pvsadmin';
my $curDbHost = '209.18.106.124';
my $curDbUser = 'web';
my $curDbPass = 'XXXXXX';
my $curDbPort = '5432';

my $oldDbName = 'pvsadmin';
my $oldDbHost = '209.18.106.125';
my $oldDbUser = 'web';
my $oldDbPass = 'XXXXXXX';
my $oldDbPort = '5432';
### End config ###

my $curdbh = DBI->connect("dbi:Pg:dbname=$curDbName;
host=$curDbHost;
port=$curDbPort",
$curDbUser,
$curDbPass,
{ AutoCommit => 1 }) or die $DBI::errstr;

my $olddbh = DBI->connect("dbi:Pg:dbname=$oldDbName;
host=$oldDbHost;
port=$oldDbPort",
$oldDbUser,
$oldDbPass,
{ AutoCommit => 0 }) or die $DBI::errstr;

# Check for API keys that are in old but NOT in current DB (these will need
# to be added)

my $getKeys = "SELECT user_api_id, key, last_payment FROM misc_userapi WHERE key <> ''";
my $curkeys = $curdbh->selectall_hashref($getKeys, 'key')
or die $DBI::errstr;
=pod
foreach my $key(keys %$curkeys) {
print "$key\t$curkeys->{$key}->{'last_payment'}\n";
}
=cut

my $oldkeys = $olddbh->selectall_hashref($getKeys, 'key')
or die $DBI::errstr;
=pod
foreach my $key(keys %$oldkeys) {
print "$key\t$oldkeys->{$key}->{'last_payment'}\n";
}
=cut

# Check for API keys in current DB with registration dates LESS THAN the
# corresponding keys in the old DB (these will need to be updated)

print "Keys in current DB with registration dates earlier than those in old DB:\n";
my $cursth = $curdbh->prepare("UPDATE misc_userapi SET last_payment=? WHERE key=?");
foreach my $key(keys %$curkeys) {
my $curkeyTS = parsedate($curkeys->{$key}->{'last_payment'});
my $oldkeyTS = parsedate($oldkeys->{$key}->{'last_payment'});
if(defined($curkeyTS) && $curkeyTS < $oldkeyTS) {
print "$key\t$curkeys->{$key}->{'last_payment'}\t$oldkeys->{$key}->{'last_payment'}\n";
print "Updating...";
if($cursth->execute($oldkeys->{$key}->{'last_payment'}, $key) == 1) {
print "done!\n";
}
else {
print "failed!\n";
}
}
}

END {
$curdbh->disconnect();
$olddbh->disconnect();
}
%%

----
CategoryITDefunct