Access Keys:
Skip to content (Access Key - 0)

Middleware Use Cases

This page contains definitions of the use cases that will be implemented for the Middleware. The definitions are based on the ICIS DLL specifications posted in the following wiki pages:

TDM Windows Application Programming Interface 5.4
TDM Windows Application Programming Interface 5.5

The use cases are grouped according to increment dates as specified in the development workplan.

January 11, 2012
Name Description
Find Germplasm by Name
  • based on GMS_findName, GMS_findName2, GMS_findName3
  • Search the NAMES table given a search string.
  • Returns Germplasm records with names matching the search string.
  • Provide option for searching using the specified string, string with all spaces removed, string with name standardization applied, filter by status, type, location of name
Find Germplasm by Location Name
  • based on GMS_findGermplasm
  • Search for Germplasm records given a name of a location.
  • Returns Germplasm records with location of creation matching the location specified by the given name.
  • Provide option for using equal or like operation.
Find Germplasm by Method Name
  • based on GMS_findGermplasm
  • Search for Germplasm records given a name of a method of genesis for germplasms.
  • Returns Germplasm records which were created using the method identified by the given name.
  • Provide option for using equal or like operation.
Get Germplasm by GID
  • based on GMS_getGermplasmRecord and GMS_getGermplasm
  • Given a gid, return the corresponding Germplasm record
Get Germplasm with Preferred Name
  • based on GMS_getGermplasm2
  • Given a gid, return the corresponding Germplasm record with the Name record for its preferred name
Get Germplasm with Preferred Name and Abbreviation
  • based on GMS_getGermplasm3
  • Given a gid, return the corresponding Germplasm record with the Name record for its preferred name and a string containing the preferred abbreviated name for the germplasm
Get Name by Name ID
  • based on GMS_getNameRecord
  • Given the id of the name, return the corresponding Name record.
Get Names by GID
  • based on GMS_getName
  • Given the gid, return all Name records for that gid.
  • Provide the option to filter results by type and/or status of the name records.
Get Attributes by GID
  • based on GMS_getAttribute
  • Given the gid, return all Attribute records associated with that gid.
  • Provide the option to filter results by type of the attribute records.
Get Method by Method ID
  • based on GMS_getMethod
  • Given a method id, return the corresponding method record.
Get All Methods
  • based on GMS_getMethod
  • Returns all method records.
Get User Defined Field by ID
  • based on GMS_getUDField
  • Given an id, return the corresponding user defined field record.
Get Location by ID
  • based on GMS_getLocation
  • Given a location id, return the corresponding location record.
Get Bibliographic reference record by ID
  • based on GMS_getBibrefs
  • Given an id, return the corresponding bibliographic reference record.
February 1, 2012
Name Description
Find Descendants
  • based on GMS_findDescendant
  • Given a GID which identifies a parent germplasm, search for all its descendants. Search the germplsm.gpid1, germplsm.gpid2 and progntr.pid fields for matching gids.
  • Return Germplasm records which are descendants of the given germplasm. The progenitor number for the given gid with respect to the descendant is also returned.
Get Progenitor by GID and progenitor number
  • based on GMS_getProgenitorID
  • Given a gid and progenitor number, return the parent Germplasm record which corresponds to the progenitor number.
Generate Tree
  • based on GMS_generateTree
  • Given a GID and a pedigree tree level number, return a pedigree tree containing all generative progenitors of the given GID down to the specified level number. GIDs and preferred names of the Germplasm records on the tree should be included.
February 29, 2012
Name Description
Find Germplasm by Example
  • based on GMS_findGermplasm
  • Given a Germplasm record sample, search for matching Germplasm records.
  • Return Germplasm records with values in fields matching the specified values in the fields of the sample Germplasm record.
Get Derivative Neighborhood
  • based on GMS_getDerivativeNeighbor
  • Given a GID, the number of step backward within the generation history of the given GID, and the number of step forward within the generation history of the given GID, generate the neighborhood containing all Germplasm which are related to the given GID and were created using derivative or management methods.
Get Management Neighborhood
  • Given a GID, return all germplasms included in the management neighborhood of the germplasm identified by the given id.
Get Group Relatives
  • Given a GID, return all germplasms which are group relatives of the germplasm identified by the given id.
  • The germplasm identified by the GID must be created using derivative methods.
Get Germplasm Generation History
  • Given a GID, return a String containing the generation history of the germplasm identified by the given id.
Get Germplasm List by ID
  • based on GMS_getListName
  • Given a list id, return the corresponding record containing list information.
Get all Germplasm List
  • based on GMS_getListName
  • Return all list records in the database.
Find Germplasm List by Name
  • based on GMS_findListName
  • Given a name, find and return list records with name matching the given parameter.
Get Germplasm List by status
  • based on GMS_getListByStatus
  • Given a list status, return list records with matching status.
Get Germplasm List Data by List ID
  • based on GMS_getListData
  • Given a list id, return all list data records for the list identified by the id.
Get Germplasm List Data by List ID and GID
  • based on GMS_getListData
  • Given a list id and a gid, return the list data record specified by the given ids.
Get Germplasm List Data by List ID and entry id
  • based on GMS_getListData
  • Given a list id and a gid, return the list data record specified by the given ids.
Get Germplasm List Data by GID
  • based on GMS_findListData
  • Given a gid, return all list data records associated with the gid.
March 28, 2012
Name Description
Update Germplasm List
  • based on GMS_setListName
  • Given a list record which corresponds to an existing record in the database, update the database with changes from the given data.
Add Germplasm List
  • based on GMS_addListName
  • Given a list record, add it to the database.
  • The list record should have valid values for its fields.
Delete Germplasm List
  • based on GMS_deleteListName
  • Given a list id, delete the list record from the database and all associated data to that list.
Update Germplasm List Data
  • based on GMS_setListData
  • Given a list data record which corresponds to an existing record in the database, update the database with the changes in the given data.
Add Germplasm List Data
  • based on GMS_addListData
  • Given a list data record, add it to the database.
  • The list data record should have values for its fields.
Delete Germplasm List Data by List ID
  • based on GMS_deleteListData
  • Given a list id, delete all list data records under the list identified by the id.
Delete Germplasm List Data by List ID and Entry id
  • based on GMS_deleteListData
  • Given a list id and entry id, delete the list data record identified by the two ids.
Get Trait by id
  • based on DMS_getTrait
  • Given a trait id, return the trait record identified by the parameter.
Get All Traits
  • based on DMS_getTrait
  • Return all trait records
Get Scale by id
  • based on DMS_getScale
  • Given a scale id, return the scale record identified by the parameter.
Get All Scales
  • based on DMS_getScale
  • Return all scale records.
Get continuous scale range
  • based on DMS_getScaleCon
  • Given a scale id, return the start value and end value for the continuous scale identified by the parameter.
  • Query is done against the scalecon table.
Get discrete values for scale
  • based on DMS_getScaleDis
  • Given a scale id, return the discrete values associated with the scale identified by the parameter.
  • Query is done against the scaledis table.
Get description of discrete value given the value and scaleid
  • based on DMS_getScaleDis2
  • Given a scale id and a value, return the descriptions for that value for the scale identified by the id.
  • Query is done against the scaledis table.
Get Scales by Trait id
  • Given a trait id, returns all scale records associated with the trait identified by the given parameter.
Get tmethod record by id
  • based on DMS_getTmethod
  • Given a tmethod id, return the corresponding tmethod record identified by the id.
Get all tmethod records
  • based on DMS_getTmethod
  • Return all tmethod records.
Get tmethod records by Trait id
  • Given a trait id, return all trait method records associated with the trait identified by the given parameter.
Get Germplasms by combinations of trait, scale, trait method, and value
  • Given combinations of trait id, scale id, trait method id, and value, return all Germplasms which have been observed to exhibit the trait with the specified value in any study dataset.
April 25, 2012

GMS Write Functions

Name Description
Add Germplasm
  • based on GMS_addGermplasm
  • Given a Germplasm record with a Name record for its preferred name, store the data in the database.
  • The Germplasm record must have valid values for the following: method of creation, number of progenitors, first progenitor, second progenitor, location, date and reference.
  • The name record must have valid values for the following: type, status, location, date, and reference.
Add Name
  • based on GMS_addName
  • Given a Name record, store the data in the database.
  • The name record must have valid values for the following: name, gid, type, status, location, date, and reference.
Add Attribute
  • based on GMS_addAtrribute
  • Given an Attribute record, store the data in the database.
  • The attribute record must have valid values for the following: attribute value, gid, type, location, date, reference.
Update Germplasm
  • based on GMS_setGermplasm
  • Given a Germplasm record which corresponds to an existing record in the database, update the database with changes from the given data.
  • The germplasm record should contain valid values for all of its fields.
Update Progenitor
  • based on GMS_setProgenitorID
  • Given GID of a child germplasm, a GID of a parent germplasm and a progenitor number, update the database record of the child germplasm to set the appropriate GID of the parent at the specified progenitor number.
Update Preferred Name
  • based on GMS_setPreferredName
  • Given a string containing the new preferred name and GID, check if there is a name record for the given string and GID as there should be, set the current preferred name record's status to 0, set the matching record as preferred name
Update Preferred Abbreviation
  • based on GMS_setPreferredAbbr
  • Given a string containing the new preferred abbreviation and GID, check is there is a name record for the given string and GID as there should be, set the current preffered abbreviation name record's status to 0, set the matching record as preferred abbreviation.
Update Name
  • based on GMS_setName
  • Given a Name record which corresponds to an existing record in the database, update the database with changes from the given data.
    The Name record should contain valid values for all of its fields.
Update Attribute
  • based on GMS_setAttribute
  • Given an Attribute record which corresponds to an existing record in the database, update the database with changes from the given data.
  • The Attribute record should contain valid values for all of its fields.
Add Bibliographic reference record
  • based on GMS_addBibrefs
  • Given a bibliographic reference record, save it to the database.
  • The bibliographic reference record must have valid values for all its fields.
Add Location
  • based on GMS_addLocation
  • Given a location record, save it to the database.
  • The location record must have valid values for all of its fields.

IMS Functions

Name Description
Add Lot
  • based on IMS_addLot
  • Given a new lot record, add it to the database.
  • The entity reference by the lot should already be present in the database.
Add Transaction
  • based on IMS_addTransaction
  • Given a new transaction record for an existing lot, add the transaction record to the database.
Find Lots
  • based on IMS_findLotRecord
  • Given a combination of entity type, entity id, and location, retrieve all lot records which contains the given combination.
  • Accepted combinations include: entity type only, entity type and entity id, entity type and location, entity type and entity id and location.
Find Transaction by Lot ID
  • based on IMS_findTransactionRecord
  • Given a lot id, return all transaction records which involve the lot identified by the given id.
Get All Reserve Transactions
  • based on IMS_getTransactionReserve
  • Return all transaction records which involve reserving lots.
  • Provide option to limit results by the user who made the transactions.
Get All Deposit Transactions
  • based on IMS_getTransactionDeposit
  • Return all transaction records which involve depositing to lots.
  • Provide option to limit results by the user who made the transactions.
Get All Reserve Transactions by Requestor
  • based on IMS_getReserveByPerson
  • Return all transaction records, which involve reserving lots, that were created at the request of a given person.
Get All Deposit Transactions by Donor
  • based on IMS_getDepositByPerson
  • Return all transaction records, which involve depositing to lots, that were created because of a donation of a given person.
Get Lot Balance
  • based on IMS_getBalanceOfLot
  • Given a lot id, calculate and return the total quantity for the lot.
Update Lot Record
  • based on IMS_setLot
  • Given a lot record, update the corresponding entry in the database with changes from the given record.
Update Transaction Record
  • based on IMS_setTransaction
  • Given a transaction record, update the corresponding entry in the database with changes from the given record.
  • The lot can not be changed.
Generate report on all uncommitted transactions
  • based on IMS_reportAllTransaction
  • Return all transaction records which are marked as uncommitted.
  • Important information to include are commitment date, quantity of transaction, and the scale, location and comment about the lot specified in the transaction.
Generate report on all reserve transactions
  • based on IMS_reportMaterialDist
  • Return all the uncommitted transactions which involve withdrawing from lots.
  • Important information to include are commitment date, quantity of transaction, and the entity id, scale, location and comment about the lot specified in the transaction.
Generate report on all withdrawal transactions
  • based on IMS_reportByRequestor
  • Return all transactions which involve withdrawing from lots.
  • Important information to include are the person who requested the withdrawal, commitment date, quantity of transaction, and the entity id, scale, location and comment about the lot specified in the transaction.
Generate balance report on all lots
  • based on IMS_reportBalance
  • Return the sum of committed transactions, location and scale of all lots.
Generate report on empty lots
  • based on IMS_reportEmptyShelves
  • Return the entity id, location, scale, of lots which have a balance equal to zero.
  • The balance of a lot is the sum of all the committed transactions done on it.
Generate report on dormant lots
  • based on IMS_reportDormantEntries
  • Given a year, return the lot id, entity id, balance, location, and scale of all lots with non-zero balance stored on or earlier the given year.
Generate report on lots given a minimum amount
  • based on IMS_reportMinimumAmount
  • Given an amount, return the lot id, entity id, balance, location, and scale of all lots with balance less than the amount specified.
Generate report on lots given an entity
  • based on IMS_reportLotByEntity
  • Given an entity type, return the lot id, entity id, balance, location, and scale of all lots associated with the given type.
Generate report on lots given an entity type and entity id
  • based on IMS_reportLotByEntityGIDFrom, IMS_reportLotByEntityGIDRange
  • Given an entity type and entity id, return the lot id, entity id, balance, location, and scale of all lots associated with the given type and id.
  • Provide the option to specify a range of entity ids.
May 23, 2012
Name Description
Find Study by name
  • based on DMS_findStudy
  • Given a name, return all study records with name matching the parameter.
  • Provide options for using equal or like operator and for using wildcards on the name parameter.
Get Study By ID
  • based on DMS_getStudy
  • Given an id, return the study record identified by the parameter.
Get All Top Level Studies
  • Return all study records which do not have parent folders. These are the top level study records.
Get Studies By Parent Folder ID
  • Returns all study records which belong to the parent study record identified by the given id.
Get Factors by Study ID
  • Returns all Factor records which belong to the Study identified by the given id.
Get Variates by Study ID
  • Returns all Variate records which belong to the Study identified by the given id.
Get Effects by Study ID
  • Returns all the Effect records which belong to the Study identified by the given id.
Get Representations by Effect ID
  • Return all Representation records which belong to the Effect identified by the given id.
Get Factors by Representation ID
  • Return all Factor records which belong to the Representation identified by the given id.
Get Variates by Representation ID
  • Return all Variate records which are involved with the Representation identified by the given id.
Get Observation Unit Ids given Representation ID
  • Returns all distinct observation unit ids which is associated with the Representation
    identified by the given id.
Get Character Level Values given a list of Observation Unit Ids
  • Return the observation unit id, factor id, factor name, and level c value, given a list of
    observation unit ids.
  • Each observation unit id represents a row in a dataset.
  • The factor id and factor name identifies a column of the dataset, and the level c
    value should be placed below that column
Get Numeric Level Values given a list of Observation Unit Ids
  • Return the observation unit id, factor id, factor name, and level n value, given a list of
    observation unit ids.
  • Each observation unit id represents a row in a dataset.
  • The factor id and factor name identifies a column of the dataset, and the level c
    value should be placed below that column
Get Character Data Values given a list of Observation Unit Ids
  • Return the observation unit id, variate id, variate name, and data c value, given a list of
    observation unit ids.
  • Each observation unit id represents a row in a dataset.
  • The variate id and variate name identifies a column of the dataset, and the data c
    value should be placed below that column
Get Numeric Data Values given a list of Observation Unit Ids
  • Return the observation unit id, variate id, variate name, and data n value, given a list of
    observation unit ids.
  • Each observation unit id represents a row in a dataset.
  • The variate id and variate name identifies a column of the dataset, and the data n
    value should be placed below that column
June 29, 2012
Name Description
Get Representations by Study ID
  • Given a study id, retrieve all Representation records associated with the Study identified by the ID
Find Location by Name
  • Return the Location records with name matching the given parameter
  • Provide the option to use the EQUAL or LIKE operator
Retrieve all Locations
  • Returns all Location records
Get User by ID
  • Given an id, return the User record identified by it
Add Method
  • Given a valid Method record, save it into the database
Retrieve all Users
  • Returns all User records
Get Person by ID
  • Given an id, return the Person record identified by it
Retrieve all Persons
  • Returns all Person records
Add Person
  • Given a valid Person record, save it into the database.
Add User
  • Given a valid User record, save it into the database.
July 25, 2012

GDMS Functions

Name Description
Get Name IDs (NID) by GIDs
  • Search the acc_metadataset table by giving the list of gids
  • Returns Name IDs matching the gids.
Get Name by Name ID
  • based on GMS_getNameRecord
  • Given the id of the name, return the corresponding Name record.
Get Name records by name ids
  • Given a list of nids, return the Name records identified by the given ids.
Get marker names by gids
  • Search the allele_values, char_values, mapping_pop_values tables for the existence of gids.
  • Get marker ids from allele_values, char_values, mapping_pop_values by gids
  • Get marker name from marker table by marker ids
Get marker ids by marker names
  • Get marker ids from marker table by marker names.
  • Returns marker ids matching marker names
Get allelic values by gids and marker names
  • Get allelic values from allele_values, char_values, mapping_pop_values tables by gids and marker ids.
  • Returns allelic values for matching the gids and marker ids
Get mapping values by gids and marker names
  • Get mapping values (dataset_id, mapping_type, parent_a_gid, parent_b_gid, marker_type) from mapping_pop, marker tables by gids and marker ids.
Get maps
  • Get all the maps from map table
Get Map information by map name
  • Get marker_name, linkage_group, start_position from mapping_data view by map name
Get germplasm names by marker names.
  • Use - Get marker ids by marker names
  • Search the allele_values, char_values, mapping_pop_values tables for the existence of marker ids.
  • Get gids from allele_values, char_values, mapping_pop_values by marker ids
  • Returns germplasm names matching the marker names
Get dataset names
  • Get dataset names from dataset table
  • Returns dataset names.
  • Filter by ignoring 'qtl'(dataset type)
Get dataset details by dataset name
  • Get dataset details(dataset id , dataset type ) from dataset table by dataset name.
  • Returns dataset details.
Get marker ids by dataset id
  • Get marker ids from marker_metadataset table by dataset id
  • Returns marker ids.
Get parents by dataset id
  • Get gids of parents, mapping type from mapping_pop table by dataset id
  • Returns parents, mapping type
Get marker type by marker ids
  • Get marker type from marker table by marker ids
  • Returns marker type.
August 22, 2012

GDMS Functions

Name Description
Get allelic values from char_values table by dataset id
  • Given a dataset id, return the combination of gids, marker_ids, and char_values from the char_values table.
Get allelic values from allele_values table by dataset id
  • Given a dataset id, return the combination of gids, marker_ids, and char_values from the allele_values table.
Get allelic values from mapping_pop_values table by dataset id
  • Given a dataset id, return the combination of gids, marker_ids, and char_values from the
    mapping_pop_values table.
Get marker names by marker ids
  • Given a list of marker ids, return the matching marker names.
Get all marker types
  • Returns all marker types from the marker table which are not equal to "UA"
Get marker names by marker type
  • Given a marker type, return the names of the markers which have the specified marker type
Get marker info by marker name
  • Given a search string for marker name, return all marker info which satisfies the condition
Get marker info by genotype
  • Given a search string for genotype, return all marker info which satisfies the condition
Get marker info by db accession id
  • Given a search string for db accession id, return all marker info which satisfies the condition
Get gids from char_values by marker id
  • Given a marker id, return all the associated gids from char_values table
Get gids from allele_values by marker id
  • Given a marker id, return all the associated gids from allele_values table
Get gids from mapping_pop_values by marker id
  • Given a marker id, return all the associated gids from mapping_pop_values table
Get gid and nid by germplasm names
  • Given a list of germplasm names, return the matching gids and nids
Get all db accession ids
  • Return all db accession ids from the marker table
Get nids by dataset ids
  • Given a list of dataset ids, return the associated nids.
  • Provide the option to filter by gids
September 19 2012

GMS Function

Name Description
Get Studies by GID Given a gid, return the study records on which the gid is included.

Workbench Database Functions

Name Description
Add activity records in the Workbench database Given a valid activity record, save it in the Workbench database.
Get activities by project ID Given a workbench project id, return all the activities done for the project identified by the id.
Add tool configuration records in the Workbench database Given a valid tool configuration, save it in the Workbench database.
Get tool configurations by tool id Given a tool id, return all tool configurations for the tool identified by the id.
Get tool configuration by tool id and config key Given a tool id and a config key, return the tool configuration identified by the given parameters.
October 31, 2012

GMS Functions

Name Description
Get methods by type Given a breeding method type, return the method records with matching type.
Get methods by group Given a breeding method group, return the method records with matching group.
Get locations by country Given a country, return the locations which belong to that country.
Get locations by type Given a location type, return the locations with matching type.

Workbench Database Functions

Name Description
Add dataset record Given a valid workbench dataset record, save it into the workbench database. This can be used to keep track of the datasets created for a workbench project.
Get all roles Return all the possible roles a workbench user can have for a project.
Get all users Return all the workbench user records.
Get all persons Return all the person records stored in the workbench database.
Get installed crops Return the crops which have their central database installed along with the workbench.
Get last opened project Return the project record pertaining to the last workbench project opened by the user.
Get the IBDB Local User ID given workbench user id and project id Return the id of the user record in the local IBDB given a workbench project id and workbench user id. The returned id identifies the user record in the local IBDB instance for the project identified by the given project id. Note that each workbench user in a project is given a local IBDB user record.
Get location ids by project id Return a list of location ids for the project identified by the given id. The location records will be found on the central and/or local IBDB instances for the project.
Get method ids by project id Return a list of breeding method ids for the project identified by the given id. The method records will be found on the central and/or local IBDB instances for the project.
Get person by id Given an id, return the person record, identified by the given id, from the Workbench database.
Get projects Return the workbench project records.
Get datasets by name Given a name, return the dataset records with matching name.
Get datasets by project id Given a project id, return all the dataset records associated with the project identified by the id.
March 20, 2013

DMS Functions

Name Description Queries
Is Label ID Numeric check if a label ID is a numeric datatype ('N' in the ltype field of the factor table else it's 'C' which is Character) SELECT ltype FROM factor WHERE labelid = ?
Is Variate ID Numeric check if a label ID is a numeric datatype ('N' in the dtype field of the variate table else it's 'C' which is Character) SELECT dtype FROM variate
WHERE variatid = ?
has Variate Values By Numeric Variate ID and Dataset ID checks if a selected dataset and a specific numeric variate in that dataset has a value stored in the database. The dataset represents a table of data and the selected variate is one of its headers/columns. If there is no value from that column, it returns false, else true. SELECT count(distinct dn.ounitid) FROM oindex ou INNER JOIN data_n dn on dn.ounitid = ou.ounitid WHERE dn.variatid = :variatid and ou.represno = ?
has Variate Values By Character Variate ID and Dataset ID checks if a selected dataset and a specific character variate in that dataset has a value stored in the database. The dataset represents a table of data and the selected variate is one of its headers/columns. If there is no value from that column, it returns false, else true. SELECT count(distinct dc.ounitid) FROM oindex ou INNER JOIN data_c dc on dc.ounitid = ou.ounitid WHERE dc.variatid = :variatid and ou.represno = ?
has Variate Values By Variate ID and Dataset ID this method is a higher-level method which invokes either 'has Variate Values By Numeric Variate ID and Dataset ID' or 'has Variate Values By Character Variate ID and Dataset ID' depending on the variate id's datatype (dtype field in variate table)  
has Variate Values By Numeric Label ID and its Label Value and Numeric Variate ID and Dataset ID checks if a selected dataset and a specific Numeric variate in that dataset has a value stored in the database. This query is further limited by also considering a selected Numeric Label and its chosen value. The label is also represented as one of the columns in the dataset. The dataset represents a table of data and the selected Numeric variate is one of its headers/columns. If there is no value from that column, it returns false, else true. SELECT count(distinct dn.ounitid) FROM (SELECT ou.represno, ou.ounitid FROM oindex ou INNER JOIN represtn r on r.represno = ou.represno INNER JOIN level_n ln on ln.factorid = ou.factorid and ou.levelno = ln.levelno WHERE ln.labelid = ? and ln.lvalue = ? and ou.represno = ?) as x INNER JOIN data_n dn on dn.ounitid = x.ounitid WHERE dn.variatid = :variatid and x.represno = ?
has Variate Values By Character Label ID and its Label Value and Numeric Variate ID and Dataset ID checks if a selected dataset and a specific Numeric variate in that dataset has a value stored in the database. This query is further limited by also considering a selected Character Label and its chosen value. The label is also represented as one of the columns in the dataset. The dataset represents a table of data and the selected Numeric variate is one of its headers/columns. If there is no value from that column, it returns false, else true. SELECT count(distinct dn.ounitid) FROM (SELECT ou.represno, ou.ounitid FROM oindex ou INNER JOIN represtn r on r.represno = ou.represno INNER JOIN level_c lc on lc.factorid = ou.factorid and ou.levelno = lc.levelno WHERE lc.labelid = ? and lc.lvalue = ? and ou.represno = ?) as x INNER JOIN data_n dn on n.ounitid = x.ounitid WHERE dn.variatid = :variatid and x.represno = ?
has Variate Values By Numeric Label ID and its Label Value and Character Variate ID and Dataset ID checks if a selected dataset and a specific Character variate in that dataset has a value stored in the database. This query is further limited by also considering a selected Numeric Label and its chosen value. The label is also represented as one of the columns in the dataset. The dataset represents a table of data and the selected Character variate is one of its headers/columns. If there is no value from that column, it returns false, else true. SELECT count(distinct dc.ounitid) FROM (SELECT ou.represno, ou.ounitid FROM oindex ou INNER JOIN represtn r on r.represno = ou.represno INNER JOIN level_n ln on ln.factorid = ou.factorid and ou.levelno = ln.levelno WHERE ln.labelid = :labelid and ln.lvalue = ? and ou.represno = ?) as x INNER JOIN data_c dc on dc.ounitid = x.ounitid WHERE dc.variatid = :variatid and x.represno = ?
has Variate Values By Character Label ID and its Label Value and Character Variate ID and Dataset ID checks if a selected dataset and a specific Character variate in that dataset has a value stored in the database. This query is further limited by also considering a selected Character Label and its chosen value. The label is also represented as one of the columns in the dataset. The dataset represents a table of data and the selected Character variate is one of its headers/columns. If there is no value from that column, it returns false, else true. SELECT count(distinct dc.ounitid) FROM (SELECT ou.represno, ou.ounitid FROM oindex ou INNER JOIN represtn r on r.represno = ou.represno INNER JOIN level_c lc on lc.factorid = ou.factorid and ou.levelno = lc.levelno WHERE lc.labelid = :labelid and lc.lvalue = ? and ou.represno = ?) as x INNER JOIN data_c dc on dc.ounitid = x.ounitid WHERE dc.variatid = :variatid and x.represno = ?
has Variate Values By Label ID and its Label Value and Variate and Dataset ID this method is a higher-level method which invokes either:
'has Variate Values By Numeric Label ID and its Label Value and Numeric Variate ID and Dataset ID'
'has Variate Values By Numeric Label ID and its Label Value and Character Variate ID and Dataset ID'
'has Variate Values By Character Label ID and its Label Value and Numeric Variate ID and Dataset ID'
'has Variate Values By Character Label ID and its Label Value and Character Variate ID and Dataset ID'
...depending on the variate id's datatype (dtype field in variate table) and label id's datatype (ltype field in factor table)
 

GDMS Functions

Name Description Queries
Get datasetIds for fingerprinting *Get dataset ids from gdms_dataset table where dataset type not equal to 'mapping' and 'QTL'
*Returns Dataset IDs matching the given condition.
SELECT dataset_id FROM gdms_dataset WHERE dataset_type!='mapping' AND dataset_type !='QTL'
Get datasetIds for mapping
Get dataset ids from gdms_dataset table where dataset type equal to 'mapping' and dataset type not equal to 'QTL'
Returns Dataset IDs matching the given condition.   SELECT dataset_id FROM gdms_dataset WHERE dataset_type ='mapping' AND dataset_type !='QTL'
Get All parents from mapping population
  • Get parents from gdms_mapping_pop table.
  • Returns parents of all the mapping datasets.
select parent_a_gid, parent_b_gid from gdms_mapping_pop
Get details from gdms_acc_metadataset by gid
  • Get details from gdms_acc_metadataset table by gid
select gid,nid,dataset_id from gdms_acc_metadataset where gid in (?,?,....,n)
Get markers by gid and dataset
  • Get marker ids from gdms_marker_metadataset table by given gid and dataset id.
  • Returns markers_ids matching the condition
SELECT DISTINCT marker_id FROM gdms_marker_metadataset JOIN gdms_acc_metadataset ON gdms_marker_metadataset.dataset_id=gdms_acc_metadataset.dataset_id WHERE gdms_marker_metadataset.dataset_id in(?,?,...,n) and gdms_acc_metadataset.gid= ? order by gdms_marker_metadataset.marker_id
Get nids by markerId, dataset id and gid
  • Get nids from gdms_acc_metadataset, gdms_marker_metadataset tables by matching given marker ids, dataset id and ignoring the given gid.
  • Returns name ids
SELECT DISTINCT nid FROM gdms_acc_metadataset JOIN gdms_marker_metadataset ON gdms_acc_metadataset.dataset_id=gdms_marker_metadataset.dataset_id WHERE gdms_acc_metadataset.dataset_id in(?,?,...,n) and gdms_marker_metadataset.marker_id in(?,?,...,n) and gdms_acc_metadataset.gid not in(?,?,...,n) order by nid desc
Get nids by markerId, dataset id
  • Get nids from gdms_acc_metadataset, gdms_marker_metadataset tables by matching given marker ids, dataset id.
  • Returns Name ids
SELECT DISTINCT nid FROM gdms_acc_metadataset JOIN gdms_marker_metadataset ON gdms_acc_metadataset.dataset_id=gdms_marker_metadataset.dataset_id WHERE gdms_acc_metadataset.dataset_id in(?,?,...,n) and gdms_marker_metadataset.marker_id in(?,?,...,n) order by nid desc
Get alleles count by gid
  • Get allele count from gdms_allele_values table by gids
select count ( * ) from gdms_allele_values where gid in(?,?,...,n)
Get char count by gid
  • Get char count from gdms_char_values table by gids
select count ( * ) from gdms_char_values where gid in(?,?,...,n)
Get int alleleValues for polymorphic markers retrieval
  • Get dataset id, gid, marker name, allele value from gdms_allele_values and gdms_marker by gids
SELECT gdms_allele_values.dataset_id,gdms_allele_values.gid,gdms_marker.marker_name,gdms_allele_values.allele_bin_value as data FROM gdms_allele_values,gdms_marker WHERE gdms_allele_values.gid in(?,?,...,n) AND gdms_marker.marker_id = gdms_allele_values.marker_id ORDER BY gid, marker_name
Get char alleleValues for polymorphic markers retrieval
  • Get dataset id, gid, marker name, allele value from gdms_char_values and gdms_marker by gids
SELECT distinct gdms_char_values.dataset_id,gdms_char_values.gid,gdms_marker.marker_name,gdms_char_values.char_value FROM gdms_char_values,gdms_marker WHERE gdms_char_values.gid in(?,?,...,n) AND gdms_marker.marker_id = gdms_char_values.marker_id ORDER BY gid, marker_name
Get mapping alleleValues for polymorphic markers retrieval
  • Get dataset id, gid, marker name, allele value from gdms_mapping_pop_values and gdms_marker by gids
SELECT gdms_mapping_pop_values.dataset_id, gdms_mapping_pop_values.gid, gdms_marker.marker_name, gdms_mapping_pop_values.map_char_value as data FROM gdms_mapping_pop_values,gdms_marker WHERE gdms_mapping_pop_values.gid in(?,?,...,n) AND gdms_marker.marker_id = gdms_mapping_pop_values.marker_id ORDER BY gid, marker_name
Get all qtls
  • Get all the qtls names from gdms_qtl table
select qtl_id from gdms_qtl order by qtl_id
Get all qtl details
  • Get details of all the qtls from gdms_qtl_details, gdms_map, tmstraits tables
SELECT gdms_qtl.qtl_name,gdms_map.map_name,gdms_qtl_details.linkage_group AS chromosome, gdms_qtl_details.min_position, gdms_qtl_details.max_position, gdms_qtl_details.trait, gdms_qtl_details.experiment, gdms_qtl_details.left_flanking_marker, gdms_qtl_details.right_flanking_marker, gdms_qtl_details.effect, gdms_qtl_details.score_value, gdms_qtl_details.r_square,gdms_qtl_details.interactions, tmstraits.trname, tmstraits.ontology FROM gdms_qtl_details, gdms_qtl,gdms_map, tmstraits WHERE gdms_qtl.qtl_id in(?,?,...,n) AND gdms_qtl.qtl_id=gdms_qtl_details.qtl_id AND gdms_qtl_details.map_id=gdms_map.map_id and gdms_qtl_details.trait=tmstraits.trabbr order by gdms_qtl.qtl_id
Get qtl id by name
  • Get qtl id from gdms_qtl table by qtl name(like operator)
select qtl_id from gdms_qtl where qtl_name like '"qtl"%' order by qtl_id
Get qtl details by names
  • Get details of all the qtls from gdms_qtl_details, gdms_map, tmstraits tables by matching the given name
SELECT gdms_qtl.qtl_name,gdms_map.map_name,gdms_qtl_details.linkage_group AS chromosome, gdms_qtl_details.min_position, gdms_qtl_details.max_position, gdms_qtl_details.trait, gdms_qtl_details.experiment, gdms_qtl_details.left_flanking_marker, gdms_qtl_details.right_flanking_marker, gdms_qtl_details.effect, gdms_qtl_details.score_value, gdms_qtl_details.r_square,gdms_qtl_details.interactions, tmstraits.trname, tmstraits.ontology FROM gdms_qtl_details, gdms_qtl, gdms_map, tmstraits WHERE gdms_qtl.qtl_name like '"qtl.toLowerCase()"%' AND gdms_qtl.qtl_id=gdms_qtl_details.qtl_id AND gdms_qtl_details.map_id=gdms_map.map_id and gdms_qtl_details.trait=tmstraits.trabbr order by gdms_qtl.qtl_id
Get qtl by trait
  • Get qtl id from gdms_qtl_details table by trait
select qtl_id from gdms_qtl_details where trait like '"qtl"%' order by qtl_id
Get all maps details
  • Get details of all the maps from gdms_mapping_data view and gdms_map table
SELECT COUNT(DISTINCT `gdms_mapping_data`.`marker_id`) AS `marker_count` ,MAX(`gdms_mapping_data`.`start_position`) AS `max` , `gdms_mapping_data`.`linkage_group` AS Linkage_group, `gdms_mapping_data`.`map_name` AS map , gdms_map.map_type AS map_type FROM `gdms_mapping_data`, `gdms_map` WHERE gdms_mapping_data.map_id=gdms_map.map_id GROUP BY UCASE(`gdms_mapping_data`.`linkage_group`),UCASE(gdms_mapping_data.map_name) ORDER BY `gdms_mapping_data`.`map_name`, `gdms_mapping_data`.`linkage_group`
Get map details by name
  • Get details of all the maps from gdms_mapping_data view and gdms_map table by given map name
SELECT COUNT(DISTINCT `gdms_mapping_data`.`marker_id`) AS `marker_count` ,MAX(`gdms_mapping_data`.`start_position`) AS `max` , `gdms_mapping_data`.`linkage_group` AS Linkage_group, `gdms_mapping_data`.`map_name` AS map , gdms_map.map_type AS map_type FROM `gdms_mapping_data`, `gdms_map` WHERE gdms_mapping_data.map_id=gdms_map.map_id and lower(gdms_mapping_data.map_name) LIKE ('"map.toLowerCase()"%') GROUP BY UCASE(`gdms_mapping_data`.`linkage_group`),UCASE(gdms_mapping_data.map_name) ORDER BY `gdms_mapping_data`.`map_name`, `gdms_mapping_data`.`linkage_group`
Get markers by qtl
  • Get qtl id from gdms_qtl table by qtl name
  • Get map_id from gdms_qtl_details table by qtl_id
  • Get markers from gdms_marker, gdms_markers_onmap tables by map_id
  • Returns markers in a qtl
Get markers in a qtl:
"select qtl_id from gdms_qtl where qtl_name='"qtlName"'"
"select map_id from gdms_qtl_details where qtl_id=(select qtl_id from gdms_qtl where qtl_name='"qtlName"')"
"select marker_name from gdms_marker where marker_id in(select marker_id from gdms_markers_onmap where map_id="linkageMapId" and linkage_group='"chromosome"' and start_position between "min" AND "max")"

GDMS High-Level Upload Functions

Name Description
Set SSR Markers
  • Add Marker - marker_type = "SSR"
  • Add Marker Alias
  • Add Marker Details
  • Add Marker User Info
Set SNP Markers
  • Add Marker - marker_type = "SNP"
  • Add Marker Alias
  • Add Marker Details
  • Add Marker User Info
Set SSR Genotyping Data
  • Add Accession Metadataset
  • Add Marker Metadataset
  • Add Dataset Users
  • Add Allele Values
  • Add Dataset - dataset_type = "SSR", datatype = "int"
Set SNP Genotyping Data
  • Add Accession Metadataset
  • Add Marker Metadataset
  • Add Dataset Users
  • Add Char Values
  • Add Dataset - dataset_type = "SNP", datatype = "int"
Set DArT Genotyping Data
  • Add Accession Metadataset
  • Add Marker Metadataset
  • Add Dataset Users
  • Add Allele Values
  • Add Dataset - dataset_type = "DArT", datatype = "int"
  • Add DArT Values
Set Mapping Data
  • Add Accession Metadataset
  • Add Marker Metadataset
  • Add Dataset Users
  • Add Mapping Population
  • Add Mapping Population Values
  • Add Dataset - dataset_type = "mapping", datatype = "map"
Set Maps
  • Add Marker - marker_type = "UA"
  • Add Markers On Map
  • Add Map
Set QTLs
  • Add Dataset Users
  • Add Dataset - dataset_type = "QTL"
  • Add QTL Details
  • Add QTL
Set CISR Markers
  • Add Marker - marker-type = "CISR"
  • Add Marker Alias
  • Add Marker Details
  • Add Marker User Info
Set CAP Markers
  • Add Marker - marker_type = "CAP"
  • Add Marker Alias
  • Add Marker Details
  • Add Marker User Info
June 2013

DMS Functions (IBDBv2 - New Schema)

Name Description SQL Query
Get Study Details Given a study id, return the corresponding study details.  
Add Dataset Given a valid dataset record, save it into the local database.  
Add Dataset Variable Type Given a valid dataset variable type record, save it into the local database.  
Add Dataset With No Data Type Given a valid dataset with no data type record, save it into the local database.  
Add Experiment Given a valid experiment record, save it into the local database.  
Add Germplasm Given a valid germplasm record, save it into the local database.  
Add Study Given a valid study record, save it into the local database.  
Add Study with No Location Given a valid study record with no location, save it into the local database.  
Add Trial Environment Given a valid trial environment record, save it into the local database.  
Get the Count of Experiments Given an experiment id, display the count of the experiment records.  
Get the Count of Experiments By Trial Environment and Variate Given a trial environment and variate, display the count of the experiment records. select count(distinct e.nd_experiment_id)
from nd_experiment e, nd_experiment_phenotype ep, phenotype p
where e.nd_experiment_id = ep.nd_experiment_id
and ep.phenotype_id = p.phenotype_id
and e.nd_geolocation_id = 10070
and p.observable_id =20870
Get the Count of Stocks Given a dataset location and variate, return the count of the stock records. select count(distinct nes.stock_id)
from nd_experiment e, nd_experiment_phenotype ep, phenotype p, nd_experiment_stock nes, nd_experiment_project nep
where e.nd_experiment_id = ep.nd_experiment_id
and ep.phenotype_id = p.phenotype_id
and nes.nd_experiment_id = e.nd_experiment_id
and nep.nd_experiment_id = e.nd_experiment_id
and e.nd_geolocation_id = 10081
and p.observable_id = 18190
and nep.project_id = 10087
Delete Dataset Given a valid dataset record, delete it in the local database.  
Delete Experiments by Location Given a location, delete experiment record/s in the local database.  
Find One Dataset by Dataset type Given a dataset type, returns a single dataset belonging to the study that has the given dataset type.
If there is more than one matching dataset, only one is returned. If there are none, null is returned.
select *
from project p join project_relationship pr on p.project_id = pr.subject_project_id
join projectprop prop on p.project_id = prop.project_id
where pr.type_id = 1150
and pr.object_project_id = 10080
and prop.type_id = 8160
and prop.value = 10070
Get All Study Factors Given a study id, return all the factors associated with the study. set @studyId = 1010;
select pp1.value as localName, pp2.value as localDescription,
pp1.rank as rank, pp.value as term_id, c1.name as term_name, c1.definition as term_definition,
prop.cvterm_id as property_id, prop.name as prop_name, prop.definition as prop_definition,
meth.cvterm_id as method_id, meth.name as method_name, meth.definition as method_definition,
scale.cvterm_id as scale_id, scale.name as scale_name, scale.definition as scale_definition,
stin.cvterm_id as storedin_id, stin.name as storedin_name, stin.definition as storedin_definition
from projectprop pp
join projectprop pp1 on pp.rank = pp1.rank and pp.projectprop_id <> pp1.projectprop_id and pp1.type_id not in (1060, 1070, cast(pp.value as unsigned)) and pp1.project_id = pp.project_id
join projectprop pp2 on pp.rank = pp2.rank and pp2.type_id = 1060 and pp2.projectprop_id <> pp.projectprop_id and pp2.projectprop_id <> pp1.projectprop_id and pp2.project_id = pp.project_id
join cvterm c1 on c1.cvterm_id = cast(pp.value as unsigned)
join cvterm_relationship cr1 on cr1.subject_id = c1.cvterm_id and cr1.type_id = 1200
join cvterm prop on prop.cvterm_id = cr1.object_id
join cvterm_relationship cr2 on cr2.subject_id = c1.cvterm_id and cr2.type_id = 1210
join cvterm meth on meth.cvterm_id = cr2.object_id
join cvterm_relationship cr3 on cr3.subject_id = c1.cvterm_id and cr3.type_id = 1220
join cvterm scale on scale.cvterm_id = cr3.object_id
join cvterm_relationship cr4 on cr4.subject_id = c1.cvterm_id and cr4.type_id = 1044
join cvterm stin on stin.cvterm_id = cr4.object_id
join project_relationship pr on pr.object_project_id = @studyId and pr.type_id = 1150
where pp.type_id = 1070 and (pp.project_id = @studyId or pp.project_id = pr.subject_project_id)
and stin.cvterm_id not in (1043, 1048);
Get All Study Variates Given a study id, return all the variates associated with the study. set @studyId = 1010;
select pp1.value as localName, pp2.value as localDescription,
pp1.rank as rank, pp.value as term_id, c1.name as term_name, c1.definition as term_definition,
prop.cvterm_id as property_id, prop.name as prop_name, prop.definition as prop_definition,
meth.cvterm_id as method_id, meth.name as method_name, meth.definition as method_definition,
scale.cvterm_id as scale_id, scale.name as scale_name, scale.definition as scale_definition,
stin.cvterm_id as storedin_id, stin.name as storedin_name, stin.definition as storedin_definition
from projectprop pp
join projectprop pp1 on pp.rank = pp1.rank and pp.projectprop_id <> pp1.projectprop_id and pp1.type_id not in (1060, 1070, cast(pp.value as unsigned)) and pp1.project_id = pp.project_id
join projectprop pp2 on pp.rank = pp2.rank and pp2.type_id = 1060 and pp2.projectprop_id <> pp.projectprop_id and pp2.projectprop_id <> pp1.projectprop_id and pp2.project_id = pp.project_id
join cvterm c1 on c1.cvterm_id = cast(pp.value as unsigned)
join cvterm_relationship cr1 on cr1.subject_id = c1.cvterm_id and cr1.type_id = 1200
join cvterm prop on prop.cvterm_id = cr1.object_id
join cvterm_relationship cr2 on cr2.subject_id = c1.cvterm_id and cr2.type_id = 1210
join cvterm meth on meth.cvterm_id = cr2.object_id
join cvterm_relationship cr3 on cr3.subject_id = c1.cvterm_id and cr3.type_id = 1220
join cvterm scale on scale.cvterm_id = cr3.object_id
join cvterm_relationship cr4 on cr4.subject_id = c1.cvterm_id and cr4.type_id = 1044
join cvterm stin on stin.cvterm_id = cr4.object_id
join project_relationship pr on pr.object_project_id = @studyId and pr.type_id = 1150
where pp.type_id = 1070 and (pp.project_id = @studyId or pp.project_id = pr.subject_project_id)
and stin.cvterm_id in (1043, 1048);
Get Children of Folder Given a folder id, returns the list of children of the folder from the specified database. SELECT DISTINCT subject.project_id, subject.name, subject.description
, (CASE WHEN (type_id = 1145) THEN 1 ELSE 0 END) AS is_study
FROM project subject
INNER JOIN project_relationship pr on subject.project_id = pr.subject_project_id
WHERE (pr.type_id = 1140 or pr.type_id = 1145)
AND pr.object_project_id=1000
ORDER BY name;
Get Dataset Given a dataset id, returns the associated dataset details.  
Get Dataset Nodes by Study ID Given a study id, return the corresponding dataset nodes.  
Get Datasets by Type Given a dataset type, return the corresponding dataset records.  
Get Dataset with Multiple Trial Design Properties Display all dataset with multiple trial design properties.  
Get Experiments Display all experiments.  
Get Experiments with Average Type Given average as the experiment type, returns all the corresponding experiment records. select distinct ne.*
from nd_experiment_project nep
join nd_experiment ne on nep.nd_experiment_id = ne.nd_experiment_id
where project_id = 5803 and type_id in (1170) limit 500;
Get Experiment with Multiple Properties Display all experiments with multiple properties.  
Get Factors by Factor Type Given a factor type, return the corresponding factor records set @datasetId = 10015;
select pp.value as term_id, cvr.object_id as storedIn_id
from projectprop pp
join cvterm cvt on cvt.cvterm_id = cast(pp.value as unsigned) and pp.type_id = 1070
join cvterm_relationship cvr on cvr.type_id = 1044 and cvr.subject_id = cvt.cvterm_id
where pp.project_id = @datasetId
and cvr.object_id not in (1043, 1048)
and (@factorType = 'STUDY' and cvr.object_id in (1010, 1011, 1012, 1015, 1016, 1017)
or @factorType = 'TRIAL_DESIGN' and cvr.object_id = 1030
or @factorType = 'TRIAL_ENVIRONMENT' and cvr.object_id in (1020, 1021, 1022, 1023, 1024, 1025)
or @factorType = 'GERMPLASM' and cvr.object_id in (1040, 1041, 1042, 1046, 1047));
Get Factors by Property Given a property id, return the corresponding factor records set @datasetId = 10015;
set @propertyId = 2205;
select pp1.value as localName, pp2.value as localDescription,
pp1.rank as rank, pp.value as term_id, c1.name as term_name, c1.definition as term_definition,
prop.cvterm_id as property_id, prop.name as prop_name, prop.definition as prop_definition,
meth.cvterm_id as method_id, meth.name as method_name, meth.definition as method_definition,
scale.cvterm_id as scale_id, scale.name as scale_name, scale.definition as scale_definition,
stin.cvterm_id as storedin_id, stin.name as storedin_name, stin.definition as storedin_definition
from projectprop pp
join projectprop pp1 on pp.rank = pp1.rank and pp.projectprop_id <> pp1.projectprop_id and pp1.type_id not in (1060, 1070, cast(pp.value as unsigned)) and pp1.project_id = pp.project_id
join projectprop pp2 on pp.rank = pp2.rank and pp2.type_id = 1060 and pp2.projectprop_id <> pp.projectprop_id and pp2.projectprop_id <> pp1.projectprop_id and pp2.project_id = pp.project_id
join cvterm c1 on c1.cvterm_id = cast(pp.value as unsigned)
join cvterm_relationship cr1 on cr1.subject_id = c1.cvterm_id and cr1.type_id = 1200
join cvterm prop on prop.cvterm_id = cr1.object_id
join cvterm_relationship cr2 on cr2.subject_id = c1.cvterm_id and cr2.type_id = 1210
join cvterm meth on meth.cvterm_id = cr2.object_id
join cvterm_relationship cr3 on cr3.subject_id = c1.cvterm_id and cr3.type_id = 1220
join cvterm scale on scale.cvterm_id = cr3.object_id
join cvterm_relationship cr4 on cr4.subject_id = c1.cvterm_id and cr4.type_id = 1044
join cvterm stin on stin.cvterm_id = cr4.object_id
where pp.type_id = 1070 and pp.project_id = @datasetId
and stin.cvterm_id not in (1043, 1048)
and prop.cvterm_id = @propertyId;
Get Root Folders Return the list of root or top-level folders from specified database  
Get Stocks in Dataset Given a dataset, display all the corresponding stock records. SELECT DISTINCT es.stock_id
FROM nd_experiment_stock es
INNER JOIN nd_experiment e ON e.nd_experiment_id = es.nd_experiment_id
INNER JOIN nd_experiment_project ep ON ep.nd_experiment_id = e.nd_experiment_id
WHERE ep.project_id = 10085;
Get Studies by Folder Given a folder id, returns the list of study records. SET @HAS_PARENT_FOLDER = 1140;
SET @IS_STUDY = 1145;
SET @parentFolderId = 1000;
SELECT subject.project_id, subject.name, subject.description as title FROM project subject
INNER JOIN project_relationship pr ON subject.project_id = pr.subject_project_id
LEFT JOIN project_relationship is_study ON subject.project_id = is_study.subject_project_id AND is_study.type_id = @IS_STUDY
WHERE pr.type_id = @HAS_PARENT_FOLDER
and pr.object_project_id = @parentFolderId
and is_study.type_id = @IS_STUDY
ORDER BY project_id;
Get Trial Environments in Dataset Given a dataset, display the corresponding trial environment records.  
Search Studies by GID Given a GID, return the corresponding study records. set @gid = 70125;
select distinct s.*
from project s
inner join nd_experiment_project ep on ep.project_id = s.project_id
inner join project_relationship pr on pr.object_project_id = s.project_id and pr.type_id = 1150
inner join nd_experiment_stock es on es.nd_experiment_id = ep.nd+D10
12_experiment_id
inner join stock st on st.stock_id = es.stock_id
where st.dbxref_id = @gid
union
select distinct s.*
from project s
inner join project_relationship pr on pr.object_project_id = s.project_id and pr.type_id = 1150
inner join project d on d.project_id = pr.subject_project_id
inner join nd_experiment_project ep on ep.project_id = d.project_id
inner join nd_experiment_stock es on es.nd_experiment_id = ep.nd_experiment_id
inner join stock st on st.stock_id = es.stock_id
where st.dbxref_id = @gid;
Search Studies for All Returns the list of all studies.  
Search Studies By Country Given a country, return the corresponding study records. select distinct p.project_id, p.name, p.description
from nd_geolocationprop gp, nd_experiment e, nd_experiment_project ep, project_relationship pr, project p
where gp.type_id = 8190
and gp.value in (select distinct loc.locid from location loc where cntryid in (171))
and gp.nd_geolocation_id = e.nd_geolocation_id
and ((e.nd_experiment_id = ep.nd_experiment_id
and e.type_id = 1010
and ep.project_id = p.project_id) or
(e.nd_experiment_id = ep.nd_experiment_id
and e.type_id in (1155,1160,1170,1180)
and ep.project_id = pr.subject_project_id
and pr.object_project_id = p.project_id));
Search Studies By Name Given a study name, return the corresponding study records. select distinct p.project_id, p.name, p.description from project p where p.name = 'RYT2000WS';
Search Studies By Season Given a season, return the corresponding study records. select distinct p.project_id, p.name, p.description
from nd_geolocationprop gp, nd_experiment e, nd_experiment_project ep, project_relationship pr, project p
where gp.type_id = 8371
and gp.value = [enter value]
and gp.nd_geolocation_id = e.nd_geolocation_id
and ((e.nd_experiment_id = ep.nd_experiment_id
and e.type_id = 1010
and ep.project_id = p.project_id) or
(e.nd_experiment_id = ep.nd_experiment_id
and e.type_id in (1155,1160,1170,1180)
and ep.project_id = pr.subject_project_id
and pr.object_project_id = p.project_id));
Search Studies By Start Date Given a start date, return the corresponding study records. select distinct p.project_id, p.name, p.description from projectprop pp, project p
where pp.type_id = 8050 and pp.value = 20050119 and pp.project_id = p.project_id;
Set Experiment Value Change the value of any variable in a dataset  

OMS Functions (IBDBv2 - New Schema)

Name Description SQL Query
Add Method Given a valid Method record, save it into the local database.  
Add Standard Variable Given a valid Standard Variable record, save it into the local database.
Record should have property, method, scale, dataType, and storedIn info.
 
Copy Standard Variable Given a standard variable id, produce a duplicate of the standard variable record.
Standard variable id of the duplicated record is 0
 
Find Method by ID Given a method id, return the corresponding method record. select * from cvterm where cv_id=1020 and cvterm_id=4000;
Find Method By Name Given a method name, return the corresponding method record. select * from cvterm where cv_id=1020 and name='Cooking Test at Maturity';
Find Standard Variable By Name or Synonym Given a name or a synonym, return the corresponding standard variable record. select distinct cvterm.cvterm_id
from cvterm cvterm, cvtermsynonym syn
where cvterm.cv_id = 1040
and (cvterm.name = 'Accession name'
or (syn.synonym = 'Accession name'
and syn.cvterm_id = cvterm.cvterm_id));
Get CVTerm by ID Given a cvterm id, return the corresponding cvterm record select * from cvterm wherecvterm_id=1010;
Get Standard Variable Given a standard variable id, return the corresponding standard variable record.  
July 2013

GDMS Functions

Name Description SQL Query
countNidsFromAccMetadatasetByDatasetIds Input: List of datasetIds "select count(distinct nid) from gdms_acc_metadataset where dataset_id in ("+ datasetIds +")"
getMapIdByName Input: mapName
Output: mapId
"select map_id from gdms_map where map_name =(" + mapName + ")"
getQTLsByMapId Input: mapId
"select * from gdms_qtl_details where map_id =("+ mapId + ")"
countQTLsByMapId Input: mapId "select count ( * )
from gdms_qtl_details where =("+ mapId + ")"
getAllFromAccMetadataset List of gids, datasetId, SetOperation (IN, NOT_IN) "select * from gdms_acc_metadataset where gid IN/NOT IN (" + gidList + ") and dataset_id = ("+ datasetId+")"
getMapAndMarkerCountByMarkers Input: List of markerIds "SELECT gdms_map.map_name, COUNT(gdms_markers_onmap.marker_id) FROM gdms_markers_onmap JOIN gdms_map ON gdms_map.map_id=gdms_markers_onmap.map_id WHERE gdms_markers_onmap.marker_id IN("+ markerIDs + ") GROUP BY gdms_map.map_name"
countMappingPopValuesByGids Input: list of gids "select count(distinct mp_id) from gdms_mapping_pop_values where gid in("+ gidList +")"
getMarkerFromCharValuesByGids Input: list of gids
Output: list of markerIds
"select distinct marker_id from gdms_char_values where gid in ("+ gidList +")"
getMarkerFromAlleleValuesByGids Input: list of gids
Output: list of markerIds
"select distinct marker_id from gdms_allele_values where gid in ("+ gidList +")"
getMarkerFromMappingPopValuesByGids Input: list of gids
Output: list of markerIds
"select distinct marker_id from gdms_mapping_pop_values where gid in ("+ gidList +")"
getAllFromMarkerMetadatasetByMarker Input: markerId
Output: list of MarkerMetadataSet
"select * from gdms_marker_metadataset where marker_id=("+ markerId +")"
getDatasetDetailsByDatasetId Input: List of datasetIds
Output: List of GDMS Datasets
"select * from gdms_dataset where dataset_id in ("+ datasetIds +")"
getLastId Input: tfieldName, tableName common for all the below queries
"select min(dataset_id) from gdms_dataset"
"select min(marker_id) from gdms_marker"
"select min(ac_id) from gdms_char_values"
"select min(an_id) from gdms_allele_values"
"select min(ad_id) from gdms_dart_values"
"select min(mp_id) from gdms_mapping_pop_values"
"select min(mta_id) from gdms_mta"
getQTLIdsByDatasetIds Input: list of datasetIds
Output: list of QTL Ids
"select qtl_id from gdms_qtl where dataset_id in ("+ datasetIds +")"
deleteQTLs Input: datasetId, qtlId "delete from gdms_qtl_details where qtl_id IN('"+ qtlIds +"')"
"delete from gdms_qtl where qtl_id IN('"+ qtlIds +"')"
"delete from gdms_dataset_users where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset where dataset_id='"+ datasetId +"'"
deleteMaps Input: datasetId "delete from gdms_markers_onmap where map_id='"+ datasetId +"'"
"delete from gdms_map where map_id='"+ datasetId +"'"
deleteSSRGenotypingDatasets Input: datasetId "delete from gdms_allele_values where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset_users where dataset_id='"+ datasetId +"'"
"delete from gdms_acc_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_marker_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset where dataset_id='"+ datasetId +"'"
deleteSNPGenotypingDatasets Input: datasetId "delete from gdms_char_values where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset_users where dataset_id='"+ datasetId +"'"
"delete from gdms_acc_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_marker_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset where dataset_id='"+ datasetId +"'"
deleteDArTGenotypingDatasets Input: datasetId "delete from gdms_allele_values where dataset_id='"+ datasetId +"'"
"delete from gdms_dart_values where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset_users where dataset_id='"+ datasetId +"'"
"delete from gdms_acc_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_marker_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset where dataset_id='"+ datasetId +"'"
deleteMappingPopulationDatasets Input: datasetIds "delete from gdms_mapping_pop_values where dataset_id='"+ datasetId +"'"
"delete from gdms_mapping_pop where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset_users where dataset_id='"+ datasetId +"'"
"delete from gdms_acc_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_marker_metadataset where dataset_id='"+ datasetId +"'"
"delete from gdms_dataset where dataset_id='"+ datasetId +"'"
addMTA setMTAs
Insert data into the following tables as shown below:
gdms_dataset_users
dataset_id -- auto generated unique identifier
user_id -- retrieved from users table for the Principle investigator from MTA_Source of MTA template
gdms_dataset
dataset_id -- auto generated unique identifier
dataset_name -- Dataset Name from MTA_Source sheet of MTA template
dataset_desc -- Dataset Description from MTA_Source sheet of MTA template
dataset_type -- "MTA"
genus -- Genus from MTA_Source sheet of MTA template
species -- Species from MTA_Source sheet of MTA template
upload_template_date -- auto generated
method - Method from MTA_Source sheet of MTA Template
score-- Score from MTA_Source sheet of MTA Template
gdms_mta
mta_id -- auto generated unique identifier
marker_id -- should be retrieved from gdms_marker table based on the Marker given in MTA_Data sheet of MTA template
dataset_id -- auto generated unique identifier
map_id -- should be retrieved from gdms_map table based on the Map-Name given in MTA_Data sheet of MTA template
linkage_group -- Chromosome from MTA_Data sheet of MTA template
position -- Position from MTA_Data sheet of MTA template
trait -- Trait from MTA_Data sheet of MTA template
effect -- Effect from MTA_Data sheet of MTA template
hv_allele -- High value allele from MTA_Data sheet of MTA template
experiment -- Experiment from MTA_Data sheet of MTA template
score_value -- Score (e.g.,LOD (or) -log10 (p)) from MTA_Data sheet of MTA template
r_square -- R2 from MTA_Data sheet of MTA template
 
getAllMTAs   "select * from gdms_mta"
getMTAsByTrait Input: TraitId "select * from gdms_mta where tid=("tid")"
countMappingAlleleValuesByGids Input: List of GIDs
Output: count
"SELECT COUNT(DISTINCT an_id) FROM gdms_allele_values WHERE gid in ("+ gidList +")"
Unassigned
Functions involving metadata tables
Name Description
Get All User Defined Field
  • based on GMS_getUDField
  • Return all user defined field records.
Get User Defined Field by field type and table name
  • based on GMS_getUDField2
  • Given the field type and table name, return all user defined field records which are the constant values for the specified field type of the specified table.
  • Match the given parameters to udflds.ftype and udflds.ftable
Get User Defined Field by name
  • based on GMS_findUDField
  • Given a name, return all user defined field records with name matching the parameter.
  • Provide option for supporting wildcard characters in the given name.
Get Location by Name
  • based on GMS_findLocation
  • Given a name, return all location records with name matching the parameter.
  • Provide option for supporting wildcard characters in the given name.
Get All Locations
  • based on GMS_getLocation
  • Return all location records.
Find Locations by example
  • based on GMS_getLocation2
  • Given a sample location record, return all location records matching the sample.
Get All Bibliographic reference record
  • based on GMS_getBibrefs
  • Return all bibliographic reference records.
DMS Functions
Name Description
Find Study by name
  • based on DMS_findStudy
  • Given a name, return all study records with name matching the parameter.
  • Provide options for using equal or like operator and for using wildcards on the name parameter.
Find Factor by name and study id
  • based on DMS_findFactor
  • Given a name and study id, return all factor records which belong to the study identified by the id and the factor name should match the parameter.
  • Provide options for using equal or like operator and for using wildcards on the name parameter.
Find Factor by name
  • based on DMS_findFactor
  • Given a name, return all factor records with names matching the parameter.
  • Provide options for using equal or like operator and for using wildcards on the name parameter.
Find Variate by name and study id
  • based on DMS_findVariate
  • Given a name and study id, return all variate records which belong to the study identified by the id and the factor name should match the parameter.
  • Provide options for using equal or like operator and for using wildcards on the name parameter.
Adaptavist Theme Builder (3.3.3-conf210) Powered by Atlassian Confluence 2.10.3, the Enterprise Wiki.
Free theme builder license