void cloneOldDatabase(char *host, char *user, char *password, char *newDatabase, char *oldDatabase)
/* Write out old database and read in new one. */
{
struct sqlConnection *oldConn = sqlConnectRemote(host, user, password, oldDatabase);
struct sqlConnection *newConn = sqlConnectRemote(host, user, password, newDatabase);
fakeCloneOldTable(oldConn, newConn, userTable);
}
void vgLoadJax(char *visiGeneDir, char *jaxDb, char *outDir)
/* vgLoadJax - Load visiGene database from jackson database. */
{
struct sqlConnection *conn = sqlConnect(jaxDb);
struct sqlConnection *conn2 = sqlConnect(jaxDb);
struct sqlConnection *connSp = sqlConnect("uniProt");
char inFull[PATH_LEN];
char *jaxPath = "inSitu/Mouse/jax";
char inJax[PATH_LEN];
#ifdef UNUSED
/* won't work yet */
char* host = "genome-test";
char* user = cfgOptionEnv("HGDB_USER", "db.user");
char* password = cfgOptionEnv("HGDB_PASSWORD", "db.password");
connSp = sqlConnectRemote(host, user, password, "uniProt");
#endif
safef(inFull, sizeof(inFull), "%s/full", visiGeneDir);
safef(inJax, sizeof(inJax), "%s/%s", inFull, jaxPath);
submitToDir(conn, conn2, connSp, outDir, inJax);
sqlDisconnect(&connSp);
sqlDisconnect(&conn2);
sqlDisconnect(&conn);
}
void createFakeEntries(char *host, char *user, char *password, char *database, int count)
/* create count new fake entries in database. */
{
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
int i;
for (i=0; i<count; ++i)
createFakeEntry(conn, randomFakeSize());
sqlDisconnect(&conn);
}
void createEmptyDatabase(char *host, char *user, char *password, char *database)
/* Create a new database with no tables. */
{
struct sqlConnection *conn = sqlConnectRemote(host, user, password, NULL);
char query[512];
sqlSafef(query, sizeof(query), "create database %s", database);
sqlUpdate(conn, query);
sqlDisconnect(&conn);
}
struct sqlConnection *dbConnect()
/* Get a connection do database specified in ~/.hg.conf */
{
struct sqlConnection *conn = NULL;
char *hdbHost = cfgOption("db.host");
char *hdbUser = cfgOption("db.user");
char *hdbPassword = cfgOption("db.password");
char *hdbDatabase = cfgOption("db.database");
if(hdbDatabase == NULL)
hdbDatabase = hDefaultDb();
if(hdbHost == NULL || hdbUser == NULL || hdbPassword == NULL)
errAbort("Cannot read in connection setting from configuration file.");
conn = sqlConnectRemote(hdbHost, hdbUser, hdbPassword, hdbDatabase);
return conn;
}
void dropUserTable(char *host, char *user, char *password, char *database)
/* Drop database if it exists. */
{
if (databaseExists(host, user, password, database))
{
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
if (sqlTableExists(conn, userTable))
{
char query[512];
sqlSafef(query, sizeof(query), "drop table %s", userTable);
sqlUpdate(conn, query);
}
sqlDisconnect(&conn);
}
}
void cleanupTable(char *host, char *user, char *password, char *database, char *table, int target)
/* Trim table to target most recent items. */
{
uglyTime(NULL);
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
struct dyString *query = dyStringNew(0);
sqlDyStringPrintf(query, "select count(*) from %s", table);
int initialCount = sqlQuickNum(conn, query->string);
uglyTime("%d initial vs %d target", initialCount, target);
if (target < initialCount)
{
/* Query database for id's ordered by age */
dyStringClear(query);
sqlDyStringPrintf(query, "select id,now()-lastUse age from %s order by age", table);
struct sqlResult *sr = sqlGetResult(conn, query->string);
/* Build up new query that'll delete old things. */
dyStringClear(query);
sqlDyStringPrintf(query, "delete from %s where id in (", table);
int i=0;
boolean addComma = FALSE;
char **row;
while ((row = sqlNextRow(sr)) != NULL)
{
if (++i > target)
{
if (addComma)
dyStringAppendC(query, ',');
else
addComma = TRUE;
dyStringPrintf(query, "'%s'", row[0]);
}
}
dyStringPrintf(query, ")");
sqlFreeResult(&sr);
uglyTime("made delete query %d chars", query->stringSize);
/* Excute deletion */
sqlUpdate(conn, query->string);
uglyTime("deleted");
}
sqlDisconnect(&conn);
}
void createNewFakeDatabase(char *host, char *user, char *password, char *database)
/* Create a fake database with empty fake useDb table. */
{
dropUserTable(host, user, password, database);
if (!databaseExists(host, user, password, database))
createEmptyDatabase(host, user, password, database);
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
char query[1024];
sqlSafef(query, sizeof(query),
"CREATE TABLE %s (\n"
" id integer unsigned not null auto_increment, # Cart ID\n"
" contents longblob not null, # Contents - encoded variables\n"
" reserved tinyint not null, # always 0\n"
" firstUse DATETIME not null, # First time this was used\n"
" lastUse DATETIME not null, # Last time this was used\n"
" useCount int not null, # Number of times used\n"
" #Indices\n"
" PRIMARY KEY(id)\n"
") ENGINE = %s\n", userTable, engine);
sqlUpdate(conn, query);
sqlDisconnect(&conn);
}
boolean hgcentralTidy(char *config)
/* hgcentralTidy - Clean out old carts. */
{
boolean squealed = FALSE;
/* get connection info */
database = getCfgOption(config, "db" );
host = getCfgOption(config, "host" );
user = getCfgOption(config, "user" );
password = getCfgOption(config, "password");
conn = sqlConnectRemote(host, user, password, database);
verbose(1, "Cleaning database %s.%s\n", host, database);
verbose(1, "chunkWait=%d chunkSize=%d\n", chunkWait, chunkSize);
//sessionDbTableName = "sessionDbGalt";
//userDbTableName = "userDbGalt";
if (!purgeTable || sameString(purgeTable,sessionDbTableName))
{
if (cleanTable(sessionDbTableName))
squealed = TRUE;
}
if (!purgeTable || sameString(purgeTable,userDbTableName))
{
if (cleanTable(userDbTableName))
squealed = TRUE;
}
sqlDisconnect(&conn);
return squealed;
}
/** load the sage experiment data
*/
struct sageExp *loadSageExps(char *tableName, struct slName *nmList)
{
char *user = cfgOption("db.user");
char *password = cfgOption("db.password");
struct sqlConnection *sc = NULL;
char query[256];
struct sageExp *seList = NULL, *se=NULL;
char **row;
struct sqlResult *sr = NULL;
char *db = cgiUsualString("db", "hgFixed");
sc = sqlConnectRemote("localhost", user, password, db);
sqlSafef(query, sizeof query, "select * from sageExp order by num");
sr = sqlGetResult(sc,query);
while((row = sqlNextRow(sr)) != NULL)
{
se = sageExpLoad(row);
slAddHead(&seList,se);
}
sqlFreeResult(&sr);
sqlDisconnect(&sc);
slReverse(&seList);
return seList;
}
/** load the sage data by constructing a query based on the names in nmList
*/
struct sage *loadSageData(char *table, struct slName *nmList)
{
char *user = cfgOption("db.user");
char *password = cfgOption("db.password");
struct sqlConnection *sc = NULL;
struct dyString *query = newDyString(2048);
struct sage *sgList = NULL, *sg=NULL;
struct slName *nm =NULL;
char *db = cgiUsualString("db", "hgFixed");
char **row;
int count=0;
struct sqlResult *sr = NULL;
sc = sqlConnectRemote("localhost", user, password, db);
sqlDyStringPrintf(query, "select * from sage where ");
for(nm=nmList;nm!=NULL;nm=nm->next)
{
if (count++)
{
sqlDyStringPrintf(query," or uni=%s ", nm->name );
}
else
{
sqlDyStringPrintf(query," uni=%s ", nm->name);
}
}
sr = sqlGetResult(sc,query->string);
while((row = sqlNextRow(sr)) != NULL)
{
sg = sageLoad(row);
slAddHead(&sgList,sg);
}
sqlFreeResult(&sr);
sqlDisconnect(&sc);
slReverse(&sgList);
freeDyString(&query);
return sgList;
}
void cartSimulate(char *host, char *user, char *password, char *database)
/* Simulate action of various UCSC Genome Browser CGIs on cart. */
{
/* Figure out size of tables. */
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
int userDbSize = sqlQuickNum(conn, "NOSQLINJ select count(*) from userDb");
if (userDbSize == 0)
errAbort("%s.%s table is empty", database, userTable);
int maxSampleSize = 1024*1024;
int sampleSize = min(userDbSize, maxSampleSize);
verbose(2, "# userDb has %d rows, sampling %d\n"
, userDbSize, sampleSize);
/* Get sample of user id's. */
int *userIds = getSomeInts(conn, "userDb", "id", sampleSize);
/* Get userCount random indexes. */
int *randomIxArray, ix;
AllocArray(randomIxArray, userCount);
verbose(2, "random user ix:\n");
for (ix=0; ix<userCount; ++ix)
{
randomIxArray[ix] = rand() % sampleSize;
verbose(2, "%d ", randomIxArray[ix]);
}
verbose(2, "\n");
sqlDisconnect(&conn);
int iteration = 0;
for (;;)
{
for (ix = 0; ix < userCount; ++ix)
{
int randomIx = rand()%sampleSize;
boolean doNew = randomBitFromProb(newRatio);
long startTime = clock1000();
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
long connectTime = clock1000();
struct dyString *contents = fakeCart(randomFakeSize());
char *userContents = NULL;
int userId = userIds[randomIx];
if (doNew)
userId = userIds[randomIx] = dummyInsert(conn, userTable);
int userUseCount = dummyQuery(conn, userTable, userId, &userContents);
long userReadTime = clock1000();
sleep1000(cgiDelay);
long cgiSleepTime = clock1000();
updateOne(conn, userTable, contents->string, userId, userUseCount);
long userWriteTime = clock1000();
sqlDisconnect(&conn);
long disconnectTime = clock1000();
printf("%ld total, %ld oldSize, %ld newSize, %ld connect, %ld userRead, %ld userWrite, %ld disconnect\n",
disconnectTime - startTime - (cgiSleepTime - userReadTime),
(long) strlen(userContents),
(long)contents->stringSize,
connectTime - startTime,
userReadTime - connectTime,
userWriteTime - cgiSleepTime,
disconnectTime - userWriteTime );
dyStringFree(&contents);
freez(&userContents);
sleep1000(hitDelay);
if (++iteration >= iterations)
return;
}
}
errAbort("cartSimulate(%s %s %s %s) not implemented", host, user, password, database);
}
void testTableExists(char *config)
/* testTableExists - Experiments with ways to test for table existence ... */
{
/* get connection info */
database = "hg18";
host = getCfgOption(config, "host" );
user = getCfgOption(config, "user" );
password = getCfgOption(config, "password");
verbose(1, "running on %s\n", getenv("HOST"));
verbose(1, "connecting to host %s\n", host);
conn = sqlConnectRemote(host, user, password, database);
verbose(1, "sqlVersion=%s\n", sqlVersion(conn));
verbose(1, "numReps=%d\n", numReps);
verbose(1, "MyISAM exists: %s.%s\n", existingMyIsamDb, existingMyIsamTable);
verbose(1, "InnoDb exists: %s.%s\n", existingInnodbDb, existingInnodbTable);
verbose(1, "does not exist: %s.%s\n", nonExistingTableDb, nonExistingTable);
//verbose(1, "got here 1\n");
// kinds:
// 0 means not exists when hadError==TRUE
// 1 means not exists when rowCount==0
//good
testTableExistsRepeatedly("select count(*) from %s", 0);
//good
testTableExistsRepeatedly("describe %s", 0);
//testTableExistsRepeatedly("CHECK TABLE %s FAST QUICK;");
//need to grep for "doesn't exist" in all output fields.
//good
testTableExistsRepeatedly("SELECT 1 FROM %s LIMIT 0", 0);
//good
//testTableExistsRepeatedly("show tables like '%s'", 1);
verbose(1, "\nskipping this too slow: show tables like '%%s'\n");
// need to check if output rowcount is 1 or 0
//testTableExistsRepeatedly("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'hg18' AND table_name = '%s'");
// need to check if output rowcount is 1 or 0
// need to patch in the bloody database too
// only works on mysql 5
//good
testTableExistsRepeatedly("show columns from %s", 0);
//flush table DOES NOT WORK!: it doesn't return any failure at all: testTableExistsRepeatedly("flush table %s", 0);
//testTableExistsRepeatedly("analyze table %s");
//need to grep for "doesn't exist" in all output fields.
//good
testTableExistsRepeatedly("show index from %s", 0);
//good
testTableExistsRepeatedly("explain SELECT 1 FROM %s LIMIT 0", 0);
//good
//testTableExistsRepeatedly("show table status like '%s'", 1);
verbose(1, "\nskipping this too slow: show table status like '%%s'\n");
// need to check if output rowcount is 0
//verbose(1, "got here 99\n");
sqlDisconnect(&conn);
}
void cartSimNoInsert(char *host, char *user, char *password, char *database, char *milliDelayString,
char *iterationString)
/* cartSimNoInsert - simulates N users accessing cart at regular intervals
* where cart data is read and then written back unchanged */
{
int milliDelay = sqlUnsigned(milliDelayString);
int iterations = sqlUnsigned(iterationString);
/* Figure out size of tables. */
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
int userDbSize = sqlQuickNum(conn, "NOSQLINJ select count(*) from userDb");
int sessionDbSize = sqlQuickNum(conn, "NOSQLINJ select count(*) from sessionDb");
int sampleSize = min(userDbSize, sessionDbSize);
int maxSampleSize = 8*1024;
sampleSize = min(sampleSize, maxSampleSize);
verbose(2, "# userDb has %d rows, sessionDb has %d rows, sampling %d\n"
, userDbSize, sessionDbSize, sampleSize);
/* Get sample of user id's. */
int *userIds = getSomeInts(conn, "userDb", "id", sampleSize);
int *sessionIds = getSomeInts(conn, "sessionDb", "id", sampleSize);
/* Get userCount random indexes. */
int *randomIxArray, ix;
AllocArray(randomIxArray, userCount);
verbose(2, "random user ix:\n");
for (ix=0; ix<userCount; ++ix)
{
randomIxArray[ix] = rand() % sampleSize;
verbose(2, "%d ", randomIxArray[ix]);
}
verbose(2, "\n");
sqlDisconnect(&conn);
int iteration = 0;
int querySize = 1024*1024*16;
char *query = needLargeMem(querySize);
for (;;)
{
for (ix = 0; ix < userCount; ++ix)
{
int randomIx = randomIxArray[ix];
long startTime = clock1000();
struct sqlConnection *conn = sqlConnectRemote(host, user, password, database);
long connectTime = clock1000();
sqlSafef(query, querySize, "select contents from userDb where id=%d",
userIds[randomIx]);
char *userContents = sqlQuickString(conn, query);
long userReadTime = clock1000();
sqlSafef(query, querySize, "select contents from sessionDb where id=%d",
sessionIds[randomIx]);
char *sessionContents = sqlQuickString(conn, query);
long sessionReadTime = clock1000();
sqlSafef(query, querySize, "update userDb set contents='%s' where id=%d",
userContents, userIds[randomIx]);
if (!readOnly)
sqlUpdate(conn, query);
long userWriteTime = clock1000();
sqlSafef(query, querySize, "update sessionDb set contents='%s' where id=%d",
sessionContents, sessionIds[randomIx]);
if (!readOnly)
sqlUpdate(conn, query);
long sessionWriteTime = clock1000();
sqlDisconnect(&conn);
long disconnectTime = clock1000();
printf("%ld total, %ld size, %ld connect, %ld userRead, %ld sessionRead, %ld userWrite, %ld sessionWrite\n",
disconnectTime - startTime,
(long) strlen(userContents) + strlen(sessionContents),
connectTime - startTime,
userReadTime - connectTime,
sessionReadTime - userReadTime,
userWriteTime - sessionReadTime,
sessionWriteTime - userReadTime);
freez(&userContents);
freez(&sessionContents);
sleep1000(milliDelay);
if (++iteration >= iterations)
return;
}
}
}
