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 WITH RECURSIVE ou_depth AS (
81 FROM actor.org_unit_type aout
82 INNER JOIN actor.org_unit ou ON ou_type = aout.id
86 ), descendant_depth AS (
90 FROM actor.org_unit ou
91 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
92 JOIN anscestor_depth ad ON (ad.id = ou.id),
94 WHERE ad.depth = ou_depth.depth
99 FROM actor.org_unit ou
100 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
101 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
102 ), anscestor_depth AS (
106 FROM actor.org_unit ou
107 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
113 FROM actor.org_unit ou
114 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
115 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
117 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
120 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
121 SELECT acn.id, aou.name, acn.label_sortkey,
123 FROM asset.call_number acn
124 JOIN asset.copy acp ON (acn.id = acp.call_number)
125 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
126 WHERE acn.record = ANY ($1)
127 AND acn.deleted IS FALSE
128 AND acp.deleted IS FALSE
129 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
132 FROM asset.opac_visible_copies
133 WHERE copy_id = acp.id AND record = acn.record
135 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
139 CASE WHEN aou.id = $2 THEN -20000 END,
140 CASE WHEN aou.id = $6 THEN -10000 END,
141 (SELECT distance - 5000
142 FROM actor.org_unit_descendants_distance($6) as x
143 WHERE x.id = aou.id AND $6 IN (
144 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
145 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
148 evergreen.rank_cp_status(acp.status)
151 GROUP BY ua.id, ua.name, ua.label_sortkey
152 ORDER BY rank, ua.name, ua.label_sortkey
153 LIMIT ($4 -> 'acn')::INT
154 OFFSET ($5 -> 'acn')::INT;
155 $$ LANGUAGE SQL STABLE ROWS 10;
157 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
158 ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] )
159 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
160 AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;
163 CREATE OR REPLACE FUNCTION evergreen.located_uris (
166 pref_lib INT DEFAULT NULL
167 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
168 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
169 SELECT DISTINCT ON (id) * FROM (
170 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
171 FROM asset.call_number acn
172 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
173 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
174 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
175 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
177 WHERE acn.record = ANY ($1)
178 AND acn.deleted IS FALSE
179 AND auri.active IS TRUE
180 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL))
182 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
183 FROM asset.call_number acn
184 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
185 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
186 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
187 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
189 WHERE acn.record = ANY ($1)
190 AND acn.deleted IS FALSE
191 AND auri.active IS TRUE
192 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL)))x
193 ORDER BY id, pref_ou DESC;
197 CREATE OR REPLACE FUNCTION evergreen.located_uris ( bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL)
198 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT)
199 AS $$ SELECT * FROM evergreen.located_uris(ARRAY[$1],$2,$3) $$ LANGUAGE SQL STABLE;
201 CREATE TABLE unapi.bre_output_layout (
202 name TEXT PRIMARY KEY,
203 transform TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
204 mime_type TEXT NOT NULL,
205 feed_top TEXT NOT NULL,
206 holdings_element TEXT,
208 description_element TEXT,
209 creator_element TEXT,
210 update_ts_element TEXT
213 INSERT INTO unapi.bre_output_layout
214 (name, transform, mime_type, holdings_element, feed_top, title_element, description_element, creator_element, update_ts_element)
216 ('holdings_xml', NULL, 'application/xml', NULL, 'hxml', NULL, NULL, NULL, NULL),
217 ('marcxml', 'marcxml', 'application/marc+xml', 'record', 'collection', NULL, NULL, NULL, NULL),
218 ('mods32', 'mods32', 'application/mods+xml', 'mods', 'modsCollection', NULL, NULL, NULL, NULL)
221 -- Dummy functions, so we can create the real ones out of order
222 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;
223 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;
224 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;
225 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;
226 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;
227 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;
228 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;
229 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;
230 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;
231 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;
232 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;
233 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;
234 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;
235 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;
236 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;
237 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;
238 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;
239 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;
240 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;
241 CREATE OR REPLACE FUNCTION unapi.bre (
247 depth INT DEFAULT NULL,
248 slimit HSTORE DEFAULT NULL,
249 soffset HSTORE DEFAULT NULL,
250 include_xmlns BOOL DEFAULT TRUE,
251 pref_lib INT DEFAULT NULL
253 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
254 CREATE OR REPLACE FUNCTION unapi.mmr (
260 depth INT DEFAULT NULL,
261 slimit HSTORE DEFAULT NULL,
262 soffset HSTORE DEFAULT NULL,
263 include_xmlns BOOL DEFAULT TRUE,
264 pref_lib INT DEFAULT NULL
266 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
267 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;
268 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;
269 CREATE OR REPLACE FUNCTION unapi.mmr_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, pref_lib INT DEFAULT NULL) RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
270 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;
272 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
276 depth INT DEFAULT NULL,
277 includes TEXT[] DEFAULT NULL::TEXT[],
278 slimit HSTORE DEFAULT NULL,
279 soffset HSTORE DEFAULT NULL,
280 include_xmlns BOOL DEFAULT TRUE,
281 pref_lib INT DEFAULT NULL
283 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
285 CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml (
289 depth INT DEFAULT NULL,
290 includes TEXT[] DEFAULT NULL::TEXT[],
291 slimit HSTORE DEFAULT NULL,
292 soffset HSTORE DEFAULT NULL,
293 include_xmlns BOOL DEFAULT TRUE,
294 pref_lib INT DEFAULT NULL
296 RETURNS XML AS $F$ SELECT NULL::XML $F$ LANGUAGE SQL STABLE;
298 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;
300 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$
306 'id' || COALESCE(obj_id::TEXT,'') ||
307 'format' || COALESCE(format::TEXT,'') ||
308 'ename' || COALESCE(ename::TEXT,'') ||
309 'includes' || COALESCE(includes::TEXT,'{}'::TEXT[]::TEXT) ||
310 'org' || COALESCE(org::TEXT,'') ||
311 'depth' || COALESCE(depth::TEXT,'') ||
312 'slimit' || COALESCE(slimit::TEXT,'') ||
313 'soffset' || COALESCE(soffset::TEXT,'') ||
314 'include_xmlns' || COALESCE(include_xmlns::TEXT,'');
315 -- RAISE NOTICE 'memoize key: %', key;
318 -- RAISE NOTICE 'memoize hash: %', key;
320 -- XXX cache logic ... memcached? table?
322 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;
325 $F$ LANGUAGE PLPGSQL STABLE;
327 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$
329 layout unapi.bre_output_layout%ROWTYPE;
330 transform config.xml_transform%ROWTYPE;
333 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
338 IF org = '-' OR org IS NULL THEN
339 SELECT shortname INTO org FROM evergreen.org_top();
342 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
343 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
345 IF layout.name IS NULL THEN
349 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
350 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
352 -- Gather the bib xml
353 SELECT XMLAGG( unapi.bre(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
355 IF layout.title_element IS NOT NULL THEN
356 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
359 IF layout.description_element IS NOT NULL THEN
360 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
363 IF layout.creator_element IS NOT NULL THEN
364 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
367 IF layout.update_ts_element IS NOT NULL THEN
368 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;
371 IF unapi_url IS NOT NULL THEN
372 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;
375 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
377 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
378 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
379 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
384 $F$ LANGUAGE PLPGSQL STABLE;
386 CREATE OR REPLACE FUNCTION unapi.bre (
392 depth INT DEFAULT NULL,
393 slimit HSTORE DEFAULT NULL,
394 soffset HSTORE DEFAULT NULL,
395 include_xmlns BOOL DEFAULT TRUE,
396 pref_lib INT DEFAULT NULL
400 me biblio.record_entry%ROWTYPE;
401 layout unapi.bre_output_layout%ROWTYPE;
402 xfrm config.xml_transform%ROWTYPE;
411 IF org = '-' OR org IS NULL THEN
412 SELECT shortname INTO org FROM evergreen.org_top();
415 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
421 IF format = 'holdings_xml' THEN -- the special case
422 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
426 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
428 IF layout.name IS NULL THEN
432 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
434 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
436 -- grab SVF if we need them
437 IF ('mra' = ANY (includes)) THEN
438 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
443 -- grab holdings if we need them
444 IF ('holdings_xml' = ANY (includes)) THEN
445 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns, pref_lib);
451 -- generate our item node
454 IF format = 'marcxml' THEN
456 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
457 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
460 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
463 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
465 IF axml IS NOT NULL THEN
466 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
469 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
470 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
473 IF ('bre.unapi' = ANY (includes)) THEN
474 output := REGEXP_REPLACE(
476 '</' || top_el || '>(.*?)',
480 'http://www.w3.org/1999/xhtml' AS xmlns,
482 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
484 )::TEXT || '</' || top_el || E'>\\1'
490 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
493 $F$ LANGUAGE PLPGSQL STABLE;
495 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
499 depth INT DEFAULT NULL,
500 includes TEXT[] DEFAULT NULL::TEXT[],
501 slimit HSTORE DEFAULT NULL,
502 soffset HSTORE DEFAULT NULL,
503 include_xmlns BOOL DEFAULT TRUE,
504 pref_lib INT DEFAULT NULL
510 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
511 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
512 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
516 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
519 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
521 FROM asset.opac_ou_record_copy_count($2, $1)
525 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
527 FROM asset.staff_ou_record_copy_count($2, $1)
531 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
533 FROM asset.opac_ou_record_copy_count($9, $1)
538 WHEN ('bmp' = ANY ($5)) THEN
540 name monograph_parts,
541 (SELECT XMLAGG(bmp) FROM (
542 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)
543 FROM biblio.monograph_part
551 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
553 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
554 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
557 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
558 FROM evergreen.located_uris($1, $2, $9) AS uris
561 CASE WHEN ('ssub' = ANY ($5)) THEN
564 (SELECT XMLAGG(ssub) FROM (
565 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
566 FROM serial.subscription
567 WHERE record_entry = $1
571 CASE WHEN ('acp' = ANY ($5)) THEN
574 (SELECT XMLAGG(acp) FROM (
575 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
576 FROM biblio.peer_bib_copy_map p
577 JOIN asset.copy c ON (p.target_copy = c.id)
578 WHERE NOT c.deleted AND p.peer_record = $1
579 LIMIT ($6 -> 'acp')::INT
580 OFFSET ($7 -> 'acp')::INT
585 $F$ LANGUAGE SQL STABLE;
587 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$
591 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
592 'tag:open-ils.org:U2@ssub/' || id AS id,
593 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
594 start_date AS start, end_date AS end, expected_date_offset
597 WHEN ('sdist' = ANY ($4)) THEN
598 XMLELEMENT( name distributions,
599 (SELECT XMLAGG(sdist) FROM (
600 SELECT unapi.sdist( id, 'xml', 'distribution', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8, FALSE)
601 FROM serial.distribution
602 WHERE subscription = ssub.id
608 FROM serial.subscription ssub
610 GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
611 $F$ LANGUAGE SQL STABLE;
613 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$
617 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
618 'tag:open-ils.org:U2@sdist/' || id AS id,
619 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
620 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
621 unit_label_prefix, label, unit_label_suffix, summary_method
623 unapi.aou( holding_lib, $2, 'holding_lib', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8),
624 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,
626 WHEN ('sstr' = ANY ($4)) THEN
627 XMLELEMENT( name streams,
628 (SELECT XMLAGG(sstr) FROM (
629 SELECT unapi.sstr( id, 'xml', 'stream', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
631 WHERE distribution = sdist.id
636 XMLELEMENT( name summaries,
638 WHEN ('sbsum' = ANY ($4)) THEN
639 (SELECT XMLAGG(sbsum) FROM (
640 SELECT unapi.sbsum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
641 FROM serial.basic_summary
642 WHERE distribution = sdist.id
647 WHEN ('sisum' = ANY ($4)) THEN
648 (SELECT XMLAGG(sisum) FROM (
649 SELECT unapi.sisum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
650 FROM serial.index_summary
651 WHERE distribution = sdist.id
656 WHEN ('sssum' = ANY ($4)) THEN
657 (SELECT XMLAGG(sssum) FROM (
658 SELECT unapi.sssum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
659 FROM serial.supplement_summary
660 WHERE distribution = sdist.id
666 FROM serial.distribution sdist
668 GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
669 $F$ LANGUAGE SQL STABLE;
671 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$
675 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
676 'tag:open-ils.org:U2@sstr/' || id AS id,
679 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,
681 WHEN ('sitem' = ANY ($4)) THEN
682 XMLELEMENT( name items,
683 (SELECT XMLAGG(sitem) FROM (
684 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE)
686 WHERE stream = sstr.id
692 FROM serial.stream sstr
694 GROUP BY id, routing_label, distribution;
695 $F$ LANGUAGE SQL STABLE;
697 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$
701 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
702 'tag:open-ils.org:U2@siss/' || id AS id,
703 create_date, edit_date, label, date_published,
704 holding_code, holding_type, holding_link_id
706 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,
708 WHEN ('sitem' = ANY ($4)) THEN
709 XMLELEMENT( name items,
710 (SELECT XMLAGG(sitem) FROM (
711 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE)
713 WHERE issuance = sstr.id
719 FROM serial.issuance sstr
721 GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
722 $F$ LANGUAGE SQL STABLE;
724 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$
728 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
729 'tag:open-ils.org:U2@sitem/' || id AS id,
730 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
731 date_expected, date_received
733 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,
734 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,
735 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,
736 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
737 -- XMLELEMENT( name notes,
739 -- WHEN ('acpn' = ANY ($4)) THEN
740 -- (SELECT XMLAGG(acpn) FROM (
741 -- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8)
742 -- FROM asset.copy_note
743 -- WHERE owning_copy = cp.id AND pub
749 FROM serial.item sitem
751 $F$ LANGUAGE SQL STABLE;
754 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$
758 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
759 'tag:open-ils.org:U2@sbsum/' || id AS id,
760 'sssum' AS type, generated_coverage, textual_holdings, show_generated
762 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
764 FROM serial.supplement_summary ssum
766 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
767 $F$ LANGUAGE SQL STABLE;
769 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$
773 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
774 'tag:open-ils.org:U2@sbsum/' || id AS id,
775 'sbsum' AS type, generated_coverage, textual_holdings, show_generated
777 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
779 FROM serial.basic_summary ssum
781 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
782 $F$ LANGUAGE SQL STABLE;
784 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$
788 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
789 'tag:open-ils.org:U2@sbsum/' || id AS id,
790 'sisum' AS type, generated_coverage, textual_holdings, show_generated
792 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
794 FROM serial.index_summary ssum
796 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
797 $F$ LANGUAGE SQL STABLE;
800 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$
804 IF ename = 'circlib' THEN
808 'http://open-ils.org/spec/actors/v1' AS xmlns,
813 FROM actor.org_unit aou
816 EXECUTE $$SELECT XMLELEMENT(
817 name $$ || ename || $$,
819 'http://open-ils.org/spec/actors/v1' AS xmlns,
820 'tag:open-ils.org:U2@aou/' || id AS id,
821 shortname, name, opac_visible
824 FROM actor.org_unit aou
825 WHERE id = $1 $$ INTO output USING obj_id;
831 $F$ LANGUAGE PLPGSQL STABLE;
833 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$
837 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
841 label_prefix AS prefix,
842 label_suffix AS suffix
846 FROM asset.copy_location
848 $F$ LANGUAGE SQL STABLE;
850 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$
854 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
861 FROM config.copy_status
863 $F$ LANGUAGE SQL STABLE;
865 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$
869 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
877 $F$ LANGUAGE SQL STABLE;
879 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$
883 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
889 FROM asset.stat_cat_entry asce
890 JOIN asset.stat_cat sc ON (sc.id = asce.stat_cat)
892 $F$ LANGUAGE SQL STABLE;
894 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$
898 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
899 'tag:open-ils.org:U2@bmp/' || id AS id,
903 'tag:open-ils.org:U2@bre/' || record AS record
906 WHEN ('acp' = ANY ($4)) THEN
907 XMLELEMENT( name copies,
908 (SELECT XMLAGG(acp) FROM (
909 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE)
911 JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
913 AND cp.deleted IS FALSE
914 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
915 LIMIT ($7 -> 'acp')::INT
916 OFFSET ($8 -> 'acp')::INT
922 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
924 FROM biblio.monograph_part
926 GROUP BY id, label, label_sortkey, record;
927 $F$ LANGUAGE SQL STABLE;
929 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$
933 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
934 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
935 create_date, edit_date, copy_number, circulate, deposit,
936 ref, holdable, deleted, deposit_amount, price, barcode,
937 circ_modifier, circ_as_type, opac_visible, age_protect
939 unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
940 unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
941 unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
942 unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
943 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 WHEN ('acpn' = ANY ($4)) THEN
946 XMLELEMENT( name copy_notes,
947 (SELECT XMLAGG(acpn) FROM (
948 SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
950 WHERE owning_copy = cp.id AND pub
956 WHEN ('ascecm' = ANY ($4)) THEN
957 XMLELEMENT( name statcats,
958 (SELECT XMLAGG(ascecm) FROM (
959 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
960 FROM asset.stat_cat_entry_copy_map
961 WHERE owning_copy = cp.id
967 WHEN ('bre' = ANY ($4)) THEN
968 XMLELEMENT( name foreign_records,
969 (SELECT XMLAGG(bre) FROM (
970 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
971 FROM biblio.peer_bib_copy_map
972 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, deposit, ref, holdable,
1007 deleted, deposit_amount, price, barcode, circ_modifier,
1008 circ_as_type, opac_visible, age_protect;
1009 $F$ LANGUAGE SQL STABLE;
1011 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$
1015 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1016 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
1017 create_date, edit_date, copy_number, circulate, deposit,
1018 ref, holdable, deleted, deposit_amount, price, barcode,
1019 circ_modifier, circ_as_type, opac_visible, age_protect,
1020 status_changed_time, floating, mint_condition,
1021 detailed_contents, sort_key, summary_contents, cost
1023 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),
1024 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),
1025 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),
1026 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),
1027 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,
1028 XMLELEMENT( name copy_notes,
1030 WHEN ('acpn' = ANY ($4)) THEN
1031 (SELECT XMLAGG(acpn) FROM (
1032 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)
1033 FROM asset.copy_note
1034 WHERE owning_copy = cp.id AND pub
1039 XMLELEMENT( name statcats,
1041 WHEN ('ascecm' = ANY ($4)) THEN
1042 (SELECT XMLAGG(ascecm) FROM (
1043 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
1044 FROM asset.stat_cat_entry_copy_map
1045 WHERE owning_copy = cp.id
1050 XMLELEMENT( name foreign_records,
1052 WHEN ('bre' = ANY ($4)) THEN
1053 (SELECT XMLAGG(bre) FROM (
1054 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
1055 FROM biblio.peer_bib_copy_map
1056 WHERE target_copy = cp.id
1062 WHEN ('bmp' = ANY ($4)) THEN
1063 XMLELEMENT( name monograph_parts,
1064 (SELECT XMLAGG(bmp) FROM (
1065 SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
1066 FROM asset.copy_part_map
1067 WHERE target_copy = cp.id
1073 WHEN ('circ' = ANY ($4)) THEN
1074 XMLELEMENT( name current_circulation,
1075 (SELECT XMLAGG(circ) FROM (
1076 SELECT unapi.circ( id, 'xml', 'circ', evergreen.array_remove_item_by_value($4,'circ'), $5, $6, $7, $8, FALSE)
1077 FROM action.circulation
1078 WHERE target_copy = cp.id
1079 AND checkin_time IS NULL
1087 AND cp.deleted IS FALSE
1088 GROUP BY id, status, location, circ_lib, call_number, create_date,
1089 edit_date, copy_number, circulate, floating, mint_condition,
1090 deposit, ref, holdable, deleted, deposit_amount, price,
1091 barcode, circ_modifier, circ_as_type, opac_visible,
1092 status_changed_time, detailed_contents, sort_key,
1093 summary_contents, cost, age_protect;
1094 $F$ LANGUAGE SQL STABLE;
1096 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$
1100 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1101 'tag:open-ils.org:U2@acn/' || acn.id AS id,
1102 acn.id AS vol_id, o.shortname AS lib,
1103 o.opac_visible AS opac_visible,
1104 deleted, label, label_sortkey, label_class, record
1106 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8),
1108 WHEN ('acp' = ANY ($4)) THEN
1109 CASE WHEN $6 IS NOT NULL THEN
1110 XMLELEMENT( name copies,
1111 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
1112 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1113 evergreen.rank_cp_status(cp.status) AS rank_avail
1115 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id)
1116 WHERE cp.call_number = acn.id
1117 AND cp.deleted IS FALSE
1118 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
1119 LIMIT ($7 -> 'acp')::INT
1120 OFFSET ($8 -> 'acp')::INT
1124 XMLELEMENT( name copies,
1125 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
1126 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1127 evergreen.rank_cp_status(cp.status) AS rank_avail
1129 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id)
1130 WHERE cp.call_number = acn.id
1131 AND cp.deleted IS FALSE
1132 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
1133 LIMIT ($7 -> 'acp')::INT
1134 OFFSET ($8 -> 'acp')::INT
1142 (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)
1144 unapi.acnp( acn.prefix, 'marcxml', 'prefix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1145 unapi.acns( acn.suffix, 'marcxml', 'suffix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
1146 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
1148 FROM asset.call_number acn
1149 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
1151 AND acn.deleted IS FALSE
1152 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
1153 $F$ LANGUAGE SQL STABLE;
1155 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$
1157 name call_number_prefix,
1159 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1162 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
1166 FROM asset.call_number_prefix
1168 $F$ LANGUAGE SQL STABLE;
1170 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$
1172 name call_number_suffix,
1174 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1177 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
1181 FROM asset.call_number_suffix
1183 $F$ LANGUAGE SQL STABLE;
1185 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$
1189 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1190 'tag:open-ils.org:U2@auri/' || uri.id AS id,
1196 WHEN ('acn' = ANY ($4)) THEN
1197 XMLELEMENT( name copies,
1198 (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)
1205 GROUP BY uri.id, use_restriction, href, label;
1206 $F$ LANGUAGE SQL STABLE;
1208 CREATE OR REPLACE FUNCTION unapi.mra (
1214 depth INT DEFAULT NULL,
1215 slimit HSTORE DEFAULT NULL,
1216 soffset HSTORE DEFAULT NULL,
1217 include_xmlns BOOL DEFAULT TRUE
1218 ) RETURNS XML AS $F$
1222 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
1223 'tag:open-ils.org:U2@mra/' || $1 AS id,
1224 'tag:open-ils.org:U2@bre/' || $1 AS record
1226 (SELECT XMLAGG(foo.y)
1232 cvm.value AS "coded-value",
1241 FROM metabib.record_attr_flat mra
1242 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
1243 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
1248 $F$ LANGUAGE SQL STABLE;
1250 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$
1254 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1255 'tag:open-ils.org:U2@circ/' || id AS id,
1259 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,
1260 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
1262 FROM action.circulation
1264 $F$ LANGUAGE SQL STABLE;
1266 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
1272 depth INT DEFAULT NULL,
1273 slimit HSTORE DEFAULT NULL,
1274 soffset HSTORE DEFAULT NULL,
1275 include_xmlns BOOL DEFAULT TRUE,
1276 pref_lib INT DEFAULT NULL
1277 ) RETURNS XML AS $F$
1281 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
1282 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
1284 (SELECT XMLAGG(foo.y)
1286 SELECT DISTINCT ON (COALESCE(cvm.id,uvm.id))
1287 COALESCE(cvm.id,uvm.id),
1292 cvm.value AS "coded-value",
1301 FROM metabib.record_attr_flat mra
1302 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
1303 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
1304 LEFT JOIN metabib.uncontrolled_record_attr_value uvm ON (uvm.attr = mra.attr AND uvm.value = mra.value)
1306 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id
1307 FROM actor.org_unit WHERE shortname = $5 LIMIT 1)
1309 FROM metabib.metarecord_source_map, aou
1310 WHERE metarecord = $1 AND (
1312 SELECT 1 FROM asset.opac_visible_copies
1313 WHERE record = source AND circ_lib IN (
1314 SELECT id FROM actor.org_unit_descendants(aou.id, $6))
1317 OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1)
1324 $F$ LANGUAGE SQL STABLE;
1326 CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml (
1330 depth INT DEFAULT NULL,
1331 includes TEXT[] DEFAULT NULL::TEXT[],
1332 slimit HSTORE DEFAULT NULL,
1333 soffset HSTORE DEFAULT NULL,
1334 include_xmlns BOOL DEFAULT TRUE,
1335 pref_lib INT DEFAULT NULL
1341 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
1342 CASE WHEN ('mmr' = ANY ($5)) THEN 'tag:open-ils.org:U2@mmr/' || $1 || '/' || $3 ELSE NULL END AS id,
1343 (SELECT metarecord_has_holdable_copy FROM asset.metarecord_has_holdable_copy($1)) AS has_holdable
1347 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
1350 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
1352 FROM asset.opac_ou_metarecord_copy_count($2, $1)
1356 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
1358 FROM asset.staff_ou_metarecord_copy_count($2, $1)
1362 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
1364 FROM asset.opac_ou_metarecord_copy_count($9, $1)
1368 -- XXX monograph_parts and foreign_copies are skipped in MRs ... put them back some day?
1371 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
1373 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
1374 FROM evergreen.ranked_volumes((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $4, $6, $7, $9, $5) AS y
1377 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
1378 FROM evergreen.located_uris((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $9) AS uris
1381 CASE WHEN ('ssub' = ANY ($5)) THEN
1384 (SELECT XMLAGG(ssub) FROM (
1385 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
1386 FROM serial.subscription
1387 WHERE record_entry IN (SELECT source FROM metabib.metarecord_source_map WHERE metarecord = $1)
1392 $F$ LANGUAGE SQL STABLE;
1394 CREATE OR REPLACE FUNCTION unapi.mmr (
1400 depth INT DEFAULT NULL,
1401 slimit HSTORE DEFAULT NULL,
1402 soffset HSTORE DEFAULT NULL,
1403 include_xmlns BOOL DEFAULT TRUE,
1404 pref_lib INT DEFAULT NULL
1408 mmrec metabib.metarecord%ROWTYPE;
1409 leadrec biblio.record_entry%ROWTYPE;
1410 subrec biblio.record_entry%ROWTYPE;
1411 layout unapi.bre_output_layout%ROWTYPE;
1412 xfrm config.xml_transform%ROWTYPE;
1414 xml_buf TEXT; -- growing XML document
1415 tmp_xml TEXT; -- single-use XML string
1416 xml_frag TEXT; -- single-use XML fragment
1421 subxml XML; -- subordinate records elements
1426 -- xpath for extracting bre.marc values from subordinate records
1427 -- so they may be appended to the MARC of the master record prior
1428 -- to XSLT processing.
1429 -- subjects, isbn, issn, upc -- anything else?
1431 '//*[starts-with(@tag, "6") or @tag="020" or @tag="022" or @tag="024"]';
1433 IF org = '-' OR org IS NULL THEN
1434 SELECT shortname INTO org FROM evergreen.org_top();
1437 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
1439 IF ouid IS NULL THEN
1443 SELECT INTO mmrec * FROM metabib.metarecord WHERE id = obj_id;
1448 -- TODO: aggregate holdings from constituent records
1449 IF format = 'holdings_xml' THEN -- the special case
1450 output := unapi.mmr_holdings_xml(
1451 obj_id, ouid, org, depth,
1452 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
1453 slimit, soffset, include_xmlns, pref_lib);
1457 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
1459 IF layout.name IS NULL THEN
1463 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
1465 SELECT INTO leadrec * FROM biblio.record_entry WHERE id = mmrec.master_record;
1467 -- Grab distinct MVF for all records if requested
1468 IF ('mra' = ANY (includes)) THEN
1469 axml := unapi.mmr_mra(obj_id,NULL,NULL,NULL,org,depth,NULL,NULL,TRUE,pref_lib);
1474 xml_buf = leadrec.marc;
1477 IF ('holdings_xml' = ANY (includes)) THEN
1478 hxml := unapi.mmr_holdings_xml(
1479 obj_id, ouid, org, depth,
1480 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
1481 slimit, soffset, include_xmlns, pref_lib);
1484 subxml := NULL::XML;
1485 parts := '{}'::TEXT[];
1486 FOR subrec IN SELECT bre.* FROM biblio.record_entry bre
1487 JOIN metabib.metarecord_source_map mmsm ON (mmsm.source = bre.id)
1488 JOIN metabib.metarecord mmr ON (mmr.id = mmsm.metarecord)
1489 WHERE mmr.id = obj_id AND NOT bre.deleted
1490 ORDER BY CASE WHEN bre.id = mmr.master_record THEN 0 ELSE bre.id END
1491 LIMIT COALESCE((slimit->'bre')::INT, 5) LOOP
1493 IF subrec.id = leadrec.id THEN CONTINUE; END IF;
1494 -- Append choice data from the the non-lead records to the
1495 -- the lead record document
1497 parts := parts || xpath(sub_xpath, subrec.marc::XML)::TEXT[];
1500 SELECT ARRAY_TO_STRING( ARRAY_AGG( DISTINCT p ), '' )::XML INTO subxml FROM UNNEST(parts) p;
1502 -- append data from the subordinate records to the
1503 -- main record document before applying the XSLT
1505 IF subxml IS NOT NULL THEN
1506 xml_buf := REGEXP_REPLACE(xml_buf,
1507 '</record>(.*?)$', subxml || '</record>' || E'\\1');
1510 IF format = 'marcxml' THEN
1511 -- If we're not using the prefixed namespace in
1512 -- this record, then remove all declarations of it
1513 IF xml_buf !~ E'<marc:' THEN
1514 xml_buf := REGEXP_REPLACE(xml_buf,
1515 ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
1518 xml_buf := oils_xslt_process(xml_buf, xfrm.xslt)::XML;
1521 -- update top_el to reflect the change in xml_buf, which may
1522 -- now be a different type of document (e.g. record -> mods)
1523 top_el := REGEXP_REPLACE(xml_buf, E'^.*?<((?:\\S+:)?' ||
1524 layout.holdings_element || ').*$', E'\\1');
1526 IF axml IS NOT NULL THEN
1527 xml_buf := REGEXP_REPLACE(xml_buf,
1528 '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
1531 IF hxml IS NOT NULL THEN
1532 xml_buf := REGEXP_REPLACE(xml_buf,
1533 '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
1536 IF ('mmr.unapi' = ANY (includes)) THEN
1537 output := REGEXP_REPLACE(
1539 '</' || top_el || '>(.*?)',
1543 'http://www.w3.org/1999/xhtml' AS xmlns,
1544 'unapi-id' AS class,
1545 'tag:open-ils.org:U2@mmr/' || obj_id || '/' || org AS title
1547 )::TEXT || '</' || top_el || E'>\\1'
1553 -- remove ignorable whitesace
1554 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
1557 $F$ LANGUAGE PLPGSQL STABLE;
1562 -- Some test queries
1564 SELECT unapi.memoize( 'bre', 1,'mods32','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1565 SELECT unapi.memoize( 'bre', 1,'marcxml','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1566 SELECT unapi.memoize( 'bre', 1,'holdings_xml','','{holdings_xml,acp}'::TEXT[], 'SYS1');
1568 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>');
1570 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>');
1571 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>');
1572 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>');
1573 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>');
1575 SELECT unapi.biblio_record_entry_feed('{216}'::BIGINT[],'marcxml','{}'::TEXT[], 'BR1');
1576 EXPLAIN ANALYZE SELECT unapi.bre(216,'marcxml','record','{holdings_xml,bre.unapi}'::TEXT[], 'BR1');
1577 EXPLAIN ANALYZE SELECT unapi.bre(216,'holdings_xml','record','{}'::TEXT[], 'BR1');
1578 EXPLAIN ANALYZE SELECT unapi.holdings_xml(216,4,'BR1',2,'{bre}'::TEXT[]);
1579 EXPLAIN ANALYZE SELECT unapi.bre(216,'mods32','record','{}'::TEXT[], 'BR1');
1581 -- Limit to 5 call numbers, 5 copies, with a preferred library of 4 (BR1), in SYS2 at a depth of 0
1582 EXPLAIN ANALYZE SELECT unapi.bre(36,'marcxml','record','{holdings_xml,mra,acp,acnp,acns,bmp}','SYS2',0,'acn=>5,acp=>5',NULL,TRUE,4);