/* encodeExp.c was originally generated by the autoSql program, which also
 * generated encodeExp.h and encodeExp.sql.  This module links the database and
 * the RAM representation of objects. */

/* Copyright (C) 2014 The Regents of the University of California 
 * See kent/LICENSE or http://genome.ucsc.edu/license/ for licensing information. */

#include "common.h"
#include "linefile.h"
#include "dystring.h"
#include "jksql.h"
#include "encode/encodeExp.h"

// WARNING: autogen section has been manually updated to support fields that can be NULL

void encodeExpStaticLoad(char **row, struct encodeExp *ret)
/* Load a row from encodeExp table into ret.  The contents of ret will
 * be replaced at the next call to this function. */
{

ret->ix = sqlUnsigned(row[0]);
ret->organism = row[1];
ret->lab = row[2];
ret->dataType = row[3];
ret->cellType = row[4];
ret->expVars = row[5];
ret->accession = row[6];
ret->updateTime = row[7];
}

struct encodeExp *encodeExpLoadByQuery(struct sqlConnection *conn, char *query)
/* Load all encodeExp from table that satisfy the query given.
 * Where query is of the form 'select * from example where something=something'
 * or 'select example.* from example, anotherTable where example.something =
 * anotherTable.something'.
 * Dispose of this with encodeExpFreeList(). */
{
struct encodeExp *list = NULL, *el;
struct sqlResult *sr;
char **row;

sr = sqlGetResult(conn, query);
while ((row = sqlNextRow(sr)) != NULL)
    {
    el = encodeExpLoad(row);
    slAddHead(&list, el);
    }
slReverse(&list);
sqlFreeResult(&sr);
return list;
}

// Forward declaration
void encodeExpSaveToDb(struct sqlConnection *conn, struct encodeExp *el, char *tableName, int updateSize);

struct encodeExp *encodeExpLoad(char **row)
/* Load a encodeExp from row fetched with select * from encodeExp
 * from database.  Dispose of this with encodeExpFree(). */
{
struct encodeExp *ret;

AllocVar(ret);
ret->ix = sqlUnsigned(row[0]);
ret->organism = cloneString(row[1]);
ret->lab = cloneString(row[2]);
ret->dataType = cloneString(row[3]);
ret->cellType = cloneString(row[4]);
ret->expVars = cloneString(row[5]);
ret->accession = cloneString(row[6]);
ret->updateTime = cloneString(row[7]);
return ret;
}

struct encodeExp *encodeExpLoadAll(char *fileName)
/* Load all encodeExp from a whitespace-separated file.
 * Dispose of this with encodeExpFreeList(). */
{
struct encodeExp *list = NULL, *el;
struct lineFile *lf = lineFileOpen(fileName, TRUE);
char *row[8];

while (lineFileRow(lf, row))
    {
    el = encodeExpLoad(row);
    slAddHead(&list, el);
    }
lineFileClose(&lf);
slReverse(&list);
return list;
}

struct encodeExp *encodeExpLoadAllByChar(char *fileName, char chopper)
/* Load all encodeExp from a chopper separated file.
 * Dispose of this with encodeExpFreeList(). */
{
struct encodeExp *list = NULL, *el;
struct lineFile *lf = lineFileOpen(fileName, TRUE);
char *row[8];

while (lineFileNextCharRow(lf, chopper, row, ArraySize(row)))
    {
    el = encodeExpLoad(row);
    slAddHead(&list, el);
    }
lineFileClose(&lf);
slReverse(&list);
return list;
}

struct encodeExp *encodeExpCommaIn(char **pS, struct encodeExp *ret)
/* Create a encodeExp out of a comma separated string.
 * This will fill in ret if non-null, otherwise will
 * return a new encodeExp */
{
char *s = *pS;

if (ret == NULL)
    AllocVar(ret);
ret->ix = sqlUnsignedComma(&s);
ret->organism = sqlStringComma(&s);
ret->lab = sqlStringComma(&s);
ret->dataType = sqlStringComma(&s);
ret->cellType = sqlStringComma(&s);
ret->expVars = sqlStringComma(&s);
ret->accession = sqlStringComma(&s);
ret->updateTime = sqlStringComma(&s);
*pS = s;
return ret;
}

void encodeExpFree(struct encodeExp **pEl)
/* Free a single dynamically allocated encodeExp such as created
 * with encodeExpLoad(). */
{
struct encodeExp *el;

if ((el = *pEl) == NULL) return;
freeMem(el->organism);
freeMem(el->lab);
freeMem(el->dataType);
freeMem(el->cellType);
freeMem(el->expVars);
freeMem(el->accession);
freeMem(el->updateTime);
freez(pEl);
}

void encodeExpFreeList(struct encodeExp **pList)
/* Free a list of dynamically allocated encodeExp's */
{
struct encodeExp *el, *next;

for (el = *pList; el != NULL; el = next)
    {
    next = el->next;
    encodeExpFree(&el);
    }
*pList = NULL;
}

void encodeExpOutput(struct encodeExp *el, FILE *f, char sep, char lastSep)
/* Print out encodeExp.  Separate fields with sep. Follow last field with lastSep. */
{
fprintf(f, "%u", el->ix);
fputc(sep,f);
if (sep == ',') fputc('"',f);
fprintf(f, "%s", el->organism);
if (sep == ',') fputc('"',f);
fputc(sep,f);
if (sep == ',') fputc('"',f);
fprintf(f, "%s", el->lab);
if (sep == ',') fputc('"',f);
fputc(sep,f);
if (sep == ',') fputc('"',f);
fprintf(f, "%s", el->dataType);
if (sep == ',') fputc('"',f);
fputc(sep,f);
if (sep == ',') fputc('"',f);
fprintf(f, "%s", el->cellType);
if (sep == ',') fputc('"',f);
fputc(sep,f);
if (sep == ',') fputc('"',f);
fprintf(f, "%s", el->expVars);
if (sep == ',') fputc('"',f);
fputc(sep,f);
if (sep == ',') fputc('"',f);
fprintf(f, "%s", el->accession);
if (sep == ',') fputc('"',f);
fputc(sep,f);
if (sep == ',') fputc('"',f);
fprintf(f, "%s", el->updateTime);
if (sep == ',') fputc('"',f);
fputc(lastSep,f);
}

void encodeExpJsonOutput(struct encodeExp *el, FILE *f)
/* Print out encodeExp in JSON format. */
{
fputc('{',f);
fputc('"',f);
fprintf(f,"ix");
fputc('"',f);
fputc(':',f);
fprintf(f, "%u", el->ix);
fputc(',',f);
fputc('"',f);
fprintf(f,"organism");
fputc('"',f);
fputc(':',f);
fputc('"',f);
fprintf(f, "%s", el->organism);
fputc('"',f);
fputc(',',f);
fputc('"',f);
fprintf(f,"lab");
fputc('"',f);
fputc(':',f);
fputc('"',f);
fprintf(f, "%s", el->lab);
fputc('"',f);
fputc(',',f);
fputc('"',f);
fprintf(f,"dataType");
fputc('"',f);
fputc(':',f);
fputc('"',f);
fprintf(f, "%s", el->dataType);
fputc('"',f);
fputc(',',f);
fputc('"',f);
fprintf(f,"cellType");
fputc('"',f);
fputc(':',f);
fputc('"',f);
fprintf(f, "%s", el->cellType);
fputc('"',f);
fputc(',',f);
fputc('"',f);
fprintf(f,"expVars");
fputc('"',f);
fputc(':',f);
fputc('"',f);
fprintf(f, "%s", el->expVars);
fputc('"',f);
fputc(',',f);
fputc('"',f);
fprintf(f,"accession");
fputc('"',f);
fputc(':',f);
fputc('"',f);
fprintf(f, "%s", el->accession);
fputc('"',f);
fputc(',',f);
fputc('"',f);
fprintf(f,"updateTime");
fputc('"',f);
fputc(':',f);
fputc('"',f);
fprintf(f, "%s", el->updateTime);
fputc('"',f);
fputc('}',f);
}

/* -------------------------------- End autoSql Generated Code -------------------------------- */

#include "hdb.h"
#include "mdb.h"

/* Schema in alternate format with additional properties.
   For each field, there is a 'get' function and an entry in the fields table.
   WARNING:  Must parallel .sql */

/* BEGIN schema-dependent section */

void encodeExpJson(struct dyString *json, struct encodeExp *el)
/* Print out encodeExp in JSON format. Manually converted from autoSql which outputs
 * to file pointer.
 */
// TODO: Extend autoSql to support in-mem version
{
dyStringPrintf(json, "{");
dyStringPrintf(json, "\"ix\":%u", el->ix);
dyStringPrintf(json, ", ");
dyStringPrintf(json, "\"organism\":\"%s\"", el->organism);
dyStringPrintf(json, ", ");
dyStringPrintf(json, "\"lab\":\"%s\"", el->lab);
dyStringPrintf(json, ", ");
dyStringPrintf(json, "\"dataType\":\"%s\"", el->dataType);
dyStringPrintf(json, ", ");
dyStringPrintf(json, "\"cellType\":\"%s\"", el->cellType);
dyStringPrintf(json, ", ");
dyStringPrintf(json, "\"expVars\":\"%s\"", el->expVars);
dyStringPrintf(json, ", ");
dyStringPrintf(json, "\"accession\":\"%s\"", el->accession);
dyStringPrintf(json, "}");
}

static char *encodeExpGetIx(struct encodeExp *exp)
/* Return ix field of encodeExp */
{
char buf[64];
safef(buf, sizeof(buf), "%d", exp->ix);
return cloneString(buf);
}

static char *encodeExpGetOrganism(struct encodeExp *exp)
/* Return organism field of encodeExp */
{
return cloneString(exp->organism);
}

static char *encodeExpGetAccession(struct encodeExp *exp)
/* Return accession field of encodeExp */
{
return cloneString(exp->accession);
}

static char *encodeExpGetLab(struct encodeExp *exp)
/* Return lab field of encodeExp */
{
return cloneString(exp->lab);
}

static char *encodeExpGetDataType(struct encodeExp *exp)
/* Return dataType field of encodeExp */
{
return cloneString(exp->dataType);
}

static char *encodeExpGetCellType(struct encodeExp *exp)
/* Return cellType field of encodeExp */
{
return cloneString(exp->cellType);
}

static char *encodeExpGetExpVars(struct encodeExp *exp)
/* Return expVars field of encodeExp */
{
return cloneString(exp->expVars);
}

static char *encodeExpGetUpdateTime(struct encodeExp *exp)
/* Return updateTime field of encodeExp */
{
return cloneString(exp->updateTime);
}

typedef char * (*encodeExpGetFieldFunc)(struct encodeExp *exp);

struct encodeExpField {
    char *name;
    encodeExpGetFieldFunc get;
    boolean required;
} encodeExpField;

struct encodeExpField encodeExpFields[] =
   { {ENCODE_EXP_FIELD_IX, &encodeExpGetIx, TRUE},                  //required, set to 0 initially
     {ENCODE_EXP_FIELD_ORGANISM, &encodeExpGetOrganism, TRUE},      //required
     {ENCODE_EXP_FIELD_LAB, &encodeExpGetLab, TRUE},                 //required
     {ENCODE_EXP_FIELD_DATA_TYPE, &encodeExpGetDataType, TRUE},      //required
     {ENCODE_EXP_FIELD_CELL_TYPE, &encodeExpGetCellType, TRUE},      //required
     {ENCODE_EXP_FIELD_FACTORS, &encodeExpGetExpVars, FALSE},
     {ENCODE_EXP_FIELD_ACCESSION, &encodeExpGetAccession, FALSE},
     {ENCODE_EXP_FIELD_UPDATE_TIME, &encodeExpGetUpdateTime, FALSE},
     {NULL, 0, 0} };

static char *sqlCreate =
"CREATE TABLE %s (\n"
"    ix int auto_increment,              # auto-increment ID\n"
"    organism varchar(255) not null,     # human | mouse\n"
"    lab varchar(255) not null,  # lab name from ENCODE cv.ra\n"
"    dataType varchar(255) not null,     # dataType from ENCODE cv.ra\n"
"    cellType varchar(255) not null,     # cellType from ENCODE cv.ra\n"
"    expVars varchar(255),	         # var=value list of experiment-defining variables. May be NULL if none.\n"
"    accession varchar(255),	        # wgEncodeE[H|M]00000N or NULL if proposed but not yet approved\n"
"    updateTime timestamp default now() on update now(),  # last update date-time"
"              #Indices\n"
"    PRIMARY KEY(ix)\n"
")";


/* History table approach from Peter Brawley, http://www.artfulsoftware.com */

static void encodExpAddHistoryTrigger(struct sqlConnection *conn, char *tableName, char *action)
/* Create an SQL query to add a trigger to the history table for an encodeExp table */
{
struct dyString *dy;
char *which = NULL;

if (sameString(action, "insert") || sameString(action, "update"))
    which = "NEW";
else if sameString(action, "delete")
    which = "OLD";
else
    errAbort("Invalid SQL trigger action: %s", action);
dy = sqlDyStringCreate(
    "CREATE TRIGGER %s_%s AFTER %s ON %s FOR EACH ROW INSERT INTO %s%s VALUES \n"
    "(%s.ix, %s.organism, %s.lab, %s.dataType, %s.cellType, %s.expVars, %s.accession, NOW(), '%c', USER(), '')",
        tableName, action, action, tableName,
        tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX,
        which, which, which, which, which, which, which, toupper(action[0]));
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);
}

static void encodExpDropHistoryTrigger(struct sqlConnection *conn, char *tableName, char *action)
/* Drop history trigger from a table */
{
struct dyString *dy;

if (differentString(action, "insert") && differentString(action, "update") &&
    differentString(action, "delete"))
        errAbort("Invalid SQL trigger action: %s", action);
dy = sqlDyStringCreate("DROP TRIGGER IF EXISTS %s_%s", tableName, action);
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);
}

static void encodExpAddTriggers(struct sqlConnection *conn, char *tableName)
{
/* Add history triggers to experiment table */
encodExpAddHistoryTrigger(conn, tableName, "insert");
encodExpAddHistoryTrigger(conn, tableName, "update");
encodExpAddHistoryTrigger(conn, tableName, "delete");
}

static void encodeExpDropTriggers(struct sqlConnection *conn, char *tableName)
{
/* Drop history triggers from experiment table */
encodExpDropHistoryTrigger(conn, tableName, "insert");
encodExpDropHistoryTrigger(conn, tableName, "update");
encodExpDropHistoryTrigger(conn, tableName, "delete");
}

void encodeExpTableRename(struct sqlConnection *conn, char *tableName, char *newTableName)
/* Rename table and history table, updating triggers to match */
{
char oldBuf[64];
char newBuf[64];

encodeExpDropTriggers(conn, tableName);
sqlRenameTable(conn, tableName, newTableName);
safef(oldBuf, sizeof oldBuf, "%s%s", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX);
safef(newBuf, sizeof newBuf, "%s%s", newTableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX);
sqlRenameTable(conn, oldBuf, newBuf);
encodExpAddTriggers(conn, newTableName);
}

void encodeExpTableCopy(struct sqlConnection *conn, char *tableName, char *newTableName)
/* Copy table and history table, updating triggers */
{
char oldBuf[64];
char newBuf[64];

sqlCopyTable(conn, tableName, newTableName);
safef(oldBuf, sizeof oldBuf, "%s%s", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX);
safef(newBuf, sizeof newBuf, "%s%s", newTableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX);
sqlCopyTable(conn, oldBuf, newBuf);
encodExpAddTriggers(conn, newTableName);
}

void encodeExpTableDrop(struct sqlConnection *conn, char *tableName)
{
/* Drop an encodeExp table */
char buf[64];

encodeExpDropTriggers(conn, tableName);
sqlDropTable(conn, tableName);
safef(buf, sizeof buf, "%s%s", tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX);
sqlDropTable(conn, buf);
}

#define ENCODE_EXP_HISTORY_FIELD_WHAT   "action"
#define ENCODE_EXP_HISTORY_FIELD_WHO    "changedBy"
#define ENCODE_EXP_HISTORY_FIELD_WHY    "why"

void encodeExpTableCreate(struct sqlConnection *conn, char *tableName)
/* Create an encodeExp table */
{
struct dyString *dy;
dy = sqlDyStringCreate(sqlCreate, tableName);
sqlRemakeTable(conn, tableName, dyStringContents(dy));
dyStringFree(&dy);

/* Create history table -- a clone with 3 additional columns (action, changedBy, why).
 * 'why' is only required for deletes
 * Remove auto-inc attribute on ix, and use ix and updateTime as primary key  */
dy = sqlDyStringCreate("CREATE TABLE %s%s LIKE %s",
                tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, tableName);
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);
dy = sqlDyStringCreate("ALTER TABLE %s%s ADD COLUMN %s CHAR(1) DEFAULT ''",
                        tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_HISTORY_FIELD_WHAT);
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);
dy = sqlDyStringCreate("ALTER TABLE %s%s ADD COLUMN %s VARCHAR(77) NOT NULL",
                        tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_HISTORY_FIELD_WHO);
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);
dy = sqlDyStringCreate("ALTER TABLE %s%s ADD COLUMN %s VARCHAR(255) NOT NULL",
                        tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_HISTORY_FIELD_WHY);
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);

dy = sqlDyStringCreate("ALTER TABLE %s%s MODIFY COLUMN %s INT DEFAULT 0",
                        tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_FIELD_IX);
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);
dy = sqlDyStringCreate("ALTER TABLE %s%s DROP PRIMARY KEY",
                        tableName, ENCODE_EXP_HISTORY_TABLE_SUFFIX);

sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);

//possible TODO:  if we do need a primary key, will need to add a msec or autoinc column */
//alter table %s add idx int unsigned not null auto_increment, add primary key (idx);

encodExpAddTriggers(conn, tableName);
}

int encodeExpIdMax(struct sqlConnection *conn) 
/* Return largest ix value */
{
char query[1024];
sqlSafef(query, sizeof query, "select max(ix) from %s", ENCODE_EXP_TABLE);
return sqlQuickNum(conn, query);
}

/* END schema-dependent section */

struct encodeExp *encodeExpLoadAllFromTable(struct sqlConnection *conn, char *tableName)
/* Load all encodeExp in table */
{
if (!sqlTableExists(conn, tableName))
    return NULL;
struct dyString *dy = dyStringNew(0);
sqlDyStringPrintf(dy, "select * from %s", tableName);
struct encodeExp *exps = NULL;
exps = encodeExpLoadByQuery(conn, dyStringContents(dy));
dyStringFree(&dy);
return exps;
}

static void encodeExpAddToLatestHistory(struct sqlConnection *conn, char *table, int id, char *field, char *value)
/* Add user name to history table record */
{
struct dyString *dy = sqlDyStringCreate(
        "select max(updateTime) from %s%s where %s='%d'",
                        table, ENCODE_EXP_HISTORY_TABLE_SUFFIX, ENCODE_EXP_FIELD_IX, id);
verbose(3, "%s\n", dy->string);
char *updateTime = sqlQuickString(conn, dyStringContents(dy));
dyStringFree(&dy);
dy = sqlDyStringCreate(
        "update %s%s set %s='%s' where updateTime = '%s'",
                        table, ENCODE_EXP_HISTORY_TABLE_SUFFIX, field, value, updateTime);
verbose(3, "%s\n", dy->string);
sqlUpdate(conn, dyStringContents(dy));
dyStringFree(&dy);
}

static void encodeExpAddUserToLatestHistory(struct sqlConnection *conn, char *table, int id)
/* Add user name to history table record */
{
encodeExpAddToLatestHistory(conn, table, id, ENCODE_EXP_HISTORY_FIELD_WHO, getlogin());
}

static void encodeExpAddWhyToLatestHistory(struct sqlConnection *conn, char *table, int id, char *why)
/* Add comment to history table record */
{
encodeExpAddToLatestHistory(conn, table, id, ENCODE_EXP_HISTORY_FIELD_WHY, why);
}

void encodeExpSaveToDb(struct sqlConnection *conn, struct encodeExp *el, char *tableName, int updateSize)
/* Save encodeExp as a row to the table specified by tableName.
 * As blob fields may be arbitrary size updateSize specifies the approx size.
 * of a string that would contain the entire query. Automatically
 * escapes all simple strings (not arrays of string). */
{
// NOTE: Manually updated to handle NULL fields, including setting NULL (for expVars and accession)
struct dyString *update = dyStringNew(updateSize);
sqlDyStringPrintf(update, "insert into %s set %s=%u, %s='%s', %s='%s', %s='%s', %s='%s'", tableName,
                ENCODE_EXP_FIELD_IX, el->ix,
                ENCODE_EXP_FIELD_ORGANISM, el->organism,
                ENCODE_EXP_FIELD_LAB, el->lab,
                ENCODE_EXP_FIELD_DATA_TYPE, el->dataType,
                ENCODE_EXP_FIELD_CELL_TYPE, el->cellType);
// Note: The sql literal NULL is not quoted in the final sql string.
sqlDyStringPrintf(update, ", %s=", ENCODE_EXP_FIELD_FACTORS);
if (el->expVars == NULL)
    sqlDyStringPrintf(update, "NULL");
else
    sqlDyStringPrintf(update, "'%s'", el->expVars);
sqlDyStringPrintf(update, ", %s=", ENCODE_EXP_FIELD_ACCESSION);
if (el->accession == NULL)
    sqlDyStringPrintf(update, "NULL");
else
    sqlDyStringPrintf(update, "'%s'", el->accession);

sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK);
sqlUpdate(conn, update->string);
int id = sqlLastAutoId(conn);
encodeExpAddUserToLatestHistory(conn, tableName, id);
sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK);

dyStringFree(&update);
}


struct encodeExp *encodeExpFromMdb(struct sqlConnection *conn, char *db, struct mdbObj *mdb)
/* Create an encodeExp from an ENCODE metaDb object */
{
if (!mdbObjIsEncode(mdb))
    errAbort("Metadata object is not from ENCODE");

struct mdbVar *edVars = mdbObjFindEncodeEdvs(conn,mdb,FALSE); // exclude vars where val=None
// To use shared metaDb:
//struct mdbVar *edVars = mdbObjFindEncodeEdvPairs(conn, MDB_DEFAULT_NAME, mdb, FALSE);
if (edVars == NULL)
    {  // Not willing to make these erraborts at this time.
    char *composite = mdbObjFindValue(mdb,MDB_VAR_COMPOSITE);
    if (composite == NULL)
        verbose(1,"MDB object '%s' does not have a composite defined in user metaDb\n",mdb->obj);
    else
        verbose(1,"Experiment Defining Variables not defined for composite '%s' in user metaDb\n",composite);
    return NULL;
    }
struct encodeExp *exp = encodeExpFromMdbVars(db, edVars);
mdbVarsFree(&edVars);
return exp;
}


struct encodeExp *encodeExpFromMdbVars(char *db, struct mdbVar *vars)
// Creates and returns an encodeExp struct from mdbVars, but does not touch the table
// Only Experiment Defining Variables should be in the list.
{
struct encodeExp *exp;
AllocVar(exp);
exp->ix = ENCODE_EXP_IX_UNDEFINED; // This exp is not yet defined

if (db == NULL)
    errAbort("Missing assembly");

// FIXME: centralize treatment of organism/lower-casing
exp->organism = hOrganism(db);
strLower(exp->organism);

struct slPair *varPairs = NULL;
struct mdbVar *edv = vars;
for (;edv != NULL; edv = edv->next)
    {
    if (sameWord(edv->var,MDB_VAR_LAB))
        {
        assert(exp->lab == NULL);
        exp->lab = cvLabNormalize((char *)(edv->val));
        }
    else if (sameWord(edv->var,MDB_VAR_DATATYPE))
        {
        assert(exp->dataType == NULL);
        exp->dataType = cloneString((char *)(edv->val));
        }
    else if (sameWord(edv->var,MDB_VAR_CELL))
        {
        assert(exp->cellType == NULL);
        exp->cellType = cloneString((char *)(edv->val));
        }
    else
        {
        // exclude uninformative EDV's
        if (differentString(MDB_VAL_ENCODE_EDV_NONE, (char *)(edv->val)))
            slPairAdd(&varPairs, edv->var, edv->val); // No need to clone
        }
    }

// Be sure we have what we need
if (exp->lab == NULL || exp->dataType == NULL)
    {
    verbose(1,"Experiment Defining Variables must contain '%s' and '%s'\n",
            MDB_VAR_LAB,MDB_VAR_DATATYPE); // Not willing to make this an errabort at this time.
    return NULL;
    }
if (exp->cellType == NULL)  // Okay if no cell
    exp->cellType = cloneString(ENCODE_EXP_NO_CELL);

if (varPairs != NULL)
    {
    slPairSortCase(&varPairs);
    exp->expVars = slPairListToString(varPairs,FALSE); // don't bother adding quotes since EDVs
                                                       // should not have spaces
    slPairFreeList(&varPairs);
    }
return exp;
}

struct encodeExp *encodeExpFromRa(struct hash *ra)
/* Load an encodeExp from a Ra hash. */
{
char *rows[ENCODEEXP_NUM_COLS];
struct encodeExp *exp;
int i;

AllocVar(exp);
for (i = 0; i < ENCODEEXP_NUM_COLS; i++)
    {
    struct encodeExpField *fp = &encodeExpFields[i];
    assert(fp->name != NULL);
    char *val = hashFindVal(ra, fp->name);
    if (val == NULL && fp->required)
        errAbort("Required field \'%s\' not found in .ra:\n\n%s", fp->name, hashToRaString(ra));
    rows[i] = cloneString(val);
    }
encodeExpStaticLoad(rows, exp);
return exp;
}

struct hash *encodeExpToRaFile(struct encodeExp *exp, FILE *f)
/* Create a Ra hash from an encodeExp.  Print to file if non NULL */
{
struct hash *ra = hashNew(0);
int i;
for (i = 0; i < ENCODEEXP_NUM_COLS; i++)
    {
    struct encodeExpField *fp = &encodeExpFields[i];
    assert(fp->name != NULL);
    char *val = fp->get(exp);
    if (val != NULL)
        {
        hashAdd(ra, fp->name, val);
        if (f != NULL)
            fprintf(f, "%s %s\n", fp->name, val);
        }
    }
if (f != NULL)
    fputs("\n", f);
return ra;
}

boolean encodeExpSame(struct encodeExp *exp, struct encodeExp *exp2)
/* Return TRUE if two experiments are the same */
{
int i;
for (i = 0; i < ENCODEEXP_NUM_COLS; i++)
    {
    struct encodeExpField *fp = &encodeExpFields[i];
    assert(fp->name != NULL);
    if (differentStringNullOk(fp->get(exp), fp->get(exp2)))
        return FALSE;
    }
return TRUE;
}

struct hash *encodeExpToRa(struct encodeExp *exp)
/* Create a Ra hash from an encodeExp */
{
return encodeExpToRaFile(exp, NULL);
}

struct encodeExp *encodeExpGetByIdFromTable(struct sqlConnection *conn, char *tableName, int id)
/* Return experiment specified by id from named table */
{
struct dyString *dy = sqlDyStringCreate("select * from %s where %s=\'%d\'", tableName, ENCODE_EXP_FIELD_IX, id);

struct encodeExp *exps = NULL;
exps = encodeExpLoadByQuery(conn, dyStringContents(dy));
dyStringFree(&dy);
return exps;
}

struct encodeExp *encodeExpGetById(struct sqlConnection *conn, int id)
/* Return experiment specified by id from default table */
{
return encodeExpGetByIdFromTable(conn, ENCODE_EXP_TABLE, id);
}

struct encodeExp *encodeExpGetByAccession(struct sqlConnection *conn, char *accession)
/* Return experiment specified by accession from default table */
{
if (accession == NULL || strlen(accession) <= encodeExpIdOffset())
    return NULL;
int id = atoi(accession + encodeExpIdOffset());
return encodeExpGetById(conn, id);
}

static char *encodeExpMakeAccession(struct encodeExp *exp)
/* Make accession string from prefix + organism + id */
{
char accession[64];

char org = '\0';
if (sameString(exp->organism, ENCODE_EXP_ORGANISM_HUMAN))
    org = 'H';
else if (sameString(exp->organism, ENCODE_EXP_ORGANISM_MOUSE))
    org = 'M';
else
    errAbort("Invalid organism %s", exp->organism);
safef(accession, sizeof(accession), "%s%c%06d", ENCODE_EXP_ACC_PREFIX, org, exp->ix);
return cloneString(accession);
}

int encodeExpIdOffset() {
/* Length of prefix preceding experiment ID in the accession. 
   Prefix is defined string + 1 for org character
*/
    return strlen(ENCODE_EXP_ACC_PREFIX) + 1;
}

void encodeExpAdd(struct sqlConnection *conn, char *tableName, struct encodeExp *exp)
/* Add encodeExp as a new row to the table specified by tableName.
*/
{
encodeExpSaveToDb(conn, exp, tableName, 0);
}

static char *encodeExpAccession(struct sqlConnection *conn, char *tableName, int id, boolean add)
/* Add or remove an accession from an experiment.
   This is done after the experiment definition is checked for validity.
*/
{
struct dyString *query = NULL;
char *accession = NULL;
struct encodeExp *exp = NULL;
char queryAcc[64];

sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK);
exp = encodeExpGetByIdFromTable(conn, tableName, id);
if (exp == NULL)
    errAbort("Experiment id %d not found in table %s", id, tableName);
if (add)
    {
    accession = encodeExpMakeAccession(exp);
    safef(queryAcc, sizeof(queryAcc), "\'%s\'", accession);
    }
else
    safecpy(queryAcc, sizeof(queryAcc), "NULL");

query = sqlDyStringCreate("update %s set %s=%s where %s=%d", tableName,
                        ENCODE_EXP_FIELD_ACCESSION, queryAcc,
                        ENCODE_EXP_FIELD_IX, exp->ix);
sqlUpdate(conn, query->string);
encodeExpAddUserToLatestHistory(conn, tableName, id);
sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK);
dyStringFree(&query);
return accession;
}

char *encodeExpAddAccession(struct sqlConnection *conn, char *tableName, int id)
/* Add accession field to an existing "temp" experiment.  This is done
 * after experiment is determined to be valid.
 * Return the accession. */
{
return encodeExpAccession(conn, tableName, id, TRUE);
}

void encodeExpSetAccession(struct encodeExp *exp, char *tableName)
/* Adds accession field to an existing experiment, updating the table. */
{
struct sqlConnection *conn = sqlConnect(ENCODE_EXP_DATABASE);

exp->accession = encodeExpAccession(conn, tableName, exp->ix, TRUE);

sqlDisconnect(&conn);
}

void encodeExpRemoveAccession(struct sqlConnection *conn, char *tableName, int id)
/* Revoke an experiment by removing the accession.
*/
{
encodeExpAccession(conn, tableName, id, FALSE);
}

boolean encodeExpIsAccessioned(struct encodeExp *exp)
/* Determine if experiment has an accession (not unaccessioned or deaccessioned) */
{
return encodeExpGetAccession(exp) != NULL;
}

void encodeExpRemove(struct sqlConnection *conn, char *tableName, struct encodeExp *exp, char *why)
/* Delete row containing experiment from encodeExp.
 * WARNING:  This is a management function, not for regular use.  Accession must
 * not be present.  In general, experiments should be reviewed before adding to table
 * rather than added and removed if problematic.
*/
{
char query[256];

/* must match entry in table in all ways */
struct encodeExp *exp2 = encodeExpGetByIdFromTable(conn, tableName, exp->ix);
if (encodeExpSame(exp, exp2))
    {
    sqlSafef(query, sizeof(query), "delete from %s where %s=%d",
                                tableName, ENCODE_EXP_FIELD_IX, exp->ix);
    sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK);
    sqlUpdate(conn, query);
    encodeExpAddUserToLatestHistory(conn, tableName, exp->ix);
    encodeExpAddWhyToLatestHistory(conn, tableName, exp->ix, why);
    sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK);
    }
}

boolean encodeExpIsFieldVar(char *var)
/* Return true if var is a field in schema -- one of standard set (not an expVar) */
{
if (var == NULL)
    return FALSE;
return (sameString(var, ENCODE_EXP_FIELD_LAB) ||
    sameString(var, ENCODE_EXP_FIELD_DATA_TYPE) ||
    sameString(var, ENCODE_EXP_FIELD_CELL_TYPE));
}

char *encodeExpGetVar(struct encodeExp *exp, char *var)
/* Return value of an expVar, or NULL if not found */
{
struct slPair *vars = slPairListFromString(exp->expVars, FALSE); 
return slPairFindVal(vars, var);
}

char *encodeExpGetField(struct encodeExp *exp, char *var)
/* Return value of a field, whether part of schema or an expVar */
{
if (var == NULL)
    return FALSE;
int i;
for (i = 0; i < ENCODEEXP_NUM_COLS; i++)
    {
    struct encodeExpField *fp = &encodeExpFields[i];
    assert(fp->name != NULL);
    if (sameString(fp->name, var))
        return fp->get(exp);
    }
// not a schema field, it may be an expVar
return encodeExpGetVar(exp, var);
}

static boolean cvTermIsValid(char *type, char *val)
/* Determine if term is valid for CV type of term
 * TODO:  This really belongs in cv.ra, but this limited version used just by encodeExp
 *  For now, addng special cases as needed -- e.g. allow control term for antibody type
 */
{
if (cvOneTermHash(type, val))
    return TRUE;
if (sameString(type, CV_TERM_ANTIBODY))
    {
    if (cvOneTermHash(CV_TERM_CONTROL, val))
        return TRUE;
    }
return FALSE;
}

void encodeExpUpdate(struct sqlConnection *conn, char *tableName,
                                int id, char *var, char *newVal, char *oldVal)
/* Update field in encodeExp or var in expVars, identified by id with value.
 * If oldVal is non-NULL, verify it matches experiment, as a safety check.
 * OldVal of ENCODE_EXP_NO_VAR allows adding expVar.
 * TODO: Setting newVal to ENCODE_EXP_NO_VAR will remove expVar.  
 * Abort if experiment is accessioned (must deaccession first) */
{
char *val = NULL;
struct dyString *dy = NULL;

char *type = (char *)cvTermNormalized(var);
if (type == NULL)
    errAbort("Attempt to update encodeExp experiment with unknown CV type %s", var);

if (cvTermIsCvDefined(type))
    {
    verbose(1, "     var %s is cv defined\n", type);
    /* verify new value is valid term in CV */
    if (!cvTermIsValid(type, newVal))
        errAbort("Attempt to update encodeExp experiment with unknown CV term %s of type %s", 
                    newVal, var);
    }
struct encodeExp *exp = encodeExpGetByIdFromTable(conn, tableName, id);
if (exp == NULL)
    errAbort("Id %d not found in experiment table %s", id, tableName);
if (exp->accession)
    errAbort("Id %d in table %s has accession", id, tableName);

if (encodeExpIsFieldVar(var))
    {
    /* check if old value matches */
    if (oldVal)
        {
        struct hash *expRa = encodeExpToRa(exp);
        val = hashFindVal(expRa, var);
        if (val == NULL)
            errAbort("Field %s not found in id %d from table %s", var, id, tableName);
        if (differentString(val, oldVal))
            errAbort("Mismatch: id %d has %s=%s, not %s in table %s", id, var, val, oldVal, tableName);
        }
    dy = sqlDyStringCreate("update %s set %s=\'%s\' ", tableName, var, newVal);
    }
else
    {
    /* must be an expVar -- extract all expVars for this experiment */
    struct slPair *varPairs = slPairListFromString(exp->expVars,FALSE);
    struct slPair *pair = slPairFind(varPairs, var);
    if (pair != NULL)
        {
        /* change the designated var */
        if (oldVal)
            {
            // TODO: remove expVar if newVal == None
            val = (char *)pair->val;
            if (differentString(val, oldVal))
                errAbort("Mismatch: id %d has %s=%s, not %s in table %s", 
                        id, var, val, oldVal, tableName);
            }
        pair->val = newVal;
        }
    else 
        {
        // this var not found in this experiment - add new var
        if (oldVal && differentString(oldVal, ENCODE_EXP_NO_VAR))
            {
            errAbort("Attempt to change expVar %s from value %s not found in experiment %d",
                    var, oldVal, id);
            }
        verbose(3, "Adding %s=%s to experiment %d\n", var, newVal, id);
        slPairAdd(&varPairs, var, newVal);
        slPairSortCase(&varPairs);
        verbose(1, "WARNING: not verifying %s is valid expVar for this experiment\n", var);
        }
    char *expVars = slPairListToString(varPairs, FALSE);
    dy = sqlDyStringCreate("update %s set %s=\'%s\' ", tableName, ENCODE_EXP_FIELD_FACTORS, expVars);
    }
sqlDyStringPrintf(dy, " where ix=%d", id);
sqlGetLock(conn, ENCODE_EXP_TABLE_LOCK);
sqlUpdate(conn, dyStringContents(dy));
encodeExpAddUserToLatestHistory(conn, tableName, id);
sqlReleaseLock(conn, ENCODE_EXP_TABLE_LOCK);
dyStringFree(&dy);
}

char *encodeExpKey(struct encodeExp *exp)
/* Create a hash key from an encodeExp */
{
struct dyString *dy = dyStringNew(0);
dyStringPrintf(dy, "lab:%s dataType:%s cellType:%s", exp->lab, exp->dataType, exp->cellType);
if (exp->expVars != NULL)
    dyStringPrintf(dy, " expVars:%s", exp->expVars);
return dyStringCannibalize(&dy);
}

char *encodeExpVars(struct encodeExp *exp)
// Create a string of all experiment defining vars and vals as "lab=UW dataType=ChipSeq ..."
// WARNING: May be missing var=None if the var was added after composite had defined exps.
{
struct dyString *dy = dyStringNew(0);
dyStringPrintf(dy, "%s=%s %s=%s", MDB_VAR_LAB, exp->lab, MDB_VAR_DATATYPE, exp->dataType );
if (exp->cellType != NULL)
    dyStringPrintf(dy, " %s=%s", MDB_VAR_CELL, exp->cellType);
if (exp->expVars != NULL)
    dyStringPrintf(dy, " %s", exp->expVars);
return dyStringCannibalize(&dy);
}

struct encodeExp *encodeExpGetFromTable(char *organism, char *lab, char *dataType,
                                char *cell, struct slPair *varPairs, char *table)
/* Return experiments matching args in named experiment table.
 * Organism, Lab and DataType must be non-null */
{
if (organism == NULL || lab == NULL || dataType == NULL)
    errAbort("Need organism, lab, and dataType to query experiment table");

if (cell == NULL)
    cell = ENCODE_EXP_NO_CELL;

struct sqlConnection *conn = sqlConnect(ENCODE_EXP_DATABASE);

struct dyString *dy = sqlDyStringCreate(
        "select * from %s where %s=\'%s\' and %s=\'%s\' and %s=\'%s\' and %s=\'%s\' and %s",
                table,
                ENCODE_EXP_FIELD_ORGANISM, organism,
                ENCODE_EXP_FIELD_LAB, lab,
                ENCODE_EXP_FIELD_DATA_TYPE, dataType,
                ENCODE_EXP_FIELD_CELL_TYPE, cell,
                ENCODE_EXP_FIELD_FACTORS);
/* construct expVars string var=val from pairs */
if (varPairs == NULL)
    sqlDyStringPrintf(dy, " is NULL");
else
    {
    sqlDyStringPrintf(dy, "= '%s'", slPairListToString(varPairs, FALSE));
    }
verbose(4, "query: %s\n", dy->string);
struct encodeExp *exps = NULL;
exps = encodeExpLoadByQuery(conn, dyStringContents(dy));
sqlDisconnect(&conn);
dyStringFree(&dy);
return exps;
}

struct encodeExp *encodeExpGet(char *organism, char *lab, char *dataType, char *cell,
                                        struct slPair *varPairs)
/* Return experiments matching args in default experiment table.
 * Organism, Lab and DataType must be non-null */
{
return encodeExpGetFromTable(organism, lab, dataType, cell, varPairs, ENCODE_EXP_TABLE);
}

struct encodeExp *encodeExpGetByMdbVarsFromTable(char *db, struct mdbVar *vars, char *table)
/* Return experiments by looking up mdb var list from the named experiment table */
{
struct encodeExp *exp = encodeExpFromMdbVars(db,vars);
                         // don't expect quoted EDVs which should always be simple tokens.
struct slPair *edvVars = slPairListFromString(exp->expVars,FALSE); 

struct encodeExp *expFound = encodeExpGetFromTable(exp->organism,exp->lab,exp->dataType,exp->cellType,edvVars,table);
// No longer needed
encodeExpFree(&exp);
if (edvVars != NULL)
    slPairFreeValsAndList(&edvVars);
return expFound;
}

struct encodeExp *encodeExpGetByMdbVars(char *db, struct mdbVar *vars)
/* Return experiments by looking up mdb var list from the default experiment table */
{
return encodeExpGetByMdbVarsFromTable(db, vars, ENCODE_EXP_TABLE);
}

struct encodeExp *encodeExpGetOrCreateByMdbVarsFromTable(char *db, struct mdbVar *vars, char *table)
// Return experiment looked up or created from the mdb var list from the named experiment table.
{
struct encodeExp *exp = encodeExpFromMdbVars(db,vars);
                         // don't expect quoted EDVs which should always be simple tokens.
struct slPair *edvVars = slPairListFromString(exp->expVars,FALSE); 

struct encodeExp *expFound = encodeExpGetFromTable(exp->organism,exp->lab,exp->dataType,exp->cellType,edvVars,table);
if (expFound == NULL)
    {
    struct sqlConnection *conn = sqlConnect(ENCODE_EXP_DATABASE);
    encodeExpAdd(conn, table, exp);
    sqlDisconnect(&conn);
    expFound = encodeExpGetFromTable(exp->organism,exp->lab,exp->dataType,exp->cellType,
                                     edvVars,table);
    }
encodeExpFree(&exp);
slPairFreeValsAndList(&edvVars);
return expFound;
}

int encodeExpExists(char *db, struct mdbVar *vars)
/* Return TRUE if at least one experiment exists for these vars */
{
struct encodeExp *exp = encodeExpGetByMdbVars(db, vars);
int found = (exp != NULL);
freez(&exp);
return found;
}

char *encodeExpGetAccessionByMdbVars(char *db, struct mdbVar *vars)
/* Return accession of (first) experiment matching vars, or NULL if not found */
{
struct encodeExp *exp = encodeExpGetByMdbVars(db, vars);
char *acc = encodeExpGetAccession(exp);
freez(&exp);
return acc;
}