From fe93f4730b27ef64ff0fc56ef48e1ebc516a6b4f Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 13 Jun 2016 12:10:55 -0400 Subject: [PATCH] JBAS-1459 Authority propagation updates bib editor/date Cross-porting LP#1588948 - Authority record updates that propagate to bib records also updates bib editor and edit_date columns. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/auth-prop-mods-bib-meta.sql | 250 +++++++++++++++++++++ KCLS/sql/schema/revert/auth-prop-mods-bib-meta.sql | 199 ++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/auth-prop-mods-bib-meta.sql | 7 + 4 files changed, 457 insertions(+) create mode 100644 KCLS/sql/schema/deploy/auth-prop-mods-bib-meta.sql create mode 100644 KCLS/sql/schema/revert/auth-prop-mods-bib-meta.sql create mode 100644 KCLS/sql/schema/verify/auth-prop-mods-bib-meta.sql diff --git a/KCLS/sql/schema/deploy/auth-prop-mods-bib-meta.sql b/KCLS/sql/schema/deploy/auth-prop-mods-bib-meta.sql new file mode 100644 index 0000000000..980358534f --- /dev/null +++ b/KCLS/sql/schema/deploy/auth-prop-mods-bib-meta.sql @@ -0,0 +1,250 @@ +-- Deploy kcls-evergreen:auth-prop-mods-bib-meta to pg +-- requires: ingram-edi-mods + +BEGIN; + +\set eg_version '''2.7.8''' +SELECT evergreen.upgrade_deps_block_check('0994', :eg_version); + +DROP FUNCTION authority.propagate_changes (BIGINT, BIGINT); + +CREATE OR REPLACE FUNCTION authority.propagate_changes + (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$ +DECLARE + bib_rec biblio.record_entry%ROWTYPE; + new_marc TEXT; +BEGIN + + SELECT INTO bib_rec * FROM biblio.record_entry WHERE id = bid; + + new_marc := vandelay.merge_record_xml( + bib_rec.marc, authority.generate_overlay_template(aid)); + + IF new_marc = bib_rec.marc THEN + -- Authority record change had no impact on this bib record. + -- Nothing left to do. + RETURN aid; + END IF; + + PERFORM 1 FROM config.global_flag + WHERE name = 'ingest.disable_authority_auto_update_bib_meta' + AND enabled; + + IF NOT FOUND THEN + -- update the bib record editor and edit_date + bib_rec.editor := ( + SELECT editor FROM authority.record_entry WHERE id = aid); + bib_rec.edit_date = NOW(); + END IF; + + UPDATE biblio.record_entry SET + marc = new_marc, + editor = bib_rec.editor, + edit_date = bib_rec.edit_date + WHERE id = bid; + + RETURN aid; + +END; +$func$ LANGUAGE PLPGSQL; + +-- DATA +-- Disabled by default +INSERT INTO config.global_flag (name, enabled, label) VALUES ( + 'ingest.disable_authority_auto_update_bib_meta', FALSE, + oils_i18n_gettext( + 'ingest.disable_authority_auto_update_bib_meta', + 'Authority Automation: Disable automatic authority updates ' || + 'from modifying bib record editor and edit_date', + 'cgf', + 'label' + ) +); + +CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () + RETURNS TRIGGER AS $func$ +DECLARE + ashs authority.simple_heading%ROWTYPE; + mbe_row metabib.browse_entry%ROWTYPE; + mbe_id BIGINT; + ash_id BIGINT; + search_class text; + field_id integer; + are_row authority.record_entry%ROWTYPE; + bre_row biblio.record_entry%ROWTYPE; +BEGIN + --ver 2.1 KMAIN-1119 + IF NEW.deleted IS TRUE THEN -- If this authority is deleted + + -- Remove the MARC linking $0 subfields from linked bib records. + FOR bre_row IN SELECT * FROM biblio.record_entry bre + INNER JOIN authority.bib_linking abl + ON bre.id = abl.bib AND abl.authority = NEW.id LOOP + + UPDATE biblio.record_entry + SET marc = ( + SELECT regexp_replace(bre_row.marc, + E']*?code="0">\\([A-Z]+\\)' || + NEW.id || '','','g') + ) + WHERE id = bre_row.id; + END LOOP; + + DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible + DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records + DELETE FROM authority.simple_heading WHERE record = NEW.id; + + -- Remove the MARC linking $0 subfields from linked authority records. + FOR are_row IN SELECT * FROM authority.record_entry auth + INNER JOIN authority.authority_linking aal + ON auth.id = aal.source AND aal.target = NEW.id LOOP + + UPDATE authority.record_entry + SET marc = ( + SELECT regexp_replace(are_row.marc, + E']*?code="0">\\([A-Z]+\\)' || + NEW.id || '','','g') + ) + WHERE id = are_row.id; + END LOOP; + + DELETE FROM authority.authority_linking + WHERE source = NEW.id OR target = NEW.id; + + RETURN NEW; -- and we're done + END IF; + + IF TG_OP = 'UPDATE' THEN -- re-ingest? + PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled; + + IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change + RETURN NEW; + END IF; + + -- Unless there's a setting stopping us, propagate these updates to any linked bib records + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_auto_update' AND enabled; + + IF NOT FOUND THEN + -- KMAIN-1521 + -- Did the authority heading change? + -- No need to propagate to bibs if the heading did not change + -- See also LP#1637569 + IF authority.heading_changed(OLD.marc, NEW.marc) THEN + PERFORM authority.propagate_changes(NEW.id); + END IF; + END IF; + + DELETE FROM authority.simple_heading WHERE record = NEW.id; + DELETE FROM authority.authority_linking WHERE source = NEW.id; + END IF; + + INSERT INTO authority.authority_linking (source, target, field) + SELECT source, target, field FROM authority.calculate_authority_linking( + NEW.id, NEW.control_set, NEW.marc::XML + ); + + FOR ashs IN SELECT * FROM authority.simple_heading_set(NEW.marc) LOOP + + -- Get the search_class + SELECT INTO search_class, field_id cmf.field_class, cmf.id + FROM authority.control_set_auth_field_metabib_field_map_refs AS acsafmfmr + JOIN config.metabib_field AS cmf + ON acsafmfmr.metabib_field = cmf.id + WHERE acsafmfmr.authority_field = ashs.atag; + + INSERT INTO authority.simple_heading (record,atag,value,sort_value) + VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value); + + ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS); + + -- CASE statement switches on search_class to use the correct browse table (author, series, subject, title) + CASE search_class + WHEN 'author' THEN + SELECT INTO mbe_row * FROM metabib.browse_author_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_author_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_author_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_author_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + WHEN 'series' THEN + SELECT INTO mbe_row * FROM metabib.browse_series_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_series_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_series_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_series_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + WHEN 'subject' THEN + SELECT INTO mbe_row * FROM metabib.browse_subject_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_subject_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_subject_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_subject_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + WHEN 'title' THEN + SELECT INTO mbe_row * FROM metabib.browse_title_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_title_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_title_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_title_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + ELSE + -- mainly to handle when search_class is 'keyword' + END CASE; + + END LOOP; + + -- Flatten and insert the afr data + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled; + IF NOT FOUND THEN + PERFORM authority.reingest_authority_full_rec(NEW.id); + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled; + IF NOT FOUND THEN + PERFORM authority.reingest_authority_rec_descriptor(NEW.id); + END IF; + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/KCLS/sql/schema/revert/auth-prop-mods-bib-meta.sql b/KCLS/sql/schema/revert/auth-prop-mods-bib-meta.sql new file mode 100644 index 0000000000..e4f7f89997 --- /dev/null +++ b/KCLS/sql/schema/revert/auth-prop-mods-bib-meta.sql @@ -0,0 +1,199 @@ +-- Revert kcls-evergreen:auth-prop-mods-bib-meta from pg + +BEGIN; + +DELETE FROM config.upgrade_log WHERE version = '0994'; + +DROP FUNCTION authority.propagate_changes (BIGINT, BIGINT); + +CREATE FUNCTION authority.propagate_changes(aid bigint, bid bigint) RETURNS bigint + LANGUAGE sql + AS $_$ + UPDATE biblio.record_entry + SET marc = vandelay.merge_record_xml( marc, authority.generate_overlay_template( $1 ) ) + WHERE id = $2; + SELECT $1; +$_$; + +DELETE FROM config.global_flag + WHERE name = 'ingest.disable_authority_auto_update_bib_meta'; + + +CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () + RETURNS TRIGGER AS $func$ +DECLARE + ashs authority.simple_heading%ROWTYPE; + mbe_row metabib.browse_entry%ROWTYPE; + mbe_id BIGINT; + ash_id BIGINT; + search_class text; + field_id integer; + are_row authority.record_entry%ROWTYPE; + bre_row biblio.record_entry%ROWTYPE; +BEGIN + --ver 2.1 KMAIN-1119 + IF NEW.deleted IS TRUE THEN -- If this authority is deleted + -- Remove the actual linking subfields present in + -- marc bib records that is controlled by this one + FOR bre_row IN SELECT * FROM biblio.record_entry bre + INNER JOIN authority.bib_linking abl + ON bre.id = abl.bib AND abl.authority = NEW.id LOOP + + UPDATE biblio.record_entry + SET marc = (SELECT regexp_replace(bre_row.marc,E']*?code="0">\\([A-Z]+\\)' || NEW.id || '','','g')) + WHERE id = bre_row.id; + + END LOOP; + DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible + DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records + DELETE FROM authority.simple_heading WHERE record = NEW.id; + -- Remove the actual linking subfields present in + -- authority records that target this one + FOR are_row IN SELECT * FROM authority.record_entry auth + INNER JOIN authority.authority_linking aal + ON auth.id = aal.source AND aal.target = NEW.id LOOP + + UPDATE authority.record_entry + SET marc = (SELECT regexp_replace(are_row.marc,E']*?code="0">\\([A-Z]+\\)' || NEW.id || '','','g')) + WHERE id = are_row.id; + + END LOOP; + DELETE FROM authority.authority_linking + WHERE source = NEW.id OR target = NEW.id; + + RETURN NEW; -- and we're done + END IF; + + IF TG_OP = 'UPDATE' THEN -- re-ingest? + PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled; + + IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change + RETURN NEW; + END IF; + + -- Unless there's a setting stopping us, propagate these updates to any linked bib records + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_auto_update' AND enabled; + + IF NOT FOUND THEN + -- KMAIN-1521 + -- Did the authority heading change? + -- No need to propagate to bibs if the heading did not change + IF authority.heading_changed(OLD.marc, NEW.marc) THEN + PERFORM authority.propagate_changes(NEW.id); + END IF; + END IF; + + DELETE FROM authority.simple_heading WHERE record = NEW.id; + DELETE FROM authority.authority_linking WHERE source = NEW.id; + END IF; + + INSERT INTO authority.authority_linking (source, target, field) + SELECT source, target, field FROM authority.calculate_authority_linking( + NEW.id, NEW.control_set, NEW.marc::XML + ); + + FOR ashs IN SELECT * FROM authority.simple_heading_set(NEW.marc) LOOP + + -- Get the search_class + SELECT INTO search_class, field_id cmf.field_class, cmf.id + FROM authority.control_set_auth_field_metabib_field_map_refs AS acsafmfmr + JOIN config.metabib_field AS cmf + ON acsafmfmr.metabib_field = cmf.id + WHERE acsafmfmr.authority_field = ashs.atag; + + INSERT INTO authority.simple_heading (record,atag,value,sort_value) + VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value); + + ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS); + + -- CASE statement switches on search_class to use the correct browse table (author, series, subject, title) + CASE search_class + WHEN 'author' THEN + SELECT INTO mbe_row * FROM metabib.browse_author_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_author_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_author_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_author_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + WHEN 'series' THEN + SELECT INTO mbe_row * FROM metabib.browse_series_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_series_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_series_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_series_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + WHEN 'subject' THEN + SELECT INTO mbe_row * FROM metabib.browse_subject_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_subject_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_subject_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_subject_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + WHEN 'title' THEN + SELECT INTO mbe_row * FROM metabib.browse_title_entry + WHERE value = ashs.value AND sort_value = ashs.sort_value + ORDER BY id; + + IF FOUND THEN + mbe_id := mbe_row.id; + ELSE + INSERT INTO metabib.browse_title_entry + ( value, sort_value, truncated_sort_value ) VALUES + ( ashs.value, ashs.sort_value, substr(ashs.sort_value, 1, 2700) ); + + mbe_id := CURRVAL('metabib.browse_title_entry_id_seq'::REGCLASS); + END IF; + + INSERT INTO metabib.browse_title_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id); + + ELSE + -- mainly to handle when search_class is 'keyword' + END CASE; + + END LOOP; + + -- Flatten and insert the afr data + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled; + IF NOT FOUND THEN + PERFORM authority.reingest_authority_full_rec(NEW.id); + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled; + IF NOT FOUND THEN + PERFORM authority.reingest_authority_rec_descriptor(NEW.id); + END IF; + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index fb2f7675dd..a371feac10 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -36,3 +36,4 @@ new-headings-report [po-print-li-count-and-date] 2016-01-28T22:23:12Z Bill Erick copy-status-alerts [purge-user-activity] 2016-05-25T14:10:24Z Bill Erickson # Reduce copy checkin alerts by status self-reg-net-access-level [purge-user-activity] 2016-05-25T14:59:56Z Bill Erickson # Staged users get net_access_level vand-delete-speed-indexes [self-reg-net-access-level] 2016-09-30T14:52:21Z Bill Erickson # Indexes to speed up vandelay queue deleting +auth-prop-mods-bib-meta [ingram-edi-mods] 2016-06-13T16:07:28Z Bill Erickson # Authority propagation udpates bib editor/edit_date diff --git a/KCLS/sql/schema/verify/auth-prop-mods-bib-meta.sql b/KCLS/sql/schema/verify/auth-prop-mods-bib-meta.sql new file mode 100644 index 0000000000..f883579fda --- /dev/null +++ b/KCLS/sql/schema/verify/auth-prop-mods-bib-meta.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:auth-prop-mods-bib-meta on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0