From 2a8cc90f11def99973e008c1ba6e66253ed96fb1 Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Thu, 7 Mar 2013 08:30:02 -0500 Subject: [PATCH] LP#1112723 Consider copy-level opac_visible flag when ranking The current unAPI calls consider opac_visibility of the status, but not the copy itself. If you have a record with copies set to opac_visible = false, you might see few or no copies in the OPAC results list, as these invisible copies may be the first five returned, and they then crowd out the visible copies. Signed-off-by: Dan Wells Signed-off-by: Josh Stompro Signed-off-by: Ben Shum --- .../lib/OpenILS/WWW/EGCatLoader/Record.pm | 4 +- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 41 +++- .../XXXX.schema.rank_cp_visibility.sql | 209 ++++++++++++++++++ 3 files changed, 243 insertions(+), 11 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.rank_cp_visibility.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm index be1a402d53..418f8841bd 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm @@ -272,8 +272,8 @@ sub mk_copy_query { } ); push(@{$query->{order_by}}, - { class => "acp", field => 'status', - transform => 'evergreen.rank_cp_status' + { class => "acp", field => 'id', + transform => 'evergreen.rank_cp' } ); diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index b4da6a0536..effa2b9879 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -41,8 +41,24 @@ RETURNS INTEGER AS $$ ); $$ LANGUAGE SQL STABLE; -CREATE OR REPLACE FUNCTION evergreen.rank_cp_status(status INT) +-- this version exists mainly to accommodate JSON query transform limitations +-- (the transform argument must be an IDL field, not an entire row/object) +-- XXX is there another way? +CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy_id BIGINT) RETURNS INTEGER AS $$ +DECLARE + copy asset.copy%ROWTYPE; +BEGIN + SELECT * INTO copy FROM asset.copy WHERE id = copy_id; + RETURN evergreen.rank_cp(copy); +END; +$$ LANGUAGE PLPGSQL STABLE; + +CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy asset.copy) +RETURNS INTEGER AS $$ +DECLARE + rank INT; +BEGIN WITH totally_available AS ( SELECT id, 0 AS avail_rank FROM config.copy_status @@ -58,11 +74,17 @@ RETURNS INTEGER AS $$ OR id = 1 -- "Checked out" ) SELECT COALESCE( - (SELECT avail_rank FROM totally_available WHERE $1 IN (id)), - (SELECT avail_rank FROM almost_available WHERE $1 IN (id)), + CASE WHEN NOT copy.opac_visible THEN 100 END, + (SELECT avail_rank FROM totally_available WHERE copy.status IN (id)), + CASE WHEN copy.holdable THEN + (SELECT avail_rank FROM almost_available WHERE copy.status IN (id)) + END, 100 - ); -$$ LANGUAGE SQL STABLE; + ) INTO rank; + + RETURN rank; +END; +$$ LANGUAGE PLPGSQL STABLE; CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( bibid BIGINT[], @@ -119,6 +141,7 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( SELECT acn.id, aou.name, acn.label_sortkey, + evergreen.rank_cp(acp), RANK() OVER w FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) @@ -132,7 +155,7 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( FROM asset.opac_visible_copies WHERE copy_id = acp.id AND record = acn.record ) ELSE TRUE END - GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id + GROUP BY acn.id, evergreen.rank_cp(acp), aou.name, acn.label_sortkey, aou.id WINDOW w AS ( ORDER BY COALESCE( @@ -145,7 +168,7 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id), 1000 ), - evergreen.rank_cp_status(acp.status) + evergreen.rank_cp(acp) ) ) AS ua GROUP BY ua.id, ua.name, ua.label_sortkey @@ -1122,7 +1145,7 @@ CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, XMLELEMENT( name copies, (SELECT XMLAGG(acp ORDER BY rank_avail) FROM ( SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE), - evergreen.rank_cp_status(cp.status) AS rank_avail + evergreen.rank_cp(cp) AS rank_avail FROM asset.copy cp JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id) WHERE cp.call_number = acn.id @@ -1136,7 +1159,7 @@ CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, XMLELEMENT( name copies, (SELECT XMLAGG(acp ORDER BY rank_avail) FROM ( SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE), - evergreen.rank_cp_status(cp.status) AS rank_avail + evergreen.rank_cp(cp) AS rank_avail FROM asset.copy cp JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id) WHERE cp.call_number = acn.id diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.rank_cp_visibility.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.rank_cp_visibility.sql new file mode 100644 index 0000000000..87c0da1ca2 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.rank_cp_visibility.sql @@ -0,0 +1,209 @@ +-- Evergreen DB patch XXXX.schema.rank_cp_visibility.sql +-- +-- rank_cp() is meant to return the most-available copies, so it needs to +-- factor in the opac_visible flag on the copies themselves +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- function is being expanded and renamed, so drop the old version +DROP FUNCTION IF EXISTS evergreen.rank_cp_status(INT); + +-- this version exists mainly to accommodate JSON query transform limitations +-- (the transform argument must be an IDL field, not an entire row/object) +-- XXX is there another way? +CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy_id BIGINT) +RETURNS INTEGER AS $$ +DECLARE + copy asset.copy%ROWTYPE; +BEGIN + SELECT * INTO copy FROM asset.copy WHERE id = copy_id; + RETURN evergreen.rank_cp(copy); +END; +$$ LANGUAGE PLPGSQL STABLE; + +CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy asset.copy) +RETURNS INTEGER AS $$ +DECLARE + rank INT; +BEGIN + WITH totally_available AS ( + SELECT id, 0 AS avail_rank + FROM config.copy_status + WHERE opac_visible IS TRUE + AND copy_active IS TRUE + AND id != 1 -- "Checked out" + ), almost_available AS ( + SELECT id, 10 AS avail_rank + FROM config.copy_status + WHERE holdable IS TRUE + AND opac_visible IS TRUE + AND copy_active IS FALSE + OR id = 1 -- "Checked out" + ) + SELECT COALESCE( + CASE WHEN NOT copy.opac_visible THEN 100 END, + (SELECT avail_rank FROM totally_available WHERE copy.status IN (id)), + CASE WHEN copy.holdable THEN + (SELECT avail_rank FROM almost_available WHERE copy.status IN (id)) + END, + 100 + ) INTO rank; + + RETURN rank; +END; +$$ LANGUAGE PLPGSQL STABLE; + +CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( + bibid BIGINT[], + ouid INT, + depth INT DEFAULT NULL, + slimit HSTORE DEFAULT NULL, + soffset HSTORE DEFAULT NULL, + pref_lib INT DEFAULT NULL, + includes TEXT[] DEFAULT NULL::TEXT[] +) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ + WITH RECURSIVE ou_depth AS ( + SELECT COALESCE( + $3, + ( + SELECT depth + FROM actor.org_unit_type aout + INNER JOIN actor.org_unit ou ON ou_type = aout.id + WHERE ou.id = $2 + ) + ) AS depth + ), descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ad ON (ad.id = ou.id), + ou_depth + WHERE ad.depth = ou_depth.depth + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $2 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ), descendants as ( + SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id) + ) + + SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( + SELECT acn.id, aou.name, acn.label_sortkey, + evergreen.rank_cp(acp), + RANK() OVER w + FROM asset.call_number acn + JOIN asset.copy acp ON (acn.id = acp.call_number) + JOIN descendants AS aou ON (acp.circ_lib = aou.id) + WHERE acn.record = ANY ($1) + AND acn.deleted IS FALSE + AND acp.deleted IS FALSE + AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN + EXISTS ( + SELECT 1 + FROM asset.opac_visible_copies + WHERE copy_id = acp.id AND record = acn.record + ) ELSE TRUE END + GROUP BY acn.id, evergreen.rank_cp(acp), aou.name, acn.label_sortkey, aou.id + WINDOW w AS ( + ORDER BY + COALESCE( + CASE WHEN aou.id = $2 THEN -20000 END, + CASE WHEN aou.id = $6 THEN -10000 END, + (SELECT distance - 5000 + FROM actor.org_unit_descendants_distance($6) as x + WHERE x.id = aou.id AND $6 IN ( + SELECT q.id FROM actor.org_unit_descendants($2) as q)), + (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id), + 1000 + ), + evergreen.rank_cp(acp) + ) + ) AS ua + GROUP BY ua.id, ua.name, ua.label_sortkey + ORDER BY rank, ua.name, ua.label_sortkey + LIMIT ($4 -> 'acn')::INT + OFFSET ($5 -> 'acn')::INT; +$$ LANGUAGE SQL STABLE ROWS 10; + +CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name volume, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@acn/' || acn.id AS id, + acn.id AS vol_id, o.shortname AS lib, + o.opac_visible AS opac_visible, + deleted, label, label_sortkey, label_class, record + ), + unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8), + CASE + WHEN ('acp' = ANY ($4)) THEN + CASE WHEN $6 IS NOT NULL THEN + XMLELEMENT( name copies, + (SELECT XMLAGG(acp ORDER BY rank_avail) FROM ( + SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE), + evergreen.rank_cp(cp) AS rank_avail + FROM asset.copy cp + JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id) + WHERE cp.call_number = acn.id + AND cp.deleted IS FALSE + ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode + LIMIT ($7 -> 'acp')::INT + OFFSET ($8 -> 'acp')::INT + )x) + ) + ELSE + XMLELEMENT( name copies, + (SELECT XMLAGG(acp ORDER BY rank_avail) FROM ( + SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE), + evergreen.rank_cp(cp) AS rank_avail + FROM asset.copy cp + JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id) + WHERE cp.call_number = acn.id + AND cp.deleted IS FALSE + ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode + LIMIT ($7 -> 'acp')::INT + OFFSET ($8 -> 'acp')::INT + )x) + ) + END + ELSE NULL + END, + XMLELEMENT( + name uris, + (SELECT XMLAGG(auri) FROM (SELECT unapi.auri(uri,'xml','uri', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE call_number = acn.id)x) + ), + unapi.acnp( acn.prefix, 'marcxml', 'prefix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE), + unapi.acns( acn.suffix, 'marcxml', 'suffix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE), + CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( acn.record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END + ) AS x + FROM asset.call_number acn + JOIN actor.org_unit o ON (o.id = acn.owning_lib) + WHERE acn.id = $1 + AND acn.deleted IS FALSE + GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix; +$F$ LANGUAGE SQL STABLE; + +COMMIT; -- 2.43.2