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_core/api/tripal_core.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_delete_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic function for deleting a record(s) from any chado table
chado_delete_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic function for deleting a record(s) from any chado table
chado_expand_var tripal_core/api/tripal_core.chado_variables.api.inc Retrieves fields/tables/nodes that were excluded by default from a variable and adds them
chado_generate_var tripal_core/api/tripal_core.chado_variables.api.inc Generates an array containing the full details of a record(s) in chado. The returned array differs from the array returned by chado_select_record as all foreign key relationships have been followed and those data are also included. The array returned…
chado_insert_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic routine for inserting into any Chado table
chado_insert_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic routine for inserting into any Chado table
chado_pager_get_count tripal_core/api/tripal_core.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_core/api/tripal_core.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_core/api/tripal_core.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_core/api/tripal_core.chado_query.api.inc Use this function instead of pager_query() when selecting a subset of records from a Chado table.
chado_query tripal_core/api/tripal_core.chado_query.api.inc Use this function instead of db_query() to avoid switching databases when making query to the chado database
chado_query tripal_core/api/tripal_core.chado_query.api.inc Use this function instead of db_query() to avoid switching databases when making query to the chado database
chado_schema_get_foreign_key tripal_core/api/tripal_core.chado_query.api.inc Gets the value of a foreign key relationship
chado_select_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic routine for selecting data from a Chado table
chado_select_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic routine for selecting data from a Chado table
chado_select_record_check_value_type tripal_core/api/tripal_core.chado_query.api.inc Helper Function: check that the value is the correct type.
chado_update_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic routine for updating into any Chado table
chado_update_record tripal_core/api/tripal_core.chado_query.api.inc Provides a generic routine for updating into any Chado table