1 -- XXXX.schema-acs-nfi.sql
4 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
6 -- AFTER UPDATE OR INSERT trigger for authority.record_entry
7 CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
10 IF NEW.deleted IS TRUE THEN -- If this authority is deleted
11 DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
12 DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
13 DELETE FROM authority.simple_heading WHERE record = NEW.id;
14 -- Should remove matching $0 from controlled fields at the same time?
15 RETURN NEW; -- and we're done
18 IF TG_OP = 'UPDATE' THEN -- re-ingest?
19 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
21 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
25 -- Propagate these updates to any linked bib records
26 PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id;
28 DELETE FROM authority.simple_heading WHERE record = NEW.id;
31 INSERT INTO authority.simple_heading (record,atag,value,sort_value)
32 SELECT record, atag, value, sort_value FROM authority.simple_heading_set(NEW.marc);
34 -- Flatten and insert the afr data
35 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
37 PERFORM authority.reingest_authority_full_rec(NEW.id);
38 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
40 PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
46 $func$ LANGUAGE PLPGSQL;
48 ALTER TABLE authority.control_set_authority_field ADD COLUMN nfi CHAR(1);
50 -- Entries that need to respect an NFI
51 UPDATE authority.control_set_authority_field SET nfi = '2'
52 WHERE id IN (4,24,44,64);
54 DROP TRIGGER authority_full_rec_fti_trigger ON authority.full_rec;
55 CREATE TRIGGER authority_full_rec_fti_trigger
56 BEFORE UPDATE OR INSERT ON authority.full_rec
57 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
59 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
61 acsaf authority.control_set_authority_field%ROWTYPE;
70 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
72 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
75 SELECT control_set INTO cset
76 FROM authority.control_set_authority_field
77 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
81 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
82 IF thes_code IS NULL THEN
84 ELSIF thes_code = 'z' THEN
85 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
89 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
90 tag_used := acsaf.tag;
91 nfi_used := acsaf.nfi;
93 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
94 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
96 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
98 tmp_text := SUBSTRING(
103 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
118 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
119 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
122 EXIT WHEN heading_text <> '';
125 IF heading_text <> '' THEN
126 IF no_thesaurus IS TRUE THEN
127 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
129 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
132 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
137 $func$ LANGUAGE PLPGSQL IMMUTABLE;
140 CREATE TABLE authority.simple_heading (
141 id BIGSERIAL PRIMARY KEY,
142 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
143 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
145 sort_value TEXT NOT NULL,
146 index_vector tsvector NOT NULL
148 CREATE TRIGGER authority_simple_heading_fti_trigger
149 BEFORE UPDATE OR INSERT ON authority.simple_heading
150 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
152 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
153 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
154 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
156 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
158 res authority.simple_heading%ROWTYPE;
159 acsaf authority.control_set_authority_field%ROWTYPE;
169 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
172 res.record := auth_id;
174 SELECT control_set INTO cset
175 FROM authority.control_set_authority_field
176 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
179 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
181 res.atag := acsaf.id;
182 tag_used := acsaf.tag;
183 nfi_used := acsaf.nfi;
185 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
188 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
189 heading_text := heading_text || COALESCE( ' ' || oils_xpath_string('//*[@code="'||sf||'"]',tmp_xml::TEXT), '');
192 heading_text := public.naco_normalize(heading_text);
194 IF nfi_used IS NOT NULL THEN
196 sort_text := SUBSTRING(
201 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
213 sort_text := heading_text;
216 IF heading_text IS NOT NULL AND heading_text <> '' THEN
217 res.value := heading_text;
218 res.sort_value := sort_text;
228 $func$ LANGUAGE PLPGSQL IMMUTABLE;
230 -- Support function used to find the pivot for alpha-heading-browse style searching
231 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
233 sort_value_row RECORD;
238 t_term := public.naco_normalize(q);
240 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
241 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
244 FROM authority.simple_heading ash
245 WHERE ash.atag = ANY (a)
246 AND ash.sort_value >= t_term
247 ORDER BY rank DESC, ash.sort_value
250 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
251 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
254 FROM authority.simple_heading ash
255 WHERE ash.atag = ANY (a)
256 AND ash.value >= t_term
257 ORDER BY rank DESC, ash.sort_value
260 IF value_row.rank > sort_value_row.rank THEN
261 RETURN value_row.sort_value;
263 RETURN sort_value_row.sort_value;
269 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
271 pivot_sort_value TEXT;
272 boffset INT DEFAULT 0;
273 aoffset INT DEFAULT 0;
274 blimit INT DEFAULT 0;
275 alimit INT DEFAULT 0;
278 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
281 blimit := pagesize / 2;
284 IF pagesize % 2 <> 0 THEN
285 alimit := alimit + 1;
291 boffset := pagesize / 2;
294 IF pagesize % 2 <> 0 THEN
295 boffset := boffset + 1;
301 -- "bottom" half of the browse results
305 FROM authority.simple_heading ash
306 WHERE ash.atag = ANY (atag_list)
307 AND ash.sort_value < pivot_sort_value
308 ORDER BY ash.sort_value DESC
310 OFFSET ABS(page) * pagesize - boffset
311 ) x ORDER BY row_number DESC;
316 -- "bottom" half of the browse results
318 FROM authority.simple_heading ash
319 WHERE ash.atag = ANY (atag_list)
320 AND ash.sort_value >= pivot_sort_value
321 ORDER BY ash.sort_value
323 OFFSET ABS(page) * pagesize - aoffset;
326 $$ LANGUAGE PLPGSQL ROWS 10;
328 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
330 pivot_sort_value TEXT;
333 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
337 -- "bottom" half of the browse results
341 FROM authority.simple_heading ash
342 WHERE ash.atag = ANY (atag_list)
343 AND ash.sort_value < pivot_sort_value
344 ORDER BY ash.sort_value DESC
346 OFFSET (ABS(page) - 1) * pagesize
347 ) x ORDER BY row_number DESC;
352 -- "bottom" half of the browse results
354 FROM authority.simple_heading ash
355 WHERE ash.atag = ANY (atag_list)
356 AND ash.sort_value >= pivot_sort_value
357 ORDER BY ash.sort_value
359 OFFSET ABS(page) * pagesize ;
362 $$ LANGUAGE PLPGSQL ROWS 10;
364 CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
366 FROM authority.simple_heading ash,
367 public.naco_normalize($2) t(term),
368 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
369 WHERE ash.atag = ANY ($1)
370 AND ash.index_vector @@ ptsq.term
371 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
372 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
373 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
376 $$ LANGUAGE SQL ROWS 10;
378 CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
380 FROM authority.simple_heading ash,
381 public.naco_normalize($2) t(term),
382 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
383 WHERE ash.atag = ANY ($1)
384 AND ash.index_vector @@ ptsq.term
385 ORDER BY ash.sort_value
388 $$ LANGUAGE SQL ROWS 10;
391 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
392 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
395 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
398 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
400 FROM authority.browse_axis_authority_field_map a
406 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
407 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
410 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
412 ARRAY[a.authority_field],
413 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
415 FROM authority.control_set_bib_field a
421 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
422 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
425 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
428 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
430 FROM authority.control_set_authority_field a
435 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
436 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
437 $$ LANGUAGE SQL ROWS 10;
439 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
440 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
441 $$ LANGUAGE SQL ROWS 10;
443 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
444 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
445 $$ LANGUAGE SQL ROWS 10;
447 CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
448 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
449 $$ LANGUAGE SQL ROWS 10;
451 CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
452 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
453 $$ LANGUAGE SQL ROWS 10;
455 CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
456 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
457 $$ LANGUAGE SQL ROWS 10;
460 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
461 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
462 $$ LANGUAGE SQL ROWS 10;
464 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
465 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
466 $$ LANGUAGE SQL ROWS 10;
468 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
469 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
470 $$ LANGUAGE SQL ROWS 10;
472 CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
473 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
474 $$ LANGUAGE SQL ROWS 10;
476 CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
477 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
478 $$ LANGUAGE SQL ROWS 10;
480 CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
481 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
482 $$ LANGUAGE SQL ROWS 10;
485 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
486 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
487 $$ LANGUAGE SQL ROWS 10;
489 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
490 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
491 $$ LANGUAGE SQL ROWS 10;
493 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
494 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
495 $$ LANGUAGE SQL ROWS 10;
497 CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
498 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
499 $$ LANGUAGE SQL ROWS 10;
501 CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
502 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
503 $$ LANGUAGE SQL ROWS 10;
505 CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
506 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
507 $$ LANGUAGE SQL ROWS 10;
510 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
511 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
512 $$ LANGUAGE SQL ROWS 10;
514 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
515 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
516 $$ LANGUAGE SQL ROWS 10;
518 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
519 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
520 $$ LANGUAGE SQL ROWS 10;
522 CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
523 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
524 $$ LANGUAGE SQL ROWS 10;
526 CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
527 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
528 $$ LANGUAGE SQL ROWS 10;
530 CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
531 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
532 $$ LANGUAGE SQL ROWS 10;