3 INSERT INTO config.upgrade_log (version) VALUES ('0515'); -- miker
5 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
9 do_add BOOLEAN := false;
10 do_remove BOOLEAN := false;
13 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
14 SELECT id, circ_lib, record FROM (
15 SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number
17 JOIN asset.call_number cn ON (cn.id = cp.call_number)
18 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
19 JOIN asset.copy_location cl ON (cp.location = cl.id)
20 JOIN config.copy_status cs ON (cp.status = cs.id)
21 JOIN biblio.record_entry b ON (cn.record = b.id)
30 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number
32 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
33 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
34 JOIN asset.copy_location cl ON (cp.location = cl.id)
35 JOIN config.copy_status cs ON (cp.status = cs.id)
45 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;
47 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
48 IF TG_OP = 'INSERT' THEN
49 add_query := add_query || 'WHERE x.id = ' || NEW.target_copy || ' AND x.record = ' || NEW.peer_record || ';';
53 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
59 IF TG_OP = 'INSERT' THEN
61 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
62 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
70 -- handle items first, since with circulation activity
71 -- their statuses change frequently
72 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
74 IF OLD.location <> NEW.location OR
75 OLD.call_number <> NEW.call_number OR
76 OLD.status <> NEW.status OR
77 OLD.circ_lib <> NEW.circ_lib THEN
78 -- any of these could change visibility, but
79 -- we'll save some queries and not try to calculate
80 -- the change directly
85 IF OLD.deleted <> NEW.deleted THEN
93 IF OLD.opac_visible <> NEW.opac_visible THEN
94 IF OLD.opac_visible THEN
96 ELSIF NOT do_remove THEN -- handle edge case where deleted item
97 -- is also marked opac_visible
105 DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
108 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
116 IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
118 IF OLD.deleted AND NEW.deleted THEN -- do nothing
122 ELSIF NEW.deleted THEN -- remove rows
124 IF TG_TABLE_NAME = 'call_number' THEN
125 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
126 ELSIF TG_TABLE_NAME = 'record_entry' THEN
127 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
132 ELSIF OLD.deleted THEN -- add rows
134 IF TG_TABLE_NAME IN ('copy','unit') THEN
135 add_query := add_query || 'WHERE x.id = ' || NEW.id || ';';
136 ELSIF TG_TABLE_NAME = 'call_number' THEN
137 add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';';
138 ELSIF TG_TABLE_NAME = 'record_entry' THEN
139 add_query := add_query || 'WHERE x.record = ' || NEW.id || ';';
149 IF TG_TABLE_NAME = 'call_number' THEN
151 IF OLD.record <> NEW.record THEN
152 -- call number is linked to different bib
153 remove_query := remove_query || 'call_number = ' || NEW.id || ');';
154 EXECUTE remove_query;
155 add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';';
163 IF TG_TABLE_NAME IN ('record_entry') THEN
164 RETURN NEW; -- don't have 'opac_visible'
167 -- actor.org_unit, asset.copy_location, asset.copy_status
168 IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
172 ELSIF NEW.opac_visible THEN -- add rows
174 IF TG_TABLE_NAME = 'org_unit' THEN
175 add_query := add_query || 'AND cp.circ_lib = ' || NEW.id || ';';
176 ELSIF TG_TABLE_NAME = 'copy_location' THEN
177 add_query := add_query || 'AND cp.location = ' || NEW.id || ';';
178 ELSIF TG_TABLE_NAME = 'copy_status' THEN
179 add_query := add_query || 'AND cp.status = ' || NEW.id || ';';
186 IF TG_TABLE_NAME = 'org_unit' THEN
187 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
188 ELSIF TG_TABLE_NAME = 'copy_location' THEN
189 remove_query := remove_query || 'location = ' || NEW.id || ');';
190 ELSIF TG_TABLE_NAME = 'copy_status' THEN
191 remove_query := remove_query || 'status = ' || NEW.id || ');';
194 EXECUTE remove_query;
200 $func$ LANGUAGE PLPGSQL;