3 INSERT INTO config.upgrade_log (version) VALUES ('0172'); -- Scott McKellar
5 CREATE TABLE acq.provider_note (
7 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
8 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
9 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
10 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
11 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
14 CREATE INDEX acq_pro_note_pro_idx ON acq.provider_note ( provider );
15 CREATE INDEX acq_pro_note_creator_idx ON acq.provider_note ( creator );
16 CREATE INDEX acq_pro_note_editor_idx ON acq.provider_note ( editor );
18 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
30 mailing_address = NULL,
31 billing_address = NULL
35 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
36 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
37 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
38 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
39 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
40 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
41 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
43 -- Update with a rename to avoid collisions
49 suffix := ' (' || src_usr || ')';
53 SET owner = dest_usr, name = name || suffix
54 WHERE id = renamable_row.id;
55 EXCEPTION WHEN unique_violation THEN
56 suffix := suffix || ' ';
63 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
64 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
65 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
66 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
67 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
68 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
69 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
70 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
71 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
74 DELETE FROM action.circulation WHERE usr = src_usr;
75 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
76 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
77 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
78 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
79 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
80 DELETE FROM action.hold_request WHERE usr = src_usr;
81 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
82 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
83 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
84 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
85 DELETE FROM action.survey_response WHERE usr = src_usr;
88 DELETE FROM actor.card WHERE usr = src_usr;
89 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
91 -- The following update is intended to avoid transient violations of a foreign
92 -- key constraint, whereby actor.usr_address references itself. It may not be
93 -- necessary, but it does no harm.
94 UPDATE actor.usr_address SET replaces = NULL
95 WHERE usr = src_usr AND replaces IS NOT NULL;
96 DELETE FROM actor.usr_address WHERE usr = src_usr;
97 DELETE FROM actor.usr_note WHERE usr = src_usr;
98 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
99 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
100 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
101 DELETE FROM actor.usr_setting WHERE usr = src_usr;
102 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
103 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
106 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
107 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
108 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
109 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
110 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
111 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
114 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
115 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
116 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
117 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
118 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
119 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
120 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
121 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
124 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
125 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
126 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
127 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
130 -- Update buckets with a rename to avoid collisions
133 FROM container.biblio_record_entry_bucket
134 WHERE owner = src_usr
136 suffix := ' (' || src_usr || ')';
139 UPDATE container.biblio_record_entry_bucket
140 SET owner = dest_usr, name = name || suffix
141 WHERE id = renamable_row.id;
142 EXCEPTION WHEN unique_violation THEN
143 suffix := suffix || ' ';
152 FROM container.call_number_bucket
153 WHERE owner = src_usr
155 suffix := ' (' || src_usr || ')';
158 UPDATE container.call_number_bucket
159 SET owner = dest_usr, name = name || suffix
160 WHERE id = renamable_row.id;
161 EXCEPTION WHEN unique_violation THEN
162 suffix := suffix || ' ';
171 FROM container.copy_bucket
172 WHERE owner = src_usr
174 suffix := ' (' || src_usr || ')';
177 UPDATE container.copy_bucket
178 SET owner = dest_usr, name = name || suffix
179 WHERE id = renamable_row.id;
180 EXCEPTION WHEN unique_violation THEN
181 suffix := suffix || ' ';
190 FROM container.user_bucket
191 WHERE owner = src_usr
193 suffix := ' (' || src_usr || ')';
196 UPDATE container.user_bucket
197 SET owner = dest_usr, name = name || suffix
198 WHERE id = renamable_row.id;
199 EXCEPTION WHEN unique_violation THEN
200 suffix := suffix || ' ';
207 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
210 DELETE FROM money.billable_xact WHERE usr = src_usr;
211 DELETE FROM money.collections_tracker WHERE usr = src_usr;
212 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
215 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
216 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
217 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
218 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
221 -- Update with a rename to avoid collisions
225 FROM reporter.output_folder
226 WHERE owner = src_usr
228 suffix := ' (' || src_usr || ')';
231 UPDATE reporter.output_folder
232 SET owner = dest_usr, name = name || suffix
233 WHERE id = renamable_row.id;
234 EXCEPTION WHEN unique_violation THEN
235 suffix := suffix || ' ';
241 EXCEPTION WHEN undefined_table THEN
246 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
247 EXCEPTION WHEN undefined_table THEN
251 -- Update with a rename to avoid collisions
255 FROM reporter.report_folder
256 WHERE owner = src_usr
258 suffix := ' (' || src_usr || ')';
261 UPDATE reporter.report_folder
262 SET owner = dest_usr, name = name || suffix
263 WHERE id = renamable_row.id;
264 EXCEPTION WHEN unique_violation THEN
265 suffix := suffix || ' ';
271 EXCEPTION WHEN undefined_table THEN
276 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
277 EXCEPTION WHEN undefined_table THEN
282 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
283 EXCEPTION WHEN undefined_table THEN
287 -- Update with a rename to avoid collisions
291 FROM reporter.template_folder
292 WHERE owner = src_usr
294 suffix := ' (' || src_usr || ')';
297 UPDATE reporter.template_folder
298 SET owner = dest_usr, name = name || suffix
299 WHERE id = renamable_row.id;
300 EXCEPTION WHEN unique_violation THEN
301 suffix := suffix || ' ';
307 EXCEPTION WHEN undefined_table THEN
312 -- Update with a rename to avoid collisions
316 WHERE owner = src_usr
318 suffix := ' (' || src_usr || ')';
321 UPDATE vandelay.queue
322 SET owner = dest_usr, name = name || suffix
323 WHERE id = renamable_row.id;
324 EXCEPTION WHEN unique_violation THEN
325 suffix := suffix || ' ';