3 INSERT INTO config.upgrade_log (version) VALUES ('0346'); -- Scott McKellar
5 CREATE TABLE action.fieldset (
7 owner INT NOT NULL REFERENCES actor.usr (id)
8 DEFERRABLE INITIALLY DEFERRED,
9 owning_lib INT NOT NULL REFERENCES actor.org_unit (id)
10 DEFERRABLE INITIALLY DEFERRED,
12 CONSTRAINT valid_status CHECK ( status in
13 ( 'PENDING', 'APPLIED', 'ERROR' )),
14 creation_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
15 scheduled_time TIMESTAMPTZ,
16 applied_time TIMESTAMPTZ,
17 classname TEXT NOT NULL, -- an IDL class name
19 stored_query INT REFERENCES query.stored_query (id)
20 DEFERRABLE INITIALLY DEFERRED,
22 CONSTRAINT lib_name_unique UNIQUE (owning_lib, name),
23 CONSTRAINT fieldset_one_or_the_other CHECK (
24 (stored_query IS NOT NULL AND pkey_value IS NULL) OR
25 (pkey_value IS NOT NULL AND stored_query IS NULL)
27 -- the CHECK constraint means we can update the fields for a single
28 -- row without all the extra overhead involved in a query
31 CREATE INDEX action_fieldset_sched_time_idx ON action.fieldset( scheduled_time );
32 CREATE INDEX action_owner_idx ON action.fieldset( owner );
35 CREATE TABLE action.fieldset_col_val (
36 id SERIAL PRIMARY KEY,
37 fieldset INT NOT NULL REFERENCES action.fieldset
39 DEFERRABLE INITIALLY DEFERRED,
40 col TEXT NOT NULL, -- "field" from the idl ... the column on the table
41 val TEXT, -- value for the column ... NULL means, well, NULL
42 CONSTRAINT fieldset_col_once_per_set UNIQUE (fieldset, col)
45 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
57 mailing_address = NULL,
58 billing_address = NULL
62 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
63 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
64 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
65 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
66 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
67 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
68 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
70 -- Update with a rename to avoid collisions
76 suffix := ' (' || src_usr || ')';
80 SET owner = dest_usr, name = name || suffix
81 WHERE id = renamable_row.id;
82 EXCEPTION WHEN unique_violation THEN
83 suffix := suffix || ' ';
90 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
91 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
92 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
93 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
94 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
95 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
96 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
97 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
100 DELETE FROM action.circulation WHERE usr = src_usr;
101 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
102 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
103 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
104 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
105 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
106 DELETE FROM action.hold_request WHERE usr = src_usr;
107 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
108 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
109 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
110 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
111 DELETE FROM action.survey_response WHERE usr = src_usr;
112 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
115 DELETE FROM actor.card WHERE usr = src_usr;
116 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
118 -- The following update is intended to avoid transient violations of a foreign
119 -- key constraint, whereby actor.usr_address references itself. It may not be
120 -- necessary, but it does no harm.
121 UPDATE actor.usr_address SET replaces = NULL
122 WHERE usr = src_usr AND replaces IS NOT NULL;
123 DELETE FROM actor.usr_address WHERE usr = src_usr;
124 DELETE FROM actor.usr_note WHERE usr = src_usr;
125 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
126 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
127 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
128 DELETE FROM actor.usr_setting WHERE usr = src_usr;
129 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
130 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
133 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
134 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
135 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
136 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
137 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
138 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
141 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
142 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
143 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
144 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
145 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
146 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
147 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
148 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
151 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
152 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
153 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
154 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
157 -- Update buckets with a rename to avoid collisions
160 FROM container.biblio_record_entry_bucket
161 WHERE owner = src_usr
163 suffix := ' (' || src_usr || ')';
166 UPDATE container.biblio_record_entry_bucket
167 SET owner = dest_usr, name = name || suffix
168 WHERE id = renamable_row.id;
169 EXCEPTION WHEN unique_violation THEN
170 suffix := suffix || ' ';
179 FROM container.call_number_bucket
180 WHERE owner = src_usr
182 suffix := ' (' || src_usr || ')';
185 UPDATE container.call_number_bucket
186 SET owner = dest_usr, name = name || suffix
187 WHERE id = renamable_row.id;
188 EXCEPTION WHEN unique_violation THEN
189 suffix := suffix || ' ';
198 FROM container.copy_bucket
199 WHERE owner = src_usr
201 suffix := ' (' || src_usr || ')';
204 UPDATE container.copy_bucket
205 SET owner = dest_usr, name = name || suffix
206 WHERE id = renamable_row.id;
207 EXCEPTION WHEN unique_violation THEN
208 suffix := suffix || ' ';
217 FROM container.user_bucket
218 WHERE owner = src_usr
220 suffix := ' (' || src_usr || ')';
223 UPDATE container.user_bucket
224 SET owner = dest_usr, name = name || suffix
225 WHERE id = renamable_row.id;
226 EXCEPTION WHEN unique_violation THEN
227 suffix := suffix || ' ';
234 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
237 DELETE FROM money.billable_xact WHERE usr = src_usr;
238 DELETE FROM money.collections_tracker WHERE usr = src_usr;
239 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
242 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
243 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
244 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
245 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
248 -- Update with a rename to avoid collisions
252 FROM reporter.output_folder
253 WHERE owner = src_usr
255 suffix := ' (' || src_usr || ')';
258 UPDATE reporter.output_folder
259 SET owner = dest_usr, name = name || suffix
260 WHERE id = renamable_row.id;
261 EXCEPTION WHEN unique_violation THEN
262 suffix := suffix || ' ';
268 EXCEPTION WHEN undefined_table THEN
273 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
274 EXCEPTION WHEN undefined_table THEN
278 -- Update with a rename to avoid collisions
282 FROM reporter.report_folder
283 WHERE owner = src_usr
285 suffix := ' (' || src_usr || ')';
288 UPDATE reporter.report_folder
289 SET owner = dest_usr, name = name || suffix
290 WHERE id = renamable_row.id;
291 EXCEPTION WHEN unique_violation THEN
292 suffix := suffix || ' ';
298 EXCEPTION WHEN undefined_table THEN
303 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
304 EXCEPTION WHEN undefined_table THEN
309 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
310 EXCEPTION WHEN undefined_table THEN
314 -- Update with a rename to avoid collisions
318 FROM reporter.template_folder
319 WHERE owner = src_usr
321 suffix := ' (' || src_usr || ')';
324 UPDATE reporter.template_folder
325 SET owner = dest_usr, name = name || suffix
326 WHERE id = renamable_row.id;
327 EXCEPTION WHEN unique_violation THEN
328 suffix := suffix || ' ';
334 EXCEPTION WHEN undefined_table THEN
339 -- Update with a rename to avoid collisions
343 WHERE owner = src_usr
345 suffix := ' (' || src_usr || ')';
348 UPDATE vandelay.queue
349 SET owner = dest_usr, name = name || suffix
350 WHERE id = renamable_row.id;
351 EXCEPTION WHEN unique_violation THEN
352 suffix := suffix || ' ';