Make all the necessary changes to the users and proposal tables

[mutambaraf]

What is the problem and why is it a problem

There is need to update changes in user numbers in the proposal system. The current identified tables are users and proposals where the user numbers need to be updated.

Steps to reproduce (if it's a bug).

[mutambaraf]

This can be implemented with a script that try to find any missing user numbers in the users table and make necessary changes to the new proposal system databases table. This can be implemented while waiting for Update merge tool . This is one of the proposal which is being affected.[2023-10-23T13:12:06.365Z] ERROR - User was not PI or co-proposer {​​​​​​​"exception":{​​​​​​​"name":"Error","message":"User was not PI or co-proposer","stack":"Error: User was not PI or co-proposer\n at /home/node/app/build/src/factory/pdf/proposal.js:128:15\n at Generator.next (<anonymous>)\n at fulfilled (/home/node/app/build/src/factory/pdf/proposal.js:5:58)\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)"}​​​​​​​,"originalUrl":"/download/pdf/proposal/16404"​​​​​​​

[simonfernandes]

@ellen-wright @bashanlam @mutambaraf Please would you mind reviewing these steps (thumbs up or comment) for updating all existing merged users in the submissions software? As Deepak found in https://github.com/UserOfficeProject/user-office-project-issue-tracker/issues/955 it unfortunately can't be done through Trino due to Trino limitations.

Step 1

Export a CSV of all the old and new user numbers of all users who have had their accounts merged. Run in Oracle.

WITH latest_merge AS (
  SELECT
    user_number,
    new_user_number,
    ROW_NUMBER() OVER (PARTITION BY user_number ORDER BY COALESCE(new_user_number, user_number) DESC, from_date DESC) AS row_num
  FROM person
  WHERE user_number IS NOT NULL
    AND new_user_number IS NOT NULL
)
SELECT 
  user_number,
  new_user_number
FROM latest_merge
WHERE row_num = 1;

Step 2

Create a temparory table 'user_merges_temp' with columns old_user_number and new_user_number in new system db.

CREATE TABLE user_merges_temp (old_user_number integer, new_user_number integer, primary key(old_user_number, new_user_number));

Step 3

Import the CSV file into the temporary table in pgAdmin.

Step 4

Add the user to the users table if they don't already exist. Ignore updating or deleting the previous user number for simplicity.

INSERT INTO users (user_id, user_title, middlename, firstname, lastname, username, preferredname, oidc_sub, oauth_refresh_token, gender, birthdate, department, "position", email, telephone, telephone_alt, created_at, updated_at, institution_id, nationality, placeholder, oauth_issuer)
SELECT DISTINCT
    new_user_number,
    '', 
    '', 
    '',
    '', 
    new_user_number,
    '',
    '',
    '',
    '',
    '2000-01-01'::date,
    '',
    '',
    new_user_number,
    '',
    '',
    NOW(),
    NOW(),
    1,
    1,
    false,
    ''
FROM user_merges_temp
ON CONFLICT DO NOTHING;

Step 5

Update all of the occurrences of old user number with the new user number.

UPDATE event_logs AS T1 SET changed_by = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.changed_by = user_merges_temp.old_user_number;
UPDATE experiment_safety_inputs AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE fap_assignments AS T1 SET fap_member_user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.fap_member_user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.fap_id, T2.proposal_pk FROM fap_assignments T2 WHERE T2.fap_id = T1.fap_id AND T2.fap_member_user_id = user_merges_temp.new_user_number AND T2.proposal_pk = T1.proposal_pk);
DELETE FROM fap_assignments T1 USING user_merges_temp WHERE fap_member_user_id = user_merges_temp.old_user_number;
UPDATE fap_meeting_decisions AS T1 SET submitted_by = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.submitted_by = user_merges_temp.old_user_number;
UPDATE fap_reviewers AS T1 SET user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.fap_id FROM fap_reviewers T2 WHERE T2.fap_id = T1.fap_id AND T2.user_id = user_merges_temp.new_user_number);
DELETE FROM fap_reviewers T1 USING user_merges_temp WHERE user_id = user_merges_temp.old_user_number;
UPDATE fap_reviews AS T1 SET user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.proposal_pk FROM fap_reviews T2 WHERE T2.proposal_pk = T1.proposal_pk AND T2.user_id = user_merges_temp.new_user_number);
DELETE FROM fap_reviews T1 USING user_merges_temp WHERE user_id = user_merges_temp.old_user_number;
UPDATE fap_secretaries AS T1 SET user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.fap_id FROM fap_secretaries T2 WHERE T2.fap_id = T1.fap_id AND T2.user_id = user_merges_temp.new_user_number);
DELETE FROM fap_secretaries T1 USING user_merges_temp WHERE user_id = user_merges_temp.old_user_number;
UPDATE feedbacks AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE generic_templates AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE instrument_has_scientists AS T1 SET user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.instrument_id FROM instrument_has_scientists T2 WHERE T2.user_id = user_merges_temp.new_user_number AND T2.instrument_id = T1.instrument_id);
DELETE FROM instrument_has_scientists T1 USING user_merges_temp WHERE user_id = user_merges_temp.old_user_number;
UPDATE instruments AS T1 SET manager_user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.manager_user_id = user_merges_temp.old_user_number;
UPDATE internal_reviews AS T1 SET assigned_by = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.assigned_by = user_merges_temp.old_user_number;
UPDATE internal_reviews AS T1 SET reviewer_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.reviewer_id = user_merges_temp.old_user_number;
UPDATE pdf_templates AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE predefined_messages AS T1 SET last_modified_by = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.last_modified_by = user_merges_temp.old_user_number;
UPDATE proposal_user AS T1 SET user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.proposal_pk FROM proposal_user T2 WHERE T2.user_id = user_merges_temp.new_user_number AND T2.proposal_pk = T1.proposal_pk);
DELETE FROM proposal_user T1 USING user_merges_temp WHERE user_id = user_merges_temp.old_user_number;
UPDATE proposals AS T1 SET proposer_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.proposer_id = user_merges_temp.old_user_number;
UPDATE questionaries AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE redeem_codes AS T1 SET claimed_by = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.claimed_by = user_merges_temp.old_user_number;
UPDATE redeem_codes AS T1 SET created_by = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.created_by = user_merges_temp.old_user_number;
UPDATE redeem_codes AS T1 SET placeholder_user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.placeholder_user_id = user_merges_temp.old_user_number;
UPDATE role_user AS T1 SET user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.role_id FROM role_user T2 WHERE T2.user_id = user_merges_temp.new_user_number AND T2.role_id = T1.role_id);
DELETE FROM role_user T1 USING user_merges_temp WHERE user_id = user_merges_temp.old_user_number;
UPDATE samples AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE scheduled_events AS T1 SET local_contact = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.local_contact = user_merges_temp.old_user_number;
UPDATE shipments AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE technical_review AS T1 SET reviewer_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.reviewer_id = user_merges_temp.old_user_number;
UPDATE technical_review AS T1 SET technical_review_assignee_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.technical_review_assignee_id = user_merges_temp.old_user_number;
UPDATE visits AS T1 SET creator_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.creator_id = user_merges_temp.old_user_number;
UPDATE visits AS T1 SET team_lead_user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.team_lead_user_id = user_merges_temp.old_user_number;
UPDATE visits_has_users AS T1 SET user_id = user_merges_temp.new_user_number FROM user_merges_temp WHERE T1.user_id = user_merges_temp.old_user_number  AND NOT EXISTS ( SELECT T2.visit_id FROM visits_has_users T2 WHERE T2.user_id = user_merges_temp.new_user_number AND T2.visit_id = T1.visit_id);
DELETE FROM visits_has_users T1 USING user_merges_temp WHERE user_id = user_merges_temp.old_user_number;

Step 6

Drop the temporary table.

DROP TABLE user_merges_temp;

[simonfernandes]

I have run this on dev successfully now. Running on prod will wait until new merges start actively taking place via https://github.com/isisbusapps/merge-user-tool/issues/227

[simonfernandes]

@mutambaraf and @ellen-wright please could you re-review the steps above? The only change I've made is to step 5 to update all of the tables that Chi Kai has identified in his merge user tool update PR.

[ellen-wright]

Looks good to me

[simonfernandes]

Initially executed on 16th May (37 PIs and 235 users) and again today on 15th July (0 PIs and 1 user). The merge user tool and new postgres function will be able to handle merged users from this point on.