From 2fee9f9693b382c999a62be75604d0bc2752fe0e Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Fri, 11 Jan 2013 08:49:11 -0500 Subject: [PATCH] Fix a data destruction bug when authorities are updated. Modify the two-argument form of vandelay.merge_record_xml to return the target_marc when the add_rule, preserve_rule, replace_rule and strip_rule are all empty. This method is used by authority.propagate_changes and would replace the bibliographic record's marc with the overlay template when the above would happen. By returning the biblographic record entry's own marc in that case, we prevent data destruction. Additionally, protect bib and authority template overlay functions in the same manner. Commit includes an upgrade script for this change as well as for the previous commit's authority.generate_overlay_template fix. Signed-off-by: Jason Stephenson Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 15 +- ...on.authority.generate_overlay_template.sql | 275 ++++++++++++++++++ 3 files changed, 288 insertions(+), 4 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0750.function.authority.generate_overlay_template.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index db3e77e3dc..f8128a66a1 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -87,7 +87,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0749', :eg_version); -- dbs/bshum +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0750', :eg_version); -- miker/jstephenson CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 8f9ad968b2..b7f79d08b1 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -1268,7 +1268,10 @@ BEGIN RETURN FALSE; END IF; - IF dyn_profile.replace_rule <> '' THEN + IF dyn_profile.replace_rule = '' AND dyn_profile.preserve_rule = '' AND dyn_profile.add_rule = '' AND dyn_profile.strip_rule = '' THEN + --Since we have nothing to do, just return a NOOP "we did it" + RETURN TRUE; + ELSIF dyn_profile.replace_rule <> '' THEN source_marc = v_marc; target_marc = eg_marc; replace_rule = dyn_profile.replace_rule; @@ -1314,7 +1317,10 @@ BEGIN RETURN NULL; END IF; - IF dyn_profile.replace_rule <> '' THEN + IF dyn_profile.replace_rule = '' AND dyn_profile.preserve_rule = '' AND dyn_profile.add_rule = '' AND dyn_profile.strip_rule = '' THEN + --Since we have nothing to do, just return what we were given. + RETURN target_marc; + ELSIF dyn_profile.replace_rule <> '' THEN trgt_marc = target_marc; tmpl_marc = template_marc; replace_rule = dyn_profile.replace_rule; @@ -1689,7 +1695,10 @@ BEGIN RETURN FALSE; END IF; - IF dyn_profile.replace_rule <> '' THEN + IF dyn_profile.replace_rule = '' AND dyn_profile.preserve_rule = '' AND dyn_profile.add_rule = '' AND dyn_profile.strip_rule = '' THEN + --Since we have nothing to do, just return a NOOP "we did it" + RETURN TRUE; + ELSIF dyn_profile.replace_rule <> '' THEN source_marc = v_marc; target_marc = eg_marc; replace_rule = dyn_profile.replace_rule; diff --git a/Open-ILS/src/sql/Pg/upgrade/0750.function.authority.generate_overlay_template.sql b/Open-ILS/src/sql/Pg/upgrade/0750.function.authority.generate_overlay_template.sql new file mode 100644 index 0000000000..178e8ed555 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0750.function.authority.generate_overlay_template.sql @@ -0,0 +1,275 @@ +-- Only consider main entry headings for bib overlay + +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0750', :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 acsaf WHERE acsaf.control_set = cset AND acsaf.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; + +-- Change the two argument form of vandelay.merge_record_xml to +-- prevent bibliographic record destruction when there is nothing to +-- do. + + +CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$ +DECLARE + dyn_profile vandelay.compile_profile%ROWTYPE; + replace_rule TEXT; + tmp_marc TEXT; + trgt_marc TEXT; + tmpl_marc TEXT; + match_count INT; +BEGIN + + IF target_marc IS NULL OR template_marc IS NULL THEN + -- RAISE NOTICE 'no marc for target or template record'; + RETURN NULL; + END IF; + + dyn_profile := vandelay.compile_profile( template_marc ); + + IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN + -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule; + RETURN NULL; + END IF; + + IF dyn_profile.replace_rule = '' AND dyn_profile.preserve_rule = '' AND dyn_profile.add_rule = '' THEN + --Since we have nothing to do, just return what we were given. + RETURN target_marc; + ELSIF dyn_profile.replace_rule <> '' THEN + trgt_marc = target_marc; + tmpl_marc = template_marc; + replace_rule = dyn_profile.replace_rule; + ELSE + tmp_marc = target_marc; + trgt_marc = template_marc; + tmpl_marc = tmp_marc; + replace_rule = dyn_profile.preserve_rule; + END IF; + + RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule ); + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$ +DECLARE + merge_profile vandelay.merge_profile%ROWTYPE; + dyn_profile vandelay.compile_profile%ROWTYPE; + editor_string TEXT; + editor_id INT; + source_marc TEXT; + target_marc TEXT; + eg_marc TEXT; + replace_rule TEXT; + match_count INT; +BEGIN + + SELECT b.marc INTO eg_marc + FROM biblio.record_entry b + WHERE b.id = eg_id + LIMIT 1; + + IF eg_marc IS NULL OR v_marc IS NULL THEN + -- RAISE NOTICE 'no marc for template or bib record'; + RETURN FALSE; + END IF; + + dyn_profile := vandelay.compile_profile( v_marc ); + + IF merge_profile_id IS NOT NULL THEN + SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id; + IF FOUND THEN + dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ','); + dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ','); + dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ','); + dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ','); + END IF; + END IF; + + IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN + -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule; + RETURN FALSE; + END IF; + + IF dyn_profile.replace_rule = '' AND dyn_profile.preserve_rule = '' AND dyn_profile.add_rule = '' AND dyn_profile.strip_rule = '' THEN + --Since we have nothing to do, just return a NOOP "we did it" + RETURN TRUE; + ELSIF dyn_profile.replace_rule <> '' THEN + source_marc = v_marc; + target_marc = eg_marc; + replace_rule = dyn_profile.replace_rule; + ELSE + source_marc = eg_marc; + target_marc = v_marc; + replace_rule = dyn_profile.preserve_rule; + END IF; + + UPDATE biblio.record_entry + SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule ) + WHERE id = eg_id; + + IF NOT FOUND THEN + -- RAISE NOTICE 'update of biblio.record_entry failed'; + RETURN FALSE; + END IF; + + RETURN TRUE; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$ +DECLARE + merge_profile vandelay.merge_profile%ROWTYPE; + dyn_profile vandelay.compile_profile%ROWTYPE; + source_marc TEXT; + target_marc TEXT; + eg_marc TEXT; + v_marc TEXT; + replace_rule TEXT; + match_count INT; +BEGIN + + SELECT b.marc INTO eg_marc + FROM authority.record_entry b + JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id) + LIMIT 1; + + SELECT q.marc INTO v_marc + FROM vandelay.queued_record q + JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id) + LIMIT 1; + + IF eg_marc IS NULL OR v_marc IS NULL THEN + -- RAISE NOTICE 'no marc for vandelay or authority record'; + RETURN FALSE; + END IF; + + dyn_profile := vandelay.compile_profile( v_marc ); + + IF merge_profile_id IS NOT NULL THEN + SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id; + IF FOUND THEN + dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ','); + dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ','); + dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ','); + dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ','); + END IF; + END IF; + + IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN + -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule; + RETURN FALSE; + END IF; + + IF dyn_profile.replace_rule = '' AND dyn_profile.preserve_rule = '' AND dyn_profile.add_rule = '' AND dyn_profile.strip_rule = '' THEN + --Since we have nothing to do, just return a NOOP "we did it" + RETURN TRUE; + ELSIF dyn_profile.replace_rule <> '' THEN + source_marc = v_marc; + target_marc = eg_marc; + replace_rule = dyn_profile.replace_rule; + ELSE + source_marc = eg_marc; + target_marc = v_marc; + replace_rule = dyn_profile.preserve_rule; + END IF; + + UPDATE authority.record_entry + SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule ) + WHERE id = eg_id; + + IF FOUND THEN + UPDATE vandelay.queued_authority_record + SET imported_as = eg_id, + import_time = NOW() + WHERE id = import_id; + RETURN TRUE; + END IF; + + -- RAISE NOTICE 'update of authority.record_entry failed'; + + RETURN FALSE; + +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2