-- Evergreen DB patch 0612.schema.authority_overlay_protection.sql -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0612', :eg_version); -- FIXME: add/check SQL statements to perform the upgrade -- 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 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;