Forming a GCP Germplasm object with data from ICIS

This document describes how a Germplasm object is filled with data from ICIS by the IcisLocalDataSource.  For each attribute of the Germplasm object, there is a corresponding column from a table or query from which the data is retrieved.  Before reading this document, you must have at least a basic knowledge of the GCP Domain model and its Java implementation.  For more information on the Germplasm model, read this page.  For the Java API of the Germplasm object, see this page.  You will understand this document better if you are familiar with the concepts discussed on the pages cited.

A number of queries are involved in getting the data for the various attributes of the Germplasm object.  Note that not all of the attributes are filled with data, only those that are needed and are currently provided by the IcisLocalDataSource.

Getting the unique indentifier, name, acquisition date, and crop name:

• The query:  SELECT g.gid, n2.nval, g.gdate FROM germplsm g LEFT JOIN names as n2 on g.gid = n2.gid and n2.nstat = 1 WHERE g.grplce = 0
  
• unique identifier = g.gid (converted to a String)
  
• name = n2.nval
  
• Acquisition date is a TimeValue object formed with the data from g.gdate.  The value from g.gdate is converetd into a String which follows the ISO8601 standard in representing dates.  The date can be retrieved by calling TimeValue.getISO8601().
  
• The value for Crop name is an Identifier object which has its name attirbute equal to either of this three values: "Rice", "Wheat", "Maize".  The value is taken from a property of the IcisLocalDataSource set by the user.
  

Getting data for Germplasm relationships:

• Retrieved by calling Germplasm.getRelationships(), the method gives back a number of EntityRelationship objects.<span class="SpellE" /><span class="SpellE" />  The number depends on how the particular germplasm was created.
  
• All Germplasm objects have 2 EntityRelationship object containing their children.  One object for derivative children and one object maintenance children.
  
• The other EntityRelationship objects are for the parents of the Germplasm:
  

                - if the Germplasm was created with a derivative method, there is an EntityRelationship object for the source and another for the group

                - if the Germplasm was created with a generative method and has one parent, there is one EntityRelationship containing the female parent

                - if the Germplasm was created with a generative method and has two parents, there is one EntityRelationship containing the female parent and another one for the male

                     parent

                - if the Germplasm was created with a generative method and has more than two parents, there is one EntityRelationship containing the female parent, another on for the

                     male parent, and another one for the rest of the other parents

• The contents of the EntityRelationship object can be determined by getting its type ( EntitiyRelationship.getType() ), which is a SimpleOntologyTerm object with the following possible ids and names:
  

                - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.PARENTS:1  name: parents

                - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010:0000380:1     name: pedigree source

                - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.0000381:1     name: pedigree group

                - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.0000053:1     name: female parent

                - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.0000054:1     name: male parent

                - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.0000382:1     name: derivative germplasm children

                - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.0000383:1     name: maintenance germplasm children

• The query for forming the EntityRelationship objects:

SELECT DISTINCT g.gid, g.gnpgs FROM germplsm g WHERE g.gid=?

                - g.gnpgs tells how the germplasm is created

                - if gnpgs = -1, then the germplasm was created by a derivative method

                - if gnpgs > 0, then the germplasm was created by a generative method, and gnpgs is the number of parents it has

• To get the Germplasm objects representing the entities contained in the EntityRelationship objects, you must call EntityRelationship.getObjects().  This method will return a List of Germplasm objects.  Below are the queries for getting the various Germplasm objects:
  

                 - for getting the female parent or source germplasm:

                 SELECT g.gpid1, n.nval FROM germplsm g LEFT JOIN names n ON g.gpid1 = n.gid AND n.nstat = 1 INNER JOIN germplsm p on p.gid = g.gpid1 WHERE p.grplce = 0

                 AND g.gid = ?

                 - for getting the male parent or group germplasm:

                 SELECT g.gpid2, n.nval FROM germplsm g LEFT JOIN names n ON g.gpid2 = n.gid AND n.nstat = 1 INNER JOIN germplsm p on p.gid = g.gpid2 WHERE p.grplce = 0

                 AND g.gid = ?

                 - for getting the other parents:

                 SELECT p.pid, n.nval FROM progntrs p LEFT JOIN names n on p.pid = n.gid AND n.nstat = 1 WHERE p.gid = ?

                 - for getting maintenance children:

                 SELECT g.gid, n.nval FROM germplsm g LEFT JOIN names n on g.gid = n.gid and n.nstat = 1 LEFT JOIN methods m on g.methn = m.mid WHERE m.mtype = 'MAN'

                AND (g.gpid2 = ? OR (g.gpid1 = ? AND g.gpid2 = 0)) AND g.grplce = 0

                 - for getting derivative children:

                 SELECT g.gid, n.nval, m.mtype FROM germplsm g LEFT JOIN names n on g.gid = n.gid and n.nstat = 1 LEFT JOIN methods m on g.methn = m.mid WHERE g.gnpgs < 0

                 AND (g.gpid2 = ? OR (g.gpid1 = ? AND g.gpid2 = 0)) AND g.grplce = 0

Getting data for Germplasm Annotations:

• retrieved by calling Germplasm.getAnnotations()

• query used:
  

SELECT DISTINCT atb.aid, g.gid, atb.atype, m.mname, m.mtype FROM germplsm g LEFT JOIN atributs atb ON g.gid = atb.gid LEFT JOIN methods m ON g.methn = m.mid WHERE g.gid = ?

• data retrieved from query are used to create SimpleFeature objects (for the name and type of the method used to create the germplasm) and Feature objects (for other attributes associated with the germplasm) contained in the List returned by the method.
• each atb.aid is used as the unique identifier for Feature objects that are formed.  These objects contain more data in their values attribute.  Retrieved by calling  Feature.getValues().  Only those with atb.atype != 999 are used to form a Feature object.
• For the Feature objects, calling Feature.getValues() will give you a List of four Value objects:
  

                     - this query will be executed: SELECT u.fcode, u.fname, a.aval, a.adate FROM udflds u INNER JOIN atributs a ON u.fldno = a.atype WHERE a.gid = ? AND a.aid = ?                      - the four Value will contain (one for each object):

                           u.fcode – name of germplasm attribute

                           u.fname – description of germplasm attribute

                           a.aval – value of germplasm attribute

                           a.adate – date of germplasm attribute

                    - the data is in the value string attribute ( Value.getValueString() )of their Value object, the name of the Value object will tell which data it is

• for the SimpleFeature objects, calling SimpleFeature.getValueString() will return the method name or method type.  These data are taken from the mname and mtype columns of the methods table.
  
• The objects returned can be identified by their type (call getType() method) which is a SimpleOntologyTerm which can have the following unique identifiers and names:
  

               - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.METHODNAME:1       name: creation method name

               - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.METHODTYPE:1        name: creation method type

               - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.GATTRIBUTE:1           name: germplasm attribute

Getting data for Germplasm Locations:

• retrieved by calling Germplasm.getLocations(),<span /> the method will return a List of GermplasmLocation objects
• SQL queries used:
  

SELECT DISTINCT ctry.isotwo, ctry.isoabbr, loc.lname, loc.locid, ctry.cntryid, locd.dval FROM cntry ctry JOIN location loc ON loc.cntryid = ctry.cntryid JOIN locdes locd on loc.locid = locd.locid JOIN germplsm g on g.glocn = loc.locid WHERE g.gid = ?

               ctry.cntryid – used for the unique identifier attribute of GermplasmLocation containing the country of creation

               ctry.isoabbr – name of country of creation

               ctry.isotwo – country code, placed in the value string attribute of the GermplasmLocation object for country of creation

               loc.locid – used for the unique identifier of the GermplasmLocation object for the location of collecting site

               loc.lname – name of location

               loc.dval – description of location, placed in the value string attribute of the GermplasmLocation object for the location of collecting site

SELECT a.aval FROM atributs a JOIN udflds u ON a.atype = u.fldno AND u.fcode = 'ORI_COUN'  WHERE a.gid = ?

• you can check the type of the GermplasmLocation object by calling GermplasmLocation.getType(), a SimpleOntology Term will be returned with the following possible unique ids and names:

                 - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.COUNTRYCREATED:1         name: country of creation

                 - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.0000028:1                           name: location of collecting site

                 - id: urn:lsid:irri.org:IRIS.SimpleOntologyTerm:GCP_010.ORICOUNTRY:1                   name: ori country attribute