3 INSERT INTO config.upgrade_log (version) VALUES ('0121'); -- Scott McKellar
5 CREATE TABLE acq.fund_transfer (
7 src_fund INT NOT NULL REFERENCES acq.fund( id )
8 DEFERRABLE INITIALLY DEFERRED,
9 src_amount NUMERIC NOT NULL,
10 dest_fund INT NOT NULL REFERENCES acq.fund( id )
11 DEFERRABLE INITIALLY DEFERRED,
12 dest_amount NUMERIC NOT NULL,
13 transfer_time TIMESTAMPTZ NOT NULL DEFAULT now(),
14 transfer_user INT NOT NULL REFERENCES actor.usr( id )
15 DEFERRABLE INITIALLY DEFERRED,
19 CREATE INDEX acqftr_usr_idx
20 ON acq.fund_transfer( transfer_user );
22 COMMENT ON TABLE acq.fund_transfer IS $$
24 * Copyright (C) 2009 Georgia Public Library Service
25 * Scott McKellar <scott@esilibrary.com>
29 * Each row represents the transfer of money from a source fund
30 * to a destination fund. There should be corresponding entries
31 * in acq.fund_allocation. The purpose of acq.fund_transfer is
32 * to record how much money moved from which fund to which other
35 * The presence of two amount fields, rather than one, reflects
36 * the possibility that the two funds are denominated in different
37 * currencies. If they use the same currency type, the two
38 * amounts should be the same.
42 * This program is free software; you can redistribute it and/or
43 * modify it under the terms of the GNU General Public License
44 * as published by the Free Software Foundation; either version 2
45 * of the License, or (at your option) any later version.
47 * This program is distributed in the hope that it will be useful,
48 * but WITHOUT ANY WARRANTY; without even the implied warranty of
49 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
50 * GNU General Public License for more details.
54 CREATE OR REPLACE FUNCTION actor.usr_merge(
59 deactivate_cards BOOLEAN
69 -- do some initial cleanup
70 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
71 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
72 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
76 DELETE FROM actor.card where usr = src_usr;
78 IF deactivate_cards THEN
79 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
81 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
86 DELETE FROM actor.usr_address WHERE usr = src_usr;
88 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
91 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
92 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
93 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
94 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
95 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
98 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
99 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
100 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
101 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
106 -- For each *_bucket table: transfer every bucket belonging to src_usr
107 -- into the custody of dest_usr.
109 -- In order to avoid colliding with an existing bucket owned by
110 -- the destination user, append the source user's id (in parenthesese)
111 -- to the name. If you still get a collision, add successive
112 -- spaces to the name and keep trying until you succeed.
116 FROM container.biblio_record_entry_bucket
117 WHERE owner = src_usr
119 suffix := ' (' || src_usr || ')';
122 UPDATE container.biblio_record_entry_bucket
123 SET owner = dest_usr, name = name || suffix
124 WHERE id = bucket_row.id;
125 EXCEPTION WHEN unique_violation THEN
126 suffix := suffix || ' ';
135 FROM container.call_number_bucket
136 WHERE owner = src_usr
138 suffix := ' (' || src_usr || ')';
141 UPDATE container.call_number_bucket
142 SET owner = dest_usr, name = name || suffix
143 WHERE id = bucket_row.id;
144 EXCEPTION WHEN unique_violation THEN
145 suffix := suffix || ' ';
154 FROM container.copy_bucket
155 WHERE owner = src_usr
157 suffix := ' (' || src_usr || ')';
160 UPDATE container.copy_bucket
161 SET owner = dest_usr, name = name || suffix
162 WHERE id = bucket_row.id;
163 EXCEPTION WHEN unique_violation THEN
164 suffix := suffix || ' ';
173 FROM container.user_bucket
174 WHERE owner = src_usr
176 suffix := ' (' || src_usr || ')';
179 UPDATE container.user_bucket
180 SET owner = dest_usr, name = name || suffix
181 WHERE id = bucket_row.id;
182 EXCEPTION WHEN unique_violation THEN
183 suffix := suffix || ' ';
190 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
193 -- transfer queues the same way we transfer buckets (see above)
197 WHERE owner = src_usr
199 suffix := ' (' || src_usr || ')';
202 UPDATE vandelay.queue
203 SET owner = dest_usr, name = name || suffix
204 WHERE id = queue_row.id;
205 EXCEPTION WHEN unique_violation THEN
206 suffix := suffix || ' ';
214 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
215 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
216 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
217 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
218 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
221 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
222 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
223 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
225 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
226 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
227 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
228 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
230 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
231 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
232 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
233 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
234 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
237 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
238 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
240 -- transfer picklists the same way we transfer buckets (see above)
244 WHERE owner = src_usr
246 suffix := ' (' || src_usr || ')';
250 SET owner = dest_usr, name = name || suffix
251 WHERE id = picklist_row.id;
252 EXCEPTION WHEN unique_violation THEN
253 suffix := suffix || ' ';
260 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
261 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
262 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
263 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
264 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
265 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
268 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
269 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
270 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
271 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
272 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
273 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
276 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
277 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
280 -- It's not uncommon to define the reporter schema in a replica
281 -- DB only, so don't assume these tables exist in the write DB.
283 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
284 EXCEPTION WHEN undefined_table THEN
288 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
289 EXCEPTION WHEN undefined_table THEN
293 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
294 EXCEPTION WHEN undefined_table THEN
298 -- transfer folders the same way we transfer buckets (see above)
301 FROM reporter.template_folder
302 WHERE owner = src_usr
304 suffix := ' (' || src_usr || ')';
307 UPDATE reporter.template_folder
308 SET owner = dest_usr, name = name || suffix
309 WHERE id = folder_row.id;
310 EXCEPTION WHEN unique_violation THEN
311 suffix := suffix || ' ';
317 EXCEPTION WHEN undefined_table THEN
321 -- transfer folders the same way we transfer buckets (see above)
324 FROM reporter.report_folder
325 WHERE owner = src_usr
327 suffix := ' (' || src_usr || ')';
330 UPDATE reporter.report_folder
331 SET owner = dest_usr, name = name || suffix
332 WHERE id = folder_row.id;
333 EXCEPTION WHEN unique_violation THEN
334 suffix := suffix || ' ';
340 EXCEPTION WHEN undefined_table THEN
344 -- transfer folders the same way we transfer buckets (see above)
347 FROM reporter.output_folder
348 WHERE owner = src_usr
350 suffix := ' (' || src_usr || ')';
353 UPDATE reporter.output_folder
354 SET owner = dest_usr, name = name || suffix
355 WHERE id = folder_row.id;
356 EXCEPTION WHEN unique_violation THEN
357 suffix := suffix || ' ';
363 EXCEPTION WHEN undefined_table THEN
367 -- Finally, delete the source user
368 DELETE FROM actor.usr WHERE id = src_usr;
373 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
379 renamable_row RECORD;
385 mailing_address = NULL,
386 billing_address = NULL
390 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
391 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
392 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
393 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
394 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
395 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
396 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
397 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
399 -- Update with a rename to avoid collisions
403 WHERE owner = src_usr
405 suffix := ' (' || src_usr || ')';
409 SET owner = dest_usr, name = name || suffix
410 WHERE id = renamable_row.id;
411 EXCEPTION WHEN unique_violation THEN
412 suffix := suffix || ' ';
419 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
420 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
421 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
422 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
423 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
424 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
425 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
428 DELETE FROM action.circulation WHERE usr = src_usr;
429 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
430 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
431 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
432 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
433 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
434 DELETE FROM action.hold_request WHERE usr = src_usr;
435 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
436 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
437 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
438 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
439 DELETE FROM action.survey_response WHERE usr = src_usr;
442 DELETE FROM actor.card WHERE usr = src_usr;
443 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
445 -- The following update is intended to avoid transient violations of a foreign
446 -- key constraint, whereby actor.usr_address references itself. It may not be
447 -- necessary, but it does no harm.
448 UPDATE actor.usr_address SET replaces = NULL
449 WHERE usr = src_usr AND replaces IS NOT NULL;
450 DELETE FROM actor.usr_address WHERE usr = src_usr;
451 DELETE FROM actor.usr_note WHERE usr = src_usr;
452 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
453 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
454 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
455 DELETE FROM actor.usr_setting WHERE usr = src_usr;
456 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
457 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
460 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
461 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
462 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
463 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
464 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
465 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
468 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
469 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
470 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
471 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
472 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
473 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
474 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
475 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
478 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
479 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
480 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
481 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
484 -- Update buckets with a rename to avoid collisions
487 FROM container.biblio_record_entry_bucket
488 WHERE owner = src_usr
490 suffix := ' (' || src_usr || ')';
493 UPDATE container.biblio_record_entry_bucket
494 SET owner = dest_usr, name = name || suffix
495 WHERE id = renamable_row.id;
496 EXCEPTION WHEN unique_violation THEN
497 suffix := suffix || ' ';
506 FROM container.call_number_bucket
507 WHERE owner = src_usr
509 suffix := ' (' || src_usr || ')';
512 UPDATE container.call_number_bucket
513 SET owner = dest_usr, name = name || suffix
514 WHERE id = renamable_row.id;
515 EXCEPTION WHEN unique_violation THEN
516 suffix := suffix || ' ';
525 FROM container.copy_bucket
526 WHERE owner = src_usr
528 suffix := ' (' || src_usr || ')';
531 UPDATE container.copy_bucket
532 SET owner = dest_usr, name = name || suffix
533 WHERE id = renamable_row.id;
534 EXCEPTION WHEN unique_violation THEN
535 suffix := suffix || ' ';
544 FROM container.user_bucket
545 WHERE owner = src_usr
547 suffix := ' (' || src_usr || ')';
550 UPDATE container.user_bucket
551 SET owner = dest_usr, name = name || suffix
552 WHERE id = renamable_row.id;
553 EXCEPTION WHEN unique_violation THEN
554 suffix := suffix || ' ';
561 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
564 DELETE FROM money.billable_xact WHERE usr = src_usr;
565 DELETE FROM money.collections_tracker WHERE usr = src_usr;
566 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
569 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
570 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
571 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
572 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
575 -- Update with a rename to avoid collisions
579 FROM reporter.output_folder
580 WHERE owner = src_usr
582 suffix := ' (' || src_usr || ')';
585 UPDATE reporter.output_folder
586 SET owner = dest_usr, name = name || suffix
587 WHERE id = renamable_row.id;
588 EXCEPTION WHEN unique_violation THEN
589 suffix := suffix || ' ';
595 EXCEPTION WHEN undefined_table THEN
600 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
601 EXCEPTION WHEN undefined_table THEN
605 -- Update with a rename to avoid collisions
609 FROM reporter.report_folder
610 WHERE owner = src_usr
612 suffix := ' (' || src_usr || ')';
615 UPDATE reporter.report_folder
616 SET owner = dest_usr, name = name || suffix
617 WHERE id = renamable_row.id;
618 EXCEPTION WHEN unique_violation THEN
619 suffix := suffix || ' ';
625 EXCEPTION WHEN undefined_table THEN
630 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
631 EXCEPTION WHEN undefined_table THEN
636 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
637 EXCEPTION WHEN undefined_table THEN
641 -- Update with a rename to avoid collisions
645 FROM reporter.template_folder
646 WHERE owner = src_usr
648 suffix := ' (' || src_usr || ')';
651 UPDATE reporter.template_folder
652 SET owner = dest_usr, name = name || suffix
653 WHERE id = renamable_row.id;
654 EXCEPTION WHEN unique_violation THEN
655 suffix := suffix || ' ';
661 EXCEPTION WHEN undefined_table THEN
666 -- Update with a rename to avoid collisions
670 WHERE owner = src_usr
672 suffix := ' (' || src_usr || ')';
675 UPDATE vandelay.queue
676 SET owner = dest_usr, name = name || suffix
677 WHERE id = renamable_row.id;
678 EXCEPTION WHEN unique_violation THEN
679 suffix := suffix || ' ';