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 -- Intended to be used in a unique index on authority.record_entry like so:
211 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
212 -- ON authority.record_entry (heading)
213 -- WHERE deleted IS FALSE or deleted = FALSE;
214 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
216 acsaf authority.control_set_authority_field%ROWTYPE;
227 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
229 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
232 SELECT control_set INTO cset
233 FROM authority.control_set_authority_field
234 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
238 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
239 IF thes_code IS NULL THEN
241 ELSIF thes_code = 'z' THEN
242 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
246 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
247 tag_used := acsaf.tag;
248 nfi_used := acsaf.nfi;
251 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
252 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
254 tmp_text := oils_xpath_string('.', sf_node);
255 sf := oils_xpath_string('./@code', sf_node);
257 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
259 tmp_text := SUBSTRING(
264 oils_xpath_string('./@ind'||nfi_used, tag_node),
279 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
280 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
284 EXIT WHEN heading_text <> '';
287 EXIT WHEN heading_text <> '';
290 IF heading_text <> '' THEN
291 IF no_thesaurus IS TRUE THEN
292 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
294 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
297 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
302 $func$ LANGUAGE PLPGSQL STABLE STRICT;
304 CREATE TABLE authority.simple_heading (
305 id BIGSERIAL PRIMARY KEY,
306 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
307 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
309 sort_value TEXT NOT NULL,
310 index_vector tsvector NOT NULL
312 CREATE TRIGGER authority_simple_heading_fti_trigger
313 BEFORE UPDATE OR INSERT ON authority.simple_heading
314 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
316 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
317 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
318 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
320 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
322 res authority.simple_heading%ROWTYPE;
323 acsaf authority.control_set_authority_field%ROWTYPE;
334 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
337 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
340 SELECT control_set INTO cset
341 FROM authority.control_set_authority_field
342 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
346 res.record := auth_id;
348 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
350 res.atag := acsaf.id;
351 tag_used := acsaf.tag;
352 nfi_used := acsaf.nfi;
353 joiner_text := COALESCE(acsaf.joiner, ' ');
355 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
357 heading_text := COALESCE(
358 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
362 IF nfi_used IS NOT NULL THEN
364 sort_text := SUBSTRING(
369 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
381 sort_text := heading_text;
384 IF heading_text IS NOT NULL AND heading_text <> '' THEN
385 res.value := heading_text;
386 res.sort_value := public.naco_normalize(sort_text);
387 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
397 $func$ LANGUAGE PLPGSQL STABLE STRICT;
399 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
400 SELECT authority.normalize_heading($1, TRUE);
401 $func$ LANGUAGE SQL STABLE STRICT;
403 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
404 SELECT authority.normalize_heading($1, FALSE);
405 $func$ LANGUAGE SQL STABLE STRICT;
407 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
408 Extract the authority heading, thesaurus, and NACO-normalized values
409 from an authority record. The primary purpose is to build a unique
410 index to defend against duplicated authority records from the same
414 -- Store these in line with the MARC for easier indexing
415 CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$
417 NEW.heading := authority.normalize_heading( NEW.marc );
418 NEW.simple_heading := authority.simple_normalize_heading( NEW.marc );
421 $f$ LANGUAGE PLPGSQL;
423 CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert();
425 -- Adding indexes using oils_xpath_string() for the main entry tags described in
426 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
427 -- the existing index on authority.normalize_heading() helps already with a record in hand
428 CREATE OR REPLACE VIEW authority.tracing_links AS
429 SELECT main.record AS record,
431 main.tag AS main_tag,
432 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
433 substr(link.value,1,1) AS relationship,
434 substr(link.value,2,1) AS use_restriction,
435 substr(link.value,3,1) AS deprecation,
436 substr(link.value,4,1) AS display_restriction,
438 link.tag AS link_tag,
439 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
440 are.heading AS normalized_main_value
441 FROM authority.full_rec main
442 JOIN authority.record_entry are ON (main.record = are.id)
443 JOIN authority.control_set_authority_field main_entry
444 ON (main_entry.tag = main.tag
445 AND main_entry.main_entry IS NULL
446 AND main.subfield = 'a' )
447 JOIN authority.control_set_authority_field sub_entry
448 ON (main_entry.id = sub_entry.main_entry)
449 JOIN authority.full_rec link
450 ON (link.record = main.record
451 AND link.tag = sub_entry.tag
452 AND link.subfield = 'w' );
454 -- Function to generate an ephemeral overlay template from an authority record
455 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
458 main_entry authority.control_set_authority_field%ROWTYPE;
459 bib_field authority.control_set_bib_field%ROWTYPE;
460 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
462 replace_data XML[] DEFAULT '{}'::XML[];
463 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
468 IF auth_id IS NULL THEN
472 -- Default to the LoC controll set
473 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
475 -- if none, make a best guess
477 SELECT control_set INTO cset
478 FROM authority.control_set_authority_field
480 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
481 FROM authority.record_entry
487 -- if STILL none, no-op change
491 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
492 XMLELEMENT( name leader, '00881nam a2200193 4500'),
495 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
498 XMLATTRIBUTES('d' AS code),
505 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
506 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
507 auth_i1 = (XPATH('@ind1',auth_field[1]))[1];
508 auth_i2 = (XPATH('@ind2',auth_field[1]))[1];
509 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
510 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
511 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
513 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
515 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
516 replace_data := replace_data || tmp_data;
517 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
524 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
528 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
529 XMLELEMENT( name leader, '00881nam a2200193 4500'),
533 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
536 XMLATTRIBUTES('r' AS code),
537 ARRAY_TO_STRING(replace_rules,',')
542 $f$ STABLE LANGUAGE PLPGSQL;
544 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
545 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
548 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
550 moved_objects INT := 0;
552 bib_rec biblio.record_entry%ROWTYPE;
553 auth_link authority.bib_linking%ROWTYPE;
557 -- Defining our terms:
558 -- "target record" = the record that will survive the merge
559 -- "source record" = the record that is sacrifing its existence and being
560 -- replaced by the target record
562 -- 1. Update all bib records with the ID from target_record in their $0
565 FROM biblio.record_entry bre
566 JOIN authority.bib_linking abl ON abl.bib = bre.id
567 WHERE abl.authority = source_record
570 UPDATE biblio.record_entry
571 SET marc = REGEXP_REPLACE(
573 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
574 E'\\1' || target_record || '<',
577 WHERE id = bib_rec.id;
579 moved_objects := moved_objects + 1;
582 -- 2. Grab the current value of reingest on same MARC flag
583 SELECT enabled INTO ingest_same
584 FROM config.internal_flag
585 WHERE name = 'ingest.reingest.force_on_same_marc'
588 -- 3. Temporarily set reingest on same to TRUE
589 UPDATE config.internal_flag
591 WHERE name = 'ingest.reingest.force_on_same_marc'
594 -- 4. Make a harmless update to target_record to trigger auto-update
595 -- in linked bibliographic records
596 UPDATE authority.record_entry
598 WHERE id = target_record;
600 -- 5. "Delete" source_record
601 DELETE FROM authority.record_entry WHERE id = source_record;
603 -- 6. Set "reingest on same MARC" flag back to initial value
604 UPDATE config.internal_flag
605 SET enabled = ingest_same
606 WHERE name = 'ingest.reingest.force_on_same_marc'
609 RETURN moved_objects;
611 $func$ LANGUAGE plpgsql;
614 -- Support function used to find the pivot for alpha-heading-browse style searching
615 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
617 sort_value_row RECORD;
622 t_term := public.naco_normalize(q);
624 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
625 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
628 FROM authority.simple_heading ash
629 WHERE ash.atag = ANY (a)
630 AND ash.sort_value >= t_term
631 ORDER BY rank DESC, ash.sort_value
634 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
635 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
638 FROM authority.simple_heading ash
639 WHERE ash.atag = ANY (a)
640 AND ash.value >= t_term
641 ORDER BY rank DESC, ash.sort_value
644 IF value_row.rank > sort_value_row.rank THEN
645 RETURN value_row.sort_value;
647 RETURN sort_value_row.sort_value;
652 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 $$
654 pivot_sort_value TEXT;
655 boffset INT DEFAULT 0;
656 aoffset INT DEFAULT 0;
657 blimit INT DEFAULT 0;
658 alimit INT DEFAULT 0;
661 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
664 blimit := pagesize / 2;
667 IF pagesize % 2 <> 0 THEN
668 alimit := alimit + 1;
674 boffset := pagesize / 2;
677 IF pagesize % 2 <> 0 THEN
678 boffset := boffset + 1;
684 -- "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 DESC
693 OFFSET ABS(page) * pagesize - boffset
694 ) x ORDER BY row_number DESC;
699 -- "bottom" half of the browse results
701 FROM authority.simple_heading ash
702 WHERE ash.atag = ANY (atag_list)
703 AND ash.sort_value >= pivot_sort_value
704 ORDER BY ash.sort_value
706 OFFSET ABS(page) * pagesize - aoffset;
709 $$ LANGUAGE PLPGSQL ROWS 10;
711 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
712 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
716 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
717 SELECT ARRAY_AGG(y) from (
718 SELECT unnest(ARRAY_CAT(
720 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
722 FROM authority.browse_axis_authority_field_map a
727 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
728 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
732 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
733 SELECT ARRAY_AGG(y) from (
734 SELECT unnest(ARRAY_CAT(
735 ARRAY[a.authority_field],
736 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
738 FROM authority.control_set_bib_field a
743 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
744 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
747 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
748 SELECT ARRAY_AGG(y) from (
749 SELECT unnest(ARRAY_CAT(
751 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
753 FROM authority.control_set_authority_field a
758 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
759 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
760 $$ LANGUAGE SQL ROWS 10;
762 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
763 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
764 $$ LANGUAGE SQL ROWS 10;
766 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
767 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
768 $$ LANGUAGE SQL ROWS 10;
770 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 $$
771 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
772 $$ LANGUAGE SQL ROWS 10;
774 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 $$
775 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
776 $$ LANGUAGE SQL ROWS 10;
778 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 $$
779 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
780 $$ LANGUAGE SQL ROWS 10;
783 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 $$
785 pivot_sort_value TEXT;
788 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
792 -- "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 DESC
801 OFFSET (ABS(page) - 1) * pagesize
802 ) x ORDER BY row_number DESC;
807 -- "bottom" half of the browse results
809 FROM authority.simple_heading ash
810 WHERE ash.atag = ANY (atag_list)
811 AND ash.sort_value >= pivot_sort_value
812 ORDER BY ash.sort_value
814 OFFSET ABS(page) * pagesize ;
817 $$ LANGUAGE PLPGSQL ROWS 10;
819 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
820 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
821 $$ LANGUAGE SQL ROWS 10;
823 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
824 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
825 $$ LANGUAGE SQL ROWS 10;
827 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
828 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
829 $$ LANGUAGE SQL ROWS 10;
831 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 $$
832 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
833 $$ LANGUAGE SQL ROWS 10;
835 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 $$
836 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
837 $$ LANGUAGE SQL ROWS 10;
839 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 $$
840 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
841 $$ LANGUAGE SQL ROWS 10;
844 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 $$
846 FROM authority.simple_heading ash,
847 public.naco_normalize($2) t(term),
848 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
849 WHERE ash.atag = ANY ($1)
850 AND ash.index_vector @@ ptsq.term
851 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
852 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
853 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
856 $$ LANGUAGE SQL ROWS 10;
858 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
859 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
860 $$ LANGUAGE SQL ROWS 10;
862 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
863 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
864 $$ LANGUAGE SQL ROWS 10;
866 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
867 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
868 $$ LANGUAGE SQL ROWS 10;
870 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 $$
871 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
872 $$ LANGUAGE SQL ROWS 10;
874 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 $$
875 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
876 $$ LANGUAGE SQL ROWS 10;
878 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 $$
879 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
880 $$ LANGUAGE SQL ROWS 10;
883 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 $$
885 FROM authority.simple_heading ash,
886 public.naco_normalize($2) t(term),
887 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
888 WHERE ash.atag = ANY ($1)
889 AND ash.index_vector @@ ptsq.term
890 ORDER BY ash.sort_value
893 $$ LANGUAGE SQL ROWS 10;
895 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
896 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
897 $$ LANGUAGE SQL ROWS 10;
899 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
900 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
901 $$ LANGUAGE SQL ROWS 10;
903 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
904 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
905 $$ LANGUAGE SQL ROWS 10;
907 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 $$
908 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
909 $$ LANGUAGE SQL ROWS 10;
911 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 $$
912 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
913 $$ LANGUAGE SQL ROWS 10;
915 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 $$
916 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
917 $$ LANGUAGE SQL ROWS 10;