5e8e0ed53c05f2ef9b51ce897440bae308367f47 chmalee Wed Feb 9 14:45:54 2022 -0800 Add csv output option to table browser to support opening files in Excel, refs #27623. Also change the output filename message to hint that Excel can auto-recognize .csv extensions and behave appropriately. Lastly, fix output dropdown selection to properly warn on update when using GTF output option or a snp table. diff --git src/hg/hgTables/hgTables.h src/hg/hgTables/hgTables.h index d3bba3c..a755d63 100644 --- src/hg/hgTables/hgTables.h +++ src/hg/hgTables/hgTables.h @@ -172,31 +172,31 @@ /* Get autoSQL description if any associated with table. */ char *connectingTableForTrack(char *rawTable); /* Return table name to use with all.joiner for track. * You can freeMem this when done. */ char *chromTable(struct sqlConnection *conn, char *table); /* Get chr1_table if it exists, otherwise table. * You can freeMem this when done. */ char *getDbTable(char *db, char *table); /* If table already contains its real database as a dot-prefix, then * return a clone of table; otherwise alloc and return db.table . */ void doTabOutTable(char *database, char *table, FILE *f, - struct sqlConnection *conn, char *fields); + struct sqlConnection *conn, char *fields, char outSep); /* Do tab-separated output on table. */ struct slName *fullTableFields(char *db, char *table); /* Return list of fields in db.table.field format. */ struct bed *getFilteredBeds(struct sqlConnection *conn, char *table, struct region *region, struct lm *lm, int *retFieldCount); /* Get list of beds on single region that pass filtering. */ void bedSqlFieldsExceptForChrom(struct hTableInfo *hti, int *retFieldCount, char **retFields); /* Given tableInfo figure out what fields are needed to * add to a database query to have information to create * a bed. The chromosome is not one of these fields - we @@ -269,35 +269,36 @@ boolean isSqlIntType(char *type); /* Return TRUE if it is an integer SQL type. */ void sqlFieldTypeFree(struct sqlFieldType **pFt); /* Free resources used by sqlFieldType */ void sqlFieldTypeFreeList(struct sqlFieldType **pList); /* Free a list of dynamically allocated sqlFieldType's */ struct sqlFieldType *sqlListFieldsAndTypes(struct sqlConnection *conn, char *table); /* Get list of fields including their names and types. The type currently is just * a MySQL type string. */ /* ------------- Functions related to joining and filtering ------------*/ -void tabOutSelectedFields( +void sepOutSelectedFields( char *primaryDb, /* The primary database. */ char *primaryTable, /* The primary table. */ FILE *f, /* file for output, null for stdout */ - struct slName *fieldList); /* List of db.table.field */ + struct slName *fieldList, /* List of db.table.field */ + char outSep); /* The separator for the output */ /* Do tab-separated output on selected fields, which may * or may not include multiple tables. */ boolean anyFilter(); /* Return TRUE if any filter set. */ struct joinerDtf *filteringTables(); /* Get list of tables we're filtering on as joinerDtf list (with * the field entry NULL). */ char *filterClause(char *db, char *table, char *chrom, char *extraClause); /* Get filter clause (something to put after 'where') * for table */ char *filterFieldVarName(char *db, char *table, char *field, char *type); @@ -437,30 +438,31 @@ #define hgtaDoSubmitUserRegions "hgta_doSubmitUserRegions" #define hgtaDoClearUserRegions "hgta_doClearUserRegions" #define hgtaRegionTypeUserRegions "userRegions" #define hgtaRegionTypeEncode "encode" #define hgtaRegionTypeGenome "genome" #define hgtaRegionTypeRange "range" #define hgtaDoPalOut "hgta_palOut" #define hgtaDoPal "hgta_doPal" /* Other CGI variables. */ #define hgtaGroup "hgta_group" #define hgtaTrack "hgta_track" #define hgtaSelDb "hgta_selDb" #define hgtaRegionType "hgta_regionType" #define hgtaCompressType "hgta_compressType" +#define hgtaOutSep "hgta_outSep" #define hgtaRange "position" #define hgtaOffsetStart "hgta_offsetStart" #define hgtaOffsetEnd "hgta_offsetEnd" #define hgtaOffsetRelativeTo "hgta_offsetRelativeTo" #define hgtaOutputType "hgta_outputType" #define hgtaOutputPad "hgta_outputPad" #define hgtaOutFileName "hgta_outFileName" #define hgtaDatabase "hgta_database" /* In most cases use "db" */ #define hgtaTable "hgta_table" #define hgtaHistoTable "hgta_histoTable" #define hgtaPastedIdentifiers "hgta_pastedIdentifiers" #define hgtaIdentifierFile "hgta_identifierFile" #define hgtaIdentifierTable "hgta_identifierTable" #define hgtaIdentifierDb "hgta_identifierDb" #define hgtaFilterTable "hgta_filterTable" @@ -545,30 +547,32 @@ #define outSelectedFields "selectedFields" #define outSchema "schema" #define outSummaryStats "stats" #define outBed "bed" #define outGff "gff" #define outCustomTrack "customTrack" #define outHyperlinks "hyperlinks" #define outWigData "wigData" #define outWigBed "wigBed" #define outChromGraphData "chromGraphData" #define outMicroarrayNames "microarrayNames" #define outMicroarrayGroupings "microarrayGroupings" #define outGalaxy "galaxyQuery" #define outMaf "maf" #define outPalOptions "fasta" +#define outTab "tab" +#define outCsv "csv" /* For disabling tables in 'tableBrowser noGenome' settings, when region is genome */ #define NO_GENOME_CLASS " class=\"hgtaNoGenome\" title=\"Position range queries only\"" /* --------- Identifier list handling stuff. ------------ */ char *identifierFileName(); /* File name identifiers are in, or NULL if no such file. */ struct hash *identifierHash(char *db, char *table); /* Return hash full of identifiers from the given table (or NULL). */ char *getIdField(char *db, struct trackDb *track, char *table, struct hTableInfo *hti); /* Get ID field for table, or NULL if none. FreeMem result when done */ @@ -778,62 +782,62 @@ struct hTableInfo *bigBedToHti(char *table, struct sqlConnection *conn); /* Get fields of bigBed into hti structure. */ struct slName *bigBedGetFields(char *table, struct sqlConnection *conn); /* Get fields of bigBed as simple name list. */ struct sqlFieldType *bigBedListFieldsAndTypes(struct trackDb *tdb, struct sqlConnection *conn); /* Get fields of bigBed as list of sqlFieldType. */ struct bed *bigBedGetFilteredBedsOnRegions(struct sqlConnection *conn, char *db, char *table, struct region *regionList, struct lm *lm, int *retFieldCount); /* Get list of beds from bigBed, in all regions, that pass filtering. */ -void bigBedTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f); +void bigBedTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f, char outSep); /* Print out selected fields from Big Bed. If fields is NULL, then print out all fields. */ struct slName *randomBigBedIds(char *table, struct sqlConnection *conn, int count); /* Return some arbitrary IDs from a bigBed file. */ void showSchemaBigBed(char *table, struct trackDb *tdb); /* Show schema on bigBed. */ /* More stuff in bigBed.c that makes use of autoSql files. */ struct sqlFieldType *sqlFieldTypesFromAs(struct asObject *as); /* Convert asObject to list of sqlFieldTypes */ /* HAL stuff from hal.c */ boolean isHalTable(char *table); /* Return TRUE if table corresponds to a HAL file. */ struct slName *halGetFields(char *table); /* Get fields of hal as simple name list. */ void halTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f); /* BAM stuff from bam.c */ boolean isLongTabixTable(char *table); /* Return TRUE if table corresponds to a longTabix file. */ struct slName *getLongTabixFields(); /* Get fields of bam as simple name list. */ -void longTabixTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f); +void longTabixTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f, char outSep); /* Print out selected fields from long tabix. If fields is NULL, then print out all fields. */ struct hTableInfo *longTabixToHti(char *table); /* Get standard fields of BAM into hti structure. */ struct bed *longTabixGetFilteredBedsOnRegions(struct sqlConnection *conn, char *db, char *table, struct region *regionList, struct lm *lm, int *retFieldCount); /* Get list of beds from long tabix, in all regions, that pass filtering. */ struct sqlFieldType *longTabixListFieldsAndTypes(); /* Get fields of BAM as list of sqlFieldType. */ void showSchemaLongTabix(char *table, struct trackDb *tdb); /* Show schema on long tabix. */ @@ -841,60 +845,60 @@ boolean isBamTable(char *table); /* Return TRUE if table corresponds to a BAM file. */ struct slName *bamGetFields(); /* Get fields of bam as simple name list. */ struct sqlFieldType *bamListFieldsAndTypes(); /* Get fields of BAM as list of sqlFieldType. */ struct hTableInfo *bamToHti(char *table); /* Get standard fields of BAM into hti structure. */ void showSchemaBam(char *table, struct trackDb *tdb); /* Show schema on bam. */ -void bamTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f); +void bamTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f, char outSep); /* Print out selected fields from BAM. If fields is NULL, then print out all fields. */ struct bed *bamGetFilteredBedsOnRegions(struct sqlConnection *conn, char *db, char *table, struct region *regionList, struct lm *lm, int *retFieldCount); /* Get list of beds from BAM, in all regions, that pass filtering. */ struct slName *randomBamIds(char *table, struct sqlConnection *conn, int count); /* Return some semi-random qName based IDs from a BAM file. */ /* Hi-C stuff from hic.c */ struct hTableInfo *hicToHti(char *table); /* Get standard fields of hic into hti structure. */ boolean isHicTable(char *table); /* Return TRUE if table corresponds to a hic file. */ struct slName *hicGetFields(); /* Get fields of hic as simple name list. We represent hic with an interact structure, so * this is really just an interact as object. */ struct sqlFieldType *hicListFieldsAndTypes(); /* Get fields of hic as list of sqlFieldType (again, this is really just the list of interact fields. */ void showSchemaHic(char *table, struct trackDb *tdb); /* Show schema on hic. */ -void hicTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f); +void hicTabOut(char *db, char *table, struct sqlConnection *conn, char *fields, FILE *f, char outSep); /* Print out selected fields from hic. If fields is NULL, then print out all fields. */ struct bed *hicGetFilteredBedsOnRegions(struct sqlConnection *conn, char *db, char *table, struct region *regionList, struct lm *lm, int *retFieldCount); /* Get list of beds from HIC, in all regions, that pass filtering. */ void hicMainPageConfig(struct cart *cart, struct trackDb *tdb); /* Display Hi-C-specific track configuration options (resolution, normalization) on * the main page. */ /* VCF (Variant Call Format) stuff from vcf.c */ extern char *vcfDataLineAutoSqlString; @@ -947,35 +951,35 @@ struct bed *customTrackGetFilteredBeds(char *db, char *name, struct region *regionList, struct lm *lm, int *retFieldCount); /* Get list of beds from custom track of given name that are * in current regions and that pass filters. You can bedFree * this when done. */ struct customTrack *newCt(char *ctName, char *ctDesc, int visNum, char *ctUrl, int fields); /* Make a new custom track record for the query results. */ struct hTableInfo *ctToHti(struct customTrack *ct); /* Create an hTableInfo from a customTrack. */ void doTabOutDb( char *db, char *dbVarName, char *table, char *tableVarName, - FILE *f, struct sqlConnection *conn, char *fields); + FILE *f, struct sqlConnection *conn, char *fields, char outSep); /* Do tab-separated output on fields of a single table. */ void doTabOutCustomTracks(char *db, char *table, struct sqlConnection *conn, - char *fields, FILE *f); + char *fields, FILE *f, char outSep); /* Print out selected fields from custom track. If fields * is NULL, then print out all fields. */ /* ----------- Bed/joining stuff -------------- */ struct bed *getRegionAsBed( char *db, char *table, /* Database and table. */ struct region *region, /* Region to get data for. */ char *filter, /* Filter to add to SQL where clause if any. */ struct hash *idHash, /* Restrict to id's in this hash if non-NULL. */ struct lm *lm, /* Where to allocate memory. */ int *retFieldCount); /* Number of fields. */ /* Return a bed list of all items in the given range in table. * Cleanup result via lmCleanup(&lm) rather than bedFreeList. */