1 -- Evergreen DB patch XXXX.schema.unAPI_XMLAGG_cleanup.sql
3 -- FIXME: insert description of change, if needed
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0566', :eg_version);
11 CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
13 me biblio.record_entry%ROWTYPE;
14 layout unapi.bre_output_layout%ROWTYPE;
15 xfrm config.xml_transform%ROWTYPE;
24 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
30 IF format = 'holdings_xml' THEN -- the special case
31 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
35 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
37 IF layout.name IS NULL THEN
41 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
43 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
45 -- grab SVF if we need them
46 IF ('mra' = ANY (includes)) THEN
47 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
52 -- grab hodlings if we need them
53 IF ('holdings_xml' = ANY (includes)) THEN
54 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns);
60 -- generate our item node
63 IF format = 'marcxml' THEN
65 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
66 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
69 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
72 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
74 IF axml IS NOT NULL THEN
75 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
78 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
79 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
82 IF ('bre.unapi' = ANY (includes)) THEN
83 output := REGEXP_REPLACE(
85 '</' || top_el || '>(.*?)',
89 'http://www.w3.org/1999/xhtml' AS xmlns,
91 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
93 )::TEXT || '</' || top_el || E'>\\1'
99 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
102 $F$ LANGUAGE PLPGSQL;
104 CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$
108 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
109 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id
113 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
116 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
118 FROM asset.opac_ou_record_copy_count($2, $1)
122 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
124 FROM asset.staff_ou_record_copy_count($2, $1)
129 WHEN ('bmp' = ANY ($5)) THEN
131 name monograph_parts,
132 (SELECT XMLAGG(bmp) FROM (
133 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)
134 FROM biblio.monograph_part
142 (SELECT XMLAGG(acn) FROM (
143 SELECT unapi.acn(acn.id,'xml','volume',array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE)
144 FROM asset.call_number acn
145 WHERE acn.record = $1
149 JOIN actor.org_unit_descendants(
154 FROM actor.org_unit_type aout
155 JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.id = $2)
158 ) aoud ON (acp.circ_lib = aoud.id)
161 ORDER BY label_sortkey
166 CASE WHEN ('ssub' = ANY ($5)) THEN
169 (SELECT XMLAGG(ssub) FROM (
170 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
171 FROM serial.subscription
172 WHERE record_entry = $1
176 CASE WHEN ('acp' = ANY ($5)) THEN
179 (SELECT XMLAGG(acp) FROM (
180 SELECT unapi.acp(p.target_copy,'xml','copy','{}'::TEXT[], $3, $4, $6, $7, FALSE)
181 FROM biblio.peer_bib_copy_map p
182 JOIN asset.copy c ON (p.target_copy = c.id)
183 WHERE NOT c.deleted AND peer_record = $1
190 CREATE OR REPLACE FUNCTION unapi.ssub ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
194 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
195 'tag:open-ils.org:U2@ssub/' || id AS id,
196 start_date AS start, end_date AS end, expected_date_offset
198 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8),
199 XMLELEMENT( name distributions,
201 WHEN ('sdist' = ANY ($4)) THEN
202 (SELECT XMLAGG(sdist) FROM (
203 SELECT unapi.sdist( id, 'xml', 'distribution', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8, FALSE)
204 FROM serial.distribution
205 WHERE subscription = ssub.id
211 FROM serial.subscription ssub
213 GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
216 CREATE OR REPLACE FUNCTION unapi.sdist ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
220 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
221 'tag:open-ils.org:U2@sdist/' || id AS id,
222 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
223 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
224 unit_label_prefix, label, unit_label_suffix, summary_method
226 unapi.aou( holding_lib, $2, 'holding_lib', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8),
227 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,
228 XMLELEMENT( name streams,
230 WHEN ('sstr' = ANY ($4)) THEN
231 (SELECT XMLAGG(sstr) FROM (
232 SELECT unapi.sstr( id, 'xml', 'stream', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
234 WHERE distribution = sdist.id
239 XMLELEMENT( name summaries,
241 WHEN ('ssum' = ANY ($4)) THEN
242 (SELECT XMLAGG(sbsum) FROM (
243 SELECT unapi.sbsum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
244 FROM serial.basic_summary
245 WHERE distribution = sdist.id
250 WHEN ('ssum' = ANY ($4)) THEN
251 (SELECT XMLAGG(sisum) FROM (
252 SELECT unapi.sisum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
253 FROM serial.index_summary
254 WHERE distribution = sdist.id
259 WHEN ('ssum' = ANY ($4)) THEN
260 (SELECT XMLAGG(sssum) FROM (
261 SELECT unapi.sssum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
262 FROM serial.supplement_summary
263 WHERE distribution = sdist.id
269 FROM serial.distribution sdist
271 GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
274 CREATE OR REPLACE FUNCTION unapi.sstr ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
278 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
279 'tag:open-ils.org:U2@sstr/' || id AS id,
282 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,
283 XMLELEMENT( name items,
285 WHEN ('sitem' = ANY ($4)) THEN
286 (SELECT XMLAGG(sitem) FROM (
287 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE)
289 WHERE stream = sstr.id
295 FROM serial.stream sstr
297 GROUP BY id, routing_label, distribution;
300 CREATE OR REPLACE FUNCTION unapi.siss ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
304 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
305 'tag:open-ils.org:U2@siss/' || id AS id,
306 create_date, edit_date, label, date_published,
307 holding_code, holding_type, holding_link_id
309 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,
310 XMLELEMENT( name items,
312 WHEN ('sitem' = ANY ($4)) THEN
313 (SELECT XMLAGG(sitem) FROM (
314 SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE)
316 WHERE issuance = sstr.id
322 FROM serial.issuance sstr
324 GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
327 CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
331 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
332 'tag:open-ils.org:U2@sitem/' || id AS id,
333 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
334 date_expected, date_received
336 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,
337 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,
338 CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( stream, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
339 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
340 -- XMLELEMENT( name notes,
342 -- WHEN ('acpn' = ANY ($4)) THEN
343 -- (SELECT XMLAGG(acpn) FROM (
344 -- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8)
345 -- FROM asset.copy_note
346 -- WHERE owning_copy = cp.id AND pub
352 FROM serial.item sitem
357 CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
361 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
362 'tag:open-ils.org:U2@bmp/' || id AS id,
366 'tag:open-ils.org:U2@bre/' || record AS record
369 WHEN ('acp' = ANY ($4)) THEN
370 XMLELEMENT( name copies,
371 (SELECT XMLAGG(acp) FROM (
372 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE)
374 JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
376 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
383 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
385 FROM biblio.monograph_part
387 GROUP BY id, label, label_sortkey, record;
390 CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
394 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
395 'tag:open-ils.org:U2@acp/' || id AS id,
396 create_date, edit_date, copy_number, circulate, deposit,
397 ref, holdable, deleted, deposit_amount, price, barcode,
398 circ_modifier, circ_as_type, opac_visible
400 unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
401 unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
402 unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
403 unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
404 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,
405 XMLELEMENT( name copy_notes,
407 WHEN ('acpn' = ANY ($4)) THEN
408 (SELECT XMLAGG(acpn) FROM (
409 SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
411 WHERE owning_copy = cp.id AND pub
416 XMLELEMENT( name statcats,
418 WHEN ('ascecm' = ANY ($4)) THEN
419 (SELECT XMLAGG(ascecm) FROM (
420 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
421 FROM asset.stat_cat_entry_copy_map
422 WHERE owning_copy = cp.id
427 XMLELEMENT( name foreign_records,
429 WHEN ('bre' = ANY ($4)) THEN
430 (SELECT XMLAGG(bre) FROM (
431 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
432 FROM biblio.peer_bib_copy_map
433 WHERE target_copy = cp.id
440 WHEN ('bmp' = ANY ($4)) THEN
441 XMLELEMENT( name monograph_parts,
442 (SELECT XMLAGG(bmp) FROM (
443 SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
444 FROM asset.copy_part_map
445 WHERE target_copy = cp.id
453 GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible;
456 CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
460 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
461 'tag:open-ils.org:U2@acp/' || id AS id,
462 create_date, edit_date, copy_number, circulate, deposit,
463 ref, holdable, deleted, deposit_amount, price, barcode,
464 circ_modifier, circ_as_type, opac_visible, status_changed_time,
465 floating, mint_condition, detailed_contents, sort_key, summary_contents, cost
467 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),
468 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),
469 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),
470 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),
471 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,
472 XMLELEMENT( name copy_notes,
474 WHEN ('acpn' = ANY ($4)) THEN
475 (SELECT XMLAGG(acpn) FROM (
476 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)
478 WHERE owning_copy = cp.id AND pub
483 XMLELEMENT( name statcats,
485 WHEN ('ascecm' = ANY ($4)) THEN
486 (SELECT XMLAGG(ascecm) FROM (
487 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
488 FROM asset.stat_cat_entry_copy_map
489 WHERE owning_copy = cp.id
494 XMLELEMENT( name foreign_records,
496 WHEN ('bre' = ANY ($4)) THEN
497 (SELECT XMLAGG(bre) FROM (
498 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
499 FROM biblio.peer_bib_copy_map
500 WHERE target_copy = cp.id
507 WHEN ('bmp' = ANY ($4)) THEN
508 XMLELEMENT( name monograph_parts,
509 (SELECT XMLAGG(bmp) FROM (
510 SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
511 FROM asset.copy_part_map
512 WHERE target_copy = cp.id
520 GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, floating, mint_condition,
521 deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible, status_changed_time, detailed_contents, sort_key, summary_contents, cost;