3 INSERT INTO config.upgrade_log (version) VALUES ('0198'); -- Scott McKellar
5 CREATE TABLE acq.claim_type (
7 org_unit INT NOT NULL REFERENCES actor.org_unit(id)
8 DEFERRABLE INITIALLY DEFERRED,
10 description TEXT NOT NULL,
11 CONSTRAINT claim_type_once_per_org UNIQUE ( org_unit, code )
14 CREATE TABLE acq.claim_event_type (
15 id SERIAL PRIMARY KEY,
16 org_unit INT NOT NULL REFERENCES actor.org_unit(id)
17 DEFERRABLE INITIALLY DEFERRED,
19 description TEXT NOT NULL,
20 library_initiated BOOL NOT NULL DEFAULT FALSE,
21 CONSTRAINT event_type_once_per_org UNIQUE ( org_unit, code )
24 CREATE TABLE acq.claim (
25 id SERIAL PRIMARY KEY,
26 type INT NOT NULL REFERENCES acq.claim_type
27 DEFERRABLE INITIALLY DEFERRED,
28 lineitem_detail BIGINT NOT NULL REFERENCES acq.lineitem_detail
29 DEFERRABLE INITIALLY DEFERRED
32 CREATE INDEX claim_lid_idx ON acq.claim( lineitem_detail );
34 CREATE TABLE acq.claim_event (
35 id BIGSERIAL PRIMARY KEY,
36 type INT NOT NULL REFERENCES acq.claim_event_type
37 DEFERRABLE INITIALLY DEFERRED,
38 claim SERIAL NOT NULL REFERENCES acq.claim
39 DEFERRABLE INITIALLY DEFERRED,
40 event_date TIMESTAMPTZ NOT NULL DEFAULT now(),
41 creator INT NOT NULL REFERENCES actor.usr
42 DEFERRABLE INITIALLY DEFERRED,
46 CREATE INDEX claim_event_claim_date_idx ON acq.claim_event( claim, event_date );
48 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
60 mailing_address = NULL,
61 billing_address = NULL
65 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
66 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
67 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
68 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
69 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
70 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
71 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
73 -- Update with a rename to avoid collisions
79 suffix := ' (' || src_usr || ')';
83 SET owner = dest_usr, name = name || suffix
84 WHERE id = renamable_row.id;
85 EXCEPTION WHEN unique_violation THEN
86 suffix := suffix || ' ';
93 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
94 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
95 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
96 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
97 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
98 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
99 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
100 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
103 DELETE FROM action.circulation WHERE usr = src_usr;
104 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
105 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
106 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
107 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
108 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
109 DELETE FROM action.hold_request WHERE usr = src_usr;
110 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
111 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
112 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
113 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
114 DELETE FROM action.survey_response WHERE usr = src_usr;
117 DELETE FROM actor.card WHERE usr = src_usr;
118 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
120 -- The following update is intended to avoid transient violations of a foreign
121 -- key constraint, whereby actor.usr_address references itself. It may not be
122 -- necessary, but it does no harm.
123 UPDATE actor.usr_address SET replaces = NULL
124 WHERE usr = src_usr AND replaces IS NOT NULL;
125 DELETE FROM actor.usr_address WHERE usr = src_usr;
126 DELETE FROM actor.usr_note WHERE usr = src_usr;
127 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
128 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
129 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
130 DELETE FROM actor.usr_setting WHERE usr = src_usr;
131 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
132 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
135 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
136 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
137 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
138 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
139 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
140 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
143 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
144 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
145 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
146 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
147 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
148 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
149 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
150 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
153 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
154 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
155 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
156 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
159 -- Update buckets with a rename to avoid collisions
162 FROM container.biblio_record_entry_bucket
163 WHERE owner = src_usr
165 suffix := ' (' || src_usr || ')';
168 UPDATE container.biblio_record_entry_bucket
169 SET owner = dest_usr, name = name || suffix
170 WHERE id = renamable_row.id;
171 EXCEPTION WHEN unique_violation THEN
172 suffix := suffix || ' ';
181 FROM container.call_number_bucket
182 WHERE owner = src_usr
184 suffix := ' (' || src_usr || ')';
187 UPDATE container.call_number_bucket
188 SET owner = dest_usr, name = name || suffix
189 WHERE id = renamable_row.id;
190 EXCEPTION WHEN unique_violation THEN
191 suffix := suffix || ' ';
200 FROM container.copy_bucket
201 WHERE owner = src_usr
203 suffix := ' (' || src_usr || ')';
206 UPDATE container.copy_bucket
207 SET owner = dest_usr, name = name || suffix
208 WHERE id = renamable_row.id;
209 EXCEPTION WHEN unique_violation THEN
210 suffix := suffix || ' ';
219 FROM container.user_bucket
220 WHERE owner = src_usr
222 suffix := ' (' || src_usr || ')';
225 UPDATE container.user_bucket
226 SET owner = dest_usr, name = name || suffix
227 WHERE id = renamable_row.id;
228 EXCEPTION WHEN unique_violation THEN
229 suffix := suffix || ' ';
236 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
239 DELETE FROM money.billable_xact WHERE usr = src_usr;
240 DELETE FROM money.collections_tracker WHERE usr = src_usr;
241 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
244 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
245 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
246 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
247 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
250 -- Update with a rename to avoid collisions
254 FROM reporter.output_folder
255 WHERE owner = src_usr
257 suffix := ' (' || src_usr || ')';
260 UPDATE reporter.output_folder
261 SET owner = dest_usr, name = name || suffix
262 WHERE id = renamable_row.id;
263 EXCEPTION WHEN unique_violation THEN
264 suffix := suffix || ' ';
270 EXCEPTION WHEN undefined_table THEN
275 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
276 EXCEPTION WHEN undefined_table THEN
280 -- Update with a rename to avoid collisions
284 FROM reporter.report_folder
285 WHERE owner = src_usr
287 suffix := ' (' || src_usr || ')';
290 UPDATE reporter.report_folder
291 SET owner = dest_usr, name = name || suffix
292 WHERE id = renamable_row.id;
293 EXCEPTION WHEN unique_violation THEN
294 suffix := suffix || ' ';
300 EXCEPTION WHEN undefined_table THEN
305 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
306 EXCEPTION WHEN undefined_table THEN
311 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
312 EXCEPTION WHEN undefined_table THEN
316 -- Update with a rename to avoid collisions
320 FROM reporter.template_folder
321 WHERE owner = src_usr
323 suffix := ' (' || src_usr || ')';
326 UPDATE reporter.template_folder
327 SET owner = dest_usr, name = name || suffix
328 WHERE id = renamable_row.id;
329 EXCEPTION WHEN unique_violation THEN
330 suffix := suffix || ' ';
336 EXCEPTION WHEN undefined_table THEN
341 -- Update with a rename to avoid collisions
345 WHERE owner = src_usr
347 suffix := ' (' || src_usr || ')';
350 UPDATE vandelay.queue
351 SET owner = dest_usr, name = name || suffix
352 WHERE id = renamable_row.id;
353 EXCEPTION WHEN unique_violation THEN
354 suffix := suffix || ' ';