Provides a generic routine for inserting into any Chado table

Use this function to insert a record into any Chado table. The first argument specifies the table for inserting and the second is an array of values to be inserted. The array is mutli-dimensional such that foreign key lookup values can be specified.

  $values =  array(
    'organism_id' => array(
        'genus' => 'Citrus',
        'species' => 'sinensis',
     ),
    'name' => 'orange1.1g000034m.g',
    'uniquename' => 'orange1.1g000034m.g',
    'type_id' => array (
        'cv_id' => array (
           'name' => 'sequence',
        ),
        'name' => 'gene',
        'is_obsolete' => 0
     ),
  );
  $result = tripal_core_chado_insert('feature',$values);

The above code inserts a record into the feature table. The $values array is nested such that the organism is selected by way of the organism_id foreign key constraint by specifying the genus and species. The cvterm is also specified using its foreign key and the cv_id for the cvterm is nested as well.

Parameters

$table: The name of the chado table for inserting

$values: An associative array containing the values for inserting.

$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.
• skip_validation: TRUE or FALSE. If TRUE will skip all the validation steps and just try to insert as is. This is much faster but results in unhandled non user-friendly errors if the insert fails.
• return_record: by default, the function will return the record but with the primary keys added after insertion. To simply return TRUE on success set this option to FALSE

Return value

On success this function returns the inserted record with the new primary keys added to the returned array. On failure, it returns FALSE.

Example usage:

Related topics

File

tripal_core/api/tripal_core_chado.api.inc, line 105

The Tripal Core API

Code

function tripal_core_chado_insert($table, $values, $options = array()) {

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

  $insert_values = array();

  // 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 (insert): $table\n";
    //debug_print_backtrace();
  }

  if (array_key_exists('skip_validation', $options)) {
    $validate = !$options['skip_validation'];
  }
  else {
    $validate = TRUE;
  }

  // get the table description
  $table_desc = tripal_core_get_chado_table_schema($table);
  if (empty($table_desc)) {
    watchdog('tripal_core', 'tripal_core_chado_insert: There is no table description for !table_name', array('!table_name' => $table), WATCHDOG_WARNING);
  }

  // iterate through the values array and create a new 'insert_values' array
  // that has all the values needed for insert with all foreign relationsihps
  // resolved.
  foreach ($values as $field => $value) {
    // make sure the field is in the table description. If not then return an error
    // message
    if (!array_key_exists($field, $table_desc['fields'])) {
      watchdog('tripal_core', "tripal_core_chado_insert: The field '%field' does not exist " .
        "for the table '%table'.  Cannot perform insert. Values: %array", 
      array('%field' => $field, '%table' => $table, '%array' => print_r($values, 1)), WATCHDOG_ERROR);
      return FALSE;
    }

    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;
      }
      // select the value from the foreign key relationship for this value
      $results = tripal_core_chado_get_foreign_key($table_desc, $field, $value, $foreign_options);

      if (sizeof($results) > 1) {
        watchdog('tripal_core', 'tripal_core_chado_insert: 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_insert: no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), WATCHDOG_ERROR);
      }
      else {
        $insert_values[$field] = $results[0];
      }
    }
    else {
      $insert_values[$field] = $value;
    }
  }

  if ($validate) {

    // check for violation of any unique constraints
    $ukeys = array();
    if (array_key_exists('unique keys', $table_desc)) {
      $ukeys = $table_desc['unique keys'];
    }
    $ukselect_cols = array();
    $ukselect_vals = array();
    if ($ukeys) {
      foreach ($ukeys as $name => $fields) {
        foreach ($fields as $index => $field) {
          // build the arrays for performing a select that will check the contraint
          $ukselect_cols[] = $field;
          if (!array_key_exists($field, $insert_values)) {
            if (array_key_exists('default', $table_desc['fields'][$field])) {
              $ukselect_vals[$field] = $table_desc['fields'][$field]['default'];
            }
          }
          else {
            $ukselect_vals[$field] = $insert_values[$field];
          }
        }
        // now check the constraint
        $coptions = array();
        if ($options['statement_name']) {
          $coptions = array('statement_name' => 'uqsel_' . $table . '_' . $name);
        }
        if (tripal_core_chado_select($table, $ukselect_cols, $ukselect_vals, $coptions)) {
          watchdog('tripal_core', "tripal_core_chado_insert: Cannot insert duplicate record into $table table: " .
          print_r($values, 1), array(), 'WATCHDOG_ERROR');
          return FALSE;
        }
      }
    }

    // if trying to insert a field that is the primary key, make sure it also is unique
    if (array_key_exists('primary key', $table_desc)) {
      $pkey = $table_desc['primary key'][0];
      if (array_key_exists($pkey, $insert_values)) {
        $coptions = array('statement_name' => 'pqsel_' . $table . '_' . $pkey);
        if (tripal_core_chado_select($table, array($pkey), array($pkey => $insert_values[$pkey]), $coptions)) {
          watchdog('tripal_core', "tripal_core_chado_insert: Cannot insert duplicate primary key into $table table: " . print_r($values, 1), array(), 'WATCHDOG_ERROR');
          return FALSE;
        }
      }
    }

    // make sure required fields have a value
    if (!is_array($table_desc['fields'])) {
      $table_desc['fields'] = array();
      watchdog('tripal_core', "tripal_core_chado_insert: %table missing fields: \n %schema", 
      array('%table' => $table, '%schema' => print_r($table_desc, 1)), WATCHDOG_WARNING);

    }
    foreach ($table_desc['fields'] as $field => $def) {
      // a field is considered missing if it cannot be NULL and there is no default
      // value for it or it is of type 'serial'
      if (array_key_exists('NOT NULL', $def) and 
        !array_key_exists($field, $insert_values) and 
        !array_key_exists('default', $def) and 
        strcmp($def['type'], serial) != 0) {
        watchdog('tripal_core', "tripal_core_chado_insert: Field $table.$field cannot be NULL: " .
        print_r($values, 1), array(), 'WATCHDOG_ERROR');
        return FALSE;
      }
    }
  } //end of validation

  // Now build the insert SQL statement
  $ifields = array(); // contains the names of the fields
  $ivalues = array(); // contains the values of the fields
  $itypes = array(); // contains %d/%s placeholders for the sql query
  $iplaceholders = array(); // contains $1/$2 placeholders for the prepare query
  $idatatypes = array(); // contains the data type of the fields (int, text, etc.)
  $i = 1;
  foreach ($insert_values as $field => $value) {
    $ifields[] = $field;
    $ivalues[] = $value;
    $iplaceholders[] = '$' . $i;
    $i++;
    if (strcmp($value, '__NULL__') == 0) {
      $itypes[] = "NULL";
      $idatatypes[] = "NULL";
    }
    elseif (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) {
      $itypes[] = "%d";
      $idatatypes[] = 'int';
    }
    elseif (strcasecmp($table_desc['fields'][$field]['type'], 'boolean') == 0) {
      $itypes[] = "%s";
      $idatatypes[] = 'bool';
    }
    elseif (strcasecmp($table_desc['fields'][$field]['type'], 'float') == 0) {
      $itypes[] = "%s";
      $idatatypes[] = 'numeric';
    }
    else {
      $itypes[] = "'%s'";
      $idatatypes[] = 'text';
    }
  }

  // create the SQL
  $sql = 'INSERT INTO {' . $table . '} (' . implode(", ", $ifields) . ") VALUES (" . implode(", ", $itypes) . ")";

  // if this is a prepared statement then execute it
  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) {
      // prepare the statement
      $psql = "PREPARE " . $options['statement_name'] . " (" . implode(', ', $idatatypes) . ') AS INSERT INTO {' . $table . '} (' . implode(", ", $ifields) . ") VALUES (" . implode(", ", $iplaceholders) . ")";
      $status = tripal_core_chado_prepare($options['statement_name'], $psql, $idatatypes);

      if (!$status) {
        watchdog('tripal_core', "tripal_core_chado_insert: 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(", ", $itypes) . ")";
    $result = tripal_core_chado_execute_prepared($options['statement_name'], $sql, $ivalues);
  }
  // if it's not a prepared statement then insert normally
  else {
    $result = chado_query($sql, $ivalues);
  }

  // if we have a result then add primary keys to return array
  if ($options['return_record'] == TRUE and $result) {
    if (array_key_exists('primary key', $table_desc) and is_array($table_desc['primary key'])) {
      foreach ($table_desc['primary key'] as $field) {
        $sql = '';
        $psql = "PREPARE currval_" . $table . "_" . $field . " AS SELECT CURRVAL('{" . $table . "_" . $field . "_seq}')";
        $is_prepared = tripal_core_chado_prepare("currval_" . $table . "_" . $field, $psql, array());
        $value = '';
        if ($is_prepared) {
          $value = db_result(chado_query("EXECUTE currval_" . $table . "_" . $field));
          if (!$value) {
            watchdog('tripal_core', "tripal_core_chado_insert: not able to retrieve primary key after insert: %sql", 
            array('%sql' => $psql), WATCHDOG_ERROR);
            return FALSE;
          }
        }
        else {
          $sql = "SELECT CURRVAL('{" . $table . "_" . $field . "_seq}')";
          $value = db_result(chado_query($sql));
          if (!$value) {
            watchdog('tripal_core', "tripal_core_chado_insert: not able to retrieve primary key after insert: %sql", 
            array('%sql' => $sql), WATCHDOG_ERROR);
            return FALSE;
          }
        }
        $values[$field] = $value;
      }
    }
    return $values;
  }
  elseif ($options['return_record'] == FALSE and $result) {
    return TRUE;
  }
  else {
    watchdog('tripal_core', "tripal_core_chado_insert: Cannot insert record into '%table': " . print_r($values, 1), 
    array('%table' => $table), 'WATCHDOG_ERROR');
    return FALSE;
  }

  return FALSE;

}