From a74dc950673bb6b8a5c764afccedef58d1e428d9 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 12 Mar 2010 15:45:37 +0000 Subject: [PATCH] adding authority version of the record merging and queue processing functions git-svn-id: svn://svn.open-ils.org/ILS/trunk@15828 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 123 ++++++++++++++++++++ ...3.schema.vandelay.authority_merge_functions.sql | 129 +++++++++++++++++++++ 3 files changed, 253 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0193.schema.vandelay.authority_merge_functions.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 176dc25401..8a70fe34ee 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0192'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0193'); -- miker 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 584ab3f520..5707563a5f 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -924,6 +924,129 @@ CREATE TRIGGER ingest_authority_trigger AFTER INSERT OR UPDATE ON vandelay.queued_authority_record FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc(); +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 <> '' 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; + +CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$ +DECLARE + eg_id BIGINT; + match_count INT; +BEGIN + SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id; + + IF match_count <> 1 THEN + -- RAISE NOTICE 'not an exact match'; + RETURN FALSE; + END IF; + + SELECT m.eg_record INTO eg_id + FROM vandelay.authority_match m + WHERE m.queued_record = import_id + LIMIT 1; + + IF eg_id IS NULL THEN + RETURN FALSE; + END IF; + + RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id ); +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$ +DECLARE + queued_record vandelay.queued_authority_record%ROWTYPE; + success BOOL; +BEGIN + + FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP + success := vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ); + + IF success THEN + RETURN NEXT queued_record.id; + END IF; + + END LOOP; + + RETURN; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL ); +$$ LANGUAGE SQL; + + -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (1, 'title', oils_i18n_gettext(1, 'vqbrad', 'Title of work', 'description'),'//*[@tag="245"]/*[contains("abcmnopr",@code)]'); --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (2, 'author', oils_i18n_gettext(1, 'vqbrad', 'Author of work', 'description'),'//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]'); diff --git a/Open-ILS/src/sql/Pg/upgrade/0193.schema.vandelay.authority_merge_functions.sql b/Open-ILS/src/sql/Pg/upgrade/0193.schema.vandelay.authority_merge_functions.sql new file mode 100644 index 0000000000..e6336b7f51 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0193.schema.vandelay.authority_merge_functions.sql @@ -0,0 +1,129 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0193'); -- miker + +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 <> '' 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; + +CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$ +DECLARE + eg_id BIGINT; + match_count INT; +BEGIN + SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id; + + IF match_count <> 1 THEN + -- RAISE NOTICE 'not an exact match'; + RETURN FALSE; + END IF; + + SELECT m.eg_record INTO eg_id + FROM vandelay.authority_match m + WHERE m.queued_record = import_id + LIMIT 1; + + IF eg_id IS NULL THEN + RETURN FALSE; + END IF; + + RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id ); +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$ +DECLARE + queued_record vandelay.queued_authority_record%ROWTYPE; + success BOOL; +BEGIN + + FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP + success := vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ); + + IF success THEN + RETURN NEXT queued_record.id; + END IF; + + END LOOP; + + RETURN; + +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL ); +$$ LANGUAGE SQL; + + +COMMIT; + -- 2.11.0