1 -- Compiled list of all changed functions and views where we went from:
2 -- array_accum() to array_agg()
3 -- array_to_string(array_agg()) to string_agg()
7 SELECT evergreen.upgrade_deps_block_check('0855', :eg_version);
9 -- from 000.functions.general.sql
11 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL;
14 -- from 002.functions.config.sql
16 CREATE OR REPLACE FUNCTION public.extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
17 SELECT regexp_replace(string_agg(output,' '),$4,'','g') FROM oils_xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT);
21 -- from 011.schema.authority.sql
23 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
24 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
27 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
28 SELECT ARRAY_AGG(y) from (
29 SELECT unnest(ARRAY_CAT(
31 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
33 FROM authority.browse_axis_authority_field_map a
37 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
38 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
41 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
42 SELECT ARRAY_AGG(y) from (
43 SELECT unnest(ARRAY_CAT(
44 ARRAY[a.authority_field],
45 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
47 FROM authority.control_set_bib_field a
51 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
52 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
55 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
56 SELECT ARRAY_AGG(y) from (
57 SELECT unnest(ARRAY_CAT(
59 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
61 FROM authority.control_set_authority_field a
66 -- from 012.schema.vandelay.sql
68 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
73 xfrm config.xml_transform%ROWTYPE;
75 new_attrs HSTORE := ''::HSTORE;
76 attr_def config.record_attr_definition%ROWTYPE;
79 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
81 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
82 SELECT STRING_AGG(x.value, COALESCE(attr_def.joiner,' ')) INTO attr_value
83 FROM vandelay.flatten_marc(xml) AS x
84 WHERE x.tag LIKE attr_def.tag
86 WHEN attr_def.sf_list IS NOT NULL
87 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
94 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
95 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
97 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
99 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
101 -- See if we can skip the XSLT ... it's expensive
102 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
103 -- Can't skip the transform
104 IF xfrm.xslt <> '---' THEN
105 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
107 transformed_xml := xml;
110 prev_xfrm := xfrm.name;
113 IF xfrm.name IS NULL THEN
114 -- just grab the marcxml (empty) transform
115 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
116 prev_xfrm := xfrm.name;
119 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
121 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
122 SELECT m.value::TEXT INTO attr_value
123 FROM vandelay.marc21_physical_characteristics(xml) v
124 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
125 WHERE v.subfield = attr_def.phys_char_sf
126 LIMIT 1; -- Just in case ...
130 -- apply index normalizers to attr_value
132 SELECT n.func AS func,
133 n.param_count AS param_count,
135 FROM config.index_normalizer n
136 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
137 WHERE attr = attr_def.name
139 EXECUTE 'SELECT ' || normalizer.func || '(' ||
140 quote_nullable( attr_value ) ||
142 WHEN normalizer.param_count > 0
143 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
150 -- Add the new value to the hstore
151 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
157 $_$ LANGUAGE PLPGSQL;
159 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
160 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_AGG(name) FROM config.record_attr_definition));
163 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
164 match_set_id INTEGER, record_xml TEXT, bucket_id INTEGER
165 ) RETURNS SETOF vandelay.match_set_test_result AS $$
176 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
177 svf_rstore := vandelay.extract_rec_attrs(record_xml);
179 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
180 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
182 -- generate the where clause and return that directly (into wq), and as
183 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
184 wq := vandelay.get_expr_from_match_set(match_set_id, tags_rstore);
186 query_ := 'SELECT DISTINCT(record), ';
188 -- qrows table is for the quality bits we add to the SELECT clause
190 'COALESCE(n' || q::TEXT || '.quality, 0)', ' + '
191 ) INTO coal FROM _vandelay_tmp_qrows;
193 -- our query string so far is the SELECT clause and the inital FROM.
194 -- no JOINs yet nor the WHERE clause
195 query_ := query_ || coal || ' AS quality ' || E'\n';
197 -- jrows table is for the joins we must make (and the real text conditions)
198 SELECT STRING_AGG(j, E'\n') INTO joins
199 FROM _vandelay_tmp_jrows;
201 -- add those joins and the where clause to our query.
202 query_ := query_ || joins || E'\n';
204 -- join the record bucket
205 IF bucket_id IS NOT NULL THEN
206 query_ := query_ || 'JOIN container.biblio_record_entry_bucket_item ' ||
207 'brebi ON (brebi.target_biblio_record_entry = record ' ||
208 'AND brebi.bucket = ' || bucket_id || E')\n';
211 query_ := query_ || 'JOIN biblio.record_entry bre ON (bre.id = record) ' || 'WHERE ' || wq || ' AND not bre.deleted';
213 -- this will return rows of record,quality
214 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
218 DROP TABLE _vandelay_tmp_qrows;
219 DROP TABLE _vandelay_tmp_jrows;
224 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
226 ) RETURNS HSTORE AS $func$
230 ARRAY_AGG(tag || (COALESCE(subfield, ''))),
234 SELECT tag, subfield, ARRAY_AGG(value)::TEXT AS value
237 CASE WHEN tag = '020' THEN -- caseless -- isbn
238 LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%')
239 WHEN tag = '022' THEN -- caseless -- issn
240 LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
241 WHEN tag = '024' THEN -- caseless -- upc (other)
246 FROM vandelay.flatten_marc(record_xml)) x
247 GROUP BY tag, subfield ORDER BY tag, subfield
251 $func$ LANGUAGE PLPGSQL;
253 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
254 node vandelay.match_set_point,
262 child vandelay.match_set_point;
264 SELECT ARRAY_AGG(id) INTO children FROM vandelay.match_set_point
265 WHERE parent = node.id;
267 IF ARRAY_LENGTH(children, 1) > 0 THEN
268 this_op := vandelay._get_expr_render_one(node);
271 WHILE children[i] IS NOT NULL LOOP
272 SELECT * INTO child FROM vandelay.match_set_point
273 WHERE id = children[i];
275 q := q || ' ' || this_op || ' ';
278 q := q || vandelay.get_expr_from_match_set_point(child, tags_rstore);
282 ELSIF node.bool_op IS NULL THEN
283 PERFORM vandelay._get_expr_push_qrow(node);
284 PERFORM vandelay._get_expr_push_jrow(node, tags_rstore);
285 RETURN vandelay._get_expr_render_one(node);
293 -- from 030.schema.metabib.sql
295 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
302 uri_owner_list TEXT[];
310 -- Clear any URI mappings and call numbers for this bib.
311 -- This leads to acn / auricnm inflation, but also enables
312 -- old acn/auricnm's to go away and for bibs to be deleted.
313 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
314 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
315 DELETE FROM asset.call_number WHERE id = uri_cn_id;
318 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
319 IF ARRAY_UPPER(uris,1) > 0 THEN
320 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
321 -- First we pull info out of the 856
324 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
325 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
326 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
328 IF uri_label IS NULL THEN
329 uri_label := uri_href;
331 CONTINUE WHEN uri_href IS NULL;
333 -- Get the distinct list of libraries wanting to use
335 DISTINCT REGEXP_REPLACE(
337 $re$^.*?\((\w+)\).*$$re$,
340 ) INTO uri_owner_list
343 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
348 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
350 -- look for a matching uri
351 IF uri_use IS NULL THEN
352 SELECT id INTO uri_id
354 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
356 IF NOT FOUND THEN -- create one
357 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
358 SELECT id INTO uri_id
360 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
363 SELECT id INTO uri_id
365 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
367 IF NOT FOUND THEN -- create one
368 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
369 SELECT id INTO uri_id
371 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
375 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
376 uri_owner := uri_owner_list[j];
378 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
379 CONTINUE WHEN NOT FOUND;
381 -- we need a call number to link through
382 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;
384 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
385 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
386 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;
389 -- now, link them if they're not already
390 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
392 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
404 $func$ LANGUAGE PLPGSQL;
406 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
408 transformed_xml TEXT;
411 xfrm config.xml_transform%ROWTYPE;
413 new_attrs HSTORE := ''::HSTORE;
414 attr_def config.record_attr_definition%ROWTYPE;
417 IF NEW.deleted IS TRUE THEN -- If this bib is deleted
418 PERFORM * FROM config.internal_flag WHERE
419 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
421 -- One needs to keep these around to support searches
422 -- with the #deleted modifier, so one should turn on the named
423 -- internal flag for that functionality.
424 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id;
425 DELETE FROM metabib.record_attr WHERE id = NEW.id;
428 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
429 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
430 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
431 RETURN NEW; -- and we're done
434 IF TG_OP = 'UPDATE' THEN -- re-ingest?
435 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
437 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
442 -- Record authority linking
443 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
445 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
448 -- Flatten and insert the mfr data
449 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
451 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
453 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
454 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
456 FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
458 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
459 SELECT STRING_AGG(value, COALESCE(attr_def.joiner,' ')) INTO attr_value
460 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
461 WHERE record = NEW.id
462 AND tag LIKE attr_def.tag
464 WHEN attr_def.sf_list IS NOT NULL
465 THEN POSITION(subfield IN attr_def.sf_list) > 0
472 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
473 attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
475 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
477 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
479 -- See if we can skip the XSLT ... it's expensive
480 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
481 -- Can't skip the transform
482 IF xfrm.xslt <> '---' THEN
483 transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
485 transformed_xml := NEW.marc;
488 prev_xfrm := xfrm.name;
491 IF xfrm.name IS NULL THEN
492 -- just grab the marcxml (empty) transform
493 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
494 prev_xfrm := xfrm.name;
497 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
499 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
500 SELECT m.value INTO attr_value
501 FROM biblio.marc21_physical_characteristics(NEW.id) v
502 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
503 WHERE v.subfield = attr_def.phys_char_sf
504 LIMIT 1; -- Just in case ...
508 -- apply index normalizers to attr_value
510 SELECT n.func AS func,
511 n.param_count AS param_count,
513 FROM config.index_normalizer n
514 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
515 WHERE attr = attr_def.name
517 EXECUTE 'SELECT ' || normalizer.func || '(' ||
518 COALESCE( quote_literal( attr_value ), 'NULL' ) ||
520 WHEN normalizer.param_count > 0
521 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
528 -- Add the new value to the hstore
529 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
533 IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
534 DELETE FROM metabib.record_attr WHERE id = NEW.id;
535 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
537 UPDATE metabib.record_attr SET attrs = new_attrs WHERE id = NEW.id;
543 -- Gather and insert the field entry data
544 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
547 IF TG_OP = 'INSERT' THEN
548 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
550 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
553 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
555 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
559 -- (re)map metarecord-bib linking
560 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
561 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
563 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
565 ELSE -- we're doing an update, and we're not deleted, remap
566 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
568 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
574 $func$ LANGUAGE PLPGSQL;
577 -- from 100.circ_matrix.sql
579 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
581 user_object actor.usr%ROWTYPE;
582 new_sp_row actor.usr_standing_penalty%ROWTYPE;
583 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
584 collections_fines permission.grp_penalty_threshold%ROWTYPE;
585 max_fines permission.grp_penalty_threshold%ROWTYPE;
586 max_overdue permission.grp_penalty_threshold%ROWTYPE;
587 max_items_out permission.grp_penalty_threshold%ROWTYPE;
588 max_lost permission.grp_penalty_threshold%ROWTYPE;
589 max_longoverdue permission.grp_penalty_threshold%ROWTYPE;
594 items_longoverdue INT;
595 context_org_list INT[];
596 current_fines NUMERIC(8,2) := 0.0;
597 tmp_fines NUMERIC(8,2);
600 tmp_org actor.org_unit%ROWTYPE;
601 tmp_penalty config.standing_penalty%ROWTYPE;
604 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
607 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
609 -- Fail if the user has a high fine balance
611 tmp_grp := user_object.profile;
613 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
615 IF max_fines.threshold IS NULL THEN
616 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
621 IF tmp_grp IS NULL THEN
626 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
630 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
634 IF max_fines.threshold IS NOT NULL THEN
638 FROM actor.usr_standing_penalty
639 WHERE usr = match_user
640 AND org_unit = max_fines.org_unit
641 AND (stop_date IS NULL or stop_date > NOW())
642 AND standing_penalty = 1;
644 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
646 SELECT SUM(f.balance_owed) INTO current_fines
647 FROM money.materialized_billable_xact_summary f
650 FROM booking.reservation r
651 WHERE r.usr = match_user
652 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
653 AND xact_finish IS NULL
657 WHERE g.usr = match_user
658 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
659 AND xact_finish IS NULL
662 FROM action.circulation circ
663 WHERE circ.usr = match_user
664 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
665 AND xact_finish IS NULL ) l USING (id);
667 IF current_fines >= max_fines.threshold THEN
668 new_sp_row.usr := match_user;
669 new_sp_row.org_unit := max_fines.org_unit;
670 new_sp_row.standing_penalty := 1;
671 RETURN NEXT new_sp_row;
675 -- Start over for max overdue
676 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
678 -- Fail if the user has too many overdue items
680 tmp_grp := user_object.profile;
683 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
685 IF max_overdue.threshold IS NULL THEN
686 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
691 IF tmp_grp IS NULL THEN
696 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
700 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
704 IF max_overdue.threshold IS NOT NULL THEN
708 FROM actor.usr_standing_penalty
709 WHERE usr = match_user
710 AND org_unit = max_overdue.org_unit
711 AND (stop_date IS NULL or stop_date > NOW())
712 AND standing_penalty = 2;
714 SELECT INTO items_overdue COUNT(*)
715 FROM action.circulation circ
716 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
717 WHERE circ.usr = match_user
718 AND circ.checkin_time IS NULL
719 AND circ.due_date < NOW()
720 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
722 IF items_overdue >= max_overdue.threshold::INT THEN
723 new_sp_row.usr := match_user;
724 new_sp_row.org_unit := max_overdue.org_unit;
725 new_sp_row.standing_penalty := 2;
726 RETURN NEXT new_sp_row;
730 -- Start over for max out
731 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
733 -- Fail if the user has too many checked out items
735 tmp_grp := user_object.profile;
737 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
739 IF max_items_out.threshold IS NULL THEN
740 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
745 IF tmp_grp IS NULL THEN
750 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
754 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
759 -- Fail if the user has too many items checked out
760 IF max_items_out.threshold IS NOT NULL THEN
764 FROM actor.usr_standing_penalty
765 WHERE usr = match_user
766 AND org_unit = max_items_out.org_unit
767 AND (stop_date IS NULL or stop_date > NOW())
768 AND standing_penalty = 3;
770 SELECT INTO items_out COUNT(*)
771 FROM action.circulation circ
772 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
773 WHERE circ.usr = match_user
774 AND circ.checkin_time IS NULL
775 AND (circ.stop_fines IN (
776 SELECT 'MAXFINES'::TEXT
778 SELECT 'LONGOVERDUE'::TEXT
784 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
789 SELECT 'CLAIMSRETURNED'::TEXT
793 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
797 ) OR circ.stop_fines IS NULL)
798 AND xact_finish IS NULL;
800 IF items_out >= max_items_out.threshold::INT THEN
801 new_sp_row.usr := match_user;
802 new_sp_row.org_unit := max_items_out.org_unit;
803 new_sp_row.standing_penalty := 3;
804 RETURN NEXT new_sp_row;
808 -- Start over for max lost
809 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
811 -- Fail if the user has too many lost items
813 tmp_grp := user_object.profile;
816 SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 5 AND org_unit = tmp_org.id;
818 IF max_lost.threshold IS NULL THEN
819 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
824 IF tmp_grp IS NULL THEN
829 IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
833 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
837 IF max_lost.threshold IS NOT NULL THEN
841 FROM actor.usr_standing_penalty
842 WHERE usr = match_user
843 AND org_unit = max_lost.org_unit
844 AND (stop_date IS NULL or stop_date > NOW())
845 AND standing_penalty = 5;
847 SELECT INTO items_lost COUNT(*)
848 FROM action.circulation circ
849 JOIN actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
850 WHERE circ.usr = match_user
851 AND circ.checkin_time IS NULL
852 AND (circ.stop_fines = 'LOST')
853 AND xact_finish IS NULL;
855 IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
856 new_sp_row.usr := match_user;
857 new_sp_row.org_unit := max_lost.org_unit;
858 new_sp_row.standing_penalty := 5;
859 RETURN NEXT new_sp_row;
863 -- Start over for max longoverdue
864 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
866 -- Fail if the user has too many longoverdue items
868 tmp_grp := user_object.profile;
871 SELECT * INTO max_longoverdue
872 FROM permission.grp_penalty_threshold
873 WHERE grp = tmp_grp AND
875 org_unit = tmp_org.id;
877 IF max_longoverdue.threshold IS NULL THEN
878 SELECT parent INTO tmp_grp
879 FROM permission.grp_tree WHERE id = tmp_grp;
884 IF tmp_grp IS NULL THEN
889 IF max_longoverdue.threshold IS NOT NULL
890 OR tmp_org.parent_ou IS NULL THEN
894 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
898 IF max_longoverdue.threshold IS NOT NULL THEN
902 FROM actor.usr_standing_penalty
903 WHERE usr = match_user
904 AND org_unit = max_longoverdue.org_unit
905 AND (stop_date IS NULL or stop_date > NOW())
906 AND standing_penalty = 35;
908 SELECT INTO items_longoverdue COUNT(*)
909 FROM action.circulation circ
910 JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp
911 ON (circ.circ_lib = fp.id)
912 WHERE circ.usr = match_user
913 AND circ.checkin_time IS NULL
914 AND (circ.stop_fines = 'LONGOVERDUE')
915 AND xact_finish IS NULL;
917 IF items_longoverdue >= max_longoverdue.threshold::INT
918 AND 0 < max_longoverdue.threshold::INT THEN
919 new_sp_row.usr := match_user;
920 new_sp_row.org_unit := max_longoverdue.org_unit;
921 new_sp_row.standing_penalty := 35;
922 RETURN NEXT new_sp_row;
927 -- Start over for collections warning
928 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
930 -- Fail if the user has a collections-level fine balance
932 tmp_grp := user_object.profile;
934 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
936 IF max_fines.threshold IS NULL THEN
937 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
942 IF tmp_grp IS NULL THEN
947 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
951 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
955 IF max_fines.threshold IS NOT NULL THEN
959 FROM actor.usr_standing_penalty
960 WHERE usr = match_user
961 AND org_unit = max_fines.org_unit
962 AND (stop_date IS NULL or stop_date > NOW())
963 AND standing_penalty = 4;
965 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
967 SELECT SUM(f.balance_owed) INTO current_fines
968 FROM money.materialized_billable_xact_summary f
971 FROM booking.reservation r
972 WHERE r.usr = match_user
973 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
974 AND r.xact_finish IS NULL
978 WHERE g.usr = match_user
979 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
980 AND g.xact_finish IS NULL
983 FROM action.circulation circ
984 WHERE circ.usr = match_user
985 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
986 AND circ.xact_finish IS NULL ) l USING (id);
988 IF current_fines >= max_fines.threshold THEN
989 new_sp_row.usr := match_user;
990 new_sp_row.org_unit := max_fines.org_unit;
991 new_sp_row.standing_penalty := 4;
992 RETURN NEXT new_sp_row;
996 -- Start over for in collections
997 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
999 -- Remove the in-collections penalty if the user has paid down enough
1000 -- This penalty is different, because this code is not responsible for creating
1001 -- new in-collections penalties, only for removing them
1003 tmp_grp := user_object.profile;
1005 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
1007 IF max_fines.threshold IS NULL THEN
1008 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1013 IF tmp_grp IS NULL THEN
1018 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1022 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1026 IF max_fines.threshold IS NOT NULL THEN
1028 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
1030 -- first, see if the user had paid down to the threshold
1031 SELECT SUM(f.balance_owed) INTO current_fines
1032 FROM money.materialized_billable_xact_summary f
1035 FROM booking.reservation r
1036 WHERE r.usr = match_user
1037 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
1038 AND r.xact_finish IS NULL
1041 FROM money.grocery g
1042 WHERE g.usr = match_user
1043 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
1044 AND g.xact_finish IS NULL
1047 FROM action.circulation circ
1048 WHERE circ.usr = match_user
1049 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
1050 AND circ.xact_finish IS NULL ) l USING (id);
1052 IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
1053 -- patron has paid down enough
1055 SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
1057 IF tmp_penalty.org_depth IS NOT NULL THEN
1059 -- since this code is not responsible for applying the penalty, it can't
1060 -- guarantee the current context org will match the org at which the penalty
1061 --- was applied. search up the org tree until we hit the configured penalty depth
1062 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1063 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
1065 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
1069 FROM actor.usr_standing_penalty
1070 WHERE usr = match_user
1071 AND org_unit = tmp_org.id
1072 AND (stop_date IS NULL or stop_date > NOW())
1073 AND standing_penalty = 30;
1075 IF tmp_org.parent_ou IS NULL THEN
1079 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1080 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
1085 -- no penalty depth is defined, look for exact matches
1089 FROM actor.usr_standing_penalty
1090 WHERE usr = match_user
1091 AND org_unit = max_fines.org_unit
1092 AND (stop_date IS NULL or stop_date > NOW())
1093 AND standing_penalty = 30;
1102 $func$ LANGUAGE plpgsql;
1105 -- from 110.hold_matrix.sql
1107 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$
1110 user_object actor.usr%ROWTYPE;
1111 age_protect_object config.rule_age_hold_protect%ROWTYPE;
1112 standing_penalty config.standing_penalty%ROWTYPE;
1113 transit_range_ou_type actor.org_unit_type%ROWTYPE;
1114 transit_source actor.org_unit%ROWTYPE;
1115 item_object asset.copy%ROWTYPE;
1116 item_cn_object asset.call_number%ROWTYPE;
1117 item_status_object config.copy_status%ROWTYPE;
1118 item_location_object asset.copy_location%ROWTYPE;
1119 ou_skip actor.org_unit_setting%ROWTYPE;
1120 result action.matrix_test_result;
1121 hold_test config.hold_matrix_matchpoint%ROWTYPE;
1122 use_active_date TEXT;
1123 age_protect_date TIMESTAMP WITH TIME ZONE;
1125 hold_transit_prox INT;
1126 frozen_hold_count INT;
1127 context_org_list INT[];
1131 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1132 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( pickup_ou );
1134 result.success := TRUE;
1136 -- The HOLD penalty block only applies to new holds.
1137 -- The CAPTURE penalty block applies to existing holds.
1138 hold_penalty := 'HOLD';
1139 IF retargetting THEN
1140 hold_penalty := 'CAPTURE';
1143 -- Fail if we couldn't find a user
1144 IF user_object.id IS NULL THEN
1145 result.fail_part := 'no_user';
1146 result.success := FALSE;
1152 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1154 -- Fail if we couldn't find a copy
1155 IF item_object.id IS NULL THEN
1156 result.fail_part := 'no_item';
1157 result.success := FALSE;
1163 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
1164 result.matchpoint := matchpoint_id;
1166 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
1168 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
1169 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
1170 result.fail_part := 'circ.holds.target_skip_me';
1171 result.success := FALSE;
1177 -- Fail if user is barred
1178 IF user_object.barred IS TRUE THEN
1179 result.fail_part := 'actor.usr.barred';
1180 result.success := FALSE;
1186 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
1187 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
1188 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
1190 -- Fail if we couldn't find any matchpoint (requires a default)
1191 IF matchpoint_id IS NULL THEN
1192 result.fail_part := 'no_matchpoint';
1193 result.success := FALSE;
1199 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
1201 IF hold_test.holdable IS FALSE THEN
1202 result.fail_part := 'config.hold_matrix_test.holdable';
1203 result.success := FALSE;
1208 IF item_object.holdable IS FALSE THEN
1209 result.fail_part := 'item.holdable';
1210 result.success := FALSE;
1215 IF item_status_object.holdable IS FALSE THEN
1216 result.fail_part := 'status.holdable';
1217 result.success := FALSE;
1222 IF item_location_object.holdable IS FALSE THEN
1223 result.fail_part := 'location.holdable';
1224 result.success := FALSE;
1229 IF hold_test.transit_range IS NOT NULL THEN
1230 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
1231 IF hold_test.distance_is_from_owner THEN
1232 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;
1234 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
1237 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
1240 result.fail_part := 'transit_range';
1241 result.success := FALSE;
1247 FOR standing_penalty IN
1248 SELECT DISTINCT csp.*
1249 FROM actor.usr_standing_penalty usp
1250 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1251 WHERE usr = match_user
1252 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
1253 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1254 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
1256 result.fail_part := standing_penalty.name;
1257 result.success := FALSE;
1262 IF hold_test.stop_blocked_user IS TRUE THEN
1263 FOR standing_penalty IN
1264 SELECT DISTINCT csp.*
1265 FROM actor.usr_standing_penalty usp
1266 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1267 WHERE usr = match_user
1268 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
1269 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1270 AND csp.block_list LIKE '%CIRC%' LOOP
1272 result.fail_part := standing_penalty.name;
1273 result.success := FALSE;
1279 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
1280 SELECT INTO hold_count COUNT(*)
1281 FROM action.hold_request
1282 WHERE usr = match_user
1283 AND fulfillment_time IS NULL
1284 AND cancel_time IS NULL
1285 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
1287 IF hold_count >= hold_test.max_holds THEN
1288 result.fail_part := 'config.hold_matrix_test.max_holds';
1289 result.success := FALSE;
1295 IF item_object.age_protect IS NOT NULL THEN
1296 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
1297 IF hold_test.distance_is_from_owner THEN
1298 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
1300 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
1302 IF use_active_date = 'true' THEN
1303 age_protect_date := COALESCE(item_object.active_date, NOW());
1305 age_protect_date := item_object.create_date;
1307 IF age_protect_date + age_protect_object.age > NOW() THEN
1308 IF hold_test.distance_is_from_owner THEN
1309 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
1310 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
1312 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
1315 IF hold_transit_prox > age_protect_object.prox THEN
1316 result.fail_part := 'config.rule_age_hold_protect.prox';
1317 result.success := FALSE;
1330 $func$ LANGUAGE plpgsql;
1333 -- from 300.schema.staged_search.sql
1335 CREATE OR REPLACE FUNCTION search.query_parser_fts (
1337 param_search_ou INT,
1340 param_statuses INT[],
1341 param_locations INT[],
1347 deleted_search BOOL,
1348 param_pref_ou INT DEFAULT NULL
1349 ) RETURNS SETOF search.search_result AS $func$
1352 current_res search.search_result%ROWTYPE;
1353 search_org_list INT[];
1354 luri_org_list INT[];
1363 core_cursor REFCURSOR;
1364 core_rel_query TEXT;
1366 total_count INT := 0;
1367 check_count INT := 0;
1368 deleted_count INT := 0;
1369 visible_count INT := 0;
1370 excluded_count INT := 0;
1374 check_limit := COALESCE( param_check, 1000 );
1375 core_limit := COALESCE( param_limit, 25000 );
1376 core_offset := COALESCE( param_offset, 0 );
1378 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
1380 IF param_search_ou > 0 THEN
1381 IF param_depth IS NOT NULL THEN
1382 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
1384 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
1387 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
1389 ELSIF param_search_ou < 0 THEN
1390 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
1392 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
1393 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
1394 luri_org_list := luri_org_list || tmp_int_list;
1397 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
1399 ELSIF param_search_ou = 0 THEN
1400 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
1403 IF param_pref_ou IS NOT NULL THEN
1404 SELECT array_agg(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
1405 luri_org_list := luri_org_list || tmp_int_list;
1408 OPEN core_cursor FOR EXECUTE param_query;
1412 FETCH core_cursor INTO core_result;
1413 EXIT WHEN NOT FOUND;
1414 EXIT WHEN total_count >= core_limit;
1416 total_count := total_count + 1;
1418 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
1420 check_count := check_count + 1;
1422 IF NOT deleted_search THEN
1424 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
1426 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
1427 deleted_count := deleted_count + 1;
1432 FROM biblio.record_entry b
1433 JOIN config.bib_source s ON (b.source = s.id)
1434 WHERE s.transcendant
1435 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
1438 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
1439 visible_count := visible_count + 1;
1441 current_res.id = core_result.id;
1442 current_res.rel = core_result.rel;
1446 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
1450 current_res.record = core_result.records[1];
1452 current_res.record = NULL;
1455 RETURN NEXT current_res;
1461 FROM asset.call_number cn
1462 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
1463 JOIN asset.uri uri ON (map.uri = uri.id)
1464 WHERE NOT cn.deleted
1465 AND cn.label = '##URI##'
1467 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
1468 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1469 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
1473 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
1474 visible_count := visible_count + 1;
1476 current_res.id = core_result.id;
1477 current_res.rel = core_result.rel;
1481 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
1485 current_res.record = core_result.records[1];
1487 current_res.record = NULL;
1490 RETURN NEXT current_res;
1495 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
1498 FROM asset.call_number cn
1499 JOIN asset.copy cp ON (cp.call_number = cn.id)
1500 WHERE NOT cn.deleted
1502 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
1503 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1504 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1509 FROM biblio.peer_bib_copy_map pr
1510 JOIN asset.copy cp ON (cp.id = pr.target_copy)
1511 WHERE NOT cp.deleted
1512 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
1513 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1514 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1518 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
1519 excluded_count := excluded_count + 1;
1526 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
1529 FROM asset.call_number cn
1530 JOIN asset.copy cp ON (cp.call_number = cn.id)
1531 WHERE NOT cn.deleted
1533 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
1534 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1535 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1540 FROM biblio.peer_bib_copy_map pr
1541 JOIN asset.copy cp ON (cp.id = pr.target_copy)
1542 WHERE NOT cp.deleted
1543 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
1544 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1545 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1549 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
1550 excluded_count := excluded_count + 1;
1557 IF staff IS NULL OR NOT staff THEN
1560 FROM asset.opac_visible_copies
1561 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1562 AND record IN ( SELECT * FROM unnest( core_result.records ) )
1567 FROM biblio.peer_bib_copy_map pr
1568 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
1569 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1570 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1575 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
1576 excluded_count := excluded_count + 1;
1584 FROM asset.call_number cn
1585 JOIN asset.copy cp ON (cp.call_number = cn.id)
1586 WHERE NOT cn.deleted
1588 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1589 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1595 FROM biblio.peer_bib_copy_map pr
1596 JOIN asset.copy cp ON (cp.id = pr.target_copy)
1597 WHERE NOT cp.deleted
1598 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1599 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1605 FROM asset.call_number cn
1606 JOIN asset.copy cp ON (cp.call_number = cn.id)
1607 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1612 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
1613 excluded_count := excluded_count + 1;
1624 visible_count := visible_count + 1;
1626 current_res.id = core_result.id;
1627 current_res.rel = core_result.rel;
1631 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
1635 current_res.record = core_result.records[1];
1637 current_res.record = NULL;
1640 RETURN NEXT current_res;
1642 IF visible_count % 1000 = 0 THEN
1643 -- RAISE NOTICE ' % visible so far ... ', visible_count;
1648 current_res.id = NULL;
1649 current_res.rel = NULL;
1650 current_res.record = NULL;
1651 current_res.total = total_count;
1652 current_res.checked = check_count;
1653 current_res.deleted = deleted_count;
1654 current_res.visible = visible_count;
1655 current_res.excluded = excluded_count;
1659 RETURN NEXT current_res;
1662 $func$ LANGUAGE PLPGSQL;
1665 -- from 990.schema.unapi.sql
1667 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT)
1668 RETURNS anyarray AS $$
1669 SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
1670 $$ LANGUAGE SQL STABLE;
1673 -- from 999.functions.global.sql
1675 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
1677 moved_objects INT := 0;
1678 source_cn asset.call_number%ROWTYPE;
1679 target_cn asset.call_number%ROWTYPE;
1680 metarec metabib.metarecord%ROWTYPE;
1681 hold action.hold_request%ROWTYPE;
1682 ser_rec serial.record_entry%ROWTYPE;
1683 ser_sub serial.subscription%ROWTYPE;
1684 acq_lineitem acq.lineitem%ROWTYPE;
1685 acq_request acq.user_request%ROWTYPE;
1686 booking booking.resource_type%ROWTYPE;
1687 source_part biblio.monograph_part%ROWTYPE;
1688 target_part biblio.monograph_part%ROWTYPE;
1689 multi_home biblio.peer_bib_copy_map%ROWTYPE;
1693 uri_text TEXT := '';
1696 -- move any 856 entries on records that have at least one MARC-mapped URI entry
1697 SELECT INTO uri_count COUNT(*)
1698 FROM asset.uri_call_number_map m
1699 JOIN asset.call_number cn ON (m.call_number = cn.id)
1700 WHERE cn.record = source_record;
1702 IF uri_count > 0 THEN
1704 -- This returns more nodes than you might expect:
1705 -- 7 instead of 1 for an 856 with $u $y $9
1706 SELECT COUNT(*) INTO counter
1707 FROM oils_xpath_table(
1710 'biblio.record_entry',
1712 'id=' || source_record
1713 ) as t(i int,c text);
1715 FOR i IN 1 .. counter LOOP
1716 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
1718 ' ind1="' || FIRST(ind1) || '"' ||
1719 ' ind2="' || FIRST(ind2) || '">' ||
1721 '<subfield code="' || subfield || '">' ||
1724 regexp_replace(data,'&','&','g'),
1728 ) || '</subfield>', ''
1729 ) || '</datafield>' INTO uri_datafield
1730 FROM oils_xpath_table(
1733 'biblio.record_entry',
1734 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
1735 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
1736 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
1737 '//*[@tag="856"][position()=' || i || ']/*[@code]',
1738 'id=' || source_record
1739 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
1741 -- As most of the results will be NULL, protect against NULLifying
1742 -- the valid content that we do generate
1743 uri_text := uri_text || COALESCE(uri_datafield, '');
1746 IF uri_text <> '' THEN
1747 UPDATE biblio.record_entry
1748 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
1749 WHERE id = target_record;
1754 -- Find and move metarecords to the target record
1755 SELECT INTO metarec *
1756 FROM metabib.metarecord
1757 WHERE master_record = source_record;
1760 UPDATE metabib.metarecord
1761 SET master_record = target_record,
1763 WHERE id = metarec.id;
1765 moved_objects := moved_objects + 1;
1768 -- Find call numbers attached to the source ...
1769 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
1771 SELECT INTO target_cn *
1772 FROM asset.call_number
1773 WHERE label = source_cn.label
1774 AND owning_lib = source_cn.owning_lib
1775 AND record = target_record
1778 -- ... and if there's a conflicting one on the target ...
1781 -- ... move the copies to that, and ...
1783 SET call_number = target_cn.id
1784 WHERE call_number = source_cn.id;
1786 -- ... move V holds to the move-target call number
1787 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
1789 UPDATE action.hold_request
1790 SET target = target_cn.id
1793 moved_objects := moved_objects + 1;
1798 -- ... just move the call number to the target record
1799 UPDATE asset.call_number
1800 SET record = target_record
1801 WHERE id = source_cn.id;
1804 moved_objects := moved_objects + 1;
1807 -- Find T holds targeting the source record ...
1808 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
1810 -- ... and move them to the target record
1811 UPDATE action.hold_request
1812 SET target = target_record
1815 moved_objects := moved_objects + 1;
1818 -- Find serial records targeting the source record ...
1819 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
1820 -- ... and move them to the target record
1821 UPDATE serial.record_entry
1822 SET record = target_record
1823 WHERE id = ser_rec.id;
1825 moved_objects := moved_objects + 1;
1828 -- Find serial subscriptions targeting the source record ...
1829 FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
1830 -- ... and move them to the target record
1831 UPDATE serial.subscription
1832 SET record_entry = target_record
1833 WHERE id = ser_sub.id;
1835 moved_objects := moved_objects + 1;
1838 -- Find booking resource types targeting the source record ...
1839 FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
1840 -- ... and move them to the target record
1841 UPDATE booking.resource_type
1842 SET record = target_record
1843 WHERE id = booking.id;
1845 moved_objects := moved_objects + 1;
1848 -- Find acq lineitems targeting the source record ...
1849 FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
1850 -- ... and move them to the target record
1852 SET eg_bib_id = target_record
1853 WHERE id = acq_lineitem.id;
1855 moved_objects := moved_objects + 1;
1858 -- Find acq user purchase requests targeting the source record ...
1859 FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
1860 -- ... and move them to the target record
1861 UPDATE acq.user_request
1862 SET eg_bib = target_record
1863 WHERE id = acq_request.id;
1865 moved_objects := moved_objects + 1;
1868 -- Find parts attached to the source ...
1869 FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
1871 SELECT INTO target_part *
1872 FROM biblio.monograph_part
1873 WHERE label = source_part.label
1874 AND record = target_record;
1876 -- ... and if there's a conflicting one on the target ...
1879 -- ... move the copy-part maps to that, and ...
1880 UPDATE asset.copy_part_map
1881 SET part = target_part.id
1882 WHERE part = source_part.id;
1884 -- ... move P holds to the move-target part
1885 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
1887 UPDATE action.hold_request
1888 SET target = target_part.id
1891 moved_objects := moved_objects + 1;
1896 -- ... just move the part to the target record
1897 UPDATE biblio.monograph_part
1898 SET record = target_record
1899 WHERE id = source_part.id;
1902 moved_objects := moved_objects + 1;
1905 -- Find multi_home items attached to the source ...
1906 FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
1907 -- ... and move them to the target record
1908 UPDATE biblio.peer_bib_copy_map
1909 SET peer_record = target_record
1910 WHERE id = multi_home.id;
1912 moved_objects := moved_objects + 1;
1915 -- And delete mappings where the item's home bib was merged with the peer bib
1916 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
1917 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
1918 FROM asset.copy WHERE id = target_copy
1921 -- Finally, "delete" the source record
1922 DELETE FROM biblio.record_entry WHERE id = source_record;
1924 -- That's all, folks!
1925 RETURN moved_objects;
1927 $func$ LANGUAGE plpgsql;
1929 -- from reporter-schema.sql
1931 CREATE OR REPLACE VIEW reporter.simple_record AS
1938 title.value AS title,
1939 uniform_title.value AS uniform_title,
1940 author.value AS author,
1941 publisher.value AS publisher,
1942 SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
1943 series_title.value AS series_title,
1944 series_statement.value AS series_statement,
1945 summary.value AS summary,
1946 ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
1947 ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
1948 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
1949 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
1950 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
1951 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
1952 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
1953 ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
1954 FROM biblio.record_entry r
1955 JOIN metabib.metarecord_source_map s ON (s.source = r.id)
1956 LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
1957 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
1958 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
1959 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
1960 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
1961 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
1962 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
1963 LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
1964 LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
1965 LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
1966 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
1968 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
1974 FIRST(title.value) AS title,
1975 FIRST(author.value) AS author,
1976 STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
1977 STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
1978 CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
1980 ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
1982 CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
1984 ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
1986 FROM biblio.record_entry r
1987 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
1988 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
1989 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND (publisher.tag = '260' OR (publisher.tag = '264' AND publisher.ind2 = '1')) AND publisher.subfield = 'b')
1990 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND (pubdate.tag = '260' OR (pubdate.tag = '264' AND pubdate.ind2 = '1')) AND pubdate.subfield = 'c')
1991 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
1992 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
1997 -- Not running changes from example.reporter-extension.sql since these are
1998 -- not installed by default, but including a helpful note.
2000 \qecho 'There were also changes in example.reporter-extension.sql'
2001 \qecho 'Please run that script again if you use it in your system'
2002 \qecho 'to apply new changes.'