From b405e712249eeadc3b85d60484b1a19d2a382493 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 4 Oct 2011 14:50:30 -0400 Subject: [PATCH] Reify 2.0-2.1 upgrade script Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql | 2298 +++++++++++++++------------- 1 file changed, 1201 insertions(+), 1097 deletions(-) diff --git a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql index dbbd61e1e1..4c9686f82b 100644 --- a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql @@ -3492,310 +3492,6 @@ INSERT INTO biblio.peer_type (id,name) VALUES SELECT SETVAL('biblio.peer_type_id_seq'::TEXT, 100); -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 - -) RETURNS SETOF search.search_result AS $func$ -DECLARE - - current_res search.search_result%ROWTYPE; - search_org_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; - -BEGIN - - check_limit := COALESCE( param_check, 1000 ); - core_limit := COALESCE( param_limit, 25000 ); - core_offset := COALESCE( param_offset, 0 ); - - -- core_skip_chk := COALESCE( param_skip_chk, 1 ); - - IF param_search_ou > 0 THEN - IF param_depth IS NOT NULL THEN - SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); - ELSE - SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); - END IF; - ELSIF param_search_ou < 0 THEN - SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; - ELSIF param_search_ou = 0 THEN - -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. - 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; - - 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( search_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 - WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) - LIMIT 1; - - IF 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; - - 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; - CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$ SELECT XMLELEMENT( name holdings, @@ -7398,210 +7094,547 @@ CREATE OR REPLACE FUNCTION unapi.siss ( obj_id BIGINT, format TEXT, ename TEXT, ELSE NULL END ) - ) - FROM serial.issuance sstr - WHERE id = $1 - GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription; + ) + FROM serial.issuance sstr + WHERE id = $1 + GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name serial_item, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@sitem/' || id AS id, + 'tag:open-ils.org:U2@siss/' || issuance AS issuance, + date_expected, date_received + ), + CASE WHEN issuance IS NOT NULL AND ('siss' = ANY ($4)) THEN unapi.siss( issuance, $2, 'issuance', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, + CASE WHEN stream IS NOT NULL AND ('sstr' = ANY ($4)) THEN unapi.sstr( stream, $2, 'stream', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, + CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( stream, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, + CASE WHEN uri IS NOT NULL AND ('auri' = ANY ($4)) THEN unapi.auri( uri, $2, 'uri', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END +-- XMLELEMENT( name notes, +-- CASE +-- WHEN ('acpn' = ANY ($4)) THEN +-- (SELECT XMLAGG(acpn) FROM ( +-- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8) +-- FROM asset.copy_note +-- WHERE owning_copy = cp.id AND pub +-- )x) +-- ELSE NULL +-- END +-- ) + ) + FROM serial.item sitem + WHERE id = $1; +$F$ LANGUAGE SQL; + + +CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name monograph_part, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@bmp/' || id AS id, + id AS ident, + label, + label_sortkey, + 'tag:open-ils.org:U2@bre/' || record AS record + ), + CASE + WHEN ('acp' = ANY ($4)) THEN + XMLELEMENT( name copies, + (SELECT XMLAGG(acp) FROM ( + SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) + FROM asset.copy cp + JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id) + WHERE cpm.part = $1 + ORDER BY COALESCE(cp.copy_number,0), cp.barcode + LIMIT $7 + OFFSET $8 + )x) + ) + ELSE NULL + END, + CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END + ) + FROM biblio.monograph_part + WHERE id = $1 + GROUP BY id, label, label_sortkey, record; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name copy, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@acp/' || id AS id, + create_date, edit_date, copy_number, circulate, deposit, + ref, holdable, deleted, deposit_amount, price, barcode, + circ_modifier, circ_as_type, opac_visible + ), + unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE), + unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE), + unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8), + unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8), + CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END, + XMLELEMENT( name copy_notes, + CASE + WHEN ('acpn' = ANY ($4)) THEN + (SELECT XMLAGG(acpn) FROM ( + SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.copy_note + WHERE owning_copy = cp.id AND pub + )x) + ELSE NULL + END + ), + XMLELEMENT( name statcats, + CASE + WHEN ('ascecm' = ANY ($4)) THEN + (SELECT XMLAGG(ascecm) FROM ( + SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.stat_cat_entry_copy_map + WHERE owning_copy = cp.id + )x) + ELSE NULL + END + ), + XMLELEMENT( name foreign_records, + CASE + WHEN ('bre' = ANY ($4)) THEN + (SELECT XMLAGG(bre) FROM ( + SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE) + FROM biblio.peer_bib_copy_map + WHERE target_copy = cp.id + )x) + ELSE NULL + END + + ), + CASE + WHEN ('bmp' = ANY ($4)) THEN + XMLELEMENT( name monograph_parts, + (SELECT XMLAGG(bmp) FROM ( + SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.copy_part_map + WHERE target_copy = cp.id + )x) + ) + ELSE NULL + END + ) + FROM asset.copy cp + WHERE id = $1 + GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible; $F$ LANGUAGE SQL; -CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ +CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT XMLELEMENT( - name serial_item, + name serial_unit, XMLATTRIBUTES( CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, - 'tag:open-ils.org:U2@sitem/' || id AS id, - 'tag:open-ils.org:U2@siss/' || issuance AS issuance, - date_expected, date_received + 'tag:open-ils.org:U2@acp/' || id AS id, + create_date, edit_date, copy_number, circulate, deposit, + ref, holdable, deleted, deposit_amount, price, barcode, + circ_modifier, circ_as_type, opac_visible, status_changed_time, + floating, mint_condition, detailed_contents, sort_key, summary_contents, cost ), - CASE WHEN issuance IS NOT NULL AND ('siss' = ANY ($4)) THEN unapi.siss( issuance, $2, 'issuance', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, - CASE WHEN stream IS NOT NULL AND ('sstr' = ANY ($4)) THEN unapi.sstr( stream, $2, 'stream', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, - CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( stream, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, - CASE WHEN uri IS NOT NULL AND ('auri' = ANY ($4)) THEN unapi.auri( uri, $2, 'uri', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END --- XMLELEMENT( name notes, --- CASE --- WHEN ('acpn' = ANY ($4)) THEN --- (SELECT XMLAGG(acpn) FROM ( --- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8) --- FROM asset.copy_note --- WHERE owning_copy = cp.id AND pub --- )x) --- ELSE NULL --- END --- ) + unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE), + unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE), + unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8), + unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8), + CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END, + XMLELEMENT( name copy_notes, + CASE + WHEN ('acpn' = ANY ($4)) THEN + (SELECT XMLAGG(acpn) FROM ( + SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE) + FROM asset.copy_note + WHERE owning_copy = cp.id AND pub + )x) + ELSE NULL + END + ), + XMLELEMENT( name statcats, + CASE + WHEN ('ascecm' = ANY ($4)) THEN + (SELECT XMLAGG(ascecm) FROM ( + SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.stat_cat_entry_copy_map + WHERE owning_copy = cp.id + )x) + ELSE NULL + END + ), + XMLELEMENT( name foreign_records, + CASE + WHEN ('bre' = ANY ($4)) THEN + (SELECT XMLAGG(bre) FROM ( + SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE) + FROM biblio.peer_bib_copy_map + WHERE target_copy = cp.id + )x) + ELSE NULL + END + + ), + CASE + WHEN ('bmp' = ANY ($4)) THEN + XMLELEMENT( name monograph_parts, + (SELECT XMLAGG(bmp) FROM ( + SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.copy_part_map + WHERE target_copy = cp.id + )x) + ) + ELSE NULL + END ) - FROM serial.item sitem - WHERE id = $1; + FROM serial.unit cp + WHERE id = $1 + GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, floating, mint_condition, + deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible, status_changed_time, detailed_contents, sort_key, summary_contents, cost; $F$ LANGUAGE SQL; +INSERT INTO config.upgrade_log (version) VALUES ('0568'); -- miker for tsbere + +CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ +DECLARE + add_front TEXT; + add_back TEXT; + add_base_query TEXT; + add_peer_query TEXT; + remove_query TEXT; + do_add BOOLEAN := false; + do_remove BOOLEAN := false; +BEGIN + add_base_query := $$ + SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status + FROM asset.copy cp + JOIN asset.call_number cn ON (cn.id = cp.call_number) + JOIN actor.org_unit a ON (cp.circ_lib = a.id) + JOIN asset.copy_location cl ON (cp.location = cl.id) + JOIN config.copy_status cs ON (cp.status = cs.id) + JOIN biblio.record_entry b ON (cn.record = b.id) + WHERE NOT cp.deleted + AND NOT cn.deleted + AND NOT b.deleted + AND cs.opac_visible + AND cl.opac_visible + AND cp.opac_visible + AND a.opac_visible + $$; + add_peer_query := $$ + SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status + FROM asset.copy cp + JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id) + JOIN actor.org_unit a ON (cp.circ_lib = a.id) + JOIN asset.copy_location cl ON (cp.location = cl.id) + JOIN config.copy_status cs ON (cp.status = cs.id) + WHERE NOT cp.deleted + AND cs.opac_visible + AND cl.opac_visible + AND cp.opac_visible + AND a.opac_visible + $$; + add_front := $$ + INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record) + SELECT id, circ_lib, record FROM ( + $$; + add_back := $$ + ) AS x + $$; + + remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$; + + IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN + IF TG_OP = 'INSERT' THEN + add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.record = ' || NEW.peer_record; + EXECUTE add_front || add_peer_query || add_back; + RETURN NEW; + ELSE + remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';'; + EXECUTE remove_query; + RETURN OLD; + END IF; + END IF; + + IF TG_OP = 'INSERT' THEN + + IF TG_TABLE_NAME IN ('copy', 'unit') THEN + add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; + EXECUTE add_front || add_base_query || add_back; + END IF; + + RETURN NEW; + + END IF; + + -- handle items first, since with circulation activity + -- their statuses change frequently + IF TG_TABLE_NAME IN ('copy', 'unit') THEN + + IF OLD.location <> NEW.location OR + OLD.call_number <> NEW.call_number OR + OLD.status <> NEW.status OR + OLD.circ_lib <> NEW.circ_lib THEN + -- any of these could change visibility, but + -- we'll save some queries and not try to calculate + -- the change directly + do_remove := true; + do_add := true; + ELSE + + IF OLD.deleted <> NEW.deleted THEN + IF NEW.deleted THEN + do_remove := true; + ELSE + do_add := true; + END IF; + END IF; + + IF OLD.opac_visible <> NEW.opac_visible THEN + IF OLD.opac_visible THEN + do_remove := true; + ELSIF NOT do_remove THEN -- handle edge case where deleted item + -- is also marked opac_visible + do_add := true; + END IF; + END IF; + + END IF; + + IF do_remove THEN + DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id; + END IF; + IF do_add THEN + add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; + add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id; + EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + END IF; + + RETURN NEW; + + END IF; + + IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column + + IF OLD.deleted AND NEW.deleted THEN -- do nothing + + RETURN NEW; + + ELSIF NEW.deleted THEN -- remove rows + + IF TG_TABLE_NAME = 'call_number' THEN + DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id); + ELSIF TG_TABLE_NAME = 'record_entry' THEN + DELETE FROM asset.opac_visible_copies WHERE record = NEW.id; + END IF; + + RETURN NEW; + + ELSIF OLD.deleted THEN -- add rows + + IF TG_TABLE_NAME = 'call_number' THEN + add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; + EXECUTE add_front || add_base_query || add_back; + ELSIF TG_TABLE_NAME = 'record_entry' THEN + add_base_query := add_base_query || ' AND cn.record = ' || NEW.id; + add_peer_query := add_peer_query || ' AND pbcm.record = ' || NEW.id; + EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + END IF; + + RETURN NEW; + + END IF; + + END IF; + + IF TG_TABLE_NAME = 'call_number' THEN + + IF OLD.record <> NEW.record THEN + -- call number is linked to different bib + remove_query := remove_query || 'call_number = ' || NEW.id || ');'; + EXECUTE remove_query; + add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; + EXECUTE add_front || add_base_query || add_back; + END IF; + + RETURN NEW; -CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ - SELECT XMLELEMENT( - name monograph_part, - XMLATTRIBUTES( - CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, - 'tag:open-ils.org:U2@bmp/' || id AS id, - id AS ident, - label, - label_sortkey, - 'tag:open-ils.org:U2@bre/' || record AS record - ), - CASE - WHEN ('acp' = ANY ($4)) THEN - XMLELEMENT( name copies, - (SELECT XMLAGG(acp) FROM ( - SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) - FROM asset.copy cp - JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id) - WHERE cpm.part = $1 - ORDER BY COALESCE(cp.copy_number,0), cp.barcode - LIMIT $7 - OFFSET $8 - )x) - ) - ELSE NULL - END, - CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END - ) - FROM biblio.monograph_part - WHERE id = $1 - GROUP BY id, label, label_sortkey, record; -$F$ LANGUAGE SQL; + END IF; -CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ - SELECT XMLELEMENT( - name copy, - XMLATTRIBUTES( - CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, - 'tag:open-ils.org:U2@acp/' || id AS id, - create_date, edit_date, copy_number, circulate, deposit, - ref, holdable, deleted, deposit_amount, price, barcode, - circ_modifier, circ_as_type, opac_visible - ), - unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE), - unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE), - unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8), - unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8), - CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END, - XMLELEMENT( name copy_notes, - CASE - WHEN ('acpn' = ANY ($4)) THEN - (SELECT XMLAGG(acpn) FROM ( - SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) - FROM asset.copy_note - WHERE owning_copy = cp.id AND pub - )x) - ELSE NULL - END - ), - XMLELEMENT( name statcats, - CASE - WHEN ('ascecm' = ANY ($4)) THEN - (SELECT XMLAGG(ascecm) FROM ( - SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) - FROM asset.stat_cat_entry_copy_map - WHERE owning_copy = cp.id - )x) - ELSE NULL - END - ), - XMLELEMENT( name foreign_records, - CASE - WHEN ('bre' = ANY ($4)) THEN - (SELECT XMLAGG(bre) FROM ( - SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE) - FROM biblio.peer_bib_copy_map - WHERE target_copy = cp.id - )x) - ELSE NULL - END + IF TG_TABLE_NAME IN ('record_entry') THEN + RETURN NEW; -- don't have 'opac_visible' + END IF; - ), - CASE - WHEN ('bmp' = ANY ($4)) THEN - XMLELEMENT( name monograph_parts, - (SELECT XMLAGG(bmp) FROM ( - SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) - FROM asset.copy_part_map - WHERE target_copy = cp.id - )x) - ) - ELSE NULL - END - ) - FROM asset.copy cp - WHERE id = $1 - GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible; -$F$ LANGUAGE SQL; + -- actor.org_unit, asset.copy_location, asset.copy_status + IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing -CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + RETURN NEW; + + ELSIF NEW.opac_visible THEN -- add rows + + IF TG_TABLE_NAME = 'org_unit' THEN + add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id || ';'; + add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_location' THEN + add_base_query := add_base_query || ' AND cp.location = ' || NEW.id || ';'; + add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_status' THEN + add_base_query := add_base_query || ' AND cp.status = ' || NEW.id || ';'; + add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id || ';'; + END IF; + + EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + + ELSE -- delete rows + + IF TG_TABLE_NAME = 'org_unit' THEN + remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_location' THEN + remove_query := remove_query || 'location = ' || NEW.id || ');'; + ELSIF TG_TABLE_NAME = 'copy_status' THEN + remove_query := remove_query || 'status = ' || NEW.id || ');'; + END IF; + + EXECUTE remove_query; + + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0569'); --miker + +CREATE OR REPLACE FUNCTION unapi.auri ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT XMLELEMENT( - name serial_unit, + name uri, XMLATTRIBUTES( CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, - 'tag:open-ils.org:U2@acp/' || id AS id, - create_date, edit_date, copy_number, circulate, deposit, - ref, holdable, deleted, deposit_amount, price, barcode, - circ_modifier, circ_as_type, opac_visible, status_changed_time, - floating, mint_condition, detailed_contents, sort_key, summary_contents, cost - ), - unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE), - unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE), - unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8), - unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8), - CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END, - XMLELEMENT( name copy_notes, - CASE - WHEN ('acpn' = ANY ($4)) THEN - (SELECT XMLAGG(acpn) FROM ( - SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE) - FROM asset.copy_note - WHERE owning_copy = cp.id AND pub - )x) - ELSE NULL - END - ), - XMLELEMENT( name statcats, - CASE - WHEN ('ascecm' = ANY ($4)) THEN - (SELECT XMLAGG(ascecm) FROM ( - SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) - FROM asset.stat_cat_entry_copy_map - WHERE owning_copy = cp.id - )x) - ELSE NULL - END + 'tag:open-ils.org:U2@auri/' || uri.id AS id, + use_restriction, + href, + label ), - XMLELEMENT( name foreign_records, + XMLELEMENT( name copies, CASE - WHEN ('bre' = ANY ($4)) THEN - (SELECT XMLAGG(bre) FROM ( - SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE) - FROM biblio.peer_bib_copy_map - WHERE target_copy = cp.id - )x) + WHEN ('acn' = ANY ($4)) THEN + (SELECT XMLAGG(acn) FROM (SELECT unapi.acn( call_number, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'auri'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE uri = uri.id)x) ELSE NULL END + ) + ) AS x + FROM asset.uri uri + WHERE uri.id = $1 + GROUP BY uri.id, use_restriction, href, label; +$F$ LANGUAGE SQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0570'); + +-- Not everything in 1XX tags should become part of the authorsort field +-- ($0 for example). The list of subfields chosen here is a superset of all +-- the fields found in the LoC authority mappin definitions for 1XX fields. +-- Anyway, if more fields should be here, add them. + +UPDATE config.record_attr_definition + SET sf_list = 'abcdefgklmnopqrstvxyz' + WHERE name='authorsort' AND sf_list IS NULL; + +INSERT INTO config.upgrade_log (version) VALUES ('0571'); + +-- FIXME: add/check SQL statements to perform the upgrade +CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$ +DECLARE + normalizer RECORD; + facet_text TEXT; +BEGIN + facet_text := NEW.value; + + FOR normalizer IN + SELECT n.func AS func, + n.param_count AS param_count, + m.params AS params + FROM config.index_normalizer n + JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id) + WHERE m.field = NEW.field AND m.pos < 0 + ORDER BY m.pos LOOP + + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_literal( facet_text ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO facet_text; + + END LOOP; + + NEW.value = facet_text; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER facet_normalize_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE metabib.facet_normalize_trigger(); + + + +INSERT INTO config.upgrade_log (version) VALUES ('0578'); -- tsbere via miker + +CREATE OR REPLACE VIEW reporter.hold_request_record AS +SELECT id, + target, + hold_type, + CASE + WHEN hold_type = 'T' + THEN target + WHEN hold_type = 'I' + THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target) + WHEN hold_type = 'V' + THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target) + WHEN hold_type IN ('C','R','F') + THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target) + WHEN hold_type = 'M' + THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target) + WHEN hold_type = 'P' + THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target) + END AS bib_record + FROM action.hold_request ahr; - ), - CASE - WHEN ('bmp' = ANY ($4)) THEN - XMLELEMENT( name monograph_parts, - (SELECT XMLAGG(bmp) FROM ( - SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) - FROM asset.copy_part_map - WHERE target_copy = cp.id - )x) - ) - ELSE NULL - END - ) - FROM serial.unit cp - WHERE id = $1 - GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, floating, mint_condition, - deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible, status_changed_time, detailed_contents, sort_key, summary_contents, cost; -$F$ LANGUAGE SQL; +INSERT INTO config.upgrade_log (version) VALUES ('0583'); -INSERT INTO config.upgrade_log (version) VALUES ('0568'); -- miker for tsbere +CREATE OR REPLACE VIEW action.all_circulation AS + SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, + copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, + circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, + stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, + max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, + max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ + FROM action.aged_circulation + UNION ALL + SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year, + cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff, + circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule, + circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time, + circ.parent_circ + FROM action.circulation circ + JOIN asset.copy cp ON (circ.target_copy = cp.id) + JOIN asset.call_number cn ON (cp.call_number = cn.id) + JOIN actor.usr p ON (circ.usr = p.id) + LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id) + LEFT JOIN actor.usr_address b ON (p.billing_address = b.id); + + + +INSERT INTO config.upgrade_log (version) VALUES ('0590'); -- miker/tsbere CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ DECLARE @@ -7781,14 +7814,14 @@ BEGIN ELSIF NEW.opac_visible THEN -- add rows IF TG_TABLE_NAME = 'org_unit' THEN - add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id || ';'; - add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id || ';'; + add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id; + add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id; ELSIF TG_TABLE_NAME = 'copy_location' THEN - add_base_query := add_base_query || ' AND cp.location = ' || NEW.id || ';'; - add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id || ';'; + add_base_query := add_base_query || ' AND cp.location = ' || NEW.id; + add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id; ELSIF TG_TABLE_NAME = 'copy_status' THEN - add_base_query := add_base_query || ' AND cp.status = ' || NEW.id || ';'; - add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id || ';'; + add_base_query := add_base_query || ' AND cp.status = ' || NEW.id; + add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id; END IF; EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; @@ -7811,134 +7844,205 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -INSERT INTO config.upgrade_log (version) VALUES ('0569'); --miker - -CREATE OR REPLACE FUNCTION unapi.auri ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ - SELECT XMLELEMENT( - name uri, - XMLATTRIBUTES( - CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, - 'tag:open-ils.org:U2@auri/' || uri.id AS id, - use_restriction, - href, - label - ), - XMLELEMENT( name copies, - CASE - WHEN ('acn' = ANY ($4)) THEN - (SELECT XMLAGG(acn) FROM (SELECT unapi.acn( call_number, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'auri'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE uri = uri.id)x) - ELSE NULL - END - ) - ) AS x - FROM asset.uri uri - WHERE uri.id = $1 - GROUP BY uri.id, use_restriction, href, label; -$F$ LANGUAGE SQL; +INSERT INTO config.upgrade_log (version) VALUES ('0591'); -- berick/miker -INSERT INTO config.upgrade_log (version) VALUES ('0570'); +CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$ +DECLARE + c action.circulation%ROWTYPE; + view_age INTERVAL; + usr_view_age actor.usr_setting%ROWTYPE; + usr_view_start actor.usr_setting%ROWTYPE; +BEGIN + SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age'; + SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start'; --- Not everything in 1XX tags should become part of the authorsort field --- ($0 for example). The list of subfields chosen here is a superset of all --- the fields found in the LoC authority mappin definitions for 1XX fields. --- Anyway, if more fields should be here, add them. + IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN + -- User opted in and supplied a retention age + IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + ELSE + view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; + END IF; + ELSIF usr_view_start.value IS NOT NULL THEN + -- User opted in + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + ELSE + -- User did not opt in + RETURN; + END IF; -UPDATE config.record_attr_definition - SET sf_list = 'abcdefgklmnopqrstvxyz' - WHERE name='authorsort' AND sf_list IS NULL; + FOR c IN + SELECT * + FROM action.circulation + WHERE usr = usr_id + AND parent_circ IS NULL + AND xact_start > NOW() - view_age + ORDER BY xact_start DESC + LOOP + RETURN NEXT c; + END LOOP; -INSERT INTO config.upgrade_log (version) VALUES ('0571'); + RETURN; +END; +$func$ LANGUAGE PLPGSQL; --- FIXME: add/check SQL statements to perform the upgrade -CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$ +CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$ DECLARE - normalizer RECORD; - facet_text TEXT; + h action.hold_request%ROWTYPE; + view_age INTERVAL; + view_count INT; + usr_view_count actor.usr_setting%ROWTYPE; + usr_view_age actor.usr_setting%ROWTYPE; + usr_view_start actor.usr_setting%ROWTYPE; BEGIN - facet_text := NEW.value; + SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count'; + SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age'; + SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start'; - FOR normalizer IN - SELECT n.func AS func, - n.param_count AS param_count, - m.params AS params - FROM config.index_normalizer n - JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id) - WHERE m.field = NEW.field AND m.pos < 0 - ORDER BY m.pos LOOP + FOR h IN + SELECT * + FROM action.hold_request + WHERE usr = usr_id + AND fulfillment_time IS NULL + AND cancel_time IS NULL + ORDER BY request_time DESC + LOOP + RETURN NEXT h; + END LOOP; - EXECUTE 'SELECT ' || normalizer.func || '(' || - quote_literal( facet_text ) || - CASE - WHEN normalizer.param_count > 0 - THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') - ELSE '' - END || - ')' INTO facet_text; + IF usr_view_start.value IS NULL THEN + RETURN; + END IF; - END LOOP; + IF usr_view_age.value IS NOT NULL THEN + -- User opted in and supplied a retention age + IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + ELSE + view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; + END IF; + ELSE + -- User opted in + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + END IF; - NEW.value = facet_text; + IF usr_view_count.value IS NOT NULL THEN + view_count := oils_json_to_text(usr_view_count.value)::INT; + ELSE + view_count := 1000; + END IF; - RETURN NEW; + -- show some fulfilled/canceled holds + FOR h IN + SELECT * + FROM action.hold_request + WHERE usr = usr_id + AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL ) + AND request_time > NOW() - view_age + ORDER BY request_time DESC + LIMIT view_count + LOOP + RETURN NEXT h; + END LOOP; + + RETURN; END; -$$ LANGUAGE PLPGSQL; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0599'); -- miker/gmc + +UPDATE config.metabib_field +SET xpath = $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role/mods32:roleTerm[text()='creator'])]$$ +WHERE field_class = 'author' +AND name = 'other' +AND xpath = $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role)]$$ +AND format = 'mods32'; + +\qecho To reindex bibs that use the author|other index definition, +\qecho you can run something like this: +\qecho +\qecho SELECT metabib.reingest_metabib_field_entries(record) +\qecho FROM ( +\qecho SELECT DISTINCT record +\qecho FROM metabib.real_full_rec +\qecho WHERE tag IN ('600', '700', '720', '800') +\qecho AND subfield IN ('4', 'e') +\qecho ) a; + +-- Resolves an error in calculating copy counts for org lassos +-- Per LP 790329 +INSERT INTO config.upgrade_log (version) VALUES ('0603'); + +-- FIXME: add/check SQL statements to perform the upgrade +CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.copy_id) + GROUP BY 1,2,6; -CREATE TRIGGER facet_normalize_tgr - BEFORE UPDATE OR INSERT ON metabib.facet_entry - FOR EACH ROW EXECUTE PROCEDURE metabib.facet_normalize_trigger(); + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; -INSERT INTO config.upgrade_log (version) VALUES ('0578'); -- tsbere via miker +-- Staff record copy counts also triggered an SQL error for org lassos +-- Per LP790329 +-- +INSERT INTO config.upgrade_log (version) VALUES ('0604'); -CREATE OR REPLACE VIEW reporter.hold_request_record AS -SELECT id, - target, - hold_type, - CASE - WHEN hold_type = 'T' - THEN target - WHEN hold_type = 'I' - THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target) - WHEN hold_type = 'V' - THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target) - WHEN hold_type IN ('C','R','F') - THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target) - WHEN hold_type = 'M' - THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target) - WHEN hold_type = 'P' - THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target) - END AS bib_record - FROM action.hold_request ahr; +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; -INSERT INTO config.upgrade_log (version) VALUES ('0583'); + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) + GROUP BY 1,2,6; -CREATE OR REPLACE VIEW action.all_circulation AS - SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, - copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, - circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, - stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, - max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, - max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ - FROM action.aged_circulation - UNION ALL - SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year, - cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, - cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff, - circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, - circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule, - circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time, - circ.parent_circ - FROM action.circulation circ - JOIN asset.copy cp ON (circ.target_copy = cp.id) - JOIN asset.call_number cn ON (cp.call_number = cn.id) - JOIN actor.usr p ON (circ.usr = p.id) - LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id) - LEFT JOIN actor.usr_address b ON (p.billing_address = b.id); + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + END LOOP; + RETURN; +END; +$f$ LANGUAGE PLPGSQL; -INSERT INTO config.upgrade_log (version) VALUES ('0590'); -- miker/tsbere +INSERT INTO config.upgrade_log (version) VALUES ('0614'); --miker/phasefx CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ DECLARE @@ -7991,7 +8095,7 @@ BEGIN IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN IF TG_OP = 'INSERT' THEN - add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.record = ' || NEW.peer_record; + add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record; EXECUTE add_front || add_peer_query || add_back; RETURN NEW; ELSE @@ -8082,7 +8186,7 @@ BEGIN EXECUTE add_front || add_base_query || add_back; ELSIF TG_TABLE_NAME = 'record_entry' THEN add_base_query := add_base_query || ' AND cn.record = ' || NEW.id; - add_peer_query := add_peer_query || ' AND pbcm.record = ' || NEW.id; + add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id; EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; END IF; @@ -8148,611 +8252,610 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -INSERT INTO config.upgrade_log (version) VALUES ('0591'); -- berick/miker +INSERT INTO config.upgrade_log (version) VALUES ('0579'); -- superceded by 0620 +INSERT INTO config.upgrade_log (version) VALUES ('0620'); -- tsbere via miker -CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$ +CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$ DECLARE - c action.circulation%ROWTYPE; - view_age INTERVAL; - usr_view_age actor.usr_setting%ROWTYPE; - usr_view_start actor.usr_setting%ROWTYPE; + user_object actor.usr%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_status_object config.copy_status%ROWTYPE; + item_location_object asset.copy_location%ROWTYPE; + result action.circ_matrix_test_result; + circ_test action.found_circ_matrix_matchpoint; + circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE; + out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; + circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; + hold_ratio action.hold_stats%ROWTYPE; + penalty_type TEXT; + items_out INT; + context_org_list INT[]; + done BOOL := FALSE; BEGIN - SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age'; - SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start'; + -- Assume success unless we hit a failure condition + result.success := TRUE; - IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN - -- User opted in and supplied a retention age - IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN - view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); - ELSE - view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; - END IF; - ELSIF usr_view_start.value IS NOT NULL THEN - -- User opted in - view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); - ELSE - -- User did not opt in + -- Need user info to look up matchpoints + SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted; + + -- (Insta)Fail if we couldn't find the user + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; RETURN; END IF; - FOR c IN - SELECT * - FROM action.circulation - WHERE usr = usr_id - AND parent_circ IS NULL - AND xact_start > NOW() - view_age - ORDER BY xact_start DESC - LOOP - RETURN NEXT c; - END LOOP; + -- Need item info to look up matchpoints + SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted; - RETURN; -END; -$func$ LANGUAGE PLPGSQL; + -- (Insta)Fail if we couldn't find the item + IF item_object.id IS NULL THEN + result.fail_part := 'no_item'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; -CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$ -DECLARE - h action.hold_request%ROWTYPE; - view_age INTERVAL; - view_count INT; - usr_view_count actor.usr_setting%ROWTYPE; - usr_view_age actor.usr_setting%ROWTYPE; - usr_view_start actor.usr_setting%ROWTYPE; -BEGIN - SELECT * INTO usr_view_count FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_count'; - SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_age'; - SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.hold.retention_start'; + SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal); + + circ_matchpoint := circ_test.matchpoint; + result.matchpoint := circ_matchpoint.id; + result.circulate := circ_matchpoint.circulate; + result.duration_rule := circ_matchpoint.duration_rule; + result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule; + result.max_fine_rule := circ_matchpoint.max_fine_rule; + result.hard_due_date := circ_matchpoint.hard_due_date; + result.renewals := circ_matchpoint.renewals; + result.grace_period := circ_matchpoint.grace_period; + result.buildrows := circ_test.buildrows; + + -- (Insta)Fail if we couldn't find a matchpoint + IF circ_test.success = false THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- All failures before this point are non-recoverable + -- Below this point are possibly overridable failures + + -- Fail if the user is barred + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate + IF item_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item isn't in a circulateable status on a non-renewal + IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + -- Alternately, fail if the item isn't checked out on a renewal + ELSIF renewal AND item_object.status <> 1 THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate because of the shelving location + SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; + IF item_location_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy_location.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Use Circ OU for penalties and such + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_ou ); + + IF renewal THEN + penalty_type = '%RENEW%'; + ELSE + penalty_type = '%CIRC%'; + END IF; - FOR h IN - SELECT * - FROM action.hold_request - WHERE usr = usr_id - AND fulfillment_time IS NULL - AND cancel_time IS NULL - ORDER BY request_time DESC - LOOP - RETURN NEXT h; + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE penalty_type LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; END LOOP; - IF usr_view_start.value IS NULL THEN - RETURN; + -- Fail if the test is set to hard non-circulating + IF circ_matchpoint.circulate IS FALSE THEN + result.fail_part := 'config.circ_matrix_test.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; END IF; - IF usr_view_age.value IS NOT NULL THEN - -- User opted in and supplied a retention age - IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN - view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); - ELSE - view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; + -- Fail if the total copy-hold ratio is too low + IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; END IF; - ELSE - -- User opted in - view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); END IF; - IF usr_view_count.value IS NOT NULL THEN - view_count := oils_json_to_text(usr_view_count.value)::INT; - ELSE - view_count := 1000; + -- Fail if the available copy-hold ratio is too low + IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN + IF hold_ratio.hold_count IS NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + END IF; + IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; END IF; - -- show some fulfilled/canceled holds - FOR h IN - SELECT * - FROM action.hold_request - WHERE usr = usr_id - AND ( fulfillment_time IS NOT NULL OR cancel_time IS NOT NULL ) - AND request_time > NOW() - view_age - ORDER BY request_time DESC - LIMIT view_count - LOOP - RETURN NEXT h; + -- Fail if the user has too many items with specific circ_modifiers checked out + FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP + SELECT INTO items_out COUNT(*) + FROM action.circulation circ + JOIN asset.copy cp ON (cp.id = circ.target_copy) + WHERE circ.usr = match_user + AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) ) + AND circ.checkin_time IS NULL + AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) + AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id); + IF items_out >= out_by_circ_mod.items_out THEN + result.fail_part := 'config.circ_matrix_circ_mod_test'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; END LOOP; + -- If we passed everything, return the successful matchpoint + IF NOT done THEN + RETURN NEXT result; + END IF; + RETURN; END; -$func$ LANGUAGE PLPGSQL; +$func$ LANGUAGE plpgsql; -INSERT INTO config.upgrade_log (version) VALUES ('0599'); -- miker/gmc -UPDATE config.metabib_field -SET xpath = $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role/mods32:roleTerm[text()='creator'])]$$ -WHERE field_class = 'author' -AND name = 'other' -AND xpath = $$//mods32:mods/mods32:name[@type='personal' and not(mods32:role)]$$ -AND format = 'mods32'; -\qecho To reindex bibs that use the author|other index definition, -\qecho you can run something like this: -\qecho -\qecho SELECT metabib.reingest_metabib_field_entries(record) -\qecho FROM ( -\qecho SELECT DISTINCT record -\qecho FROM metabib.real_full_rec -\qecho WHERE tag IN ('600', '700', '720', '800') -\qecho AND subfield IN ('4', 'e') -\qecho ) a; +INSERT INTO config.upgrade_log (version) VALUES ('0628'); --- Resolves an error in calculating copy counts for org lassos --- Per LP 790329 -INSERT INTO config.upgrade_log (version) VALUES ('0603'); +-- acq.fund_combined_balance and acq.fund_spent_balance are unchanged, +-- however we need to drop them to recreate the other views. +-- we need to drop all our views because we change the number of columns +-- for example, debit_total does not need an encumberance column when we +-- have a sepearate total for that. --- FIXME: add/check SQL statements to perform the upgrade -CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ -DECLARE - ans RECORD; - trans INT; -BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; +DROP VIEW acq.fund_spent_balance; +DROP VIEW acq.fund_combined_balance; +DROP VIEW acq.fund_encumbrance_total; +DROP VIEW acq.fund_spent_total; +DROP VIEW acq.fund_debit_total; - FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP - RETURN QUERY - SELECT -1, - ans.id, - COUNT( av.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), - COUNT( av.id ), - trans - FROM - actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) - JOIN asset.copy cp ON (cp.id = av.copy_id) - GROUP BY 1,2,6; +CREATE OR REPLACE VIEW acq.fund_debit_total AS + SELECT fund.id AS fund, + sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount + FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund + GROUP BY fund.id; - IF NOT FOUND THEN - RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; - END IF; +CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS + SELECT + fund.id AS fund, + sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount + FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund + WHERE fund_debit.encumbrance GROUP BY fund.id; - END LOOP; - - RETURN; -END; -$f$ LANGUAGE PLPGSQL; +CREATE OR REPLACE VIEW acq.fund_spent_total AS + SELECT fund.id AS fund, + sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount + FROM acq.fund fund + LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund + WHERE NOT fund_debit.encumbrance + GROUP BY fund.id; +CREATE OR REPLACE VIEW acq.fund_combined_balance AS + SELECT c.fund, + c.amount - COALESCE(d.amount, 0.0) AS amount + FROM acq.fund_allocation_total c + LEFT JOIN acq.fund_debit_total d USING (fund); --- Staff record copy counts also triggered an SQL error for org lassos --- Per LP790329 --- -INSERT INTO config.upgrade_log (version) VALUES ('0604'); +CREATE OR REPLACE VIEW acq.fund_spent_balance AS + SELECT c.fund, + c.amount - COALESCE(d.amount,0.0) AS amount + FROM acq.fund_allocation_total c + LEFT JOIN acq.fund_spent_total d USING (fund); -CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ -DECLARE - ans RECORD; - trans INT; -BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; - FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP - RETURN QUERY - SELECT -1, - ans.id, - COUNT( cp.id ), - SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), - COUNT( cp.id ), - trans - FROM - actor.org_unit_descendants(ans.id) d - JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) - JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) - GROUP BY 1,2,6; - IF NOT FOUND THEN - RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; - END IF; +INSERT INTO config.upgrade_log (version) VALUES ('0631'); - END LOOP; +CREATE OR REPLACE FUNCTION search.query_parser_fts ( - RETURN; -END; -$f$ LANGUAGE PLPGSQL; + 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 + +) RETURNS SETOF search.search_result AS $func$ +DECLARE -INSERT INTO config.upgrade_log (version) VALUES ('0614'); --miker/phasefx + 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; -CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ -DECLARE - add_front TEXT; - add_back TEXT; - add_base_query TEXT; - add_peer_query TEXT; - remove_query TEXT; - do_add BOOLEAN := false; - do_remove BOOLEAN := false; BEGIN - add_base_query := $$ - SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status - FROM asset.copy cp - JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN actor.org_unit a ON (cp.circ_lib = a.id) - JOIN asset.copy_location cl ON (cp.location = cl.id) - JOIN config.copy_status cs ON (cp.status = cs.id) - JOIN biblio.record_entry b ON (cn.record = b.id) - WHERE NOT cp.deleted - AND NOT cn.deleted - AND NOT b.deleted - AND cs.opac_visible - AND cl.opac_visible - AND cp.opac_visible - AND a.opac_visible - $$; - add_peer_query := $$ - SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status - FROM asset.copy cp - JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id) - JOIN actor.org_unit a ON (cp.circ_lib = a.id) - JOIN asset.copy_location cl ON (cp.location = cl.id) - JOIN config.copy_status cs ON (cp.status = cs.id) - WHERE NOT cp.deleted - AND cs.opac_visible - AND cl.opac_visible - AND cp.opac_visible - AND a.opac_visible - $$; - add_front := $$ - INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record) - SELECT id, circ_lib, record FROM ( - $$; - add_back := $$ - ) AS x - $$; - - remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$; - IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN - IF TG_OP = 'INSERT' THEN - add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record; - EXECUTE add_front || add_peer_query || add_back; - RETURN NEW; + check_limit := COALESCE( param_check, 1000 ); + core_limit := COALESCE( param_limit, 25000 ); + core_offset := COALESCE( param_offset, 0 ); + + -- core_skip_chk := COALESCE( param_skip_chk, 1 ); + + IF param_search_ou > 0 THEN + IF param_depth IS NOT NULL THEN + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); ELSE - remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';'; - EXECUTE remove_query; - RETURN OLD; + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); END IF; - END IF; - IF TG_OP = 'INSERT' THEN + SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou ); - IF TG_TABLE_NAME IN ('copy', 'unit') THEN - add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; - EXECUTE add_front || add_base_query || add_back; - END IF; + ELSIF param_search_ou < 0 THEN + SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; - RETURN NEW; + FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP + SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int ); + luri_org_list := luri_org_list || tmp_int_list; + END LOOP; + + SELECT array_accum(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; - -- handle items first, since with circulation activity - -- their statuses change frequently - IF TG_TABLE_NAME IN ('copy', 'unit') THEN + OPEN core_cursor FOR EXECUTE param_query; - IF OLD.location <> NEW.location OR - OLD.call_number <> NEW.call_number OR - OLD.status <> NEW.status OR - OLD.circ_lib <> NEW.circ_lib THEN - -- any of these could change visibility, but - -- we'll save some queries and not try to calculate - -- the change directly - do_remove := true; - do_add := true; - ELSE + LOOP - IF OLD.deleted <> NEW.deleted THEN - IF NEW.deleted THEN - do_remove := true; - ELSE - do_add := true; - END IF; - END IF; + FETCH core_cursor INTO core_result; + EXIT WHEN NOT FOUND; + EXIT WHEN total_count >= core_limit; - IF OLD.opac_visible <> NEW.opac_visible THEN - IF OLD.opac_visible THEN - do_remove := true; - ELSIF NOT do_remove THEN -- handle edge case where deleted item - -- is also marked opac_visible - do_add := true; - END IF; - END IF; + total_count := total_count + 1; - END IF; + CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset; - IF do_remove THEN - DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id; - END IF; - IF do_add THEN - add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; - add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id; - EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + check_count := check_count + 1; + + 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; - RETURN NEW; + 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 ) ); - END IF; + IF FOUND THEN + -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; + visible_count := visible_count + 1; - IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column - - IF OLD.deleted AND NEW.deleted THEN -- do nothing + current_res.id = core_result.id; + current_res.rel = core_result.rel; - RETURN NEW; - - ELSIF NEW.deleted THEN -- remove rows - - IF TG_TABLE_NAME = 'call_number' THEN - DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id); - ELSIF TG_TABLE_NAME = 'record_entry' THEN - DELETE FROM asset.opac_visible_copies WHERE record = NEW.id; + 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; - - RETURN NEW; - - ELSIF OLD.deleted THEN -- add rows - - IF TG_TABLE_NAME = 'call_number' THEN - add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; - EXECUTE add_front || add_base_query || add_back; - ELSIF TG_TABLE_NAME = 'record_entry' THEN - add_base_query := add_base_query || ' AND cn.record = ' || NEW.id; - add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id; - EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; END IF; - - RETURN NEW; - - END IF; - - END IF; - IF TG_TABLE_NAME = 'call_number' THEN + RETURN NEXT current_res; - IF OLD.record <> NEW.record THEN - -- call number is linked to different bib - remove_query := remove_query || 'call_number = ' || NEW.id || ');'; - EXECUTE remove_query; - add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; - EXECUTE add_front || add_base_query || add_back; + CONTINUE; END IF; - RETURN NEW; + 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; - END IF; + IF FOUND THEN + -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; + visible_count := visible_count + 1; - IF TG_TABLE_NAME IN ('record_entry') THEN - RETURN NEW; -- don't have 'opac_visible' - END IF; + current_res.id = core_result.id; + current_res.rel = core_result.rel; - -- actor.org_unit, asset.copy_location, asset.copy_status - IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing + 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; - RETURN NEW; + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; - ELSIF NEW.opac_visible THEN -- add rows + RETURN NEXT current_res; - IF TG_TABLE_NAME = 'org_unit' THEN - add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id; - add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id; - ELSIF TG_TABLE_NAME = 'copy_location' THEN - add_base_query := add_base_query || ' AND cp.location = ' || NEW.id; - add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id; - ELSIF TG_TABLE_NAME = 'copy_status' THEN - add_base_query := add_base_query || ' AND cp.status = ' || NEW.id; - add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id; + CONTINUE; END IF; - - EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; - - ELSE -- delete rows - IF TG_TABLE_NAME = 'org_unit' THEN - remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';'; - ELSIF TG_TABLE_NAME = 'copy_location' THEN - remove_query := remove_query || 'location = ' || NEW.id || ');'; - ELSIF TG_TABLE_NAME = 'copy_status' THEN - remove_query := remove_query || 'status = ' || NEW.id || ');'; - END IF; - - EXECUTE remove_query; - - END IF; - - RETURN NEW; -END; -$func$ LANGUAGE PLPGSQL; + IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN -INSERT INTO config.upgrade_log (version) VALUES ('0579'); -- superceded by 0620 -INSERT INTO config.upgrade_log (version) VALUES ('0620'); -- tsbere via miker + 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; -CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$ -DECLARE - user_object actor.usr%ROWTYPE; - standing_penalty config.standing_penalty%ROWTYPE; - item_object asset.copy%ROWTYPE; - item_status_object config.copy_status%ROWTYPE; - item_location_object asset.copy_location%ROWTYPE; - result action.circ_matrix_test_result; - circ_test action.found_circ_matrix_matchpoint; - circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE; - out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; - circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; - hold_ratio action.hold_stats%ROWTYPE; - penalty_type TEXT; - items_out INT; - context_org_list INT[]; - done BOOL := FALSE; -BEGIN - -- Assume success unless we hit a failure condition - result.success := TRUE; + 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; - -- Need user info to look up matchpoints - SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted; + 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; - -- (Insta)Fail if we couldn't find the user - IF user_object.id IS NULL THEN - result.fail_part := 'no_user'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; + 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; - -- Need item info to look up matchpoints - SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted; + 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; - -- (Insta)Fail if we couldn't find the item - IF item_object.id IS NULL THEN - result.fail_part := 'no_item'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; + END IF; - SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal); + IF staff IS NULL OR NOT staff THEN - circ_matchpoint := circ_test.matchpoint; - result.matchpoint := circ_matchpoint.id; - result.circulate := circ_matchpoint.circulate; - result.duration_rule := circ_matchpoint.duration_rule; - result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule; - result.max_fine_rule := circ_matchpoint.max_fine_rule; - result.hard_due_date := circ_matchpoint.hard_due_date; - result.renewals := circ_matchpoint.renewals; - result.grace_period := circ_matchpoint.grace_period; - result.buildrows := circ_test.buildrows; + 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; - -- (Insta)Fail if we couldn't find a matchpoint - IF circ_test.success = false THEN - result.fail_part := 'no_matchpoint'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - RETURN; - END IF; + 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; - -- All failures before this point are non-recoverable - -- Below this point are possibly overridable failures + IF NOT FOUND THEN - -- Fail if the user is barred - IF user_object.barred IS TRUE THEN - result.fail_part := 'actor.usr.barred'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; - -- Fail if the item can't circulate - IF item_object.circulate IS FALSE THEN - result.fail_part := 'asset.copy.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; + ELSE - -- Fail if the item isn't in a circulateable status on a non-renewal - IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN - result.fail_part := 'asset.copy.status'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - -- Alternately, fail if the item isn't checked out on a renewal - ELSIF renewal AND item_object.status <> 1 THEN - result.fail_part := 'asset.copy.status'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; + 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; - -- Fail if the item can't circulate because of the shelving location - SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; - IF item_location_object.circulate IS FALSE THEN - result.fail_part := 'asset.copy_location.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; + IF NOT FOUND THEN - -- Use Circ OU for penalties and such - SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_ou ); + 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 renewal THEN - penalty_type = '%RENEW%'; - ELSE - penalty_type = '%CIRC%'; - END IF; + IF NOT FOUND THEN - FOR standing_penalty IN - SELECT DISTINCT csp.* - FROM actor.usr_standing_penalty usp - JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) - WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) - AND (usp.stop_date IS NULL or usp.stop_date > NOW()) - AND csp.block_list LIKE penalty_type LOOP + 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; - result.fail_part := standing_penalty.name; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END LOOP; + IF 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; - -- Fail if the test is set to hard non-circulating - IF circ_matchpoint.circulate IS FALSE THEN - result.fail_part := 'config.circ_matrix_test.circulate'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; - END IF; + END IF; - -- Fail if the total copy-hold ratio is too low - IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN - SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); - IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN - result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; END IF; - END IF; - -- Fail if the available copy-hold ratio is too low - IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN - IF hold_ratio.hold_count IS NULL THEN - SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + 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 hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN - result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; END IF; - END IF; - -- Fail if the user has too many items with specific circ_modifiers checked out - FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP - SELECT INTO items_out COUNT(*) - FROM action.circulation circ - JOIN asset.copy cp ON (cp.id = circ.target_copy) - WHERE circ.usr = match_user - AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) ) - AND circ.checkin_time IS NULL - AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) - AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id); - IF items_out >= out_by_circ_mod.items_out THEN - result.fail_part := 'config.circ_matrix_circ_mod_test'; - result.success := FALSE; - done := TRUE; - RETURN NEXT result; + RETURN NEXT current_res; + + IF visible_count % 1000 = 0 THEN + -- RAISE NOTICE ' % visible so far ... ', visible_count; END IF; + END LOOP; - -- If we passed everything, return the successful matchpoint - IF NOT done THEN - RETURN NEXT result; - END IF; + 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; - RETURN; END; -$func$ LANGUAGE plpgsql; +$func$ LANGUAGE PLPGSQL; + + +INSERT INTO config.upgrade_log (version) VALUES ('0633'); +INSERT INTO config.upgrade_log (version) VALUES ('0634'); COMMIT; +--0633 +INSERT into config.org_unit_setting_type +( name, grp, label, description, datatype ) VALUES +( + 'print.custom_js_file', 'circ', + oils_i18n_gettext( + 'print.custom_js_file', + 'Printing: Custom Javascript File', + 'coust', + 'label' + ), + oils_i18n_gettext( + 'print.custom_js_file', + 'Full URL path to a Javascript File to be loaded when printing. Should' + || ' implement a print_custom function for DOM manipulation. Can change' + || ' the value of the do_print variable to false to cancel printing.', + 'coust', + 'description' + ), + 'string' + ); + + +--0634 +INSERT INTO permission.perm_list ( id, code, description ) VALUES + ( 513, 'DEBUG_CLIENT', oils_i18n_gettext( 513, + 'Allows a user to use debug functions in the staff client', 'ppl', 'description' )); + + -- 0529 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES @@ -8814,3 +8917,4 @@ UPDATE metabib.record_attr WHERE x.record = metabib.record_attr.id; + -- 2.11.0