static void showTableFieldsDb(char *db, char *rootTable, boolean withGetButton)
/* Put up a little html table with a check box, name, and hopefully
* a description for each field in SQL rootTable. */
{
struct sqlConnection *conn = NULL;
if (!trackHubDatabase(database))
conn = hAllocConn(db);
struct trackDb *tdb = findTdbForTable(db, curTrack, rootTable, ctLookupName);
struct asObject *asObj = asForTable(conn, rootTable);
boolean showItemRgb = FALSE;
showItemRgb=bedItemRgb(tdb); /* should we expect itemRgb instead of "reserved" */
struct slName *fieldList;
if (isBigBed(database, rootTable, curTrack, ctLookupName))
fieldList = bigBedGetFields(rootTable, conn);
else if (isBamTable(rootTable))
fieldList = bamGetFields();
else if (isVcfTable(rootTable, NULL))
fieldList = vcfGetFields();
else
{
char *table = chromTable(conn, rootTable);
fieldList = sqlListFields(conn, table);
freez(&table);
}
showTableFieldsOnList(db, rootTable, asObj, fieldList, showItemRgb, withGetButton);
hFreeConn(&conn);
}
struct kgXref *getKgList(struct sqlConnection *conn)
/* Get list of all known genes. */
{
/* Verify that the number of fields present in this kgXref table is what's
* expected, since more fields were added to the schema recently (10/19/2011) */
struct slName *kgXrefFields = sqlListFields(conn, "kgXref");
if (slCount(kgXrefFields) != KGXREF_NUM_COLS)
{
errAbort("This genome has %d columns in kgXref but %d are expected - old genome?",
slCount(kgXrefFields), KGXREF_NUM_COLS);
}
slFreeList(kgXrefFields);
struct sqlResult *sr = sqlGetResult(conn, "NOSQLINJ select * from kgXref");
struct kgXref *kgList = NULL, *kg;
char **row;
struct hash *uniqHash = hashNew(18);
while ((row = sqlNextRow(sr)) != NULL)
{
kg = kgXrefLoad(row);
if (hashLookup(uniqHash, kg->kgID) == NULL)
{
hashAdd(uniqHash, kg->kgID, NULL);
slAddHead(&kgList, kg);
}
}
sqlFreeResult(&sr);
slReverse(&kgList);
hashFree(&uniqHash);
return kgList;
}
static void showTableFieldsCt(char *db, char *table, boolean withGetButton)
/* Put up html table with a check box for each field of custom
* track. */
{
struct customTrack *ct = ctLookupName(table);
char *type = ct->dbTrackType;
if (type == NULL)
type = ct->tdb->type;
struct sqlConnection *conn = hAllocConn(CUSTOM_TRASH);
struct asObject *asObj = asForTdb(conn, ct->tdb);
if (asObj)
{
struct slName *fieldList = NULL;
if (ct->dbTableName != NULL)
{
if (isVcfTable(table, NULL))
fieldList = vcfGetFields();
else
fieldList = sqlListFields(conn, ct->dbTableName);
}
if (fieldList == NULL)
fieldList = asColNames(asObj);
showTableFieldsOnList(db, table, asObj, fieldList, FALSE, withGetButton);
asObjectFree(&asObj);
slNameFreeList(&fieldList);
}
else
showBedTableFields(db, table, ct->fieldCount, withGetButton);
hFreeConn(&conn);
}
static void orderFieldsInTable(struct tableJoiner *tj)
/* Rearrange order of fields in tj->fieldList so that
* they are the same as the order in the database. */
{
struct sqlConnection *conn = hAllocConn(tj->database);
char *splitTable = chromTable(conn, tj->table);
struct hash *fieldHash = hashNew(0);
struct slName *field, *fieldList = sqlListFields(conn, splitTable);
struct joinerDtf *dtf, *newList = NULL;
/* Build up hash of field names. */
for (dtf = tj->fieldList; dtf != NULL; dtf = dtf->next)
hashAdd(fieldHash, dtf->field, dtf);
hFreeConn(&conn);
/* Build up new list in correct order. */
for (field = fieldList; field != NULL; field = field->next)
{
dtf = hashFindVal(fieldHash, field->name);
if (dtf != NULL)
{
slAddHead(&newList, dtf);
}
}
slFreeList(&fieldList);
slReverse(&newList);
tj->fieldList = newList;
freez(&splitTable);
}
boolean hasBinColumn(struct sqlConnection *conn, char* table)
/* check if a table has a bin column */
{
struct slName *fields = sqlListFields(conn, table);
struct slName *f;
boolean found = FALSE;
for (f = fields; (f != NULL) && (!found); f = f->next)
found = sameString(f->name, "bin");
slFreeList(&fields);
return found;
}
void makeDbOrderedCommaFieldList(struct sqlConnection *conn,
char *table, struct joinerDtf *dtfList, struct dyString *dy)
/* Assumes that dtfList all points to same table. This will return
* a comma-separated field list in the same order as the fields are
* in database. */
{
char *split = chromTable(conn, table);
struct slName *fieldList = sqlListFields(conn, split);
makeOrderedCommaFieldList(fieldList, dtfList, dy);
slFreeList(&fieldList);
freez(&split);
}
struct dMatrix *dataFromTable(char *tableName, char *query)
/* Read data from table name with rowname. */
{
struct sqlConnection *conn = hAllocConn();
struct slName *colNames = sqlListFields(conn, tableName);
struct slName *name = NULL;
int count = 0;
struct dMatrix *dM = NULL;
struct sqlResult *sr = NULL;
char **row = NULL;
int colIx = 0;
/* Allocate some initial memory. */
AllocVar(dM);
dM->colCount = slCount(colNames) -1;
dM->rowCount = 1;
AllocArray(dM->matrix, dM->rowCount);
AllocArray(dM->rowNames, dM->rowCount);
AllocArray(dM->matrix[0], dM->colCount);
AllocArray(dM->colNames, dM->colCount);
for(name = colNames->next; name != NULL; name = name->next)
dM->colNames[count++] = cloneString(name->name);
/* Execute our query. */
sr = sqlGetResult(conn, query);
count = 0;
/* Read out the data. */
while((row = sqlNextRow(sr)) != NULL)
{
/* Expand matrix data as necessary. */
if(count > 0)
{
ExpandArray(dM->matrix, dM->rowCount, dM->rowCount+1);
AllocArray(dM->matrix[dM->rowCount], dM->colCount);
ExpandArray(dM->rowNames, dM->rowCount, dM->rowCount+1);
dM->rowCount++;
}
dM->rowNames[count] = cloneString(row[0]);
for(colIx = 0; colIx < dM->colCount; colIx++)
dM->matrix[count][colIx] = atof(row[colIx+1]);
count++;
}
if(count == 0)
errAbort("Didn't find any results for query:\n%s", query);
sqlFreeResult(&sr);
hFreeConn(&conn);
return dM;
}
void checkTableForFields(struct sqlConnection *conn, char *tableName)
/* Do basic checks on the table to make sure it's kosher. i.e. */
/* chrom, chromStart, name all exist along with the table itself. */
{
struct slName *fieldList;
if (!sqlTableExists(conn, tableName))
errAbort("table %s not found.", tableName);
fieldList = sqlListFields(conn, tableName);
if (!slNameInList(fieldList, "chrom"))
errAbort("table %s doesn't have a chrom field. It must have chrom, chromStart, and name at the minimum.", tableName);
if (!slNameInList(fieldList, "chromStart"))
errAbort("table %s doesn't have a chrom field. It must have chrom, chromStart, and name at the minimum.", tableName);
if (!slNameInList(fieldList, "name"))
errAbort("table %s doesn't have a chrom field. It must have chrom, chromStart, and name at the minimum.", tableName);
slNameFreeList(&fieldList);
}
static void makeCtOrderedCommaFieldList(struct joinerDtf *dtfList,
struct dyString *dy)
/* Make comma-separated field list in same order as fields are in
* custom track. */
{
char *track = dtfList->table;
struct customTrack *ct = ctLookupName(track);
char *type = ct->dbTrackType;
struct slName *fieldList = NULL;
if (startsWithWord("makeItems", type) || sameWord("bedDetail", type) || sameWord("pgSnp", type))
{
struct sqlConnection *conn = hAllocConn(CUSTOM_TRASH);
fieldList = sqlListFields(conn, ct->dbTableName);
hFreeConn(&conn);
}
else
{
fieldList = getBedFields(15);
}
makeOrderedCommaFieldList(fieldList, dtfList, dy);
slFreeList(&fieldList);
}
