From bccad2bb7ab6207752b7967094624916e54b793e Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Sun, 11 Dec 2011 21:35:47 -0500 Subject: [PATCH] unapi: Sort volumes in holdings_xml by library, label In-database unapi currently returns volumes sorted strictly by label, but a more meaningful order would sort the volumes first by library (in alphabetical order), and then by label within each library. This commit makes it so. Signed-off-by: Dan Scott --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 3 +- ...ema.unapi_holdings_xml_sort_vols_by_library.sql | 99 ++++++++++++++++++++++ 2 files changed, 101 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_holdings_xml_sort_vols_by_library.sql diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 5b3e0931f9..580e763d0d 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -277,6 +277,7 @@ CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, d (SELECT XMLAGG(acn) FROM ( SELECT unapi.acn(acn.id,'xml','volume',array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE) FROM asset.call_number acn + INNER JOIN actor.org_unit aou ON aou.id = acn.owning_lib WHERE acn.record = $1 AND acn.deleted IS FALSE AND EXISTS ( @@ -294,7 +295,7 @@ CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, d ) aoud ON (acp.circ_lib = aoud.id) LIMIT 1 ) - ORDER BY label_sortkey + ORDER BY aou.name, label_sortkey LIMIT $6 OFFSET $7 )x) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_holdings_xml_sort_vols_by_library.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_holdings_xml_sort_vols_by_library.sql new file mode 100644 index 0000000000..279e99344f --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_holdings_xml_sort_vols_by_library.sql @@ -0,0 +1,99 @@ +-- Evergreen DB patch XXXX.schema.unapi_holdings_xml_sort_vols_by_library.sql +-- +-- Rather than sorting call numbers within holdings_xml strictly by their +-- labels, sort the results first by library and then by label. +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +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, + XMLATTRIBUTES( + CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id + ), + XMLELEMENT( + name counts, + (SELECT XMLAGG(XMLELEMENT::XML) FROM ( + SELECT XMLELEMENT( + name count, + XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) + )::text + FROM asset.opac_ou_record_copy_count($2, $1) + UNION + SELECT XMLELEMENT( + name count, + XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) + )::text + FROM asset.staff_ou_record_copy_count($2, $1) + ORDER BY 1 + )x) + ), + CASE + WHEN ('bmp' = ANY ($5)) THEN + XMLELEMENT( + name monograph_parts, + (SELECT XMLAGG(bmp) FROM ( + SELECT unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE) + FROM biblio.monograph_part + WHERE record = $1 + )x) + ) + ELSE NULL + END, + XMLELEMENT( + name volumes, + (SELECT XMLAGG(acn) FROM ( + SELECT unapi.acn(acn.id,'xml','volume',array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE) + FROM asset.call_number acn + INNER JOIN actor.org_unit aou ON aou.id = acn.owning_lib + WHERE acn.record = $1 + AND acn.deleted IS FALSE + AND EXISTS ( + SELECT 1 + FROM asset.copy acp + JOIN actor.org_unit_descendants( + $2, + (COALESCE( + $4, + (SELECT aout.depth + FROM actor.org_unit_type aout + JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.id = $2) + ) + )) + ) aoud ON (acp.circ_lib = aoud.id) + LIMIT 1 + ) + ORDER BY aou.name, label_sortkey + LIMIT $6 + OFFSET $7 + )x) + ), + CASE WHEN ('ssub' = ANY ($5)) THEN + XMLELEMENT( + name subscriptions, + (SELECT XMLAGG(ssub) FROM ( + SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE) + FROM serial.subscription + WHERE record_entry = $1 + )x) + ) + ELSE NULL END, + CASE WHEN ('acp' = ANY ($5)) THEN + XMLELEMENT( + name foreign_copies, + (SELECT XMLAGG(acp) FROM ( + SELECT unapi.acp(p.target_copy,'xml','copy','{"circ"}'::TEXT[], $3, $4, $6, $7, FALSE) + FROM biblio.peer_bib_copy_map p + JOIN asset.copy c ON (p.target_copy = c.id) + WHERE NOT c.deleted AND peer_record = $1 + )x) + ) + ELSE NULL END + ); +$F$ LANGUAGE SQL; + +COMMIT; -- 2.11.0