Skip to content

Reloading MRI data for mislabelled session

Jump to bottom
christinerogers edited this page Dec 7, 2015 · 5 revisions

This is a walkthrough of steps for correcting and re-loading an MRI dataset when scans have been inserted with incorrect labelling. E.g. wrong subject IDs, wrong visit label, or wrong acquisition date.

BETA VERSION - These instructions should be tested

Take Backups

  • Create a dump of the entire database:

    

    • 



mysqldump -u PreventADUser -h netmind.cbrain.mcgill.ca PreventAD > PreventAD_dump_date.sql@
```


    

  • 

    Back up key tarchive-specific tables: tarchive, tarchive_files, tarchive_find_new_uploads, tarchive_series:
    

    

    • 



mysqldump -u PreventADUser -h megamind.cbrain.mcgill.ca PreventAD tarchive tarchive_files tarchive_series tarchive_find_new_uploads > PreventAD_tarchiveTables_20121109.sql
```


    

  • 

    Back up key imaging metadata and QC tables: files, parameter_file, mri_acquisition_date, files_qcstatus, feedback_mri_comments:
    

    

    • 



mysqldump -u PreventADUser -h megamind.cbrain.mcgill.ca PreventAD files parameter_file mri_acquisition_dates files_qcstatus feedback_mri_comments > PreventAD_tarchiveTables_20121109.sql
```


    

  • 

    Create duplicates of the different MRI tables:
    

    

    • 



CREATE TABLE tarchive_bkp SELECT * FROM tarchive;
CREATE TABLE tarchive_files_bkp SELECT * FROM tarchive_files;
CREATE TABLE tarchive_series_bkp SELECT * FROM tarchive_series;
CREATE TABLE tarchive_find_new_uploads_bkp SELECT * FROM tarchive_find_new_uploads;
CREATE TABLE file_bkp SELECT * FROM files;
CREATE TABLE parameter_file_bkp SELECT * FROM parameter_file;
CREATE TABLE mri_acquisition_date_bkp SELECT * FROM mri_acquisition_dates;
```


    

  • 

    Make sure SeriesUID and EchoTime are populated for every entry in the files_qcstatus and feedback_mri_comments table.
If not all populated, run following queries:
    

    

    • 



CREATE TEMPORARY TABLE SeriesUIDs AS select pf.FileID, pf.Value AS SeriesUID from parameter_file AS pf LEFT JOIN parameter_type AS pt ON pf.ParameterTypeID=pt.ParameterTypeID where pt.Name="series_instance_uid" GROUP BY SeriesUID;
UPDATE feedback_mri_comments AS fmc, SeriesUIDs AS S SET fmc.SeriesUID=S.SeriesUID where fmc.FileID=S.FileID;
```


Move tarchive file to the incoming directory and modify the patientName in the DICOM files


Move tarchive


```bash



mv /data/+project+/data/tarchive/*/CandID_BadVisitLabel /data/incoming/project/incoming
```


Untar tarchive


```bash



tar -xvf /data/incoming/+project+/incoming/+Tarfile+
tar -xvzf /data/incoming/+project+/incoming/+BadPatientName.tar.gz+
```


Modify PatientName in all DICOMs


```mysql



dcmodify -ma PatientName="GoodPatientName" /data/incoming/+project+/incoming/+BadPatientName+/*
```


Remove back up files from the directory and rename DICOM folder with the good PatientName


```bash



rm /data/incoming/+project+/incoming/+BadPatientName+/*bak
mv /data/incoming/+project+/incoming/+BadPatientName+ /data/incoming/+project+/incoming/+GoodPatientName+
```


Delete files from the filesystem


```bash



rmdir /data/+project+/data/assembly/+CandID+/+Bad_Visit_label+  # minc files
rm /data/+project+/data/pic/+CandID+/+Bad_Visit_label+               # jpeg files
rm /data/+project+/data/jiv/+CandID+/+Bad_Visit_label+                # jiv files
rm /data/+project+/data/tarchive/*/+CandID_BadVisitlabel+            # tarchive file
```


Remove scans for the MySQL database


Find out the FileIDs of the minc files in the files table


```mysql



SELECT FileID, File FROM files WHERE File LIKE "%CandID_BadVisitLabel%";
```


Remove from the parameter_file and files tables the entries for the corresponding FileIDs


```mysql



DELETE FROM parameter_file WHERE FileID IN (@fileIDs);
DELETE FROM files WHERE FileID IN (@fileIDs);
```


Find out the TarchiveID of the uploaded tarchive


```mysql



SELECT TArchiveID, ArchiveLocation FROM tarchive WHERE ArchiveLocation LIKE "%CandID_BadVisitLabel%"
```


Remove from the tarchive_table the tarchive


```mysql



DELETE FROM tarchive WHERE TarchiveID=$BadTarchiveID
```


Rename visit_label in the session table


```mysql



SELECT ID, CandID, Visit_label FROM session WHERE CandID=$CandID AND Visit_label=$BadVisitLabel
UPDATE session SET Visit_label=$GoodVisitLabel WHERE ID=$SessionID
```


Run dicomTar.pl on the new incoming folder


```mysql



dicomTar.pl /data/incoming/+project+/incoming/+GoodPatientName+ /data/+project+/data/tarchive -database -profile prod -mri_upload_update
```


Remove -mri_upload_update option if running older pipeline versions


Run batch_upload on the inserted tarchive file


Get the list of tarchive to process


```mysql



cd /data/project/data/tarchive
ls DCM* > /data/project/data/bin/mri/tarchive_list.txt
```


Run batch_upload on list of tarchive


```mysql



batch_uploads_tarchive < tarchive_list.txt
```


Update files_qcstatus and feedback_mri_comments with new FileIDs:


Create temporary table with new FileIDs (from new files table), SeriesUID and EchoTime from files_qcstatus


```mysql



CREATE TEMPORARY TABLE FileIDs AS SELECT f.FileID, f.SeriesUID, f.EchoTime, fq.QCStatus, fq.QCFirstChangeTime, fq.QCLastChangeTime FROM files AS f LEFT JOIN files_qcstatus AS fq ON fq.SeriesUID=f.SeriesUID AND fq.EchoTime=f.EchoTime GROUP BY f.FileID;
```


update files_qcstatus with new FileIDs


```mysql



UPDATE files_qcstatus AS fq, FileIDs AS F SET fq.FileID=F.FileID WHERE fq.SeriesUID=F.SeriesUID AND fq.EchoTime=F.EchoTime;
```


Repeat to update feedback_mri_comments table


```mysql



CREATE TEMPORARY TABLE FileIDsComments AS SELECT f.FileID, f.SeriesUID, f.EchoTime, fmc.CommentTypeID, fmc.PredefinedCommentID, fmc.Comment FROM files AS f LEFT JOIN feedback_mri_comments AS fmc ON fmc.SeriesUID=f.SeriesUID AND fmc.EchoTime=f.EchoTime GROUP BY f.FileID;
UPDATE feedback_mri_comments AS fmc, FileIDsComments AS F SET fmc.FileID=F.FileID WHERE fmc.SeriesUID=F.SeriesUID AND fmc.EchoTime=F.EchoTime;
```


Update the selected in parameter_file table


in sandbox mysql database load old parameter_file and files table. Create a new table called selecteds with all the selected info. (don't do this on the live database!!)


```mysql



CREATE TABLE selecteds AS SELECT f.SeriesUID,f.EchoTime,pf.* FROM parameter_file pf JOIN files f ON f.FileID=pf.FileID WHERE ParameterTypeID=1 AND value IS NOT NULL;
```


dump the selecteds table and load it in the live database. Create a temporary table selectedFileID with the new FileIDs to insert the selected with


```mysql



CREATE TEMPORARY_TABLE selectedFileID AS select f.FileID, f.SeriesUID, f.EchoTime, s.ParameterTypeID, s.Value, s.InsertTime FROM files f JOIN selecteds s ON s.SeriesUID=f.SeriesUID AND s.EchoTime=f.EchoTime GROUP BY f.FileID;
```


Insert the selecteds into parameter_file


```mysql



INSERT INTO parameter_file (FileID,ParameterTypeID,Value,InsertTime)  (SELECT FileID, ParameterTypeID,Value,InsertTime FROM selectedFileID);
```


              


              

                

                  

                    
Home | About | Community | Setup | Technical


                  

                

              

          



  
          

            

              

                

                  

                    

                  

                

              


            
Clone this wiki locally