From 6343d40803699e3d287ba8adec979dd99c4687ef Mon Sep 17 00:00:00 2001
From: Mike Rylander <mrylander@gmail.com>
Date: Mon, 11 Jul 2011 11:41:35 -0400
Subject: [PATCH] Unstamped upgrade script for Authority Control Sets
... with additional fixes from Lebbeous Fogle-Weekley
Signed-off-by: Mike Rylander <mrylander@gmail.com>
Signed-off-by: Lebbeous Fogle-Weekley <lebbeous@esilibrary.com>
---
Open-ILS/src/sql/Pg/011.schema.authority.sql | 4 +-
.../upgrade/XXXX.schema.authority-control-sets.sql | 501 +++++++++++++++++++++
2 files changed, 503 insertions(+), 2 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority-control-sets.sql
diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql
index 7c44d7eab7..630fece552 100644
--- a/Open-ILS/src/sql/Pg/011.schema.authority.sql
+++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql
@@ -218,14 +218,14 @@ CREATE OR REPLACE VIEW authority.tracing_links AS
main.id AS main_id,
main.tag AS main_tag,
oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
- authority.normalize_heading(are.marc) AS normalized_main_value,
substr(link.value,1,1) AS relationship,
substr(link.value,2,1) AS use_restriction,
substr(link.value,3,1) AS deprecation,
substr(link.value,4,1) AS display_restriction,
link.id AS link_id,
link.tag AS link_tag,
- oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value
+ oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
+ authority.normalize_heading(are.marc) AS normalized_main_value,
FROM authority.full_rec main
JOIN authority.record_entry are ON (main.record = are.id)
JOIN authority.control_set_authority_field main_entry
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority-control-sets.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority-control-sets.sql
new file mode 100644
index 0000000000..97033018d7
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.authority-control-sets.sql
@@ -0,0 +1,501 @@
+-- Evergreen DB patch XXXX.schema.authority-control-sets.sql
+--
+-- Schema upgrade to add Authority Control Set functionality
+--
+BEGIN;
+
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+CREATE TABLE authority.control_set (
+ id SERIAL PRIMARY KEY,
+ name TEXT NOT NULL UNIQUE, -- i18n
+ description TEXT -- i18n
+);
+
+CREATE TABLE authority.control_set_authority_field (
+ id SERIAL PRIMARY KEY,
+ main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ tag CHAR(3) NOT NULL,
+ sf_list TEXT NOT NULL,
+ name TEXT NOT NULL, -- i18n
+ description TEXT -- i18n
+);
+
+CREATE TABLE authority.control_set_bib_field (
+ id SERIAL PRIMARY KEY,
+ authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ tag CHAR(3) NOT NULL
+);
+
+CREATE TABLE authority.thesaurus (
+ code TEXT PRIMARY KEY, -- MARC21 thesaurus code
+ control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ name TEXT NOT NULL UNIQUE, -- i18n
+ description TEXT -- i18n
+);
+
+CREATE TABLE authority.browse_axis (
+ code TEXT PRIMARY KEY,
+ name TEXT UNIQUE NOT NULL, -- i18n
+ sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ description TEXT
+);
+
+CREATE TABLE authority.browse_axis_authority_field_map (
+ id SERIAL PRIMARY KEY,
+ axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+);
+
+ALTER TABLE authority.record_entry ADD COLUMN control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE authority.rec_descriptor DROP COLUMN char_encoding, ADD COLUMN encoding_level TEXT, ADD COLUMN thesaurus TEXT;
+
+CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
+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);
+
+CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
+DECLARE
+ acsaf authority.control_set_authority_field%ROWTYPE;
+ tag_used TEXT;
+ sf TEXT;
+ thes_code TEXT;
+ cset INT;
+ heading_text TEXT;
+ tmp_text TEXT;
+BEGIN
+ thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
+ IF thes_code IS NULL THEN
+ thes_code := '|';
+ END IF;
+
+ SELECT control_set INTO cset FROM authority.thesaurus WHERE code = thes_code;
+ IF NOT FOUND THEN
+ cset = 1;
+ END IF;
+
+ heading_text := '';
+ FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
+ tag_used := acsaf.tag;
+ FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
+ tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
+ IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
+ heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
+ END IF;
+ END LOOP;
+ EXIT WHEN heading_text <> '';
+ END LOOP;
+
+ IF thes_code = 'z' THEN
+ thes_code := oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml);
+ END IF;
+
+ IF heading_text <> '' THEN
+ IF no_thesaurus IS TRUE THEN
+ heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
+ ELSE
+ heading_text := tag_used || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
+ END IF;
+ ELSE
+ heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
+ END IF;
+
+ RETURN heading_text;
+END;
+$func$ LANGUAGE PLPGSQL IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
+ SELECT authority.normalize_heading($1, TRUE);
+$func$ LANGUAGE SQL IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
+ SELECT authority.normalize_heading($1, FALSE);
+$func$ LANGUAGE SQL IMMUTABLE;
+
+CREATE OR REPLACE VIEW authority.tracing_links AS
+ SELECT main.record AS record,
+ main.id AS main_id,
+ main.tag AS main_tag,
+ oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
+ substr(link.value,1,1) AS relationship,
+ substr(link.value,2,1) AS use_restriction,
+ substr(link.value,3,1) AS deprecation,
+ substr(link.value,4,1) AS display_restriction,
+ link.id AS link_id,
+ link.tag AS link_tag,
+ oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
+ authority.normalize_heading(are.marc) AS normalized_main_value
+ FROM authority.full_rec main
+ JOIN authority.record_entry are ON (main.record = are.id)
+ JOIN authority.control_set_authority_field main_entry
+ ON (main_entry.tag = main.tag
+ AND main_entry.main_entry IS NULL
+ AND main.subfield = 'a' )
+ JOIN authority.control_set_authority_field sub_entry
+ ON (main_entry.id = sub_entry.main_entry)
+ JOIN authority.full_rec link
+ ON (link.record = main.record
+ AND link.tag = sub_entry.tag
+ AND link.subfield = 'w' );
+
+CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
+DECLARE
+ cset INT;
+ main_entry authority.control_set_authority_field%ROWTYPE;
+ bib_field authority.control_set_bib_field%ROWTYPE;
+ auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
+ replace_data XML[] DEFAULT '{}'::XML[];
+ replace_rules TEXT[] DEFAULT '{}'::TEXT[];
+ auth_field XML[];
+BEGIN
+ IF auth_id IS NULL THEN
+ RETURN NULL;
+ END IF;
+
+ -- Default to the LoC controll set
+ SELECT COALESCE(control_set,1) INTO cset FROM authority.record_entry WHERE id = auth_id;
+
+ FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
+ auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
+ IF ARRAY_LENGTH(auth_field,1) > 0 THEN
+ FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
+ replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
+ replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
+ END LOOP;
+ EXIT;
+ END IF;
+ END LOOP;
+
+ RETURN XMLELEMENT(
+ name record,
+ XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
+ XMLELEMENT( name leader, '00881nam a2200193 4500'),
+ replace_data,
+ XMLELEMENT(
+ name datafield,
+ XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
+ XMLELEMENT(
+ name subfield,
+ XMLATTRIBUTES('r' AS code),
+ ARRAY_TO_STRING(replace_rules,',')
+ )
+ )
+ )::TEXT;
+END;
+$f$ STABLE LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
+ SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
+$func$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
+
+ use MARC::Record;
+ use MARC::File::XML (BinaryEncoding => 'UTF-8');
+ use MARC::Charset;
+ use strict;
+
+ MARC::Charset->assume_unicode(1);
+
+ my $target_xml = shift;
+ my $source_xml = shift;
+ my $field_spec = shift;
+ my $force_add = shift || 0;
+
+ my $target_r = MARC::Record->new_from_xml( $target_xml );
+ my $source_r = MARC::Record->new_from_xml( $source_xml );
+
+ return $target_xml unless ($target_r && $source_r);
+
+ my @field_list = split(',', $field_spec);
+
+ my %fields;
+ for my $f (@field_list) {
+ $f =~ s/^\s*//; $f =~ s/\s*$//;
+ if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
+ my $field = $1;
+ $field =~ s/\s+//;
+ my $sf = $2;
+ $sf =~ s/\s+//;
+ my $match = $3;
+ $match =~ s/^\s*//; $match =~ s/\s*$//;
+ $fields{$field} = { sf => [ split('', $sf) ] };
+ if ($match) {
+ my ($msf,$mre) = split('~', $match);
+ if (length($msf) > 0 and length($mre) > 0) {
+ $msf =~ s/^\s*//; $msf =~ s/\s*$//;
+ $mre =~ s/^\s*//; $mre =~ s/\s*$//;
+ $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
+ }
+ }
+ }
+ }
+
+ for my $f ( keys %fields) {
+ if ( @{$fields{$f}{sf}} ) {
+ for my $from_field ($source_r->field( $f )) {
+ my @tos = $target_r->field( $f );
+ if (!@tos) {
+ next if (exists($fields{$f}{match}) and !$force_add);
+ my @new_fields = map { $_->clone } $source_r->field( $f );
+ $target_r->insert_fields_ordered( @new_fields );
+ } else {
+ for my $to_field (@tos) {
+ if (exists($fields{$f}{match})) {
+ next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
+ }
+ my @new_sf = map { ($_ => $from_field->subfield($_)) } grep { defined($from_field->subfield($_)) } @{$fields{$f}{sf}};
+ $to_field->add_subfields( @new_sf );
+ }
+ }
+ }
+ } else {
+ my @new_fields = map { $_->clone } $source_r->field( $f );
+ $target_r->insert_fields_ordered( @new_fields );
+ }
+ }
+
+ $target_xml = $target_r->as_xml_record;
+ $target_xml =~ s/^<\?.+?\?>$//mo;
+ $target_xml =~ s/\n//sgo;
+ $target_xml =~ s/>\s+</></sgo;
+
+ return $target_xml;
+
+$_$ LANGUAGE PLPERLU;
+
+
+CREATE INDEX by_heading ON authority.record_entry (authority.simple_normalize_heading(marc)) WHERE deleted IS FALSE or deleted = FALSE;
+
+INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, search_field, facet_field) VALUES
+ (28, 'identifier', 'authority_id', oils_i18n_gettext(28, 'Authority Record ID', 'cmf', 'label'), 'marcxml', '//marc:datafield/marc:subfield[@code="0"]', FALSE, TRUE);
+
+INSERT INTO config.marc21_rec_type_map (code, type_val, blvl_val) VALUES ('AUT','z',' ');
+INSERT INTO config.marc21_rec_type_map (code, type_val, blvl_val) VALUES ('MFHD','uvxy',' ');
+
+INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('ELvl', 'ldr', 'AUT', 17, 1, ' ');
+INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Subj', '008', 'AUT', 11, 1, '|');
+INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('RecStat', 'ldr', 'AUT', 5, 1, 'n');
+
+INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
+ SELECT m.id,
+ i.id,
+ -1
+ FROM config.metabib_field m,
+ config.index_normalizer i
+ WHERE i.func = 'remove_paren_substring'
+ AND m.id IN (28);
+
+SELECT SETVAL('authority.control_set_id_seq'::TEXT, 100);
+SELECT SETVAL('authority.control_set_authority_field_id_seq'::TEXT, 1000);
+SELECT SETVAL('authority.control_set_bib_field_id_seq'::TEXT, 1000);
+
+INSERT INTO authority.control_set (id, name, description) VALUES (
+ 1,
+ oils_i18n_gettext('1','LoC','acs','name'),
+ oils_i18n_gettext('1','Library of Congress standard authority record control semantics','acs','description')
+);
+
+INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, tag, sf_list, name) VALUES
+
+-- Main entries
+ (1, 1, NULL, '100', 'abcdefklmnopqrstvxyz', oils_i18n_gettext('1','Heading -- Personal Name','acsaf','name')),
+ (2, 1, NULL, '110', 'abcdefgklmnoprstvxyz', oils_i18n_gettext('2','Heading -- Corporate Name','acsaf','name')),
+ (3, 1, NULL, '111', 'acdefgklnpqstvxyz', oils_i18n_gettext('3','Heading -- Meeting Name','acsaf','name')),
+ (4, 1, NULL, '130', 'adfgklmnoprstvxyz', oils_i18n_gettext('4','Heading -- Uniform Title','acsaf','name')),
+ (5, 1, NULL, '150', 'abvxyz', oils_i18n_gettext('5','Heading -- Topical Term','acsaf','name')),
+ (6, 1, NULL, '151', 'avxyz', oils_i18n_gettext('6','Heading -- Geographic Name','acsaf','name')),
+ (7, 1, NULL, '155', 'avxyz', oils_i18n_gettext('7','Heading -- Genre/Form Term','acsaf','name')),
+ (8, 1, NULL, '180', 'vxyz', oils_i18n_gettext('8','Heading -- General Subdivision','acsaf','name')),
+ (9, 1, NULL, '181', 'vxyz', oils_i18n_gettext('9','Heading -- Geographic Subdivision','acsaf','name')),
+ (10, 1, NULL, '182', 'vxyz', oils_i18n_gettext('10','Heading -- Chronological Subdivision','acsaf','name')),
+ (11, 1, NULL, '185', 'vxyz', oils_i18n_gettext('11','Heading -- Form Subdivision','acsaf','name')),
+ (12, 1, NULL, '148', 'avxyz', oils_i18n_gettext('12','Heading -- Chronological Term','acsaf','name')),
+
+-- See Also From tracings
+ (21, 1, 1, '500', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('21','See Also From Tracing -- Personal Name','acsaf','name')),
+ (22, 1, 2, '510', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('22','See Also From Tracing -- Corporate Name','acsaf','name')),
+ (23, 1, 3, '511', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('23','See Also From Tracing -- Meeting Name','acsaf','name')),
+ (24, 1, 4, '530', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('24','See Also From Tracing -- Uniform Title','acsaf','name')),
+ (25, 1, 5, '550', 'abivwxyz4', oils_i18n_gettext('25','See Also From Tracing -- Topical Term','acsaf','name')),
+ (26, 1, 6, '551', 'aivwxyz4', oils_i18n_gettext('26','See Also From Tracing -- Geographic Name','acsaf','name')),
+ (27, 1, 7, '555', 'aivwxyz4', oils_i18n_gettext('27','See Also From Tracing -- Genre/Form Term','acsaf','name')),
+ (28, 1, 8, '580', 'ivwxyz4', oils_i18n_gettext('28','See Also From Tracing -- General Subdivision','acsaf','name')),
+ (29, 1, 9, '581', 'ivwxyz4', oils_i18n_gettext('29','See Also From Tracing -- Geographic Subdivision','acsaf','name')),
+ (30, 1, 10, '582', 'ivwxyz4', oils_i18n_gettext('30','See Also From Tracing -- Chronological Subdivision','acsaf','name')),
+ (31, 1, 11, '585', 'ivwxyz4', oils_i18n_gettext('31','See Also From Tracing -- Form Subdivision','acsaf','name')),
+ (32, 1, 12, '548', 'aivwxyz4', oils_i18n_gettext('32','See Also From Tracing -- Chronological Term','acsaf','name')),
+
+-- Linking entries
+ (41, 1, 1, '700', 'abcdefghjklmnopqrstvwxyz25', oils_i18n_gettext('41','Established Heading Linking Entry -- Personal Name','acsaf','name')),
+ (42, 1, 2, '710', 'abcdefghklmnoprstvwxyz25', oils_i18n_gettext('42','Established Heading Linking Entry -- Corporate Name','acsaf','name')),
+ (43, 1, 3, '711', 'acdefghklnpqstvwxyz25', oils_i18n_gettext('43','Established Heading Linking Entry -- Meeting Name','acsaf','name')),
+ (44, 1, 4, '730', 'adfghklmnoprstvwxyz25', oils_i18n_gettext('44','Established Heading Linking Entry -- Uniform Title','acsaf','name')),
+ (45, 1, 5, '750', 'abvwxyz25', oils_i18n_gettext('45','Established Heading Linking Entry -- Topical Term','acsaf','name')),
+ (46, 1, 6, '751', 'avwxyz25', oils_i18n_gettext('46','Established Heading Linking Entry -- Geographic Name','acsaf','name')),
+ (47, 1, 7, '755', 'avwxyz25', oils_i18n_gettext('47','Established Heading Linking Entry -- Genre/Form Term','acsaf','name')),
+ (48, 1, 8, '780', 'vwxyz25', oils_i18n_gettext('48','Subdivision Linking Entry -- General Subdivision','acsaf','name')),
+ (49, 1, 9, '781', 'vwxyz25', oils_i18n_gettext('49','Subdivision Linking Entry -- Geographic Subdivision','acsaf','name')),
+ (50, 1, 10, '782', 'vwxyz25', oils_i18n_gettext('50','Subdivision Linking Entry -- Chronological Subdivision','acsaf','name')),
+ (51, 1, 11, '785', 'vwxyz25', oils_i18n_gettext('51','Subdivision Linking Entry -- Form Subdivision','acsaf','name')),
+ (52, 1, 12, '748', 'avwxyz25', oils_i18n_gettext('52','Established Heading Linking Entry -- Chronological Term','acsaf','name')),
+
+-- See From tracings
+ (61, 1, 1, '400', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('61','See Also Tracing -- Personal Name','acsaf','name')),
+ (62, 1, 2, '410', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('62','See Also Tracing -- Corporate Name','acsaf','name')),
+ (63, 1, 3, '411', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('63','See Also Tracing -- Meeting Name','acsaf','name')),
+ (64, 1, 4, '430', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('64','See Also Tracing -- Uniform Title','acsaf','name')),
+ (65, 1, 5, '450', 'abivwxyz4', oils_i18n_gettext('65','See Also Tracing -- Topical Term','acsaf','name')),
+ (66, 1, 6, '451', 'aivwxyz4', oils_i18n_gettext('66','See Also Tracing -- Geographic Name','acsaf','name')),
+ (67, 1, 7, '455', 'aivwxyz4', oils_i18n_gettext('67','See Also Tracing -- Genre/Form Term','acsaf','name')),
+ (68, 1, 8, '480', 'ivwxyz4', oils_i18n_gettext('68','See Also Tracing -- General Subdivision','acsaf','name')),
+ (69, 1, 9, '481', 'ivwxyz4', oils_i18n_gettext('69','See Also Tracing -- Geographic Subdivision','acsaf','name')),
+ (70, 1, 10, '482', 'ivwxyz4', oils_i18n_gettext('70','See Also Tracing -- Chronological Subdivision','acsaf','name')),
+ (71, 1, 11, '485', 'ivwxyz4', oils_i18n_gettext('71','See Also Tracing -- Form Subdivision','acsaf','name')),
+ (72, 1, 12, '448', 'aivwxyz4', oils_i18n_gettext('72','See Also Tracing -- Chronological Term','acsaf','name'));
+
+INSERT INTO authority.browse_axis (code,name,description,sorter) VALUES
+ ('title','Title','Title axis','titlesort'),
+ ('author','Author','Author axis','titlesort'),
+ ('subject','Subject','Subject axis','titlesort'),
+ ('topic','Topic','Topic Subject axis','titlesort');
+
+INSERT INTO authority.browse_axis_authority_field_map (axis,field) VALUES
+ ('author', 1 ),
+ ('author', 2 ),
+ ('author', 3 ),
+ ('title', 4 ),
+ ('topic', 5 ),
+ ('subject', 5 ),
+ ('subject', 6 ),
+ ('subject', 7 ),
+ ('subject', 12);
+
+INSERT INTO authority.control_set_bib_field (tag, authority_field)
+ SELECT '100', id FROM authority.control_set_authority_field WHERE tag IN ('100')
+ UNION
+ SELECT '600', id FROM authority.control_set_authority_field WHERE tag IN ('100','180','181','182','185')
+ UNION
+ SELECT '700', id FROM authority.control_set_authority_field WHERE tag IN ('100')
+ UNION
+ SELECT '800', id FROM authority.control_set_authority_field WHERE tag IN ('100')
+ UNION
+
+ SELECT '110', id FROM authority.control_set_authority_field WHERE tag IN ('110')
+ UNION
+ SELECT '610', id FROM authority.control_set_authority_field WHERE tag IN ('110')
+ UNION
+ SELECT '710', id FROM authority.control_set_authority_field WHERE tag IN ('110')
+ UNION
+ SELECT '810', id FROM authority.control_set_authority_field WHERE tag IN ('110')
+ UNION
+
+ SELECT '111', id FROM authority.control_set_authority_field WHERE tag IN ('111')
+ UNION
+ SELECT '611', id FROM authority.control_set_authority_field WHERE tag IN ('111')
+ UNION
+ SELECT '711', id FROM authority.control_set_authority_field WHERE tag IN ('111')
+ UNION
+ SELECT '811', id FROM authority.control_set_authority_field WHERE tag IN ('111')
+ UNION
+
+ SELECT '130', id FROM authority.control_set_authority_field WHERE tag IN ('130')
+ UNION
+ SELECT '240', id FROM authority.control_set_authority_field WHERE tag IN ('130')
+ UNION
+ SELECT '630', id FROM authority.control_set_authority_field WHERE tag IN ('130')
+ UNION
+ SELECT '730', id FROM authority.control_set_authority_field WHERE tag IN ('130')
+ UNION
+ SELECT '830', id FROM authority.control_set_authority_field WHERE tag IN ('130')
+ UNION
+
+ SELECT '648', id FROM authority.control_set_authority_field WHERE tag IN ('148')
+ UNION
+
+ SELECT '650', id FROM authority.control_set_authority_field WHERE tag IN ('150','180','181','182','185')
+ UNION
+ SELECT '651', id FROM authority.control_set_authority_field WHERE tag IN ('151','180','181','182','185')
+ UNION
+ SELECT '655', id FROM authority.control_set_authority_field WHERE tag IN ('155','180','181','182','185')
+;
+
+INSERT INTO authority.thesaurus (code, name, control_set) VALUES
+ ('a', oils_i18n_gettext('a','Library of Congress Subject Headings','at','name'), 1),
+ ('b', oils_i18n_gettext('b',$$LC subject headings for children's literature$$,'at','name'), 1), -- silly vim '
+ ('c', oils_i18n_gettext('c','Medical Subject Headings','at','name'), 1),
+ ('d', oils_i18n_gettext('d','National Agricultural Library subject authority file','at','name'), 1),
+ ('k', oils_i18n_gettext('k','Canadian Subject Headings','at','name'), 1),
+ ('n', oils_i18n_gettext('n','Not applicable','at','name'), 1),
+ ('r', oils_i18n_gettext('r','Art and Architecture Thesaurus','at','name'), 1),
+ ('s', oils_i18n_gettext('s','Sears List of Subject Headings','at','name'), 1),
+ ('v', oils_i18n_gettext('v','Repertoire de vedettes-matiere','at','name'), 1),
+ ('z', oils_i18n_gettext('z','Other','at','name'), 1),
+ ('|', oils_i18n_gettext('|','No attempt to code','at','name'), 1);
+
+CREATE OR REPLACE FUNCTION authority.map_thesaurus_to_control_set () RETURNS TRIGGER AS $func$
+BEGIN
+ IF NEW.control_set IS NULL THEN
+ SELECT control_set INTO NEW.control_set
+ FROM authority.thesaurus
+ WHERE vandelay.marc21_extract_fixed_field(NEW.marc,'Subj') = code;
+ END IF;
+
+ RETURN NEW;
+END;
+$func$ LANGUAGE PLPGSQL;
+
+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 ();
+
+CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
+BEGIN
+ DELETE FROM authority.rec_descriptor WHERE record = auth_id;
+ INSERT INTO authority.rec_descriptor (record, record_status, encoding_level, thesaurus)
+ SELECT auth_id,
+ vandelay.marc21_extract_fixed_field(marc,'RecStat'),
+ vandelay.marc21_extract_fixed_field(marc,'ELvl'),
+ vandelay.marc21_extract_fixed_field(marc,'Subj')
+ FROM authority.record_entry
+ WHERE id = auth_id;
+ RETURN;
+ END;
+ $func$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
+BEGIN
+
+ IF NEW.deleted IS TRUE THEN -- If this authority is deleted
+ DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
+ DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
+ -- Should remove matching $0 from controlled fields at the same time?
+ RETURN NEW; -- and we're done
+ END IF;
+
+ IF TG_OP = 'UPDATE' THEN -- re-ingest?
+ PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
+
+ IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
+ RETURN NEW;
+ END IF;
+ -- Propagate these updates to any linked bib records
+ PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id;
+ END IF;
+
+ -- Flatten and insert the afr data
+ PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
+ IF NOT FOUND THEN
+ PERFORM authority.reingest_authority_full_rec(NEW.id);
+ PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
+ IF NOT FOUND THEN
+ PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
+ END IF;
+ END IF;
+
+ RETURN NEW;
+END;
+$func$ LANGUAGE PLPGSQL;
+
+COMMIT;
+
--
2.11.0