From 20daaeb68912abdd34f157a6d1c3bba2e3662309 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 8 Jun 2010 15:26:24 +0000 Subject: [PATCH] Split the ingest trigger logic into bits, simplifying optimization and configuration git-svn-id: svn://svn.open-ils.org/ILS/trunk@16624 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 7 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 355 ++++++++++-------- .../0300.schema.bib-ingest-splitting.sql | 261 +++++++++++++ 3 files changed, 471 insertions(+), 152 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0300.schema.bib-ingest-splitting.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 4596b9fbfc..f39205364e 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -53,7 +53,10 @@ CREATE TABLE config.internal_flag ( ); INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_insert'); INSERT INTO config.internal_flag (name) VALUES ('ingest.reingest.force_on_same_marc'); -INSERT INTO config.internal_flag (name) VALUES ('ingest.reingest.skip_located_uri'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_located_uri'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_field_entry'); CREATE TABLE config.global_flag ( label TEXT NOT NULL @@ -65,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 ('0299'); -- Galen Charlton +INSERT INTO config.upgrade_log (version) VALUES ('0300'); -- miker 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 ace8449b53..92a49c189a 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -694,14 +694,79 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; --- AFTER UPDATE OR INSERT trigger for biblio.record_entry -CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_rec_descriptor( bib_id BIGINT ) RETURNS VOID AS $func$ +BEGIN + DELETE FROM metabib.rec_descriptor WHERE record = bib_id; + INSERT INTO metabib.rec_descriptor (record, item_type, item_form, bib_level, control_type, enc_level, audience, lit_form, type_mat, cat_form, pub_status, item_lang, vr_format, date1, date2) + SELECT bib_id, + biblio.marc21_extract_fixed_field( bib_id, 'Type' ), + biblio.marc21_extract_fixed_field( bib_id, 'Form' ), + biblio.marc21_extract_fixed_field( bib_id, 'BLvl' ), + biblio.marc21_extract_fixed_field( bib_id, 'Ctrl' ), + biblio.marc21_extract_fixed_field( bib_id, 'ELvl' ), + biblio.marc21_extract_fixed_field( bib_id, 'Audn' ), + biblio.marc21_extract_fixed_field( bib_id, 'LitF' ), + biblio.marc21_extract_fixed_field( bib_id, 'TMat' ), + biblio.marc21_extract_fixed_field( bib_id, 'Desc' ), + biblio.marc21_extract_fixed_field( bib_id, 'DtSt' ), + biblio.marc21_extract_fixed_field( bib_id, 'Lang' ), + ( SELECT v.value + FROM biblio.marc21_physical_characteristics( bib_id) p + JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield) + JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value) + WHERE p.ptype = 'v' AND s.subfield = 'e' ), + biblio.marc21_extract_fixed_field( bib_id, 'Date1'), + biblio.marc21_extract_fixed_field( bib_id, 'Date2'); + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_full_rec( bib_id BIGINT ) RETURNS VOID AS $func$ +BEGIN + DELETE FROM metabib.real_full_rec WHERE record = bib_id; + INSERT INTO metabib.real_full_rec (record, tag, ind1, ind2, subfield, value) + SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( bib_id ); + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT ) RETURNS VOID AS $func$ DECLARE + fclass RECORD; ind_data metabib.field_entry_template%ROWTYPE; - old_mr INT; - tmp_mr metabib.metarecord%ROWTYPE; - source_count INT; - deleted_mrs INT[]; + ind_vector TSVECTOR; +BEGIN + FOR fclass IN SELECT * FROM config.metabib_class LOOP + -- RAISE NOTICE 'Emptying out %', fclass.name; + EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id; + END LOOP; + + FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP + IF ind_data.field < 0 THEN + ind_vector = ''; + ind_data.field = -1 * ind_data.field; + ELSE + ind_vector = NULL; + END IF; + + EXECUTE $$ + INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value, index_vector) + VALUES ($$ || + quote_literal(ind_data.field) || $$, $$ || + quote_literal(ind_data.source) || $$, $$ || + quote_literal(ind_data.value) || $$, $$ || + COALESCE(quote_literal(ind_vector),'NULL'::TEXT) || + $$);$$; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$ +DECLARE uris TEXT[]; uri_xml TEXT; uri_label TEXT; @@ -712,13 +777,117 @@ DECLARE uri_id INT; uri_cn_id INT; uri_map_id INT; +BEGIN - ind_vector TSVECTOR; + uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml); + IF ARRAY_UPPER(uris,1) > 0 THEN + FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP + -- First we pull info out of the 856 + uri_xml := uris[i]; - fclass RECORD; + uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1]; + CONTINUE WHEN uri_href IS NULL; + + uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1]; + CONTINUE WHEN uri_label IS NULL; + + uri_owner := (oils_xpath('//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',uri_xml))[1]; + CONTINUE WHEN uri_owner IS NULL; + + uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1]; + + uri_owner := REGEXP_REPLACE(uri_owner, $re$^.*?\((\w+)\).*$$re$, E'\\1'); + + SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner; + CONTINUE WHEN NOT FOUND; + + -- now we look for a matching uri + SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + IF NOT FOUND THEN -- create one + INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); + SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + END IF; + + -- we need a call number to link through + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + IF NOT FOUND THEN + INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label) + VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##'); + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + END IF; + + -- now, link them if they're not already + SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id; + IF NOT FOUND THEN + INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id); + END IF; + + END LOOP; + END IF; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT ) RETURNS BIGINT AS $func$ +DECLARE + source_count INT; + old_mr BIGINT; + tmp_mr metabib.metarecord%ROWTYPE; + deleted_mrs BIGINT[]; +BEGIN + + DELETE FROM metabib.metarecord_source_map WHERE source = bib_id; -- Rid ourselves of the search-estimate-killing linkage + + FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP + + IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching + old_mr := tmp_mr.id; + ELSE + SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id; + IF source_count = 0 THEN -- No other records + deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id); + DELETE FROM metabib.metarecord WHERE id = tmp_mr.id; + END IF; + END IF; + + END LOOP; + + IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint? + IF old_mr IS NULL THEN -- nope, create one and grab its id + INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id ); + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; + ELSE -- indeed there is. update it with a null cache and recalcualated master record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + ELSE -- there was one we already attached to, update its mods cache and master_record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + + INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping + + IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN + UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT explode_array(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + END IF; + + RETURN old_mr; + +END; +$func$ LANGUAGE PLPGSQL; + +-- AFTER UPDATE OR INSERT trigger for biblio.record_entry +CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ BEGIN - IF NEW.deleted IS TRUE THEN + 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 RETURN NEW; -- and we're done END IF; @@ -730,160 +899,46 @@ BEGIN RETURN NEW; END IF; - DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage END IF; - IF TG_OP = 'UPDATE' THEN -- Clean out the cruft - FOR fclass IN SELECT * FROM config.metabib_class LOOP - -- RAISE NOTICE 'Emptying out %', fclass.name; - EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || NEW.id; - END LOOP; - DELETE FROM metabib.full_rec WHERE record = NEW.id; - DELETE FROM metabib.rec_descriptor WHERE record = NEW.id; - + -- 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; - -- Shove the flattened MARC in - INSERT INTO metabib.full_rec (record, tag, ind1, ind2, subfield, value) - SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( NEW.id ); + -- Gather and insert the field entry data + PERFORM metabib.reingest_metabib_field_entries(NEW.id); - -- And now the indexing data - FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( NEW.id ) LOOP - IF ind_data.field < 0 THEN - ind_vector = ''; - ind_data.field = -1 * ind_data.field; - ELSE - ind_vector = NULL; + -- 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; - - EXECUTE $$ - INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value, index_vector) - VALUES ($$ || - quote_literal(ind_data.field) || $$, $$ || - quote_literal(ind_data.source) || $$, $$ || - quote_literal(ind_data.value) || $$, $$ || - COALESCE(quote_literal(ind_vector),'NULL'::TEXT) || - $$);$$; - END LOOP; - - -- Then, the rec_descriptor - INSERT INTO metabib.rec_descriptor (record, item_type, item_form, bib_level, control_type, enc_level, audience, lit_form, type_mat, cat_form, pub_status, item_lang, vr_format, date1, date2) - SELECT NEW.id, - biblio.marc21_extract_fixed_field( NEW.id, 'Type' ), - biblio.marc21_extract_fixed_field( NEW.id, 'Form' ), - biblio.marc21_extract_fixed_field( NEW.id, 'BLvl' ), - biblio.marc21_extract_fixed_field( NEW.id, 'Ctrl' ), - biblio.marc21_extract_fixed_field( NEW.id, 'ELvl' ), - biblio.marc21_extract_fixed_field( NEW.id, 'Audn' ), - biblio.marc21_extract_fixed_field( NEW.id, 'LitF' ), - biblio.marc21_extract_fixed_field( NEW.id, 'TMat' ), - biblio.marc21_extract_fixed_field( NEW.id, 'Desc' ), - biblio.marc21_extract_fixed_field( NEW.id, 'DtSt' ), - biblio.marc21_extract_fixed_field( NEW.id, 'Lang' ), - ( SELECT v.value - FROM biblio.marc21_physical_characteristics( NEW.id) p - JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield) - JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value) - WHERE p.ptype = 'v' AND s.subfield = 'e' ), - biblio.marc21_extract_fixed_field( NEW.id, 'Date1'), - biblio.marc21_extract_fixed_field( NEW.id, 'Date2'); - - -- On to URIs ... - PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.skip_located_uri' AND enabled; - - IF NOT FOUND OR TG_OP = 'INSERT' THEN - uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',NEW.marc); - IF ARRAY_UPPER(uris,1) > 0 THEN - FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP - -- First we pull infot out of the 856 - uri_xml := uris[i]; - - uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1]; - CONTINUE WHEN uri_href IS NULL; - - uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1]; - CONTINUE WHEN uri_label IS NULL; - - uri_owner := (oils_xpath('//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',uri_xml))[1]; - CONTINUE WHEN uri_owner IS NULL; - - uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1]; - - uri_owner := REGEXP_REPLACE(uri_owner, $re$^.*?\((\w+)\).*$$re$, E'\\1'); - - SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner; - CONTINUE WHEN NOT FOUND; - - -- now we look for a matching uri - SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; - IF NOT FOUND THEN -- create one - INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); - SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; - END IF; - - -- we need a call number to link through - SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = NEW.id AND label = '##URI##' AND NOT deleted; - IF NOT FOUND THEN - INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label) - VALUES (uri_owner_id, NEW.id, 'now', 'now', NEW.editor, NEW.editor, '##URI##'); - SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = NEW.id AND label = '##URI##' AND NOT deleted; - END IF; - - -- now, link them if they're not already - SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id; - IF NOT FOUND THEN - INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id); - END IF; - - END LOOP; + 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; - -- And, finally, metarecord mapping! - - PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled; - - IF NOT FOUND OR TG_OP = 'UPDATE' THEN - FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = NEW.id LOOP - - IF old_mr IS NULL AND NEW.fingerprint = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching - old_mr := tmp_mr.id; - ELSE - SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id; - IF source_count = 0 THEN -- No other records - deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id); - DELETE FROM metabib.metarecord WHERE id = tmp_mr.id; - END IF; - END IF; - - END LOOP; - - IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps - SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint; -- is there one for our current fingerprint? - IF old_mr IS NULL THEN -- nope, create one and grab its id - INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( NEW.fingerprint, NEW.id ); - SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint; - ELSE -- indeed there is. update it with a null cache and recalcualated master record - UPDATE metabib.metarecord - SET mods = NULL, - master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1) - WHERE id = old_mr; - END IF; - ELSE -- there was one we already attached to, update its mods cache and master_record - UPDATE metabib.metarecord - SET mods = NULL, - master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1) - WHERE id = old_mr; + -- (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; - - INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, NEW.id); -- new source mapping - - UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT explode_array(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + 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; + RETURN NEW; END; $func$ LANGUAGE PLPGSQL; diff --git a/Open-ILS/src/sql/Pg/upgrade/0300.schema.bib-ingest-splitting.sql b/Open-ILS/src/sql/Pg/upgrade/0300.schema.bib-ingest-splitting.sql new file mode 100644 index 0000000000..22ce511121 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0300.schema.bib-ingest-splitting.sql @@ -0,0 +1,261 @@ + +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_located_uri'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor'); +INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_field_entry'); + +BEGIN; + +-- Splitting the ingest trigger up into little bits + +INSERT INTO config.upgrade_log (version) VALUES ('0300'); --miker + +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_rec_descriptor( bib_id BIGINT ) RETURNS VOID AS $func$ +BEGIN + DELETE FROM metabib.rec_descriptor WHERE record = bib_id; + INSERT INTO metabib.rec_descriptor (record, item_type, item_form, bib_level, control_type, enc_level, audience, lit_form, type_mat, cat_form, pub_status, item_lang, vr_format, date1, date2) + SELECT bib_id, + biblio.marc21_extract_fixed_field( bib_id, 'Type' ), + biblio.marc21_extract_fixed_field( bib_id, 'Form' ), + biblio.marc21_extract_fixed_field( bib_id, 'BLvl' ), + biblio.marc21_extract_fixed_field( bib_id, 'Ctrl' ), + biblio.marc21_extract_fixed_field( bib_id, 'ELvl' ), + biblio.marc21_extract_fixed_field( bib_id, 'Audn' ), + biblio.marc21_extract_fixed_field( bib_id, 'LitF' ), + biblio.marc21_extract_fixed_field( bib_id, 'TMat' ), + biblio.marc21_extract_fixed_field( bib_id, 'Desc' ), + biblio.marc21_extract_fixed_field( bib_id, 'DtSt' ), + biblio.marc21_extract_fixed_field( bib_id, 'Lang' ), + ( SELECT v.value + FROM biblio.marc21_physical_characteristics( bib_id) p + JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield) + JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value) + WHERE p.ptype = 'v' AND s.subfield = 'e' ), + biblio.marc21_extract_fixed_field( bib_id, 'Date1'), + biblio.marc21_extract_fixed_field( bib_id, 'Date2'); + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_full_rec( bib_id BIGINT ) RETURNS VOID AS $func$ +BEGIN + DELETE FROM metabib.real_full_rec WHERE record = bib_id; + INSERT INTO metabib.real_full_rec (record, tag, ind1, ind2, subfield, value) + SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( bib_id ); + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT ) RETURNS VOID AS $func$ +DECLARE + fclass RECORD; + ind_data metabib.field_entry_template%ROWTYPE; + ind_vector TSVECTOR; +BEGIN + FOR fclass IN SELECT * FROM config.metabib_class LOOP + -- RAISE NOTICE 'Emptying out %', fclass.name; + EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id; + END LOOP; + + FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP + IF ind_data.field < 0 THEN + ind_vector = ''; + ind_data.field = -1 * ind_data.field; + ELSE + ind_vector = NULL; + END IF; + + EXECUTE $$ + INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value, index_vector) + VALUES ($$ || + quote_literal(ind_data.field) || $$, $$ || + quote_literal(ind_data.source) || $$, $$ || + quote_literal(ind_data.value) || $$, $$ || + COALESCE(quote_literal(ind_vector),'NULL'::TEXT) || + $$);$$; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$ +DECLARE + uris TEXT[]; + uri_xml TEXT; + uri_label TEXT; + uri_href TEXT; + uri_use TEXT; + uri_owner TEXT; + uri_owner_id INT; + uri_id INT; + uri_cn_id INT; + uri_map_id INT; +BEGIN + + uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml); + IF ARRAY_UPPER(uris,1) > 0 THEN + FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP + -- First we pull info out of the 856 + uri_xml := uris[i]; + + uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1]; + CONTINUE WHEN uri_href IS NULL; + + uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1]; + CONTINUE WHEN uri_label IS NULL; + + uri_owner := (oils_xpath('//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',uri_xml))[1]; + CONTINUE WHEN uri_owner IS NULL; + + uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1]; + + uri_owner := REGEXP_REPLACE(uri_owner, $re$^.*?\((\w+)\).*$$re$, E'\\1'); + + SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner; + CONTINUE WHEN NOT FOUND; + + -- now we look for a matching uri + SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + IF NOT FOUND THEN -- create one + INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); + SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + END IF; + + -- we need a call number to link through + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + IF NOT FOUND THEN + INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label) + VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##'); + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + END IF; + + -- now, link them if they're not already + SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id; + IF NOT FOUND THEN + INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id); + END IF; + + END LOOP; + END IF; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT ) RETURNS BIGINT AS $func$ +DECLARE + source_count INT; + old_mr BIGINT; + tmp_mr metabib.metarecord%ROWTYPE; + deleted_mrs BIGINT[]; +BEGIN + + DELETE FROM metabib.metarecord_source_map WHERE source = bib_id; -- Rid ourselves of the search-estimate-killing linkage + + FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP + + IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching + old_mr := tmp_mr.id; + ELSE + SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id; + IF source_count = 0 THEN -- No other records + deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id); + DELETE FROM metabib.metarecord WHERE id = tmp_mr.id; + END IF; + END IF; + + END LOOP; + + IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint? + IF old_mr IS NULL THEN -- nope, create one and grab its id + INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id ); + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; + ELSE -- indeed there is. update it with a null cache and recalcualated master record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + ELSE -- there was one we already attached to, update its mods cache and master_record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + + INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping + + IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN + UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT explode_array(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + END IF; + + RETURN old_mr; + +END; +$func$ LANGUAGE PLPGSQL; + +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 + 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 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; + +COMMIT; + -- 2.43.2