1 --Upgrade Script for 3.7.0 to 3.7.1
2 \set eg_version '''3.7.1'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.7.1', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1261', :eg_version);
8 CREATE OR REPLACE FUNCTION evergreen.located_uris_as_uris
9 (bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL)
10 RETURNS SETOF asset.uri AS $FUNK$
11 /* Maps a bib directly to its scoped asset.uri's */
14 FROM evergreen.located_uris($1, $2, $3) located_uri
15 JOIN asset.uri_call_number_map map ON (map.call_number = located_uri.id)
16 JOIN asset.uri uri ON (uri.id = map.uri)
18 $FUNK$ LANGUAGE SQL STABLE;
21 SELECT evergreen.upgrade_deps_block_check('1262', :eg_version);
23 CREATE OR REPLACE FUNCTION search.highlight_display_fields_impl(
26 field_list INT[] DEFAULT '{}'::INT[],
27 css_class TEXT DEFAULT 'oils_SH',
28 hl_all BOOL DEFAULT TRUE,
29 minwords INT DEFAULT 5,
30 maxwords INT DEFAULT 25,
31 shortwords INT DEFAULT 0,
32 maxfrags INT DEFAULT 0,
33 delimiter TEXT DEFAULT ' ... '
34 ) RETURNS SETOF search.highlight_result AS $f$
37 v_css_class TEXT := css_class;
38 v_delimiter TEXT := delimiter;
39 v_field_list INT[] := field_list;
42 IF v_delimiter LIKE $$%'%$$ OR v_delimiter LIKE '%"%' THEN --"
43 v_delimiter := ' ... ';
47 opts := opts || 'MinWords=' || minwords;
48 opts := opts || ', MaxWords=' || maxwords;
49 opts := opts || ', ShortWords=' || shortwords;
50 opts := opts || ', MaxFragments=' || maxfrags;
51 opts := opts || ', FragmentDelimiter="' || delimiter || '"';
53 opts := opts || 'HighlightAll=TRUE';
56 IF v_css_class LIKE $$%'%$$ OR v_css_class LIKE '%"%' THEN -- "
57 v_css_class := 'oils_SH';
60 opts := opts || $$, StopSel=</b>, StartSel="<b class='$$ || v_css_class; -- "
62 IF v_field_list = '{}'::INT[] THEN
63 SELECT ARRAY_AGG(id) INTO v_field_list FROM config.metabib_field WHERE display_field;
70 evergreen.escape_for_html(de.value) AS value,
73 evergreen.escape_for_html(de.value),
74 $$ || quote_literal(tsq) || $$,
75 $1 || ' ' || mf.field_class || ' ' || mf.name || $xx$'>"$xx$ -- "'
77 FROM metabib.display_entry de
78 JOIN config.metabib_field mf ON (mf.id = de.field)
79 JOIN search.best_tsconfig t ON (t.id = de.field)
84 RETURN QUERY EXECUTE hl_query USING opts, rid, v_field_list;
88 CREATE OR REPLACE FUNCTION search.highlight_display_fields(
90 tsq_map TEXT, -- { '(a | b) & c' => '1,2,3,4', ...}
91 css_class TEXT DEFAULT 'oils_SH',
92 hl_all BOOL DEFAULT TRUE,
93 minwords INT DEFAULT 5,
94 maxwords INT DEFAULT 25,
95 shortwords INT DEFAULT 0,
96 maxfrags INT DEFAULT 0,
97 delimiter TEXT DEFAULT ' ... '
98 ) RETURNS SETOF search.highlight_result AS $f$
106 IF (tsq_map ILIKE 'hstore%') THEN
107 EXECUTE 'SELECT ' || tsq_map INTO tsq_hstore;
109 tsq_hstore := tsq_map::HSTORE;
112 FOR tsq, fields IN SELECT key, value FROM each(tsq_hstore::HSTORE) LOOP
113 SELECT ARRAY_AGG(unnest::INT) INTO afields
114 FROM unnest(regexp_split_to_array(fields,','));
115 seen := seen || afields;
118 SELECT * FROM search.highlight_display_fields_impl(
119 rid, tsq, afields, css_class, hl_all,minwords,
120 maxwords, shortwords, maxfrags, delimiter
128 evergreen.escape_for_html(value) AS value,
129 evergreen.escape_for_html(value) AS highlight
130 FROM metabib.display_entry
132 AND NOT (field = ANY (seen));
134 $f$ LANGUAGE PLPGSQL ROWS 10;
137 SELECT evergreen.upgrade_deps_block_check('1263', :eg_version);
139 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
141 'eg.catalog.search.form.open', 'gui', 'bool',
143 'eg.catalog.search.form.open',
144 'Catalog Search Form Visibility Sticky Setting',
150 SELECT evergreen.upgrade_deps_block_check('1264', :eg_version);
152 CREATE OR REPLACE FUNCTION unapi.bre (
158 depth INT DEFAULT NULL,
159 slimit HSTORE DEFAULT NULL,
160 soffset HSTORE DEFAULT NULL,
161 include_xmlns BOOL DEFAULT TRUE,
162 pref_lib INT DEFAULT NULL
166 me biblio.record_entry%ROWTYPE;
167 layout unapi.bre_output_layout%ROWTYPE;
168 xfrm config.xml_transform%ROWTYPE;
178 IF org = '-' OR org IS NULL THEN
179 SELECT shortname INTO org FROM evergreen.org_top();
182 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
188 IF format = 'holdings_xml' THEN -- the special case
189 output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
193 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
195 IF layout.name IS NULL THEN
199 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
201 SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
203 -- grab bib_source, if any
204 IF ('cbs' = ANY (includes) AND me.source IS NOT NULL) THEN
205 source := unapi.cbs(me.source,NULL,NULL,NULL,NULL);
210 -- grab SVF if we need them
211 IF ('mra' = ANY (includes)) THEN
212 axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
217 -- grab holdings if we need them
218 IF ('holdings_xml' = ANY (includes)) THEN
219 hxml := unapi.holdings_xml(obj_id, ouid, org, depth, array_remove(includes,'holdings_xml'), slimit, soffset, include_xmlns, pref_lib);
225 -- generate our item node
228 IF format = 'marcxml' THEN
230 IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
231 tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
234 tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
237 top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
239 IF source IS NOT NULL THEN
240 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', source || '</' || top_el || E'>\\1');
243 IF axml IS NOT NULL THEN
244 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
247 IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
248 tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
251 IF ('bre.unapi' = ANY (includes)) THEN
252 output := REGEXP_REPLACE(
254 '</' || top_el || '>(.*?)',
258 'http://www.w3.org/1999/xhtml' AS xmlns,
260 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
262 )::TEXT || '</' || top_el || E'>\\1'
268 IF ('bre.extern' = ANY (includes)) THEN
269 output := REGEXP_REPLACE(
271 '</' || top_el || '>(.*?)',
275 'http://open-ils.org/spec/biblio/v1' AS xmlns,
276 me.creator AS creator,
278 me.create_date AS create_date,
279 me.edit_date AS edit_date,
280 me.quality AS quality,
281 me.fingerprint AS fingerprint,
282 me.tcn_source AS tcn_source,
283 me.tcn_value AS tcn_value,
285 me.share_depth AS share_depth,
287 me.deleted AS deleted
289 )::TEXT || '</' || top_el || E'>\\1'
295 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
298 $F$ LANGUAGE PLPGSQL STABLE;
300 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
304 depth INT DEFAULT NULL,
305 includes TEXT[] DEFAULT NULL::TEXT[],
306 slimit HSTORE DEFAULT NULL,
307 soffset HSTORE DEFAULT NULL,
308 include_xmlns BOOL DEFAULT TRUE,
309 pref_lib INT DEFAULT NULL
315 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
316 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
317 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
321 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
324 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
326 FROM asset.opac_ou_record_copy_count($2, $1)
330 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
332 FROM asset.staff_ou_record_copy_count($2, $1)
336 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
338 FROM asset.opac_ou_record_copy_count($9, $1)
343 WHEN ('bmp' = ANY ($5)) THEN
345 name monograph_parts,
346 (SELECT XMLAGG(bmp) FROM (
347 SELECT unapi.bmp( id, 'xml', 'monograph_part', array_remove( array_remove($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
348 FROM biblio.monograph_part
349 WHERE NOT deleted AND record = $1
356 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
358 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
359 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
362 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
363 FROM evergreen.located_uris($1, $2, $9) AS uris
366 CASE WHEN ('ssub' = ANY ($5)) THEN
369 (SELECT XMLAGG(ssub) FROM (
370 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
371 FROM serial.subscription
372 WHERE record_entry = $1
376 CASE WHEN ('acp' = ANY ($5)) THEN
379 (SELECT XMLAGG(acp) FROM (
380 SELECT unapi.acp(p.target_copy,'xml','copy',array_remove($5,'acp'), $3, $4, $6, $7, FALSE)
381 FROM biblio.peer_bib_copy_map p
382 JOIN asset.copy c ON (p.target_copy = c.id)
383 WHERE NOT c.deleted AND p.peer_record = $1
384 LIMIT ($6 -> 'acp')::INT
385 OFFSET ($7 -> 'acp')::INT
390 $F$ LANGUAGE SQL STABLE;
392 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$
396 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
397 'tag:open-ils.org:U2@ssub/' || id AS id,
398 'tag:open-ils.org:U2@aou/' || owning_lib AS owning_lib,
399 start_date AS start, end_date AS end, expected_date_offset
402 WHEN ('sdist' = ANY ($4)) THEN
403 XMLELEMENT( name distributions,
404 (SELECT XMLAGG(sdist) FROM (
405 SELECT unapi.sdist( id, 'xml', 'distribution', array_remove($4,'ssub'), $5, $6, $7, $8, FALSE)
406 FROM serial.distribution
407 WHERE subscription = ssub.id
413 FROM serial.subscription ssub
415 GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
416 $F$ LANGUAGE SQL STABLE;
418 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$
422 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
423 'tag:open-ils.org:U2@sdist/' || id AS id,
424 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
425 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
426 unit_label_prefix, label, unit_label_suffix, summary_method
428 unapi.aou( holding_lib, $2, 'holding_lib', array_remove($4,'sdist'), $5, $6, $7, $8),
429 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,
431 WHEN ('sstr' = ANY ($4)) THEN
432 XMLELEMENT( name streams,
433 (SELECT XMLAGG(sstr) FROM (
434 SELECT unapi.sstr( id, 'xml', 'stream', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
436 WHERE distribution = sdist.id
441 XMLELEMENT( name summaries,
443 WHEN ('sbsum' = ANY ($4)) THEN
444 (SELECT XMLAGG(sbsum) FROM (
445 SELECT unapi.sbsum( id, 'xml', 'serial_summary', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
446 FROM serial.basic_summary
447 WHERE distribution = sdist.id
452 WHEN ('sisum' = ANY ($4)) THEN
453 (SELECT XMLAGG(sisum) FROM (
454 SELECT unapi.sisum( id, 'xml', 'serial_summary', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
455 FROM serial.index_summary
456 WHERE distribution = sdist.id
461 WHEN ('sssum' = ANY ($4)) THEN
462 (SELECT XMLAGG(sssum) FROM (
463 SELECT unapi.sssum( id, 'xml', 'serial_summary', array_remove($4,'sdist'), $5, $6, $7, $8, FALSE)
464 FROM serial.supplement_summary
465 WHERE distribution = sdist.id
471 FROM serial.distribution sdist
473 GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
474 $F$ LANGUAGE SQL STABLE;
476 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$
480 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
481 'tag:open-ils.org:U2@sstr/' || id AS id,
484 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,
486 WHEN ('sitem' = ANY ($4)) THEN
487 XMLELEMENT( name items,
488 (SELECT XMLAGG(sitem) FROM (
489 SELECT unapi.sitem( id, 'xml', 'serial_item', array_remove($4,'sstr'), $5, $6, $7, $8, FALSE)
491 WHERE stream = sstr.id
497 FROM serial.stream sstr
499 GROUP BY id, routing_label, distribution;
500 $F$ LANGUAGE SQL STABLE;
502 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$
506 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
507 'tag:open-ils.org:U2@siss/' || id AS id,
508 create_date, edit_date, label, date_published,
509 holding_code, holding_type, holding_link_id
511 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,
513 WHEN ('sitem' = ANY ($4)) THEN
514 XMLELEMENT( name items,
515 (SELECT XMLAGG(sitem) FROM (
516 SELECT unapi.sitem( id, 'xml', 'serial_item', array_remove($4,'siss'), $5, $6, $7, $8, FALSE)
518 WHERE issuance = sstr.id
524 FROM serial.issuance sstr
526 GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
527 $F$ LANGUAGE SQL STABLE;
529 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$
533 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
534 'tag:open-ils.org:U2@sitem/' || id AS id,
535 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
536 date_expected, date_received
538 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,
539 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,
540 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,
541 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
542 -- XMLELEMENT( name notes,
544 -- WHEN ('acpn' = ANY ($4)) THEN
545 -- (SELECT XMLAGG(acpn) FROM (
546 -- SELECT unapi.acpn( id, 'xml', 'copy_note', array_remove($4,'acp'), $5, $6, $7, $8)
547 -- FROM asset.copy_note
548 -- WHERE owning_copy = cp.id AND pub
554 FROM serial.item sitem
556 $F$ LANGUAGE SQL STABLE;
559 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$
563 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
564 'tag:open-ils.org:U2@sbsum/' || id AS id,
565 'sssum' AS type, generated_coverage, textual_holdings, show_generated
567 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', array_remove($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
569 FROM serial.supplement_summary ssum
571 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
572 $F$ LANGUAGE SQL STABLE;
574 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$
578 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
579 'tag:open-ils.org:U2@sbsum/' || id AS id,
580 'sbsum' AS type, generated_coverage, textual_holdings, show_generated
582 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', array_remove($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
584 FROM serial.basic_summary ssum
586 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
587 $F$ LANGUAGE SQL STABLE;
589 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$
593 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
594 'tag:open-ils.org:U2@sbsum/' || id AS id,
595 'sisum' AS type, generated_coverage, textual_holdings, show_generated
597 CASE WHEN ('sdist' = ANY ($4)) THEN unapi.sdist( distribution, 'xml', 'distribtion', array_remove($4,'ssum'), $5, $6, $7, $8, FALSE) ELSE NULL END
599 FROM serial.index_summary ssum
601 GROUP BY id, generated_coverage, textual_holdings, distribution, show_generated;
602 $F$ LANGUAGE SQL STABLE;
604 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$
608 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
609 'tag:open-ils.org:U2@bmp/' || id AS id,
613 'tag:open-ils.org:U2@bre/' || record AS record
616 WHEN ('acp' = ANY ($4)) THEN
617 XMLELEMENT( name copies,
618 (SELECT XMLAGG(acp) FROM (
619 SELECT unapi.acp( cp.id, 'xml', 'copy', array_remove($4,'bmp'), $5, $6, $7, $8, FALSE)
621 JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
623 AND cp.deleted IS FALSE
624 ORDER BY COALESCE(cp.copy_number,0), cp.barcode
625 LIMIT ($7 -> 'acp')::INT
626 OFFSET ($8 -> 'acp')::INT
632 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', array_remove($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END
634 FROM biblio.monograph_part
635 WHERE NOT deleted AND id = $1
636 GROUP BY id, label, label_sortkey, record;
637 $F$ LANGUAGE SQL STABLE;
639 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$
643 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
644 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
645 create_date, edit_date, copy_number, circulate, deposit,
646 ref, holdable, deleted, deposit_amount, price, barcode,
647 circ_modifier, circ_as_type, opac_visible, age_protect
649 unapi.ccs( status, $2, 'status', array_remove($4,'acp'), $5, $6, $7, $8, FALSE),
650 unapi.acl( location, $2, 'location', array_remove($4,'acp'), $5, $6, $7, $8, FALSE),
651 unapi.aou( circ_lib, $2, 'circ_lib', array_remove($4,'acp'), $5, $6, $7, $8),
652 unapi.aou( circ_lib, $2, 'circlib', array_remove($4,'acp'), $5, $6, $7, $8),
653 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,
655 WHEN ('acpn' = ANY ($4)) THEN
656 XMLELEMENT( name copy_notes,
657 (SELECT XMLAGG(acpn) FROM (
658 SELECT unapi.acpn( id, 'xml', 'copy_note', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
660 WHERE owning_copy = cp.id AND pub
666 WHEN ('ascecm' = ANY ($4)) THEN
667 XMLELEMENT( name statcats,
668 (SELECT XMLAGG(ascecm) FROM (
669 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
670 FROM asset.stat_cat_entry_copy_map
671 WHERE owning_copy = cp.id
677 WHEN ('bre' = ANY ($4)) THEN
678 XMLELEMENT( name foreign_records,
679 (SELECT XMLAGG(bre) FROM (
680 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
681 FROM biblio.peer_bib_copy_map
682 WHERE target_copy = cp.id
689 WHEN ('bmp' = ANY ($4)) THEN
690 XMLELEMENT( name monograph_parts,
691 (SELECT XMLAGG(bmp) FROM (
692 SELECT unapi.bmp( part, 'xml', 'monograph_part', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
693 FROM asset.copy_part_map
694 WHERE target_copy = cp.id
700 WHEN ('circ' = ANY ($4)) THEN
701 XMLELEMENT( name current_circulation,
702 (SELECT XMLAGG(circ) FROM (
703 SELECT unapi.circ( id, 'xml', 'circ', array_remove($4,'circ'), $5, $6, $7, $8, FALSE)
704 FROM action.circulation
705 WHERE target_copy = cp.id
706 AND checkin_time IS NULL
714 AND cp.deleted IS FALSE
715 GROUP BY id, status, location, circ_lib, call_number, create_date,
716 edit_date, copy_number, circulate, deposit, ref, holdable,
717 deleted, deposit_amount, price, barcode, circ_modifier,
718 circ_as_type, opac_visible, age_protect;
719 $F$ LANGUAGE SQL STABLE;
721 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$
725 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
726 'tag:open-ils.org:U2@acp/' || id AS id, id AS copy_id,
727 create_date, edit_date, copy_number, circulate, deposit,
728 ref, holdable, deleted, deposit_amount, price, barcode,
729 circ_modifier, circ_as_type, opac_visible, age_protect,
730 status_changed_time, floating, mint_condition,
731 detailed_contents, sort_key, summary_contents, cost
733 unapi.ccs( status, $2, 'status', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
734 unapi.acl( location, $2, 'location', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
735 unapi.aou( circ_lib, $2, 'circ_lib', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8),
736 unapi.aou( circ_lib, $2, 'circlib', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8),
737 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,
738 XMLELEMENT( name copy_notes,
740 WHEN ('acpn' = ANY ($4)) THEN
741 (SELECT XMLAGG(acpn) FROM (
742 SELECT unapi.acpn( id, 'xml', 'copy_note', array_remove( array_remove($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE)
744 WHERE owning_copy = cp.id AND pub
749 XMLELEMENT( name statcats,
751 WHEN ('ascecm' = ANY ($4)) THEN
752 (SELECT XMLAGG(ascecm) FROM (
753 SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
754 FROM asset.stat_cat_entry_copy_map
755 WHERE owning_copy = cp.id
760 XMLELEMENT( name foreign_records,
762 WHEN ('bre' = ANY ($4)) THEN
763 (SELECT XMLAGG(bre) FROM (
764 SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
765 FROM biblio.peer_bib_copy_map
766 WHERE target_copy = cp.id
772 WHEN ('bmp' = ANY ($4)) THEN
773 XMLELEMENT( name monograph_parts,
774 (SELECT XMLAGG(bmp) FROM (
775 SELECT unapi.bmp( part, 'xml', 'monograph_part', array_remove($4,'acp'), $5, $6, $7, $8, FALSE)
776 FROM asset.copy_part_map
777 WHERE target_copy = cp.id
783 WHEN ('circ' = ANY ($4)) THEN
784 XMLELEMENT( name current_circulation,
785 (SELECT XMLAGG(circ) FROM (
786 SELECT unapi.circ( id, 'xml', 'circ', array_remove($4,'circ'), $5, $6, $7, $8, FALSE)
787 FROM action.circulation
788 WHERE target_copy = cp.id
789 AND checkin_time IS NULL
797 AND cp.deleted IS FALSE
798 GROUP BY id, status, location, circ_lib, call_number, create_date,
799 edit_date, copy_number, circulate, floating, mint_condition,
800 deposit, ref, holdable, deleted, deposit_amount, price,
801 barcode, circ_modifier, circ_as_type, opac_visible,
802 status_changed_time, detailed_contents, sort_key,
803 summary_contents, cost, age_protect;
804 $F$ LANGUAGE SQL STABLE;
806 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$
810 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
811 'tag:open-ils.org:U2@acn/' || acn.id AS id,
812 acn.id AS vol_id, o.shortname AS lib,
813 o.opac_visible AS opac_visible,
814 deleted, label, label_sortkey, label_class, record
816 unapi.aou( owning_lib, $2, 'owning_lib', array_remove($4,'acn'), $5, $6, $7, $8),
818 WHEN ('acp' = ANY ($4)) THEN
819 CASE WHEN $6 IS NOT NULL THEN
820 XMLELEMENT( name copies,
821 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
822 SELECT unapi.acp( cp.id, 'xml', 'copy', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
823 evergreen.rank_cp(cp) AS rank_avail
825 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id)
826 WHERE cp.call_number = acn.id
827 AND cp.deleted IS FALSE
828 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
829 LIMIT ($7 -> 'acp')::INT
830 OFFSET ($8 -> 'acp')::INT
834 XMLELEMENT( name copies,
835 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
836 SELECT unapi.acp( cp.id, 'xml', 'copy', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
837 evergreen.rank_cp(cp) AS rank_avail
839 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id)
840 WHERE cp.call_number = acn.id
841 AND cp.deleted IS FALSE
842 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
843 LIMIT ($7 -> 'acp')::INT
844 OFFSET ($8 -> 'acp')::INT
852 (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)
854 unapi.acnp( acn.prefix, 'marcxml', 'prefix', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
855 unapi.acns( acn.suffix, 'marcxml', 'suffix', array_remove($4,'acn'), $5, $6, $7, $8, FALSE),
856 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( acn.record, 'marcxml', 'record', array_remove($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END
858 FROM asset.call_number acn
859 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
861 AND acn.deleted IS FALSE
862 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
863 $F$ LANGUAGE SQL STABLE;
865 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$
869 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
870 'tag:open-ils.org:U2@auri/' || uri.id AS id,
876 WHEN ('acn' = ANY ($4)) THEN
877 XMLELEMENT( name copies,
878 (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)
885 GROUP BY uri.id, use_restriction, href, label;
886 $F$ LANGUAGE SQL STABLE;
888 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$
892 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
893 'tag:open-ils.org:U2@circ/' || id AS id,
897 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,
898 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
900 FROM action.circulation
902 $F$ LANGUAGE SQL STABLE;
904 CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml (
908 depth INT DEFAULT NULL,
909 includes TEXT[] DEFAULT NULL::TEXT[],
910 slimit HSTORE DEFAULT NULL,
911 soffset HSTORE DEFAULT NULL,
912 include_xmlns BOOL DEFAULT TRUE,
913 pref_lib INT DEFAULT NULL
919 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
920 CASE WHEN ('mmr' = ANY ($5)) THEN 'tag:open-ils.org:U2@mmr/' || $1 || '/' || $3 ELSE NULL END AS id,
921 (SELECT metarecord_has_holdable_copy FROM asset.metarecord_has_holdable_copy($1)) AS has_holdable
925 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
928 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
930 FROM asset.opac_ou_metarecord_copy_count($2, $1)
934 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
936 FROM asset.staff_ou_metarecord_copy_count($2, $1)
940 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
942 FROM asset.opac_ou_metarecord_copy_count($9, $1)
946 -- XXX monograph_parts and foreign_copies are skipped in MRs ... put them back some day?
949 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
951 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
952 FROM evergreen.ranked_volumes((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $4, $6, $7, $9, $5) AS y
955 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
956 FROM evergreen.located_uris((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $9) AS uris
959 CASE WHEN ('ssub' = ANY ($5)) THEN
962 (SELECT XMLAGG(ssub) FROM (
963 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
964 FROM serial.subscription
965 WHERE record_entry IN (SELECT source FROM metabib.metarecord_source_map WHERE metarecord = $1)
970 $F$ LANGUAGE SQL STABLE;
972 CREATE OR REPLACE FUNCTION unapi.mmr (
978 depth INT DEFAULT NULL,
979 slimit HSTORE DEFAULT NULL,
980 soffset HSTORE DEFAULT NULL,
981 include_xmlns BOOL DEFAULT TRUE,
982 pref_lib INT DEFAULT NULL
986 mmrec metabib.metarecord%ROWTYPE;
987 leadrec biblio.record_entry%ROWTYPE;
988 subrec biblio.record_entry%ROWTYPE;
989 layout unapi.bre_output_layout%ROWTYPE;
990 xfrm config.xml_transform%ROWTYPE;
992 xml_buf TEXT; -- growing XML document
993 tmp_xml TEXT; -- single-use XML string
994 xml_frag TEXT; -- single-use XML fragment
999 subxml XML; -- subordinate records elements
1004 -- xpath for extracting bre.marc values from subordinate records
1005 -- so they may be appended to the MARC of the master record prior
1006 -- to XSLT processing.
1007 -- subjects, isbn, issn, upc -- anything else?
1009 '//*[starts-with(@tag, "6") or @tag="020" or @tag="022" or @tag="024"]';
1011 IF org = '-' OR org IS NULL THEN
1012 SELECT shortname INTO org FROM evergreen.org_top();
1015 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
1017 IF ouid IS NULL THEN
1021 SELECT INTO mmrec * FROM metabib.metarecord WHERE id = obj_id;
1026 -- TODO: aggregate holdings from constituent records
1027 IF format = 'holdings_xml' THEN -- the special case
1028 output := unapi.mmr_holdings_xml(
1029 obj_id, ouid, org, depth,
1030 array_remove(includes,'holdings_xml'),
1031 slimit, soffset, include_xmlns, pref_lib);
1035 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
1037 IF layout.name IS NULL THEN
1041 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
1043 SELECT INTO leadrec * FROM biblio.record_entry WHERE id = mmrec.master_record;
1045 -- Grab distinct MVF for all records if requested
1046 IF ('mra' = ANY (includes)) THEN
1047 axml := unapi.mmr_mra(obj_id,NULL,NULL,NULL,org,depth,NULL,NULL,TRUE,pref_lib);
1052 xml_buf = leadrec.marc;
1055 IF ('holdings_xml' = ANY (includes)) THEN
1056 hxml := unapi.mmr_holdings_xml(
1057 obj_id, ouid, org, depth,
1058 array_remove(includes,'holdings_xml'),
1059 slimit, soffset, include_xmlns, pref_lib);
1062 subxml := NULL::XML;
1063 parts := '{}'::TEXT[];
1064 FOR subrec IN SELECT bre.* FROM biblio.record_entry bre
1065 JOIN metabib.metarecord_source_map mmsm ON (mmsm.source = bre.id)
1066 JOIN metabib.metarecord mmr ON (mmr.id = mmsm.metarecord)
1067 WHERE mmr.id = obj_id AND NOT bre.deleted
1068 ORDER BY CASE WHEN bre.id = mmr.master_record THEN 0 ELSE bre.id END
1069 LIMIT COALESCE((slimit->'bre')::INT, 5) LOOP
1071 IF subrec.id = leadrec.id THEN CONTINUE; END IF;
1072 -- Append choice data from the the non-lead records to the
1073 -- the lead record document
1075 parts := parts || xpath(sub_xpath, subrec.marc::XML)::TEXT[];
1078 SELECT ARRAY_TO_STRING( ARRAY_AGG( DISTINCT p ), '' )::XML INTO subxml FROM UNNEST(parts) p;
1080 -- append data from the subordinate records to the
1081 -- main record document before applying the XSLT
1083 IF subxml IS NOT NULL THEN
1084 xml_buf := REGEXP_REPLACE(xml_buf,
1085 '</record>(.*?)$', subxml || '</record>' || E'\\1');
1088 IF format = 'marcxml' THEN
1089 -- If we're not using the prefixed namespace in
1090 -- this record, then remove all declarations of it
1091 IF xml_buf !~ E'<marc:' THEN
1092 xml_buf := REGEXP_REPLACE(xml_buf,
1093 ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
1096 xml_buf := oils_xslt_process(xml_buf, xfrm.xslt)::XML;
1099 -- update top_el to reflect the change in xml_buf, which may
1100 -- now be a different type of document (e.g. record -> mods)
1101 top_el := REGEXP_REPLACE(xml_buf, E'^.*?<((?:\\S+:)?' ||
1102 layout.holdings_element || ').*$', E'\\1');
1104 IF axml IS NOT NULL THEN
1105 xml_buf := REGEXP_REPLACE(xml_buf,
1106 '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
1109 IF hxml IS NOT NULL THEN
1110 xml_buf := REGEXP_REPLACE(xml_buf,
1111 '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
1114 IF ('mmr.unapi' = ANY (includes)) THEN
1115 output := REGEXP_REPLACE(
1117 '</' || top_el || '>(.*?)',
1121 'http://www.w3.org/1999/xhtml' AS xmlns,
1122 'unapi-id' AS class,
1123 'tag:open-ils.org:U2@mmr/' || obj_id || '/' || org AS title
1125 )::TEXT || '</' || top_el || E'>\\1'
1131 -- remove ignorable whitesace
1132 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
1135 $F$ LANGUAGE PLPGSQL STABLE;
1137 CREATE OR REPLACE FUNCTION authority.extract_headings(marc TEXT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1139 idx authority.heading_field%ROWTYPE;
1140 xfrm config.xml_transform%ROWTYPE;
1142 transformed_xml TEXT;
1144 heading_node_list TEXT[];
1145 component_node TEXT;
1146 component_node_list TEXT[];
1148 normalized_text TEXT;
1153 base_thesaurus TEXT := NULL;
1154 output_row authority.heading;
1157 -- Loop over the indexing entries
1158 FOR idx IN SELECT * FROM authority.heading_field WHERE restrict IS NULL OR id = ANY (restrict) ORDER BY format LOOP
1160 output_row.field := idx.id;
1161 output_row.type := idx.heading_type;
1162 output_row.purpose := idx.heading_purpose;
1164 joiner := COALESCE(idx.joiner, ' ');
1166 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1168 -- See if we can skip the XSLT ... it's expensive
1169 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1170 -- Can't skip the transform
1171 IF xfrm.xslt <> '---' THEN
1172 transformed_xml := oils_xslt_process(marc, xfrm.xslt);
1174 transformed_xml := marc;
1177 prev_xfrm := xfrm.name;
1180 IF idx.thesaurus_xpath IS NOT NULL THEN
1181 base_thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1184 heading_node_list := oils_xpath( idx.heading_xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1186 FOR heading_node IN SELECT x FROM unnest(heading_node_list) AS x LOOP
1188 CONTINUE WHEN heading_node !~ E'^\\s*<';
1190 output_row.variant_type := NULL;
1191 output_row.related_type := NULL;
1192 output_row.thesaurus := NULL;
1193 output_row.heading := NULL;
1195 IF idx.heading_purpose = 'variant' AND idx.type_xpath IS NOT NULL THEN
1196 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1198 output_row.variant_type := type_value;
1199 EXCEPTION WHEN invalid_text_representation THEN
1200 RAISE NOTICE 'Do not recognize variant heading type %', type_value;
1203 IF idx.heading_purpose = 'related' AND idx.type_xpath IS NOT NULL THEN
1204 type_value := ARRAY_TO_STRING(oils_xpath(idx.type_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1206 output_row.related_type := type_value;
1207 EXCEPTION WHEN invalid_text_representation THEN
1208 RAISE NOTICE 'Do not recognize related heading type %', type_value;
1212 IF idx.thesaurus_override_xpath IS NOT NULL THEN
1213 output_row.thesaurus := ARRAY_TO_STRING(oils_xpath(idx.thesaurus_override_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]), '');
1215 IF output_row.thesaurus IS NULL THEN
1216 output_row.thesaurus := base_thesaurus;
1221 -- now iterate over components of heading
1222 component_node_list := oils_xpath( idx.component_xpath, heading_node, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1223 FOR component_node IN SELECT x FROM unnest(component_node_list) AS x LOOP
1224 -- XXX much of this should be moved into oils_xpath_string...
1225 curr_text := ARRAY_TO_STRING(array_remove(array_remove(
1226 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
1227 REGEXP_REPLACE( component_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
1228 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
1232 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
1234 IF raw_text IS NOT NULL THEN
1235 raw_text := raw_text || joiner;
1238 raw_text := COALESCE(raw_text,'') || curr_text;
1241 IF raw_text IS NOT NULL THEN
1242 output_row.heading := raw_text;
1243 normalized_text := raw_text;
1246 SELECT n.func AS func,
1247 n.param_count AS param_count,
1249 FROM config.index_normalizer n
1250 JOIN authority.heading_field_norm_map m ON (m.norm = n.id)
1251 WHERE m.field = idx.id
1254 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1255 quote_literal( normalized_text ) ||
1257 WHEN normalizer.param_count > 0
1258 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1261 ')' INTO normalized_text;
1265 output_row.normalized_heading := normalized_text;
1267 RETURN NEXT output_row;
1273 $func$ LANGUAGE PLPGSQL;
1275 CREATE OR REPLACE FUNCTION authority.extract_headings(rid BIGINT, restrict INT[] DEFAULT NULL) RETURNS SETOF authority.heading AS $func$
1277 auth authority.record_entry%ROWTYPE;
1278 output_row authority.heading;
1281 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
1283 RETURN QUERY SELECT * FROM authority.extract_headings(auth.marc, restrict);
1285 $func$ LANGUAGE PLPGSQL;
1287 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
1289 default_joiner TEXT,
1292 ) RETURNS SETOF metabib.field_entry_template AS $func$
1294 bib biblio.record_entry%ROWTYPE;
1295 idx config.metabib_field%ROWTYPE;
1296 xfrm config.xml_transform%ROWTYPE;
1298 transformed_xml TEXT;
1300 xml_node_list TEXT[];
1307 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
1308 authority_text TEXT;
1309 authority_link BIGINT;
1310 output_row metabib.field_entry_template%ROWTYPE;
1314 -- Start out with no field-use bools set
1315 output_row.browse_field = FALSE;
1316 output_row.facet_field = FALSE;
1317 output_row.display_field = FALSE;
1318 output_row.search_field = FALSE;
1321 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
1323 -- Loop over the indexing entries
1324 FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
1325 CONTINUE WHEN idx.xpath IS NULL OR idx.xpath = ''; -- pure virtual field
1327 process_idx := FALSE;
1328 IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
1329 IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
1330 IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
1331 IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
1332 CONTINUE WHEN process_idx = FALSE; -- disabled for all types
1334 joiner := COALESCE(idx.joiner, default_joiner);
1336 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1338 -- See if we can skip the XSLT ... it's expensive
1339 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1340 -- Can't skip the transform
1341 IF xfrm.xslt <> '---' THEN
1342 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
1344 transformed_xml := bib.marc;
1347 prev_xfrm := xfrm.name;
1350 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1353 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
1354 CONTINUE WHEN xml_node !~ E'^\\s*<';
1356 -- XXX much of this should be moved into oils_xpath_string...
1357 curr_text := ARRAY_TO_STRING(array_remove(array_remove(
1358 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
1359 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
1360 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
1364 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
1366 IF raw_text IS NOT NULL THEN
1367 raw_text := raw_text || joiner;
1370 raw_text := COALESCE(raw_text,'') || curr_text;
1372 -- autosuggest/metabib.browse_entry
1373 IF idx.browse_field THEN
1375 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
1376 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1378 browse_text := curr_text;
1381 IF idx.browse_sort_xpath IS NOT NULL AND
1382 idx.browse_sort_xpath <> '' THEN
1384 sort_value := oils_xpath_string(
1385 idx.browse_sort_xpath, xml_node, joiner,
1386 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1389 sort_value := browse_text;
1392 output_row.field_class = idx.field_class;
1393 output_row.field = idx.id;
1394 output_row.source = rid;
1395 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
1396 output_row.sort_value :=
1397 public.naco_normalize(sort_value);
1399 output_row.authority := NULL;
1401 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
1402 authority_text := oils_xpath_string(
1403 idx.authority_xpath, xml_node, joiner,
1405 ARRAY[xfrm.prefix, xfrm.namespace_uri],
1406 ARRAY['xlink','http://www.w3.org/1999/xlink']
1410 IF authority_text ~ '^\d+$' THEN
1411 authority_link := authority_text::BIGINT;
1412 PERFORM * FROM authority.record_entry WHERE id = authority_link;
1414 output_row.authority := authority_link;
1420 output_row.browse_field = TRUE;
1421 -- Returning browse rows with search_field = true for search+browse
1422 -- configs allows us to retain granularity of being able to search
1423 -- browse fields with "starts with" type operators (for example, for
1424 -- titles of songs in music albums)
1425 IF idx.search_field THEN
1426 output_row.search_field = TRUE;
1428 RETURN NEXT output_row;
1429 output_row.browse_field = FALSE;
1430 output_row.search_field = FALSE;
1431 output_row.sort_value := NULL;
1434 -- insert raw node text for faceting
1435 IF idx.facet_field THEN
1437 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
1438 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1440 facet_text := curr_text;
1443 output_row.field_class = idx.field_class;
1444 output_row.field = -1 * idx.id;
1445 output_row.source = rid;
1446 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
1448 output_row.facet_field = TRUE;
1449 RETURN NEXT output_row;
1450 output_row.facet_field = FALSE;
1453 -- insert raw node text for display
1454 IF idx.display_field THEN
1456 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
1457 display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1459 display_text := curr_text;
1462 output_row.field_class = idx.field_class;
1463 output_row.field = -1 * idx.id;
1464 output_row.source = rid;
1465 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
1467 output_row.display_field = TRUE;
1468 RETURN NEXT output_row;
1469 output_row.display_field = FALSE;
1474 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
1476 -- insert combined node text for searching
1477 IF idx.search_field THEN
1478 output_row.field_class = idx.field_class;
1479 output_row.field = idx.id;
1480 output_row.source = rid;
1481 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
1483 output_row.search_field = TRUE;
1484 RETURN NEXT output_row;
1485 output_row.search_field = FALSE;
1491 $func$ LANGUAGE PLPGSQL;
1493 -- We no longer need the custom function
1494 DROP FUNCTION evergreen.array_remove_item_by_value(ANYARRAY, ANYELEMENT);
1498 SELECT evergreen.upgrade_deps_block_check('1265', :eg_version);
1500 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
1502 'eg.orgselect.hopeless.wide_holds', 'gui', 'integer',
1504 'eg.orgselect.hopeless.wide_holds',
1505 'Default org unit for hopeless holds interface',
1512 -- Update auditor tables to catch changes to source tables.
1513 -- Can be removed/skipped if there were no schema changes.
1514 SELECT auditor.update_auditors();