1 --Upgrade Script for 3.6.3 to 3.6.4
2 \set eg_version '''3.6.4'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.6.4', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1259', :eg_version);
8 INSERT INTO action_trigger.environment (event_def,path)
9 SELECT id,'items' from action_trigger.event_definition WHERE name='biblio.record_entry.print.full'
10 AND NOT EXISTS (SELECT 1 FROM action_trigger.environment WHERE
11 event_def=(SELECT id FROM action_trigger.event_definition WHERE name ='biblio.record_entry.print.full' AND owner=1 LIMIT 1)
14 INSERT INTO action_trigger.environment (event_def,path)
15 SELECT id,'items' from action_trigger.event_definition WHERE name='biblio.record_entry.email.full'
16 AND NOT EXISTS (SELECT 1 FROM action_trigger.environment WHERE
17 event_def=(SELECT id FROM action_trigger.event_definition WHERE name ='biblio.record_entry.email.full' AND owner=1 LIMIT 1)
20 INSERT INTO action_trigger.environment (event_def,path)
21 SELECT id,'owner' from action_trigger.event_definition WHERE name='biblio.record_entry.email.full'
22 AND NOT EXISTS (SELECT 1 FROM action_trigger.environment WHERE
23 event_def=(SELECT id FROM action_trigger.event_definition WHERE name ='biblio.record_entry.email.full' AND owner=1 LIMIT 1)
27 SELECT evergreen.upgrade_deps_block_check('1261', :eg_version);
29 CREATE OR REPLACE FUNCTION evergreen.located_uris_as_uris
30 (bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL)
31 RETURNS SETOF asset.uri AS $FUNK$
32 /* Maps a bib directly to its scoped asset.uri's */
35 FROM evergreen.located_uris($1, $2, $3) located_uri
36 JOIN asset.uri_call_number_map map ON (map.call_number = located_uri.id)
37 JOIN asset.uri uri ON (uri.id = map.uri)
39 $FUNK$ LANGUAGE SQL STABLE;
42 SELECT evergreen.upgrade_deps_block_check('1262', :eg_version);
44 CREATE OR REPLACE FUNCTION search.highlight_display_fields_impl(
47 field_list INT[] DEFAULT '{}'::INT[],
48 css_class TEXT DEFAULT 'oils_SH',
49 hl_all BOOL DEFAULT TRUE,
50 minwords INT DEFAULT 5,
51 maxwords INT DEFAULT 25,
52 shortwords INT DEFAULT 0,
53 maxfrags INT DEFAULT 0,
54 delimiter TEXT DEFAULT ' ... '
55 ) RETURNS SETOF search.highlight_result AS $f$
58 v_css_class TEXT := css_class;
59 v_delimiter TEXT := delimiter;
60 v_field_list INT[] := field_list;
63 IF v_delimiter LIKE $$%'%$$ OR v_delimiter LIKE '%"%' THEN --"
64 v_delimiter := ' ... ';
68 opts := opts || 'MinWords=' || minwords;
69 opts := opts || ', MaxWords=' || maxwords;
70 opts := opts || ', ShortWords=' || shortwords;
71 opts := opts || ', MaxFragments=' || maxfrags;
72 opts := opts || ', FragmentDelimiter="' || delimiter || '"';
74 opts := opts || 'HighlightAll=TRUE';
77 IF v_css_class LIKE $$%'%$$ OR v_css_class LIKE '%"%' THEN -- "
78 v_css_class := 'oils_SH';
81 opts := opts || $$, StopSel=</b>, StartSel="<b class='$$ || v_css_class; -- "
83 IF v_field_list = '{}'::INT[] THEN
84 SELECT ARRAY_AGG(id) INTO v_field_list FROM config.metabib_field WHERE display_field;
91 evergreen.escape_for_html(de.value) AS value,
94 evergreen.escape_for_html(de.value),
95 $$ || quote_literal(tsq) || $$,
96 $1 || ' ' || mf.field_class || ' ' || mf.name || $xx$'>"$xx$ -- "'
98 FROM metabib.display_entry de
99 JOIN config.metabib_field mf ON (mf.id = de.field)
100 JOIN search.best_tsconfig t ON (t.id = de.field)
105 RETURN QUERY EXECUTE hl_query USING opts, rid, v_field_list;
107 $f$ LANGUAGE PLPGSQL;
109 CREATE OR REPLACE FUNCTION search.highlight_display_fields(
111 tsq_map TEXT, -- { '(a | b) & c' => '1,2,3,4', ...}
112 css_class TEXT DEFAULT 'oils_SH',
113 hl_all BOOL DEFAULT TRUE,
114 minwords INT DEFAULT 5,
115 maxwords INT DEFAULT 25,
116 shortwords INT DEFAULT 0,
117 maxfrags INT DEFAULT 0,
118 delimiter TEXT DEFAULT ' ... '
119 ) RETURNS SETOF search.highlight_result AS $f$
127 IF (tsq_map ILIKE 'hstore%') THEN
128 EXECUTE 'SELECT ' || tsq_map INTO tsq_hstore;
130 tsq_hstore := tsq_map::HSTORE;
133 FOR tsq, fields IN SELECT key, value FROM each(tsq_hstore::HSTORE) LOOP
134 SELECT ARRAY_AGG(unnest::INT) INTO afields
135 FROM unnest(regexp_split_to_array(fields,','));
136 seen := seen || afields;
139 SELECT * FROM search.highlight_display_fields_impl(
140 rid, tsq, afields, css_class, hl_all,minwords,
141 maxwords, shortwords, maxfrags, delimiter
149 evergreen.escape_for_html(value) AS value,
150 evergreen.escape_for_html(value) AS highlight
151 FROM metabib.display_entry
153 AND NOT (field = ANY (seen));
155 $f$ LANGUAGE PLPGSQL ROWS 10;
158 SELECT evergreen.upgrade_deps_block_check('1263', :eg_version);
160 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
162 'eg.catalog.search.form.open', 'gui', 'bool',
164 'eg.catalog.search.form.open',
165 'Catalog Search Form Visibility Sticky Setting',
171 SELECT evergreen.upgrade_deps_block_check('1264', :eg_version);
173 CREATE OR REPLACE FUNCTION unapi.bre (
179 depth INT DEFAULT NULL,
180 slimit HSTORE DEFAULT NULL,
181 soffset HSTORE DEFAULT NULL,
182 include_xmlns BOOL DEFAULT TRUE,
183 pref_lib INT DEFAULT NULL
187 me biblio.record_entry%ROWTYPE;
188 layout unapi.bre_output_layout%ROWTYPE;
189 xfrm config.xml_transform%ROWTYPE;
199 IF org = '-' OR org IS NULL THEN
200 SELECT shortname INTO org FROM evergreen.org_top();
203 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
209 IF format = 'holdings_xml' THEN -- the special case
210 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
214 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
216 IF layout.name IS NULL THEN
220 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
222 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
224 -- grab bib_source, if any
225 IF ('cbs' = ANY (includes) AND me.source IS NOT NULL) THEN
226 source := unapi.cbs(me.source,NULL,NULL,NULL,NULL);
231 -- grab SVF if we need them
232 IF ('mra' = ANY (includes)) THEN
233 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
238 -- grab holdings if we need them
239 IF ('holdings_xml' = ANY (includes)) THEN
240 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, array_remove(includes,'holdings_xml'), slimit, soffset, include_xmlns, pref_lib);
246 -- generate our item node
249 IF format = 'marcxml' THEN
251 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
252 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
255 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
258 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
260 IF source IS NOT NULL THEN
261 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', source || '</' || top_el || E'>\\1');
264 IF axml IS NOT NULL THEN
265 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
268 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
269 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
272 IF ('bre.unapi' = ANY (includes)) THEN
273 output := REGEXP_REPLACE(
275 '</' || top_el || '>(.*?)',
279 'http://www.w3.org/1999/xhtml' AS xmlns,
281 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
283 )::TEXT || '</' || top_el || E'>\\1'
289 IF ('bre.extern' = ANY (includes)) THEN
290 output := REGEXP_REPLACE(
292 '</' || top_el || '>(.*?)',
296 'http://open-ils.org/spec/biblio/v1' AS xmlns,
297 me.creator AS creator,
299 me.create_date AS create_date,
300 me.edit_date AS edit_date,
301 me.quality AS quality,
302 me.fingerprint AS fingerprint,
303 me.tcn_source AS tcn_source,
304 me.tcn_value AS tcn_value,
306 me.share_depth AS share_depth,
308 me.deleted AS deleted
310 )::TEXT || '</' || top_el || E'>\\1'
316 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
319 $F$ LANGUAGE PLPGSQL STABLE;
321 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
325 depth INT DEFAULT NULL,
326 includes TEXT[] DEFAULT NULL::TEXT[],
327 slimit HSTORE DEFAULT NULL,
328 soffset HSTORE DEFAULT NULL,
329 include_xmlns BOOL DEFAULT TRUE,
330 pref_lib INT DEFAULT NULL
336 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
337 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
338 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
342 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
345 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
347 FROM asset.opac_ou_record_copy_count($2, $1)
351 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
353 FROM asset.staff_ou_record_copy_count($2, $1)
357 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
359 FROM asset.opac_ou_record_copy_count($9, $1)
364 WHEN ('bmp' = ANY ($5)) THEN
366 name monograph_parts,
367 (SELECT XMLAGG(bmp) FROM (
368 SELECT unapi.bmp( id, 'xml', 'monograph_part', array_remove( array_remove($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
369 FROM biblio.monograph_part
370 WHERE NOT deleted AND record = $1
377 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
379 SELECT unapi.acn(y.id,'xml','volume',array_remove( array_remove($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
380 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
383 SELECT unapi.acn(uris.id,'xml','volume',array_remove( array_remove($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), uris.rank, name, label_sortkey
384 FROM evergreen.located_uris($1, $2, $9) AS uris
387 CASE WHEN ('ssub' = ANY ($5)) THEN
390 (SELECT XMLAGG(ssub) FROM (
391 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
392 FROM serial.subscription
393 WHERE record_entry = $1
397 CASE WHEN ('acp' = ANY ($5)) THEN
400 (SELECT XMLAGG(acp) FROM (
401 SELECT unapi.acp(p.target_copy,'xml','copy',array_remove($5,'acp'), $3, $4, $6, $7, FALSE)
402 FROM biblio.peer_bib_copy_map p
403 JOIN asset.copy c ON (p.target_copy = c.id)
404 WHERE NOT c.deleted AND p.peer_record = $1
405 LIMIT ($6 -> 'acp')::INT
406 OFFSET ($7 -> 'acp')::INT
411 $F$ LANGUAGE SQL STABLE;
413 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$
417 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
418 'tag:open-ils.org:U2@ssub/' || id AS id,
419 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
420 start_date AS start, end_date AS end, expected_date_offset
423 WHEN ('sdist' = ANY ($4)) THEN
424 XMLELEMENT( name distributions,
425 (SELECT XMLAGG(sdist) FROM (
426 SELECT unapi.sdist( id, 'xml', 'distribution', array_remove($4,'ssub'), $5, $6, $7, $8, FALSE)
427 FROM serial.distribution
428 WHERE subscription = ssub.id
434 FROM serial.subscription ssub
436 GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
437 $F$ LANGUAGE SQL STABLE;
439 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$
443 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
444 'tag:open-ils.org:U2@sdist/' || id AS id,
445 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
446 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
447 unit_label_prefix, label, unit_label_suffix, summary_method
449 unapi.aou( holding_lib, $2, 'holding_lib', array_remove($4,'sdist'), $5, $6, $7, $8),
450 CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE) ELSE NULL END,
452 WHEN ('sstr' = ANY ($4)) THEN
453 XMLELEMENT( name streams,
454 (SELECT XMLAGG(sstr) FROM (
455 SELECT unapi.sstr( id, 'xml', 'stream', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
457 WHERE distribution = sdist.id
462 XMLELEMENT( name summaries,
464 WHEN ('sbsum' = ANY ($4)) THEN
465 (SELECT XMLAGG(sbsum) FROM (
466 SELECT unapi.sbsum( id, 'xml', 'serial_summary', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
467 FROM serial.basic_summary
468 WHERE distribution = sdist.id
473 WHEN ('sisum' = ANY ($4)) THEN
474 (SELECT XMLAGG(sisum) FROM (
475 SELECT unapi.sisum( id, 'xml', 'serial_summary', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
476 FROM serial.index_summary
477 WHERE distribution = sdist.id
482 WHEN ('sssum' = ANY ($4)) THEN
483 (SELECT XMLAGG(sssum) FROM (
484 SELECT unapi.sssum( id, 'xml', 'serial_summary', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
485 FROM serial.supplement_summary
486 WHERE distribution = sdist.id
492 FROM serial.distribution sdist
494 GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
495 $F$ LANGUAGE SQL STABLE;
497 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$
501 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
502 'tag:open-ils.org:U2@sstr/' || id AS id,
505 CASE WHEN distribution IS NOT NULL AND ('sdist' = ANY ($4)) THEN unapi.sssum( distribution, 'xml', 'distribtion', array_remove($4,'sstr'), $5, $6, $7, $8, FALSE) ELSE NULL END,
507 WHEN ('sitem' = ANY ($4)) THEN
508 XMLELEMENT( name items,
509 (SELECT XMLAGG(sitem) FROM (
510 SELECT unapi.sitem( id, 'xml', 'serial_item', array_remove($4,'sstr'), $5, $6, $7, $8, FALSE)
512 WHERE stream = sstr.id
518 FROM serial.stream sstr
520 GROUP BY id, routing_label, distribution;
521 $F$ LANGUAGE SQL STABLE;
523 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$
527 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
528 'tag:open-ils.org:U2@siss/' || id AS id,
529 create_date, edit_date, label, date_published,
530 holding_code, holding_type, holding_link_id
532 CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', array_remove($4,'siss'), $5, $6, $7, $8, FALSE) ELSE NULL END,
534 WHEN ('sitem' = ANY ($4)) THEN
535 XMLELEMENT( name items,
536 (SELECT XMLAGG(sitem) FROM (
537 SELECT unapi.sitem( id, 'xml', 'serial_item', array_remove($4,'siss'), $5, $6, $7, $8, FALSE)
539 WHERE issuance = sstr.id
545 FROM serial.issuance sstr
547 GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
548 $F$ LANGUAGE SQL STABLE;
550 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$
554 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
555 'tag:open-ils.org:U2@sitem/' || id AS id,
556 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
557 date_expected, date_received
559 CASE WHEN issuance IS NOT NULL AND ('siss' = ANY ($4)) THEN unapi.siss( issuance, $2, 'issuance', array_remove($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
560 CASE WHEN stream IS NOT NULL AND ('sstr' = ANY ($4)) THEN unapi.sstr( stream, $2, 'stream', array_remove($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
561 CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( unit, $2, 'serial_unit', array_remove($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
562 CASE WHEN uri IS NOT NULL AND ('auri' = ANY ($4)) THEN unapi.auri( uri, $2, 'uri', array_remove($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END
563 -- XMLELEMENT( name notes,
565 -- WHEN ('acpn' = ANY ($4)) THEN
566 -- (SELECT XMLAGG(acpn) FROM (
567 -- SELECT unapi.acpn( id, 'xml', 'copy_note', array_remove($4,'acp'), $5, $6, $7, $8)
568 -- FROM asset.copy_note
569 -- WHERE owning_copy = cp.id AND pub
575 FROM serial.item sitem
577 $F$ LANGUAGE SQL STABLE;
580 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$
584 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
585 'tag:open-ils.org:U2@sbsum/' || id AS id,
586 'sssum' AS type, generated_coverage, textual_holdings, show_generated
588 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', array_remove($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
590 FROM serial.supplement_summary ssum
592 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
593 $F$ LANGUAGE SQL STABLE;
595 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$
599 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
600 'tag:open-ils.org:U2@sbsum/' || id AS id,
601 'sbsum' AS type, generated_coverage, textual_holdings, show_generated
603 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', array_remove($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
605 FROM serial.basic_summary ssum
607 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
608 $F$ LANGUAGE SQL STABLE;
610 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$
614 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
615 'tag:open-ils.org:U2@sbsum/' || id AS id,
616 'sisum' AS type, generated_coverage, textual_holdings, show_generated
618 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', array_remove($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
620 FROM serial.index_summary ssum
622 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
623 $F$ LANGUAGE SQL STABLE;
625 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$
629 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
630 'tag:open-ils.org:U2@bmp/' || id AS id,
634 'tag:open-ils.org:U2@bre/' || record AS record
637 WHEN ('acp' = ANY ($4)) THEN
638 XMLELEMENT( name copies,
639 (SELECT XMLAGG(acp) FROM (
640 SELECT unapi.acp( cp.id, 'xml', 'copy', array_remove($4,'bmp'), $5, $6, $7, $8, FALSE)
642 JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
644 AND cp.deleted IS FALSE
645 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
646 LIMIT ($7 -> 'acp')::INT
647 OFFSET ($8 -> 'acp')::INT
653 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', array_remove($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END
655 FROM biblio.monograph_part
656 WHERE NOT deleted AND id = $1
657 GROUP BY id, label, label_sortkey, record;
658 $F$ LANGUAGE SQL STABLE;
660 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$
664 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
665 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
666 create_date, edit_date, copy_number, circulate, deposit,
667 ref, holdable, deleted, deposit_amount, price, barcode,
668 circ_modifier, circ_as_type, opac_visible, age_protect
670 unapi.ccs( status, $2, 'status', array_remove($4,'acp'), $5, $6, $7, $8, FALSE),
671 unapi.acl( location, $2, 'location', array_remove($4,'acp'), $5, $6, $7, $8, FALSE),
672 unapi.aou( circ_lib, $2, 'circ_lib', array_remove($4,'acp'), $5, $6, $7, $8),
673 unapi.aou( circ_lib, $2, 'circlib', array_remove($4,'acp'), $5, $6, $7, $8),
674 CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', array_remove($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END,
676 WHEN ('acpn' = ANY ($4)) THEN
677 XMLELEMENT( name copy_notes,
678 (SELECT XMLAGG(acpn) FROM (
679 SELECT unapi.acpn( id, 'xml', 'copy_note', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
681 WHERE owning_copy = cp.id AND pub
687 WHEN ('ascecm' = ANY ($4)) THEN
688 XMLELEMENT( name statcats,
689 (SELECT XMLAGG(ascecm) FROM (
690 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
691 FROM asset.stat_cat_entry_copy_map
692 WHERE owning_copy = cp.id
698 WHEN ('bre' = ANY ($4)) THEN
699 XMLELEMENT( name foreign_records,
700 (SELECT XMLAGG(bre) FROM (
701 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
702 FROM biblio.peer_bib_copy_map
703 WHERE target_copy = cp.id
710 WHEN ('bmp' = ANY ($4)) THEN
711 XMLELEMENT( name monograph_parts,
712 (SELECT XMLAGG(bmp) FROM (
713 SELECT unapi.bmp( part, 'xml', 'monograph_part', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
714 FROM asset.copy_part_map
715 WHERE target_copy = cp.id
721 WHEN ('circ' = ANY ($4)) THEN
722 XMLELEMENT( name current_circulation,
723 (SELECT XMLAGG(circ) FROM (
724 SELECT unapi.circ( id, 'xml', 'circ', array_remove($4,'circ'), $5, $6, $7, $8, FALSE)
725 FROM action.circulation
726 WHERE target_copy = cp.id
727 AND checkin_time IS NULL
735 AND cp.deleted IS FALSE
736 GROUP BY id, status, location, circ_lib, call_number, create_date,
737 edit_date, copy_number, circulate, deposit, ref, holdable,
738 deleted, deposit_amount, price, barcode, circ_modifier,
739 circ_as_type, opac_visible, age_protect;
740 $F$ LANGUAGE SQL STABLE;
742 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$
746 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
747 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
748 create_date, edit_date, copy_number, circulate, deposit,
749 ref, holdable, deleted, deposit_amount, price, barcode,
750 circ_modifier, circ_as_type, opac_visible, age_protect,
751 status_changed_time, floating, mint_condition,
752 detailed_contents, sort_key, summary_contents, cost
754 unapi.ccs( status, $2, 'status', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
755 unapi.acl( location, $2, 'location', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
756 unapi.aou( circ_lib, $2, 'circ_lib', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8),
757 unapi.aou( circ_lib, $2, 'circlib', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8),
758 CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', array_remove($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END,
759 XMLELEMENT( name copy_notes,
761 WHEN ('acpn' = ANY ($4)) THEN
762 (SELECT XMLAGG(acpn) FROM (
763 SELECT unapi.acpn( id, 'xml', 'copy_note', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE)
765 WHERE owning_copy = cp.id AND pub
770 XMLELEMENT( name statcats,
772 WHEN ('ascecm' = ANY ($4)) THEN
773 (SELECT XMLAGG(ascecm) FROM (
774 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
775 FROM asset.stat_cat_entry_copy_map
776 WHERE owning_copy = cp.id
781 XMLELEMENT( name foreign_records,
783 WHEN ('bre' = ANY ($4)) THEN
784 (SELECT XMLAGG(bre) FROM (
785 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
786 FROM biblio.peer_bib_copy_map
787 WHERE target_copy = cp.id
793 WHEN ('bmp' = ANY ($4)) THEN
794 XMLELEMENT( name monograph_parts,
795 (SELECT XMLAGG(bmp) FROM (
796 SELECT unapi.bmp( part, 'xml', 'monograph_part', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
797 FROM asset.copy_part_map
798 WHERE target_copy = cp.id
804 WHEN ('circ' = ANY ($4)) THEN
805 XMLELEMENT( name current_circulation,
806 (SELECT XMLAGG(circ) FROM (
807 SELECT unapi.circ( id, 'xml', 'circ', array_remove($4,'circ'), $5, $6, $7, $8, FALSE)
808 FROM action.circulation
809 WHERE target_copy = cp.id
810 AND checkin_time IS NULL
818 AND cp.deleted IS FALSE
819 GROUP BY id, status, location, circ_lib, call_number, create_date,
820 edit_date, copy_number, circulate, floating, mint_condition,
821 deposit, ref, holdable, deleted, deposit_amount, price,
822 barcode, circ_modifier, circ_as_type, opac_visible,
823 status_changed_time, detailed_contents, sort_key,
824 summary_contents, cost, age_protect;
825 $F$ LANGUAGE SQL STABLE;
827 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$
831 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
832 'tag:open-ils.org:U2@acn/' || acn.id AS id,
833 acn.id AS vol_id, o.shortname AS lib,
834 o.opac_visible AS opac_visible,
835 deleted, label, label_sortkey, label_class, record
837 unapi.aou( owning_lib, $2, 'owning_lib', array_remove($4,'acn'), $5, $6, $7, $8),
839 WHEN ('acp' = ANY ($4)) THEN
840 CASE WHEN $6 IS NOT NULL THEN
841 XMLELEMENT( name copies,
842 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
843 SELECT unapi.acp( cp.id, 'xml', 'copy', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
844 evergreen.rank_cp(cp) AS rank_avail
846 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id)
847 WHERE cp.call_number = acn.id
848 AND cp.deleted IS FALSE
849 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
850 LIMIT ($7 -> 'acp')::INT
851 OFFSET ($8 -> 'acp')::INT
855 XMLELEMENT( name copies,
856 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
857 SELECT unapi.acp( cp.id, 'xml', 'copy', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
858 evergreen.rank_cp(cp) AS rank_avail
860 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id)
861 WHERE cp.call_number = acn.id
862 AND cp.deleted IS FALSE
863 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
864 LIMIT ($7 -> 'acp')::INT
865 OFFSET ($8 -> 'acp')::INT
873 (SELECT XMLAGG(auri) FROM (SELECT unapi.auri(uri,'xml','uri', array_remove($4,'acn'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE call_number = acn.id)x)
875 unapi.acnp( acn.prefix, 'marcxml', 'prefix', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
876 unapi.acns( acn.suffix, 'marcxml', 'suffix', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
877 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( acn.record, 'marcxml', 'record', array_remove($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END
879 FROM asset.call_number acn
880 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
882 AND acn.deleted IS FALSE
883 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
884 $F$ LANGUAGE SQL STABLE;
886 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$
890 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
891 'tag:open-ils.org:U2@auri/' || uri.id AS id,
897 WHEN ('acn' = ANY ($4)) THEN
898 XMLELEMENT( name copies,
899 (SELECT XMLAGG(acn) FROM (SELECT unapi.acn( call_number, 'xml', 'copy', array_remove($4,'auri'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE uri = uri.id)x)
906 GROUP BY uri.id, use_restriction, href, label;
907 $F$ LANGUAGE SQL STABLE;
909 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$
913 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
914 'tag:open-ils.org:U2@circ/' || id AS id,
918 CASE WHEN ('aou' = ANY ($4)) THEN unapi.aou( circ_lib, $2, 'circ_lib', array_remove($4,'circ'), $5, $6, $7, $8, FALSE) ELSE NULL END,
919 CASE WHEN ('acp' = ANY ($4)) THEN unapi.acp( circ_lib, $2, 'target_copy', array_remove($4,'circ'), $5, $6, $7, $8, FALSE) ELSE NULL END
921 FROM action.circulation
923 $F$ LANGUAGE SQL STABLE;
925 CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml (
929 depth INT DEFAULT NULL,
930 includes TEXT[] DEFAULT NULL::TEXT[],
931 slimit HSTORE DEFAULT NULL,
932 soffset HSTORE DEFAULT NULL,
933 include_xmlns BOOL DEFAULT TRUE,
934 pref_lib INT DEFAULT NULL
940 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
941 CASE WHEN ('mmr' = ANY ($5)) THEN 'tag:open-ils.org:U2@mmr/' || $1 || '/' || $3 ELSE NULL END AS id,
942 (SELECT metarecord_has_holdable_copy FROM asset.metarecord_has_holdable_copy($1)) AS has_holdable
946 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
949 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
951 FROM asset.opac_ou_metarecord_copy_count($2, $1)
955 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
957 FROM asset.staff_ou_metarecord_copy_count($2, $1)
961 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
963 FROM asset.opac_ou_metarecord_copy_count($9, $1)
967 -- XXX monograph_parts and foreign_copies are skipped in MRs ... put them back some day?
970 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
972 SELECT unapi.acn(y.id,'xml','volume',array_remove( array_remove($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
973 FROM evergreen.ranked_volumes((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $4, $6, $7, $9, $5) AS y
976 SELECT unapi.acn(uris.id,'xml','volume',array_remove( array_remove($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), uris.rank, name, label_sortkey
977 FROM evergreen.located_uris((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $9) AS uris
980 CASE WHEN ('ssub' = ANY ($5)) THEN
983 (SELECT XMLAGG(ssub) FROM (
984 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
985 FROM serial.subscription
986 WHERE record_entry IN (SELECT source FROM metabib.metarecord_source_map WHERE metarecord = $1)
991 $F$ LANGUAGE SQL STABLE;
993 CREATE OR REPLACE FUNCTION unapi.mmr (
999 depth INT DEFAULT NULL,
1000 slimit HSTORE DEFAULT NULL,
1001 soffset HSTORE DEFAULT NULL,
1002 include_xmlns BOOL DEFAULT TRUE,
1003 pref_lib INT DEFAULT NULL
1007 mmrec metabib.metarecord%ROWTYPE;
1008 leadrec biblio.record_entry%ROWTYPE;
1009 subrec biblio.record_entry%ROWTYPE;
1010 layout unapi.bre_output_layout%ROWTYPE;
1011 xfrm config.xml_transform%ROWTYPE;
1013 xml_buf TEXT; -- growing XML document
1014 tmp_xml TEXT; -- single-use XML string
1015 xml_frag TEXT; -- single-use XML fragment
1020 subxml XML; -- subordinate records elements
1025 -- xpath for extracting bre.marc values from subordinate records
1026 -- so they may be appended to the MARC of the master record prior
1027 -- to XSLT processing.
1028 -- subjects, isbn, issn, upc -- anything else?
1030 '//*[starts-with(@tag, "6") or @tag="020" or @tag="022" or @tag="024"]';
1032 IF org = '-' OR org IS NULL THEN
1033 SELECT shortname INTO org FROM evergreen.org_top();
1036 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
1038 IF ouid IS NULL THEN
1042 SELECT INTO mmrec * FROM metabib.metarecord WHERE id = obj_id;
1047 -- TODO: aggregate holdings from constituent records
1048 IF format = 'holdings_xml' THEN -- the special case
1049 output := unapi.mmr_holdings_xml(
1050 obj_id, ouid, org, depth,
1051 array_remove(includes,'holdings_xml'),
1052 slimit, soffset, include_xmlns, pref_lib);
1056 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
1058 IF layout.name IS NULL THEN
1062 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
1064 SELECT INTO leadrec * FROM biblio.record_entry WHERE id = mmrec.master_record;
1066 -- Grab distinct MVF for all records if requested
1067 IF ('mra' = ANY (includes)) THEN
1068 axml := unapi.mmr_mra(obj_id,NULL,NULL,NULL,org,depth,NULL,NULL,TRUE,pref_lib);
1073 xml_buf = leadrec.marc;
1076 IF ('holdings_xml' = ANY (includes)) THEN
1077 hxml := unapi.mmr_holdings_xml(
1078 obj_id, ouid, org, depth,
1079 array_remove(includes,'holdings_xml'),
1080 slimit, soffset, include_xmlns, pref_lib);
1083 subxml := NULL::XML;
1084 parts := '{}'::TEXT[];
1085 FOR subrec IN SELECT bre.* FROM biblio.record_entry bre
1086 JOIN metabib.metarecord_source_map mmsm ON (mmsm.source = bre.id)
1087 JOIN metabib.metarecord mmr ON (mmr.id = mmsm.metarecord)
1088 WHERE mmr.id = obj_id AND NOT bre.deleted
1089 ORDER BY CASE WHEN bre.id = mmr.master_record THEN 0 ELSE bre.id END
1090 LIMIT COALESCE((slimit->'bre')::INT, 5) LOOP
1092 IF subrec.id = leadrec.id THEN CONTINUE; END IF;
1093 -- Append choice data from the the non-lead records to the
1094 -- the lead record document
1096 parts := parts || xpath(sub_xpath, subrec.marc::XML)::TEXT[];
1099 SELECT ARRAY_TO_STRING( ARRAY_AGG( DISTINCT p ), '' )::XML INTO subxml FROM UNNEST(parts) p;
1101 -- append data from the subordinate records to the
1102 -- main record document before applying the XSLT
1104 IF subxml IS NOT NULL THEN
1105 xml_buf := REGEXP_REPLACE(xml_buf,
1106 '</record>(.*?)$', subxml || '</record>' || E'\\1');
1109 IF format = 'marcxml' THEN
1110 -- If we're not using the prefixed namespace in
1111 -- this record, then remove all declarations of it
1112 IF xml_buf !~ E'<marc:' THEN
1113 xml_buf := REGEXP_REPLACE(xml_buf,
1114 ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
1117 xml_buf := oils_xslt_process(xml_buf, xfrm.xslt)::XML;
1120 -- update top_el to reflect the change in xml_buf, which may
1121 -- now be a different type of document (e.g. record -> mods)
1122 top_el := REGEXP_REPLACE(xml_buf, E'^.*?<((?:\\S+:)?' ||
1123 layout.holdings_element || ').*$', E'\\1');
1125 IF axml IS NOT NULL THEN
1126 xml_buf := REGEXP_REPLACE(xml_buf,
1127 '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
1130 IF hxml IS NOT NULL THEN
1131 xml_buf := REGEXP_REPLACE(xml_buf,
1132 '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
1135 IF ('mmr.unapi' = ANY (includes)) THEN
1136 output := REGEXP_REPLACE(
1138 '</' || top_el || '>(.*?)',
1142 'http://www.w3.org/1999/xhtml' AS xmlns,
1143 'unapi-id' AS class,
1144 'tag:open-ils.org:U2@mmr/' || obj_id || '/' || org AS title
1146 )::TEXT || '</' || top_el || E'>\\1'
1152 -- remove ignorable whitesace
1153 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
1156 $F$ LANGUAGE PLPGSQL STABLE;
1158 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1160 idx authority.heading_field%ROWTYPE;
1161 xfrm config.xml_transform%ROWTYPE;
1163 transformed_xml TEXT;
1165 heading_node_list TEXT[];
1166 component_node TEXT;
1167 component_node_list TEXT[];
1169 normalized_text TEXT;
1174 base_thesaurus TEXT := NULL;
1175 output_row authority.heading;
1178 -- Loop over the indexing entries
1179 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
1181 output_row.field := idx.id;
1182 output_row.type := idx.heading_type;
1183 output_row.purpose := idx.heading_purpose;
1185 joiner := COALESCE(idx.joiner, ' ');
1187 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1189 -- See if we can skip the XSLT ... it's expensive
1190 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1191 -- Can't skip the transform
1192 IF xfrm.xslt <> '---' THEN
1193 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
1195 transformed_xml := marc;
1198 prev_xfrm := xfrm.name;
1201 IF idx.thesaurus_xpath IS NOT NULL THEN
1202 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1205 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1207 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
1209 CONTINUE WHEN heading_node !~ E'^\\s*<';
1211 output_row.variant_type := NULL;
1212 output_row.related_type := NULL;
1213 output_row.thesaurus := NULL;
1214 output_row.heading := NULL;
1216 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
1217 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1219 output_row.variant_type := type_value;
1220 EXCEPTION WHEN invalid_text_representation THEN
1221 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
1224 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
1225 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1227 output_row.related_type := type_value;
1228 EXCEPTION WHEN invalid_text_representation THEN
1229 RAISE NOTICE 'Do not recognize related heading type %', type_value;
1233 IF idx.thesaurus_override_xpath IS NOT NULL THEN
1234 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1236 IF output_row.thesaurus IS NULL THEN
1237 output_row.thesaurus := base_thesaurus;
1242 -- now iterate over components of heading
1243 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1244 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
1245 -- XXX much of this should be moved into oils_xpath_string...
1246 curr_text := ARRAY_TO_STRING(array_remove(array_remove(
1247 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
1248 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
1249 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
1253 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
1255 IF raw_text IS NOT NULL THEN
1256 raw_text := raw_text || joiner;
1259 raw_text := COALESCE(raw_text,'') || curr_text;
1262 IF raw_text IS NOT NULL THEN
1263 output_row.heading := raw_text;
1264 normalized_text := raw_text;
1267 SELECT n.func AS func,
1268 n.param_count AS param_count,
1270 FROM config.index_normalizer n
1271 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
1272 WHERE m.field = idx.id
1275 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1276 quote_literal( normalized_text ) ||
1278 WHEN normalizer.param_count > 0
1279 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1282 ')' INTO normalized_text;
1286 output_row.normalized_heading := normalized_text;
1288 RETURN NEXT output_row;
1294 $func$ LANGUAGE PLPGSQL;
1296 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1298 auth authority.record_entry%ROWTYPE;
1299 output_row authority.heading;
1302 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
1304 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
1306 $func$ LANGUAGE PLPGSQL;
1308 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
1310 default_joiner TEXT,
1313 ) RETURNS SETOF metabib.field_entry_template AS $func$
1315 bib biblio.record_entry%ROWTYPE;
1316 idx config.metabib_field%ROWTYPE;
1317 xfrm config.xml_transform%ROWTYPE;
1319 transformed_xml TEXT;
1321 xml_node_list TEXT[];
1328 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
1329 authority_text TEXT;
1330 authority_link BIGINT;
1331 output_row metabib.field_entry_template%ROWTYPE;
1335 -- Start out with no field-use bools set
1336 output_row.browse_field = FALSE;
1337 output_row.facet_field = FALSE;
1338 output_row.display_field = FALSE;
1339 output_row.search_field = FALSE;
1342 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
1344 -- Loop over the indexing entries
1345 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
1346 CONTINUE WHEN idx.xpath IS NULL OR idx.xpath = ''; -- pure virtual field
1348 process_idx := FALSE;
1349 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
1350 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
1351 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
1352 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
1353 CONTINUE WHEN process_idx = FALSE; -- disabled for all types
1355 joiner := COALESCE(idx.joiner, default_joiner);
1357 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1359 -- See if we can skip the XSLT ... it's expensive
1360 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1361 -- Can't skip the transform
1362 IF xfrm.xslt <> '---' THEN
1363 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
1365 transformed_xml := bib.marc;
1368 prev_xfrm := xfrm.name;
1371 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1374 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
1375 CONTINUE WHEN xml_node !~ E'^\\s*<';
1377 -- XXX much of this should be moved into oils_xpath_string...
1378 curr_text := ARRAY_TO_STRING(array_remove(array_remove(
1379 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
1380 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
1381 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
1385 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
1387 IF raw_text IS NOT NULL THEN
1388 raw_text := raw_text || joiner;
1391 raw_text := COALESCE(raw_text,'') || curr_text;
1393 -- autosuggest/metabib.browse_entry
1394 IF idx.browse_field THEN
1396 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
1397 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1399 browse_text := curr_text;
1402 IF idx.browse_sort_xpath IS NOT NULL AND
1403 idx.browse_sort_xpath <> '' THEN
1405 sort_value := oils_xpath_string(
1406 idx.browse_sort_xpath, xml_node, joiner,
1407 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1410 sort_value := browse_text;
1413 output_row.field_class = idx.field_class;
1414 output_row.field = idx.id;
1415 output_row.source = rid;
1416 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
1417 output_row.sort_value :=
1418 public.naco_normalize(sort_value);
1420 output_row.authority := NULL;
1422 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
1423 authority_text := oils_xpath_string(
1424 idx.authority_xpath, xml_node, joiner,
1426 ARRAY[xfrm.prefix, xfrm.namespace_uri],
1427 ARRAY['xlink','http://www.w3.org/1999/xlink']
1431 IF authority_text ~ '^\d+$' THEN
1432 authority_link := authority_text::BIGINT;
1433 PERFORM * FROM authority.record_entry WHERE id = authority_link;
1435 output_row.authority := authority_link;
1441 output_row.browse_field = TRUE;
1442 -- Returning browse rows with search_field = true for search+browse
1443 -- configs allows us to retain granularity of being able to search
1444 -- browse fields with "starts with" type operators (for example, for
1445 -- titles of songs in music albums)
1446 IF idx.search_field THEN
1447 output_row.search_field = TRUE;
1449 RETURN NEXT output_row;
1450 output_row.browse_field = FALSE;
1451 output_row.search_field = FALSE;
1452 output_row.sort_value := NULL;
1455 -- insert raw node text for faceting
1456 IF idx.facet_field THEN
1458 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
1459 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1461 facet_text := curr_text;
1464 output_row.field_class = idx.field_class;
1465 output_row.field = -1 * idx.id;
1466 output_row.source = rid;
1467 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
1469 output_row.facet_field = TRUE;
1470 RETURN NEXT output_row;
1471 output_row.facet_field = FALSE;
1474 -- insert raw node text for display
1475 IF idx.display_field THEN
1477 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
1478 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1480 display_text := curr_text;
1483 output_row.field_class = idx.field_class;
1484 output_row.field = -1 * idx.id;
1485 output_row.source = rid;
1486 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
1488 output_row.display_field = TRUE;
1489 RETURN NEXT output_row;
1490 output_row.display_field = FALSE;
1495 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
1497 -- insert combined node text for searching
1498 IF idx.search_field THEN
1499 output_row.field_class = idx.field_class;
1500 output_row.field = idx.id;
1501 output_row.source = rid;
1502 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
1504 output_row.search_field = TRUE;
1505 RETURN NEXT output_row;
1506 output_row.search_field = FALSE;
1512 $func$ LANGUAGE PLPGSQL;
1514 -- We no longer need the custom function
1515 DROP FUNCTION evergreen.array_remove_item_by_value(ANYARRAY, ANYELEMENT);
1519 SELECT evergreen.upgrade_deps_block_check('1265', :eg_version);
1521 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
1523 'eg.orgselect.hopeless.wide_holds', 'gui', 'integer',
1525 'eg.orgselect.hopeless.wide_holds',
1526 'Default org unit for hopeless holds interface',
1533 -- Update auditor tables to catch changes to source tables.
1534 -- Can be removed/skipped if there were no schema changes.
1535 SELECT auditor.update_auditors();