From 59cc4b9524bfcf9a56f54c5ed5a3d56a7d1b86e6 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 31 Jan 2013 12:37:44 -0500 Subject: [PATCH] Vandelay record bucket-limited matching Provides the option to link record buckets to vandelay queues. When linked, vandelay imports where a match set is specified will limit matches to records within the selected bucket. Signed-off-by: Bill Erickson --- Open-ILS/examples/fm_IDL.xml | 2 + .../lib/OpenILS/Application/Vandelay.pm | 2 + Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 20 ++- .../XXXX.schema.z39-batch-fetch-overlay.sql | 1 - .../YYYY.schema.vandelay_bucket_match.sql | 135 ++++++++++++++++++ .../src/templates/vandelay/inc/upload.tt2 | 5 + .../web/js/ui/default/vandelay/vandelay.js | 47 +++++- 7 files changed, 202 insertions(+), 10 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 8e69bbd875..837e5e6b93 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -370,11 +370,13 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm index 67964478f8..778ab8b76e 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm @@ -63,6 +63,7 @@ sub create_bib_queue { my $type = shift; my $match_set = shift; my $import_def = shift; + my $match_bucket = shift; my $e = new_editor(authtoken => $auth, xact => 1); @@ -81,6 +82,7 @@ sub create_bib_queue { $queue->queue_type( $type ) if ($type); $queue->item_attr_def( $import_def ) if ($import_def); $queue->match_set($match_set) if $match_set; + $queue->match_bucket($match_bucket); my $new_q = $e->create_vandelay_bib_queue( $queue ); return $e->die_event unless ($new_q); diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 4122444ec5..34249d8b51 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -496,7 +496,7 @@ $_$ LANGUAGE SQL; CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER); CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml( - match_set_id INTEGER, record_xml TEXT + match_set_id INTEGER, record_xml TEXT, bucket_id INTEGER ) RETURNS SETOF vandelay.match_set_test_result AS $$ DECLARE tags_rstore HSTORE; @@ -534,7 +534,16 @@ BEGIN FROM _vandelay_tmp_jrows; -- add those joins and the where clause to our query. - query_ := query_ || joins || E'\n' || 'JOIN biblio.record_entry bre ON (bre.id = record) ' || 'WHERE ' || wq || ' AND not bre.deleted'; + query_ := query_ || joins || E'\n'; + + -- join the record bucket + IF bucket_id IS NOT NULL THEN + query_ := query_ || 'JOIN container.biblio_record_entry_bucket_item ' || + 'brebi ON (brebi.target_biblio_record_entry = record ' || + 'AND brebi.bucket = ' || bucket_id || E')\n'; + END IF; + + query_ := query_ || 'JOIN biblio.record_entry bre ON (bre.id = record) ' || 'WHERE ' || wq || ' AND not bre.deleted'; -- this will return rows of record,quality FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP @@ -545,9 +554,9 @@ BEGIN DROP TABLE _vandelay_tmp_jrows; RETURN; END; - $$ LANGUAGE PLPGSQL; + CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore( record_xml TEXT ) RETURNS HSTORE AS $func$ @@ -774,6 +783,7 @@ DECLARE test_result vandelay.match_set_test_result%ROWTYPE; tmp_rec BIGINT; match_set INT; + match_bucket INT; BEGIN IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN RETURN NEW; @@ -810,8 +820,10 @@ BEGIN RETURN NEW; END IF; + SELECT q.match_bucket INTO match_bucket FROM vandelay.bib_queue q WHERE q.id = NEW.queue; + FOR test_result IN SELECT * FROM - vandelay.match_set_test_marcxml(match_set, NEW.marc) LOOP + vandelay.match_set_test_marcxml(match_set, NEW.marc, match_bucket) LOOP INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality ) SELECT diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql index 5e65faf348..8edf2986b3 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql @@ -30,7 +30,6 @@ CREATE TABLE config.z3950_index_field_map ( ) ); - -- seed data INSERT INTO config.z3950_index_field_map diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql new file mode 100644 index 0000000000..b4e3caaa30 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql @@ -0,0 +1,135 @@ +BEGIN; + +-- TODO version check + +ALTER TABLE vandelay.bib_queue ADD COLUMN match_bucket + INTEGER REFERENCES container.biblio_record_entry_bucket(id); + +CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$ +DECLARE + incoming_existing_id TEXT; + test_result vandelay.match_set_test_result%ROWTYPE; + tmp_rec BIGINT; + match_set INT; + match_bucket INT; +BEGIN + IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN + RETURN NEW; + END IF; + + DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id; + + SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue; + + IF match_set IS NOT NULL THEN + NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set ); + END IF; + + -- Perfect matches on 901$c exit early with a match with high quality. + incoming_existing_id := + oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc); + + IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN + SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint; + IF tmp_rec IS NOT NULL THEN + INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality) + SELECT + NEW.id, + b.id, + 9999, + -- note: no match_set means quality==0 + vandelay.measure_record_quality( b.marc, match_set ) + FROM biblio.record_entry b + WHERE id = incoming_existing_id::bigint; + END IF; + END IF; + + IF match_set IS NULL THEN + RETURN NEW; + END IF; + + SELECT q.match_bucket INTO match_bucket FROM vandelay.bib_queue q WHERE q.id = NEW.queue; + + FOR test_result IN SELECT * FROM + vandelay.match_set_test_marcxml(match_set, NEW.marc, match_bucket) LOOP + + INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality ) + SELECT + NEW.id, + test_result.record, + test_result.quality, + vandelay.measure_record_quality( b.marc, match_set ) + FROM biblio.record_entry b + WHERE id = test_result.record; + + END LOOP; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + + +DROP FUNCTION IF EXISTS vandelay.match_set_test_marcxml(INTEGER, TEXT); + +CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml( + match_set_id INTEGER, record_xml TEXT, bucket_id INTEGER +) 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, tags_rstore); + + query_ := 'SELECT DISTINCT(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'; + + -- 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'; + + -- join the record bucket + IF bucket_id IS NOT NULL THEN + query_ := query_ || 'JOIN container.biblio_record_entry_bucket_item ' || + 'brebi ON (brebi.target_biblio_record_entry = record ' || + 'AND brebi.bucket = ' || bucket_id || E')\n'; + END IF; + + query_ := query_ || 'JOIN biblio.record_entry bre ON (bre.id = record) ' || '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; diff --git a/Open-ILS/src/templates/vandelay/inc/upload.tt2 b/Open-ILS/src/templates/vandelay/inc/upload.tt2 index 020330622d..bca673e4be 100644 --- a/Open-ILS/src/templates/vandelay/inc/upload.tt2 +++ b/Open-ILS/src/templates/vandelay/inc/upload.tt2 @@ -30,6 +30,11 @@ + [% l('Limit matches to bucket') %] + + + [% l('Holdings Import Profile') %] diff --git a/Open-ILS/web/js/ui/default/vandelay/vandelay.js b/Open-ILS/web/js/ui/default/vandelay/vandelay.js index bc82bca951..00f78e1b9b 100644 --- a/Open-ILS/web/js/ui/default/vandelay/vandelay.js +++ b/Open-ILS/web/js/ui/default/vandelay/vandelay.js @@ -89,6 +89,7 @@ var vlQueueGridColumePicker = {}; var vlBibSources = []; var importItemDefs = []; var matchSets = {biblio : [], authority : []}; +var matchBuckets = {}; var mergeProfiles = []; var copyStatusCache = {}; var copyLocationCache = {}; @@ -208,6 +209,19 @@ function vlInit() { } ); + fieldmapper.standardRequest( + ['open-ils.actor', 'open-ils.actor.container.retrieve_by_class'], + { async : true, + params : [authtoken, new openils.User().user.id(), 'biblio'], + oncomplete : function(r) { + var buckets = openils.Util.readResponse(r); + // only bib buckets are supported + matchBuckets.biblio = buckets; + checkInitDone(); + } + } + ); + new openils.PermaCrud().retrieveAll('ccs', { async: true, oncomplete: function(r) { @@ -417,7 +431,9 @@ function uploadMARC(onload){ /** * Creates a new vandelay queue */ -function createQueue(queueName, type, onload, importDefId, matchSet) { +function createQueue( + queueName, type, onload, importDefId, matchSet, matchBucket) { + var name = (type=='bib') ? 'bib' : 'authority'; var method = 'open-ils.vandelay.'+ name +'_queue.create' @@ -431,7 +447,10 @@ function createQueue(queueName, type, onload, importDefId, matchSet) { fieldmapper.standardRequest( ['open-ils.vandelay', method], { async: true, - params: [authtoken, queueName, null, qType, matchSet, importDefId], + params: [ + authtoken, queueName, null, + qType, matchSet, importDefId, matchBucket + ], oncomplete : function(r) { var queue = r.recv().content(); if(e = openils.Event.parse(queue)) @@ -1421,7 +1440,8 @@ function batchUpload() { } else { createQueue(queueName, currentType, handleCreateQueue, vlUploadQueueHoldingsImportProfile.attr('value'), - vlUploadQueueMatchSet.attr('value') + vlUploadQueueMatchSet.attr('value'), + vlUploadQueueMatchBucket.attr('value') ); } } @@ -1467,11 +1487,13 @@ function vlFleshQueueSelect(selector, type) { vlUploadQueueHoldingsImportProfile.attr('disabled', true); vlUploadQueueMatchSet.attr('value', queue.match_set() || ''); vlUploadQueueMatchSet.attr('disabled', true); + vlUploadQueueMatchBucket.attr('value', queue.match_bucket() || ''); + vlUploadQueueMatchBucket.attr('disabled', true); } else { vlUploadQueueHoldingsImportProfile.attr('value', ''); vlUploadQueueHoldingsImportProfile.attr('disabled', false); - vlUploadQueueMatchSet.attr('value', ''); - vlUploadQueueMatchSet.attr('disabled', false); + vlUploadQueueMatchBucket.attr('value', ''); + vlUploadQueueMatchBucket.attr('disabled', false); } dojo.disconnect(qInput._onchange); qInput.attr('value', ''); @@ -1483,6 +1505,7 @@ function vlFleshQueueSelect(selector, type) { // user entered a new queue name. clear the selector vlUploadQueueHoldingsImportProfile.attr('disabled', false); vlUploadQueueMatchSet.attr('disabled', false); + vlUploadQueueMatchBucket.attr('disabled', false); dojo.disconnect(selector._onchange); selector.attr('value', ''); selector._onchange = dojo.connect(selector, 'onChange', selChange); @@ -1505,6 +1528,19 @@ function vlUpdateMatchSetSelector(type) { } } +function vlUpdateMatchBucketSelector(type) { + type = (type.match(/bib/)) ? 'biblio' : 'authority'; + if (type == 'authority') { + vlUploadQueueMatchBucket.attr('value', ''); + vlUploadQueueMatchBucket.attr('disabled', true); + } else { + vlUploadQueueMatchBucket.attr('disabled', false); + vlUploadQueueMatchBucket.store = + new dojo.data.ItemFileReadStore( + {data:cbreb.toStoreData(matchBuckets[type])}); + } +} + function vlShowUploadForm() { displayGlobalDiv('vl-marc-upload-div'); vlFleshQueueSelect(vlUploadQueueSelector, vlUploadRecordType.getValue()); @@ -1514,6 +1550,7 @@ function vlShowUploadForm() { vlUploadQueueHoldingsImportProfile.store = new dojo.data.ItemFileReadStore({data:viiad.toStoreData(importItemDefs)}); vlUpdateMatchSetSelector(vlUploadRecordType.getValue()); + vlUpdateMatchBucketSelector(vlUploadRecordType.getValue()); if (vlUploadRecordType.attr('value').match(/auth/) || trashGroups.length == 0) { openils.Util.hide('vl-trash-groups-row'); -- 2.43.2