3 INSERT INTO config.upgrade_log (version) VALUES ('0332'); -- gmc
5 CREATE TABLE asset.opac_visible_copies (
6 id BIGINT primary key, -- copy id
10 COMMENT ON TABLE asset.opac_visible_copies IS $$
11 Materialized view of copies that are visible in the OPAC, used by
12 search.query_parser_fts() to speed up OPAC visibility checks on large
13 databases. Contents are maintained by a set of triggers.
15 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
17 -- copy OPAC visibility materialized view
18 CREATE OR REPLACE FUNCTION asset.refresh_opac_visible_copies_mat_view () RETURNS VOID AS $$
20 TRUNCATE TABLE asset.opac_visible_copies;
22 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
23 SELECT cp.id, cp.circ_lib, cn.record
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)
39 COMMENT ON FUNCTION asset.refresh_opac_visible_copies_mat_view() IS $$
40 Rebuild the copy OPAC visibility cache. Useful during migrations.
43 -- and actually populate the table
44 SELECT asset.refresh_opac_visible_copies_mat_view();
46 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
50 do_add BOOLEAN := false;
51 do_remove BOOLEAN := false;
54 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
55 SELECT cp.id, cp.circ_lib, cn.record
57 JOIN asset.call_number cn ON (cn.id = cp.call_number)
58 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
59 JOIN asset.copy_location cl ON (cp.location = cl.id)
60 JOIN config.copy_status cs ON (cp.status = cs.id)
61 JOIN biblio.record_entry b ON (cn.record = b.id)
71 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE id IN ( SELECT id FROM asset.copy WHERE $$;
73 IF TG_OP = 'INSERT' THEN
75 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
76 add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
84 -- handle items first, since with circulation activity
85 -- their statuses change frequently
86 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
88 IF OLD.location <> NEW.location OR
89 OLD.call_number <> NEW.call_number OR
90 OLD.status <> NEW.status OR
91 OLD.circ_lib <> NEW.circ_lib THEN
92 -- any of these could change visibility, but
93 -- we'll save some queries and not try to calculate
94 -- the change directly
99 IF OLD.deleted <> NEW.deleted THEN
107 IF OLD.opac_visible <> NEW.opac_visible THEN
108 IF OLD.opac_visible THEN
110 ELSIF NOT do_remove THEN -- handle edge case where deleted item
111 -- is also marked opac_visible
119 DELETE FROM asset.opac_visible_copies WHERE id = NEW.id;
122 add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
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 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 IN ('copy','unit') THEN
149 add_query := add_query || 'AND cp.id = ' || NEW.id || ';';
150 ELSIF TG_TABLE_NAME = 'call_number' THEN
151 add_query := add_query || 'AND cp.call_number = ' || NEW.id || ';';
152 ELSIF TG_TABLE_NAME = 'record_entry' THEN
153 add_query := add_query || 'AND cn.record = ' || NEW.id || ';';
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_query := add_query || 'AND cp.call_number = ' || NEW.id || ';';
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_query := add_query || 'AND cp.circ_lib = ' || NEW.id || ';';
190 ELSIF TG_TABLE_NAME = 'copy_location' THEN
191 add_query := add_query || 'AND cp.location = ' || NEW.id || ';';
192 ELSIF TG_TABLE_NAME = 'copy_status' THEN
193 add_query := add_query || 'AND cp.status = ' || NEW.id || ';';
200 IF TG_TABLE_NAME = 'org_unit' THEN
201 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
202 ELSIF TG_TABLE_NAME = 'copy_location' THEN
203 remove_query := remove_query || 'location = ' || NEW.id || ');';
204 ELSIF TG_TABLE_NAME = 'copy_status' THEN
205 remove_query := remove_query || 'status = ' || NEW.id || ');';
208 EXECUTE remove_query;
214 $func$ LANGUAGE PLPGSQL;
215 COMMENT ON FUNCTION asset.cache_copy_visibility() IS $$
216 Trigger function to update the copy OPAC visiblity cache.
218 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
219 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
220 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
221 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy_location FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
222 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
223 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON config.copy_status FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
224 CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
226 CREATE OR REPLACE FUNCTION search.query_parser_fts (
231 param_statuses INT[],
232 param_locations INT[],
239 ) RETURNS SETOF search.search_result AS $func$
242 current_res search.search_result%ROWTYPE;
243 search_org_list INT[];
251 core_cursor REFCURSOR;
254 total_count INT := 0;
255 check_count INT := 0;
256 deleted_count INT := 0;
257 visible_count INT := 0;
258 excluded_count INT := 0;
262 check_limit := COALESCE( param_check, 1000 );
263 core_limit := COALESCE( param_limit, 25000 );
264 core_offset := COALESCE( param_offset, 0 );
266 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
268 IF param_search_ou > 0 THEN
269 IF param_depth IS NOT NULL THEN
270 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
272 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
274 ELSIF param_search_ou < 0 THEN
275 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
276 ELSIF param_search_ou = 0 THEN
277 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
280 OPEN core_cursor FOR EXECUTE param_query;
284 FETCH core_cursor INTO core_result;
286 EXIT WHEN total_count >= core_limit;
288 total_count := total_count + 1;
290 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
292 check_count := check_count + 1;
294 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
296 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
297 deleted_count := deleted_count + 1;
302 FROM biblio.record_entry b
303 JOIN config.bib_source s ON (b.source = s.id)
305 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
308 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
309 visible_count := visible_count + 1;
311 current_res.id = core_result.id;
312 current_res.rel = core_result.rel;
316 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
320 current_res.record = core_result.records[1];
322 current_res.record = NULL;
325 RETURN NEXT current_res;
331 FROM asset.call_number cn
332 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
333 JOIN asset.uri uri ON (map.uri = uri.id)
335 AND cn.label = '##URI##'
337 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
338 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
339 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
343 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
344 visible_count := visible_count + 1;
346 current_res.id = core_result.id;
347 current_res.rel = core_result.rel;
351 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
355 current_res.record = core_result.records[1];
357 current_res.record = NULL;
360 RETURN NEXT current_res;
365 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
368 FROM asset.call_number cn
369 JOIN asset.copy cp ON (cp.call_number = cn.id)
372 AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
373 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
374 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
378 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
379 excluded_count := excluded_count + 1;
385 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
388 FROM asset.call_number cn
389 JOIN asset.copy cp ON (cp.call_number = cn.id)
392 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
393 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
394 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
398 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
399 excluded_count := excluded_count + 1;
405 IF staff IS NULL OR NOT staff THEN
408 FROM asset.opac_visible_copies
409 WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
410 AND record IN ( SELECT * FROM search.explode_array( core_result.records ) )
414 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
415 excluded_count := excluded_count + 1;
422 FROM asset.call_number cn
423 JOIN asset.copy cp ON (cp.call_number = cn.id)
424 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
427 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
428 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
434 FROM asset.call_number cn
435 WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
439 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
440 excluded_count := excluded_count + 1;
448 visible_count := visible_count + 1;
450 current_res.id = core_result.id;
451 current_res.rel = core_result.rel;
455 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
459 current_res.record = core_result.records[1];
461 current_res.record = NULL;
464 RETURN NEXT current_res;
466 IF visible_count % 1000 = 0 THEN
467 -- RAISE NOTICE ' % visible so far ... ', visible_count;
472 current_res.id = NULL;
473 current_res.rel = NULL;
474 current_res.record = NULL;
475 current_res.total = total_count;
476 current_res.checked = check_count;
477 current_res.deleted = deleted_count;
478 current_res.visible = visible_count;
479 current_res.excluded = excluded_count;
483 RETURN NEXT current_res;
486 $func$ LANGUAGE PLPGSQL;