From 3eb616e56c82a5106e14f5a446f1d875015db7f0 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Fri, 22 Mar 2013 17:11:50 -0400 Subject: [PATCH] Inter-authority linking Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Kathy Lussier Signed-off-by: Jason Stephenson --- Open-ILS/examples/fm_IDL.xml | 1 + Open-ILS/src/sql/Pg/011.schema.authority.sql | 15 +- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 4 + Open-ILS/src/sql/Pg/999.functions.global.sql | 67 +++++++++ ...X.schema.config-metabib-interauthority.sql | 129 ++++++++++++++++++ 5 files changed, 215 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 5053070a68..a439d6d085 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -2119,6 +2119,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index 0d0e308e85..6d4fb508b7 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -36,7 +36,8 @@ CREATE TABLE authority.control_set_authority_field ( nfi CHAR(1), -- non-filing indicator sf_list TEXT NOT NULL, name TEXT NOT NULL, -- i18n - description TEXT -- i18n + description TEXT, -- i18n + linking_subfield CHAR(1) ); CREATE TABLE authority.control_set_bib_field ( @@ -86,6 +87,13 @@ CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON authority.rec CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901(); CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers(); +CREATE TABLE authority.authority_linking ( + id BIGSERIAL PRIMARY KEY, + source BIGINT REFERENCES authority.record_entry (id) NOT NULL, + target BIGINT REFERENCES authority.record_entry (id) NOT NULL, + field INT REFERENCES authority.control_set_authority_field (id) NOT NULL +); + CREATE TABLE authority.bib_linking ( id BIGSERIAL PRIMARY KEY, bib BIGINT NOT NULL REFERENCES biblio.record_entry (id), @@ -270,6 +278,11 @@ BEGIN res.record := auth_id; + -- XXX this SELECT control_set... business below should actually only + -- be a fallback. We should (SELECT control_set FROM authority.record_entry + -- WHERE id = auth_id) when we have an auth_id, and use that if we can get + -- it. + SELECT control_set INTO cset FROM authority.control_set_authority_field WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) ) diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 2ec5f9b7d0..1aa746cd10 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -10392,6 +10392,10 @@ INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, (71, 1, 11, '485', 'ivwxyz4', oils_i18n_gettext('71','See From Tracing -- Form Subdivision','acsaf','name')), (72, 1, 12, '448', 'aivwxyz4', oils_i18n_gettext('72','See From Tracing -- Chronological Term','acsaf','name')); +UPDATE authority.control_set_authority_field + SET linking_subfield = '0' WHERE main_entry IS NOT NULL; + + INSERT INTO authority.browse_axis (code,name,description,sorter) VALUES ('title','Title','Title axis','titlesort'), ('author','Author','Author axis','titlesort'), diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index c3f1533369..404480f018 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1520,6 +1520,61 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; +-- Given an authority record's ID, control set ID (if known), and marc::XML, +-- return all links to other authority records in the form of rows that +-- can be inserted into authority.authority_linking. +CREATE OR REPLACE FUNCTION authority.calculate_authority_linking( + rec_id BIGINT, rec_control_set INT, rec_marc_xml XML +) RETURNS SETOF authority.authority_linking AS $func$ +DECLARE + acsaf authority.control_set_authority_field%ROWTYPE; + link TEXT; + aal authority.authority_linking%ROWTYPE; +BEGIN + IF rec_control_set IS NULL THEN + -- No control_set on record? Guess at one + SELECT control_set INTO rec_control_set + FROM authority.control_set_authority_field + WHERE tag IN ( + SELECT UNNEST( + XPATH('//*[starts-with(@tag,"1")]/@tag',rec_marc::XML)::TEXT[] + ) + ) LIMIT 1; + + IF NOT FOUND THEN + RAISE WARNING 'Could not even guess at control set for authority record %', rec_id; + RETURN; + END IF; + END IF; + + aal.source := rec_id; + + FOR acsaf IN + SELECT * FROM authority.control_set_authority_field + WHERE control_set = rec_control_set + AND linking_subfield IS NOT NULL + AND main_entry IS NOT NULL + LOOP + link := SUBSTRING( + (XPATH('//*[@tag="' || acsaf.tag || '"]/*[@code="' || + acsaf.linking_subfield || '"]/text()', rec_marc_xml))[1]::TEXT, + '\d+$' + ); + + -- Ignore links that are null, malformed, circular, or point to + -- non-existent authority records. + IF link IS NOT NULL AND link::BIGINT <> rec_id THEN + PERFORM * FROM authority.record_entry WHERE id = link::BIGINT; + IF FOUND THEN + aal.target := link::BIGINT; + aal.field := acsaf.id; + RETURN NEXT aal; + END IF; + END IF; + END LOOP; +END; +$func$ LANGUAGE PLPGSQL; + -- AFTER UPDATE OR INSERT trigger for authority.record_entry CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ BEGIN @@ -1529,6 +1584,12 @@ BEGIN DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records DELETE FROM authority.simple_heading WHERE record = NEW.id; -- Should remove matching $0 from controlled fields at the same time? + + -- XXX What do we about the actual linking subfields present in + -- authority records that target this one when this happens? + DELETE FROM authority.authority_linking + WHERE source = NEW.id OR target = NEW.id; + RETURN NEW; -- and we're done END IF; @@ -1543,8 +1604,14 @@ BEGIN PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id; DELETE FROM authority.simple_heading WHERE record = NEW.id; + DELETE FROM authority.authority_linking WHERE source = NEW.id; END IF; + INSERT INTO authority.authority_linking (source, target, field) + SELECT source, target, field FROM authority.calculate_authority_linking( + NEW.id, NEW.control_set, NEW.marc::XML + ); + INSERT INTO authority.simple_heading (record,atag,value,sort_value) SELECT record, atag, value, sort_value FROM authority.simple_heading_set(NEW.marc); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql new file mode 100644 index 0000000000..0424a77272 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config-metabib-interauthority.sql @@ -0,0 +1,129 @@ +BEGIN; + +-- check whether patch can be applied +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +ALTER TABLE authority.control_set_authority_field + ADD COLUMN linking_subfield CHAR(1); + +UPDATE authority.control_set_authority_field + SET linking_subfield = '0' WHERE main_entry IS NOT NULL; + +CREATE TABLE authority.authority_linking ( + id BIGSERIAL PRIMARY KEY, + source BIGINT REFERENCES authority.record_entry (id) NOT NULL, + target BIGINT REFERENCES authority.record_entry (id) NOT NULL, + field INT REFERENCES authority.control_set_authority_field (id) NOT NULL +); + +-- Given an authority record's ID, control set ID (if known), and marc::XML, +-- return all links to other authority records in the form of rows that +-- can be inserted into authority.authority_linking. +CREATE OR REPLACE FUNCTION authority.calculate_authority_linking( + rec_id BIGINT, rec_control_set INT, rec_marc_xml XML +) RETURNS SETOF authority.authority_linking AS $func$ +DECLARE + acsaf authority.control_set_authority_field%ROWTYPE; + link TEXT; + aal authority.authority_linking%ROWTYPE; +BEGIN + IF rec_control_set IS NULL THEN + -- No control_set on record? Guess at one + SELECT control_set INTO rec_control_set + FROM authority.control_set_authority_field + WHERE tag IN ( + SELECT UNNEST( + XPATH('//*[starts-with(@tag,"1")]/@tag',rec_marc::XML)::TEXT[] + ) + ) LIMIT 1; + + IF NOT FOUND THEN + RAISE WARNING 'Could not even guess at control set for authority record %', rec_id; + RETURN; + END IF; + END IF; + + aal.source := rec_id; + + FOR acsaf IN + SELECT * FROM authority.control_set_authority_field + WHERE control_set = rec_control_set + AND linking_subfield IS NOT NULL + AND main_entry IS NOT NULL + LOOP + link := SUBSTRING( + (XPATH('//*[@tag="' || acsaf.tag || '"]/*[@code="' || + acsaf.linking_subfield || '"]/text()', rec_marc_xml))[1]::TEXT, + '\d+$' + ); + + -- Ignore links that are null, malformed, circular, or point to + -- non-existent authority records. + IF link IS NOT NULL AND link::BIGINT <> rec_id THEN + PERFORM * FROM authority.record_entry WHERE id = link::BIGINT; + IF FOUND THEN + aal.target := link::BIGINT; + aal.field := acsaf.id; + RETURN NEXT aal; + END IF; + END IF; + END LOOP; +END; +$func$ LANGUAGE PLPGSQL; + + +-- AFTER UPDATE OR INSERT trigger for authority.record_entry +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 + DELETE FROM authority.simple_heading WHERE record = NEW.id; + -- Should remove matching $0 from controlled fields at the same time? + + -- XXX What do we about the actual linking subfields present in + -- authority records that target this one when this happens? + DELETE FROM authority.authority_linking + WHERE source = NEW.id OR target = NEW.id; + + 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; + + DELETE FROM authority.simple_heading WHERE record = NEW.id; + DELETE FROM authority.authority_linking WHERE source = NEW.id; + END IF; + + INSERT INTO authority.authority_linking (source, target, field) + SELECT source, target, field FROM authority.calculate_authority_linking( + NEW.id, NEW.control_set, NEW.marc::XML + ); + + INSERT INTO authority.simple_heading (record,atag,value,sort_value) + SELECT record, atag, value, sort_value FROM authority.simple_heading_set(NEW.marc); + + -- 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.43.2