1 -- Evergreen DB patch 0612.schema.authority_overlay_protection.sql
6 -- check whether patch can be applied
7 SELECT evergreen.upgrade_deps_block_check('0612', :eg_version);
9 -- FIXME: add/check SQL statements to perform the upgrade
11 -- Function to generate an ephemeral overlay template from an authority record
12 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
15 main_entry authority.control_set_authority_field%ROWTYPE;
16 bib_field authority.control_set_bib_field%ROWTYPE;
17 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
18 replace_data XML[] DEFAULT '{}'::XML[];
19 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
22 IF auth_id IS NULL THEN
26 -- Default to the LoC controll set
27 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
29 -- if none, make a best guess
31 SELECT control_set INTO cset
32 FROM authority.control_set_authority_field
34 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
35 FROM authority.record_entry
41 -- if STILL none, no-op change
45 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
46 XMLELEMENT( name leader, '00881nam a2200193 4500'),
49 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
52 XMLATTRIBUTES('d' AS code),
59 FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
60 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
61 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
62 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
63 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
64 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
72 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
73 XMLELEMENT( name leader, '00881nam a2200193 4500'),
77 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
80 XMLATTRIBUTES('r' AS code),
81 ARRAY_TO_STRING(replace_rules,',')
86 $f$ STABLE LANGUAGE PLPGSQL;