MVLC OCLC Number maintenance code

CREATE TABLE extend_reporter.mvlc_oclc
(
  oclc_num bigint NOT NULL,
  deleted boolean NOT NULL DEFAULT false,
  last_deleted timestamp without time zone,
  last_created timestamp without time zone,
  last_exported timestamp without time zone,
  last_exported_state boolean,
  CONSTRAINT mvlc_oclc_pkey PRIMARY KEY (oclc_num)
);

CREATE OR REPLACE FUNCTION extend_reporter.update_oclc()
  RETURNS void AS
$BODY$
DECLARE
	cur_oclc BIGINT;
	cur_deleted BOOLEAN;
BEGIN
	FOR cur_oclc, cur_deleted IN
		SELECT oclc_num, FIRST(deleted ORDER BY deleted ASC) AS deleted FROM (
			SELECT SUBSTRING(value FROM 'ocolc +(?:oc[nm]|on)?0*([0-9]*)')::BIGINT AS oclc_num, bre.deleted
			FROM metabib.real_full_rec mrfr
			JOIN biblio.record_entry bre ON mrfr.record = bre.id
			WHERE bre.source = 1 AND mrfr.tag = '035' AND subfield = 'a' AND value LIKE 'ocolc %' AND SUBSTRING(value FROM 'ocolc +(?:oc[nm]|on)?0*([0-9]*)') NOT SIMILAR TO ' *'
			UNION ALL
			SELECT oclc_num, true FROM extend_reporter.mvlc_oclc WHERE NOT deleted
		)x GROUP BY 1
		LOOP
		UPDATE extend_reporter.mvlc_oclc SET deleted = cur_deleted WHERE oclc_num = cur_oclc;
		IF NOT FOUND THEN
			INSERT INTO extend_reporter.mvlc_oclc(oclc_num, deleted) VALUES (cur_oclc, cur_deleted);
		END IF;
	END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION extend_reporter.mvlc_oclc_update()
  RETURNS trigger AS
$BODY$
BEGIN
	IF TG_OP = 'UPDATE' THEN
		IF NEW.deleted AND NOT OLD.deleted THEN
			NEW.last_deleted := NOW();
		ELSIF NOT NEW.deleted AND OLD.deleted THEN
			NEW.last_created := NOW();
		END IF;
	ELSE
		IF NEW.deleted THEN
			NEW.last_deleted := NOW();
		ELSE
			NEW.last_created := NOW();
		END IF;
	END IF;
	RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER mvlc_oclc_trg
  BEFORE INSERT OR UPDATE
  ON extend_reporter.mvlc_oclc
  FOR EACH ROW
  EXECUTE PROCEDURE extend_reporter.mvlc_oclc_update();


We call the extend_reporter.update_oclc function daily....we also do this kind of thing first:

-- Fix OCLC records that appear to be null sourced
WITH incorrect_oclc AS (
SELECT DISTINCT bre.id FROM metabib.real_full_rec mrfr
JOIN biblio.record_entry bre ON bre.id = mrfr.record
WHERE mrfr.tag = '035' AND mrfr.value LIKE '%ocolc%'
AND NOT bre.deleted AND bre.source IS NULL
)
UPDATE biblio.record_entry
SET source = 1
WHERE id IN
(SELECT id
 FROM incorrect_oclc);




Script we use to dump files:

#!/usr/bin/perl

use warnings;
use strict;

use DBI; # DB connection
use Getopt::Long; # Command line options
use OpenSRF::System; # Read DB info from OpenSRF
use OpenSRF::Utils; # Read DB info from OpenSRF
use File::Basename; # Store output with our script unless we had an absolute path provided

my (undef,undef,undef,$mday,$mon,$year,undef,undef,undef) = localtime();
my ($config, $filebase, $daysbefore, $outputdir ) = ('/openils/conf/opensrf_core.xml', sprintf('oclc_%04d%02d%02d_',$year + 1900, $mon + 1, $mday), 7, 'output');

GetOptions(
    'osrf-config=s' => \$config,        # OpenSRF core config file
    'filebase=s'    => \$filebase,      # Base filename for output files
    'daysbefore=i'  => \$daysbefore,    # How many days before today to limit output to
    'outputdir=s'   => \$outputdir,     # Output Directory
);

# Base SELECT for bib ID, MARC XML, and AOU Shortname
my $query = <<'BASE_QUERY';
SELECT oclc_num, deleted, last_exported_state
FROM extend_reporter.mvlc_oclc
WHERE
    (last_exported IS NULL OR last_exported_state != deleted)
    AND NOW() - $1::interval > CASE WHEN deleted THEN last_deleted ELSE last_created END
ORDER BY oclc_num
BASE_QUERY

my $update_query = <<'UPDATE_QUERY';
UPDATE extend_reporter.mvlc_oclc
SET last_exported_state = $1, last_exported = NOW()
WHERE oclc_num = ANY($2)
UPDATE_QUERY

OpenSRF::System->bootstrap_client( config_file => $config );

my %data_db;

my $sc = OpenSRF::Utils::SettingsClient->new;

$data_db{db_driver} = $sc->config_value( reporter => setup => state_store => 'driver' );
$data_db{db_host}   = $sc->config_value( reporter => setup => state_store => 'host' );
$data_db{db_port}   = $sc->config_value( reporter => setup => state_store => 'port' );
$data_db{db_name}   = $sc->config_value( reporter => setup => state_store => 'db' );
if (!$data_db{db_name}) {
    $data_db{db_name} = $sc->config_value( reporter => setup => state_store => 'name' );
    print STDERR 'WARN: <database><name> is a deprecated setting for database name. For future compatibility, you should use <database><db> instead.' if $data_db{db_name};
}
$data_db{db_user}   = $sc->config_value( reporter => setup => state_store => 'user' );
$data_db{db_pw}     = $sc->config_value( reporter => setup => state_store => 'pw' );

die 'Unable to retrieve database connection information from the settings server'
    unless ($data_db{db_driver} && $data_db{db_host} && $data_db{db_port} && $data_db{db_name} && $data_db{db_user});

my $data_dsn  = 'dbi:' .  $data_db{db_driver} . ':dbname=' .  $data_db{db_name} .';host=' .  $data_db{db_host} . ';port=' .  $data_db{db_port};
my $dbh;

$dbh = DBI->connect(
    $data_dsn,
    $data_db{db_user},
    $data_db{db_pw},
    { AutoCommit => 0,
      pg_expand_array => 0,
      pg_enable_utf8 => 1,
      RaiseError => 1
    }
);

my $oclc_sth = $dbh->prepare($query);

$oclc_sth->execute("$daysbefore days");

my $oclc_data = { deleted => [], created => [], returned => [] };
while(my $data = $oclc_sth->fetchrow_hashref){
    if($data->{deleted} == 1) {
        push @{$oclc_data->{deleted}}, $data->{oclc_num};
    } elsif($data->{last_exported_state} and not $data->{deleted}) {
        push @{$oclc_data->{returned}}, $data->{oclc_num};
    } else {
        push @{$oclc_data->{created}}, $data->{oclc_num};
    }
}

$oclc_sth->finish;

sub write_files {
    my $type = shift;
    my $data = shift;
    return unless $data;
    my $fnum = 0;
    my $flines = -1;
    foreach my $num (@$data) {
        if (++$flines == 80000) {
            close OUTFILE;
            $flines = 0;
            $fnum++;
        }
        if ($flines == 0) {
            print "Opening $outputdir/$filebase$type.$fnum.txt\n";
            open OUTFILE, ">$outputdir/$filebase$type.$fnum.txt";
        }
        print OUTFILE "#$num\n";
    }
    close OUTFILE;
}

write_files('deleted', $oclc_data->{deleted}) if $oclc_data->{deleted};
write_files('created', $oclc_data->{created}) if $oclc_data->{created};
write_files('returned', $oclc_data->{returned}) if $oclc_data->{returned};

my $update_sth = $dbh->prepare($update_query);
$update_sth->execute(1, $oclc_data->{deleted}) if $oclc_data->{deleted};
$update_sth->execute(0, $oclc_data->{created}) if $oclc_data->{created};
$update_sth->execute(0, $oclc_data->{returned}) if $oclc_data->{returned};

$update_sth->finish;
$dbh->commit;
# Close database connection
$dbh->disconnect;