3 INSERT INTO config.upgrade_log (version) VALUES ('2.0.7');
5 INSERT INTO config.upgrade_log (version) VALUES ('0534'); --gmc
6 -- Superseded below, but keep the number in the log
8 INSERT INTO config.upgrade_log (version) VALUES ('0535'); --dbs
10 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
12 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
14 INSERT INTO config.upgrade_log (version) VALUES ('0538'); -- senator
16 UPDATE action_trigger.event_definition
17 SET template = '[% FILTER collapse %]' || template
19 SUBSTR(template, 0, 24) NOT LIKE '%FILTER collapse%';
21 -- Bring serial.unit into line with asset.copy
22 INSERT INTO config.upgrade_log (version) VALUES ('0540'); -- dbwells
24 CREATE TRIGGER sunit_status_changed_trig
25 BEFORE UPDATE ON serial.unit
26 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
28 SELECT auditor.create_auditor ( 'serial', 'unit' );
29 CREATE INDEX aud_serial_unit_hist_creator_idx ON auditor.serial_unit_history ( creator );
30 CREATE INDEX aud_serial_unit_hist_editor_idx ON auditor.serial_unit_history ( editor );
32 INSERT INTO config.upgrade_log (version) VALUES ('0541'); -- dbwells
34 ALTER TABLE asset.call_number ALTER COLUMN label_class DROP DEFAULT;
36 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
40 sortkey := NEW.label_sortkey;
42 IF NEW.label_class IS NULL THEN
43 NEW.label_class := COALESCE(
45 SELECT substring(value from E'\\d+')::integer
46 FROM actor.org_unit_setting
47 WHERE name = 'cat.default_classification_scheme'
48 AND org_unit = NEW.owning_lib
53 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
54 quote_literal( NEW.label ) || ')'
55 FROM asset.call_number_class acnc
56 WHERE acnc.id = NEW.label_class
58 NEW.label_sortkey = sortkey;
61 $func$ LANGUAGE PLPGSQL;
63 -- Reformat generated_coverage to be JSON arrays rather than simple comma-
66 -- This upgrade script is technically imperfect, but should do the right thing
67 -- in 99.9% of cases, and any mistakes will be self-healing as more serials
70 INSERT INTO config.upgrade_log (version) VALUES ('0543'); -- dbwells
72 UPDATE serial.basic_summary SET generated_coverage = '["' || regexp_replace(regexp_replace(generated_coverage, '"', E'\\"', 'g'), ', ', '","', 'g') || '"]' WHERE generated_coverage <> '';
74 UPDATE serial.supplement_summary SET generated_coverage = '["' || regexp_replace(regexp_replace(generated_coverage, '"', E'\\"', 'g'), ', ', '","', 'g') || '"]' WHERE generated_coverage <> '';
76 UPDATE serial.index_summary SET generated_coverage = '["' || regexp_replace(regexp_replace(generated_coverage, '"', E'\\"', 'g'), ', ', '","', 'g') || '"]' WHERE generated_coverage <> '';
78 -- Evergreen DB patch 0551.unnest_metabib_remap_metarecord_for_bib.sql
80 -- Replace usage of custom explode_array() function with native unnest()
83 INSERT INTO config.upgrade_log (version) VALUES ('0551'); -- dbs
85 CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT ) RETURNS BIGINT AS $func$
89 tmp_mr metabib.metarecord%ROWTYPE;
93 DELETE FROM metabib.metarecord_source_map WHERE source = bib_id; -- Rid ourselves of the search-estimate-killing linkage
95 FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP
97 IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching
100 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
101 IF source_count = 0 THEN -- No other records
102 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
103 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
109 IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps
110 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
111 IF old_mr IS NULL THEN -- nope, create one and grab its id
112 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
113 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
114 ELSE -- indeed there is. update it with a null cache and recalcualated master record
115 UPDATE metabib.metarecord
117 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1)
120 ELSE -- there was one we already attached to, update its mods cache and master_record
121 UPDATE metabib.metarecord
123 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1)
127 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
129 IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
130 UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved
136 $func$ LANGUAGE PLPGSQL;
138 -- Evergreen DB patch 0552.unnest_biblio_map_authority_linking.sql
140 -- Replace usage of custom explode_array() function with native unnest()
143 INSERT INTO config.upgrade_log (version) VALUES ('0552'); -- dbs
145 CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$
146 DELETE FROM authority.bib_linking WHERE bib = $1;
147 INSERT INTO authority.bib_linking (bib, authority)
150 FROM ( SELECT DISTINCT $1 AS bib,
151 BTRIM(remove_paren_substring(txt))::BIGINT AS authority
152 FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt)
153 WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$
154 ) y JOIN authority.record_entry r ON r.id = y.authority;
158 -- Evergreen DB patch 0553.unnest_action_hold_request_permit_test.sql
160 -- Replace usage of custom explode_array() function with native unnest()
163 INSERT INTO config.upgrade_log (version) VALUES ('0553'); -- dbs
165 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
168 user_object actor.usr%ROWTYPE;
169 age_protect_object config.rule_age_hold_protect%ROWTYPE;
170 standing_penalty config.standing_penalty%ROWTYPE;
171 transit_range_ou_type actor.org_unit_type%ROWTYPE;
172 transit_source actor.org_unit%ROWTYPE;
173 item_object asset.copy%ROWTYPE;
174 item_cn_object asset.call_number%ROWTYPE;
175 ou_skip actor.org_unit_setting%ROWTYPE;
176 result action.matrix_test_result;
177 hold_test config.hold_matrix_matchpoint%ROWTYPE;
179 hold_transit_prox INT;
180 frozen_hold_count INT;
181 context_org_list INT[];
184 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
185 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
187 result.success := TRUE;
189 -- Fail if we couldn't find a user
190 IF user_object.id IS NULL THEN
191 result.fail_part := 'no_user';
192 result.success := FALSE;
198 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
200 -- Fail if we couldn't find a copy
201 IF item_object.id IS NULL THEN
202 result.fail_part := 'no_item';
203 result.success := FALSE;
209 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
210 result.matchpoint := matchpoint_id;
212 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
214 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
215 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
216 result.fail_part := 'circ.holds.target_skip_me';
217 result.success := FALSE;
223 -- Fail if user is barred
224 IF user_object.barred IS TRUE THEN
225 result.fail_part := 'actor.usr.barred';
226 result.success := FALSE;
232 -- Fail if we couldn't find any matchpoint (requires a default)
233 IF matchpoint_id IS NULL THEN
234 result.fail_part := 'no_matchpoint';
235 result.success := FALSE;
241 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
243 IF hold_test.holdable IS FALSE THEN
244 result.fail_part := 'config.hold_matrix_test.holdable';
245 result.success := FALSE;
250 IF hold_test.transit_range IS NOT NULL THEN
251 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
252 IF hold_test.distance_is_from_owner THEN
253 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
255 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
258 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
261 result.fail_part := 'transit_range';
262 result.success := FALSE;
268 FOR standing_penalty IN
269 SELECT DISTINCT csp.*
270 FROM actor.usr_standing_penalty usp
271 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
272 WHERE usr = match_user
273 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
274 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
275 AND csp.block_list LIKE '%HOLD%' LOOP
277 result.fail_part := standing_penalty.name;
278 result.success := FALSE;
283 IF hold_test.stop_blocked_user IS TRUE THEN
284 FOR standing_penalty IN
285 SELECT DISTINCT csp.*
286 FROM actor.usr_standing_penalty usp
287 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
288 WHERE usr = match_user
289 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
290 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
291 AND csp.block_list LIKE '%CIRC%' LOOP
293 result.fail_part := standing_penalty.name;
294 result.success := FALSE;
300 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
301 SELECT INTO hold_count COUNT(*)
302 FROM action.hold_request
303 WHERE usr = match_user
304 AND fulfillment_time IS NULL
305 AND cancel_time IS NULL
306 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
308 IF hold_count >= hold_test.max_holds THEN
309 result.fail_part := 'config.hold_matrix_test.max_holds';
310 result.success := FALSE;
316 IF item_object.age_protect IS NOT NULL THEN
317 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
319 IF item_object.create_date + age_protect_object.age > NOW() THEN
320 IF hold_test.distance_is_from_owner THEN
321 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
322 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
324 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
327 IF hold_transit_prox > age_protect_object.prox THEN
328 result.fail_part := 'config.rule_age_hold_protect.prox';
329 result.success := FALSE;
342 $func$ LANGUAGE plpgsql;
344 -- Evergreen DB patch 0554.unnest_search_query_parser_fts.sql
346 -- Replace usage of custom explode_array() function with native unnest()
349 INSERT INTO config.upgrade_log (version) VALUES ('0554'); -- dbs
351 CREATE OR REPLACE FUNCTION search.query_parser_fts (
356 param_statuses INT[],
357 param_locations INT[],
364 ) RETURNS SETOF search.search_result AS $func$
367 current_res search.search_result%ROWTYPE;
368 search_org_list INT[];
376 core_cursor REFCURSOR;
379 total_count INT := 0;
380 check_count INT := 0;
381 deleted_count INT := 0;
382 visible_count INT := 0;
383 excluded_count INT := 0;
387 check_limit := COALESCE( param_check, 1000 );
388 core_limit := COALESCE( param_limit, 25000 );
389 core_offset := COALESCE( param_offset, 0 );
391 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
393 IF param_search_ou > 0 THEN
394 IF param_depth IS NOT NULL THEN
395 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
397 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
399 ELSIF param_search_ou < 0 THEN
400 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
401 ELSIF param_search_ou = 0 THEN
402 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
405 OPEN core_cursor FOR EXECUTE param_query;
409 FETCH core_cursor INTO core_result;
411 EXIT WHEN total_count >= core_limit;
413 total_count := total_count + 1;
415 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
417 check_count := check_count + 1;
419 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
421 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
422 deleted_count := deleted_count + 1;
427 FROM biblio.record_entry b
428 JOIN config.bib_source s ON (b.source = s.id)
430 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
433 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
434 visible_count := visible_count + 1;
436 current_res.id = core_result.id;
437 current_res.rel = core_result.rel;
441 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
445 current_res.record = core_result.records[1];
447 current_res.record = NULL;
450 RETURN NEXT current_res;
456 FROM asset.call_number cn
457 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
458 JOIN asset.uri uri ON (map.uri = uri.id)
460 AND cn.label = '##URI##'
462 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
463 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
464 AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) )
468 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
469 visible_count := visible_count + 1;
471 current_res.id = core_result.id;
472 current_res.rel = core_result.rel;
476 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
480 current_res.record = core_result.records[1];
482 current_res.record = NULL;
485 RETURN NEXT current_res;
490 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
493 FROM asset.call_number cn
494 JOIN asset.copy cp ON (cp.call_number = cn.id)
497 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
498 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
499 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
503 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
504 excluded_count := excluded_count + 1;
510 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
513 FROM asset.call_number cn
514 JOIN asset.copy cp ON (cp.call_number = cn.id)
517 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
518 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
519 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
523 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
524 excluded_count := excluded_count + 1;
530 IF staff IS NULL OR NOT staff THEN
533 FROM asset.opac_visible_copies
534 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
535 AND record IN ( SELECT * FROM unnest( core_result.records ) )
539 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
540 excluded_count := excluded_count + 1;
547 FROM asset.call_number cn
548 JOIN asset.copy cp ON (cp.call_number = cn.id)
551 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
552 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
558 FROM asset.call_number cn
559 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
563 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
564 excluded_count := excluded_count + 1;
572 visible_count := visible_count + 1;
574 current_res.id = core_result.id;
575 current_res.rel = core_result.rel;
579 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
583 current_res.record = core_result.records[1];
585 current_res.record = NULL;
588 RETURN NEXT current_res;
590 IF visible_count % 1000 = 0 THEN
591 -- RAISE NOTICE ' % visible so far ... ', visible_count;
596 current_res.id = NULL;
597 current_res.rel = NULL;
598 current_res.record = NULL;
599 current_res.total = total_count;
600 current_res.checked = check_count;
601 current_res.deleted = deleted_count;
602 current_res.visible = visible_count;
603 current_res.excluded = excluded_count;
607 RETURN NEXT current_res;
610 $func$ LANGUAGE PLPGSQL;
612 -- Evergreen DB patch 0555.unnest_oils_xpath_table.sql
614 -- Replace usage of custom explode_array() function with native unnest()
617 INSERT INTO config.upgrade_log (version) VALUES ('0555'); -- dbs
619 CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$
628 xpath_list := STRING_TO_ARRAY( xpaths, '|' );
630 select_list := ARRAY_APPEND( select_list, key || '::INT AS key' );
632 FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP
633 IF xpath_list[i] = 'null()' THEN
634 select_list := ARRAY_APPEND( select_list, 'NULL::TEXT AS c_' || i );
636 select_list := ARRAY_APPEND(
646 WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i]
647 ELSE xpath_list[i] || '//text()'
651 $sel$ || document_field || $sel$
659 where_list := ARRAY_APPEND(
661 'c_' || i || ' IS NOT NULL'
668 SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$)
669 )x WHERE $q$ || ARRAY_TO_STRING( where_list, ' OR ' );
670 -- RAISE NOTICE 'query: %', q;
672 FOR out_record IN EXECUTE q LOOP
673 RETURN NEXT out_record;
678 $func$ LANGUAGE PLPGSQL IMMUTABLE;
680 -- Evergreen DB patch 0556.unnest_biblio_extract_metabib_field_entry.sql
682 -- Replace usage of custom explode_array() function with native unnest()
685 INSERT INTO config.upgrade_log (version) VALUES ('0556'); -- dbs
687 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
689 bib biblio.record_entry%ROWTYPE;
690 idx config.metabib_field%ROWTYPE;
691 xfrm config.xml_transform%ROWTYPE;
693 transformed_xml TEXT;
695 xml_node_list TEXT[];
699 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
700 output_row metabib.field_entry_template%ROWTYPE;
704 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
706 -- Loop over the indexing entries
707 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
709 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
711 -- See if we can skip the XSLT ... it's expensive
712 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
713 -- Can't skip the transform
714 IF xfrm.xslt <> '---' THEN
715 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
717 transformed_xml := bib.marc;
720 prev_xfrm := xfrm.name;
723 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
726 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
727 CONTINUE WHEN xml_node !~ E'^\\s*<';
729 curr_text := ARRAY_TO_STRING(
730 oils_xpath( '//text()',
731 REGEXP_REPLACE( -- This escapes all &s not followed by "amp;". Data ise returned from oils_xpath (above) in UTF-8, not entity encoded
732 REGEXP_REPLACE( -- This escapes embeded <s
734 $re$(>[^<]+)(<)([^>]+<)$re$,
746 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
748 IF raw_text IS NOT NULL THEN
749 raw_text := raw_text || joiner;
752 raw_text := COALESCE(raw_text,'') || curr_text;
754 -- insert raw node text for faceting
755 IF idx.facet_field THEN
757 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
758 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
760 facet_text := curr_text;
763 output_row.field_class = idx.field_class;
764 output_row.field = -1 * idx.id;
765 output_row.source = rid;
766 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
768 RETURN NEXT output_row;
773 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
775 -- insert combined node text for searching
776 IF idx.search_field THEN
777 output_row.field_class = idx.field_class;
778 output_row.field = idx.id;
779 output_row.source = rid;
780 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
782 RETURN NEXT output_row;
788 $func$ LANGUAGE PLPGSQL;
790 -- Evergreen DB patch 0559.schema.biblio.extract_located_uris.sql
792 -- * Add a stored procedure to reingest problematic URIs
793 -- * Avoid duplicate row issues in biblio.extract_located_uris
794 -- * Fix LP 797304 and 797307 - asset.uri parsing bugs
798 -- check whether patch can be applied
799 INSERT INTO config.upgrade_log (version) VALUES ('0559'); -- dbs
801 -- FIXME: add/check SQL statements to perform the upgrade
802 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
809 uri_owner_list TEXT[];
817 -- Clear any URI mappings and call numbers for this bib.
818 -- This leads to acn / auricnm inflation, but also enables
819 -- old acn/auricnm's to go away and for bibs to be deleted.
820 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
821 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
822 DELETE FROM asset.call_number WHERE id = uri_cn_id;
825 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
826 IF ARRAY_UPPER(uris,1) > 0 THEN
827 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
828 -- First we pull info out of the 856
831 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
832 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
833 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
835 IF uri_label IS NULL THEN
836 uri_label := uri_href;
838 CONTINUE WHEN uri_href IS NULL;
840 -- Get the distinct list of libraries wanting to use
842 DISTINCT REGEXP_REPLACE(
844 $re$^.*?\((\w+)\).*$$re$,
847 ) INTO uri_owner_list
850 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
855 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
857 -- look for a matching uri
858 IF uri_use IS NULL THEN
859 SELECT id INTO uri_id
861 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
863 IF NOT FOUND THEN -- create one
864 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
865 SELECT id INTO uri_id
867 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
870 SELECT id INTO uri_id
872 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
874 IF NOT FOUND THEN -- create one
875 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
876 SELECT id INTO uri_id
878 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
882 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
883 uri_owner := uri_owner_list[j];
885 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
886 CONTINUE WHEN NOT FOUND;
888 -- we need a call number to link through
889 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;
891 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
892 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
893 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;
896 -- now, link them if they're not already
897 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
899 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
911 $func$ LANGUAGE PLPGSQL;
913 CREATE OR REPLACE FUNCTION biblio.reingest_uris() RETURNS VOID AS $func$
917 -- Get the distinct set of record IDs that need to be reingested
918 -- (assuming that href = label is a reasonable red flag)
919 FOR rec_id IN SELECT rec_uris.id FROM (
920 SELECT acn.record AS id
921 FROM asset.call_number acn
922 INNER JOIN asset.uri_call_number_map auricnm ON auricnm.call_number = acn.id
923 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
924 INNER JOIN biblio.record_entry bre ON acn.record = bre.id
925 WHERE auri.href = auri.label
926 AND xml_is_well_formed(bre.marc)
931 -- Reingest the offending records
932 PERFORM biblio.extract_located_uris(rec_id, bre.marc, 1)
933 FROM biblio.record_entry bre
934 WHERE bre.id = rec_id;
937 $func$ LANGUAGE PLPGSQL;
939 -- Kick off the reingest; this may take a while
940 SELECT biblio.reingest_uris();
942 -- Hopefully this isn't something we'll need to run again
943 DROP FUNCTION biblio.reingest_uris();
945 INSERT INTO config.upgrade_log (version) VALUES ('0561'); -- miker
947 CREATE INDEX metabib_full_rec_tnf_idx ON metabib.real_full_rec (record, tag, subfield) WHERE tag = 'tnf' AND subfield = 'a';