1 -- Evergreen DB patch 0614.schema.cache_visibility_and_peer_records.sql
3 -- LP#837566 undeleting a bib record causes an error
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0614', :eg_version);
11 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
18 do_add BOOLEAN := false;
19 do_remove BOOLEAN := false;
22 SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status
24 JOIN asset.call_number cn ON (cn.id = cp.call_number)
25 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
26 JOIN asset.copy_location cl ON (cp.location = cl.id)
27 JOIN config.copy_status cs ON (cp.status = cs.id)
28 JOIN biblio.record_entry b ON (cn.record = b.id)
38 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status
40 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
41 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
42 JOIN asset.copy_location cl ON (cp.location = cl.id)
43 JOIN config.copy_status cs ON (cp.status = cs.id)
51 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
52 SELECT id, circ_lib, record FROM (
58 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;
60 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
61 IF TG_OP = 'INSERT' THEN
62 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record;
63 EXECUTE add_front || add_peer_query || add_back;
66 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
72 IF TG_OP = 'INSERT' THEN
74 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
75 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
76 EXECUTE add_front || add_base_query || add_back;
83 -- handle items first, since with circulation activity
84 -- their statuses change frequently
85 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
87 IF OLD.location <> NEW.location OR
88 OLD.call_number <> NEW.call_number OR
89 OLD.status <> NEW.status OR
90 OLD.circ_lib <> NEW.circ_lib THEN
91 -- any of these could change visibility, but
92 -- we'll save some queries and not try to calculate
93 -- the change directly
98 IF OLD.deleted <> NEW.deleted THEN
106 IF OLD.opac_visible <> NEW.opac_visible THEN
107 IF OLD.opac_visible THEN
109 ELSIF NOT do_remove THEN -- handle edge case where deleted item
110 -- is also marked opac_visible
118 DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
121 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
122 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id;
123 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
130 IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
132 IF OLD.deleted AND NEW.deleted THEN -- do nothing
136 ELSIF NEW.deleted THEN -- remove rows
138 IF TG_TABLE_NAME = 'call_number' THEN
139 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
140 ELSIF TG_TABLE_NAME = 'record_entry' THEN
141 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
146 ELSIF OLD.deleted THEN -- add rows
148 IF TG_TABLE_NAME = 'call_number' THEN
149 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
150 EXECUTE add_front || add_base_query || add_back;
151 ELSIF TG_TABLE_NAME = 'record_entry' THEN
152 add_base_query := add_base_query || ' AND cn.record = ' || NEW.id;
153 add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id;
154 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
163 IF TG_TABLE_NAME = 'call_number' THEN
165 IF OLD.record <> NEW.record THEN
166 -- call number is linked to different bib
167 remove_query := remove_query || 'call_number = ' || NEW.id || ');';
168 EXECUTE remove_query;
169 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
170 EXECUTE add_front || add_base_query || add_back;
177 IF TG_TABLE_NAME IN ('record_entry') THEN
178 RETURN NEW; -- don't have 'opac_visible'
181 -- actor.org_unit, asset.copy_location, asset.copy_status
182 IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
186 ELSIF NEW.opac_visible THEN -- add rows
188 IF TG_TABLE_NAME = 'org_unit' THEN
189 add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id;
190 add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id;
191 ELSIF TG_TABLE_NAME = 'copy_location' THEN
192 add_base_query := add_base_query || ' AND cp.location = ' || NEW.id;
193 add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id;
194 ELSIF TG_TABLE_NAME = 'copy_status' THEN
195 add_base_query := add_base_query || ' AND cp.status = ' || NEW.id;
196 add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id;
199 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
203 IF TG_TABLE_NAME = 'org_unit' THEN
204 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
205 ELSIF TG_TABLE_NAME = 'copy_location' THEN
206 remove_query := remove_query || 'location = ' || NEW.id || ');';
207 ELSIF TG_TABLE_NAME = 'copy_status' THEN
208 remove_query := remove_query || 'status = ' || NEW.id || ');';
211 EXECUTE remove_query;
217 $func$ LANGUAGE PLPGSQL;