1 CREATE OR REPLACE FUNCTION conifer.enrich_uris(record BIGINT) RETURNS TEXT AS $func$
4 use MARC::File::XML (BinaryEncoding => 'UTF-8');
7 use Unicode::Normalize;
11 MARC::Charset->assume_unicode(1);
13 my $q = spi_prepare('SELECT marc FROM biblio.record_entry WHERE id = $1', 'BIGINT');
14 my $marc = spi_exec_prepared($q, $_[0])->{rows}->[0]->{marc};
16 my $record = MARC::Record->new_from_xml($marc);
18 my @eights = $record->field('856');
19 foreach my $ocho (@eights) {
20 my @ous = $ocho->subfield('9');
21 foreach my $ou (@ous) {
22 if ($ou eq 'WINDSYS' or $ou eq 'OWA') {
23 $record->delete_field($ocho);
26 $ocho->update('9' => 'LUSYS');
30 # Fix URIs that raise SSL cert issues
31 my $uri = $ocho->subfield('u');
32 if ($uri =~ m#^https://librweb#) {
33 $uri =~ s{^https://librweb}{http://librweb};
34 $ocho->update('u' => $uri);
37 # Provide an indication of the platform
38 if ($uri =~ m#scholarsportal#) {
39 $ocho->update('y' => 'Available online / disponible en ligne (ScholarsPortal)');
40 } elsif ($uri =~ m#myilibrary#) {
41 $ocho->update('y' => 'Available online / disponible en ligne (MyiLibrary)');
42 } elsif ($uri =~ m#sagepub#) {
43 $ocho->update('y' => 'Available online / disponible en ligne (Sage)');
47 # Do not need access notes anymore
48 my @access = $record->field('506');
49 foreach my $note (@access) {
50 my @ous = $note->subfield('9');
51 foreach my $ou (@ous) {
53 $record->delete_field($note);
58 # Update provenance of the records
59 my @provs = $record->field('040');
60 foreach my $prov (@provs) {
61 $prov->delete_subfield(code => 'd', match => qr/CaOWA/);
62 my @subfields = $prov->subfield('d');
64 foreach my $subfield (@subfields) {
65 if ($subfield eq 'CaOSUL') {
70 $prov->add_subfields('d' => 'CaOSUL');
75 my $ua = LWP::UserAgent->new;
82 my $lccn = $record->field('010');
84 $lccn_number = $lccn->subfield('a');
88 $lccn_number =~ s{^\s*(\S+)\s*$}{$1};
89 $lccn->update('a', $lccn_number);
90 my $response = $ua->get("http://xisbn.worldcat.org/webservices/xid/lccn/$lccn_number?method=getMetadata&format=json&fl=*");
91 my $metadata = decode_json($response->decoded_content);
92 if ($response->is_success) {
93 $oclc_number = $metadata->{'list'}->[0]->{'oclcnum'}->[0];
94 $owi_number = $metadata->{'list'}->[0]->{'owi'}->[0];
97 my @isbntags = $record->field('020');
98 foreach my $isbntag (@isbntags) {
99 my $rawisbn = $isbntag->subfield('a');
100 next unless $rawisbn;
101 $rawisbn =~ s{^\s*(\S+)\s*$}{$1};
102 my ($isbn, $rest) = split(/ /, $rawisbn, 2);
103 if ($rawisbn ne $isbn) {
104 $isbntag->update('a', $isbn);
106 if ($rest and $isbntag->subfield('q') ne $rest) {
107 $isbntag->update('q', $rest);
109 # short-circuit to save API budget
110 next if $oclc_number and $owi_number and $lccn_number;
112 my $response = $ua->get("http://xisbn.worldcat.org/webservices/xid/isbn/$isbn?method=getMetadata&format=json&fl=*");
113 next unless $response->is_success;
114 my $metadata = decode_json($response->decoded_content);
115 if (exists $metadata->{'list'}->[0]->{'lccn'} and !$lccn_number) {
116 $lccn_number = $metadata->{'list'}->[0]->{'lccn'}->[0];
117 my $nf = MARC::Field->new('010', '', '', 'a' => "$lccn_number");
118 $record->insert_grouped_field($nf);
119 my $response = $ua->get("http://xisbn.worldcat.org/webservices/xid/lccn/$lccn_number?method=getMetadata&format=json&fl=*");
120 if ($response->is_success) {
121 my $metadata = decode_json($response->decoded_content);
122 $owi_number = $metadata->{'list'}->[0]->{'owi'}->[0];
125 if (exists $metadata->{'list'}->[0]->{'oclcnum'} and !$oclc_number) {
126 $oclc_number = $metadata->{'list'}->[0]->{'oclcnum'}->[0];
127 my $response = $ua->get("http://xisbn.worldcat.org/webservices/xid/oclcnum/$oclc_number?method=getMetadata&format=json&fl=*");
128 if ($response->is_success) {
129 my $metadata = decode_json($response->decoded_content);
130 $owi_number = $metadata->{'list'}->[0]->{'owi'}->[0];
136 my $found_oclcnum = 0;
137 my $found_owinumber = 0;
140 $owi_number =~ s{^owi}{};
141 my @idtags = $record->field('024');
142 foreach my $idtag (@idtags) {
143 if ($idtag->indicator(1) eq '7' and $idtag->subfield('a') eq "http://worldcat.org/entity/work/id/$owi_number" and $idtag->subfield('2') eq 'uri') {
144 $found_owinumber = 1;
147 if (!$found_owinumber) {
148 my $nf = MARC::Field->new('024', '7', '', 'a' => "http://worldcat.org/entity/work/id/$owi_number", '2' => 'uri');
149 $record->insert_grouped_field($nf);
154 my @idtags = $record->field('035');
155 foreach my $idtag (@idtags) {
156 if ($idtag->subfield('a') =~ m#^\(OCoLC\)$oclc_number#) {
160 if (!$found_oclcnum) {
161 my $nf = MARC::Field->new('035', '', '', 'a' => "(OCoLC)$oclc_number");
162 $record->insert_grouped_field($nf);
166 my $xml = $record->as_xml_record();
168 $xml =~ s/^<\?xml.+\?\s*>//go;
169 $xml =~ s/>\s+</></go;
170 $xml =~ s/\p{Cc}//go;
172 # Embed a version of OpenILS::Application::AppUtils->entityize()
173 # to avoid having to set PERL5LIB for PostgreSQL as well
177 # Convert raw ampersands to entities
178 $xml =~ s/&(?!\S+;)/&/gso;
180 # Convert Unicode characters to entities
181 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
183 $xml =~ s/[\x00-\x1f]//go;
186 $func$ LANGUAGE PLPERLU;
188 CREATE OR REPLACE FUNCTION conifer.marc_version() RETURNS TEXT AS $func$
190 return $MARC::Record::VERSION;
191 $func$ LANGUAGE PLPERLU;
193 CREATE OR REPLACE FUNCTION conifer.enrich_next() RETURNS BIGINT AS $func$
198 SELECT id INTO last_id FROM conifer.conifer.enriched_bre ORDER BY id DESC LIMIT 1;
199 SELECT record INTO next_id FROM asset.call_number WHERE record > last_id AND deleted IS FALSE AND owning_lib IN (103, 105, 131, 150) ORDER BY record LIMIT 1;
200 UPDATE biblio.record_entry SET marc = conifer.enrich_uris(next_id), edit_date = NOW() WHERE id = next_id;
201 INSERT INTO conifer.enriched_bre (id, success, result) VALUES (next_id, TRUE, NULL);
204 WHEN OTHERS THEN INSERT INTO conifer.enriched_bre (id, success, result) VALUES (next_id, FALSE, SQLERRM);
207 $func$ LANGUAGE PLPGSQL;