C++ StatementHandle::execute方法代码示例

Vector *HomologyAdaptor_listStableIdsFromSpecies(HomologyAdaptor *ha, char *sp)  {
  StatementHandle *sth;
  ResultRow *row;
  Vector *genes;
  char qStr[1024];
  char *species;

  species = StrUtil_copyString(&species,sp,0);
  species = StrUtil_strReplChr(species,'_',' ');

  sprintf(qStr,
          "select  distinct grm.member_stable_id "
          " from    gene_relationship_member grm,"
          "         genome_db gd "
          " where   gd.genome_db_id = grm.genome_db_id "
          " and     gd.name = '%s'", species);


  sth = ha->prepare((BaseAdaptor *)ha, qStr, strlen(qStr));
  sth->execute(sth);

  genes = Vector_new();

  while ((row = sth->fetchRow(sth))) {
    char *tmpStr;
    Vector_addElement(genes,StrUtil_copyString(&tmpStr, row->getStringAt(row,0),0));
  }
  sth->finish(sth);

  free(species);

  return genes;
}                               

Chromosome *ChromosomeAdaptor_fetchByChrName(ChromosomeAdaptor *ca, char *chrName) {
  Chromosome *chromosome;
  char qStr[256];
  StatementHandle *sth;
  ResultRow *row;

  if (StringHash_contains(ca->chrNameCache,chrName)) {

    chromosome = StringHash_getValue(ca->chrNameCache, chrName);

  } else {
    sprintf(qStr,"SELECT chromosome_id, name, length"
      " FROM chromosome"
      " WHERE  name = '%s'", chrName);
  
    sth = ca->prepare((BaseAdaptor *)ca,qStr,strlen(qStr));
    sth->execute(sth);
  
    row = sth->fetchRow(sth);
    if( row == NULL ) {
      sth->finish(sth);
      fprintf(stderr, "ERROR: Do not recognise chromosome %s\n",chrName);
      return NULL;
    }
  
    chromosome = ChromosomeAdaptor_chromosomeFromRow(ca, row);
    sth->finish(sth);
  }

  return chromosome;
}

// Removed the circular stuff 
void AttributeAdaptor_doStoreAllByTypeAndTableAndID(AttributeAdaptor *ata, char *type, char *table, IDType objectId, Vector *attributes) {
  int ok = 1;
  char qStr[1024];
  sprintf(qStr, "INSERT into %s_attrib SET %s_id = %"IDFMTSTR", attrib_type_id = %"IDFMTSTR", value = '%%s'", table, type);

  StatementHandle *sth = ata->prepare((BaseAdaptor *)ata,qStr,strlen(qStr));

  int i;
  for (i=0; i<Vector_getNumElement(attributes); i++) {
    Attribute *attrib = Vector_getElementAt(attributes, i);

    if (attrib == NULL ) {
      fprintf(stderr, "Reference to list of Bio::EnsEMBL::Attribute objects argument expected.\n");
      ok = 0;
      break;
    }

    Class_assertType(CLASS_ATTRIBUTE, attrib->objectType);

    IDType atId = AttributeAdaptor_storeType(ata, attrib);

    sth->execute(sth, objectId, atId, Attribute_getValue(attrib));
  }

  if (ok) {
    sth->finish(sth);
  }

  return;
}

Vector *IntronSupportingEvidenceAdaptor_listLinkedTranscriptIds(IntronSupportingEvidenceAdaptor *isea, IntronSupportingEvidence *ise) {
  char qStr[1024];

  sprintf(qStr,"SELECT transcript_id from transcript_intron_supporting_evidence "
               "WHERE intron_supporting_evidence_id = "IDFMTSTR, IntronSupportingEvidence_getDbID(ise));

  StatementHandle *sth = isea->prepare((BaseAdaptor *)isea,qStr,strlen(qStr));
  sth->execute(sth);

  Vector *idVec = Vector_new();
  ResultRow *row;
  while ((row = sth->fetchRow(sth))) {
    IDType id = row->getLongLongAt(row, 0);
    IDType *idP;

    if ((idP = calloc(1,sizeof(IDType))) == NULL) {
      fprintf(stderr, "Failed allocating space for a id\n");
      exit(1);
    }

    *idP = id;
    Vector_addElement(idVec, idP);
  }
  sth->finish(sth);

  Vector_setFreeFunc(idVec, free);
  return idVec;
}

int BaseAdaptor_genericCount(BaseAdaptor *ba, char *constraint) {
  char *qStr = NULL;

  if ((qStr = (char *)calloc(655500,sizeof(char))) == NULL) {
    fprintf(stderr,"Failed allocating qStr\n");
    return 0;
  }

  qStr[0] = '\0';

  BaseAdaptor_generateSql(ba, constraint, countCols, qStr);

  StatementHandle *sth = ba->prepare(ba,qStr,strlen(qStr));
  sth->execute(sth);

  if (sth->numRows(sth) != 1) {
    fprintf(stderr, "genericCount didn't return a row - bye!\n");
    return 0;
  }
  ResultRow *row = sth->fetchRow(sth);
  int count = row->getLongAt(row, 0);

  free(qStr);
  return count;
}

/* 
=head2 store_transcript_linkage

  Arg[1]      : Bio::EnsEMBL::IntronSupportingEvidence Evidence to link
  Arg[2]      : Bio::EnsEMBL::Transcript Transcript to link
  Arg[3]      : Integer an optional ID to give if the Transcript's own ID is possibly incorrect
  Example     : $isea->store_transcript_linkage($ise, $transcript);
                $isea->store_transcript_linkage($ise, $transcript, $tid);
  Description : Links a Transcript to a portion of Intron evidence
  Returntype  : None
  Exceptions  : Thrown if the given object is not a Transcript, if the 
                transcript is not stored, if the supporting evidence is not
                stored and for any DB exception. 

=cut
*/
void IntronSupportingEvidenceAdaptor_storeTranscriptLinkage(IntronSupportingEvidenceAdaptor *isea, 
                                                            IntronSupportingEvidence *sf,
                                                            Transcript *transcript,
                                                            IDType transcriptId) {
 
  if (sf == NULL || transcript == NULL) {
    fprintf(stderr,"sf or transcript is NULL in IntronSupportingEvidenceAdaptor_storeTranscriptLinkage\n");
    exit(1);
  }

  Class_assertType(CLASS_INTRONSUPPORTINGEVIDENCE, sf->objectType);
  Class_assertType(CLASS_TRANSCRIPT, transcript->objectType);
  
  if (! IntronSupportingEvidence_isStored(sf, isea->dba)) {
    fprintf(stderr,"Cannot perform the link. The IntronSupportingEvidence must be persisted first\n");
    exit(1);
  }

// Moved up so can use in sprintf
  Intron *intron = IntronSupportingEvidence_getIntron(sf, transcript);
  Exon *prevExon = Intron_getPrevExon(intron);
  Exon *nextExon = Intron_getNextExon(intron);

  if (!transcriptId) {
    transcriptId = Transcript_getDbID(transcript);
  }

  char qStr[1024];
  sprintf(qStr, "INSERT IGNORE INTO transcript_intron_supporting_evidence "
          "(transcript_id, intron_supporting_evidence_id, previous_exon_id, next_exon_id) "
          "VALUES ("IDFMTSTR","IDFMTSTR","IDFMTSTR","IDFMTSTR")", 
          transcriptId, IntronSupportingEvidence_getDbID(sf), Exon_getDbID(prevExon), Exon_getDbID(nextExon));

  StatementHandle *sth = isea->prepare((BaseAdaptor *)isea,qStr,strlen(qStr));
  
  sth->execute(sth);
  sth->finish(sth);

  Intron_free(intron);
  
  return;
}

IDType HomologyAdaptor_getRelationship(HomologyAdaptor *ha, char *qStr) {
  StatementHandle *sth;
  ResultRow *row;
  IDType relId;
  

  sth = ha->prepare((BaseAdaptor *)ha, qStr, strlen(qStr));
  sth->execute(sth);

  if ((row = sth->fetchRow(sth))) {
    relId = row->getLongLongAt(row,0);
  } else {
    fprintf(stderr, "Error: No relationship found by %s\n",qStr);
    relId = 0;
  }

  sth->finish(sth);

  return relId;
}

/*
=head2 generic_fetch

  Arg [1]    : (optional) string $constraint
               An SQL query constraint (i.e. part of the WHERE clause)
  Arg [2]    : (optional) Bio::EnsEMBL::AssemblyMapper $mapper
               A mapper object used to remap features
               as they are retrieved from the database
  Arg [3]    : (optional) Bio::EnsEMBL::Slice $slice
               A slice that features should be remapped to
  Example    : $fts = $a->generic_fetch('contig_id in (1234, 1235)');
  Description: Performs a database fetch and returns feature objects in
               contig coordinates.
  Returntype : listref of Bio::EnsEMBL::SeqFeature in contig coordinates
  Exceptions : Thrown if there is an issue with querying the data
  Caller     : BaseFeatureAdaptor, ProxyDnaAlignFeatureAdaptor::generic_fetch
  Status     : Stable

=cut
*/
Vector *BaseAdaptor_genericFetch(BaseAdaptor *ba, char *constraint, AssemblyMapper *mapper, Slice *slice) {
  Vector *res = NULL;
  char *qStr = NULL;

  if ((qStr = (char *)calloc(655500,sizeof(char))) == NULL) {
    fprintf(stderr,"Failed allocating qStr\n");
    return res;
  }

  qStr[0] = '\0';

  BaseAdaptor_generateSql(ba, constraint, NULL, qStr);

  StatementHandle *sth = ba->prepare((BaseAdaptor *)ba,qStr,strlen(qStr));

  sth->execute(sth);

  res = ba->objectsFromStatementHandle(ba, sth, mapper, slice);
  sth->finish(sth);

  free(qStr);
  return res;
}

int HomologyAdaptor_getRelationships(HomologyAdaptor *ha, char *qStr, IDType **idsP) {
  StatementHandle *sth;
  ResultRow *row;
  int nAlloced = 2;
  int nId = 0;
  int ok = 1;

  sth = ha->prepare((BaseAdaptor *)ha, qStr, strlen(qStr));
  sth->execute(sth);

  if ((*idsP = (IDType *)calloc(nAlloced,sizeof(IDType))) == NULL) {
    fprintf(stderr,"Error: Failed allocating idsP\n");
    ok = 0;
  }

  if (ok) {
    while ((row = sth->fetchRow(sth))) {
      if (nId == nAlloced) {
        nAlloced = (nAlloced *2);
        if ((*idsP = (IDType *)realloc(*idsP,nAlloced*sizeof(IDType))) == NULL) {
          fprintf(stderr,"Error: Failed reallocating idsP\n");
          ok = 0;
          break;
        }
      }

      (*idsP)[nId++] = row->getLongLongAt(row,0); 
    }
  }

  if (ok) {
    sth->finish(sth);
  }

  return nId;
}

Vector *HomologyAdaptor_getHomologues(HomologyAdaptor *ha, char *qStr) {
  StatementHandle *sth;
  ResultRow *row;
  Vector *genes;

  sth = ha->prepare((BaseAdaptor *)ha, qStr, strlen(qStr));
  sth->execute(sth);

  genes = Vector_new();
  while ((row = sth->fetchRow(sth))) {
    Homology *homol = Homology_new();
    Homology_setSpecies(homol, row->getStringAt(row,1));
    Homology_setStableId(homol, row->getStringAt(row,0));
    Homology_setChromosome(homol, row->getStringAt(row,2));
    Homology_setChrStart(homol, row->getIntAt(row,3));
    Homology_setChrEnd(homol, row->getIntAt(row,4));
        
    Vector_addElement(genes,homol);
  }

  sth->finish(sth);

  return genes;
}

Vector *AttributeAdaptor_doFetchAllByTypeAndTableAndID(AttributeAdaptor *ata, char *type, char *table, IDType objectId, char *code) {
  char qStr[1024];

  sprintf(qStr, "SELECT at.code, at.name, at.description, t.value "
                  "FROM %s_attrib t, attrib_type at "
                 "WHERE at.attrib_type_id = t.attrib_type_id", table);

  if (code != NULL){
    sprintf(qStr,"%s AND at.code like '%s'", qStr, code);
  }

//  if(defined($object_id)){
  sprintf(qStr,"%s AND t.%s_id = "IDFMTSTR, qStr, type, objectId);
//  }
		   
  StatementHandle *sth = ata->prepare((BaseAdaptor *)ata,qStr,strlen(qStr));
  sth->execute(sth);

  Vector *results = AttributeAdaptor_objectsFromStatementHandle(ata, sth);

  sth->finish(sth);

  return results;
}

// This method was a mess - tidied and rearranged
IDType IntronSupportingEvidenceAdaptor_store(IntronSupportingEvidenceAdaptor *isea, IntronSupportingEvidence *sf)  {
  if (sf == NULL) {
    fprintf(stderr,"sf is NULL in IntronSupportingEvidenceAdaptor_store\n");
    exit(1);
  }

  Class_assertType(CLASS_INTRONSUPPORTINGEVIDENCE, sf->objectType);
  
  DBAdaptor *db = isea->dba;
  AnalysisAdaptor *analysisAdaptor = DBAdaptor_getAnalysisAdaptor(db);
  
  if (IntronSupportingEvidence_isStored(sf, db)) {
    fprintf(stderr,"ISE already stored\n");
    return IntronSupportingEvidence_getDbID(sf);
  }
  
  Analysis *analysis = IntronSupportingEvidence_getAnalysis(sf);

  if (!Analysis_isStored(analysis, db)) {
    AnalysisAdaptor_store(analysisAdaptor, analysis);
  }
  IDType analysisId = Analysis_getDbID(analysis);

  // Think the above is equivalent to this horror
  //my $analysis_id = $analysis->is_stored($db) ? $analysis->dbID() : $db->get_AnalysisAdaptor()->store($analysis);
  
/* No transfer (see GeneAdaptor for why)
  my $seq_region_id;
  ($sf, $seq_region_id) = $self->_pre_store($sf);
*/

  IDType seqRegionId = BaseFeatureAdaptor_preStore((BaseFeatureAdaptor *)isea, (SeqFeature*)sf);

  char qStr[1024];
  sprintf(qStr, "INSERT IGNORE INTO intron_supporting_evidence "
                "(analysis_id, seq_region_id, seq_region_start, seq_region_end, seq_region_strand, hit_name, score, score_type, is_splice_canonical) "
                "VALUES ("IDFMTSTR","IDFMTSTR",%ld,%ld,%d,'%s',%f,'%s',%d)", 
                analysisId,
                seqRegionId,
          IntronSupportingEvidence_getSeqRegionStart((SeqFeature*)sf),
          IntronSupportingEvidence_getSeqRegionEnd((SeqFeature*)sf),
          IntronSupportingEvidence_getSeqRegionStrand((SeqFeature*)sf),
                IntronSupportingEvidence_getHitName(sf),
                IntronSupportingEvidence_getScore(sf),
                IntronSupportingEvidence_getScoreType(sf),
                IntronSupportingEvidence_getIsSpliceCanonical(sf));

  StatementHandle *sth = isea->prepare((BaseAdaptor *)isea,qStr,strlen(qStr));
  
  sth->execute(sth);
  IDType sfId = sth->getInsertId(sth);
  sth->finish(sth);

  if (!sfId) {
    sprintf(qStr,"SELECT intron_supporting_evidence_id "
                   "FROM intron_supporting_evidence "
                  "WHERE analysis_id = "IDFMTSTR
                   " AND seq_region_id = "IDFMTSTR
                   " AND seq_region_start = %ld"
                   " AND seq_region_end = %ld" 
                   " AND seq_region_strand = %d"
                   " AND hit_name = '%s'",
                analysisId,
                seqRegionId,
            IntronSupportingEvidence_getSeqRegionStart((SeqFeature*)sf),
            IntronSupportingEvidence_getSeqRegionEnd((SeqFeature*)sf),
            IntronSupportingEvidence_getSeqRegionStrand((SeqFeature*)sf),
                IntronSupportingEvidence_getHitName(sf));

    sth = isea->prepare((BaseAdaptor *)isea,qStr,strlen(qStr));
    sth->execute(sth);
    if (sth->numRows(sth) > 0) {
      ResultRow *row = sth->fetchRow(sth);
      sfId = row->getLongLongAt(row, 0);
    }
    sth->finish(sth);
  }
  
  IntronSupportingEvidence_setAdaptor((SeqFeature*)sf, (BaseAdaptor*)isea);
  IntronSupportingEvidence_setDbID(sf, sfId);

  return IntronSupportingEvidence_getDbID(sf);
}

// For ordered, the default should be 0 (if you just need to fill out the args)
// Note ONLY stable_id can be char, all other pk's must be IDType (see code)
Vector *BaseAdaptor_listDbIDs(BaseAdaptor *ba, char *table, char *pk, int ordered) {
  int ok = 1;
  char colName[1024];

  if (pk == NULL) {
    sprintf(colName, "%s_id", table);
  } else {
    strcpy(colName, pk);
  }

  char qStr[1024];
  sprintf(qStr,"SELECT `%s` FROM `%s`", colName, table );

  if ( BaseAdaptor_isMultiSpecies(BaseAdaptor *ba)
      // For now just the multi species because I don't have adaptors in the Class hierarchy
      // && $self->isa('Bio::EnsEMBL::DBSQL::BaseFeatureAdaptor')
      // && !$self->isa('Bio::EnsEMBL::DBSQL::UnmappedObjectAdaptor') 
     ) {
    char tmpStr[1024];
    
    sprintf(tmpStr, "JOIN seq_region USING (seq_region_id) "
                    "JOIN coord_system cs USING (coord_system_id) "
                    "WHERE cs.species_id = "IDFMTSTR, BaseAdaptor_getSpeciesId(ba));

    sprintf(qStr, "%s %s", qStr, tmpStr);
  }

  if (ordered) {
    sprintf(qStr, "%s ORDER BY seq_region_id, seq_region_start", qStr);
  }

  StatementHandle *sth = ba->prepare(ba,qStr,strlen(qStr));

  sth->execute(sth);

  Vector *out = Vector_new();

  if (strcmp(pk, "stable_id")) {
    ResultRow *row;
    while ((row = sth->fetchRow(sth))) {
      char *stableId = row->getStringCopyAt(row, 0);
  
      Vector_addElement(out, stableId);
    }
  } else  {
    IDType *idP;
    ResultRow *row;
    while ((row = sth->fetchRow(sth))) {
      IDType id = row->getLongLongAt(row, 0);
  
      if ((idP = calloc(1,sizeof(IDType))) == NULL) {
        fprintf(stderr, "Failed allocating space for a id\n");      
        ok = 0;
      } else {
        *idP = id;
        Vector_addElement(out, idP);
      }
    }
  }

  if (!ok) {
    Vector_free(out);
    out = NULL;
  }

  return out;
}