3 SELECT evergreen.upgrade_deps_block_check('1311', :eg_version);
5 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
12 uri_owner_list TEXT[];
21 current_uri_map_list INT[];
22 current_map_owner_list INT[];
26 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
27 IF ARRAY_UPPER(uris,1) > 0 THEN
28 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
29 -- First we pull info out of the 856
32 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
33 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
34 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
36 IF uri_label IS NULL THEN
37 uri_label := uri_href;
39 CONTINUE WHEN uri_href IS NULL;
41 -- Get the distinct list of libraries wanting to use
43 DISTINCT REGEXP_REPLACE(
45 $re$^.*?\((\w+)\).*$$re$,
51 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
56 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
58 -- look for a matching uri
59 IF uri_use IS NULL THEN
62 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
64 IF NOT FOUND THEN -- create one
65 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
68 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
73 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use 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 = uri_use AND active;
83 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
84 uri_owner := uri_owner_list[j];
86 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = BTRIM(REPLACE(uri_owner,chr(160),''));
87 CONTINUE WHEN NOT FOUND;
89 -- we need a call number to link through
90 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;
92 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
93 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
94 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;
97 -- now, link them if they're not already
98 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
100 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
101 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
104 current_uri_map_list := current_uri_map_list || uri_map_id;
105 current_map_owner_list := current_map_owner_list || uri_cn_id;
114 -- Clear any orphaned URIs, URI mappings and call
115 -- numbers for this bib that weren't mapped above.
118 FROM asset.uri_call_number_map m
119 LEFT JOIN asset.call_number cn ON (cn.id = m.call_number)
120 WHERE cn.record = bib_id
121 AND cn.label = '##URI##'
122 AND (NOT (m.id = ANY (current_uri_map_list))
123 OR current_uri_map_list is NULL)
125 SELECT uri INTO current_uri FROM asset.uri_call_number_map WHERE id = current_map;
126 DELETE FROM asset.uri_call_number_map WHERE id = current_map;
128 SELECT COUNT(*) INTO uri_map_count FROM asset.uri_call_number_map WHERE uri = current_uri;
129 IF uri_map_count = 0 THEN
130 DELETE FROM asset.uri WHERE id = current_uri;
134 UPDATE asset.call_number
135 SET deleted = TRUE, edit_date = now(), editor = editor_id
138 FROM asset.call_number
139 WHERE record = bib_id
140 AND label = '##URI##'
142 AND (NOT (id = ANY (current_map_owner_list))
143 OR current_map_owner_list is NULL)
148 $func$ LANGUAGE PLPGSQL;
150 -- Remove existing orphaned URIs from the database.
151 DELETE FROM asset.uri
156 LEFT JOIN asset.uri_call_number_map
157 ON uri_call_number_map.uri = uri.id
158 LEFT JOIN serial.item
160 WHERE uri_call_number_map IS NULL