From 2573288d5ab3f721255eff42fd0dd9ba130a941e Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Sun, 4 Dec 2011 15:56:56 -0500 Subject: [PATCH] Revamp solr notification schema to rely on copy visibility asset.opac_copy_visibility tracks copy visibility for a given record / library combination quite nicely, so piggy-back on that for our physical media purposes and focus asset.call_number solely on events involving located URIs. Thanks to Discovery Garden, Inc for sponsoring this enhancement. Signed-off-by: Dan Scott --- Open-ILS/src/sql/Pg/solr.sql | 99 +++++++++++++++++++++++++++++++++++++------- 1 file changed, 84 insertions(+), 15 deletions(-) diff --git a/Open-ILS/src/sql/Pg/solr.sql b/Open-ILS/src/sql/Pg/solr.sql index da5c12f413..9732d6c1f8 100644 --- a/Open-ILS/src/sql/Pg/solr.sql +++ b/Open-ILS/src/sql/Pg/solr.sql @@ -55,8 +55,11 @@ $notify_solr$ LANGUAGE plpgsql; COMMENT ON FUNCTION solr.notify_solr_bib() IS $about$ Adds an entry to the solr.bib_updates table when a bib record is -updated (if the MARC has changed) or deleted (generally when all -call numbers attached to the bib record have been deleted). +updated (if the MARC has changed) or deleted. Deletes generally occur +when all call numbers attached to the bib record have been deleted, +but sites have been known to delete bib records while leaving undeleted +call numbers and copies around - and a deleted bib record should not be +visible. $about$; DROP TRIGGER IF EXISTS notify_solr ON biblio.record_entry; @@ -65,28 +68,82 @@ CREATE TRIGGER notify_solr AFTER UPDATE OR DELETE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE solr.notify_solr_bib(); -CREATE FUNCTION solr.notify_solr_call_number() RETURNS trigger AS $notify_solr$ +CREATE OR REPLACE FUNCTION solr.notify_solr_assets() RETURNS trigger AS $notify_solr$ DECLARE callnum_cnt INT; +DECLARE callnum_id BIGINT; +DECLARE copy_cnt INT; +DECLARE library INT; +DECLARE record_id BIGINT; BEGIN + IF (TG_OP = 'DELETE' AND TG_TABLE_NAME = 'call_number') THEN + library := OLD.owning_lib; + record_id := OLD.record; + callnum_id := OLD.id; + ELSIF (TG_TABLE_NAME = 'call_number') THEN + library := NEW.owning_lib; + record_id := NEW.record; + callnum_id := NEW.id; + ELSIF (TG_OP = 'DELETE' AND TG_TABLE_NAME = 'opac_visible_copies') THEN + library := OLD.circ_lib; + record_id := OLD.record; + callnum_cnt := 0; + ELSIF (TG_TABLE_NAME = 'opac_visible_copies') THEN + library := NEW.circ_lib; + record_id := NEW.record; + callnum_cnt := 0; + END IF; + IF (TG_OP = 'INSERT') THEN - -- Update the bib_updates table when a call number is added for + -- Update the bib_updates table when copy visibility changes for -- a given library + + IF (TG_TABLE_NAME = 'call_number') THEN + -- We only care about located URIs for the purposes of this trigger + IF (NEW.label != '##URI##') THEN + RETURN NULL; + END IF; + END IF; + INSERT INTO solr.bib_updates (record, update_type, owning_lib) - VALUES (NEW.record, 'INSERT', NEW.owning_lib); + VALUES (record_id, 'INSERT', library); - ELSIF (TG_OP = 'TRUNCATE' OR TG_OP = 'DELETE' OR (NEW.deleted IS TRUE AND OLD.deleted IS FALSE)) THEN + ELSE + IF (TG_OP = 'UPDATE') THEN + IF NEW.deleted = OLD.deleted THEN + RETURN NULL; + END IF; + END IF; - SELECT COUNT(*) INTO callnum_cnt - FROM asset.call_number acn - WHERE acn.record = NEW.record - AND acn.owning_lib = NEW.owning_lib - AND acn.deleted IS FALSE - AND acn.id <> NEW.id; + -- Do we have any located URIs? + IF TG_TABLE_NAME = 'call_number' THEN + SELECT COUNT(*) INTO callnum_cnt + FROM asset.call_number acn + WHERE acn.record = record_id + AND acn.owning_lib = library + AND acn.label = '##URI##' + AND acn.deleted IS FALSE + AND acn.id <> callnum_id; + ELSE + SELECT COUNT(*) INTO callnum_cnt + FROM asset.call_number acn + WHERE acn.record = record_id + AND acn.owning_lib = library + AND acn.label = '##URI##' + AND acn.deleted IS FALSE; + END IF; IF (callnum_cnt = 0) THEN - INSERT INTO solr.bib_updates (record, update_type, owning_lib) - VALUES (NEW.record, 'DELETE', NEW.owning_lib); + -- Do we have any visible copies? + SELECT COUNT(*) INTO copy_cnt + FROM asset.opac_visible_copies aovc + WHERE aovc.record = record_id + AND circ_lib = library; + + IF (copy_cnt = 0) THEN + INSERT INTO solr.bib_updates (record, update_type, owning_lib) + VALUES (record_id, 'DELETE', library); + END IF; END IF; END IF; @@ -94,11 +151,23 @@ BEGIN END; $notify_solr$ LANGUAGE plpgsql; +COMMENT ON FUNCTION solr.notify_solr_assets() IS $about$ +Adds an entry to the solr.bib_updates table when copy visibility +for a given record/library combination changes or when a located +URI is added or deleted from a given record. +$about$; + DROP TRIGGER IF EXISTS notify_solr_call_number ON asset.call_number; CREATE TRIGGER notify_solr_call_number AFTER INSERT OR DELETE OR UPDATE ON asset.call_number - FOR EACH ROW EXECUTE PROCEDURE solr.notify_solr_call_number(); + FOR EACH ROW EXECUTE PROCEDURE solr.notify_solr_assets(); + +DROP TRIGGER IF EXISTS notify_solr_copy_visibility ON asset.opac_visible_copies; + +CREATE TRIGGER notify_solr_copy_visibility + AFTER INSERT OR DELETE OR UPDATE ON asset.opac_visible_copies + FOR EACH ROW EXECUTE PROCEDURE solr.notify_solr_assets(); COMMIT; -- 2.11.0