Schema Changes: 2.6.2
Upgrade: 2.6.1 → 2.6.2
This release applied 2 migration(s) to the database schema.
| ID | Type | Description |
|---|---|---|
function |
staff uri visibility |
|
function |
staff uri visibility 2.5 |
Migration Details
882 — staff uri visibility
Type: function
View SQL
CREATE OR REPLACE FUNCTION search.query_parser_fts (
param_search_ou INT,
param_depth INT,
param_query TEXT,
param_statuses INT[],
param_locations INT[],
param_offset INT,
param_check INT,
param_limit INT,
metarecord BOOL,
staff BOOL,
deleted_search BOOL,
param_pref_ou INT DEFAULT NULL
) RETURNS SETOF search.search_result AS $func$
DECLARE
current_res search.search_result%ROWTYPE;
search_org_list INT[];
luri_org_list INT[];
tmp_int_list INT[];
check_limit INT;
core_limit INT;
core_offset INT;
tmp_int INT;
core_result RECORD;
core_cursor REFCURSOR;
core_rel_query TEXT;
total_count INT := 0;
check_count INT := 0;
deleted_count INT := 0;
visible_count INT := 0;
excluded_count INT := 0;
luri_as_copy BOOL;
check_limit := COALESCE( param_check, 1000 );
core_limit := COALESCE( param_limit, 25000 );
core_offset := COALESCE( param_offset, 0 );
SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
-- core_skip_chk := COALESCE( param_skip_chk, 1 );
IF param_search_ou > 0 THEN
IF param_depth IS NOT NULL THEN
SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
ELSE
SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
END IF;
IF luri_as_copy THEN
SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
ELSE
SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
END IF;
ELSIF param_search_ou < 0 THEN
SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
IF luri_as_copy THEN
SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
ELSE
SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
END IF;
luri_org_list := luri_org_list || tmp_int_list;
END LOOP;
SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
ELSIF param_search_ou = 0 THEN
-- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
END IF;
IF param_pref_ou IS NOT NULL THEN
IF luri_as_copy THEN
SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
ELSE
SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
END IF;
luri_org_list := luri_org_list || tmp_int_list;
END IF;
OPEN core_cursor FOR EXECUTE param_query;
LOOP
FETCH core_cursor INTO core_result;
EXIT WHEN NOT FOUND;
EXIT WHEN total_count >= core_limit;
total_count := total_count + 1;
CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
check_count := check_count + 1;
IF NOT deleted_search THEN
PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
IF NOT FOUND THEN
-- RAISE NOTICE ' % were all deleted ... ', core_result.records;
deleted_count := deleted_count + 1;
CONTINUE;
END IF;
PERFORM 1
FROM biblio.record_entry b
JOIN config.bib_source s ON (b.source = s.id)
WHERE s.transcendant
AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
IF FOUND THEN
-- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
visible_count := visible_count + 1;
current_res.id = core_result.id;
current_res.rel = core_result.rel;
tmp_int := 1;
IF metarecord THEN
SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
END IF;
IF tmp_int = 1 THEN
current_res.record = core_result.records[1];
ELSE
current_res.record = NULL;
END IF;
RETURN NEXT current_res;
CONTINUE;
END IF;
PERFORM 1
FROM asset.call_number cn
JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
JOIN asset.uri uri ON (map.uri = uri.id)
WHERE NOT cn.deleted
AND cn.label = '##URI##'
AND uri.active
AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
LIMIT 1;
IF FOUND THEN
-- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
visible_count := visible_count + 1;
current_res.id = core_result.id;
current_res.rel = core_result.rel;
tmp_int := 1;
IF metarecord THEN
SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
END IF;
IF tmp_int = 1 THEN
current_res.record = core_result.records[1];
ELSE
current_res.record = NULL;
END IF;
RETURN NEXT current_res;
CONTINUE;
END IF;
IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
PERFORM 1
FROM asset.call_number cn
JOIN asset.copy cp ON (cp.call_number = cn.id)
WHERE NOT cn.deleted
AND NOT cp.deleted
AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
LIMIT 1;
IF NOT FOUND THEN
PERFORM 1
FROM biblio.peer_bib_copy_map pr
JOIN asset.copy cp ON (cp.id = pr.target_copy)
WHERE NOT cp.deleted
AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
LIMIT 1;
IF NOT FOUND THEN
-- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
excluded_count := excluded_count + 1;
CONTINUE;
END IF;
END IF;
END IF;
IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
PERFORM 1
FROM asset.call_number cn
JOIN asset.copy cp ON (cp.call_number = cn.id)
WHERE NOT cn.deleted
AND NOT cp.deleted
AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
LIMIT 1;
IF NOT FOUND THEN
PERFORM 1
FROM biblio.peer_bib_copy_map pr
JOIN asset.copy cp ON (cp.id = pr.target_copy)
WHERE NOT cp.deleted
AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
LIMIT 1;
IF NOT FOUND THEN
-- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
excluded_count := excluded_count + 1;
CONTINUE;
END IF;
END IF;
END IF;
IF staff IS NULL OR NOT staff THEN
PERFORM 1
FROM asset.opac_visible_copies
WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
AND record IN ( SELECT * FROM unnest( core_result.records ) )
LIMIT 1;
IF NOT FOUND THEN
PERFORM 1
FROM biblio.peer_bib_copy_map pr
JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
LIMIT 1;
IF NOT FOUND THEN
-- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
excluded_count := excluded_count + 1;
CONTINUE;
END IF;
END IF;
ELSE
PERFORM 1
FROM asset.call_number cn
JOIN asset.copy cp ON (cp.call_number = cn.id)
WHERE NOT cn.deleted
AND NOT cp.deleted
AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
LIMIT 1;
IF NOT FOUND THEN
PERFORM 1
FROM biblio.peer_bib_copy_map pr
JOIN asset.copy cp ON (cp.id = pr.target_copy)
WHERE NOT cp.deleted
AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
LIMIT 1;
IF NOT FOUND THEN
PERFORM 1
FROM asset.call_number cn
JOIN asset.copy cp ON (cp.call_number = cn.id)
WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
AND NOT cp.deleted
LIMIT 1;
IF NOT FOUND THEN
-- Recheck Located URI visibility in the case of no "foreign" copies
PERFORM 1
FROM asset.call_number cn
JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
JOIN asset.uri uri ON (map.uri = uri.id)
WHERE NOT cn.deleted
AND cn.label = '##URI##'
AND uri.active
AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
LIMIT 1;
IF FOUND THEN
-- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
excluded_count := excluded_count + 1;
CONTINUE;
END IF;
ELSE
-- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
excluded_count := excluded_count + 1;
CONTINUE;
END IF;
END IF;
END IF;
END IF;
END IF;
visible_count := visible_count + 1;
current_res.id = core_result.id;
current_res.rel = core_result.rel;
tmp_int := 1;
IF metarecord THEN
SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
END IF;
IF tmp_int = 1 THEN
current_res.record = core_result.records[1];
ELSE
current_res.record = NULL;
END IF;
RETURN NEXT current_res;
IF visible_count % 1000 = 0 THEN
-- RAISE NOTICE ' % visible so far ... ', visible_count;
END IF;
END LOOP;
current_res.id = NULL;
current_res.rel = NULL;
current_res.record = NULL;
current_res.total = total_count;
current_res.checked = check_count;
current_res.deleted = deleted_count;
current_res.visible = visible_count;
current_res.excluded = excluded_count;
CLOSE core_cursor;
RETURN NEXT current_res;
END;
$func$ LANGUAGE PLPGSQL;