d00cd1b5dd768c8edf09b8a2f2ccb48786c8d1ff
hiram
  Fri Feb 8 14:01:15 2019 -0800
beginning to use "desc" SQL query on table to get column header names refs #18869

diff --git src/hg/hubApi/hubApi.c src/hg/hubApi/hubApi.c
index 40f8e25..4f16665 100644
--- src/hg/hubApi/hubApi.c
+++ src/hg/hubApi/hubApi.c
@@ -70,69 +70,114 @@
 static char *defaultHub = "Plants";
 static char *defaultDb = "ce11";
 static long enteredMainTime = 0;	/* will become = clock1000() on entry */
 		/* to allow calculation of when to bail out, taking too long */
 static long timeOutSeconds = 100;
 static boolean timedOut = FALSE;
 
 /* ######################################################################### */
 
 static void jsonInteger(FILE *f, char *tag, long long value)
 /* output one json interger: "tag":value appropriately quoted and encoded */
 {
 fprintf(f,"\"%s\":%lld",tag, value);
 }
 
-static void jsonStringOut(FILE *f, char *tag, char *value)
-/* output one json string: "tag":"value" appropriately quoted and encoded */
+static void jsonStringPrint(FILE *f, char *value)
+/* escape string for output */
 {
-fprintf(f,"\"%s\":",tag);
 char *a = jsonStringEscape(value);
 if (isEmpty(a))
     fprintf(f, "%s", "null");
 else
     fprintf(f, "\"%s\"", a);
 freeMem(a);
 }
 
+static void jsonTagValue(FILE *f, char *tag, char *value)
+/* output one json string: "tag":"value" appropriately quoted and encoded */
+{
+fprintf(f,"\"%s\":",tag);
+jsonStringPrint(f, value);
+}
+
 static void jsonStartOutput(FILE *f)
 /* begin json output */
 {
 fputc('{',f);
-jsonStringOut(f, "source", "UCSantaCruz");
+jsonTagValue(f, "source", "UCSantaCruz");
 fputc(',',f);
 }
 
+static void jsonErrAbort(char *format, ...)
+/* Issue an error message in json format. */
+{
+char errMsg[2048];
+va_list args;
+va_start(args, format);
+vsnprintf(errMsg, sizeof(errMsg), format, args);
+fputc('{',stdout);
+jsonTagValue(stdout, "error", errMsg);
+fputc('}',stdout);
+}
+
+static void hubPublicJsonData(struct hubPublic *el, FILE *f)
+/* Print array data for one row from hubPublic table, order here
+ * must be same as was stated in the columnName header element
+ *  TODO: need to figure out how to use the order of the columns as
+ *        they are in the 'desc' request
+ */
+{
+fputc('[',f);
+jsonStringPrint(f, el->hubUrl);
+fputc(',',f);
+jsonStringPrint(f, el->shortLabel);
+fputc(',',f);
+jsonStringPrint(f, el->longLabel);
+fputc(',',f);
+jsonStringPrint(f, el->registrationTime);
+fputc(',',f);
+fprintf(f, "%lld", (long long)el->dbCount);
+fputc(',',f);
+jsonStringPrint(f, el->dbList);
+fputc(',',f);
+jsonStringPrint(f, el->descriptionUrl);
+fputc(']',f);
+}
+
+#ifdef NOT
+/* This function should be in hg/lib/hubPublic.c */
 static void hubPublicJsonOutput(struct hubPublic *el, FILE *f)
 /* Print out hubPublic element in JSON format. */
 {
 fputc('{',f);
-jsonStringOut(f, "hubUrl", el->hubUrl);
+jsonTagValue(f, "hubUrl", el->hubUrl);
 fputc(',',f);
-jsonStringOut(f, "shortLabel", el->shortLabel);
+jsonTagValue(f, "shortLabel", el->shortLabel);
 fputc(',',f);
-jsonStringOut(f, "longLabel", el->longLabel);
+jsonTagValue(f, "longLabel", el->longLabel);
 fputc(',',f);
-jsonStringOut(f, "registrationTime", el->registrationTime);
+jsonTagValue(f, "registrationTime", el->registrationTime);
 fputc(',',f);
 jsonInteger(f, "dbCount", el->dbCount);
 fputc(',',f);
-jsonStringOut(f, "dbList", el->dbList);
+jsonTagValue(f, "dbList", el->dbList);
 fputc(',',f);
-jsonStringOut(f, "descriptionUrl", el->descriptionUrl);
+jsonTagValue(f, "descriptionUrl", el->descriptionUrl);
 fputc('}',f);
 }
+#endif
 
 static int publicHubCmpCase(const void *va, const void *vb)
 /* Compare two slNames, ignore case. */
 {
 const struct hubPublic *a = *((struct hubPublic **)va);
 const struct hubPublic *b = *((struct hubPublic **)vb);
 return strcasecmp(a->shortLabel, b->shortLabel);
 }
 
 static void publicHubSortCase(struct hubPublic **pList)
 /* Sort slName list, ignore case. */
 {
 slSort(pList, publicHubCmpCase);
 }
 
@@ -494,69 +539,100 @@
 char query[1024];
 struct sqlConnection *conn = hConnectCentral();
 // Build a query to select the hubUrl for the given shortLabel
 sqlSafef(query, sizeof(query), "select hubUrl from %s where shortLabel='%s'",
       hubPublicTableName(), shortLabel);
 if (! sqlQuickQuery(conn, query, hubUrl, sizeof(hubUrl)))
     hubUrl[0] = 0;
 hDisconnectCentral(&conn);
 return cloneString(hubUrl);
 }
 
 static void dbDbJsonOutput(struct dbDb *el, FILE *f)
 /* Print out hubPublic element in JSON format. */
 {
 fputc('{',f);
-jsonStringOut(f, "name", el->name);
+jsonTagValue(f, "name", el->name);
 fputc(',',f);
-jsonStringOut(f, "description", el->description);
+jsonTagValue(f, "description", el->description);
 fputc(',',f);
-jsonStringOut(f, "nibPath", el->nibPath);
+jsonTagValue(f, "nibPath", el->nibPath);
 fputc(',',f);
-jsonStringOut(f, "organism", el->organism);
+jsonTagValue(f, "organism", el->organism);
 fputc(',',f);
-jsonStringOut(f, "defaultPos", el->defaultPos);
+jsonTagValue(f, "defaultPos", el->defaultPos);
 fputc(',',f);
 jsonInteger(f, "active", el->active);
 fputc(',',f);
 jsonInteger(f, "orderKey", el->orderKey);
 fputc(',',f);
-jsonStringOut(f, "genome", el->genome);
+jsonTagValue(f, "genome", el->genome);
 fputc(',',f);
-jsonStringOut(f, "scientificName", el->scientificName);
+jsonTagValue(f, "scientificName", el->scientificName);
 fputc(',',f);
-jsonStringOut(f, "htmlPath", el->htmlPath);
+jsonTagValue(f, "htmlPath", el->htmlPath);
 fputc(',',f);
 jsonInteger(f, "hgNearOk", el->hgNearOk);
 fputc(',',f);
 jsonInteger(f, "hgPbOk", el->hgPbOk);
 fputc(',',f);
-jsonStringOut(f, "sourceName", el->sourceName);
+jsonTagValue(f, "sourceName", el->sourceName);
 fputc(',',f);
 jsonInteger(f, "taxId", el->taxId);
 fputc('}',f);
 }
 
+static boolean tableColumns(FILE *f, char *table)
+/* output the column names for the given table
+ * return: TRUE on error, FALSE on success
+ */
+{
+fprintf(f, "\"columnNames\":[");
+char query[1024];
+struct sqlConnection *conn = hConnectCentral();
+sqlSafef(query, sizeof(query), "desc %s", table);
+struct sqlResult *sr = sqlGetResult(conn, query);
+char **row;
+row = sqlNextRow(sr);
+if (NULL == row)
+    {
+    jsonErrAbort("ERROR: can not 'desc' table '%s'\n", table);
+    return TRUE;
+    }
+fprintf(f, "\"%s\"",row[0]);
+while (row != NULL)
+    {
+    row = sqlNextRow(sr);
+    if (row)
+       fprintf(f, ",\"%s\"",row[0]);
+    }
+sqlFreeResult(&sr);
+hDisconnectCentral(&conn);
+fprintf(f, "],");
+return FALSE;
+}
+
 static void jsonPublicHubs()
 /* output the hubPublic SQL table */
 {
 struct hubPublic *el = publicHubList;
 jsonStartOutput(stdout);
-printf("\"publicHubs\":[");
+tableColumns(stdout, hubPublicTableName());
+printf("\"publicHubData\":[");
 for ( ; el != NULL; el = el->next )
     {
-    hubPublicJsonOutput(el, stdout);
+    hubPublicJsonData(el, stdout);
     if (el->next)
        printf(",");
     }
 printf("]}\n");
 }
 
 static int dbDbCmpName(const void *va, const void *vb)
 /* Compare two dbDb elements: name, ignore case. */
 {
 const struct dbDb *a = *((struct dbDb **)va);
 const struct dbDb *b = *((struct dbDb **)vb);
 return strcasecmp(a->name, b->name);
 }
 
 static struct dbDb *ucscDbDb()
@@ -583,55 +659,43 @@
 /* output the dbDb SQL table */
 {
 struct dbDb *dbList = ucscDbDb();
 struct dbDb *el;
 jsonStartOutput(stdout);
 printf("\"ucscGenomes\":[");
 for ( el=dbList; el != NULL; el = el->next )
     {
     dbDbJsonOutput(el, stdout);
     if (el->next)
        printf(",");
     }
 printf("]}\n");
 }
 
-static void jsonErrAbort(char *format, ...)
-/* Issue an error message in json format. */
-{
-char errMsg[2048];
-va_list args;
-va_start(args, format);
-vsnprintf(errMsg, sizeof(errMsg), format, args);
-fputc('{',stdout);
-jsonStringOut(stdout, "error", errMsg);
-fputc('}',stdout);
-}
-
 static void chromInfoJsonOutput(char *db, FILE *f, char *track)
 {
 if (track)
     {
     struct sqlConnection *conn = hAllocConn(db);
     if (! sqlTableExists(conn, track))
 	jsonErrAbort("ERROR: endpoint: /list/chromosomes?db=%&table=%s ERROR table does not exist", db, track);
     if (sqlColumnExists(conn, track, "chrom"))
 	{
 	jsonStartOutput(f);
-	jsonStringOut(f, "genome", db);
+	jsonTagValue(f, "genome", db);
 	fputc(',',f);
-	jsonStringOut(f, "track", track);
+	jsonTagValue(f, "track", track);
 	fputc(',',f);
         struct slPair *list = NULL;
 	char query[2048];
         sqlSafef(query, sizeof(query), "select distinct chrom from %s", track);
 	struct sqlResult *sr = sqlGetResult(conn, query);
 	char **row;
 	while ((row = sqlNextRow(sr)) != NULL)
     	{
             int size = hChromSize(db, row[0]);
 	    slAddHead(&list, slPairNew(row[0], intToPt(size)));
     	}
 	sqlFreeResult(&sr);
         slPairIntSort(&list);
         slReverse(&list);
         jsonInteger(f, "chromCount", slCount(list));
@@ -644,103 +708,99 @@
 		fputc(',',f);
 	    }
         fputc('}',f);
 	}
     else
 	{
 	jsonErrAbort("ERROR: table '%s' is not a position table, no chromosomes for genome: '%s'", track, db);
 	}
     hFreeConn(&conn);
     }
 else
     {
     struct chromInfo *ciList = createChromInfoList(NULL, db);
     struct chromInfo *el = ciList;
     jsonStartOutput(f);
-    jsonStringOut(f, "genome", db);
+    jsonTagValue(f, "genome", db);
     fputc(',',f);
     jsonInteger(f, "chromCount", slCount(ciList));
     fputc(',',f);
     for ( ; el != NULL; el = el->next )
 	{
         jsonInteger(f, el->chrom, el->size);
 	if (el->next)
            fputc(',',f);
 	}
     fputc('}',f);
     }
 }
 
 static void chromListJsonOutput(char *db, FILE *f)
 /* return chromsome list from specified UCSC database name,
  * can be for a specific track if cgiVar(track) exists, otherwise,
  * the chrom list is from the chromInfo table.
  */
 {
 char *track = cgiOptionalString("track");
 chromInfoJsonOutput(db, f, track);
 }	/*	static void chromListJsonOutput(char *db, FILE *f)	*/
 
 static void trackDbJsonOutput(char *db, FILE *f)
 /* return track list from specified UCSC database name */
 {
 struct trackDb *tdbList = hTrackDb(db);
 struct trackDb *el;
 jsonStartOutput(f);
-jsonStringOut(f, "db", db);
+jsonTagValue(f, "db", db);
 fputc(',',f);
 fprintf(f, "\"tracks\":[");
 for (el = tdbList; el != NULL; el = el->next )
     {
-    char *a = jsonStringEscape(el->track);
-    printf("\"%s\"", a);
-    freeMem(a);
+    jsonStringPrint(f, el->track);
     if (el->next)
 	fputc(',',f);
     }
 fprintf(f, "]}\n");
 }	/*	static void trackDbJsonOutput(char *db, FILE *f)	*/
 
 #define MAX_PATH_INFO 32
 static void apiList(char *words[MAX_PATH_INFO])
 /* 'list' function words[1] is the subCommand */
 {
 if (sameWord("publicHubs", words[1]))
     jsonPublicHubs();
 else if (sameWord("ucscGenomes", words[1]))
     jsonDbDb();
 else if (sameWord("hubGenomes", words[1]))
     {
     char *hubUrl = cgiOptionalString("hubUrl");
     if (isEmpty(hubUrl))
 	jsonErrAbort("ERROR: must supply hubUrl='http:...' some URL to a hub for /list/genomes\n");
 
     struct trackHub *hub = trackHubOpen(hubUrl, "");
     if (hub->genomeList)
 	{
         jsonStartOutput(stdout);
-	jsonStringOut(stdout, "hubUrl", hubUrl);
+	jsonTagValue(stdout, "hubUrl", hubUrl);
 	fputc(',',stdout);
 	printf("\"genomes\":[");
 	struct slName *theList = genomeList(hub, NULL, NULL);
 	slNameSort(&theList);
 	struct slName *el = theList;
 	for ( ; el ; el = el->next )
 	    {
-	    char *a = jsonStringEscape(el->name);
-	    printf("\"%s\"", a);
-	    freeMem(a);
+	    jsonStringPrint(stdout, el->name);
 	    if (el->next)
 		fputc(',',stdout);
 	    }
 	printf("]}\n");
 	}
     }
 else if (sameWord("tracks", words[1]))
     {
     char *hubUrl = cgiOptionalString("hubUrl");
     char *genome = cgiOptionalString("genome");
     char *db = cgiOptionalString("db");
     if (isEmpty(hubUrl) && isEmpty(db))
       jsonErrAbort("ERROR: must supply hubUrl or db name to return track list");
     if (isEmpty(hubUrl))	// missing hubUrl implies UCSC database
 	{
@@ -748,41 +808,39 @@
 	return;
 	}
     if (isEmpty(genome) || isEmpty(hubUrl))
 	{
         if (isEmpty(genome))
 	    warn("# must supply genome='someName' the name of a genome in a hub for /list/tracks\n");
 	if (isEmpty(hubUrl))
             jsonErrAbort("ERROR: must supply hubUrl='http:...' some URL to a hub for /list/genomes\n");
 	}
     struct trackHub *hub = trackHubOpen(hubUrl, "");
     if (hub->genomeList)
 	{
 	struct slName *dbTrackList = NULL;
 	(void) genomeList(hub, &dbTrackList, genome);
         jsonStartOutput(stdout);
-	jsonStringOut(stdout, "hubUrl", hubUrl);
+	jsonTagValue(stdout, "hubUrl", hubUrl);
 	fputc(',',stdout);
-	jsonStringOut(stdout, "genome", genome);
+	jsonTagValue(stdout, "genome", genome);
 	fputc(',',stdout);
 	slNameSort(&dbTrackList);
 	struct slName *el = dbTrackList;
 	for ( ; el != NULL; el = el->next )
 	    {
-	    char *a = jsonStringEscape(el->name);
-	    printf("\"%s\"", a);
-	    freeMem(a);
+            jsonStringPrint(stdout, el->name);
 	    if (el->next)
 		fputc(',',stdout);
 	    }
 	printf("]}\n");
 	}
     }
 else if (sameWord("chromosomes", words[1]))
     {
     char *hubUrl = cgiOptionalString("hubUrl");
 //    char *genome = cgiOptionalString("genome");
     char *db = cgiOptionalString("db");
     if (isEmpty(hubUrl) && isEmpty(db))
         jsonErrAbort("ERROR: must supply hubUrl or db name to return chromosome list");
 
     if (isEmpty(hubUrl))	// missing hubUrl implies UCSC database