Bulk Loading of Isozyme Data Using mySQL
From ICISWiki
SET UP THE INSTALLATION
- Start with a local GMS+DMS template
- Identify the installation in the INSTLN table and local users in the USERS table if necessary
- Import the GEMS tables. Here is the script for building the GEMS database template.
- Export the ISO table from MS Access to mySQL. ISO table can be exported in two ways: Using an ODBC connection and exporting the table in ASCII format. It can also be imported using a 3rd party software(downloadable)that can convert MS Access database to mySQL.
- USING ODBC connection
- Download and install an ODBC driver for mySQL
- Create a System Data Source to the mySQL server
- From Microsoft Access, export the ISO table into the newly created DSN
- ASCII format
- In Microsoft Access, export the ISO table into text format (can be tab delimited or comma delimited or any user-defined delimiter)
- In mySQL, create a table with the same definition as the ISO table
- insert the data from the ASCII file to the ISO table in mySQL
- USING ODBC connection
LOAD DATA LOCAL INFILE "</path/filename>" INTO TABLE ISO;
DATA PREPARATION FOR LOADING THE ALLELE EFFECT
To create the serial representation of the ISO table, multiple unions on the ISO table were made. The isoser_allele table will is the serialized representation of the allele effect.
create the isoser_allele table
- Drop isoser_allele table if the table already exist in the database
DROP TABLE IF EXISTS isoser_allele;
- create the table with the following fields:
| FIELD | FIELD TYPE |
|---|---|
| ORDERNO | orderno mediumint(11) not null auto_increment unique |
| OUNITID | long |
| SAMPLENO | Long |
| GID | Long |
| SOURCE | Text (255) |
| ORIGIN | long |
| NAME | Text (255) |
| ALLELE | Text (50) |
| MVID | Long |
| PDID | Long |
| FREQ | Double |
| SNLEVEL | Long |
| SNVALUE | Double |
| MVLEVEL | Long |
| MVVALUE | Double |
CREATE TABLE isoser_allele (orderno mediumint(11) not null auto_increment unique, ounitid int (11),sampleno double,gid int (11),source text (255),origin int (11), name text (255),allele text (50),mvid int (11),pdid int (11),freq double,snlevel int (11),snvalue double,mvlevel int (11),mvvalue double, KEY mvidx (mvid), KEY samplex sampleno)) ;
Populate the ISOSER_ALLELE table
395528 records will be inserted into the isoser_allele in 326922ms
INSERT INTO isoser_allele ( SAMPLENO, GID, NAME, ORIGIN, SOURCE, ALLELE, FREQ ) SELECT temp.order_merge, temp.GID, temp.NAME, temp.ORIGIN, temp.SOURCE, temp.ALLELE, temp.FREQ FROM (SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_0" AS allele, acp1_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_1" AS allele,acp1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_2" AS allele,acp1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "acp1_3" AS allele,acp1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_0" AS allele,adh1_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_1" AS allele,adh1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_2" AS allele, adh1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "adh1_3" AS allele, adh1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_0" AS allele,amp1_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_1" AS allele,amp1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_2" AS llele,amp1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_3" AS allele,amp1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_4" AS allele,amp1_4 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_5" AS allele,amp1_5 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp1_6" AS allele,amp1_6 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_0" AS allele,amp2_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_1" AS allele,amp2_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_2" AS allele,amp2_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_3" AS allele,amp2_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp2_4" AS allele,amp2_4 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_0" AS allele,amp3_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_1" AS allele,amp3_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_2" AS allele,amp3_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_3" AS allele,amp3_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_4" AS allele,amp3_4 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_5" AS allele,amp3_5 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp3_6" AS allele,amp3_6 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_0" AS allele,amp4_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_1" AS allele,amp4_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_2" AS allele, amp4_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_3" AS allele,amp4_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_4" AS allele,amp4_4 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "amp4_5" AS allele,amp4_5 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_0" AS allele,cat1_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_1" AS allele,cat1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_2" AS allele,cat1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "cat1_3" AS allele,cat1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "enP1_0" AS allele,enP1_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "enP1_1" AS allele,enP1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "enP1_2" AS allele,enP1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est1_0" AS allele,est1_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est1_1" AS allele,est1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est1_2" AS allele,est1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est2_0" AS allele,est2_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est2_1" AS allele,est2_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est2_2" AS allele,est2_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est5_0" AS allele,est5_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est5_1" AS allele,est5_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est5_2" AS allele,est5_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est9_0" AS allele,est9_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est9_1" AS allele,est9_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "est9_2" AS allele,est9_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got1_1" AS allele,got1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got1_2" AS allele,got1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got1_3" AS allele,got1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got3_1" AS allele,got3_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got3_2" AS allele,got3_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "got3_3" AS allele,got3_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "icd1_1" AS allele,icd1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "icd1_2" AS allele,icd1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "icd1_3" AS allele,icd1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "mal1_1" AS allele,mal1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "mal1_2" AS allele,mal1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd1_1" AS allele,pgd1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd1_2" AS allele,pgd1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd1_3" AS allele,pgd1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd2_1" AS allele,pgd2_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgd2_2" AS allele,pgd2_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_0" AS allele,pgi1_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_1" AS allele,pgi1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_2" AS allele,pgi1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_3" AS allele,pgi1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi1_4" AS allele,pgi1_4 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_0" AS allele,pgi2_0 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_1" AS allele,pgi2_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_2" AS allele,pgi2_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_3" AS allele,pgi2_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_4" AS allele,pgi2_4 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pgi2_5" AS allele,pgi2_5 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pox5_1" AS allele,pox5_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "pox5_2" AS allele,pox5_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_1" AS allele, sdh1_1 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_2" AS allele,sdh1_2 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_3" AS allele,sdh1_3 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_4" AS allele,sdh1_4 AS freq FROM iso UNION ALL SELECT order_merge,GID,varname as NAME,ORIGIN,supplier as SOURCE, "sdh1_5" AS allele,sdh1_5 AS freq FROM iso ) AS temp WHERE (((temp.ORIGIN) Is Not Null) AND ((temp.FREQ)=1));,
Make sure all the distinct loci are identified in the gems_locname
- Create iso_mvnames table. The iso_mvnames will contain all the distinct allele names from the isoser_allele table.
DROP TABLE IF EXISTS iso_mvnames;
CREATE TABLE iso_mvnames (mvid mediumint(11) not null auto_increment unique,levelno int (11),mvname text (50));
- Append distinct alleles in the isoser_allele table. 85 distinct alleles will be appended to the iso_mvnames table.
INSERT INTO iso_mvnames ( mvname ) SELECT DISTINCT allele AS expr1 FROM isoser_allele ORDER BY allele;
- Create ISO_LOCUS table. The iso_locus table will contain all the distinct locus in the isoser_allele table.
DROP TABLE IF EXISTS iso_locus; CREATE TABLE iso_locus ( locusid mediumint(11) not null auto_increment unique, lname text (255),ltype text (50));
- Append distinct locus to the ISO_LOCUS table. 22 distinct locus will be appended to the iso_locus table.
INSERT INTO iso_locus ( lname, ltype) SELECT DISTINCT mid(allele,1,instr(allele,'_')-1) AS Expr1, 'allele' AS Expr2 FROM isoser_allele;
- Append the locus in ISO_LOCUS table in gems_locus table.
INSERT INTO gems_locname ( locusid, lnval ) SELECT newlocus, lname
FROM (SELECT (SELECT if(max(gems_locname.locusid) is null, 0, max
(gems_locname.locusid)) as maxlocus FROM gems_locname) + locusid AS
newlocus,lname FROM iso_locus) AS x;
Complete other gems information as required.
Populate the tables gems_mvnames, gems_mv, gems_pd and gems_locus tables.
- Append MVNAMES from ISO_MVNAMES to gems_mvnames (should append 85 records to gems_mvnames)
INSERT INTO gems_mvnames ( mvid, mvnval) SELECT newmv, mvname
FROM (SELECT (SELECT if((max(gems_mvnames.mvid))is null,0,max(gems_mvnames.mvid))
as maxl FROM gems_mvnames)+mvid AS newmv,mvname
FROM iso_mvnames) AS X ;
- Append unobserved allele names to gems_mvnames table. Unobserved alleles can also be encoded directly into the gems_mvnames table. INSERT INTO gems_mvnames ( mvid, mvnval ) VALUES (86, 'pgi1_3');
- Append pdid and pdtype to gems_pd tables (22 rows,63 ms)
INSERT INTO gems_pd ( pdid, pdtype ) SELECT locusid, 'isozyme' FROM gems_locname;
- Append MVIDs to gems_mv table (86 rows, 32ms)
INSERT INTO gems_mv ( mvid, mvtype, pdid ) SELECT gems_mvnames.mvid, iso_locus.LTYPE, iso_locus.LOCUSID FROM iso_locus, gems_mvnames WHERE iso_locus.lname =mid(gems_mvnames.mvnval,1, instr(gems_mvnames.mvnval,'_')-1);
- Append locus to gems_locus table
INSERT INTO gems_locus ( mvid, locusid, pdid ) SELECT gems_mvnames.mvid, gems_locname.locusid, gems_mv.pdid FROM gems_locname, gems_mv,gems_mvnames WHERE (gems_mv.mvid = gems_mvnames.mvid) AND (gems_locname.lnval=mid(gems_mvnames.mvnval,1, instr(gems_mvnames.mvnval,'_')-1));
Update MVID and PDID in the ISOSER_ALLELE table
- Update MVID field in the ISOSER_ALLELE table
UPDATE isoser_allele ,gems_mvnames SET isoser_Allele.mvid = gems_mvnames.mvid WHERE gems_mvnames.mvnval=isoser_allele.allele;
- Update the PDID field in the ISOSER_ALLELE table
UPDATE isoser_allele,gems_mv SET isoser_allele.pdid = gems_mv.pdid WHERE gems_mv.mvid = isoser_allele.mvid ;
Look for putative heterozygotes and change its frequency to 0.5.
UPDATE isoser_allele as a,(SELECT b.sampleno AS sampleno, b.pdid, Count(b.sampleno)
AS sncount FROM isoser_allele AS b GROUP BY b.sampleno, b.pdid
HAVING (((Count(b.sampleno))>1)))as c
SET a.freq = 0.5 where c.sampleno = a.sampleno AND c.sncount =2 and C.PDID = a.PDID;
SET UP THE STUDY
- Set up the study in the study table
| STUDYID | SNAME | PMKEY | TITLE | OBJECTIV | INVESTID | STYPE | SDATE | EDATE |
|---|---|---|---|---|---|---|---|---|
| -1 | ISOZYME04 | 0 | ISOZYME04 | - | 0 | E | 0 | 0 |
INSERT INTO study ( studyid, sname, pmkey, title, objectiv, investid, stype, sdate,edate) VALUES (-1, 'isozyme04', 0, 'isozyme04', '-', 0, 'E', 0, 0);
- Define the study factor in the FACTOR table (look up the appropriate TRAITID SCALEID and TMETHID in the central TRAIT , SCALE and TMETHOD tables) LTYPE is C for character since the study name is used as the study level.
| LABELID | FACTORID | FNAME | STUDYID | TRAITID | SCALEID | TMETHID | LTYPE |
|---|---|---|---|---|---|---|---|
| -1 | -1 | STUDY | -1 | 201 | 134 | 123 | C |
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid,
tmethid,ltype )VALUES (-1 , -1, 'STUDY' , -1 , 201 , 134 , 123, 'C');
- Define the study level in the LEVEL_C table:
| LABELID | FACTORID | LEVELNO | LVALUE |
|---|---|---|---|
| -1 | -1 | -1 | ISOZYME04 |
INSERT INTO LEVEL_C ( labelid, factorid, levelno, lvalue )
VALUES (-1 , -1 , -1 , 'ISOZYME04');
- Define the study effect in the EFFECT table
| REPRESNO | FACTORID | EFFECTID |
|---|---|---|
| -1 | -1 | -1 |
INSERT INTO effect ( represno, factorid, effectid )
VALUES (-1 , -1, -1);
- Define the observation units for the study effect in the OINDEX table. There is only one since the factor study has only one level and effect -1 is only indexed by the study factor.
INSERT INTO oindex ( ounitid, factorid, levelno, represno )
VALUES (-1, -1, -1, -1);
There are no variates measured at the study level, and there are no labels of the Study factor so we are finished with defining the study.
LOADING THE SAMPLE ALLELE
- Define the FREQ variate in the VARIATE table. look up the TRAITID, SCALEID and TMETHID in the central tables or assign new ones with negative IDs in the local TRAIT, SCALE and TMETHOD tables.
| VARIATID | VNAME | STUDYID | VTYPE | TRAITID | SCALEID | TMETHID | DTYPE |
|---|---|---|---|---|---|---|---|
| -1 | FREQ | -1 | MV | 501 | 508 | 508 | N |
INSERT INTO variate ( variatid, vname, studyid, vtype, traitid, scaleid, tmethid, dtype )VALUES (-1, 'FREQ', -1, 'MV', 501, 508, 508, 'N');
- Define the Sample No factor in the factor table and add GID as a label. (Note SampleNo:GID is Many:one) and the factor must have unique levels so Sample No is the factor.
| LABELID | FACTORID | FNAME | STUDYID | TRAITID | SCALEID | TMETHID | LTYPE |
|---|---|---|---|---|---|---|---|
| -3 | -2 | GID | -1 | 251 | 91 | 17 | N |
| -2 | -2 | SAMPLENO | -1 | 212 | 219 | 17 | N |
| -4 | -2 | NAME | -1 | 251 | 92 | 17 | C |
| -5 | -2 | ORIGIN | -1 | 215 | 95 | 17 | C |
| -6 | -2 | SOURCE | -1 | 251 | 104 | 17 | C |
SAMPLENO--
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype ) VALUES (-2, -2, 'SAMPLENO' , -1, 212, 219, 17, 'N');
GID--
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype ) VALUES (-3, -2, 'GID' , -1, 251, 91, 17, 'N');
NAME--
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype )
VALUES (-4, -2, 'NAME' , -1, 251, 92, 17, 'C'); ORIGIN--
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype ) VALUES (-5, -2, 'ORIGIN' , -1, 215, 95, 17, 'N');
SOURCE
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype )
VALUES (-6, -2, 'SOURCE' , -1, 251, 104, 17, 'C');
Define the allele factor (MVID) in the FACTOR table and allele (MVNAME) label
- Define the allele factor (MVID) in the FACTOR table:
| LABELID | FACTORID | FNAME | STUDYID | TRAITID | SCALEID | TMETHID | LTYPE |
|---|---|---|---|---|---|---|---|
| -7 | -7 | MVID | -1 | 501 | 503 | 17 | N |
| -8 | -7 | ALLELE | -1 | 501 | 520 | 17 | C |
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype )
VALUES (-7, -7, 'MVID' , -1, 501, 503, 17, 'N');
MVNAME--
INSERT INTO factor ( labelid, factorid, fname, studyid, traitid, scaleid, tmethid, ltype )
VALUES (-8, -7, 'ALLELE' , -1, 501, 520, 17, 'C');
Define the Sample by Allele effect in the EFFECT table
- Define the Sample by Allele effect in the EFFECT table
| REPRESNO | FACTORID | EFFECTID |
|---|---|---|
| -2 | -1 | -2 |
| -2 | -2 | -2 |
| -2 | -7 | -2 |
STUDY
INSERT INTO effect ( represno, factorid, effectid ) VALUES (-2, -1, -2);
SAMPLENO
INSERT INTO effect ( represno, factorid, effectid ) VALUES (-2, -2, -2);
MVID
INSERT INTO effect ( represno, factorid, effectid ) VALUES (-2, -7, -2);
SET UP THE FACTOR LEVELS
Set up the SAMPLENO factor
- Create isoser_alabels with the following fields
DROP TABLE IF EXISTS iso_alabels; CREATE TABLE iso_alabels (ORDERNO mediumint(11) not null auto_increment unique, levelno int (11),sampleno double, gid int (11), ORIGIN int (11), SOURCE text (255),NAME text (255));
- Populate the isoser_alabels from the ISO table
INSERT INTO iso_alabels ( sampleno, gid, origin, source,name ) SELECT ISO.order_merge, ISO.GID, ISO.ORIGIN, ISO.supplier, ISO.varname FROM ISO;
- Get the minimum of LEVELNO in LEVEL_C and LEVEL_N tables AND Update LEVELNO in iso_alabels
UPDATE iso_alabels SET levelno = (SELECT Min(mina) AS minlevel
FROM (SELECT min(levelno) AS mina FROM level_n UNION SELECT min(levelno) AS mina FROM level_c ) AS c) - orderno;
- Append LABELS (SAMPLENO, GID, ORIGIN) in the LEVEL_N table
- Append SAMPLENO label to LEVEL_N table
INSERT INTO level_n ( labelid, factorid, levelno, lvalue ) SELECT -2 AS labelid, -2 AS factorid,levelno,sampleno FROM iso_alabels;
- Append GID label to LEVEL_N table
INSERT INTO level_n ( labelid, factorid, levelno, lvalue ) SELECT -3 AS labelid, -2 AS factorid,levelno,gid FROM iso_alabels;
- Append ORIGIN label to LEVEL_N table
INSERT INTO level_n ( labelid, factorid, levelno, lvalue ) SELECT -5 AS labelid, -2 AS factorid,levelno,origin FROM iso_alabels;
- Append NAME label to LEVEL_C table
INSERT INTO level_c ( labelid, factorid, levelno, lvalue ) SELECT -4 AS labelid, -2 AS factorid,levelno,name FROM iso_alabels;
- Append SOURCE label to LEVEL_C table
INSERT INTO level_c ( labelid, factorid, levelno, lvalue ) SELECT -6 AS labelid, -2 AS factorid,levelno,source FROM iso_alabels;
Set up the MVID factor
- Update the LEVELNO in the ISO_MVNAMES (contains the labels for MVID factor) table
UPDATE iso_mvnames
SET iso_mvnames.levelno = (SELECT Min(mina) AS minlevel
FROM (SELECT min(levelno) AS mina FROM level_n
UNION SELECT min(levelno) AS mina FROM level_c ) AS c) - mvid;
- Append MVID factor to LEVEL_N table
INSERT INTO level_n( labelid, factorid, levelno, lvalue ) SELECT -7 AS Expr1, -7 AS Expr2, iso_mvnames.levelno, iso_mvnames.mvid FROM iso_mvnames ;
- Append ALLELE label to LEVEL_C table
INSERT INTO level_n( labelid, factorid, levelno, lvalue ) SELECT -8 AS Expr1, -7 AS Expr2, iso_mvnames.levelno, iso_mvnames.mvname FROM iso_mvnames;
SET UP THE OBSERVATION UNITS FOR THE SAMPLE ALLELE
Update the ISOSER_ALLELE
- Update MVLEVEL field in the ISOSER_ALLELE table
UPDATE isoser_allele,level_n SET isoser_allele.mvlevel = level_n.levelno WHERE (isoser_allele.mvid = level_n.lvalue) AND (level_n.labelid=-7) ;
- UPDATE SNLEVEL IN ISOSER_ALLELE
UPDATE isoser_allele,level_n SET isoser_allele.snlevel = level_n.levelno WHERE ((isoser_allele.sampleno = level_n.lvalue ) AND (level_n.labelid=-2));
- Update MVVALUE field in the ISOSER_ALLELE table. Values in this column should be equal to MVID
UPDATE isoser_allele,level_n SET isoser_allele.mvvalue = level_n.lvalue WHERE (level_n.labelid=-7) AND (isoser_allele.mvid = level_n.lvalue);
- Update SNVALUE field in the ISOSER_ALLELE table. Values in this column should be equal to SAMPLENO
UPDATE isoser_allele ,level_n SET isoser_allele.snvalue = level_n.lvalue WHERE (level_n.labelid=-2) AND (isoser_allele.sampleno = level_n.lvalue) ;
Update OUNITID field in ISOSER_ALLELE table
- Get the minimum of OUNITID from the OINDEX table and update OUNITID field in the ISOSER_ALLELE table
UPDATE isoser_allele
SET isoser_allele.ounitid = (SELECT min(c.ounitid) as mino from oindex as c)
isoser_allele.orderno;
Append STUDY,SAMPLENO and MVID observation units to the OINDEX tables.
- Append observation units for the study factor
INSERT INTO oindex ( ounitid, factorid, levelno, represno ) SELECT isoser_allele.ounitid, -1 AS Expr3, -1 AS Expr2, -2 AS Expr1 FROM isoser_allele ;
- Append observation units for the SAMPLENO factor
INSERT INTO oindex ( ounitid, factorid, levelno, represno ) SELECT isoser_allele.ounitid, -2 AS Expr3, isoser_allele.snlevel, -2 AS Expr1 FROM isoser_allele;
- Append observation units for the MVID factor
INSERT INTO oindex ( ounitid, factorid, levelno, represno ) SELECT isoser_allele.ounitid, -7 AS Expr2, isoser_allele.mvlevel, -2 AS Expr1 FROM isoser_allele;
ADD THE FREQUENCY IN THE DATA TABLE
INSERT INTO data_n ( ounitid, dvalue, variatid ) SELECT isoser_allele.ounitid, isoser_allele.freq, -1 AS Expr1 FROM isoser_allele ;
LOADING THE SAMPLE BY LOCUS DATA
This follows similar steps to loading the sample by allele data but uses the sample by locus serial table ISOSER_LOCUS table.
