GEMS data migration from DMS database

From ICISWiki

Contents 1 What values in the DMS tables can be migrated into the GEMS_GENOTYPE_SCORES table? 2 Data Curation 2.1 Create a temporary table to store the new GEMS values found in the DMS database 2.2 Insert all the marker IDs and Marker Names into the temporary table 2.3 Insert all the molecular variant IDs and molecular variant names into the temporary table 2.4 Insert new found marker and molecular values into GEMS tables 2.5 Update GEMS IDs in the DMS database 3 Data Migration

What values in the DMS tables can be migrated into the GEMS_GENOTYPE_SCORES table?

1. ounitid
2. level values of factors GID,MARKER, ALLELE NUMBER
3. variate values of variates ALLELE_ID and FREQ

Data Curation

Create a temporary table to store the new GEMS values found in the DMS database

CREATE TABLE `newgemsvalues` (  
`gentity_id` int(11) NOT NULL AUTO_INCREMENT,
 `dmsName` varchar(255) DEFAULT NULL,
 `dmsID` int(11) DEFAULT NULL,
 `dmsType` int(11) DEFAULT NULL,
 PRIMARY KEY (`gentity_id`));

Insert all the marker IDs and Marker Names into the temporary table

INSERT INTO newgemsvalues ( dmsName, dmsID,dmstype) 
SELECT x.marker, x.markerid FROM 
(
SELECT DISTINCT  lc.lvalue AS marker, ln2.lvalue AS markerid  
FROM oindex o INNER JOIN  level_c lc ON o.levelno  = lc.levelno
INNER JOIN  level_n ln2  ON ln2.levelno = lc.levelno
INNER JOIN  factor f ON (ln2.labelid = f.labelid AND f.scaleid = 516 ) )AS X 
LEFT JOIN gems54.gems_names g ON  
g.gobjid = x.markerid
AND g.gobjtype = "gems_marker_detector"
WHERE g.gnval IS NULL;

Insert all the molecular variant IDs and molecular variant names into the temporary table

INSERT INTO newgemsvalues (dmstype,dmsID, dmsName)
SELECT DISTINCT   ln2.lvalue AS markerid, w.dvalue AS alleleid, dc.dvalue AS mvname , mvid
FROM (variate v INNER JOIN data_c dc ON (dc.variatid = v.variatid AND v.scaleid = 501)
INNER JOIN (SELECT dn.ounitid,dn.variatid, dn.dvalue 
	    FROM data_n dn INNER JOIN variate va 
	    WHERE dn.variatid = va.variatid 
	    AND va.scaleid = 503) w 
	    ON w.ounitid = dc.ounitid 
INNER JOIN oindex o ON o.ounitid = w.ounitid
INNER JOIN  level_n ln2  ON ln2.levelno = o.levelno
INNER JOIN  factor f ON (ln2.labelid = f.labelid AND f.scaleid = 516 ))
LEFT JOIN gems54.gems_mv ON mvid = w.dvalue
WHERE mvid

Insert new found marker and molecular values into GEMS tables

• Insert marker to gems_entity table

INSERT INTO gems_entity (gentity_id, class_id, description)
SELECT gentity_id, 120, dmsId FROM newgemsvalues WHERE dmstype = 0;

• Insert molecular variant into gems_entity table

INSERT INTO gems_entity (gentity_id, class_id, description)
SELECT gentity_id, 116, dmsId FROM newgemsvalues WHERE dmstype !=0;

• Insert markers into gems_genomic_feature_detector

INSERT INTO gems_genomic_feature_detector (gfdetector_id, gfd_name, class_id, term_id)
SELECT gentity_id, dmsName, 120, 0 FROM newgemsvalues WHERE dmstype = 0;

• Insert molecular variant into gems_detected_variant

INSERT INTO gems_detected_variant (gdvariant_id, gdv_name, class_id, term_id, gfdetector_id, gmfeature_id)
SELECT gentity_id, dmsName, 116,0, dmsID,0 FROM newgemsvalues WHERE dmstype != 0;

Update GEMS IDs in the DMS database

• Update marker IDs in the level_n table

UPDATE level_n l, factor f,gems6_with_data.gems_annotation  a
SET l.lvalue = a.gentity_id
WHERE
l.lvalue = a.ga_value_id
AND l.labelid = f.labelid
AND f.scaleid = 516
AND term_id = 6; 

• update allele_id from data_n table

UPDATE 
data_n dn, variate v, gems6_with_data.gems_annotation  a
SET dn.dvalue = a.gentity_id
WHERE 
dn.variatid  =v.variatid
AND dn.dvalue  = a.ga_value_id 
AND v.scaleid = 503
AND a.term_id = 9

Data Migration