Chado Query

  1. 2.x tripal_core/api/tripal_core.chado_query.api.inc tripal_chado_query_api
  2. 3.x tripal_chado/api/tripal_chado.query.api.inc tripal_chado_query_api

Provides an API for querying of chado including inserting, updating, deleting and selecting from specific chado tables. There is also a generic function, chado_query(), to execute and SQL statement on chado. It is ideal to use these functions to interact with chado in order to keep your module compatible with both local & external chado databases. Furthermore, it ensures connection to the chado database is taken care of for you.

Generic Queries to a specifc chado table:

chado_select_record( [table name], [columns to select], [specify record to select], [options*] ) This function allows you to select various columns from the specified chado table. Although you can only select from a single table, you can specify the record to select using values from related tables through use of a nested array. For example, the following code shows you how to select the name and uniquename of a feature based on it's type and source organism.

  $values =  array(
    'organism_id' => array(
        'genus' => 'Citrus',
        'species' => 'sinensis',
     ),
    'type_id' => array (
        'cv_id' => array (
           'name' => 'sequence',
        ),
        'name' => 'gene',
        'is_obsolete' => 0
     ),
  );
  $result = chado_select_record(
     'feature',                      // table to select from
     array('name', 'uniquename'),    // columns to select
     $values                         // record to select (see variable defn. 
                                                          above)
  );

chado_insert_record( [table name], [values to insert], [options*] ) This function allows you to insert a single record into a specific table. The values to insert are specified using an associative array where the keys are the column names to insert into and they point to the value to be inserted into that column. If the column is a foreign key, the key will point to an array specifying the record in the foreign table and then the primary key of that record will be inserted in the column. For example, the following code will insert a feature and for the type_id, the cvterm.cvterm_id of the cvterm record will be inserted and for the organism_id, the organism.organism_id of the organism_record will be inserted.

  $values =  array(
    'organism_id' => array(
        'genus' => 'Citrus',
        'species' => 'sinensis',
     ),
    'name' => 'orange1.1g000034m.g',
    'uniquename' => 'orange1.1g000034m.g',
    'type_id' => array (
        'cv_id' => array (
           'name' => 'sequence',
        ),
        'name' => 'gene',
        'is_obsolete' => 0
     ),
  );
  $result = chado_insert_record(
    'feature',             // table to insert into
    $values                // values to insert
  );

chado_update_record( [table name], [specify record to update], [values to change], [options*] ) This function allows you to update records in a specific chado table. The record(s) you wish to update are specified the same as in the select function above and the values to be update are specified the same as the values to be inserted were. For example, the following code species that a feature with a given uniquename, organism_id, and type_id (the unique constraint for the feature table) will be updated with a new name, and the type changed from a gene to an mRNA.

$umatch = array(
  'organism_id' => array(
    'genus' => 'Citrus',
    'species' => 'sinensis',
  ),
  'uniquename' => 'orange1.1g000034m.g7',
  'type_id' => array (
    'cv_id' => array (
      'name' => 'sequence',
    ),
    'name' => 'gene',
    'is_obsolete' => 0
  ),
);
$uvalues = array(
  'name' => 'orange1.1g000034m.g',
  'type_id' => array (
    'cv_id' => array (
      'name' => 'sequence',
    ),
    'name' => 'mRNA',
    'is_obsolete' => 0
  ),
);
  $result = chado_update_record('feature',$umatch,$uvalues);

chado_delete_record( [table name], [specify records to delete], [options*] ) This function allows you to delete records from a specific chado table. The record(s) to delete are specified the same as the record to select/update was above. For example, the following code will delete all genes from the organism Citrus sinensis.

  $values =  array(
    'organism_id' => array(
        'genus' => 'Citrus',
        'species' => 'sinensis',
     ),
    'type_id' => array (
        'cv_id' => array (
           'name' => 'sequence',
        ),
        'name' => 'gene',
        'is_obsolete' => 0
     ),
  );
  $result = chado_select_record(
     'feature',                      // table to select from
     $values                         // records to delete (see variable defn. 
                                                           above)
  );

Generic Queries for any SQL:

Often it is necessary to select from more then one table in chado or to execute other complex queries that cannot be handled efficiently by the above functions. It is for this reason that the chado_query( [sql string], [arguments to sub-in to the sql] ) function was created. This function allows you to execute any SQL directly on the chado database and should be used with care. If any user input will be used in the query make sure to put a placeholder in your SQL string and then define the value in the arguments array. This will make sure that the user input is santized and safe through type-checking and escaping. The following code shows an example of how to use user input resulting from a form and would be called withing the form submit function.

$sql = "SELECT F.name, CVT.name as type_name, ORG.common_name
         FROM feature F
         LEFT JOIN cvterm CVT ON F.type_id = CVT.cvterm_id
         LEFT JOIN organism ORG ON F.organism_id = ORG.organism_id
         WHERE
           F.uniquename = :feature_uniquename";
$args = array( ':feature_uniquename' => $form_state['values']['uniquename'] );
$result = chado_query( $sql, $args );
foreach ($result as $r) { [Do something with the records here] }

If you are going to need more then a couple fields, you might want to use the Chado Variables API (specifically chado_generate_var()) to select all of the common fields needed including following foreign keys.

Loading of Variables from chado data:

These functions, chado_generate_var() and chado_expand_var(), generate objects containing the full details of a record(s) in chado. These should be used in all theme templates.

This differs from the objects returned by chado_select_record in so far as all foreign key relationships have been followed meaning you have more complete details. Thus this function should be used whenever you need a full variable and chado_select_record should be used if you only case about a few columns.

The initial variable is generated by the chado_generate_var([table], [filter criteria], [optional options]) function. An example of how to use this function is:

$values = array(
'name' => 'Medtr4g030710'
);
$features = chado_generate_var('feature', $values);

This will return an object if there is only one feature with the name Medtr4g030710 or it will return an array of feature objects if more than one feature has that name.

Some tables and fields are excluded by default. To have those tables & fields added to your variable you can use the chado_expand_var([chado variable], [type], [what to expand], [optional options]) function. An example of how to use this function is:


Get a chado object to be expanded
$values = array(
'name' => 'Medtr4g030710'
);
$features = chado_generate_var('feature', $values);
Expand the organism node
$feature = chado_expand_var($feature, 'node', 'organism');
Expand the feature.residues field
$feature = chado_expand_var($feature, 'field', 'feature.residues');
Expand the feature properties (featureprop table)
$feature = chado_expand_var($feature, 'table', 'featureprop');

Parent topics

File

tripal_chado/api/tripal_chado.query.api.inc, line 8
Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.

Functions

Namesort descending Location Description
chado_db_select tripal_chado/api/tripal_chado.query.api.inc A replacment for db_select when querying Chado.
chado_db_select tripal_chado/api/tripal_chado.query.api.inc A replacment for db_select when querying Chado.
chado_delete_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic function for deleting a record(s) from any chado table.
chado_delete_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic function for deleting a record(s) from any chado table.
chado_get_schema_name tripal_chado/api/tripal_chado.query.api.inc Retrieve the name of the PostgreSQL schema housing Chado or Drupal.
chado_get_schema_name tripal_chado/api/tripal_chado.query.api.inc Retrieve the name of the PostgreSQL schema housing Chado or Drupal.
chado_get_table_max_rank tripal_chado/api/tripal_chado.query.api.inc Get max rank for a given set of criteria.
chado_get_table_max_rank tripal_chado/api/tripal_chado.query.api.inc Get max rank for a given set of criteria.
chado_insert_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic routine for inserting into any Chado table
chado_insert_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic routine for inserting into any Chado table
chado_pager_get_count tripal_chado/api/tripal_chado.query.api.inc A function to retrieve the total number of records for a pager that was generated using the chado_pager_query() function.
chado_pager_get_count tripal_chado/api/tripal_chado.query.api.inc A function to retrieve the total number of records for a pager that was generated using the chado_pager_query() function.
chado_pager_query tripal_chado/api/tripal_chado.query.api.inc Use this function instead of pager_query() when selecting a subset of records from a Chado table.
chado_pager_query tripal_chado/api/tripal_chado.query.api.inc Use this function instead of pager_query() when selecting a subset of records from a Chado table.
chado_query tripal_chado/api/tripal_chado.query.api.inc A substitute for db_query() when querying from Chado.
chado_query tripal_chado/api/tripal_chado.query.api.inc A substitute for db_query() when querying from Chado.
chado_schema_get_foreign_key tripal_chado/api/tripal_chado.query.api.inc Gets the value of a foreign key relationship.
chado_select_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic routine for selecting data from a Chado table.
chado_select_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic routine for selecting data from a Chado table.
chado_select_record_check_value_type tripal_chado/api/tripal_chado.query.api.inc Helper Function: check that the value is the correct type.
chado_select_record_check_value_type tripal_chado/api/tripal_chado.query.api.inc Helper Function: check that the value is the correct type.
chado_set_active tripal_chado/api/tripal_chado.query.api.inc Set the Tripal Database
chado_set_active tripal_chado/api/tripal_chado.query.api.inc Set the Tripal Database
chado_update_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic routine for updating into any Chado table.
chado_update_record tripal_chado/api/tripal_chado.query.api.inc Provides a generic routine for updating into any Chado table.
hook_chado_connection_alter tripal_chado/api/tripal_chado.query.api.inc Alter Chado connection settings.
hook_chado_connection_alter tripal_chado/api/tripal_chado.query.api.inc Alter Chado connection settings.
hook_chado_get_schema_name_alter tripal_chado/api/tripal_chado.query.api.inc Alter the name of the schema housing Chado and/or Drupal.
hook_chado_get_schema_name_alter tripal_chado/api/tripal_chado.query.api.inc Alter the name of the schema housing Chado and/or Drupal.
hook_chado_query_alter tripal_chado/api/tripal_chado.query.api.inc This hook provides a way for module developers to alter any/all queries on the chado schema by Tripal.
hook_chado_query_alter tripal_chado/api/tripal_chado.query.api.inc This hook provides a way for module developers to alter any/all queries on the chado schema by Tripal.