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
109 CREATE TABLE authority.browse_axis (
110 code TEXT PRIMARY KEY,
111 name TEXT UNIQUE NOT NULL, -- i18n
112 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
116 CREATE TABLE authority.browse_axis_authority_field_map (
117 id SERIAL PRIMARY KEY,
118 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
119 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
122 CREATE TABLE authority.record_entry (
123 id BIGSERIAL PRIMARY KEY,
124 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
125 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
126 creator INT NOT NULL DEFAULT 1,
127 editor INT NOT NULL DEFAULT 1,
128 active BOOL NOT NULL DEFAULT TRUE,
129 deleted BOOL NOT NULL DEFAULT FALSE,
131 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
133 last_xact_id TEXT NOT NULL,
138 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
139 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
140 CREATE INDEX authority_record_entry_create_date_idx ON authority.record_entry ( create_date );
141 CREATE INDEX authority_record_entry_edit_date_idx ON authority.record_entry ( edit_date );
142 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
143 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();
144 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
145 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
147 CREATE TABLE authority.authority_linking (
148 id BIGSERIAL PRIMARY KEY,
149 source BIGINT REFERENCES authority.record_entry (id) NOT NULL,
150 target BIGINT REFERENCES authority.record_entry (id) NOT NULL,
151 field INT REFERENCES authority.control_set_authority_field (id) NOT NULL
154 CREATE TABLE authority.bib_linking (
155 id BIGSERIAL PRIMARY KEY,
156 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
157 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
159 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
160 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
162 CREATE TABLE authority.record_note (
163 id BIGSERIAL PRIMARY KEY,
164 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
166 creator INT NOT NULL DEFAULT 1,
167 editor INT NOT NULL DEFAULT 1,
168 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
169 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
171 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
172 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
173 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
175 CREATE TABLE authority.rec_descriptor (
176 id BIGSERIAL PRIMARY KEY,
182 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
184 CREATE TABLE authority.full_rec (
185 id BIGSERIAL PRIMARY KEY,
186 record BIGINT NOT NULL,
187 tag CHAR(3) NOT NULL,
192 index_vector tsvector NOT NULL
194 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
195 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
196 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
197 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
198 CREATE TRIGGER authority_full_rec_fti_trigger
199 BEFORE UPDATE OR INSERT ON authority.full_rec
200 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
202 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
203 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
204 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
205 /* But we still need this (boooo) for paging using >, <, etc */
206 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
208 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);
210 CREATE OR REPLACE FUNCTION authority.extract_thesaurus( marcxml TEXT ) RETURNS TEXT AS $func$
214 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
215 IF thes_code IS NULL THEN
217 ELSIF thes_code = 'z' THEN
218 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
222 $func$ LANGUAGE PLPGSQL STABLE STRICT;
224 -- Intended to be used in a unique index on authority.record_entry like so:
225 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
226 -- ON authority.record_entry (heading)
227 -- WHERE deleted IS FALSE or deleted = FALSE;
228 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
230 acsaf authority.control_set_authority_field%ROWTYPE;
241 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
243 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
246 SELECT control_set INTO cset
247 FROM authority.control_set_authority_field
248 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
253 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
254 tag_used := acsaf.tag;
255 nfi_used := acsaf.nfi;
258 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
259 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
261 tmp_text := oils_xpath_string('.', sf_node);
262 sf := oils_xpath_string('./@code', sf_node);
264 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
266 tmp_text := SUBSTRING(
271 oils_xpath_string('./@ind'||nfi_used, tag_node),
286 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
287 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
291 EXIT WHEN heading_text <> '';
294 EXIT WHEN heading_text <> '';
297 IF heading_text <> '' THEN
298 IF no_thesaurus IS TRUE THEN
299 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
301 thes_code := authority.extract_thesaurus(marcxml);
302 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
305 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
310 $func$ LANGUAGE PLPGSQL STABLE STRICT;
312 CREATE TABLE authority.simple_heading (
313 id BIGSERIAL PRIMARY KEY,
314 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
315 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
317 sort_value TEXT NOT NULL,
318 index_vector tsvector NOT NULL,
321 CREATE TRIGGER authority_simple_heading_fti_trigger
322 BEFORE UPDATE OR INSERT ON authority.simple_heading
323 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
325 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
326 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
327 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
328 CREATE INDEX authority_simple_heading_record_idx ON authority.simple_heading (record);
329 CREATE INDEX authority_simple_heading_thesaurus_idx ON authority.simple_heading (thesaurus);
331 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
333 res authority.simple_heading%ROWTYPE;
334 acsaf authority.control_set_authority_field%ROWTYPE;
345 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
348 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
351 SELECT control_set INTO cset
352 FROM authority.control_set_authority_field
353 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
357 res.record := auth_id;
358 res.thesaurus := authority.extract_thesaurus(marcxml);
360 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
362 res.atag := acsaf.id;
363 tag_used := acsaf.tag;
364 nfi_used := acsaf.nfi;
365 joiner_text := COALESCE(acsaf.joiner, ' ');
367 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
369 heading_text := COALESCE(
370 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
374 IF nfi_used IS NOT NULL THEN
376 sort_text := SUBSTRING(
381 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
393 sort_text := heading_text;
396 IF heading_text IS NOT NULL AND heading_text <> '' THEN
397 res.value := heading_text;
398 res.sort_value := public.naco_normalize(sort_text);
399 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
409 $func$ LANGUAGE PLPGSQL STABLE STRICT;
411 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
412 SELECT authority.normalize_heading($1, TRUE);
413 $func$ LANGUAGE SQL STABLE STRICT;
415 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
416 SELECT authority.normalize_heading($1, FALSE);
417 $func$ LANGUAGE SQL STABLE STRICT;
419 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
420 Extract the authority heading, thesaurus, and NACO-normalized values
421 from an authority record. The primary purpose is to build a unique
422 index to defend against duplicated authority records from the same
426 -- Store these in line with the MARC for easier indexing
427 CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$
429 NEW.heading := authority.normalize_heading( NEW.marc );
430 NEW.simple_heading := authority.simple_normalize_heading( NEW.marc );
433 $f$ LANGUAGE PLPGSQL;
435 CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert();
437 -- Adding indexes using oils_xpath_string() for the main entry tags described in
438 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
439 -- the existing index on authority.normalize_heading() helps already with a record in hand
440 CREATE OR REPLACE VIEW authority.tracing_links AS
441 SELECT main.record AS record,
443 main.tag AS main_tag,
444 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
445 substr(link.value,1,1) AS relationship,
446 substr(link.value,2,1) AS use_restriction,
447 substr(link.value,3,1) AS deprecation,
448 substr(link.value,4,1) AS display_restriction,
450 link.tag AS link_tag,
451 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
452 are.heading AS normalized_main_value
453 FROM authority.full_rec main
454 JOIN authority.record_entry are ON (main.record = are.id)
455 JOIN authority.control_set_authority_field main_entry
456 ON (main_entry.tag = main.tag
457 AND main_entry.main_entry IS NULL
458 AND main.subfield = 'a' )
459 JOIN authority.control_set_authority_field sub_entry
460 ON (main_entry.id = sub_entry.main_entry)
461 JOIN authority.full_rec link
462 ON (link.record = main.record
463 AND link.tag = sub_entry.tag
464 AND link.subfield = 'w' );
466 -- Function to generate an ephemeral overlay template from an authority record
467 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
470 main_entry authority.control_set_authority_field%ROWTYPE;
471 bib_field authority.control_set_bib_field%ROWTYPE;
472 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
474 replace_data XML[] DEFAULT '{}'::XML[];
475 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
480 IF auth_id IS NULL THEN
484 -- Default to the LoC controll set
485 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
487 -- if none, make a best guess
489 SELECT control_set INTO cset
490 FROM authority.control_set_authority_field
492 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
493 FROM authority.record_entry
499 -- if STILL none, no-op change
503 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
504 XMLELEMENT( name leader, '00881nam a2200193 4500'),
507 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
510 XMLATTRIBUTES('d' AS code),
517 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
518 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
519 auth_i1 = (XPATH('@ind1',auth_field[1]))[1];
520 auth_i2 = (XPATH('@ind2',auth_field[1]))[1];
521 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
522 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
523 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
525 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
527 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
528 replace_data := replace_data || tmp_data;
529 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
536 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
540 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
541 XMLELEMENT( name leader, '00881nam a2200193 4500'),
545 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
548 XMLATTRIBUTES('r' AS code),
549 ARRAY_TO_STRING(replace_rules,',')
554 $f$ STABLE LANGUAGE PLPGSQL;
556 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
557 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
560 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
562 moved_objects INT := 0;
564 bib_rec biblio.record_entry%ROWTYPE;
565 auth_link authority.bib_linking%ROWTYPE;
569 -- Defining our terms:
570 -- "target record" = the record that will survive the merge
571 -- "source record" = the record that is sacrifing its existence and being
572 -- replaced by the target record
574 -- 1. Update all bib records with the ID from target_record in their $0
577 FROM biblio.record_entry bre
578 JOIN authority.bib_linking abl ON abl.bib = bre.id
579 WHERE abl.authority = source_record
582 UPDATE biblio.record_entry
583 SET marc = REGEXP_REPLACE(
585 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
586 E'\\1' || target_record || '<',
589 WHERE id = bib_rec.id;
591 moved_objects := moved_objects + 1;
594 -- 2. Grab the current value of reingest on same MARC flag
595 SELECT enabled INTO ingest_same
596 FROM config.internal_flag
597 WHERE name = 'ingest.reingest.force_on_same_marc'
600 -- 3. Temporarily set reingest on same to TRUE
601 UPDATE config.internal_flag
603 WHERE name = 'ingest.reingest.force_on_same_marc'
606 -- 4. Make a harmless update to target_record to trigger auto-update
607 -- in linked bibliographic records
608 UPDATE authority.record_entry
610 WHERE id = target_record;
612 -- 5. "Delete" source_record
613 DELETE FROM authority.record_entry WHERE id = source_record;
615 -- 6. Set "reingest on same MARC" flag back to initial value
616 UPDATE config.internal_flag
617 SET enabled = ingest_same
618 WHERE name = 'ingest.reingest.force_on_same_marc'
621 RETURN moved_objects;
623 $func$ LANGUAGE plpgsql;
626 -- Support function used to find the pivot for alpha-heading-browse style searching
627 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT, thesauruses TEXT DEFAULT '' ) RETURNS TEXT AS $$
629 sort_value_row RECORD;
634 t_term := public.naco_normalize(q);
636 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
637 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
640 FROM authority.simple_heading ash
641 WHERE ash.atag = ANY (a)
642 AND ash.sort_value >= t_term
645 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
647 ORDER BY rank DESC, ash.sort_value
650 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
651 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
654 FROM authority.simple_heading ash
655 WHERE ash.atag = ANY (a)
656 AND ash.value >= t_term
659 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
661 ORDER BY rank DESC, ash.sort_value
664 IF value_row.rank > sort_value_row.rank THEN
665 RETURN value_row.sort_value;
667 RETURN sort_value_row.sort_value;
672 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 $$
674 pivot_sort_value TEXT;
675 boffset INT DEFAULT 0;
676 aoffset INT DEFAULT 0;
677 blimit INT DEFAULT 0;
678 alimit INT DEFAULT 0;
681 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
684 blimit := pagesize / 2;
687 IF pagesize % 2 <> 0 THEN
688 alimit := alimit + 1;
694 boffset := pagesize / 2;
697 IF pagesize % 2 <> 0 THEN
698 boffset := boffset + 1;
704 -- "bottom" half of the browse results
708 FROM authority.simple_heading ash
709 WHERE ash.atag = ANY (atag_list)
712 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
714 AND ash.sort_value < pivot_sort_value
715 ORDER BY ash.sort_value DESC
717 OFFSET ABS(page) * pagesize - boffset
718 ) x ORDER BY row_number DESC;
723 -- "bottom" half of the browse results
725 FROM authority.simple_heading ash
726 WHERE ash.atag = ANY (atag_list)
729 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
731 AND ash.sort_value >= pivot_sort_value
732 ORDER BY ash.sort_value
734 OFFSET ABS(page) * pagesize - aoffset;
737 $$ LANGUAGE PLPGSQL ROWS 10;
739 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
740 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
744 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
745 SELECT ARRAY_AGG(y) from (
746 SELECT unnest(ARRAY_CAT(
748 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
750 FROM authority.browse_axis_authority_field_map a
755 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
756 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
760 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
761 SELECT ARRAY_AGG(y) from (
762 SELECT unnest(ARRAY_CAT(
763 ARRAY[a.authority_field],
764 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
766 FROM authority.control_set_bib_field a
771 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
772 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
775 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
776 SELECT ARRAY_AGG(y) from (
777 SELECT unnest(ARRAY_CAT(
779 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
781 FROM authority.control_set_authority_field a
786 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 $$
787 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4, $5)
788 $$ LANGUAGE SQL ROWS 10;
790 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 $$
791 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4, $5)
792 $$ LANGUAGE SQL ROWS 10;
794 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 $$
795 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4, $5)
796 $$ LANGUAGE SQL ROWS 10;
798 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 $$
799 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
800 $$ LANGUAGE SQL ROWS 10;
802 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 $$
803 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
804 $$ LANGUAGE SQL ROWS 10;
806 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 $$
807 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
808 $$ LANGUAGE SQL ROWS 10;
811 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 $$
813 pivot_sort_value TEXT;
816 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q,thesauruses);
820 -- "bottom" half of the browse results
824 FROM authority.simple_heading ash
825 WHERE ash.atag = ANY (atag_list)
828 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
830 AND ash.sort_value < pivot_sort_value
831 ORDER BY ash.sort_value DESC
833 OFFSET (ABS(page) - 1) * pagesize
834 ) x ORDER BY row_number DESC;
839 -- "bottom" half of the browse results
841 FROM authority.simple_heading ash
842 WHERE ash.atag = ANY (atag_list)
845 ELSE ash.thesaurus = ANY(regexp_split_to_array(thesauruses, ','))
847 AND ash.sort_value >= pivot_sort_value
848 ORDER BY ash.sort_value
850 OFFSET ABS(page) * pagesize ;
853 $$ LANGUAGE PLPGSQL ROWS 10;
855 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 $$
856 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4, $5)
857 $$ LANGUAGE SQL ROWS 10;
859 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 $$
860 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4, $5)
861 $$ LANGUAGE SQL ROWS 10;
863 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 $$
864 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4, $5)
865 $$ LANGUAGE SQL ROWS 10;
867 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 $$
868 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
869 $$ LANGUAGE SQL ROWS 10;
871 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 $$
872 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
873 $$ LANGUAGE SQL ROWS 10;
875 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 $$
876 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
877 $$ LANGUAGE SQL ROWS 10;
880 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 $$
882 FROM authority.simple_heading ash,
883 public.naco_normalize($2) t(term),
884 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
885 WHERE ash.atag = ANY ($1)
886 AND ash.index_vector @@ ptsq.term
889 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
891 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
892 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
893 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
896 $$ LANGUAGE SQL ROWS 10;
898 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 $$
899 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4, $5)
900 $$ LANGUAGE SQL ROWS 10;
902 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 $$
903 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4, $5)
904 $$ LANGUAGE SQL ROWS 10;
906 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 $$
907 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4, $5)
908 $$ LANGUAGE SQL ROWS 10;
910 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 $$
911 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
912 $$ LANGUAGE SQL ROWS 10;
914 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 $$
915 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
916 $$ LANGUAGE SQL ROWS 10;
918 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 $$
919 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
920 $$ LANGUAGE SQL ROWS 10;
923 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 $$
925 FROM authority.simple_heading ash,
926 public.naco_normalize($2) t(term),
927 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
928 WHERE ash.atag = ANY ($1)
929 AND ash.index_vector @@ ptsq.term
932 ELSE ash.thesaurus = ANY(regexp_split_to_array($5, ','))
934 ORDER BY ash.sort_value
937 $$ LANGUAGE SQL ROWS 10;
939 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 $$
940 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4, $5)
941 $$ LANGUAGE SQL ROWS 10;
943 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 $$
944 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4, $5)
945 $$ LANGUAGE SQL ROWS 10;
947 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 $$
948 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4, $5)
949 $$ LANGUAGE SQL ROWS 10;
951 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 $$
952 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4, $5)
953 $$ LANGUAGE SQL ROWS 10;
955 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 $$
956 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4, $5)
957 $$ LANGUAGE SQL ROWS 10;
959 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 $$
960 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4, $5)
961 $$ LANGUAGE SQL ROWS 10;