From e5c95d36f245919bd378c5f577d92b7b0ced9b14 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 13 Aug 2012 16:10:38 -0400 Subject: [PATCH] Function for ingesting URLs from container item + session Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/076.functions.url_verify.sql | 43 ++++++++++++++++++++++ .../sql/Pg/upgrade/YYYY.functions.url_verify.sql | 42 +++++++++++++++++++++ 2 files changed, 85 insertions(+) diff --git a/Open-ILS/src/sql/Pg/076.functions.url_verify.sql b/Open-ILS/src/sql/Pg/076.functions.url_verify.sql index 23dedab0ab..89478ad6f1 100644 --- a/Open-ILS/src/sql/Pg/076.functions.url_verify.sql +++ b/Open-ILS/src/sql/Pg/076.functions.url_verify.sql @@ -60,5 +60,48 @@ CREATE TRIGGER ingest_url_tgr BEFORE INSERT ON url_verify.url FOR EACH ROW EXECUTE PROCEDURE url_verify.ingest_url(); +CREATE OR REPLACE FUNCTION url_verify.extract_urls ( session_id INT, item_id INT ) RETURNS INT AS $$ +DECLARE + current_tag TEXT; + current_sf TEXT; + current_url TEXT; + current_ord INT; + current_url_pos INT; + current_selector url_verify.url_selector%ROWTYPE; +BEGIN + current_ord := 1; + + FOR current_selector IN SELECT * FROM url_verify.url_selector s WHERE s.session = session_id LOOP + current_url_pos := 1; + LOOP + SELECT (XPATH(current_selector.xpath || '/text()', b.marc))[current_url_pos]::TEXT INTO current_url + FROM biblio.record_entry b + JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id) + WHERE c.id = item_id; + + EXIT WHEN current_url IS NULL; + + SELECT (XPATH(current_selector.xpath || '/../@tag', b.marc))[current_url_pos]::TEXT INTO current_tag + FROM biblio.record_entry b + JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id) + WHERE c.id = item_id; + + SELECT (XPATH(current_selector.xpath || '/@subfield', b.marc))[current_url_pos]::TEXT INTO current_sf + FROM biblio.record_entry b + JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id) + WHERE c.id = item_id; + + INSERT INTO url_verify.url (item, url_selector, tag, subfield, ord, full_url) + VALUES ( item_id, current_selector.id, current_tag, current_sf, current_ord, current_url); + + current_url_pos := current_url_pos + 1; + current_ord := current_ord + 1; + END LOOP; + END LOOP; + + RETURN current_ord - 1; +END; +$$ LANGUAGE PLPGSQL; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.functions.url_verify.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.functions.url_verify.sql index c63c2d9756..2990382a49 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.functions.url_verify.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.functions.url_verify.sql @@ -44,6 +44,48 @@ CREATE TRIGGER ingest_url_tgr BEFORE INSERT ON url_verify.url FOR EACH ROW EXECUTE PROCEDURE url_verify.ingest_url(); +CREATE OR REPLACE FUNCTION url_verify.extract_urls ( session_id INT, item_id INT ) RETURNS INT AS $$ +DECLARE + current_tag TEXT; + current_sf TEXT; + current_url TEXT; + current_ord INT; + current_url_pos INT; + current_selector url_verify.url_selector%ROWTYPE; +BEGIN + current_ord := 1; + + FOR current_selector IN SELECT * FROM url_verify.url_selector s WHERE s.session = session_id LOOP + current_url_pos := 1; + LOOP + SELECT (XPATH(current_selector.xpath || '/text()', b.marc))[current_url_pos]::TEXT INTO current_url + FROM biblio.record_entry b + JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id) + WHERE c.id = item_id; + + EXIT WHEN current_url IS NULL; + + SELECT (XPATH(current_selector.xpath || '/../@tag', b.marc))[current_url_pos]::TEXT INTO current_tag + FROM biblio.record_entry b + JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id) + WHERE c.id = item_id; + + SELECT (XPATH(current_selector.xpath || '/@subfield', b.marc))[current_url_pos]::TEXT INTO current_sf + FROM biblio.record_entry b + JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id) + WHERE c.id = item_id; + + INSERT INTO url_verify.url (item, url_selector, tag, subfield, ord, full_url) + VALUES ( item_id, current_selector.id, current_tag, current_sf, current_ord, current_url); + + current_url_pos := current_url_pos + 1; + current_ord := current_ord + 1; + END LOOP; + END LOOP; + + RETURN current_ord - 1; +END; +$$ LANGUAGE PLPGSQL; COMMIT; -- 2.11.0