1 -- Evergreen DB patch 0559.schema.biblio.extract_located_uris.sql
3 -- * Add a stored procedure to reingest problematic URIs
4 -- * Avoid duplicate row issues in biblio.extract_located_uris
5 -- * Fix LP 797304 and 797307 - asset.uri parsing bugs
10 -- check whether patch can be applied
11 SELECT evergreen.upgrade_deps_block_check('0559', :eg_version);
13 -- FIXME: add/check SQL statements to perform the upgrade
14 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
21 uri_owner_list TEXT[];
29 -- Clear any URI mappings and call numbers for this bib.
30 -- This leads to acn / auricnm inflation, but also enables
31 -- old acn/auricnm's to go away and for bibs to be deleted.
32 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
33 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
34 DELETE FROM asset.call_number WHERE id = uri_cn_id;
37 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
38 IF ARRAY_UPPER(uris,1) > 0 THEN
39 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
40 -- First we pull info out of the 856
43 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
44 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
45 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
47 IF uri_label IS NULL THEN
48 uri_label := uri_href;
50 CONTINUE WHEN uri_href IS NULL;
52 -- Get the distinct list of libraries wanting to use
54 DISTINCT REGEXP_REPLACE(
56 $re$^.*?\((\w+)\).*$$re$,
62 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
67 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
69 -- look for a matching uri
70 IF uri_use IS NULL THEN
73 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
75 IF NOT FOUND THEN -- create one
76 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
79 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
84 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
86 IF NOT FOUND THEN -- create one
87 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
90 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
94 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
95 uri_owner := uri_owner_list[j];
97 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
98 CONTINUE WHEN NOT FOUND;
100 -- we need a call number to link through
101 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
103 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
104 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
105 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
108 -- now, link them if they're not already
109 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
111 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
123 $func$ LANGUAGE PLPGSQL;
125 CREATE OR REPLACE FUNCTION biblio.reingest_uris() RETURNS VOID AS $func$
129 -- Get the distinct set of record IDs that need to be reingested
130 -- (assuming that href = label is a reasonable red flag)
131 FOR rec_id IN SELECT rec_uris.id FROM (
132 SELECT acn.record AS id
133 FROM asset.call_number acn
134 INNER JOIN asset.uri_call_number_map auricnm ON auricnm.call_number = acn.id
135 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
136 INNER JOIN biblio.record_entry bre ON acn.record = bre.id
137 WHERE auri.href = auri.label
138 AND xml_is_well_formed(bre.marc)
143 -- Reingest the offending records
144 PERFORM biblio.extract_located_uris(rec_id, bre.marc, 1)
145 FROM biblio.record_entry bre
146 WHERE bre.id = rec_id;
149 $func$ LANGUAGE PLPGSQL;
151 -- Kick off the reingest; this may take a while
152 SELECT biblio.reingest_uris();
154 -- Hopefully this isn't something we'll need to run again
155 DROP FUNCTION biblio.reingest_uris();