Add a new table to the Chado schema. This function is simply a wrapper for the db_create_table() function of Drupal, but ensures the table is created inside the Chado schema rather than the Drupal schema. If the table already exists then it will be dropped and recreated using the schema provided. However, it will only drop a table if it exsits in the tripal_custom_tables table. This way the function cannot be used to accidentally alter existing non custom tables. If $skip_if_exists is set then the table is simply added to the tripal_custom_tables and no table is created in Chado.

If you are creating a materialized view do not use this function, but rather use the chado_add_mview(). A materialized view is also considered a custom table and an entry for it will be added to both the tripal_mviews and tripal_custom_tables tables, but only if the chado_add_mview() function is used. The optional $mview_id parameters in this function is intended for use by the chado_add_mview() function when it calls this function to create the table.

Parameters

$table: The name of the table to create.

$schema: A Drupal-style Schema API definition of the table.

$skip_if_exists: Set as TRUE to skip dropping and re-creation of the table if it already exists. This is useful if the table was already created through another means and you simply want to make Tripal aware of the table schema. If the table does not exist it will be created.

$mview_id: Optional. If this custom table is also a materialized view then provide it's mview_id. This paramter is intended only when this function is called by the chado_add_mview() function. When creating a custom table you shouldn't need to use this parameter.

$redirect: Optional (default: TRUE). By default this function redirects back to admin pages. However, when called by Drush we don't want to redirect. This parameter allows this to be used as a true API function.

Return value

TRUE on success, FALSE on failure.

Related topics

File

tripal_chado/api/tripal_chado.custom_tables.api.inc, line 129

Provides an API to manage custom tables in Chado.

Code

function chado_create_custom_table($table, $schema, $skip_if_exists = TRUE, 
$mview_id = NULL, $redirect = TRUE) {

  global $databases;
  $created = 0;
  $recreated = 0;

  $chado_schema = chado_get_schema_name('chado');
  $chado_dot = $chado_schema . '.';

  $transaction = db_transaction();
  try {
    // See if the table entry already exists in the tripal_custom_tables table.
    $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_name = :table_name";
    $results = db_query($sql, array(':table_name' => $table));
    $centry = $results->fetchObject();

    // Check to see if the table already exists in the chado schema.
    $exists = chado_table_exists($table);

    // If the table does not exist then create it.
    if (!$exists) {
      $ret = db_create_table($chado_dot . $table, $schema);
      $created = 1;
    }

    // If the table exists in Chado and in our custom table and
    // skip creation is turned off then drop and re-create the table.
    if ($exists and is_object($centry) and !$skip_if_exists) {

      // Drop the table we'll recreate it with the new schema.
      chado_query('DROP TABLE {' . $table . '}');
      // Remove any 'referring_tables' from the array as the 
      // db_create_table doesn't use that.
      $new_schema = $schema;
      if (array_key_exists('referring_tables', $new_schema)) {
        unset($new_schema['referring_tables']);
      }
      db_create_table($chado_dot . $table, $new_schema);
      $recreated = 1;
    }

    // Add an entry in the tripal_custom_table.
    $record = new stdClass();
    $record->table_name = $table;
    $record->schema = serialize($schema);
    if ($mview_id) {
      $record->mview_id = $mview_id;
    }

    // If an entry already exists then remove it.
    if ($centry) {
      $sql = "DELETE FROM {tripal_custom_tables} WHERE table_name = :table_name";
      db_query($sql, array(':table_name' => $table));
    }
    $success = drupal_write_record('tripal_custom_tables', $record);

    // Now add any foreign key constraints.
    if (($created or !$skip_if_exists) and array_key_exists('foreign keys', $schema)) {

      // Iterate through the foreign keys and add each one.
      $fkeys = $schema['foreign keys'];
      foreach ($fkeys as $fktable => $fkdetails) {
        $relations = $fkdetails['columns'];
        foreach ($relations as $left => $right) {
          $sql = '
            ALTER TABLE {' . $table . '}
              ADD CONSTRAINT ' . $table . '_' . $left . '_fkey FOREIGN KEY (' . $left . ')
              REFERENCES  {' . $fktable . '} (' . $right . ')
              ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
          ';
          chado_query($sql);
        }
      }
    }

    // Add the custom table to the semantic web interface.
    chado_add_semweb_table($table);
  }
  catch (Exception $e) {
    $transaction->rollback();
    $error = $e->getMessage();
    watchdog_exception('tripal_chado', $e);
    drupal_set_message(t("Could not add custom table '%table_name': %message.", 
    array('%table_name' => $table, '%message' => $error)), 'error');
    return FALSE;
  }

  if ($created) {
    drupal_set_message("Custom table, '" . $table . "' ,  created successfully.", 'status');
  }
  elseif ($recreated) {
    drupal_set_message("Custom table, '" . $table . "' ,  re-created successfully.", 'status');
  }
  else {
    drupal_set_message("Custom table, '" . $table . "' , already exists. Table structure not changed, but definition array has been saved.", 'status');
  }

  // Only redirect if asked to. This allows us to not try to redirect when this
  // function is called by Drush.
  if ($redirect) {
    if ($mview_id) {
      drupal_goto('admin/tripal/storage/chado/mviews/');
    }
    else {
      drupal_goto('admin/tripal/storage/chado/custom_tables');
    }
  }

  return TRUE;
}