Link checker: DB layer fixes

[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / YYYY.functions.url_verify.sql

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 c63c2d9..2990382 100644 (file)
--- 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;