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_creation is set then the table is simply added to the tripal_custom_tables and no table is created in Chado.

Parameters

$ret: Array to which query results will be added.

$table: The name of the table to create.

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

$skip_creation: 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.

Return value

A database query result resource for the new table, or FALSE if table was not constructed.

Related topics

File

tripal_core/api/tripal_core_custom_tables.api.inc, line 106

Contains functions for the Custom Tables API

Code

function tripal_core_create_custom_table(&$ret, $table, $schema, $skip_creation = 1) {
  $ret = array();

  // see if the table entry already exists in the tripal_custom_tables table.
  $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_name = '%s'";
  $centry = db_fetch_object(db_query($sql, $table));

  // check to see if the table already exists in the chado schema  
  $previous_db = tripal_db_set_active('chado'); // use chado database
  $exists = db_table_exists($table);
  tripal_db_set_active($previous_db); // now use drupal database


  // if the table does not exist then create it
  if (!$exists) {
    $previous_db = tripal_db_set_active('chado'); // use chado database
    db_create_table($ret, $table, $schema);
    tripal_db_set_active($previous_db); // now use drupal database
    if (count($ret) == 0) {
      watchdog('tripal_core', "Error adding custom table '!table_name'.", 
      array('!table_name' => $table), WATCHDOG_ERROR);
      return FALSE;
    }
  }

  // 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_creation) {

    // drop the table we'll recreate it with the new schema
    $previous_db = tripal_db_set_active('chado'); // use chado database
    db_drop_table($ret, $table);
    db_create_table($ret, $table, $schema);
    tripal_db_set_active($previous_db); // now use drupal database
  }

  // add an entry in the tripal_custom_table
  $record = new stdClass();
  $record->table_name = $table;
  $record->schema = serialize($schema);

  // if an entry already exists then remove it
  if ($centry) {
    $sql = "DELETE FROM {tripal_custom_tables} WHERE table_name = '%s'";
    db_query($sql, $table);
  }
  $success = drupal_write_record('tripal_custom_tables', $record);
  if (!$success) {
    watchdog('tripal_core', "Error adding custom table %table_name.", 
    array('%table_name' => $table), WATCHDOG_ERROR);
    drupal_set_message(t("Could not add custom table %table_name. 
      Please check the schema array.", array('%table_name' => $table)), 'error');
    return FALSE;
  }

  // now add any foreign key constraints
  if (!$skip_creation and array_key_exists('foreign keys', $schema)) {
    $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";
        if (!chado_query($sql)) {
          watchdog('tripal_core', "Error, could not add foreign key contraint to custom table.", 
          array('!table_name' => $table), WATCHDOG_ERROR);
          drupal_set_message(t("Could not add foreign key contraint to table %table_name. 
            Please check the schema array and the report log for errors.", 
          array('%table_name' => $table)), 'error');
          return FALSE;
        }
      }
    }
  }

  return TRUE;
}