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; }