Infrastructure

On unm run against the tidy database¶

To verify ucsb controlled records to change on ORC

su postgres -c "psql -d d1-tidy -c \"select guid, \
serial_version, TO_CHAR(date_modified, 'YYYY-MM-DD\\"T\\"HH24:MI:SS.MSTZ') \
from ucsb_systemmetadata usm \
where exists (
select cr1.pid \
from change_record cr1, change_record cr2 \
where usm.guid = cr1.pid and \
cr1.id = cr2.id and \
cr1.runid = cr2.runid and \
coalesce(cr1.cnucsb,'nvl') like cr2.resulting and \
coalesce(cr1.cnorc, 'nvl') not like cr2.resulting and \
cr1.runid = 2 )\"" > ucsb_to_orc_verification.in

---

To verify ucsb controlled records to change on UNM

su postgres -c "psql -d d1-tidy -c \"select guid, \
serial_version, TO_CHAR(date_modified, 'YYYY-MM-DD\\"T\\"HH24:MI:SS.MSTZ') \
from ucsb_systemmetadata usm \
where exists (
select cr1.pid \
from change_record cr1, change_record cr2 \
where usm.guid = cr1.pid and \
cr1.id = cr2.id and \
cr1.runid = cr2.runid and \
coalesce(cr1.cnucsb,'nvl') like cr2.resulting and \
coalesce(cr1.cnunm, 'nvl') not like cr2.resulting and \
cr1.runid = 2 )\"" > ucsb_to_unm_verification.in

---

To verify orc and unm controlled records to change on UCSB

su postgres -c "psql -d d1-tidy -c \"select guid, \
serial_version, TO_CHAR(date_modified, 'YYYY-MM-DD\\"T\\"HH24:MI:SS.MSTZ') \
from orc_systemmetadata usm \
where exists (
select cr1.pid \
from change_record cr1, change_record cr2 \
where usm.guid = cr1.pid and \
cr1.id = cr2.id and \
cr1.runid = cr2.runid and \
coalesce(cr1.cnorc, 'nvl') <> 'null' and \
coalesce(cr1.cnunm, 'nvl') <> 'null' and \
coalesce(cr1.cnorc, 'nvl') like cr2.resulting and \
coalesce(cr1.cnunm, 'nvl') like cr2.resulting and \
coalesce(cr1.cnucsb, 'nvl') not like cr2.resulting and \
cr1.runid = 2 )\"" > to_ucsb_verification.in

---

delete first and last 2 lines of [orc|ucsb]_verification.in

results in file with the following lines similar to

knb-lter-fce.1108.2 | 2 | 2014-05-15 22:26:16.088

translate file into csv

perl -i -pe "s/^{\s([\s])\s+|\s([\s|])\s+|\s(.+)$/\1} \2 \3/" [orc|ucsb]_verification.in

read csv file and query database for correct entries, note any query that is 0.

---

Test records

su postgres -c "psql -d metacat -c \"select guid, serial_version, TO_CHAR(date_modified, 'YYYY-MM-DD\\"T\\"HH24:MI:SS') \
from systemmetadata \

LIMIT 10\"" > sysmeta_verification¶

install on target CNs

apt-get install libdbi-perl libdbd-pg-perl libio-prompt-perl

---

save the following perl script and
run with appropriate csv files generated above

on the appropriate cns¶

#!/usr/bin/perl

#####

file input must be a csv file that conforms to the pattern¶

pidserial_versiondate¶

#####

The program will execute a query against a metacat database's¶

table systemMetadata attempting to determine if any of the¶

rows in the database are different from the rows in the¶

CSV file¶

#####
#####

use strict;
use Carp;
use DBI;
use Getopt::Long();
use IO::Prompt;

#####

GLOBAL VARIABLES¶

#####

my $dbh;
my $dbname = "metacat";
my $host = "localhost";
my $username = "metacat";

my $metacat_select = "select count(*) AS COUNT from systemMetadata
where guid = ? and
serial_version = ? and
date_modified = to_timestamp(?, 'YYYY-MM-DD\"T\"HH24:MI:SS.MS')";

my $metacat_select_sth;

my $pid_column_key = 'PID';
my $serial_version_column_key = "SERIAL_VERSION";
my $date_modified_column_key = "DATE_MODIFIED";

sub usage {
my $message = $_[0];
if (defined $message && length $message) {
$message .= "\n"
unless $message =~ /\n$/;
}

my $command = $0;
$command =~ s#^.*/##;

print STDERR (
$message,
"usage: $command [--help] --file /full/path/of/csv/file.cvs\n",
"\n\tfile input must be a csv file that conforms to the pattern\n",
"\tpidserial_versiondate\n"
);

die("\n");
}

#####

read a cvs file with the format¶

pidserial_versiondate¶

return a reference to an array of hashes with the format¶

@output = {¶

'PID' => $pid,¶

'SERIAL_VERSION' => $serial_version,¶

'DATE_MODIFIED' => $date_modified);¶

#####
sub readCVS
{
my $file = shift;
my @output_array = ();
my $csv_fh;
open ($csv_fh, "< $file") or die "Cannot open $file: $!\n";
while (<$csv_fh>)
{
chop;
my ($pid,$serial_version,$date_modified) = split(" ", $_);
my %record_hash = ($pid_column_key => $pid,
$serial_version_column_key => $serial_version,
$date_modified_column_key => $date_modified);
push (@output_array, \%record_hash);

}

close ($csv_fh);
return \@output_array
}

#####

verify that each row in the CSV file has a corresponding¶

row in the database. Return 1 (true) if the row is verified,¶

otherwise return 0 (false)¶

#####
sub verifyMetacatRow
{
my ($pid, $serial_version, $date_modified) = @_;
$metacat_select_sth->bind_param(1,$pid);
$metacat_select_sth->bind_param(2,$serial_version);
$metacat_select_sth->bind_param(3,$date_modified);
$metacat_select_sth->execute();
my $metacat_select_hash_ref = $metacat_select_sth->fetchrow_hashref('NAME_lc');
if ( defined($metacat_select_hash_ref->{count}) &&
($metacat_select_hash_ref->{count} == 1) )
{
return 1;
}
return 0;
}

main: {

my $file;
my $help;
Getopt::Long::GetOptions(
'file=s' => \$file,
'help!' => \$help
) or usage("Invalid commmand line options.");

usage() if (defined $help);

usage("The full path of a CSV file (space separated) must be specified.")
unless ((defined $file) && (-e $file)) ;

my $password = prompt("Enter Metacat Postgresql DB password: ", -e => '*');
chomp($password);

my $rows_to_validate = readCVS($file);

my $dbh = DBI->connect("DBI:Pg:dbname=${dbname};host=${host}", "$username", "$password", {'RaiseError' => 1, 'PrintError' => 0});
$metacat_select_sth = $dbh->prepare($metacat_select);
foreach my $row (@{$rows_to_validate})
{
print "FAILURE: " . $row->{$pid_column_key} . "\n" unless (verifyMetacatRow($row->{$pid_column_key}, $row->{$serial_version_column_key}, $row->{$date_modified_column_key}));
}

}¶