From 87a5ea7d1e3d4fba37085d49051a217e742e7c05 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 12 Mar 2010 22:40:36 +0000 Subject: [PATCH] bug fix for auto-merge short circut mapping on 901c git-svn-id: svn://svn.open-ils.org/ILS/trunk@15840 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 18 ++- ...chema.vandelay.bib-merge-crosscheck-bug-fix.sql | 131 +++++++++++++++++++++ 3 files changed, 146 insertions(+), 5 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0195.schema.vandelay.bib-merge-crosscheck-bug-fix.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 6b1c3e05ad..e9229fbdbe 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0194'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0195'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 2fdc70003b..f6635880ed 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -360,6 +360,14 @@ DECLARE match_count INT; match_attr vandelay.bib_attr_definition%ROWTYPE; BEGIN + + PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id; + + IF FOUND THEN + -- RAISE NOTICE 'already imported, cannot auto-overlay' + RETURN FALSE; + END IF; + SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id; IF match_count <> 1 THEN @@ -374,7 +382,7 @@ BEGIN WHERE m.queued_record = import_id; IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN - -- RAISE NOTICE 'not a 901c match'; + -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath; RETURN FALSE; END IF; @@ -758,6 +766,7 @@ $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$ DECLARE attr RECORD; + attr_def RECORD; eg_rec RECORD; id_value TEXT; exact_id BIGINT; @@ -765,13 +774,14 @@ BEGIN DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id; - SELECT * INTO attr FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1; + SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1; - IF attr IS NOT NULL AND attr.id IS NOT NULL THEN - id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr.xpath, attr.remove); + IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN + id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove); IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted; + SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1; IF exact_id IS NOT NULL THEN INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id); END IF; diff --git a/Open-ILS/src/sql/Pg/upgrade/0195.schema.vandelay.bib-merge-crosscheck-bug-fix.sql b/Open-ILS/src/sql/Pg/upgrade/0195.schema.vandelay.bib-merge-crosscheck-bug-fix.sql new file mode 100644 index 0000000000..7ab26f4d24 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0195.schema.vandelay.bib-merge-crosscheck-bug-fix.sql @@ -0,0 +1,131 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0195'); -- miker + +CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$ +DECLARE + eg_id BIGINT; + match_count INT; + match_attr vandelay.bib_attr_definition%ROWTYPE; +BEGIN + + PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id; + + IF FOUND THEN + -- RAISE NOTICE 'already imported, cannot auto-overlay' + RETURN FALSE; + END IF; + + SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id; + + IF match_count <> 1 THEN + -- RAISE NOTICE 'not an exact match'; + RETURN FALSE; + END IF; + + SELECT d.* INTO match_attr + FROM vandelay.bib_attr_definition d + JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id) + JOIN vandelay.bib_match m ON (m.matched_attr = a.id) + WHERE m.queued_record = import_id; + + IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN + -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath; + RETURN FALSE; + END IF; + + SELECT m.eg_record INTO eg_id + FROM vandelay.bib_match m + WHERE m.queued_record = import_id + LIMIT 1; + + IF eg_id IS NULL THEN + RETURN FALSE; + END IF; + + RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id ); +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$ +DECLARE + attr RECORD; + attr_def RECORD; + eg_rec RECORD; + id_value TEXT; + exact_id BIGINT; +BEGIN + + DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id; + + SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1; + + IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN + id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove); + + IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN + SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted; + SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1; + IF exact_id IS NOT NULL THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id); + END IF; + END IF; + END IF; + + IF exact_id IS NULL THEN + FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP + + -- All numbers? check for an id match + IF (attr.attr_value ~ $r$^\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- Looks like an ISBN? check for an isbn match + IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN + FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP + PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE; + IF FOUND THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record); + END IF; + END LOOP; + + -- subcheck for isbn-as-tcn + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for an OCLC tcn_value match + IF (attr.attr_value ~ $r$^o\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for a direct tcn_value match + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + + -- check for a direct item barcode match + FOR eg_rec IN + SELECT DISTINCT b.* + FROM biblio.record_entry b + JOIN asset.call_number cn ON (cn.record = b.id) + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE + LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + + END LOOP; + END IF; + + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.11.0