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_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
139 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();
140 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
141 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
143 CREATE TABLE authority.authority_linking (
144 id BIGSERIAL PRIMARY KEY,
145 source BIGINT REFERENCES authority.record_entry (id) NOT NULL,
146 target BIGINT REFERENCES authority.record_entry (id) NOT NULL,
147 field INT REFERENCES authority.control_set_authority_field (id) NOT NULL
150 CREATE TABLE authority.bib_linking (
151 id BIGSERIAL PRIMARY KEY,
152 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
153 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
155 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
156 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
158 CREATE TABLE authority.record_note (
159 id BIGSERIAL PRIMARY KEY,
160 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
162 creator INT NOT NULL DEFAULT 1,
163 editor INT NOT NULL DEFAULT 1,
164 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
165 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
167 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
168 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
169 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
171 CREATE TABLE authority.rec_descriptor (
172 id BIGSERIAL PRIMARY KEY,
178 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
180 CREATE TABLE authority.full_rec (
181 id BIGSERIAL PRIMARY KEY,
182 record BIGINT NOT NULL,
183 tag CHAR(3) NOT NULL,
188 index_vector tsvector NOT NULL
190 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
191 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
192 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
193 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
194 CREATE TRIGGER authority_full_rec_fti_trigger
195 BEFORE UPDATE OR INSERT ON authority.full_rec
196 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
198 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
199 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
200 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
201 /* But we still need this (boooo) for paging using >, <, etc */
202 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
204 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);
206 -- Intended to be used in a unique index on authority.record_entry like so:
207 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
208 -- ON authority.record_entry (authority.normalize_heading(marc))
209 -- WHERE deleted IS FALSE or deleted = FALSE;
210 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
212 acsaf authority.control_set_authority_field%ROWTYPE;
223 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
225 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
228 SELECT control_set INTO cset
229 FROM authority.control_set_authority_field
230 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
234 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
235 IF thes_code IS NULL THEN
237 ELSIF thes_code = 'z' THEN
238 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
242 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
243 tag_used := acsaf.tag;
244 nfi_used := acsaf.nfi;
247 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
248 FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
250 tmp_text := oils_xpath_string('.', sf_node);
251 sf := oils_xpath_string('./@code', sf_node);
253 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
255 tmp_text := SUBSTRING(
260 oils_xpath_string('./@ind'||nfi_used, tag_node),
275 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
276 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
280 EXIT WHEN heading_text <> '';
283 EXIT WHEN heading_text <> '';
286 IF heading_text <> '' THEN
287 IF no_thesaurus IS TRUE THEN
288 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
290 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
293 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
298 $func$ LANGUAGE PLPGSQL IMMUTABLE;
300 CREATE TABLE authority.simple_heading (
301 id BIGSERIAL PRIMARY KEY,
302 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
303 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
305 sort_value TEXT NOT NULL,
306 index_vector tsvector NOT NULL
308 CREATE TRIGGER authority_simple_heading_fti_trigger
309 BEFORE UPDATE OR INSERT ON authority.simple_heading
310 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
312 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
313 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
314 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
316 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
318 res authority.simple_heading%ROWTYPE;
319 acsaf authority.control_set_authority_field%ROWTYPE;
330 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
333 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
336 SELECT control_set INTO cset
337 FROM authority.control_set_authority_field
338 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
342 res.record := auth_id;
344 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
346 res.atag := acsaf.id;
347 tag_used := acsaf.tag;
348 nfi_used := acsaf.nfi;
349 joiner_text := COALESCE(acsaf.joiner, ' ');
351 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
353 heading_text := COALESCE(
354 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml::TEXT, joiner_text),
358 IF nfi_used IS NOT NULL THEN
360 sort_text := SUBSTRING(
365 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
377 sort_text := heading_text;
380 IF heading_text IS NOT NULL AND heading_text <> '' THEN
381 res.value := heading_text;
382 res.sort_value := public.naco_normalize(sort_text);
383 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
393 $func$ LANGUAGE PLPGSQL IMMUTABLE;
395 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
396 SELECT authority.normalize_heading($1, TRUE);
397 $func$ LANGUAGE SQL IMMUTABLE;
399 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
400 SELECT authority.normalize_heading($1, FALSE);
401 $func$ LANGUAGE SQL IMMUTABLE;
403 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
404 Extract the authority heading, thesaurus, and NACO-normalized values
405 from an authority record. The primary purpose is to build a unique
406 index to defend against duplicated authority records from the same
410 -- Adding indexes using oils_xpath_string() for the main entry tags described in
411 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
412 -- the existing index on authority.normalize_heading() helps already with a record in hand
413 CREATE OR REPLACE VIEW authority.tracing_links AS
414 SELECT main.record AS record,
416 main.tag AS main_tag,
417 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
418 substr(link.value,1,1) AS relationship,
419 substr(link.value,2,1) AS use_restriction,
420 substr(link.value,3,1) AS deprecation,
421 substr(link.value,4,1) AS display_restriction,
423 link.tag AS link_tag,
424 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
425 authority.normalize_heading(are.marc) AS normalized_main_value
426 FROM authority.full_rec main
427 JOIN authority.record_entry are ON (main.record = are.id)
428 JOIN authority.control_set_authority_field main_entry
429 ON (main_entry.tag = main.tag
430 AND main_entry.main_entry IS NULL
431 AND main.subfield = 'a' )
432 JOIN authority.control_set_authority_field sub_entry
433 ON (main_entry.id = sub_entry.main_entry)
434 JOIN authority.full_rec link
435 ON (link.record = main.record
436 AND link.tag = sub_entry.tag
437 AND link.subfield = 'w' );
439 -- Function to generate an ephemeral overlay template from an authority record
440 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
443 main_entry authority.control_set_authority_field%ROWTYPE;
444 bib_field authority.control_set_bib_field%ROWTYPE;
445 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
447 replace_data XML[] DEFAULT '{}'::XML[];
448 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
453 IF auth_id IS NULL THEN
457 -- Default to the LoC controll set
458 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
460 -- if none, make a best guess
462 SELECT control_set INTO cset
463 FROM authority.control_set_authority_field
465 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
466 FROM authority.record_entry
472 -- if STILL none, no-op change
476 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
477 XMLELEMENT( name leader, '00881nam a2200193 4500'),
480 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
483 XMLATTRIBUTES('d' AS code),
490 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
491 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
492 auth_i1 = (XPATH('@ind1',auth_field[1]))[1];
493 auth_i2 = (XPATH('@ind2',auth_field[1]))[1];
494 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
495 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
496 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
498 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
500 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
501 replace_data := replace_data || tmp_data;
502 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
509 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
513 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
514 XMLELEMENT( name leader, '00881nam a2200193 4500'),
518 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
521 XMLATTRIBUTES('r' AS code),
522 ARRAY_TO_STRING(replace_rules,',')
527 $f$ STABLE LANGUAGE PLPGSQL;
529 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
530 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
533 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
535 moved_objects INT := 0;
537 bib_rec biblio.record_entry%ROWTYPE;
538 auth_link authority.bib_linking%ROWTYPE;
542 -- Defining our terms:
543 -- "target record" = the record that will survive the merge
544 -- "source record" = the record that is sacrifing its existence and being
545 -- replaced by the target record
547 -- 1. Update all bib records with the ID from target_record in their $0
550 FROM biblio.record_entry bre
551 JOIN authority.bib_linking abl ON abl.bib = bre.id
552 WHERE abl.authority = source_record
555 UPDATE biblio.record_entry
556 SET marc = REGEXP_REPLACE(
558 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
559 E'\\1' || target_record || '<',
562 WHERE id = bib_rec.id;
564 moved_objects := moved_objects + 1;
567 -- 2. Grab the current value of reingest on same MARC flag
568 SELECT enabled INTO ingest_same
569 FROM config.internal_flag
570 WHERE name = 'ingest.reingest.force_on_same_marc'
573 -- 3. Temporarily set reingest on same to TRUE
574 UPDATE config.internal_flag
576 WHERE name = 'ingest.reingest.force_on_same_marc'
579 -- 4. Make a harmless update to target_record to trigger auto-update
580 -- in linked bibliographic records
581 UPDATE authority.record_entry
583 WHERE id = target_record;
585 -- 5. "Delete" source_record
586 DELETE FROM authority.record_entry WHERE id = source_record;
588 -- 6. Set "reingest on same MARC" flag back to initial value
589 UPDATE config.internal_flag
590 SET enabled = ingest_same
591 WHERE name = 'ingest.reingest.force_on_same_marc'
594 RETURN moved_objects;
596 $func$ LANGUAGE plpgsql;
599 -- Support function used to find the pivot for alpha-heading-browse style searching
600 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
602 sort_value_row RECORD;
607 t_term := public.naco_normalize(q);
609 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
610 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
613 FROM authority.simple_heading ash
614 WHERE ash.atag = ANY (a)
615 AND ash.sort_value >= t_term
616 ORDER BY rank DESC, ash.sort_value
619 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
620 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
623 FROM authority.simple_heading ash
624 WHERE ash.atag = ANY (a)
625 AND ash.value >= t_term
626 ORDER BY rank DESC, ash.sort_value
629 IF value_row.rank > sort_value_row.rank THEN
630 RETURN value_row.sort_value;
632 RETURN sort_value_row.sort_value;
637 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 $$
639 pivot_sort_value TEXT;
640 boffset INT DEFAULT 0;
641 aoffset INT DEFAULT 0;
642 blimit INT DEFAULT 0;
643 alimit INT DEFAULT 0;
646 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
649 blimit := pagesize / 2;
652 IF pagesize % 2 <> 0 THEN
653 alimit := alimit + 1;
659 boffset := pagesize / 2;
662 IF pagesize % 2 <> 0 THEN
663 boffset := boffset + 1;
669 -- "bottom" half of the browse results
673 FROM authority.simple_heading ash
674 WHERE ash.atag = ANY (atag_list)
675 AND ash.sort_value < pivot_sort_value
676 ORDER BY ash.sort_value DESC
678 OFFSET ABS(page) * pagesize - boffset
679 ) x ORDER BY row_number DESC;
684 -- "bottom" half of the browse results
686 FROM authority.simple_heading ash
687 WHERE ash.atag = ANY (atag_list)
688 AND ash.sort_value >= pivot_sort_value
689 ORDER BY ash.sort_value
691 OFFSET ABS(page) * pagesize - aoffset;
694 $$ LANGUAGE PLPGSQL ROWS 10;
696 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
697 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
701 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
702 SELECT ARRAY_AGG(y) from (
703 SELECT unnest(ARRAY_CAT(
705 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
707 FROM authority.browse_axis_authority_field_map a
712 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
713 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
717 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
718 SELECT ARRAY_AGG(y) from (
719 SELECT unnest(ARRAY_CAT(
720 ARRAY[a.authority_field],
721 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
723 FROM authority.control_set_bib_field a
728 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
729 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
732 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
733 SELECT ARRAY_AGG(y) from (
734 SELECT unnest(ARRAY_CAT(
736 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
738 FROM authority.control_set_authority_field a
743 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
744 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
745 $$ LANGUAGE SQL ROWS 10;
747 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
748 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
749 $$ LANGUAGE SQL ROWS 10;
751 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
752 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
753 $$ LANGUAGE SQL ROWS 10;
755 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 $$
756 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
757 $$ LANGUAGE SQL ROWS 10;
759 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 $$
760 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
761 $$ LANGUAGE SQL ROWS 10;
763 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 $$
764 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
765 $$ LANGUAGE SQL ROWS 10;
768 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 $$
770 pivot_sort_value TEXT;
773 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
777 -- "bottom" half of the browse results
781 FROM authority.simple_heading ash
782 WHERE ash.atag = ANY (atag_list)
783 AND ash.sort_value < pivot_sort_value
784 ORDER BY ash.sort_value DESC
786 OFFSET (ABS(page) - 1) * pagesize
787 ) x ORDER BY row_number DESC;
792 -- "bottom" half of the browse results
794 FROM authority.simple_heading ash
795 WHERE ash.atag = ANY (atag_list)
796 AND ash.sort_value >= pivot_sort_value
797 ORDER BY ash.sort_value
799 OFFSET ABS(page) * pagesize ;
802 $$ LANGUAGE PLPGSQL ROWS 10;
804 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
805 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
806 $$ LANGUAGE SQL ROWS 10;
808 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
809 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
810 $$ LANGUAGE SQL ROWS 10;
812 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
813 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
814 $$ LANGUAGE SQL ROWS 10;
816 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 $$
817 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
818 $$ LANGUAGE SQL ROWS 10;
820 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 $$
821 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
822 $$ LANGUAGE SQL ROWS 10;
824 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 $$
825 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
826 $$ LANGUAGE SQL ROWS 10;
829 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 $$
831 FROM authority.simple_heading ash,
832 public.naco_normalize($2) t(term),
833 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
834 WHERE ash.atag = ANY ($1)
835 AND ash.index_vector @@ ptsq.term
836 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
837 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
838 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
841 $$ LANGUAGE SQL ROWS 10;
843 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
844 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
845 $$ LANGUAGE SQL ROWS 10;
847 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
848 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
849 $$ LANGUAGE SQL ROWS 10;
851 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
852 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
853 $$ LANGUAGE SQL ROWS 10;
855 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 $$
856 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
857 $$ LANGUAGE SQL ROWS 10;
859 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 $$
860 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
861 $$ LANGUAGE SQL ROWS 10;
863 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 $$
864 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
865 $$ LANGUAGE SQL ROWS 10;
868 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 $$
870 FROM authority.simple_heading ash,
871 public.naco_normalize($2) t(term),
872 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
873 WHERE ash.atag = ANY ($1)
874 AND ash.index_vector @@ ptsq.term
875 ORDER BY ash.sort_value
878 $$ LANGUAGE SQL ROWS 10;
880 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
881 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
882 $$ LANGUAGE SQL ROWS 10;
884 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
885 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
886 $$ LANGUAGE SQL ROWS 10;
888 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
889 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
890 $$ LANGUAGE SQL ROWS 10;
892 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 $$
893 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
894 $$ LANGUAGE SQL ROWS 10;
896 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 $$
897 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
898 $$ LANGUAGE SQL ROWS 10;
900 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 $$
901 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
902 $$ LANGUAGE SQL ROWS 10;