1 --Upgrade Script for 2.1.0 to 2.1.1
3 INSERT INTO config.upgrade_log (version) VALUES ('2.1.1');
4 -- Patch from Doug Kyle re: https://bugs.launchpad.net/evergreen/+bug/822918
6 INSERT INTO config.upgrade_log (version) VALUES ('0637');
8 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
10 user_object actor.usr%ROWTYPE;
11 standing_penalty config.standing_penalty%ROWTYPE;
12 item_object asset.copy%ROWTYPE;
13 item_status_object config.copy_status%ROWTYPE;
14 item_location_object asset.copy_location%ROWTYPE;
15 result action.circ_matrix_test_result;
16 circ_test action.found_circ_matrix_matchpoint;
17 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
18 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
19 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
20 hold_ratio action.hold_stats%ROWTYPE;
23 context_org_list INT[];
26 -- Assume success unless we hit a failure condition
27 result.success := TRUE;
29 -- Need user info to look up matchpoints
30 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
32 -- (Insta)Fail if we couldn't find the user
33 IF user_object.id IS NULL THEN
34 result.fail_part := 'no_user';
35 result.success := FALSE;
41 -- Need item info to look up matchpoints
42 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
44 -- (Insta)Fail if we couldn't find the item
45 IF item_object.id IS NULL THEN
46 result.fail_part := 'no_item';
47 result.success := FALSE;
53 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
55 circ_matchpoint := circ_test.matchpoint;
56 result.matchpoint := circ_matchpoint.id;
57 result.circulate := circ_matchpoint.circulate;
58 result.duration_rule := circ_matchpoint.duration_rule;
59 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
60 result.max_fine_rule := circ_matchpoint.max_fine_rule;
61 result.hard_due_date := circ_matchpoint.hard_due_date;
62 result.renewals := circ_matchpoint.renewals;
63 result.grace_period := circ_matchpoint.grace_period;
64 result.buildrows := circ_test.buildrows;
66 -- (Insta)Fail if we couldn't find a matchpoint
67 IF circ_test.success = false THEN
68 result.fail_part := 'no_matchpoint';
69 result.success := FALSE;
75 -- All failures before this point are non-recoverable
76 -- Below this point are possibly overridable failures
78 -- Fail if the user is barred
79 IF user_object.barred IS TRUE THEN
80 result.fail_part := 'actor.usr.barred';
81 result.success := FALSE;
86 -- Fail if the item can't circulate
87 IF item_object.circulate IS FALSE THEN
88 result.fail_part := 'asset.copy.circulate';
89 result.success := FALSE;
94 -- Fail if the item isn't in a circulateable status on a non-renewal
95 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
96 result.fail_part := 'asset.copy.status';
97 result.success := FALSE;
100 -- Alternately, fail if the item isn't checked out on a renewal
101 ELSIF renewal AND item_object.status <> 1 THEN
102 result.fail_part := 'asset.copy.status';
103 result.success := FALSE;
108 -- Fail if the item can't circulate because of the shelving location
109 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
110 IF item_location_object.circulate IS FALSE THEN
111 result.fail_part := 'asset.copy_location.circulate';
112 result.success := FALSE;
117 -- Use Circ OU for penalties and such
118 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_ou );
121 penalty_type = '%RENEW%';
123 penalty_type = '%CIRC%';
126 FOR standing_penalty IN
127 SELECT DISTINCT csp.*
128 FROM actor.usr_standing_penalty usp
129 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
130 WHERE usr = match_user
131 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
132 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
133 AND csp.block_list LIKE penalty_type LOOP
135 result.fail_part := standing_penalty.name;
136 result.success := FALSE;
141 -- Fail if the test is set to hard non-circulating
142 IF circ_matchpoint.circulate IS FALSE THEN
143 result.fail_part := 'config.circ_matrix_test.circulate';
144 result.success := FALSE;
149 -- Fail if the total copy-hold ratio is too low
150 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
151 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
152 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
153 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
154 result.success := FALSE;
160 -- Fail if the available copy-hold ratio is too low
161 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
162 IF hold_ratio.hold_count IS NULL THEN
163 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
165 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
166 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
167 result.success := FALSE;
173 -- Fail if the user has too many items with specific circ_modifiers checked out
175 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP
176 SELECT INTO items_out COUNT(*)
177 FROM action.circulation circ
178 JOIN asset.copy cp ON (cp.id = circ.target_copy)
179 WHERE circ.usr = match_user
180 AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) )
181 AND circ.checkin_time IS NULL
182 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
183 AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id);
184 IF items_out >= out_by_circ_mod.items_out THEN
185 result.fail_part := 'config.circ_matrix_circ_mod_test';
186 result.success := FALSE;
193 -- If we passed everything, return the successful matchpoint
200 $func$ LANGUAGE plpgsql;
204 INSERT INTO config.upgrade_log (version) VALUES ('0638'); -- miker
206 CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
210 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
211 'tag:open-ils.org:U2@sitem/' || id AS id,
212 'tag:open-ils.org:U2@siss/' || issuance AS issuance,
213 date_expected, date_received
215 CASE WHEN issuance IS NOT NULL AND ('siss' = ANY ($4)) THEN unapi.siss( issuance, $2, 'issuance', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
216 CASE WHEN stream IS NOT NULL AND ('sstr' = ANY ($4)) THEN unapi.sstr( stream, $2, 'stream', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
217 CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( unit, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
218 CASE WHEN uri IS NOT NULL AND ('auri' = ANY ($4)) THEN unapi.auri( uri, $2, 'uri', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END
219 -- XMLELEMENT( name notes,
221 -- WHEN ('acpn' = ANY ($4)) THEN
222 -- (SELECT XMLAGG(acpn) FROM (
223 -- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8)
224 -- FROM asset.copy_note
225 -- WHERE owning_copy = cp.id AND pub
231 FROM serial.item sitem
236 -- Evergreen DB patch XXXX.schema.asset_merge_record_assets.sql
240 INSERT INTO config.upgrade_log (version) VALUES ('0639');
242 -- Dupe function replace removed
244 INSERT INTO config.upgrade_log (version) VALUES ('0645');
246 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
248 transformed_xml TEXT;
251 xfrm config.xml_transform%ROWTYPE;
253 new_attrs HSTORE := ''::HSTORE;
254 attr_def config.record_attr_definition%ROWTYPE;
257 IF NEW.deleted IS TRUE THEN -- If this bib is deleted
258 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
259 DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records
260 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
261 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
262 RETURN NEW; -- and we're done
265 IF TG_OP = 'UPDATE' THEN -- re-ingest?
266 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
268 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
273 -- Record authority linking
274 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
276 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
279 -- Flatten and insert the mfr data
280 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
282 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
284 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
285 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
287 FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
289 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
290 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value
291 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
292 WHERE record = NEW.id
293 AND tag LIKE attr_def.tag
295 WHEN attr_def.sf_list IS NOT NULL
296 THEN POSITION(subfield IN attr_def.sf_list) > 0
303 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
304 attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
306 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
308 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
310 -- See if we can skip the XSLT ... it's expensive
311 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
312 -- Can't skip the transform
313 IF xfrm.xslt <> '---' THEN
314 transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
316 transformed_xml := NEW.marc;
319 prev_xfrm := xfrm.name;
322 IF xfrm.name IS NULL THEN
323 -- just grab the marcxml (empty) transform
324 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
325 prev_xfrm := xfrm.name;
328 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
330 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
331 SELECT m.value INTO attr_value
332 FROM biblio.marc21_physical_characteristics(NEW.id) v
333 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
334 WHERE v.subfield = attr_def.phys_char_sf
335 LIMIT 1; -- Just in case ...
339 -- apply index normalizers to attr_value
341 SELECT n.func AS func,
342 n.param_count AS param_count,
344 FROM config.index_normalizer n
345 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
346 WHERE attr = attr_def.name
348 EXECUTE 'SELECT ' || normalizer.func || '(' ||
349 COALESCE( quote_literal( attr_value ), 'NULL' ) ||
351 WHEN normalizer.param_count > 0
352 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
359 -- Add the new value to the hstore
360 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
364 IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
365 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
367 UPDATE metabib.record_attr SET attrs = attrs || new_attrs WHERE id = NEW.id;
373 -- Gather and insert the field entry data
374 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
377 IF TG_OP = 'INSERT' THEN
378 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
380 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
383 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
385 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
389 -- (re)map metarecord-bib linking
390 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
391 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
393 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
395 ELSE -- we're doing an update, and we're not deleted, remap
396 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
398 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
404 $func$ LANGUAGE PLPGSQL;
407 INSERT INTO config.upgrade_log (version) VALUES ('0646');
409 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
414 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
416 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
421 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
422 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
425 actor.org_unit_descendants(ans.id) d
426 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
427 JOIN asset.copy_location cl ON (cp.location = cl.id)
428 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
432 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
439 $f$ LANGUAGE PLPGSQL;
441 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
446 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
448 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
453 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
454 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
457 actor.org_unit_descendants(ans.id) d
458 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
459 JOIN asset.copy_location cl ON (cp.location = cl.id)
460 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
464 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
471 $f$ LANGUAGE PLPGSQL;
474 INSERT INTO config.upgrade_log (version) VALUES ('0648');
476 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
478 moved_objects INT := 0;
479 source_cn asset.call_number%ROWTYPE;
480 target_cn asset.call_number%ROWTYPE;
481 metarec metabib.metarecord%ROWTYPE;
482 hold action.hold_request%ROWTYPE;
483 ser_rec serial.record_entry%ROWTYPE;
484 ser_sub serial.subscription%ROWTYPE;
485 acq_lineitem acq.lineitem%ROWTYPE;
486 acq_request acq.user_request%ROWTYPE;
487 booking booking.resource_type%ROWTYPE;
488 source_part biblio.monograph_part%ROWTYPE;
489 target_part biblio.monograph_part%ROWTYPE;
490 multi_home biblio.peer_bib_copy_map%ROWTYPE;
497 -- move any 856 entries on records that have at least one MARC-mapped URI entry
498 SELECT INTO uri_count COUNT(*)
499 FROM asset.uri_call_number_map m
500 JOIN asset.call_number cn ON (m.call_number = cn.id)
501 WHERE cn.record = source_record;
503 IF uri_count > 0 THEN
505 SELECT COUNT(*) INTO counter
506 FROM oils_xpath_table(
509 'biblio.record_entry',
511 'id=' || source_record
512 ) as t(i int,c text);
514 FOR i IN 1 .. counter LOOP
515 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
517 ' ind1="' || FIRST(ind1) || '"' ||
518 ' ind2="' || FIRST(ind2) || '">' ||
521 '<subfield code="' || subfield || '">' ||
524 regexp_replace(data,'&','&','g'),
530 ) || '</datafield>' INTO uri_datafield
531 FROM oils_xpath_table(
534 'biblio.record_entry',
535 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
536 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
537 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
538 '//*[@tag="856"][position()=' || i || ']/*[@code]',
539 'id=' || source_record
540 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
542 uri_text := uri_text || uri_datafield;
545 IF uri_text <> '' THEN
546 UPDATE biblio.record_entry
547 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
548 WHERE id = target_record;
553 -- Find and move metarecords to the target record
554 SELECT INTO metarec *
555 FROM metabib.metarecord
556 WHERE master_record = source_record;
559 UPDATE metabib.metarecord
560 SET master_record = target_record,
562 WHERE id = metarec.id;
564 moved_objects := moved_objects + 1;
567 -- Find call numbers attached to the source ...
568 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
570 SELECT INTO target_cn *
571 FROM asset.call_number
572 WHERE label = source_cn.label
573 AND owning_lib = source_cn.owning_lib
574 AND record = target_record;
576 -- ... and if there's a conflicting one on the target ...
579 -- ... move the copies to that, and ...
581 SET call_number = target_cn.id
582 WHERE call_number = source_cn.id;
584 -- ... move V holds to the move-target call number
585 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
587 UPDATE action.hold_request
588 SET target = target_cn.id
591 moved_objects := moved_objects + 1;
596 -- ... just move the call number to the target record
597 UPDATE asset.call_number
598 SET record = target_record
599 WHERE id = source_cn.id;
602 moved_objects := moved_objects + 1;
605 -- Find T holds targeting the source record ...
606 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
608 -- ... and move them to the target record
609 UPDATE action.hold_request
610 SET target = target_record
613 moved_objects := moved_objects + 1;
616 -- Find serial records targeting the source record ...
617 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
618 -- ... and move them to the target record
619 UPDATE serial.record_entry
620 SET record = target_record
621 WHERE id = ser_rec.id;
623 moved_objects := moved_objects + 1;
626 -- Find serial subscriptions targeting the source record ...
627 FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
628 -- ... and move them to the target record
629 UPDATE serial.subscription
630 SET record_entry = target_record
631 WHERE id = ser_sub.id;
633 moved_objects := moved_objects + 1;
636 -- Find booking resource types targeting the source record ...
637 FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
638 -- ... and move them to the target record
639 UPDATE booking.resource_type
640 SET record = target_record
641 WHERE id = booking.id;
643 moved_objects := moved_objects + 1;
646 -- Find acq lineitems targeting the source record ...
647 FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
648 -- ... and move them to the target record
650 SET eg_bib_id = target_record
651 WHERE id = acq_lineitem.id;
653 moved_objects := moved_objects + 1;
656 -- Find acq user purchase requests targeting the source record ...
657 FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
658 -- ... and move them to the target record
659 UPDATE acq.user_request
660 SET eg_bib = target_record
661 WHERE id = acq_request.id;
663 moved_objects := moved_objects + 1;
666 -- Find parts attached to the source ...
667 FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
669 SELECT INTO target_part *
670 FROM biblio.monograph_part
671 WHERE label = source_part.label
672 AND record = target_record;
674 -- ... and if there's a conflicting one on the target ...
677 -- ... move the copy-part maps to that, and ...
678 UPDATE asset.copy_part_map
679 SET part = target_part.id
680 WHERE part = source_part.id;
682 -- ... move P holds to the move-target part
683 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
685 UPDATE action.hold_request
686 SET target = target_part.id
689 moved_objects := moved_objects + 1;
694 -- ... just move the part to the target record
695 UPDATE biblio.monograph_part
696 SET record = target_record
697 WHERE id = source_part.id;
700 moved_objects := moved_objects + 1;
703 -- Find multi_home items attached to the source ...
704 FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
705 -- ... and move them to the target record
706 UPDATE biblio.peer_bib_copy_map
707 SET peer_record = target_record
708 WHERE id = multi_home.id;
710 moved_objects := moved_objects + 1;
713 -- And delete mappings where the item's home bib was merged with the peer bib
714 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
715 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
716 FROM asset.copy WHERE id = target_copy
719 -- Finally, "delete" the source record
720 DELETE FROM biblio.record_entry WHERE id = source_record;
722 -- That's all, folks!
723 RETURN moved_objects;
725 $func$ LANGUAGE plpgsql;
729 INSERT INTO config.upgrade_log (version) VALUES ('0649');
731 CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS
732 SELECT cp.id, COALESCE(c.circ_count, 0::bigint) + COALESCE(count(DISTINCT circ.id), 0::bigint) + COALESCE(count(DISTINCT acirc.id), 0::bigint) AS circ_count
734 LEFT JOIN extend_reporter.legacy_circ_count c USING (id)
735 LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id
736 LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id
737 GROUP BY cp.id, c.circ_count;
741 INSERT INTO config.upgrade_log (version) VALUES ('0650');
743 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
750 do_add BOOLEAN := false;
751 do_remove BOOLEAN := false;
754 SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status
756 JOIN asset.call_number cn ON (cn.id = cp.call_number)
757 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
758 JOIN asset.copy_location cl ON (cp.location = cl.id)
759 JOIN config.copy_status cs ON (cp.status = cs.id)
760 JOIN biblio.record_entry b ON (cn.record = b.id)
770 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status
772 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
773 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
774 JOIN asset.copy_location cl ON (cp.location = cl.id)
775 JOIN config.copy_status cs ON (cp.status = cs.id)
783 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
784 SELECT DISTINCT ON (id, record) id, circ_lib, record FROM (
790 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;
792 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
793 IF TG_OP = 'INSERT' THEN
794 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record;
795 EXECUTE add_front || add_peer_query || add_back;
798 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
799 EXECUTE remove_query;
804 IF TG_OP = 'INSERT' THEN
806 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
807 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
808 EXECUTE add_front || add_base_query || add_back;
815 -- handle items first, since with circulation activity
816 -- their statuses change frequently
817 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
819 IF OLD.location <> NEW.location OR
820 OLD.call_number <> NEW.call_number OR
821 OLD.status <> NEW.status OR
822 OLD.circ_lib <> NEW.circ_lib THEN
823 -- any of these could change visibility, but
824 -- we'll save some queries and not try to calculate
825 -- the change directly
830 IF OLD.deleted <> NEW.deleted THEN
838 IF OLD.opac_visible <> NEW.opac_visible THEN
839 IF OLD.opac_visible THEN
841 ELSIF NOT do_remove THEN -- handle edge case where deleted item
842 -- is also marked opac_visible
850 DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
853 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
854 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id;
855 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
862 IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column
864 IF OLD.deleted AND NEW.deleted THEN -- do nothing
868 ELSIF NEW.deleted THEN -- remove rows
870 IF TG_TABLE_NAME = 'call_number' THEN
871 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
872 ELSIF TG_TABLE_NAME = 'record_entry' THEN
873 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
878 ELSIF OLD.deleted THEN -- add rows
880 IF TG_TABLE_NAME = 'call_number' THEN
881 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
882 EXECUTE add_front || add_base_query || add_back;
883 ELSIF TG_TABLE_NAME = 'record_entry' THEN
884 add_base_query := add_base_query || ' AND cn.record = ' || NEW.id;
885 add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id;
886 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
895 IF TG_TABLE_NAME = 'call_number' THEN
897 IF OLD.record <> NEW.record THEN
898 -- call number is linked to different bib
899 remove_query := remove_query || 'call_number = ' || NEW.id || ');';
900 EXECUTE remove_query;
901 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
902 EXECUTE add_front || add_base_query || add_back;
909 IF TG_TABLE_NAME IN ('record_entry') THEN
910 RETURN NEW; -- don't have 'opac_visible'
913 -- actor.org_unit, asset.copy_location, asset.copy_status
914 IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
918 ELSIF NEW.opac_visible THEN -- add rows
920 IF TG_TABLE_NAME = 'org_unit' THEN
921 add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id;
922 add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id;
923 ELSIF TG_TABLE_NAME = 'copy_location' THEN
924 add_base_query := add_base_query || ' AND cp.location = ' || NEW.id;
925 add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id;
926 ELSIF TG_TABLE_NAME = 'copy_status' THEN
927 add_base_query := add_base_query || ' AND cp.status = ' || NEW.id;
928 add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id;
931 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
935 IF TG_TABLE_NAME = 'org_unit' THEN
936 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
937 ELSIF TG_TABLE_NAME = 'copy_location' THEN
938 remove_query := remove_query || 'location = ' || NEW.id || ');';
939 ELSIF TG_TABLE_NAME = 'copy_status' THEN
940 remove_query := remove_query || 'status = ' || NEW.id || ');';
943 EXECUTE remove_query;
949 $func$ LANGUAGE PLPGSQL;