function tripal_core_chado_delete

2.x tripal_core.DEPRECATED.api.inc tripal_core_chado_delete($table, $match, $options = NULL)
3.x tripal_core.DEPRECATED.inc tripal_core_chado_delete($table, $match, $options = NULL)
1.x tripal_core_chado.api.inc tripal_core_chado_delete($table, $match, $options = NULL)

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

Parameters

$table: The name of the chado table for inserting

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

$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 value

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

Example usage:

Related topics

11 calls to tripal_core_chado_delete()
chado_contact_update in tripal_contact/tripal_contact.module
chado_featuremap_update in tripal_featuremap/tripal_featuremap.module
Update nodes
chado_organism_delete in tripal_organism/tripal_organism.module
Delete organism from both drupal and chado databases. Check dependency before deleting from chado.
chado_pub_update in tripal_pub/tripal_pub.module
tripal_core_delete_property in tripal_core/api/tripal_core_chado.api.inc
Deletes a property for a given base table record using the property name

... See full list

File

tripal_core/api/tripal_core_chado.api.inc, line 858
The Tripal Core API

Code

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

  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;
  }

  // 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();
  }

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

  // get the table description
  $table_desc = tripal_core_get_chado_table_schema($table);
  $fields = $table_desc['fields'];

  // 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 = tripal_core_chado_get_foreign_key($table_desc, $field, $value);
        if (sizeof($results) > 1) {
          watchdog('tripal_core', 'tripal_core_chado_delete: 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)), WATCHDOG_ERROR);
        }
        elseif (sizeof($results) < 1) {
          //watchdog('tripal_core', 'tripal_core_chado_delete: 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)), WATCHDOG_ERROR);
        }
        else {
          $delete_matches[$field] = $results[0];
        }
      }
    }
    else {
      $delete_matches[$field] = $value;
    }
  }

  // now build the SQL statement
  $sql = 'DELETE FROM {' . $table . '} WHERE ';
  $psql = $sql;
  $uargs = array();
  $idatatypes = array();
  $pvalues = array();
  $ivalues = array();
  $dargs = array();
  $void_prepared = 0;
  $i = 1;
  foreach ($delete_matches as $field => $value) {

    // if we have an array values then this is an "IN" clasue.
    // we cannot use prepared statements with these
    if (count($value) > 1) {
      $sql .= "$field IN (" . db_placeholders($value, 'varchar') . ") AND ";
      foreach ($value as $v) {
        $dargs[] = $v;
      }
      $void_prepared = 1;
      continue;
    }

    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 = NULL AND ";
        $ivalues[] = 'NULL';
        $pvalues[] = '%s';
        $uargs[] = 'NULL';
      }
      else {
        $sql .= " $field = %d AND ";
        $ivalues[] = $value;
        $pvalues[] = '%d';
        $uargs[] = $value;
      }
      $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';
    }
    array_push($dargs, $value);
    $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 and !$void_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_delete: 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) . ")";
    $resource = chado_query($sql, $ivalues);
  }
  // if it's not a prepared statement then insert normally
  else {
    $resource = chado_query($sql, $uargs);
  }

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