From 4b3b85a06b52a1eb536c25b95522e364aa4783df Mon Sep 17 00:00:00 2001 From: dbs Date: Thu, 15 Jul 2010 15:50:58 +0000 Subject: [PATCH] Delete links to deleted bib records as part of biblio.indexing_ingest_or_delete() Moves authority.bib_linking cleanup from the special case of merging bib record assets to the general case of deleting bib records git-svn-id: svn://svn.open-ils.org/ILS/trunk@16940 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 1 + Open-ILS/src/sql/Pg/999.functions.global.sql | 11 -- .../Pg/upgrade/0338.functions.biblio.indexing.sql | 214 +++++++++++++++++++++ 4 files changed, 216 insertions(+), 12 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0338.functions.biblio.indexing.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index e64a1e9398..26667491d3 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 ('0337'); -- dbs +INSERT INTO config.upgrade_log (version) VALUES ('0338'); -- 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 3c03e0aade..030864c325 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -950,6 +950,7 @@ BEGIN IF NEW.deleted IS TRUE THEN -- If this bib is deleted DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage + DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible RETURN NEW; -- and we're done END IF; diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 2f82968da4..343e259e4f 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -964,7 +964,6 @@ COMMENT ON FUNCTION container.clear_all_expired_circ_history_items( ) IS $$ */ $$; - CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$ DECLARE moved_objects INT := 0; @@ -973,7 +972,6 @@ DECLARE metarec metabib.metarecord%ROWTYPE; hold action.hold_request%ROWTYPE; ser_rec serial.record_entry%ROWTYPE; - auth_link authority.bib_linking%ROWTYPE; uri_count INT := 0; counter INT := 0; uri_datafield TEXT; @@ -1104,15 +1102,6 @@ BEGIN moved_objects := moved_objects + 1; END LOOP; - -- Delete authority->bib links to the source record to avoid - -- the overhead of updating controlled fields in deleted records - FOR auth_link IN SELECT * FROM authority.bib_linking WHERE bib = source_record LOOP - DELETE FROM authority.bib_linking - WHERE id = auth_link.id; - - moved_objects := moved_objects + 1; - END LOOP; - -- Finally, "delete" the source record DELETE FROM biblio.record_entry WHERE id = source_record; diff --git a/Open-ILS/src/sql/Pg/upgrade/0338.functions.biblio.indexing.sql b/Open-ILS/src/sql/Pg/upgrade/0338.functions.biblio.indexing.sql new file mode 100644 index 0000000000..059f8278c8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0338.functions.biblio.indexing.sql @@ -0,0 +1,214 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0338'); -- dbs + +CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +BEGIN + + IF NEW.deleted IS TRUE THEN -- If this bib is deleted + DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage + DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible + 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; + + -- Record authority linking + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.map_authority_linking( NEW.id, NEW.marc ); + END IF; + + -- Flatten and insert the mfr data + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.reingest_metabib_full_rec(NEW.id); + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.reingest_metabib_rec_descriptor(NEW.id); + END IF; + END IF; + + -- Gather and insert the field entry data + PERFORM metabib.reingest_metabib_field_entries(NEW.id); + + -- Located URI magic + IF TG_OP = 'INSERT' THEN + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); + END IF; + ELSE + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); + END IF; + END IF; + + -- (re)map metarecord-bib linking + IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag + PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint ); + END IF; + ELSE -- we're doing an update, and we're not deleted, remap + PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint ); + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$ +DECLARE + moved_objects INT := 0; + source_cn asset.call_number%ROWTYPE; + target_cn asset.call_number%ROWTYPE; + metarec metabib.metarecord%ROWTYPE; + hold action.hold_request%ROWTYPE; + ser_rec serial.record_entry%ROWTYPE; + uri_count INT := 0; + counter INT := 0; + uri_datafield TEXT; + uri_text TEXT := ''; +BEGIN + + -- move any 856 entries on records that have at least one MARC-mapped URI entry + SELECT INTO uri_count COUNT(*) + FROM asset.uri_call_number_map m + JOIN asset.call_number cn ON (m.call_number = cn.id) + WHERE cn.record = source_record; + + IF uri_count > 0 THEN + + SELECT COUNT(*) INTO counter + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"]', + 'id=' || lineitem + ) as t(i int,c text); + + FOR i IN 1 .. counter LOOP + SELECT '' || + array_to_string( + array_accum( + '' || + regexp_replace( + regexp_replace( + regexp_replace(data,'&','&','g'), + '>', '>', 'g' + ), + '<', '<', 'g' + ) || '' + ), '' + ) || '' INTO uri_datafield + FROM oils_xpath_table( + 'id', + 'marc', + 'biblio.record_entry', + '//*[@tag="856"][position()=' || i || ']/*/@code|' || + '//*[@tag="856"][position()=' || i || ']/*[@code]', + 'id=' || source_record + ) as t(id int,subfield text,data text); + + uri_text := uri_text || uri_datafield; + END LOOP; + + IF uri_text <> '' THEN + UPDATE biblio.record_entry + SET marc = regexp_replace(marc,'(]*record>)', uri_text || E'\\1') + WHERE id = target_record; + END IF; + + END IF; + + -- Find and move metarecords to the target record + SELECT INTO metarec * + FROM metabib.metarecord + WHERE master_record = source_record; + + IF FOUND THEN + UPDATE metabib.metarecord + SET master_record = target_record, + mods = NULL + WHERE id = metarec.id; + + moved_objects := moved_objects + 1; + END IF; + + -- Find call numbers attached to the source ... + FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP + + SELECT INTO target_cn * + FROM asset.call_number + WHERE label = source_cn.label + AND owning_lib = source_cn.owning_lib + AND record = target_record; + + -- ... and if there's a conflicting one on the target ... + IF FOUND THEN + + -- ... move the copies to that, and ... + UPDATE asset.copy + SET call_number = target_cn.id + WHERE call_number = source_cn.id; + + -- ... move V holds to the move-target call number + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP + + UPDATE action.hold_request + SET target = target_cn.id + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- ... if not ... + ELSE + -- ... just move the call number to the target record + UPDATE asset.call_number + SET record = target_record + WHERE id = source_cn.id; + END IF; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find T holds targeting the source record ... + FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP + + -- ... and move them to the target record + UPDATE action.hold_request + SET target = target_record + WHERE id = hold.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Find serial records targeting the source record ... + FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP + -- ... and move them to the target record + UPDATE serial.record_entry + SET record = target_record + WHERE id = ser_rec.id; + + moved_objects := moved_objects + 1; + END LOOP; + + -- Finally, "delete" the source record + DELETE FROM biblio.record_entry WHERE id = source_record; + + -- That's all, folks! + RETURN moved_objects; +END; +$func$ LANGUAGE plpgsql; + +COMMIT; -- 2.11.0