From 3612011cba7d7bcbb2546b3f38e4b0e647368d9d Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 12 Aug 2011 17:03:11 -0400 Subject: [PATCH] Vandelay: capture one match per catalog record When searching for matches to existing catalog records (biblio.record_entry's), Vandelay can in some cases result in multiple like matches for the same record (with the same match score). This trims it down to 1 match per record. Signed-off-by: Bill Erickson Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 2 +- .../0598.schema.vandelay_one_match_per.sql | 63 +++++++++++++++++++ 3 files changed, 65 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0598.schema.vandelay_one_match_per.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 1ad0290e15..07a62ceedf 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -86,7 +86,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0597', :eg_version); -- miker/senator +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0598', :eg_version); -- berick/senator 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 d6258bc6f4..2d03b75590 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -508,7 +508,7 @@ BEGIN -- a side-effect, populate the _vandelay_tmp_[qj]rows tables. wq := vandelay.get_expr_from_match_set(match_set_id); - query_ := 'SELECT bre.id AS record, '; + query_ := 'SELECT DISTINCT(bre.id) AS record, '; -- qrows table is for the quality bits we add to the SELECT clause SELECT ARRAY_TO_STRING( diff --git a/Open-ILS/src/sql/Pg/upgrade/0598.schema.vandelay_one_match_per.sql b/Open-ILS/src/sql/Pg/upgrade/0598.schema.vandelay_one_match_per.sql new file mode 100644 index 0000000000..ace7ef6561 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0598.schema.vandelay_one_match_per.sql @@ -0,0 +1,63 @@ +-- Evergreen DB patch 0598.schema.vandelay_one_match_per.sql +-- +BEGIN; + + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0598', :eg_version); + +CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml( + match_set_id INTEGER, record_xml TEXT +) RETURNS SETOF vandelay.match_set_test_result AS $$ +DECLARE + tags_rstore HSTORE; + svf_rstore HSTORE; + coal TEXT; + joins TEXT; + query_ TEXT; + wq TEXT; + qvalue INTEGER; + rec RECORD; +BEGIN + tags_rstore := vandelay.flatten_marc_hstore(record_xml); + svf_rstore := vandelay.extract_rec_attrs(record_xml); + + CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER); + CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT); + + -- generate the where clause and return that directly (into wq), and as + -- a side-effect, populate the _vandelay_tmp_[qj]rows tables. + wq := vandelay.get_expr_from_match_set(match_set_id); + + query_ := 'SELECT DISTINCT(bre.id) AS record, '; + + -- qrows table is for the quality bits we add to the SELECT clause + SELECT ARRAY_TO_STRING( + ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + ' + ) INTO coal FROM _vandelay_tmp_qrows; + + -- our query string so far is the SELECT clause and the inital FROM. + -- no JOINs yet nor the WHERE clause + query_ := query_ || coal || ' AS quality ' || E'\n' || + 'FROM biblio.record_entry bre '; + + -- jrows table is for the joins we must make (and the real text conditions) + SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins + FROM _vandelay_tmp_jrows; + + -- add those joins and the where clause to our query. + query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted'; + + -- this will return rows of record,quality + FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP + RETURN NEXT rec; + END LOOP; + + DROP TABLE _vandelay_tmp_qrows; + DROP TABLE _vandelay_tmp_jrows; + RETURN; +END; + +$$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2