/*
 * dettool.c
 *
 * Copyright (C) 2006  Joshua Hoblitt
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the Free
 * Software Foundation; version 2 of the License.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
 * more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * program; see the file COPYING. If not, write to the Free Software
 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
 */

#include "dettool.h"

static bool pendingMode(pxConfig *config);
static bool definebytagMode(pxConfig *config);
static bool definebyqueryMode(pxConfig *config);
static bool definebydetrunMode(pxConfig *config);
static bool runsMode(pxConfig *config);
static bool childlessrunMode(pxConfig *config);
static bool inputMode(pxConfig *config);
static bool rawMode(pxConfig *config);
// processedimfile
static bool toprocessedimfileMode(pxConfig *config);
static bool addprocessedimfileMode(pxConfig *config);
static bool processedimfileMode(pxConfig *config);
static bool revertprocessedimfileMode(pxConfig *config);
// processedexp
static bool toprocessedexpMode(pxConfig *config);
static bool addprocessedexpMode(pxConfig *config);
static bool processedexpMode(pxConfig *config);
static bool revertprocessedexpMode(pxConfig *config);
// stackedimfile
static bool tostackedMode(pxConfig *config);
static bool addstackedMode(pxConfig *config);
static bool stackedMode(pxConfig *config);
static bool revertstackedMode(pxConfig *config);
// normalizedstat
static bool tonormalizedstatMode(pxConfig *config);
static bool addnormalizedstatMode(pxConfig *config);
static bool normalizedstatMode(pxConfig *config);
static bool revertnormalizedstatMode(pxConfig *config);
// normalizedimfile
static bool tonormalizeMode(pxConfig *config);
static bool addnormalizedimfileMode(pxConfig *config);
static bool normalizedimfileMode(pxConfig *config);
static bool revertnormalizedimfileMode(pxConfig *config);
// normalizedexp
static bool tonormalizedexpMode(pxConfig *config);
static bool addnormalizedexpMode(pxConfig *config);
static bool normalizedexpMode(pxConfig *config);
static bool revertnormalizedexpMode(pxConfig *config);
// residimfile
static bool toresidimfileMode(pxConfig *config);
static bool addresidimfileMode(pxConfig *config);
static bool residimfileMode(pxConfig *config);
static bool revertresidimfileMode(pxConfig *config);
// residexp
static bool toresidexpMode(pxConfig *config);
static bool addresidexpMode(pxConfig *config);
static bool residexpMode(pxConfig *config);
static bool revertresidexpMode(pxConfig *config);
static bool updateresidexpMode(pxConfig *config);
// detrunsummary
static bool todetrunsummaryMode(pxConfig *config);
static bool adddetrunsummaryMode(pxConfig *config);
static bool detrunsummaryMode(pxConfig *config);
static bool revertdetrunsummaryMode(pxConfig *config);
static bool updatedetrunsummaryMode(pxConfig *config);
// run
static bool updatedetrunMode(pxConfig *config);
static bool rerunMode(pxConfig *config);
// register
static bool register_detrendMode(pxConfig *config);

static detRunSummaryRow *mdToDetRunSummary(pxConfig *config, psMetadata *row);
//static psArray *validDetInputClassIds(pxConfig *config, const char *det_id);
//static psArray *searchInputImfiles(pxConfig *config, const char *det_id);
static detInputExpRow *rawDetrenTodetInputExpRow(rawExpRow *rawExp, psS64 det_id, psS32 iteration);
static psArray *searchRawImfiles(pxConfig *config);
static bool startNewIteration(pxConfig *config, psS64 det_id);
static psS32 incrementIteration(pxConfig *config, psS64 det_id);
static bool setDetRunState(pxConfig *config, psS64 det_id, const char *state);
static bool isValidMode(pxConfig *config, const char *mode);

# define MODECASE(caseName, func) \
    case caseName: \
    if (!func(config)) { \
        goto FAIL; \
    } \
    break;

int main(int argc, char **argv)
{
    psLibInit(NULL);

    pxConfig *config = dettoolConfig(NULL, argc, argv);
    if (!config) {
        psError(PXTOOLS_ERR_CONFIG, false, "failed to configure");
        goto FAIL;
    }

    switch (config->mode) {
        MODECASE(DETTOOL_MODE_PENDING,          pendingMode);
        MODECASE(DETTOOL_MODE_DEFINEBYTAG,      definebytagMode);
        MODECASE(DETTOOL_MODE_DEFINEBYQUERY,    definebyqueryMode);
        MODECASE(DETTOOL_MODE_DEFINEBYDETRUN,   definebydetrunMode);
        MODECASE(DETTOOL_MODE_RUNS,             runsMode);
        MODECASE(DETTOOL_MODE_CHILDLESSRUN,     childlessrunMode);
        MODECASE(DETTOOL_MODE_INPUT,            inputMode);
        MODECASE(DETTOOL_MODE_RAW,              rawMode);
        // correction
        MODECASE(DETTOOL_MODE_MAKECORRECTION,    makecorrectionMode);
        MODECASE(DETTOOL_MODE_TOCORRECTEXP,      tocorrectexpMode);
        MODECASE(DETTOOL_MODE_TOCORRECTIMFILE,   tocorrectimfileMode);
        MODECASE(DETTOOL_MODE_ADDCORRECTIMFILE,  addcorrectimfileMode);
        // imfile
        MODECASE(DETTOOL_MODE_TOPROCESSEDIMFILE,toprocessedimfileMode);
        MODECASE(DETTOOL_MODE_ADDPROCESSEDIMFILE,addprocessedimfileMode);
        MODECASE(DETTOOL_MODE_PROCESSEDIMFILE,  processedimfileMode);
        MODECASE(DETTOOL_MODE_REVERTPROCESSEDIMFILE, revertprocessedimfileMode);
        // exp
        MODECASE(DETTOOL_MODE_TOPROCESSEDEXP,   toprocessedexpMode);
        MODECASE(DETTOOL_MODE_ADDPROCESSEDEXP,  addprocessedexpMode);
        MODECASE(DETTOOL_MODE_PROCESSEDEXP,     processedexpMode);
        MODECASE(DETTOOL_MODE_REVERTPROCESSEDEXP, revertprocessedexpMode);
        // stacked
        MODECASE(DETTOOL_MODE_TOSTACKED,        tostackedMode);
        MODECASE(DETTOOL_MODE_ADDSTACKED,       addstackedMode);
        MODECASE(DETTOOL_MODE_STACKED,          stackedMode);
        MODECASE(DETTOOL_MODE_REVERTSTACKED,    revertstackedMode);
        // normalizedstat
        MODECASE(DETTOOL_MODE_TONORMALIZEDSTAT, tonormalizedstatMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDSTAT,addnormalizedstatMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDSTAT,   normalizedstatMode);
        MODECASE(DETTOOL_MODE_REVERTNORMALIZEDSTAT, revertnormalizedstatMode);
        // normalizedimfile
        MODECASE(DETTOOL_MODE_TONORMALIZE,      tonormalizeMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDIMFILE,addnormalizedimfileMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDIMFILE, normalizedimfileMode);
        MODECASE(DETTOOL_MODE_REVERTNORMALIZEDIMFILE, revertnormalizedimfileMode);
        // normalizedexp
        MODECASE(DETTOOL_MODE_TONORMALIZEDEXP,  tonormalizedexpMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDEXP, addnormalizedexpMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDEXP,    normalizedexpMode);
        MODECASE(DETTOOL_MODE_REVERTNORMALIZEDEXP, revertnormalizedexpMode);
        // residimfile
        MODECASE(DETTOOL_MODE_TORESIDIMFILE,    toresidimfileMode);
        MODECASE(DETTOOL_MODE_ADDRESIDIMFILE,   addresidimfileMode);
        MODECASE(DETTOOL_MODE_RESIDIMFILE,      residimfileMode);
        MODECASE(DETTOOL_MODE_REVERTRESIDIMFILE,revertresidimfileMode);
        // residexp
        MODECASE(DETTOOL_MODE_TORESIDEXP,       toresidexpMode);
        MODECASE(DETTOOL_MODE_ADDRESIDEXP,      addresidexpMode);
        MODECASE(DETTOOL_MODE_RESIDEXP,         residexpMode);
        MODECASE(DETTOOL_MODE_REVERTRESIDEXP,   revertresidexpMode);
        MODECASE(DETTOOL_MODE_UPDATERESIDEXP,   updateresidexpMode);
        // detrunsummary
        MODECASE(DETTOOL_MODE_TODETRUNSUMMARY,  todetrunsummaryMode);
        MODECASE(DETTOOL_MODE_ADDDETRUNSUMMARY, adddetrunsummaryMode);
        MODECASE(DETTOOL_MODE_DETRUNSUMMARY,    detrunsummaryMode);
        MODECASE(DETTOOL_MODE_REVERTDETRUNSUMMARY, revertdetrunsummaryMode);
        MODECASE(DETTOOL_MODE_UPDATEDETRUNSUMMARY, updatedetrunsummaryMode);
        MODECASE(DETTOOL_MODE_UPDATEDETRUN,     updatedetrunMode);
        MODECASE(DETTOOL_MODE_RERUN,            rerunMode);
        // register
        MODECASE(DETTOOL_MODE_REGISTER_DETREND, register_detrendMode);
        MODECASE(DETTOOL_MODE_REGISTER_DETREND_IMFILE, register_detrend_imfileMode);
        default:
            psAbort("invalid option (this should not happen)");
    }

    psFree(config);
    pmConfigDone();
    psLibFinalize();

    exit(EXIT_SUCCESS);

FAIL:
    psErrorStackPrint(stderr, "\n");
    int exit_status = pxerrorGetExitStatus();

    psFree(config);
    pmConfigDone();
    psLibFinalize();

    exit(exit_status);
}

static bool pendingMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);

    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_pending.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }
    
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-exp_id", "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_type", "exp_type", "==");
    PXOPT_COPY_STR(config->args, where, "-inst", "camera", "==");
    PXOPT_COPY_STR(config->args, where, "-telescope", "telescope", "==");
    PXOPT_COPY_STR(config->args, where, "-filter", "filter", "==");

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "rawExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool definebytagMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // what type of detRun is this?
    // required
    PXOPT_LOOKUP_STR(det_type, config->args, "-det_type", true, false);
    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false);

    // optional
    PXOPT_LOOKUP_STR(filelevel, config->args, "-filelevel", false, false);
    PXOPT_LOOKUP_STR(mode, config->args, "-mode", false, false);
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }
    PXOPT_LOOKUP_STR(camera, config->args, "-inst", false, false);
    PXOPT_LOOKUP_STR(telescope, config->args, "-telescope", false, false);
    PXOPT_LOOKUP_STR(exp_type, config->args, "-exp_type", false, false);
    PXOPT_LOOKUP_STR(filter, config->args, "-filter", false, false);

    PXOPT_LOOKUP_F32(airmass_min, config->args, "-airmass_min", false, false);
    PXOPT_LOOKUP_F32(airmass_max, config->args, "-airmass_max", false, false);
    PXOPT_LOOKUP_F32(exp_time_min, config->args, "-exp_time_min", false, false);
    PXOPT_LOOKUP_F32(exp_time_max, config->args, "-exp_time_max", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_min, config->args, "-ccd_temp_min", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_max, config->args, "-ccd_temp_max", false, false);
    PXOPT_LOOKUP_F64(posang_min, config->args, "-posang_min", false, false);
    PXOPT_LOOKUP_F64(posang_max, config->args, "-posang_max", false, false);
    PXOPT_LOOKUP_F64(solang_min, config->args, "-solang_min", false, false);
    PXOPT_LOOKUP_F64(solang_max, config->args, "-solang_max", false, false);
    PXOPT_LOOKUP_TIME(time_begin, config->args, "-time_begin", false, false);
    PXOPT_LOOKUP_TIME(time_end, config->args, "-time_end", false, false);
    PXOPT_LOOKUP_TIME(use_begin, config->args, "-use_begin", false, false);
    PXOPT_LOOKUP_TIME(use_end, config->args, "-use_end", false, false);
    PXOPT_LOOKUP_STR(reduction, config->args, "-reduction", false, false);
    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);

    // default
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);

    // we have to support multipe exp_ids
    psMetadataItem *item = psMetadataLookup(config->args, "-exp_id");
    if (!item) {
        // this shouldn't actually happen when using psArgs
        psError(PS_ERR_UNKNOWN, true, "-exp_id is required");
        return false;
    }
    psMetadata *where = psMetadataAlloc();

    // make sure that -exp_id was parsed correctly
    // XXX this can be removed someday
    // XXX Special case for a multi : move into a macro?
    if (item->type == PS_DATA_METADATA_MULTI) {
        psListIterator *iter = psListIteratorAlloc(item->data.list, 0, false);
        psMetadataItem *mItem = NULL;
        while ((mItem = psListGetAndIncrement(iter))) {
            psString exp_id = mItem->data.V;
            // if exp_id is NULL then it means that -exp_id has not been
            // specified
            if (!exp_id) {
                psError(PS_ERR_UNKNOWN, true,
                        "at least one -exp_id is required");
                psFree(where);
                return false;
            }

            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id",
                 PS_META_DUPLICATE_OK, "==", exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(iter);
                psFree(where);
                return false;
            }
        }
        psFree(iter);
    } else {
        psAbort(                "-exp_id was not parsed correctly (this should not happen");
    }

    if (psListLength(where->list) < 1) {
        psFree(where);
        where = NULL;
    }

    // check that the specified exp_ids actually exist
    psArray *detrendExps = rawExpSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    if (!detrendExps) {
        psError(PS_ERR_UNKNOWN, false, "no rawExp rows found");
        return false;
    }

    // we should have one rawExp row per exp_id specified
    if (psListLength(item->data.list) != psArrayLength(detrendExps)) {
        psAbort(    "an -exp_id matched more then one rawExp (this should not happen");

    }

    // check to see if -filelevel was set on the command line
    if (!filelevel) {
        filelevel = psStringCopy(((rawExpRow *)detrendExps->data[0])->filelevel);
    }

    // start a transaction so we don't end up with childlessed det_ids
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detrendExps);
        return false;
    }

    // the first iteration is always 0
    // XXX the camera name is set from the first inputExp
    // XXX det_id
    detRunInsert(config->dbh,
                 0,
                 0,
                 det_type,
                 mode,
                 "run",
                 filelevel,
                 workdir,
                 camera,
                 telescope,
                 exp_type,
                 reduction,
                 filter,
                 airmass_min,
                 airmass_max,
                 exp_time_min,
                 exp_time_max,
                 ccd_temp_min,
                 ccd_temp_max,
                 posang_min,
                 posang_max,
                 registered,
                 time_begin,
                 time_end,
                 use_begin,
                 use_end,
                 solang_min,
                 solang_max,
                 label,
                 0       // parent
        );
    psS64 det_id = psDBLastInsertID(config->dbh);

    // create new detInputExp row(s) from the rawExp row(s)
    psArray *inputExps = psArrayAllocEmpty(psArrayLength(detrendExps));
    for (long i = 0; i < psArrayLength(detrendExps); i++) {
        detInputExpRow *inputExp = rawDetrenTodetInputExpRow(
            detrendExps->data[i],
            det_id,
            0 // the first iteration is explicitly 0
        );
        psArrayAdd(inputExps, 0, inputExp);
        psFree(inputExp);
    }

    psFree(detrendExps);

    // insert detInputExp objects into the database
    if (!detInputExpInsertObjects(config->dbh, inputExps)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(inputExps);
        return false;
    }
    psFree(inputExps);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    // print the new det_id
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id);
        detRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check results
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", det_id);
        return false;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, detRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(detRuns);
        return false;
    }
    psFree(detRuns);

    return true;
}

#if 0
// This function is used to convert the det_id from an int, as it is used
// internally, to be a string for external use.  The rationale being that we may
// want to change how det_id is generated in the future and don't want to
// external programs to become depending on this value being an int.
static bool convertDetIdToStr(psArray *mds)
{
    PS_ASSERT_PTR_NON_NULL(mds, false);

    for (long i = 0; i < psArrayLength(mds); i++) {
        psMetadata *md = mds->data[i];
        bool status = false;
        psS32 det_id = psMetadataLookupS32(&status, md, "det_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for det_id");
            return false;
        }
        psMetadataRemoveKey(md, "det_id");
        psString det_idStr = psDBIntToString((psU64)det_id);
        psMetadataAddStr(mds->data[i], PS_LIST_HEAD, "det_id", 0, NULL, det_idStr);
        psFree(det_idStr);
    }

    return true;
}
#endif

static bool definebyqueryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required
    PXOPT_LOOKUP_STR(det_type, config->args, "-det_type", true, false);
    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false);
    PXOPT_LOOKUP_STR(camera, config->args, "-inst", true, false);

    // optional
    PXOPT_LOOKUP_STR(mode, config->args, "-mode", false, false);
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalid mode");
        return false;
    }
    PXOPT_LOOKUP_STR(filelevel, config->args, "-filelevel", false, false);
    PXOPT_LOOKUP_STR(telescope, config->args, "-telescope", false, false);
    PXOPT_LOOKUP_STR(filter, config->args, "-filter", false, false);
    PXOPT_LOOKUP_F32(airmass_min, config->args, "-airmass_min", false, false);
    PXOPT_LOOKUP_F32(airmass_max, config->args, "-airmass_max", false, false);
    PXOPT_LOOKUP_F32(exp_time_min, config->args, "-exp_time_min", false, false);
    PXOPT_LOOKUP_F32(exp_time_max, config->args, "-exp_time_max", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_min, config->args, "-ccd_temp_min", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_max, config->args, "-ccd_temp_max", false, false);
    PXOPT_LOOKUP_F64(posang_min, config->args, "-posang_min", false, false);
    PXOPT_LOOKUP_F64(posang_max, config->args, "-posang_max", false, false);
    PXOPT_LOOKUP_F64(solang_min, config->args, "-solang_min", false, false);
    PXOPT_LOOKUP_F64(solang_max, config->args, "-solang_max", false, false);

    PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
    PXOPT_LOOKUP_STR(reduction, config->args, "-reduction", false, false);
    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    PXOPT_LOOKUP_BOOL(randomSubset, config->args, "-random_subset", false);
    PXOPT_LOOKUP_S32(randomLimit, config->args, "-random_limit", false, false);

    PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);
    PXOPT_LOOKUP_TIME(time_begin, config->args, "-time_begin", false, false);
    PXOPT_LOOKUP_TIME(time_end, config->args, "-time_end", false, false);
    PXOPT_LOOKUP_TIME(use_begin, config->args, "-use_begin", false, false);
    PXOPT_LOOKUP_TIME(use_end, config->args, "-use_end", false, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-select_exp_type", "exp_type", "==");
    PXOPT_COPY_STR(config->args, where, "-select_inst", "camera", "==");
    PXOPT_COPY_STR(config->args, where, "-select_telescope", "telescope", "==");
    PXOPT_COPY_STR(config->args, where, "-select_filter", "filter", "==");
    PXOPT_COPY_STR(config->args, where, "-select_uri", "uri", "==");
    PXOPT_COPY_TIME(config->args, where, "-select_dateobs_begin", "dateobs", ">=");
    PXOPT_COPY_TIME(config->args, where, "-select_dateobs_end", "dateobs", "<=");
    PXOPT_COPY_F32(config->args, where, "-select_airmass_min", "airmass", ">=");
    PXOPT_COPY_F32(config->args, where, "-select_airmass_max", "airmass", "<=");
    PXOPT_COPY_F32(config->args, where, "-select_sat_pixel_frac_max", "sat_pixel_frac", "<=");
    PXOPT_COPY_F32(config->args, where, "-select_exp_time_min", "exp_time", ">=");
    PXOPT_COPY_F32(config->args, where, "-select_exp_time_max", "exp_time", "<=");
    PXOPT_COPY_F64(config->args, where, "-select_ccd_temp_min", "ccd_temp", ">=");
    PXOPT_COPY_F64(config->args, where, "-select_ccd_temp_max", "ccd_temp", "<=");
    PXOPT_COPY_F64(config->args, where, "-select_pon_time_min", "pon_time", ">=");
    PXOPT_COPY_F64(config->args, where, "-select_pon_time_max", "pon_time", "<=");
    PXOPT_COPY_F64(config->args, where, "-select_posang_min", "posang", ">=");
    PXOPT_COPY_F64(config->args, where, "-select_posang_max", "posang", "<=");
    PXOPT_COPY_F64(config->args, where, "-select_solang_min", "solang", ">=");
    PXOPT_COPY_F64(config->args, where, "-select_solang_max", "solang", "<=");

    if (!psListLength(where->list)) {
        psFree(where);
        where = NULL;
    }

    // there is some namespace overlap between the names of the fields we'd
    // like to search by to setup a detrun and the names of the fields we'd
    // like to assign values to so I've separated them but prepending set- to
    // the assigned values

    // search for rawExps with the specified options
    psArray *detrendExps = rawExpSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    // make sure that we found at least one rawExp
    if (!detrendExps) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(detrendExps)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(detrendExps);
        return true;
    }

    if (randomSubset && (randomLimit < detrendExps->n)) {
      // generate a random-valued vector, return an index sorted by the random values
      psVector *randomVector = psVectorAlloc(detrendExps->n, PS_TYPE_F32); // random values
      psRandom *rng = psRandomAlloc(PS_RANDOM_TAUS, 0);
      for (int i = 0; i < randomVector->n; i++) {
        randomVector->data.F32[i] = psRandomUniform(rng);
      }
      psVector *indexVector = psVectorSortIndex(NULL, randomVector);

      // accept for first n of the sequence
      psArray *subset = psArrayAlloc (randomLimit);
      for (int i = 0; i < randomLimit; i++ ){
        int j = indexVector->data.S32[i];
        subset->data[i] = psMemIncrRefCounter (detrendExps->data[j]);
      }
      psFree (detrendExps);
      detrendExps = subset;
    }

    // check to see if -filelevel was set on the command line
    if (!filelevel) {
        filelevel = psStringCopy(((rawExpRow *)detrendExps->data[0])->filelevel);
    }

    if (pretend) {
        // negative simple so the default is true
        if (!rawExpPrintObjects(stdout, detrendExps, !simple)) {
            psError(PS_ERR_UNKNOWN, false, "failed to print array");
            psFree(detrendExps);
            return false;
        }
        psFree(detrendExps);
        return true;
    }

    // start a transaction so we don't end up with childlessed det_ids
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detrendExps);
        return false;
    }

    // the first iteration is always 0
    // XXX det_id
    detRunInsert(config->dbh,
                 0,      // det_id
                 0,      // iteration
                 det_type,
                 mode,
                 "run",  // state
                 filelevel,
                 workdir,
                 camera,
                 telescope,
                 "NA",
                 reduction,
                 filter,
                 airmass_min,
                 airmass_max,
                 exp_time_min,
                 exp_time_max,
                 ccd_temp_min,
                 ccd_temp_max,
                 posang_min,
                 posang_max,
                 registered,
                 time_begin,
                 time_end,
                 use_begin,
                 use_end,
                 solang_min,
                 solang_max,
                 label,
                 0       // parent
        );
    psS64 det_id = psDBLastInsertID(config->dbh);

    // create new detInputExp row(s) from the rawExp row(s)
    psArray *inputExps = psArrayAllocEmpty(psArrayLength(detrendExps));
    for (long i = 0; i < psArrayLength(detrendExps); i++) {
        detInputExpRow *inputExp = rawDetrenTodetInputExpRow(
            detrendExps->data[i],
            det_id,
            0 // the first iteration is explicitly 0
        );
        psArrayAdd(inputExps, 0, inputExp);
        psFree(inputExp);
    }

    psFree(detrendExps);

    // insert detInputExp objects into the database
    if (!detInputExpInsertObjects(config->dbh, inputExps)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(inputExps);
        return false;
    }
    psFree(inputExps);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }


    // print the new det_id
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id);
        detRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check results
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", det_id);
        return false;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, detRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(detRuns);
        return false;
    }
    psFree(detRuns);

    return true;
}

static bool definebydetrunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_STR(det_type, config->args, "-set_det_type", false, false); // optional
    PXOPT_LOOKUP_STR(mode, config->args, "-set_mode", false, false); // optional
    PXOPT_LOOKUP_STR(exp_type, config->args, "-set_exp_type", false, false);
    PXOPT_LOOKUP_STR(filelevel, config->args, "-set_filelevel", false, false);
    PXOPT_LOOKUP_STR(workdir, config->args, "-set_workdir", false, false);
    PXOPT_LOOKUP_STR(filter, config->args, "-set_filter", false, false);
    PXOPT_LOOKUP_F32(airmass_min, config->args, "-set_airmass_min", false, false);
    PXOPT_LOOKUP_F32(airmass_max, config->args, "-set_airmass_max", false, false);
    PXOPT_LOOKUP_F32(exp_time_min, config->args, "-set_exp_time_min", false, false);
    PXOPT_LOOKUP_F32(exp_time_max, config->args, "-set_exp_time_max", false, false);
    PXOPT_LOOKUP_F64(ccd_temp_min, config->args, "-set_ccd_temp_min", false, false);
    PXOPT_LOOKUP_F64(ccd_temp_max, config->args, "-set_ccd_temp_max", false, false);
    PXOPT_LOOKUP_F64(posang_min, config->args, "-set_posang_min", false, false);
    PXOPT_LOOKUP_F64(posang_max, config->args, "-set_posang_max", false, false);
    PXOPT_LOOKUP_F64(solang_min, config->args, "-set_solang_min", false, false);
    PXOPT_LOOKUP_F64(solang_max, config->args, "-set_solang_max", false, false);
    PXOPT_LOOKUP_TIME(registered, config->args, "-set_registered", false, false);
    PXOPT_LOOKUP_TIME(time_begin, config->args, "-set_time_begin", false, false);
    PXOPT_LOOKUP_TIME(time_end, config->args, "-set_end", false, false);
    PXOPT_LOOKUP_TIME(use_begin, config->args, "-use_begin", false, false);
    PXOPT_LOOKUP_TIME(use_end, config->args, "-use_end", false, false);
    PXOPT_LOOKUP_STR(reduction, config->args, "-set_reduction", false, false);
    PXOPT_LOOKUP_STR(label, config->args, "-set_label", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false); // optional

    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }

    // lookup the detRun that we will be basing this one on
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", (psS64)atoll(det_id));
        detRuns = detRunSelectRowObjects(config->dbh, where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    // sanity check the result... we should have only found one det_id
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", (psS64)atoll(det_id));
        return false;                   // unreachable
    }

    // pull the detRun object out the result array
    detRunRow *detRun = psMemIncrRefCounter(detRuns->data[0]);

    // discard the resultarray
    psFree(detRuns);

    // set the det_id to 0/NULL so the database can assign it
    detRun->det_id = 0;

    // reset the iteration to 0
    detRun->iteration = 0;

    // reset the state to "run"
    psFree(detRun->state);
    detRun->state = psStringCopy("run");

    // walk through the optional values and update the detRun as required
    if (det_type) {
        psFree(detRun->det_type);
        detRun->det_type = psStringCopy(det_type);
    }

    if (mode) {
        psFree(detRun->mode);
        detRun->mode = psStringCopy(mode);
    }

    if (exp_type) {
        psFree(detRun->exp_type);
        detRun->exp_type = psStringCopy(exp_type);
    }

    if (filelevel) {
        psFree(detRun->filelevel);
        detRun->filelevel = psStringCopy(filelevel);
    }

    if (workdir) {
        psFree(detRun->workdir);
        detRun->workdir = psStringCopy(workdir);
    }

    if (filter) {
        psFree(detRun->filter);
        detRun->filter = psStringCopy(filter);
    }

    if (!isnan(airmass_min)) {
        detRun->airmass_min = airmass_min;
    }

    if (!isnan(airmass_max)) {
        detRun->airmass_max = airmass_max;
    }

    if (!isnan(exp_time_min)) {
        detRun->exp_time_min = exp_time_min;
    }

    if (!isnan(exp_time_max)) {
        detRun->exp_time_max = exp_time_max;
    }

    if (!isnan(ccd_temp_min)) {
        detRun->ccd_temp_min = ccd_temp_min;
    }

    if (!isnan(ccd_temp_max)) {
        detRun->ccd_temp_max = ccd_temp_max;
    }

    if (!isnan(posang_min)) {
        detRun->posang_min = posang_min;
    }

    if (!isnan(posang_max)) {
        detRun->posang_max = posang_max;
    }

    if (!isnan(solang_min)) {
        detRun->solang_min = solang_min;
    }

    if (!isnan(solang_max)) {
        detRun->solang_max = solang_max;
    }

    if (label) {
        psFree(detRun->label);
        detRun->label = label;
    }

    if (registered) {
        psFree(detRun->registered);
        detRun->registered = psMemIncrRefCounter(registered);
    }

    if (time_begin) {
        psFree(detRun->time_begin);
        detRun->time_begin = psMemIncrRefCounter(time_begin);
    }

    if (time_end) {
        psFree(detRun->time_end);
        detRun->time_end = psMemIncrRefCounter(time_end);
    }

    if (use_begin) {
        psFree(detRun->use_begin);
        detRun->use_begin = psMemIncrRefCounter(use_begin);
    }

    if (use_end) {
        psFree(detRun->use_end);
        detRun->use_end = psMemIncrRefCounter(use_end);
    }

    if (reduction) {
        psFree(detRun->reduction);
        detRun->reduction = psStringCopy(reduction);
    }

    // create a metadata to restrict detInputExp's be in in the specified range

    psMetadata *time_filter = psMetadataAlloc();

    // XXX: possible mem leak: PXOPT_COPY* will free time_filter but NOTHING
    // that has previously been allocated
    PXOPT_COPY_TIME(config->args, time_filter, "-set_input_begin", "dateobs", ">=");
    PXOPT_COPY_TIME(config->args, time_filter, "-set_input_end", "dateobs", "<");

    // start a transaction so we don't end up with childlessed det_ids
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(time_filter);
        psFree(detRun);
        return false;
    }

    if (!detRunInsertObject(config->dbh, detRun)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(time_filter);
        psFree(detRun);
        return false;
    }
    psFree(detRun);

    // get the det_id
    psS64 newDet_id = psDBLastInsertID(config->dbh);

    psString query = pxDataGet("dettool_definebydetrun.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (time_filter->list->n) {
        psString whereClause = psDBGenerateWhereConditionSQL(time_filter, "rawExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(time_filter);

    if (!p_psDBRunQuery(config->dbh, query, (psS64)newDet_id, (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    // print the new det_id
    psArray *newDetRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", newDet_id);
        newDetRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!newDetRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check the result... we should have only found one det_id
    if (psArrayLength(newDetRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 " (this should not happen)", newDet_id);
        return false;                   // unreachable
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, newDetRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(newDetRuns);
        return false;
    }
    psFree(newDetRuns);

    return true;
}

static bool runsMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_type", "det_type", "==");
    PXOPT_COPY_STR(config->args, where, "-det_id",   "det_id", "==");

    PXOPT_COPY_STR(config->args, where, "-inst", "camera", "==");
    PXOPT_COPY_STR(config->args, where, "-telescope", "telescope", "==");
    PXOPT_COPY_STR(config->args, where, "-filter", "filter", "==");

    // airmass_min  < airmass  < airmass_max
    PXOPT_COPY_F32(config->args, where, "-airmass", "airmass_min", "<=");
    PXOPT_COPY_F32(config->args, where, "-airmass", "airmass_max", ">=");

    // exp_time_min < exp_time < exp_time_max
    PXOPT_COPY_F32(config->args, where, "-exp_time", "exp_time_min", "<=");
    PXOPT_COPY_F32(config->args, where, "-exp_time", "exp_time_max", ">=");

    // ccd_temp_min < ccd_temp < ccd_temp_max
    PXOPT_COPY_F32(config->args, where, "-ccd_temp", "ccd_temp_min", "<=");
    PXOPT_COPY_F32(config->args, where, "-ccd_temp", "ccd_temp_max", ">=");

    PXOPT_COPY_F64(config->args, where, "-posang", "posang_min", "<=");
    PXOPT_COPY_F64(config->args, where, "-posang", "posang_max", ">=");

    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(active, config->args, "-active", false);
    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);

    psString query = pxDataGet("dettool_runs.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list) && active) {
        psString whereClause = psDBGenerateWhereSQL(where, "detRun");
        psStringAppend(&query, " %s AND (detRun.state = 'run' OR detRun.state = 'stop' OR detRun.state = 'register')", whereClause);
        psFree(whereClause);
    }
    if (psListLength(where->list) && !active) {
        psString whereClause = psDBGenerateWhereSQL(where, "detRun");
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }
    if (!psListLength(where->list) && active) {
        psStringAppend(&query, " WHERE (detRun.state = 'run' OR detRun.state = 'stop' OR detRun.state = 'register')");
    }
    psFree (where);

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *runs = p_psDBFetchResult(config->dbh);
    if (!runs) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!psArrayLength(runs)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(runs);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, runs, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(runs);
        return false;
    }

    psFree(runs);

    return true;
}

static bool childlessrunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_type", "det_type", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    // find the exp_id of all the exposures that we want to queue up.
    psString query = pxDataGet("dettool_childlessrun.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        psFree(where);
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detRun");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree (where);

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static detInputExpRow *rawDetrenTodetInputExpRow(rawExpRow *rawExp, psS64 det_id, psS32 iteration)
{
    PS_ASSERT_PTR_NON_NULL(rawExp, NULL);

    return detInputExpRowAlloc(
        det_id,
        iteration,
        rawExp->exp_id,
        true            // use
    );
}

static bool inputMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_S64(config->args, where, "-exp_id",    "exp_id", "==");

    psString query = pxDataGet("dettool_input.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        psFree(where);
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereSQL(where, "detInputExp");
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }
    psFree (where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detInputExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool rawMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psMetadata *where = psMetadataAlloc();

    psString query = pxDataGet("dettool_raw.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "rawImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree (where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool toprocessedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",   "det_id", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id",   "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id", "class_id", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_toprocessedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "rawImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree (where);

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingProcessedImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addprocessedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, exp_id, class_id, uri, recipe, -bg, -bg_stdev are required
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false);
    PXOPT_LOOKUP_STR(exp_id, config->args, "-exp_id", true, false);
    PXOPT_LOOKUP_STR(class_id, config->args, "-class_id", true, false);

    // default values
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

    // Required if code == 0
    PXOPT_LOOKUP_STR(uri, config->args, "-uri", (code == 0), false);
    PXOPT_LOOKUP_STR(recipe, config->args, "-recip", (code == 0), false);

    // optional
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(fringe_0, config->args, "-fringe_0", false, false);
    PXOPT_LOOKUP_F64(fringe_1, config->args, "-fringe_1", false, false);
    PXOPT_LOOKUP_F64(fringe_2, config->args, "-fringe_2", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);

    // find the matching rawImfile by exp_id/class_id
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-exp_id",   "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id", "class_id", "==");

    psArray *rawImfiles = rawImfileSelectRowObjects(config->dbh, where, 0);
    psFree(where);

    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "no rawImfile rows found ");
        return false;
    }

    // create a new detProcessedImfile object
    detProcessedImfileRow *detRow = detProcessedImfileRowAlloc(
        (psS64)atoll(det_id),
        (psS64)atoll(exp_id),
        class_id,
        uri,
        recipe,
        bg,
        bg_stdev,
        bg_mean_stdev,
        fringe_0,
        fringe_1,
        fringe_2,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );
    psFree(rawImfiles);

    // insert the new row into the detProcessedImfile table
    if (!detProcessedImfileInsertObject(config->dbh, detRow)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detRow);
        return false;
    }

    psFree(detRow);

    return true;
}

static bool processedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    char *value = NULL;

    PXOPT_LOOKUP_BOOL(included, config->args, "-included", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);
    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id", "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id", "class_id", "==");

    psString query = pxDataGet("dettool_processedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        psFree(where);
        return false;
    }

    // add the two required restrictions: detRun.state and detRun.mode
    // NOTE the above query requires one of the following two WHERE statements
    {
        bool status;
        if ((value = psMetadataLookupStr(&status, config->args, "-select_state"))) {
            psStringAppend(&query, " WHERE detRun.state = '%s'", value);
        } else {
            psStringAppend(&query, " WHERE detRun.state = 'run'");
        }
        if ((value = psMetadataLookupStr(&status, config->args, "-select_mode"))) {
            psStringAppend(&query, " AND detRun.mode = '%s'", value);
        } else {
            psStringAppend(&query, " AND detRun.mode = 'master'");
        }
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detProcessedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree (where);

    // restrict search to included imfiles
    if (included) {
	psStringAppend(&query, " AND detInputExp.include = 1");
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detProcessedImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detProcessedImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool revertprocessedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id", "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id", "class_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code", "fault", "==");

    psString query = pxDataGet("dettool_revertprocessedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detProcessedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}

static bool toprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_toprocessedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingProcessedExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, exp_id, recip, -bg, -bg_stdev
    // are required
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false);
    PXOPT_LOOKUP_STR(exp_id, config->args, "-exp_id", true, false);

    // default values
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

    // Required if code == 0
    PXOPT_LOOKUP_STR(recipe, config->args, "-recip", (code == 0), false);

    // optional
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(fringe_0, config->args, "-fringe_0", false, false);
    PXOPT_LOOKUP_F64(fringe_1, config->args, "-fringe_1", false, false);
    PXOPT_LOOKUP_F64(fringe_2, config->args, "-fringe_2", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", true, false);

    psString query = pxDataGet("dettool_addprocessedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (!p_psDBRunQuery(config->dbh, query, det_id, exp_id)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }
    psFree(output);

    // create a new detProcessedImfile object
    detProcessedExpRow *detRow = detProcessedExpRowAlloc(
        (psS64)atoll(det_id),
        (psS64)atoll(exp_id),
        recipe,
        bg,
        bg_stdev,
        bg_mean_stdev,
        fringe_0,
        fringe_1,
        fringe_2,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );

    // insert the new row into the detProcessedImfile table
    if (!detProcessedExpInsertObject(config->dbh, detRow)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(detRow);
        return false;
    }

    psFree(detRow);

    return true;
}

static bool processedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id", "exp_id", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = psStringCopy("SELECT * FROM detProcessedExp");

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereSQL(where, NULL);
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detProcessedExp.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detProcessedExp.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detProcessedExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id", "exp_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code",   "fault", "==");

    psString query = pxDataGet("dettool_revertprocessedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detProcessedExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool tostackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_tostacked.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingStackedImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

// this is NOT a command-line mode : it is used by 'addstackedMode'
static psArray *searchRawImfiles(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");

    // select exp_ids from detInputExp matching det_id & iteration
    // where query should be pre-generated
    psArray *detInputExp = detInputExpSelectRowObjects(config->dbh, where, 0);
    psFree (where);

    if (!detInputExp) {
        psError(PS_ERR_UNKNOWN, false, "no rawExp rows found");
        return NULL;
    }

    // generate where query with just the exp_ids
    psMetadata *where_exp_ids = psMetadataAlloc();
    for (long i = 0; i < psArrayLength(detInputExp); i++) {
        detInputExpRow *row = detInputExp->data[i];
        if (!psMetadataAddS64(where_exp_ids, PS_LIST_TAIL, "exp_id",
                PS_META_DUPLICATE_OK, "==", row->exp_id)
        ) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
            psFree(detInputExp);
            psFree(where_exp_ids);
            return NULL;
        }
    }
    psFree(detInputExp);

    // select rawImfiles with matching exp_ids
    psArray *rawImfiles =
        rawImfileSelectRowObjects(config->dbh, where_exp_ids, 0);
    psFree(where_exp_ids);
    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "no rawImfile rows found");
        return NULL;
    }

    return rawImfiles;
}

static bool addstackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, iteration, class_id, uri, & recipe are required
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false);
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", true, false);
    PXOPT_LOOKUP_STR(class_id, config->args, "-class_id", true, false);

    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

    // Required if code == 0
    PXOPT_LOOKUP_STR(uri, config->args, "-uri", (code == 0), false);
    PXOPT_LOOKUP_STR(recipe, config->args, "-recip", (code == 0), false);

    // optional
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);

    // correlate the class_id against the input exposure(s)
    // searchRawImfiles defines a where clause to search by det_id and iteration
    psArray *rawImfiles = searchRawImfiles(config);

    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "failed to get rawImfiles from db");
        return false;
    }

    bool valid_class_id = false;
    if (rawImfiles) {
        for (long i = 0; i < psArrayLength(rawImfiles); i++) {
            if (!rawImfiles->data[i]) {
                fprintf (stderr, "*");
                continue;
            }
            if (strcmp(class_id, ((rawImfileRow *)rawImfiles->data[i])->class_id) == 0) {
                valid_class_id = true;
                break;
            }
        }
        psFree(rawImfiles);
    }

    if (!valid_class_id) {
        psError(PS_ERR_UNKNOWN, true,
            "class_id can not be correlated with the input exposures");
        return false;
    }

    // create a new detStackedImfile object
    detStackedImfileRow *stackedImfile = detStackedImfileRowAlloc(
            (psS64)atoll(det_id),
            iteration,
            class_id,
            uri,
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            code
        );

    // insert the new row into the detProcessedImfile table
    if (!detStackedImfileInsertObject(config->dbh, stackedImfile)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(stackedImfile);
        return false;
    }

    psFree(stackedImfile);

    return true;
}

static bool stackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",  "class_id", "==");
    PXOPT_COPY_STR(config->args, where, "-recip",     "recipe", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_stacked.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        psFree(where);
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detStackedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detStackedImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detStackedImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertstackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",  "class_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code",      "fault", "==");

    psString query = pxDataGet("dettool_revertstacked.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detStackedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}

static bool tonormalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_tonormalizedstat.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingNormStatImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addnormalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);

    // required
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false);
    PXOPT_LOOKUP_STR(class_id, config->args, "-class_id", true, false);

    // optional
    PXOPT_LOOKUP_F32(norm, config->args, "-norm", false, false);

    // default
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", false, false);
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

    if (!detNormalizedStatImfileInsert(config->dbh,
        (psS64)atoll(det_id),
        iteration,
        class_id,
        norm,
        code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}


static bool normalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",  "class_id", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_normalizedstat.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "WHERE fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "WHERE fault = 0");
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detNormalizedStatImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool revertnormalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",  "class_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code",      "fault", "==");

    psString query = pxDataGet("dettool_revertnormalizedstat.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detNormalizedStatImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}

static bool tonormalizeMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_tonormalize.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingNormImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addnormalizedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false);
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", false, false);
    PXOPT_LOOKUP_STR(class_id, config->args, "-class_id", true, false);

    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);

    // Required if code == 0
    PXOPT_LOOKUP_STR(uri, config->args, "-uri", (code == 0), false);

    // optional
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);

    if (!detNormalizedImfileInsert(config->dbh,
        (psS64)atoll(det_id),
        iteration,
        class_id,
        uri,
        bg,
        bg_stdev,
        bg_mean_stdev,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool normalizedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",  "class_id", "==");
    PXOPT_COPY_STR(config->args, where, "-recip",     "recipe", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);

    psString query = pxDataGet("dettool_normalizedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detNormalizedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detNormalizedImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertnormalizedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",  "class_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code",      "fault", "==");

    psString query = pxDataGet("dettool_revertnormalizedimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detNormalizedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}

static bool tonormalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_tonormalizedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingNormExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

static bool addnormalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", false, false);
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);
    PXOPT_LOOKUP_STR(recipe, config->args, "-recip", (code == 0), false); // Required if code == 0
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);

    psString query = pxDataGet("dettool_tonormalizedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // XXX in some other places, we put the arguments in the .sql and supply them to RunQuery
    psStringAppend(&query, " WHERE det_id = %s AND iteration = %d", det_id, iteration);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }
    psFree(output);

    // insert the new row into the detProcessedImfile table
    if (!detNormalizedExpInsert(config->dbh,
        (psS64)atoll(det_id),
        iteration,
        recipe,
        bg,
        bg_stdev,
        bg_mean_stdev,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}


static bool normalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-recip",  "recipe", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);

    psString query = pxDataGet("dettool_normalizedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereSQL(where, NULL);
        psStringAppend(&query, " %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedExp.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detNormalizedExp.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detNormalizedExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}



static bool revertnormalizedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_S16(config->args, where, "-code",      "fault", "==");

    psString query = pxDataGet("dettool_revertnormalizedexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detNormalizedExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool toresidimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_toresidimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingResidImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool addresidimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", false, false);
    PXOPT_LOOKUP_STR(exp_id, config->args, "-exp_id", true, false); // required
    PXOPT_LOOKUP_STR(class_id, config->args, "-class_id", true, false); // required
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);
    PXOPT_LOOKUP_STR(uri, config->args, "-uri", (code == 0), false); // Required if code == 0
    PXOPT_LOOKUP_STR(recipe, config->args, "-recip", (code == 0), false); // Required if code == 0
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_skewness, config->args, "-bg_skewness", false, false);
    PXOPT_LOOKUP_F64(bg_kurtosis, config->args, "-bg_kurtosis", false, false);
    PXOPT_LOOKUP_F64(bin_stdev, config->args, "-bin_stdev", false, false);
    PXOPT_LOOKUP_F64(fringe_0, config->args, "-fringe_0", false, false);
    PXOPT_LOOKUP_F64(fringe_1, config->args, "-fringe_1", false, false);
    PXOPT_LOOKUP_F64(fringe_2, config->args, "-fringe_2", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_0, config->args, "-fringe_resid_0", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_1, config->args, "-fringe_resid_1", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_2, config->args, "-fringe_resid_2", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);

    if (!detResidImfileInsert(config->dbh,
            (psS64)atoll(det_id),
            iteration,
            (psS64)atoll(exp_id),
            class_id,
            uri,
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            bg_skewness,
            bg_kurtosis,
            bin_stdev,
            fringe_0,
            fringe_1,
            fringe_2,
            fringe_resid_0,
            fringe_resid_1,
            fringe_resid_2,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            path_base,
            code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}


static bool residimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",     "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration",  "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id",     "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",   "class_id", "==");
    PXOPT_COPY_STR(config->args, where, "-recip",      "recipe", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);

    psString query = pxDataGet("dettool_residimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // NOTE: the query ends in a WHERE, add the required restriction on detRun.state
    char *value = NULL;
    bool status;
    if ((value = psMetadataLookupStr(&status, config->args, "-select_state"))) {
        psStringAppend(&query, " detRun.state = '%s'", value);
    } else {
        psStringAppend(&query, " detRun.state = 'run'");
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detResidImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detResidImfile.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detResidImfile.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawResidImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertresidimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id",  "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-class_id",  "class_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code",      "fault", "==");

    psString query = pxDataGet("dettool_revertresidimfile.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detResidImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


/*
  The SQL returns a list of exposures for which all component class ids have had residuals
  created.  This list includes the detrend id, iteration, exposure id, detrend type, and
  whether the exposure was included in the stack for this iteration.
*/

static bool toresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_toresidexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detPendingResidExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

/*
  The SQL returns a list of exposures for which all component class ids have had residuals
  created.  This list includes the detrend id, iteration, exposure id, detrend type, and
  whether the exposure was included in the stack for this iteration.
*/

static bool addresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // limit search by det_id, iteration, exp_id
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id", "exp_id", "==");

    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", false, false);
    PXOPT_LOOKUP_STR(exp_id, config->args, "-exp_id", true, false); // required
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);
    PXOPT_LOOKUP_STR(recipe, config->args, "-recip", (code == 0), false); // Required if code == 0
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_skewness, config->args, "-bg_skewness", false, false);
    PXOPT_LOOKUP_F64(bg_kurtosis, config->args, "-bg_kurtosis", false, false);
    PXOPT_LOOKUP_F64(bin_stdev, config->args, "-bin_stdev", false, false);
    PXOPT_LOOKUP_F64(fringe_0, config->args, "-fringe_0", false, false);
    PXOPT_LOOKUP_F64(fringe_1, config->args, "-fringe_1", false, false);
    PXOPT_LOOKUP_F64(fringe_2, config->args, "-fringe_2", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_0, config->args, "-fringe_resid_0", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_1, config->args, "-fringe_resid_1", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_2, config->args, "-fringe_resid_2", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);
    PXOPT_LOOKUP_BOOL(reject, config->args, "-reject", false);

    psString query = pxDataGet("dettool_toresidexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
	psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
	psStringAppend(&query, " WHERE %s", whereClause);
	psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        // XXX check psError here
        psError(PS_ERR_UNKNOWN, false, "no detResidImfile rows found");
        psFree(output);
        return false;
    }
    psFree(output);

    if (!detResidExpInsert(config->dbh,
            (psS64)atoll(det_id),
            iteration,
            (psS64)atoll(exp_id),
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            bg_skewness,
            bg_kurtosis,
            bin_stdev,
            fringe_0,
            fringe_1,
            fringe_2,
            fringe_resid_0,
            fringe_resid_1,
            fringe_resid_2,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            path_base,
            !reject,
            code
        )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool residexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id",    "exp_id", "==");
    PXOPT_COPY_STR(config->args, where, "-recip",     "recipe", "==");

    PXOPT_LOOKUP_U64(limit,    config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple,  config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);
    PXOPT_LOOKUP_BOOL(reject,  config->args, "-reject", false);

    psString query = pxDataGet("dettool_residexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detResidExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detResidExp.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detResidExp.fault = 0");
    }

    // list only accepted rows
    // XXX the usage of this flag is unclear : -reject means "accepted"?
    if (reject) {
        psStringAppend(&query, " %s", "AND detResidExp.accept != 0");
    } 

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detResidExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id",    "exp_id", "==");
    PXOPT_COPY_S16(config->args, where, "-code",      "fault", "==");

    psString query = pxDataGet("dettool_revertresidexp.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detResidExp");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool updateresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_BOOL(reject, config->args, "-reject", false);

    // build a query to search by det_id, iteration, exp_id
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-exp_id", "exp_id", "==");

    // find the values we're going to set
    // copy everything but det_id, iteration, & exp_id from the args and
    // remove the '-' prefix
    psMetadata *set = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, set, "-recip", "recipe", "==");
    PXOPT_COPY_F64(config->args, set, "-bg", "bg", "==");
    PXOPT_COPY_F64(config->args, set, "-bg_stdev", "bg_stdev", "==");
    PXOPT_COPY_F64(config->args, set, "-bg_mean_stdev", "bg_mean_stdev", "==");
    PXOPT_COPY_STR(config->args, set, "-path_base", "path_base", "==");

    // this can't be PXOPT_ macro-ized as reject is !'d
    if (!psMetadataAddBool(set, PS_LIST_TAIL, "accept", 0, "==", !reject)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item accept");
        psFree(set);
        psFree(where);
        return false;
    }

    long changed = psDBUpdateRows(config->dbh, "detResidExp", where, set);
    psFree(set);
    psFree(where);

    if (changed < 0) {
        psError(PS_ERR_UNKNOWN, false, "no rows were updated");
        return false;
    }

    return true;
}

/* The SQL returns a list of detrend runs (with detrend id, iteration and detrend type) which
 * have completed all residexps.
 */

static bool todetrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    psString query = pxDataGet("dettool_todetrunsummary.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "detRejectExp", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}

// this function is used to generate the detrunSummary entries used below
// XXX why is this a separate function?  roll it back into adddetrunsummary?
static detRunSummaryRow *mdToDetRunSummary(pxConfig *config, psMetadata *row)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    // from row
    psS64 det_id = psMetadataLookupS64(&status, row, "det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for det_id");
        return false;
    }
    psS32 iteration = psMetadataLookupS32(&status, row, "iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for iteration");
        return false;
    }

    // optional
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);

    // default values
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, false);
    PXOPT_LOOKUP_BOOL(accept, config->args, "-accept", false);

    return detRunSummaryRowAlloc(
            det_id,
            iteration,
            bg,
            bg_stdev,
            bg_mean_stdev,
            accept,
            code
        );
}

static bool adddetrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    
    // build a query to search by det_id, iteration, exp_id
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");

    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_BOOL(again, config->args, "-again", false);

    // The values supplied as arguments on the command (eg, -bg) are parsed 
    // by mdToDetRunSummary below. 
    // XXX why is there ever more than one?

    psString query = pxDataGet("dettool_find_completed_runs.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
	psStringAppend(&query, " WHERE %s", whereClause);
	psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // start a transaction so it's all rows or nothing
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(output);
        return false;
    }

    // XXX why is there ever more than one result here?
    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detResidExp object
        detRunSummaryRow *runSummary = mdToDetRunSummary(config, row);
        if (!runSummary) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert metadata to detResidExp");
            psFree(output);
            return false;
        }
        // insert detResidExp object into the database
        if (!detRunSummaryInsertObject(config->dbh, runSummary)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(runSummary);
            psFree(output);
            return false;
        }
        psFree(runSummary);
    }

    psFree(output);

    // XXX this logic does not deal with the case of -code being set
    // XXX it should be an error for -again and -code to both be set
    if (again) {
        if (!startNewIteration(config, (psS64)atoll(det_id))) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to start new iteration");
            return false;
        }
    } else {
        // set detRun.state to stop
        if (!setDetRunState(config, (psS64)atoll(det_id), "stop")) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to set detRun.state");
            return false;
        }
    }

    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    return true;
}

static bool detrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_BOOL(faulted, config->args, "-faulted", false);

    psString query = pxDataGet("dettool_detrunsummary.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        psFree(where);
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "NULL");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (faulted) {
        // list only faulted rows
        psStringAppend(&query, " %s", "AND detRunSummary.fault != 0");
    } else {
        // don't list faulted rows
        psStringAppend(&query, " %s", "AND detRunSummary.fault = 0");
    }

    // treat limit == 0 as "no limit"
    if (limit) {
        psString limitString = psDBGenerateLimitSQL(limit);
        psStringAppend(&query, " %s", limitString);
        psFree(limitString);
    }

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(output);
        return true;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, output, "rawDetrendImfile", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(output);
        return false;
    }

    psFree(output);

    return true;
}


static bool revertdetrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id",    "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");
    PXOPT_COPY_STR(config->args, where, "-code",      "fault", "==");

    psString query = pxDataGet("dettool_revertdetrunsummary.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    if (psListLength(where->list)) {
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detRunSummary");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
    psFree(where);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    if (psDBAffectedRows(config->dbh) < 1) {
        psError(PS_ERR_UNKNOWN, false, "should have affected atleast 1 row");
        return false;
    }

    return true;
}


static bool updatedetrunsummaryMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_BOOL(accept, config->args, "-accept", false);
    PXOPT_LOOKUP_BOOL(reject, config->args, "-reject", false);

    if (accept && reject) {
        psError(PS_ERR_UNKNOWN, true, "-accept and -reject are exclusive");
        return false;
    }

    if (!(accept || reject)) {
        psError(PS_ERR_UNKNOWN, true, "either -accept or -reject is required");
        return false;
    }

    char *query = "UPDATE detRunSummary SET accept = %d WHERE det_id = %"PRId64; 
    if (!p_psDBRunQuery(config->dbh, query, accept, (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}


static bool updatedetrunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_BOOL(again, config->args, "-again", false);
    PXOPT_LOOKUP_STR(state, config->args, "-state", false, false);

    // either -rerun or -state must be specified
    if (!(again || state)) {
        psError(PS_ERR_UNKNOWN, true, "either -again or -state must be specified");
        return false;
    }
    if (again && state) {
        psError(PS_ERR_UNKNOWN, true, "-accept and -reject are exclusive");
        return false;
    }

    if (state) {
        // set detRun.state to state
        return setDetRunState(config, (psS64)atoll(det_id), state);
    }

    // else
    // -again
    if (!startNewIteration(config, (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "failed to start new iteration");
        return false;
    }

    return true;
}

// used by updatedetrunMode 
static bool startNewIteration(pxConfig *config, psS64 det_id)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    psString query = pxDataGet("dettool_start_new_iteration.sql");
    if (!query) {
        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
        return false;
    }

    // restrict the update only to the specified det_id
    psStringAppend(&query, " WHERE det_id = %" PRId64 , det_id);

    if (!p_psDBRunQuery(config->dbh, query)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

    psArray *output = p_psDBFetchResult(config->dbh);
    if (!output) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }
    if (!psArrayLength(output)) {
        psError(PS_ERR_UNKNOWN, false, "det_id %" PRId64 " not found", det_id);
        psFree(output);
        return false;
    }

    // start a transaction so we don't end up with an incremented iteration
    // count but no detInputExps
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(output);
        return false;
    }

    // up the detRuns iteration count for for the single det_id we are
    // operating on
    // XXX this will have to changed in order to support multiple det_ids with
    // a single invocation of this functions
    psS32 newIteration = incrementIteration(config, det_id);
    if (!newIteration) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(output);
        return false;
    }

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        bool status = false;
        psS64 exp_id = psMetadataLookupS64(&status, row, "exp_id");
        if (!status) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for exp_id");
            psFree(output);
            return false;
        }
        bool accept = psMetadataLookupBool(&status, row, "accept");
        if (!status) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for accept");
            psFree(output);
            return false;
        }

        // detResidExp.include is used to set detInputExp.include
        if (!detInputExpInsert(
                    config->dbh,
                    det_id,
                    newIteration,
                    exp_id,
                    accept
                )
        ) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(output);
            return false;
        }
    }

    psFree(output);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool rerunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id is required
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false);

    // we have to support multipe exp_ids
    psMetadataItem *item = psMetadataLookup(config->args, "-exp_id");
    if (!item) {
        // this shouldn't actually happen when using psArgs
        psError(PS_ERR_UNKNOWN, true, "-exp_id is required");
        return false;
    }

    psList *exp_id_list = item->data.list;
    psMetadata *where = psMetadataAlloc();
    // make sure that -exp_id was parsed correctly
    // XXX this can be removed someday
    if (item->type == PS_DATA_METADATA_MULTI) {
        psListIterator *iter = psListIteratorAlloc(item->data.list, 0, false);
        psMetadataItem *mItem = NULL;
        while ((mItem = psListGetAndIncrement(iter))) {
            psString exp_id = mItem->data.V;
            // if exp_id is NULL then it means that -exp_id has not been
            // specified
            if (!exp_id) {
                psError(PS_ERR_UNKNOWN, true,
                        "at least one -exp_id is required");
                psFree(where);
                return false;
            }

            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id",
                        PS_META_DUPLICATE_OK, "==", exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(iter);
                psFree(where);
                return false;
            }
        }
        psFree(iter);
    } else {
        psAbort("-exp_id was not parsed correctly (this should not happen");
    }

    // check that the specified exp_ids actually exist in the iteration zero
    // detInputExp set

    // add the det_id & iteration == 0 to the where clause
    if (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==",
                (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
        psFree(where);
        return false;
    }
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", 0)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
        psFree(where);
        return false;
    }

    psArray *detrendExps = detInputExpSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    if (!detrendExps) {
        psError(PS_ERR_UNKNOWN, false, "no rawExp rows found");
        psFree(where);
        return false;
    }

    // build a hash for the valid exp_ids
    psHash *valid_exp_ids = psHashAlloc(psArrayLength(detrendExps));
    for (long i = 0; i < psArrayLength(detrendExps); i++) {
        psString exp_idStr = psDBIntToString(((detInputExpRow *)detrendExps->data[i])->exp_id);
        psHashAdd(valid_exp_ids, exp_idStr, detrendExps->data[i]);
        psFree(exp_idStr);
    }
    psFree(detrendExps);

    // start a transaction so we don't end up with an incremented iteration
    // count but no detInputExps
    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(valid_exp_ids);
        return false;
    }

    // up the detRuns iteration count
    psS32 newIteration = incrementIteration(config, (psS64)atoll(det_id));
    if (!newIteration) {
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        psFree(valid_exp_ids);
        return false;
    }

    // check exp_ids and build up an array of new detInputExp rows at the same
    // time
    psListIterator *iter = psListIteratorAlloc(exp_id_list, 0, false);
    psMetadataItem *mItem = NULL;
    psArray *newInputExps = psArrayAllocEmpty(psListLength(exp_id_list));
    while ((mItem = psListGetAndIncrement(iter))) {
        detInputExpRow *inputExp = psHashLookup(valid_exp_ids,
                (char *)mItem->data.V);
        if (!inputExp) {
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            // invalid exp_id
            psError(PS_ERR_UNKNOWN, false, "exp_id %s is invalid for det_id %s",
                    (char *)mItem->data.V, det_id);
            psFree(iter);
            psFree(valid_exp_ids);
            return false;
        }
        detInputExpRow *newInputExp = detInputExpRowAlloc(
            (psS64)atoll(det_id),
            newIteration,
            inputExp->exp_id,
            true   // use
        );
        psArrayAdd(newInputExps, 0, newInputExp);
        psFree(newInputExp);
    }
    psFree(iter);
    psFree(valid_exp_ids);

    for (long i = 0; i < psArrayLength(newInputExps); i++) {
        if (!detInputExpInsertObject(config->dbh, newInputExps->data[i])) {
            psError(PS_ERR_UNKNOWN, false, "database error");
            // rollback
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psFree(newInputExps);
            return false;
        }
    }
    psFree(newInputExps);

    // point of no return for det_id creation
    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static bool register_detrendMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    PXOPT_LOOKUP_STR(det_type, config->args, "-det_type", true, false); // required
    PXOPT_LOOKUP_STR(filelevel, config->args, "-filelevel", true, false); // required
    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", false, false);
    PXOPT_LOOKUP_STR(camera, config->args, "-inst", false, false);
    PXOPT_LOOKUP_STR(telescope, config->args, "-telescope", false, false);
    PXOPT_LOOKUP_STR(exp_type, config->args, "-exp_type", false, false);
    PXOPT_LOOKUP_STR(filter, config->args, "-filter", false, false);
    PXOPT_LOOKUP_F32(airmass_min, config->args, "-airmass_min", false, false);
    PXOPT_LOOKUP_F32(airmass_max, config->args, "-airmass_max", false, false);
    PXOPT_LOOKUP_F32(exp_time_min, config->args, "-exp_time_min", false, false);
    PXOPT_LOOKUP_F32(exp_time_max, config->args, "-exp_time_max", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_min, config->args, "-ccd_temp_min", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_max, config->args, "-ccd_temp_max", false, false);
    PXOPT_LOOKUP_F64(posang_min, config->args, "-posang_min", false, false);
    PXOPT_LOOKUP_F64(posang_max, config->args, "-posang_max", false, false);
    PXOPT_LOOKUP_F64(solang_min, config->args, "-solang_min", false, false);
    PXOPT_LOOKUP_F64(solang_max, config->args, "-solang_max", false, false);
    PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);
    PXOPT_LOOKUP_TIME(time_begin, config->args, "-time_begin", false, false);
    PXOPT_LOOKUP_TIME(time_end, config->args, "-time_end", false, false);
    PXOPT_LOOKUP_TIME(use_begin, config->args, "-use_begin", false, false);
    PXOPT_LOOKUP_TIME(use_end, config->args, "-use_end", false, false);
    PXOPT_LOOKUP_STR(parent, config->args, "-parent", false, false);
    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);

    if (!psDBTransaction(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    if (!detRunInsert(config->dbh,
                      0,            // det_id
                      0,            // the iteration is fixed at 0
                      det_type,
                      "register",   // mode is required to be "register"
                      "register",   // state
                      filelevel,
                      workdir,
                      camera,
                      telescope,
                      exp_type,
                      NULL,
                      filter,
                      airmass_min,
                      airmass_max,
                      exp_time_min,
                      exp_time_max,
                      ccd_temp_min,
                      ccd_temp_max,
                      posang_min,
                      posang_max,
                      registered,
                      time_begin,
                      time_end,
                      use_begin,
                      use_end,
                      solang_min,
                      solang_max,
                      label,      // label
                      parent ? (psS64)atoll(parent) : 0
            )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        return false;
    }

    // print the new detRun
    psS64 det_id = psDBLastInsertID(config->dbh);

    if (!psDBCommit(config->dbh)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id);
        detRuns = psDBSelectRows(config->dbh, "detRun", where, 0);
        psFree(where);
    }
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "can't find the detRun we just created");
        return false;
    }
    // sanity check results
    if (psArrayLength(detRuns) != 1) {
        psAbort("found more then one detRun matching det_id %" PRId64 "(this should not happen)", det_id);
        return false;
    }

    // negative simple so the default is true
    if (!ippdbPrintMetadatas(stdout, detRuns, "detRun", !simple)) {
        psError(PS_ERR_UNKNOWN, false, "failed to print array");
        psFree(detRuns);
        return false;
    }
    psFree(detRuns);

    return true;
}

// NOTE : this function is also used by addcorrectimfileMode
bool register_detrend_imfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false); // required
    PXOPT_LOOKUP_STR(class_id, config->args, "-class_id", true, false); // required
    PXOPT_LOOKUP_STR(uri, config->args, "-uri", true, false); // required
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);

    if (!detRegisteredImfileInsert(config->dbh,
        (psS64)atoll(det_id),
        0,  // the iteration is fixed at 0
        class_id,
        uri,
        bg,
        bg_stdev,
        bg_mean_stdev,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        0       // fault code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        return false;
    }

    return true;
}

static psS32 incrementIteration(pxConfig *config, psS64 det_id)
{
    // this function returns zero on error
    PS_ASSERT_PTR_NON_NULL(config, 0);

    char *query = "UPDATE detRun SET iteration = iteration + 1 WHERE det_id = %" PRId64;
    if (!p_psDBRunQuery(config->dbh, query, det_id)) {
        psError(PS_ERR_UNKNOWN, false,
                "failed to increment iteration for det_id %" PRId64, det_id);
        return 0;
    }

    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
        psFree(where);
        return 0;
    }

    psArray *detRuns = detRunSelectRowObjects(config->dbh, where, 0);
    psFree(where);
    if (!detRuns) {
        psError(PS_ERR_UNKNOWN, false, "no detRun rows found");
        return 0;
    }

    // sanity check the database
    if (psArrayLength(detRuns) != 1) {
        // this should no happen
        psAbort(                "database query return too many rows (this should not happen");
    }

    psS32 newIteration = ((detRunRow *)detRuns->data[0])->iteration;
    psFree(detRuns);

    return newIteration;
}

static bool setDetRunState(pxConfig *config, psS64 det_id, const char *state)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    PS_ASSERT_PTR_NON_NULL(state, false);

    // check that state is a valid string value
    if (!(
            (strncmp(state, "run", 4) == 0)
            || (strncmp(state, "stop", 5) == 0)
            || (strncmp(state, "drop", 5) == 0)
            || (strncmp(state, "register", 4) == 0)
        )
    ) {
        psError(PS_ERR_UNKNOWN, false,
                "invalid detRun state: %s", state);
        return false;
    }

    char *query = "UPDATE detRun SET state = '%s' WHERE det_id = %" PRId64;
    if (!p_psDBRunQuery(config->dbh, query, state, det_id)) {
        psError(PS_ERR_UNKNOWN, false,
                "failed to change state for det_id %" PRId64, det_id);
        return false;
    }

    return true;
}

static bool isValidMode(pxConfig *config, const char *mode)
{
    PS_ASSERT_PTR_NON_NULL(config, false);
    PS_ASSERT_PTR_NON_NULL(mode, false);

    // check that state is a valid string value
    if (!(
            (strncmp(mode, "master", 7) == 0)
            || (strncmp(mode, "verify", 7) == 0)
        )
    ) {
        psError(PS_ERR_UNKNOWN, false,
                "invalid detRun mode: %s", mode);
        return false;
    }

    return true;
}


#if 0
// XXX this function was left in commented as this method may be useful in the
// future
static psArray *validDetInputClassIds(pxConfig *config, const char *det_id)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    // det_id is input as a string because the fact that it is an integer
    // is just a database impliementation detail.
    PS_ASSERT_PTR_NON_NULL(det_id, NULL);

    psArray *rawImfiles = searchInputImfiles(config, det_id);
    if (!rawImfiles) {
        return NULL;
    }

    psArray *valid_class_ids = NULL;
    {
        // All this jumping through hoops is so that we end up with unique
        // values. PP thinks that making multiple passes through this array and
        // deleting matched elements would end up being more expensive then a
        // double sort and stagger scheme. JH thinks it would be cheaper to
        // just do a unqiue sort and delete.  So this is really just a cheap
        // hack to avoid implimenting a unique sort function but at least it's
        // stable.
        psHash *hash = psHashAlloc(psArrayLength(rawImfiles));
        for (long i = 0; i < psArrayLength(rawImfiles); i++) {
            psHashAdd(hash,
                ((rawImfileRow *)rawImfiles->data[i])->class_id,
                ((rawImfileRow *)rawImfiles->data[i])->class_id
            );
        }
        valid_class_ids = psHashToArray(hash);
        psFree(hash);
    }
    psFree(rawImfiles);

    return valid_class_ids;
}

static psArray *searchInputImfiles(pxConfig *config, const char *det_id)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    // det_id is input as a string because the fact that it is an integer
    // is just a database impliementation detail.
    PS_ASSERT_PTR_NON_NULL(det_id, NULL);

    psArray *inputExps = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "det_id", 0, "==",
                (psS32)atoi(det_id))) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
            psFree(where);
            return NULL;
        }
        inputExps = detInputExpSelectRowObjects(config->dbh, where, 0);
        psFree(where);
    }
    if (!inputExps) {
        psError(PS_ERR_UNKNOWN, false, "no detInputExp rows found");
        return NULL;
    }

    // find rawImfiles associated with detInputExps
    psArray *rawImfiles = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        for (long i = 0; i < psArrayLength(inputExps); i++) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id",
                    PS_META_DUPLICATE_OK, "==",
                    ((detInputExpRow *)inputExps->data[i])->exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(inputExps);
                psFree(where);
                return NULL;
            }
        }
        psFree(inputExps);
        rawImfiles = rawImfileSelectRowObjects(config->dbh, where, 0);
        // XXX this really should be sorted for uniqueness
        psFree(where);
    }
    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "no rawImfile rows found");
        return NULL;
    }

    return rawImfiles;
}
#endif

