From 106fbf0ea5da5f50ad4746557a68331a4ce13424 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 6 Dec 2017 18:18:29 -0500 Subject: [PATCH] LP#1730758: Track record visibility on all Located URI DML This commit simplifies the handling of Located URI call numbers by separating them from normal call numbers. This will allow proper updating of bib-level visibility attributes for all actions that may happen to a Located URI. We also insure that we don't return a NULL INT[] as an attribute set for either copies or bibs. This was always impossible for copies, but was possible for bibs. Now both are future-proof. The upgrade script contains a post-transaction command to forcibly update the visibility attributes of all bibs that make use of Located URIs or bib sources. It may take a while to run on large datasets. If it it running too long, it can be canceled and the following psql commands will create and run a script that will perform the same action serially over time without blocking writes to bibs: \t \o /tmp/luri_visibility_update.sql SELECT 'UPDATE biblio.record_entry SET ' || 'vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id) ' || 'WHERE id = ' || id || '; SELECT ' || id || ';' FROM biblio.record_entry WHERE id IN ( SELECT DISTINCT cn.record FROM asset.call_number cn WHERE NOT cn.deleted AND cn.label = '##URI##' AND EXISTS ( SELECT 1 FROM asset.uri_call_number_map m WHERE m.call_number = cn.id ) UNION SELECT id FROM biblio.record_entry WHERE source IS NOT NULL ); \o \t \i /tmp/luri_visibility_update.sql It will output the id of each updated bib so that the script can be killed and then edited to remove completed bibs. The remainder can be run at a later time. NOTE: When the internal flag 'ingest.reingest.force_on_same_marc' is enabled, we do NOT update the bib's visibility attributes, as doing so causes a loop and an eventual trigger stack violation. This flag should ONLY be used when forcing reingest of record attributes (NOT visibility attributes), search, facet, and display fields, so if using this flag under normal operation, proceed at your own risk and know that Located URI and bib source changes will not be reflected in the visibility attributes of the record. Signed-off-by: Mike Rylander Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 132 ++++++------ .../Pg/upgrade/XXXX.function.luri_vis_cache.sql | 224 +++++++++++++++++++++ 2 files changed, 290 insertions(+), 66 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.luri_vis_cache.sql diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql index 8dabef943f..c6cfdf4c49 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -523,7 +523,7 @@ CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_ DECLARE copy_row asset.copy%ROWTYPE; lgroup_map asset.copy_location_group_map%ROWTYPE; - attr_set INT[]; + attr_set INT[] := '{}'::INT[]; BEGIN SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id; @@ -547,26 +547,28 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$ +CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT, new_source INT DEFAULT NULL, force_source BOOL DEFAULT FALSE ) RETURNS INT[] AS $f$ DECLARE bib_row biblio.record_entry%ROWTYPE; cn_row asset.call_number%ROWTYPE; - attr_set INT[]; + attr_set INT[] := '{}'::INT[]; BEGIN SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id; - IF bib_row.source IS NOT NULL THEN + IF force_source THEN + IF new_source IS NOT NULL THEN + attr_set := attr_set || search.calculate_visibility_attribute(new_source, 'bib_source'); + END IF; + ELSIF bib_row.source IS NOT NULL THEN attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source'); END IF; FOR cn_row IN - SELECT cn.* - FROM asset.call_number cn - JOIN asset.uri_call_number_map m ON (cn.id = m.call_number) - JOIN asset.uri u ON (u.id = m.uri) - WHERE cn.record = bib_id - AND cn.label = '##URI##' - AND u.active + SELECT * + FROM asset.call_number + WHERE record = bib_id + AND label = '##URI##' + AND NOT deleted LOOP attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org'); END LOOP; @@ -580,8 +582,11 @@ DECLARE ocn asset.call_number%ROWTYPE; ncn asset.call_number%ROWTYPE; cid BIGINT; + dobib BOOL; BEGIN + SELECT enabled = FALSE INTO dobib FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc'; + IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately IF TG_OP = 'INSERT' THEN INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES ( @@ -608,7 +613,12 @@ BEGIN asset.calculate_copy_visibility_attribute_set(NEW.id) ); ELSIF TG_TABLE_NAME = 'record_entry' THEN - NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id); + NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE); + ELSIF TG_TABLE_NAME = 'call_number' AND NEW.label = '##URI##' AND dobib THEN -- New located URI + UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record) + WHERE id = NEW.record; + END IF; RETURN NEW; @@ -637,81 +647,70 @@ BEGIN END IF; RETURN NEW; - ELSIF OLD.call_number <> NEW.call_number THEN - SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number; - - IF ncn.record <> ocn.record THEN - UPDATE biblio.record_entry - SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record) - WHERE id = ocn.record; + ELSIF OLD.location <> NEW.location OR + OLD.status <> NEW.status OR + OLD.opac_visible <> NEW.opac_visible OR + OLD.circ_lib <> NEW.circ_lib OR + OLD.call_number <> NEW.call_number + THEN + IF OLD.call_number <> NEW.call_number THEN -- Special check since it's more expensive than the next branch + SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number; + + IF ncn.record <> ocn.record THEN + -- We have to use a record-specific WHERE clause + -- to avoid modifying the entries for peer-bib copies. + UPDATE asset.copy_vis_attr_cache + SET target_copy = NEW.id, + record = ncn.record + WHERE target_copy = OLD.id + AND record = ocn.record; - -- We have to use a record-specific WHERE clause - -- to avoid modifying the entries for peer-bib copies. + END IF; + ELSE + -- Any of these could change visibility, but + -- we'll save some queries and not try to calculate + -- the change directly. We want to update peer-bib + -- entries in this case, unlike above. UPDATE asset.copy_vis_attr_cache SET target_copy = NEW.id, - record = ncn.record - WHERE target_copy = OLD.id - AND record = ocn.record; + vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id) + WHERE target_copy = OLD.id; END IF; END IF; - IF OLD.location <> NEW.location OR - OLD.status <> NEW.status OR - OLD.opac_visible <> NEW.opac_visible OR - OLD.circ_lib <> NEW.circ_lib - THEN - -- Any of these could change visibility, but - -- we'll save some queries and not try to calculate - -- the change directly. We want to update peer-bib - -- entries in this case, unlike above. - UPDATE asset.copy_vis_attr_cache - SET target_copy = NEW.id, - vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id) - WHERE target_copy = OLD.id; + ELSIF TG_TABLE_NAME = 'call_number' THEN + IF TG_OP = 'DELETE' AND OLD.label = '##URI##' AND dobib THEN -- really deleted located URI, if the delete protection rule is disabled... + UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record) + WHERE id = OLD.record; + RETURN OLD; END IF; - ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE. - - IF OLD.record <> NEW.record THEN - IF NEW.label = '##URI##' THEN - UPDATE biblio.record_entry - SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record) - WHERE id = OLD.record; - + IF OLD.label = '##URI##' AND dobib THEN -- Located URI + IF OLD.deleted <> NEW.deleted OR OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record) WHERE id = NEW.record; + + IF OLD.record <> NEW.record THEN -- maybe on merge? + UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record) + WHERE id = OLD.record; + END IF; END IF; + ELSIF OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN UPDATE asset.copy_vis_attr_cache SET record = NEW.record, vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy) WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id) AND record = OLD.record; - ELSIF OLD.owning_lib <> NEW.owning_lib THEN - UPDATE asset.copy_vis_attr_cache - SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy) - WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id) - AND record = NEW.record; - - IF NEW.label = '##URI##' THEN - UPDATE biblio.record_entry - SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record) - WHERE id = OLD.record; - END IF; - END IF; - - ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE - - IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally - DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id; - RETURN OLD; - ELSIF OLD.source <> NEW.source THEN - NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id); END IF; + ELSIF TG_TABLE_NAME = 'record_entry' AND OLD.source IS DISTINCT FROM NEW.source THEN -- Only handles ON UPDATE, INSERT above + NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE); END IF; RETURN NEW; @@ -720,7 +719,7 @@ $func$ LANGUAGE PLPGSQL; CREATE TRIGGER z_opac_vis_mat_view_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR DELETE ON biblio.peer_bib_copy_map FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); -CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); +CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE OR DELETE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); @@ -1307,3 +1306,4 @@ $p$ LANGUAGE PLPGSQL ROWS 10; COMMIT; + diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.luri_vis_cache.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.luri_vis_cache.sql new file mode 100644 index 0000000000..8ac1383712 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.luri_vis_cache.sql @@ -0,0 +1,224 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$ +DECLARE + copy_row asset.copy%ROWTYPE; + lgroup_map asset.copy_location_group_map%ROWTYPE; + attr_set INT[] := '{}'::INT[]; +BEGIN + SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id; + + attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags'); + attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib'); + attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status'); + attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location'); + + SELECT ARRAY_APPEND( + attr_set, + search.calculate_visibility_attribute(owning_lib, 'owning_lib') + ) INTO attr_set + FROM asset.call_number + WHERE id = copy_row.call_number; + + FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP + attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group'); + END LOOP; + + RETURN attr_set; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION IF EXISTS biblio.calculate_bib_visibility_attribute_set ( BIGINT ); +CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT, new_source INT DEFAULT NULL, force_source BOOL DEFAULT FALSE ) RETURNS INT[] AS $f$ +DECLARE + bib_row biblio.record_entry%ROWTYPE; + cn_row asset.call_number%ROWTYPE; + attr_set INT[] := '{}'::INT[]; +BEGIN + SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id; + + IF force_source THEN + IF new_source IS NOT NULL THEN + attr_set := attr_set || search.calculate_visibility_attribute(new_source, 'bib_source'); + END IF; + ELSIF bib_row.source IS NOT NULL THEN + attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source'); + END IF; + + FOR cn_row IN + SELECT * + FROM asset.call_number + WHERE record = bib_id + AND label = '##URI##' + AND NOT deleted + LOOP + attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org'); + END LOOP; + + RETURN attr_set; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ +DECLARE + ocn asset.call_number%ROWTYPE; + ncn asset.call_number%ROWTYPE; + cid BIGINT; + dobib BOOL; +BEGIN + + SELECT enabled = FALSE INTO dobib FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc'; + + IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately + IF TG_OP = 'INSERT' THEN + INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES ( + NEW.peer_record, + NEW.target_copy, + asset.calculate_copy_visibility_attribute_set(NEW.target_copy) + ); + + RETURN NEW; + ELSIF TG_OP = 'DELETE' THEN + DELETE FROM asset.copy_vis_attr_cache + WHERE record = OLD.peer_record AND target_copy = OLD.target_copy; + + RETURN OLD; + END IF; + END IF; + + IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below. + IF TG_TABLE_NAME IN ('copy', 'unit') THEN + SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number; + INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES ( + ncn.record, + NEW.id, + asset.calculate_copy_visibility_attribute_set(NEW.id) + ); + ELSIF TG_TABLE_NAME = 'record_entry' THEN + NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE); + ELSIF TG_TABLE_NAME = 'call_number' AND NEW.label = '##URI##' AND dobib THEN -- New located URI + UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record) + WHERE id = NEW.record; + + END IF; + + RETURN NEW; + END IF; + + -- handle items first, since with circulation activity + -- their statuses change frequently + IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above + + IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally + DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id; + RETURN OLD; + END IF; + + SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number; + + IF OLD.deleted <> NEW.deleted THEN + IF NEW.deleted THEN + DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id; + ELSE + INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES ( + ncn.record, + NEW.id, + asset.calculate_copy_visibility_attribute_set(NEW.id) + ); + END IF; + + RETURN NEW; + ELSIF OLD.location <> NEW.location OR + OLD.status <> NEW.status OR + OLD.opac_visible <> NEW.opac_visible OR + OLD.circ_lib <> NEW.circ_lib OR + OLD.call_number <> NEW.call_number + THEN + IF OLD.call_number <> NEW.call_number THEN -- Special check since it's more expensive than the next branch + SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number; + + IF ncn.record <> ocn.record THEN + -- We have to use a record-specific WHERE clause + -- to avoid modifying the entries for peer-bib copies. + UPDATE asset.copy_vis_attr_cache + SET target_copy = NEW.id, + record = ncn.record + WHERE target_copy = OLD.id + AND record = ocn.record; + + END IF; + ELSE + -- Any of these could change visibility, but + -- we'll save some queries and not try to calculate + -- the change directly. We want to update peer-bib + -- entries in this case, unlike above. + UPDATE asset.copy_vis_attr_cache + SET target_copy = NEW.id, + vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id) + WHERE target_copy = OLD.id; + END IF; + END IF; + + ELSIF TG_TABLE_NAME = 'call_number' THEN + + IF TG_OP = 'DELETE' AND OLD.label = '##URI##' AND dobib THEN -- really deleted located URI, if the delete protection rule is disabled... + UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record) + WHERE id = OLD.record; + RETURN OLD; + END IF; + + IF OLD.label = '##URI##' AND dobib THEN -- Located URI + IF OLD.deleted <> NEW.deleted OR OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN + UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record) + WHERE id = NEW.record; + + IF OLD.record <> NEW.record THEN -- maybe on merge? + UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record) + WHERE id = OLD.record; + END IF; + END IF; + + ELSIF OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN + UPDATE asset.copy_vis_attr_cache + SET record = NEW.record, + vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy) + WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id) + AND record = OLD.record; + + END IF; + + ELSIF TG_TABLE_NAME = 'record_entry' AND OLD.source IS DISTINCT FROM NEW.source THEN -- Only handles ON UPDATE, INSERT above + NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE); + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +DROP TRIGGER z_opac_vis_mat_view_tgr ON asset.call_number; +DROP TRIGGER z_opac_vis_mat_view_tgr ON biblio.record_entry; +CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE OR DELETE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); +CREATE TRIGGER z_opac_vis_mat_view_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); + +COMMIT; + +UPDATE biblio.record_entry + SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id) + WHERE id IN ( + SELECT DISTINCT cn.record + FROM asset.call_number cn + WHERE NOT cn.deleted + AND cn.label = '##URI##' + AND EXISTS ( + SELECT 1 + FROM asset.uri_call_number_map m + WHERE m.call_number = cn.id + ) + UNION + SELECT id FROM biblio.record_entry WHERE source IS NOT NULL + ); + -- 2.11.0