1 --Upgrade Script for 3.0.2 to 3.0.3
2 \set eg_version '''3.0.3'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.0.3', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1085', :eg_version);
8 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
10 copy_row asset.copy%ROWTYPE;
11 lgroup_map asset.copy_location_group_map%ROWTYPE;
12 attr_set INT[] := '{}'::INT[];
14 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
16 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
17 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
18 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
19 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
23 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
25 FROM asset.call_number
26 WHERE id = copy_row.call_number;
28 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
29 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
36 DROP FUNCTION IF EXISTS biblio.calculate_bib_visibility_attribute_set ( BIGINT );
37 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$
39 bib_row biblio.record_entry%ROWTYPE;
40 cn_row asset.call_number%ROWTYPE;
41 attr_set INT[] := '{}'::INT[];
43 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
46 IF new_source IS NOT NULL THEN
47 attr_set := attr_set || search.calculate_visibility_attribute(new_source, 'bib_source');
49 ELSIF bib_row.source IS NOT NULL THEN
50 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
55 FROM asset.call_number
60 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
67 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
69 ocn asset.call_number%ROWTYPE;
70 ncn asset.call_number%ROWTYPE;
75 SELECT enabled = FALSE INTO dobib FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc';
77 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
78 IF TG_OP = 'INSERT' THEN
79 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
82 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
86 ELSIF TG_OP = 'DELETE' THEN
87 DELETE FROM asset.copy_vis_attr_cache
88 WHERE record = OLD.peer_record AND target_copy = OLD.target_copy;
94 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
95 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
96 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
97 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
100 asset.calculate_copy_visibility_attribute_set(NEW.id)
102 ELSIF TG_TABLE_NAME = 'record_entry' THEN
103 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
104 ELSIF TG_TABLE_NAME = 'call_number' AND NEW.label = '##URI##' AND dobib THEN -- New located URI
105 UPDATE biblio.record_entry
106 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
107 WHERE id = NEW.record;
114 -- handle items first, since with circulation activity
115 -- their statuses change frequently
116 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
118 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
119 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
123 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
125 IF OLD.deleted <> NEW.deleted THEN
127 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
129 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
132 asset.calculate_copy_visibility_attribute_set(NEW.id)
137 ELSIF OLD.location <> NEW.location OR
138 OLD.status <> NEW.status OR
139 OLD.opac_visible <> NEW.opac_visible OR
140 OLD.circ_lib <> NEW.circ_lib OR
141 OLD.call_number <> NEW.call_number
143 IF OLD.call_number <> NEW.call_number THEN -- Special check since it's more expensive than the next branch
144 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
146 IF ncn.record <> ocn.record THEN
147 -- We have to use a record-specific WHERE clause
148 -- to avoid modifying the entries for peer-bib copies.
149 UPDATE asset.copy_vis_attr_cache
150 SET target_copy = NEW.id,
152 WHERE target_copy = OLD.id
153 AND record = ocn.record;
157 -- Any of these could change visibility, but
158 -- we'll save some queries and not try to calculate
159 -- the change directly. We want to update peer-bib
160 -- entries in this case, unlike above.
161 UPDATE asset.copy_vis_attr_cache
162 SET target_copy = NEW.id,
163 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
164 WHERE target_copy = OLD.id;
168 ELSIF TG_TABLE_NAME = 'call_number' THEN
170 IF TG_OP = 'DELETE' AND OLD.label = '##URI##' AND dobib THEN -- really deleted located URI, if the delete protection rule is disabled...
171 UPDATE biblio.record_entry
172 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
173 WHERE id = OLD.record;
177 IF OLD.label = '##URI##' AND dobib THEN -- Located URI
178 IF OLD.deleted <> NEW.deleted OR OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
179 UPDATE biblio.record_entry
180 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
181 WHERE id = NEW.record;
183 IF OLD.record <> NEW.record THEN -- maybe on merge?
184 UPDATE biblio.record_entry
185 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
186 WHERE id = OLD.record;
190 ELSIF OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
191 UPDATE asset.copy_vis_attr_cache
192 SET record = NEW.record,
193 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
194 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
195 AND record = OLD.record;
199 ELSIF TG_TABLE_NAME = 'record_entry' AND OLD.source IS DISTINCT FROM NEW.source THEN -- Only handles ON UPDATE, INSERT above
200 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
205 $func$ LANGUAGE PLPGSQL;
207 DROP TRIGGER z_opac_vis_mat_view_tgr ON asset.call_number;
208 DROP TRIGGER z_opac_vis_mat_view_tgr ON biblio.record_entry;
209 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();
210 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();
213 SELECT evergreen.upgrade_deps_block_check('1086', :eg_version);
215 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
216 SELECT '!()'::TEXT; -- For now, as there's no way to cause a location group to hide all copies.
218 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
219 FROM asset.copy_location_group
220 WHERE NOT opac_visible;
222 $f$ LANGUAGE SQL IMMUTABLE;
226 SELECT evergreen.upgrade_deps_block_check('1087', :eg_version);
228 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
230 copy_flags TEXT; -- "c" attr
232 owning_lib TEXT; -- "c" attr
233 circ_lib TEXT; -- "c" attr
234 status TEXT; -- "c" attr
235 location TEXT; -- "c" attr
236 location_group TEXT; -- "c" attr
238 luri_org TEXT; -- "b" attr
239 bib_sources TEXT; -- "b" attr
241 bib_tests TEXT := '';
243 copy_flags := asset.all_visible_flags(); -- Will always have at least one
245 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
247 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
248 status := NULLIF(asset.status_default(),'!()');
249 location := NULLIF(asset.location_default(),'!()');
250 location_group := NULLIF(asset.location_group_default(),'!()');
252 -- LURIs will be handled at the perl layer directly
253 -- luri_org := NULLIF(asset.luri_org_default(),'!()');
254 bib_sources := NULLIF(asset.bib_source_default(),'()');
257 IF luri_org IS NOT NULL AND bib_sources IS NOT NULL THEN
258 bib_tests := '('||ARRAY_TO_STRING( ARRAY[luri_org,bib_sources], '|')||')&('||luri_org||')&';
259 ELSIF luri_org IS NOT NULL THEN
260 bib_tests := luri_org || '&';
261 ELSIF bib_sources IS NOT NULL THEN
262 bib_tests := bib_sources || '|';
265 RETURN QUERY SELECT bib_tests,
266 '('||ARRAY_TO_STRING(
267 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
271 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
277 UPDATE biblio.record_entry
278 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id)
280 SELECT DISTINCT cn.record
281 FROM asset.call_number cn
283 AND cn.label = '##URI##'
286 FROM asset.uri_call_number_map m
287 WHERE m.call_number = cn.id
290 SELECT id FROM biblio.record_entry WHERE source IS NOT NULL