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 sf_list TEXT NOT NULL,
37 name TEXT NOT NULL, -- i18n
38 description TEXT -- i18n
41 CREATE TABLE authority.control_set_bib_field (
42 id SERIAL PRIMARY KEY,
43 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
47 CREATE TABLE authority.thesaurus (
48 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
49 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 name TEXT NOT NULL UNIQUE, -- i18n
51 description TEXT -- i18n
54 CREATE TABLE authority.browse_axis (
55 code TEXT PRIMARY KEY,
56 name TEXT UNIQUE NOT NULL, -- i18n
57 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
61 CREATE TABLE authority.browse_axis_authority_field_map (
62 id SERIAL PRIMARY KEY,
63 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
64 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
67 CREATE TABLE authority.record_entry (
68 id BIGSERIAL PRIMARY KEY,
69 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
70 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
71 creator INT NOT NULL DEFAULT 1,
72 editor INT NOT NULL DEFAULT 1,
73 active BOOL NOT NULL DEFAULT TRUE,
74 deleted BOOL NOT NULL DEFAULT FALSE,
76 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
78 last_xact_id TEXT NOT NULL,
81 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
82 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
83 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
84 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();
85 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
86 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
88 CREATE TABLE authority.bib_linking (
89 id BIGSERIAL PRIMARY KEY,
90 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
91 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
93 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
94 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
96 CREATE TABLE authority.record_note (
97 id BIGSERIAL PRIMARY KEY,
98 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
100 creator INT NOT NULL DEFAULT 1,
101 editor INT NOT NULL DEFAULT 1,
102 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
103 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
105 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
106 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
107 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
109 CREATE TABLE authority.rec_descriptor (
110 id BIGSERIAL PRIMARY KEY,
116 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
118 CREATE TABLE authority.full_rec (
119 id BIGSERIAL PRIMARY KEY,
120 record BIGINT NOT NULL,
121 tag CHAR(3) NOT NULL,
126 index_vector tsvector NOT NULL
128 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
129 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
130 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
131 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
132 CREATE TRIGGER authority_full_rec_fti_trigger
133 BEFORE UPDATE OR INSERT ON authority.full_rec
134 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
136 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
137 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
138 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
139 /* But we still need this (boooo) for paging using >, <, etc */
140 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
142 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);
144 -- Intended to be used in a unique index on authority.record_entry like so:
145 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
146 -- ON authority.record_entry (authority.normalize_heading(marc))
147 -- WHERE deleted IS FALSE or deleted = FALSE;
148 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
150 acsaf authority.control_set_authority_field%ROWTYPE;
158 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
159 IF thes_code IS NULL THEN
163 SELECT control_set INTO cset FROM authority.thesaurus WHERE code = thes_code;
169 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
170 tag_used := acsaf.tag;
171 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
172 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
173 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
174 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
177 EXIT WHEN heading_text <> '';
180 IF thes_code = 'z' THEN
181 thes_code := oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml);
184 IF heading_text <> '' THEN
185 IF no_thesaurus IS TRUE THEN
186 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
188 heading_text := tag_used || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
191 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
196 $func$ LANGUAGE PLPGSQL IMMUTABLE;
198 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
199 SELECT authority.normalize_heading($1, TRUE);
200 $func$ LANGUAGE SQL IMMUTABLE;
202 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
203 SELECT authority.normalize_heading($1, FALSE);
204 $func$ LANGUAGE SQL IMMUTABLE;
206 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
207 Extract the authority heading, thesaurus, and NACO-normalized values
208 from an authority record. The primary purpose is to build a unique
209 index to defend against duplicated authority records from the same
213 -- Adding indexes using oils_xpath_string() for the main entry tags described in
214 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
215 -- the existing index on authority.normalize_heading() helps already with a record in hand
216 CREATE OR REPLACE VIEW authority.tracing_links AS
217 SELECT main.record AS record,
219 main.tag AS main_tag,
220 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
221 substr(link.value,1,1) AS relationship,
222 substr(link.value,2,1) AS use_restriction,
223 substr(link.value,3,1) AS deprecation,
224 substr(link.value,4,1) AS display_restriction,
226 link.tag AS link_tag,
227 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
228 authority.normalize_heading(are.marc) AS normalized_main_value
229 FROM authority.full_rec main
230 JOIN authority.record_entry are ON (main.record = are.id)
231 JOIN authority.control_set_authority_field main_entry
232 ON (main_entry.tag = main.tag
233 AND main_entry.main_entry IS NULL
234 AND main.subfield = 'a' )
235 JOIN authority.control_set_authority_field sub_entry
236 ON (main_entry.id = sub_entry.main_entry)
237 JOIN authority.full_rec link
238 ON (link.record = main.record
239 AND link.tag = sub_entry.tag
240 AND link.subfield = 'w' );
242 -- Function to generate an ephemeral overlay template from an authority record
243 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
246 main_entry authority.control_set_authority_field%ROWTYPE;
247 bib_field authority.control_set_bib_field%ROWTYPE;
248 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
249 replace_data XML[] DEFAULT '{}'::XML[];
250 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
253 IF auth_id IS NULL THEN
257 -- Default to the LoC controll set
258 SELECT COALESCE(control_set,1) INTO cset FROM authority.record_entry WHERE id = auth_id;
260 FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
261 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
262 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
263 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
264 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
265 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
273 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
274 XMLELEMENT( name leader, '00881nam a2200193 4500'),
278 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
281 XMLATTRIBUTES('r' AS code),
282 ARRAY_TO_STRING(replace_rules,',')
287 $f$ STABLE LANGUAGE PLPGSQL;
289 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
290 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
293 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
295 moved_objects INT := 0;
297 bib_rec biblio.record_entry%ROWTYPE;
298 auth_link authority.bib_linking%ROWTYPE;
302 -- Defining our terms:
303 -- "target record" = the record that will survive the merge
304 -- "source record" = the record that is sacrifing its existence and being
305 -- replaced by the target record
307 -- 1. Update all bib records with the ID from target_record in their $0
310 FROM biblio.record_entry bre
311 JOIN authority.bib_linking abl ON abl.bib = bre.id
312 WHERE abl.authority = source_record
315 UPDATE biblio.record_entry
316 SET marc = REGEXP_REPLACE(
318 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
319 E'\\1' || target_record || '<',
322 WHERE id = bib_rec.id;
324 moved_objects := moved_objects + 1;
327 -- 2. Grab the current value of reingest on same MARC flag
328 SELECT enabled INTO ingest_same
329 FROM config.internal_flag
330 WHERE name = 'ingest.reingest.force_on_same_marc'
333 -- 3. Temporarily set reingest on same to TRUE
334 UPDATE config.internal_flag
336 WHERE name = 'ingest.reingest.force_on_same_marc'
339 -- 4. Make a harmless update to target_record to trigger auto-update
340 -- in linked bibliographic records
341 UPDATE authority.record_entry
343 WHERE id = target_record;
345 -- 5. "Delete" source_record
346 DELETE FROM authority.record_entry WHERE id = source_record;
348 -- 6. Set "reingest on same MARC" flag back to initial value
349 UPDATE config.internal_flag
350 SET enabled = ingest_same
351 WHERE name = 'ingest.reingest.force_on_same_marc'
354 RETURN moved_objects;
356 $func$ LANGUAGE plpgsql;