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 = tripal_core_chado_update('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.

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:

• statement_name: the name of the prepared statement to use. If the statement has not yet been prepared it will be prepared automatically. On subsequent calls with the same statement_name only an execute on the previously prepared statement will occur.
• is_prepared: TRUE or FALSE. Whether or not the statement is prepared. By default if the statement is not prepared it will be automatically. However to avoid this check, which requires a database query you can set this value to true and the check will not be performed.
• 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_core/api/tripal_core_chado.api.inc, line 457

The Tripal Core API

Code

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

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

  if (!is_array($match)) {
    watchdog('tripal_core', 'Cannot pass non array as values for matching.', array(), 
    WATCHDOG_ERROR);
    return FALSE;
  }
  if (count($match) == 0) {
    watchdog('tripal_core', 'Cannot pass an empty array as values for matching.', array(), 
    WATCHDOG_ERROR);
    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('is_prepared', $options)) {
    $options['is_prepared'] = FALSE;
  }
  if (!array_key_exists('statement_name', $options)) {
    $options['statement_name'] = FALSE;
  }
  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

  // Determine plan of action
  if ($options['statement_name']) {
    // we have a prepared statment (or want to create one) so set $prepared = TRUE
    $prepared = TRUE;

    // we need to get a persistent connection.  If one exists this function
    // will not recreate it, but if not it will create one and store it in
    // a Drupal variable for reuse later.
    $connection = tripal_db_persistent_chado();

    // if we cannot get a connection the abandon the prepared statement
    if (!$connection) {
      $prepared = FALSE;
      unset($options['statement_name']);
    }
  }
  else {
    //print "NO STATEMENT (update): $table\n";
    //debug_print_backtrace();
  }

  // get the table description
  $table_desc = tripal_core_get_chado_table_schema($table);

  // 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('statement_name' => 'sel_' . $table . '_' . $stmt_suffix);
      $results = tripal_core_chado_select($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)) {
      $foreign_options = array();
      if ($options['statement_name']) {
        // add the fk relationship info to the prepared statement name so that
        // we can prepare the selects run by the recrusive tripal_core_chado_get_foreign_key
        // function.
        $fk_sname = "fk_" . $table . "_" . $field;
        foreach ($value as $k => $v) {
          $fk_sname .= substr($k, 0, 2);
        }
        $foreign_options['statement_name'] = $fk_sname;
      }
      $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value, $foreign_options);
      if (sizeof($results) > 1) {
        watchdog('tripal_core', 'tripal_core_chado_update: 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)), WATCHDOG_ERROR);
      }
      elseif (sizeof($results) < 1) {
        //watchdog('tripal_core', 'tripal_core_chado_update: 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)), WATCHDOG_ERROR);
      }
      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
      if ($options['statement_name']) {
        // add the fk relationship info to the prepared statement name so that
        // we can prepare the selects run by the recrusive tripal_core_chado_get_foreign_key
        // function.
        $fk_sname = "fk_" . $table . "_" . $field;
        foreach ($value as $k => $v) {
          $fk_sname .= substr($k, 0, 2);
        }
        $foreign_options['statement_name'] = $fk_sname;
      }
      $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value, $foreign_options);
      if (sizeof($results) > 1) {
        watchdog('tripal_core', 'tripal_core_chado_update: 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)), WATCHDOG_ERROR);
      }
      elseif (sizeof($results) < 1) {
        //watchdog('tripal_core', 'tripal_core_chado_update: 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)), WATCHDOG_ERROR);
      }
      else {
        $update_values[$field] = $results[0];
      }
    }
    else {
      $update_values[$field] = $value;
    }
  }

  // now build the SQL statement
  $sql = 'UPDATE {' . $table . '} SET ';
  $psql = 'UPDATE {' . $table . '} SET ';
  $uargs = array();
  $idatatypes = array();
  $pvalues = array();
  $ivalues = array();
  $i = 1;
  foreach ($update_values as $field => $value) {

    if (strcasecmp($table_desc['fields'][$field]['type'], 'serial') == 0 OR 
      strcasecmp($table_desc['fields'][$field]['type'], 'int') == 0 OR 
      strcasecmp($table_desc['fields'][$field]['type'], 'integer') == 0) {
      if (strcmp($value, '__NULL__') == 0) {
        $sql .= " $field = %s, ";
        $ivalues[] = 'NULL';
        $pvalues[] = '%s';
        $uargs[] = 'NULL';
      }
      else {
        $sql .= " $field = %d, ";
        $ivalues[] = $value;
        $pvalues[] = '%d';
        $uargs[] = $value;
      }
      $idatatypes[] = 'int';

    }
    elseif (strcasecmp($table_desc['fields'][$field]['type'], 'boolean') == 0) {
      $sql .= " $field = %s, ";
      $pvalues[] = '%s';
      if (strcmp($value, '__NULL__') == 0) {
        $ivalues[] = 'NULL';
        $uargs[] = 'NULL';
      }
      else {
        $ivalues[] = $value;
        $uargs[] = $value;
      }
      $idatatypes[] = 'bool';
    }
    elseif (strcasecmp($table_desc['fields'][$field]['type'], 'float') == 0) {
      $sql .= " $field = %s, ";
      $pvalues[] = '%s';
      if (strcmp($value, '__NULL__') == 0) {
        $ivalues[] = 'NULL';
        $uargs[] = 'NULL';
      }
      else {
        $ivalues[] = $value;
        $uargs[] = $value;
      }
      $idatatypes[] = 'numeric';
    }
    else {
      if (strcmp($value, '__NULL__') == 0) {
        $sql .= " $field = %s, ";
        $ivalues[] = 'NULL';
        $uargs[] = 'NULL';
        $pvalues[] = '%s';
      }
      else {
        $sql .= " $field = '%s', ";
        $ivalues[] = $value;
        $uargs[] = $value;
        $pvalues[] = "'%s'";
      }
      $idatatypes[] = 'text';
    }
    $psql .= "$field = \$" . $i . ", ";
    $i++;
  }
  $sql = drupal_substr($sql, 0, -2); // get rid of the trailing comma & space
  $psql = drupal_substr($psql, 0, -2); // get rid of the trailing comma & space

  $sql .= " WHERE ";
  $psql .= " WHERE ";
  foreach ($update_matches as $field => $value) {

    if (strcasecmp($table_desc['fields'][$field]['type'], 'serial') == 0 OR 
      strcasecmp($table_desc['fields'][$field]['type'], 'int') == 0 OR 
      strcasecmp($table_desc['fields'][$field]['type'], 'integer') == 0) {
      if (strcmp($value, '__NULL__') == 0) {
        $sql .= " $field = %s AND ";
        $ivalues[] = 'NULL';
        $uargs[] = 'NULL';
        $pvalues[] = '%s';
      }
      else {
        $sql .= " $field = %d AND ";
        $ivalues[] = $value;
        $uargs[] = $value;
        $pvalues[] = '%s';
      }
      $idatatypes[] = 'int';
    }
    elseif (strcasecmp($table_desc['fields'][$field]['type'], 'boolean') == 0) {
      $sql .= " $field = %s AND ";
      $pvalues[] = '%s';
      if (strcmp($value, '__NULL__') == 0) {
        $ivalues[] = 'NULL';
        $uargs[] = 'NULL';
      }
      else {
        $ivalues[] = $value;
        $uargs[] = $value;
      }
      $idatatypes[] = 'bool';
    }
    elseif (strcasecmp($table_desc['fields'][$field]['type'], 'float') == 0) {
      $sql .= " $field = %s AND ";
      $pvalues[] = '%s';
      if (strcmp($value, '__NULL__') == 0) {
        $ivalues[] = 'NULL';
        $uargs[] = 'NULL';
      }
      else {
        $ivalues[] = $value;
        $uargs[] = $value;
      }
      $idatatypes[] = 'numeric';
    }
    else {
      if (strcmp($value, '__NULL__') == 0) {
        $sql .= " $field = %s AND ";
        $ivalues[] = 'NULL';
        $uargs[] = 'NULL';
        $pvalues[] = '%s';
      }
      else {
        $sql .= " $field = '%s' AND ";
        $ivalues[] = $value;
        $uargs[] = $value;
        $pvalues[] = "'%s'";
      }
      $idatatypes[] = 'text';
    }
    $psql .= "$field = \$" . $i . " AND ";
    $i++;
  }
  $sql = drupal_substr($sql, 0, -4); // get rid of the trailing 'AND'
  $psql = drupal_substr($psql, 0, -4); // get rid of the trailing 'AND'

  // finish constructing the prepared SQL statement
  $psql = "PREPARE " . $options['statement_name'] . " (" . implode(', ', $idatatypes) . ") AS " . $psql;

  // finally perform the update.  If successful, return the updated record
  if ($prepared) {
    // if this is the first time we've run this query
    // then we need to do the prepare, otherwise just execute
    if ($options['is_prepared'] != TRUE and !tripal_core_is_sql_prepared($options['statement_name'])) {
      $status = chado_query($psql);
      if (!$status) {
        watchdog('tripal_core', "tripal_core_chado_update: not able to prepare '%name' statement for: %sql", 
        array('%name' => $options['statement_name'], '%sql' => $sql), WATCHDOG_ERROR);
        return FALSE;
      }
    }
    $sql = "EXECUTE " . $options['statement_name'] . "(" . implode(", ", $pvalues) . ")";
    $result = chado_query($sql, $ivalues);
  }
  // if it's not a prepared statement then insert normally
  else {
    $result = chado_query($sql, $uargs);
  }
  // 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 {
    watchdog('tripal_core', "Cannot update record in $table table.  \nMatch:" . print_r($match, 1) . "\nValues: " . print_r($values, 1), array(), 'WATCHDOG_ERROR');
    return FALSE;
  }

  return FALSE;
}