1 -- Evergreen DB patch XXXX.fix_opac_copy_vis_cache.sql
3 -- Correct LP#788763: glitch in asset.cache_copy_visibility
4 -- prevented updating the visibility of copy locations, org
5 -- units, and copy statuses.
10 -- check whether patch can be applied
11 SELECT evergreen.upgrade_deps_block_check('0560', :eg_version);
13 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
17 do_add BOOLEAN := false;
18 do_remove BOOLEAN := false;
21 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
22 SELECT id, circ_lib, record FROM (
23 SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status
25 JOIN asset.call_number cn ON (cn.id = cp.call_number)
26 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
27 JOIN asset.copy_location cl ON (cp.location = cl.id)
28 JOIN config.copy_status cs ON (cp.status = cs.id)
29 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)
53 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;
55 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
56 IF TG_OP = 'INSERT' THEN
57 add_query := add_query || 'WHERE x.id = ' || NEW.target_copy || ' AND x.record = ' || NEW.peer_record || ';';
61 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
67 IF TG_OP = 'INSERT' THEN
69 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
70 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
78 -- handle items first, since with circulation activity
79 -- their statuses change frequently
80 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
82 IF OLD.location <> NEW.location OR
83 OLD.call_number <> NEW.call_number OR
84 OLD.status <> NEW.status OR
85 OLD.circ_lib <> NEW.circ_lib THEN
86 -- any of these could change visibility, but
87 -- we'll save some queries and not try to calculate
88 -- the change directly
93 IF OLD.deleted <> NEW.deleted THEN
101 IF OLD.opac_visible <> NEW.opac_visible THEN
102 IF OLD.opac_visible THEN
104 ELSIF NOT do_remove THEN -- handle edge case where deleted item
105 -- is also marked opac_visible
113 DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
116 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
124 IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
126 IF OLD.deleted AND NEW.deleted THEN -- do nothing
130 ELSIF NEW.deleted THEN -- remove rows
132 IF TG_TABLE_NAME = 'call_number' THEN
133 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
134 ELSIF TG_TABLE_NAME = 'record_entry' THEN
135 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
140 ELSIF OLD.deleted THEN -- add rows
142 IF TG_TABLE_NAME IN ('copy','unit') THEN
143 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
144 ELSIF TG_TABLE_NAME = 'call_number' THEN
145 add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';';
146 ELSIF TG_TABLE_NAME = 'record_entry' THEN
147 add_query := add_query || 'WHERE x.record = ' || NEW.id || ';';
157 IF TG_TABLE_NAME = 'call_number' THEN
159 IF OLD.record <> NEW.record THEN
160 -- call number is linked to different bib
161 remove_query := remove_query || 'call_number = ' || NEW.id || ');';
162 EXECUTE remove_query;
163 add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';';
171 IF TG_TABLE_NAME IN ('record_entry') THEN
172 RETURN NEW; -- don't have 'opac_visible'
175 -- actor.org_unit, asset.copy_location, asset.copy_status
176 IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
180 ELSIF NEW.opac_visible THEN -- add rows
182 IF TG_TABLE_NAME = 'org_unit' THEN
183 add_query := add_query || 'WHERE x.circ_lib = ' || NEW.id || ';';
184 ELSIF TG_TABLE_NAME = 'copy_location' THEN
185 add_query := add_query || 'WHERE x.location = ' || NEW.id || ';';
186 ELSIF TG_TABLE_NAME = 'copy_status' THEN
187 add_query := add_query || 'WHERE x.status = ' || NEW.id || ';';
194 IF TG_TABLE_NAME = 'org_unit' THEN
195 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
196 ELSIF TG_TABLE_NAME = 'copy_location' THEN
197 remove_query := remove_query || 'location = ' || NEW.id || ');';
198 ELSIF TG_TABLE_NAME = 'copy_status' THEN
199 remove_query := remove_query || 'status = ' || NEW.id || ');';
202 EXECUTE remove_query;
208 $func$ LANGUAGE PLPGSQL;