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 tripal_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 tripal_add_mview() function is used. The optional $mview_id parameters in this function is intended for use by the tripal_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 tripal_add_mview() function. When creating a custom table you shouldn't need to use this parameter.

Return value

TRUE on success, FALSE on failure

Related topics

File

tripal_core/api/tripal_core.custom_tables.api.inc, line 121

Provides an API to manage custom tables in Chado.

Code

function chado_create_custom_table($table, $schema, $skip_if_exists = 1, $mview_id = NULL) {
  global $databases;
  $created = 0;
  $recreated = 0;
  $chado_schema = tripal_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);
        }
      }
    }
  }
  catch (Exception $e) {
    $transaction->rollback();
    watchdog_exception('tripal_core', $e);
    $error = _drupal_decode_exception($e);
    drupal_set_message(t("Could not add custom table '%table_name': %message.", 
    array('%table_name' => $table, '%message' => $error['!message'])), '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');
  }
  return TRUE;
}