function chado_update_record

2.x tripal_core.chado_query.api.inc chado_update_record($table, $match, $values, $options = NULL)

Provides a generic routine for updating into any Chado table

Use this function to update a record in any Chado table. The first argument specifies the table for inserting, the second is an array of values to matched for locating the record for updating, and the third argument give the values to update. The arrays are mutli-dimensional such that foreign key lookup values can be specified.

$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);

The above code species that a feature with a given uniquename, organism_id, and type_id (the unique constraint for the feature table) will be updated. The organism_id is specified as a nested array that uses the organism_id foreign key constraint to lookup the specified values to find the exact organism_id. The same nested struture is also used for specifying the values to update. The function will find the record that matches the columns specified and update the record with the avlues in the $uvalues array.

@TODO: Support Complex filtering as is done in chado_select_record();

Parameters

$table: The name of the chado table for inserting

$match: An associative array containing the values for locating a record to update.

$values: An associative array containing the values for updating.

$options: An array of options such as:

  • return_record: by default, the function will return the TRUE if the record was succesfully updated. However, set this option to TRUE to return the record that was updated. The returned record will have the fields provided but the primary key (if available for the table) will be added to the record.

Return value

On success this function returns TRUE. On failure, it returns FALSE.

Example usage:

Related topics

25 calls to chado_update_record()
chado_analysis_update in tripal_analysis/includes/tripal_analysis.chado_node.inc
Implements hook_update(). Update analyses
chado_contact_update in tripal_contact/includes/tripal_contact.chado_node.inc
Implements hook_update
chado_example_update in tripal_example/includes/tripal_example.chado_node.inc
Implementation of hook_update(). This function runs after the node has been inserted into the Drupal schema and allows us to update the record in Chado.
chado_featuremap_update in tripal_featuremap/includes/tripal_featuremap.chado_node.inc
Implements hook_update(). Update nodes
chado_feature_update in tripal_feature/includes/tripal_feature.chado_node.inc
Implements hook_update().

... See full list

1 string reference to 'chado_update_record'

File

tripal_core/api/tripal_core.chado_query.api.inc, line 552
Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.

Code

function chado_update_record($table, $match, $values, $options = NULL) {

  $print_errors = (isset($options['print_errors'])) ? $options['print_errors'] : FALSE;

  if (!is_array($values)) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'Cannot pass non array as values for updating.', 
    array(), array('print' => $print_errors)
    );
    return FALSE;
  }
  if (count($values) == 0) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'Cannot pass an empty array as values for updating.', 
    array(), array('print' => $print_errors)
    );
    return FALSE;
  }

  if (!is_array($match)) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'Cannot pass non array as values for matching.', 
    array(), array('print' => $print_errors)
    );
    return FALSE;
  }
  if (count($match) == 0) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'Cannot pass an empty array as values for matching.', 
    array(), array('print' => $print_errors)
    );
    return FALSE;
  }

  // set defaults for options. If we don't set defaults then
  // we get memory leaks when we try to access the elements
  if (!is_array($options)) {
    $options = array();
  }

  if (!array_key_exists('return_record', $options)) {
    $options['return_record'] = FALSE;
  }

  $update_values = array(); // contains the values to be updated
  $update_matches = array(); // contains the values for the where clause

  // get the table description
  $table_desc = chado_get_schema($table);
  if (!$table_desc) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'The table name, %table, does not exist.', 
    array('%table', $table), array('print' => $print_errors)
    );
    return FALSE;
  }

  // if the user wants us to return the record then we need to get the
  // unique primary key if one exists.  That way we can add it to the
  // values that get returned at the end of the function
  $pkeys = array();
  if ($options['return_record'] == TRUE) {
    if (array_key_exists('primary key', $table_desc) and is_array($table_desc['primary key'])) {
      $columns = array();
      $stmt_suffix = '';
      foreach ($table_desc['primary key'] as $field) {
        $columns[] = $field;
        $stmt_suffix .= substr($field, 0, 2);
      }
      $options2 = array();
      $results = chado_select_record($table, $columns, $match, $options2);
      if (count($results) > 0) {
        foreach ($results as $index => $pkey) {
          $pkeys[] = $pkey;
        }
      }
    }
  }

  // get the values needed for matching in the SQL statement
  foreach ($match as $field => $value) {
    if (is_array($value)) {
      $results = chado_schema_get_foreign_key($table_desc, $field, $value);
      if (sizeof($results) > 1) {
        tripal_report_error('tripal_core', TRIPAL_ERROR, 
        'chado_update_record: When trying to find record to update, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', 
        array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), 
        array('print' => $print_errors)
        );
        return FALSE;
      }
      elseif (sizeof($results) < 1) {
        tripal_report_error('tripal_core', TRIPAL_DEBUG, 
        'chado_update_record: When trying to find record to update, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', 
        array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), 
        array('print' => $print_errors)
        );
        return FALSE;
      }
      else {
        $update_matches[$field] = $results[0];
      }
    }
    else {
      $update_matches[$field] = $value;
    }
  }

  // get the values used for updating
  foreach ($values as $field => &$value) {
    if (is_array($value)) {
      $foreign_options = array();
      // select the value from the foreign key relationship for this value
      $results = chado_schema_get_foreign_key($table_desc, $field, $value, $foreign_options);
      if (sizeof($results) > 1) {
        tripal_report_error('tripal_core', TRIPAL_ERROR, 
        'chado_update_record: When trying to find update values, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', 
        array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), 
        array('print' => $print_errors)
        );
        return FALSE;
      }
      elseif (sizeof($results) < 1) {
        tripal_report_error('tripal_core', TRIPAL_DEBUG, 
        'chado_update_record: When trying to find update values, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', 
        array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), 
        array('print' => $print_errors)
        );
        return FALSE;
      }
      else {
        $update_values[$field] = $results[0];
      }
    }
    else {
      $update_values[$field] = $value;
    }
  }

  // now build the SQL statement
  $sql = 'UPDATE {' . $table . '} SET ';
  $args = array(); // arguments passed to chado_query
  foreach ($update_values as $field => $value) {
    if (strcmp($value, '__NULL__') == 0) {
      $sql .= " $field = NULL, ";
    }
    else {
      $sql .= " $field = :$field, ";
      $args[":$field"] = $value;
    }
  }
  $sql = drupal_substr($sql, 0, -2); // get rid of the trailing comma & space

  $sql .= " WHERE ";
  foreach ($update_matches as $field => $value) {
    if (strcmp($value, '__NULL__') == 0) {
      $sql .= " $field = NULL AND ";
    }
    else {
      $sql .= " $field = :$field AND ";
      $args[":$field"] = $value;
    }
  }
  $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND'

  $result = chado_query($sql, $args);

  // if we have a result then add primary keys to return array
  if ($options['return_record'] == TRUE and $result) {
    // only if we have a single result do we want to add the primary keys to the values
    // array.  If the update matched many records we can't add the pkeys

    if (count($pkeys) == 1) {
      foreach ($pkeys as $index => $pkey) {
        foreach ($pkey as $field => $fvalue) {
          $values[$field] = $fvalue;
        }
      }
    }
    return $values;
  }
  elseif ($options['return_record'] == FALSE and $result) {
    return TRUE;
  }
  else {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    "chado_update_record: Cannot update record in %table table.  \nMatch: %match \nValues: %values", 
    array('%table' => table, '%match' => print_r($match, TRUE), '%values' => print_r($values, 1)), 
    array('print' => $print_errors)
    );
    return FALSE;
  }

  return FALSE;
}