2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Copyright (C) 2010 Laurentian University
5 * Mike Rylander <miker@esilibrary.com>
6 * Dan Scott <dscott@laurentian.ca>
8 * This program is free software; you can redistribute it and/or
9 * modify it under the terms of the GNU General Public License
10 * as published by the Free Software Foundation; either version 2
11 * of the License, or (at your option) any later version.
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
20 DROP SCHEMA IF EXISTS authority CASCADE;
23 CREATE SCHEMA authority;
25 CREATE TABLE authority.control_set (
26 id SERIAL PRIMARY KEY,
27 name TEXT NOT NULL UNIQUE, -- i18n
28 description TEXT -- i18n
31 CREATE TABLE authority.control_set_authority_field (
32 id SERIAL PRIMARY KEY,
33 main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
34 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
36 nfi CHAR(1), -- non-filing indicator
37 sf_list TEXT NOT NULL,
38 display_sf_list TEXT NOT NULL,
39 name TEXT NOT NULL, -- i18n
40 description TEXT, -- i18n
42 linking_subfield CHAR(1)
45 CREATE TABLE authority.control_set_bib_field (
46 id SERIAL PRIMARY KEY,
47 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 -- Seed data will be generated from class <-> axis mapping
52 CREATE TABLE authority.control_set_bib_field_metabib_field_map (
53 id SERIAL PRIMARY KEY,
54 bib_field INT NOT NULL REFERENCES authority.control_set_bib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
55 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
56 CONSTRAINT a_bf_mf_map_once UNIQUE (bib_field, metabib_field)
59 CREATE VIEW authority.control_set_auth_field_metabib_field_map_main AS
60 SELECT DISTINCT b.authority_field, m.metabib_field
61 FROM authority.control_set_bib_field_metabib_field_map m JOIN authority.control_set_bib_field b ON (b.id = m.bib_field);
62 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_main IS $$metabib fields for main entry auth fields$$;
64 CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs_only AS
65 SELECT DISTINCT a.id AS authority_field, m.metabib_field
66 FROM authority.control_set_authority_field a
67 JOIN authority.control_set_authority_field ame ON (a.main_entry = ame.id)
68 JOIN authority.control_set_bib_field b ON (b.authority_field = ame.id)
69 JOIN authority.control_set_bib_field_metabib_field_map mf ON (mf.bib_field = b.id)
70 JOIN authority.control_set_auth_field_metabib_field_map_main m ON (ame.id = m.authority_field);
71 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs_only IS $$metabib fields for NON-main entry auth fields$$;
73 CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs AS
74 SELECT * FROM authority.control_set_auth_field_metabib_field_map_main
76 SELECT * FROM authority.control_set_auth_field_metabib_field_map_refs_only;
77 COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs IS $$metabib fields for all auth fields$$;
80 -- blind refs only is probably what we want for lookup in bib/auth browse
81 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only AS
83 FROM authority.control_set_auth_field_metabib_field_map_refs_only r
84 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
85 WHERE linking_subfield IS NULL;
86 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$$; -- '
88 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs AS
90 FROM authority.control_set_auth_field_metabib_field_map_refs r
91 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
92 WHERE linking_subfield IS NULL;
93 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$$; -- '
95 CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_main AS
97 FROM authority.control_set_auth_field_metabib_field_map_main r
98 JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
99 WHERE linking_subfield IS NULL;
100 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$$; -- '
102 CREATE TABLE authority.thesaurus (
103 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
104 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
105 name TEXT NOT NULL UNIQUE, -- i18n
106 description TEXT, -- i18n
111 CREATE TRIGGER thes_code_tracking_trigger
112 AFTER UPDATE ON authority.thesaurus
113 FOR EACH ROW EXECUTE PROCEDURE oils_i18n_code_tracking('at');
115 CREATE TABLE authority.browse_axis (
116 code TEXT PRIMARY KEY,
117 name TEXT UNIQUE NOT NULL, -- i18n
118 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
122 CREATE TABLE authority.browse_axis_authority_field_map (
123 id SERIAL PRIMARY KEY,
124 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
125 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
128 CREATE TABLE authority.record_entry (
129 id BIGSERIAL PRIMARY KEY,
130 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
131 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
132 creator INT NOT NULL DEFAULT 1,
133 editor INT NOT NULL DEFAULT 1,
134 active BOOL NOT NULL DEFAULT TRUE,
135 deleted BOOL NOT NULL DEFAULT FALSE,
137 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
139 last_xact_id TEXT NOT NULL,
144 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
145 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
146 CREATE INDEX authority_record_entry_create_date_idx ON authority.record_entry ( create_date );
147 CREATE INDEX authority_record_entry_edit_date_idx ON authority.record_entry ( edit_date );
148 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
149 CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.check_marcxml_well_formed();
150 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
151 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
153 CREATE TABLE authority.authority_linking (
154 id BIGSERIAL PRIMARY KEY,
155 source BIGINT REFERENCES authority.record_entry (id) NOT NULL,
156 target BIGINT REFERENCES authority.record_entry (id) NOT NULL,
157 field INT REFERENCES authority.control_set_authority_field (id) NOT NULL
160 CREATE TABLE authority.bib_linking (
161 id BIGSERIAL PRIMARY KEY,
162 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
163 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
165 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
166 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
168 CREATE TABLE authority.record_note (
169 id BIGSERIAL PRIMARY KEY,
170 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
172 creator INT NOT NULL DEFAULT 1,
173 editor INT NOT NULL DEFAULT 1,
174 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
175 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
177 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
178 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
179 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
181 CREATE TABLE authority.rec_descriptor (
182 id BIGSERIAL PRIMARY KEY,
188 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
190 CREATE TABLE authority.full_rec (
191 id BIGSERIAL PRIMARY KEY,
192 record BIGINT NOT NULL,
193 tag CHAR(3) NOT NULL,
198 index_vector tsvector NOT NULL
200 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
201 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
202 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
203 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
204 CREATE TRIGGER authority_full_rec_fti_trigger
205 BEFORE UPDATE OR INSERT ON authority.full_rec
206 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
208 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
209 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
210 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
211 /* But we still need this (boooo) for paging using >, <, etc */
212 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
214 CREATE RULE protect_authority_rec_delete AS ON DELETE TO authority.record_entry DO INSTEAD (UPDATE authority.record_entry SET deleted = TRUE WHERE OLD.id = authority.record_entry.id; DELETE FROM authority.full_rec WHERE record = OLD.id);
216 CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$
220 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
221 IF thes_code IS NULL THEN
223 ELSIF thes_code = 'z' THEN
224 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
226 SELECT code INTO thes_code FROM authority.thesaurus WHERE short_code = thes_code;
228 thes_code := '|'; -- default
233 $func$ LANGUAGE PLPGSQL STABLE STRICT;
235 -- Intended to be used in a unique index on authority.record_entry like so:
236 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
237 -- ON authority.record_entry (heading)
238 -- WHERE deleted IS FALSE or deleted = FALSE;
239 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
241 acsaf authority.control_set_authority_field%ROWTYPE;
252 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
254 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
257 SELECT control_set INTO cset
258 FROM authority.control_set_authority_field
259 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
264 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
265 tag_used := acsaf.tag;
266 nfi_used := acsaf.nfi;
269 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
270 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
272 tmp_text := oils_xpath_string('.', sf_node);
273 sf := oils_xpath_string('./@code', sf_node);
275 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
277 tmp_text := SUBSTRING(
282 oils_xpath_string('./@ind'||nfi_used, tag_node),
297 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
298 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
302 EXIT WHEN heading_text <> '';
305 EXIT WHEN heading_text <> '';
308 IF heading_text <> '' THEN
309 IF no_thesaurus IS TRUE THEN
310 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
312 thes_code := authority.extract_thesaurus(marcxml);
313 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
316 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
321 $func$ LANGUAGE PLPGSQL STABLE STRICT;
323 CREATE TABLE authority.simple_heading (
324 id BIGSERIAL PRIMARY KEY,
325 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
326 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
328 sort_value TEXT NOT NULL,
329 index_vector tsvector NOT NULL,
332 CREATE TRIGGER authority_simple_heading_fti_trigger
333 BEFORE UPDATE OR INSERT ON authority.simple_heading
334 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
336 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
337 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
338 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
339 CREATE INDEX authority_simple_heading_record_idx ON authority.simple_heading (record);
340 CREATE INDEX authority_simple_heading_thesaurus_idx ON authority.simple_heading (thesaurus);
342 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
344 res authority.simple_heading%ROWTYPE;
345 acsaf authority.control_set_authority_field%ROWTYPE;
356 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
359 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
362 SELECT control_set INTO cset
363 FROM authority.control_set_authority_field
364 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
368 res.record := auth_id;
369 res.thesaurus := authority.extract_thesaurus(marcxml);
371 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
373 res.atag := acsaf.id;
374 tag_used := acsaf.tag;
375 nfi_used := acsaf.nfi;
376 joiner_text := COALESCE(acsaf.joiner, ' ');
378 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
380 heading_text := COALESCE(
381 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
385 IF nfi_used IS NOT NULL THEN
387 sort_text := SUBSTRING(
392 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
404 sort_text := heading_text;
407 IF heading_text IS NOT NULL AND heading_text <> '' THEN
408 res.value := heading_text;
409 res.sort_value := public.naco_normalize(sort_text);
410 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
420 $func$ LANGUAGE PLPGSQL STABLE STRICT;
422 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
423 SELECT authority.normalize_heading($1, TRUE);
424 $func$ LANGUAGE SQL STABLE STRICT;
426 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
427 SELECT authority.normalize_heading($1, FALSE);
428 $func$ LANGUAGE SQL STABLE STRICT;
430 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
431 Extract the authority heading, thesaurus, and NACO-normalized values
432 from an authority record. The primary purpose is to build a unique
433 index to defend against duplicated authority records from the same
437 -- Store these in line with the MARC for easier indexing
438 CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$
440 NEW.heading := authority.normalize_heading( NEW.marc );
441 NEW.simple_heading := authority.simple_normalize_heading( NEW.marc );
444 $f$ LANGUAGE PLPGSQL;
446 CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert();
448 -- Adding indexes using oils_xpath_string() for the main entry tags described in
449 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
450 -- the existing index on authority.normalize_heading() helps already with a record in hand
451 CREATE OR REPLACE VIEW authority.tracing_links AS
452 SELECT main.record AS record,
454 main.tag AS main_tag,
455 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
456 substr(link.value,1,1) AS relationship,
457 substr(link.value,2,1) AS use_restriction,
458 substr(link.value,3,1) AS deprecation,
459 substr(link.value,4,1) AS display_restriction,
461 link.tag AS link_tag,
462 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
463 are.heading AS normalized_main_value
464 FROM authority.full_rec main
465 JOIN authority.record_entry are ON (main.record = are.id)
466 JOIN authority.control_set_authority_field main_entry
467 ON (main_entry.tag = main.tag
468 AND main_entry.main_entry IS NULL
469 AND main.subfield = 'a' )
470 JOIN authority.control_set_authority_field sub_entry
471 ON (main_entry.id = sub_entry.main_entry)
472 JOIN authority.full_rec link
473 ON (link.record = main.record
474 AND link.tag = sub_entry.tag
475 AND link.subfield = 'w' );
477 -- Function to generate an ephemeral overlay template from an authority record
478 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
481 main_entry authority.control_set_authority_field%ROWTYPE;
482 bib_field authority.control_set_bib_field%ROWTYPE;
483 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
485 replace_data XML[] DEFAULT '{}'::XML[];
486 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
491 IF auth_id IS NULL THEN
495 -- Default to the LoC controll set
496 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
498 -- if none, make a best guess
500 SELECT control_set INTO cset
501 FROM authority.control_set_authority_field
503 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
504 FROM authority.record_entry
510 -- if STILL none, no-op change
514 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
515 XMLELEMENT( name leader, '00881nam a2200193 4500'),
518 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
521 XMLATTRIBUTES('d' AS code),
528 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
529 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
530 auth_i1 = (XPATH('@ind1',auth_field[1]))[1];
531 auth_i2 = (XPATH('@ind2',auth_field[1]))[1];
532 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
533 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
534 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
536 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
538 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
539 replace_data := replace_data || tmp_data;
540 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
547 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
551 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
552 XMLELEMENT( name leader, '00881nam a2200193 4500'),
556 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
559 XMLATTRIBUTES('r' AS code),
560 ARRAY_TO_STRING(replace_rules,',')
565 $f$ STABLE LANGUAGE PLPGSQL;
567 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
568 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
571 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
573 moved_objects INT := 0;
575 bib_rec biblio.record_entry%ROWTYPE;
576 auth_link authority.bib_linking%ROWTYPE;
580 -- Defining our terms:
581 -- "target record" = the record that will survive the merge
582 -- "source record" = the record that is sacrifing its existence and being
583 -- replaced by the target record
585 -- 1. Update all bib records with the ID from target_record in their $0
588 FROM biblio.record_entry bre
589 JOIN authority.bib_linking abl ON abl.bib = bre.id
590 WHERE abl.authority = source_record
593 UPDATE biblio.record_entry
594 SET marc = REGEXP_REPLACE(
596 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
597 E'\\1' || target_record || '<',
600 WHERE id = bib_rec.id;
602 moved_objects := moved_objects + 1;
605 -- 2. Grab the current value of reingest on same MARC flag
606 SELECT enabled INTO ingest_same
607 FROM config.internal_flag
608 WHERE name = 'ingest.reingest.force_on_same_marc'
611 -- 3. Temporarily set reingest on same to TRUE
612 UPDATE config.internal_flag
614 WHERE name = 'ingest.reingest.force_on_same_marc'
617 -- 4. Make a harmless update to target_record to trigger auto-update
618 -- in linked bibliographic records
619 UPDATE authority.record_entry
621 WHERE id = target_record;
623 -- 5. "Delete" source_record
624 DELETE FROM authority.record_entry WHERE id = source_record;
626 -- 6. Set "reingest on same MARC" flag back to initial value
627 UPDATE config.internal_flag
628 SET enabled = ingest_same
629 WHERE name = 'ingest.reingest.force_on_same_marc'
632 RETURN moved_objects;
634 $func$ LANGUAGE plpgsql;
637 -- Support function used to find the pivot for alpha-heading-browse style searching
638 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT, thesauruses TEXT DEFAULT '' ) RETURNS TEXT AS $$
640 sort_value_row RECORD;
645 t_term := public.naco_normalize(q);
647 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
648 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
651 FROM authority.simple_heading ash
652 WHERE ash.atag = ANY (a)
653 AND ash.sort_value >= t_term
656 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
658 ORDER BY rank DESC, ash.sort_value
661 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
662 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
665 FROM authority.simple_heading ash
666 WHERE ash.atag = ANY (a)
667 AND ash.value >= t_term
670 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
672 ORDER BY rank DESC, ash.sort_value
675 IF value_row.rank > sort_value_row.rank THEN
676 RETURN value_row.sort_value;
678 RETURN sort_value_row.sort_value;
683 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
685 pivot_sort_value TEXT;
686 boffset INT DEFAULT 0;
687 aoffset INT DEFAULT 0;
688 blimit INT DEFAULT 0;
689 alimit INT DEFAULT 0;
692 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
695 blimit := pagesize / 2;
698 IF pagesize % 2 <> 0 THEN
699 alimit := alimit + 1;
705 boffset := pagesize / 2;
708 IF pagesize % 2 <> 0 THEN
709 boffset := boffset + 1;
715 -- "bottom" half of the browse results
719 FROM authority.simple_heading ash
720 WHERE ash.atag = ANY (atag_list)
723 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
725 AND ash.sort_value < pivot_sort_value
726 ORDER BY ash.sort_value DESC
728 OFFSET ABS(page) * pagesize - boffset
729 ) x ORDER BY row_number DESC;
734 -- "bottom" half of the browse results
736 FROM authority.simple_heading ash
737 WHERE ash.atag = ANY (atag_list)
740 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
742 AND ash.sort_value >= pivot_sort_value
743 ORDER BY ash.sort_value
745 OFFSET ABS(page) * pagesize - aoffset;
748 $$ LANGUAGE PLPGSQL ROWS 10;
750 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
751 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
755 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
756 SELECT ARRAY_AGG(y) from (
757 SELECT unnest(ARRAY_CAT(
759 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
761 FROM authority.browse_axis_authority_field_map a
766 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
767 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
771 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
772 SELECT ARRAY_AGG(y) from (
773 SELECT unnest(ARRAY_CAT(
774 ARRAY[a.authority_field],
775 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
777 FROM authority.control_set_bib_field a
782 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
783 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
786 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
787 SELECT ARRAY_AGG(y) from (
788 SELECT unnest(ARRAY_CAT(
790 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
792 FROM authority.control_set_authority_field a
797 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
798 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4, $5)
799 $$ LANGUAGE SQL ROWS 10;
801 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
802 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4, $5)
803 $$ LANGUAGE SQL ROWS 10;
805 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
806 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4, $5)
807 $$ LANGUAGE SQL ROWS 10;
809 CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
810 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
811 $$ LANGUAGE SQL ROWS 10;
813 CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
814 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
815 $$ LANGUAGE SQL ROWS 10;
817 CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
818 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
819 $$ LANGUAGE SQL ROWS 10;
822 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
824 pivot_sort_value TEXT;
827 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
831 -- "bottom" half of the browse results
835 FROM authority.simple_heading ash
836 WHERE ash.atag = ANY (atag_list)
839 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
841 AND ash.sort_value < pivot_sort_value
842 ORDER BY ash.sort_value DESC
844 OFFSET (ABS(page) - 1) * pagesize
845 ) x ORDER BY row_number DESC;
850 -- "bottom" half of the browse results
852 FROM authority.simple_heading ash
853 WHERE ash.atag = ANY (atag_list)
856 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
858 AND ash.sort_value >= pivot_sort_value
859 ORDER BY ash.sort_value
861 OFFSET ABS(page) * pagesize ;
864 $$ LANGUAGE PLPGSQL ROWS 10;
866 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
867 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4, $5)
868 $$ LANGUAGE SQL ROWS 10;
870 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
871 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4, $5)
872 $$ LANGUAGE SQL ROWS 10;
874 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
875 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4, $5)
876 $$ LANGUAGE SQL ROWS 10;
878 CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
879 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
880 $$ LANGUAGE SQL ROWS 10;
882 CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
883 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
884 $$ LANGUAGE SQL ROWS 10;
886 CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
887 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
888 $$ LANGUAGE SQL ROWS 10;
891 CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
893 FROM authority.simple_heading ash,
894 public.naco_normalize($2) t(term),
895 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
896 WHERE ash.atag = ANY ($1)
897 AND ash.index_vector @@ ptsq.term
900 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
902 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
903 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
904 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
907 $$ LANGUAGE SQL ROWS 10;
909 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
910 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4, $5)
911 $$ LANGUAGE SQL ROWS 10;
913 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
914 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4, $5)
915 $$ LANGUAGE SQL ROWS 10;
917 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
918 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4, $5)
919 $$ LANGUAGE SQL ROWS 10;
921 CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
922 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
923 $$ LANGUAGE SQL ROWS 10;
925 CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
926 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
927 $$ LANGUAGE SQL ROWS 10;
929 CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
930 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
931 $$ LANGUAGE SQL ROWS 10;
934 CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
936 FROM authority.simple_heading ash,
937 public.naco_normalize($2) t(term),
938 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
939 WHERE ash.atag = ANY ($1)
940 AND ash.index_vector @@ ptsq.term
943 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
945 ORDER BY ash.sort_value
948 $$ LANGUAGE SQL ROWS 10;
950 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
951 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4, $5)
952 $$ LANGUAGE SQL ROWS 10;
954 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
955 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4, $5)
956 $$ LANGUAGE SQL ROWS 10;
958 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
959 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4, $5)
960 $$ LANGUAGE SQL ROWS 10;
962 CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
963 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
964 $$ LANGUAGE SQL ROWS 10;
966 CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
967 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
968 $$ LANGUAGE SQL ROWS 10;
970 CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10, thesauruses TEXT DEFAULT '' ) RETURNS SETOF BIGINT AS $$
971 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
972 $$ LANGUAGE SQL ROWS 10;