1 DROP SCHEMA IF EXISTS unapi CASCADE;
6 CREATE OR REPLACE FUNCTION evergreen.org_top()
7 RETURNS SETOF actor.org_unit AS $$
8 SELECT * FROM actor.org_unit WHERE parent_ou IS NULL LIMIT 1;
12 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT)
13 RETURNS anyarray AS $$
14 SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
15 $$ LANGUAGE SQL STABLE;
17 CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT DEFAULT NULL)
21 -- lib matches search_lib
22 (SELECT CASE WHEN $1 = $2 THEN -20000 END),
24 -- lib matches pref_lib
25 (SELECT CASE WHEN $1 = $3 THEN -10000 END),
28 -- pref_lib is a child of search_lib and lib is a child of pref lib.
29 -- For example, searching CONS, pref lib is SYS1,
30 -- copies at BR1 and BR2 sort to the front.
31 (SELECT distance - 5000
32 FROM actor.org_unit_descendants_distance($3)
33 WHERE id = $1 AND $3 IN (
34 SELECT id FROM actor.org_unit_descendants($2))),
36 -- lib is a child of search_lib
37 (SELECT distance FROM actor.org_unit_descendants_distance($2) WHERE id = $1),
39 -- all others pay cash
42 $$ LANGUAGE SQL STABLE;
44 CREATE OR REPLACE FUNCTION evergreen.rank_cp_status(status INT)
46 WITH totally_available AS (
47 SELECT id, 0 AS avail_rank
48 FROM config.copy_status
49 WHERE opac_visible IS TRUE
50 AND copy_active IS TRUE
51 AND id != 1 -- "Checked out"
52 ), almost_available AS (
53 SELECT id, 10 AS avail_rank
54 FROM config.copy_status
55 WHERE holdable IS TRUE
56 AND opac_visible IS TRUE
57 AND copy_active IS FALSE
58 OR id = 1 -- "Checked out"
61 (SELECT avail_rank FROM totally_available WHERE $1 IN (id)),
62 (SELECT avail_rank FROM almost_available WHERE $1 IN (id)),
65 $$ LANGUAGE SQL STABLE;
67 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
70 depth INT DEFAULT NULL,
71 slimit HSTORE DEFAULT NULL,
72 soffset HSTORE DEFAULT NULL,
73 pref_lib INT DEFAULT NULL,
74 includes TEXT[] DEFAULT NULL::TEXT[]
75 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
76 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
77 SELECT acn.id, aou.name, acn.label_sortkey,
78 evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
80 FROM asset.call_number acn
81 JOIN asset.copy acp ON (acn.id = acp.call_number)
82 JOIN actor.org_unit_descendants( $2, COALESCE(
85 FROM actor.org_unit_type aout
86 INNER JOIN actor.org_unit ou ON ou_type = aout.id
89 ) AS aou ON (acp.circ_lib = aou.id)
91 AND acn.deleted IS FALSE
92 AND acp.deleted IS FALSE
93 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
96 FROM asset.opac_visible_copies
97 WHERE copy_id = acp.id AND record = acn.record
99 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
101 ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
104 GROUP BY ua.id, ua.name, ua.label_sortkey
105 ORDER BY rank, ua.name, ua.label_sortkey
106 LIMIT ($4 -> 'acn')::INT
107 OFFSET ($5 -> 'acn')::INT;
111 CREATE OR REPLACE FUNCTION evergreen.located_uris (
114 pref_lib INT DEFAULT NULL
115 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
116 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
117 SELECT DISTINCT ON (id) * FROM (
118 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
119 FROM asset.call_number acn
120 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
121 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
122 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
123 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
125 WHERE acn.record = $1
126 AND acn.deleted IS FALSE
127 AND auri.active IS TRUE
128 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL)
130 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
131 FROM asset.call_number acn
132 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
133 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
134 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
135 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
137 WHERE acn.record = $1
138 AND acn.deleted IS FALSE
139 AND auri.active IS TRUE
140 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x
141 ORDER BY id, pref_ou DESC;
145 CREATE TABLE unapi.bre_output_layout (
146 name TEXT PRIMARY KEY,
147 transform TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
148 mime_type TEXT NOT NULL,
149 feed_top TEXT NOT NULL,
150 holdings_element TEXT,
152 description_element TEXT,
153 creator_element TEXT,
154 update_ts_element TEXT
157 INSERT INTO unapi.bre_output_layout
158 (name, transform, mime_type, holdings_element, feed_top, title_element, description_element, creator_element, update_ts_element)
160 ('holdings_xml', NULL, 'application/xml', NULL, 'hxml', NULL, NULL, NULL, NULL),
161 ('marcxml', 'marcxml', 'application/marc+xml', 'record', 'collection', NULL, NULL, NULL, NULL),
162 ('mods32', 'mods32', 'application/mods+xml', 'mods', 'modsCollection', NULL, NULL, NULL, NULL)
165 -- Dummy functions, so we can create the real ones out of order
166 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;
167 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;
168 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;
169 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;
170 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;
171 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;
172 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;
173 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;
174 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;
175 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;
176 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;
177 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;
178 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;
179 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;
180 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;
181 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;
182 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;
183 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;
184 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;
185 CREATE OR REPLACE FUNCTION unapi.bre (
191 depth INT DEFAULT NULL,
192 slimit HSTORE DEFAULT NULL,
193 soffset HSTORE DEFAULT NULL,
194 include_xmlns BOOL DEFAULT TRUE,
195 pref_lib INT DEFAULT NULL
197 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
198 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;
199 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;
200 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;
202 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
206 depth INT DEFAULT NULL,
207 includes TEXT[] DEFAULT NULL::TEXT[],
208 slimit HSTORE DEFAULT NULL,
209 soffset HSTORE DEFAULT NULL,
210 include_xmlns BOOL DEFAULT TRUE,
211 pref_lib INT DEFAULT NULL
213 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
215 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;
217 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$
223 'id' || COALESCE(obj_id::TEXT,'') ||
224 'format' || COALESCE(format::TEXT,'') ||
225 'ename' || COALESCE(ename::TEXT,'') ||
226 'includes' || COALESCE(includes::TEXT,'{}'::TEXT[]::TEXT) ||
227 'org' || COALESCE(org::TEXT,'') ||
228 'depth' || COALESCE(depth::TEXT,'') ||
229 'slimit' || COALESCE(slimit::TEXT,'') ||
230 'soffset' || COALESCE(soffset::TEXT,'') ||
231 'include_xmlns' || COALESCE(include_xmlns::TEXT,'');
232 -- RAISE NOTICE 'memoize key: %', key;
235 -- RAISE NOTICE 'memoize hash: %', key;
237 -- XXX cache logic ... memcached? table?
239 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;
242 $F$ LANGUAGE PLPGSQL STABLE;
244 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$
246 layout unapi.bre_output_layout%ROWTYPE;
247 transform config.xml_transform%ROWTYPE;
250 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
255 IF org = '-' OR org IS NULL THEN
256 SELECT shortname INTO org FROM evergreen.org_top();
259 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
260 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
262 IF layout.name IS NULL THEN
266 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
267 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
269 -- Gather the bib xml
270 SELECT XMLAGG( unapi.bre(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
272 IF layout.title_element IS NOT NULL THEN
273 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
276 IF layout.description_element IS NOT NULL THEN
277 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
280 IF layout.creator_element IS NOT NULL THEN
281 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
284 IF layout.update_ts_element IS NOT NULL THEN
285 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;
288 IF unapi_url IS NOT NULL THEN
289 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;
292 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
294 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
295 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
296 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
301 $F$ LANGUAGE PLPGSQL STABLE;
303 CREATE OR REPLACE FUNCTION unapi.bre (
309 depth INT DEFAULT NULL,
310 slimit HSTORE DEFAULT NULL,
311 soffset HSTORE DEFAULT NULL,
312 include_xmlns BOOL DEFAULT TRUE,
313 pref_lib INT DEFAULT NULL
317 me biblio.record_entry%ROWTYPE;
318 layout unapi.bre_output_layout%ROWTYPE;
319 xfrm config.xml_transform%ROWTYPE;
328 IF org = '-' OR org IS NULL THEN
329 SELECT shortname INTO org FROM evergreen.org_top();
332 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
338 IF format = 'holdings_xml' THEN -- the special case
339 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
343 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
345 IF layout.name IS NULL THEN
349 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
351 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
353 -- grab SVF if we need them
354 IF ('mra' = ANY (includes)) THEN
355 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
360 -- grab holdings if we need them
361 IF ('holdings_xml' = ANY (includes)) THEN
362 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns, pref_lib);
368 -- generate our item node
371 IF format = 'marcxml' THEN
373 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
374 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
377 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
380 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
382 IF axml IS NOT NULL THEN
383 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
386 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
387 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
390 IF ('bre.unapi' = ANY (includes)) THEN
391 output := REGEXP_REPLACE(
393 '</' || top_el || '>(.*?)',
397 'http://www.w3.org/1999/xhtml' AS xmlns,
399 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
401 )::TEXT || '</' || top_el || E'>\\1'
407 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
410 $F$ LANGUAGE PLPGSQL STABLE;
412 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
416 depth INT DEFAULT NULL,
417 includes TEXT[] DEFAULT NULL::TEXT[],
418 slimit HSTORE DEFAULT NULL,
419 soffset HSTORE DEFAULT NULL,
420 include_xmlns BOOL DEFAULT TRUE,
421 pref_lib INT DEFAULT NULL
427 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
428 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
429 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
433 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
436 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
438 FROM asset.opac_ou_record_copy_count($2, $1)
442 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
444 FROM asset.staff_ou_record_copy_count($2, $1)
448 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
450 FROM asset.opac_ou_record_copy_count($9, $1)
455 WHEN ('bmp' = ANY ($5)) THEN
457 name monograph_parts,
458 (SELECT XMLAGG(bmp) FROM (
459 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)
460 FROM biblio.monograph_part
468 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
470 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
471 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
474 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), uris.rank, name, label_sortkey
475 FROM evergreen.located_uris($1, $2, $9) AS uris
478 CASE WHEN ('ssub' = ANY ($5)) THEN
481 (SELECT XMLAGG(ssub) FROM (
482 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
483 FROM serial.subscription
484 WHERE record_entry = $1
488 CASE WHEN ('acp' = ANY ($5)) THEN
491 (SELECT XMLAGG(acp) FROM (
492 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
493 FROM biblio.peer_bib_copy_map p
494 JOIN asset.copy c ON (p.target_copy = c.id)
495 WHERE NOT c.deleted AND p.peer_record = $1
496 LIMIT ($6 -> 'acp')::INT
497 OFFSET ($7 -> 'acp')::INT
502 $F$ LANGUAGE SQL STABLE;
504 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$
508 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
509 'tag:open-ils.org:U2@ssub/' || id AS id,
510 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
511 start_date AS start, end_date AS end, expected_date_offset
514 WHEN ('sdist' = ANY ($4)) THEN
515 XMLELEMENT( name distributions,
516 (SELECT XMLAGG(sdist) FROM (
517 SELECT unapi.sdist( id, 'xml', 'distribution', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8, FALSE)
518 FROM serial.distribution
519 WHERE subscription = ssub.id
525 FROM serial.subscription ssub
527 GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
528 $F$ LANGUAGE SQL STABLE;
530 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$
534 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
535 'tag:open-ils.org:U2@sdist/' || id AS id,
536 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
537 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
538 unit_label_prefix, label, unit_label_suffix, summary_method
540 unapi.aou( holding_lib, $2, 'holding_lib', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8),
541 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,
543 WHEN ('sstr' = ANY ($4)) THEN
544 XMLELEMENT( name streams,
545 (SELECT XMLAGG(sstr) FROM (
546 SELECT unapi.sstr( id, 'xml', 'stream', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
548 WHERE distribution = sdist.id
553 XMLELEMENT( name summaries,
555 WHEN ('sbsum' = ANY ($4)) THEN
556 (SELECT XMLAGG(sbsum) FROM (
557 SELECT unapi.sbsum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
558 FROM serial.basic_summary
559 WHERE distribution = sdist.id
564 WHEN ('sisum' = ANY ($4)) THEN
565 (SELECT XMLAGG(sisum) FROM (
566 SELECT unapi.sisum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
567 FROM serial.index_summary
568 WHERE distribution = sdist.id
573 WHEN ('sssum' = ANY ($4)) THEN
574 (SELECT XMLAGG(sssum) FROM (
575 SELECT unapi.sssum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
576 FROM serial.supplement_summary
577 WHERE distribution = sdist.id
583 FROM serial.distribution sdist
585 GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
586 $F$ LANGUAGE SQL STABLE;
588 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$
592 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
593 'tag:open-ils.org:U2@sstr/' || id AS id,
596 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,
598 WHEN ('sitem' = ANY ($4)) THEN
599 XMLELEMENT( name items,
600 (SELECT XMLAGG(sitem) FROM (
601 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE)
603 WHERE stream = sstr.id
609 FROM serial.stream sstr
611 GROUP BY id, routing_label, distribution;
612 $F$ LANGUAGE SQL STABLE;
614 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$
618 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
619 'tag:open-ils.org:U2@siss/' || id AS id,
620 create_date, edit_date, label, date_published,
621 holding_code, holding_type, holding_link_id
623 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,
625 WHEN ('sitem' = ANY ($4)) THEN
626 XMLELEMENT( name items,
627 (SELECT XMLAGG(sitem) FROM (
628 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE)
630 WHERE issuance = sstr.id
636 FROM serial.issuance sstr
638 GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
639 $F$ LANGUAGE SQL STABLE;
641 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$
645 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
646 'tag:open-ils.org:U2@sitem/' || id AS id,
647 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
648 date_expected, date_received
650 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,
651 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,
652 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,
653 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
654 -- XMLELEMENT( name notes,
656 -- WHEN ('acpn' = ANY ($4)) THEN
657 -- (SELECT XMLAGG(acpn) FROM (
658 -- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8)
659 -- FROM asset.copy_note
660 -- WHERE owning_copy = cp.id AND pub
666 FROM serial.item sitem
668 $F$ LANGUAGE SQL STABLE;
671 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$
675 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
676 'tag:open-ils.org:U2@sbsum/' || id AS id,
677 'sssum' AS type, generated_coverage, textual_holdings, show_generated
679 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
681 FROM serial.supplement_summary ssum
683 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
684 $F$ LANGUAGE SQL STABLE;
686 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$
690 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
691 'tag:open-ils.org:U2@sbsum/' || id AS id,
692 'sbsum' AS type, generated_coverage, textual_holdings, show_generated
694 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
696 FROM serial.basic_summary ssum
698 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
699 $F$ LANGUAGE SQL STABLE;
701 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$
705 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
706 'tag:open-ils.org:U2@sbsum/' || id AS id,
707 'sisum' AS type, generated_coverage, textual_holdings, show_generated
709 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
711 FROM serial.index_summary ssum
713 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
714 $F$ LANGUAGE SQL STABLE;
717 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$
721 IF ename = 'circlib' THEN
725 'http://open-ils.org/spec/actors/v1' AS xmlns,
730 FROM actor.org_unit aou
733 EXECUTE $$SELECT XMLELEMENT(
734 name $$ || ename || $$,
736 'http://open-ils.org/spec/actors/v1' AS xmlns,
737 'tag:open-ils.org:U2@aou/' || id AS id,
738 shortname, name, opac_visible
741 FROM actor.org_unit aou
742 WHERE id = $1 $$ INTO output USING obj_id;
748 $F$ LANGUAGE PLPGSQL STABLE;
750 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$
754 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
758 label_prefix AS prefix,
759 label_suffix AS suffix
763 FROM asset.copy_location
765 $F$ LANGUAGE SQL STABLE;
767 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$
771 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
778 FROM config.copy_status
780 $F$ LANGUAGE SQL STABLE;
782 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$
786 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
794 $F$ LANGUAGE SQL STABLE;
796 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$
800 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
806 FROM asset.stat_cat_entry asce
807 JOIN asset.stat_cat sc ON (sc.id = asce.stat_cat)
809 $F$ LANGUAGE SQL STABLE;
811 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$
815 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
816 'tag:open-ils.org:U2@bmp/' || id AS id,
820 'tag:open-ils.org:U2@bre/' || record AS record
823 WHEN ('acp' = ANY ($4)) THEN
824 XMLELEMENT( name copies,
825 (SELECT XMLAGG(acp) FROM (
826 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE)
828 JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
830 AND cp.deleted IS FALSE
831 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
832 LIMIT ($7 -> 'acp')::INT
833 OFFSET ($8 -> 'acp')::INT
839 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
841 FROM biblio.monograph_part
843 GROUP BY id, label, label_sortkey, record;
844 $F$ LANGUAGE SQL STABLE;
846 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$
850 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
851 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
852 create_date, edit_date, copy_number, circulate, deposit,
853 ref, holdable, deleted, deposit_amount, price, barcode,
854 circ_modifier, circ_as_type, opac_visible, age_protect
856 unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
857 unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
858 unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
859 unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
860 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,
862 WHEN ('acpn' = ANY ($4)) THEN
863 XMLELEMENT( name copy_notes,
864 (SELECT XMLAGG(acpn) FROM (
865 SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
867 WHERE owning_copy = cp.id AND pub
873 WHEN ('ascecm' = ANY ($4)) THEN
874 XMLELEMENT( name statcats,
875 (SELECT XMLAGG(ascecm) FROM (
876 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
877 FROM asset.stat_cat_entry_copy_map
878 WHERE owning_copy = cp.id
884 WHEN ('bre' = ANY ($4)) THEN
885 XMLELEMENT( name foreign_records,
886 (SELECT XMLAGG(bre) FROM (
887 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
888 FROM biblio.peer_bib_copy_map
889 WHERE target_copy = cp.id
896 WHEN ('bmp' = ANY ($4)) THEN
897 XMLELEMENT( name monograph_parts,
898 (SELECT XMLAGG(bmp) FROM (
899 SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
900 FROM asset.copy_part_map
901 WHERE target_copy = cp.id
907 WHEN ('circ' = ANY ($4)) THEN
908 XMLELEMENT( name current_circulation,
909 (SELECT XMLAGG(circ) FROM (
910 SELECT unapi.circ( id, 'xml', 'circ', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE)
911 FROM action.circulation
912 WHERE target_copy = cp.id
913 AND checkin_time IS NULL
921 AND cp.deleted IS FALSE
922 GROUP BY id, status, location, circ_lib, call_number, create_date,
923 edit_date, copy_number, circulate, deposit, ref, holdable,
924 deleted, deposit_amount, price, barcode, circ_modifier,
925 circ_as_type, opac_visible, age_protect;
926 $F$ LANGUAGE SQL STABLE;
928 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$
932 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
933 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
934 create_date, edit_date, copy_number, circulate, deposit,
935 ref, holdable, deleted, deposit_amount, price, barcode,
936 circ_modifier, circ_as_type, opac_visible, age_protect,
937 status_changed_time, floating, mint_condition,
938 detailed_contents, sort_key, summary_contents, cost
940 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),
941 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),
942 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),
943 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),
944 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,
945 XMLELEMENT( name copy_notes,
947 WHEN ('acpn' = ANY ($4)) THEN
948 (SELECT XMLAGG(acpn) FROM (
949 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)
951 WHERE owning_copy = cp.id AND pub
956 XMLELEMENT( name statcats,
958 WHEN ('ascecm' = ANY ($4)) THEN
959 (SELECT XMLAGG(ascecm) FROM (
960 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
961 FROM asset.stat_cat_entry_copy_map
962 WHERE owning_copy = cp.id
967 XMLELEMENT( name foreign_records,
969 WHEN ('bre' = ANY ($4)) THEN
970 (SELECT XMLAGG(bre) FROM (
971 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
972 FROM biblio.peer_bib_copy_map
973 WHERE target_copy = cp.id
979 WHEN ('bmp' = ANY ($4)) THEN
980 XMLELEMENT( name monograph_parts,
981 (SELECT XMLAGG(bmp) FROM (
982 SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
983 FROM asset.copy_part_map
984 WHERE target_copy = cp.id
990 WHEN ('circ' = ANY ($4)) THEN
991 XMLELEMENT( name current_circulation,
992 (SELECT XMLAGG(circ) FROM (
993 SELECT unapi.circ( id, 'xml', 'circ', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE)
994 FROM action.circulation
995 WHERE target_copy = cp.id
996 AND checkin_time IS NULL
1004 AND cp.deleted IS FALSE
1005 GROUP BY id, status, location, circ_lib, call_number, create_date,
1006 edit_date, copy_number, circulate, floating, mint_condition,
1007 deposit, ref, holdable, deleted, deposit_amount, price,
1008 barcode, circ_modifier, circ_as_type, opac_visible,
1009 status_changed_time, detailed_contents, sort_key,
1010 summary_contents, cost, age_protect;
1011 $F$ LANGUAGE SQL STABLE;
1013 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$
1017 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1018 'tag:open-ils.org:U2@acn/' || acn.id AS id,
1019 acn.id AS vol_id, o.shortname AS lib,
1020 o.opac_visible AS opac_visible,
1021 deleted, label, label_sortkey, label_class, record
1023 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8),
1025 WHEN ('acp' = ANY ($4)) THEN
1026 CASE WHEN $6 IS NOT NULL THEN
1027 XMLELEMENT( name copies,
1028 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
1029 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1030 evergreen.rank_cp_status(cp.status) AS rank_avail
1032 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id)
1033 WHERE cp.call_number = acn.id
1034 AND cp.deleted IS FALSE
1035 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
1036 LIMIT ($7 -> 'acp')::INT
1037 OFFSET ($8 -> 'acp')::INT
1041 XMLELEMENT( name copies,
1042 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
1043 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1044 evergreen.rank_cp_status(cp.status) AS rank_avail
1046 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id)
1047 WHERE cp.call_number = acn.id
1048 AND cp.deleted IS FALSE
1049 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
1050 LIMIT ($7 -> 'acp')::INT
1051 OFFSET ($8 -> 'acp')::INT
1059 (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)
1061 unapi.acnp( acn.prefix, 'marcxml', 'prefix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1062 unapi.acns( acn.suffix, 'marcxml', 'suffix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1063 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
1065 FROM asset.call_number acn
1066 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
1068 AND acn.deleted IS FALSE
1069 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
1070 $F$ LANGUAGE SQL STABLE;
1072 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$
1074 name call_number_prefix,
1076 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1079 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
1083 FROM asset.call_number_prefix
1085 $F$ LANGUAGE SQL STABLE;
1087 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$
1089 name call_number_suffix,
1091 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1094 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
1098 FROM asset.call_number_suffix
1100 $F$ LANGUAGE SQL STABLE;
1102 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$
1106 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1107 'tag:open-ils.org:U2@auri/' || uri.id AS id,
1113 WHEN ('acn' = ANY ($4)) THEN
1114 XMLELEMENT( name copies,
1115 (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)
1122 GROUP BY uri.id, use_restriction, href, label;
1123 $F$ LANGUAGE SQL STABLE;
1125 CREATE OR REPLACE FUNCTION unapi.mra (
1131 depth INT DEFAULT NULL,
1132 slimit HSTORE DEFAULT NULL,
1133 soffset HSTORE DEFAULT NULL,
1134 include_xmlns BOOL DEFAULT TRUE
1135 ) RETURNS XML AS $F$
1139 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
1140 'tag:open-ils.org:U2@mra/' || $1 AS id,
1141 'tag:open-ils.org:U2@bre/' || $1 AS record
1143 (SELECT XMLAGG(foo.y)
1149 cvm.value AS "coded-value",
1158 FROM metabib.record_attr_flat mra
1159 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
1160 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
1165 $F$ LANGUAGE SQL STABLE;
1167 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$
1171 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1172 'tag:open-ils.org:U2@circ/' || id AS id,
1176 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,
1177 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
1179 FROM action.circulation
1181 $F$ LANGUAGE SQL STABLE;
1185 -- Some test queries
1187 SELECT unapi.memoize( 'bre', 1,'mods32','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1188 SELECT unapi.memoize( 'bre', 1,'marcxml','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1189 SELECT unapi.memoize( 'bre', 1,'holdings_xml','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1191 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>');
1193 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>');
1194 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>');
1195 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>');
1196 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>');
1198 SELECT unapi.biblio_record_entry_feed('{216}'::BIGINT[],'marcxml','{}'::TEXT[], 'BR1');
1199 EXPLAIN ANALYZE SELECT unapi.bre(216,'marcxml','record','{holdings_xml,bre.unapi}'::TEXT[], 'BR1');
1200 EXPLAIN ANALYZE SELECT unapi.bre(216,'holdings_xml','record','{}'::TEXT[], 'BR1');
1201 EXPLAIN ANALYZE SELECT unapi.holdings_xml(216,4,'BR1',2,'{bre}'::TEXT[]);
1202 EXPLAIN ANALYZE SELECT unapi.bre(216,'mods32','record','{}'::TEXT[], 'BR1');
1204 -- Limit to 5 call numbers, 5 copies, with a preferred library of 4 (BR1), in SYS2 at a depth of 0
1205 EXPLAIN ANALYZE SELECT unapi.bre(36,'marcxml','record','{holdings_xml,mra,acp,acnp,acns,bmp}','SYS2',0,'acn=>5,acp=>5',NULL,TRUE,4);