1 -- XXXX.schema-acs-nfi.sql
4 SELECT evergreen.upgrade_deps_block_check('0640', :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;
139 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
140 SELECT authority.normalize_heading($1, TRUE);
141 $func$ LANGUAGE SQL IMMUTABLE;
143 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
144 SELECT authority.normalize_heading($1, FALSE);
145 $func$ LANGUAGE SQL IMMUTABLE;
148 CREATE TABLE authority.simple_heading (
149 id BIGSERIAL PRIMARY KEY,
150 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
151 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
153 sort_value TEXT NOT NULL,
154 index_vector tsvector NOT NULL
156 CREATE TRIGGER authority_simple_heading_fti_trigger
157 BEFORE UPDATE OR INSERT ON authority.simple_heading
158 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
160 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
161 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
162 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
164 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
166 res authority.simple_heading%ROWTYPE;
167 acsaf authority.control_set_authority_field%ROWTYPE;
177 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
180 res.record := auth_id;
182 SELECT control_set INTO cset
183 FROM authority.control_set_authority_field
184 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
187 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
189 res.atag := acsaf.id;
190 tag_used := acsaf.tag;
191 nfi_used := acsaf.nfi;
193 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
196 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
197 heading_text := heading_text || COALESCE( ' ' || oils_xpath_string('//*[@code="'||sf||'"]',tmp_xml::TEXT), '');
200 heading_text := public.naco_normalize(heading_text);
202 IF nfi_used IS NOT NULL THEN
204 sort_text := SUBSTRING(
209 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
221 sort_text := heading_text;
224 IF heading_text IS NOT NULL AND heading_text <> '' THEN
225 res.value := heading_text;
226 res.sort_value := sort_text;
236 $func$ LANGUAGE PLPGSQL IMMUTABLE;
238 -- Support function used to find the pivot for alpha-heading-browse style searching
239 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
241 sort_value_row RECORD;
246 t_term := public.naco_normalize(q);
248 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
249 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
252 FROM authority.simple_heading ash
253 WHERE ash.atag = ANY (a)
254 AND ash.sort_value >= t_term
255 ORDER BY rank DESC, ash.sort_value
258 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
259 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
262 FROM authority.simple_heading ash
263 WHERE ash.atag = ANY (a)
264 AND ash.value >= t_term
265 ORDER BY rank DESC, ash.sort_value
268 IF value_row.rank > sort_value_row.rank THEN
269 RETURN value_row.sort_value;
271 RETURN sort_value_row.sort_value;
277 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 $$
279 pivot_sort_value TEXT;
280 boffset INT DEFAULT 0;
281 aoffset INT DEFAULT 0;
282 blimit INT DEFAULT 0;
283 alimit INT DEFAULT 0;
286 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
289 blimit := pagesize / 2;
292 IF pagesize % 2 <> 0 THEN
293 alimit := alimit + 1;
299 boffset := pagesize / 2;
302 IF pagesize % 2 <> 0 THEN
303 boffset := boffset + 1;
309 -- "bottom" half of the browse results
313 FROM authority.simple_heading ash
314 WHERE ash.atag = ANY (atag_list)
315 AND ash.sort_value < pivot_sort_value
316 ORDER BY ash.sort_value DESC
318 OFFSET ABS(page) * pagesize - boffset
319 ) x ORDER BY row_number DESC;
324 -- "bottom" half of the browse results
326 FROM authority.simple_heading ash
327 WHERE ash.atag = ANY (atag_list)
328 AND ash.sort_value >= pivot_sort_value
329 ORDER BY ash.sort_value
331 OFFSET ABS(page) * pagesize - aoffset;
334 $$ LANGUAGE PLPGSQL ROWS 10;
336 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 $$
338 pivot_sort_value TEXT;
341 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
345 -- "bottom" half of the browse results
349 FROM authority.simple_heading ash
350 WHERE ash.atag = ANY (atag_list)
351 AND ash.sort_value < pivot_sort_value
352 ORDER BY ash.sort_value DESC
354 OFFSET (ABS(page) - 1) * pagesize
355 ) x ORDER BY row_number DESC;
360 -- "bottom" half of the browse results
362 FROM authority.simple_heading ash
363 WHERE ash.atag = ANY (atag_list)
364 AND ash.sort_value >= pivot_sort_value
365 ORDER BY ash.sort_value
367 OFFSET ABS(page) * pagesize ;
370 $$ LANGUAGE PLPGSQL ROWS 10;
372 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 $$
374 FROM authority.simple_heading ash,
375 public.naco_normalize($2) t(term),
376 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
377 WHERE ash.atag = ANY ($1)
378 AND ash.index_vector @@ ptsq.term
379 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
380 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
381 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
384 $$ LANGUAGE SQL ROWS 10;
386 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 $$
388 FROM authority.simple_heading ash,
389 public.naco_normalize($2) t(term),
390 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
391 WHERE ash.atag = ANY ($1)
392 AND ash.index_vector @@ ptsq.term
393 ORDER BY ash.sort_value
396 $$ LANGUAGE SQL ROWS 10;
399 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
400 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
403 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
406 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
408 FROM authority.browse_axis_authority_field_map a
414 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
415 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
418 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
420 ARRAY[a.authority_field],
421 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
423 FROM authority.control_set_bib_field a
429 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
430 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
433 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
436 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
438 FROM authority.control_set_authority_field a
443 CREATE OR REPLACE FUNCTION authority.axis_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.axis_authority_tags($1), $2, $3, $4)
445 $$ LANGUAGE SQL ROWS 10;
447 CREATE OR REPLACE FUNCTION authority.btag_browse_center( 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.btag_authority_tags($1), $2, $3, $4)
449 $$ LANGUAGE SQL ROWS 10;
451 CREATE OR REPLACE FUNCTION authority.atag_browse_center( 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.atag_authority_tags($1), $2, $3, $4)
453 $$ LANGUAGE SQL ROWS 10;
455 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 $$
456 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
457 $$ LANGUAGE SQL ROWS 10;
459 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 $$
460 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
461 $$ LANGUAGE SQL ROWS 10;
463 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 $$
464 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
465 $$ LANGUAGE SQL ROWS 10;
468 CREATE OR REPLACE FUNCTION authority.axis_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.axis_authority_tags($1), $2, $3, $4)
470 $$ LANGUAGE SQL ROWS 10;
472 CREATE OR REPLACE FUNCTION authority.btag_browse_top( 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.btag_authority_tags($1), $2, $3, $4)
474 $$ LANGUAGE SQL ROWS 10;
476 CREATE OR REPLACE FUNCTION authority.atag_browse_top( 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.atag_authority_tags($1), $2, $3, $4)
478 $$ LANGUAGE SQL ROWS 10;
480 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 $$
481 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
482 $$ LANGUAGE SQL ROWS 10;
484 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 $$
485 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
486 $$ LANGUAGE SQL ROWS 10;
488 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 $$
489 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
490 $$ LANGUAGE SQL ROWS 10;
493 CREATE OR REPLACE FUNCTION authority.axis_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.axis_authority_tags($1), $2, $3, $4)
495 $$ LANGUAGE SQL ROWS 10;
497 CREATE OR REPLACE FUNCTION authority.btag_search_rank( 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.btag_authority_tags($1), $2, $3, $4)
499 $$ LANGUAGE SQL ROWS 10;
501 CREATE OR REPLACE FUNCTION authority.atag_search_rank( 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.atag_authority_tags($1), $2, $3, $4)
503 $$ LANGUAGE SQL ROWS 10;
505 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 $$
506 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
507 $$ LANGUAGE SQL ROWS 10;
509 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 $$
510 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
511 $$ LANGUAGE SQL ROWS 10;
513 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 $$
514 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
515 $$ LANGUAGE SQL ROWS 10;
518 CREATE OR REPLACE FUNCTION authority.axis_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.axis_authority_tags($1), $2, $3, $4)
520 $$ LANGUAGE SQL ROWS 10;
522 CREATE OR REPLACE FUNCTION authority.btag_search_heading( 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.btag_authority_tags($1), $2, $3, $4)
524 $$ LANGUAGE SQL ROWS 10;
526 CREATE OR REPLACE FUNCTION authority.atag_search_heading( 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.atag_authority_tags($1), $2, $3, $4)
528 $$ LANGUAGE SQL ROWS 10;
530 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 $$
531 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
532 $$ LANGUAGE SQL ROWS 10;
534 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 $$
535 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
536 $$ LANGUAGE SQL ROWS 10;
538 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 $$
539 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
540 $$ LANGUAGE SQL ROWS 10;