From a1612cfd664fc9af03c6e7487516b5f61ea5b644 Mon Sep 17 00:00:00 2001 From: dbs Date: Sat, 17 Jul 2010 20:51:15 +0000 Subject: [PATCH] Switch to ingesting authority records automatically via database triggers Taken largely from the existing approach for ingesting biblio records, there is some duplication here that we could genericize. But for now, we'll go with the working-but-separate approach to avoid introducing badness into the relatively stable biblio ingest pipeline. Also, move some of the existing authority-related functions out of the metabib schema file and into 999.functions.global.sql. Finally, the direct_ingest.pl script is no longer necessary for ingest of authority records, either, so die in that case as well. git-svn-id: svn://svn.open-ils.org/ILS/trunk@16970 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/extras/import/direct_ingest.pl | 4 +- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 38 ----- Open-ILS/src/sql/Pg/999.functions.global.sql | 124 +++++++++++++++++ .../0342.schema.authority_ingest_triggers.sql | 130 ++++++++++++++++++ 5 files changed, 256 insertions(+), 42 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0342.schema.authority_ingest_triggers.sql diff --git a/Open-ILS/src/extras/import/direct_ingest.pl b/Open-ILS/src/extras/import/direct_ingest.pl index bbec8bf817..efda18351c 100755 --- a/Open-ILS/src/extras/import/direct_ingest.pl +++ b/Open-ILS/src/extras/import/direct_ingest.pl @@ -50,9 +50,7 @@ Fieldmapper->import(IDL => OpenSRF::Utils::SettingsClient->new->config_value("ID OpenILS::Application::Ingest->use; -if (!$auth) { - die "We have no more use for biblio ingest ... just insert the bre objects and you're done!\n"; -} +die "We have no more use for authority or biblio ingest ... just insert the are or bre objects and you're done!\n"; my $meth = 'open-ils.ingest.full.biblio.object.readonly'; $meth = 'open-ils.ingest.full.authority.object.readonly' if ($auth); diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 7c5d592868..b98a852da8 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0341'); -- gmc +INSERT INTO config.upgrade_log (version) VALUES ('0342'); -- dbs CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 030864c325..fc17c38c3e 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -894,44 +894,6 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$ - UPDATE biblio.record_entry - SET marc = vandelay.merge_record_xml( marc, authority.generate_overlay_template( $1 ) ) - WHERE id = $2; - SELECT $1; -$func$ LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT) RETURNS SETOF BIGINT AS $func$ - SELECT authority.propagate_changes( authority, bib ) FROM authority.bib_linking WHERE authority = $1; -$func$ LANGUAGE SQL; - -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 - 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; - END IF; - - - -- authority change propogation - PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_auto_update' AND enabled; - IF NOT FOUND THEN - PERFORM authority.propagate_changes( NEW.id ); - END IF; - - RETURN NEW; -END; -$func$ LANGUAGE PLPGSQL; - - CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$ DELETE FROM authority.bib_linking WHERE bib = $1; INSERT INTO authority.bib_linking (bib, authority) diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 343e259e4f..9d522b78c8 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1316,6 +1316,130 @@ CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON config.copy_status FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); CREATE TRIGGER a_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); +-- Authority ingest routines +CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$ + UPDATE biblio.record_entry + SET marc = vandelay.merge_record_xml( marc, authority.generate_overlay_template( $1 ) ) + WHERE id = $2; + SELECT $1; +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT) RETURNS SETOF BIGINT AS $func$ + SELECT authority.propagate_changes( authority, bib ) FROM authority.bib_linking WHERE authority = $1; +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.flatten_marc ( TEXT ) RETURNS SETOF authority.full_rec AS $func$ + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'UTF-8'); + +my $xml = shift; +my $r = MARC::Record->new_from_xml( $xml ); + +return_next( { tag => 'LDR', value => $r->leader } ); + +for my $f ( $r->fields ) { + if ($f->is_control_field) { + return_next({ tag => $f->tag, value => $f->data }); + } else { + for my $s ($f->subfields) { + return_next({ + tag => $f->tag, + ind1 => $f->indicator(1), + ind2 => $f->indicator(2), + subfield => $s->[0], + value => $s->[1] + }); + + } + } +} + +return undef; + +$func$ LANGUAGE PLPERLU; + +CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$ +DECLARE + auth authority.record_entry%ROWTYPE; + output authority.full_rec%ROWTYPE; + field RECORD; +BEGIN + SELECT INTO auth * FROM authority.record_entry WHERE id = rid; + + FOR field IN SELECT * FROM authority.flatten_marc( auth.marc ) LOOP + output.record := rid; + output.ind1 := field.ind1; + output.ind2 := field.ind2; + output.tag := field.tag; + output.subfield := field.subfield; + IF field.subfield IS NOT NULL THEN + output.value := naco_normalize(field.value, field.subfield); + ELSE + output.value := field.value; + END IF; + + CONTINUE WHEN output.value IS NULL; + + RETURN NEXT output; + END LOOP; +END; +$func$ LANGUAGE PLPGSQL; + +-- authority.rec_descriptor appears to be unused currently +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, char_encoding) +-- SELECT auth_id, ; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION authority.reingest_authority_full_rec( auth_id BIGINT ) RETURNS VOID AS $func$ +BEGIN + DELETE FROM authority.full_rec WHERE record = auth_id; + INSERT INTO authority.full_rec (record, tag, ind1, ind2, subfield, value) + SELECT record, tag, ind1, ind2, subfield, value FROM authority.flatten_marc( auth_id ); + + RETURN; +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 + -- 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; + 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); +-- authority.rec_descriptor is not currently used +-- 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; + -- Ingest triggers CREATE TRIGGER fingerprint_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.fingerprint_trigger ('eng','BKS'); CREATE TRIGGER aaa_indexing_ingest_or_delete AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.indexing_ingest_or_delete (); diff --git a/Open-ILS/src/sql/Pg/upgrade/0342.schema.authority_ingest_triggers.sql b/Open-ILS/src/sql/Pg/upgrade/0342.schema.authority_ingest_triggers.sql new file mode 100644 index 0000000000..7159e8c3a0 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0342.schema.authority_ingest_triggers.sql @@ -0,0 +1,130 @@ +-- Enable automated ingest of authority records; just insert the row into +-- authority.record_entry and authority.full_rec will automatically be populated +BEGIN; + +INSERT INTO config.upgrade_log('342'); --dbs + +CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$ + UPDATE biblio.record_entry + SET marc = vandelay.merge_record_xml( marc, authority.generate_overlay_template( $1 ) ) + WHERE id = $2; + SELECT $1; +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.propagate_changes (aid BIGINT) RETURNS SETOF BIGINT AS $func$ + SELECT authority.propagate_changes( authority, bib ) FROM authority.bib_linking WHERE authority = $1; +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.flatten_marc ( TEXT ) RETURNS SETOF authority.full_rec AS $func$ + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'UTF-8'); + +my $xml = shift; +my $r = MARC::Record->new_from_xml( $xml ); + +return_next( { tag => 'LDR', value => $r->leader } ); + +for my $f ( $r->fields ) { + if ($f->is_control_field) { + return_next({ tag => $f->tag, value => $f->data }); + } else { + for my $s ($f->subfields) { + return_next({ + tag => $f->tag, + ind1 => $f->indicator(1), + ind2 => $f->indicator(2), + subfield => $s->[0], + value => $s->[1] + }); + + } + } +} + +return undef; + +$func$ LANGUAGE PLPERLU; + +CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$ +DECLARE + auth authority.record_entry%ROWTYPE; + output authority.full_rec%ROWTYPE; + field RECORD; +BEGIN + SELECT INTO auth * FROM authority.record_entry WHERE id = rid; + + FOR field IN SELECT * FROM authority.flatten_marc( auth.marc ) LOOP + output.record := rid; + output.ind1 := field.ind1; + output.ind2 := field.ind2; + output.tag := field.tag; + output.subfield := field.subfield; + IF field.subfield IS NOT NULL THEN + output.value := naco_normalize(field.value, field.subfield); + ELSE + output.value := field.value; + END IF; + + CONTINUE WHEN output.value IS NULL; + + RETURN NEXT output; + END LOOP; +END; +$func$ LANGUAGE PLPGSQL; + +-- authority.rec_descriptor appears to be unused currently +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, char_encoding) +-- SELECT auth_id, ; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION authority.reingest_authority_full_rec( auth_id BIGINT ) RETURNS VOID AS $func$ +BEGIN + DELETE FROM authority.full_rec WHERE record = auth_id; + INSERT INTO authority.full_rec (record, tag, ind1, ind2, subfield, value) + SELECT record, tag, ind1, ind2, subfield, value FROM authority.flatten_marc( auth_id ); + + RETURN; +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 + -- 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; + 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); +-- authority.rec_descriptor is not currently used +-- 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