From 44dc3c49b79091a805cfc5adf777f296a0f85749 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 14 Dec 2016 10:51:54 -0500 Subject: [PATCH] JBAS-1692 2.7 -> 2.9 SQL Upgrade X-Ports Port KCLS mods to modified functions back into upgrade script. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/2.7-to-2.9-upgrade.sql | 156 +++++++++++++++++++++----- 1 file changed, 131 insertions(+), 25 deletions(-) diff --git a/KCLS/sql/schema/deploy/2.7-to-2.9-upgrade.sql b/KCLS/sql/schema/deploy/2.7-to-2.9-upgrade.sql index dc63af4283..90badfd9a5 100644 --- a/KCLS/sql/schema/deploy/2.7-to-2.9-upgrade.sql +++ b/KCLS/sql/schema/deploy/2.7-to-2.9-upgrade.sql @@ -659,12 +659,13 @@ BEGIN ans.id, COUNT( cp.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), - SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), + COUNT( cp.id ), -- KCLS CUSTOM + --SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) - JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) + --JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) GROUP BY 1,2,6; @@ -691,12 +692,13 @@ BEGIN ans.id, COUNT( cp.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), - SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), + COUNT( cp.id ), -- KCLS CUSTOM + --SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) - JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) + --JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) GROUP BY 1,2,6; @@ -24054,25 +24056,55 @@ BEGIN RETURN; END; - $func$ LANGUAGE PLPGSQL STABLE STRICT; --- AFTER UPDATE OR INSERT trigger for authority.record_entry -CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ + +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; - -- Should remove matching $0 from controlled fields at the same time? - -- XXX What do we about the actual linking subfields present in - -- authority records that target this one when this happens? + -- 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; @@ -24090,9 +24122,15 @@ BEGIN PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_auto_update' AND enabled; IF NOT FOUND THEN - PERFORM authority.propagate_changes(NEW.id); + -- 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; @@ -24104,24 +24142,91 @@ BEGIN 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,thesaurus) VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value, ashs.thesaurus); - ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS); - SELECT INTO mbe_row * FROM metabib.browse_entry - WHERE value = ashs.value AND sort_value = ashs.sort_value; + ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS); - IF FOUND THEN - mbe_id := mbe_row.id; - ELSE - INSERT INTO metabib.browse_entry - ( value, sort_value ) VALUES - ( ashs.value, ashs.sort_value ); + -- 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; - mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS); - END IF; + 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); - INSERT INTO metabib.browse_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; @@ -24139,6 +24244,7 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; + DROP FUNCTION IF EXISTS authority.atag_search_heading_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.btag_search_heading_refs(TEXT, TEXT, INT, INT); DROP FUNCTION IF EXISTS authority.axis_search_heading_refs(TEXT, TEXT, INT, INT); -- 2.11.0