Provides a generic function for deleting a record(s) from any chado table

Use this function to delete a record(s) in any Chado table. The first argument specifies the table to delete from and the second is an array of values to match for locating the record(s) to be deleted. 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 deleted. 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 all records that match the columns specified and delete them.

@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.

$options: Currently there are no options

Return value

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

Example usage:

Related topics

File

tripal_core/api/tripal_core.chado_query.api.inc, line 805

Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.

Code

function chado_delete_record($table, $match, $options = NULL) {

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

  if (!is_array($match)) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'Cannot pass non array as values for matching.', array());
    return FALSE;
  }
  if (count($match) == 0) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'Cannot pass an empty array as values for matching.', array());
    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();
  }

  $delete_matches = array(); // contains the values for the where clause

  // get the table description
  $table_desc = chado_get_schema($table);
  $fields = $table_desc['fields'];
  if (empty($table_desc)) {
    tripal_report_error('tripal_core', TRIPAL_WARNING, 
    'chado_delete_record; There is no table description for !table_name', 
    array('!table_name' => $table), array('print' => $print_errors)
    );
  }

  // get the values needed for matching in the SQL statement
  foreach ($match as $field => $value) {
    if (is_array($value)) {
      // if the user has specified an array of values to delete rather than
      // FK relationships the keep those in our match
      if (array_values($value) === $value) {
        $delete_matches[$field] = $value;
      }
      else {
        $results = chado_schema_get_foreign_key($table_desc, $field, $value);
        if (sizeof($results) > 1) {
          tripal_report_error('tripal_core', TRIPAL_ERROR, 
          'chado_delete_record: When trying to find record to delete, too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', 
          array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)));
          return FALSE;
        }
        elseif (sizeof($results) < 1) {
          //tripal_report_error('tripal_core', TRIPAL_ERROR, 'chado_delete_record: When trying to find record to delete, no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value,TRUE)));
        }
        else {
          $delete_matches[$field] = $results[0];
        }
      }
    }
    else {
      $delete_matches[$field] = $value;
    }
  }

  // now build the SQL statement
  $sql = 'DELETE FROM {' . $table . '} WHERE ';
  $args = array();
  foreach ($delete_matches as $field => $value) {
    // if we have an array values then this is an "IN" clasue.

    if (count($value) > 1) {
      $sql .= "$field IN (";
      $index = 0;
      foreach ($value as $v) {
        $sql .= ":$field" . $index . ", ";
        $args[":$field" . $index] = $v;
        $index++;
      }
      $sql = drupal_substr($sql, 0, -2); // get rid of trailing ', '
      $sql .= ") AND ";
    }
    else {
      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'

  // finally perform the delete.  If successful, return the updated record
  $result = chado_query($sql, $args);
  if ($result) {
    return TRUE;
  }
  else {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    "Cannot delete record in $table table.  Match:" . print_r($match, 1) . ". Values: " . print_r($values, 1), array());
    return FALSE;
  }
  return FALSE;
}