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

File

tripal_chado/api/tripal_chado.query.api.inc, line 719

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_chado', 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_chado', 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_chado', 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_chado', 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_chado', 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_chado', 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_chado', 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_chado', 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_chado', 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_chado', 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;
}