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,
136 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
137 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
138 CREATE INDEX authority_record_entry_create_date_idx ON authority.record_entry ( create_date );
139 CREATE INDEX authority_record_entry_edit_date_idx ON authority.record_entry ( edit_date );
140 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
141 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();
142 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
143 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
145 CREATE TABLE authority.authority_linking (
146 id BIGSERIAL PRIMARY KEY,
147 source BIGINT REFERENCES authority.record_entry (id) NOT NULL,
148 target BIGINT REFERENCES authority.record_entry (id) NOT NULL,
149 field INT REFERENCES authority.control_set_authority_field (id) NOT NULL
152 CREATE TABLE authority.bib_linking (
153 id BIGSERIAL PRIMARY KEY,
154 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
155 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
157 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
158 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
160 CREATE TABLE authority.record_note (
161 id BIGSERIAL PRIMARY KEY,
162 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
164 creator INT NOT NULL DEFAULT 1,
165 editor INT NOT NULL DEFAULT 1,
166 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
167 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
169 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
170 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
171 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
173 CREATE TABLE authority.rec_descriptor (
174 id BIGSERIAL PRIMARY KEY,
180 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
182 CREATE TABLE authority.full_rec (
183 id BIGSERIAL PRIMARY KEY,
184 record BIGINT NOT NULL,
185 tag CHAR(3) NOT NULL,
190 index_vector tsvector NOT NULL
192 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
193 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
194 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
195 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
196 CREATE TRIGGER authority_full_rec_fti_trigger
197 BEFORE UPDATE OR INSERT ON authority.full_rec
198 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
200 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
201 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
202 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
203 /* But we still need this (boooo) for paging using >, <, etc */
204 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
206 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);
208 -- Intended to be used in a unique index on authority.record_entry like so:
209 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
210 -- ON authority.record_entry (authority.normalize_heading(marc))
211 -- WHERE deleted IS FALSE or deleted = FALSE;
212 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
214 acsaf authority.control_set_authority_field%ROWTYPE;
225 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
227 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
230 SELECT control_set INTO cset
231 FROM authority.control_set_authority_field
232 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
236 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
237 IF thes_code IS NULL THEN
239 ELSIF thes_code = 'z' THEN
240 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
244 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
245 tag_used := acsaf.tag;
246 nfi_used := acsaf.nfi;
249 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
250 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
252 tmp_text := oils_xpath_string('.', sf_node);
253 sf := oils_xpath_string('./@code', sf_node);
255 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
257 tmp_text := SUBSTRING(
262 oils_xpath_string('./@ind'||nfi_used, tag_node),
277 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
278 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
282 EXIT WHEN heading_text <> '';
285 EXIT WHEN heading_text <> '';
288 IF heading_text <> '' THEN
289 IF no_thesaurus IS TRUE THEN
290 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
292 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
295 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
300 $func$ LANGUAGE PLPGSQL IMMUTABLE;
302 CREATE TABLE authority.simple_heading (
303 id BIGSERIAL PRIMARY KEY,
304 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
305 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
307 sort_value TEXT NOT NULL,
308 index_vector tsvector NOT NULL
310 CREATE TRIGGER authority_simple_heading_fti_trigger
311 BEFORE UPDATE OR INSERT ON authority.simple_heading
312 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
314 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
315 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
316 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
318 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
320 res authority.simple_heading%ROWTYPE;
321 acsaf authority.control_set_authority_field%ROWTYPE;
332 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
335 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
338 SELECT control_set INTO cset
339 FROM authority.control_set_authority_field
340 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
344 res.record := auth_id;
346 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
348 res.atag := acsaf.id;
349 tag_used := acsaf.tag;
350 nfi_used := acsaf.nfi;
351 joiner_text := COALESCE(acsaf.joiner, ' ');
353 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
355 heading_text := COALESCE(
356 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml::TEXT, joiner_text),
360 IF nfi_used IS NOT NULL THEN
362 sort_text := SUBSTRING(
367 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
379 sort_text := heading_text;
382 IF heading_text IS NOT NULL AND heading_text <> '' THEN
383 res.value := heading_text;
384 res.sort_value := public.naco_normalize(sort_text);
385 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
395 $func$ LANGUAGE PLPGSQL IMMUTABLE;
397 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
398 SELECT authority.normalize_heading($1, TRUE);
399 $func$ LANGUAGE SQL IMMUTABLE;
401 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
402 SELECT authority.normalize_heading($1, FALSE);
403 $func$ LANGUAGE SQL IMMUTABLE;
405 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
406 Extract the authority heading, thesaurus, and NACO-normalized values
407 from an authority record. The primary purpose is to build a unique
408 index to defend against duplicated authority records from the same
412 -- Adding indexes using oils_xpath_string() for the main entry tags described in
413 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
414 -- the existing index on authority.normalize_heading() helps already with a record in hand
415 CREATE OR REPLACE VIEW authority.tracing_links AS
416 SELECT main.record AS record,
418 main.tag AS main_tag,
419 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
420 substr(link.value,1,1) AS relationship,
421 substr(link.value,2,1) AS use_restriction,
422 substr(link.value,3,1) AS deprecation,
423 substr(link.value,4,1) AS display_restriction,
425 link.tag AS link_tag,
426 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
427 authority.normalize_heading(are.marc) AS normalized_main_value
428 FROM authority.full_rec main
429 JOIN authority.record_entry are ON (main.record = are.id)
430 JOIN authority.control_set_authority_field main_entry
431 ON (main_entry.tag = main.tag
432 AND main_entry.main_entry IS NULL
433 AND main.subfield = 'a' )
434 JOIN authority.control_set_authority_field sub_entry
435 ON (main_entry.id = sub_entry.main_entry)
436 JOIN authority.full_rec link
437 ON (link.record = main.record
438 AND link.tag = sub_entry.tag
439 AND link.subfield = 'w' );
441 -- Function to generate an ephemeral overlay template from an authority record
442 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
445 main_entry authority.control_set_authority_field%ROWTYPE;
446 bib_field authority.control_set_bib_field%ROWTYPE;
447 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
449 replace_data XML[] DEFAULT '{}'::XML[];
450 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
455 IF auth_id IS NULL THEN
459 -- Default to the LoC controll set
460 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
462 -- if none, make a best guess
464 SELECT control_set INTO cset
465 FROM authority.control_set_authority_field
467 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
468 FROM authority.record_entry
474 -- if STILL none, no-op change
478 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
479 XMLELEMENT( name leader, '00881nam a2200193 4500'),
482 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
485 XMLATTRIBUTES('d' AS code),
492 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
493 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
494 auth_i1 = (XPATH('@ind1',auth_field[1]))[1];
495 auth_i2 = (XPATH('@ind2',auth_field[1]))[1];
496 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
497 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
498 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
500 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
502 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
503 replace_data := replace_data || tmp_data;
504 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
511 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
515 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
516 XMLELEMENT( name leader, '00881nam a2200193 4500'),
520 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
523 XMLATTRIBUTES('r' AS code),
524 ARRAY_TO_STRING(replace_rules,',')
529 $f$ STABLE LANGUAGE PLPGSQL;
531 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
532 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
535 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
537 moved_objects INT := 0;
539 bib_rec biblio.record_entry%ROWTYPE;
540 auth_link authority.bib_linking%ROWTYPE;
544 -- Defining our terms:
545 -- "target record" = the record that will survive the merge
546 -- "source record" = the record that is sacrifing its existence and being
547 -- replaced by the target record
549 -- 1. Update all bib records with the ID from target_record in their $0
552 FROM biblio.record_entry bre
553 JOIN authority.bib_linking abl ON abl.bib = bre.id
554 WHERE abl.authority = source_record
557 UPDATE biblio.record_entry
558 SET marc = REGEXP_REPLACE(
560 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
561 E'\\1' || target_record || '<',
564 WHERE id = bib_rec.id;
566 moved_objects := moved_objects + 1;
569 -- 2. Grab the current value of reingest on same MARC flag
570 SELECT enabled INTO ingest_same
571 FROM config.internal_flag
572 WHERE name = 'ingest.reingest.force_on_same_marc'
575 -- 3. Temporarily set reingest on same to TRUE
576 UPDATE config.internal_flag
578 WHERE name = 'ingest.reingest.force_on_same_marc'
581 -- 4. Make a harmless update to target_record to trigger auto-update
582 -- in linked bibliographic records
583 UPDATE authority.record_entry
585 WHERE id = target_record;
587 -- 5. "Delete" source_record
588 DELETE FROM authority.record_entry WHERE id = source_record;
590 -- 6. Set "reingest on same MARC" flag back to initial value
591 UPDATE config.internal_flag
592 SET enabled = ingest_same
593 WHERE name = 'ingest.reingest.force_on_same_marc'
596 RETURN moved_objects;
598 $func$ LANGUAGE plpgsql;
601 -- Support function used to find the pivot for alpha-heading-browse style searching
602 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
604 sort_value_row RECORD;
609 t_term := public.naco_normalize(q);
611 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
612 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
615 FROM authority.simple_heading ash
616 WHERE ash.atag = ANY (a)
617 AND ash.sort_value >= t_term
618 ORDER BY rank DESC, ash.sort_value
621 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
622 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
625 FROM authority.simple_heading ash
626 WHERE ash.atag = ANY (a)
627 AND ash.value >= t_term
628 ORDER BY rank DESC, ash.sort_value
631 IF value_row.rank > sort_value_row.rank THEN
632 RETURN value_row.sort_value;
634 RETURN sort_value_row.sort_value;
639 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 $$
641 pivot_sort_value TEXT;
642 boffset INT DEFAULT 0;
643 aoffset INT DEFAULT 0;
644 blimit INT DEFAULT 0;
645 alimit INT DEFAULT 0;
648 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
651 blimit := pagesize / 2;
654 IF pagesize % 2 <> 0 THEN
655 alimit := alimit + 1;
661 boffset := pagesize / 2;
664 IF pagesize % 2 <> 0 THEN
665 boffset := boffset + 1;
671 -- "bottom" half of the browse results
675 FROM authority.simple_heading ash
676 WHERE ash.atag = ANY (atag_list)
677 AND ash.sort_value < pivot_sort_value
678 ORDER BY ash.sort_value DESC
680 OFFSET ABS(page) * pagesize - boffset
681 ) x ORDER BY row_number DESC;
686 -- "bottom" half of the browse results
688 FROM authority.simple_heading ash
689 WHERE ash.atag = ANY (atag_list)
690 AND ash.sort_value >= pivot_sort_value
691 ORDER BY ash.sort_value
693 OFFSET ABS(page) * pagesize - aoffset;
696 $$ LANGUAGE PLPGSQL ROWS 10;
698 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
699 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
703 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
704 SELECT ARRAY_AGG(y) from (
705 SELECT unnest(ARRAY_CAT(
707 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
709 FROM authority.browse_axis_authority_field_map a
714 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
715 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
719 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
720 SELECT ARRAY_AGG(y) from (
721 SELECT unnest(ARRAY_CAT(
722 ARRAY[a.authority_field],
723 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
725 FROM authority.control_set_bib_field a
730 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
731 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
734 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
735 SELECT ARRAY_AGG(y) from (
736 SELECT unnest(ARRAY_CAT(
738 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
740 FROM authority.control_set_authority_field a
745 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
746 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
747 $$ LANGUAGE SQL ROWS 10;
749 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
750 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
751 $$ LANGUAGE SQL ROWS 10;
753 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
754 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
755 $$ LANGUAGE SQL ROWS 10;
757 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 $$
758 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
759 $$ LANGUAGE SQL ROWS 10;
761 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 $$
762 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
763 $$ LANGUAGE SQL ROWS 10;
765 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 $$
766 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
767 $$ LANGUAGE SQL ROWS 10;
770 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 $$
772 pivot_sort_value TEXT;
775 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
779 -- "bottom" half of the browse results
783 FROM authority.simple_heading ash
784 WHERE ash.atag = ANY (atag_list)
785 AND ash.sort_value < pivot_sort_value
786 ORDER BY ash.sort_value DESC
788 OFFSET (ABS(page) - 1) * pagesize
789 ) x ORDER BY row_number DESC;
794 -- "bottom" half of the browse results
796 FROM authority.simple_heading ash
797 WHERE ash.atag = ANY (atag_list)
798 AND ash.sort_value >= pivot_sort_value
799 ORDER BY ash.sort_value
801 OFFSET ABS(page) * pagesize ;
804 $$ LANGUAGE PLPGSQL ROWS 10;
806 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
807 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
808 $$ LANGUAGE SQL ROWS 10;
810 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
811 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
812 $$ LANGUAGE SQL ROWS 10;
814 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
815 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
816 $$ LANGUAGE SQL ROWS 10;
818 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 $$
819 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
820 $$ LANGUAGE SQL ROWS 10;
822 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 $$
823 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
824 $$ LANGUAGE SQL ROWS 10;
826 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 $$
827 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
828 $$ LANGUAGE SQL ROWS 10;
831 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 $$
833 FROM authority.simple_heading ash,
834 public.naco_normalize($2) t(term),
835 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
836 WHERE ash.atag = ANY ($1)
837 AND ash.index_vector @@ ptsq.term
838 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
839 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
840 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
843 $$ LANGUAGE SQL ROWS 10;
845 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
846 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
847 $$ LANGUAGE SQL ROWS 10;
849 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
850 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
851 $$ LANGUAGE SQL ROWS 10;
853 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
854 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
855 $$ LANGUAGE SQL ROWS 10;
857 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 $$
858 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
859 $$ LANGUAGE SQL ROWS 10;
861 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 $$
862 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
863 $$ LANGUAGE SQL ROWS 10;
865 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 $$
866 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
867 $$ LANGUAGE SQL ROWS 10;
870 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 $$
872 FROM authority.simple_heading ash,
873 public.naco_normalize($2) t(term),
874 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
875 WHERE ash.atag = ANY ($1)
876 AND ash.index_vector @@ ptsq.term
877 ORDER BY ash.sort_value
880 $$ LANGUAGE SQL ROWS 10;
882 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
883 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
884 $$ LANGUAGE SQL ROWS 10;
886 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
887 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
888 $$ LANGUAGE SQL ROWS 10;
890 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
891 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
892 $$ LANGUAGE SQL ROWS 10;
894 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 $$
895 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
896 $$ LANGUAGE SQL ROWS 10;
898 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 $$
899 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
900 $$ LANGUAGE SQL ROWS 10;
902 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 $$
903 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
904 $$ LANGUAGE SQL ROWS 10;