3 INSERT INTO config.upgrade_log (version) VALUES ('2.0.6');
4 INSERT INTO config.upgrade_log (version) VALUES ('0508'); -- gmc
6 CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$
8 use_id_for_tcn BOOLEAN;
12 -- Remove any existing 901 fields before we insert the authoritative one
13 NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield[^>]*?tag="901".+?</datafield>', '', 'g');
15 IF TG_TABLE_SCHEMA = 'biblio' THEN
16 -- Set TCN value to record ID?
17 SELECT enabled FROM config.global_flag INTO use_id_for_tcn
18 WHERE name = 'cat.bib.use_id_for_tcn';
20 IF use_id_for_tcn = 't' THEN
21 NEW.tcn_value := NEW.id;
22 norm_tcn_value := NEW.tcn_value;
24 -- yes, ampersands can show up in tcn_values ...
25 norm_tcn_value := REGEXP_REPLACE(NEW.tcn_value, E'&(?!\\S+;)', '&', 'g');
27 -- ... and TCN sources
28 -- FIXME we have here yet another (stub) version of entityize
29 norm_tcn_source := REGEXP_REPLACE(NEW.tcn_source, E'&(?!\\S+;)', '&', 'g');
31 NEW.marc := REGEXP_REPLACE(
33 E'(</(?:[^:]*?:)?record>)',
34 E'<datafield tag="901" ind1=" " ind2=" ">' ||
35 '<subfield code="a">' || norm_tcn_value || E'</subfield>' ||
36 '<subfield code="b">' || norm_tcn_source || E'</subfield>' ||
37 '<subfield code="c">' || NEW.id || E'</subfield>' ||
38 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
39 CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
40 CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
43 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
44 NEW.marc := REGEXP_REPLACE(
46 E'(</(?:[^:]*?:)?record>)',
47 E'<datafield tag="901" ind1=" " ind2=" ">' ||
48 '<subfield code="c">' || NEW.id || E'</subfield>' ||
49 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
52 ELSIF TG_TABLE_SCHEMA = 'serial' THEN
53 NEW.marc := REGEXP_REPLACE(
55 E'(</(?:[^:]*?:)?record>)',
56 E'<datafield tag="901" ind1=" " ind2=" ">' ||
57 '<subfield code="c">' || NEW.id || E'</subfield>' ||
58 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
59 '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
60 CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
64 NEW.marc := REGEXP_REPLACE(
66 E'(</(?:[^:]*?:)?record>)',
67 E'<datafield tag="901" ind1=" " ind2=" ">' ||
68 '<subfield code="c">' || NEW.id || E'</subfield>' ||
69 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
76 $func$ LANGUAGE PLPGSQL;
78 INSERT INTO config.upgrade_log (version) VALUES ('0509'); -- gmc
80 CREATE OR REPLACE FUNCTION evergreen.xml_escape(str TEXT) RETURNS text AS $$
81 SELECT REPLACE(REPLACE(REPLACE($1,
85 $$ LANGUAGE SQL IMMUTABLE;
87 CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$
89 use_id_for_tcn BOOLEAN;
91 -- Remove any existing 901 fields before we insert the authoritative one
92 NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield[^>]*?tag="901".+?</datafield>', '', 'g');
94 IF TG_TABLE_SCHEMA = 'biblio' THEN
95 -- Set TCN value to record ID?
96 SELECT enabled FROM config.global_flag INTO use_id_for_tcn
97 WHERE name = 'cat.bib.use_id_for_tcn';
99 IF use_id_for_tcn = 't' THEN
100 NEW.tcn_value := NEW.id;
103 NEW.marc := REGEXP_REPLACE(
105 E'(</(?:[^:]*?:)?record>)',
106 E'<datafield tag="901" ind1=" " ind2=" ">' ||
107 '<subfield code="a">' || evergreen.xml_escape(NEW.tcn_value) || E'</subfield>' ||
108 '<subfield code="b">' || evergreen.xml_escape(NEW.tcn_source) || E'</subfield>' ||
109 '<subfield code="c">' || NEW.id || E'</subfield>' ||
110 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
111 CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
112 CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
115 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
116 NEW.marc := REGEXP_REPLACE(
118 E'(</(?:[^:]*?:)?record>)',
119 E'<datafield tag="901" ind1=" " ind2=" ">' ||
120 '<subfield code="c">' || NEW.id || E'</subfield>' ||
121 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
124 ELSIF TG_TABLE_SCHEMA = 'serial' THEN
125 NEW.marc := REGEXP_REPLACE(
127 E'(</(?:[^:]*?:)?record>)',
128 E'<datafield tag="901" ind1=" " ind2=" ">' ||
129 '<subfield code="c">' || NEW.id || E'</subfield>' ||
130 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
131 '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
132 CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
136 NEW.marc := REGEXP_REPLACE(
138 E'(</(?:[^:]*?:)?record>)',
139 E'<datafield tag="901" ind1=" " ind2=" ">' ||
140 '<subfield code="c">' || NEW.id || E'</subfield>' ||
141 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
148 $func$ LANGUAGE PLPGSQL;
150 INSERT INTO config.upgrade_log (version) VALUES ('0510'); -- miker
152 SELECT evergreen.change_db_setting('search_path', ARRAY['evergreen','public','pg_catalog']);
154 -- Fix function breakage due to short search path
155 CREATE OR REPLACE FUNCTION evergreen.force_unicode_normal_form(string TEXT, form TEXT) RETURNS TEXT AS $func$
156 use Unicode::Normalize 'normalize';
157 return normalize($_[1],$_[0]); # reverse the params
158 $func$ LANGUAGE PLPERLU;
160 CREATE OR REPLACE FUNCTION evergreen.facet_force_nfc() RETURNS TRIGGER AS $$
162 NEW.value := force_unicode_normal_form(NEW.value,'NFC');
167 DROP TRIGGER facet_force_nfc_tgr ON metabib.facet_entry;
169 CREATE TRIGGER facet_force_nfc_tgr
170 BEFORE UPDATE OR INSERT ON metabib.facet_entry
171 FOR EACH ROW EXECUTE PROCEDURE evergreen.facet_force_nfc();
173 DROP FUNCTION IF EXISTS public.force_unicode_normal_form (TEXT,TEXT);
174 DROP FUNCTION IF EXISTS public.facet_force_nfc ();
176 CREATE OR REPLACE FUNCTION evergreen.xml_escape(str TEXT) RETURNS text AS $$
177 SELECT REPLACE(REPLACE(REPLACE($1,
181 $$ LANGUAGE SQL IMMUTABLE;
183 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
185 use_id_for_tcn BOOLEAN;
187 -- Remove any existing 901 fields before we insert the authoritative one
188 NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield[^>]*?tag="901".+?</datafield>', '', 'g');
190 IF TG_TABLE_SCHEMA = 'biblio' THEN
191 -- Set TCN value to record ID?
192 SELECT enabled FROM config.global_flag INTO use_id_for_tcn
193 WHERE name = 'cat.bib.use_id_for_tcn';
195 IF use_id_for_tcn = 't' THEN
196 NEW.tcn_value := NEW.id;
199 NEW.marc := REGEXP_REPLACE(
201 E'(</(?:[^:]*?:)?record>)',
202 E'<datafield tag="901" ind1=" " ind2=" ">' ||
203 '<subfield code="a">' || evergreen.xml_escape(NEW.tcn_value) || E'</subfield>' ||
204 '<subfield code="b">' || evergreen.xml_escape(NEW.tcn_source) || E'</subfield>' ||
205 '<subfield code="c">' || NEW.id || E'</subfield>' ||
206 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
207 CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
208 CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
211 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
212 NEW.marc := REGEXP_REPLACE(
214 E'(</(?:[^:]*?:)?record>)',
215 E'<datafield tag="901" ind1=" " ind2=" ">' ||
216 '<subfield code="c">' || NEW.id || E'</subfield>' ||
217 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
220 ELSIF TG_TABLE_SCHEMA = 'serial' THEN
221 NEW.marc := REGEXP_REPLACE(
223 E'(</(?:[^:]*?:)?record>)',
224 E'<datafield tag="901" ind1=" " ind2=" ">' ||
225 '<subfield code="c">' || NEW.id || E'</subfield>' ||
226 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
227 '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
228 CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
232 NEW.marc := REGEXP_REPLACE(
234 E'(</(?:[^:]*?:)?record>)',
235 E'<datafield tag="901" ind1=" " ind2=" ">' ||
236 '<subfield code="c">' || NEW.id || E'</subfield>' ||
237 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
244 $func$ LANGUAGE PLPGSQL;
246 DROP TRIGGER b_maintain_901 ON biblio.record_entry;
247 DROP TRIGGER b_maintain_901 ON authority.record_entry;
248 DROP TRIGGER b_maintain_901 ON serial.record_entry;
250 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
251 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
252 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
254 DROP FUNCTION IF EXISTS public.maintain_901 ();
256 INSERT INTO config.upgrade_log (version) VALUES ('0511'); -- miker
258 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
262 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
263 PERFORM * FROM asset.copy WHERE id = copy_id;
265 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
269 $F$ LANGUAGE PLPGSQL;
271 CREATE TRIGGER action_circulation_target_copy_trig AFTER INSERT OR UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy');
273 INSERT INTO config.upgrade_log (version) VALUES ('0516');
275 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
276 SELECT extract_marc_field('acq.lineitem', $1, $2, $3);
279 INSERT INTO config.upgrade_log (version) VALUES ('0517'); --miker
281 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
288 uri_owner_list TEXT[];
296 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
297 IF ARRAY_UPPER(uris,1) > 0 THEN
298 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
299 -- First we pull info out of the 856
302 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
303 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
304 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()|//*[@code="u"]/text()',uri_xml))[1];
305 CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL;
307 -- Get the distinct list of libraries wanting to use
309 DISTINCT REGEXP_REPLACE(
311 $re$^.*?\((\w+)\).*$$re$,
314 ) INTO uri_owner_list
317 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
322 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
324 -- look for a matching uri
325 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
326 IF NOT FOUND THEN -- create one
327 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
328 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
331 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
332 uri_owner := uri_owner_list[j];
334 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
335 CONTINUE WHEN NOT FOUND;
337 -- we need a call number to link through
338 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
340 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
341 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
342 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
345 -- now, link them if they're not already
346 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
348 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
360 $func$ LANGUAGE PLPGSQL;
362 INSERT INTO config.upgrade_log (version) VALUES ('0520'); --dbs
363 INSERT INTO config.upgrade_log (version) VALUES ('0521'); --dbs
365 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
372 uri_owner_list TEXT[];
380 -- Clear any URI mappings and call numbers for this bib.
381 -- This leads to acn / auricnm inflation, but also enables
382 -- old acn/auricnm's to go away and for bibs to be deleted.
383 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
384 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
385 DELETE FROM asset.call_number WHERE id = uri_cn_id;
388 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
389 IF ARRAY_UPPER(uris,1) > 0 THEN
390 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
391 -- First we pull info out of the 856
394 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
395 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
396 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
397 CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL;
399 -- Get the distinct list of libraries wanting to use
401 DISTINCT REGEXP_REPLACE(
403 $re$^.*?\((\w+)\).*$$re$,
406 ) INTO uri_owner_list
409 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
414 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
416 -- look for a matching uri
417 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
418 IF NOT FOUND THEN -- create one
419 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
420 IF uri_use IS NULL THEN
421 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
423 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
427 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
428 uri_owner := uri_owner_list[j];
430 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
431 CONTINUE WHEN NOT FOUND;
433 -- we need a call number to link through
434 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
436 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
437 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
438 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
441 -- now, link them if they're not already
442 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
444 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
456 $func$ LANGUAGE PLPGSQL;
458 INSERT INTO config.upgrade_log (version) VALUES ('0528'); -- dbs
460 CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
463 use MARC::File::XML (BinaryEncoding => 'UTF-8');
466 use Unicode::Normalize;
468 MARC::Charset->assume_unicode(1);
470 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
471 my $schema = $_TD->{table_schema};
472 my $rec_id = $_TD->{new}{id};
474 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
475 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
476 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
480 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
481 my $ou_cni = 'EVRGRN';
484 if ($schema eq 'serial') {
485 $owner = $_TD->{new}{owning_lib};
487 # are.owner and bre.owner can be null, so fall back to the consortial setting
488 $owner = $_TD->{new}{owner} || 1;
491 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
492 if ($ous_rv->{processed}) {
493 $ou_cni = $ous_rv->{rows}[0]->{value};
494 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
496 # Fall back to the shortname of the OU if there was no OU setting
497 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
498 if ($ous_rv->{processed}) {
499 $ou_cni = $ous_rv->{rows}[0]->{shortname};
503 my ($create, $munge) = (0, 0);
505 my @scns = $record->field('035');
507 foreach my $id_field ('001', '003') {
509 my @controls = $record->field($id_field);
511 if ($id_field eq '001') {
512 $spec_value = $rec_id;
514 $spec_value = $ou_cni;
517 # Create the 001/003 if none exist
518 if (scalar(@controls) == 1) {
519 # Only one field; check to see if we need to munge it
520 unless (grep $_->data() eq $spec_value, @controls) {
524 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
525 foreach my $control (@controls) {
526 unless ($control->data() eq $spec_value) {
527 $record->delete_field($control);
530 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
535 # Now, if we need to munge the 001, we will first push the existing 001/003
536 # into the 035; but if the record did not have one (and one only) 001 and 003
537 # to begin with, skip this process
538 if ($munge and not $create) {
539 my $scn = "(" . $record->field('003')->data() . ")" . $record->field('001')->data();
541 # Do not create duplicate 035 fields
542 unless (grep $_->subfield('a') eq $scn, @scns) {
543 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
547 # Set the 001/003 and update the MARC
548 if ($create or $munge) {
549 $record->field('001')->data($rec_id);
550 $record->field('003')->data($ou_cni);
552 my $xml = $record->as_xml_record();
554 $xml =~ s/^<\?xml.+\?\s*>//go;
555 $xml =~ s/>\s+</></go;
556 $xml =~ s/\p{Cc}//go;
558 # Embed a version of OpenILS::Application::AppUtils->entityize()
559 # to avoid having to set PERL5LIB for PostgreSQL as well
561 # If we are going to convert non-ASCII characters to XML entities,
562 # we had better be dealing with a UTF8 string to begin with
563 $xml = decode_utf8($xml);
567 # Convert raw ampersands to entities
568 $xml =~ s/&(?!\S+;)/&/gso;
570 # Convert Unicode characters to entities
571 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
573 $xml =~ s/[\x00-\x1f]//go;
574 $_TD->{new}{marc} = $xml;
580 $func$ LANGUAGE PLPERLU;
582 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$
585 use MARC::File::XML (BinaryEncoding => 'UTF-8');
588 MARC::Charset->assume_unicode(1);
591 my $r = MARC::Record->new_from_xml( $xml );
593 return undef unless ($r);
595 my $id = shift() || $r->subfield( '901' => 'c' );
596 $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/;
597 return undef unless ($id); # We need an ID!
599 my $tmpl = MARC::Record->new();
600 $tmpl->encoding( 'UTF-8' );
603 for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record
605 my $tag = $field->tag;
606 my $i1 = $field->indicator(1);
607 my $i2 = $field->indicator(2);
608 my $sf = join '', map { $_->[0] } $field->subfields;
609 my @data = map { @$_ } $field->subfields;
613 # Map the authority field to bib fields it can control.
614 if ($tag >= 100 and $tag <= 111) { # names
615 @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700);
616 } elsif ($tag eq '130') { # uniform title
617 @replace_them = qw/130 240 440 730 830/;
618 } elsif ($tag >= 150 and $tag <= 155) { # subjects
619 @replace_them = ($tag + 500);
620 } elsif ($tag >= 180 and $tag <= 185) { # floating subdivisions
621 @replace_them = qw/100 400 600 700 800 110 410 610 710 810 111 411 611 711 811 130 240 440 730 830 650 651 655/;
626 # Dummy up the bib-side data
627 $tmpl->append_fields(
629 MARC::Field->new( $_, $i1, $i2, @data )
633 # Construct some 'replace' rules
634 push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them;
637 # Insert the replace rules into the template
638 $tmpl->append_fields(
639 MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) )
642 $xml = $tmpl->as_xml_record;
643 $xml =~ s/^<\?.+?\?>$//mo;
645 $xml =~ s/>\s+</></sgo;
649 $func$ LANGUAGE PLPERLU;
651 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
654 use MARC::File::XML (BinaryEncoding => 'UTF-8');
658 MARC::Charset->assume_unicode(1);
660 my $target_xml = shift;
661 my $source_xml = shift;
662 my $field_spec = shift;
663 my $force_add = shift || 0;
665 my $target_r = MARC::Record->new_from_xml( $target_xml );
666 my $source_r = MARC::Record->new_from_xml( $source_xml );
668 return $target_xml unless ($target_r && $source_r);
670 my @field_list = split(',', $field_spec);
673 for my $f (@field_list) {
674 $f =~ s/^\s*//; $f =~ s/\s*$//;
675 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
681 $match =~ s/^\s*//; $match =~ s/\s*$//;
682 $fields{$field} = { sf => [ split('', $sf) ] };
684 my ($msf,$mre) = split('~', $match);
685 if (length($msf) > 0 and length($mre) > 0) {
686 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
687 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
688 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
694 for my $f ( keys %fields) {
695 if ( @{$fields{$f}{sf}} ) {
696 for my $from_field ($source_r->field( $f )) {
697 my @tos = $target_r->field( $f );
699 next if (exists($fields{$f}{match}) and !$force_add);
700 my @new_fields = map { $_->clone } $source_r->field( $f );
701 $target_r->insert_fields_ordered( @new_fields );
703 for my $to_field (@tos) {
704 if (exists($fields{$f}{match})) {
705 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
707 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
708 $to_field->add_subfields( @new_sf );
713 my @new_fields = map { $_->clone } $source_r->field( $f );
714 $target_r->insert_fields_ordered( @new_fields );
718 $target_xml = $target_r->as_xml_record;
719 $target_xml =~ s/^<\?.+?\?>$//mo;
720 $target_xml =~ s/\n//sgo;
721 $target_xml =~ s/>\s+</></sgo;
725 $_$ LANGUAGE PLPERLU;
727 CREATE OR REPLACE FUNCTION authority.normalize_heading( TEXT ) RETURNS TEXT AS $func$
733 use MARC::File::XML (BinaryEncoding => 'UTF8');
735 use UUID::Tiny ':std';
737 MARC::Charset->assume_unicode(1);
739 my $xml = shift() or return undef;
743 # Prevent errors in XML parsing from blowing out ungracefully
745 $r = MARC::Record->new_from_xml( $xml );
748 return 'BAD_MARCXML_' . create_uuid_as_string(UUID_MD5, $xml);
752 return 'BAD_MARCXML_' . create_uuid_as_string(UUID_MD5, $xml);
755 # From http://www.loc.gov/standards/sourcelist/subject.html
756 my $thes_code_map = {
762 n => 'notapplicable',
768 # Default to "No attempt to code" if the leader is horribly broken
769 my $fixed_field = $r->field('008');
772 $thes_char = substr($fixed_field->data(), 11, 1) || '|';
775 my $thes_code = 'UNDEFINED';
777 if ($thes_char eq 'z') {
778 # Grab the 040 $f per http://www.loc.gov/marc/authority/ad040.html
779 $thes_code = $r->subfield('040', 'f') || 'UNDEFINED';
780 } elsif ($thes_code_map->{$thes_char}) {
781 $thes_code = $thes_code_map->{$thes_char};
785 my $head = $r->field('1..');
787 # Concatenate all of these subfields together, prefixed by their code
788 # to prevent collisions along the lines of "Fiction, North Carolina"
789 foreach my $sf ($head->subfields()) {
790 $auth_txt .= '‡' . $sf->[0] . ' ' . $sf->[1];
795 my $stmt = spi_prepare('SELECT public.naco_normalize($1) AS norm_text', 'TEXT');
796 my $result = spi_exec_prepared($stmt, $auth_txt);
797 my $norm_txt = $result->{rows}[0]->{norm_text};
800 return $head->tag() . "_" . $thes_code . " " . $norm_txt;
803 return 'NOHEADING_' . $thes_code . ' ' . create_uuid_as_string(UUID_MD5, $xml);
804 $func$ LANGUAGE 'plperlu' IMMUTABLE;
806 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
809 use MARC::File::XML (BinaryEncoding => 'UTF-8');
813 MARC::Charset->assume_unicode(1);
816 my $r = MARC::Record->new_from_xml( $xml );
818 return $xml unless ($r);
820 my $field_spec = shift;
821 my @field_list = split(',', $field_spec);
824 for my $f (@field_list) {
825 $f =~ s/^\s*//; $f =~ s/\s*$//;
826 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
832 $match =~ s/^\s*//; $match =~ s/\s*$//;
833 $fields{$field} = { sf => [ split('', $sf) ] };
835 my ($msf,$mre) = split('~', $match);
836 if (length($msf) > 0 and length($mre) > 0) {
837 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
838 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
839 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
845 for my $f ( keys %fields) {
846 for my $to_field ($r->field( $f )) {
847 if (exists($fields{$f}{match})) {
848 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
851 if ( @{$fields{$f}{sf}} ) {
852 $to_field->delete_subfield(code => $fields{$f}{sf});
854 $r->delete_field( $to_field );
859 $xml = $r->as_xml_record;
860 $xml =~ s/^<\?.+?\?>$//mo;
862 $xml =~ s/>\s+</></sgo;
866 $_$ LANGUAGE PLPERLU;
868 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( TEXT ) RETURNS SETOF metabib.full_rec AS $func$
871 use MARC::File::XML (BinaryEncoding => 'UTF-8');
874 MARC::Charset->assume_unicode(1);
877 my $r = MARC::Record->new_from_xml( $xml );
879 return_next( { tag => 'LDR', value => $r->leader } );
881 for my $f ( $r->fields ) {
882 if ($f->is_control_field) {
883 return_next({ tag => $f->tag, value => $f->data });
885 for my $s ($f->subfields) {
888 ind1 => $f->indicator(1),
889 ind2 => $f->indicator(2),
894 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
895 my $trim = $f->indicator(2) || 0;
898 ind1 => $f->indicator(1),
899 ind2 => $f->indicator(2),
901 value => substr( $s->[1], $trim )
910 $func$ LANGUAGE PLPERLU;
912 CREATE OR REPLACE FUNCTION authority.flatten_marc ( TEXT ) RETURNS SETOF authority.full_rec AS $func$
915 use MARC::File::XML (BinaryEncoding => 'UTF-8');
918 MARC::Charset->assume_unicode(1);
921 my $r = MARC::Record->new_from_xml( $xml );
923 return_next( { tag => 'LDR', value => $r->leader } );
925 for my $f ( $r->fields ) {
926 if ($f->is_control_field) {
927 return_next({ tag => $f->tag, value => $f->data });
929 for my $s ($f->subfields) {
932 ind1 => $f->indicator(1),
933 ind2 => $f->indicator(2),
944 $func$ LANGUAGE PLPERLU;
946 INSERT INTO config.upgrade_log (version) VALUES ('0529');
948 INSERT INTO config.org_unit_setting_type
949 ( name, label, description, datatype ) VALUES
950 ( 'circ.user_merge.delete_addresses',
951 'Circ: Patron Merge Address Delete',
952 'Delete address(es) of subordinate user(s) in a patron merge',
956 INSERT INTO config.org_unit_setting_type
957 ( name, label, description, datatype ) VALUES
958 ( 'circ.user_merge.delete_cards',
959 'Circ: Patron Merge Barcode Delete',
960 'Delete barcode(s) of subordinate user(s) in a patron merge',
964 INSERT INTO config.org_unit_setting_type
965 ( name, label, description, datatype ) VALUES
966 ( 'circ.user_merge.deactivate_cards',
967 'Circ: Patron Merge Deactivate Card',
968 'Mark barcode(s) of subordinate user(s) in a patron merge as inactive',