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

This needs to be tested!!

h2. - Create a dump of

  • +the entire database:+ @mysqldump -u PreventADUser -h netmind.cbrain.mcgill.ca PreventAD > PreventAD_dump_date.sql@
  • +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@
  • +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@

h2. - 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;



h2. -  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;



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




Move tarchive






move /data/+project+/data/tarchive/*/+CandID_BadVisitLabel+ /data/incoming/+project+/incoming





Untar tarchive






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





Modify PatientName in all DICOMs






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





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






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

h2. -  Delete files from the filesystem








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




    

  1. Find out the FileIDs of the minc files in the files table
mysql SELECT FileID, File FROM files WHERE File LIKE "%CandID_BadVisitLabel%";     

    2. 







    

  1. 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);     

    2. 







    

  1. Find out the TarchiveID of the uploaded tarchive
mysql SELECT TArchiveID, ArchiveLocation FROM tarchive WHERE ArchiveLocation LIKE "%CandID_BadVisitLabel%"     

    2. 







    

  1. Remove from the tarchive_table the tarchive
mysql DELETE FROM tarchive WHERE TarchiveID=$BadTarchiveID     

    2. 







    

  1. 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     

    2. 





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

    

    • 



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


    

  • Run batch_upload on list of tarchive

    

    • 



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
    

    

    • 



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

    

    • 



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


              


              

                

                  

                    
Home | About | Community | Setup | Technical


                  

                

              

          



  
          

            

              

                

                  

                    

                  

                

              


            
Clone this wiki locally