3 SELECT evergreen.upgrade_deps_block_check('1122', :eg_version);
6 ADD COLUMN pref_prefix TEXT,
7 ADD COLUMN pref_first_given_name TEXT,
8 ADD COLUMN pref_second_given_name TEXT,
9 ADD COLUMN pref_family_name TEXT,
10 ADD COLUMN pref_suffix TEXT,
11 ADD COLUMN name_keywords TEXT,
12 ADD COLUMN name_kw_tsvector TSVECTOR;
14 ALTER TABLE staging.user_stage
15 ADD COLUMN pref_first_given_name TEXT,
16 ADD COLUMN pref_second_given_name TEXT,
17 ADD COLUMN pref_family_name TEXT;
19 CREATE INDEX actor_usr_pref_first_given_name_idx
20 ON actor.usr (evergreen.lowercase(pref_first_given_name));
21 CREATE INDEX actor_usr_pref_second_given_name_idx
22 ON actor.usr (evergreen.lowercase(pref_second_given_name));
23 CREATE INDEX actor_usr_pref_family_name_idx
24 ON actor.usr (evergreen.lowercase(pref_family_name));
25 CREATE INDEX actor_usr_pref_first_given_name_unaccent_idx
26 ON actor.usr (evergreen.unaccent_and_squash(pref_first_given_name));
27 CREATE INDEX actor_usr_pref_second_given_name_unaccent_idx
28 ON actor.usr (evergreen.unaccent_and_squash(pref_second_given_name));
29 CREATE INDEX actor_usr_pref_family_name_unaccent_idx
30 ON actor.usr (evergreen.unaccent_and_squash(pref_family_name));
32 -- Update keyword indexes for existing patrons
34 UPDATE actor.usr SET name_kw_tsvector =
36 COALESCE(prefix, '') || ' ' ||
37 COALESCE(first_given_name, '') || ' ' ||
38 COALESCE(evergreen.unaccent_and_squash(first_given_name), '') || ' ' ||
39 COALESCE(second_given_name, '') || ' ' ||
40 COALESCE(evergreen.unaccent_and_squash(second_given_name), '') || ' ' ||
41 COALESCE(family_name, '') || ' ' ||
42 COALESCE(evergreen.unaccent_and_squash(family_name), '') || ' ' ||
46 CREATE OR REPLACE FUNCTION actor.user_ingest_name_keywords()
47 RETURNS TRIGGER AS $func$
49 NEW.name_kw_tsvector := TO_TSVECTOR(
50 COALESCE(NEW.prefix, '') || ' ' ||
51 COALESCE(NEW.first_given_name, '') || ' ' ||
52 COALESCE(evergreen.unaccent_and_squash(NEW.first_given_name), '') || ' ' ||
53 COALESCE(NEW.second_given_name, '') || ' ' ||
54 COALESCE(evergreen.unaccent_and_squash(NEW.second_given_name), '') || ' ' ||
55 COALESCE(NEW.family_name, '') || ' ' ||
56 COALESCE(evergreen.unaccent_and_squash(NEW.family_name), '') || ' ' ||
57 COALESCE(NEW.suffix, '') || ' ' ||
58 COALESCE(NEW.pref_prefix, '') || ' ' ||
59 COALESCE(NEW.pref_first_given_name, '') || ' ' ||
60 COALESCE(evergreen.unaccent_and_squash(NEW.pref_first_given_name), '') || ' ' ||
61 COALESCE(NEW.pref_second_given_name, '') || ' ' ||
62 COALESCE(evergreen.unaccent_and_squash(NEW.pref_second_given_name), '') || ' ' ||
63 COALESCE(NEW.pref_family_name, '') || ' ' ||
64 COALESCE(evergreen.unaccent_and_squash(NEW.pref_family_name), '') || ' ' ||
65 COALESCE(NEW.pref_suffix, '') || ' ' ||
66 COALESCE(NEW.name_keywords, '')
70 $func$ LANGUAGE PLPGSQL;
72 -- Add after the batch upate above to avoid duplicate updates.
73 CREATE TRIGGER user_ingest_name_keywords_tgr
74 BEFORE INSERT OR UPDATE ON actor.usr
75 FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords();
78 -- merge pref names from source user to target user, except when
79 -- clobbering existing pref names.
80 CREATE OR REPLACE FUNCTION actor.usr_merge(src_usr INT, dest_usr INT,
81 del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN )
91 -- do some initial cleanup
92 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
93 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
94 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
98 DELETE FROM actor.card where usr = src_usr;
100 IF deactivate_cards THEN
101 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
103 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
108 DELETE FROM actor.usr_address WHERE usr = src_usr;
110 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
113 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
114 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
115 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
116 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
117 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
120 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
121 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
122 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
123 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
128 -- For each *_bucket table: transfer every bucket belonging to src_usr
129 -- into the custody of dest_usr.
131 -- In order to avoid colliding with an existing bucket owned by
132 -- the destination user, append the source user's id (in parenthesese)
133 -- to the name. If you still get a collision, add successive
134 -- spaces to the name and keep trying until you succeed.
138 FROM container.biblio_record_entry_bucket
139 WHERE owner = src_usr
141 suffix := ' (' || src_usr || ')';
144 UPDATE container.biblio_record_entry_bucket
145 SET owner = dest_usr, name = name || suffix
146 WHERE id = bucket_row.id;
147 EXCEPTION WHEN unique_violation THEN
148 suffix := suffix || ' ';
157 FROM container.call_number_bucket
158 WHERE owner = src_usr
160 suffix := ' (' || src_usr || ')';
163 UPDATE container.call_number_bucket
164 SET owner = dest_usr, name = name || suffix
165 WHERE id = bucket_row.id;
166 EXCEPTION WHEN unique_violation THEN
167 suffix := suffix || ' ';
176 FROM container.copy_bucket
177 WHERE owner = src_usr
179 suffix := ' (' || src_usr || ')';
182 UPDATE container.copy_bucket
183 SET owner = dest_usr, name = name || suffix
184 WHERE id = bucket_row.id;
185 EXCEPTION WHEN unique_violation THEN
186 suffix := suffix || ' ';
195 FROM container.user_bucket
196 WHERE owner = src_usr
198 suffix := ' (' || src_usr || ')';
201 UPDATE container.user_bucket
202 SET owner = dest_usr, name = name || suffix
203 WHERE id = bucket_row.id;
204 EXCEPTION WHEN unique_violation THEN
205 suffix := suffix || ' ';
212 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
215 -- transfer queues the same way we transfer buckets (see above)
219 WHERE owner = src_usr
221 suffix := ' (' || src_usr || ')';
224 UPDATE vandelay.queue
225 SET owner = dest_usr, name = name || suffix
226 WHERE id = queue_row.id;
227 EXCEPTION WHEN unique_violation THEN
228 suffix := suffix || ' ';
236 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
237 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
238 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
239 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
240 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
243 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
244 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
245 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
246 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
248 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
249 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
250 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
251 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
253 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
254 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
255 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
256 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
257 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
260 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
261 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
263 -- transfer picklists the same way we transfer buckets (see above)
267 WHERE owner = src_usr
269 suffix := ' (' || src_usr || ')';
273 SET owner = dest_usr, name = name || suffix
274 WHERE id = picklist_row.id;
275 EXCEPTION WHEN unique_violation THEN
276 suffix := suffix || ' ';
283 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
284 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
285 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
286 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
287 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
288 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
289 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
290 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
293 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
294 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
295 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
296 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
297 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
298 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
301 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
302 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
305 -- It's not uncommon to define the reporter schema in a replica
306 -- DB only, so don't assume these tables exist in the write DB.
308 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
309 EXCEPTION WHEN undefined_table THEN
313 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
314 EXCEPTION WHEN undefined_table THEN
318 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
319 EXCEPTION WHEN undefined_table THEN
323 -- transfer folders the same way we transfer buckets (see above)
326 FROM reporter.template_folder
327 WHERE owner = src_usr
329 suffix := ' (' || src_usr || ')';
332 UPDATE reporter.template_folder
333 SET owner = dest_usr, name = name || suffix
334 WHERE id = folder_row.id;
335 EXCEPTION WHEN unique_violation THEN
336 suffix := suffix || ' ';
342 EXCEPTION WHEN undefined_table THEN
346 -- transfer folders the same way we transfer buckets (see above)
349 FROM reporter.report_folder
350 WHERE owner = src_usr
352 suffix := ' (' || src_usr || ')';
355 UPDATE reporter.report_folder
356 SET owner = dest_usr, name = name || suffix
357 WHERE id = folder_row.id;
358 EXCEPTION WHEN unique_violation THEN
359 suffix := suffix || ' ';
365 EXCEPTION WHEN undefined_table THEN
369 -- transfer folders the same way we transfer buckets (see above)
372 FROM reporter.output_folder
373 WHERE owner = src_usr
375 suffix := ' (' || src_usr || ')';
378 UPDATE reporter.output_folder
379 SET owner = dest_usr, name = name || suffix
380 WHERE id = folder_row.id;
381 EXCEPTION WHEN unique_violation THEN
382 suffix := suffix || ' ';
388 EXCEPTION WHEN undefined_table THEN
392 -- propagate preferred name values from the source user to the
393 -- destination user, but only when values are not being replaced.
394 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
397 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
398 pref_first_given_name =
399 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
400 pref_second_given_name =
401 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
403 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
405 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
408 -- Copy and deduplicate name keywords
409 -- String -> array -> rows -> DISTINCT -> array -> string
410 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
411 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
412 UPDATE actor.usr SET name_keywords = (
414 SELECT DISTINCT UNNEST(
415 REGEXP_SPLIT_TO_ARRAY(
416 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
417 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
420 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
421 ) WHERE id = dest_usr;
423 -- Finally, delete the source user
424 DELETE FROM actor.usr WHERE id = src_usr;