From d06b0f015ddc01dac08b3cbb6f9f96bc7b2e31b7 Mon Sep 17 00:00:00 2001
From: Jason Etheridge <jason@esilibrary.com>
Date: Mon, 23 Sep 2013 09:48:35 -0400
Subject: [PATCH] script to make sql for bib overlays

Example:

./make_overlay_sql_from_marc.pl marc_file_with_901c_tags.xml > overlay.sql 2> rejected_marc_files.xml

Signed-off-by: Jason Etheridge <jason@esilibrary.com>
---
 make_overlay_sql_from_marc.pl | 51 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 51 insertions(+)
 create mode 100755 make_overlay_sql_from_marc.pl

diff --git a/make_overlay_sql_from_marc.pl b/make_overlay_sql_from_marc.pl
new file mode 100755
index 000000000..5ec4cc1fb
--- /dev/null
+++ b/make_overlay_sql_from_marc.pl
@@ -0,0 +1,51 @@
+#!/usr/bin/perl
+# Feed this script a list of UTF8 MARC-XML files as parameters.  Generated SQL goes to STDOUT,
+# MARC missing a single 901c value gets spit back out to STDERR.  Be sure to update the database
+# credentials below.
+
+use MARC::Batch;
+use MARC::File::XML ( BinaryEncoding => 'utf-8' );
+use MARC::Field;
+use Unicode::Normalize;
+use DBI;
+
+# DBI is just used for quoting, but the method does need a handle to the database
+my $DBI_RESOURCE = "dbi:Pg:dbname=evergreen;host=localhost;port=5432";
+my $DBI_USER = 'evergreen';
+my $DBI_PASSWD = 'evergreen';
+my $dbh = DBI->connect($DBI_RESOURCE, $DBI_USER, $DBI_PASSWD) or die("Database error: $DBI::errstr");
+
+binmode(STDOUT, ':utf8');
+binmode(STDIN, ':utf8');
+binmode(STDERR, ':utf8');
+
+print STDERR MARC::File::XML::header();
+
+foreach my $argnum ( 0 .. $#ARGV ) {
+
+    my $M;
+    open $M, '<:utf8', $ARGV[$argnum];
+    my $batch = MARC::Batch->new('XML',$M);
+
+	$batch->strict_off();
+	$batch->warnings_off();
+
+	while ( my $record = $batch->next() ) {
+
+        my @my901 = $record->field('901');
+        if (scalar(@my901) == 0 || scalar(@my901) > 1) {
+            print STDERR MARC::File::XML::record( $record );
+        } else {
+            my @my901c = $my901[0]->subfield('c');
+            if (scalar(@my901c) == 0 || scalar(@my901c) > 1) {
+                print STDERR MARC::File::XML::record( $record );
+            } else {
+                print "UPDATE biblio.record_entry SET marc = "
+                    . $dbh->quote( $record->as_xml_record() )
+                    . " WHERE id = " . $dbh->quote( $my901c[0] ) . ";\n";
+            }
+        }
+	}
+}
+
+print STDERR MARC::File::XML::footer();
-- 
2.11.0