From 9be344b288f0b16c6e29ff138614f7f3f8a1164d Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Thu, 8 Dec 2011 16:49:56 -0500 Subject: [PATCH] Add attributes to in-db unapi acp, sunit, holdings_xml This gives us more flexibility in displaying due dates within search results or other contexts in which holdings are displayed. The age_protect, copy_id, and vol_id attributes are required by the record summary display. Signed-off-by: Dan Scott Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 56 ++++++++++++++++++++++++++++---- 1 file changed, 49 insertions(+), 7 deletions(-) diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 8ba8efc41d..4f8dd0443f 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -49,6 +49,7 @@ CREATE OR REPLACE FUNCTION unapi.ascecm ( obj_id BIGINT, format TEXT, ename TEXT CREATE OR REPLACE FUNCTION unapi.bre ( 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 NULL::XML $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 HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION unapi.mra ( 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 NULL::XML $F$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION unapi.circ ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT DEFAULT '-', depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION unapi.holdings_xml ( bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION unapi.biblio_record_entry_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE, title TEXT DEFAULT NULL, description TEXT DEFAULT NULL, creator TEXT DEFAULT NULL, update_ts TEXT DEFAULT NULL, unapi_url TEXT DEFAULT NULL, header_xml XML DEFAULT NULL ) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL; @@ -319,7 +320,7 @@ CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, d XMLELEMENT( name foreign_copies, (SELECT XMLAGG(acp) FROM ( - SELECT unapi.acp(p.target_copy,'xml','copy','{}'::TEXT[], $3, $4, $6, $7, FALSE) + SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $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 p.peer_record = $1 @@ -677,10 +678,10 @@ CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, 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, + 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, - circ_modifier, circ_as_type, opac_visible + circ_modifier, circ_as_type, opac_visible, age_protect ), 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), @@ -731,6 +732,18 @@ CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, )x) ) ELSE NULL + END, + CASE + WHEN ('circ' = ANY ($4)) THEN + XMLELEMENT( name current_circulation, + (SELECT XMLAGG(circ) FROM ( + SELECT unapi.circ( id, 'xml', 'circ', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE) + FROM action.circulation + WHERE target_copy = cp.id + AND checkin_time IS NULL + )x) + ) + ELSE NULL END ) FROM asset.copy cp @@ -743,11 +756,12 @@ CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT 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@acp/' || id AS id, + 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_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 + circ_modifier, circ_as_type, opac_visible, age_protect, + 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), @@ -797,6 +811,18 @@ CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT )x) ) ELSE NULL + END, + CASE + WHEN ('circ' = ANY ($4)) THEN + XMLELEMENT( name current_circulation, + (SELECT XMLAGG(circ) FROM ( + SELECT unapi.circ( id, 'xml', 'circ', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE) + FROM action.circulation + WHERE target_copy = cp.id + AND checkin_time IS NULL + )x) + ) + ELSE NULL END ) FROM serial.unit cp @@ -811,7 +837,7 @@ CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, 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, - o.shortname AS lib, + acn.id AS vol_id, o.shortname AS lib, o.opac_visible AS opac_visible, deleted, label, label_sortkey, label_class, record ), @@ -941,6 +967,22 @@ CREATE OR REPLACE FUNCTION unapi.mra ( obj_id BIGINT, format TEXT, ename TEXT, WHERE mra.id = $1; $F$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION unapi.circ (obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT DEFAULT '-', depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name circ, + XMLATTRIBUTES( + CASE WHEN $2 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@circ/' || id AS id, + xact_start, + due_date + ), + CASE WHEN ('aou' = ANY ($4)) THEN unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE) ELSE NULL END, + CASE WHEN ('acp' = ANY ($4)) THEN unapi.acp( circ_lib, $2, 'target_copy', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE) ELSE NULL END + ) + FROM action.circulation + WHERE id = $1; +$F$ LANGUAGE SQL; + /* -- Some test queries -- 2.11.0