4919a9ebc4669d592f68cb4feb7c00c650a371d8
angie
  Thu Mar 21 12:04:08 2019 -0700
Add support for 'db.table' notation where necessary.  refs #22440
Most jksql.c functions with table as a parameter already support 'db.table' notation (as opposed to just 'table', e.g. 'hgFixed.cds' as opposed to 'cds').
Here I'm adding a oneShot test program to test all jksql functions that take table as a param; for functions that don't already support 'db.table', parse db.table and temporarily change database for the table query.
The motivation is to support using the customTrash connection for safe-storage customData* databases that will appear as 'db.table' e.g. 'customData01.t1_hgwdev_...'.

diff --git src/hg/lib/jksql.c src/hg/lib/jksql.c
index e459903..af7b4ac 100644
--- src/hg/lib/jksql.c
+++ src/hg/lib/jksql.c
@@ -1761,37 +1761,71 @@
 	if ((sr = sqlGetResultExt(sc->failoverConn, query, &errNo, &err)) == NULL)
 	    {
 	    if (errNo == tableNotFoundCode)
 		return FALSE;
 	    }
 	}
     }
 
 if (!sr)
     errAbort("Mysql error during sqlTableExists(%s) %d: %s", table, errNo, err);
 
 sqlFreeResult(&sr);
 return TRUE;
 }
 
-bool sqlColumnExists(struct sqlConnection *conn, char *tableName, char *column)
-/* return TRUE if column exists in table. tableName can contain sql wildcards  */
+// Note: this is copied from hdb.c's hParseDbDotTable.  Normally I abhor copying but I really
+// don't want to make jksql.c depend on hdb.h...
+void sqlParseDbDotTable(char *dbIn, char *dbDotTable, char *dbOut, size_t dbOutSize,
+                        char *tableOut, size_t tableOutSize)
+/* If dbDotTable contains a '.', then assume it is db.table and parse out into dbOut and tableOut.
+ * If not, then it's just a table; copy dbIn into dbOut and dbDotTable into tableOut. */
 {
+char *dot = strchr(dbDotTable, '.');
+char *table = dbDotTable;
+if (dot != NULL)
+    {
+    safencpy(dbOut, dbOutSize, dbDotTable, dot - dbDotTable);
+    table = &dot[1];
+    }
+else
+    safecpy(dbOut, dbOutSize, dbIn);
+safecpy(tableOut, tableOutSize, table);
+}
+
+// forward declaration to avoid moving code around:
+static boolean sqlConnChangeDbMainOrFailover(struct sqlConnection *sc, char *database, boolean abort);
+
+bool sqlColumnExists(struct sqlConnection *conn, char *table, char *column)
+/* return TRUE if column exists in table. column can contain sql wildcards  */
+{
+// "show columns ... like" does not support db.table names, so temporarily change database
+// if table is really db.table.
+char *connDb = cloneString(sqlGetDatabase(conn));
+char tableDb[1024];
+char tableName[1024];
+sqlParseDbDotTable(connDb, table, tableDb, sizeof tableDb, tableName, sizeof tableName);
 char query[1024];
 sqlSafef(query, 1024, "SHOW COLUMNS FROM `%s` LIKE '%s'", tableName, column);
+boolean changeDb = differentStringNullOk(connDb, tableDb);
+if (changeDb)
+    sqlConnChangeDbMainOrFailover(conn, tableDb, TRUE);
 char buf[1024];
 char *ret = sqlQuickQuery(conn, query, buf, 1024);
+if (changeDb)
+    sqlConnChangeDbMainOrFailover(conn, connDb, TRUE);
+freeMem(connDb);
 return (ret!=NULL);
 }
 
 int sqlTableSizeIfExists(struct sqlConnection *sc, char *table)
 /* Return row count if a table exists, -1 if it doesn't. */
 {
 char query[256];
 struct sqlResult *sr;
 char **row = 0;
 int ret = 0;
 
 sqlSafef(query, sizeof(query), "select count(*) from %s", table);
 if ((sr = sqlUseOrStore(sc, query, DEFAULTGETTER, FALSE)) == NULL)
     return -1;
 row = sqlNextRow(sr);
@@ -1815,35 +1849,47 @@
 	 break;
 	 }
      }
 freeMem(dupe);
 return ok;
 }
 
 boolean sqlTableWildExists(struct sqlConnection *sc, char *table)
 /* Return TRUE if table (which can include SQL wildcards) exists.
  * A bit slower than sqlTableExists. */
 {
 char query[512];
 struct sqlResult *sr;
 char **row;
 boolean exists;
-
-sqlSafef(query, sizeof(query), "show tables like '%s'", table);
+// "show tables" does not support db.table names, so temporarily change database
+// if table is really db.table.
+char *connDb = cloneString(sqlGetDatabase(sc));
+char tableDb[1024];
+char tableName[1024];
+sqlParseDbDotTable(connDb, table, tableDb, sizeof tableDb, tableName, sizeof tableName);
+
+sqlSafef(query, sizeof(query), "show tables like '%s'", tableName);
+boolean changeDb = differentStringNullOk(connDb, tableDb);
+if (changeDb)
+    sqlConnChangeDbMainOrFailover(sc, tableDb, TRUE);
 sr = sqlGetResult(sc, query);
 exists = ((row = sqlNextRow(sr)) != NULL);
 sqlFreeResult(&sr);
+if (changeDb)
+    sqlConnChangeDbMainOrFailover(sc, connDb, TRUE);
+freeMem(connDb);
 return exists;
 }
 
 static char **sqlMaybeNextRow(struct sqlResult *sr, boolean *retOk)
 /* Get next row from query result; set retOk according to error status. */
 {
 char** row = NULL;
 if (sr != NULL)
     {
     monitorEnter();
     row = mysql_fetch_row(sr->result);
     sr->fetchTime += monitorLeave();
     if (mysql_errno(sr->conn->conn) != 0)
 	{
 	if (retOk != NULL)
@@ -2659,41 +2705,47 @@
 }
 
 static boolean sqlConnChangeDbMain(struct sqlConnection *sc, char *database, boolean abort)
 /* change the database of an sql connection */
 {
 int connErr = sqlConnChangeDb(sc, database, abort);
 if (connErr != 0)
     {
     if (abort) 
         sqlAbort(sc, "Couldn't set connection database to %s", database);
     return FALSE;
     }
 return TRUE;
 }
 
+static boolean sqlConnChangeDbMainOrFailover(struct sqlConnection *sc, char *database, boolean abort)
+/* change the database of an sql connection, using failover if applicable */
+{
+if (sc->failoverConn == NULL)
+    return sqlConnChangeDbMain(sc, database, abort);
+else
+    return sqlConnChangeDbFailover(sc, database, abort);
+}
+
 static boolean sqlConnCacheEntrySetDb(struct sqlConnCacheEntry *scce,
                                       char *database,
                                       boolean abort)
 /* set the connect cache and connect to the specified database */
 {
 struct sqlConnection *sc = scce->conn;
 
-if (sc->failoverConn == NULL) 
-    return sqlConnChangeDbMain(sc, database, abort);
-else
-    return sqlConnChangeDbFailover(sc, database, abort);
+return sqlConnChangeDbMainOrFailover(sc, database, abort);
 }
 
 static struct sqlConnCacheEntry *sqlConnCacheFindFree(struct sqlConnCache *cache,
                                                       struct sqlProfile *profile,
                                                       char *database,
                                                       boolean matchDatabase)
 /* find a free entry associated with profile and database. Return NULL if no
  * entries are available.  Will attempt to match database if requested, this
  * includes connections to no database (database==NULL). */
 {
 struct sqlConnCacheEntry *scce;
 for (scce = cache->entries; scce != NULL; scce = scce->next)
     {
     if (!scce->inUse && (profile == scce->profile)
         && ((!matchDatabase) || sqlConnCacheEntryDbMatch(scce, database)))
@@ -3082,74 +3134,99 @@
 	    break;
 	    }
 	}
     }
 return updateFieldIndex;
 }
 
 char *sqlTableUpdate(struct sqlConnection *conn, char *table)
 /* Get last update time for table as an SQL string 
  * Note: does NOT work on innoDB! */
 {
 char query[512], **row;
 struct sqlResult *sr;
 int updateIx;
 char *ret;
-sqlSafef(query, sizeof(query), "show table status like '%s'", table);
+// "show table status" does not support db.table names, so temporarily change database
+// if table is really db.table.
+char *connDb = cloneString(sqlGetDatabase(conn));
+char tableDb[1024];
+char tableName[1024];
+sqlParseDbDotTable(connDb, table, tableDb, sizeof tableDb, tableName, sizeof tableName);
+boolean changeDb = differentStringNullOk(connDb, tableDb);
+sqlSafef(query, sizeof(query), "show table status like '%s'", tableName);
 // the failover strategy for failoverConn does not work for this command, 
 // as it never returns an error. So we run this on the failover server
 // if we have a failover connection and the table is not on the main server
-if (conn->failoverConn && !sqlTableExistsOnMain(conn, table))
+boolean useFailOver = conn->failoverConn && !sqlTableExistsOnMain(conn, tableName);
+if (useFailOver)
     {
     sqlConnectIfUnconnected(conn->failoverConn, TRUE);
     monitorPrintInfo(conn->failoverConn, "SQL_TABLE_STATUS_FAILOVER");
+    if (changeDb)
+        sqlConnChangeDb(conn->failoverConn, tableDb, TRUE);
     sr = sqlGetResult(conn->failoverConn, query);
     }
 else
+    {
+    if (changeDb)
+        sqlConnChangeDb(conn, tableDb, TRUE);
     sr = sqlGetResult(conn, query);
+    }
 updateIx = getUpdateFieldIndex(sr);
 row = sqlNextRow(sr);
 if (row == NULL)
-    errAbort("Database table %s doesn't exist", table);
+    sqlAbort(conn, "Database table %s doesn't exist", table);
 ret = cloneString(row[updateIx]);
 sqlFreeResult(&sr);
+if (changeDb)
+    {
+    if (useFailOver)
+        sqlConnChangeDb(conn->failoverConn, connDb, TRUE);
+    else
+        sqlConnChangeDb(conn, connDb, TRUE);
+    }
+freeMem(connDb);
 return ret;
 }
 
 time_t sqlTableUpdateTime(struct sqlConnection *conn, char *table)
 /* Get last update time for table.
  * Note: does NOT work on innoDB! */
 {
 char *date = sqlTableUpdate(conn, table);
 time_t time = sqlDateToUnixTime(date);
 freeMem(date);
 return time;
 }
 
 static char *sqlTablePropertyFromSchema(struct sqlConnection *conn, char *db, char *table, char *field)
 /* Get table property. Table must exist or will abort. */
 {
 char query[512], **row;
 struct sqlResult *sr;
 char *ret;
+char tableDb[1024];
+char tableName[1024];
+sqlParseDbDotTable(db, table, tableDb, sizeof tableDb, tableName, sizeof tableName);
 sqlSafef(query, sizeof(query), 
     "SELECT %s FROM information_schema.TABLES"
-    " WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'", field, db, table);
+    " WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'", field, tableDb, tableName);
 // the failover strategy for failoverConn does not work for this command, 
 // as it never returns an error. So we run this on the failover server
 // if we have a failover connection and the table is not on the main server
-if (conn->failoverConn && !sqlTableExistsOnMain(conn, table))
+if (conn->failoverConn && !sqlTableExistsOnMain(conn, tableName))
     {
     sqlConnectIfUnconnected(conn->failoverConn, TRUE);
     monitorPrintInfo(conn->failoverConn, "SQL_TABLE_STATUS_FAILOVER");
     sr = sqlGetResult(conn->failoverConn, query);
     }
 else
     sr = sqlGetResult(conn, query);
 row = sqlNextRow(sr);
 if (row == NULL)
     errAbort("Database table %s or field %s doesn't exist", table, field);
 ret = cloneString(row[0]);
 sqlFreeResult(&sr);
 return ret;
 }