From 277c48b5f318f380f19c848040a91834c883637d Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 29 Sep 2017 11:52:18 -0400 Subject: [PATCH] JBAS-1470 Auth propatation supports partial matches Avoid deleting controlled subfields within a controlled bib field when the linked authority record record field has no values for the subfields in question. Signed-off-by: Bill Erickson --- .../schema/deploy/auth-prop-partial-matches.sql | 125 +++++++++++++++++++++ .../schema/revert/auth-prop-partial-matches.sql | 96 ++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + .../schema/verify/auth-prop-partial-matches.sql | 7 ++ 4 files changed, 229 insertions(+) create mode 100644 KCLS/sql/schema/deploy/auth-prop-partial-matches.sql create mode 100644 KCLS/sql/schema/revert/auth-prop-partial-matches.sql create mode 100644 KCLS/sql/schema/verify/auth-prop-partial-matches.sql diff --git a/KCLS/sql/schema/deploy/auth-prop-partial-matches.sql b/KCLS/sql/schema/deploy/auth-prop-partial-matches.sql new file mode 100644 index 0000000000..e386dfd815 --- /dev/null +++ b/KCLS/sql/schema/deploy/auth-prop-partial-matches.sql @@ -0,0 +1,125 @@ +-- Deploy kcls-evergreen:auth-prop-partial-matches to pg +-- requires: 2.9-to-2.10-upgrade-reingest + +BEGIN; + +CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$ +DECLARE + cset INT; + main_entry authority.control_set_authority_field%ROWTYPE; + bib_field authority.control_set_bib_field%ROWTYPE; + auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT; + tmp_data XML; + replace_data XML[] DEFAULT '{}'::XML[]; + replace_rules TEXT[] DEFAULT '{}'::TEXT[]; + replace_subfields TEXT; + auth_field XML[]; + auth_i1 TEXT; + auth_i2 TEXT; +BEGIN + IF auth_id IS NULL THEN + RETURN NULL; + END IF; + + -- Default to the LoC controll set + SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id; + + -- if none, make a best guess + IF cset IS NULL THEN + SELECT control_set INTO cset + FROM authority.control_set_authority_field + WHERE tag IN ( + SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[]) + FROM authority.record_entry + WHERE id = auth_id + ) + LIMIT 1; + END IF; + + -- if STILL none, no-op change + IF cset IS NULL THEN + RETURN XMLELEMENT( + name record, + XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns), + XMLELEMENT( name leader, '00881nam a2200193 4500'), + XMLELEMENT( + name datafield, + XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2), + XMLELEMENT( + name subfield, + XMLATTRIBUTES('d' AS code), + '901c' + ) + ) + )::TEXT; + END IF; + + FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP + auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML); + auth_i1 = (XPATH('@ind1',auth_field[1]))[1]; + auth_i2 = (XPATH('@ind2',auth_field[1]))[1]; + IF ARRAY_LENGTH(auth_field,1) > 0 THEN + FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP + + /* KCLS JBAS-1470 + Determine which subfields in the controlling + authority field have values applied. This + represents the list of subfields to replace in the + controlled MARC bib field. This is different from + stock EG where the lack of a controlled subfield + value in the authority field means the same subfield + in the controlled bib field will be removed. + */ + SELECT INTO replace_subfields + ARRAY_TO_STRING(ARRAY_AGG(codes.code), '') + FROM ( + SELECT UNNEST( + XPATH('//*[local-name()="subfield"]/@code', auth_field[1]) + ) as code + ) codes; + + /* + RAISE NOTICE 'auth_tag=% bib_tag=% replace_fields=%', + main_entry.tag, bib_field.tag, replace_subfields; + */ + + SELECT XMLELEMENT( -- XMLAGG avoids magical creation, but requires unnest subquery + name datafield, + XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2), + XMLAGG(UNNEST) + ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1])); + replace_data := replace_data || tmp_data; + replace_rules := replace_rules || ( + bib_field.tag || replace_subfields || E'[0~\\)' || auth_id || '$]' + ); + -- STOCK + -- replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' ); + tmp_data = NULL; + END LOOP; + EXIT; + END IF; + END LOOP; + + SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data); + + RETURN XMLELEMENT( + name record, + XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns), + XMLELEMENT( name leader, '00881nam a2200193 4500'), + tmp_data, + XMLELEMENT( + name datafield, + XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2), + XMLELEMENT( + name subfield, + XMLATTRIBUTES('r' AS code), + ARRAY_TO_STRING(replace_rules,',') + ) + ) + )::TEXT; +END; +$f$ STABLE LANGUAGE PLPGSQL; + + + +COMMIT; diff --git a/KCLS/sql/schema/revert/auth-prop-partial-matches.sql b/KCLS/sql/schema/revert/auth-prop-partial-matches.sql new file mode 100644 index 0000000000..817967b609 --- /dev/null +++ b/KCLS/sql/schema/revert/auth-prop-partial-matches.sql @@ -0,0 +1,96 @@ +-- Revert kcls-evergreen:auth-prop-partial-matches from pg + +BEGIN; + +-- Function to generate an ephemeral overlay template from an authority record +CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$ +DECLARE + cset INT; + main_entry authority.control_set_authority_field%ROWTYPE; + bib_field authority.control_set_bib_field%ROWTYPE; + auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT; + tmp_data XML; + replace_data XML[] DEFAULT '{}'::XML[]; + replace_rules TEXT[] DEFAULT '{}'::TEXT[]; + auth_field XML[]; + auth_i1 TEXT; + auth_i2 TEXT; +BEGIN + IF auth_id IS NULL THEN + RETURN NULL; + END IF; + + -- Default to the LoC controll set + SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id; + + -- if none, make a best guess + IF cset IS NULL THEN + SELECT control_set INTO cset + FROM authority.control_set_authority_field + WHERE tag IN ( + SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[]) + FROM authority.record_entry + WHERE id = auth_id + ) + LIMIT 1; + END IF; + + -- if STILL none, no-op change + IF cset IS NULL THEN + RETURN XMLELEMENT( + name record, + XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns), + XMLELEMENT( name leader, '00881nam a2200193 4500'), + XMLELEMENT( + name datafield, + XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2), + XMLELEMENT( + name subfield, + XMLATTRIBUTES('d' AS code), + '901c' + ) + ) + )::TEXT; + END IF; + + FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP + auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML); + auth_i1 = (XPATH('@ind1',auth_field[1]))[1]; + auth_i2 = (XPATH('@ind2',auth_field[1]))[1]; + IF ARRAY_LENGTH(auth_field,1) > 0 THEN + FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP + SELECT XMLELEMENT( -- XMLAGG avoids magical creation, but requires unnest subquery + name datafield, + XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2), + XMLAGG(UNNEST) + ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1])); + replace_data := replace_data || tmp_data; + replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' ); + tmp_data = NULL; + END LOOP; + EXIT; + END IF; + END LOOP; + + SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data); + + RETURN XMLELEMENT( + name record, + XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns), + XMLELEMENT( name leader, '00881nam a2200193 4500'), + tmp_data, + XMLELEMENT( + name datafield, + XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2), + XMLELEMENT( + name subfield, + XMLATTRIBUTES('r' AS code), + ARRAY_TO_STRING(replace_rules,',') + ) + ) + )::TEXT; +END; +$f$ STABLE LANGUAGE PLPGSQL; + + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 07213670d5..da71759cd1 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -56,3 +56,4 @@ selfcheck-email-receipts [2.7-to-2.9-upgrade-part-2] 2017-04-21T19:02:22Z Victor checkout-ok-2.10-recovery [2.9-to-2.10-upgrade] 2017-12-20T20:32:10Z Bill Erickson,,, # Recover lost checkout_ok circ test check new-headings-mattype [2.9-to-2.10-upgrade] 2017-11-30T15:41:38Z Bill Erickson,,, # Add mattype filter for new headings report browse-mattype-filter [2.9-to-2.10-upgrade] 2017-11-28T20:39:59Z Bill Erickson,,, # Add mattype filter for browse search +auth-prop-partial-matches [2.9-to-2.10-upgrade-reingest] 2017-09-29T15:47:04Z Bill Erickson,,, # Authority propagation partial matches diff --git a/KCLS/sql/schema/verify/auth-prop-partial-matches.sql b/KCLS/sql/schema/verify/auth-prop-partial-matches.sql new file mode 100644 index 0000000000..c22d24f723 --- /dev/null +++ b/KCLS/sql/schema/verify/auth-prop-partial-matches.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:auth-prop-partial-matches on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0