GMS data validation using MySQL stored procedures (merged DB)

See table view merged_CHK01A. Set GNPGS = -1

See table view merged_CHK01B. Set GNPGS = 2 if with two parents, 3 if with three, etc

Updated Check:

DROP VIEW IF EXISTS merged_CHK01b;

CREATE VIEW merged_CHK01B AS

SELECT germplsm.gid, germplsm.methn, methods.mtype, germplsm.gnpgs, germplsm.grplce , COUNT(p.gid) pno

FROM germplsm INNER JOIN methods ON germplsm.methn = methods.mid

LEFT JOIN progntrs p ON p.gid=germplsm.gid

WHERE (((methods.mtype)="GEN") AND ((germplsm.gnpgs)<0))

GROUP BY germplsm.gid;

To Correct:

UPDATE germplsm g, `merged_chk01b` m SET g.gnpgs = m.pno + 2 WHERE g.gid = m.gid;

See table view merged_CHK01C. Change value of GPID1

1. Set gpid1 = 0 if gpid2 = 0;
2. Set gpid1 = gpid1 of gpid2 if gpid2 <> 0
   
3. set gpid1 = 0 if gpid1 of gpid2 = 0 or NULL gpid1

Updated Check:

DROP VIEW IF EXISTS merged_CHK01C;

CREATE VIEW merged_CHK01C AS

SELECT g.gid, g.methn,g.gpid2, m.mtype, g2.gpid1 gpid1_of_parent2 FROM germplsm g LEFT JOIN methods m ON g.methn = m.mid LEFT JOIN germplsm g2 ON g.gpid2 = g2.gid WHERE g.gid=g.gpid1;

Two Step Update Query:

1. UPDATE germplsm g, `merged_chk01c` m SET g.gpid1 = 0 WHERE g.gid = m.gid AND parent2 = 0;

2. UPDATE germplsm g, `merged_chk01c` m SET g.gpid1 = IF(((gpid2>0) AND (m.gpid1_of_parent2>0)),gpid1_of_parent2,parent2) WHERE g.gid = m.gid;

Check that all source germplasm (which are not also the group sources) are derivative

UPDATE germplsm g, merged_CHK10L2 m

SET g.gpid1 = m.change_gpid1_to

WHERE g.gid= m.gid;

UPDATE germplsm g, merged_CHK10L3 m

SET g.gpid2 = m.change_gpid2_to

WHERE g.gid= m.gid;

UPDATE progntrs p, merged_CHK10L4 m

SET p.pid = m.change_pid_to

WHERE p.pid = m.gid;

Exactly one name (neither more nor less) of a non-deleted GID (germplsm.grplce <> germplsm.gid) must have NSTAT=1 (preferred English name)

ResolveDuplicateLocationAbbr.sql will add "2" to the duplicate labbr

Note:  labbr field is only allowed to have 8 characters.  Adding another character (in this case, 2) will result to an error.  Check first if there are duplicates with 8 characters in length:

SELECT labbr,LENGTH(labbr), COUNT(labbr)
FROM location WHERE LENGTH(labbr)=8
GROUP BY labbr HAVING COUNT(labbr)>1;

For Multiple duplicates and to correct duplicates for all crops in a BMS instance, you may use: ResolveMultipleDuplicateLocationAbbrv.sql

Usage:

1. Download file
2. Execute the script in workbench database
3. call procedure by executing : Call resolveLocationMultipleDuplicates();

You can also manually edit this:

SELECT location.locid, lname, location.labbr FROM location INNER JOIN merged_chk01l3 ON location.`labbr` = merged_chk01l3.labbr2
ORDER BY location.labbr;

Check id Name type in NAMES table are present in UDFLDS table