3 INSERT INTO config.upgrade_log (version) VALUES ('2.0.1');
4 INSERT INTO config.upgrade_log (version) VALUES ('0480');
6 DROP FUNCTION IF EXISTS actor.usr_purge_data(INT, INT);
7 CREATE FUNCTION actor.usr_purge_data(
9 specified_dest_usr IN INTEGER
17 IF specified_dest_usr IS NULL THEN
18 dest_usr := 1; -- Admin user on stock installs
20 dest_usr := specified_dest_usr;
26 mailing_address = NULL,
27 billing_address = NULL
31 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
32 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
33 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
34 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
35 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
36 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
37 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
39 -- Update with a rename to avoid collisions
45 suffix := ' (' || src_usr || ')';
49 SET owner = dest_usr, name = name || suffix
50 WHERE id = renamable_row.id;
51 EXCEPTION WHEN unique_violation THEN
52 suffix := suffix || ' ';
59 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
60 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
61 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
62 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
63 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
64 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
65 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
66 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
69 DELETE FROM action.circulation WHERE usr = src_usr;
70 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
71 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
72 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
73 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
74 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
75 DELETE FROM action.hold_request WHERE usr = src_usr;
76 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
77 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
78 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
79 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
80 DELETE FROM action.survey_response WHERE usr = src_usr;
81 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
84 DELETE FROM actor.card WHERE usr = src_usr;
85 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
87 -- The following update is intended to avoid transient violations of a foreign
88 -- key constraint, whereby actor.usr_address references itself. It may not be
89 -- necessary, but it does no harm.
90 UPDATE actor.usr_address SET replaces = NULL
91 WHERE usr = src_usr AND replaces IS NOT NULL;
92 DELETE FROM actor.usr_address WHERE usr = src_usr;
93 DELETE FROM actor.usr_note WHERE usr = src_usr;
94 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
95 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
96 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
97 DELETE FROM actor.usr_setting WHERE usr = src_usr;
98 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
99 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
102 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
103 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
104 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
105 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
106 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
107 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
110 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
111 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
112 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
113 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
114 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
115 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
116 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
117 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
120 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
121 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
122 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
123 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
126 -- Update buckets with a rename to avoid collisions
129 FROM container.biblio_record_entry_bucket
130 WHERE owner = src_usr
132 suffix := ' (' || src_usr || ')';
135 UPDATE container.biblio_record_entry_bucket
136 SET owner = dest_usr, name = name || suffix
137 WHERE id = renamable_row.id;
138 EXCEPTION WHEN unique_violation THEN
139 suffix := suffix || ' ';
148 FROM container.call_number_bucket
149 WHERE owner = src_usr
151 suffix := ' (' || src_usr || ')';
154 UPDATE container.call_number_bucket
155 SET owner = dest_usr, name = name || suffix
156 WHERE id = renamable_row.id;
157 EXCEPTION WHEN unique_violation THEN
158 suffix := suffix || ' ';
167 FROM container.copy_bucket
168 WHERE owner = src_usr
170 suffix := ' (' || src_usr || ')';
173 UPDATE container.copy_bucket
174 SET owner = dest_usr, name = name || suffix
175 WHERE id = renamable_row.id;
176 EXCEPTION WHEN unique_violation THEN
177 suffix := suffix || ' ';
186 FROM container.user_bucket
187 WHERE owner = src_usr
189 suffix := ' (' || src_usr || ')';
192 UPDATE container.user_bucket
193 SET owner = dest_usr, name = name || suffix
194 WHERE id = renamable_row.id;
195 EXCEPTION WHEN unique_violation THEN
196 suffix := suffix || ' ';
203 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
206 DELETE FROM money.billable_xact WHERE usr = src_usr;
207 DELETE FROM money.collections_tracker WHERE usr = src_usr;
208 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
211 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
212 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
213 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
214 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
217 -- Update with a rename to avoid collisions
221 FROM reporter.output_folder
222 WHERE owner = src_usr
224 suffix := ' (' || src_usr || ')';
227 UPDATE reporter.output_folder
228 SET owner = dest_usr, name = name || suffix
229 WHERE id = renamable_row.id;
230 EXCEPTION WHEN unique_violation THEN
231 suffix := suffix || ' ';
237 EXCEPTION WHEN undefined_table THEN
242 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
243 EXCEPTION WHEN undefined_table THEN
247 -- Update with a rename to avoid collisions
251 FROM reporter.report_folder
252 WHERE owner = src_usr
254 suffix := ' (' || src_usr || ')';
257 UPDATE reporter.report_folder
258 SET owner = dest_usr, name = name || suffix
259 WHERE id = renamable_row.id;
260 EXCEPTION WHEN unique_violation THEN
261 suffix := suffix || ' ';
267 EXCEPTION WHEN undefined_table THEN
272 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
273 EXCEPTION WHEN undefined_table THEN
278 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
279 EXCEPTION WHEN undefined_table THEN
283 -- Update with a rename to avoid collisions
287 FROM reporter.template_folder
288 WHERE owner = src_usr
290 suffix := ' (' || src_usr || ')';
293 UPDATE reporter.template_folder
294 SET owner = dest_usr, name = name || suffix
295 WHERE id = renamable_row.id;
296 EXCEPTION WHEN unique_violation THEN
297 suffix := suffix || ' ';
303 EXCEPTION WHEN undefined_table THEN
308 -- Update with a rename to avoid collisions
312 WHERE owner = src_usr
314 suffix := ' (' || src_usr || ')';
317 UPDATE vandelay.queue
318 SET owner = dest_usr, name = name || suffix
319 WHERE id = renamable_row.id;
320 EXCEPTION WHEN unique_violation THEN
321 suffix := suffix || ' ';
331 INSERT INTO config.upgrade_log(version) VALUES ('0481');
333 -- We defined the same trigger on the parent table asset.copy
334 -- but we need to define it on child tables explicitly as well
335 CREATE TRIGGER autogenerate_placeholder_barcode
336 BEFORE INSERT OR UPDATE ON serial.unit
337 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode()