3 SELECT evergreen.upgrade_deps_block_check('1292', :eg_version);
5 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
10 -- If there are any renewals for this circulation, don't archive or delete
11 -- it yet. We'll do so later, when we archive and delete the renewals.
14 FROM action.circulation
15 WHERE parent_circ = OLD.id
19 RETURN NULL; -- don't delete
22 -- Archive a copy of the old row to action.aged_circulation
24 INSERT INTO action.aged_circulation
25 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
26 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
27 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
28 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
29 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
30 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
31 auto_renewal, auto_renewal_remaining)
33 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
34 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
35 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
36 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
37 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
38 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
39 auto_renewal, auto_renewal_remaining
40 FROM action.all_circulation WHERE id = OLD.id;
42 -- Migrate billings and payments to aged tables
44 SELECT 'Y' INTO found FROM config.global_flag
45 WHERE name = 'history.money.age_with_circs' AND enabled;
48 PERFORM money.age_billings_and_payments_for_xact(OLD.id);
51 -- Break the link with the user in action_trigger.event (warning: event_output may essentially have this information)
53 action_trigger.event e
57 action.all_circulation c
60 AND e.context_user = c.usr
64 FROM action_trigger.event_definition
65 WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')
71 $$ LANGUAGE 'plpgsql';
73 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
75 specified_dest_usr IN INTEGER
83 IF specified_dest_usr IS NULL THEN
84 dest_usr := 1; -- Admin user on stock installs
86 dest_usr := specified_dest_usr;
89 -- action_trigger.event (even doing this, event_output may--and probably does--contain PII and should have a retention/removal policy)
90 UPDATE action_trigger.event SET context_user = dest_usr WHERE context_user = src_usr;
93 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
94 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
95 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
96 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
97 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
98 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
99 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
100 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
102 -- Update with a rename to avoid collisions
106 WHERE owner = src_usr
108 suffix := ' (' || src_usr || ')';
112 SET owner = dest_usr, name = name || suffix
113 WHERE id = renamable_row.id;
114 EXCEPTION WHEN unique_violation THEN
115 suffix := suffix || ' ';
122 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
123 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
124 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
125 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
126 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
127 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
128 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
129 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
132 DELETE FROM action.circulation WHERE usr = src_usr;
133 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
134 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
135 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
136 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
137 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
138 DELETE FROM action.hold_request WHERE usr = src_usr;
139 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
140 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
141 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
142 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
143 DELETE FROM action.survey_response WHERE usr = src_usr;
144 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
145 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
148 DELETE FROM actor.card WHERE usr = src_usr;
149 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
150 DELETE FROM actor.usr_privacy_waiver WHERE usr = src_usr;
152 -- The following update is intended to avoid transient violations of a foreign
153 -- key constraint, whereby actor.usr_address references itself. It may not be
154 -- necessary, but it does no harm.
155 UPDATE actor.usr_address SET replaces = NULL
156 WHERE usr = src_usr AND replaces IS NOT NULL;
157 DELETE FROM actor.usr_address WHERE usr = src_usr;
158 DELETE FROM actor.usr_note WHERE usr = src_usr;
159 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
160 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
161 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
162 DELETE FROM actor.usr_setting WHERE usr = src_usr;
163 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
164 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
167 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
168 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
169 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
170 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
171 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
172 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
175 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
176 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
177 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
178 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
179 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
180 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
181 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
182 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
185 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
186 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
187 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
188 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
191 -- Update buckets with a rename to avoid collisions
194 FROM container.biblio_record_entry_bucket
195 WHERE owner = src_usr
197 suffix := ' (' || src_usr || ')';
200 UPDATE container.biblio_record_entry_bucket
201 SET owner = dest_usr, name = name || suffix
202 WHERE id = renamable_row.id;
203 EXCEPTION WHEN unique_violation THEN
204 suffix := suffix || ' ';
213 FROM container.call_number_bucket
214 WHERE owner = src_usr
216 suffix := ' (' || src_usr || ')';
219 UPDATE container.call_number_bucket
220 SET owner = dest_usr, name = name || suffix
221 WHERE id = renamable_row.id;
222 EXCEPTION WHEN unique_violation THEN
223 suffix := suffix || ' ';
232 FROM container.copy_bucket
233 WHERE owner = src_usr
235 suffix := ' (' || src_usr || ')';
238 UPDATE container.copy_bucket
239 SET owner = dest_usr, name = name || suffix
240 WHERE id = renamable_row.id;
241 EXCEPTION WHEN unique_violation THEN
242 suffix := suffix || ' ';
251 FROM container.user_bucket
252 WHERE owner = src_usr
254 suffix := ' (' || src_usr || ')';
257 UPDATE container.user_bucket
258 SET owner = dest_usr, name = name || suffix
259 WHERE id = renamable_row.id;
260 EXCEPTION WHEN unique_violation THEN
261 suffix := suffix || ' ';
268 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
271 DELETE FROM money.billable_xact WHERE usr = src_usr;
272 DELETE FROM money.collections_tracker WHERE usr = src_usr;
273 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
276 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
277 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
278 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
279 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
282 -- Update with a rename to avoid collisions
286 FROM reporter.output_folder
287 WHERE owner = src_usr
289 suffix := ' (' || src_usr || ')';
292 UPDATE reporter.output_folder
293 SET owner = dest_usr, name = name || suffix
294 WHERE id = renamable_row.id;
295 EXCEPTION WHEN unique_violation THEN
296 suffix := suffix || ' ';
302 EXCEPTION WHEN undefined_table THEN
307 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
308 EXCEPTION WHEN undefined_table THEN
312 -- Update with a rename to avoid collisions
316 FROM reporter.report_folder
317 WHERE owner = src_usr
319 suffix := ' (' || src_usr || ')';
322 UPDATE reporter.report_folder
323 SET owner = dest_usr, name = name || suffix
324 WHERE id = renamable_row.id;
325 EXCEPTION WHEN unique_violation THEN
326 suffix := suffix || ' ';
332 EXCEPTION WHEN undefined_table THEN
337 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
338 EXCEPTION WHEN undefined_table THEN
343 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
344 EXCEPTION WHEN undefined_table THEN
348 -- Update with a rename to avoid collisions
352 FROM reporter.template_folder
353 WHERE owner = src_usr
355 suffix := ' (' || src_usr || ')';
358 UPDATE reporter.template_folder
359 SET owner = dest_usr, name = name || suffix
360 WHERE id = renamable_row.id;
361 EXCEPTION WHEN unique_violation THEN
362 suffix := suffix || ' ';
368 EXCEPTION WHEN undefined_table THEN
373 -- Update with a rename to avoid collisions
377 WHERE owner = src_usr
379 suffix := ' (' || src_usr || ')';
382 UPDATE vandelay.queue
383 SET owner = dest_usr, name = name || suffix
384 WHERE id = renamable_row.id;
385 EXCEPTION WHEN unique_violation THEN
386 suffix := suffix || ' ';
393 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
395 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
396 -- can access the information before deletion.
400 mailing_address = NULL,
401 billing_address = NULL