tripal_core_custom_tables.api.inc

Go to the documentation of this file.

<?php
function tripal_core_edit_custom_table($table_id, $table_name, $schema, $skip_creation = 1) {

  // Create a new record
  $record = new stdClass();
  $record->table_id = $table_id;
  $record->table_name = $table_name;
  $record->schema = serialize($schema);

  // get the current custom table record
  $sql = "SELECT * FROM {tripal_custom_tables} WHERE table_id = %d";
  $custom_table = db_fetch_object(db_query($sql, $table_id));
  
  // if the user changed the table name, we want to drop the old one and force
  // creation of the new one.
  if ($custom_table->table_name != $table_name) {
    chado_query("DROP TABLE %s", $custom_table->table_name);  
    $skip_creation = 0; // we want to create the table
  }

  // if skip creation is not set, then drop the table from chado if it exists
  if(!$skip_creation){
    if (db_table_exists($custom_table->table_name)) {
      chado_query("DROP TABLE %s", $custom_table->table_name);
      drupal_set_message(t("Custom Table '%name' dropped", array('%name' => $custom_table->table_name)));
    }
  }

  // update the custom table record and re-create the table in Chado
  if (drupal_write_record('tripal_custom_tables', $record, 'table_id')) {

    // drop the table from chado if it exists
    if(!$skip_creation){
      if (db_table_exists($custom_table->table_name)) {
        chado_query("DROP TABLE %s", $custom_table->table_name);
        drupal_set_message(t("Custom Table '%name' dropped", array('%name' => $custom_table->table_name)));
      }

      // re-create the table
      if (!tripal_core_create_custom_table ($ret, $table_name, $schema)) {
        drupal_set_message(t("Could not create the custom table. Check Drupal error report logs."));
      }
      else {
        drupal_set_message(t("Custom table '%name' created", array('%name' => $table_name)));
      }
    }
    // TODO: add FK constraints
  }
}

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;
}
function tripal_custom_tables_get_table_id($table_name) {
  $sql = "SELECT * FROM {tripal_custom_tables} ".
         "WHERE table_name = '%s'";
  if (db_table_exists('tripal_custom_tables')) {
    $custom_table = db_fetch_object(db_query($sql, $table_name));
    if ($custom_table) {
      return $custom_table->table_id;
    }
  }

  return FALSE;
}