==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