Database script to rename studies

[aalex]

As data coordinators, we need to rename the studies.

The search queries in the cohort builder and the file repository store the short_name of the studies in text.

Acceptance criteria

• We need to run a migration script for Riff in production. See the details of the renaming, and the technical details below.

Details of the renaming

This task is to rename the following studies in the queries for both the cohort builder and the file repository:

• Rename Adolescent Idiopathic Scoliosis to Kids First: Adolescent Idiopathic Scoliosis
• Rename Disorders of Sex Development to Kids First: Disorders of Sex Development
• Rename Ewing Sarcoma: Genetic Risk to Kids First: Ewing Sarcoma - Genetic Risk
• Rename Orofacial Cleft: European Ancestry to Kids First: Orofacial Cleft - European Ancestry
• Rename Orofacial Cleft: Latin American to Kids First: Orofacial Cleft - Latin American
• Rename Pediatric Brain Tumors: CBTTC to Consortium: Pediatric Brain Tumors - CBTTC
• Rename Syndromic Cranial Dysinnervation to Kids First: Syndromic Cranial Dysinnervation
• Rename TARGET: Neuroblastoma to NCI CRDC: TARGET - Neuroblastoma
• Rename Congenital Heart Defects to Kids First: Congenital Heart Defects
• Rename TARGET: Acute Myeloid Leukemia to NCI CRDC: TARGET - Acute Myeloid Leukemia

Technical notes

Riff stores its infos in JSON fields in a PosgreSQL database.

We can use Flyway, a database migration tool, to do the migration, and run SQL.

The SQL should look like this:

• SELECT content, id FROM riff;
• for each row, do a string substitution for each of the study names.
• UPDATE each row WHERE id = the id of the row.

The script should be located here: https://github.com/kids-first/riff/tree/master/src/main/resources/flyway/sql

• file repository queries stored in riff, as well

[aalex]

Trying to test this in QA, and then some studies got renamed there, but the ETL run failed. I logged in to the computer whose IP should be the ETL in QA, but there only seems to be a MySQL Docker container there.

[aalex]

We are waiting for the ETL to be ran in QA before merging this into the develop branch. (since merging to the develop branch will automatically deploy it to the QA environment, and we want the new studies to be renamed, first, in order to test whether or not this migration script makes old queries still work once the renaming has been done)

SEe https://github.com/cr-ste-justine/wiki/wiki/KidsFirst-Deployment

[aalex]

(QA) It worked in QA.

[aalex]

Pediatric Brain Tumors: CBTTC is not anymore changed to Consortium: Pediatric Brain Tumors - CBTTC, but now Pediatric Brain Tumor Atlas: CBTTC.