Uploading Local DMS

From ICISWiki

Contents 1 MICROSOFT ACCESS DATABASE 2 MYSQL DATABASE 2.1 STEPS IN UPLOADING LOCAL DMS TO CENTRAL DMS 2.1.1 1.  Create triggers if it does not exist 2.1.2 2.  Create the procedures if it does not exist 2.1.3 3.  Call the checkCentralTMS procedure 2.1.4 4. Manually Curate Trait, Method and Scale Tables 2.1.5 5. Call the uploadDMS procedure 2.1.5.1 UploadDMS without upload queries 2.1.5.2 uploadDMS with upload queries 2.2 PROCEDURES USED 2.2.1 checkCentralTMS 2.2.2 uploadDMSwithQueries 2.3 TRIGGERS USED 2.3.1 UpdateTrait 2.3.2 UpdateScale 2.3.3 UpdateMethod 2.4 Observed MySQL bugs for some versions

MICROSOFT ACCESS DATABASE

MYSQL DATABASE

This approach of uploading a local DMS into central DMS in MysQL database uses procedures and triggers thus requiring version 5.0 or higher. The procedures can be called just like any MySQL query.

STEPS IN UPLOADING LOCAL DMS TO CENTRAL DMS

1.  Create triggers if it does not exist

2.  Create the procedures if it does not exist

3.  Call the checkCentralTMS procedure

call checkCentralTMS ('centralDatabaseName','localDatabaseName')

Procedure Description :

The checkCentralTMS procedure checks records in local Trait, Tmethod and Scale tables if it already exists in the Central Trait, Tmethod and Scale tables. If a Trait, Tmethod or Scale value in the local DMS already exists in the Central DMS, the value in the local DMS tables will be replaced by the value from the Central DMS. If a value in the Trait, Tmethod or Scale table is changed, a trigger will update the other values in the tables where this value is linked. For example, if a trname from the local Trait table is existing in the Central Trait table, the local traitid value will be updated with the central traitid value. This update on Trait table will trigger the updateTrait trigger which updates the traitid values in the factor, variate and scale tables.

All positive Trait, Tmethod and Scale values in the local DMS will be marked as 'deleted'. Only negative values should be left in the local Trait, Tmethod and Scale tables after calling this procedure.

4. Manually Curate Trait, Method and Scale Tables

Manual curation of values is only needed if and when the following is true:

1. scale, trait or method name in the local table is not consistent with the central values. It may have spaces or underscores(_) or dashes(-) but may mean the same thing. Ex. MARKER DETECTOR is also the same as MARKER_DETECTOR and MARKER-DETECTOR.
2. there are duplicate values
   

.

5. Call the uploadDMS procedure

call uploadDMSwithQueries ('centralDatabaseName','localDatabaseName')

Procedure Description :

This procedure should be executed after the execution of checkCentralTMS procedure. UploadDMS assumes that all values remaining in the Trait, Method and Scale tables are negative values. The procedure does the following:

• gets all maximum ids in the central database
• creates new tables that will serve as the lookup table for new IDs
• inserts all negative IDs from the local DMS to the newly created tables
• generates new IDs (maximum ID from central + an auto_increment number) which will be used to replace the negative values
• create index in the newly created tables for faster searching of values
• updates the negative IDs in the local DMS tables with the newly generated IDs from the new tables
• drops the new tables.

UploadDMS without upload queries

This procedure does not have insert queries which directly inserts the local DMS into the Central DMS. Instead a mysqldump file of the local DMS is created. This dump file is then restored into the Central DMS. To create the dump file :

mysqldump -u username -ppassword localDatabaseName -t > dumpFile.sql

The -t option is used so that the dump file will only have insert queries. This option will create a dump file without "CREATE TABLE" and "DROP TABLE" statements.

To restore the dumpfile:

 mysql -u username -ppassword centralDatabaseName <dumpFile.sql

Caution:

Make sure that the fields in the Local DMS tables corresponds with the fields in the Central DMS. It might insert values on the wrong field.

uploadDMS with upload queries

The procedure contains a prepared statement that inserts local DMS values into the central DMS tables. Example of an query in the procedure.

SET @upload = concat('INSERT INTO ',centraldb,'.study ( StudyID, SNAME, PMKEY, Title, OBJECTIV, STYPE, SDATE, EDATE, USERID )
       SELECT STUDYID, SNAME, PMKEY, TITLE,OBJECTIV, STYPE, SDATE, EDATE,USERID
       FROM  ',localdb,'.study');
       PREPARE stmnt1 FROM @upload;
       EXECUTE stmnt1;
       DEALLOCATE PREPARE stmnt1;

PROCEDURES USED

checkCentralTMS

DELIMITER $$;
DROP PROCEDURE IF EXISTS `checkCentralTMS`$$
CREATE PROCEDURE `checkCentralTMS`(IN centraldb varchar(50), IN localdb varchar(50) )
BEGIN
SET @updateTrait = concat('update ',localdb,'.trait lt, ',centraldb,'.trait ct 
                           set lt.traitid  = ct.traitid
                           where lt.trname = ct.trname');
PREPARE stmnt1 FROM @updateTrait;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @updateScale = concat('update ',localdb,'.scale ls, ',centraldb,'.scale cs  
                          set ls.scaleid  = cs.scaleid, ls.traitid = cs.traitid
                          where ls.scname = cs.scname and ls.traitid = cs.traitid');
PREPARE stmnt1 FROM @updateScale;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @updateMethod = concat('update ',localdb,'.tmethod lt, ',centraldb,'.tmethod ct  
                            set lt.tmethid  = ct.tmethid, lt.traitid = ct.traitid
                            where lt.tmname = ct.tmname and lt.traitid = ct.traitid');
PREPARE stmnt1 FROM @updateMethod;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
END$$
DELIMITER ;$$

uploadDMSwithQueries

DELIMITER $$

DROP PROCEDURE IF EXISTS `uploadDMSwithQueries`$$

CREATE PROCEDURE `uploadDMSwithQueries`(IN centraldb VARCHAR(50), IN localdb VARCHAR(50))
BEGIN
DECLARE maxStudyid INT;
DECLARE maxLevelno INT;
DECLARE maxLevelc INT;
DECLARE maxLeveln INT;
DECLARE maxLabelid INT;
DECLARE maxTid INT;
DECLARE maxScaleid INT;
DECLARE maxTmethid INT;
DECLARE maxVariatid INT;
DECLARE maxOunitid INT;
DECLARE maxDmsatid INT;
DECLARE maxEffectid INT;
DECLARE maxRepresno INT;
DECLARE maxTraitid INT;

-- GET MAXIMUM IDs for study
SET @getMax = CONCAT('SELECT MAX(studyid) INTO @maxStudyid FROM ',centraldb,'.study');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(labelid) INTO @maxLabelid FROM ',centraldb,'.factor');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(levelno) INTO @maxLevelc FROM ',centraldb,'.level_c');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(levelno) INTO @maxLeveln FROM ',centraldb,'.level_n');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
IF @maxLevelc>@maxLeveln THEN
SET @maxLevelno = @maxLevelc;
ELSE
SET @maxLevelno = @maxLeveln;
END IF;
SET @getMax = CONCAT('SELECT MAX(ounitid) INTO @maxOunitid FROM ',centraldb,'.oindex');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(variatid) INTO @maxVariatid FROM ',centraldb,'.variate');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(effectid) INTO @maxEffectid FROM ',centraldb,'.effect');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(represno) INTO @maxRepresno FROM ',centraldb,'.effect');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(traitid) INTO @maxTraitid FROM ',centraldb,'.trait');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(scaleid) INTO @maxScaleid FROM ',centraldb,'.scale');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(tmethid) INTO @maxTmethid FROM ',centraldb,'.tmethod');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(dmsatid) INTO @maxDmsatid FROM ',centraldb,'.dmsattr');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;
SET @getMax = CONCAT('SELECT MAX(tid) INTO @maxTid FROM ',centraldb,'.trait');
PREPARE stmnt1 FROM @getMax;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

-- Drop table if exists
DROP TABLE IF EXISTS zNewStudy;
DROP TABLE IF EXISTS zNewLabel;
DROP TABLE IF EXISTS zNewLevel;
DROP TABLE IF EXISTS zNewOunit;
DROP TABLE IF EXISTS zNewVariate;
DROP TABLE IF EXISTS zNewEffect;
DROP TABLE IF EXISTS zNewRepresno;
DROP TABLE IF EXISTS zNewTrait;
DROP TABLE IF EXISTS zNewScale;
DROP TABLE IF EXISTS zNewTmethod;
DROP TABLE IF EXISTS zNewDmsattr; -- added

-- Create all new Tables
CREATE TABLE zNewStudy (studyid INT);
CREATE TABLE zNewLabel (labelid INT);
CREATE TABLE zNewLevel (levelno INT);
CREATE TABLE zNewOunit (ounitid INT);
CREATE TABLE zNewVariate (variatid INT);
CREATE TABLE zNewEffect (effectid INT);
CREATE TABLE zNewRepresno (represno INT);
CREATE TABLE zNewTrait (traitid INT);
CREATE TABLE zNewScale (scaleid INT);
CREATE TABLE zNewTmethod (tmethid INT);
CREATE TABLE zNewDmsattr (dmsatid INT); -- added

-- INSERT all IDs to the new tables
INSERT INTO zNewStudy
SELECT DISTINCT studyid FROM study WHERE studyid<0;
INSERT INTO zNewLabel
SELECT DISTINCT labelid FROM factor WHERE labelid<0;
INSERT INTO zNewLevel
SELECT newLevelTable.levelno FROM
(SELECT levelno FROM level_c WHERE levelno<0
UNION DISTINCT
SELECT levelno FROM level_n WHERE levelno<0) AS newLevelTable;
INSERT INTO zNewOunit
SELECT DISTINCT ounitid FROM oindex WHERE ounitid <0;
INSERT INTO zNewVariate
SELECT DISTINCT variatid FROM variate WHERE variatid<0;
INSERT INTO zNewEffect
SELECT DISTINCT effectid FROM effect WHERE effectid<0;
INSERT INTO zNewRepresno
SELECT DISTINCT represno FROM effect WHERE represno<0;
INSERT INTO zNewTrait
SELECT DISTINCT traitid FROM trait WHERE traitid<0;
INSERT INTO zNewScale
SELECT DISTINCT scaleid FROM scale WHERE scaleid<0;
INSERT INTO zNewTmethod
SELECT DISTINCT tmethid FROM tmethod WHERE tmethid<0;
INSERT INTO zNewDmsattr
SELECT DISTINCT dmsatid FROM dmsattr WHERE dmsatid<0; -- added

-- ADD new Auto increment column to the tables
ALTER TABLE zNewStudy ADD COLUMN (newStudyid INT, id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewLabel ADD COLUMN (newLabelid INT, id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewLevel ADD COLUMN (newlevelno INT, id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewOunit ADD COLUMN (newOunitid INT, id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewVariate ADD COLUMN (newVariatid INT , id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewEffect ADD COLUMN (newEffectid INT , id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewRepresno ADD COLUMN (newRepresno INT , id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewTrait ADD COLUMN (newTraitid INT , id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewScale ADD COLUMN (newScaleid INT , id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewTmethod ADD COLUMN (newTmethid INT , id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE zNewDmsattr ADD COLUMN (newDmsatid INT , id INT AUTO_INCREMENT NOT NULL PRIMARY KEY); -- added

-- Generate new IDs
UPDATE zNewStudy SET newStudyid = id + @maxStudyid;
UPDATE zNewLabel SET newLabelid = id + @maxLabelid;
UPDATE zNewLevel SET newLevelno = id + @maxLevelno;
UPDATE zNewOunit SET newOunitid = id + @maxOunitid;
UPDATE zNewVariate SET newVariatid = id + @maxVariatid;
UPDATE zNewEffect SET newEffectid = id + @maxEffectid;
UPDATE zNewRepresno SET newRepresno = id + @maxRepresno;
UPDATE zNewTrait SET newTraitid = id + @maxTraitid;
UPDATE zNewScale SET newScaleid = id + @maxScaleid;
UPDATE zNewTmethod SET newTmethid = id + @maxTmethid;
UPDATE zNewDmsattr SET newDmsatid = id + @maxDmsatid; -- added

-- ADD INDEX to New Tables
ALTER TABLE zNewStudy ADD INDEX studyid(studyid);
ALTER TABLE zNewLabel ADD INDEX labelid(labelid);
ALTER TABLE zNewLevel ADD INDEX levelno(levelno);
ALTER TABLE zNewOunit ADD INDEX ounitid(ounitid);
ALTER TABLE zNewVariate ADD INDEX variatid(variatid);
ALTER TABLE zNewEffect ADD INDEX effectid(effectid);
ALTER TABLE zNewRepresno ADD INDEX represno(represno);
ALTER TABLE zNewTrait ADD INDEX traitid(traitid);
ALTER TABLE zNewScale ADD INDEX scaleid(scaleid);
ALTER TABLE zNewTmethod ADD INDEX tmethid(tmethid);
ALTER TABLE zNewDmsattr ADD INDEX dmsatid(dmsatid); -- added

-- Update GIDs of local
-- added
UPDATE updategid
INNER JOIN (LEVEL_N
INNER JOIN FACTOR ON LEVEL_N.LABELID = FACTOR.LABELID) ON updategid.LGID = LEVEL_N.LVALUE
SET LEVEL_N.LVALUE = updategid.GID
WHERE ((FACTOR.TRAITID=251) AND (FACTOR.SCALEID=91) AND (LEVEL_N.LVALUE<0));

-- UPDATE STUDY
UPDATE study s, zNewStudy z
SET s.studyid = z.newStudyid
WHERE s.studyid = z.studyid;

-- UPDATE FACTOR
UPDATE factor f, zNewLabel z
SET f.factorid = z.newLabelid
WHERE f.factorid = z.labelid;
UPDATE factor f, zNewLabel z
SET f.labelid = z.newLabelid
WHERE f.labelid = z.labelid;
UPDATE factor f, zNewStudy z
SET f.studyid = z.newStudyid
WHERE f.studyid = z.studyid;
UPDATE factor f, zNewTrait z
SET f.traitid = z.newTraitid
WHERE f.traitid = z.Traitid;
UPDATE factor f, zNewScale z
SET f.scaleid = z.newScaleid
WHERE f.scaleid = z.scaleid;
UPDATE factor f, zNewTmethod z
SET f.tmethid = z.newTmethid
WHERE f.tmethid = z.Tmethid;

-- UPDATE LEVEL_C
UPDATE level_c l, zNewLevel z
SET l.levelno = z.newLevelno
WHERE l.levelno = z.levelno;
UPDATE level_c l, zNewLabel z
SET l.labelid = z.newLabelid
WHERE l.labelid = z.labelid;
UPDATE level_c l, zNewLabel z
SET l.factorid = z.newLabelid
WHERE l.factorid = z.labelid;

-- UPDATE LEVEL_N
UPDATE level_n l, zNewLevel z
SET l.levelno = z.newLevelno
WHERE l.levelno = z.levelno;
UPDATE level_n l, zNewLabel z
SET l.labelid = z.newLabelid
WHERE l.labelid = z.labelid;
UPDATE level_n l, zNewLabel z
SET l.factorid = z.newLabelid
WHERE l.factorid = z.labelid;

-- UPDATE LEVELS added 3/6/2010
UPDATE levels l, zNewLevel z
SET l.levelno = z.newLevelno
WHERE l.levelno = z.levelno;
UPDATE levels l, zNewLabel z
SET l.factorid = z.newLabelid
WHERE l.factorid = z.labelid;

-- UPDATE EFFECT
UPDATE effect e, zNewEffect z
SET e.effectid = z.newEffectid
WHERE e.effectid = z.effectid;
UPDATE effect e, zNewLabel z
SET e.factorid = z.newLabelid
WHERE e.factorid = z.labelid;
UPDATE effect e, zNewRepresno z
SET e.represno = z.newRepresno
WHERE e.represno = z.represno;

-- UPDATE OINDEX
UPDATE oindex o, zNewOunit z
SET o.ounitid = z.newOunitid
WHERE o.ounitid = z.ounitid;
UPDATE oindex o, zNewLabel z
SET o.factorid = z.newLabelid
WHERE o.factorid = z.labelid;
UPDATE oindex o, zNewLevel z
SET o.levelno = z.newLevelno
WHERE o.levelno = z.levelno;
UPDATE oindex o, zNewRepresno z
SET o.represno = z.newRepresno
WHERE o.represno = z.represno;

-- UPDATE VEFFECT
UPDATE veffect v, zNewRepresno z
SET v.represno = z.newRepresno
WHERE v.represno = z.represno;
UPDATE veffect v, zNewVariate z
SET v.variatid = z.newVariatid
WHERE v.variatid = z.variatid;

-- UPDATE VARIATE
UPDATE variate v, zNewVariate z
SET v.variatid = z.newVariatid
WHERE v.variatid = z.variatid;
UPDATE variate v, zNewStudy z
SET v.studyid = z.newStudyid
WHERE v.studyid = z.studyid;
UPDATE variate v, zNewTrait z
SET v.traitid = z.newTraitid
WHERE v.traitid = z.Traitid;
UPDATE variate v, zNewScale z
SET v.scaleid = z.newScaleid
WHERE v.scaleid = z.scaleid;
UPDATE variate v, zNewTmethod z
SET v.tmethid = z.newTmethid
WHERE v.tmethid = z.Tmethid;

-- UPDATE DATA_C
UPDATE data_c d, zNewVariate z
SET d.variatid = z.newVariatid
WHERE d.variatid = z.variatid;
UPDATE data_c d, zNewOunit z
SET d.ounitid = z.newOunitid
WHERE d.ounitid = z.ounitid;

-- UPDATE DATA_N
UPDATE data_n d, zNewVariate z
SET d.variatid = z.newVariatid
WHERE d.variatid = z.variatid;
UPDATE data_n d, zNewOunit z
SET d.ounitid = z.newOunitid
WHERE d.ounitid = z.ounitid;

-- UPDATE DMSATTR
UPDATE dmsattr d, zNewDmsattr z
SET d.dmsatid = z.newDmsatid
WHERE d.dmsatid = z.dmsatid;
UPDATE dmsattr d, zNewVariate z
SET d.dmsatrec = z.newVariatid
WHERE d.dmsatrec = z.variatid
AND d.dmsatab = "VARIATE";
UPDATE dmsattr d, zNewLabel z
SET d.dmsatrec = z.newLabelid
WHERE d.dmsatrec = z.labelid
AND d.dmsatab = "FACTOR";
UPDATE dmsattr d, zNewRepresno z
SET d.dmsatrec = z.newRepresno
WHERE d.dmsatrec = z.represno
AND d.dmsatab = "DATASET"; -- added

-- UPDATE TRAIT
UPDATE trait t, zNewTrait z
SET t.traitid = z.newTraitid, t.tid = z.id + @maxTid
WHERE t.traitid = z.traitid;

-- UPDATE SCALE
UPDATE scale s, zNewTrait z
SET s.traitid = z.newTraitid
WHERE s.traitid = z.traitid;
UPDATE scale s, zNewScale z
SET s.scaleid = z.newScaleid
WHERE s.scaleid = z.scaleid;

-- UPDATE TMETHOD
UPDATE tmethod t, zNewTrait z
SET t.traitid = z.newTraitid
WHERE t.traitid = z.traitid;
UPDATE tmethod t, zNewTmethod z
SET t.tmethid = z.newTmethid
WHERE t.tmethid = z.tmethid;

-- UPDATE SCALEDIS
UPDATE scaledis s, zNewScale z
SET s.scaleid = z.newScaleid
WHERE s.scaleid = z.scaleid;

-- UPDATE OBSUNIT added 3/6/2010
UPDATE obsunit s, zNewOunit z
SET s.ounitid = z.newOunitid
WHERE s.ounitid = z.ounitid;
UPDATE obsunit s, zNewEffect z
SET s.effectid = z.newEffectid
WHERE s.effectid = z.effectid;
-- UPDATE REPRESTN added 3/6/2010
UPDATE represtn s, zNewRepresno z
SET s.represno = z.newRepresno
WHERE s.represno = z.represno;
UPDATE represtn s, zNewEffect z
SET s.effectid = z.newEffectid
WHERE s.effectid = z.effectid;

-- UPDATE STEFFECT added 3/6/2010
UPDATE steffect e, zNewEffect z
SET e.effectid = z.newEffectid
WHERE e.effectid = z.effectid;
UPDATE steffect e, zNewStudy z
SET e.studyid = z.newStudyid
WHERE e.studyid = z.studyid;

-- UPLOAD New values to Central TABLES
SET @upload = CONCAT('INSERT INTO ',centraldb,'.study (StudyID, SNAME, PMKEY, Title, OBJECTIV, STYPE, SDATE, EDATE, USERID)
SELECT STUDYID, SNAME, PMKEY, TITLE, OBJECTIV, STYPE, SDATE, EDATE, USERID
FROM ',localdb,'.study');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.FACTOR (LABELID, FactorID, StudyID, FNAME, TraitID, ScaleID, TMETHID, LTYPE)
SELECT LABELID, FACTORID, STUDYID, FNAME, TRAITID, SCALEID, TMETHID, LTYPE
FROM ',localdb,'.FACTOR');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.VARIATE (VARIATID, StudyID, VNAME, TraitID, ScaleID, TMETHID, DTYPE, VTYPE)
SELECT VARIATID, STUDYID ,VNAME, TRAITID, SCALEID, TMETHID, DTYPE, VTYPE
FROM ',localdb,'.VARIATE');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ', centraldb,'.OINDEX (OUNITID, FACTORID, LEVELNO, REPRESNO)
SELECT OUNITID, FACTORID, LEVELNO, REPRESNO
FROM ',localdb,'.OINDEX');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.LEVEL_N (LABELID, FactorID, LevelNo, LVALUE)
SELECT LABELID, FACTORID, LEVELNO, LVALUE
FROM ',localdb,'.LEVEL_N');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.LEVEL_C (LABELID, FactorID, LevelNo, LVALUE)
SELECT LABELID , FACTORID, LEVELNO, LVALUE
FROM ',localdb,'.LEVEL_C');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.DATA_N (OUNITID, VARIATID, DVALUE)
SELECT OUNITID, VARIATID, DVALUE
FROM ',localdb,'.DATA_N');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.DATA_C (OUNITID, VARIATID, DVALUE)
SELECT OUNITID, VARIATID, DVALUE
FROM ',localdb,'.DATA_C');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.DMSATTR (DMSATID, DMSATYPE, DMSATAB, DMSATREC, DMSATVAL)
SELECT DMSATID, DMSATYPE, DMSATAB, DMSATREC, DMSATVAL
FROM ', localdb,'.DMSATTR WHERE DMSATAB<>"LISTNMS"');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.EFFECT (REPRESNO, FACTORID, EFFECTID)
SELECT REPRESNO, FACTORID, EFFECTID
FROM ',localdb,'.EFFECT');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.VEFFECT (REPRESNO, VARIATID)
SELECT REPRESNO, VARIATID
FROM ',localdb,'.VEFFECT');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.TRAIT (TID, TRAITID, TRNAME, TRABBR, TRDESC, SCALEID, TMETHID, TNSTAT)
SELECT TID, TRAITID, TRNAME, TRABBR, TRDESC, SCALEID, TMETHID, TNSTAT
FROM ',localdb,'.TRAIT WHERE (TNSTAT<>9);'); -- added TNSTAT<>9
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ', centraldb,'.SCALE (SCALEID, SCNAME, TRAITID, SCTYPE)
SELECT SCALEID, SCNAME, TRAITID, SCTYPE
FROM ', localdb,'. SCALE');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.TMETHOD (TMETHID, TMNAME, TRAITID, TMABBR, TMDESC)
SELECT TMETHID, TMNAME, TRAITID, TMABBR, TMDESC
FROM ',localdb,'.TMETHOD');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

-- added 3/6/2010
SET @upload = CONCAT('INSERT INTO ',centraldb,'.LEVELS (LEVELNO, FACTORID)
SELECT LEVELNO, FACTORID
FROM ',localdb,'.LEVELS');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.OBSUNIT (OUNITID, EFFECTID)
SELECT OUNITID, EFFECTID
FROM ',localdb,'.OBSUNIT');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.REPRESTN (REPRESNO, EFFECTID, REPRESNAME)
SELECT REPRESNO, EFFECTID, REPRESNAME
FROM ',localdb,'.REPRESTN');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

SET @upload = CONCAT('INSERT INTO ',centraldb,'.STEFFECT (EFFECTID, STUDYID, EFFECTNAME)
SELECT EFFECTID, STUDYID, EFFECTNAME
FROM ',localdb,'.STEFFECT');
PREPARE stmnt1 FROM @upload;
EXECUTE stmnt1;
DEALLOCATE PREPARE stmnt1;

END$$

DELIMITER ;

TRIGGERS USED

UpdateTrait

This trigger is an update trigger for the local Trait table. It updates the traitid value in the Factor, Scale and Variate tables if the traitid field in the Trait table is updated.

DELIMITER $$;
CREATE TRIGGER `updateTrait`
AFTER UPDATE ON Trait for each row
BEGIN
UPDATE factor set factor.traitid  = new.traitid where factor.traitid = old.traitid;
UPDATE scale set scale.traitid = new.traitid where scale.traitid = old.traitid;
UPDATE variate set variate.traitid = new.traitid where variate.traitid = old.traitid;
UPDATE tmethod set tmethod.traitid = new.traitid where tmethod.traitid = old.traitid;
END$$
DELIMITER ;$$

UpdateScale

This trigger is an update trigger for the local Scale table. It updates the scaleid value in the Factor, Variate, scalecon, scaledis and dmsattr tables if the traitid field in the Trait table is updated.

DELIMITER $$;
CREATE TRIGGER `updateScale`
AFTER UPDATE ON scale for each row
BEGIN
UPDATE factor set factor.scaleid  = new.scaleid 
             where factor.traitid = old.traitid and factor.scaleid = old.scaleid; 
UPDATE variate set variate.scaleid = new.scaleid 
             where variate.traitid = old.traitid and variate.scaleid = old.scaleid; 
UPDATE scalecon set scalecon.scaleid = new.scaleid where scalecon.scaleid = old.scaleid; 
UPDATE scaledis set scaledis.scaleid = new.scaleid where scaledis.scaleid = old.scaleid; 
END$$
DELIMITER ;$$

UpdateMethod

This trigger is an update trigger for the Tmethod table in the local DMS. It updates the tmethid field in the factor and variate table if the traitid field in the Trait table is updated.

DELIMITER $$;
DROP TRIGGER `updateMethod`$$
CREATE TRIGGER `updateTmethod` AFTER UPDATE on `tmethod`
FOR EACH ROW 
BEGIN
UPDATE factor SET factor.tmethid  = new.tmethid 
       WHERE factor.tmethid = old.tmethid;
UPDATE variate SET variate.tmethid = new.tmethid 
       WHERE variate.tmethid = old.tmethid;
END$$
DELIMITER ;$$

Observed MySQL bugs for some versions

• Auto_increment in version 5.0.15-nt - If there are more than one auto_increment field, the next auto_increment field will begin with the last value of the first auto_increment field.
• Error in Usage of Union and INTO statement in version 5.1.22 - values from a UNION of tables cannot be stored into a variable.