自学教程：C++ sqlDyStringPrintf函数代码示例

void addImagesMatchingStage(struct visiSearcher *searcher,	struct sqlConnection *conn, int schemeId, int taxon,	char *minAge, int wordIx, int wordCount)/* Given a developmental stage scheme (schemeId) and a specific * stage, return all images that match stage */{struct dyString *dy = dyStringNew(0);char *maxAge;// note in the code below minAge and maxAge are strings//  but they should contain float values.  Putting single-quotes//  around them and escaping their contents is something that will //  protect against sql injection.dyStringClear(dy);sqlDyStringPrintf(dy,     "select age from lifeStage where lifeStageScheme = %d ",     schemeId);sqlDyStringPrintf(dy,    "and age > '%s' order by age", minAge);maxAge = sqlQuickString(conn, dy->string);dyStringClear(dy);sqlDyStringPrintf(dy, "select image.id from specimen,image ");sqlDyStringPrintf(dy, "where specimen.age >= '%s' ", minAge);if (maxAge != NULL)    sqlDyStringPrintf(dy, "and specimen.age < '%s' ", maxAge);dyStringPrintf(dy, "and specimen.taxon = %d ", taxon);dyStringPrintf(dy, "and specimen.id = image.specimen");addImagesMatchingQuery(searcher, conn, dy->string, NULL, NULL,	wordIx, wordCount);dyStringFree(&dy);}

static void addImagesMatchingBodyPart(struct visiSearcher *searcher,	struct sqlConnection *conn, struct dyString *dy, char *bodyPart,	int startWord, int wordCount)/* Add images that are contributed by given contributor to * searcher with a weight of one.  Use dy for scratch space for * the query. */{struct hash *uniqHash = newHash(0);dyStringClear(dy);sqlDyStringPrintf(dy,    "select imageProbe.image from "   "bodyPart,expressionLevel,imageProbe "   "where bodyPart.name = /"%s/" "   "and bodyPart.id = expressionLevel.bodyPart "   "and expressionLevel.imageProbe = imageProbe.id "   "and expressionLevel.level > 0"   , bodyPart);addImagesMatchingQuery(searcher, conn, dy->string, uniqHash, bodyPart,	startWord, wordCount);dyStringClear(dy);sqlDyStringPrintf(dy,    "select image.id from bodyPart,specimen,image "    "where bodyPart.name = /"%s/" "    "and bodyPart.id = specimen.bodyPart "    "and specimen.id = image.specimen", bodyPart);addImagesMatchingQuery(searcher, conn, dy->string, uniqHash, bodyPart,	startWord, wordCount);hashFree(&uniqHash);}

static struct slName *getListFromCgapSageLibs(struct sqlConnection *conn, char *column, boolean returnIds, boolean distinct)/* Return [unique] list of tissues sorted alphabetically. */{struct slName *list = NULL;struct dyString *dy = dyStringNew(0);char **row;struct sqlResult *sr;sqlDyStringPrintf(dy, "select ");if (distinct)    dyStringAppend(dy, "distinct ");sqlDyStringPrintf(dy, "%s", column);if (returnIds)    dyStringAppend(dy, ",libId");sqlDyStringPrintf(dy, " from cgapSageLib order by %s", column);sr = sqlGetResult(conn, dy->string);while ((row = sqlNextRow(sr)) != NULL)    {    char *word = (returnIds) ? row[1] : row[0];    slNameAddHead(&list, word);    }slReverse(&list);sqlFreeResult(&sr);dyStringFree(&dy);return list;}

void weighMatches(struct sqlConnection *conn, struct visiMatch *matchList)/* Set match field in match list according to priority, etc. */{struct visiMatch *match;struct dyString *dy = dyStringNew(0);/* Weigh matches by priority, and secondarily by age. */for (match = matchList; match != NULL; match = match->next)    {    double priority;    double age;    /* Fetch priority. */    dyStringClear(dy);    sqlDyStringPrintf(dy,    	"select imageFile.priority from imageFile,image "	"where image.id = %d and image.imageFile = imageFile.id"	, match->imageId);    priority = sqlQuickDouble(conn, dy->string);    /* Fetch age. */    dyStringClear(dy);    sqlDyStringPrintf(dy,        "select specimen.age from image,specimen "	"where image.id = %d and image.specimen = specimen.id"	, match->imageId);    age = sqlQuickDouble(conn, dy->string);    match->weight = -priority - age*0.0001;    }}

static void transformToCanonical(struct searchResult *list,	struct sqlConnection *conn)/* Transform search results to canonical versions.  */{struct dyString *dy = newDyString(1024);char *cannon = genomeSetting("canonicalTable");char *isoform = genomeSetting("isoformTable");struct sqlResult *sr;char **row;struct searchResult *el;for (el = list; el != NULL; el = el->next)    {    dyStringClear(dy);    sqlDyStringPrintf(dy,     	"select %s.transcript,%s.chrom,%s.chromStart,%s.chromEnd,%s.protein ",	cannon, cannon, cannon, cannon, cannon);    sqlDyStringPrintf(dy,	"from %s,%s ",	isoform, cannon);    sqlDyStringPrintf(dy,        "where %s.transcript = '%s' ",	isoform, el->gp.name);    sqlDyStringPrintf(dy,        "and %s.clusterId = %s.clusterId",	isoform, cannon);    sr = sqlGetResult(conn, dy->string);    if ((row = sqlNextRow(sr)) != NULL)	genePosFillFrom5(&el->gp, row);    sqlFreeResult(&sr);    }dyStringFree(&dy);}

void hgSoftberryHom(char *database, int fileCount, char *files[])/* hgSoftberryHom - Make table storing Softberry protein homology information. */{int i;char *fileName;char *table = "softberryHom";char *tabFileName = "softberryHom.tab";FILE *f = mustOpen(tabFileName, "w");struct sqlConnection *conn = NULL;struct dyString *ds = newDyString(2048);for (i=0; i<fileCount; ++i)    {    fileName = files[i];    printf("Processing %s/n", fileName);    makeTabLines(fileName, f);    }carefulClose(&f);/* Create table if it doesn't exist, delete whatever is * already in it, and fill it up from tab file. */conn = sqlConnect(database);printf("Loading %s table/n", table);sqlMaybeMakeTable(conn, table, createTable);sqlDyStringPrintf(ds, "DELETE from %s", table);sqlUpdate(conn, ds->string);dyStringClear(ds);sqlDyStringPrintf(ds, "LOAD data local infile '%s' into table %s",     tabFileName, table);sqlUpdate(conn, ds->string);sqlDisconnect(&conn);}

void loadOneTable(char *database, struct sqlConnection *conn, char *tempName, char *tableName)/* Load .tab file tempName into tableName and remove tempName. */{struct dyString *query = newDyString(1024);verbose(1, "Loading up table %s/n", tableName);if (sqlTableExists(conn, tableName))    {    sqlDyStringPrintf(query, "DROP table %s", tableName);    sqlUpdate(conn, query->string);    }/* Create first part of table definitions, the fields. */dyStringClear(query);sqlDyStringPrintf(query, createRmskOut, tableName);/* Create the indexes */int indexLen = hGetMinIndexLength(database);sqlDyStringPrintf(query, "   INDEX(genoName(%d),bin))/n", indexLen);sqlUpdate(conn, query->string);/* Load database from tab-file. */dyStringClear(query);sqlDyStringPrintf(query, "LOAD data local infile '%s' into table %s",	       tempName, tableName);sqlUpdate(conn, query->string);remove(tempName);}

void encode2ExpDumpFlat(char *outFile, char *viewSql, char *flatTable)/* encode2ExpDumpFlat - Dump the experiment table in a semi-flat way from a relationalized Encode2 metadatabase.. */{FILE *f = mustOpen(outFile, "w");struct sqlConnection *conn = sqlConnect(database);struct starTableInfo **stiArray;AllocArray(stiArray, ArraySize(starFields));int i;for (i=0; i<ArraySize(starFields); ++i)    stiArray[i] = starTableInfoNew(conn, starFields[i]);struct dyString *query = dyStringNew(2000);sqlDyStringPrintf(query, "select ");for (i=0; i<ArraySize(flatFields); ++i)    {    if (i != 0)       dyStringAppendC(query, ',');    sqlDyStringPrintf(query, "%s", flatFields[i]);    }for (i=0; i<ArraySize(starFields); ++i)    {    dyStringAppendC(query, ',');    sqlDyStringPrintf(query, "%s", starFields[i]);    }dyStringPrintf(query, " from %s%s", tablePrefix, "experiment");struct sqlResult *sr = sqlGetResult(conn, query->string);char **row;while ((row = sqlNextRow(sr)) != NULL)    {    int flatSize = ArraySize(flatFields);    int starSize = ArraySize(starFields);    int i;    for (i=0; i<flatSize; ++i)        {	if (i != 0)	    fputc('/t', f);	fputs(row[i], f);	}    for (i=0; i<starSize; ++i)        {	int id = sqlUnsigned(row[i+flatSize]);	fputc('/t', f);	if (id == 0)	    fputs("n/a", f);	else	    fputs(stiArray[i]->termsForIds[id], f);	}    fputc('/n', f);    }sqlFreeResult(&sr);sqlDisconnect(&conn);carefulClose(&f);makeViewSql(viewSql);makeFlatTableSql(flatTable);}

void doFlyreg(struct trackDb *tdb, char *item)/* flyreg.org: Drosophila DNase I Footprint db. */{struct dyString *query = newDyString(256);struct sqlConnection *conn = hAllocConn(database);struct sqlResult *sr = NULL;char **row;int start = cartInt(cart, "o");int end   = cartInt(cart, "t");char fullTable[HDB_MAX_TABLE_STRING];boolean hasBin = FALSE;char *motifTable = "flyregMotif";struct dnaMotif *motif = NULL;boolean isVersion2 = sameString(tdb->table, "flyreg2");genericHeader(tdb, item);if (!hFindSplitTable(database, seqName, tdb->table, fullTable, sizeof fullTable, &hasBin))    errAbort("track %s not found", tdb->table);sqlDyStringPrintf(query, "select * from %s where chrom = '%s' and ",	       fullTable, seqName);hAddBinToQuery(start, end, query);sqlDyStringPrintf(query, "chromStart = %d and name = '%s'", start, item);sr = sqlGetResult(conn, query->string);if ((row = sqlNextRow(sr)) != NULL)    {    struct flyreg2 fr;    if (isVersion2)	flyreg2StaticLoad(row+hasBin, &fr);    else	flyregStaticLoad(row+hasBin, (struct flyreg *)(&fr));    printf("<B>Factor:</B> %s<BR>/n", fr.name);    printf("<B>Target:</B> %s<BR>/n", fr.target);    if (isVersion2)	printf("<B>Footprint ID:</B> %06d<BR>/n", fr.fpid);    printf("<B>PubMed ID:</B> <A HREF=/"");    printEntrezPubMedUidUrl(stdout, fr.pmid);    printf("/" TARGET=_BLANK>%d</A><BR>/n", fr.pmid);    bedPrintPos((struct bed *)(&fr), 3, tdb);    if (hTableExists(database, motifTable))	{	motif = loadDnaMotif(item, motifTable);	if (motif != NULL)	    motifHitSection(NULL, motif);	}    }else    errAbort("query returned no results: /"%s/"", query->string);dyStringFree(&query);sqlFreeResult(&sr);hFreeConn(&conn);if (motif != NULL)    webNewSection("%s",tdb->longLabel);printTrackHtml(tdb);}

void gapFileToTable(struct sqlConnection *conn, char *gapFileName,		    char *gapTableName)/* Build a single gap table from a single gap file. */{struct lineFile *lf = lineFileOpen(gapFileName, TRUE);char tabFileName[256];FILE *tabFile = NULL;char *words[16];int wordCount;safef(tabFileName, sizeof(tabFileName), "%s.tab", gapTableName);tabFile = mustOpen(tabFileName, "w");while ((wordCount = lineFileChop(lf, words)) > 0)    {    if (wordCount < 5)	errAbort("Short line %d of %s", lf->lineIx, lf->fileName);    if (words[4][0] == 'N' || words[4][0] == 'U')	{	int len = strlen(words[0]);	if (len > maxChromNameSize)	    {	    maxChromNameSize = len;	    if (maxChromNameSize > 254)		errAbort("ERROR: chrom name size is over 254(%d) characters: "			"'%s'", maxChromNameSize, words[0]);	    }	struct agpGap gap;	agpGapStaticLoad(words, &gap);	gap.chromStart -= 1;	fprintf(tabFile, "%u/t", hFindBin(gap.chromStart, gap.chromEnd));	agpGapTabOut(&gap, tabFile);	}    }lineFileClose(&lf);fclose(tabFile);if (! noLoad)    {    struct dyString *ds = newDyString(2048);    if (unsplit)	sqlDyStringPrintf(ds,  createGapUnsplit, gapTableName,		maxChromNameSize, maxChromNameSize);    else	sqlDyStringPrintf(ds, createGapSplit, gapTableName);    char query[1024];    sqlRemakeTable(conn, gapTableName, ds->string);    sqlSafef(query, sizeof(query), "LOAD data local infile '%s' into table %s", 	  tabFileName, gapTableName);    sqlUpdate(conn, query);    remove(tabFileName);    freeDyString(&ds);    }}

void intInClause(struct dyString *query, struct slInt *list)/* Add in clause to query for all integers in list. */{struct slInt *el;sqlDyStringPrintf(query, " in (");for (el = list; el != NULL; el = el->next)    {    sqlDyStringPrintf(query, "%d", el->val);    if (el->next != NULL)	sqlDyStringPrintf(query, ",");    }sqlDyStringPrintf(query, ") ");}

void loadIntoDatabase(char *database, char *createString, char *table, char *tabName)/* Load tabbed file into database table. */{struct sqlConnection *conn = sqlConnect(database);struct dyString *ds = newDyString(2048);sqlDyStringPrintf(ds, createString, table);sqlRemakeTable(conn, table, ds->string);dyStringClear(ds);sqlDyStringPrintf(ds,    "LOAD data local infile '%s' into table %s", tabName, table);sqlUpdate(conn, ds->string);sqlDisconnect(&conn);freeDyString(&ds);}

void genotypeAndStrainFromKey(char *genotypeKey, struct sqlConnection *conn,	char **retGenotype, char **retStrain)/* Return dynamically allocated string describing genotype */{int key = atoi(genotypeKey);char *genotype = NULL, *strain = NULL;if (key > 0)    {    struct dyString *query = dyStringNew(0);    struct dyString *geno = dyStringNew(256);    struct sqlResult *sr;    char **row;    /* Figure out genotype.  Create string that looks something like:     *     adh:cheap date,antp:+,       * That is a comma separated list gene:allele. */    sqlDyStringPrintf(query,     	"select MRK_Marker.symbol,ALL_Allele.symbol "	"from GXD_AlleleGenotype,MRK_Marker,ALL_Allele "	"where GXD_AlleleGenotype._Genotype_key = %s "	"and GXD_AlleleGenotype._Marker_key = MRK_Marker._Marker_key "	"and GXD_AlleleGenotype._Allele_key = ALL_Allele._Allele_key "	, genotypeKey);    sr = sqlGetResultVerbose(conn, query->string);    while ((row = sqlNextRow(sr)) != NULL)	dyStringPrintf(geno, "%s:%s,", row[0], row[1]);    sqlFreeResult(&sr);    genotype = dyStringCannibalize(&geno);    /* Figure out strain */    dyStringClear(query);    sqlDyStringPrintf(query,        "select PRB_Strain.strain from GXD_Genotype,PRB_Strain "	"where GXD_Genotype._Genotype_key = %s "	"and GXD_Genotype._Strain_key = PRB_Strain._Strain_key"	, genotypeKey);    strain = sqlQuickStringVerbose(conn, query->string);    if (isUnknown(strain))        freez(&strain);    dyStringFree(&query);    }if (genotype == NULL)    genotype = cloneString("");if (strain == NULL)    strain = cloneString("");*retGenotype = genotype;*retStrain = strain;}

static void asdDoQuerySimple(struct annoStreamDb *self, char *minChrom, uint minEnd)/* Return a sqlResult for a query on table items in position range. * If doing a whole genome query. just select all rows from table. */// NOTE: it would be possible to implement filters at this level, as in hgTables.{struct annoStreamer *streamer = &(self->streamer);boolean hasWhere = FALSE;struct dyString *query = self->makeBaselineQuery(self, &hasWhere);if (!streamer->positionIsGenome)    {    if (minChrom && differentString(minChrom, streamer->chrom))	errAbort("annoStreamDb %s: nextRow minChrom='%s' but region chrom='%s'",		 streamer->name, minChrom, streamer->chrom);    if (self->hasBin)	{	// Results will be in bin order, but we can restore chromStart order by	// accumulating initial coarse-bin items and merge-sorting them with	// subsequent finest-bin items which will be in chromStart order.	resetMergeState(self);	self->mergeBins = TRUE;	self->qLm = lmInit(0);	}    if (self->endFieldIndexName != NULL)	// Don't let mysql use a (chrom, chromEnd) index because that messes up	// sorting by chromStart.	sqlDyStringPrintf(query, " IGNORE INDEX (%s)", self->endFieldIndexName);    sqlDyStringAppend(query, hasWhere ? " and " : " where ");    sqlDyStringPrintf(query, "%s='%s'", self->chromField, streamer->chrom);    int chromSize = annoAssemblySeqSize(streamer->assembly, streamer->chrom);    if (streamer->regionStart != 0 || streamer->regionEnd != chromSize)	{	dyStringAppend(query, " and ");	if (self->hasBin)	    hAddBinToQuery(streamer->regionStart, streamer->regionEnd, query);	sqlDyStringPrintf(query, "%s < %u and %s > %u", self->startField, streamer->regionEnd,		       self->endField, streamer->regionStart);	}    if (self->notSorted)	sqlDyStringPrintf(query, " order by %s", self->startField);    }else if (self->notSorted)    sqlDyStringPrintf(query, " order by %s,%s", self->chromField, self->startField);if (self->maxOutRows > 0)    dyStringPrintf(query, " limit %d", self->maxOutRows);struct sqlResult *sr = sqlGetResult(self->conn, query->string);dyStringFree(&query);self->sr = sr;self->needQuery = FALSE;}

static void queryInputTrackTable(struct dyString *query, char *inputTrackTable,                                struct slName *fieldList)/* Construct query in dyString to return contents of inputTrackTable ordered appropriately */{struct dyString *fields = dyStringNew(0);struct slName *field;sqlDyStringPrintf(query, "select tableName ");for (field = fieldList; field != NULL; field = field->next)    sqlDyStringPrintfFrag(fields, ",%s", field->name);sqlDyStringPrintf(query, "%-s from %s", fields->string, inputTrackTable);if (fieldList != NULL)    // skip leading comma    dyStringPrintf(query, " order by %s", fields->string+1);dyStringFree(&fields);}

struct estOrientInfo *estOrientInfoLoadWhere(struct sqlConnection *conn, char *table, char *where)/* Load all estOrientInfo from table that satisfy where clause. The * where clause may be NULL in which case whole table is loaded * Dispose of this with estOrientInfoFreeList(). */{struct estOrientInfo *list = NULL, *el;struct dyString *query = dyStringNew(256);struct sqlResult *sr;char **row;sqlDyStringPrintf(query, "select * from %s", table);if (where != NULL)    dyStringPrintf(query, " where %s", where);sr = sqlGetResult(conn, query->string);int off = sqlFieldColumn(sr, "bin") + 1; // offset of data; function returns -1 if no binwhile ((row = sqlNextRow(sr)) != NULL)    {    el = estOrientInfoLoad(row+off);    slAddHead(&list, el);    }slReverse(&list);sqlFreeResult(&sr);dyStringFree(&query);return list;}

void createTable(char *chromName)/* create a chrN_snpTmp table */{struct sqlConnection *conn = hAllocConn();char tableName[64];char *createString ="CREATE TABLE %s (/n""    snp_id int(11) not null,/n""    ctg_id int(11) not null,/n""    chromName char(32) not null,/n""    loc_type tinyint(4) not null,/n""    phys_pos_from int(11) not null,/n""    phys_pos varchar(32),/n""    orientation tinyint(4) not null,/n""    allele blob/n"");/n";struct dyString *dy = newDyString(1024);safef(tableName, ArraySize(tableName), "chr%s_snpTmp", chromName);sqlDyStringPrintf(dy, createString, tableName);sqlRemakeTable(conn, tableName, dy->string);dyStringFree(&dy);hFreeConn(&conn);}

void recreateDatabaseTable(char *chromName)/* create a new chrN_snpTmp table with new definition */{struct sqlConnection *conn = hAllocConn();char tableName[64];char *createString ="CREATE TABLE %s (/n""    snp_id int(11) not null,/n""    chromStart int(11) not null,/n""    chromEnd int(11) not null,/n""    loc_type tinyint(4) not null,/n""    class varchar(255) not null,/n""    orientation tinyint(4) not null,/n""    fxn_class varchar(255) not null,/n""    allele blob,/n""    refUCSC blob,/n""    refUCSCReverseComp blob,/n""    observed blob,/n""    weight int/n"");/n";struct dyString *dy = newDyString(1024);safef(tableName, ArraySize(tableName), "%s_snpTmp", chromName);sqlDyStringPrintf(dy, createString, tableName);sqlRemakeTable(conn, tableName, dy->string);dyStringFree(&dy);hFreeConn(&conn);}

void scoredRefTableCreate(struct sqlConnection *conn, char *tableName, int indexSize)/* Create a scored-ref table with the given name. */{static char *createString ="#High level information about a multiple alignment. Link to details in maf file./n""CREATE TABLE %s (/n""    bin smallint unsigned not null,/n""    chrom varchar(255) not null,	# Chromosome (this species)/n""    chromStart int unsigned not null,	# Start position in chromosome (forward strand)/n""    chromEnd int unsigned not null,	# End position in chromosome/n""    extFile int unsigned not null,	# Pointer to associated MAF file/n""    offset bigint not null,	# Offset in MAF file/n""    score double not null,	# Score/n""              #Indices/n""    INDEX(chrom(%d),bin)/n"#ifdef OLD	/* The other two indexes actually slow things down these days. */"    INDEX(chrom(%d),bin),/n""    INDEX(chrom(%d),chromStart),/n""    INDEX(chrom(%d),chromEnd)/n"#endif /* OLD */")/n";struct dyString *dy = newDyString(1024);sqlDyStringPrintf(dy, createString, tableName, indexSize, indexSize, indexSize);sqlRemakeTable(conn, tableName, dy->string);dyStringFree(&dy);}

void constructQueryForEvent(struct dyString *query, char *skipPSet, char *tableName)/* Construct a query for all the probe sets in a particular event for   a matrix table. */{int i = 0;char eventQuery[256];struct mouseAPSetEventMap *event = NULL;struct sqlConnection *conn = hAllocConn();sqlSafef(eventQuery, sizeof(eventQuery),       "select * from mouseAPSetEventMap where skipPSet = '%s';",      skipPSet);/* Check our little cache. */if(altEvent == NULL)          altEvent = event = mouseAPSetEventMapLoadByQuery(conn, eventQuery);else     event = altEvent;if(event == NULL)    errAbort("Couldn't find an alternative events for: %s", skipPSet);assert(query);dyStringClear(query);sqlDyStringPrintf(query, "select * from %s where ", tableName);for(i = 0; i < event->incCount; i++)     dyStringPrintf(query, "name like '%s' or ", event->incPSets[i]);for(i = 0; i < event->geneCount; i++)     dyStringPrintf(query, "name like '%s' or ", event->genePSets[i]);dyStringPrintf(query, "name like '%s' order by name;", skipPSet);hFreeConn(&conn);}

void fixMotif(struct dnaMotif *motif,  int targetSize, char *motifTable, struct sqlConnection *conn)/* Try and fix motif by trimming degenerate columns. */{struct dyString *dy = dyStringNew(0);printf("Fixing %s in %s from:/n", motif->name, motifTable);dnaMotifPrintProb(motif, uglyOut);while (targetSize < motif->columnCount)    {    double startInfo = dnaMotifBitsOfInfo(motif, 0);    double endInfo = dnaMotifBitsOfInfo(motif, motif->columnCount-1);    motif->columnCount -= 1;    if (startInfo < endInfo)	{	memcpy(motif->aProb, motif->aProb+1, sizeof(motif->aProb[0]) * motif->columnCount);	memcpy(motif->cProb, motif->cProb+1, sizeof(motif->cProb[0]) * motif->columnCount);	memcpy(motif->gProb, motif->gProb+1, sizeof(motif->gProb[0]) * motif->columnCount);	memcpy(motif->tProb, motif->tProb+1, sizeof(motif->tProb[0]) * motif->columnCount);	}    }printf("to:/n");dnaMotifPrintProb(motif, uglyOut);sqlDyStringPrintf(dy, "update %s set ", motifTable);formatProb(dy, "aProb", motif->aProb, motif->columnCount);formatProb(dy, "cProb", motif->cProb, motif->columnCount);formatProb(dy, "gProb", motif->gProb, motif->columnCount);formatProb(dy, "tProb", motif->tProb, motif->columnCount);dyStringPrintf(dy, "columnCount=%d ", motif->columnCount);dyStringPrintf(dy, "where name = '%s'", motif->name);sqlUpdate(conn, dy->string);dyStringFree(&dy);}

int nextGapPos(char *chrom, int desiredPos, struct sqlConnection *conn){/* Find next gap on the chrom and return midpoint */struct sqlResult *sr;char **row;int pos = -1;int start, end;struct hTableInfo *hti = hFindTableInfo(db, chrom, "gap");struct dyString *query = newDyString(1024);if (hti == NULL)    errAbort("table %s.gap doesn't exist", db);sqlDyStringPrintf(query, "select chromStart,chromEnd from ");if (hti->isSplit)    dyStringPrintf(query, "%s_gap where ", chrom);else    dyStringPrintf(query, "gap where %s='%s' AND ", hti->chromField, chrom);dyStringPrintf(query, "(chromStart >= %d and chromEnd-chromStart > %d)/    order by chromStart limit 1",        desiredPos, minGap);sr = sqlGetResult(conn, query->string);freeDyString(&query);if ((row = sqlNextRow(sr)) != NULL)    {    start = sqlSigned(row[0]);    end = sqlSigned(row[1]);    pos = start + (end - start)/2;    }sqlFreeResult(&sr);return pos;}

static void addPrimaryIdsToHash(struct sqlConnection *conn, struct hash *hash,				char *idField, struct slName *tableList,				struct lm *lm, char *extraWhere)/* For each table in tableList, query all idField values and add to hash, * id -> uppercased id for case-insensitive matching. */{struct slName *table;struct sqlResult *sr;char **row;struct dyString *query = dyStringNew(0);for (table = tableList;  table != NULL;  table = table->next)    {    dyStringClear(query);    sqlDyStringPrintf(query, "select %s from %s", idField, table->name);    if (extraWhere != NULL)	dyStringPrintf(query, " where %s", extraWhere);    sr = sqlGetResult(conn, query->string);    while ((row = sqlNextRow(sr)) != NULL)	{	if (isNotEmpty(row[0]))	    {	    char *origCase = lmCloneString(lm, row[0]);	    touppers(row[0]);	    hashAdd(hash, row[0], origCase);	    }	}    sqlFreeResult(&sr);    }}

void chainDbAddBlocks(struct chain *chain, char *track, struct sqlConnection *conn)/* Add blocks to chain header. */{struct dyString *query = newDyString(1024);struct sqlResult *sr = NULL;char **row;struct cBlock *b;char fullName[64];safef(fullName, sizeof(fullName), "%s_%s", chain->tName, track);if (!sqlTableExists(conn, fullName))    strcpy(fullName, track);sqlDyStringPrintf(query, 	"select tStart,tEnd,qStart from %sLink where chainId = %d",fullName, chain->id);sr = sqlGetResult(conn, query->string);while ((row = sqlNextRow(sr)) != NULL)    {    AllocVar(b);    b->tStart = sqlUnsigned(row[0]);    b->tEnd = sqlUnsigned(row[1]);    b->qStart = sqlUnsigned(row[2]);    b->qEnd = b->qStart + (b->tEnd - b->tStart);    slAddHead(&chain->blockList, b);    }slReverse(&chain->blockList);sqlFreeResult(&sr);dyStringFree(&query);}

void saveClonePos(struct clonePos *cloneList, char *database)/* Save sorted clone position list to database. */{    struct sqlConnection *conn = sqlConnect(database);    struct clonePos *clone;    struct tempName tn;    FILE *f;    struct dyString *ds = newDyString(2048);    /* Create tab file from clone list. */    printf("Creating tab file/n");    makeTempName(&tn, "hgCP", ".tab");    f = mustOpen(tn.forCgi, "w");    for (clone = cloneList; clone != NULL; clone = clone->next)        clonePosTabOut(clone, f);    fclose(f);    /* Create table if it doesn't exist, delete whatever is     * already in it, and fill it up from tab file. */    printf("Loading clonePos table/n");    sqlMaybeMakeTable(conn, "clonePos", createClonePos);    sqlUpdate(conn, "NOSQLINJ DELETE from clonePos");    sqlDyStringPrintf(ds, "LOAD data local infile '%s' into table clonePos",                      tn.forCgi);    sqlUpdate(conn, ds->string);    /* Clean up. */    remove(tn.forCgi);    sqlDisconnect(&conn);}

struct dyString *readAndReplaceTableName(char *fileName, char *table)/* Read file into string.  While doing so strip any leading comments * and insist that the first non-comment line contain the words * "create table" followed by a table name.  Replace the table name, * and copy the rest of the file verbatem. */{    struct lineFile *lf = lineFileOpen(fileName, TRUE);    struct dyString *dy = dyStringNew(0);    char *line, *word;    if (!lineFileNextReal(lf, &line))        errAbort("No real lines in %s/n", fileName);    word = nextWord(&line);    if (!sameWord(word, "create"))        errAbort("Expecting first word in file to be CREATE. Got %s", word);    word = nextWord(&line);    if (word == NULL || !sameWord(word, "table"))        errAbort("Expecting second word in file to be table. Got %s", emptyForNull(word));    word = nextWord(&line);    if (word == NULL)        errAbort("Expecting table name on same line as CREATE TABLE");    sqlDyStringPrintf(dy, "CREATE TABLE %s ", table);    if (line != NULL)        dyStringAppend(dy, line);    dyStringAppendC(dy, '/n');    while (lineFileNext(lf, &line, NULL))    {        dyStringAppend(dy, line);        dyStringAppendC(dy, '/n');    }    lineFileClose(&lf);    return dy;}

int nextRepeatPos(char *chrom, int desiredPos, struct sqlConnection *conn)/* Find next 0% diverged repeat on the chrom and return midpoint */{struct sqlResult *sr;char **row;int pos = -1;int start, end;struct hTableInfo *hti = hFindTableInfo(db, chrom, "rmsk");struct dyString *query = newDyString(1024);if (hti == NULL)    errAbort("table %s.rmsk doesn't exist", db);sqlDyStringPrintf(query, "select genoStart,genoEnd from ");if (hti->isSplit)    dyStringPrintf(query, "%s_rmsk where ", chrom);else    dyStringPrintf(query, "rmsk where %s='%s' AND ", hti->chromField, chrom);dyStringPrintf(query,    "(genoStart >= %d AND /    milliDiv=0 AND /    repClass<>'Simple_repeat' AND repClass<>'Low_complexity' AND /    genoEnd-genoStart>%d) order by genoStart limit 1",        desiredPos, minRepeat);sr = sqlGetResult(conn, query->string);freeDyString(&query);if ((row = sqlNextRow(sr)) != NULL)    {    start = sqlSigned(row[0]);    end = sqlSigned(row[1]);    pos = start + (end - start)/2;    }sqlFreeResult(&sr);return pos;}

void saveCtgPos(struct ctgPos *ctgList, char *database)/* Save ctgList to database. */{struct sqlConnection *conn = sqlConnect(database);struct ctgPos *ctg;char *tabFileName = "ctgPos.tab";FILE *f;struct dyString *ds = newDyString(2048);/* Create tab file from ctg list. */printf("Creating tab file/n");f = mustOpen(tabFileName, "w");for (ctg = ctgList; ctg != NULL; ctg = ctg->next)    ctgPosTabOut(ctg, f);fclose(f);/* Create table if it doesn't exist, delete whatever is * already in it, and fill it up from tab file. */printf("Loading ctgPos table/n");sqlMaybeMakeTable(conn, "ctgPos", createCtgPos);sqlUpdate(conn, "NOSQLINJ DELETE from ctgPos");sqlDyStringPrintf(ds, "LOAD data local infile '%s' into table ctgPos",     tabFileName);sqlUpdate(conn, ds->string);/* Clean up. */remove(tabFileName);sqlDisconnect(&conn);}

struct spFeature *spFeatures(struct sqlConnection *conn, char *acc,	int classId, 	/* Feature class ID, 0 for all */	int typeId)	/* Feature type ID, 0 for all *//* Get feature list.  slFreeList this when done. */{struct dyString *dy = dyStringNew(0);struct spFeature *list = NULL, *el;char **row;struct sqlResult *sr;sqlDyStringAppend(dy, 	"select start,end,featureClass,featureType,softEndBits from feature ");sqlDyStringPrintf(dy,         "where acc = '%s'", acc);if (classId != 0)    dyStringPrintf(dy, " and featureClass=%d", classId);if (typeId != 0)    dyStringPrintf(dy, " and featureType=%d", typeId);sr = sqlGetResult(conn, dy->string);while ((row = sqlNextRow(sr)) != NULL)    {    AllocVar(el);    el->start = sqlUnsigned(row[0]);    el->end = sqlUnsigned(row[1]);    el->featureClass = sqlUnsigned(row[2]);    el->featureType = sqlUnsigned(row[3]);    el->softEndBits = sqlUnsigned(row[4]);    slAddHead(&list, el);    }sqlFreeResult(&sr);dyStringFree(&dy);slReverse(&list);return list;}

void snpTmpTableCreate(struct sqlConnection *conn, char *tableName)/* create a chrN_snpTmp table */{char *createString ="CREATE TABLE %s (/n""    chrom         varchar(255) not null,/n""    chromStart    int unsigned not null,/n""    chromEnd      int unsigned not null,/n""    name          varchar(255) not null,/n""    strand        char(1) not null,/n""    refNCBI       longblob not null,/n""    locType       enum('unknown', 'range', 'exact', 'between',/n""                  'rangeInsertion', 'rangeSubstitution', 'rangeDeletion') /n""                  DEFAULT 'unknown' NOT NULL,/n""    func       set( 'unknown', 'locus', 'coding', 'coding-synon', 'coding-nonsynon', /n""    		     'untranslated', 'intron', 'splice-site', 'cds-reference') /n""		     DEFAULT 'unknown' NOT NULL,/n""    			# The functional category of the SNP/n""    contigName    varchar(255) not null,/n""    INDEX         name(name)/n"")/n";struct dyString *dy = newDyString(512);sqlDyStringPrintf(dy, createString, tableName);sqlRemakeTable(conn, tableName, dy->string);dyStringFree(&dy);}