1 -- Evergreen DB patch 0837.schema.browse-auth-linking.plus-joiner.sql
3 -- In this upgrade script we complete inter-subfield joiner support, so that
4 -- subject components can be separated by " -- ", for instance. That's the
7 -- We also add the ability to browse by in-use authority main entries and find
8 -- bibs that use unauthorized versions of the authority's value, by string matching.
13 -- check whether patch can be applied
14 SELECT evergreen.upgrade_deps_block_check('0837', :eg_version);
16 ALTER TABLE config.metabib_field ADD COLUMN joiner TEXT;
17 UPDATE config.metabib_field SET joiner = ' -- ' WHERE field_class = 'subject' AND name NOT IN ('name', 'complete');
19 -- To avoid problems with altering a table column after doing an
21 ALTER TABLE authority.control_set_authority_field DISABLE TRIGGER ALL;
23 ALTER TABLE authority.control_set_authority_field ADD COLUMN joiner TEXT;
24 UPDATE authority.control_set_authority_field SET joiner = ' -- ' WHERE tag LIKE ANY (ARRAY['_4_','_5_','_8_']);
26 ALTER TABLE authority.control_set_authority_field ENABLE TRIGGER ALL;
28 -- Seed data will be generated from class <-> axis mapping
29 CREATE TABLE authority.control_set_bib_field_metabib_field_map (
30 id SERIAL PRIMARY KEY,
31 bib_field INT NOT NULL REFERENCES authority.control_set_bib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
32 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
33 CONSTRAINT a_bf_mf_map_once UNIQUE (bib_field, metabib_field)
36 CREATE VIEW authority.control_set_auth_field_metabib_field_map_main AS
37 SELECT DISTINCT b.authority_field, m.metabib_field
38 FROM authority.control_set_bib_field_metabib_field_map m JOIN authority.control_set_bib_field b ON (b.id = m.bib_field);
39 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_main IS $$metabib fields for main entry auth fields$$;
41 CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs_only AS
42 SELECT DISTINCT a.id AS authority_field, m.metabib_field
43 FROM authority.control_set_authority_field a
44 JOIN authority.control_set_authority_field ame ON (a.main_entry = ame.id)
45 JOIN authority.control_set_bib_field b ON (b.authority_field = ame.id)
46 JOIN authority.control_set_bib_field_metabib_field_map mf ON (mf.bib_field = b.id)
47 JOIN authority.control_set_auth_field_metabib_field_map_main m ON (ame.id = m.authority_field);
48 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs_only IS $$metabib fields for NON-main entry auth fields$$;
50 CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs AS
51 SELECT * FROM authority.control_set_auth_field_metabib_field_map_main
53 SELECT * FROM authority.control_set_auth_field_metabib_field_map_refs_only;
54 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs IS $$metabib fields for all auth fields$$;
57 -- blind refs only is probably what we want for lookup in bib/auth browse
58 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only AS
60 FROM authority.control_set_auth_field_metabib_field_map_refs_only r
61 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
62 WHERE linking_subfield IS NULL;
63 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only IS $$metabib fields for NON-main entry auth fields that can't be linked to other records$$; -- '
65 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs AS
67 FROM authority.control_set_auth_field_metabib_field_map_refs r
68 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
69 WHERE linking_subfield IS NULL;
70 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs IS $$metabib fields for all auth fields that can't be linked to other records$$; -- '
72 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_main AS
74 FROM authority.control_set_auth_field_metabib_field_map_main r
75 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
76 WHERE linking_subfield IS NULL;
77 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_main IS $$metabib fields for main entry auth fields that can't be linked to other records$$; -- '
79 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
81 acsaf authority.control_set_authority_field%ROWTYPE;
92 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
94 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
97 SELECT control_set INTO cset
98 FROM authority.control_set_authority_field
99 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
103 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
104 IF thes_code IS NULL THEN
106 ELSIF thes_code = 'z' THEN
107 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
111 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
112 tag_used := acsaf.tag;
113 nfi_used := acsaf.nfi;
116 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
117 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
119 tmp_text := oils_xpath_string('.', sf_node);
120 sf := oils_xpath_string('./@code', sf_node);
122 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
124 tmp_text := SUBSTRING(
129 oils_xpath_string('./@ind'||nfi_used, tag_node),
144 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
145 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
149 EXIT WHEN heading_text <> '';
152 EXIT WHEN heading_text <> '';
155 IF heading_text <> '' THEN
156 IF no_thesaurus IS TRUE THEN
157 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
159 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
162 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
167 $func$ LANGUAGE PLPGSQL IMMUTABLE;
169 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
171 res authority.simple_heading%ROWTYPE;
172 acsaf authority.control_set_authority_field%ROWTYPE;
183 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
186 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
189 SELECT control_set INTO cset
190 FROM authority.control_set_authority_field
191 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
195 res.record := auth_id;
197 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
199 res.atag := acsaf.id;
200 tag_used := acsaf.tag;
201 nfi_used := acsaf.nfi;
202 joiner_text := COALESCE(acsaf.joiner, ' ');
204 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
206 heading_text := COALESCE(
207 oils_xpath_string('./*[contains("'||acsaf.sf_list||'",@code)]', tmp_xml::TEXT, joiner_text),
211 IF nfi_used IS NOT NULL THEN
213 sort_text := SUBSTRING(
218 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
230 sort_text := heading_text;
233 IF heading_text IS NOT NULL AND heading_text <> '' THEN
234 res.value := heading_text;
235 res.sort_value := public.naco_normalize(sort_text);
236 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
246 $func$ LANGUAGE PLPGSQL IMMUTABLE;
248 CREATE TABLE metabib.browse_entry_simple_heading_map (
249 id BIGSERIAL PRIMARY KEY,
250 entry BIGINT REFERENCES metabib.browse_entry (id),
251 simple_heading BIGINT REFERENCES authority.simple_heading (id) ON DELETE CASCADE
253 CREATE INDEX browse_entry_sh_map_entry_idx ON metabib.browse_entry_simple_heading_map (entry);
254 CREATE INDEX browse_entry_sh_map_sh_idx ON metabib.browse_entry_simple_heading_map (simple_heading);
256 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
258 bib biblio.record_entry%ROWTYPE;
259 idx config.metabib_field%ROWTYPE;
260 xfrm config.xml_transform%ROWTYPE;
262 transformed_xml TEXT;
264 xml_node_list TEXT[];
270 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
272 authority_link BIGINT;
273 output_row metabib.field_entry_template%ROWTYPE;
276 -- Start out with no field-use bools set
277 output_row.browse_field = FALSE;
278 output_row.facet_field = FALSE;
279 output_row.search_field = FALSE;
282 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
284 -- Loop over the indexing entries
285 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
287 joiner := COALESCE(idx.joiner, default_joiner);
289 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
291 -- See if we can skip the XSLT ... it's expensive
292 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
293 -- Can't skip the transform
294 IF xfrm.xslt <> '---' THEN
295 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
297 transformed_xml := bib.marc;
300 prev_xfrm := xfrm.name;
303 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
306 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
307 CONTINUE WHEN xml_node !~ E'^\\s*<';
309 -- XXX much of this should be moved into oils_xpath_string...
310 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
311 oils_xpath( '//text()',
313 REGEXP_REPLACE( -- This escapes all &s not followed by "amp;". Data ise returned from oils_xpath (above) in UTF-8, not entity encoded
314 REGEXP_REPLACE( -- This escapes embeded <s
316 $re$(>[^<]+)(<)([^>]+<)$re$,
332 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
334 IF raw_text IS NOT NULL THEN
335 raw_text := raw_text || joiner;
338 raw_text := COALESCE(raw_text,'') || curr_text;
340 -- autosuggest/metabib.browse_entry
341 IF idx.browse_field THEN
343 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
344 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
346 browse_text := curr_text;
349 IF idx.browse_sort_xpath IS NOT NULL AND
350 idx.browse_sort_xpath <> '' THEN
352 sort_value := oils_xpath_string(
353 idx.browse_sort_xpath, xml_node, joiner,
354 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
357 sort_value := browse_text;
360 output_row.field_class = idx.field_class;
361 output_row.field = idx.id;
362 output_row.source = rid;
363 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
364 output_row.sort_value :=
365 public.naco_normalize(sort_value);
367 output_row.authority := NULL;
369 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
370 authority_text := oils_xpath_string(
371 idx.authority_xpath, xml_node, joiner,
373 ARRAY[xfrm.prefix, xfrm.namespace_uri],
374 ARRAY['xlink','http://www.w3.org/1999/xlink']
378 IF authority_text ~ '^\d+$' THEN
379 authority_link := authority_text::BIGINT;
380 PERFORM * FROM authority.record_entry WHERE id = authority_link;
382 output_row.authority := authority_link;
388 output_row.browse_field = TRUE;
389 RETURN NEXT output_row;
390 output_row.browse_field = FALSE;
391 output_row.sort_value := NULL;
394 -- insert raw node text for faceting
395 IF idx.facet_field THEN
397 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
398 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
400 facet_text := curr_text;
403 output_row.field_class = idx.field_class;
404 output_row.field = -1 * idx.id;
405 output_row.source = rid;
406 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
408 output_row.facet_field = TRUE;
409 RETURN NEXT output_row;
410 output_row.facet_field = FALSE;
415 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
417 -- insert combined node text for searching
418 IF idx.search_field THEN
419 output_row.field_class = idx.field_class;
420 output_row.field = idx.id;
421 output_row.source = rid;
422 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
424 output_row.search_field = TRUE;
425 RETURN NEXT output_row;
426 output_row.search_field = FALSE;
433 $func$ LANGUAGE PLPGSQL;
436 FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
439 orig_ended_in_space BOOLEAN;
444 orig_ended_in_space := orig ~ E'\\s$';
446 orig := ARRAY_TO_STRING(
447 evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
450 normalized := public.naco_normalize(orig); -- also trim()s
453 IF NOT orig_ended_in_space THEN
454 plain := plain || ':*';
455 normalized := normalized || ':*';
458 plain := ARRAY_TO_STRING(
459 evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
461 normalized := ARRAY_TO_STRING(
462 evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
465 RETURN ARRAY[normalized, plain];
469 ALTER TYPE metabib.flat_browse_entry_appearance ADD ATTRIBUTE sees TEXT;
470 ALTER TYPE metabib.flat_browse_entry_appearance ADD ATTRIBUTE asources INT;
471 ALTER TYPE metabib.flat_browse_entry_appearance ADD ATTRIBUTE aaccurate TEXT;
473 CREATE OR REPLACE FUNCTION metabib.browse_bib_pivot(
476 ) RETURNS BIGINT AS $p$
478 FROM metabib.browse_entry mbe
479 JOIN metabib.browse_entry_def_map mbedm ON (
481 AND mbedm.def = ANY($1)
483 WHERE mbe.sort_value >= public.naco_normalize($2)
484 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
487 CREATE OR REPLACE FUNCTION metabib.browse_authority_pivot(
490 ) RETURNS BIGINT AS $p$
492 FROM metabib.browse_entry mbe
493 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
494 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
495 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
496 ash.atag = map.authority_field
497 AND map.metabib_field = ANY($1)
499 WHERE mbe.sort_value >= public.naco_normalize($2)
500 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
503 CREATE OR REPLACE FUNCTION metabib.browse_authority_refs_pivot(
506 ) RETURNS BIGINT AS $p$
508 FROM metabib.browse_entry mbe
509 JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
510 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
511 JOIN authority.control_set_auth_field_metabib_field_map_refs_only map ON (
512 ash.atag = map.authority_field
513 AND map.metabib_field = ANY($1)
515 WHERE mbe.sort_value >= public.naco_normalize($2)
516 ORDER BY mbe.sort_value, mbe.value LIMIT 1;
519 -- The drop is necessary because the language change from PLPGSQL to SQL
520 -- carries with it name changes to the parameters
521 DROP FUNCTION metabib.browse_pivot(INT[], TEXT);
522 CREATE FUNCTION metabib.browse_pivot(
525 ) RETURNS BIGINT AS $p$
526 SELECT id FROM metabib.browse_entry
528 metabib.browse_bib_pivot($1, $2),
529 metabib.browse_authority_refs_pivot($1,$2) -- only look in 4xx, 5xx, 7xx of authority
531 ORDER BY sort_value, value LIMIT 1;
534 CREATE OR REPLACE FUNCTION metabib.staged_browse(
538 context_locations INT[],
540 browse_superpage_size INT,
541 count_up_from_zero BOOL, -- if false, count down from -1
544 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
552 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
553 results_skipped INT := 0;
554 row_counter INT := 0;
559 all_records BIGINT[];
560 all_brecords BIGINT[];
561 all_arecords BIGINT[];
562 superpage_of_records BIGINT[];
565 IF count_up_from_zero THEN
571 OPEN curs FOR EXECUTE query;
576 IF result_row.pivot_point IS NOT NULL THEN
577 RETURN NEXT result_row;
583 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
584 SELECT INTO all_arecords, result_row.sees, afields
585 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
586 ARRAY_TO_STRING(ARRAY_AGG(DISTINCT aal.source), $$,$$), -- authority record ids
587 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
589 FROM metabib.browse_entry_simple_heading_map mbeshm
590 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
591 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
592 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
593 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
594 ash.atag = map.authority_field
595 AND map.metabib_field = ANY(fields)
597 WHERE mbeshm.entry = rec.id;
600 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
601 SELECT INTO all_brecords, result_row.authorities, bfields
602 ARRAY_AGG(DISTINCT source),
603 ARRAY_TO_STRING(ARRAY_AGG(DISTINCT authority), $$,$$),
604 ARRAY_AGG(DISTINCT def)
605 FROM metabib.browse_entry_def_map
607 AND def = ANY(fields);
609 SELECT INTO result_row.fields ARRAY_TO_STRING(ARRAY_AGG(DISTINCT x), $$,$$) FROM UNNEST(afields || bfields) x;
611 result_row.sources := 0;
612 result_row.asources := 0;
614 -- Bib-linked vis checking
615 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
617 full_end := ARRAY_LENGTH(all_brecords, 1);
618 superpage_size := COALESCE(browse_superpage_size, full_end);
620 slice_end := superpage_size;
622 WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
623 superpage_of_records := all_brecords[slice_start:slice_end];
625 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
626 '1::INT AS rel FROM (SELECT UNNEST(' ||
627 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
629 -- We use search.query_parser_fts() for visibility testing.
630 -- We're calling it once per browse-superpage worth of records
631 -- out of the set of records related to a given mbe, until we've
632 -- either exhausted that set of records or found at least 1
635 SELECT INTO result_row.sources visible
636 FROM search.query_parser_fts(
637 context_org, NULL, qpfts_query, NULL,
638 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
640 WHERE qpfts.rel IS NULL;
642 slice_start := slice_start + superpage_size;
643 slice_end := slice_end + superpage_size;
646 -- Accurate? Well, probably.
647 result_row.accurate := browse_superpage_size IS NULL OR
648 browse_superpage_size >= full_end;
652 -- Authority-linked vis checking
653 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
655 full_end := ARRAY_LENGTH(all_arecords, 1);
656 superpage_size := COALESCE(browse_superpage_size, full_end);
658 slice_end := superpage_size;
660 WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
661 superpage_of_records := all_arecords[slice_start:slice_end];
663 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
664 '1::INT AS rel FROM (SELECT UNNEST(' ||
665 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
667 -- We use search.query_parser_fts() for visibility testing.
668 -- We're calling it once per browse-superpage worth of records
669 -- out of the set of records related to a given mbe, via
670 -- authority until we've either exhausted that set of records
671 -- or found at least 1 visible record.
673 SELECT INTO result_row.asources visible
674 FROM search.query_parser_fts(
675 context_org, NULL, qpfts_query, NULL,
676 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
678 WHERE qpfts.rel IS NULL;
680 slice_start := slice_start + superpage_size;
681 slice_end := slice_end + superpage_size;
685 -- Accurate? Well, probably.
686 result_row.aaccurate := browse_superpage_size IS NULL OR
687 browse_superpage_size >= full_end;
691 IF result_row.sources > 0 OR result_row.asources > 0 THEN
693 -- The function that calls this function needs row_number in order
694 -- to correctly order results from two different runs of this
696 result_row.row_number := row_number;
698 -- Now, if row_counter is still less than limit, return a row. If
699 -- not, but it is less than next_pivot_pos, continue on without
700 -- returning actual result rows until we find
701 -- that next pivot, and return it.
703 IF row_counter < result_limit THEN
704 result_row.browse_entry := rec.id;
705 result_row.value := rec.value;
707 RETURN NEXT result_row;
709 result_row.browse_entry := NULL;
710 result_row.authorities := NULL;
711 result_row.fields := NULL;
712 result_row.value := NULL;
713 result_row.sources := NULL;
714 result_row.sees := NULL;
715 result_row.accurate := NULL;
716 result_row.aaccurate := NULL;
717 result_row.pivot_point := rec.id;
719 IF row_counter >= next_pivot_pos THEN
720 RETURN NEXT result_row;
725 IF count_up_from_zero THEN
726 row_number := row_number + 1;
728 row_number := row_number - 1;
731 -- row_counter is different from row_number.
732 -- It simply counts up from zero so that we know when
733 -- we've reached our limit.
734 row_counter := row_counter + 1;
738 $p$ LANGUAGE PLPGSQL;
740 CREATE OR REPLACE FUNCTION metabib.browse(
743 context_org INT DEFAULT NULL,
744 context_loc_group INT DEFAULT NULL,
745 staff BOOL DEFAULT FALSE,
746 pivot_id BIGINT DEFAULT NULL,
747 result_limit INT DEFAULT 10
748 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
753 pivot_sort_value TEXT;
754 pivot_sort_fallback TEXT;
755 context_locations INT[];
756 browse_superpage_size INT;
757 results_skipped INT := 0;
761 forward_to_pivot INT;
763 -- First, find the pivot if we were given a browse term but not a pivot.
764 IF pivot_id IS NULL THEN
765 pivot_id := metabib.browse_pivot(search_field, browse_term);
768 SELECT INTO pivot_sort_value, pivot_sort_fallback
769 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
771 -- Bail if we couldn't find a pivot.
772 IF pivot_sort_value IS NULL THEN
776 -- Transform the context_loc_group argument (if any) (logc at the
777 -- TPAC layer) into a form we'll be able to use.
778 IF context_loc_group IS NOT NULL THEN
779 SELECT INTO context_locations ARRAY_AGG(location)
780 FROM asset.copy_location_group_map
781 WHERE lgroup = context_loc_group;
784 -- Get the configured size of browse superpages.
785 SELECT INTO browse_superpage_size value -- NULL ok
786 FROM config.global_flag
787 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
789 -- First we're going to search backward from the pivot, then we're going
790 -- to search forward. In each direction, we need two limits. At the
791 -- lesser of the two limits, we delineate the edge of the result set
792 -- we're going to return. At the greater of the two limits, we find the
793 -- pivot value that would represent an offset from the current pivot
794 -- at a distance of one "page" in either direction, where a "page" is a
795 -- result set of the size specified in the "result_limit" argument.
797 -- The two limits in each direction make four derived values in total,
798 -- and we calculate them now.
799 back_limit := CEIL(result_limit::FLOAT / 2);
800 back_to_pivot := result_limit;
801 forward_limit := result_limit / 2;
802 forward_to_pivot := result_limit - 1;
804 -- This is the meat of the SQL query that finds browse entries. We'll
805 -- pass this to a function which uses it with a cursor, so that individual
806 -- rows may be fetched in a loop until some condition is satisfied, without
807 -- waiting for a result set of fixed size to be collected all at once.
812 FROM metabib.browse_entry mbe
814 EXISTS ( -- are there any bibs using this mbe via the requested fields?
816 FROM metabib.browse_entry_def_map mbedm
817 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
819 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
821 FROM metabib.browse_entry_simple_heading_map mbeshm
822 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
823 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
824 ash.atag = map.authority_field
825 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
827 WHERE mbeshm.entry = mbe.id
831 -- This is the variant of the query for browsing backward.
832 back_query := core_query ||
833 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
834 ' ORDER BY mbe.sort_value DESC, mbe.value DESC ';
836 -- This variant browses forward.
837 forward_query := core_query ||
838 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
839 ' ORDER BY mbe.sort_value, mbe.value ';
841 -- We now call the function which applies a cursor to the provided
842 -- queries, stopping at the appropriate limits and also giving us
843 -- the next page's pivot.
845 SELECT * FROM metabib.staged_browse(
846 back_query, search_field, context_org, context_locations,
847 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
849 SELECT * FROM metabib.staged_browse(
850 forward_query, search_field, context_org, context_locations,
851 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
852 ) ORDER BY row_number DESC;
855 $p$ LANGUAGE PLPGSQL;
857 -- No 4XX inter-authority linking
858 UPDATE authority.control_set_authority_field SET linking_subfield = NULL;
859 UPDATE authority.control_set_authority_field SET linking_subfield = '0' WHERE tag LIKE ANY (ARRAY['5%','7%']);
861 -- Map between authority controlled bib fields and stock indexing metabib fields
862 INSERT INTO authority.control_set_bib_field_metabib_field_map (bib_field, metabib_field)
863 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
864 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
865 WHERE a.tag = '100' AND m.name = 'personal'
869 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
870 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
871 WHERE a.tag = '110' AND m.name = 'corporate'
875 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
876 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
877 WHERE a.tag = '111' AND m.name = 'conference'
881 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
882 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
883 WHERE a.tag = '130' AND m.name = 'uniform'
887 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
888 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
889 WHERE a.tag = '148' AND m.name = 'temporal'
893 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
894 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
895 WHERE a.tag = '150' AND m.name = 'topic'
899 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
900 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
901 WHERE a.tag = '151' AND m.name = 'geographic'
905 SELECT DISTINCT b.id AS bib_field, m.id AS metabib_field
906 FROM authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
907 WHERE a.tag = '155' AND m.name = 'genre' -- Just in case...
910 CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
912 ashs authority.simple_heading%ROWTYPE;
913 mbe_row metabib.browse_entry%ROWTYPE;
918 IF NEW.deleted IS TRUE THEN -- If this authority is deleted
919 DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
920 DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
921 DELETE FROM authority.simple_heading WHERE record = NEW.id;
922 -- Should remove matching $0 from controlled fields at the same time?
924 -- XXX What do we about the actual linking subfields present in
925 -- authority records that target this one when this happens?
926 DELETE FROM authority.authority_linking
927 WHERE source = NEW.id OR target = NEW.id;
929 RETURN NEW; -- and we're done
932 IF TG_OP = 'UPDATE' THEN -- re-ingest?
933 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
935 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
939 -- Propagate these updates to any linked bib records
940 PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id;
942 DELETE FROM authority.simple_heading WHERE record = NEW.id;
943 DELETE FROM authority.authority_linking WHERE source = NEW.id;
946 INSERT INTO authority.authority_linking (source, target, field)
947 SELECT source, target, field FROM authority.calculate_authority_linking(
948 NEW.id, NEW.control_set, NEW.marc::XML
951 FOR ashs IN SELECT * FROM authority.simple_heading_set(NEW.marc) LOOP
953 INSERT INTO authority.simple_heading (record,atag,value,sort_value)
954 VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value);
955 ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS);
957 SELECT INTO mbe_row * FROM metabib.browse_entry
958 WHERE value = ashs.value AND sort_value = ashs.sort_value;
961 mbe_id := mbe_row.id;
963 INSERT INTO metabib.browse_entry
964 ( value, sort_value ) VALUES
965 ( ashs.value, ashs.sort_value );
967 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
970 INSERT INTO metabib.browse_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id);
974 -- Flatten and insert the afr data
975 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
977 PERFORM authority.reingest_authority_full_rec(NEW.id);
978 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
980 PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
986 $func$ LANGUAGE PLPGSQL;