Description

Story: BI-2283

This migration gives the experiment_program_user_role.program_user_role_id foreign key constraint the ON DELETE CASCADE behavior. From the end user perspective, when a program user in the "Experimental Collaborator" role is assigned to a different role, this operation will not result in an error, and any record of the experiments they were given access to as an "Experimental Collaborator" will be lost.

Because changing the role a program user is in results in a DELETE followed by an INSERT, changing a program user's role results in any associated rows in experiment_program_user_role being deleted once this migration is applied.

Testing

1. Run this migration.
2. If it hasn't been merged into develop yet, check out and run the feature/BI-2258 branch of bi-api.
3. Upload all the data files in this story.
4. Make a user you can log in as an "Experimental Collaborator". You'll need the email address of this user for step 6.
5. Navigate to the detail view of an experiment and copy the programId and experimentId from the URL for step 6.
6. Run the following SQL script against your bidb database after pasting in the email address from step 4 and the programId and experimentId from step 5.

   -- Inputs:
   --      - the email of the experimental collaborator user
   --      - the program id
   --      - the experiment id
   
   DO $$
       DECLARE
           -- populate these variables!
           collaborator_email text := 'REPLACE';
           program_uuid uuid := 'REPLACE'::uuid;
           experiment_uuid uuid := 'REPLACE'::uuid;
       BEGIN
           INSERT INTO
               experiment_program_user_role (experiment_id, program_user_role_id, created_by, updated_by)
           VALUES
           (
               experiment_uuid,
               (SELECT r.id FROM program_user_role r JOIN public.bi_user u on u.id = r.user_id WHERE u.email = collaborator_email AND r.program_id = program_uuid),
               '00000000-0000-0000-0000-000000000000'::uuid,
               '00000000-0000-0000-0000-000000000000'::uuid
           )
           ;
       END
   $$;
   

7. [BONUS! - tests BI-2258] Step 6 authorized the Experimental Collaborator to access one of the experiments. Navigate to the experiment list view as the Experimental Collaborator to ensure the results are filtered to only the authorized experiment(s).
8. Now, as a System Admin, change the role that the Experimental Collaborator user is in. Ensure that saving does not produce an error. If you check the database, you will see that the row that step 6 inserted into experiment_program_user_role has been deleted.

Checklist:

• I have performed a self-review of my own code
• I have tested my code and ensured it meets the acceptance criteria of the story
• I have tested that my code works with both the brapi-java-server and BreedBase
• I have create/modified unit tests to cover this change
• I have commented my code, particularly in hard-to-understand areas
• I have made corresponding changes to documentation
• I have run TAF: <please include a link to TAF run>