From 972fdaeed65ba8abbba670692dd8511855a5e0da Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Fri, 11 Jan 2013 08:49:11 -0500 Subject: [PATCH] Upgrade script for authority.generate_overlay_template fix. Signed-off-by: Jason Stephenson --- ...unction.authority.generate_overlay_template.sql | 86 ++++++++++++++++++++++ 1 file changed, 86 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.authority.generate_overlay_template.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.authority.generate_overlay_template.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.authority.generate_overlay_template.sql new file mode 100644 index 0000000000..b1da5e02bf --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.authority.generate_overlay_template.sql @@ -0,0 +1,86 @@ +-- Only consider main entry headings for bib overlay + +BEGIN; + +-- check whether patch can be applied +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + + +-- 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; + replace_data XML[] DEFAULT '{}'::XML[]; + replace_rules TEXT[] DEFAULT '{}'::TEXT[]; + auth_field XML[]; +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 WHERE control_set = cset AND authority.control_set_authority_field.main_entry IS NULL LOOP + auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML); + 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 + replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]); + replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' ); + END LOOP; + EXIT; + END IF; + END LOOP; + + RETURN XMLELEMENT( + name record, + XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns), + XMLELEMENT( name leader, '00881nam a2200193 4500'), + replace_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; -- 2.11.0