1 -- Evergreen DB patch 0690.schema.unapi_limit_rank.sql
3 -- Rewrite the in-database unapi functions to include per-object limits and
4 -- offsets, such as a maximum number of copies and call numbers for given
5 -- bib record via the HSTORE syntax (for example, 'acn => 5, acp => 10' would
6 -- limit to a maximum of 5 call numbers for the bib, with up to 10 copies per
9 -- Add some notion of "preferred library" that will provide copy counts
10 -- and optionally affect the sorting of returned copies.
12 -- Sort copies by availability, preferring the most available copies.
14 -- Return located URIs.
19 -- check whether patch can be applied
20 SELECT evergreen.upgrade_deps_block_check('0690', :eg_version);
22 -- The simplest way to apply all of these changes is just to replace the unapi
23 -- schema entirely -- the following is a copy of 990.schema.unapi.sql with
24 -- the initial COMMIT in place in case the upgrade_deps_block_check fails;
25 -- if it does, then the attempt to create the unapi schema in the following
26 -- transaction will also fail. Not graceful, but safe!
27 DROP SCHEMA IF EXISTS unapi CASCADE;
32 CREATE OR REPLACE FUNCTION evergreen.org_top()
33 RETURNS SETOF actor.org_unit AS $$
34 SELECT * FROM actor.org_unit WHERE parent_ou IS NULL LIMIT 1;
35 $$ LANGUAGE SQL STABLE
38 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT)
39 RETURNS anyarray AS $$
40 SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
41 $$ LANGUAGE SQL STABLE;
43 CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT DEFAULT NULL)
46 SELECT id, distance FROM actor.org_unit_descendants_distance($2)
49 (SELECT -10000 FROM actor.org_unit
50 WHERE $1 = $3 AND id = $3 AND $2 IN (
51 SELECT id FROM actor.org_unit WHERE parent_ou IS NULL
54 (SELECT distance FROM search_libs WHERE id = $1),
57 $$ LANGUAGE SQL STABLE;
59 CREATE OR REPLACE FUNCTION evergreen.rank_cp_status(status INT)
61 WITH totally_available AS (
62 SELECT id, 0 AS avail_rank
63 FROM config.copy_status
64 WHERE opac_visible IS TRUE
65 AND copy_active IS TRUE
66 AND id != 1 -- "Checked out"
67 ), almost_available AS (
68 SELECT id, 10 AS avail_rank
69 FROM config.copy_status
70 WHERE holdable IS TRUE
71 AND opac_visible IS TRUE
72 AND copy_active IS FALSE
73 OR id = 1 -- "Checked out"
76 (SELECT avail_rank FROM totally_available WHERE $1 IN (id)),
77 (SELECT avail_rank FROM almost_available WHERE $1 IN (id)),
80 $$ LANGUAGE SQL STABLE;
82 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
85 depth INT DEFAULT NULL,
86 slimit HSTORE DEFAULT NULL,
87 soffset HSTORE DEFAULT NULL,
88 pref_lib INT DEFAULT NULL
89 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
90 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
91 SELECT acn.id, aou.name, acn.label_sortkey,
92 evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
94 FROM asset.call_number acn
95 JOIN asset.copy acp ON (acn.id = acp.call_number)
96 JOIN actor.org_unit_descendants( $2, COALESCE(
99 FROM actor.org_unit_type aout
100 INNER JOIN actor.org_unit ou ON ou_type = aout.id
103 ) AS aou ON (acp.circ_lib = aou.id)
104 WHERE acn.record = $1
105 AND acn.deleted IS FALSE
106 AND acp.deleted IS FALSE
107 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
109 ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
112 GROUP BY ua.id, ua.name, ua.label_sortkey
113 ORDER BY rank, ua.name, ua.label_sortkey
114 LIMIT ($4 -> 'acn')::INT
115 OFFSET ($5 -> 'acn')::INT;
119 CREATE OR REPLACE FUNCTION evergreen.located_uris (
122 pref_lib INT DEFAULT NULL
123 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
124 SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
125 FROM asset.call_number acn
126 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
127 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
128 INNER JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
129 WHERE acn.record = $1
130 AND acn.deleted IS FALSE
131 AND auri.active IS TRUE
133 SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
134 FROM asset.call_number acn
135 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
136 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
137 INNER JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
138 WHERE acn.record = $1
139 AND acn.deleted IS FALSE
140 AND auri.active IS TRUE;
144 CREATE TABLE unapi.bre_output_layout (
145 name TEXT PRIMARY KEY,
146 transform TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
147 mime_type TEXT NOT NULL,
148 feed_top TEXT NOT NULL,
149 holdings_element TEXT,
151 description_element TEXT,
152 creator_element TEXT,
153 update_ts_element TEXT
156 INSERT INTO unapi.bre_output_layout
157 (name, transform, mime_type, holdings_element, feed_top, title_element, description_element, creator_element, update_ts_element)
159 ('holdings_xml', NULL, 'application/xml', NULL, 'hxml', NULL, NULL, NULL, NULL),
160 ('marcxml', 'marcxml', 'application/marc+xml', 'record', 'collection', NULL, NULL, NULL, NULL),
161 ('mods32', 'mods32', 'application/mods+xml', 'mods', 'modsCollection', NULL, NULL, NULL, NULL)
164 -- Dummy functions, so we can create the real ones out of order
165 CREATE OR REPLACE FUNCTION unapi.aou ( 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 STABLE;
166 CREATE OR REPLACE FUNCTION unapi.acnp ( 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 STABLE;
167 CREATE OR REPLACE FUNCTION unapi.acns ( 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 STABLE;
168 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 NULL::XML $F$ LANGUAGE SQL STABLE;
169 CREATE OR REPLACE FUNCTION unapi.ssub ( 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 STABLE;
170 CREATE OR REPLACE FUNCTION unapi.sdist ( 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 STABLE;
171 CREATE OR REPLACE FUNCTION unapi.sstr ( 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 STABLE;
172 CREATE OR REPLACE FUNCTION unapi.sitem ( 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 STABLE;
173 CREATE OR REPLACE FUNCTION unapi.sunit ( 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 STABLE;
174 CREATE OR REPLACE FUNCTION unapi.sisum ( 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 STABLE;
175 CREATE OR REPLACE FUNCTION unapi.sbsum ( 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 STABLE;
176 CREATE OR REPLACE FUNCTION unapi.sssum ( 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 STABLE;
177 CREATE OR REPLACE FUNCTION unapi.siss ( 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 STABLE;
178 CREATE OR REPLACE FUNCTION unapi.auri ( 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 STABLE;
179 CREATE OR REPLACE FUNCTION unapi.acp ( 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 STABLE;
180 CREATE OR REPLACE FUNCTION unapi.acpn ( 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 STABLE;
181 CREATE OR REPLACE FUNCTION unapi.acl ( 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 STABLE;
182 CREATE OR REPLACE FUNCTION unapi.ccs ( 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 STABLE;
183 CREATE OR REPLACE FUNCTION unapi.ascecm ( 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 STABLE;
184 CREATE OR REPLACE FUNCTION unapi.bre (
190 depth INT DEFAULT NULL,
191 slimit HSTORE DEFAULT NULL,
192 soffset HSTORE DEFAULT NULL,
193 include_xmlns BOOL DEFAULT TRUE,
194 pref_lib INT DEFAULT NULL
196 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
197 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 STABLE;
198 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 STABLE;
199 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 STABLE;
201 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
205 depth INT DEFAULT NULL,
206 includes TEXT[] DEFAULT NULL::TEXT[],
207 slimit HSTORE DEFAULT NULL,
208 soffset HSTORE DEFAULT NULL,
209 include_xmlns BOOL DEFAULT TRUE,
210 pref_lib INT DEFAULT NULL
212 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
214 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 STABLE;
216 CREATE OR REPLACE FUNCTION unapi.memoize (classname TEXT, 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$
222 'id' || COALESCE(obj_id::TEXT,'') ||
223 'format' || COALESCE(format::TEXT,'') ||
224 'ename' || COALESCE(ename::TEXT,'') ||
225 'includes' || COALESCE(includes::TEXT,'{}'::TEXT[]::TEXT) ||
226 'org' || COALESCE(org::TEXT,'') ||
227 'depth' || COALESCE(depth::TEXT,'') ||
228 'slimit' || COALESCE(slimit::TEXT,'') ||
229 'soffset' || COALESCE(soffset::TEXT,'') ||
230 'include_xmlns' || COALESCE(include_xmlns::TEXT,'');
231 -- RAISE NOTICE 'memoize key: %', key;
234 -- RAISE NOTICE 'memoize hash: %', key;
236 -- XXX cache logic ... memcached? table?
238 EXECUTE $$SELECT unapi.$$ || classname || $$( $1, $2, $3, $4, $5, $6, $7, $8, $9);$$ INTO output USING obj_id, format, ename, includes, org, depth, slimit, soffset, include_xmlns;
241 $F$ LANGUAGE PLPGSQL STABLE;
243 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$
245 layout unapi.bre_output_layout%ROWTYPE;
246 transform config.xml_transform%ROWTYPE;
249 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
254 IF org = '-' OR org IS NULL THEN
255 SELECT shortname INTO org FROM evergreen.org_top();
258 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
259 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
261 IF layout.name IS NULL THEN
265 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
266 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
268 -- Gather the bib xml
269 SELECT XMLAGG( unapi.bre(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
271 IF layout.title_element IS NOT NULL THEN
272 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
275 IF layout.description_element IS NOT NULL THEN
276 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
279 IF layout.creator_element IS NOT NULL THEN
280 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
283 IF layout.update_ts_element IS NOT NULL THEN
284 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.update_ts_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, update_ts;
287 IF unapi_url IS NOT NULL THEN
288 EXECUTE $$SELECT XMLCONCAT( XMLELEMENT( name link, XMLATTRIBUTES( 'http://www.w3.org/1999/xhtml' AS xmlns, 'unapi-server' AS rel, $1 AS href, 'unapi' AS title)), $2)$$ INTO tmp_xml USING unapi_url, tmp_xml::XML;
291 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
293 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
294 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
295 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
300 $F$ LANGUAGE PLPGSQL STABLE;
302 CREATE OR REPLACE FUNCTION unapi.bre (
308 depth INT DEFAULT NULL,
309 slimit HSTORE DEFAULT NULL,
310 soffset HSTORE DEFAULT NULL,
311 include_xmlns BOOL DEFAULT TRUE,
312 pref_lib INT DEFAULT NULL
316 me biblio.record_entry%ROWTYPE;
317 layout unapi.bre_output_layout%ROWTYPE;
318 xfrm config.xml_transform%ROWTYPE;
327 IF org = '-' OR org IS NULL THEN
328 SELECT shortname INTO org FROM evergreen.org_top();
331 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
337 IF format = 'holdings_xml' THEN -- the special case
338 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
342 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
344 IF layout.name IS NULL THEN
348 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
350 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
352 -- grab SVF if we need them
353 IF ('mra' = ANY (includes)) THEN
354 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
359 -- grab holdings if we need them
360 IF ('holdings_xml' = ANY (includes)) THEN
361 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns, pref_lib);
367 -- generate our item node
370 IF format = 'marcxml' THEN
372 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
373 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
376 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
379 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
381 IF axml IS NOT NULL THEN
382 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
385 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
386 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
389 IF ('bre.unapi' = ANY (includes)) THEN
390 output := REGEXP_REPLACE(
392 '</' || top_el || '>(.*?)',
396 'http://www.w3.org/1999/xhtml' AS xmlns,
398 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
400 )::TEXT || '</' || top_el || E'>\\1'
406 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
409 $F$ LANGUAGE PLPGSQL STABLE;
411 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
415 depth INT DEFAULT NULL,
416 includes TEXT[] DEFAULT NULL::TEXT[],
417 slimit HSTORE DEFAULT NULL,
418 soffset HSTORE DEFAULT NULL,
419 include_xmlns BOOL DEFAULT TRUE,
420 pref_lib INT DEFAULT NULL
426 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
427 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id
431 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
434 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
436 FROM asset.opac_ou_record_copy_count($2, $1)
440 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
442 FROM asset.staff_ou_record_copy_count($2, $1)
446 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
448 FROM asset.opac_ou_record_copy_count($9, $1)
453 WHEN ('bmp' = ANY ($5)) THEN
455 name monograph_parts,
456 (SELECT XMLAGG(bmp) FROM (
457 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)
458 FROM biblio.monograph_part
466 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
468 SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
469 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
472 SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), 0, name, label_sortkey
473 FROM evergreen.located_uris($1, $2, $9) AS uris
476 CASE WHEN ('ssub' = ANY ($5)) THEN
479 (SELECT XMLAGG(ssub) FROM (
480 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
481 FROM serial.subscription
482 WHERE record_entry = $1
486 CASE WHEN ('acp' = ANY ($5)) THEN
489 (SELECT XMLAGG(acp) FROM (
490 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
491 FROM biblio.peer_bib_copy_map p
492 JOIN asset.copy c ON (p.target_copy = c.id)
493 WHERE NOT c.deleted AND p.peer_record = $1
494 LIMIT ($6 -> 'acp')::INT
495 OFFSET ($7 -> 'acp')::INT
500 $F$ LANGUAGE SQL STABLE;
502 CREATE OR REPLACE FUNCTION unapi.ssub ( 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$
506 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
507 'tag:open-ils.org:U2@ssub/' || id AS id,
508 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
509 start_date AS start, end_date AS end, expected_date_offset
512 WHEN ('sdist' = ANY ($4)) THEN
513 XMLELEMENT( name distributions,
514 (SELECT XMLAGG(sdist) FROM (
515 SELECT unapi.sdist( id, 'xml', 'distribution', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8, FALSE)
516 FROM serial.distribution
517 WHERE subscription = ssub.id
523 FROM serial.subscription ssub
525 GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
526 $F$ LANGUAGE SQL STABLE;
528 CREATE OR REPLACE FUNCTION unapi.sdist ( 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$
532 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
533 'tag:open-ils.org:U2@sdist/' || id AS id,
534 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
535 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
536 unit_label_prefix, label, unit_label_suffix, summary_method
538 unapi.aou( holding_lib, $2, 'holding_lib', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8),
539 CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) ELSE NULL END,
541 WHEN ('sstr' = ANY ($4)) THEN
542 XMLELEMENT( name streams,
543 (SELECT XMLAGG(sstr) FROM (
544 SELECT unapi.sstr( id, 'xml', 'stream', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
546 WHERE distribution = sdist.id
551 XMLELEMENT( name summaries,
553 WHEN ('sbsum' = ANY ($4)) THEN
554 (SELECT XMLAGG(sbsum) FROM (
555 SELECT unapi.sbsum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
556 FROM serial.basic_summary
557 WHERE distribution = sdist.id
562 WHEN ('sisum' = ANY ($4)) THEN
563 (SELECT XMLAGG(sisum) FROM (
564 SELECT unapi.sisum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
565 FROM serial.index_summary
566 WHERE distribution = sdist.id
571 WHEN ('sssum' = ANY ($4)) THEN
572 (SELECT XMLAGG(sssum) FROM (
573 SELECT unapi.sssum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
574 FROM serial.supplement_summary
575 WHERE distribution = sdist.id
581 FROM serial.distribution sdist
583 GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
584 $F$ LANGUAGE SQL STABLE;
586 CREATE OR REPLACE FUNCTION unapi.sstr ( 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$
590 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
591 'tag:open-ils.org:U2@sstr/' || id AS id,
594 CASE WHEN distribution IS NOT NULL AND ('sdist' = ANY ($4)) THEN unapi.sssum( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE) ELSE NULL END,
596 WHEN ('sitem' = ANY ($4)) THEN
597 XMLELEMENT( name items,
598 (SELECT XMLAGG(sitem) FROM (
599 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE)
601 WHERE stream = sstr.id
607 FROM serial.stream sstr
609 GROUP BY id, routing_label, distribution;
610 $F$ LANGUAGE SQL STABLE;
612 CREATE OR REPLACE FUNCTION unapi.siss ( 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$
616 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
617 'tag:open-ils.org:U2@siss/' || id AS id,
618 create_date, edit_date, label, date_published,
619 holding_code, holding_type, holding_link_id
621 CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE) ELSE NULL END,
623 WHEN ('sitem' = ANY ($4)) THEN
624 XMLELEMENT( name items,
625 (SELECT XMLAGG(sitem) FROM (
626 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE)
628 WHERE issuance = sstr.id
634 FROM serial.issuance sstr
636 GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
637 $F$ LANGUAGE SQL STABLE;
639 CREATE OR REPLACE FUNCTION unapi.sitem ( 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$
643 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
644 'tag:open-ils.org:U2@sitem/' || id AS id,
645 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
646 date_expected, date_received
648 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,
649 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,
650 CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( unit, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
651 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
652 -- XMLELEMENT( name notes,
654 -- WHEN ('acpn' = ANY ($4)) THEN
655 -- (SELECT XMLAGG(acpn) FROM (
656 -- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8)
657 -- FROM asset.copy_note
658 -- WHERE owning_copy = cp.id AND pub
664 FROM serial.item sitem
666 $F$ LANGUAGE SQL STABLE;
669 CREATE OR REPLACE FUNCTION unapi.sssum ( 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$
673 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
674 'tag:open-ils.org:U2@sbsum/' || id AS id,
675 'sssum' AS type, generated_coverage, textual_holdings, show_generated
677 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
679 FROM serial.supplement_summary ssum
681 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
682 $F$ LANGUAGE SQL STABLE;
684 CREATE OR REPLACE FUNCTION unapi.sbsum ( 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$
688 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
689 'tag:open-ils.org:U2@sbsum/' || id AS id,
690 'sbsum' AS type, generated_coverage, textual_holdings, show_generated
692 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
694 FROM serial.basic_summary ssum
696 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
697 $F$ LANGUAGE SQL STABLE;
699 CREATE OR REPLACE FUNCTION unapi.sisum ( 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$
703 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
704 'tag:open-ils.org:U2@sbsum/' || id AS id,
705 'sisum' AS type, generated_coverage, textual_holdings, show_generated
707 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
709 FROM serial.index_summary ssum
711 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
712 $F$ LANGUAGE SQL STABLE;
715 CREATE OR REPLACE FUNCTION unapi.aou ( 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$
719 IF ename = 'circlib' THEN
723 'http://open-ils.org/spec/actors/v1' AS xmlns,
728 FROM actor.org_unit aou
731 EXECUTE $$SELECT XMLELEMENT(
732 name $$ || ename || $$,
734 'http://open-ils.org/spec/actors/v1' AS xmlns,
735 'tag:open-ils.org:U2@aou/' || id AS id,
736 shortname, name, opac_visible
739 FROM actor.org_unit aou
740 WHERE id = $1 $$ INTO output USING obj_id;
746 $F$ LANGUAGE PLPGSQL STABLE;
748 CREATE OR REPLACE FUNCTION unapi.acl ( 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$
752 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
756 label_prefix AS prefix,
757 label_suffix AS suffix
761 FROM asset.copy_location
763 $F$ LANGUAGE SQL STABLE;
765 CREATE OR REPLACE FUNCTION unapi.ccs ( 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$
769 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
776 FROM config.copy_status
778 $F$ LANGUAGE SQL STABLE;
780 CREATE OR REPLACE FUNCTION unapi.acpn ( 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$
784 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
792 $F$ LANGUAGE SQL STABLE;
794 CREATE OR REPLACE FUNCTION unapi.ascecm ( 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$
798 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
804 FROM asset.stat_cat_entry asce
805 JOIN asset.stat_cat sc ON (sc.id = asce.stat_cat)
807 $F$ LANGUAGE SQL STABLE;
809 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$
813 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
814 'tag:open-ils.org:U2@bmp/' || id AS id,
818 'tag:open-ils.org:U2@bre/' || record AS record
821 WHEN ('acp' = ANY ($4)) THEN
822 XMLELEMENT( name copies,
823 (SELECT XMLAGG(acp) FROM (
824 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE)
826 JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
828 AND cp.deleted IS FALSE
829 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
830 LIMIT ($7 -> 'acp')::INT
831 OFFSET ($8 -> 'acp')::INT
837 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
839 FROM biblio.monograph_part
841 GROUP BY id, label, label_sortkey, record;
842 $F$ LANGUAGE SQL STABLE;
844 CREATE OR REPLACE FUNCTION unapi.acp ( 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$
848 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
849 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
850 create_date, edit_date, copy_number, circulate, deposit,
851 ref, holdable, deleted, deposit_amount, price, barcode,
852 circ_modifier, circ_as_type, opac_visible, age_protect
854 unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
855 unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
856 unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
857 unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
858 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,
860 WHEN ('acpn' = ANY ($4)) THEN
861 XMLELEMENT( name copy_notes,
862 (SELECT XMLAGG(acpn) FROM (
863 SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
865 WHERE owning_copy = cp.id AND pub
871 WHEN ('ascecm' = ANY ($4)) THEN
872 XMLELEMENT( name statcats,
873 (SELECT XMLAGG(ascecm) FROM (
874 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
875 FROM asset.stat_cat_entry_copy_map
876 WHERE owning_copy = cp.id
882 WHEN ('bre' = ANY ($4)) THEN
883 XMLELEMENT( name foreign_records,
884 (SELECT XMLAGG(bre) FROM (
885 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
886 FROM biblio.peer_bib_copy_map
887 WHERE target_copy = cp.id
894 WHEN ('bmp' = ANY ($4)) THEN
895 XMLELEMENT( name monograph_parts,
896 (SELECT XMLAGG(bmp) FROM (
897 SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
898 FROM asset.copy_part_map
899 WHERE target_copy = cp.id
905 WHEN ('circ' = ANY ($4)) THEN
906 XMLELEMENT( name current_circulation,
907 (SELECT XMLAGG(circ) FROM (
908 SELECT unapi.circ( id, 'xml', 'circ', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE)
909 FROM action.circulation
910 WHERE target_copy = cp.id
911 AND checkin_time IS NULL
919 AND cp.deleted IS FALSE
920 GROUP BY id, status, location, circ_lib, call_number, create_date,
921 edit_date, copy_number, circulate, deposit, ref, holdable,
922 deleted, deposit_amount, price, barcode, circ_modifier,
923 circ_as_type, opac_visible, age_protect;
924 $F$ LANGUAGE SQL STABLE;
926 CREATE OR REPLACE FUNCTION unapi.sunit ( 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$
930 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
931 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
932 create_date, edit_date, copy_number, circulate, deposit,
933 ref, holdable, deleted, deposit_amount, price, barcode,
934 circ_modifier, circ_as_type, opac_visible, age_protect,
935 status_changed_time, floating, mint_condition,
936 detailed_contents, sort_key, summary_contents, cost
938 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),
939 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),
940 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),
941 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),
942 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,
943 XMLELEMENT( name copy_notes,
945 WHEN ('acpn' = ANY ($4)) THEN
946 (SELECT XMLAGG(acpn) FROM (
947 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)
949 WHERE owning_copy = cp.id AND pub
954 XMLELEMENT( name statcats,
956 WHEN ('ascecm' = ANY ($4)) THEN
957 (SELECT XMLAGG(ascecm) FROM (
958 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
959 FROM asset.stat_cat_entry_copy_map
960 WHERE owning_copy = cp.id
965 XMLELEMENT( name foreign_records,
967 WHEN ('bre' = ANY ($4)) THEN
968 (SELECT XMLAGG(bre) FROM (
969 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
970 FROM biblio.peer_bib_copy_map
971 WHERE target_copy = cp.id
977 WHEN ('bmp' = ANY ($4)) THEN
978 XMLELEMENT( name monograph_parts,
979 (SELECT XMLAGG(bmp) FROM (
980 SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
981 FROM asset.copy_part_map
982 WHERE target_copy = cp.id
988 WHEN ('circ' = ANY ($4)) THEN
989 XMLELEMENT( name current_circulation,
990 (SELECT XMLAGG(circ) FROM (
991 SELECT unapi.circ( id, 'xml', 'circ', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE)
992 FROM action.circulation
993 WHERE target_copy = cp.id
994 AND checkin_time IS NULL
1002 AND cp.deleted IS FALSE
1003 GROUP BY id, status, location, circ_lib, call_number, create_date,
1004 edit_date, copy_number, circulate, floating, mint_condition,
1005 deposit, ref, holdable, deleted, deposit_amount, price,
1006 barcode, circ_modifier, circ_as_type, opac_visible,
1007 status_changed_time, detailed_contents, sort_key,
1008 summary_contents, cost, age_protect;
1009 $F$ LANGUAGE SQL STABLE;
1011 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$
1015 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1016 'tag:open-ils.org:U2@acn/' || acn.id AS id,
1017 acn.id AS vol_id, o.shortname AS lib,
1018 o.opac_visible AS opac_visible,
1019 deleted, label, label_sortkey, label_class, record
1021 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8),
1023 WHEN ('acp' = ANY ($4)) THEN
1024 CASE WHEN $6 IS NOT NULL THEN
1025 XMLELEMENT( name copies,
1026 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
1027 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1028 evergreen.rank_cp_status(cp.status) AS rank_avail
1030 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id)
1031 WHERE cp.call_number = acn.id
1032 AND cp.deleted IS FALSE
1033 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
1034 LIMIT ($7 -> 'acp')::INT
1035 OFFSET ($8 -> 'acp')::INT
1039 XMLELEMENT( name copies,
1040 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
1041 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1042 evergreen.rank_cp_status(cp.status) AS rank_avail
1044 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id)
1045 WHERE cp.call_number = acn.id
1046 AND cp.deleted IS FALSE
1047 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
1048 LIMIT ($7 -> 'acp')::INT
1049 OFFSET ($8 -> 'acp')::INT
1057 (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)
1059 unapi.acnp( acn.prefix, 'marcxml', 'prefix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1060 unapi.acns( acn.suffix, 'marcxml', 'suffix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1061 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
1063 FROM asset.call_number acn
1064 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
1066 AND acn.deleted IS FALSE
1067 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
1068 $F$ LANGUAGE SQL STABLE;
1070 CREATE OR REPLACE FUNCTION unapi.acnp ( 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$
1072 name call_number_prefix,
1074 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1077 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
1081 FROM asset.call_number_prefix
1083 $F$ LANGUAGE SQL STABLE;
1085 CREATE OR REPLACE FUNCTION unapi.acns ( 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$
1087 name call_number_suffix,
1089 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1092 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
1096 FROM asset.call_number_suffix
1098 $F$ LANGUAGE SQL STABLE;
1100 CREATE OR REPLACE FUNCTION unapi.auri ( 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$
1104 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1105 'tag:open-ils.org:U2@auri/' || uri.id AS id,
1111 WHEN ('acn' = ANY ($4)) THEN
1112 XMLELEMENT( name copies,
1113 (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)
1120 GROUP BY uri.id, use_restriction, href, label;
1121 $F$ LANGUAGE SQL STABLE;
1123 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$
1127 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
1128 'tag:open-ils.org:U2@mra/' || mra.id AS id,
1129 'tag:open-ils.org:U2@bre/' || mra.id AS record
1131 (SELECT XMLAGG(foo.y)
1132 FROM (SELECT XMLELEMENT(
1136 cvm.value AS "coded-value",
1142 FROM EACH(mra.attrs) AS x
1143 JOIN config.record_attr_definition rad ON (x.key = rad.name)
1144 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = x.key AND code = x.value)
1148 FROM metabib.record_attr mra
1150 $F$ LANGUAGE SQL STABLE;
1152 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$
1156 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1157 'tag:open-ils.org:U2@circ/' || id AS id,
1161 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,
1162 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
1164 FROM action.circulation
1166 $F$ LANGUAGE SQL STABLE;
1170 -- Some test queries
1172 SELECT unapi.memoize( 'bre', 1,'mods32','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1173 SELECT unapi.memoize( 'bre', 1,'marcxml','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1174 SELECT unapi.memoize( 'bre', 1,'holdings_xml','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1176 SELECT unapi.biblio_record_entry_feed('{1}'::BIGINT[],'mods32','{holdings_xml,acp}'::TEXT[],'SYS1',NULL,'acn=>1',NULL, NULL,NULL,NULL,NULL,'http://c64/opac/extras/unapi', '<totalResults xmlns="http://a9.com/-/spec/opensearch/1.1/">2</totalResults><startIndex xmlns="http://a9.com/-/spec/opensearch/1.1/">1</startIndex><itemsPerPage xmlns="http://a9.com/-/spec/opensearch/1.1/">10</itemsPerPage>');
1178 SELECT unapi.biblio_record_entry_feed('{7209,7394}'::BIGINT[],'marcxml','{}'::TEXT[],'SYS1',NULL,'acn=>1',NULL, NULL,NULL,NULL,NULL,'http://fulfillment2.esilibrary.com/opac/extras/unapi', '<totalResults xmlns="http://a9.com/-/spec/opensearch/1.1/">2</totalResults><startIndex xmlns="http://a9.com/-/spec/opensearch/1.1/">1</startIndex><itemsPerPage xmlns="http://a9.com/-/spec/opensearch/1.1/">10</itemsPerPage>');
1179 EXPLAIN ANALYZE SELECT unapi.biblio_record_entry_feed('{7209,7394}'::BIGINT[],'marcxml','{}'::TEXT[],'SYS1',NULL,'acn=>1',NULL, NULL,NULL,NULL,NULL,'http://fulfillment2.esilibrary.com/opac/extras/unapi', '<totalResults xmlns="http://a9.com/-/spec/opensearch/1.1/">2</totalResults><startIndex xmlns="http://a9.com/-/spec/opensearch/1.1/">1</startIndex><itemsPerPage xmlns="http://a9.com/-/spec/opensearch/1.1/">10</itemsPerPage>');
1180 EXPLAIN ANALYZE SELECT unapi.biblio_record_entry_feed('{7209,7394}'::BIGINT[],'marcxml','{holdings_xml}'::TEXT[],'SYS1',NULL,'acn=>1',NULL, NULL,NULL,NULL,NULL,'http://fulfillment2.esilibrary.com/opac/extras/unapi', '<totalResults xmlns="http://a9.com/-/spec/opensearch/1.1/">2</totalResults><startIndex xmlns="http://a9.com/-/spec/opensearch/1.1/">1</startIndex><itemsPerPage xmlns="http://a9.com/-/spec/opensearch/1.1/">10</itemsPerPage>');
1181 EXPLAIN ANALYZE SELECT unapi.biblio_record_entry_feed('{7209,7394}'::BIGINT[],'mods32','{holdings_xml}'::TEXT[],'SYS1',NULL,'acn=>1',NULL, NULL,NULL,NULL,NULL,'http://fulfillment2.esilibrary.com/opac/extras/unapi', '<totalResults xmlns="http://a9.com/-/spec/opensearch/1.1/">2</totalResults><startIndex xmlns="http://a9.com/-/spec/opensearch/1.1/">1</startIndex><itemsPerPage xmlns="http://a9.com/-/spec/opensearch/1.1/">10</itemsPerPage>');
1183 SELECT unapi.biblio_record_entry_feed('{216}'::BIGINT[],'marcxml','{}'::TEXT[], 'BR1');
1184 EXPLAIN ANALYZE SELECT unapi.bre(216,'marcxml','record','{holdings_xml,bre.unapi}'::TEXT[], 'BR1');
1185 EXPLAIN ANALYZE SELECT unapi.bre(216,'holdings_xml','record','{}'::TEXT[], 'BR1');
1186 EXPLAIN ANALYZE SELECT unapi.holdings_xml(216,4,'BR1',2,'{bre}'::TEXT[]);
1187 EXPLAIN ANALYZE SELECT unapi.bre(216,'mods32','record','{}'::TEXT[], 'BR1');
1189 -- Limit to 5 call numbers, 5 copies, with a preferred library of 4 (BR1), in SYS2 at a depth of 0
1190 EXPLAIN ANALYZE SELECT unapi.bre(36,'marcxml','record','{holdings_xml,mra,acp,acnp,acns,bmp}','SYS2',0,'acn=>5,acp=>5',NULL,TRUE,4);