1 --Upgrade Script for 3.1.5 to 3.2.0
2 \set eg_version '''3.2.0'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.2.0', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1115', :eg_version);
8 INSERT INTO permission.perm_list (id,code,description) VALUES ( 607, 'EMERGENCY_CLOSING', 'Create and manage Emergency Closings');
10 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout.due.emergency_closing','aecc','Circulation due date was adjusted by the Emergency Closing handler');
11 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.shelf_expire.emergency_closing','aech','Hold shelf expire time was adjusted by the Emergency Closing handler');
12 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('booking.due.emergency_closing','aecr','Booking reservation return date was adjusted by the Emergency Closing handler');
14 CREATE TABLE action.emergency_closing (
15 id SERIAL PRIMARY KEY,
16 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
17 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
18 process_start_time TIMESTAMPTZ,
19 process_end_time TIMESTAMPTZ,
20 last_update_time TIMESTAMPTZ
23 ALTER TABLE actor.org_unit_closed
24 ADD COLUMN emergency_closing INT
25 REFERENCES action.emergency_closing (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
27 CREATE TABLE action.emergency_closing_circulation (
28 id BIGSERIAL PRIMARY KEY,
29 emergency_closing INT NOT NULL REFERENCES action.emergency_closing (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
30 circulation INT NOT NULL REFERENCES action.circulation (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
31 original_due_date TIMESTAMPTZ,
32 process_time TIMESTAMPTZ
34 CREATE INDEX emergency_closing_circulation_emergency_closing_idx ON action.emergency_closing_circulation (emergency_closing);
35 CREATE INDEX emergency_closing_circulation_circulation_idx ON action.emergency_closing_circulation (circulation);
37 CREATE TABLE action.emergency_closing_reservation (
38 id BIGSERIAL PRIMARY KEY,
39 emergency_closing INT NOT NULL REFERENCES action.emergency_closing (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
40 reservation INT NOT NULL REFERENCES booking.reservation (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
41 original_end_time TIMESTAMPTZ,
42 process_time TIMESTAMPTZ
44 CREATE INDEX emergency_closing_reservation_emergency_closing_idx ON action.emergency_closing_reservation (emergency_closing);
45 CREATE INDEX emergency_closing_reservation_reservation_idx ON action.emergency_closing_reservation (reservation);
47 CREATE TABLE action.emergency_closing_hold (
48 id BIGSERIAL PRIMARY KEY,
49 emergency_closing INT NOT NULL REFERENCES action.emergency_closing (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 original_shelf_expire_time TIMESTAMPTZ,
52 process_time TIMESTAMPTZ
54 CREATE INDEX emergency_closing_hold_emergency_closing_idx ON action.emergency_closing_hold (emergency_closing);
55 CREATE INDEX emergency_closing_hold_hold_idx ON action.emergency_closing_hold (hold);
57 CREATE OR REPLACE VIEW action.emergency_closing_status AS
59 COALESCE(c.count, 0) AS circulations,
60 COALESCE(c.completed, 0) AS circulations_complete,
61 COALESCE(b.count, 0) AS reservations,
62 COALESCE(b.completed, 0) AS reservations_complete,
63 COALESCE(h.count, 0) AS holds,
64 COALESCE(h.completed, 0) AS holds_complete
65 FROM action.emergency_closing e
66 LEFT JOIN (SELECT emergency_closing, count(*) count, SUM((process_time IS NOT NULL)::INT) completed FROM action.emergency_closing_circulation GROUP BY 1) c ON (c.emergency_closing = e.id)
67 LEFT JOIN (SELECT emergency_closing, count(*) count, SUM((process_time IS NOT NULL)::INT) completed FROM action.emergency_closing_reservation GROUP BY 1) b ON (b.emergency_closing = e.id)
68 LEFT JOIN (SELECT emergency_closing, count(*) count, SUM((process_time IS NOT NULL)::INT) completed FROM action.emergency_closing_hold GROUP BY 1) h ON (h.emergency_closing = e.id)
71 CREATE OR REPLACE FUNCTION evergreen.find_next_open_time ( circ_lib INT, initial TIMESTAMPTZ, hourly BOOL DEFAULT FALSE, initial_time TIME DEFAULT NULL, dow_count INT DEFAULT 0 )
72 RETURNS TIMESTAMPTZ AS $$
78 hoo_open TIME WITHOUT TIME ZONE;
79 hoo_close TIME WITHOUT TIME ZONE;
80 adjacent actor.org_unit_closed%ROWTYPE;
88 IF initial_time IS NULL THEN
89 initial_time := initial::TIME;
92 final_time := (initial + '1 second'::INTERVAL)::TEXT;
94 breakout := breakout + 1;
96 time_adjusted := FALSE;
98 IF dow_count > 0 THEN -- we're recursing, so check for HOO closing
99 day_number := EXTRACT(ISODOW FROM final_time::TIMESTAMPTZ) - 1;
102 EXECUTE 'SELECT dow_' || day_number || '_open, dow_' || day_number || '_close FROM actor.hours_of_operation WHERE id = $1'
103 INTO hoo_open, hoo_close
106 -- RAISE NOTICE 'initial time: %; dow: %; close: %',initial_time,day_number,hoo_close;
108 IF hoo_close = '00:00:00' THEN -- bah ... I guess we'll check the next day
109 day_number := (day_number + 1) % 7;
110 plus_days := plus_days + 1;
111 time_adjusted := TRUE;
115 IF hoo_close IS NULL THEN -- no hours of operation ... assume no closing?
116 hoo_close := '23:59:59';
122 final_time := DATE(final_time::TIMESTAMPTZ + (plus_days || ' days')::INTERVAL)::TEXT;
123 IF hoo_close <> '00:00:00' AND hourly THEN -- Not a day-granular circ
124 final_time := final_time||' '|| hoo_close;
126 final_time := final_time||' 23:59:59';
130 -- Loop through other closings
132 SELECT * INTO adjacent FROM actor.org_unit_closed WHERE org_unit = circ_lib AND final_time::TIMESTAMPTZ between close_start AND close_end;
133 EXIT WHEN adjacent.id IS NULL;
134 time_adjusted := TRUE;
135 -- RAISE NOTICE 'recursing for closings with final_time: %',final_time;
136 final_time := evergreen.find_next_open_time(circ_lib, adjacent.close_end::TIMESTAMPTZ, hourly, initial_time, dow_count + 1)::TEXT;
139 EXIT WHEN breakout > 100;
140 EXIT WHEN NOT time_adjusted;
148 CREATE TYPE action.emergency_closing_stage_1_count AS (circulations INT, reservations INT, holds INT);
149 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_1 ( e_closing INT )
150 RETURNS SETOF action.emergency_closing_stage_1_count AS $$
153 touched action.emergency_closing_stage_1_count%ROWTYPE;
155 -- First, gather circs
156 INSERT INTO action.emergency_closing_circulation (emergency_closing, circulation)
159 FROM actor.org_unit_closed closing
160 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
161 JOIN action.circulation circ ON (
162 circ.circ_lib = closing.org_unit
163 AND circ.due_date BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
164 AND circ.xact_finish IS NULL
166 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_circulation t WHERE t.emergency_closing = e_closing AND t.circulation = circ.id);
168 GET DIAGNOSTICS tmp = ROW_COUNT;
169 touched.circulations := tmp;
171 INSERT INTO action.emergency_closing_reservation (emergency_closing, reservation)
174 FROM actor.org_unit_closed closing
175 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
176 JOIN booking.reservation res ON (
177 res.pickup_lib = closing.org_unit
178 AND res.end_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
180 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_reservation t WHERE t.emergency_closing = e_closing AND t.reservation = res.id);
182 GET DIAGNOSTICS tmp = ROW_COUNT;
183 touched.reservations := tmp;
185 INSERT INTO action.emergency_closing_hold (emergency_closing, hold)
188 FROM actor.org_unit_closed closing
189 JOIN action.emergency_closing ec ON (closing.emergency_closing = ec.id AND ec.id = e_closing)
190 JOIN action.hold_request hold ON (
191 pickup_lib = closing.org_unit
192 AND hold.shelf_expire_time BETWEEN closing.close_start AND (closing.close_end + '1s'::INTERVAL)
193 AND hold.fulfillment_time IS NULL
194 AND hold.cancel_time IS NULL
196 WHERE NOT EXISTS (SELECT 1 FROM action.emergency_closing_hold t WHERE t.emergency_closing = e_closing AND t.hold = hold.id);
198 GET DIAGNOSTICS tmp = ROW_COUNT;
199 touched.holds := tmp;
201 UPDATE action.emergency_closing
202 SET process_start_time = NOW(),
203 last_update_time = NOW()
204 WHERE id = e_closing;
210 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_hold ( hold_closing_entry INT )
213 hold action.hold_request%ROWTYPE;
214 e_closing action.emergency_closing%ROWTYPE;
215 e_c_hold action.emergency_closing_hold%ROWTYPE;
216 closing actor.org_unit_closed%ROWTYPE;
218 hoo_close TIME WITHOUT TIME ZONE;
221 -- Gather objects involved
222 SELECT * INTO e_c_hold
223 FROM action.emergency_closing_hold
224 WHERE id = hold_closing_entry;
226 IF e_c_hold.process_time IS NOT NULL THEN
227 -- Already processed ... moving on
231 SELECT * INTO e_closing
232 FROM action.emergency_closing
233 WHERE id = e_c_hold.emergency_closing;
235 IF e_closing.process_start_time IS NULL THEN
236 -- Huh... that's odd. And wrong.
240 SELECT * INTO closing
241 FROM actor.org_unit_closed
242 WHERE emergency_closing = e_closing.id;
245 FROM action.hold_request h
246 WHERE id = e_c_hold.hold;
248 -- Record the processing
249 UPDATE action.emergency_closing_hold
250 SET original_shelf_expire_time = hold.shelf_expire_time,
252 WHERE id = hold_closing_entry;
254 UPDATE action.emergency_closing
255 SET last_update_time = NOW()
256 WHERE id = e_closing.id;
258 UPDATE action.hold_request
259 SET shelf_expire_time = evergreen.find_next_open_time(closing.org_unit, hold.shelf_expire_time, TRUE)
266 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_circ ( circ_closing_entry INT )
269 circ action.circulation%ROWTYPE;
270 e_closing action.emergency_closing%ROWTYPE;
271 e_c_circ action.emergency_closing_circulation%ROWTYPE;
272 closing actor.org_unit_closed%ROWTYPE;
273 adjacent actor.org_unit_closed%ROWTYPE;
274 bill money.billing%ROWTYPE;
275 last_bill money.billing%ROWTYPE;
277 hoo_close TIME WITHOUT TIME ZONE;
283 -- Gather objects involved
284 SELECT * INTO e_c_circ
285 FROM action.emergency_closing_circulation
286 WHERE id = circ_closing_entry;
288 IF e_c_circ.process_time IS NOT NULL THEN
289 -- Already processed ... moving on
293 SELECT * INTO e_closing
294 FROM action.emergency_closing
295 WHERE id = e_c_circ.emergency_closing;
297 IF e_closing.process_start_time IS NULL THEN
298 -- Huh... that's odd. And wrong.
302 SELECT * INTO closing
303 FROM actor.org_unit_closed
304 WHERE emergency_closing = e_closing.id;
307 FROM action.circulation
308 WHERE id = e_c_circ.circulation;
310 -- Record the processing
311 UPDATE action.emergency_closing_circulation
312 SET original_due_date = circ.due_date,
314 WHERE id = circ_closing_entry;
316 UPDATE action.emergency_closing
317 SET last_update_time = NOW()
318 WHERE id = e_closing.id;
320 SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', circ.circ_lib);
321 SELECT value::BOOL INTO extend_grace FROM actor.org_unit_ancestor_setting('circ.grace.extend', circ.circ_lib);
323 new_due_date := evergreen.find_next_open_time( closing.org_unit, circ.due_date, EXTRACT(EPOCH FROM circ.duration)::INT % 86400 > 0 )::TEXT;
324 UPDATE action.circulation SET due_date = new_due_date::TIMESTAMPTZ WHERE id = circ.id;
326 -- Now, see if we need to get rid of some fines
327 SELECT * INTO last_bill
329 WHERE b.xact = circ.id
332 ORDER BY billing_ts DESC
338 WHERE b.xact = circ.id
342 b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
343 OR (extend_grace AND last_bill.billing_ts <= new_due_date::TIMESTAMPTZ + circ.grace_period)
345 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
348 IF avoid_negative THEN
349 PERFORM FROM money.materialized_billable_xact_summary WHERE id = circ.id AND balanced_owd < bill.amount;
350 EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
356 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
364 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT )
367 res booking.reservation%ROWTYPE;
368 e_closing action.emergency_closing%ROWTYPE;
369 e_c_res action.emergency_closing_reservation%ROWTYPE;
370 closing actor.org_unit_closed%ROWTYPE;
371 adjacent actor.org_unit_closed%ROWTYPE;
372 bill money.billing%ROWTYPE;
374 hoo_close TIME WITHOUT TIME ZONE;
379 -- Gather objects involved
380 SELECT * INTO e_c_res
381 FROM action.emergency_closing_reservation
382 WHERE id = res_closing_entry;
384 IF e_c_res.process_time IS NOT NULL THEN
385 -- Already processed ... moving on
389 SELECT * INTO e_closing
390 FROM action.emergency_closing
391 WHERE id = e_c_res.emergency_closing;
393 IF e_closing.process_start_time IS NULL THEN
394 -- Huh... that's odd. And wrong.
398 SELECT * INTO closing
399 FROM actor.org_unit_closed
400 WHERE emergency_closing = e_closing.id;
403 FROM booking.reservation
404 WHERE id = e_c_res.reservation;
406 IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
410 -- Record the processing
411 UPDATE action.emergency_closing_reservation
412 SET original_end_time = res.end_time,
414 WHERE id = res_closing_entry;
416 UPDATE action.emergency_closing
417 SET last_update_time = NOW()
418 WHERE id = e_closing.id;
420 SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);
422 new_due_date := evergreen.find_next_open_time( closing.org_unit, res.end_time, EXTRACT(EPOCH FROM res.booking_interval)::INT % 86400 > 0 )::TEXT;
423 UPDATE booking.reservation SET end_time = new_due_date::TIMESTAMPTZ WHERE id = res.id;
425 -- Now, see if we need to get rid of some fines
429 WHERE b.xact = res.id
432 AND b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
433 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
435 IF avoid_negative THEN
436 PERFORM FROM money.materialized_billable_xact_summary WHERE id = res.id AND balanced_owd < bill.amount;
437 EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
443 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
453 CREATE TYPE actor.cascade_setting_summary AS (
456 has_org_setting BOOLEAN,
457 has_user_setting BOOLEAN,
458 has_workstation_setting BOOLEAN
461 SELECT evergreen.upgrade_deps_block_check('1116', :eg_version);
463 CREATE TABLE config.workstation_setting_type (
464 name TEXT PRIMARY KEY,
465 label TEXT UNIQUE NOT NULL,
466 grp TEXT REFERENCES config.settings_group (name),
468 datatype TEXT NOT NULL DEFAULT 'string',
471 -- define valid datatypes
473 CONSTRAINT cwst_valid_datatype CHECK ( datatype IN
474 ( 'bool', 'integer', 'float', 'currency', 'interval',
475 'date', 'string', 'object', 'array', 'link' ) ),
477 -- fm_class is meaningful only for 'link' datatype
479 CONSTRAINT cwst_no_empty_link CHECK
480 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
481 ( datatype <> 'link' AND fm_class IS NULL ) )
484 CREATE TABLE actor.workstation_setting (
485 id SERIAL PRIMARY KEY,
486 workstation INT NOT NULL REFERENCES actor.workstation (id)
487 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
488 name TEXT NOT NULL REFERENCES config.workstation_setting_type (name)
489 ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
494 CREATE INDEX actor_workstation_setting_workstation_idx
495 ON actor.workstation_setting (workstation);
497 CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws()
498 RETURNS TRIGGER AS $FUNC$
501 IF TG_TABLE_NAME = 'usr_setting_type' THEN
502 PERFORM TRUE FROM config.workstation_setting_type cwst
503 WHERE cwst.name = NEW.name;
509 IF TG_TABLE_NAME = 'workstation_setting_type' THEN
510 PERFORM TRUE FROM config.usr_setting_type cust
511 WHERE cust.name = NEW.name;
518 '% Cannot be used as both a user setting and a workstation setting.',
521 $FUNC$ LANGUAGE PLPGSQL STABLE;
523 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
524 AFTER INSERT OR UPDATE ON config.usr_setting_type
525 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
527 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
528 AFTER INSERT OR UPDATE ON config.workstation_setting_type
529 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
531 CREATE OR REPLACE FUNCTION actor.get_cascade_setting(
532 setting_name TEXT, org_id INT, user_id INT, workstation_id INT)
533 RETURNS actor.cascade_setting_summary AS
537 summary actor.cascade_setting_summary;
538 org_setting_type config.org_unit_setting_type%ROWTYPE;
541 summary.name := setting_name;
543 -- Collect the org setting type status first in case we exit early.
544 -- The existance of an org setting type is not considered
545 -- privileged information.
546 SELECT INTO org_setting_type *
547 FROM config.org_unit_setting_type WHERE name = setting_name;
549 summary.has_org_setting := TRUE;
551 summary.has_org_setting := FALSE;
554 -- User and workstation settings have the same priority.
555 -- Start with user settings since that's the simplest code path.
556 -- The workstation_id is ignored if no user_id is provided.
557 IF user_id IS NOT NULL THEN
559 SELECT INTO summary.value value FROM actor.usr_setting
560 WHERE usr = user_id AND name = setting_name;
563 -- if we have a value, we have a setting type
564 summary.has_user_setting := TRUE;
566 IF workstation_id IS NOT NULL THEN
567 -- Only inform the caller about the workstation
568 -- setting type disposition when a workstation id is
569 -- provided. Otherwise, it's NULL to indicate UNKNOWN.
570 summary.has_workstation_setting := FALSE;
576 -- no user setting value, but a setting type may exist
577 SELECT INTO summary.has_user_setting EXISTS (
578 SELECT TRUE FROM config.usr_setting_type
579 WHERE name = setting_name
582 IF workstation_id IS NOT NULL THEN
584 IF NOT summary.has_user_setting THEN
585 -- A workstation setting type may only exist when a user
586 -- setting type does not.
588 SELECT INTO summary.value value
589 FROM actor.workstation_setting
590 WHERE workstation = workstation_id AND name = setting_name;
593 -- if we have a value, we have a setting type
594 summary.has_workstation_setting := TRUE;
598 -- no value, but a setting type may exist
599 SELECT INTO summary.has_workstation_setting EXISTS (
600 SELECT TRUE FROM config.workstation_setting_type
601 WHERE name = setting_name
605 -- Finally make use of the workstation to determine the org
606 -- unit if none is provided.
607 IF org_id IS NULL AND summary.has_org_setting THEN
608 SELECT INTO org_id owning_lib
609 FROM actor.workstation WHERE id = workstation_id;
614 -- Some org unit settings are protected by a view permission.
615 -- First see if we have any data that needs protecting, then
616 -- check the permission if needed.
618 IF NOT summary.has_org_setting THEN
622 -- avoid putting the value into the summary until we confirm
623 -- the value should be visible to the caller.
624 SELECT INTO setting_value value
625 FROM actor.org_unit_ancestor_setting(setting_name, org_id);
628 -- No value found -- perm check is irrelevant.
632 IF org_setting_type.view_perm IS NOT NULL THEN
634 IF user_id IS NULL THEN
635 RAISE NOTICE 'Perm check required but no user_id provided';
639 IF NOT permission.usr_has_perm(
640 user_id, (SELECT code FROM permission.perm_list
641 WHERE id = org_setting_type.view_perm), org_id)
643 RAISE NOTICE 'Perm check failed for user % on %',
644 user_id, org_setting_type.view_perm;
649 -- Perm check succeeded or was not necessary.
650 summary.value := setting_value;
653 $FUNC$ LANGUAGE PLPGSQL;
656 CREATE OR REPLACE FUNCTION actor.get_cascade_setting_batch(
657 setting_names TEXT[], org_id INT, user_id INT, workstation_id INT)
658 RETURNS SETOF actor.cascade_setting_summary AS
660 -- Returns a row per setting matching the setting name order. If no
661 -- value is applied, NULL is returned to retain name-response ordering.
664 summary actor.cascade_setting_summary;
666 FOREACH setting_name IN ARRAY setting_names LOOP
667 SELECT INTO summary * FROM actor.get_cascade_setting(
668 setting_Name, org_id, user_id, workstation_id);
672 $FUNC$ LANGUAGE PLPGSQL;
678 SELECT evergreen.upgrade_deps_block_check('1117', :eg_version);
680 INSERT INTO permission.perm_list (id, code, description) VALUES
681 (608, 'APPLY_WORKSTATION_SETTING',
682 oils_i18n_gettext(608, 'APPLY_WORKSTATION_SETTING', 'ppl', 'description'));
684 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
686 'eg.circ.checkin.no_precat_alert', 'circ', 'bool',
688 'eg.circ.checkin.no_precat_alert',
689 'Checkin: Ignore Precataloged Items',
693 'eg.circ.checkin.noop', 'circ', 'bool',
695 'eg.circ.checkin.noop',
696 'Checkin: Suppress Holds and Transits',
700 'eg.circ.checkin.void_overdues', 'circ', 'bool',
702 'eg.circ.checkin.void_overdues',
703 'Checkin: Amnesty Mode',
707 'eg.circ.checkin.auto_print_holds_transits', 'circ', 'bool',
709 'eg.circ.checkin.auto_print_holds_transits',
710 'Checkin: Auto-Print Holds and Transits',
714 'eg.circ.checkin.clear_expired', 'circ', 'bool',
716 'eg.circ.checkin.clear_expired',
717 'Checkin: Clear Holds Shelf',
721 'eg.circ.checkin.retarget_holds', 'circ', 'bool',
723 'eg.circ.checkin.retarget_holds',
724 'Checkin: Retarget Local Holds',
728 'eg.circ.checkin.retarget_holds_all', 'circ', 'bool',
730 'eg.circ.checkin.retarget_holds_all',
731 'Checkin: Retarget All Statuses',
735 'eg.circ.checkin.hold_as_transit', 'circ', 'bool',
737 'eg.circ.checkin.hold_as_transit',
738 'Checkin: Capture Local Holds as Transits',
742 'eg.circ.checkin.manual_float', 'circ', 'bool',
744 'eg.circ.checkin.manual_float',
745 'Checkin: Manual Floating Active',
749 'eg.circ.patron.summary.collapse', 'circ', 'bool',
751 'eg.circ.patron.summary.collapse',
752 'Collaps Patron Summary Display',
756 'circ.bills.receiptonpay', 'circ', 'bool',
758 'circ.bills.receiptonpay',
759 'Print Receipt On Payment',
763 'circ.renew.strict_barcode', 'circ', 'bool',
765 'circ.renew.strict_barcode',
766 'Renew: Strict Barcode',
770 'circ.checkin.strict_barcode', 'circ', 'bool',
772 'circ.checkin.strict_barcode',
773 'Checkin: Strict Barcode',
777 'circ.checkout.strict_barcode', 'circ', 'bool',
779 'circ.checkout.strict_barcode',
780 'Checkout: Strict Barcode',
784 'cat.holdings_show_copies', 'cat', 'bool',
786 'cat.holdings_show_copies',
787 'Holdings View Show Copies',
791 'cat.holdings_show_empty', 'cat', 'bool',
793 'cat.holdings_show_empty',
794 'Holdings View Show Empty Volumes',
798 'cat.holdings_show_empty_org', 'cat', 'bool',
800 'cat.holdings_show_empty_org',
801 'Holdings View Show Empty Orgs',
805 'cat.holdings_show_vols', 'cat', 'bool',
807 'cat.holdings_show_vols',
808 'Holdings View Show Volumes',
812 'cat.copy.defaults', 'cat', 'object',
815 'Copy Edit Default Values',
819 'cat.printlabels.default_template', 'cat', 'string',
821 'cat.printlabels.default_template',
822 'Print Label Default Template',
826 'cat.printlabels.templates', 'cat', 'object',
828 'cat.printlabels.templates',
829 'Print Label Templates',
833 'eg.circ.patron.search.include_inactive', 'circ', 'bool',
835 'eg.circ.patron.search.include_inactive',
836 'Patron Search Include Inactive',
840 'eg.circ.patron.search.show_extras', 'circ', 'bool',
842 'eg.circ.patron.search.show_extras',
843 'Patron Search Show Extra Search Options',
847 'eg.grid.circ.checkin.checkin', 'gui', 'object',
849 'eg.grid.circ.checkin.checkin',
850 'Grid Config: circ.checkin.checkin',
854 'eg.grid.circ.checkin.capture', 'gui', 'object',
856 'eg.grid.circ.checkin.capture',
857 'Grid Config: circ.checkin.capture',
861 'eg.grid.admin.server.config.copy_tag_type', 'gui', 'object',
863 'eg.grid.admin.server.config.copy_tag_type',
864 'Grid Config: admin.server.config.copy_tag_type',
868 'eg.grid.admin.server.config.metabib_field_virtual_map.grid', 'gui', 'object',
870 'eg.grid.admin.server.config.metabib_field_virtual_map.grid',
871 'Grid Config: admin.server.config.metabib_field_virtual_map.grid',
875 'eg.grid.admin.server.config.metabib_field.grid', 'gui', 'object',
877 'eg.grid.admin.server.config.metabib_field.grid',
878 'Grid Config: admin.server.config.metabib_field.grid',
882 'eg.grid.admin.server.config.marc_field', 'gui', 'object',
884 'eg.grid.admin.server.config.marc_field',
885 'Grid Config: admin.server.config.marc_field',
889 'eg.grid.admin.server.asset.copy_tag', 'gui', 'object',
891 'eg.grid.admin.server.asset.copy_tag',
892 'Grid Config: admin.server.asset.copy_tag',
896 'eg.grid.admin.local.circ.neg_balance_users', 'gui', 'object',
898 'eg.grid.admin.local.circ.neg_balance_users',
899 'Grid Config: admin.local.circ.neg_balance_users',
903 'eg.grid.admin.local.rating.badge', 'gui', 'object',
905 'eg.grid.admin.local.rating.badge',
906 'Grid Config: admin.local.rating.badge',
910 'eg.grid.admin.workstation.work_log', 'gui', 'object',
912 'eg.grid.admin.workstation.work_log',
913 'Grid Config: admin.workstation.work_log',
917 'eg.grid.admin.workstation.patron_log', 'gui', 'object',
919 'eg.grid.admin.workstation.patron_log',
920 'Grid Config: admin.workstation.patron_log',
924 'eg.grid.admin.serials.pattern_template', 'gui', 'object',
926 'eg.grid.admin.serials.pattern_template',
927 'Grid Config: admin.serials.pattern_template',
931 'eg.grid.serials.copy_templates', 'gui', 'object',
933 'eg.grid.serials.copy_templates',
934 'Grid Config: serials.copy_templates',
938 'eg.grid.cat.record_overlay.holdings', 'gui', 'object',
940 'eg.grid.cat.record_overlay.holdings',
941 'Grid Config: cat.record_overlay.holdings',
945 'eg.grid.cat.bucket.record.search', 'gui', 'object',
947 'eg.grid.cat.bucket.record.search',
948 'Grid Config: cat.bucket.record.search',
952 'eg.grid.cat.bucket.record.view', 'gui', 'object',
954 'eg.grid.cat.bucket.record.view',
955 'Grid Config: cat.bucket.record.view',
959 'eg.grid.cat.bucket.record.pending', 'gui', 'object',
961 'eg.grid.cat.bucket.record.pending',
962 'Grid Config: cat.bucket.record.pending',
966 'eg.grid.cat.bucket.copy.view', 'gui', 'object',
968 'eg.grid.cat.bucket.copy.view',
969 'Grid Config: cat.bucket.copy.view',
973 'eg.grid.cat.bucket.copy.pending', 'gui', 'object',
975 'eg.grid.cat.bucket.copy.pending',
976 'Grid Config: cat.bucket.copy.pending',
980 'eg.grid.cat.items', 'gui', 'object',
983 'Grid Config: cat.items',
987 'eg.grid.cat.volcopy.copies', 'gui', 'object',
989 'eg.grid.cat.volcopy.copies',
990 'Grid Config: cat.volcopy.copies',
994 'eg.grid.cat.volcopy.copies.complete', 'gui', 'object',
996 'eg.grid.cat.volcopy.copies.complete',
997 'Grid Config: cat.volcopy.copies.complete',
1001 'eg.grid.cat.peer_bibs', 'gui', 'object',
1003 'eg.grid.cat.peer_bibs',
1004 'Grid Config: cat.peer_bibs',
1008 'eg.grid.cat.catalog.holds', 'gui', 'object',
1010 'eg.grid.cat.catalog.holds',
1011 'Grid Config: cat.catalog.holds',
1015 'eg.grid.cat.holdings', 'gui', 'object',
1017 'eg.grid.cat.holdings',
1018 'Grid Config: cat.holdings',
1022 'eg.grid.cat.z3950_results', 'gui', 'object',
1024 'eg.grid.cat.z3950_results',
1025 'Grid Config: cat.z3950_results',
1029 'eg.grid.circ.holds.shelf', 'gui', 'object',
1031 'eg.grid.circ.holds.shelf',
1032 'Grid Config: circ.holds.shelf',
1036 'eg.grid.circ.holds.pull', 'gui', 'object',
1038 'eg.grid.circ.holds.pull',
1039 'Grid Config: circ.holds.pull',
1043 'eg.grid.circ.in_house_use', 'gui', 'object',
1045 'eg.grid.circ.in_house_use',
1046 'Grid Config: circ.in_house_use',
1050 'eg.grid.circ.renew', 'gui', 'object',
1052 'eg.grid.circ.renew',
1053 'Grid Config: circ.renew',
1057 'eg.grid.circ.transits.list', 'gui', 'object',
1059 'eg.grid.circ.transits.list',
1060 'Grid Config: circ.transits.list',
1064 'eg.grid.circ.patron.holds', 'gui', 'object',
1066 'eg.grid.circ.patron.holds',
1067 'Grid Config: circ.patron.holds',
1071 'eg.grid.circ.pending_patrons.list', 'gui', 'object',
1073 'eg.grid.circ.pending_patrons.list',
1074 'Grid Config: circ.pending_patrons.list',
1078 'eg.grid.circ.patron.items_out.noncat', 'gui', 'object',
1080 'eg.grid.circ.patron.items_out.noncat',
1081 'Grid Config: circ.patron.items_out.noncat',
1085 'eg.grid.circ.patron.items_out', 'gui', 'object',
1087 'eg.grid.circ.patron.items_out',
1088 'Grid Config: circ.patron.items_out',
1092 'eg.grid.circ.patron.billhistory_payments', 'gui', 'object',
1094 'eg.grid.circ.patron.billhistory_payments',
1095 'Grid Config: circ.patron.billhistory_payments',
1099 'eg.grid.user.bucket.view', 'gui', 'object',
1101 'eg.grid.user.bucket.view',
1102 'Grid Config: user.bucket.view',
1106 'eg.grid.user.bucket.pending', 'gui', 'object',
1108 'eg.grid.user.bucket.pending',
1109 'Grid Config: user.bucket.pending',
1113 'eg.grid.circ.patron.staff_messages', 'gui', 'object',
1115 'eg.grid.circ.patron.staff_messages',
1116 'Grid Config: circ.patron.staff_messages',
1120 'eg.grid.circ.patron.archived_messages', 'gui', 'object',
1122 'eg.grid.circ.patron.archived_messages',
1123 'Grid Config: circ.patron.archived_messages',
1127 'eg.grid.circ.patron.bills', 'gui', 'object',
1129 'eg.grid.circ.patron.bills',
1130 'Grid Config: circ.patron.bills',
1134 'eg.grid.circ.patron.checkout', 'gui', 'object',
1136 'eg.grid.circ.patron.checkout',
1137 'Grid Config: circ.patron.checkout',
1141 'eg.grid.serials.mfhd_grid', 'gui', 'object',
1143 'eg.grid.serials.mfhd_grid',
1144 'Grid Config: serials.mfhd_grid',
1148 'eg.grid.serials.view_item_grid', 'gui', 'object',
1150 'eg.grid.serials.view_item_grid',
1151 'Grid Config: serials.view_item_grid',
1155 'eg.grid.serials.dist_stream_grid', 'gui', 'object',
1157 'eg.grid.serials.dist_stream_grid',
1158 'Grid Config: serials.dist_stream_grid',
1162 'eg.grid.circ.patron.search', 'gui', 'object',
1164 'eg.grid.circ.patron.search',
1165 'Grid Config: circ.patron.search',
1169 'eg.cat.record.summary.collapse', 'gui', 'bool',
1171 'eg.cat.record.summary.collapse',
1172 'Collapse Bib Record Summary',
1176 'cat.marcedit.flateditor', 'gui', 'bool',
1178 'cat.marcedit.flateditor',
1179 'Use Flat MARC Editor',
1183 'cat.marcedit.stack_subfields', 'gui', 'bool',
1185 'cat.marcedit.stack_subfields',
1186 'MARC Editor Stack Subfields',
1190 'eg.offline.print_receipt', 'gui', 'bool',
1192 'eg.offline.print_receipt',
1193 'Offline Print Receipt',
1197 'eg.offline.strict_barcode', 'gui', 'bool',
1199 'eg.offline.strict_barcode',
1200 'Offline Use Strict Barcode',
1204 'cat.default_bib_marc_template', 'gui', 'string',
1206 'cat.default_bib_marc_template',
1207 'Default MARC Template',
1211 'eg.audio.disable', 'gui', 'bool',
1214 'Disable Staff Client Notification Audio',
1218 'eg.search.adv_pane', 'gui', 'string',
1220 'eg.search.adv_pane',
1221 'Catalog Advanced Search Default Pane',
1225 'eg.print.template_context.bills_current', 'gui', 'string',
1227 'eg.print.template_context.bills_current',
1228 'Print Template Context: bills_current',
1232 'eg.print.template.bills_current', 'gui', 'string',
1234 'eg.print.template.bills_current',
1235 'Print Template: bills_current',
1239 'eg.print.template_context.bills_historical', 'gui', 'string',
1241 'eg.print.template_context.bills_historical',
1242 'Print Template Context: bills_historical',
1246 'eg.print.template.bills_historical', 'gui', 'string',
1248 'eg.print.template.bills_historical',
1249 'Print Template: bills_historical',
1253 'eg.print.template_context.bill_payment', 'gui', 'string',
1255 'eg.print.template_context.bill_payment',
1256 'Print Template Context: bill_payment',
1260 'eg.print.template.bill_payment', 'gui', 'string',
1262 'eg.print.template.bill_payment',
1263 'Print Template: bill_payment',
1267 'eg.print.template_context.checkin', 'gui', 'string',
1269 'eg.print.template_context.checkin',
1270 'Print Template Context: checkin',
1274 'eg.print.template.checkin', 'gui', 'string',
1276 'eg.print.template.checkin',
1277 'Print Template: checkin',
1281 'eg.print.template_context.checkout', 'gui', 'string',
1283 'eg.print.template_context.checkout',
1284 'Print Template Context: checkout',
1288 'eg.print.template.checkout', 'gui', 'string',
1290 'eg.print.template.checkout',
1291 'Print Template: checkout',
1295 'eg.print.template_context.hold_transit_slip', 'gui', 'string',
1297 'eg.print.template_context.hold_transit_slip',
1298 'Print Template Context: hold_transit_slip',
1302 'eg.print.template.hold_transit_slip', 'gui', 'string',
1304 'eg.print.template.hold_transit_slip',
1305 'Print Template: hold_transit_slip',
1309 'eg.print.template_context.hold_shelf_slip', 'gui', 'string',
1311 'eg.print.template_context.hold_shelf_slip',
1312 'Print Template Context: hold_shelf_slip',
1316 'eg.print.template.hold_shelf_slip', 'gui', 'string',
1318 'eg.print.template.hold_shelf_slip',
1319 'Print Template: hold_shelf_slip',
1323 'eg.print.template_context.holds_for_bib', 'gui', 'string',
1325 'eg.print.template_context.holds_for_bib',
1326 'Print Template Context: holds_for_bib',
1330 'eg.print.template.holds_for_bib', 'gui', 'string',
1332 'eg.print.template.holds_for_bib',
1333 'Print Template: holds_for_bib',
1337 'eg.print.template_context.holds_for_patron', 'gui', 'string',
1339 'eg.print.template_context.holds_for_patron',
1340 'Print Template Context: holds_for_patron',
1344 'eg.print.template.holds_for_patron', 'gui', 'string',
1346 'eg.print.template.holds_for_patron',
1347 'Print Template: holds_for_patron',
1351 'eg.print.template_context.hold_pull_list', 'gui', 'string',
1353 'eg.print.template_context.hold_pull_list',
1354 'Print Template Context: hold_pull_list',
1358 'eg.print.template.hold_pull_list', 'gui', 'string',
1360 'eg.print.template.hold_pull_list',
1361 'Print Template: hold_pull_list',
1365 'eg.print.template_context.hold_shelf_list', 'gui', 'string',
1367 'eg.print.template_context.hold_shelf_list',
1368 'Print Template Context: hold_shelf_list',
1372 'eg.print.template.hold_shelf_list', 'gui', 'string',
1374 'eg.print.template.hold_shelf_list',
1375 'Print Template: hold_shelf_list',
1379 'eg.print.template_context.in_house_use_list', 'gui', 'string',
1381 'eg.print.template_context.in_house_use_list',
1382 'Print Template Context: in_house_use_list',
1386 'eg.print.template.in_house_use_list', 'gui', 'string',
1388 'eg.print.template.in_house_use_list',
1389 'Print Template: in_house_use_list',
1393 'eg.print.template_context.item_status', 'gui', 'string',
1395 'eg.print.template_context.item_status',
1396 'Print Template Context: item_status',
1400 'eg.print.template.item_status', 'gui', 'string',
1402 'eg.print.template.item_status',
1403 'Print Template: item_status',
1407 'eg.print.template_context.items_out', 'gui', 'string',
1409 'eg.print.template_context.items_out',
1410 'Print Template Context: items_out',
1414 'eg.print.template.items_out', 'gui', 'string',
1416 'eg.print.template.items_out',
1417 'Print Template: items_out',
1421 'eg.print.template_context.patron_address', 'gui', 'string',
1423 'eg.print.template_context.patron_address',
1424 'Print Template Context: patron_address',
1428 'eg.print.template.patron_address', 'gui', 'string',
1430 'eg.print.template.patron_address',
1431 'Print Template: patron_address',
1435 'eg.print.template_context.patron_data', 'gui', 'string',
1437 'eg.print.template_context.patron_data',
1438 'Print Template Context: patron_data',
1442 'eg.print.template.patron_data', 'gui', 'string',
1444 'eg.print.template.patron_data',
1445 'Print Template: patron_data',
1449 'eg.print.template_context.patron_note', 'gui', 'string',
1451 'eg.print.template_context.patron_note',
1452 'Print Template Context: patron_note',
1456 'eg.print.template.patron_note', 'gui', 'string',
1458 'eg.print.template.patron_note',
1459 'Print Template: patron_note',
1463 'eg.print.template_context.renew', 'gui', 'string',
1465 'eg.print.template_context.renew',
1466 'Print Template Context: renew',
1470 'eg.print.template.renew', 'gui', 'string',
1472 'eg.print.template.renew',
1473 'Print Template: renew',
1477 'eg.print.template_context.transit_list', 'gui', 'string',
1479 'eg.print.template_context.transit_list',
1480 'Print Template Context: transit_list',
1484 'eg.print.template.transit_list', 'gui', 'string',
1486 'eg.print.template.transit_list',
1487 'Print Template: transit_list',
1491 'eg.print.template_context.transit_slip', 'gui', 'string',
1493 'eg.print.template_context.transit_slip',
1494 'Print Template Context: transit_slip',
1498 'eg.print.template.transit_slip', 'gui', 'string',
1500 'eg.print.template.transit_slip',
1501 'Print Template: transit_slip',
1505 'eg.print.template_context.offline_checkout', 'gui', 'string',
1507 'eg.print.template_context.offline_checkout',
1508 'Print Template Context: offline_checkout',
1512 'eg.print.template.offline_checkout', 'gui', 'string',
1514 'eg.print.template.offline_checkout',
1515 'Print Template: offline_checkout',
1519 'eg.print.template_context.offline_renew', 'gui', 'string',
1521 'eg.print.template_context.offline_renew',
1522 'Print Template Context: offline_renew',
1526 'eg.print.template.offline_renew', 'gui', 'string',
1528 'eg.print.template.offline_renew',
1529 'Print Template: offline_renew',
1533 'eg.print.template_context.offline_checkin', 'gui', 'string',
1535 'eg.print.template_context.offline_checkin',
1536 'Print Template Context: offline_checkin',
1540 'eg.print.template.offline_checkin', 'gui', 'string',
1542 'eg.print.template.offline_checkin',
1543 'Print Template: offline_checkin',
1547 'eg.print.template_context.offline_in_house_use', 'gui', 'string',
1549 'eg.print.template_context.offline_in_house_use',
1550 'Print Template Context: offline_in_house_use',
1554 'eg.print.template.offline_in_house_use', 'gui', 'string',
1556 'eg.print.template.offline_in_house_use',
1557 'Print Template: offline_in_house_use',
1561 'eg.serials.stream_names', 'gui', 'array',
1563 'eg.serials.stream_names',
1564 'Serials Local Stream Names',
1568 'eg.serials.items.do_print_routing_lists', 'gui', 'bool',
1570 'eg.serials.items.do_print_routing_lists',
1571 'Serials Print Routing Lists',
1575 'eg.serials.items.receive_and_barcode', 'gui', 'bool',
1577 'eg.serials.items.receive_and_barcode',
1578 'Serials Barcode On Receive',
1584 -- More values with fm_class'es
1585 INSERT INTO config.workstation_setting_type (name, grp, datatype, fm_class, label)
1587 'eg.search.search_lib', 'gui', 'link', 'aou',
1589 'eg.search.search_lib',
1590 'Staff Catalog Default Search Library',
1594 'eg.search.pref_lib', 'gui', 'link', 'aou',
1596 'eg.search.pref_lib',
1597 'Staff Catalog Preferred Library',
1606 SELECT evergreen.upgrade_deps_block_check('1118', :eg_version);
1608 UPDATE action_trigger.event_definition
1612 [%- SET user = target.0.owner -%]
1613 To: [%- params.recipient_email || user.email %]
1614 From: [%- params.sender_email || default_sender %]
1615 Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %]
1616 Subject: Bibliographic Records
1617 Auto-Submitted: auto-generated
1619 [% FOR cbreb IN target %]
1620 [% FOR item IN cbreb.items;
1621 bre_id = item.target_biblio_record_entry;
1623 bibxml = helpers.unapi_bre(bre_id, {flesh => '{mra}'});
1625 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
1626 title = title _ part.textContent;
1629 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
1630 item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
1631 publisher = bibxml.findnodes('//*[@tag="260"]/*[@code="b"]').textContent;
1632 pubdate = bibxml.findnodes('//*[@tag="260"]/*[@code="c"]').textContent;
1633 isbn = bibxml.findnodes('//*[@tag="020"]/*[@code="a"]').textContent;
1634 issn = bibxml.findnodes('//*[@tag="022"]/*[@code="a"]').textContent;
1635 upc = bibxml.findnodes('//*[@tag="024"]/*[@code="a"]').textContent;
1638 [% loop.count %]/[% loop.size %]. Bib ID# [% bre_id %]
1639 [% IF isbn %]ISBN: [% isbn _ "\n" %][% END -%]
1640 [% IF issn %]ISSN: [% issn _ "\n" %][% END -%]
1641 [% IF upc %]UPC: [% upc _ "\n" %] [% END -%]
1643 Author: [% author %]
1644 Publication Info: [% publisher %] [% pubdate %]
1645 Item Type: [% item_type %]
1650 WHERE hook = 'biblio.format.record_entry.email'
1651 -- from previous stock definition
1652 AND MD5(template) = 'ee4e6c1b3049086c570c7a77413d46c1';
1654 UPDATE action_trigger.event_definition
1658 <style> li { padding: 8px; margin 5px; }</style>
1660 [% FOR cbreb IN target %]
1661 [% FOR item IN cbreb.items;
1662 bre_id = item.target_biblio_record_entry;
1664 bibxml = helpers.unapi_bre(bre_id, {flesh => '{mra}'});
1666 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
1667 title = title _ part.textContent;
1670 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
1671 item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
1672 publisher = bibxml.findnodes('//*[@tag="260"]/*[@code="b"]').textContent;
1673 pubdate = bibxml.findnodes('//*[@tag="260"]/*[@code="c"]').textContent;
1674 isbn = bibxml.findnodes('//*[@tag="020"]/*[@code="a"]').textContent;
1678 Bib ID# [% bre_id %] ISBN: [% isbn %]<br />
1679 Title: [% title %]<br />
1680 Author: [% author %]<br />
1681 Publication Info: [% publisher %] [% pubdate %]<br/>
1682 Item Type: [% item_type %]
1689 WHERE hook = 'biblio.format.record_entry.print'
1690 -- from previous stock definition
1691 AND MD5(template) = '9ada7ea8417cb23f89d0dc8f15ec68d0';
1694 SELECT evergreen.upgrade_deps_block_check('1120', :eg_version);
1696 --Only insert if the attributes are not already present
1698 INSERT INTO config.z3950_attr (source, name, label, code, format, truncation)
1699 SELECT 'oclc','upc','UPC','1007','6','0'
1700 WHERE NOT EXISTS (SELECT name FROM config.z3950_attr WHERE source = 'oclc' AND name = 'upc');
1702 INSERT INTO config.z3950_attr (source, name, label, code, format, truncation)
1703 SELECT 'loc','upc','UPC','1007','1','1'
1704 WHERE NOT EXISTS (SELECT name FROM config.z3950_attr WHERE source = 'loc' AND name = 'upc');
1706 SELECT evergreen.upgrade_deps_block_check('1121', :eg_version);
1708 CREATE TABLE permission.grp_tree_display_entry (
1709 id SERIAL PRIMARY KEY,
1710 position INTEGER NOT NULL,
1711 org INTEGER NOT NULL REFERENCES actor.org_unit (id)
1712 DEFERRABLE INITIALLY DEFERRED,
1713 grp INTEGER NOT NULL REFERENCES permission.grp_tree (id)
1714 DEFERRABLE INITIALLY DEFERRED,
1715 CONSTRAINT pgtde_once_per_org UNIQUE (org, grp)
1718 ALTER TABLE permission.grp_tree_display_entry
1719 ADD COLUMN parent integer REFERENCES permission.grp_tree_display_entry (id)
1720 DEFERRABLE INITIALLY DEFERRED;
1722 INSERT INTO permission.perm_list (id, code, description)
1723 VALUES (609, 'MANAGE_CUSTOM_PERM_GRP_TREE', oils_i18n_gettext( 609,
1724 'Allows a user to manage custom permission group lists.', 'ppl', 'description' ));
1727 SELECT evergreen.upgrade_deps_block_check('1122', :eg_version);
1729 ALTER TABLE actor.usr
1730 ADD COLUMN pref_prefix TEXT,
1731 ADD COLUMN pref_first_given_name TEXT,
1732 ADD COLUMN pref_second_given_name TEXT,
1733 ADD COLUMN pref_family_name TEXT,
1734 ADD COLUMN pref_suffix TEXT,
1735 ADD COLUMN name_keywords TEXT,
1736 ADD COLUMN name_kw_tsvector TSVECTOR;
1738 ALTER TABLE staging.user_stage
1739 ADD COLUMN pref_first_given_name TEXT,
1740 ADD COLUMN pref_second_given_name TEXT,
1741 ADD COLUMN pref_family_name TEXT;
1743 CREATE INDEX actor_usr_pref_first_given_name_idx
1744 ON actor.usr (evergreen.lowercase(pref_first_given_name));
1745 CREATE INDEX actor_usr_pref_second_given_name_idx
1746 ON actor.usr (evergreen.lowercase(pref_second_given_name));
1747 CREATE INDEX actor_usr_pref_family_name_idx
1748 ON actor.usr (evergreen.lowercase(pref_family_name));
1749 CREATE INDEX actor_usr_pref_first_given_name_unaccent_idx
1750 ON actor.usr (evergreen.unaccent_and_squash(pref_first_given_name));
1751 CREATE INDEX actor_usr_pref_second_given_name_unaccent_idx
1752 ON actor.usr (evergreen.unaccent_and_squash(pref_second_given_name));
1753 CREATE INDEX actor_usr_pref_family_name_unaccent_idx
1754 ON actor.usr (evergreen.unaccent_and_squash(pref_family_name));
1756 -- Update keyword indexes for existing patrons
1758 UPDATE actor.usr SET name_kw_tsvector =
1760 COALESCE(prefix, '') || ' ' ||
1761 COALESCE(first_given_name, '') || ' ' ||
1762 COALESCE(evergreen.unaccent_and_squash(first_given_name), '') || ' ' ||
1763 COALESCE(second_given_name, '') || ' ' ||
1764 COALESCE(evergreen.unaccent_and_squash(second_given_name), '') || ' ' ||
1765 COALESCE(family_name, '') || ' ' ||
1766 COALESCE(evergreen.unaccent_and_squash(family_name), '') || ' ' ||
1767 COALESCE(suffix, '')
1770 CREATE OR REPLACE FUNCTION actor.user_ingest_name_keywords()
1771 RETURNS TRIGGER AS $func$
1773 NEW.name_kw_tsvector := TO_TSVECTOR(
1774 COALESCE(NEW.prefix, '') || ' ' ||
1775 COALESCE(NEW.first_given_name, '') || ' ' ||
1776 COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' ||
1777 COALESCE(NEW.second_given_name, '') || ' ' ||
1778 COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' ||
1779 COALESCE(NEW.family_name, '') || ' ' ||
1780 COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' ||
1781 COALESCE(NEW.suffix, '') || ' ' ||
1782 COALESCE(NEW.pref_prefix, '') || ' ' ||
1783 COALESCE(NEW.pref_first_given_name, '') || ' ' ||
1784 COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' ||
1785 COALESCE(NEW.pref_second_given_name, '') || ' ' ||
1786 COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' ||
1787 COALESCE(NEW.pref_family_name, '') || ' ' ||
1788 COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' ||
1789 COALESCE(NEW.pref_suffix, '') || ' ' ||
1790 COALESCE(NEW.name_keywords, '')
1794 $func$ LANGUAGE PLPGSQL;
1796 -- Add after the batch upate above to avoid duplicate updates.
1797 CREATE TRIGGER user_ingest_name_keywords_tgr
1798 BEFORE INSERT OR UPDATE ON actor.usr
1799 FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords();
1802 -- merge pref names from source user to target user, except when
1803 -- clobbering existing pref names.
1804 CREATE OR REPLACE FUNCTION actor.usr_merge(src_usr INT, dest_usr INT,
1805 del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN )
1810 picklist_row RECORD;
1815 -- do some initial cleanup
1816 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
1817 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
1818 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
1822 DELETE FROM actor.card where usr = src_usr;
1824 IF deactivate_cards THEN
1825 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
1827 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
1832 DELETE FROM actor.usr_address WHERE usr = src_usr;
1834 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
1837 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
1838 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
1839 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
1840 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
1841 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
1844 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
1845 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
1846 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
1847 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
1852 -- For each *_bucket table: transfer every bucket belonging to src_usr
1853 -- into the custody of dest_usr.
1855 -- In order to avoid colliding with an existing bucket owned by
1856 -- the destination user, append the source user's id (in parenthesese)
1857 -- to the name. If you still get a collision, add successive
1858 -- spaces to the name and keep trying until you succeed.
1862 FROM container.biblio_record_entry_bucket
1863 WHERE owner = src_usr
1865 suffix := ' (' || src_usr || ')';
1868 UPDATE container.biblio_record_entry_bucket
1869 SET owner = dest_usr, name = name || suffix
1870 WHERE id = bucket_row.id;
1871 EXCEPTION WHEN unique_violation THEN
1872 suffix := suffix || ' ';
1881 FROM container.call_number_bucket
1882 WHERE owner = src_usr
1884 suffix := ' (' || src_usr || ')';
1887 UPDATE container.call_number_bucket
1888 SET owner = dest_usr, name = name || suffix
1889 WHERE id = bucket_row.id;
1890 EXCEPTION WHEN unique_violation THEN
1891 suffix := suffix || ' ';
1900 FROM container.copy_bucket
1901 WHERE owner = src_usr
1903 suffix := ' (' || src_usr || ')';
1906 UPDATE container.copy_bucket
1907 SET owner = dest_usr, name = name || suffix
1908 WHERE id = bucket_row.id;
1909 EXCEPTION WHEN unique_violation THEN
1910 suffix := suffix || ' ';
1919 FROM container.user_bucket
1920 WHERE owner = src_usr
1922 suffix := ' (' || src_usr || ')';
1925 UPDATE container.user_bucket
1926 SET owner = dest_usr, name = name || suffix
1927 WHERE id = bucket_row.id;
1928 EXCEPTION WHEN unique_violation THEN
1929 suffix := suffix || ' ';
1936 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
1939 -- transfer queues the same way we transfer buckets (see above)
1943 WHERE owner = src_usr
1945 suffix := ' (' || src_usr || ')';
1948 UPDATE vandelay.queue
1949 SET owner = dest_usr, name = name || suffix
1950 WHERE id = queue_row.id;
1951 EXCEPTION WHEN unique_violation THEN
1952 suffix := suffix || ' ';
1960 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
1961 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
1962 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
1963 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
1964 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
1967 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
1968 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
1969 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
1970 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
1972 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
1973 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
1974 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
1975 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
1977 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
1978 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
1979 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
1980 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
1981 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
1984 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
1985 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
1987 -- transfer picklists the same way we transfer buckets (see above)
1991 WHERE owner = src_usr
1993 suffix := ' (' || src_usr || ')';
1997 SET owner = dest_usr, name = name || suffix
1998 WHERE id = picklist_row.id;
1999 EXCEPTION WHEN unique_violation THEN
2000 suffix := suffix || ' ';
2007 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
2008 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
2009 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
2010 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
2011 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
2012 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
2013 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
2014 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
2017 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
2018 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
2019 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
2020 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
2021 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
2022 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
2025 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
2026 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
2029 -- It's not uncommon to define the reporter schema in a replica
2030 -- DB only, so don't assume these tables exist in the write DB.
2032 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
2033 EXCEPTION WHEN undefined_table THEN
2037 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
2038 EXCEPTION WHEN undefined_table THEN
2042 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
2043 EXCEPTION WHEN undefined_table THEN
2047 -- transfer folders the same way we transfer buckets (see above)
2050 FROM reporter.template_folder
2051 WHERE owner = src_usr
2053 suffix := ' (' || src_usr || ')';
2056 UPDATE reporter.template_folder
2057 SET owner = dest_usr, name = name || suffix
2058 WHERE id = folder_row.id;
2059 EXCEPTION WHEN unique_violation THEN
2060 suffix := suffix || ' ';
2066 EXCEPTION WHEN undefined_table THEN
2070 -- transfer folders the same way we transfer buckets (see above)
2073 FROM reporter.report_folder
2074 WHERE owner = src_usr
2076 suffix := ' (' || src_usr || ')';
2079 UPDATE reporter.report_folder
2080 SET owner = dest_usr, name = name || suffix
2081 WHERE id = folder_row.id;
2082 EXCEPTION WHEN unique_violation THEN
2083 suffix := suffix || ' ';
2089 EXCEPTION WHEN undefined_table THEN
2093 -- transfer folders the same way we transfer buckets (see above)
2096 FROM reporter.output_folder
2097 WHERE owner = src_usr
2099 suffix := ' (' || src_usr || ')';
2102 UPDATE reporter.output_folder
2103 SET owner = dest_usr, name = name || suffix
2104 WHERE id = folder_row.id;
2105 EXCEPTION WHEN unique_violation THEN
2106 suffix := suffix || ' ';
2112 EXCEPTION WHEN undefined_table THEN
2116 -- propagate preferred name values from the source user to the
2117 -- destination user, but only when values are not being replaced.
2118 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
2119 UPDATE actor.usr SET
2121 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
2122 pref_first_given_name =
2123 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
2124 pref_second_given_name =
2125 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
2127 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
2129 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
2130 WHERE id = dest_usr;
2132 -- Copy and deduplicate name keywords
2133 -- String -> array -> rows -> DISTINCT -> array -> string
2134 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
2135 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
2136 UPDATE actor.usr SET name_keywords = (
2138 SELECT DISTINCT UNNEST(
2139 REGEXP_SPLIT_TO_ARRAY(
2140 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
2141 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
2144 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
2145 ) WHERE id = dest_usr;
2147 -- Finally, delete the source user
2148 DELETE FROM actor.usr WHERE id = src_usr;
2151 $$ LANGUAGE plpgsql;
2156 SELECT evergreen.upgrade_deps_block_check('1123', :eg_version);
2158 ALTER TABLE config.rule_circ_duration
2159 ADD column max_auto_renewals INTEGER;
2161 ALTER TABLE action.circulation
2162 ADD column auto_renewal BOOLEAN;
2164 ALTER TABLE action.circulation
2165 ADD column auto_renewal_remaining INTEGER;
2167 ALTER TABLE action.aged_circulation
2168 ADD column auto_renewal BOOLEAN;
2170 ALTER TABLE action.aged_circulation
2171 ADD column auto_renewal_remaining INTEGER;
2173 INSERT INTO action_trigger.validator values('CircIsAutoRenewable', 'Checks whether the circulation is able to be autorenewed.');
2174 INSERT INTO action_trigger.reactor values('Circ::AutoRenew', 'Auto-Renews a circulation.');
2175 INSERT INTO action_trigger.hook(key, core_type, description) values('autorenewal', 'circ', 'Item was auto-renewed to patron.');
2177 -- AutoRenewer A/T Def:
2178 INSERT INTO action_trigger.event_definition(active, owner, name, hook, validator, reactor, delay, max_delay, delay_field, group_field)
2179 values (false, 1, 'Autorenew', 'checkout.due', 'CircIsOpen', 'Circ::AutoRenew', '-23 hours'::interval,'-1 minute'::interval, 'due_date', 'usr');
2181 -- AutoRenewal outcome Email notifier A/T Def:
2182 INSERT INTO action_trigger.event_definition(active, owner, name, hook, validator, reactor, group_field, template)
2183 values (false, 1, 'AutorenewNotify', 'autorenewal', 'NOOP_True', 'SendEmail', 'usr',
2186 [%- user = target.0.usr -%]
2187 To: [%- params.recipient_email || user.email %]
2188 From: [%- params.sender_email || default_sender %]
2189 Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %]
2190 Subject: Items Out Auto-Renewal Notification
2191 Auto-Submitted: auto-generated
2193 Dear [% user.family_name %], [% user.first_given_name %]
2194 An automatic renewal attempt was made for the following items:
2196 [% FOR circ IN target %]
2197 [%- SET idx = loop.count - 1; SET udata = user_data.$idx -%]
2198 [%- SET cid = circ.target_copy || udata.copy -%]
2199 [%- SET copy_details = helpers.get_copy_bib_basics(cid) -%]
2200 Item# [% loop.count %]
2201 Title: [% copy_details.title %]
2202 Author: [% copy_details.author %]
2203 [%- IF udata.is_renewed %]
2204 Status: Loan Renewed
2205 New Due Date: [% date.format(helpers.format_date(udata.new_due_date), '%Y-%m-%d') %]
2208 Reason: [% udata.reason %]
2209 Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
2215 INSERT INTO action_trigger.environment (event_def, path ) VALUES
2216 ( currval('action_trigger.event_definition_id_seq'), 'usr' ),
2217 ( currval('action_trigger.event_definition_id_seq'), 'circ_lib' );
2220 DROP VIEW action.all_circulation;
2221 CREATE OR REPLACE VIEW action.all_circulation AS
2222 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
2223 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
2224 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
2225 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
2226 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
2227 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
2228 auto_renewal, auto_renewal_remaining, NULL AS usr
2229 FROM action.aged_circulation
2231 SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
2232 cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
2233 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
2234 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
2235 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
2236 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
2237 circ.parent_circ, circ.auto_renewal, circ.auto_renewal_remaining, circ.usr
2238 FROM action.circulation circ
2239 JOIN asset.copy cp ON (circ.target_copy = cp.id)
2240 JOIN asset.call_number cn ON (cp.call_number = cn.id)
2241 JOIN actor.usr p ON (circ.usr = p.id)
2242 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
2243 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
2246 DROP FUNCTION action.summarize_all_circ_chain (INTEGER);
2247 DROP FUNCTION action.all_circ_chain (INTEGER);
2249 -- rebuild slim circ view
2250 DROP VIEW action.all_circulation_slim;
2251 CREATE OR REPLACE VIEW action.all_circulation_slim AS
2277 recurring_fine_rule,
2281 checkin_workstation,
2285 auto_renewal_remaining,
2287 FROM action.circulation
2314 recurring_fine_rule,
2318 checkin_workstation,
2322 auto_renewal_remaining,
2324 FROM action.aged_circulation
2327 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
2328 RETURNS SETOF action.all_circulation_slim AS $$
2330 tmp_circ action.all_circulation_slim%ROWTYPE;
2331 circ_0 action.all_circulation_slim%ROWTYPE;
2334 SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
2336 IF tmp_circ IS NULL THEN
2337 RETURN NEXT tmp_circ;
2341 -- find the front of the chain
2343 SELECT INTO tmp_circ * FROM action.all_circulation_slim
2344 WHERE id = tmp_circ.parent_circ;
2345 IF tmp_circ IS NULL THEN
2351 -- now send the circs to the caller, oldest to newest
2354 IF tmp_circ IS NULL THEN
2357 RETURN NEXT tmp_circ;
2358 SELECT INTO tmp_circ * FROM action.all_circulation_slim
2359 WHERE parent_circ = tmp_circ.id;
2363 $$ LANGUAGE 'plpgsql';
2365 -- same as action.summarize_circ_chain, but returns data collected
2366 -- from action.all_circulation, which may include aged circulations.
2367 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
2368 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
2372 -- first circ in the chain
2373 circ_0 action.all_circulation_slim%ROWTYPE;
2375 -- last circ in the chain
2376 circ_n action.all_circulation_slim%ROWTYPE;
2378 -- circ chain under construction
2379 chain action.circ_chain_summary;
2380 tmp_circ action.all_circulation_slim%ROWTYPE;
2384 chain.num_circs := 0;
2385 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
2387 IF chain.num_circs = 0 THEN
2391 chain.num_circs := chain.num_circs + 1;
2395 chain.start_time := circ_0.xact_start;
2396 chain.last_stop_fines := circ_n.stop_fines;
2397 chain.last_stop_fines_time := circ_n.stop_fines_time;
2398 chain.last_checkin_time := circ_n.checkin_time;
2399 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
2400 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
2401 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
2403 IF chain.num_circs > 1 THEN
2404 chain.last_renewal_time := circ_n.xact_start;
2405 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
2411 $$ LANGUAGE 'plpgsql';
2415 SELECT evergreen.upgrade_deps_block_check('1124', :eg_version);
2417 INSERT into config.workstation_setting_type (name, grp, datatype, label)
2419 'eg.grid.circ.wide_holds.shelf', 'gui', 'object',
2421 'eg.grid.circ.wide_holds.shelf',
2422 'Grid Config: circ.wide_holds.shelf',
2426 'eg.grid.cat.catalog.wide_holds', 'gui', 'object',
2428 'eg.grid.cat.catalog.wide_holds',
2429 'Grid Config: cat.catalog.wide_holds',
2434 DELETE from config.workstation_setting_type
2435 WHERE name = 'eg.grid.cat.catalog.holds' OR name = 'eg.grid.circ.holds.shelf';
2438 SELECT evergreen.upgrade_deps_block_check('1125', :eg_version);
2440 CREATE TABLE asset.latest_inventory (
2441 id SERIAL PRIMARY KEY,
2442 inventory_workstation INTEGER REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
2443 inventory_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
2444 copy BIGINT NOT NULL
2446 CREATE INDEX latest_inventory_copy_idx ON asset.latest_inventory (copy);
2448 CREATE OR REPLACE FUNCTION evergreen.asset_latest_inventory_copy_inh_fkey() RETURNS TRIGGER AS $f$
2450 PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
2452 RAISE foreign_key_violation USING MESSAGE = FORMAT(
2453 $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
2458 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
2460 CREATE CONSTRAINT TRIGGER inherit_asset_latest_inventory_copy_fkey
2461 AFTER UPDATE OR INSERT ON asset.latest_inventory
2462 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_latest_inventory_copy_inh_fkey();
2464 INSERT into config.workstation_setting_type (name, grp, datatype, label)
2466 'eg.circ.checkin.do_inventory_update', 'circ', 'bool',
2468 'eg.circ.checkin.do_inventory_update',
2469 'Checkin: Update Inventory',
2475 SELECT evergreen.upgrade_deps_block_check('1126', :eg_version);
2477 CREATE TABLE vandelay.session_tracker (
2478 id BIGSERIAL PRIMARY KEY,
2480 -- string of characters (e.g. md5) used for linking trackers
2481 -- of different actions into a series. There can be multiple
2482 -- session_keys of each action type, creating the opportunity
2483 -- to link multiple action trackers into a single session.
2484 session_key TEXT NOT NULL,
2486 -- optional user-supplied name
2489 usr INTEGER NOT NULL REFERENCES actor.usr(id)
2490 DEFERRABLE INITIALLY DEFERRED,
2492 -- org unit can be derived from WS
2493 workstation INTEGER NOT NULL REFERENCES actor.workstation(id)
2494 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2497 record_type vandelay.bib_queue_queue_type NOT NULL DEFAULT 'bib',
2499 -- Queue defines the source of the data, it does not necessarily
2500 -- mean that an action is being performed against an entire queue.
2501 -- E.g. some imports are misc. lists of record IDs, but they always
2502 -- come from one queue.
2503 -- No foreign key -- could be auth or bib queue.
2504 queue BIGINT NOT NULL,
2506 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2507 update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2509 state TEXT NOT NULL DEFAULT 'active',
2511 action_type TEXT NOT NULL DEFAULT 'enqueue', -- import
2513 -- total number of tasks to perform / loosely defined
2514 -- could be # of recs to import or # of recs + # of copies
2515 -- depending on the import context
2516 total_actions INTEGER NOT NULL DEFAULT 0,
2518 -- total number of tasked performed so far
2519 actions_performed INTEGER NOT NULL DEFAULT 0,
2521 CONSTRAINT vand_tracker_valid_state
2522 CHECK (state IN ('active','error','complete')),
2524 CONSTRAINT vand_tracker_valid_action_type
2525 CHECK (action_type IN ('upload', 'enqueue', 'import'))
2529 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
2533 picklist_row RECORD;
2538 -- do some initial cleanup
2539 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
2540 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
2541 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
2545 DELETE FROM actor.card where usr = src_usr;
2547 IF deactivate_cards THEN
2548 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
2550 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
2555 DELETE FROM actor.usr_address WHERE usr = src_usr;
2557 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
2560 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
2561 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
2562 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
2563 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
2564 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
2567 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
2568 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
2569 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
2570 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
2575 -- For each *_bucket table: transfer every bucket belonging to src_usr
2576 -- into the custody of dest_usr.
2578 -- In order to avoid colliding with an existing bucket owned by
2579 -- the destination user, append the source user's id (in parenthesese)
2580 -- to the name. If you still get a collision, add successive
2581 -- spaces to the name and keep trying until you succeed.
2585 FROM container.biblio_record_entry_bucket
2586 WHERE owner = src_usr
2588 suffix := ' (' || src_usr || ')';
2591 UPDATE container.biblio_record_entry_bucket
2592 SET owner = dest_usr, name = name || suffix
2593 WHERE id = bucket_row.id;
2594 EXCEPTION WHEN unique_violation THEN
2595 suffix := suffix || ' ';
2604 FROM container.call_number_bucket
2605 WHERE owner = src_usr
2607 suffix := ' (' || src_usr || ')';
2610 UPDATE container.call_number_bucket
2611 SET owner = dest_usr, name = name || suffix
2612 WHERE id = bucket_row.id;
2613 EXCEPTION WHEN unique_violation THEN
2614 suffix := suffix || ' ';
2623 FROM container.copy_bucket
2624 WHERE owner = src_usr
2626 suffix := ' (' || src_usr || ')';
2629 UPDATE container.copy_bucket
2630 SET owner = dest_usr, name = name || suffix
2631 WHERE id = bucket_row.id;
2632 EXCEPTION WHEN unique_violation THEN
2633 suffix := suffix || ' ';
2642 FROM container.user_bucket
2643 WHERE owner = src_usr
2645 suffix := ' (' || src_usr || ')';
2648 UPDATE container.user_bucket
2649 SET owner = dest_usr, name = name || suffix
2650 WHERE id = bucket_row.id;
2651 EXCEPTION WHEN unique_violation THEN
2652 suffix := suffix || ' ';
2659 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
2662 -- transfer queues the same way we transfer buckets (see above)
2666 WHERE owner = src_usr
2668 suffix := ' (' || src_usr || ')';
2671 UPDATE vandelay.queue
2672 SET owner = dest_usr, name = name || suffix
2673 WHERE id = queue_row.id;
2674 EXCEPTION WHEN unique_violation THEN
2675 suffix := suffix || ' ';
2682 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
2685 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
2686 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
2687 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
2688 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
2689 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
2692 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
2693 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
2694 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
2695 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
2697 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
2698 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
2699 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
2700 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
2702 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
2703 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
2704 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
2705 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
2706 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
2709 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
2710 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
2712 -- transfer picklists the same way we transfer buckets (see above)
2716 WHERE owner = src_usr
2718 suffix := ' (' || src_usr || ')';
2722 SET owner = dest_usr, name = name || suffix
2723 WHERE id = picklist_row.id;
2724 EXCEPTION WHEN unique_violation THEN
2725 suffix := suffix || ' ';
2732 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
2733 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
2734 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
2735 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
2736 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
2737 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
2738 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
2739 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
2742 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
2743 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
2744 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
2745 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
2746 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
2747 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
2750 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
2751 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
2754 -- It's not uncommon to define the reporter schema in a replica
2755 -- DB only, so don't assume these tables exist in the write DB.
2757 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
2758 EXCEPTION WHEN undefined_table THEN
2762 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
2763 EXCEPTION WHEN undefined_table THEN
2767 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
2768 EXCEPTION WHEN undefined_table THEN
2772 -- transfer folders the same way we transfer buckets (see above)
2775 FROM reporter.template_folder
2776 WHERE owner = src_usr
2778 suffix := ' (' || src_usr || ')';
2781 UPDATE reporter.template_folder
2782 SET owner = dest_usr, name = name || suffix
2783 WHERE id = folder_row.id;
2784 EXCEPTION WHEN unique_violation THEN
2785 suffix := suffix || ' ';
2791 EXCEPTION WHEN undefined_table THEN
2795 -- transfer folders the same way we transfer buckets (see above)
2798 FROM reporter.report_folder
2799 WHERE owner = src_usr
2801 suffix := ' (' || src_usr || ')';
2804 UPDATE reporter.report_folder
2805 SET owner = dest_usr, name = name || suffix
2806 WHERE id = folder_row.id;
2807 EXCEPTION WHEN unique_violation THEN
2808 suffix := suffix || ' ';
2814 EXCEPTION WHEN undefined_table THEN
2818 -- transfer folders the same way we transfer buckets (see above)
2821 FROM reporter.output_folder
2822 WHERE owner = src_usr
2824 suffix := ' (' || src_usr || ')';
2827 UPDATE reporter.output_folder
2828 SET owner = dest_usr, name = name || suffix
2829 WHERE id = folder_row.id;
2830 EXCEPTION WHEN unique_violation THEN
2831 suffix := suffix || ' ';
2837 EXCEPTION WHEN undefined_table THEN
2841 -- propagate preferred name values from the source user to the
2842 -- destination user, but only when values are not being replaced.
2843 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
2844 UPDATE actor.usr SET
2846 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
2847 pref_first_given_name =
2848 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
2849 pref_second_given_name =
2850 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
2852 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
2854 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
2855 WHERE id = dest_usr;
2857 -- Copy and deduplicate name keywords
2858 -- String -> array -> rows -> DISTINCT -> array -> string
2859 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
2860 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
2861 UPDATE actor.usr SET name_keywords = (
2863 SELECT DISTINCT UNNEST(
2864 REGEXP_SPLIT_TO_ARRAY(
2865 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
2866 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
2869 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
2870 ) WHERE id = dest_usr;
2872 -- Finally, delete the source user
2873 DELETE FROM actor.usr WHERE id = src_usr;
2876 $$ LANGUAGE plpgsql;
2879 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
2881 specified_dest_usr IN INTEGER
2882 ) RETURNS VOID AS $$
2885 renamable_row RECORD;
2889 IF specified_dest_usr IS NULL THEN
2890 dest_usr := 1; -- Admin user on stock installs
2892 dest_usr := specified_dest_usr;
2896 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
2897 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
2898 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
2899 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
2900 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
2901 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
2902 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
2904 -- Update with a rename to avoid collisions
2905 FOR renamable_row in
2908 WHERE owner = src_usr
2910 suffix := ' (' || src_usr || ')';
2914 SET owner = dest_usr, name = name || suffix
2915 WHERE id = renamable_row.id;
2916 EXCEPTION WHEN unique_violation THEN
2917 suffix := suffix || ' ';
2924 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
2925 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
2926 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
2927 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
2928 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
2929 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
2930 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
2931 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
2934 DELETE FROM action.circulation WHERE usr = src_usr;
2935 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
2936 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
2937 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
2938 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
2939 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
2940 DELETE FROM action.hold_request WHERE usr = src_usr;
2941 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
2942 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
2943 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
2944 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
2945 DELETE FROM action.survey_response WHERE usr = src_usr;
2946 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
2947 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
2950 DELETE FROM actor.card WHERE usr = src_usr;
2951 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
2953 -- The following update is intended to avoid transient violations of a foreign
2954 -- key constraint, whereby actor.usr_address references itself. It may not be
2955 -- necessary, but it does no harm.
2956 UPDATE actor.usr_address SET replaces = NULL
2957 WHERE usr = src_usr AND replaces IS NOT NULL;
2958 DELETE FROM actor.usr_address WHERE usr = src_usr;
2959 DELETE FROM actor.usr_note WHERE usr = src_usr;
2960 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
2961 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
2962 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
2963 DELETE FROM actor.usr_setting WHERE usr = src_usr;
2964 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
2965 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
2968 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
2969 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
2970 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
2971 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
2972 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
2973 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
2976 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
2977 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
2978 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
2979 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
2980 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
2981 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
2982 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
2983 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
2986 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
2987 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
2988 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
2989 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
2992 -- Update buckets with a rename to avoid collisions
2993 FOR renamable_row in
2995 FROM container.biblio_record_entry_bucket
2996 WHERE owner = src_usr
2998 suffix := ' (' || src_usr || ')';
3001 UPDATE container.biblio_record_entry_bucket
3002 SET owner = dest_usr, name = name || suffix
3003 WHERE id = renamable_row.id;
3004 EXCEPTION WHEN unique_violation THEN
3005 suffix := suffix || ' ';
3012 FOR renamable_row in
3014 FROM container.call_number_bucket
3015 WHERE owner = src_usr
3017 suffix := ' (' || src_usr || ')';
3020 UPDATE container.call_number_bucket
3021 SET owner = dest_usr, name = name || suffix
3022 WHERE id = renamable_row.id;
3023 EXCEPTION WHEN unique_violation THEN
3024 suffix := suffix || ' ';
3031 FOR renamable_row in
3033 FROM container.copy_bucket
3034 WHERE owner = src_usr
3036 suffix := ' (' || src_usr || ')';
3039 UPDATE container.copy_bucket
3040 SET owner = dest_usr, name = name || suffix
3041 WHERE id = renamable_row.id;
3042 EXCEPTION WHEN unique_violation THEN
3043 suffix := suffix || ' ';
3050 FOR renamable_row in
3052 FROM container.user_bucket
3053 WHERE owner = src_usr
3055 suffix := ' (' || src_usr || ')';
3058 UPDATE container.user_bucket
3059 SET owner = dest_usr, name = name || suffix
3060 WHERE id = renamable_row.id;
3061 EXCEPTION WHEN unique_violation THEN
3062 suffix := suffix || ' ';
3069 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
3072 DELETE FROM money.billable_xact WHERE usr = src_usr;
3073 DELETE FROM money.collections_tracker WHERE usr = src_usr;
3074 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
3077 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
3078 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
3079 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
3080 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
3083 -- Update with a rename to avoid collisions
3085 FOR renamable_row in
3087 FROM reporter.output_folder
3088 WHERE owner = src_usr
3090 suffix := ' (' || src_usr || ')';
3093 UPDATE reporter.output_folder
3094 SET owner = dest_usr, name = name || suffix
3095 WHERE id = renamable_row.id;
3096 EXCEPTION WHEN unique_violation THEN
3097 suffix := suffix || ' ';
3103 EXCEPTION WHEN undefined_table THEN
3108 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
3109 EXCEPTION WHEN undefined_table THEN
3113 -- Update with a rename to avoid collisions
3115 FOR renamable_row in
3117 FROM reporter.report_folder
3118 WHERE owner = src_usr
3120 suffix := ' (' || src_usr || ')';
3123 UPDATE reporter.report_folder
3124 SET owner = dest_usr, name = name || suffix
3125 WHERE id = renamable_row.id;
3126 EXCEPTION WHEN unique_violation THEN
3127 suffix := suffix || ' ';
3133 EXCEPTION WHEN undefined_table THEN
3138 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
3139 EXCEPTION WHEN undefined_table THEN
3144 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
3145 EXCEPTION WHEN undefined_table THEN
3149 -- Update with a rename to avoid collisions
3151 FOR renamable_row in
3153 FROM reporter.template_folder
3154 WHERE owner = src_usr
3156 suffix := ' (' || src_usr || ')';
3159 UPDATE reporter.template_folder
3160 SET owner = dest_usr, name = name || suffix
3161 WHERE id = renamable_row.id;
3162 EXCEPTION WHEN unique_violation THEN
3163 suffix := suffix || ' ';
3169 EXCEPTION WHEN undefined_table THEN
3174 -- Update with a rename to avoid collisions
3175 FOR renamable_row in
3178 WHERE owner = src_usr
3180 suffix := ' (' || src_usr || ')';
3183 UPDATE vandelay.queue
3184 SET owner = dest_usr, name = name || suffix
3185 WHERE id = renamable_row.id;
3186 EXCEPTION WHEN unique_violation THEN
3187 suffix := suffix || ' ';
3194 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
3196 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
3197 -- can access the information before deletion.
3198 UPDATE actor.usr SET
3201 mailing_address = NULL,
3202 billing_address = NULL
3206 $$ LANGUAGE plpgsql;
3210 SELECT evergreen.upgrade_deps_block_check('1127', :eg_version);
3212 ALTER TABLE acq.user_request ADD COLUMN cancel_time TIMESTAMPTZ;
3213 ALTER TABLE acq.user_request ADD COLUMN upc TEXT;
3214 ALTER TABLE action.hold_request ADD COLUMN acq_request INT REFERENCES acq.user_request (id);
3217 config.org_unit_setting_type
3219 label = oils_i18n_gettext(
3220 'circ.holds.canceled.display_age',
3221 'Canceled holds/requests display age',
3223 description = oils_i18n_gettext(
3224 'circ.holds.canceled.display_age',
3225 'Show all canceled entries in patron holds and patron acquisition requests interfaces that were canceled within this amount of time',
3226 'coust', 'description')
3228 name = 'circ.holds.canceled.display_age'
3232 config.org_unit_setting_type
3234 label = oils_i18n_gettext(
3235 'circ.holds.canceled.display_count',
3236 'Canceled holds/requests display count',
3238 description = oils_i18n_gettext(
3239 'circ.holds.canceled.display_count',
3240 'How many canceled entries to show in patron holds and patron acquisition requests interfaces',
3241 'coust', 'description')
3243 name = 'circ.holds.canceled.display_count'
3246 INSERT INTO acq.cancel_reason (org_unit, keep_debits, id, label, description)
3249 oils_i18n_gettext(1015, 'Canceled: Fulfilled', 'acqcr', 'label'),
3250 oils_i18n_gettext(1015, 'This acquisition request has been fulfilled.', 'acqcr', 'description')
3255 acq.user_request_type
3257 label = oils_i18n_gettext('2', 'Articles', 'aurt', 'label')
3262 INSERT INTO acq.user_request_type (id,label)
3263 SELECT 6, oils_i18n_gettext('6', 'Other', 'aurt', 'label');
3265 SELECT SETVAL('acq.user_request_type_id_seq'::TEXT, (SELECT MAX(id)+1 FROM acq.user_request_type));
3267 INSERT INTO permission.perm_list ( id, code, description ) VALUES
3268 ( 610, 'CLEAR_PURCHASE_REQUEST', oils_i18n_gettext(610,
3269 'Clear Completed User Purchase Requests', 'ppl', 'description'))
3272 CREATE TABLE acq.user_request_status_type (
3273 id SERIAL PRIMARY KEY
3277 INSERT INTO acq.user_request_status_type (id,label) VALUES
3278 (0,oils_i18n_gettext(0,'Error','aurst','label'))
3279 ,(1,oils_i18n_gettext(1,'New','aurst','label'))
3280 ,(2,oils_i18n_gettext(2,'Pending','aurst','label'))
3281 ,(3,oils_i18n_gettext(3,'Ordered, Hold Not Placed','aurst','label'))
3282 ,(4,oils_i18n_gettext(4,'Ordered, Hold Placed','aurst','label'))
3283 ,(5,oils_i18n_gettext(5,'Received','aurst','label'))
3284 ,(6,oils_i18n_gettext(6,'Fulfilled','aurst','label'))
3285 ,(7,oils_i18n_gettext(7,'Canceled','aurst','label'))
3288 SELECT SETVAL('acq.user_request_status_type_id_seq'::TEXT, 100);
3291 DELETE FROM actor.org_unit_setting WHERE name = 'acq.holds.allow_holds_from_purchase_request';
3292 DELETE FROM config.org_unit_setting_type_log WHERE field_name = 'acq.holds.allow_holds_from_purchase_request';
3293 DELETE FROM config.org_unit_setting_type WHERE name = 'acq.holds.allow_holds_from_purchase_request';
3296 SELECT evergreen.upgrade_deps_block_check('1128', :eg_version);
3298 DROP VIEW auditor.acq_invoice_lifecycle;
3300 ALTER TABLE acq.invoice
3301 ADD COLUMN close_date TIMESTAMPTZ,
3302 ADD COLUMN closed_by INTEGER
3303 REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED;
3305 -- duplicate steps for auditor table
3306 ALTER TABLE auditor.acq_invoice_history
3307 ADD COLUMN close_date TIMESTAMPTZ,
3308 ADD COLUMN closed_by INTEGER;
3310 UPDATE acq.invoice SET close_date = NOW() WHERE complete;
3311 UPDATE auditor.acq_invoice_history SET close_date = NOW() WHERE complete;
3313 ALTER TABLE acq.invoice DROP COLUMN complete;
3314 ALTER TABLE auditor.acq_invoice_history DROP COLUMN complete;
3316 -- this recreates auditor.acq_invoice_lifecycle;
3317 SELECT auditor.update_auditors();
3319 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
3323 picklist_row RECORD;
3328 -- do some initial cleanup
3329 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
3330 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
3331 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
3335 DELETE FROM actor.card where usr = src_usr;
3337 IF deactivate_cards THEN
3338 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
3340 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
3345 DELETE FROM actor.usr_address WHERE usr = src_usr;
3347 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
3350 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
3351 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
3352 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
3353 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
3354 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
3357 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
3358 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
3359 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
3360 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
3365 -- For each *_bucket table: transfer every bucket belonging to src_usr
3366 -- into the custody of dest_usr.
3368 -- In order to avoid colliding with an existing bucket owned by
3369 -- the destination user, append the source user's id (in parenthesese)
3370 -- to the name. If you still get a collision, add successive
3371 -- spaces to the name and keep trying until you succeed.
3375 FROM container.biblio_record_entry_bucket
3376 WHERE owner = src_usr
3378 suffix := ' (' || src_usr || ')';
3381 UPDATE container.biblio_record_entry_bucket
3382 SET owner = dest_usr, name = name || suffix
3383 WHERE id = bucket_row.id;
3384 EXCEPTION WHEN unique_violation THEN
3385 suffix := suffix || ' ';
3394 FROM container.call_number_bucket
3395 WHERE owner = src_usr
3397 suffix := ' (' || src_usr || ')';
3400 UPDATE container.call_number_bucket
3401 SET owner = dest_usr, name = name || suffix
3402 WHERE id = bucket_row.id;
3403 EXCEPTION WHEN unique_violation THEN
3404 suffix := suffix || ' ';
3413 FROM container.copy_bucket
3414 WHERE owner = src_usr
3416 suffix := ' (' || src_usr || ')';
3419 UPDATE container.copy_bucket
3420 SET owner = dest_usr, name = name || suffix
3421 WHERE id = bucket_row.id;
3422 EXCEPTION WHEN unique_violation THEN
3423 suffix := suffix || ' ';
3432 FROM container.user_bucket
3433 WHERE owner = src_usr
3435 suffix := ' (' || src_usr || ')';
3438 UPDATE container.user_bucket
3439 SET owner = dest_usr, name = name || suffix
3440 WHERE id = bucket_row.id;
3441 EXCEPTION WHEN unique_violation THEN
3442 suffix := suffix || ' ';
3449 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
3452 -- transfer queues the same way we transfer buckets (see above)
3456 WHERE owner = src_usr
3458 suffix := ' (' || src_usr || ')';
3461 UPDATE vandelay.queue
3462 SET owner = dest_usr, name = name || suffix
3463 WHERE id = queue_row.id;
3464 EXCEPTION WHEN unique_violation THEN
3465 suffix := suffix || ' ';
3473 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
3474 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
3475 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
3476 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
3477 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
3480 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
3481 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
3482 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
3483 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
3485 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
3486 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
3487 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
3488 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
3490 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
3491 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
3492 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
3493 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
3494 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
3497 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
3498 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
3499 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
3501 -- transfer picklists the same way we transfer buckets (see above)
3505 WHERE owner = src_usr
3507 suffix := ' (' || src_usr || ')';
3511 SET owner = dest_usr, name = name || suffix
3512 WHERE id = picklist_row.id;
3513 EXCEPTION WHEN unique_violation THEN
3514 suffix := suffix || ' ';
3521 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
3522 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
3523 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
3524 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
3525 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
3526 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
3527 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
3528 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
3531 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
3532 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
3533 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
3534 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
3535 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
3536 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
3539 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
3540 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
3543 -- It's not uncommon to define the reporter schema in a replica
3544 -- DB only, so don't assume these tables exist in the write DB.
3546 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
3547 EXCEPTION WHEN undefined_table THEN
3551 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
3552 EXCEPTION WHEN undefined_table THEN
3556 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
3557 EXCEPTION WHEN undefined_table THEN
3561 -- transfer folders the same way we transfer buckets (see above)
3564 FROM reporter.template_folder
3565 WHERE owner = src_usr
3567 suffix := ' (' || src_usr || ')';
3570 UPDATE reporter.template_folder
3571 SET owner = dest_usr, name = name || suffix
3572 WHERE id = folder_row.id;
3573 EXCEPTION WHEN unique_violation THEN
3574 suffix := suffix || ' ';
3580 EXCEPTION WHEN undefined_table THEN
3584 -- transfer folders the same way we transfer buckets (see above)
3587 FROM reporter.report_folder
3588 WHERE owner = src_usr
3590 suffix := ' (' || src_usr || ')';
3593 UPDATE reporter.report_folder
3594 SET owner = dest_usr, name = name || suffix
3595 WHERE id = folder_row.id;
3596 EXCEPTION WHEN unique_violation THEN
3597 suffix := suffix || ' ';
3603 EXCEPTION WHEN undefined_table THEN
3607 -- transfer folders the same way we transfer buckets (see above)
3610 FROM reporter.output_folder
3611 WHERE owner = src_usr
3613 suffix := ' (' || src_usr || ')';
3616 UPDATE reporter.output_folder
3617 SET owner = dest_usr, name = name || suffix
3618 WHERE id = folder_row.id;
3619 EXCEPTION WHEN unique_violation THEN
3620 suffix := suffix || ' ';
3626 EXCEPTION WHEN undefined_table THEN
3630 -- Finally, delete the source user
3631 DELETE FROM actor.usr WHERE id = src_usr;
3634 $$ LANGUAGE plpgsql;
3637 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
3639 specified_dest_usr IN INTEGER
3640 ) RETURNS VOID AS $$
3643 renamable_row RECORD;
3647 IF specified_dest_usr IS NULL THEN
3648 dest_usr := 1; -- Admin user on stock installs
3650 dest_usr := specified_dest_usr;
3654 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
3655 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
3656 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
3657 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
3658 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
3659 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
3660 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
3661 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
3663 -- Update with a rename to avoid collisions
3664 FOR renamable_row in
3667 WHERE owner = src_usr
3669 suffix := ' (' || src_usr || ')';
3673 SET owner = dest_usr, name = name || suffix
3674 WHERE id = renamable_row.id;
3675 EXCEPTION WHEN unique_violation THEN
3676 suffix := suffix || ' ';
3683 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
3684 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
3685 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
3686 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
3687 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
3688 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
3689 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
3690 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
3693 DELETE FROM action.circulation WHERE usr = src_usr;
3694 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
3695 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
3696 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
3697 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
3698 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
3699 DELETE FROM action.hold_request WHERE usr = src_usr;
3700 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
3701 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
3702 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
3703 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
3704 DELETE FROM action.survey_response WHERE usr = src_usr;
3705 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
3706 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
3709 DELETE FROM actor.card WHERE usr = src_usr;
3710 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
3712 -- The following update is intended to avoid transient violations of a foreign
3713 -- key constraint, whereby actor.usr_address references itself. It may not be
3714 -- necessary, but it does no harm.
3715 UPDATE actor.usr_address SET replaces = NULL
3716 WHERE usr = src_usr AND replaces IS NOT NULL;
3717 DELETE FROM actor.usr_address WHERE usr = src_usr;
3718 DELETE FROM actor.usr_note WHERE usr = src_usr;
3719 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
3720 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
3721 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
3722 DELETE FROM actor.usr_setting WHERE usr = src_usr;
3723 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
3724 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
3727 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
3728 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
3729 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
3730 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
3731 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
3732 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
3735 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
3736 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
3737 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
3738 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
3739 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
3740 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
3741 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
3742 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
3745 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
3746 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
3747 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
3748 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
3751 -- Update buckets with a rename to avoid collisions
3752 FOR renamable_row in
3754 FROM container.biblio_record_entry_bucket
3755 WHERE owner = src_usr
3757 suffix := ' (' || src_usr || ')';
3760 UPDATE container.biblio_record_entry_bucket
3761 SET owner = dest_usr, name = name || suffix
3762 WHERE id = renamable_row.id;
3763 EXCEPTION WHEN unique_violation THEN
3764 suffix := suffix || ' ';
3771 FOR renamable_row in
3773 FROM container.call_number_bucket
3774 WHERE owner = src_usr
3776 suffix := ' (' || src_usr || ')';
3779 UPDATE container.call_number_bucket
3780 SET owner = dest_usr, name = name || suffix
3781 WHERE id = renamable_row.id;
3782 EXCEPTION WHEN unique_violation THEN
3783 suffix := suffix || ' ';
3790 FOR renamable_row in
3792 FROM container.copy_bucket
3793 WHERE owner = src_usr
3795 suffix := ' (' || src_usr || ')';
3798 UPDATE container.copy_bucket
3799 SET owner = dest_usr, name = name || suffix
3800 WHERE id = renamable_row.id;
3801 EXCEPTION WHEN unique_violation THEN
3802 suffix := suffix || ' ';
3809 FOR renamable_row in
3811 FROM container.user_bucket
3812 WHERE owner = src_usr
3814 suffix := ' (' || src_usr || ')';
3817 UPDATE container.user_bucket
3818 SET owner = dest_usr, name = name || suffix
3819 WHERE id = renamable_row.id;
3820 EXCEPTION WHEN unique_violation THEN
3821 suffix := suffix || ' ';
3828 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
3831 DELETE FROM money.billable_xact WHERE usr = src_usr;
3832 DELETE FROM money.collections_tracker WHERE usr = src_usr;
3833 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
3836 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
3837 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
3838 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
3839 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
3842 -- Update with a rename to avoid collisions
3844 FOR renamable_row in
3846 FROM reporter.output_folder
3847 WHERE owner = src_usr
3849 suffix := ' (' || src_usr || ')';
3852 UPDATE reporter.output_folder
3853 SET owner = dest_usr, name = name || suffix
3854 WHERE id = renamable_row.id;
3855 EXCEPTION WHEN unique_violation THEN
3856 suffix := suffix || ' ';
3862 EXCEPTION WHEN undefined_table THEN
3867 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
3868 EXCEPTION WHEN undefined_table THEN
3872 -- Update with a rename to avoid collisions
3874 FOR renamable_row in
3876 FROM reporter.report_folder
3877 WHERE owner = src_usr
3879 suffix := ' (' || src_usr || ')';
3882 UPDATE reporter.report_folder
3883 SET owner = dest_usr, name = name || suffix
3884 WHERE id = renamable_row.id;
3885 EXCEPTION WHEN unique_violation THEN
3886 suffix := suffix || ' ';
3892 EXCEPTION WHEN undefined_table THEN
3897 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
3898 EXCEPTION WHEN undefined_table THEN
3903 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
3904 EXCEPTION WHEN undefined_table THEN
3908 -- Update with a rename to avoid collisions
3910 FOR renamable_row in
3912 FROM reporter.template_folder
3913 WHERE owner = src_usr
3915 suffix := ' (' || src_usr || ')';
3918 UPDATE reporter.template_folder
3919 SET owner = dest_usr, name = name || suffix
3920 WHERE id = renamable_row.id;
3921 EXCEPTION WHEN unique_violation THEN
3922 suffix := suffix || ' ';
3928 EXCEPTION WHEN undefined_table THEN
3933 -- Update with a rename to avoid collisions
3934 FOR renamable_row in
3937 WHERE owner = src_usr
3939 suffix := ' (' || src_usr || ')';
3942 UPDATE vandelay.queue
3943 SET owner = dest_usr, name = name || suffix
3944 WHERE id = renamable_row.id;
3945 EXCEPTION WHEN unique_violation THEN
3946 suffix := suffix || ' ';
3953 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
3954 -- can access the information before deletion.
3955 UPDATE actor.usr SET
3958 mailing_address = NULL,
3959 billing_address = NULL
3963 $$ LANGUAGE plpgsql;
3969 -- UNDO (minus user purge/merge changes)
3972 DROP VIEW auditor.acq_invoice_lifecycle;
3973 ALTER TABLE acq.invoice ADD COLUMN complete BOOLEAN NOT NULL DEFAULT FALSE;
3974 ALTER TABLE auditor.acq_invoice_history
3975 ADD COLUMN complete BOOLEAN NOT NULL DEFAULT FALSE;
3976 UPDATE acq.invoice SET complete = TRUE where close_date IS NOT NULL;
3977 UPDATE auditor.acq_invoice_history
3978 SET complete = TRUE where close_date IS NOT NULL;
3979 SET CONSTRAINTS ALL IMMEDIATE; -- or get pending triggers error.
3980 ALTER TABLE acq.invoice DROP COLUMN close_date, DROP COLUMN closed_by;
3981 ALTER TABLE auditor.acq_invoice_history
3982 DROP COLUMN close_date, DROP COLUMN closed_by;
3983 SELECT auditor.update_auditors();
3988 SELECT evergreen.upgrade_deps_block_check('1129', :eg_version);
3990 INSERT into config.workstation_setting_type (name, grp, datatype, label)
3992 'eg.grid.admin.acq.cancel_reason', 'gui', 'object',
3994 'eg.grid.admin.acq.cancel_reason',
3995 'Grid Config: admin.acq.cancel_reason',
3999 'eg.grid.admin.acq.claim_event_type', 'gui', 'object',
4001 'eg.grid.admin.acq.claim_event_type',
4002 'Grid Config: admin.acq.claim_event_type',
4006 'eg.grid.admin.acq.claim_policy', 'gui', 'object',
4008 'eg.grid.admin.acq.claim_policy',
4009 'Grid Config: admin.acq.claim_policy',
4013 'eg.grid.admin.acq.claim_policy_action', 'gui', 'object',
4015 'eg.grid.admin.acq.claim_policy_action',
4016 'Grid Config: admin.acq.claim_policy_action',
4020 'eg.grid.admin.acq.claim_type', 'gui', 'object',
4022 'eg.grid.admin.acq.claim_type',
4023 'Grid Config: admin.acq.claim_type',
4027 'eg.grid.admin.acq.currency_type', 'gui', 'object',
4029 'eg.grid.admin.acq.currency_type',
4030 'Grid Config: admin.acq.currency_type',
4034 'eg.grid.admin.acq.edi_account', 'gui', 'object',
4036 'eg.grid.admin.acq.edi_account',
4037 'Grid Config: admin.acq.edi_account',
4041 'eg.grid.admin.acq.edi_message', 'gui', 'object',
4043 'eg.grid.admin.acq.edi_message',
4044 'Grid Config: admin.acq.edi_message',
4048 'eg.grid.admin.acq.exchange_rate', 'gui', 'object',
4050 'eg.grid.admin.acq.exchange_rate',
4051 'Grid Config: admin.acq.exchange_rate',
4055 'eg.grid.admin.acq.fund_tag', 'gui', 'object',
4057 'eg.grid.admin.acq.fund_tag',
4058 'Grid Config: admin.acq.fund_tag',
4062 'eg.grid.admin.acq.invoice_item_type', 'gui', 'object',
4064 'eg.grid.admin.acq.invoice_item_type',
4065 'Grid Config: admin.acq.invoice_item_type',
4069 'eg.grid.admin.acq.invoice_payment_method', 'gui', 'object',
4071 'eg.grid.admin.acq.invoice_payment_method',
4072 'Grid Config: admin.acq.invoice_payment_method',
4076 'eg.grid.admin.acq.lineitem_alert_text', 'gui', 'object',
4078 'eg.grid.admin.acq.lineitem_alert_text',
4079 'Grid Config: admin.acq.lineitem_alert_text',
4083 'eg.grid.admin.acq.lineitem_marc_attr_definition', 'gui', 'object',
4085 'eg.grid.admin.acq.lineitem_marc_attr_definition',
4086 'Grid Config: admin.acq.lineitem_marc_attr_definition',
4092 SELECT evergreen.upgrade_deps_block_check('1130', :eg_version);
4094 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
4098 picklist_row RECORD;
4103 -- Bail if src_usr equals dest_usr because the result of merging a
4104 -- user with itself is not what you want.
4105 IF src_usr = dest_usr THEN
4109 -- do some initial cleanup
4110 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
4111 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
4112 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
4116 DELETE FROM actor.card where usr = src_usr;
4118 IF deactivate_cards THEN
4119 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
4121 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
4126 DELETE FROM actor.usr_address WHERE usr = src_usr;
4128 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
4131 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
4132 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
4133 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
4134 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
4135 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
4138 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
4139 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
4140 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
4141 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
4146 -- For each *_bucket table: transfer every bucket belonging to src_usr
4147 -- into the custody of dest_usr.
4149 -- In order to avoid colliding with an existing bucket owned by
4150 -- the destination user, append the source user's id (in parenthesese)
4151 -- to the name. If you still get a collision, add successive
4152 -- spaces to the name and keep trying until you succeed.
4156 FROM container.biblio_record_entry_bucket
4157 WHERE owner = src_usr
4159 suffix := ' (' || src_usr || ')';
4162 UPDATE container.biblio_record_entry_bucket
4163 SET owner = dest_usr, name = name || suffix
4164 WHERE id = bucket_row.id;
4165 EXCEPTION WHEN unique_violation THEN
4166 suffix := suffix || ' ';
4175 FROM container.call_number_bucket
4176 WHERE owner = src_usr
4178 suffix := ' (' || src_usr || ')';
4181 UPDATE container.call_number_bucket
4182 SET owner = dest_usr, name = name || suffix
4183 WHERE id = bucket_row.id;
4184 EXCEPTION WHEN unique_violation THEN
4185 suffix := suffix || ' ';
4194 FROM container.copy_bucket
4195 WHERE owner = src_usr
4197 suffix := ' (' || src_usr || ')';
4200 UPDATE container.copy_bucket
4201 SET owner = dest_usr, name = name || suffix
4202 WHERE id = bucket_row.id;
4203 EXCEPTION WHEN unique_violation THEN
4204 suffix := suffix || ' ';
4213 FROM container.user_bucket
4214 WHERE owner = src_usr
4216 suffix := ' (' || src_usr || ')';
4219 UPDATE container.user_bucket
4220 SET owner = dest_usr, name = name || suffix
4221 WHERE id = bucket_row.id;
4222 EXCEPTION WHEN unique_violation THEN
4223 suffix := suffix || ' ';
4230 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
4233 -- transfer queues the same way we transfer buckets (see above)
4237 WHERE owner = src_usr
4239 suffix := ' (' || src_usr || ')';
4242 UPDATE vandelay.queue
4243 SET owner = dest_usr, name = name || suffix
4244 WHERE id = queue_row.id;
4245 EXCEPTION WHEN unique_violation THEN
4246 suffix := suffix || ' ';
4253 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
4256 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
4257 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
4258 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
4259 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
4260 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
4263 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
4264 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
4265 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
4266 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
4268 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
4269 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
4270 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
4271 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
4273 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
4274 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
4275 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
4276 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
4277 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
4280 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
4281 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
4282 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
4284 -- transfer picklists the same way we transfer buckets (see above)
4288 WHERE owner = src_usr
4290 suffix := ' (' || src_usr || ')';
4294 SET owner = dest_usr, name = name || suffix
4295 WHERE id = picklist_row.id;
4296 EXCEPTION WHEN unique_violation THEN
4297 suffix := suffix || ' ';
4304 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
4305 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
4306 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
4307 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
4308 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
4309 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
4310 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
4311 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
4314 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
4315 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
4316 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
4317 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
4318 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
4319 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
4322 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
4323 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
4326 -- It's not uncommon to define the reporter schema in a replica
4327 -- DB only, so don't assume these tables exist in the write DB.
4329 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
4330 EXCEPTION WHEN undefined_table THEN
4334 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
4335 EXCEPTION WHEN undefined_table THEN
4339 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
4340 EXCEPTION WHEN undefined_table THEN
4344 -- transfer folders the same way we transfer buckets (see above)
4347 FROM reporter.template_folder
4348 WHERE owner = src_usr
4350 suffix := ' (' || src_usr || ')';
4353 UPDATE reporter.template_folder
4354 SET owner = dest_usr, name = name || suffix
4355 WHERE id = folder_row.id;
4356 EXCEPTION WHEN unique_violation THEN
4357 suffix := suffix || ' ';
4363 EXCEPTION WHEN undefined_table THEN
4367 -- transfer folders the same way we transfer buckets (see above)
4370 FROM reporter.report_folder
4371 WHERE owner = src_usr
4373 suffix := ' (' || src_usr || ')';
4376 UPDATE reporter.report_folder
4377 SET owner = dest_usr, name = name || suffix
4378 WHERE id = folder_row.id;
4379 EXCEPTION WHEN unique_violation THEN
4380 suffix := suffix || ' ';
4386 EXCEPTION WHEN undefined_table THEN
4390 -- transfer folders the same way we transfer buckets (see above)
4393 FROM reporter.output_folder
4394 WHERE owner = src_usr
4396 suffix := ' (' || src_usr || ')';
4399 UPDATE reporter.output_folder
4400 SET owner = dest_usr, name = name || suffix
4401 WHERE id = folder_row.id;
4402 EXCEPTION WHEN unique_violation THEN
4403 suffix := suffix || ' ';
4409 EXCEPTION WHEN undefined_table THEN
4413 -- propagate preferred name values from the source user to the
4414 -- destination user, but only when values are not being replaced.
4415 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
4416 UPDATE actor.usr SET
4418 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
4419 pref_first_given_name =
4420 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
4421 pref_second_given_name =
4422 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
4424 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
4426 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
4427 WHERE id = dest_usr;
4429 -- Copy and deduplicate name keywords
4430 -- String -> array -> rows -> DISTINCT -> array -> string
4431 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
4432 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
4433 UPDATE actor.usr SET name_keywords = (
4435 SELECT DISTINCT UNNEST(
4436 REGEXP_SPLIT_TO_ARRAY(
4437 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
4438 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
4441 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
4442 ) WHERE id = dest_usr;
4444 -- Finally, delete the source user
4445 DELETE FROM actor.usr WHERE id = src_usr;
4448 $$ LANGUAGE plpgsql;
4451 SELECT evergreen.upgrade_deps_block_check('1131', :eg_version);
4453 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
4457 picklist_row RECORD;
4462 -- Bail if src_usr equals dest_usr because the result of merging a
4463 -- user with itself is not what you want.
4464 IF src_usr = dest_usr THEN
4468 -- do some initial cleanup
4469 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
4470 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
4471 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
4475 DELETE FROM actor.card where usr = src_usr;
4477 IF deactivate_cards THEN
4478 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
4480 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
4485 DELETE FROM actor.usr_address WHERE usr = src_usr;
4487 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
4490 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
4491 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
4492 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
4493 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
4494 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
4497 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
4498 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
4499 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
4500 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
4505 -- For each *_bucket table: transfer every bucket belonging to src_usr
4506 -- into the custody of dest_usr.
4508 -- In order to avoid colliding with an existing bucket owned by
4509 -- the destination user, append the source user's id (in parenthesese)
4510 -- to the name. If you still get a collision, add successive
4511 -- spaces to the name and keep trying until you succeed.
4515 FROM container.biblio_record_entry_bucket
4516 WHERE owner = src_usr
4518 suffix := ' (' || src_usr || ')';
4521 UPDATE container.biblio_record_entry_bucket
4522 SET owner = dest_usr, name = name || suffix
4523 WHERE id = bucket_row.id;
4524 EXCEPTION WHEN unique_violation THEN
4525 suffix := suffix || ' ';
4534 FROM container.call_number_bucket
4535 WHERE owner = src_usr
4537 suffix := ' (' || src_usr || ')';
4540 UPDATE container.call_number_bucket
4541 SET owner = dest_usr, name = name || suffix
4542 WHERE id = bucket_row.id;
4543 EXCEPTION WHEN unique_violation THEN
4544 suffix := suffix || ' ';
4553 FROM container.copy_bucket
4554 WHERE owner = src_usr
4556 suffix := ' (' || src_usr || ')';
4559 UPDATE container.copy_bucket
4560 SET owner = dest_usr, name = name || suffix
4561 WHERE id = bucket_row.id;
4562 EXCEPTION WHEN unique_violation THEN
4563 suffix := suffix || ' ';
4572 FROM container.user_bucket
4573 WHERE owner = src_usr
4575 suffix := ' (' || src_usr || ')';
4578 UPDATE container.user_bucket
4579 SET owner = dest_usr, name = name || suffix
4580 WHERE id = bucket_row.id;
4581 EXCEPTION WHEN unique_violation THEN
4582 suffix := suffix || ' ';
4589 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
4592 -- transfer queues the same way we transfer buckets (see above)
4596 WHERE owner = src_usr
4598 suffix := ' (' || src_usr || ')';
4601 UPDATE vandelay.queue
4602 SET owner = dest_usr, name = name || suffix
4603 WHERE id = queue_row.id;
4604 EXCEPTION WHEN unique_violation THEN
4605 suffix := suffix || ' ';
4612 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
4615 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
4616 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
4617 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
4618 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
4619 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
4622 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
4623 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
4624 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
4625 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
4627 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
4628 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
4629 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
4630 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
4632 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
4633 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
4634 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
4635 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
4636 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
4639 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
4640 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
4641 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
4643 -- transfer picklists the same way we transfer buckets (see above)
4647 WHERE owner = src_usr
4649 suffix := ' (' || src_usr || ')';
4653 SET owner = dest_usr, name = name || suffix
4654 WHERE id = picklist_row.id;
4655 EXCEPTION WHEN unique_violation THEN
4656 suffix := suffix || ' ';
4663 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
4664 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
4665 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
4666 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
4667 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
4668 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
4669 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
4670 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
4673 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
4674 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
4675 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
4676 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
4677 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
4678 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
4681 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
4682 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
4685 -- It's not uncommon to define the reporter schema in a replica
4686 -- DB only, so don't assume these tables exist in the write DB.
4688 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
4689 EXCEPTION WHEN undefined_table THEN
4693 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
4694 EXCEPTION WHEN undefined_table THEN
4698 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
4699 EXCEPTION WHEN undefined_table THEN
4703 -- transfer folders the same way we transfer buckets (see above)
4706 FROM reporter.template_folder
4707 WHERE owner = src_usr
4709 suffix := ' (' || src_usr || ')';
4712 UPDATE reporter.template_folder
4713 SET owner = dest_usr, name = name || suffix
4714 WHERE id = folder_row.id;
4715 EXCEPTION WHEN unique_violation THEN
4716 suffix := suffix || ' ';
4722 EXCEPTION WHEN undefined_table THEN
4726 -- transfer folders the same way we transfer buckets (see above)
4729 FROM reporter.report_folder
4730 WHERE owner = src_usr
4732 suffix := ' (' || src_usr || ')';
4735 UPDATE reporter.report_folder
4736 SET owner = dest_usr, name = name || suffix
4737 WHERE id = folder_row.id;
4738 EXCEPTION WHEN unique_violation THEN
4739 suffix := suffix || ' ';
4745 EXCEPTION WHEN undefined_table THEN
4749 -- transfer folders the same way we transfer buckets (see above)
4752 FROM reporter.output_folder
4753 WHERE owner = src_usr
4755 suffix := ' (' || src_usr || ')';
4758 UPDATE reporter.output_folder
4759 SET owner = dest_usr, name = name || suffix
4760 WHERE id = folder_row.id;
4761 EXCEPTION WHEN unique_violation THEN
4762 suffix := suffix || ' ';
4768 EXCEPTION WHEN undefined_table THEN
4772 -- propagate preferred name values from the source user to the
4773 -- destination user, but only when values are not being replaced.
4774 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
4775 UPDATE actor.usr SET
4777 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
4778 pref_first_given_name =
4779 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
4780 pref_second_given_name =
4781 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
4783 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
4785 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
4786 WHERE id = dest_usr;
4788 -- Copy and deduplicate name keywords
4789 -- String -> array -> rows -> DISTINCT -> array -> string
4790 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
4791 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
4792 UPDATE actor.usr SET name_keywords = (
4794 SELECT DISTINCT UNNEST(
4795 REGEXP_SPLIT_TO_ARRAY(
4796 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
4797 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
4800 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
4801 ) WHERE id = dest_usr;
4803 -- Finally, delete the source user
4804 DELETE FROM actor.usr WHERE id = src_usr;
4807 $$ LANGUAGE plpgsql;
4810 -- check whether patch can be applied
4811 SELECT evergreen.upgrade_deps_block_check('1132', :eg_version); -- remingtron/csharp
4813 -- fix two typo/pasto's in setting descriptions
4814 UPDATE config.org_unit_setting_type
4815 SET description = oils_i18n_gettext(
4816 'circ.copy_alerts.forgive_fines_on_long_overdue_checkin',
4817 'Controls whether fines are automatically forgiven when checking out an '||
4818 'item that has been marked as long-overdue, and the corresponding copy alert has been '||
4820 'coust', 'description'
4822 WHERE NAME = 'circ.copy_alerts.forgive_fines_on_long_overdue_checkin';
4824 UPDATE config.org_unit_setting_type
4825 SET description = oils_i18n_gettext(
4826 'circ.longoverdue.xact_open_on_zero',
4827 'Leave transaction open when long-overdue balance equals zero. ' ||
4828 'This leaves the long-overdue copy on the patron record when it is paid',
4829 'coust', 'description'
4831 WHERE NAME = 'circ.longoverdue.xact_open_on_zero';
4835 SELECT evergreen.upgrade_deps_block_check('1133', :eg_version);
4838 Unique indexes are not inherited by child tables, so they will not prevent
4839 duplicate inserts on action.transit_copy and action.hold_transit_copy,
4840 for example. Use check constraints instead to enforce unique-per-copy
4841 transits accross all transit types.
4844 -- Create an index for speedy check constraint lookups.
4845 CREATE INDEX active_transit_for_copy
4846 ON action.transit_copy (target_copy)
4847 WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
4849 -- Check for duplicate transits across all transit types
4850 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
4851 RETURNS TRIGGER AS $func$
4853 PERFORM * FROM action.transit_copy
4854 WHERE target_copy = NEW.target_copy
4855 AND dest_recv_time IS NULL
4856 AND cancel_time IS NULL;
4858 RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
4862 $func$ LANGUAGE PLPGSQL STABLE;
4864 -- Apply constraint to all transit tables
4865 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
4866 AFTER INSERT ON action.transit_copy
4867 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
4869 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
4870 AFTER INSERT ON action.hold_transit_copy
4871 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
4873 CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check
4874 AFTER INSERT ON action.reservation_transit_copy
4875 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
4879 DROP TRIGGER transit_copy_is_unique_check ON action.transit_copy;
4880 DROP TRIGGER hold_transit_copy_is_unique_check ON action.hold_transit_copy;
4881 DROP TRIGGER reservation_transit_copy_is_unique_check ON action.reservation_transit_copy;
4882 DROP INDEX action.active_transit_for_copy;
4888 \qecho A unique constraint was applied to action.transit_copy. This will
4889 \qecho only effect newly created transits. Admins are encouraged to manually
4890 \qecho remove any existing duplicate transits by applying values for cancel_time
4891 \qecho or dest_recv_time, or by deleting the offending transits. Below is a
4892 \qecho query to locate duplicate transits. Note dupes may exist accross
4893 \qecho parent (action.transit_copy) and child tables (action.hold_transit_copy,
4894 \qecho action.reservation_transit_copy)
4896 \qecho WITH dupe_transits AS (
4897 \qecho SELECT COUNT(*), target_copy FROM action.transit_copy
4898 \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL
4899 \qecho GROUP BY 2 HAVING COUNT(*) > 1
4900 \qecho ) SELECT atc.*
4901 \qecho FROM dupe_transits
4902 \qecho JOIN action.transit_copy atc USING (target_copy)
4903 \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL;