DMS Setup

From ICISWiki

Jump to: navigation, search

Contents

SETTING UP A DMS DATABASE

INTRODUCTION

DMS is the component of ICIS that manages environmental data, germplasm characterization data and evaluation data for genetic resources and crop improvement projects. It is a data warehouse that accumulates and assembles data from a variety of sources and provides access to them.

ONTOLOGY TABLES

The ontology tables of property (trait), scale and methods are vital tables of the DMS. Though the Workbook application can automatically store the entered Property, Scale and Method in the Description sheet, proper curation of those tables are necessary. One important record that should be stored in Trait table is the STUDY property. Its corresponding scale (NAME) and method should also be stored in the central DMS database. To populate the TRAIT, SCALE and METHOD tables in MS Access, the ICIS-TMS application can be used. To use the ICIS-TMS.mdb,

  1. Link the Trait, Scale and Method tables by clicking the Link TMS tables button
  2. Specify the location and file name of the database that contains the tables.
  3. Close the Link TMS form.
  4. Click the ENTRY form button in the Main Switchboard.
  5. Assign a Trait ID, enter the Trait name and abbreviation on the associated text boxes. Enter 1 if the trait name is the preferred name.
  6. Click the Scale tab and enter the scale and the allowed values if it is a discreet scale.
  7. To add another scale for the same trait, click the ADD SCALE button. Click the arrow buttons to move to previous and next record.
  8. Click the Method tab and enter the method of trait and its description
  9. To add another method for the same trait, click the ADD METHOD button. Click the arrow buttons to move between records.
  10. To add another trait, click the ADD Trait button.

UPLOADING OF LOCAL DMS TO CENTRAL

The general steps for appending the local data to Central DMS are the following. See Appendix 2.1 for the detailed SQL statements.

  1. Update the local GIDs to central GIDs using an UPDATE table from local GMS
  2. Create four temporary tables (TmpSTUDY, TmpFACTOR, TmpVARIATE, TmpUNIT) consisting of a field which will hold the negative IDs.
  3. Add another field to the temporary tables that will contain the new positive IDs. These positive IDs are assigned as [Max Central ID] + ([Min Local ID in INSTN] – [Negative ID])
  4. Check that the new positive IDs are greater than the maximum IDs in the Central DMS.
  5. Replace negative IDs in the local tables with the new positive IDs obtained from step 2.
  6. Update negative level numbers with their absolute values.
  7. Check that there are no negative IDs in the Local DMS.
  8. Append Local records to the Central.
  9. Delete all appended records from the Local DMS.
  10. Update the Local INSTN table with the minimum negative IDs that have been used.
  11. Update the Central INSTN table with the new maximum positive IDs.
  12. Delete the temporary tables.

MS ACCESS

The following are the specific SQL statements in MS Access

Local DMS

  1. UPDATE [UPDATE] INNER JOIN (LEVEL_N INNER JOIN FACTOR ON LEVEL_N.LABELID = FACTOR.LABELID) ON UPDATE.LGID = LEVEL_N.LVALUE SET LEVEL_N.LVALUE = [UPDATE].[GID] WHERE (((FACTOR.TRAITID)=251) AND ((FACTOR.SCALEID)=91) AND ((LEVEL_N.LVALUE)<0));
  2. SELECT STUDY.STUDYID INTO zNewSTUDY FROM STUDY WHERE (((STUDY.STUDYID)<0));
  3. SELECT FACTOR.LABELID INTO zNewLABEL FROM FACTOR WHERE (((FACTOR.LABELID)<0));
  4. SELECT DISTINCT LEVEL_C.LEVELNO INTO ztmpLEVEL FROM LEVEL_C WHERE (((LEVEL_C.LEVELNO)<0));
  5. INSERT INTO ztmpLEVEL ( LEVELNO ) SELECT DISTINCT LEVEL_N.LEVELNO FROM LEVEL_N WHERE (((LEVEL_N.LEVELNO)<0));
  6. SELECT DISTINCT ztmpLEVEL.LEVELNO INTO zNewLEVEL FROM ztmpLEVEL;
  7. SELECT DISTINCT OINDEX.OUNITID INTO zNewOUNIT FROM OINDEX WHERE (((OINDEX.OUNITID)<0));
  8. SELECT DISTINCT VARIATE.VARIATID INTO zNewVARIATE FROM VARIATE WHERE (((VARIATE.VARIATID)<0));
  9. SELECT DISTINCT TRAIT.TID INTO zNewTRAIT1 FROM TRAIT WHERE (((TRAIT.TID)<0));
  10. SELECT DISTINCT TRAIT.TRAITID INTO zNewTRAIT2 FROM TRAIT WHERE (((TRAIT.TRAITID)<0));
  11. SELECT DISTINCT SCALE.SCALEID INTO zNewSCALE FROM SCALE WHERE (((SCALE.SCALEID)<0));
  12. SELECT DISTINCT TMETHOD.TMETHID INTO zNewTMETHOD FROM TMETHOD WHERE (((TMETHOD.TMETHID)<0));
  13. ALTER TABLE zNewSTUDY ADD COLUMN newStudy Long
  14. ALTER TABLE zNewSTUDY ADD COLUMN ID counter
  15. ALTER TABLE zNewLabel ADD COLUMN newLabel Long
  16. ALTER TABLE zNewLabel ADD COLUMN ID Counter
  17. ALTER TABLE zNewLevel add column NewLEVEL Long
  18. ALTER TABLE zNewLevel add column ID Counter
  19. ALTER TABLE zNewOUNIT add column newOunit Long
  20. ALTER TABLE zNewOUNIT add column ID Counter
  21. ALTER TABLE zNewVARIATE add column newVariate Long
  22. ALTER TABLE zNewVARIATE add column id COUNTER
  23. ALTER TABLE zNewTRAIT1 ADD COLUMN newTID Long;
  24. ALTER TABLE zNewTRAIT1 add column id COUNTER;
  25. ALTER TABLE zNewTRAIT2 ADD COLUMN newTRAITID Long;
  26. ALTER TABLE zNewTRAIT2 add column id COUNTER;
  27. ALTER TABLE zNewSCALE ADD COLUMN newSCALEID Long;
  28. ALTER TABLE zNewSCALE add column id COUNTER;
  29. ALTER TABLE zNewTMETHOD ADD COLUMN newTMETHID Long;
  30. ALTER TABLE zNewTMETHOD add column id COUNTER;
  31. UPDATE zNewLABEL SET zNewLABEL.newLabel = [id]+[maxLabelid];maxLabelid – the highest value of field LABELID from the Central DMS
  32. UPDATE zNewSTUDY SET zNewSTUDY.newStudy = [id]+[maxStudy]; maxStudy – the highest value of field STUDYID from the Central DMS
  33. UPDATE zNewLEVEL SET zNewLEVEL.NewLEVEL = [id]+[maxLEVELNO];maxLEVELNO – the highest value of field LEVELNO from the Central DMS
  34. UPDATE zNewOUNIT SET zNewOUNIT.newOunit = [id]+[maxOunitID];maxOunitID – the highest value of field OUNITID from the Central DMS
  35. UPDATE zNewVARIATE SET zNewVARIATE.newVariate = [id]+[maxVARIATID];maxVARIATID – the highest value of field VARIATID from the Central DMS
  36. UPDATE zNewTRAIT1 SET zNewTRAIT1.newTID = [id]+[maxTID];maxTID – the highest value of field TID from the Central DMS
  37. UPDATE zNewTRAIT2 SET zNewTRAIT2.newTRAITID = [id]+[maxTRAITID];maxTRAITID – the highest value of field TRAITID from the Central DMS
  38. UPDATE zNewSCALE SET zNewSCALE.newSCALEID = [id]+[maxSCALEID];maxSCALEID – the highest value of field SCALEID from the Central DMS
  39. UPDATE zNewTMETHOD SET zNewTMETHOD.newTMETHID = [id]+[maxTMETHID];maxTMETHID – the highest value of field TMETHID from the Central DMS
  40. UPDATE FACTOR INNER JOIN zNewLABEL ON FACTOR.LABELID = zNewLABEL.LABELID SET FACTOR.LABELID = [zNewLABEL].[newLabel];
  41. UPDATE zNewLABEL INNER JOIN FACTOR ON zNewLABEL.LABELID = FACTOR.FACTORID SET FACTOR.FACTORID = [zNewLABEL].[newLabel];
  42. UPDATE zNewLABEL INNER JOIN LEVEL_C ON zNewLABEL.LABELID = LEVEL_C.LABELID SET LEVEL_C.LABELID = [zNewLABEL].[newLabel];
  43. UPDATE zNewLABEL INNER JOIN LEVEL_C ON zNewLABEL.LABELID = LEVEL_C.FACTORID SET LEVEL_C.FACTORID = [zNewLABEL].[newLabel];
  44. UPDATE LEVEL_C INNER JOIN zNewLEVEL ON LEVEL_C.LEVELNO = zNewLEVEL.LEVELNO SET LEVEL_C.LEVELNO = [zNewLEVEL].[newlevel];
  45. UPDATE zNewLABEL INNER JOIN LEVEL_N ON zNewLABEL.LABELID = LEVEL_N.LABELID SET LEVEL_N.LABELID = [zNewLABEL].[newlabel];
  46. UPDATE zNewLABEL INNER JOIN LEVEL_N ON zNewLABEL.LABELID = LEVEL_N.FACTORID SET LEVEL_N.FACTORID = [zNewLABEL].[newlabel];
  47. UPDATE zNewLEVEL INNER JOIN LEVEL_N ON zNewLEVEL.LEVELNO = LEVEL_N.LEVELNO SET LEVEL_N.LEVELNO = [zNewLEVEL].[newlevel];
  48. UPDATE zNewLABEL INNER JOIN OINDEX ON zNewLABEL.LABELID = OINDEX.FACTORID SET OINDEX.FACTORID = [zNewLABEL].[newLabel];
  49. UPDATE zNewLEVEL INNER JOIN OINDEX ON zNewLEVEL.LEVELNO = OINDEX.LEVELNO SET OINDEX.LEVELNO = [zNewLEVEL].[newlevel];
  50. UPDATE zNewOUNIT INNER JOIN OINDEX ON zNewOUNIT.OUNITID = OINDEX.OUNITID SET OINDEX.OUNITID = [zNewOUNIT].[newounit];
  51. UPDATE VARIATE INNER JOIN zNewVARIATE ON VARIATE.VARIATID = zNewVARIATE.VARIATID SET VARIATE.VARIATID = [zNewVARIATE].[newVariate];
  52. UPDATE zNewVARIATE INNER JOIN DATA_C ON zNewVARIATE.VARIATID = DATA_C.VARIATID SET DATA_C.VARIATID = [zNewVARIATE].[newVariate];
  53. UPDATE zNewOUNIT INNER JOIN DATA_C ON zNewOUNIT.OUNITID = DATA_C.OUNITID SET DATA_C.OUNITID = [zNewOUNIT].[newounit];
  54. UPDATE zNewVARIATE INNER JOIN DATA_N ON zNewVARIATE.VARIATID = DATA_N.VARIATID SET DATA_N.VARIATID = [zNewVARIATE].[newVariate];
  55. UPDATE zNewOUNIT INNER JOIN DATA_N ON zNewOUNIT.OUNITID = DATA_N.OUNITID SET DATA_N.OUNITID = [zNewOUNIT].[newounit];
  56. UPDATE STUDY INNER JOIN zNewSTUDY ON STUDY.STUDYID = zNewSTUDY.STUDYID SET STUDY.STUDYID = [zNewSTUDY].[newstudy];
  57. UPDATE zNewSTUDY INNER JOIN FACTOR ON zNewSTUDY.STUDYID = FACTOR.STUDYID SET FACTOR.STUDYID = [zNewSTUDY].[newSTudy];
  58. UPDATE zNewSTUDY INNER JOIN VARIATE ON zNewSTUDY.STUDYID = VARIATE.STUDYID SET VARIATE.STUDYID = [zNewSTUDY].[newStudy];
  59. UPDATE zNewVARIATE INNER JOIN DMSATTR ON zNewVARIATE.VARIATID = DMSATTR.DMSATREC SET DMSATTR.DMSATREC = [zNewVARIATE].[newVariate] WHERE (((DMSATTR.DMSATAB)="VARIATE"));
  60. UPDATE zNewLABEL INNER JOIN DMSATTR ON zNewLABEL.LABELID = DMSATTR.DMSATREC SET DMSATTR.DMSATREC = [zNewLABEL].[newLabel] WHERE (((DMSATTR.DMSATAB)="FACTOR"));
  61. UPDATE TRAIT INNER JOIN zNewTRAIT1 ON TRAIT.TID = zNewTRAIT1.TID SET TRAIT.TID = [zNewTRAIT1].[newTID];
  62. UPDATE TRAIT INNER JOIN zNewTRAIT2 ON TRAIT.TRAITID = zNewTRAIT2.TRAITID SET TRAIT.TRAITID = [zNewTRAIT2].[newTRAITID];
  63. UPDATE VARIATE INNER JOIN zNewTRAIT2 ON VARIATE.TRAITID = zNewTRAIT2.TRAITID SET VARIATE.TRAITID = [zNewTRAIT2].[newTRAITID];
  64. UPDATE FACTOR INNER JOIN zNewTRAIT2 ON FACTOR.TRAITID = zNewTRAIT2.TRAITID SET FACTOR.TRAITID = [zNewTRAIT2].[newTRAITID];
  65. UPDATE SCALE INNER JOIN zNewTRAIT2 ON SCALE.TRAITID = zNewTRAIT2.TRAITID SET SCALE.TRAITID = [zNewTRAIT2].[newTRAITID];
  66. UPDATE TMETHOD INNER JOIN zNewTRAIT2 ON TMETHOD.TRAITID = zNewTRAIT2.TRAITID SET TMETHOD.TRAITID = [zNewTRAIT2].[newTRAITID];
  67. UPDATE SCALE INNER JOIN zNewSCALE ON SCALE.SCALEID = zNewSCALE.SCALEID SET SCALE.SCALEID = [zNewSCALE].[newSCALEID];
  68. UPDATE FACTOR INNER JOIN zNewSCALE ON FACTOR.SCALEID = zNewSCALE.SCALEID SET FACTOR.SCALEID = [zNewSCALE].[newSCALEID];
  69. UPDATE VARIATE INNER JOIN zNewSCALE ON VARIATE.SCALEID = zNewSCALE.SCALEID SET VARIATE.SCALEID = [zNewSCALE].[newSCALEID];
  70. UPDATE TMETHOD INNER JOIN zNewTMETHOD ON TMETHOD.TMETHID = zNewTMETHOD.TMETHID SET TMETHOD.TMETHID = [zNewTMETHOD].[newTMETHID];
  71. UPDATE VARIATE INNER JOIN zNewTMETHOD ON VARIATE.TMETHID = zNewTMETHOD.TMETHID SET VARIATE.TMETHID = [zNewTMETHOD].[newTMETHID];
  72. UPDATE FACTOR INNER JOIN zNewTMETHOD ON FACTOR.TMETHID = zNewTMETHOD.TMETHID SET FACTOR.TMETHID = [zNewTMETHOD].[newTMETHID];
  73. SELECT DISTINCT DMSATTR.DMSATID INTO zNewDMSATTR FROM DMSATTR WHERE (((DMSATTR.DMSATID)<0));
  74. ALTER TABLE zNewDMSATTR add column NewATTR Long
  75. ALTER TABLE zNewDMSATTR add column id COUNTER
  76. UPDATE zNewDMSATTR SET zNewDMSATTR.NewATTR = [id]+[maxATTRID];maxATTRID – the highest value of field DMSATID from the Central DMS
  77. UPDATE DMSATTR INNER JOIN zNewDMSATTR ON DMSATTR.DMSATID = zNewDMSATTR.DMSATID SET DMSATTR.DMSATID = [zNewDMSATTR].[newAttr];
  78. SELECT DISTINCT EFFECT.EFFECTID INTO zNewEffect FROM EFFECT WHERE (((EFFECT.EFFECTID)<0));
  79. SELECT DISTINCT EFFECT.REPRESNO INTO zNewRepres FROM EFFECT WHERE (((EFFECT.REPRESNO)<0));
  80. ALTER TABLE zNewEffect add column NewEffect Long
  81. ALTER TABLE zNewRepres add column NewRepres Long
  82. ALTER TABLE zNewEffect add column ID Counter
  83. ALTER TABLE zNewRepres add column ID Counter
  84. UPDATE zNewEffect SET zNewEffect.NewEffect = [id]+[maxEffectid];maxEffectid – the highest value of field EFFECTID from the Central DMS
  85. UPDATE zNewRepres SET zNewRepres.NewRepres = [id]+[maxRepres];maxRepres – the highest value of field REPRESNO from the Central DMS
  86. UPDATE zNewEffect INNER JOIN EFFECT ON zNewEffect.EFFECTID = EFFECT.EFFECTID SET EFFECT.EFFECTID = [zNewEffect].[NewEffect];
  87. UPDATE zNewRepres INNER JOIN EFFECT ON zNewRepres.REPRESNO = EFFECT.REPRESNO SET EFFECT.REPRESNO = [zNewRepres].[NewRepres];
  88. UPDATE zNewLABEL INNER JOIN EFFECT ON zNewLABEL.LABELID = EFFECT.FACTORID SET EFFECT.FACTORID = [zNewLABEL].[newLabel];
  89. UPDATE zNewRepres INNER JOIN OINDEX ON zNewRepres.REPRESNO = OINDEX.REPRESNO SET OINDEX.REPRESNO = [zNewRepres].[NewRepres];
  90. UPDATE zNewRepres INNER JOIN VEFFECT ON zNewRepres.REPRESNO = VEFFECT.REPRESNO SET VEFFECT.REPRESNO = [zNewRepres].[newrepres];
  91. UPDATE VEFFECT INNER JOIN zNewVARIATE ON VEFFECT.VARIATID = zNewVARIATE.VARIATID SET VEFFECT.VARIATID = [zNewVARIATE].[newVariate];

Download the Database that contains the queries to update the IDs of local tables to positive central IDs

Central DMS

The following queries determine the maximum key ids in the central DMS which are parameters to the conversion queries in the local DMS database.

  1. SELECT Max(FACTOR.LABELID) AS MaxOfLABELID FROM FACTOR;
  2. SELECT Max(STUDY.STUDYID) AS MaxOfSTUDYID FROM STUDY;
  3. SELECT Max(VARIATE.VARIATID) AS MaxOfVARIATID FROM VARIATE;
  4. SELECT Max(OINDEX.OUNITID) AS MaxOfOUNITID FROM OINDEX;
  5. SELECT Max(LEVEL_C.LEVELNO) AS MaxOfLEVELNO FROM LEVEL_C;
  6. SELECT Max(LEVEL_N.LEVELNO) AS MaxOfLEVELNO FROM LEVEL_N;
  7. SELECT Max(DMSATTR.DMSATID) AS MaxOfDMSATID FROM DMSATTR;
  8. SELECT Max(EFFECT.EFFECTID) AS MaxOfEFFECTID FROM EFFECT;
  9. SELECT Max(EFFECT.REPRESNO) AS MaxOfREPRESNO FROM EFFECT;

After running the conversion queries in the local DMS, link the local DMS to the central with suffix 1 as the table names. The following queries will check if there are key ids in the local database that exists in the central

  1. SELECT OINDEX.OUNITID FROM OINDEX INNER JOIN DATA_C1 ON OINDEX.OUNITID = DATA_C1.OUNITID;
  2. SELECT OINDEX.OUNITID FROM OINDEX INNER JOIN DATA_N1 ON OINDEX.OUNITID = DATA_N1.OUNITID;
  3. SELECT OINDEX.OUNITID FROM OINDEX1 INNER JOIN OINDEX ON OINDEX1.OUNITID = OINDEX.OUNITID;
  4. SELECT FACTOR.LABELID FROM FACTOR INNER JOIN FACTOR1 ON FACTOR.LABELID = FACTOR1.LABELID;
  5. SELECT LEVEL_C.LEVELNO FROM LEVEL_C INNER JOIN LEVEL_C1 ON LEVEL_C.LEVELNO = LEVEL_C1.LEVELNO;
  6. SELECT LEVEL_N.LEVELNO FROM LEVEL_N1 INNER JOIN LEVEL_N ON LEVEL_N1.LEVELNO = LEVEL_N.LEVELNO;

To upload the data in the local DMS to the central, run the following queries in the central DMS .

  1. INSERT INTO STUDY ( StudyID, SNAME, PMKEY, Title, OBJECTIV, STYPE, SDATE, EDATE )SELECT STUDY1.STUDYID, STUDY1.SNAME, STUDY1.PMKEY, STUDY1.TITLE, STUDY1.OBJECTIV, STUDY1.STYPE, STUDY1.SDATE, STUDY1.EDATE FROM STUDY1;
  2. INSERT INTO FACTOR ( LABELID, FactorID, StudyID, FNAME, TraitID, ScaleID, TMETHID, LTYPE ) SELECT FACTOR1.LABELID, FACTOR1.FACTORID, FACTOR1.STUDYID, FACTOR1.FNAME, FACTOR1.TRAITID, FACTOR1.SCALEID, FACTOR1.TMETHID, FACTOR1.LTYPE FROM FACTOR1;
  3. INSERT INTO VARIATE ( VARIATID, StudyID, VNAME, TraitID, ScaleID, TMETHID, DTYPE, VTYPE ) SELECT VARIATE1.VARIATID, VARIATE1.STUDYID, VARIATE1.VNAME, VARIATE1.TRAITID, VARIATE1.SCALEID, VARIATE1.TMETHID, VARIATE1.DTYPE, VARIATE1.VTYPE FROM VARIATE1;
  4. INSERT INTO OINDEX ( OUNITID, FACTORID, LEVELNO, REPRESNO ) SELECT OINDEX1.OUNITID, OINDEX1.FACTORID, OINDEX1.LEVELNO, OINDEX1.REPRESNO FROM OINDEX1;
  5. INSERT INTO LEVEL_N ( LABELID, FactorID, LevelNo, LVALUE ) SELECT LEVEL_N1.LABELID, LEVEL_N1.FACTORID, LEVEL_N1.LEVELNO, LEVEL_N1.LVALUE FROM LEVEL_N1;
  6. INSERT INTO LEVEL_C ( LABELID, FactorID, LevelNo, LVALUE ) SELECT LEVEL_C1.LABELID, LEVEL_C1.FACTORID, LEVEL_C1.LEVELNO, LEVEL_C1.LVALUE FROM LEVEL_C1;
  7. INSERT INTO DATA_N ( OUNITID, VARIATID, DVALUE ) SELECT DATA_N1.OUNITID, DATA_N1.VARIATID, DATA_N1.DVALUE FROM DATA_N1;
  8. INSERT INTO DATA_C ( OUNITID, VARIATID, DVALUE ) SELECT DATA_C1.OUNITID, DATA_C1.VARIATID, DATA_C1.DVALUE FROM DATA_C1;
  9. INSERT INTO DMSATTR ( DMSATID, DMSATYPE, DMSATAB, DMSATREC, DMSATVAL ) SELECT DMSATTR1.DMSATID, DMSATTR1.DMSATYPE, DMSATTR1.DMSATAB, DMSATTR1.DMSATREC, DMSATTR1.DMSATVAL FROM DMSATTR1;
  10. INSERT INTO EFFECT ( REPRESNO, FACTORID, EFFECTID ) SELECT EFFECT1.REPRESNO, EFFECT1.FACTORID, EFFECT1.EFFECTID FROM EFFECT1;
  11. INSERT INTO VEFFECT ( REPRESNO, VARIATID ) SELECT VEFFECT1.REPRESNO, VEFFECT1.VARIATID FROM VEFFECT1;
  12. INSERT INTO TRAIT ( TID, TRAITID, TRNAME, TRABBR, TRDESC, SCALEID, TMETHID, TNSTAT )
    SELECT TRAIT1.TID, TRAIT1.TRAITID, TRAIT1.TRNAME, TRAIT1.TRABBR, TRAIT1.TRDESC, TRAIT1.SCALEID, TRAIT1.TMETHID, TRAIT1.TNSTAT FROM TRAIT1;
  13. INSERT INTO SCALE ( SCALEID, SCNAME, TRAITID, SCTYPE )
    SELECT SCALE1.SCALEID, SCALE1.SCNAME, SCALE1.TRAITID, SCALE1.SCTYPE FROM SCALE1;
  14. INSERT INTO TMETHOD ( TMETHID, TMNAME, TRAITID, TMABBR, TMDESC )
    SELECT TMETHOD1.TMETHID, TMETHOD1.TMNAME, TMETHOD1.TRAITID, TMETHOD1.TMABBR, TMETHOD1.TMDESC FROM TMETHOD1;
  15. INSERT INTO LEVELS ( LEVELNO, FACTORID ) SELECT LEVELNO, FACTORID FROM LEVELS1;
  16. INSERT INTO OBSUNIT ( OUNITID, EFFECTID ) SELECT OUNITID, EFFECTID FROM OBSUNIT1;
  17. INSERT INTO REPRESTN ( REPRESNO, EFFECTID, REPRESNAME ) SELECT REPRESNO, EFFECTID, REPRESNAME FROM REPRESTN1;
  18. INSERT INTO STEFFECT ( EFFECTID, STUDYID, EFFECTNAME ) SELECT EFFECTID, STUDYID, EFFECTNAME FROM STEFFECT1;



Download the Database that contains queries to determine maximum IDs and to upload local t o central

Upload Process Using the MS Access Tools

Download DMS-CentralQry.mdb

Download DMS-LocalQry.mdb

  1. Open the DMS-CentralQry.mdb, link the central database using the Utility Form
  2. Run Determine1 – Determine9 queries to know the maximum primary IDs of the different tables
  3. Open the DMS-LocalQry.mdb, link the local DMS database and its TMS (Trait, Scale and TMethod tables) using the UploadLocal form
  4. If the GIDs in the Local database are still negative, ask for UPDATE table from the curator of the central GMS. The UPDATE table should contain the local GIDs and their corresponding positive GIDs. Link that table using the Upload Local form
  5. Run the Reassign00 query
  6. Run Reassing01 up to the last query which do the following
    1. Creates a temporary table prefix by zNew for each table with primary key
    2. Adds an autonumber field and the IDs
    3. Gives new IDs based on the [max central ID] + [assigned autonumber]
    4. Updates the tables based on the new IDs
  7. Go back to DMS-CentralQry.mdb. Link the local database using the Utility Form.
  8. Run the Upload01 to Upload24 queries (There is Upload Local macro but it needs to be updated as some queries are missing there. This tool is also accessible from the Utility form)
    1. Checks for the new IDs in the local if existing in the central
    2. If no inconsistency, appends the records from local table to central table
  9. Check the uploaded study by retrieving them using Workbook
  10. Open the updated central DMS. Go to the Database property and enter the version number of the database
  11. Log in the ZUPDATE_AUDIT a short description of the changes done to the database
  12. Go to the corresponding Communication Project (e.g. IRIS Communication, IWIS Communication etc) in cropforge, log the changes and the version number of the updated central DMS.
  • Some points to check
  1. Existence of 0 or negative IDs in the tables
  2. The added Trait, Scale, Tmethod are not redundant

MySQL

Refer to this details for Uploading Local DMS to MySQL

SUBSETTING A DMS DATABASE

MS ACCESS

Download the MS Database with the queries

Notes:

  • SubsetStudy – is a table of studies to be included in the subset database
  • Those tables with prefix 1 are for a blank database that will contain the subset data
  • Those tables with No prefix 1 are from the source database where to get the subset.

1. DATA_C 

INSERT INTO DATA_C1 ( OUNITID, VARIATID, DVALUE )
SELECT DATA_C.OUNITID, DATA_C.VARIATID, DATA_C.DVALUE
FROM VARIATE INNER JOIN DATA_C ON VARIATE.VARIATID = DATA_C.VARIATID
WHERE (((VARIATE.STUDYID) In (select studyid from SubsetStudy)));

2. Data_N 

INSERT INTO DATA_N1 ( OUNITID, VARIATID, DVALUE )
SELECT DATA_N.OUNITID, DATA_N.VARIATID, DATA_N.DVALUE
FROM VARIATE INNER JOIN DATA_N ON VARIATE.VARIATID = DATA_N.VARIATID
WHERE (((VARIATE.STUDYID) In (select studyid from SubsetStudy)));

3. Attribute of Variate 

INSERT INTO DMSATTR1 ( DMSATID, DMSATYPE, DMSATAB, DMSATREC, DMSATVAL )
SELECT DMSATTR.DMSATID, DMSATTR.DMSATYPE, DMSATTR.DMSATAB, DMSATTR.DMSATREC, DMSATTR.DMSATVAL
FROM DMSATTR INNER JOIN VARIATE ON DMSATTR.DMSATREC = VARIATE.VARIATID
WHERE (((DMSATTR.DMSATYPE)=802) AND ((DMSATTR.DMSATAB)="VARIATE") AND ((VARIATE.STUDYID) 
In (select studyid from  SubsetStudy)));

4. Variate 

INSERT INTO VARIATE1 ( VARIATID, VNAME, TRAITID, SCALEID, TMETHID, DTYPE, VTYPE, STUDYID )
SELECT VARIATE.VARIATID, VARIATE.VNAME, VARIATE.TRAITID, VARIATE.SCALEID, VARIATE.TMETHID, VARIATE.DTYPE,  
VARIATE.VTYPE, VARIATE.STUDYID
FROM VARIATE
WHERE (((VARIATE.STUDYID) In (select studyid from SubsetStudy)));

5. Level_C 

INSERT INTO LEVEL_C1 ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT LEVEL_C.LABELID, LEVEL_C.FACTORID, LEVEL_C.LEVELNO, LEVEL_C.LVALUE
FROM FACTOR INNER JOIN LEVEL_C ON FACTOR.LABELID = LEVEL_C.FACTORID
WHERE (((FACTOR.STUDYID) In (select studyid from SubsetStudy)));

6. Level_N table 

INSERT INTO LEVEL_N1 ( LABELID, FACTORID, LEVELNO, LVALUE )
SELECT LEVEL_N.LABELID, LEVEL_N.FACTORID, LEVEL_N.LEVELNO, LEVEL_N.LVALUE
FROM FACTOR INNER JOIN LEVEL_N ON FACTOR.LABELID = LEVEL_N.FACTORID
WHERE (((FACTOR.STUDYID) In (select studyid from SubsetStudy)));

7. OINDEX table 

INSERT INTO OINDEX1 ( OUNITID, FACTORID, LEVELNO, REPRESNO )
SELECT OINDEX.OUNITID, OINDEX.FACTORID, OINDEX.LEVELNO, OINDEX.REPRESNO
FROM FACTOR INNER JOIN OINDEX ON FACTOR.FACTORID = OINDEX.FACTORID
WHERE (((FACTOR.STUDYID) In (select studyid from SubsetStudy)));

8. Effect 

INSERT INTO EFFECT1 ( REPRESNO, FACTORID, EFFECTID )
SELECT EFFECT.REPRESNO, EFFECT.FACTORID, EFFECT.EFFECTID
FROM EFFECT INNER JOIN FACTOR ON EFFECT.FACTORID = FACTOR.LABELID
WHERE (((FACTOR.STUDYID) In (select studyid from SubsetStudy)));

9. VEffect 

INSERT INTO VEFFECT1 ( REPRESNO, VARIATID )
SELECT VEFFECT.REPRESNO, VARIATE.VARIATID
FROM VARIATE INNER JOIN VEFFECT ON VARIATE.VARIATID = VEFFECT.VARIATID
WHERE (((VARIATE.STUDYID) In (select studyid from SubsetStudy)));

10. Attribute of the factor 

INSERT INTO DMSATTR1 ( DMSATID, DMSATYPE, DMSATAB, DMSATREC, DMSATVAL )
SELECT DMSATTR.DMSATID, DMSATTR.DMSATYPE, DMSATTR.DMSATAB, DMSATTR.DMSATREC, DMSATTR.DMSATVAL
FROM FACTOR INNER JOIN DMSATTR ON FACTOR.LABELID = DMSATTR.DMSATREC
WHERE (((DMSATTR.DMSATYPE)=801) AND ((DMSATTR.DMSATAB)="FACTOR") AND ((FACTOR.STUDYID) 
In (select studyid from SubsetStudy)));

11. Factor 

INSERT INTO FACTOR1 ( LABELID, FACTORID, STUDYID, FNAME, TRAITID, SCALEID, TMETHID, LTYPE )
SELECT FACTOR.LABELID, FACTOR.FACTORID, FACTOR.STUDYID, FACTOR.FNAME, FACTOR.TRAITID, FACTOR.SCALEID, 
FACTOR.TMETHID, FACTOR.LTYPE
FROM FACTOR
WHERE (((FACTOR.STUDYID) In (select studyid from SubsetStudy)));

12. Study 

INSERT INTO STUDY1 ( STUDYID, PMKEY, SNAME, TITLE, OBJECTIV, INVESTID, STYPE, SDATE, EDATE )
SELECT STUDY.STUDYID, STUDY.PMKEY, STUDY.SNAME, STUDY.TITLE, STUDY.OBJECTIV, STUDY.INVESTID, STUDY.STYPE,  
STUDY.SDATE, STUDY.EDATE
FROM STUDY
WHERE (((STUDY.STUDYID) In (select studyid from SubsetStudy)));

MySQL

Download script to create the subset

Use: 
call getDMSSubset( '<name of database where to put the subset>', '<name of Database where to get the subset>', 
'<name of the table where the studyids which is needed for subset>');
Example: 
call getDMSSubset( 'subsetDB', 'grc_local_dms', 'substudy');

UPDATE OF DMS DATABASE TO DIFFERENT VERSIONS OF ICIS

Steps for updating the first version of the DMS database to the second version

The first major change of the DMS database from the original schema is the introduction of the concept of LABEL and the global assignment of the LEVELNO. The GMS database is also modified by renaming the GUID field to GERMUID. The steps to convert the 1st version of DMS to the second version are the following:

Central DMS

  1. Change the property of FACTORID from primary key to non-primary.
  2. Add LABELID to the FACTOR, LEVEL_N, LEVEL_C and LEVEL_T tables.
  3. Update LABELID to be equal to the FACTORID.
  4. Rename LEVELNO field as TLEVELNO in LEVEL_N, LEVEL_C and OINDEX tables.
  5. Add a new LEVELNO field in LEVEL_N, LEVEL_C and OINDEX tables with data type Autonumber. Save the changes.
  6. Change the data type of the newly added LEVELNO to Number (Long).
  7. Get the maximum LEVELNO of LEVEL_N table. Remember the number.
  8. Update the LEVELNO of LEVEL_C table to be equal to Max(LEVEL_N.LEVELNO) + LEVEL_C.LEVELNO.
  9. Update the LEVELNO of OINDEX table.
    1. Relate the OINDEX table with LEVEL_N through FACTORID and TLEVELNO. Update OINDEX.LEVELNO to be equal to LEVEL_N.LEVELNO.
    2. Relate the OINDEX table with LEVEL_C through FACTORID and TLEVELNO. Update OINDEX.LEVELNO to be equal to LEVEL_C.LEVELNO.

Local DMS

  1. Change the property of FACTORID from primary key to non-primary.
  2. Add LABELID to the FACTOR, LEVEL_N, LEVEL_C and LEVEL_T tables.
  3. Update LABELID to be equal to the FACTORID.
  4. Rename LEVELNO field as TLEVELNO in LEVEL_N, LEVEL_C and OINDEX tables.
  5. Add a new LEVELNO field in LEVEL_N, LEVEL_C and OINDEX tables with data type Autonumber. Save the changes.
  6. Change the data type of the newly added LEVELNO to Number (Long).
  7. Update the LEVELNO of LEVEL_N to its negative.
  8. Get the minimum LEVELNO of LEVEL_N table. Remember the number.
  9. Update the LEVELNO of LEVEL_C table to be equal to Min(LEVEL_N.LEVELNO) - LEVEL_C.LEVELNO.
  10. Update the LEVELNO of OINDEX table.
    1. Relate the OINDEX table with LEVEL_N through FACTORID and TLEVELNO. Update OINDEX.LEVELNO to be equal to LEVEL_N.LEVELNO.
    2. Relate the OINDEX table with LEVEL_C through FACTORID and TLEVELNO. Update OINDEX.LEVELNO to be equal to LEVEL_C.LEVELNO.
  11. Remove the TLEVELNO from LEVEL_N,LEVEL_C and OINDEX tables.

Steps for updating the third version of ICIS database to the fourth version*

The next major change of the DMS database is the introduction of the concept of EFFECT which is equivalent to the notion of data set.

Central DMS

  1. Go to the ICIS\Database\Template\Central folder.
  2. Open the ConvertCentraltoV4 database
  3. Click the button “Select the Central DMS v3” and choose the database to convert.
  4. Run the command “Change TRAIT Table”. It will add the fields TID (Long) and TNSTAT (long).
  5. Click the command “Export VEFFECT and EFFECT”. This will add the two new tables to the database #being converted.
  6. Link the tables of the database to convert by clicking the button “Link Tables.”
  7. Run Step 1 of the conversion queries to create a temporary table named tmpRepres.
  8. Run Step 2 to create another temporary table named tmpEffect to contain the effects in each study and the factors defining each effect.
  9. Update the values of the Represno field of tmpEffect by clicking step 3.
  10. Run step 4 to create a temporary table tmpOindex to contain the representation number associated with each observation unit.
  11. Update the representation numbers of the observation units by running step 5.
  12. Run step 6 to populate the EFFECT table.
  13. Populate the VEFFECT table by running step 7 and step 8.
  14. Click the button “Delete the Linked Central” to remove the links to the converted DMS.
  15. Close the ConvertCentraltoV4 database

Central GMS

  1. Go to the v3 central GMS database being converted.
  2. Open the GERMPLSM table in Design View.
  3. Rename the field GUID to GERMUID and save the change.
  4. Open the UDFLDS table in Design View.
  5. Add the Scaleid field with Long Integer as its data type and save the change.
  6. Close the GMS database.


Local DMS

  1. Go to the ICIS\Database\Template\Local folder.
  2. Open the ConvertLocaltoV4 database
  3. Click the button “Select the Local DMS v3” and choose the database to convert.
  4. Run the command “Change TRAIT Table”. It will add the fields TID (Long) and TNSTAT (long).
  5. Click the command “Export VEFFECT and EFFECT”.
  6. Link the tables of the database to convert by clicking the button “Link Tables.”
  7. Run Step 1 of the conversion queries to create a temporary table named tmpRepres.
  8. Create another table named tmpEffect to contain the effects and factors of each effect by running step 2.
  9. Update the values of Represno field of tmpEffect by clicking step 3.
  10. Run step 4 to create a temporary table tmpOindex to contain the representation number associated with each observation unit.
  11. Update the representation numbers of the observation units by running step 5.
  12. Run step 6 to populate the EFFECT table.
  13. Populate the VEFFECT table by running step 7 and step 8.
  14. Click the button “Delete the Linked Local” to remove the links to the converted DMS.
  15. Close the ConvertLocaltoV4 database.

Local GMS

  1. Go to the local GMS database being converted.
  2. Open the GERMPLSM table in Design View.
  3. Rename the field GUID to GERMUID and save the change.
  4. Open the UDFLDS table in Design View.
  5. Add the Scaleid field with Long Integer as its data type and save the change.
  6. Open the LISTDATA table in Design View.
  7. Modify the length of ENTRYCD from 11 to 47 and save the change.
  8. Close the GMS database.


Update of the different versions of ICIS 5

The SQL scripts to update to different versions of ICIS 5 can be found in [| ICIS Schema] of the ICIS Communication project in cropforge.

Retrieved from "http://www.cropinfo.org/icis/index.php/DMS_Setup"
Personal tools