StudyMart Data Warehouse Tables Structure
From ICISWiki
Contents |
wh_data
wh_countdatapoints
Modified Nov 24, 2009: Now stores info for factors and variates (across all datasets). Table to be used for data quality checking as well.
Fields (as of Nov 24, 2009)
| 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 | ||
| count_datasets | int(11) | (NULL) | YES | | 0 | select,insert,update,references | ||
| count_core_datapoints | int(11) | (NULL) | YES | | 0 | select,insert,update,references | ||
| count_cf_datasets | int(11) | (NULL) | YES | | 0 | select,insert,update,references | ||
| count_cf_datapoints | int(11) | (NULL) | YES | | 0 | select,insert,update,references |
Indexes (as of Nov 24, 2009)
| 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 (as of Nov 24, 2009):
CREATE TABLE wh_countdatapoints ( variablename varchar(255), coltype varchar(2), -- added 20091123 mhabito: F or V valtype varchar(2), -- C or N traitid int, scaleid int, tmethid int, count_datasets int default 0, -- count datasets in wh_ warehouse tables count_datapoints int default 0, -- count datapoints in wh_ warehouse tables count_core_datasets int default 0, -- added 20091123 mhabito: count datasets in ICIS core tables count_core_datapoints int default 0, -- added 20091123 mhabito: count datapoints in ICIS core tables count_cf_datasets int default 0, -- added 20091123 mhabito: count datasets in Cropfinder warehouse tables count_cf_datapoints int default 0 -- added 20091123 mhabito: count datapoints in Cropfinder warehouse tables ) Engine=MyISAM DEFAULT CHARSET=utf8;
Sample data (for rice):
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):
Fieldsetup
| 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):
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): 
