From 2ade97550d6a10e993e2575e55d78fd8efb22c97 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 30 Nov 2016 09:49:10 -0500 Subject: [PATCH 1/1] LP#1117808: new Pg function to calculate MARC record merges This patch adds a new stored function, vandelay.merge_record_xml_using_profile(), that when passed two MARCXML strings and the ID of a merge profile. returns the MARCXML string representing the results of the record merge. This is meant for situations where we want to know the results of a merge, but not immediately overlay a record in the database. pgTAP tests are available in t/marc_merge.pg. Signed-off-by: Galen Charlton Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 54 ++++++++ Open-ILS/src/sql/Pg/t/marc_merge.pg | 128 ++++++++++++++++++ .../XXXX.schema.more_overlay_funcs.sql | 59 ++++++++ 3 files changed, 241 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/t/marc_merge.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.more_overlay_funcs.sql diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index ff474cd858..d60e01baea 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -1452,6 +1452,60 @@ BEGIN END; $$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION vandelay.merge_record_xml_using_profile ( incoming_marc TEXT, existing_marc TEXT, merge_profile_id BIGINT ) RETURNS TEXT AS $$ +DECLARE + merge_profile vandelay.merge_profile%ROWTYPE; + dyn_profile vandelay.compile_profile%ROWTYPE; + target_marc TEXT; + source_marc TEXT; + replace_rule TEXT; + match_count INT; +BEGIN + + IF existing_marc IS NULL OR incoming_marc IS NULL THEN + -- RAISE NOTICE 'no marc for source or target records'; + RETURN NULL; + END IF; + + 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 := COALESCE(merge_profile.add_spec,''); + dyn_profile.strip_rule := COALESCE(merge_profile.strip_spec,''); + dyn_profile.replace_rule := COALESCE(merge_profile.replace_spec,''); + dyn_profile.preserve_rule := COALESCE(merge_profile.preserve_spec,''); + ELSE + -- RAISE NOTICE 'merge profile not found'; + RETURN NULL; + END IF; + ELSE + -- RAISE NOTICE 'no merge profile specified'; + RETURN NULL; + 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 NULL; + 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 target record as is + RETURN existing_marc; + ELSIF dyn_profile.preserve_rule <> '' THEN + source_marc = existing_marc; + target_marc = incoming_marc; + replace_rule = dyn_profile.preserve_rule; + ELSE + source_marc = incoming_marc; + target_marc = existing_marc; + replace_rule = dyn_profile.replace_rule; + END IF; + + RETURN vandelay.merge_record_xml( target_marc, source_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) RETURNS BOOL AS $$ SELECT vandelay.template_overlay_bib_record( $1, $2, NULL); $$ LANGUAGE SQL; diff --git a/Open-ILS/src/sql/Pg/t/marc_merge.pg b/Open-ILS/src/sql/Pg/t/marc_merge.pg new file mode 100644 index 0000000000..e790f7a67f --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/marc_merge.pg @@ -0,0 +1,128 @@ +BEGIN; + +SELECT plan(4); + +CREATE OR REPLACE FUNCTION test_marc_merges() RETURNS SETOF TEXT AS $func$ +DECLARE + incoming_marc TEXT; + existing_marc TEXT; + merge_profile_1 BIGINT; + merge_profile_2 BIGINT; + merge_profile_3 BIGINT; + merge_profile_4 BIGINT; +BEGIN + incoming_marc := $$ + 00531nam a2200157 a 4500 + 20080729170300.0 + t19981999enka 0 eng + + merge-example-incoming + + + contents-notes-incoming + + + https://example.org/page-incoming + +$$; + existing_marc := $$ + 00531nam a2200157 a 4500 + 20080729170300.0 + t19981999enka 0 eng + + merge-example-existing + + + contents-notes-existing + + + https://example.org/page-existing + +$$; + + INSERT INTO vandelay.merge_profile (owner, name, add_spec, replace_spec, strip_spec, preserve_spec) + VALUES (1, 'Test MP 1', '505', NULL, NULL, NULL); + SELECT CURRVAL('vandelay.merge_profile_id_seq') INTO merge_profile_1; + INSERT INTO vandelay.merge_profile (owner, name, add_spec, replace_spec, strip_spec, preserve_spec) + VALUES (1, 'Test MP 2', NULL, '505', NULL, NULL); + SELECT CURRVAL('vandelay.merge_profile_id_seq') INTO merge_profile_2; + INSERT INTO vandelay.merge_profile (owner, name, add_spec, replace_spec, strip_spec, preserve_spec) + VALUES (1, 'Test MP 3', NULL, NULL, NULL, '505'); + SELECT CURRVAL('vandelay.merge_profile_id_seq') INTO merge_profile_3; + INSERT INTO vandelay.merge_profile (owner, name, add_spec, replace_spec, strip_spec, preserve_spec) + VALUES (1, 'Test MP 4', '856', '505', NULL, NULL); + SELECT CURRVAL('vandelay.merge_profile_id_seq') INTO merge_profile_4; + + -- using evergreen.xml_pretty_print(textval::XML)::TEXT to normalize the + -- values we're comparing + RETURN NEXT is(evergreen.xml_pretty_print(vandelay.merge_record_xml_using_profile(incoming_marc, existing_marc, merge_profile_1)::XML)::TEXT, evergreen.xml_pretty_print($$ + 00531nam a2200157 a 4500 + 20080729170300.0 + t19981999enka 0 eng + + merge-example-existing + + + contents-notes-incoming + + + contents-notes-existing + + + https://example.org/page-existing + +$$::XML)::TEXT, 'merge with add 505 rule'); + + RETURN NEXT is(evergreen.xml_pretty_print(vandelay.merge_record_xml_using_profile(incoming_marc, existing_marc, merge_profile_2)::XML)::TEXT, evergreen.xml_pretty_print($$ + 00531nam a2200157 a 4500 + 20080729170300.0 + t19981999enka 0 eng + + merge-example-existing + + + contents-notes-incoming + + + https://example.org/page-existing + +$$::XML)::TEXT, 'merge with replace 505 rule'); + + RETURN NEXT is(evergreen.xml_pretty_print(vandelay.merge_record_xml_using_profile(incoming_marc, existing_marc, merge_profile_3)::XML)::TEXT, evergreen.xml_pretty_print($$ + 00531nam a2200157 a 4500 + 20080729170300.0 + t19981999enka 0 eng + + merge-example-incoming + + + contents-notes-existing + + + https://example.org/page-incoming + +$$::XML)::TEXT, 'merge with preserve 505 rule'); + + RETURN NEXT is(evergreen.xml_pretty_print(vandelay.merge_record_xml_using_profile(incoming_marc, existing_marc, merge_profile_4)::XML)::TEXT, evergreen.xml_pretty_print($$ + 00531nam a2200157 a 4500 + 20080729170300.0 + t19981999enka 0 eng + + merge-example-existing + + + contents-notes-incoming + + + https://example.org/page-incoming + + + https://example.org/page-existing + +$$::XML)::TEXT, 'merge with add 856 and replace 505 rule'); +END; +$func$ LANGUAGE plpgsql; + +SELECT * FROM test_marc_merges(); + +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.more_overlay_funcs.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.more_overlay_funcs.sql new file mode 100644 index 0000000000..e3bfa2e04a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.more_overlay_funcs.sql @@ -0,0 +1,59 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION vandelay.merge_record_xml_using_profile ( incoming_marc TEXT, existing_marc TEXT, merge_profile_id BIGINT ) RETURNS TEXT AS $$ +DECLARE + merge_profile vandelay.merge_profile%ROWTYPE; + dyn_profile vandelay.compile_profile%ROWTYPE; + target_marc TEXT; + source_marc TEXT; + replace_rule TEXT; + match_count INT; +BEGIN + + IF existing_marc IS NULL OR incoming_marc IS NULL THEN + -- RAISE NOTICE 'no marc for source or target records'; + RETURN NULL; + END IF; + + 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 := COALESCE(merge_profile.add_spec,''); + dyn_profile.strip_rule := COALESCE(merge_profile.strip_spec,''); + dyn_profile.replace_rule := COALESCE(merge_profile.replace_spec,''); + dyn_profile.preserve_rule := COALESCE(merge_profile.preserve_spec,''); + ELSE + -- RAISE NOTICE 'merge profile not found'; + RETURN NULL; + END IF; + ELSE + -- RAISE NOTICE 'no merge profile specified'; + RETURN NULL; + 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 NULL; + 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 target record as is + RETURN existing_marc; + ELSIF dyn_profile.preserve_rule <> '' THEN + source_marc = existing_marc; + target_marc = incoming_marc; + replace_rule = dyn_profile.preserve_rule; + ELSE + source_marc = incoming_marc; + target_marc = existing_marc; + replace_rule = dyn_profile.replace_rule; + END IF; + + RETURN vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule ); + +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2