9 ADD COLUMN pref_prefix TEXT,
10 ADD COLUMN pref_first_given_name TEXT,
11 ADD COLUMN pref_second_given_name TEXT,
12 ADD COLUMN pref_family_name TEXT,
13 ADD COLUMN pref_suffix TEXT,
14 ADD COLUMN name_keywords TEXT,
15 ADD COLUMN name_kw_tsvector TSVECTOR;
17 ALTER TABLE staging.user_stage
18 ADD COLUMN pref_first_given_name TEXT,
19 ADD COLUMN pref_second_given_name TEXT,
20 ADD COLUMN pref_family_name TEXT;
22 CREATE INDEX actor_usr_pref_first_given_name_idx
23 ON actor.usr (evergreen.lowercase(pref_first_given_name));
24 CREATE INDEX actor_usr_pref_second_given_name_idx
25 ON actor.usr (evergreen.lowercase(pref_second_given_name));
26 CREATE INDEX actor_usr_pref_family_name_idx
27 ON actor.usr (evergreen.lowercase(pref_family_name));
28 CREATE INDEX actor_usr_pref_first_given_name_unaccent_idx
29 ON actor.usr (evergreen.unaccent_and_squash(pref_first_given_name));
30 CREATE INDEX actor_usr_pref_second_given_name_unaccent_idx
31 ON actor.usr (evergreen.unaccent_and_squash(pref_second_given_name));
32 CREATE INDEX actor_usr_pref_family_name_unaccent_idx
33 ON actor.usr (evergreen.unaccent_and_squash(pref_family_name));
35 -- Update keyword indexes for existing patrons
37 UPDATE actor.usr SET name_kw_tsvector =
39 COALESCE(prefix, '') || ' ' ||
40 COALESCE(first_given_name, '') || ' ' ||
41 COALESCE(evergreen.unaccent_and_squash(first_given_name), '') || ' ' ||
42 COALESCE(second_given_name, '') || ' ' ||
43 COALESCE(evergreen.unaccent_and_squash(second_given_name), '') || ' ' ||
44 COALESCE(family_name, '') || ' ' ||
45 COALESCE(evergreen.unaccent_and_squash(family_name), '') || ' ' ||
49 CREATE OR REPLACE FUNCTION actor.user_ingest_name_keywords()
50 RETURNS TRIGGER AS $func$
52 NEW.name_kw_tsvector := TO_TSVECTOR(
53 COALESCE(NEW.prefix, '') || ' ' ||
54 COALESCE(NEW.first_given_name, '') || ' ' ||
55 COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' ||
56 COALESCE(NEW.second_given_name, '') || ' ' ||
57 COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' ||
58 COALESCE(NEW.family_name, '') || ' ' ||
59 COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' ||
60 COALESCE(NEW.suffix, '') || ' ' ||
61 COALESCE(NEW.pref_prefix, '') || ' ' ||
62 COALESCE(NEW.pref_first_given_name, '') || ' ' ||
63 COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' ||
64 COALESCE(NEW.pref_second_given_name, '') || ' ' ||
65 COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' ||
66 COALESCE(NEW.pref_family_name, '') || ' ' ||
67 COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' ||
68 COALESCE(NEW.pref_suffix, '') || ' ' ||
69 COALESCE(NEW.name_keywords, '')
73 $func$ LANGUAGE PLPGSQL;
75 -- Add after the batch upate above to avoid duplicate updates.
76 CREATE TRIGGER user_ingest_name_keywords_tgr
77 BEFORE INSERT OR UPDATE ON actor.usr
78 FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords();
81 -- merge pref names from source user to target user, except when
82 -- clobbering existing pref names.
83 CREATE OR REPLACE FUNCTION actor.usr_merge(src_usr INT, dest_usr INT,
84 del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN )
94 -- do some initial cleanup
95 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
96 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
97 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
101 DELETE FROM actor.card where usr = src_usr;
103 IF deactivate_cards THEN
104 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
106 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
111 DELETE FROM actor.usr_address WHERE usr = src_usr;
113 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
116 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
117 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
118 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
119 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
120 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
123 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
124 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
125 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
126 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
131 -- For each *_bucket table: transfer every bucket belonging to src_usr
132 -- into the custody of dest_usr.
134 -- In order to avoid colliding with an existing bucket owned by
135 -- the destination user, append the source user's id (in parenthesese)
136 -- to the name. If you still get a collision, add successive
137 -- spaces to the name and keep trying until you succeed.
141 FROM container.biblio_record_entry_bucket
142 WHERE owner = src_usr
144 suffix := ' (' || src_usr || ')';
147 UPDATE container.biblio_record_entry_bucket
148 SET owner = dest_usr, name = name || suffix
149 WHERE id = bucket_row.id;
150 EXCEPTION WHEN unique_violation THEN
151 suffix := suffix || ' ';
160 FROM container.call_number_bucket
161 WHERE owner = src_usr
163 suffix := ' (' || src_usr || ')';
166 UPDATE container.call_number_bucket
167 SET owner = dest_usr, name = name || suffix
168 WHERE id = bucket_row.id;
169 EXCEPTION WHEN unique_violation THEN
170 suffix := suffix || ' ';
179 FROM container.copy_bucket
180 WHERE owner = src_usr
182 suffix := ' (' || src_usr || ')';
185 UPDATE container.copy_bucket
186 SET owner = dest_usr, name = name || suffix
187 WHERE id = bucket_row.id;
188 EXCEPTION WHEN unique_violation THEN
189 suffix := suffix || ' ';
198 FROM container.user_bucket
199 WHERE owner = src_usr
201 suffix := ' (' || src_usr || ')';
204 UPDATE container.user_bucket
205 SET owner = dest_usr, name = name || suffix
206 WHERE id = bucket_row.id;
207 EXCEPTION WHEN unique_violation THEN
208 suffix := suffix || ' ';
215 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
218 -- transfer queues the same way we transfer buckets (see above)
222 WHERE owner = src_usr
224 suffix := ' (' || src_usr || ')';
227 UPDATE vandelay.queue
228 SET owner = dest_usr, name = name || suffix
229 WHERE id = queue_row.id;
230 EXCEPTION WHEN unique_violation THEN
231 suffix := suffix || ' ';
239 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
240 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
241 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
242 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
243 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
246 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
247 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
248 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
249 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
251 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
252 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
253 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
254 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
256 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
257 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
258 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
259 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
260 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
263 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
264 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
266 -- transfer picklists the same way we transfer buckets (see above)
270 WHERE owner = src_usr
272 suffix := ' (' || src_usr || ')';
276 SET owner = dest_usr, name = name || suffix
277 WHERE id = picklist_row.id;
278 EXCEPTION WHEN unique_violation THEN
279 suffix := suffix || ' ';
286 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
287 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
288 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
289 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
290 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
291 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
292 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
293 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
296 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
297 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
298 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
299 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
300 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
301 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
304 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
305 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
308 -- It's not uncommon to define the reporter schema in a replica
309 -- DB only, so don't assume these tables exist in the write DB.
311 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
312 EXCEPTION WHEN undefined_table THEN
316 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
317 EXCEPTION WHEN undefined_table THEN
321 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
322 EXCEPTION WHEN undefined_table THEN
326 -- transfer folders the same way we transfer buckets (see above)
329 FROM reporter.template_folder
330 WHERE owner = src_usr
332 suffix := ' (' || src_usr || ')';
335 UPDATE reporter.template_folder
336 SET owner = dest_usr, name = name || suffix
337 WHERE id = folder_row.id;
338 EXCEPTION WHEN unique_violation THEN
339 suffix := suffix || ' ';
345 EXCEPTION WHEN undefined_table THEN
349 -- transfer folders the same way we transfer buckets (see above)
352 FROM reporter.report_folder
353 WHERE owner = src_usr
355 suffix := ' (' || src_usr || ')';
358 UPDATE reporter.report_folder
359 SET owner = dest_usr, name = name || suffix
360 WHERE id = folder_row.id;
361 EXCEPTION WHEN unique_violation THEN
362 suffix := suffix || ' ';
368 EXCEPTION WHEN undefined_table THEN
372 -- transfer folders the same way we transfer buckets (see above)
375 FROM reporter.output_folder
376 WHERE owner = src_usr
378 suffix := ' (' || src_usr || ')';
381 UPDATE reporter.output_folder
382 SET owner = dest_usr, name = name || suffix
383 WHERE id = folder_row.id;
384 EXCEPTION WHEN unique_violation THEN
385 suffix := suffix || ' ';
391 EXCEPTION WHEN undefined_table THEN
395 -- propagate preferred name values from the source user to the
396 -- destination user, but only when values are not being replaced.
397 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
400 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
401 pref_first_given_name =
402 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
403 pref_second_given_name =
404 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
406 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
408 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr)),
410 COALESCE(name_keywords, '') || ' ' ||
411 COALESCE((SELECT name_keywords FROM susr), '')
414 -- Finally, delete the source user
415 DELETE FROM actor.usr WHERE id = src_usr;