1 -- Evergreen DB patch XXXX.schema.authority-control-sets.sql
3 -- Schema upgrade to add Authority Control Set functionality
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0575', :eg_version);
11 CREATE TABLE authority.control_set (
12 id SERIAL PRIMARY KEY,
13 name TEXT NOT NULL UNIQUE, -- i18n
14 description TEXT -- i18n
17 CREATE TABLE authority.control_set_authority_field (
18 id SERIAL PRIMARY KEY,
19 main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
20 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
22 sf_list TEXT NOT NULL,
23 name TEXT NOT NULL, -- i18n
24 description TEXT -- i18n
27 CREATE TABLE authority.control_set_bib_field (
28 id SERIAL PRIMARY KEY,
29 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
33 CREATE TABLE authority.thesaurus (
34 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
35 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
36 name TEXT NOT NULL UNIQUE, -- i18n
37 description TEXT -- i18n
40 CREATE TABLE authority.browse_axis (
41 code TEXT PRIMARY KEY,
42 name TEXT UNIQUE NOT NULL, -- i18n
43 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
47 CREATE TABLE authority.browse_axis_authority_field_map (
48 id SERIAL PRIMARY KEY,
49 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
53 ALTER TABLE authority.record_entry ADD COLUMN control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
54 ALTER TABLE authority.rec_descriptor DROP COLUMN char_encoding, ADD COLUMN encoding_level TEXT, ADD COLUMN thesaurus TEXT;
56 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
57 CREATE OR REPLACE 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);
59 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
61 acsaf authority.control_set_authority_field%ROWTYPE;
69 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
70 IF thes_code IS NULL THEN
74 SELECT control_set INTO cset FROM authority.thesaurus WHERE code = thes_code;
80 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
81 tag_used := acsaf.tag;
82 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
83 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
84 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
85 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
88 EXIT WHEN heading_text <> '';
91 IF thes_code = 'z' THEN
92 thes_code := oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml);
95 IF heading_text <> '' THEN
96 IF no_thesaurus IS TRUE THEN
97 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
99 heading_text := tag_used || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
102 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
107 $func$ LANGUAGE PLPGSQL IMMUTABLE;
109 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
110 SELECT authority.normalize_heading($1, TRUE);
111 $func$ LANGUAGE SQL IMMUTABLE;
113 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
114 SELECT authority.normalize_heading($1, FALSE);
115 $func$ LANGUAGE SQL IMMUTABLE;
117 CREATE OR REPLACE VIEW authority.tracing_links AS
118 SELECT main.record AS record,
120 main.tag AS main_tag,
121 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
122 substr(link.value,1,1) AS relationship,
123 substr(link.value,2,1) AS use_restriction,
124 substr(link.value,3,1) AS deprecation,
125 substr(link.value,4,1) AS display_restriction,
127 link.tag AS link_tag,
128 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
129 authority.normalize_heading(are.marc) AS normalized_main_value
130 FROM authority.full_rec main
131 JOIN authority.record_entry are ON (main.record = are.id)
132 JOIN authority.control_set_authority_field main_entry
133 ON (main_entry.tag = main.tag
134 AND main_entry.main_entry IS NULL
135 AND main.subfield = 'a' )
136 JOIN authority.control_set_authority_field sub_entry
137 ON (main_entry.id = sub_entry.main_entry)
138 JOIN authority.full_rec link
139 ON (link.record = main.record
140 AND link.tag = sub_entry.tag
141 AND link.subfield = 'w' );
143 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
146 main_entry authority.control_set_authority_field%ROWTYPE;
147 bib_field authority.control_set_bib_field%ROWTYPE;
148 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
149 replace_data XML[] DEFAULT '{}'::XML[];
150 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
153 IF auth_id IS NULL THEN
157 -- Default to the LoC controll set
158 SELECT COALESCE(control_set,1) INTO cset FROM authority.record_entry WHERE id = auth_id;
160 FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
161 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
162 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
163 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
164 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
165 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
173 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
174 XMLELEMENT( name leader, '00881nam a2200193 4500'),
178 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
181 XMLATTRIBUTES('r' AS code),
182 ARRAY_TO_STRING(replace_rules,',')
187 $f$ STABLE LANGUAGE PLPGSQL;
189 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
190 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
193 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
196 use MARC::File::XML (BinaryEncoding => 'UTF-8');
200 MARC::Charset->assume_unicode(1);
202 my $target_xml = shift;
203 my $source_xml = shift;
204 my $field_spec = shift;
205 my $force_add = shift || 0;
207 my $target_r = MARC::Record->new_from_xml( $target_xml );
208 my $source_r = MARC::Record->new_from_xml( $source_xml );
210 return $target_xml unless ($target_r && $source_r);
212 my @field_list = split(',', $field_spec);
215 for my $f (@field_list) {
216 $f =~ s/^\s*//; $f =~ s/\s*$//;
217 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
223 $match =~ s/^\s*//; $match =~ s/\s*$//;
224 $fields{$field} = { sf => [ split('', $sf) ] };
226 my ($msf,$mre) = split('~', $match);
227 if (length($msf) > 0 and length($mre) > 0) {
228 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
229 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
230 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
236 for my $f ( keys %fields) {
237 if ( @{$fields{$f}{sf}} ) {
238 for my $from_field ($source_r->field( $f )) {
239 my @tos = $target_r->field( $f );
241 next if (exists($fields{$f}{match}) and !$force_add);
242 my @new_fields = map { $_->clone } $source_r->field( $f );
243 $target_r->insert_fields_ordered( @new_fields );
245 for my $to_field (@tos) {
246 if (exists($fields{$f}{match})) {
247 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
249 my @new_sf = map { ($_ => $from_field->subfield($_)) } grep { defined($from_field->subfield($_)) } @{$fields{$f}{sf}};
250 $to_field->add_subfields( @new_sf );
255 my @new_fields = map { $_->clone } $source_r->field( $f );
256 $target_r->insert_fields_ordered( @new_fields );
260 $target_xml = $target_r->as_xml_record;
261 $target_xml =~ s/^<\?.+?\?>$//mo;
262 $target_xml =~ s/\n//sgo;
263 $target_xml =~ s/>\s+</></sgo;
267 $_$ LANGUAGE PLPERLU;
270 CREATE INDEX by_heading ON authority.record_entry (authority.simple_normalize_heading(marc)) WHERE deleted IS FALSE or deleted = FALSE;
272 INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, search_field, facet_field) VALUES
273 (28, 'identifier', 'authority_id', oils_i18n_gettext(28, 'Authority Record ID', 'cmf', 'label'), 'marcxml', '//marc:datafield/marc:subfield[@code="0"]', FALSE, TRUE);
275 INSERT INTO config.marc21_rec_type_map (code, type_val, blvl_val) VALUES ('AUT','z',' ');
276 INSERT INTO config.marc21_rec_type_map (code, type_val, blvl_val) VALUES ('MFHD','uvxy',' ');
278 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('ELvl', 'ldr', 'AUT', 17, 1, ' ');
279 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Subj', '008', 'AUT', 11, 1, '|');
280 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('RecStat', 'ldr', 'AUT', 5, 1, 'n');
282 INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
286 FROM config.metabib_field m,
287 config.index_normalizer i
288 WHERE i.func = 'remove_paren_substring'
291 SELECT SETVAL('authority.control_set_id_seq'::TEXT, 100);
292 SELECT SETVAL('authority.control_set_authority_field_id_seq'::TEXT, 1000);
293 SELECT SETVAL('authority.control_set_bib_field_id_seq'::TEXT, 1000);
295 INSERT INTO authority.control_set (id, name, description) VALUES (
297 oils_i18n_gettext('1','LoC','acs','name'),
298 oils_i18n_gettext('1','Library of Congress standard authority record control semantics','acs','description')
301 INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, tag, sf_list, name) VALUES
304 (1, 1, NULL, '100', 'abcdefklmnopqrstvxyz', oils_i18n_gettext('1','Heading -- Personal Name','acsaf','name')),
305 (2, 1, NULL, '110', 'abcdefgklmnoprstvxyz', oils_i18n_gettext('2','Heading -- Corporate Name','acsaf','name')),
306 (3, 1, NULL, '111', 'acdefgklnpqstvxyz', oils_i18n_gettext('3','Heading -- Meeting Name','acsaf','name')),
307 (4, 1, NULL, '130', 'adfgklmnoprstvxyz', oils_i18n_gettext('4','Heading -- Uniform Title','acsaf','name')),
308 (5, 1, NULL, '150', 'abvxyz', oils_i18n_gettext('5','Heading -- Topical Term','acsaf','name')),
309 (6, 1, NULL, '151', 'avxyz', oils_i18n_gettext('6','Heading -- Geographic Name','acsaf','name')),
310 (7, 1, NULL, '155', 'avxyz', oils_i18n_gettext('7','Heading -- Genre/Form Term','acsaf','name')),
311 (8, 1, NULL, '180', 'vxyz', oils_i18n_gettext('8','Heading -- General Subdivision','acsaf','name')),
312 (9, 1, NULL, '181', 'vxyz', oils_i18n_gettext('9','Heading -- Geographic Subdivision','acsaf','name')),
313 (10, 1, NULL, '182', 'vxyz', oils_i18n_gettext('10','Heading -- Chronological Subdivision','acsaf','name')),
314 (11, 1, NULL, '185', 'vxyz', oils_i18n_gettext('11','Heading -- Form Subdivision','acsaf','name')),
315 (12, 1, NULL, '148', 'avxyz', oils_i18n_gettext('12','Heading -- Chronological Term','acsaf','name')),
317 -- See Also From tracings
318 (21, 1, 1, '500', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('21','See Also From Tracing -- Personal Name','acsaf','name')),
319 (22, 1, 2, '510', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('22','See Also From Tracing -- Corporate Name','acsaf','name')),
320 (23, 1, 3, '511', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('23','See Also From Tracing -- Meeting Name','acsaf','name')),
321 (24, 1, 4, '530', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('24','See Also From Tracing -- Uniform Title','acsaf','name')),
322 (25, 1, 5, '550', 'abivwxyz4', oils_i18n_gettext('25','See Also From Tracing -- Topical Term','acsaf','name')),
323 (26, 1, 6, '551', 'aivwxyz4', oils_i18n_gettext('26','See Also From Tracing -- Geographic Name','acsaf','name')),
324 (27, 1, 7, '555', 'aivwxyz4', oils_i18n_gettext('27','See Also From Tracing -- Genre/Form Term','acsaf','name')),
325 (28, 1, 8, '580', 'ivwxyz4', oils_i18n_gettext('28','See Also From Tracing -- General Subdivision','acsaf','name')),
326 (29, 1, 9, '581', 'ivwxyz4', oils_i18n_gettext('29','See Also From Tracing -- Geographic Subdivision','acsaf','name')),
327 (30, 1, 10, '582', 'ivwxyz4', oils_i18n_gettext('30','See Also From Tracing -- Chronological Subdivision','acsaf','name')),
328 (31, 1, 11, '585', 'ivwxyz4', oils_i18n_gettext('31','See Also From Tracing -- Form Subdivision','acsaf','name')),
329 (32, 1, 12, '548', 'aivwxyz4', oils_i18n_gettext('32','See Also From Tracing -- Chronological Term','acsaf','name')),
332 (41, 1, 1, '700', 'abcdefghjklmnopqrstvwxyz25', oils_i18n_gettext('41','Established Heading Linking Entry -- Personal Name','acsaf','name')),
333 (42, 1, 2, '710', 'abcdefghklmnoprstvwxyz25', oils_i18n_gettext('42','Established Heading Linking Entry -- Corporate Name','acsaf','name')),
334 (43, 1, 3, '711', 'acdefghklnpqstvwxyz25', oils_i18n_gettext('43','Established Heading Linking Entry -- Meeting Name','acsaf','name')),
335 (44, 1, 4, '730', 'adfghklmnoprstvwxyz25', oils_i18n_gettext('44','Established Heading Linking Entry -- Uniform Title','acsaf','name')),
336 (45, 1, 5, '750', 'abvwxyz25', oils_i18n_gettext('45','Established Heading Linking Entry -- Topical Term','acsaf','name')),
337 (46, 1, 6, '751', 'avwxyz25', oils_i18n_gettext('46','Established Heading Linking Entry -- Geographic Name','acsaf','name')),
338 (47, 1, 7, '755', 'avwxyz25', oils_i18n_gettext('47','Established Heading Linking Entry -- Genre/Form Term','acsaf','name')),
339 (48, 1, 8, '780', 'vwxyz25', oils_i18n_gettext('48','Subdivision Linking Entry -- General Subdivision','acsaf','name')),
340 (49, 1, 9, '781', 'vwxyz25', oils_i18n_gettext('49','Subdivision Linking Entry -- Geographic Subdivision','acsaf','name')),
341 (50, 1, 10, '782', 'vwxyz25', oils_i18n_gettext('50','Subdivision Linking Entry -- Chronological Subdivision','acsaf','name')),
342 (51, 1, 11, '785', 'vwxyz25', oils_i18n_gettext('51','Subdivision Linking Entry -- Form Subdivision','acsaf','name')),
343 (52, 1, 12, '748', 'avwxyz25', oils_i18n_gettext('52','Established Heading Linking Entry -- Chronological Term','acsaf','name')),
346 (61, 1, 1, '400', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('61','See Also Tracing -- Personal Name','acsaf','name')),
347 (62, 1, 2, '410', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('62','See Also Tracing -- Corporate Name','acsaf','name')),
348 (63, 1, 3, '411', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('63','See Also Tracing -- Meeting Name','acsaf','name')),
349 (64, 1, 4, '430', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('64','See Also Tracing -- Uniform Title','acsaf','name')),
350 (65, 1, 5, '450', 'abivwxyz4', oils_i18n_gettext('65','See Also Tracing -- Topical Term','acsaf','name')),
351 (66, 1, 6, '451', 'aivwxyz4', oils_i18n_gettext('66','See Also Tracing -- Geographic Name','acsaf','name')),
352 (67, 1, 7, '455', 'aivwxyz4', oils_i18n_gettext('67','See Also Tracing -- Genre/Form Term','acsaf','name')),
353 (68, 1, 8, '480', 'ivwxyz4', oils_i18n_gettext('68','See Also Tracing -- General Subdivision','acsaf','name')),
354 (69, 1, 9, '481', 'ivwxyz4', oils_i18n_gettext('69','See Also Tracing -- Geographic Subdivision','acsaf','name')),
355 (70, 1, 10, '482', 'ivwxyz4', oils_i18n_gettext('70','See Also Tracing -- Chronological Subdivision','acsaf','name')),
356 (71, 1, 11, '485', 'ivwxyz4', oils_i18n_gettext('71','See Also Tracing -- Form Subdivision','acsaf','name')),
357 (72, 1, 12, '448', 'aivwxyz4', oils_i18n_gettext('72','See Also Tracing -- Chronological Term','acsaf','name'));
359 INSERT INTO authority.browse_axis (code,name,description,sorter) VALUES
360 ('title','Title','Title axis','titlesort'),
361 ('author','Author','Author axis','titlesort'),
362 ('subject','Subject','Subject axis','titlesort'),
363 ('topic','Topic','Topic Subject axis','titlesort');
365 INSERT INTO authority.browse_axis_authority_field_map (axis,field) VALUES
376 INSERT INTO authority.control_set_bib_field (tag, authority_field)
377 SELECT '100', id FROM authority.control_set_authority_field WHERE tag IN ('100')
379 SELECT '600', id FROM authority.control_set_authority_field WHERE tag IN ('100','180','181','182','185')
381 SELECT '700', id FROM authority.control_set_authority_field WHERE tag IN ('100')
383 SELECT '800', id FROM authority.control_set_authority_field WHERE tag IN ('100')
386 SELECT '110', id FROM authority.control_set_authority_field WHERE tag IN ('110')
388 SELECT '610', id FROM authority.control_set_authority_field WHERE tag IN ('110')
390 SELECT '710', id FROM authority.control_set_authority_field WHERE tag IN ('110')
392 SELECT '810', id FROM authority.control_set_authority_field WHERE tag IN ('110')
395 SELECT '111', id FROM authority.control_set_authority_field WHERE tag IN ('111')
397 SELECT '611', id FROM authority.control_set_authority_field WHERE tag IN ('111')
399 SELECT '711', id FROM authority.control_set_authority_field WHERE tag IN ('111')
401 SELECT '811', id FROM authority.control_set_authority_field WHERE tag IN ('111')
404 SELECT '130', id FROM authority.control_set_authority_field WHERE tag IN ('130')
406 SELECT '240', id FROM authority.control_set_authority_field WHERE tag IN ('130')
408 SELECT '630', id FROM authority.control_set_authority_field WHERE tag IN ('130')
410 SELECT '730', id FROM authority.control_set_authority_field WHERE tag IN ('130')
412 SELECT '830', id FROM authority.control_set_authority_field WHERE tag IN ('130')
415 SELECT '648', id FROM authority.control_set_authority_field WHERE tag IN ('148')
418 SELECT '650', id FROM authority.control_set_authority_field WHERE tag IN ('150','180','181','182','185')
420 SELECT '651', id FROM authority.control_set_authority_field WHERE tag IN ('151','180','181','182','185')
422 SELECT '655', id FROM authority.control_set_authority_field WHERE tag IN ('155','180','181','182','185')
425 INSERT INTO authority.thesaurus (code, name, control_set) VALUES
426 ('a', oils_i18n_gettext('a','Library of Congress Subject Headings','at','name'), 1),
427 ('b', oils_i18n_gettext('b',$$LC subject headings for children's literature$$,'at','name'), 1), -- silly vim '
428 ('c', oils_i18n_gettext('c','Medical Subject Headings','at','name'), 1),
429 ('d', oils_i18n_gettext('d','National Agricultural Library subject authority file','at','name'), 1),
430 ('k', oils_i18n_gettext('k','Canadian Subject Headings','at','name'), 1),
431 ('n', oils_i18n_gettext('n','Not applicable','at','name'), 1),
432 ('r', oils_i18n_gettext('r','Art and Architecture Thesaurus','at','name'), 1),
433 ('s', oils_i18n_gettext('s','Sears List of Subject Headings','at','name'), 1),
434 ('v', oils_i18n_gettext('v','Repertoire de vedettes-matiere','at','name'), 1),
435 ('z', oils_i18n_gettext('z','Other','at','name'), 1),
436 ('|', oils_i18n_gettext('|','No attempt to code','at','name'), 1);
438 CREATE OR REPLACE FUNCTION authority.map_thesaurus_to_control_set () RETURNS TRIGGER AS $func$
440 IF NEW.control_set IS NULL THEN
441 SELECT control_set INTO NEW.control_set
442 FROM authority.thesaurus
443 WHERE vandelay.marc21_extract_fixed_field(NEW.marc,'Subj') = code;
448 $func$ LANGUAGE PLPGSQL;
450 CREATE TRIGGER map_thesaurus_to_control_set BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.map_thesaurus_to_control_set ();
452 CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
454 DELETE FROM authority.rec_descriptor WHERE record = auth_id;
455 INSERT INTO authority.rec_descriptor (record, record_status, encoding_level, thesaurus)
457 vandelay.marc21_extract_fixed_field(marc,'RecStat'),
458 vandelay.marc21_extract_fixed_field(marc,'ELvl'),
459 vandelay.marc21_extract_fixed_field(marc,'Subj')
460 FROM authority.record_entry
464 $func$ LANGUAGE PLPGSQL;
466 CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
469 IF NEW.deleted IS TRUE THEN -- If this authority is deleted
470 DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
471 DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
472 -- Should remove matching $0 from controlled fields at the same time?
473 RETURN NEW; -- and we're done
476 IF TG_OP = 'UPDATE' THEN -- re-ingest?
477 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
479 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
482 -- Propagate these updates to any linked bib records
483 PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id;
486 -- Flatten and insert the afr data
487 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
489 PERFORM authority.reingest_authority_full_rec(NEW.id);
490 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
492 PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
498 $func$ LANGUAGE PLPGSQL;