ICIS Data Warehouse Creation and Maintenance FAQs
From ICISWiki
(Redirected from ICIS Data Warehouse Creation and Maintenance)
What are the Data Warehouse tables needed by ICIS and when are they used?
To generate Pedigree trees in web application:
- wh_der_children
- wh_man_children
- See Pedigree Viewer for Developers
For getting all entries in a dataset for a given study in web application
- wh_columns
- wh_data
- wh_datasets
For cross-study queries (web application)
- wh_data
- wh_countdatapoints (new as of Sept 2009)
- factors (table adapted from original CropFinder by J.C. Alarcon)
- fieldsetup (table adapted from original CropFinder by J.C. Alarcon)
- variates (new as of Sept 2009; same concept as factors table. Columns defined by threshold in wh_countdatapoints table)
Data Warehouse Tables for Pedigree Tree generation
Schema of wh_der_children
Fields
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
| gid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| chgid | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | |||
| nval | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references |
| Indexes |
| Table | Non unique | Key name | Seq in index | Column name | Collation | Cardinality | Sub part | Packed | Null | Index type | Comment |
| wh_der_children | 1 | wh_der_children_idx1 | 1 | gid | A | 619584 | (NULL) | (NULL) | YES | BTREE |
MySQL Data Definition script:
CREATE TABLE wh_der_children ( gid int(11), chgid int(11), nval varchar(255) )ENGINE=MyISAM CHARSET=utf8;
Sample data (for rice):
Schema of wh_man_children
Table name: wh_man_children
Fields
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
| gid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| chgid | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | |||
| nval | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references |
| Indexes |
| Table | Non unique | Key name | Seq in index | Column name | Collation | Cardinality | Sub part | Packed | Null | Index type | Comment |
| wh_man_children | 1 | wh_man_children_idx1 | 1 | gid | A | 202427 | (NULL) | (NULL) | YES | BTREE |
MySQL Data Definition script:
CREATE TABLE wh_man_children ( gid int(11), chgid int(11), nval varchar(255) )ENGINE=MyISAM CHARSET=utf8;
Sample data (for rice):
How they are used
wh_der_children
- used for showing children of a given germplasm and of all other entities in the derivative neighborhood
- sql:
wh_man_children
- used for showing children of a given germplasm and of all other entities in the maintenance neighborhood
- sql:
Data Warehouse Tables for single study dataset retrieval
Schema of wh_datasets
MySQL Data Definition script:
DROP TABLE IF EXISTS `wh_datasets`; CREATE TABLE `wh_datasets` ( `study` int(11) default NULL, -- the study ID `dataset` int(11) default NULL, -- dataset number (a.k.a. REPRESNO) `maxrow` int(11) default NULL, -- maximum number of rows for the dataset `maxcol` int(11) default NULL, -- maximum number of columns for the dataset `desc` varchar(255) default NULL -- description of the dataset ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Schema of wh_columns
MySQL Data Definition script:
DROP TABLE IF EXISTS `wh_columns`; CREATE TABLE `wh_columns` ( `study` int(11) default NULL, -- the study ID `dataset` int(11) default NULL, -- this is the REPRESNO (representation number) `col` int(11) default NULL, -- column number (1 to n) `colname` varchar(255) default NULL, -- column name: combination of trait name-scale name-tmethod name `fieldid` int(11) default NULL, -- contains the LABELID or VARIATID, depending on coltype `coltype` char(1) default NULL, -- "F" (factor) or "V" (variate) `valtype` char(1) default NULL, -- "C" if text, "N" if numeric `traitid` int(11) default NULL, -- link to TRAIT table `scaleid` int(11) default NULL, -- link to SCALE table `tmethid` int(11) default NULL -- link to TMETHOD table ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Sample data (for rice):
Schema of wh_data
wh_data
Fields
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
| study | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | |||
| dataset | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| datarow | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| ounitid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| datacol | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| coltype | char(1) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| valtype | char(1) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| dataval | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| datavaldesc | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| traitid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| scaleid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| tmethid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references |
| Indexes |
| Table | Non unique | Key name | Seq in index | Column name | Collation | Cardinality | Sub part | Packed | Null | Index type | Comment |
| wh_data | 1 | wh_data_idx1 | 1 | dataset | A | 1482 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx2 | 1 | ounitid | A | 1275564 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx3 | 1 | datacol | A | 141 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx5 | 1 | datarow | A | 163272 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx6 | 1 | traitid | A | 240 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx7 | 1 | scaleid | A | 378 | (NULL) | (NULL) | YES | BTREE | |
| wh_data | 1 | wh_data_idx8 | 1 | tmethid | A | 427 | (NULL) | (NULL) | YES | BTREE |
MySQL Data Definition script:
CREATE TABLE `wh_data` ( `study` int(11) default NULL, -- link to study ID in wh_columns table `dataset` int(11) default NULL, -- link to dataset # in wh_columns table `datarow` int(11) default NULL, -- row number (1 to n) `ounitid` int(11) default NULL, -- the observation unit ID (link to OINDEX table) `datacol` int(11) default NULL, -- column number (1 to n) `coltype` char(1) default NULL, -- "F" (factor) or "V" (variate) (from wh_columns table) `valtype` char(1) default NULL, -- "C" if text, "N" if numeric (from wh_columns table) `dataval` varchar(255) default NULL, -- the data value `datavaldesc` varchar(255) default NULL, -- the description of the data value (if available in SCALEDIS) `traitid` int(11) default NULL, `scaleid` int(11) default NULL, `tmethid` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Sample data (for rice):
Data Warehouse Tables for cross study queries
Schema of wh_data
Schema of wh_countdatapoints
Currently for variates only (across all datasets).
Fields
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
| variablename | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| valtype | varchar(2) | utf8_general_ci | YES | MUL | (NULL) | select,insert,update,references | ||
| traitid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| scaleid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| tmethid | int(11) | (NULL) | YES | MUL | (NULL) | select,insert,update,references | ||
| count_datasets | int(11) | (NULL) | YES | 0 | select,insert,update,references | |||
| count_datapoints | int(11) | (NULL) | YES | 0 | select,insert,update,references |
Indexes
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| wh_countdatapoints | 1 | wh_countdatapoints_idx1 | 1 | valtype | A | 1 | (NULL) | (NULL) | YES | BTREE | |
| wh_countdatapoints | 1 | wh_countdatapoints_idx2 | 1 | traitid | A | 200 | (NULL) | (NULL) | YES | BTREE | |
| wh_countdatapoints | 1 | wh_countdatapoints_idx3 | 1 | scaleid | A | 300 | (NULL) | (NULL) | YES | BTREE | |
| wh_countdatapoints | 1 | wh_countdatapoints_idx4 | 1 | tmethid | A | 601 | (NULL) | (NULL) | YES | BTREE |
MySQL Data Definition Script:
CREATE TABLE wh_countdatapoints ( variablename varchar(255), valtype varchar(2), traitid int, scaleid int, tmethid int, count_datasets int default 0, count_datapoints int default 0 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Sample data (for rice):
|}
Schema of factors
Important: The table structure for FACTORS varies for different crops. Its structure is defined by the set of factors (traitid-scaleid-tmethid combinations) used by all datasets within a crop database.
Naming convention for columns: TRAITID.SCALEID.TMETHID (for columns other than ounitid,studyid,represno)
Fields (for Rice as of Oct 2009)
Field Type Collation Null Key Default Extra Privileges Comment ------------- ------------ --------------- ------ ------ ------- ------ ------------------------------- ------- ounitid int(11) (NULL) NO MUL select,insert,update,references studyid int(11) (NULL) NO MUL select,insert,update,references represno int(11) (NULL) NO MUL select,insert,update,references 202.151.131 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 202.151.202 double (NULL) YES (NULL) select,insert,update,references 205.146.133 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 205.1536.852 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 205.1537.852 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.94.258 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.94.854 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.95.258 double (NULL) YES (NULL) select,insert,update,references 215.96.258 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.221.258 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1522.847 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1523.847 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1524.847 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1529.0 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1529.847 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1530.0 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1530.847 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1531.0 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 215.1531.847 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 217.98.18 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 217.99.18 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 217.672.849 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 217.1533.0 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 217.1533.849 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 218.224.225 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 218.1521.846 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 218.1528.0 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 218.1528.846 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 219.100.19 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 219.107.19 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 219.107.721 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 219.118.717 int(11) (NULL) YES (NULL) select,insert,update,references 219.226.19 double (NULL) YES (NULL) select,insert,update,references 219.1493.717 double (NULL) YES (NULL) select,insert,update,references 219.1509.848 int(11) (NULL) YES (NULL) select,insert,update,references 219.1525.721 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 219.1532.848 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 220.101.20 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 229.173.179 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 229.173.857 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.91.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.91.495 double (NULL) YES (NULL) select,insert,update,references 251.91.656 double (NULL) YES (NULL) select,insert,update,references 251.91.856 double (NULL) YES (NULL) select,insert,update,references 251.92.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.93.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.97.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.103.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.103.91 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.104.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.104.92 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.115.17 int(11) (NULL) YES (NULL) select,insert,update,references 251.135.17 double (NULL) YES (NULL) select,insert,update,references 251.147.0 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.147.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.237.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.672.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.1533.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.1535.17 int(11) (NULL) YES (NULL) select,insert,update,references 251.1536.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.1537.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 251.1538.17 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 253.295.281 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 255.237.751 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 255.1123.751 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 313.666.822 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 313.1489.822 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 501.504.508 double (NULL) YES (NULL) select,insert,update,references 503.510.499 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 503.516.497 double (NULL) YES (NULL) select,insert,update,references 1549.659.657 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1550.660.658 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1552.1152.668 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1588.93.797 int(11) (NULL) YES (NULL) select,insert,update,references 1588.1037.797 double (NULL) YES (NULL) select,insert,update,references 1588.1312.797 double (NULL) YES (NULL) select,insert,update,references 1588.1480.797 int(11) (NULL) YES (NULL) select,insert,update,references 1624.1527.850 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1624.1534.712 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1624.1534.850 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1625.1544.855 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references
| Indexes |
| Table | Non unique | Key name | Seq in index | Column name | Collation | Cardinality | Sub part | Packed | Null | Index type | Comment |
| factors | 1 | factors_idx_01 | 1 | ounitid | A | 1249622 | (NULL) | (NULL) | BTREE | ||
| factors | 1 | factors_idx_02 | 1 | studyid | A | 1249622 | (NULL) | (NULL) | BTREE | ||
| factors | 1 | factors_idx_03 | 1 | represno | A | 1249622 | (NULL) | (NULL) | BTREE |
Sample data (for rice, REPRESNO = 2 / Dataset No. 2):
Schema of fieldsetup
Structural changes
- As of January 2009 (on database iris_myisam)
- Length of columns FIELDNAME, FIELDTEXT, TABLEALIAS increased to 255.
- Column TMETHID added (to handle the trait-scale-method definitions in ICIS)
- As of September 2009 (on database iris_myisam_20090904, iris_myisam_20091006)
- Unused columns deleted
- Four new columns added for storing separately the trait name, scale name, tmethodname and colname (FNAME if factor, VNAME if variate)
Data/content changes:
| Value of "GROUPOFTRAITS" column in FIELDSETUP table | Column in FIELDSETUP table | Original format/content | Modified format/content (as of March 27, 2009) | Modified format/content (as of July 17, 2009) | Modified format/content (as of September 9, 2009) | Modified format/content (as of October 29, 2009) |
|---|---|---|---|---|---|---|
| "Traits" (changed to "Variates" as of Oct 29, 2009) | fieldname | "dvalue" | TRAITNAME_SCALENAME_TMETHODNAME | TRAITNAME_SCALENAME_TMETHODNAME | TRAITNAME (SCALENAME) TMETHODNAME [VARIATENAME] | TRAITNAME (SCALENAME) TMETHODNAME ([VARIATENAME] removed) |
| fieldtext | TRAITNAME_SCALENAME
(then truncated to 50 characters max only) | TRAITNAME_SCALENAME_METHODNAME.VARIATEID
(where VARIATEID = cast(variatid as char)) --> no truncation | VARIATENAME (variate.vname) |
TRAITID.SCALEID.TMETHID_C or TRAITID.SCALEID.TMETHID_N
|
TRAITID.SCALEID.TMETHID ("_C", "_N" removed)
| |
| fieldid | (autonumber)
1 to etc...unique, sequential | unique VARIATID (variatid from ICIS "variate" table) | unique VARIATID (variatid from ICIS "variate" table) | unique VARIATID
(variatid from ICIS "variate" table) | first-occurence VARIATID
(only one variatid from ICIS "variate" table..previously all variatids were stored) | |
| tablealias |
(then truncated to 50 characters max only) |
--> no truncation |
--> no truncation |
"Variates" (referring to new warehouse table "Variates") |
"Variates" (referring to new warehouse table "Variates") | |
| "Factors" | fieldtext | FACTORNAME | FACTORNAME.LABELID
(where LABELID = cast(labelid as char)) | FACTORNAME_C (if factor.ltype = "C")
/ FACTORNAME_N (if factor.ltype = "N") | TRAITID.SCALEID.TMETHID_C
or TRAITID.SCALEID.TMETHID_N |
TRAITID.SCALEID.TMETHID ("_C", "_N" removed)
|
| fieldid | (autonumber)
1 to etc...unique, sequential | unique LABELID
(labelid from ICIS "factor" table) | unique LABELID
(labelid from ICIS "factor" table) |
unique LABELID (labelid from ICIS "factor" table) |
first-occurrence LABELID (only one labelid from ICIS "factor" table..previously all labelids were stored) |
| fieldsetup | |
| Fields (as of Sept 2009: on database iris_myisam_20090904, iris_myisam_20091006) |
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
| datatype | varchar(50) | utf8_general_ci | YES | (NULL) | select,insert,update,references | "C" or "N" | ||
| description | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | | ||
| fieldid | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | Either the first-occurrence LABELID (if factor) or VARIATID (if variate) given a traitid | ||
| fieldname | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | |||
| fieldtext | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | Link to "TRATID.SCALEID.TMETHID" column found in FACTORS,VARIATES tables | ||
| groupoftraits | varchar(50) | utf8_general_ci | YES | (NULL) | select,insert,update,references | "Factors" or "Variates" or "Study" | ||
| outputgroup | varchar(50) | utf8_general_ci | YES | (NULL) | select,insert,update,references | "Factors" or "Variates" or "Study" | ||
| outputorder | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | |||
| tablename | varchar(50) | utf8_general_ci | YES | (NULL) | select,insert,update,references | "Factors" or "Variates" or "Study" | ||
| tablealias | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | "Factors" or "Variates" or "Study" | ||
| traitid | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | TRAITID from FACTOR/VARIATE table | ||
| scaleid | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | SCALEID from FACTOR/VARIATE table | ||
| tmethid | int(11) | (NULL) | YES | (NULL) | select,insert,update,references | TMETHID from FACTOR/VARIATE table | ||
| traitname | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | TRNAME from TRAIT table | ||
| scalename | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | SCNAME from SCALE table | ||
| tmethodname | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | TMNAME from TMETHOD table | ||
| colname | varchar(255) | utf8_general_ci | YES | (NULL) | select,insert,update,references | Either the first-occurrence FNAME (if factor) or VNAME (if variate) |
Sample data (for rice):
Schema of variates
Important: The table structure for VARIATES differs for different crops. Its structure is defined by the set of factors (traitid-scaleid-tmethid combinations) used by all datasets within a crop database.
Naming convention for columns: TRAITID.SCALEID.TMETHID (for columns other than ounitid,studyid,represno)
Fields (as of Oct 29, 2009: in iris_myisam_20091006)
Field Type Collation Null Key Default Extra Privileges Comment ------------- ------------ --------------- ------ ------ ------- ------ ------------------------------- ------- ounitid int(11) (NULL) NO MUL select,insert,update,references studyid int(11) (NULL) NO MUL select,insert,update,references represno int(11) (NULL) NO MUL select,insert,update,references 222.641.639 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 401.112.100 double (NULL) YES (NULL) select,insert,update,references 405.113.248 double (NULL) YES (NULL) select,insert,update,references 405.113.249 double (NULL) YES (NULL) select,insert,update,references 405.113.827 double (NULL) YES (NULL) select,insert,update,references 406.115.103 double (NULL) YES (NULL) select,insert,update,references 408.111.99 double (NULL) YES (NULL) select,insert,update,references 501.500.508 double (NULL) YES (NULL) select,insert,update,references 501.501.508 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 501.503.508 double (NULL) YES (NULL) select,insert,update,references 501.1542.508 int(11) (NULL) YES (NULL) select,insert,update,references 501.1543.508 int(11) (NULL) YES (NULL) select,insert,update,references 1003.9.30 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1003.153.30 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1003.1535.851 int(11) (NULL) YES (NULL) select,insert,update,references 1004.7.28 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1004.622.620 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1005.8.29 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1005.8.203 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1005.1529.29 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1006.4.24 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1006.4.792 double (NULL) YES (NULL) select,insert,update,references 1006.613.610 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1007.1.21 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1008.6.27 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1008.618.615 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1009.2.25 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1009.133.25 double (NULL) YES (NULL) select,insert,update,references 1009.645.643 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1010.5.26 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1012.3.23 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1012.625.623 double (NULL) YES (NULL) select,insert,update,references 1013.70.22 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1013.70.706 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1013.1478.793 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1014.643.642 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1015.627.625 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1019.635.640 double (NULL) YES (NULL) select,insert,update,references 1019.642.640 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1019.642.641 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1021.609.606 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1028.195.200 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1051.638.636 double (NULL) YES (NULL) select,insert,update,references 1051.1540.36 double (NULL) YES (NULL) select,insert,update,references 1052.637.635 double (NULL) YES (NULL) select,insert,update,references 1053.1539.853 double (NULL) YES (NULL) select,insert,update,references 1057.150.163 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1059.630.628 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1062.633.632 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1065.639.637 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1066.617.614 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1071.626.624 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1073.269.262 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1073.651.648 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1075.632.631 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1076.631.629 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1077.650.647 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1078.656.653 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1079.646.644 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1080.649.646 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1082.621.619 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1085.655.652 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1086.657.654 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1086.658.655 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1087.608.605 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1088.628.626 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1088.629.627 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1090.611.608 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1091.610.607 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1101.163.334 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1101.163.335 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1101.164.44 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1101.164.119 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1102.37.52 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1104.21.157 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1105.35.50 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1114.39.54 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1118.23.45 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1152.121.39 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1152.121.111 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1152.121.112 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1152.121.273 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1156.18.40 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1167.19.198 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1202.30.8 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1204.171.47 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1205.55.80 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1205.123.118 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1206.28.48 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1214.29.6 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1215.27.4 varchar(150) utf8_general_ci YES (NULL) select,insert,update,references 1251.11.33 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1253.16.38 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1255.13.35 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1256.12.34 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1257.89.108 double (NULL) YES (NULL) select,insert,update,references 1260.10.32 double (NULL) YES (NULL) select,insert,update,references 1261.90.109 double (NULL) YES (NULL) select,insert,update,references 1262.640.638 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1264.636.634 double (NULL) YES (NULL) select,insert,update,references 1266.615.612 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1538.607.604 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1539.612.609 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1540.619.616 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1542.623.621 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1543.624.622 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1544.634.633 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1545.648.645 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1546.652.649 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1547.653.650 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references 1548.654.651 varchar(255) utf8_general_ci YES (NULL) select,insert,update,references
| Indexes |
| Table | Non unique | Key name | Seq in index | Column name | Collation | Cardinality | Sub part | Packed | Null | Index type | Comment |
| variates | 1 | variates_idx_01 | 1 | ounitid | A | 1249622 | (NULL) | (NULL) | BTREE | ||
| variates | 1 | variates_idx_02 | 1 | studyid | A | 1249622 | (NULL) | (NULL) | BTREE | ||
| variates | 1 | variates_idx_03 | 1 | represno | A | 1249622 | (NULL) | (NULL) | BTREE |
Sample data (for rice): 
Creation: When are Data Warehouses tables generated?
AFTER conversion of any ICIS database from MS Access to MySQL
See MS Access to MySQL conversion of ICIS databases
Updating: When and how should the Data Warehouses be updated?
- (from manacleto Aug 19, 2009) Right now, there is no clear procedure. A script is needed to add only the new entries to the data warehouses. Right now, you will have to generate them all again everytime an update(probably new conversion task) is made on the ICIS mysql databases.
Implementation Details: Data Warehouse tables creation
The script can be found in Cropforge project named “icisjavatools”. The script may be viewed on the browser, the link is:
http://cropforge.org/plugins/scmsvn/viewcvs.php/projects/ICISDatabaseTools/src/sql/mysql/warehouse_tables/?root=icisjavatools
Example of commands made to create the warehouse tables:
mysql> use iris_myisam;
mysql> source create_icis_dms_warehouse.sql;
mysql> call activate_icis_dms_warehousing('iris_myisam');
How long does it take to generate data warehouse tables for ICIS databases?
(Castor) On Castor server with the following specs: Server OS: Linux Debian. Hardware specs: 8 processors; Intel(R) Xeon(R) CPU; E5405 @ 2.00GHz 24 GB Memory
(CRIL4) On CRIL4 server with the following specs: Server OS: Linux Debian. Hardware specs: Pentium D 2.8Ghz, 2GB Memory, 500GB HD
IRIS 7 hours 41 mins 54.28 secs [for wh_columns, wh_data, wh_datasets, wh_countdatapoints] (on CRIL4) --As of Oct 20, 2009
IWIS 14 hours 25 min 34.73 sec + approx 4 hrs for factors and fieldsetup (on Castor)--As of Aug 19, 2009
IMIS
Testing of script for data warehouse tables creation
How is an ICIS database prepared/updated?
I. Convert from MS Access to MySQL database (on local machine/PC)
http://cropwiki.irri.org/icis/index.php/MS_Access_to_MySQL_conversion_of_ICIS_databases
II. Create a mysqldump file of your local Mysql database
mysql> mysqldump -usomeusername -p --default-character-set=utf8 iris > iris_yyyymmdd.sql
This local database should NOT have any indices at this stage.
III. Transfer the mysqldump file to the "database preparation server"
For Castor server, the "preparation server" is CRIL4.
IV. MySQL Database Setup (on database preparation server):
- Notes:
- 1. Use MyISAM as the storage engine (specified in database table definitions: GMS_TABLES.sql, DMS_TABLES.sql, GEMS_TABLES.sql).
- 2. Use utf8 as the default character set for the Mysql database (also specified in database table definitions: GMS_TABLES.sql, DMS_TABLES.sql, GEMS_TABLES.sql).
- 3. Increase the value of max_connections Mysql system variable (in my.cnf) to greater than 100 (100 is the default).
The examples shown below are for using the MySQL command prompt via SSH.
1. Create empty databases
1. Login remotely (via SSH) to Mysql on the preparation server. Example:
someuser@cril4:~$ mysql -usomeusername -p
2. Create empty databases. Example:
mysql> CREATE DATABASE iris_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci; mysql> CREATE DATABASE imis_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci; mysql> CREATE DATABASE iwis3_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;
where: iris_myisam_yyyymmdd is the database for RICE data, imis_myisam_yyyymmdd for MAIZE data, and iwis3_myisam_yyyymmdd for WHEAT data
2. Define table structure and populate ICIS Core tables with data
"Core" = included in the standard ICIS schema (used by standalone applications).
Rice data
mysql> use iris_myisam_yyyymmdd; mysql> source iris_myisam_yyyymmdd.sql;
Wheat data
mysql> use iwis3_myisam_yyyymmdd; mysql> source iwis3_myisam_yyyymmdd.sql;
Maize data
mysql> use imis_myisam_yyyymmdd; mysql> source imis_myisam_yyyymmdd.sql;
3. Define indices on ICIS Core tables
Index definition files for:
- Genealogy Management System (GMS) tables: GMS_INDICES.sql
- Data Management System (DMS) tables: DMS_INDICES.sql
- Gene Management System (GEMS) tables: GEMS_INDICES.sql
Usage:
mysql> use <name of database>; mysql> source GMS_INDICES.sql; mysql> source DMS_INDICES.sql; mysql> source GEMS_INDICES.sql;
4. Define UNIQUE indices on ICIS Core tables
Index definition files for:
- Genealogy Management System (GMS) tables: GMS_UNIQUEKEYS.sql
- Data Management System (DMS) tables: DMS_UNIQUEKEYS.sql
- Gene Management System (GEMS) tables: GEMS_UNIQUEKEYS.sql
Usage:
mysql> use <name of database>; mysql> source GMS_UNIQUEKEYS.sql; mysql> source DMS_UNIQUEKEYS.sql; mysql> source GEMS_UNIQUEKEYS.sql;
5. Set the Scale Type (SCTYPE) in the SCALE table
Script for setting the Scale Type (continuous [C] /discrete [D]) is here.
Usage (as of Sept 8, 2009) :
mysql> use <name of database>; mysql> source set_scale_sctype.sql; mysql> CALL set_scale_sctype();
6. Create and populate warehouse tables (based on ICIS Core tables)
GMS: wh_create_children.sql Download [Script last modified June 24, 2009]
(wh_der_children, wh_man_children)
Usage:
mysql> use <name of database>; mysql> source wh_create_children.sql;
DMS : create_icis_dms_warehouse.sql Download [Script last modified: Nov 9, 2009]
(wh_datasets, wh_columns, wh_data, wh_countdatapoints)
Usage:
mysql> use <name of database>;
mysql> source create_icis_dms_warehouse.sql;
mysql> CALL activate_icis_dms_warehousing('name of database');
DMS-Cropfinder (cross-study): create_cropfinder_warehouse.sql Download [Script last modified: Nov 9, 2009]
(fieldsetup, factors, variates)
Usage (as of Nov 9, 2009):
mysql> use <name of database>;
mysql> source create_cropfinder_warehouse.sql;
mysql> CALL activate_cropfinder_warehousing('name of database');
(Threshold removed as of Nov 9, 2009)
V. Create mysqldump file of database on preparation server then transfer to main server
Rice data
someuser@cril4:~$ mysqldump -uroot -p --disable-keys iris_myisam_yyyymmdd | gzip -9 > iris_myisam_yyyymmdd.sql.gz
Wheat data
someuser@cril4:~$ mysqldump -uroot -p --disable-keys iwis3_myisam_yyyymmdd | gzip -9 > iwis3_myisam_yyyymmdd.sql.gz
Maize data
someuser@cril4:~$ mysqldump -uroot -p --disable-keys imis_myisam_yyyymmdd | gzip -9 > imis_myisam_yyyymmdd.sql.gz
When transferring mysqldump files to Castor server:
- Rice: Store compressed mysqldump files in /databases/ICIS_mysqldump/IRIS/iris_myisam_yyyymmdd/
- Wheat: Store compressed mysqldump files in /databases/ICIS_mysqldump/IWIS3/iwis3_myisam_yyyymmdd/
- Maize: Store compressed mysqldump files in /databases/ICIS_mysqldump/IMIS/imis_myisam_yyyymmdd/
VI. MySQL Database Setup (on main server (e.g. Castor)):
- Notes:
- 1. Use utf8 as the default character set for the Mysql database.
- 2. Increase the value of max_connections Mysql system variable (in my.cnf) to greater than 100 (100 is the default).
The examples shown below are for using the MySQL command prompt via SSH.
1. Create empty database
1. Login remotely (via SSH) to Mysql on the main server (eg. Castor). Example:
someuser@castor:~$ mysql -usomeusername -p
2. Create empty databases. Example:
mysql> CREATE DATABASE iris_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci; mysql> CREATE DATABASE imis_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci; mysql> CREATE DATABASE iwis3_myisam_yyyymmdd CHARACTER SET utf8 COLLATE utf8_general_ci;
where: iris_myisam_yyyymmdd is the database for RICE data, imis_myisam_yyyymmdd for MAIZE data, and iwis3_myisam_yyyymmdd for WHEAT data
2. Restore the database
Use the mysqldump file created on the database preparation server.
Rice data
someuser@castor:~$ time gunzip < iris_myisam_yyyymmdd.sql.gz | mysql -uroot -p iris_myisam_yyyymmdd;
Wheat data
someuser@castor:~$ time gunzip < iwis3_myisam_yyyymmdd.sql.gz | mysql -uroot -p iwis3_myisam_yyyymmdd;
Maize data
someuser@castor:~$ time gunzip < imis_myisam_yyyymmdd.sql.gz | mysql -uroot -p imis_myisam_yyyymmdd;
3. Setup user privileges
(Done by admin/root user only) Please contact Ching (m.habito@cgiar.org) or Jay (f.consolacion@cgiar.org) for assistance.
- 1. Create a MySQL user called datasourceuser
mysql> CREATE USER 'datasourceuser'@'%' identified by 'somepassword';
2. Grant privileges to user datasourceuser:
mysql> GRANT SELECT on iris_myisam_yyyymmdd.* TO 'datasourceuser'@'%' IDENTIFIED BY 'somepassword'; mysql> GRANT SELECT on iwis3_myisam_yyyymmdd.* TO 'datasourceuser'@'%' IDENTIFIED BY 'somepassword'; mysql> GRANT SELECT on imis_myisam_yyyymmdd.* TO 'datasourceuser'@'%' IDENTIFIED BY 'somepassword';
