tripal/tripal_feature/gff_loader.php

<?php
/**
 * @file
 * @todo Add file header description
 */


/**
 * @defgroup gff3_loader GFF3 Feature Loader
 * @{
 * Provides gff3 loading functionality. Creates features based on their specification in a GFF3 file.
 * @}
 * @ingroup tripal_feature
 */
// TODO: The rank column on the feature_relationship table needs to be used to
//       make sure the ordering of CDS (exons) is correct.

// The entries in the GFF file are not in order so the order of the relationships
// is not put in correctly.
/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_gff3_load_form() {

  $form['gff_file']= array(
    '#type'          => 'textfield',
    '#title'         => t('GFF3 File'),
    '#description'   => t('Please enter the full system path for the GFF file, or a path within the Drupal
                           installation (e.g. /sites/default/files/xyz.gff).  The path must be accessible to the
                           server on which this Drupal instance is running.'),
    '#required' => TRUE,
    '#weight'        => 1
  );
  // get the list of organisms
  $sql = "SELECT * FROM {organism} ORDER BY genus, species";
  $previous_db = tripal_db_set_active('chado');  // use chado database
  $org_rset = db_query($sql);
  tripal_db_set_active($previous_db);  // now use drupal database
  $organisms = array();
  $organisms[''] = '';
  while ($organism = db_fetch_object($org_rset)) {
    $organisms[$organism->organism_id] = "$organism->genus $organism->species ($organism->common_name)";
  }
  $form['organism_id'] = array(
    '#title'       => t('Organism'),
    '#type'        => t('select'),
    '#description' => t("Choose the organism to which these sequences are associated"),
    '#required'    => TRUE,
    '#options'     => $organisms,
  );
  $form['import_options'] = array(
    '#type' => 'fieldset',
    '#title' => t('Import Options'),
    '#weight' => 6,
    '#collapsed' => TRUE
  );
  $form['import_options']['add_only']= array(
    '#type' => 'checkbox',
    '#title' => t('Import only new features'),
    '#required' => FALSE,
    '#description' => t('The job will skip features in the GFF file that already
                         exist in the database and import only new features.'),
    '#weight' => 2
  );
  $form['import_options']['update']= array(
    '#type' => 'checkbox',
    '#title' => t('Import all and update'),
    '#required' => FALSE,
    '#default_value' => 'checked',
    '#description' => t('Existing features will be updated and new features will be added.  Attributes
                         for a feature that are not present in the GFF but which are present in the
                         database will not be altered.'),
    '#weight' => 3
  );
  $form['import_options']['refresh']= array(
    '#type' => 'checkbox',
    '#title' => t('Import all and replace'),
    '#required' => FALSE,
    '#description' => t('Existing features will be updated and feature properties not
                         present in the GFF file will be removed.'),
    '#weight' => 4
  );
  $form['import_options']['remove']= array(
    '#type' => 'checkbox',
    '#title' => t('Delete features'),
    '#required' => FALSE,
    '#description' => t('Features present in the GFF file that exist in the database
                         will be removed rather than imported'),
    '#weight' => 5
  );

  $form['analysis'] = array(
    '#type' => 'fieldset',
    '#title' => t('Analysis Used to Derive Features'),
    '#weight' => 6,
    '#collapsed' => TRUE
  );
  $form['analysis']['desc'] = array(
    '#type' => 'markup',
    '#value' => t("Why specify an analysis for a data load?  All data comes
       from some place, even if downloaded from Genbank. By specifying
       analysis details for all data uploads, it allows an end user to reproduce the
       data set, but at least indicates the source of the data."),
  );

  // get the list of analyses
  $sql = "SELECT * FROM {analysis} ORDER BY name";
  $previous_db = tripal_db_set_active('chado');  // use chado database
  $org_rset = db_query($sql);
  tripal_db_set_active($previous_db);  // now use drupal database
  $analyses = array();
  $analyses[''] = '';
  while ($analysis = db_fetch_object($org_rset)) {
    $analyses[$analysis->analysis_id] = "$analysis->name ($analysis->program $analysis->programversion, $analysis->sourcename)";
  }
  $form['analysis']['analysis_id'] = array(
   '#title'       => t('Analysis'),
   '#type'        => t('select'),
   '#description' => t("Choose the analysis to which these features are associated"),
   '#required'    => TRUE,
   '#options'     => $analyses,
  );

  $form['button'] = array(
    '#type' => 'submit',
    '#value' => t('Import GFF3 file'),
    '#weight' => 10,
  );


  return $form;
}

/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_gff3_load_form_validate($form, &$form_state) {

  $gff_file = $form_state['values']['gff_file'];
  $organism_id = $form_state['values']['organism_id'];
  $add_only = $form_state['values']['add_only'];
  $update   = $form_state['values']['update'];
  $refresh  = $form_state['values']['refresh'];
  $remove   = $form_state['values']['remove'];

  // check to see if the file is located local to Drupal
  $dfile = $_SERVER['DOCUMENT_ROOT'] . base_path() . $gff_file;
  if (!file_exists($dfile)) {
    // if not local to Drupal, the file must be someplace else, just use
    // the full path provided
    $dfile = $gff_file;
  }
  if (!file_exists($dfile)) {
    form_set_error('gff_file', t("Cannot find the file on the system. Check that the file exists or that the web server has permissions to read the file."));
  }

  // @coder-ignore: there are no functions being called here
  // @todo: break each line of this conditional into separate variables to make more readable
  if (($add_only AND ($update OR $refresh OR $remove)) OR
    ($update AND ($add_only OR $refresh OR $remove)) OR
    ($refresh AND ($update OR $add_only OR $remove)) OR
    ($remove AND ($update OR $refresh OR $add_only))) {
      form_set_error('add_only', t("Please select only one checkbox from the import options section"));
  }
}

/**
 *
 * @ingroup gff3_loader
 */
function tripal_feature_gff3_load_form_submit($form, &$form_state) {
  global $user;

  $gff_file = $form_state['values']['gff_file'];
  $organism_id = $form_state['values']['organism_id'];
  $add_only = $form_state['values']['add_only'];
  $update   = $form_state['values']['update'];
  $refresh  = $form_state['values']['refresh'];
  $remove   = $form_state['values']['remove'];
  $analysis_id = $form_state['values']['analysis_id'];

  $args = array($gff_file, $organism_id, $analysis_id, $add_only, $update, $refresh, $remove);
  $type = '';
  if ($add_only) {
    $type = 'import only new features';
  }
  if ($update) {
    $type = 'import all and update';
  }
  if ($refresh) {
    $type = 'import all and replace';
  }
  if ($remove) {
    $type = 'delete features';
  }
  tripal_add_job("$type GFF3 file $gff_file", 'tripal_feature',
    'tripal_feature_load_gff3', $args, $user->uid);

  return '';
}

/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3($gff_file, $organism_id, $analysis_id, $add_only =0, $update = 0, $refresh = 0, $remove = 0, $job = NULL) {

  // this array is used to cache all of the features in the GFF file and
  // used to lookup parent and target relationships
  $gff_features = array();

  // check to see if the file is located local to Drupal
  $dfile = $_SERVER['DOCUMENT_ROOT'] . base_path() . $gff_file;
  if (!file_exists($dfile)) {
    // if not local to Drupal, the file must be someplace else, just use
    // the full path provided
    $dfile = $gff_file;
  }
  if (!file_exists($dfile)) {
    print "ERROR: cannot find the file: $dfile\n";
    return 0;
  }

  $previous_db = tripal_db_set_active('chado');
  print "Opening $gff_file\n";

  //$lines = file($dfile,FILE_SKIP_EMPTY_LINES);
  $fh = fopen($dfile, 'r');
  if (!$fh) {
    print "ERROR: cannot open file: $dfile\n";
    return 0;
  }
  $filesize = filesize($dfile);

  // get the controlled vocaubulary that we'll be using.  The
  // default is the 'sequence' ontology
  // @coder-ignore: non-drupal schema thus table prefixing does not apply
  $sql = "SELECT * FROM cv WHERE name = '%s'";
  $cv = db_fetch_object(db_query($sql, 'sequence'));
  if (!$cv) {
    print "ERROR:  cannot find the 'sequence' ontology\n";
    return '';
  }

  // get the organism for which this GFF3 file belongs
  // @coder-ignore: non-drupal schema thus table prefixing does not apply
  $sql = "SELECT * FROM organism WHERE organism_id = %d";
  $organism = db_fetch_object(db_query($sql, $organism_id));

  $interval = intval($filesize * 0.01);
  if ($interval == 0) {
    $interval = 1;
  }
  $in_fasta = 0;
  //   foreach ($lines as $line_num => $line) {
  $line_num = 0;
  $num_read = 0;

  while ($line = fgets($fh)) {

    $line_num++;
    $num_read += drupal_strlen($line);

    // update the job status every 1% features
    if ($job and $num_read % $interval == 0) {
      tripal_job_set_progress($job, intval(($num_read/$filesize)*100));
    }
    // check to see if we have FASTA section, if so then set the variable
    // to start parsing
    if (preg_match('/^##FASTA/i', $line)) {
      $in_fasta = 1;
      break;
    }
    // skip comments
    if (preg_match('/^#/', $line)) {
      continue;
    }
    // skip empty lines
    if (preg_match('/^\s*$/', $line)) {
      continue;
    }


    // handle FASTA section

    // TODO: handle URL encoding

    // remove URL encoding and get the columns
    $cols = explode("\t", $line);
    if (sizeof($cols) != 9) {
      print "ERROR: improper number of columns on line $line_num\n";
      print_r($cols);
      return '';
    }
    // get the column values
    $landmark = $cols[0];
    $source = $cols[1];
    $type = $cols[2];
    $start = $cols[3];
    $end = $cols[4];
    $score = $cols[5];
    $strand = $cols[6];
    $phase = $cols[7];
    $attrs = explode(";", $cols[8]);  // split by a semi-colon

    // ready the start and stop for chado.  Chado expects these positions
    // to be zero-based, so we substract 1 from the fmin
    $fmin = $start - 1;
    $fmax = $end;
    if ($end < $start) {
      $fmin = $end - 1;
      $fmax = $start;
    }
    // format the strand for chado
    if (strcmp($strand, '.')==0) {
      $strand = 0;
    }
    elseif (strcmp($strand, '+')==0) {
      $strand = 1;
    }
    elseif (strcmp($strand, '-')==0) {
      $strand = -1;
    }
    if (strcmp($phase, '.')==0) {
      $phase = '';
    }

    // get the type record
    $cvtermsql = "SELECT CVT.cvterm_id, CVT.cv_id, CVT.name, CVT.definition,
                     CVT.dbxref_id, CVT.is_obsolete, CVT.is_relationshiptype
                  FROM {cvterm} CVT
                     INNER JOIN {cv} CV on CVT.cv_id = CV.cv_id
                     LEFT JOIN {cvtermsynonym} CVTS on CVTS.cvterm_id = CVT.cvterm_id
                  WHERE CV.cv_id = %d and (CVT.name = '%s' or CVTS.synonym = '%s')";
    $cvterm = db_fetch_object(db_query($cvtermsql, $cv->cv_id, $type, $type));
    if (!$cvterm) {
      print "ERROR: cannot find ontology term '$type' on line $line_num.\n";
      return '';
    }

    // break apart each of the attributes
    $tags = array();
    $attr_name = '';
    $attr_uniquename = '';
    $attr_residue_info = '';
    $attr_locgroup = 0;
    $attr_fmin_partial = 'f';
    $attr_fmax_partial = 'f';
    $attr_is_obsolete = 'f';
    $attr_is_analysis = 'f';
    $attr_others = '';
    $residues = '';
    foreach ($attrs as $attr) {
      $attr = rtrim($attr);
      $attr = ltrim($attr);
      if (strcmp($attr, '')==0) {
        continue;
      }
      if (!preg_match('/^[^\=]+\=[^\=]+$/', $attr)) {
        print "ERROR: attribute is not correctly formatted on line $line_num: $attr\n";
        return '';
      }

      // break apart each tag
      $tag = explode("=", $attr);  // split by equals sign
      // multiple instances of an attribute are separated by commas
      $tags[$tag[0]] = explode(",", $tag[1]);  // split by comma
      if (strcmp($tag[0], 'ID')==0) {
        $attr_uniquename = $tag[1];
      }
      elseif (strcmp($tag[0], 'Name')==0) {
        $attr_name = $tag[1];
      }
      // get the list of other attributes other than those reserved ones.
      elseif (strcmp($tag[0], 'Alias')!=0        and strcmp($tag[0], 'Parent')!=0 and
        strcmp($tag[0], 'Target')!=0       and strcmp($tag[0], 'Gap')!=0 and
        strcmp($tag[0], 'Derives_from')!=0 and strcmp($tag[0], 'Note')!=0 and
        strcmp($tag[0], 'Dbxref')!=0       and strcmp($tag[0], 'Ontology_term')!=0 and
        strcmp($tag[0], 'Is_circular')!=0) {
          $attr_others[$tag[0]] = $tag[1];
      }
    }

    // if neither name nor uniquename are provided then generate one
    if (!$attr_uniquename and !$attr_name) {
      if (array_key_exists('Parent', $tags)) {
        $attr_uniquename = $tags['Parent'][0] . "-$type-$landmark:$fmin..$fmax";
      }
      else {
        print "ERROR: cannot generate a uniquename for feature on line $line_num\n";
        exit;
      }
      $attr_name = $attr_uniquename;
    }

    // if a name is not specified then use the unique name
    if (strcmp($attr_name, '')==0) {
      $attr_name = $attr_uniquename;
    }

    // if an ID attribute is not specified then use the attribute name and
    // hope for the best
    if (!$attr_uniquename) {
      $attr_uniquename = $attr_name;
    }

    // make sure the landmark sequence exists in the database.  We don't
    // know the type of the landmark so we'll hope that it's unique across
    // all types. If not we'll error out.  This test is only necessary if
    // if the landmark and the uniquename are different.  If they are the same
    // then this is the information for the landmark
    if (strcmp($landmark, $attr_uniquename)!=0) {
      $feature_sql = "SELECT count(*) as num_landmarks
                   FROM {feature}
                   WHERE organism_id = %d and uniquename = '%s'";
      $count = db_fetch_object(db_query($feature_sql, $organism_id, $landmark));
      if (!$count or $count->num_landmarks == 0) {
        print "ERROR: the landmark '$landmark' cannot be found for this organism. ".
              "Please add the landmark and then retry the import of this GFF3 ".
              "file.\n";
        return '';

      }
      if ($count->num_landmarks > 1) {
        print "ERROR: the landmark '$landmark' is not unique for this organism. ".
              "The features cannot be associated.\n";
        return '';
      }
    }

    // if the option is to remove or refresh then we want to remove
    // the feature from the database.
    if ($remove or $refresh) {
      print "Removing feature '$attr_uniquename'\n";
      $sql = "DELETE FROM {feature}
             WHERE organism_id = %d and uniquename = '%s' and type_id = %d";
      $result = db_query($sql, $organism->organism_id, $attr_uniquename, $cvterm->cvterm_id);
      if (!$result) {
        print "ERROR: cannot delete feature $attr_uniquename\n";
      }
      $feature = 0;
    }

    // add or update the feature and all properties
    if ($update or $refresh or $add_only) {


      // add/update the feature
      print "line $line_num, " . intval(($num_read/$filesize)*100) . "%. ";
      $feature = tripal_feature_load_gff3_feature($organism, $analysis_id, $cvterm,
        $attr_uniquename, $attr_name, $residues, $attr_is_analysis,
        $attr_is_obsolete, $add_only, $score);

      // store all of the features for use later by parent and target
      // relationships
      $gff_features[$feature->uniquename]['type'] = $type;
      $gff_features[$feature->uniquename]['strand'] = $strand;

      if ($feature) {

        // add/update the featureloc if the landmark and the ID are not the same
        // if they are the same then this entry in the GFF is probably a landmark identifier
        if (strcmp($landmark, $attr_uniquename)!=0) {
          tripal_feature_load_gff3_featureloc($feature, $organism,
            $landmark, $fmin, $fmax, $strand, $phase, $attr_fmin_partial,
            $attr_fmax_partial, $attr_residue_info, $attr_locgroup);
        }
        // add any aliases for this feature
        if (array_key_exists('Alias', $tags)) {
          tripal_feature_load_gff3_alias($feature, $tags['Alias']);
        }
        // add any dbxrefs for this feature
        if (array_key_exists('Dbxref', $tags)) {
          tripal_feature_load_gff3_dbxref($feature, $tags['Dbxref']);
        }
        // add any ontology terms for this feature
        if (array_key_exists('Ontology_term', $tags)) {
          tripal_feature_load_gff3_ontology($feature, $tags['Ontology_term']);
        }
        // add parent relationships
        if (array_key_exists('Parent', $tags)) {
          tripal_feature_load_gff3_parents($feature, $cvterm, $tags['Parent'], $gff_features, $organism_id, $fmin);
        }
        // add target relationships
        if (array_key_exists('Target', $tags)) {
          $target = explode(" ", $tags['Target'][0]);
          $target_feature = $target[0];
          $target_start = $target[1];
          $target_end = $target[2];
          $target_dir = $target[3];
          #print "Target: $target_feature, $target_start-$target_end\n";
          tripal_feature_load_gff3_featureloc($feature, $organism,
            $target_feature, $target_start, $target_end, $strand, $phase, $attr_fmin_partial,
            $attr_fmax_partial, $attr_residue_info, $attr_locgroup);
        }
        // add gap information.  This goes in simply as a property
        if (array_key_exists('Gap', $tags)) {
          tripal_feature_load_gff3_property($feature, 'Gap', $tags['Gap'][0]);
        }
        // add notes. This goes in simply as a property
        if (array_key_exists('Note', $tags)) {
          tripal_feature_load_gff3_property($feature, 'Note', $tags['Note'][0]);
        }
        // add the Derives_from relationship (e.g. polycistronic genes).
        if (array_key_exists('Derives_from', $tags)) {
          tripal_feature_load_gff3_derives_from($feature, $tags['Derives_from'][0], $gff_features, $organism);
        }

        // add in the GFF3_source dbxref so that GBrowse can find the feature using the source column
        $source_ref = array('GFF_source:' . $source);
        tripal_feature_load_gff3_dbxref($feature, $source_ref);

        // add any additional attributes
        if ($attr_others) {
          foreach ($attr_others as $property => $value) {
            tripal_feature_load_gff3_property($feature, $property, $value);
          }
        }
      }
    }
  }
  // now set the rank of any parent/child relationships.  The order is based
  // on the fmin.  The start rank is 1.  This allows features with other
  // relationships to be '0' (the default), and doesn't interfer with the
  // ordering defined here.
  foreach ($gff_features as $parent => $details) {
    // only iterate through parents that have children

    if ($details['children']) {
      // get the parent
      $values = array(
        'uniquename' => $parent,
        'type_id' => array(
           'cv_id' => array(
              'name' => 'sequence'
            ),
           'name' => $details['type'],
        ),
        'organism_id' => $organism->organism_id,
      );
      $pfeature = tripal_core_chado_select('feature', array('*'), $values);

      // sort the children by order of their fmin positions (values of assoc. array)
      // if the parent is on the reverse strand then sort in reverse
      if ($details['strand'] == -1) {
        arsort($details['children']);
      }
      else {
        asort($details['children']);
      }

      // now iterate through the children and set their rank
      $rank = 1;
      print "Updating child ranks for $parent (" . $details['type'] . ")\n";
      foreach ($details['children'] as $kfeature_id => $kfmin) {
        $match = array(
           'object_id' => $pfeature[0]->feature_id,
           'subject_id' => $kfeature_id,
           'type_id' => array(
              'cv_id' => array(
                 'name' => 'relationship'
              ),
              'name' => 'part_of',
            ),
        );
        $values = array(
           'rank' => $rank,
        );
        tripal_core_chado_update('feature_relationship', $match, $values);
        $rank++;
      }
      }
  }

  tripal_db_set_active($previous_db);
  return '';
}
/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3_derives_from($feature, $subject, $gff_features, $organism) {

   // first get the subject feature
  $match = array(
  'organism_id' => $organism->organism_id,
  'uniquename' => $subject,
  'type_id' => array(
    'name' => $gff_features[$subject]['type'],
    'cv_id' => array(
      'name' => 'sequence'
    ),
  ),
  );
  $sfeature = tripal_core_chado_select('feature', array('*'), $match);
  if (count($sfeature)==0) {
    print "ERROR: could not add 'Derives_from' relationship for $feature->uniquename and $subject.  Subject feature, '$subject', cannot be found\n";
    return;
  }

   // now check to see if the relationship already exists
  $values = array(
    'object_id' => $sfeature[0]->feature_id,
    'subject_id' => $feature->feature_id,
    'type_id' => array(
       'cv_id' => array(
          'name' => 'relationship'
        ),
       'name' => 'derives_from',
    ),
    'rank' => 0
  );
    $rel = tripal_core_chado_select('feature_relationship', array('*'), $values);
  if (count($rel) > 0) {
    print "   Relationship already exists: $feature->uniquename derives_from $subject\n";
    return;
  }

   // finally insert the relationship if it doesn't exist
  $ret = tripal_core_chado_insert('feature_relationship', $values);
  if (!$ret) {
    print "ERROR: could not add 'Derives_from' relationship for $feature->uniquename and $subject\n";
  }
  else {
    print "   Added relationship: $feature->uniquename derives_from $subject\n";
  }
}
/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3_parents($feature, $cvterm, $parents, &$gff_features, $organism_id, $fmin) {

  $uname = $feature->uniquename;
  $type = $cvterm->name;
  $rel_type = 'part_of';


  // create these SQL statements that will be used repeatedly below.
  $cvtermsql = "SELECT CVT.cvterm_id
               FROM {cvterm} CVT
                  INNER JOIN {cv} CV on CVT.cv_id = CV.cv_id
                  LEFT JOIN {cvtermsynonym} CVTS on CVTS.cvterm_id = CVT.cvterm_id
               WHERE cv.name = '%s' and (CVT.name = '%s' or CVTS.synonym = '%s')";

  $feature_sql = "SELECT * FROM {feature}
                 WHERE organism_id = %d and uniquename = '%s' and type_id = %d";

  // iterate through the parents in the list
  foreach ($parents as $parent) {
    $parent_type = $gff_features[$parent]['type'];

    // try to find the parent
    $parentcvterm = db_fetch_object(db_query($cvtermsql, 'sequence', $parent_type, $parent_type));
    $relcvterm = db_fetch_object(db_query($cvtermsql, 'relationship', $rel_type, $rel_type));
    $parent_feature = db_fetch_object(db_query($feature_sql, $organism_id, $parent, $parentcvterm->cvterm_id));

    // we want to add this feature to the child list for the parent
    // when the loader finishes, it will go back through the parent
    // features and rank the children by position
    $gff_features[$parent]['children'][$feature->feature_id] = $fmin;

    // if the parent exists then add the relationship otherwise print error and skip
    if ($parent_feature) {

      // check to see if the relationship already exists
      $sql = "SELECT * FROM {feature_relationship} WHERE subject_id = %d and object_id = %d and type_id = %d";
      $rel = db_fetch_object(db_query($sql, $feature->feature_id, $parent_feature->feature_id, $relcvterm->cvterm_id));
      if ($rel) {
        print "   Relationship already exists, skipping '$uname' ($type) $rel_type '$parent' ($parent_type)\n";
      }
      else {
        // the relationship doesn't already exist, so add it.
        $sql = "INSERT INTO {feature_relationship} (subject_id,object_id,type_id)
                VALUES (%d,%d,%d)";
        $result = db_query($sql, $feature->feature_id, $parent_feature->feature_id, $relcvterm->cvterm_id);
        if (!$result) {
          print "WARNING: failed to insert feature relationship '$uname' ($type) $rel_type '$parent' ($parent_type)\n";
        }
        else {
          print "   Inserted relationship relationship: '$uname' ($type) $rel_type '$parent' ($parent_type)\n";
        }
      }
    }
    else {
      print "WARNING: cannot establish relationship '$uname' ($type) $rel_type '$parent' ($parent_type): Cannot find the parent\n";
    }
  }
}

/**
 *
 *
 * @ingroup gff3_loader
 */

function tripal_feature_load_gff3_dbxref($feature, $dbxrefs) {

  // iterate through each of the dbxrefs
  foreach ($dbxrefs as $dbxref) {

    // get the database name from the reference.  If it doesn't exist then create one.
    $ref = explode(":", $dbxref);
    $dbname = $ref[0];
    $accession = $ref[1];

    // first look for the database name if it doesn't exist then create one.
    // first check for the fully qualified URI (e.g. DB:<dbname>. If that
    // can't be found then look for the name as is.  If it still can't be found
    // the create the database
    $db = tripal_core_chado_select('db', array('db_id'), array('name' => "DB:$dbname"));
    if (sizeof($db) == 0) {
      $db = tripal_core_chado_select('db', array('db_id'), array('name' => "$dbname"));
    }
    if (sizeof($db) == 0) {
      $ret = tripal_core_chado_insert('db', array('name' => $dbname,
       'description' => 'Added automatically by the GFF loader'));
      if ($ret) {
        print "   Added new database: $dbname\n";
        $db = tripal_core_chado_select('db', array('db_id'), array('name' => "$dbname"));
      }
      else {
        print "ERROR: cannot find or add the database $dbname\n";
        return 0;
      }
    }
    $db = $db[0];

    // now check to see if the accession exists
    $dbxref = tripal_core_chado_select('dbxref', array('dbxref_id'), array(
      'accession' => $accession, 'db_id' => $db->db_id));

    // if the accession doesn't exist then we want to add it
    if (sizeof($dbxref) == 0) {
      $ret = tripal_core_chado_insert('dbxref', array('db_id' => $db->db_id,
        'accession' => $accession, 'version' => ''));
      $dbxref = tripal_core_chado_select('dbxref', array('dbxref_id'), array(
        'accession' => $accession, 'db_id' => $db->db_id));
    }
    $dbxref = $dbxref[0];

    // check to see if this feature dbxref already exists
    $fdbx = tripal_core_chado_select('feature_dbxref', array('feature_dbxref_id'),
      array('dbxref_id' => $dbxref->dbxref_id, 'feature_id' => $feature->feature_id));

    // now associate this feature with the database reference if it doesn't
    // already exist
    if (sizeof($fdbx)==0) {
      $ret = tripal_core_chado_insert('feature_dbxref', array(
        'feature_id' => $feature->feature_id,
        'dbxref_id' => $dbxref->dbxref_id));
      if ($ret) {
        print "   Adding Dbxref $dbname:$accession\n";
      }
      else {
        print "ERROR: failed to insert Dbxref: $dbname:$accession\n";
        return 0;
      }
    }
    else {
      print "   Dbxref already associated, skipping $dbname:$accession\n";
    }
  }
  return 1;
}
/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3_ontology($feature, $dbxrefs) {

   // iterate through each of the dbxrefs
  foreach ($dbxrefs as $dbxref) {

    // get the database name from the reference.  If it doesn't exist then create one.
    $ref = explode(":", $dbxref);
    $dbname = $ref[0];
    $accession = $ref[1];

    // first look for the database name
    $db = tripal_core_chado_select('db', array('db_id'), array('name' => "DB:$dbname"));
    if (sizeof($db) == 0) {
      $db = tripal_core_chado_select('db', array('db_id'), array('name' => "$dbname"));
    }
    if (sizeof($db) == 0) {
      print "ERROR: Database, $dbname is missing for reference: $dbname:$accession\n";
      return 0;
    }
    $db = $db[0];

    // now check to see if the accession exists
    $dbxref = tripal_core_chado_select('dbxref', array('dbxref_id'), array(
       'accession' => $accession, 'db_id' => $db->db_id));
    if (sizeof($dbxref) == 0) {
      print "ERROR: Accession, $accession is missing for reference: $dbname:$accession\n";
      return 0;
    }
    $dbxref = $dbxref[0];

    // now check to see if the cvterm exists
    $cvterm = tripal_core_chado_select('cvterm', array('cvterm_id'), array(
       'dbxref_id' => $dbxref->dbxref_id));
    // if it doesn't exist in the cvterm table, look for an alternate id
    if (sizeof($cvterm) == 0) {
      $cvterm = tripal_core_chado_select('cvterm_dbxref', array('cvterm_id'), array(
        'dbxref_id' => $dbxref->dbxref_id));
    }
    if (sizeof($cvterm) == 0) {
      print "ERROR: CVTerm is missing for reference: $dbname:$accession\n";
      return 0;
    }
    $cvterm = $cvterm[0];


    // check to see if this feature cvterm already exists
    $fcvt = tripal_core_chado_select('feature_cvterm', array('feature_cvterm_id'),
      array('cvterm_id' => $cvterm->cvterm_id, 'feature_id' => $feature->feature_id));

    // now associate this feature with the cvterm if it doesn't already exist
    if (sizeof($fcvt)==0) {
      $values = array(
        'feature_id' => $feature->feature_id,
        'cvterm_id' => $cvterm->cvterm_id,
        'pub_id' => array(
          'uniquename' => 'null',
        ),
      );
      $ret = tripal_core_chado_insert('feature_cvterm', $values);

      if ($ret) {
        print "   Adding ontology term $dbname:$accession\n";
      }
      else {
        print "ERROR: failed to insert ontology term: $dbname:$accession\n";
        return 0;
      }
    }
    else {
      print "   Ontology term already associated, skipping $dbname:$accession\n";
    }
  }
  return 1;
}
/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3_alias($feature, $aliases) {

  // make sure we have a 'synonym_type' vocabulary
  $sql = "SELECT * FROM {cv} WHERE name='synonym_type'";
  $syncv = db_fetch_object(db_query($sql));
  if (!$syncv) {
    $sql = "INSERT INTO {cv} (name,definition) VALUES ('synonym_type','')";
    if (!db_query($sql)) {
      print("ERROR: Failed to add the synonyms type vocabulary");
      return 0;
    }
    $syncv = db_fetch_object(db_query($sql));
  }

  // get the 'exact' cvterm, which is the type of synonym we're adding
  $cvtsql = "
    SELECT * FROM {cvterm} CVT
       INNER JOIN {cv} CV ON CVT.cv_id = CV.cv_id
    WHERE CVT.name = '%s' and CV.name = '%s'
  ";
  $syntype = db_fetch_object(db_query($cvtsql, 'exact', 'synonym_type'));
  if (!$syntype) {
    $term = array(
      'name' => array('exact'),
      'id' => array("internal:exact"),
      'definition' => array(''),
      'is_obsolete' => array(0),
    );
    $syntype = tripal_cv_add_cvterm($term, $syncv->name, 0, 1);
    if (!$syntype) {
      print("Cannot add synonym type: internal:$type");
      return 0;
    }
  }

  // iterate through all of the aliases and add each one
  foreach ($aliases as $alias) {
    print "   Adding Alias $alias\n";

    // check to see if the alias already exists in the synonym table
    // if not, then add it
    $synsql = "SELECT * FROM {synonym}
               WHERE name = '%s' and type_id = %d";
    $synonym = db_fetch_object(db_query($synsql, $alias, $syntype->cvterm_id));
    if (!$synonym) {
      $sql = "INSERT INTO {synonym}
            (name,type_id,synonym_sgml)
           VALUES ('%s',%d,'%s')";
      $result = db_query($sql, $alias, $syntype->cvterm_id, '');
      if (!$result) {
        print "ERROR: cannot add alias $alias to synonym table\n";
      }
    }
    $synonym = db_fetch_object(db_query($synsql, $alias, $syntype->cvterm_id));


    // check to see if we have a NULL publication in the pub table.  If not,
    // then add one.
    // @coder-ignore: non-drupal schema thus table prefixing does not apply
    $pubsql = "SELECT * FROM {pub} WHERE uniquename = 'null'";
    $pub = db_fetch_object(db_query($pubsql));
    if (!$pub) {
      $sql = "INSERT INTO pub (uniquename,type_id) VALUES ('%s',
               (SELECT cvterm_id
                FROM cvterm CVT
                  INNER JOIN dbxref DBX on DBX.dbxref_id = CVT.dbxref_id
                  INNER JOIN db DB on DB.db_id = DBX.db_id
                WHERE CVT.name = 'null' and DB.name = 'null')";
      $result = db_query($sql, 'null');
      if (!$result) {
        print "ERROR: cannot add null publication needed for setup of alias\n";
        return 0;
      }
    }
    $pub = db_fetch_object(db_query($pubsql));

    // check to see if the synonym exists in the feature_synonym table
    // if not, then add it.
    $synsql = "SELECT * FROM {feature_synonym}
               WHERE synonym_id = %d and feature_id = %d and pub_id = %d";
    $fsyn = db_fetch_object(db_query($synsql, $synonym->synonym_id, $feature->feature_id, $pub->pub_id));
    if (!$fsyn) {
      $sql = "INSERT INTO {feature_synonym}
              (synonym_id,feature_id,pub_id)
             VALUES (%d,%d,%d)";
      $result = db_query($sql, $synonym->synonym_id, $feature->feature_id, $pub->pub_id);
      if (!$result) {
        print "ERROR: cannot add alias $alias to feature synonym table\n";
        return 0;
      }
    }
    else {
      print "   Synonym $alias already exists. Skipping\n";
    }
  }
  return 1;
}

/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3_feature($organism, $analysis_id, $cvterm, $uniquename, $name,
  $residues, $is_analysis='f', $is_obsolete='f', $add_only, $score) {

  // check to see if the feature already exists
  $feature_sql = "SELECT * FROM {feature}
                 WHERE organism_id = %d and uniquename = '%s' and type_id = %d";
  $feature = db_fetch_object(db_query($feature_sql, $organism->organism_id, $uniquename, $cvterm->cvterm_id));

  if (strcmp($is_obsolete, 'f')==0) {
    $is_obsolete = 'false';
  }
  if (strcmp($is_analysis, 'f')==0) {
    $is_analysis = 'false';
  }


   // insert the feature if it does not exist otherwise perform an update
  if (!$feature) {
    print "Adding feature '$uniquename' ($cvterm->name)\n";
    $isql = "INSERT INTO {feature} (organism_id, name, uniquename, residues, seqlen,
                md5checksum, type_id,is_analysis,is_obsolete)
             VALUES(%d,'%s','%s','%s',%d, '%s', %d, %s, %s)";
    $result = db_query($isql, $organism->organism_id, $name, $uniquename, $residues, drupal_strlen($residues),
      md5($residues), $cvterm->cvterm_id, $is_analysis, $is_obsolete);
    if (!$result) {
      print "ERROR: failed to insert feature '$uniquename' ($cvterm->name)\n";
      return 0;
    }
  }
  elseif (!$add_only) {
    print "Updating feature '$uniquename' ($cvterm->name)\n";
    $usql = "UPDATE {feature}
             SET name = '%s', residues = '%s', seqlen = '%s', md5checksum = '%s',
                is_analysis = %s, is_obsolete = %s
             WHERE organism_id = %d and uniquename = '%s' and type_id = %d";
    $result = db_query($usql, $name, $residues, drupal_strlen($residues), md5($residues), $is_analysis, $is_obsolete,
      $organism_id, $uniquename, $cvterm->cvterm_id);
    if (!$result) {
      print "ERROR: failed to update feature '$uniquename' ($cvterm->name)\n";
      return 0;
    }
  }
  else {
    // the feature exists and we don't want to update it so return
    // a value of 0.  This will stop all downstream property additions
    print "Skipping existing feature: '$uniquename' ($cvterm->name).\n";
    return 0;
  }

  // get the newly added feature
  $feature = db_fetch_object(db_query($feature_sql, $organism->organism_id, $uniquename, $cvterm->cvterm_id));

  // add the analysisfeature entry to the analysisfeature table if it doesn't already exist
  $af_values = array(
    'analysis_id' => $analysis_id,
    'feature_id' => $feature->feature_id
  );
  $afeature = tripal_core_chado_select('analysisfeature', array('analysisfeature_id'), $af_values, array('has_record'));
  if (count($afeature)==0) {
    // if a score is avaialble then set that to be the significance field
    if (strcmp($score, '.')!=0) {
      $af_values['significance'] = $score;
    }
    if (!tripal_core_chado_insert('analysisfeature', $af_values)) {
      print "ERROR: could not add analysisfeature record: $analysis_id, $feature->feature_id\n";
    }
    else {
      print "   Added analysisfeature record\n";
    }
  }
  else {
    // if a score is available then set that to be the significance field
    $new_vals = array();
    if (strcmp($score, '.')!=0) {
      $new_vals['significance'] = $score;
    }
    else {
      $new_vals['significance'] = '__NULL__';
    }
    if (!$add_only) {
      $ret = tripal_core_chado_update('analysisfeature', $af_values, $new_vals);
      if (!$ret) {
        print "ERROR: could not update analysisfeature record: $analysis_id, $feature->feature_id\n";
      }
      else {
        print "   Updated analysisfeature record\n";
      }
    }
  }
  return $feature;
}

/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3_featureloc($feature, $organism, $landmark, $fmin,
  $fmax, $strand, $phase, $is_fmin_partial, $is_fmax_partial, $residue_info, $locgroup) {

  // get the source feature
  $sql = "SELECT * FROM {feature}
         WHERE organism_id = %d and uniquename = '%s'";
  $srcfeature = db_fetch_object(db_query($sql, $organism->organism_id, $landmark));
  if (!$srcfeature) {
    print "ERROR: cannot find landmark feature $landmark.  Cannot add the feature location record\n";
    return 0;
  }


  // TODO: create an attribute that recognizes the residue_info,locgroup, is_fmin_partial and is_fmax_partial, right now these are
  //       hardcoded to be false and 0 below.


  // check to see if this featureloc already exists, but also keep track of the
  // last rank value
  $rank = 0;
  $exists = 0;
  $featureloc_sql = "SELECT FL.featureloc_id,FL.fmin,FL.fmax,F.uniquename as srcname,
                       rank
                    FROM {featureloc} FL
                      INNER JOIN {feature} F on F.feature_id = FL.srcfeature_id
                    WHERE FL.feature_id = %d
                    ORDER BY rank ASC";
  $recs = db_query($featureloc_sql, $feature->feature_id);
  while ($featureloc = db_fetch_object($recs)) {
    if (strcmp($featureloc->srcname, $landmark)==0 and
      $featureloc->fmin == $fmin and $featureloc->fmax == $fmax) {
        // this is the same featureloc, so do nothing... no need to update
        //TODO: need more checks here
        print "   No change to featureloc\n";
        $exists = 1;
    }
  $rank = $featureloc->rank + 1;
  }
  if (!$exists) {
    $rank++;
    // this feature location is new so add it
    if (!$phase) {
      $phase = 'NULL';
    }
    if (strcmp($is_fmin_partial, 'f')==0) {
      $is_fmin_partial = 'false';
    }
    elseif (strcmp($is_fmin_partial, 't')==0) {
      $is_fmin_partial = 'true';
    }
    if (strcmp($is_fmax_partial, 'f')==0) {
      $is_fmax_partial = 'false';
    }
    elseif (strcmp($is_fmax_partial, 't')==0) {
      $is_fmax_partial = 'true';
    }
    print "   Adding featureloc $srcfeature->uniquename fmin: $fmin, fmax: $fmax, strand: $strand, phase: $phase, rank: $rank\n";
    $fl_isql = "INSERT INTO {featureloc}
                  (feature_id, srcfeature_id, fmin, is_fmin_partial, fmax, is_fmax_partial,
                   strand, phase, residue_info, locgroup, rank)
               VALUES (%d,%d,%d,%s,%d,%s,%d,%s,'%s',%d,%d)";
    $result = db_query($fl_isql, $feature->feature_id, $srcfeature->feature_id, $fmin, $is_fmin_partial, $fmax, $is_fmax_partial,
      $strand, $phase, $residue_info, $locgroup, $rank);
    if (!$result) {
      print "ERROR: failed to insert featureloc\n";
      exit;
      return 0;
    }
  }
  return 1;
}
/**
 *
 *
 * @ingroup gff3_loader
 */
function tripal_feature_load_gff3_property($feature, $property, $value) {
  // first make sure the cvterm exists.  If the term already exists then
  // the function should return it of not, then add it
  $cvt_sql = "SELECT * FROM {cvterm} CVT
             INNER JOIN {cv} CV on CVT.cv_id = CV.cv_id
             WHERE CV.name = '%s' and CVT.name = '%s'";
  $cvterm = db_fetch_object(db_query($cvt_sql, 'feature_property', $property));
  if (!$cvterm) {
    $term = array(
      'id' => "null:$property",
      'name' => $property,
      'namespace' => 'feature_property',
      'is_obsolete' => 0,
    );
    print "   Adding cvterm, $property\n";
    $cvterm = (object) tripal_cv_add_cvterm($term, 'feature_property', 0, 0);
  }

  if (!$cvterm) {
    print "ERROR: cannot add cvterm, $property\n";
    exit;
  }

  // check to see if the property already exists for this feature
  // if it does but the value is unique then increment the rank and add it.
  // if the value is not unique then don't add it.
  $add = 1;
  $rank = 0;
  $sql = "SELECT rank,value FROM {featureprop}
         WHERE feature_id = %d and type_id = %d
         ORDER BY rank ASC";
  $result = db_query($sql, $feature->feature_id, $cvterm->cvterm_id);
  while ($prop = db_fetch_object($result)) {
    if (strcmp($prop->value, $value)==0) {
      $add = NULL; // don't add it, it already exists
      print "   Property already exists, skipping\n";
    }
    $rank = $prop->rank + 1;
  }

  // add the property if we pass the check above
  if ($add) {
    print "   Setting feature property. $property: $value\n";
    $isql = "INSERT INTO {featureprop} (feature_id,type_id,value,rank)
            VALUES (%d,%d,'%s',%d)";
    db_query($isql, $feature->feature_id, $cvterm->cvterm_id, $value, $rank);
  }
}
/*
function tripal_feature_load_gff3_property($feature,$property,$value){
   // first make sure the cvterm exists.  If the term already exists then
   // the function should return it
   $match = array(
      'name' => $property,
      'cv_id' => array(
         'name' => 'feature_property',
      ),
   );
   $cvterm = tripal_core_chado_select('cvterm',array('*'),$match);
   if(sizeof($cvterm) == 0){
      $term = array(
         'id' => "null:$property",
         'name' => $property,
         'namespace' => 'feature_property',
         'is_obsolete' => 0,
      );
      print "   Adding cvterm, $property\n";
      $cvterm = tripal_cv_add_cvterm($term,'feature_property',0,0);
   }

   if(!$cvterm){
      print "ERROR: cannot add cvterm, $property, before adding property\n";
      exit;
   }

   // next give the feature the property
   tripal_core_insert_property('feature',$feature->feature_id,$property,'feature_property',$value,1);
}
 */