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 = chado_insert_record('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:

• 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_query.api.inc, line 259

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

Code

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

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

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

  $insert_values = array();

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

  // get the table description
  $table_desc = chado_get_schema($table);
  if (!$table_desc) {
    tripal_report_error('tripal_core', TRIPAL_WARNING, 
    'chado_insert_record; There is no table description for !table_name', 
    array('!table_name' => $table), array('print' => $print_errors)
    );
    return FALSE;
  }

  // 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'])) {
      tripal_report_error('tripal_core', TRIPAL_ERROR, 
      "chado_insert_record; 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)), 
      array('print' => $print_errors)
      );
      return FALSE;
    }

    if (is_array($value)) {
      // select the value from the foreign key relationship for this value
      $results = chado_schema_get_foreign_key($table_desc, $field, $value);

      if (sizeof($results) > 1) {
        tripal_report_error('tripal_core', TRIPAL_ERROR, 
        'chado_insert_record: Too many records match the criteria supplied for !foreign_key foreign key constraint (!criteria)', 
        array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), 
        array('print' => $print_errors)
        );
        return FALSE;
      }
      elseif (sizeof($results) < 1) {
        tripal_report_error('tripal_core', TRIPAL_DEBUG, 
        'chado_insert_record: no record matches criteria supplied for !foreign_key foreign key constraint (!criteria)', 
        array('!foreign_key' => $field, '!criteria' => print_r($value, TRUE)), 
        array('print' => $print_errors)
        );
        return FALSE;
      }
      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
        if (chado_select_record($table, $ukselect_cols, $ukselect_vals)) {
          tripal_report_error('tripal_core', TRIPAL_ERROR, 
          "chado_insert_record; Cannot insert duplicate record into $table table: !values", 
          array('!values' => print_r($values, TRUE)), array('print' => $print_errors)
          );
          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();
        if (chado_select_record($table, array($pkey), array($pkey => $insert_values[$pkey]), $coptions)) {
          tripal_report_error('tripal_core', TRIPAL_ERROR, 
          'chado_insert_record; Cannot insert duplicate primary key into !table table: !values', 
          array('!table' => $table, '!values' => print_r($values, TRUE)), 
          array('print' => $print_errors)
          );
          return FALSE;
        }
      }
    }

    // make sure required fields have a value
    if (!is_array($table_desc['fields'])) {
      $table_desc['fields'] = array();
      tripal_report_error('tripal_core', TRIPAL_WARNING, 
      "chado_insert_record; %table missing fields: \n %schema", 
      array('%table' => $table, '%schema' => print_r($table_desc, 1)), 
      array('print' => $print_errors)
      );
    }
    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) {
        tripal_report_error('tripal_core', TRIPAL_ERROR, 
        "chado_insert_record; Field %table.%field cannot be NULL: %values", 
        array('%table' => $table, '%field' => $field, '%values' => print_r($values, 1)), 
        array('print' => $print_errors)
        );
        return FALSE;
      }
    }
  } //end of validation

  // Now build the insert SQL statement
  $ifields = array(); // contains the names of the fields
  $itypes = array(); // contains placeholders for the sql query
  $ivalues = array(); // contains the values of the fields
  $i = 1;
  foreach ($insert_values as $field => $value) {
    $ifields[] = $field;
    $ivalues[":$field"] = $value;
    $i++;
    if (strcmp($value, '__NULL__') == 0) {
      $itypes[] = "NULL";
    }
    else {
      $itypes[] = ":$field";
    }
  }

  // create the SQL
  $sql = 'INSERT INTO {' . $table . '} (' . implode(", ", $ifields) . ") VALUES (" . implode(", ", $itypes) . ")";
  $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 = "SELECT CURRVAL('{" . $table . "_" . $field . "_seq}')";
        $results = chado_query($sql);
        $value = $results->fetchField();
        if (!$value) {
          tripal_report_error('tripal_core', TRIPAL_ERROR, 
          "chado_insert_record; not able to retrieve primary key after insert: %sql", 
          array('%sql' => $sql), 
          array('print' => $print_errors)
          );
          return FALSE;
        }
        $values[$field] = $value;
      }
    }
    return $values;
  }
  elseif ($options['return_record'] == FALSE and $result) {
    return TRUE;
  }
  else {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'chado_insert_record; Cannot insert record into "%table": %values', 
    array('%table' => $table, '%values' => print_r($values, 1)), 
    array('print' => $print_errors)
    );
    return FALSE;
  }

  return FALSE;

}