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