tripal_cv.api.inc

Go to the documentation of this file.

<?php

function tripal_cv_get_cv($select_values) {

  $columns = array(
    'cv_id',
    'name',
    'definition',
  );
  $results = tripal_core_chado_select('cv', $columns, $select_values);
  if (sizeof($results) == 1) {
    return $results[0];
  }
  elseif (empty($results)) {
    watchdog('tripal_cv',
      'tripal_cv_get_cv: No cv matches criteria values:%values',
      array('%values' => print_r($select_values, TRUE)),
      WATCHDOG_WARNING
    );
    return FALSE;
  }
  else {
    watchdog('tripal_cv',
      'tripal_cv_get_cv: 2+ cvs match criteria values:%values',
      array('%values' => print_r($select_values, TRUE)),
      WATCHDOG_WARNING
    );
  }

}

// Purpose: To retrieve a chado cv object
// @param $where_options
//   @code
//        array(
//                          <column_name> => array(
//                            'type' => <type of column: INT/STRING>,
//                            'value' => <the vlaue you want to filter on>,
//                            'exact' => <if TRUE use =; if FALSE use ~>,
//                          )
//        )
// @endcode
//
// @return
//   Chado cv object with all fields from the chado cv table
//
// @ingroup tripal_cv_api
//
//function tripal_cv_get_cv ($where_options)

function tripal_cv_get_cv_by_name($name) {

  $r = tripal_core_chado_select('cv', array('*'), array('name' => $name));

  return $r[0];
}

function tripal_cv_get_cv_by_id($cv_id) {

  $r = tripal_core_chado_select('cv', array('*'), array('cv_id' => $cv_id));

  return $r;
}
function tripal_cv_get_cv_id($cv_name) {

  $sql = "
    SELECT cv_id FROM {cv} WHERE name = '%s'
  ";
  $cv = db_fetch_object(chado_query($sql, $cv_name));

  return $cv->cv_id;
}

function tripal_cv_get_cv_options() {

  $results = tripal_core_chado_select('cv', array('cv_id', 'name'), array());

  $options = array();
  foreach ($results as $r) {
    $options[$r->cv_id] = $r->name;
  }

  return $options;

}

function tripal_cv_get_cvterm_by_name($name, $cv_id = 0, $cv_name = 'tripal') {

  if ($cv_id) {
    $values = array(
       'name' => $name,
       'cv_id' => $cv_id,
    );
    $r = tripal_core_chado_select('cvterm', array('*'), $values);
  }
  elseif ($cv_name) {
    $values = array(
      'name' => $name,
      'cv_id' => array(
        'name' => $cv_name,
      ),
    );
    $r = tripal_core_chado_select('cvterm', array('*'), $values);
  }
  else {
    $values = array(
      'name' => $name,
    );
    $r = tripal_core_chado_select('cvterm', array('*'), $values);
  }

  if (!$r) {
    return FALSE;
  }
  if (count($r) > 0) {
    return FALSE;
  }
  return $r[0];
}

function tripal_cv_get_cvterm_options($cv_id = 0) {

  if ($cv_id > 0) {
    $results = tripal_core_chado_select('cvterm', array('cvterm_id', 'name'), array('cv_id' => $cv_id));
  }
  else {
    $results = tripal_core_chado_select('cvterm', array('cvterm_id', 'name'), array());
  }

  $options = array();
  foreach ($results as $r) {
    $options[$r->cvterm_id] = $r->name;
  }

  return $options;

}

function tripal_cv_update_cvtermpath($cvid, $job_id = NULL) {
  // TODO: need better error checking in this function

  // first get the controlled vocabulary name:
  $cv = db_fetch_object(chado_query("SELECT * FROM {cv} WHERE cv_id = %d", $cvid));
  print "\nUpdating cvtermpath for $cv->name...\n";

  // now fill the cvtermpath table
  // @coder-ignore: using a function rather then tablename therefore table prefixing doesn't apply
  $sql = "SELECT * FROM fill_cvtermpath('%s')";
  $success = chado_query($sql, $cv->name); 

  return TRUE;
}

function tripal_cv_add_cv($name, $definition) {
  
  // insert/update values
  $ins_values = array(
    'name'       => $name,
    'definition' => $definition
  );
  
  // see if the CV (default-namespace) exists already in the database
  $sel_values = array('name' => $name);
  $sel_options = array('statement_name' => 'sel_cv_na');
  $results = tripal_core_chado_select('cv', array('*'), $sel_values, $sel_options);

  // if it does not exists then add it
  if (count($results) == 0) {    
    $ins_options = array('statement_name' => 'ins_cv_nade');
    $success = tripal_core_chado_insert('cv', $ins_values, $ins_options);
    if (!$success) {
      watchdog('tripal_cv', "Failed to create the CV record", NULL, WATCHDOG_WARNING);
      return FALSE;
    }
    $results = tripal_core_chado_select('cv', array('*'), $sel_values, $sel_options);
  }
  // if it already exists then do an update
  else {
    $upd_options = array('statement_name' => 'upd_cv_nade');
    $success = tripal_core_chado_update('cv', $sel_values, $ins_values, $upd_options);
    if (!$success) {
      watchdog('tripal_cv', "Failed to update the CV record", NULL, WATCHDOG_WARNING);
      return FALSE;
    }
    $results = tripal_core_chado_select('cv', array('*'), $sel_values, $sel_options);
  }

  // return the cv object
  return $results[0];
}

function tripal_cv_add_cvterm($term, $defaultcv = '_global', $is_relationship = 0, 
  $update = 1, $dbname = 'internal') {
      
  // get the term properties
  $id = $term['id'];
  $name = '';
  $cvname = '';
  $definition = '';
  $is_obsolete = 0;
  $accession = '';
  
  if (array_key_exists('name', $term)) {
    $name = $term['name'];  
  }
  else {
    $name = $id;  
  }

  if (array_key_exists('namespace', $term)) {
    $cvname = $term['namespace'];
  } 
  else {
    $cvname = $defaultcv;
  }
  if (array_key_exists('def', $term)) {
    $definition = preg_replace('/^\"(.*)\"/', '\1', $term['def']);
  }
  else {
    $definition = '';
  }
  if (array_key_exists('is_obsolete', $term)) {
    $is_obsolete = $term['is_obsolete'];
    if (strcmp($is_obsolete, 'true') == 0) {
      $is_obsolete = 1;
    }
  }  
  if (!$name and !$id) {
    watchdog('tripal_cv', "Cannot find cvterm without 'id' or 'name'", NULL, WATCHDOG_WARNING);
    return 0;
  }
  if (!$id) {
    $id = $name;
  }
  
  // get the accession and the database from the cvterm id
  if ($dbname) {
    $accession = $id;
  }
  
  if (preg_match('/^.+?:.*$/', $id)) {
    $accession = preg_replace('/^.+?:(.*)$/', '\1', $id);
    $dbname = preg_replace('/^(.+?):.*$/', '\1', $id);
  }
  
  // check that we have a database name, give a different message if it's a relationship
  if ($is_relationship and !$dbname) {
    watchdog('tripal_cv', "A database name is not provided for this relationship term: $id", NULL, WATCHDOG_WARNING);
    return 0; 
  }
  if (!$is_relationship and !$dbname) {
    watchdog('tripal_cv', "A database identifier is missing from the term: $id", NULL, WATCHDOG_WARNING);
    return 0;
  }
  
  // make sure the CV name exists
  $cv = tripal_cv_add_cv($cvname, '');
  if (!$cv) {
    watchdog('tripal_cv', "Cannot find namespace '$cvname' when adding/updating $id", NULL, WATCHDOG_WARNING);
    return 0;
  }

  // this SQL statement will be used a lot to find a cvterm so just set it
  // here for easy reference below.  Because CV terms can change their names
  // but accessions don't change, the following SQL finds cvterms based on
  // their accession rather than the name
  if (!tripal_core_is_sql_prepared('sel_cvterm_by_accession')) {
    $pcvtermsql = "
      PREPARE sel_cvterm_by_accession(text, text) AS
      SELECT CVT.name, CVT.cvterm_id, CV.cv_id, CV.name as cvname, 
        DB.name as dbname, DB.db_id, DBX.accession 
      FROM cvterm CVT
        INNER JOIN dbxref DBX on CVT.dbxref_id = DBX.dbxref_id
        INNER JOIN db DB on DBX.db_id = DB.db_id
        INNER JOIN cv CV on CV.cv_id = CVT.cv_id
      WHERE DBX.accession = $1 and DB.name = $2";
    if (!tripal_core_chado_prepare('sel_cvterm_by_accession', $pcvtermsql, array('text', 'text'))) {
      watchdog('tripal_cv', "Cannot prepare statement 'sel_cvterm_by_accession'", NULL, WATCHDOG_WARNING);
      return 0; 
    }
  } 
  $cvtermsql = "EXECUTE sel_cvterm_by_accession('%s','%s')";  
  
  // add the database. The function will just return the DB object if the
  // database already exists.
  $db = tripal_db_add_db($dbname);
  if (!$db) {
    watchdog('tripal_cv', "Cannot find database '$dbname' in Chado.", NULL, WATCHDOG_WARNING);
    return 0;
  }

  // the cvterm table has two unique dependencies. We need to check both.
  // first check the (name, cv_id, is_obsolete) constraint
  $values = array(
    'name' => $name,
    'is_obsolete' => $is_obsolete,
    'cv_id' => array(
      'name' => $cvname,
    ),
  );
  $options = array('statement_name' => 'sel_cvterm_c1');
  $result = tripal_core_chado_select('cvterm', array('*'), $values, $options);
  
  // if the constraint is met then let's check it to see if
  // the database name matches the one we have been provided
  if (count($result) == 1) {
    $cvterm = $result[0];
    
    // get the dbxref record
    $values = array('dbxref_id' => $cvterm->dbxref_id);
    $options = array('statement_name' => 'sel_dbxref_id');
    $result = tripal_core_chado_select('dbxref', array('*'), $values, $options);
    $dbxref = $result[0];
    
    // get the db
    $values = array('db_id' => $dbxref->db_id);
    $options = array('statement_name' => 'sel_db_id');
    $result = tripal_core_chado_select('db', array('*'), $values, $options);
    $db_check = $result[0];
    
    // the database name for this existing term does not match that of the 
    // one provided to this function.  The CV name matches otherwise we
    // wouldn't have made it this far. So, let's swap the database for
    // this term
    if ($db_check->name != $db->name) {

      // look to see if the correct dbxref record already exists for this database
      $values = array(
        'db_id' => $db->db_id,
        'accession' => $accession,       
      );
      $options = array('statement_name' => 'sel_dbxref_idac');
      $result = tripal_core_chado_select('dbxref', array('*'), $values, $options);

      // if we already have a good dbxref then we want to update our cvterm 
      // to use this dbxref
      if (count($result) > 0) {
        $dbxref = $result[0];
        $match = array('cvterm_id' => $cvterm->cvterm_id);
        $values = array('dbxref_id' => $dbxref->dbxref_id);
        $options = array('statement_name' => 'upd_cvterm_db');
        $success = tripal_core_chado_update('cvterm', $match, $values, $options); 
        if (!$success) {
          watchdog('tripal_cv', "Failed to correct the dbxref id for the cvterm " .
            "'$name' (id: $accession), for database $dbname", NULL, WATCHDOG_WARNING);
          return 0;
        }
      }
      // if we don't have the record then we want to delete our cvterm and let the code
      // below recreate it with the correct info 
      else {          
        $match = array('cvterm_id' => $cvterm->cvterm_id);
        $options = array('statement_name' => 'del_cvterm_cv');
        tripal_core_chado_delete('cvterm', $match, $options);
      }      
    }
    
    // check that the accession matches.  Sometimes an OBO can define the same term 
    // multiple times but with different accessions.  If this is the case we
    // can't do an insert or it will violate the constraint in the cvterm table.
    // so we'll need to add the record to the cvterm_dbxref table instead
    if ($dbxref->accession != $accession) {
      
      // get/add the dbxref fort his term
      $dbxref_new =  tripal_db_add_dbxref($db->db_id, $accession);
      if (!$dbxref_new) {
        watchdog('tripal_cv', "Failed to find or insert the dbxref record for cvterm, " .
          "$name (id: $accession), for database $dbname", NULL, WATCHDOG_WARNING);
        return 0;
      }
      
      // check to see if the cvterm_dbxref record already exists
      $values = array(
        'cvterm_id' => $cvterm->cvterm_id,
        'dbxref_id' => $dbxref_new->dbxref_id,
        'is_for_definition' => 1,
      );
      $options = array('statement_name' => 'sel_cvtermdbxref_cvdbis');      
      $result = tripal_core_chado_select('cvterm_dbxref', array('*'), $values, $options);
      
      // if the cvterm_dbxref record does not exists then add it 
      if (count($result)==0) {
        $options = array(
          'statement_name' => 'ins_cvtermdbxref_cvdbis',
          'return_record' => FALSE,
        );
        $success = tripal_core_chado_insert('cvterm_dbxref', $values, $options);
        if (!$success) {
          watchdog('tripal_cv', "Failed to find or insert the cvterm_dbxref record for a " . 
            "duplicated cvterm:  $name (id: $accession), for database $dbname", NULL, WATCHDOG_WARNING);
          return 0;
        }
      }        
      // get the original cvterm with the same name and return that.
      $cvterm = db_fetch_object(chado_query($cvtermsql, $dbxref->accession, $dbname));
      return $cvterm;
    }
    
    // continue on, we've fixed the record if the db_id did not match, 
    // we can now perform and updated if we need to.
  }
  
  // get the CVterm record
  $cvterm = db_fetch_object(chado_query($cvtermsql, $accession, $dbname)); 
  //print "$pcvtermsql\n$cvtermsql\n$accession, $dbname\n";
  //print "CVTERM:\n";
  //print_r($cvterm);
  if (!$cvterm) {

    // check to see if the dbxref exists if not, add it
    $dbxref =  tripal_db_add_dbxref($db->db_id, $accession);
    if (!$dbxref) {
      watchdog('tripal_cv', "Failed to find or insert the dbxref record for cvterm, " .
        "$name (id: $accession), for database $dbname", NULL, WATCHDOG_WARNING);
      return 0;
    }
    
    // check to see if the dbxref already has an entry in the cvterm table
    // this is the second constraint in the cvterm table
    $values = array('dbxref_id' => $dbxref->dbxref_id);
    $options = array('statement_name' => 'sel_cvterm_db');
    $check = tripal_core_chado_select('cvterm', array('cvterm_id'), $values, $options);
    if (count($check) == 0) {      
      // now add the cvterm
      $ins_values = array(
        'cv_id'                => $cv->cv_id,
        'name'                 => $name,
        'definition'           => $definition,
        'dbxref_id'            => $dbxref->dbxref_id,
        'is_obsolete'          => $is_obsolete,
        'is_relationshiptype'  => $is_relationship, 
      );
      $ins_options = array('statement_name' => 'ins_cvterm_all');
      $success = tripal_core_chado_insert('cvterm', $ins_values, $ins_options);
      if (!$success) {
        if (!$is_relationship) {
          watchdog('tripal_cv', "Failed to insert the term: $name ($dbname)", NULL, WATCHDOG_WARNING);
          return 0;
        }
        else {
          watchdog('tripal_cv', "Failed to insert the relationship term: $name (cv: " . $cvname . " db: $dbname)", NULL, WATCHDOG_WARNING);
          return 0;
        }
      }
    }
    // this dbxref already exists in the cvterm table 
    else {
      watchdog('tripal_cv', "The dbxref already exists for another cvterm record: $name (cv: " . $cvname . " db: $dbname)", NULL, WATCHDOG_WARNING);
      return 0;
    }    
    $cvterm = db_fetch_object(chado_query($cvtermsql, $accession, $dbname));
  }
  // upate the cvterm
  elseif ($update) { 
    $match = array('cvterm_id' => $cvterm->cvterm_id);
    $upd_values = array(
      'name'                => $name,
      'definition'          => $definition,
      'is_obsolete'         => $is_obsolete,
      'is_relationshiptype' => $is_relationship, 
    );
    $upd_options = array('statement_name' => 'upd_cvterm_nadeisis');
    $success = tripal_core_chado_update('cvterm', $match, $upd_values, $upd_options);    
    if (!$success) {
      watchdog('tripal_cv', "Failed to update the term: $name", NULL, WATCHDOG_WARNING);
      return 0;
    }
    $cvterm = db_fetch_object(chado_query($cvtermsql, $accession, $dbname));
  } 
  else {
     // do nothing, we have the cvterm but we don't want to update
  }
  // return the cvterm
  return $cvterm;
}


function tripal_cv_get_custom_tables($table = NULL) {

 if (!$table or strcmp($table, 'tripal_obo_temp')==0) {
    $schema['tripal_obo_temp'] = array(
      'table' => 'tripal_obo_temp',
      'fields' => array(
        'id' => array(
          'type' => 'varchar',
          'length' => '255',
          'not null' => TRUE,
        ),
        'stanza' => array(
          'type' => 'text',
          'not null' => TRUE,
        ),
        'type' => array(
          'type' => 'varchar',
          'length' => '50',
          'not null' => TRUE,
        ),
      ),
      'indexes' => array(
        'tripal_obo_temp_idx0' => array('id'),
        'tripal_obo_temp_idx0' => array('type'),
      ),
      'unique keys' => array(
        'tripal_obo_temp_uq0' => array('id'),
      ),
    );
  }
  return $schema;
}