From 58776c4629742d786bc9dac3d7505727c753eb9f Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 19 May 2010 18:54:25 +0000 Subject: [PATCH] add ISBN 10/13 index normalizer (requires ISN postgres contrib module); adjust index definition xpath so it actually works; generalize indexing trigger function so it does not have to know about search classes before hand git-svn-id: svn://svn.open-ils.org/ILS/trunk@16452 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 6 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 35 ++- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 97 ++++++-- ....schema.identifier-search-class-improvement.sql | 253 +++++++++++++++++++++ 4 files changed, 346 insertions(+), 45 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0267.schema.identifier-search-class-improvement.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 6192c2272d..3c1d7de430 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -65,7 +65,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0266'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0267'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, @@ -713,6 +713,10 @@ CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' ); $func$ LANGUAGE SQL STRICT IMMUTABLE; +CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$ + SELECT isbn FROM (SELECT isn_weak(true), $1 || ' ' || REPLACE( CASE WHEN length($1) = 10 THEN isbn13($1)::TEXT WHEN length($1) = 13 THEN isbn($1)::TEXT ELSE '' END, '-', '') AS isbn)x; +$func$ LANGUAGE SQL STRICT IMMUTABLE; + -- And ... a table in which to register them CREATE TABLE config.index_normalizer ( diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index afb3ba509b..6cdf2b9ab5 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -714,6 +714,8 @@ DECLARE uri_map_id INT; ind_vector TSVECTOR; + + fclass RECORD; BEGIN IF NEW.deleted IS TRUE THEN @@ -733,11 +735,10 @@ BEGIN END IF; IF TG_OP = 'UPDATE' THEN -- Clean out the cruft - DELETE FROM metabib.title_field_entry WHERE source = NEW.id; - DELETE FROM metabib.author_field_entry WHERE source = NEW.id; - DELETE FROM metabib.subject_field_entry WHERE source = NEW.id; - DELETE FROM metabib.keyword_field_entry WHERE source = NEW.id; - DELETE FROM metabib.series_field_entry WHERE source = NEW.id; + 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; @@ -756,22 +757,14 @@ BEGIN ind_vector = NULL; END IF; - IF ind_data.field_class = 'title' THEN - INSERT INTO metabib.title_field_entry (field, source, value, index_vector) - VALUES (ind_data.field, ind_data.source, ind_data.value, ind_vector); - ELSIF ind_data.field_class = 'author' THEN - INSERT INTO metabib.author_field_entry (field, source, value, index_vector) - VALUES (ind_data.field, ind_data.source, ind_data.value, ind_vector); - ELSIF ind_data.field_class = 'subject' THEN - INSERT INTO metabib.subject_field_entry (field, source, value, index_vector) - VALUES (ind_data.field, ind_data.source, ind_data.value, ind_vector); - ELSIF ind_data.field_class = 'keyword' THEN - INSERT INTO metabib.keyword_field_entry (field, source, value, index_vector) - VALUES (ind_data.field, ind_data.source, ind_data.value, ind_vector); - ELSIF ind_data.field_class = 'series' THEN - INSERT INTO metabib.series_field_entry (field, source, value, index_vector) - VALUES (ind_data.field, ind_data.source, ind_data.value, ind_vector); - 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 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 cd8c42e912..0758192257 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -56,24 +56,24 @@ INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES (16, 'subject', 'complete', oils_i18n_gettext(16, 'All Subjects', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject//text()$$ ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (17, 'identifier', 'accession', oils_i18n_gettext(17, 'Accession Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="001"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (18, 'identifier', 'isbn', oils_i18n_gettext(18, 'ISBN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="020"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (19, 'identifier', 'issn', oils_i18n_gettext(19, 'ISSN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="022"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (20, 'identifier', 'upc', oils_i18n_gettext(20, 'UPC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="1"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (21, 'identifier', 'ismn', oils_i18n_gettext(21, 'ISMN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="2"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (22, 'identifier', 'ean', oils_i18n_gettext(22, 'EAN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="3"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (23, 'identifier', 'isrc', oils_i18n_gettext(23, 'ISRC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="0"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (24, 'identifier', 'sici', oils_i18n_gettext(24, 'SICI', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="4"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES - (25, 'identifier', 'bibcn', oils_i18n_gettext(25, 'Local Free-Text Call Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="099"]//text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (17, 'identifier', 'accession', oils_i18n_gettext(17, 'Accession Number', 'cmf', 'label'), 'marc', $$//marc:controlfield[@tag='001']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (18, 'identifier', 'isbn', oils_i18n_gettext(18, 'ISBN', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='020']/marc:subfield[@code='a' or @code='z']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (19, 'identifier', 'issn', oils_i18n_gettext(19, 'ISSN', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='022']/marc:subfield[@code='a' or @code='z']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (20, 'identifier', 'upc', oils_i18n_gettext(20, 'UPC', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='024' and ind1='1']/marc:subfield[@code='a' or @code='z']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (21, 'identifier', 'ismn', oils_i18n_gettext(21, 'ISMN', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='024' and ind1='2']/marc:subfield[@code='a' or @code='z']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (22, 'identifier', 'ean', oils_i18n_gettext(22, 'EAN', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='024' and ind1='3']/marc:subfield[@code='a' or @code='z']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (23, 'identifier', 'isrc', oils_i18n_gettext(23, 'ISRC', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='024' and ind1='0']/marc:subfield[@code='a' or @code='z']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (24, 'identifier', 'sici', oils_i18n_gettext(24, 'SICI', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='024' and ind1='4']/marc:subfield[@code='a' or @code='z']$$ ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES + (25, 'identifier', 'bibcn', oils_i18n_gettext(25, 'Local Free-Text Call Number', 'cmf', 'label'), 'marc', $$//marc:datafield[@tag='099']//text()$$ ); SELECT SETVAL('config.metabib_field_id_seq'::TEXT, (SELECT MAX(id) FROM config.metabib_field), TRUE); @@ -4168,14 +4168,65 @@ INSERT INTO config.index_normalizer (name, description, func, param_count) VALUE 0 ); +INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES ( + 'ISBN 10/13 conversion', + 'Translate ISBN10 to ISBN13 and vice versa.', + 'translate_isbn1013', + 0 +); + +INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES ( + 'Replace', + 'Replace all occurances of first parameter in the string with the second parameter.', + 'replace', + 2 +); + -- make use of the index normalizers INSERT INTO config.metabib_field_index_norm_map (field,norm) - SELECT m.id, - i.id - FROM config.metabib_field m, - config.index_normalizer i - WHERE i.func IN ('naco_normalize','split_date_range'); + SELECT m.id, + i.id + FROM config.metabib_field m, + config.index_normalizer i + WHERE i.func IN ('naco_normalize','split_date_range') + AND m.id NOT IN (19); + +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 IN ('first_word') + AND m.id IN (18); + +INSERT INTO config.metabib_field_index_norm_map (field,norm,pos) + SELECT m.id, + i.id, + 2 + FROM config.metabib_field m, + config.index_normalizer i + WHERE i.func IN ('translate_isbn1013') + AND m.id IN (18); + +INSERT INTO config.metabib_field_index_norm_map (field,norm,params) + SELECT m.id, + i.id, + $$['-','']$$ + FROM config.metabib_field m, + config.index_normalizer i + WHERE i.func IN ('replace') + AND m.id IN (19); + +INSERT INTO config.metabib_field_index_norm_map (field,norm,params) + SELECT m.id, + i.id, + $$[' ','']$$ + FROM config.metabib_field m, + config.index_normalizer i + WHERE i.func IN ('replace') + AND m.id IN (19); -- claims returned mark item missing INSERT INTO diff --git a/Open-ILS/src/sql/Pg/upgrade/0267.schema.identifier-search-class-improvement.sql b/Open-ILS/src/sql/Pg/upgrade/0267.schema.identifier-search-class-improvement.sql new file mode 100644 index 0000000000..b8d3029088 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0267.schema.identifier-search-class-improvement.sql @@ -0,0 +1,253 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0267'); -- miker + +CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$ + SELECT isbn FROM (SELECT isn_weak(true), $1 || ' ' || REPLACE( CASE WHEN length($1) = 10 THEN isbn13($1)::TEXT WHEN length($1) = 13 THEN isbn($1)::TEXT ELSE '' END, '-', '') AS isbn)x; +$func$ LANGUAGE SQL STRICT IMMUTABLE; + + +UPDATE config.metabib_field SET facet_field = FALSE WHERE id BETWEEN 17 AND 25; +UPDATE config.metabib_field SET xpath = REPLACE(xpath,'marcxml','marc') WHERE id BETWEEN 17 AND 25; +UPDATE config.metabib_field SET xpath = REPLACE(xpath,'tag','@tag') WHERE id BETWEEN 17 AND 25; +UPDATE config.metabib_field SET xpath = REPLACE(xpath,'code','@code') WHERE id BETWEEN 17 AND 25; +UPDATE config.metabib_field SET xpath = REPLACE(xpath,'"',E'\'') WHERE id BETWEEN 17 AND 25; +UPDATE config.metabib_field SET xpath = REPLACE(xpath,'/text()','') WHERE id BETWEEN 17 AND 24; + + +INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES ( + 'ISBN 10/13 conversion', + 'Translate ISBN10 to ISBN13, and vice versa, for indexing purposes.', + 'translate_isbn1013', + 0 +); + +INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES ( + 'Replace', + 'Replace all occurances of first parameter in the string with the second parameter.', + 'replace', + 2 +); + +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 IN ('first_word') + AND m.id IN (18); + +INSERT INTO config.metabib_field_index_norm_map (field,norm,pos) + SELECT m.id, i.id, 2 + FROM config.metabib_field m, + config.index_normalizer i + WHERE i.func IN ('translate_isbn1013') + AND m.id IN (18); + +INSERT INTO config.metabib_field_index_norm_map (field,norm,params) + SELECT m.id, i.id, $$['-','']$$ + FROM config.metabib_field m, + config.index_normalizer i + WHERE i.func IN ('replace') + AND m.id IN (19); + +INSERT INTO config.metabib_field_index_norm_map (field,norm,params) + SELECT m.id, i.id, $$[' ','']$$ + FROM config.metabib_field m, + config.index_normalizer i + WHERE i.func IN ('replace') + AND m.id IN (19); + +CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +DECLARE + ind_data metabib.field_entry_template%ROWTYPE; + old_mr INT; + tmp_mr metabib.metarecord%ROWTYPE; + source_count INT; + deleted_mrs INT[]; + 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; + + ind_vector TSVECTOR; + + fclass RECORD; +BEGIN + + IF NEW.deleted IS TRUE THEN + 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; + + 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; + + 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 ); + + -- 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; + 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; + 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; + 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 + END IF; + + RETURN NEW; + +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.11.0