3 SELECT evergreen.upgrade_deps_block_check('0753', :eg_version);
5 CREATE OR REPLACE FUNCTION url_verify.parse_url (url_in TEXT) RETURNS url_verify.url AS $$
10 my $url = Rose::URI->new($url_in);
12 my %parts = map { $_ => $url->$_ } qw/scheme username password host port path query fragment/;
14 $parts{full_url} = $url_in;
15 ($parts{domain} = $parts{host}) =~ s/^[^.]+\.//;
16 ($parts{tld} = $parts{domain}) =~ s/(?:[^.]+\.)+//;
17 ($parts{page} = $parts{path}) =~ s#(?:[^/]*/)+##;
23 CREATE OR REPLACE FUNCTION url_verify.ingest_url () RETURNS TRIGGER AS $$
25 tmp_row url_verify.url%ROWTYPE;
27 SELECT * INTO tmp_row FROM url_verify.parse_url(NEW.full_url);
29 NEW.scheme := tmp_row.scheme;
30 NEW.username := tmp_row.username;
31 NEW.password := tmp_row.password;
32 NEW.host := tmp_row.host;
33 NEW.domain := tmp_row.domain;
34 NEW.tld := tmp_row.tld;
35 NEW.port := tmp_row.port;
36 NEW.path := tmp_row.path;
37 NEW.page := tmp_row.page;
38 NEW.query := tmp_row.query;
39 NEW.fragment := tmp_row.fragment;
45 CREATE TRIGGER ingest_url_tgr
46 BEFORE INSERT ON url_verify.url
47 FOR EACH ROW EXECUTE PROCEDURE url_verify.ingest_url();
49 CREATE OR REPLACE FUNCTION url_verify.extract_urls ( session_id INT, item_id INT ) RETURNS INT AS $$
57 current_selector url_verify.url_selector%ROWTYPE;
61 FOR current_selector IN SELECT * FROM url_verify.url_selector s WHERE s.session = session_id LOOP
64 SELECT (XPATH(current_selector.xpath || '/text()', b.marc::XML))[current_url_pos]::TEXT INTO current_url
65 FROM biblio.record_entry b
66 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
69 EXIT WHEN current_url IS NULL;
71 SELECT (XPATH(current_selector.xpath || '/../@tag', b.marc::XML))[current_url_pos]::TEXT INTO current_tag
72 FROM biblio.record_entry b
73 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
76 IF current_tag IS NULL THEN
77 current_tag := last_seen_tag;
79 last_seen_tag := current_tag;
82 SELECT (XPATH(current_selector.xpath || '/@code', b.marc::XML))[current_url_pos]::TEXT INTO current_sf
83 FROM biblio.record_entry b
84 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
87 INSERT INTO url_verify.url (session, item, url_selector, tag, subfield, ord, full_url)
88 VALUES ( session_id, item_id, current_selector.id, current_tag, current_sf, current_ord, current_url);
90 current_url_pos := current_url_pos + 1;
91 current_ord := current_ord + 1;
95 RETURN current_ord - 1;