/*
 * 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.
 */

#ifdef HAVB_CONFIG_H
#include <config.h>
#endif

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdint.h>
#include <inttypes.h>

#include <ippdb.h>

#include "pxtools.h"
#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);
static bool toprocessedimfileMode(pxConfig *config);
static bool addprocessedimfileMode(pxConfig *config);
static bool toprocessedexpMode(pxConfig *config);
static bool addprocessedexpMode(pxConfig *config);
static bool processedexpMode(pxConfig *config);
static bool tostackedMode(pxConfig *config);
static bool processedimfileMode(pxConfig *config);
static bool addstackedMode(pxConfig *config);
static bool stackedMode(pxConfig *config);
static bool tonormalizeMode(pxConfig *config);
static bool addnormalizedstatMode(pxConfig *config);
static bool tonormalizedstatMode(pxConfig *config);
static bool addnormalizedimfileMode(pxConfig *config);
static bool tonormalizedexpMode(pxConfig *config);
static bool addnormalizedexpMode(pxConfig *config);
static bool normalizedexpMode(pxConfig *config);
static bool toresidimfileMode(pxConfig *config);
static bool addresidimfileMode(pxConfig *config);
static bool normalizedimfileMode(pxConfig *config);
static bool toresidexpMode(pxConfig *config);
static bool residimfileMode(pxConfig *config);
static bool addresidexpMode(pxConfig *config);
static bool residexpMode(pxConfig *config);
static bool residdetrunMode(pxConfig *config);
static bool updateresidexpMode(pxConfig *config);
static bool adddetrunsummaryMode(pxConfig *config);
static bool detrunsummaryMode(pxConfig *config);
static bool updatedetrunMode(pxConfig *config);
static bool rerunMode(pxConfig *config);
static bool register_detrendMode(pxConfig *config);
static bool register_detrend_imfileMode(pxConfig *config);

static detNormalizedStatImfileRow *detStackedToDetNormalizedStatImfile(pxConfig *config, detStackedImfileRow *stackedImfile);
static  detNormalizedImfileRow *detNormalizedStatToDetNormalizedmfile(pxConfig *config, detNormalizedStatImfileRow *statImfile);
static  detResidImfileRow *detNormalizedToDetResidImfile(pxConfig *config, detNormalizedImfileRow *normalizedImfile);
static detResidExpRow *mdToDetResidExp(pxConfig *config, psMetadata *row);
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, psS32 det_id, psS32 iteration);
static psArray *searchRawImfiles(pxConfig *config, psMetadata *where);
static psS32 incrementIteration(pxConfig *config, const char *det_id);
static bool setDetRunState(pxConfig *config, const char *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);
        MODECASE(DETTOOL_MODE_TOPROCESSEDIMFILE,toprocessedimfileMode);
        MODECASE(DETTOOL_MODE_ADDPROCESSEDIMFILE,addprocessedimfileMode);
        MODECASE(DETTOOL_MODE_PROCESSEDIMFILE,  processedimfileMode);
        MODECASE(DETTOOL_MODE_TOPROCESSEDEXP,   toprocessedexpMode);
        MODECASE(DETTOOL_MODE_ADDPROCESSEDEXP,  addprocessedexpMode);
        MODECASE(DETTOOL_MODE_PROCESSEDEXP,     processedexpMode);
        MODECASE(DETTOOL_MODE_TOSTACKED,        tostackedMode);
        MODECASE(DETTOOL_MODE_ADDSTACKED,       addstackedMode);
        MODECASE(DETTOOL_MODE_STACKED,          stackedMode);
        MODECASE(DETTOOL_MODE_TONORMALIZE,      tonormalizeMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDSTAT,addnormalizedstatMode);
        MODECASE(DETTOOL_MODE_TONORMALIZEDSTAT, tonormalizedstatMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDIMFILE,addnormalizedimfileMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDIMFILE, normalizedimfileMode);
        MODECASE(DETTOOL_MODE_TONORMALIZEDEXP,  tonormalizedexpMode);
        MODECASE(DETTOOL_MODE_ADDNORMALIZEDEXP, addnormalizedexpMode);
        MODECASE(DETTOOL_MODE_NORMALIZEDEXP,    normalizedexpMode);
        MODECASE(DETTOOL_MODE_TORESIDIMFILE,    toresidimfileMode);
        MODECASE(DETTOOL_MODE_ADDRESIDIMFILE,   addresidimfileMode);
        MODECASE(DETTOOL_MODE_TORESIDEXP,       toresidexpMode);
        MODECASE(DETTOOL_MODE_RESIDIMFILE,      residimfileMode);
        MODECASE(DETTOOL_MODE_ADDRESIDEXP,      addresidexpMode);
        MODECASE(DETTOOL_MODE_RESIDEXP,         residexpMode);
        MODECASE(DETTOOL_MODE_RESIDDETRUN,      residdetrunMode);
        MODECASE(DETTOOL_MODE_UPDATERESIDEXP,   updateresidexpMode);
        MODECASE(DETTOOL_MODE_ADDDETRUNSUMMARY, adddetrunsummaryMode);
        MODECASE(DETTOOL_MODE_DETRUNSUMMARY,    detrunsummaryMode);
        MODECASE(DETTOOL_MODE_UPDATEDETRUN,     updatedetrunMode);
        MODECASE(DETTOOL_MODE_RERUN,            rerunMode);
        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);

     psString query = psStringCopy(
        "SELECT"
        "   rawExp.*"
        " FROM rawExp"
        " LEFT JOIN detInputExp"
        "   ON rawExp.exp_tag = detInputExp.exp_tag"
        " WHERE"
        "    detInputExp.exp_tag IS NULL"
        "    AND rawExp.obstype != 'object'"
    );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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)
{
    bool status     = false;

    PS_ASSERT_PTR_NON_NULL(config, false);

    // what type of detRun is this?
    // det_type is required
    psString det_type = psMetadataLookupStr(&status, config->args, "-det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_type");
        return false;
    }
    if (!det_type) {
        psError(PS_ERR_UNKNOWN, true, "-det_type is required");
        return false;
    }

    // optional
    psString filelevel = psMetadataLookupStr(&status, config->args, "-filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filelevel");
        return false;
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -workdir");
        return false;
    }
    if (!workdir) {
        psError(PS_ERR_UNKNOWN, true, "-workdir is required");
        return false;
    }

    // optional
    psString mode = psMetadataLookupStr(&status, config->args, "-mode");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -mode");
        return false;
    }
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }
    psString camera = psMetadataLookupStr(&status, config->args, "-inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -inst");
        return false;
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -telescope");
        return false;
    }

    psString exp_type = psMetadataLookupStr(&status, config->args, "-exp_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_type");
        return false;
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter");
        return false;
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_min");
        return false;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_max");
        return false;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_min");
        return false;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_max");
        return false;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_min");
        return false;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_max");
        return false;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_min");
        return false;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_max");
        return false;
    }
    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_min");
        return false;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_max");
        return false;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
            if (!registered) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", registeredStr);
                return false;
            }
        } else {
            registered = NULL;
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
            if (!time_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_beginStr);
                return false;
            }
        } else {
            time_begin = NULL;
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
            if (!time_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_endStr);
                return false;
            }
        } else {
            time_end = NULL;
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
            if (!use_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_beginStr);
                return false;
            }
        } else {
            use_begin = NULL;
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
            if (!use_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_endStr);
                return false;
            }
        } else {
            use_end = NULL;
        }
    }

    psString label = psMetadataLookupStr(&status, config->args, "-label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -label");
        return false;
    }

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

    // make sure that -exp_tag 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_tag = mItem->data.V;
            // if exp_tag is NULL then it means that -exp_tag has not been
            // specified
            if (!exp_tag) {
                psError(PS_ERR_UNKNOWN, true,
                        "at least one -exp_tag is required");
                psFree(where);
                return false;
            }

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

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

    // check that the specified exp_tags 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_tag specified
    if (psListLength(item->data.list) != psArrayLength(detrendExps)) {
        psAbort(    "an -exp_tag 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,
            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
        );
    psFree(registered);
    psFree(time_begin);
    psFree(time_end);
    psFree(use_begin);
    psFree(use_end);
    long 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;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // print the new det_id
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS32(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 %ld(this should not happen)", det_id);
        return false;
    }

    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(detRuns);
        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 rational 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)
{
    bool status     = false;

    PS_ASSERT_PTR_NON_NULL(config, false);

    // what type of detRun is this?
    // det_type is required
    psString det_type = psMetadataLookupStr(&status, config->args, "-det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_type");
        return false;
    }
    if (!det_type) {
        psError(PS_ERR_UNKNOWN, true, "-det_type is required");
        return false;
    }

    // optional
    psString filelevel = psMetadataLookupStr(&status, config->args, "-filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filelevel");
        return false;
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -workdir");
        return false;
    }
    if (!workdir) {
        psError(PS_ERR_UNKNOWN, true, "-workdir is required");
        return false;
    }
    psString camera = psMetadataLookupStr(&status, config->args, "-inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -inst");
        return false;
    }
    if (!camera) {
        psError(PS_ERR_UNKNOWN, false, "-inst is required");
        return false;
    }

    psMetadata *where = psMetadataAlloc();
    {
        bool status = false;
        psString exp_type = psMetadataLookupStr(&status, config->args, "-select_exp_type");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_exp_type");
            return false;
        }
        if (exp_type) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_type", 0, "==", exp_type)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_type");
                psFree(where);
                return false;
            }
        }

        // map -inst -> camera
        psString camera = psMetadataLookupStr(&status, config->args, "-select_inst");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_inst");
            return false;
        }
        if (camera) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "camera", 0, "==", camera)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item inst");
                psFree(where);
                return false;
            }
        }

        psString telescope = psMetadataLookupStr(&status, config->args, "-select_telescope");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_telescope");
            return false;
        }
        if (telescope) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "telescope", 0, "==", telescope)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item telescope");
                psFree(where);
                return false;
            }
        }

        psString filter = psMetadataLookupStr(&status, config->args, "-select_filter");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_filter");
            return false;
        }
        if (filter) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "filter", 0, "==", filter)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item filter");
                psFree(where);
                return false;
            }
        }

        psString uri = psMetadataLookupStr(&status, config->args, "-select_uri");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_uri");
            return false;
        }
        if (uri) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "uri", 0, "==", uri)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item uri");
                psFree(where);
                return false;
            }
        }

        psString dateobs_begin = psMetadataLookupStr(&status, config->args, "-select_dateobs_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_dateobs_begin");
            psFree(where);
            return false;
        }
        if (dateobs_begin) {
            psTime *time = psTimeFromISO(dateobs_begin, PS_TIME_UTC);
            if (!time) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", dateobs_begin);
                psFree(time);
                psFree(where);
                return false;
            }
            if (!psMetadataAddTime(where, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, ">=", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(where);
                return false;
            }
            psFree(time);
        }

        psString dateobs_end = psMetadataLookupStr(&status, config->args, "-select_dateobs_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_dateobs_end");
            psFree(where);
            return false;
        }
        if (dateobs_end) {
            psTime *time = psTimeFromISO(dateobs_end, PS_TIME_UTC);
            if (!time) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", dateobs_end);
                psFree(time);
                psFree(where);
                return false;
            }
            if (!psMetadataAddTime(where, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, "<", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(where);
                return false;
            }
            psFree(time);
        }

        /** selection based on airmass range **/
        psF32 select_airmass_min = psMetadataLookupF32(&status, config->args, "-select_airmass_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_airmass_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_airmass_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "airmass", PS_META_DUPLICATE_OK, ">=", select_airmass_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item airmass");
                psFree(where);
                return false;
            }
        }
        psF32 select_airmass_max = psMetadataLookupF32(&status, config->args, "-select_airmass_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_airmass_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_airmass_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "airmass", PS_META_DUPLICATE_OK, "<=", select_airmass_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item airmass");
                psFree(where);
                return false;
            }
        }

        psF32 select_sat_pixel_frac_max = psMetadataLookupF32(&status, config->args, "-select_sat_pixel_frac_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_sat_pixel_frac_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_sat_pixel_frac_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "sat_pixel_frac", PS_META_DUPLICATE_OK, "<=", select_sat_pixel_frac_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item sat_pixel_frac");
                psFree(where);
                return false;
            }
        }

        /** selection based on exp_time range **/
        psF32 select_exp_time_min = psMetadataLookupF32(&status, config->args, "-select_exp_time_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_exp_time_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_exp_time_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "exp_time", PS_META_DUPLICATE_OK, ">=", select_exp_time_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_time");
                psFree(where);
                return false;
            }
        }
        psF32 select_exp_time_max = psMetadataLookupF32(&status, config->args, "-select_exp_time_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_exp_time_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_exp_time_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "exp_time", PS_META_DUPLICATE_OK, "<=", select_exp_time_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_time");
                psFree(where);
                return false;
            }
        }

        /** selection based on ccd_temp range **/
        psF32 select_ccd_temp_min = psMetadataLookupF32(&status, config->args, "-select_ccd_temp_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_ccd_temp_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_ccd_temp_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "ccd_temp", PS_META_DUPLICATE_OK, ">=", select_ccd_temp_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item ccd_temp");
                psFree(where);
                return false;
            }
        }
        psF32 select_ccd_temp_max = psMetadataLookupF32(&status, config->args, "-select_ccd_temp_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_ccd_temp_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_ccd_temp_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "ccd_temp", PS_META_DUPLICATE_OK, "<=", select_ccd_temp_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item ccd_temp");
                psFree(where);
                return false;
            }
        }

        /** selection based on posang **/
        psF32 select_posang_min = psMetadataLookupF32(&status, config->args, "-select_posang_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_posang_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_posang_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "posang", PS_META_DUPLICATE_OK, ">=", select_posang_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item posang");
                psFree(where);
                return false;
            }
        }
        psF32 select_posang_max = psMetadataLookupF32(&status, config->args, "-select_posang_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_posang_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_posang_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "posang", PS_META_DUPLICATE_OK, "<=", select_posang_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item posang");
                psFree(where);
                return false;
            }
        }

        /** selection based on solang **/
        psF32 select_solang_min = psMetadataLookupF32(&status, config->args, "-select_solang_min");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_solang_min");
            psFree(where);
            return false;
        }
        if (isfinite(select_solang_min)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "solang", PS_META_DUPLICATE_OK, ">=", select_solang_min)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item solang");
                psFree(where);
                return false;
            }
        }
        psF32 select_solang_max = psMetadataLookupF32(&status, config->args, "-select_solang_max");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -select_solang_max");
            psFree(where);
            return false;
        }
        if (isfinite(select_solang_max)) {
            if (!psMetadataAddF32(where, PS_LIST_TAIL, "solang", PS_META_DUPLICATE_OK, "<=", select_solang_max)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item solang");
                psFree(where);
                return false;
            }
        }

    }

    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 seperated them but prepending set- to
    // the assigned values

    // optional
    psString mode = psMetadataLookupStr(&status, config->args, "-mode");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -mode");
        return false;
    }
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -telescope");
        return false;
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter");
        return false;
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_min");
        return false;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_max");
        return false;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_min");
        return false;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_max");
        return false;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_min");
        return false;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_max");
        return false;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_min");
        return false;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_max");
        return false;
    }

    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_min");
        return false;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_max");
        return false;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
            if (!registered) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", registeredStr);
                return false;
            }
        } else {
            registered = NULL;
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
            if (!time_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_beginStr);
                return false;
            }
        } else {
            time_begin = NULL;
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
            if (!time_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", time_endStr);
                return false;
            }
        } else {
            time_end = NULL;
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
            if (!use_begin) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_beginStr);
                return false;
            }
        } else {
            use_begin = NULL;
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
            if (!use_end) {
                psError(PS_ERR_UNKNOWN, false, "error in time format %s", use_endStr);
                return false;
            }
        } else {
            use_end = NULL;
        }
    }

    psString label = psMetadataLookupStr(&status, config->args, "-label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -label");
        return false;
    }

    bool simple = psMetadataLookupBool(&status, config->args, "-simple");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return false;
    }

    bool pretend = psMetadataLookupBool(&status, config->args, "-pretend");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -pretend");
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return false;
    }

    // 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");
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return false;
    }
    if (!psArrayLength(detrendExps)) {
        psTrace("dettool", PS_LOG_INFO, "no rows found");
        psFree(detrendExps);
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        return true;
    }

    // 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);
            psFree(registered);
            psFree(use_begin);
            psFree(use_end);
            return false;
        }
        psFree(detrendExps);
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        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);
        psFree(registered);
        psFree(use_begin);
        psFree(use_end);
        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",
            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
        );
    psFree(registered);
    psFree(time_begin);
    psFree(time_end);
    psFree(use_end);
    psFree(use_begin);
    psFree(use_end);
    long 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();
        psMetadataAddS32(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 %ld(this should not happen)", det_id);
        return false;
    }

    // convert det_id to a string externaly
    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert det_id to a string");
        psFree(detRuns);
        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)
{
    bool status     = false;

    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id is the only required input
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        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
    psString det_type = psMetadataLookupStr(&status, config->args, "-set_det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_det_type");
        return false;
    }
    if (det_type) {
        psFree(detRun->det_type);
        detRun->det_type = psStringCopy(det_type);
    }

    psString mode = psMetadataLookupStr(&status, config->args, "-set_mode");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_mode");
        return false;
    }
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }
    if (mode) {
        psFree(detRun->mode);
        detRun->mode = psStringCopy(mode);
    }

    psString camera = psMetadataLookupStr(&status, config->args, "-set_inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_inst");
        return false;
    }
    if (camera) {
        psFree(detRun->camera);
        detRun->camera = psStringCopy(camera);
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-set_telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_telescope");
        return false;
    }
    if (telescope) {
        psFree(detRun->telescope);
        detRun->telescope = psStringCopy(telescope);
    }

    psString exp_type = psMetadataLookupStr(&status, config->args, "-set_exp_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_exp_type");
        return false;
    }
    if (exp_type) {
        psFree(detRun->exp_type);
        detRun->exp_type = psStringCopy(exp_type);
    }

    psString filelevel = psMetadataLookupStr(&status, config->args, "-set_filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_filelevel");
        return false;
    }
    if (filelevel) {
        psFree(detRun->filelevel);
        detRun->filelevel = psStringCopy(filelevel);
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-set_workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_workdir");
        return false;
    }
    if (workdir) {
        psFree(detRun->workdir);
        detRun->workdir = psStringCopy(workdir);
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-set_filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_filter");
        return false;
    }
    if (filter) {
        psFree(detRun->filter);
        detRun->filter = psStringCopy(filter);
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-set_airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_airmass_min");
        return false;
    }
    if (!isnan(airmass_min)) {
        detRun->airmass_min = airmass_min;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-set_airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_airmass_max");
        return false;
    }
    if (!isnan(airmass_max)) {
        detRun->airmass_max = airmass_max;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-set_exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_exp_time_min");
        return false;
    }
    if (!isnan(exp_time_min)) {
        detRun->exp_time_min = exp_time_min;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-set_exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_exp_time_max");
        return false;
    }
    if (!isnan(exp_time_max)) {
        detRun->exp_time_max = exp_time_max;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-set_ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_ccd_temp_min");
        return false;
    }
    if (!isnan(ccd_temp_min)) {
        detRun->ccd_temp_min = ccd_temp_min;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-set_ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_ccd_temp_max");
        return false;
    }
    if (!isnan(ccd_temp_max)) {
        detRun->ccd_temp_max = ccd_temp_max;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-set_posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_posang_min");
        return false;
    }
    if (!isnan(posang_min)) {
        detRun->posang_min = posang_min;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-set_posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_posang_max");
        return false;
    }
    if (!isnan(posang_max)) {
        detRun->posang_max = posang_max;
    }

    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-set_solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_solang_min");
        return false;
    }
    if (!isnan(solang_min)) {
        detRun->solang_min = solang_min;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-set_solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_solang_max");
        return false;
    }
    if (!isnan(solang_max)) {
        detRun->solang_max = solang_max;
    }

    psString label = psMetadataLookupStr(&status, config->args, "-set_label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_label");
        return false;
    }
    if (label) {
        detRun->label = label;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-set_registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            psFree(detRun->registered);
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
            detRun->registered = psMemIncrRefCounter(registered);
            psFree(registered);
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-set_time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            psFree(detRun->time_begin);
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
            detRun->time_begin = psMemIncrRefCounter(time_begin);
            psFree(time_begin);
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-set_time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            psFree(detRun->time_end);
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
            detRun->time_end = psMemIncrRefCounter(time_end);
            psFree(time_end);
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-set_use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            psFree(detRun->use_begin);
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
            detRun->use_begin = psMemIncrRefCounter(use_begin);
            psFree(use_begin);
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-set_use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -set_use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            psFree(detRun->use_end);
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
            detRun->use_end = psMemIncrRefCounter(use_end);
            psFree(use_end);
        }
    }

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

    psMetadata *time_filter = psMetadataAlloc();

    {
        psString timeStr = psMetadataLookupStr(&status, config->args, "-filter_input_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter_input_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (timeStr) {
            psTime *time = psTimeFromISO(timeStr, PS_TIME_UTC);
            if (!psMetadataAddTime(time_filter, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, ">=", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(time_filter);
                return false;
            }
            psFree(time);
        }
    }

    {
        psString timeStr = psMetadataLookupStr(&status, config->args, "-filter_input_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter_input_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (timeStr) {
            psTime *time = psTimeFromISO(timeStr, PS_TIME_UTC);
            if (!psMetadataAddTime(time_filter, PS_LIST_TAIL, "dateobs", PS_META_DUPLICATE_OK, "<", time)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item dateobs");
                psFree(time);
                psFree(time_filter);
                return false;
            }
            psFree(time);
        }
    }


    // 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
    long newDet_id = psDBLastInsertID(config->dbh);

    psString query = psStringCopy(
        "INSERT INTO detInputExp"
        "   SELECT"
        "       %d,"
        "       0,"
        "       detResidExp.exp_tag,"
        "       detResidExp.accept"
        "   FROM detResidExp"
        "   JOIN rawExp"
        "       USING(exp_tag)"
        "   WHERE det_id = %d"
    );

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // print the new det_id
    psArray *newDetRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS32(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 %ld(this should not happen)", newDet_id);
        return false;                   // unreachable
    }

    // convert det_id to a string externaly
    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(detRuns);
        return false;
    }

    // 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);

    // XXX fix the hard coding of the table name
    psArray *runs = psDBSelectRows(config->dbh, "detRun", config->where, 0);
    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;
    }

    // convert det_id to a string externaly
    if (!convertIdToStr(runs)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(runs);
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = psStringCopy(
            "SELECT DISTINCT\n"
            "   detRun.*\n"
            " FROM detRun\n"
            " LEFT JOIN detRun as foo\n"
            "   ON foo.parent = detRun.det_id\n"
            " WHERE\n"
            "   detRun.state = 'stop'\n"
            "   AND detRun.mode = 'master'\n"
            "   AND foo.det_id IS NULL\n"
        );

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

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

    // convert det_id to a string externaly
    if (!convertIdToStr(output)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(output);
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // 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, psS32 det_id, psS32 iteration)
{
    PS_ASSERT_PTR_NON_NULL(rawExp, NULL);

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

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

    // select detInputExp.*
    // select rawExp.*
    // by:
    // exp_tag

    psString query = psStringCopy(
        "SELECT DISTINCT *"
        " FROM detInputExp"
        " JOIN rawExp"
        " USING(exp_tag)"
        );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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);

    psString query = psStringCopy(
            "SELECT"
            "   detInputExp.det_id,"
            "   detRun.det_type,"
            "   rawImfile.*,"
            "   rawExp.camera"
            " FROM rawImfile"
            " JOIN detInputExp"
            "   USING(exp_tag) "
            " JOIN rawExp"
            "   USING(exp_tag) "
            " JOIN detRun"
            "   ON detInputExp.det_id = detRun.det_id"
            " WHERE"
            "   detRun.state = 'run'"
            "   AND detRun.mode = 'master'"
        );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    // select det_id, det_type & rawImfile *
    // get det_id from detInputExp.det_id
    // get det_type from detRun.det_id
    //
    // det_id is in detInputExp
    // exp_tag is in detInputExp
    // det_id is not in detProccessedImfile
    // exp_tag is not in detProccessedImfile
    // class_is is not in detProccessedImfile
    psString query = psStringCopy(
            "SELECT"
            "   detRun.det_id,"
            "   detRun.det_type,"
            "   detRun.workdir,"
            "   rawImfile.*,"
            "   rawExp.camera"
            " FROM detRun"
            " JOIN detInputExp"
            "    USING(det_id, iteration)"
            " JOIN rawExp"
            "    ON detInputExp.exp_tag = rawExp.exp_tag"
            " JOIN rawImfile"
            "    ON detInputExp.exp_tag = rawImfile.exp_tag"
            " LEFT JOIN detProcessedImfile"
            "   ON detInputExp.det_id = detProcessedImfile.det_id"
            "   AND rawImfile.exp_tag = detProcessedImfile.exp_tag"
            "   AND rawImfile.class_id = detProcessedImfile.class_id"
            " WHERE"
            "   detRun.state = 'run'"
            "   AND detRun.mode = 'master'"
            "   AND detProcessedImfile.det_id IS NULL"
            "   AND detProcessedImfile.exp_tag IS NULL"
            "   AND detProcessedImfile.class_id IS NULL"
        );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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 psArray *searchRawImfiles(pxConfig *config, psMetadata *where)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);

    // use the default where if "where" is NULL
    if (!where) {
        where = config->where;
    }

    // select exp_tags from detInputExp matching det_idp
    // where query should be pre-generated
    psArray *detInputExp =
        detInputExpSelectRowObjects(config->dbh, where, 0);
    if (!detInputExp) {
        psError(PS_ERR_UNKNOWN, false, "no rawExp rows found");
        return NULL;
    }

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

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

    return rawImfiles;
}

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

    // det_id, exp_tag, class_id, uri, recipe, -bg, -bg_stdev
    // are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString exp_tag = psMetadataLookupStr(&status, config->args, "-exp_tag");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_tag");
        return false;
    }
    if (!exp_tag) {
        psError(PS_ERR_UNKNOWN, true, "-exp_tag is required");
        return false;
    }
    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        return false;
    }
    if (!class_id) {
        psError(PS_ERR_UNKNOWN, true, "-class_id is required");
        return false;
    }
    psString uri    = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // find the matching rawImfile by exp_tag/class_id
    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_tag", 0, "==", exp_tag)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item exp_tag");
        psFree(where);
        return false;
    }
    if (!psMetadataAddStr(where, PS_LIST_TAIL, "class_id", 0, "==", class_id)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item class_id");
        psFree(where);
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    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(
        (psS32)atol(det_id),
        exp_tag,
        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 toprocessedexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    // select detProcessedImfile.det_id
    // select detRun.iteration
    // select detRun.det_type
    // select detProcessedImfile.exp_tag
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // find all rawImfiles in the current exp_tags
    // compare to detProcessedImfiles by det_id/exp_tag
    // found how many imfile there are in each class_id
    // and:
    // det_id is not in detProcessedExp;
    // iteration is not in detProcessedExp;

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "    det_id,"
        "    iteration,"
        "    det_type,"
        "    exp_tag,"
        "    camera,"
        "    workdir"
        " FROM"
        " (SELECT DISTINCT"
        "    detRun.det_id,"
        "    detRun.iteration,"
        "    detRun.det_type,"
        "    detRun.workdir,"
        "    detProcessedImfile.exp_tag,"
        "    rawExp.camera,"
        "    detProcessedImfile.class_id,"
        "    rawExp.imfiles"
        " FROM detRun"
        " JOIN detInputExp"
        "   USING(det_id, iteration)"
        " JOIN rawExp"
        "   ON detInputExp.exp_tag = rawExp.exp_tag"
        " JOIN rawImfile"
        "   ON rawExp.exp_tag = rawImfile.exp_tag"
        " LEFT JOIN detProcessedImfile"
        "   ON detRun.det_id = detProcessedImfile.det_id"
        "   AND detInputExp.exp_tag = detProcessedImfile.exp_tag"
        "   AND rawImfile.class_id = detProcessedImfile.class_id"
        " LEFT JOIN detProcessedExp"
        "   ON detProcessedImfile.det_id = detProcessedExp.det_id"
        "   AND detProcessedImfile.exp_tag = detProcessedExp.exp_tag"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
        "   AND detProcessedImfile.fault = 0"
        "   AND detProcessedExp.det_id IS NULL"
        "   AND detProcessedExp.exp_tag IS NULL"
        "   AND detInputExp.include = 1"
        " GROUP BY"
        "    rawExp.exp_tag,"
        "    detRun.det_id"
        " HAVING"
        "    COUNT(detProcessedImfile.class_id) = rawExp.imfiles"
        " ) AS detProcessedExp"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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_tag, recip, -bg, -bg_stdev
    // are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString exp_tag = psMetadataLookupStr(&status, config->args, "-exp_tag");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_tag");
        return false;
    }
    if (!exp_tag) {
        psError(PS_ERR_UNKNOWN, true, "-exp_tag is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "    detProcessedImfile.det_id,"
        "    detRun.iteration,"
        "    detRun.det_type,"
        "    detProcessedImfile.exp_tag"
        " FROM detRun"
        " JOIN detInputExp"
        "    ON detRun.det_id = detInputExp.det_id"
        "    AND detRun.iteration = detInputExp.iteration"
        " JOIN rawExp"
        "    ON detInputExp.exp_tag = rawExp.exp_tag"
        " JOIN detProcessedImfile"
        "    ON detInputExp.det_id = detProcessedImfile.det_id"
        "    AND detInputExp.exp_tag = detProcessedImfile.exp_tag"
        " LEFT JOIN detProcessedExp"
        "    ON detInputExp.det_id = detProcessedExp.det_id"
        "    AND detProcessedImfile.exp_tag= detProcessedExp.exp_tag"
        " LEFT JOIN rawImfile"
        "    ON detInputExp.exp_tag = rawImfile.exp_tag"
        "    AND detProcessedImfile.class_id = rawImfile.class_id"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
        "   AND detProcessedExp.det_id IS NULL"
        "   AND detProcessedExp.exp_tag IS NULL"
        "   AND detInputExp.include = 1"
        "   AND detRun.det_id = %s"
        "   AND detProcessedImfile.exp_tag = '%s'"
        " GROUP BY"
        "    detProcessedImfile.class_id,"
        "    rawImfile.class_id,"
        "    detRun.det_id"
        " HAVING"
        "    COUNT(detProcessedImfile.class_id) = COUNT(rawImfile.class_id)"
        );

    if (!p_psDBRunQuery(config->dbh, query, det_id, exp_tag)) {
        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(
        (psS32)atol(det_id),
        exp_tag,
        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);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

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

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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 tostackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    // select detRun.iteration
    // select detProcessedImfile.det_id
    // select detProcessedImfile.class_id
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // find all rawImfiles in the current exp_tags
    // compare to detProcessedImfiles by det_id/exp_tag
    // found how many imfile there are in each class_id
    // and:
    // det_id is not in detStackedImfile;
    // iteration is not in detStackedImfile;
    // class_id is not in detStackedImfile;

    psString query = psStringCopy(
        " SELECT\n"
        "    detProcessedImfile.det_id,\n"
        "    detRun.iteration,\n"
        "    detRun.det_type,\n"
        "    detRun.workdir,\n"
        "    detProcessedImfile.class_id,\n"
        "    rawExp.camera\n"
        " FROM detRun\n"
        " JOIN detInputExp\n"
        "    ON detRun.det_id = detInputExp.det_id\n"
        "    AND detRun.iteration = detInputExp.iteration\n"
        " JOIN rawExp\n"
        "    ON detInputExp.exp_tag = rawExp.exp_tag\n"
        " JOIN rawImfile\n"
        "    ON detInputExp.exp_tag = rawImfile.exp_tag\n"
        " LEFT JOIN detProcessedImfile\n"
        "    ON detInputExp.det_id = detProcessedImfile.det_id\n"
        "    AND detInputExp.exp_tag = detProcessedImfile.exp_tag\n"
        "    AND rawImfile.class_id = detProcessedImfile.class_id\n"
        " LEFT JOIN detStackedImfile\n"
        "    ON detInputExp.det_id = detStackedImfile.det_id\n"
        "    AND detInputExp.iteration = detStackedImfile.iteration\n"
        "    AND rawImfile.class_id = detStackedImfile.class_id\n"
        " WHERE\n"
        "   detRun.state = 'run'\n"
        "   AND detRun.mode = 'master'\n"
        "   AND (detProcessedImfile.fault = 0 or detProcessedImfile.fault IS NULL)\n"
        "   AND detStackedImfile.det_id IS NULL\n"
        "   AND detStackedImfile.iteration IS NULL\n"
        "   AND detStackedImfile.class_id IS NULL\n"
        "   AND detInputExp.include = 1\n"
        " GROUP BY\n"
        "    rawImfile.class_id,\n"
        "    detRun.det_id\n"
        " HAVING\n"
        "    COUNT(detProcessedImfile.class_id) = COUNT(rawImfile.class_id)\n"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

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

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

    char *value = NULL;
    bool status = false;

    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "   detRun.det_type,"
        "   detProcessedImfile.*"
        " FROM detProcessedImfile"
        " JOIN detRun"
        "   USING(det_id)"
        " JOIN detInputExp"
        "   ON detRun.det_id = detInputExp.det_id"
        "   AND detRun.iteration = detInputExp.iteration"
        "   AND detProcessedImfile.exp_tag = detInputExp.exp_tag"
        " WHERE"
    );
    // NOTE the above WHERE is completed with the following line:

    // add the two required restrictions: detRun.state and detRun.mode
    if ((value = psMetadataLookupStr(&status, config->args, "-select_state"))) {
	psStringAppend(&query, " detRun.state = '%s'", value);
    } else {
	psStringAppend(&query, " 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 (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detProcessedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

    {
        bool status = false;
        bool included = psMetadataLookupBool(&status, config->args, "-included");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
        // 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;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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 addstackedMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id, class_id, uri, & recipe are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        return false;
    }
    if (!class_id) {
        psError(PS_ERR_UNKNOWN, true, "-class_id is required");
        return false;
    }
    psString uri    = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    // optional values
    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // default values
    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
        return false;
    }

    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // correlate the class_id against the input exposure(s)

    // we have to generate our own where clause as we want to search only by the
    // det_id
    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "det_id", 0, "==",
            (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item exp_tag");
        psFree(where);
        return false;
    }

    psArray *rawImfiles = searchRawImfiles(config, where);
    psFree(where);

    bool valid_class_id = false;
    if (rawImfiles) {
        for (long i = 0; i < psArrayLength(rawImfiles); i++) {
            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(
            (psS32)atol(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);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    // select detStackedImfile.*
    // by:
    // where det_id, iteration, class_id is not in detNormalizedImfile

    psString query = psStringCopy(
        "SELECT"
        "   detStackedImfile.*"
        " FROM detStackedImfile"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
    );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // 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 tonormalizedstatMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    // select detRun.det_id (det_id)
    // select detRun.iteration
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // sort to detInputExp.imfiles to find the largest value per det_id/iter
    // compare imfiles to the number of detStackedImfiles by class_id
    // and:
    // ???
    // det_id is not in detStackedImfile;
    // iteration is not in detStackedImfile;
    // class_id is not in detStackedImfile;

    psString query = psStringCopy(
        "SELECT"
        "   det_id,"
        "   det_type,"
        "   iteration,"
        "   camera,"
        "   workdir"
        " FROM"
        "(SELECT DISTINCT"
        "   detRun.det_id,"
        "   detRun.det_type,"
        "   detRun.iteration,"
        "   detRun.workdir,"
        "   rawExp.camera,"
        "   rawExp.imfiles,"
        "   detStackedImfile.class_id"
        " FROM detRun"
        " JOIN detInputExp"
        "   ON detRun.det_id = detInputExp.det_id"
        "   AND detRun.iteration = detInputExp.iteration"
        " JOIN rawExp"
        "   ON detInputExp.exp_tag = rawExp.exp_tag"
        " JOIN detStackedImfile"
        "   ON detInputExp.det_id = detStackedImfile.det_id"
        "   AND detInputExp.iteration = detStackedImfile.iteration"
        " LEFT JOIN detNormalizedStatImfile"
        "   ON detStackedImfile.det_id = detNormalizedStatImfile.det_id"
        "   AND detStackedImfile.iteration = detNormalizedStatImfile.iteration"
        "   AND detStackedImfile.class_id = detNormalizedStatImfile.class_id"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
        "   AND detStackedImfile.fault = 0"
        "   AND detNormalizedStatImfile.det_id IS NULL"
        "   AND detNormalizedStatImfile.iteration IS NULL"
        "   AND detNormalizedStatImfile.class_id IS NULL"
        " GROUP BY"
        "   rawExp.exp_tag,"
        "   detRun.iteration,"
        "   detRun.det_id"
        " HAVING MAX(rawExp.imfiles) = COUNT(detStackedImfile.class_id)"
        ") as tonormalizedstat"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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);

    // select * from detStackedImfile
    // by det_id, iteration, class_id
    // where det_id, iteration, class_id is not in detNormalizedStatImfile
    psString query = psStringCopy(
        "SELECT DISTINCT"
        "   detStackedImfile.*"
        " FROM detStackedImfile"
        " LEFT JOIN detNormalizedStatImfile"
        "   USING(det_id, iteration, class_id)"
        " WHERE"
        "  detNormalizedStatImfile.det_id IS NULL"
        "  AND detNormalizedStatImfile.iteration IS NULL"
        "  AND detNormalizedStatImfile.class_id IS NULL"
        );

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

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

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detStackedImfile object
        detStackedImfileRow *stackedImfile = detStackedImfileObjectFromMetadata(row);
        // convert detStackedImfile object into a detNormalizedStat object
        detNormalizedStatImfileRow *stat = detStackedToDetNormalizedStatImfile(config, stackedImfile);
        psFree(stackedImfile);
        if (!stat) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert detStackedImfile to detNormalizedStatImfile");
            psFree(output);
            return false;
        }
        // insert detNormlized Stat object into the database
        if (!detNormalizedStatImfileInsertObject(config->dbh, stat)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(stat);
            psFree(output);
        }
        psFree(stat);
    }

    psFree(output);

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

    return true;
}

static detNormalizedStatImfileRow *detStackedToDetNormalizedStatImfile(pxConfig *config, detStackedImfileRow *stackedImfile)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    PS_ASSERT_PTR_NON_NULL(stackedImfile, NULL);

    bool status = false;
    psF32 norm = psMetadataLookupF32(&status, config->args, "-norm");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -norm");
        return false;
    }
    //if (isnan(norm)) {
    //  psError(PS_ERR_UNKNOWN, true, "-norm is required");
    //  return false;
    //}

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    return detNormalizedStatImfileRowAlloc(
        stackedImfile->det_id,
        stackedImfile->iteration,
        stackedImfile->class_id,
        norm,
        code
    );
}

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

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    // select detNormalizedStatImfile.*
    // by:
    // where det_id, iteration, class_id is not in detNormalizedImfile

    psString query = psStringCopy(
        "SELECT DISTINCT"
        "   detRun.det_type,"
        "   detRun.workdir,"
        "   rawExp.camera,"
        "   detStackedImfile.uri,"
        "   detNormalizedStatImfile.*"
        " FROM detRun"
        " JOIN detStackedImfile"
        "   USING(det_id, iteration)"
        " JOIN detInputExp"
        "   USING(det_id, iteration)"
        " JOIN rawExp"
        "   ON detInputExp.exp_tag = rawExp.exp_tag"
        " JOIN detNormalizedStatImfile"
        "   ON detStackedImfile.det_id = detNormalizedStatImfile.det_id"
        "   AND detStackedImfile.iteration = detNormalizedStatImfile.iteration"
        "   AND detStackedImfile.class_id = detNormalizedStatImfile.class_id"
        " LEFT JOIN detNormalizedImfile"
        "   ON detNormalizedStatImfile.det_id = detNormalizedImfile.det_id"
        "   AND detNormalizedStatImfile.iteration = detNormalizedImfile.iteration"
        "   AND detNormalizedStatImfile.class_id = detNormalizedImfile.class_id"
        " WHERE"
        "   detNormalizedImfile.det_id IS NULL"
        "   AND detNormalizedImfile.iteration IS NULL"
        "   AND detNormalizedImfile.class_id IS NULL"
        "   AND detNormalizedStatImfile.fault = 0"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

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

#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_tag",
                    PS_META_DUPLICATE_OK, "==",
                    ((detInputExpRow *)inputExps->data[i])->exp_tag)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_tag");
                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

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

    // make sure that there is a respondoing entry in detNormalizedStatImfile
    // select * from detNormalizedStatImfile
    // by det_id, iteration, class_id
    // where det_id, iteration, class_id is not in detNormalizedImfile
    psString query = psStringCopy(
        "SELECT"
        "   detNormalizedStatImfile.*"
        " FROM detNormalizedStatImfile"
        " LEFT JOIN detNormalizedImfile"
        "   USING(det_id, iteration, class_id)"
        " WHERE"
        "  detNormalizedImfile.det_id IS NULL"
        "  AND detNormalizedImfile.iteration IS NULL"
        "  AND detNormalizedImfile.class_id IS NULL"
        );

    {
        // build a query to search by det_id, iteration, class_id
        psMetadata *where = psMetadataAlloc();
        bool status = false;
        psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
            psFree(where);
            psFree(query);
            return false;
        }
        if (det_id) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
                psFree(where);
                psFree(query);
                return false;
            }
        }
        psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        // always set iteration
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
            psFree(where);
            psFree(query);
            return false;
        }
        if (class_id) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "class_id", 0, "==", class_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item class_id");
                psFree(where);
                psFree(query);
                return false;
            }
        }

        // there's not
        psString whereClause = psDBGenerateWhereConditionSQL(where, "detNormalizedStatImfile");
        psFree(where);
        if (whereClause) {
            psStringAppend(&query, " AND %s", whereClause);
            psFree(whereClause);
        }
    }

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

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detNormalizedStatImfile object
        detNormalizedStatImfileRow *statImfile = detNormalizedStatImfileObjectFromMetadata(row);
        // convert detNormalizedStatImfile object into a detNormalizedImfile
        detNormalizedImfileRow *normalizedImfile  = detNormalizedStatToDetNormalizedmfile(config, statImfile);
        psFree(statImfile);
        if (!normalizedImfile) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert detStackedImfile to detNormalizedStatImfile");
            psFree(output);
            return false;
        }
        // insert detNormlized Stat object into the database
        if (!detNormalizedImfileInsertObject(config->dbh, normalizedImfile)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(normalizedImfile);
            psFree(output);
        }
        psFree(normalizedImfile);
    }

    psFree(output);

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

    return true;
}

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

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "    detRun.det_id as det_id,"
        "    detRun.iteration,"
        "    detRun.det_type,"
        "    detRun.workdir,"
        "    rawExp.camera,"
        "    rawExp.telescope,"
        "    rawExp.exp_type,"
        "    rawExp.imfiles"
        " FROM detRun"
        " JOIN detInputExp"
        "    ON detRun.det_id = detInputExp.det_id"
        "    AND detRun.iteration = detInputExp.iteration"
        " JOIN rawExp"
        "    ON detInputExp.exp_tag = rawExp.exp_tag"
        " JOIN detNormalizedImfile"
        "    ON detInputExp.det_id = detNormalizedImfile.det_id"
        "    AND detInputExp.iteration = detNormalizedImfile.iteration"
        " LEFT JOIN rawImfile"
        "    ON detInputExp.exp_tag = rawImfile.exp_tag"
        "    AND detNormalizedImfile.class_id = rawImfile.class_id"
        " LEFT JOIN detNormalizedExp"
        "    ON detInputExp.det_id = detNormalizedExp.det_id"
        "    AND detInputExp.iteration = detNormalizedExp.iteration"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
        "   AND detNormalizedImfile.fault = 0"
        "   AND detNormalizedExp.det_id IS NULL"
        "   AND detNormalizedExp.iteration IS NULL"
        "   AND detInputExp.include = 1"
        " GROUP BY"
        "    detNormalizedImfile.iteration,"
        "    detRun.det_id"
        " HAVING"
        "    COUNT(detNormalizedImfile.class_id) = COUNT(rawImfile.class_id)"
    );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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);

    // det_id, recip, -bg, -bg_stdev
    // are required
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    // optional
    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // iteration has a default value
    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");        return false;
    }

    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // optional
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "    detRun.det_id as det_id,"
        "    detRun.iteration,"
        "    detRun.det_type,"
        "    rawExp.camera,"
        "    rawExp.telescope,"
        "    rawExp.exp_type,"
        "    rawExp.imfiles"
        " FROM detRun"
        " JOIN detInputExp"
        "    ON detRun.det_id = detInputExp.det_id"
        "    AND detRun.iteration = detInputExp.iteration"
        " JOIN rawExp"
        "    ON detInputExp.exp_tag = rawExp.exp_tag"
        " JOIN detNormalizedImfile"
        "    ON detInputExp.det_id = detNormalizedImfile.det_id"
        "    AND detInputExp.iteration = detNormalizedImfile.iteration"
        " LEFT JOIN rawImfile"
        "    ON detInputExp.exp_tag = rawImfile.exp_tag"
        "    AND detNormalizedImfile.class_id = rawImfile.class_id"
        " LEFT JOIN detNormalizedExp"
        "    ON detInputExp.det_id = detNormalizedExp.det_id"
        "    AND detInputExp.iteration = detNormalizedExp.iteration"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
        "   AND detNormalizedExp.det_id IS NULL"
        "   AND detNormalizedExp.iteration IS NULL"
        "   AND detInputExp.include = 1"
        "   AND detRun.det_id = %s"
        "   AND detNormalizedImfile.iteration = %d"
        " GROUP BY"
        "    detNormalizedImfile.iteration,"
        "    detRun.det_id"
        " HAVING"
        "    COUNT(detNormalizedImfile.class_id) = COUNT(rawImfile.class_id)"
        );

    if (!p_psDBRunQuery(config->dbh, query, det_id, iteration)) {
        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
    detNormalizedExpRow *detRow = detNormalizedExpRowAlloc(
        (psS32)atol(det_id),
        iteration,
        recipe,
        bg,
        bg_stdev,
        bg_mean_stdev,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );

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

    psFree(detRow);

    return true;
}

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

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT"
        "   detNormalizedExp.*"
        " FROM detNormalizedExp"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
    );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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  detNormalizedImfileRow *detNormalizedStatToDetNormalizedmfile(pxConfig *config, detNormalizedStatImfileRow *statImfile)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    PS_ASSERT_PTR_NON_NULL(statImfile, NULL);

    bool status = false;
    psString uri = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    // optional
    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // optional
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    return detNormalizedImfileRowAlloc(
        statImfile->det_id,
        statImfile->iteration,
        statImfile->class_id,
        uri,
        bg,
        bg_stdev,
        bg_mean_stdev,
        user_1,
        user_2,
        user_3,
        user_4,
        user_5,
        path_base,
        code
    );
}

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

    /*
which returns a list of processed imfiles (with corresponding detrend
id, iteration, class id, uri, type) for which the appropriate stacked
imfile has been normalised, and which have not been masked out by
detResidImfileAnalysis;
*/

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    psString query = psStringCopy(
        "SELECT DISTINCT\n"
        "   detRun.det_id,\n"
        "   detRun.iteration,\n"
        "   detRun.det_type,\n"
        "   detRun.mode,\n"
        "   detRun.workdir,\n"
        "   detProcessedImfile.exp_tag,\n"
        "   detProcessedImfile.class_id,\n"
        "   detProcessedImfile.uri,\n"
        "   detNormalizedImfile.uri AS det_uri,\n"
        "   rawExp.camera\n"
        " FROM detRun\n"
        " JOIN detInputExp\n"
        "   USING(det_id, iteration)\n"
        " JOIN rawExp\n"
        "   ON detInputExp.exp_tag = rawExp.exp_tag\n"
        " JOIN detProcessedImfile\n"
        "   ON detRun.det_id = detProcessedImfile.det_id\n"
        "   AND detInputExp.exp_tag = detProcessedImfile.exp_tag\n"
        " JOIN detNormalizedImfile\n"
        "   ON detRun.det_id = detNormalizedImfile.det_id\n"
        "   AND detRun.iteration = detNormalizedImfile.iteration\n"
        "   AND detProcessedImfile.class_id = detNormalizedImfile.class_id\n"
        " LEFT JOIN detResidImfile\n"
        "   ON detRun.det_id = detResidImfile.det_id\n"
        "   AND detRun.iteration = detResidImfile.iteration\n"
        "   AND detProcessedImfile.exp_tag = detResidImfile.exp_tag\n"
        "   AND detProcessedImfile.class_id = detResidImfile.class_id\n"
        " WHERE\n"
        "   detRun.state = 'run'\n"
        "   AND detRun.mode = 'master'\n"
        "   AND detNormalizedImfile.fault = 0\n"
        "   AND detResidImfile.det_id IS NULL\n"
        "   AND detResidImfile.iteration IS NULL\n"
        "   AND detResidImfile.exp_tag IS NULL\n"
        "   AND detResidImfile.class_id IS NULL\n"
        " UNION"
        " SELECT\n"
        "   detRun.det_id,\n"
        "   detRun.iteration,\n"
        "   detRun.det_type,\n"
        "   detRun.mode,\n"
        "   detRun.workdir,\n"
        "   rawImfile.exp_tag,\n"
        "   rawImfile.class_id,\n"
        "   rawImfile.uri,\n"
        "   'NULL' AS det_uri,\n"
        "   rawExp.camera\n"
        " FROM detRun\n"
        " JOIN detInputExp\n"
        "    USING(det_id, iteration)\n"
        " JOIN rawExp\n"
        "    ON detInputExp.exp_tag = rawExp.exp_tag\n"
        " JOIN rawImfile\n"
        "    ON detInputExp.exp_tag = rawImfile.exp_tag\n"
        " LEFT JOIN detResidImfile\n"
        "   ON detRun.det_id = detResidImfile.det_id\n"
        "   AND detRun.iteration = detResidImfile.iteration\n"
        "   AND rawImfile.exp_tag = detResidImfile.exp_tag\n"
        "   AND rawImfile.class_id = detResidImfile.class_id\n"
        " WHERE\n"
        "   detRun.state = 'run'\n"
        "   AND detRun.mode = 'verify'\n"
        "   AND detResidImfile.det_id IS NULL\n"
        "   AND detResidImfile.iteration IS NULL\n"
        "   AND detResidImfile.exp_tag IS NULL\n"
        "   AND detResidImfile.class_id IS NULL\n"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // 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 normalizedimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT"
        " detNormalizedImfile.*"
        " FROM detNormalizedImfile"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
    );

    if (config->where) {
        bool status;
        int iteration = psMetadataLookupS32 (&status, config->where, "iteration");
        if (status) {
            psMetadataRemoveKey (config->where, "iteration");
            psMetadataAddS32 (config->where, PS_LIST_TAIL, "iteration", 0, "==", iteration);
        }
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedImfile");
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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 addresidimfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // make sure that there is a coresponding entry in detNormalizedImfile
    // and the exp_tag specified is valid
    // select * from detNormalizedImfile
    // by det_id, iteration, class_id
    // where det_id, iteration, class_id is not in detResidImfile
    psString query = psStringCopy(
        " SELECT * FROM ("
        "SELECT\n"
        "   detNormalizedImfile.*\n"
        " FROM detRun\n"
        " JOIN detInputExp\n"
        "   USING(det_id, iteration)\n"
        " JOIN detNormalizedImfile\n"
        "   USING(det_id, iteration)\n"
        " LEFT JOIN detResidImfile\n"
        "   ON detRun.det_id = detResidImfile.det_id\n"
        "   AND detRun.iteration = detResidImfile.iteration\n"
        "   AND detInputExp.exp_tag = detResidImfile.exp_tag\n"
        "   AND detNormalizedImfile.class_id = detResidImfile.class_id\n"
        " WHERE\n"
        "  detRun.state = 'run'\n"
        "  AND detRun.mode= 'master'\n"
        "  AND detResidImfile.det_id IS NULL\n"
        "  AND detResidImfile.iteration IS NULL\n"
        "  AND detResidImfile.exp_tag IS NULL\n"
        "  AND detResidImfile.class_id IS NULL\n"
        " UNION\n"
        " SELECT\n"
        "   detRun.det_id,\n"
        "   detRun.iteration,\n"
        "   rawImfile.class_id,\n"
        "   rawImfile.uri,\n"
        "   0.0 as bg,\n"
        "   0.0 as bg_stdev,\n"
        "   0.0 as bg_mean_stdev,\n"
        "   0.0 as user_1,\n"
        "   0.0 as user_2,\n"
        "   0.0 as user_3,\n"
        "   0.0 as user_4,\n"
        "   0.0 as user_5,\n"
        "   'NULL' as path_base,\n"
        "    0 as fault\n"
        " FROM detRun\n"
        " JOIN detInputExp\n"
        "    USING(det_id, iteration)\n"
        " JOIN rawExp\n"
        "    ON detInputExp.exp_tag = rawExp.exp_tag\n"
        " JOIN rawImfile\n"
        "    ON detInputExp.exp_tag = rawImfile.exp_tag\n"
        " LEFT JOIN detResidImfile\n"
        "   ON detRun.det_id = detResidImfile.det_id\n"
        "   AND detRun.iteration = detResidImfile.iteration\n"
        "   AND rawImfile.exp_tag = detResidImfile.exp_tag\n"
        "   AND rawImfile.class_id = detResidImfile.class_id\n"
        " WHERE\n"
        "   detRun.state = 'run'\n"
        "   AND detRun.mode = 'verify'\n"
        "   AND detResidImfile.det_id IS NULL\n"
        "   AND detResidImfile.iteration IS NULL\n"
        "   AND detResidImfile.exp_tag IS NULL\n"
        "   AND detResidImfile.class_id IS NULL\n"
        "   AND detInputExp.exp_tag = '%1$s'\n"
        ") AS foo\n"
        "  %2$s"
    );

    // build a query to search by det_id, iteration, class_id
    psMetadata *where = psMetadataAlloc();
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        psFree(where);
        psFree(query);
        return false;
    }
    if (det_id) {
        if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
            psFree(where);
            psFree(query);
            return false;
        }
    }

    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
        psFree(where);
        psFree(query);
        return false;
    }
    // always set iteration
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
        psFree(where);
        psFree(query);
        return false;
    }

    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        psFree(where);
        psFree(query);
        return false;
    }
    if (class_id) {
        if (!psMetadataAddStr(where, PS_LIST_TAIL, "class_id", 0, "==", class_id)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item class_id");
            psFree(where);
            psFree(query);
            return false;
        }
    }

    psString whereClause = psDBGenerateWhereSQL(where, NULL);
    psFree(where);

    // exp_tag is manadatory to cross check that this is a exp_tag for this
    // detRun
    psString exp_tag = psMetadataLookupStr(&status, config->args, "-exp_tag");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_tag");
        psFree(whereClause);
        psFree(query);
        return false;
    }
    if (!exp_tag) {
        psError(PS_ERR_UNKNOWN, true, "-exp_tag is required");
        psFree(whereClause);
        psFree(query);
        return false;
    }

    if (!p_psDBRunQuery(config->dbh, query, exp_tag, whereClause)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(whereClause);
        psFree(query);
        return false;
    }
    psFree(whereClause);
    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, "no pending detNormalizedImfile rows found");
        psFree(output);
        return false;
    }

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

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detNormalizedImfile object
        detNormalizedImfileRow *normalizedImfile = detNormalizedImfileObjectFromMetadata(row);
        // convert detNormalizedImfile object into a detResidImfile
        detResidImfileRow *residImfile  = detNormalizedToDetResidImfile(config, normalizedImfile);
        psFree(normalizedImfile);
        if (!residImfile) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "failed to convert detNormalizedImfile to detResidImfile");
            psFree(output);
            return false;
        }
        // insert detResidImfile object into the database
        if (!detResidImfileInsertObject(config->dbh, residImfile)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(residImfile);
            psFree(output);
        }
        psFree(residImfile);
    }

    psFree(output);

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

    return true;
}

static  detResidImfileRow *detNormalizedToDetResidImfile(pxConfig *config, detNormalizedImfileRow *normalizedImfile)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    PS_ASSERT_PTR_NON_NULL(normalizedImfile, NULL);

    bool status = false;
    psString exp_tag = psMetadataLookupStr(&status, config->args, "-exp_tag");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_tag");
        return false;
    }
    if (!exp_tag) {
        psError(PS_ERR_UNKNOWN, true, "-exp_tag is required");
        return false;
    }
    psString uri = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    psF64 bin_stdev = psMetadataLookupF64(&status, config->args, "-bin_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bin_stdev");
        return false;
    }

    // optional
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // create a new detResidImfileRow and insert it
    return detResidImfileRowAlloc(
            normalizedImfile->det_id,
            normalizedImfile->iteration,
            exp_tag,
            normalizedImfile->class_id,
            uri,
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            bin_stdev,
            fringe_0,
            fringe_1,
            fringe_2,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            path_base,
            code
        );
}

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

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    // select detResidImfile.*
    // by:
    // where det_id, iteration, exp_tag is not in detResidExp;

    psString query = psStringCopy(
        "SELECT"
        "   detRun.det_type,"
        "   detRun.mode,"
        "   detResidImfile.*,"
        "   rawExp.exp_time"
        " FROM detResidImfile"
        " JOIN detRun"
        "   USING(det_id, iteration)"
        " JOIN rawExp"
        "   USING(exp_tag)"
        " WHERE"
        "   detRun.state = 'run'"
    );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            psFree(output);
            return false;
        }
    }

    // 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 toresidexpMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    /*
which 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.
    */


    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    // select detRun.det_id
    // select detRun.iteration
    // select detRun.det_type
    // select detInputExp.exp_tag
    // select detInputExp.include
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // compare to detInputExp.imfiles to derResidImfile by class_id
    // and:
    // detResidImfile.{det_id, iteration, exp_tag} is not in detResidExp

    psString query = psStringCopy(
        "SELECT DISTINCT\n"
        "   det_id,\n"
        "   iteration,\n"
        "   det_type,\n"
        "   mode,\n"
        "   exp_tag,\n"
        "   include,\n"
        "   camera,\n"
        "   workdir\n"
        " FROM\n"
        "   (SELECT DISTINCT\n"
        "       detRun.det_id AS det_id,\n"
        "       detRun.iteration,\n"
        "       detRun.det_type,\n"
        "       detRun.mode,\n"
        "       detRun.workdir,\n"
        "       detInputExp.exp_tag,\n"
        "       detInputExp.include,\n"
        "       rawExp.imfiles,\n"
        "       rawExp.camera,\n"
        "       detResidImfile.class_id\n"
        "   FROM detRun\n"
        "   JOIN detInputExp\n"
        "       USING(det_id, iteration)\n"
        "   JOIN rawExp\n"
        "       ON detInputExp.exp_tag = rawExp.exp_tag\n"
        "   JOIN detResidImfile\n"
        "       ON detRun.det_id = detResidImfile.det_id\n"
        "       AND detRun.iteration = detResidImfile.iteration\n"
        "       AND detInputExp.exp_tag = detResidImfile.exp_tag\n"
        "   LEFT JOIN detResidExp\n"
        "       ON detResidImfile.det_id = detResidExp.det_id\n"
        "       AND detResidImfile.iteration = detResidExp.iteration\n"
        "       AND detResidImfile.exp_tag = detResidExp.exp_tag\n"
        "   WHERE\n"
        "       detRun.state = 'run'\n"
        "       AND detResidImfile.fault = 0\n"
        "       AND detResidExp.det_id IS NULL\n"
        "       AND detResidExp.iteration IS NULL\n"
        "       AND detResidExp.exp_tag IS NULL\n"
        "   GROUP BY\n"
        "       detInputExp.exp_tag,\n"
        "       detRun.iteration,\n"
        "       detRun.det_id\n"
        "   HAVING\n"
        "       rawExp.imfiles = COUNT(detResidImfile.class_id)\n"
        " ) AS toresidexp\n"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

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

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

    /*
which 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.
    */


    // select detRun.det_id
    // select detRun.iteration
    // select detRun.det_type
    // select detInputExp.exp_tag
    // select detInputExp.include
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // compare to detInputExp.imfiles to derResidImfile by class_id
    // and:
    // detResidImfile.{det_id, iteration, exp_tag} is not in detResidExp

    psString query = psStringCopy(
        "SELECT DISTINCT"
        "   det_id,"
        "   iteration,"
        "   exp_tag,"
        "   include"
        " FROM"
        "   (SELECT DISTINCT"
        "       detRun.det_id AS det_id,"
        "       detRun.iteration,"
        "       detRun.det_type,"
        "       detInputExp.exp_tag,"
        "       detInputExp.include,"
        "       rawExp.imfiles"
        "   FROM detRun"
        "   JOIN detInputExp"
        "       USING(det_id, iteration)"
        "   JOIN rawExp"
        "       ON detInputExp.exp_tag = rawExp.exp_tag"
        "   JOIN detResidImfile"
        "       ON detRun.det_id = detResidImfile.det_id"
        "       AND detRun.iteration = detResidImfile.iteration"
        "       AND detInputExp.exp_tag = detResidImfile.exp_tag"
        "   LEFT JOIN detResidExp"
        "       ON detResidImfile.det_id = detResidExp.det_id"
        "       AND detResidImfile.iteration = detResidExp.iteration"
        "       AND detResidImfile.exp_tag = detResidExp.exp_tag"
        "   WHERE"
        "       detRun.state = 'run'"
        "       AND detResidExp.det_id IS NULL"
        "       AND detResidExp.iteration IS NULL"
        "       AND detResidExp.exp_tag IS NULL"
        "   GROUP BY"
        "       detInputExp.exp_tag,"
        "       detRun.iteration,"
        "       detRun.det_id"
        "   HAVING"
        "       rawExp.imfiles = COUNT(detResidImfile.class_id)"
        " ) AS toresidexp"
        );

    {
        // build a query to search by det_id, iteration, exp_tag
        psMetadata *where = psMetadataAlloc();
        bool status = false;
        psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
            return false;
        }
        if (!det_id) {
            psError(PS_ERR_UNKNOWN, true, "-det_id is required");
            return false;
        }
        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);
            psFree(query);
            return false;
        }

        psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        // always set iteration
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        psString exp_tag = psMetadataLookupStr(&status, config->args, "-exp_tag");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_tag");
            psFree(where);
            psFree(query);
            return false;
        }
        if (exp_tag) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_tag", 0, "==", exp_tag)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_tag");
                psFree(where);
                psFree(query);
                return false;
            }
        }

        // there's not
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psFree(where);
        if (whereClause) {
            psStringAppend(&query, " WHERE %s", whereClause);
            psFree(whereClause);
        }
    }

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

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

    for (long i = 0; i < psArrayLength(output); i++) {
        psMetadata *row = output->data[i];
        // convert metadata into a detResidExp object
        detResidExpRow *residExp = mdToDetResidExp(config, row);
        if (!residExp) {
            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 (!detResidExpInsertObject(config->dbh, residExp)) {
            if (!psDBRollback(config->dbh)) {
                psError(PS_ERR_UNKNOWN, false, "database error");
            }
            psError(PS_ERR_UNKNOWN, false, "database error");
            psFree(residExp);
            psFree(output);
        }
        psFree(residExp);
    }

    psFree(output);

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

    return true;
}

static detResidExpRow *mdToDetResidExp(pxConfig *config, psMetadata *row)
{
    PS_ASSERT_PTR_NON_NULL(config, NULL);
    PS_ASSERT_PTR_NON_NULL(row, NULL);

    bool status = false;
    // values from row
    psS32 det_id = psMetadataLookupS32(&status, row, "det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for det_id");
        return false;
    }
    //if (isnan(det_id)) {
    //  psError(PS_ERR_UNKNOWN, true, "det_id is required");
    //  return false;
    //}
    psS32 iteration = psMetadataLookupS32(&status, row, "iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for iteration");
        return false;
    }
    psString exp_tag = psMetadataLookupStr(&status, row, "exp_tag");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for exp_tag");
        return false;
    }
    if (!exp_tag) {
        psError(PS_ERR_UNKNOWN, true, "exp_tag is required");
        return false;
    }
    // values from config
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        return false;
    }
    if (!recipe) {
        psError(PS_ERR_UNKNOWN, true, "-recip is required");
        return false;
    }
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return false;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return false;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }
    psF64 bin_stdev = psMetadataLookupF64(&status, config->args, "-bin_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bin_stdev");
        return false;
    }
    // optional
    psF64 fringe_0 = psMetadataLookupF64(&status, config->args, "-fringe_0");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_0");
        return false;
    }
    psF64 fringe_1 = psMetadataLookupF64(&status, config->args, "-fringe_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_1");
        return false;
    }
    psF64 fringe_2 = psMetadataLookupF64(&status, config->args, "-fringe_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -fringe_2");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    // optional
    bool reject = psMetadataLookupBool(&status, config->args, "-reject");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -reject");
        return false;
    }
    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }
    if (!path_base) {
        psError(PS_ERR_UNKNOWN, true, "-path_base is required");
        return false;
    }

    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

    // create a new detResidImfileRow and insert it
    return detResidExpRowAlloc(
            det_id,
            iteration,
            exp_tag,
            recipe,
            bg,
            bg_stdev,
            bg_mean_stdev,
            bin_stdev,
            fringe_0,
            fringe_1,
            fringe_2,
            user_1,
            user_2,
            user_3,
            user_4,
            user_5,
            path_base,
            !reject,
            code
        );
}

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

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT"
        "   detRun.mode,"
        "   detResidExp.*,"
        "   detInputExp.include"
        " FROM detRun"
        " JOIN detInputExp"
        "   USING(det_id, iteration)"
        " JOIN detResidExp"
        "   USING(det_id, iteration, exp_tag)"
        " WHERE"
        "   detRun.state = 'run'"
    );

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

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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 residdetrunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

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

    // select detRun.det_id
    // select detRun.iteration
    // select detRun.det_type
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // find all exp_tags in the current det_id/iteration from detResidExp
    // compare the counts of exp_tags

    psString query = psStringCopy(
        "SELECT DISTINCT\n"
        "   det_id,\n"
        "   iteration,\n"
        "   det_type,\n"
        "   mode,\n"
        "   workdir,\n"
        "   camera\n"
        " FROM\n"
        "   (SELECT DISTINCT\n"
        "       detRun.det_id,\n"
        "       detRun.iteration,\n"
        "       detRun.det_type,\n"
        "       detRun.mode,\n"
        "       detRun.workdir,\n"
        "       detInputExp.exp_tag,\n"
        "       rawExp.camera\n"
        "   FROM detRun\n"
        "   JOIN detInputExp\n"
        "       USING(det_id, iteration)\n"
        "   JOIN rawExp\n"
        "       ON detInputExp.exp_tag = rawExp.exp_tag\n"
        "   LEFT JOIN detResidExp\n"
        "       ON detRun.det_id = detResidExp.det_id\n"
        "       AND detRun.iteration = detResidExp.iteration\n"
        "       AND detInputExp.exp_tag = detResidExp.exp_tag\n"
        "   WHERE\n"
        "       detRun.state = 'run'\n"
        "   GROUP BY\n"
        "       detRun.det_id,\n"
        "       detRun.iteration\n"
        "   HAVING\n"
        "       COUNT(detResidExp.exp_tag) = COUNT(detInputExp.exp_tag)\n"
        " ) AS residdetrun\n"
        );

    // XXX does it make sense to accept any search params?
#if 0
    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where);
        psStringAppend(&query, " AND %s", whereClause);
        psFree(whereClause);
    }
#endif

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

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

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

    // build a query to search by det_id, iteration, exp_tag
    psMetadata *where = psMetadataAlloc();
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        psFree(where);
        return false;
    }
    if (det_id) {
        if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
            psFree(where);
            return false;
        }
    }
    psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
        psFree(where);
        return false;
    }
    // always where iteration
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
        psFree(where);
        return false;
    }
    psString exp_tag = psMetadataLookupStr(&status, config->args, "-exp_tag");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_tag");
        psFree(where);
        return false;
    }
    if (exp_tag) {
        if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_tag", 0, "==", exp_tag)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item exp_tag");
            psFree(where);
            return false;
        }
    }

    // find the values we're going to set
    // copy everything but det_id, iteration, & exp_tag from the args and
    // remove the '-' prefix
    psMetadata *set = psMetadataAlloc();
    psString recipe = psMetadataLookupStr(&status, config->args, "-recip");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -recip");
        psFree(set);
        psFree(where);
        return false;
    }
    if (recipe) {
        if (!psMetadataAddStr(set, PS_LIST_TAIL, "recipe", 0, "==", recipe)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item recipe");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        psFree(set);
        psFree(where);
        return false;
    }
    if (!isnan(bg)) {
        if (!psMetadataAddF64(set, PS_LIST_TAIL, "bg", 0, "==", bg)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item bg");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        psFree(set);
        psFree(where);
        return false;
    }
    if (!isnan(bg_stdev)) {
        if (!psMetadataAddF64(set, PS_LIST_TAIL, "bg_stdev", 0, "==", bg_stdev)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item bg_stdev");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        psFree(set);
        psFree(where);
        return false;
    }
    if (!isnan(bg_mean_stdev)) {
        if (!psMetadataAddF64(set, PS_LIST_TAIL, "bg_mean_stdev", 0, "==", bg_mean_stdev)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item bg_mean_stdev");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        psFree(set);
        psFree(where);
        return false;
    }
    if (path_base) {
        if (!psMetadataAddStr(set, PS_LIST_TAIL, "path_base", 0, "==", path_base)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item path_base");
            psFree(set);
            psFree(where);
            return false;
        }
    }

    bool reject = psMetadataLookupBool(&status, config->args, "-reject");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -reject");
        psFree(set);
        psFree(where);
        return false;
    }
    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;
}

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

    // select detRun.det_id
    // select detRun.iteration
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // find all exp_tags in the current det_id/iteration from detResidExp
    // compare the counts of exp_tags

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

    {
        // build a query to search by det_id, iteration, exp_tag
        psMetadata *where = psMetadataAlloc();
        bool status = false;
        psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
            psFree(where);
            psFree(query);
            return false;
        }
        if (det_id) {
            if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item det_id");
                psFree(where);
                psFree(query);
                return false;
            }
        }
        psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration");
            psFree(where);
            psFree(query);
            return false;
        }
        // always set iteration
        if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item iteration");
            psFree(where);
            psFree(query);
            return false;
        }

        // there's not
        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
        psFree(where);
        if (whereClause) {
            psStringAppend(&query, " WHERE %s", whereClause);
            psFree(whereClause);
        }
    }

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

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

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

static detRunSummaryRow *mdToDetRunSummary(pxConfig *config, psMetadata *row)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    bool status = false;
    // from row
    psS32 det_id = psMetadataLookupS32(&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;
    }

    // from config->args
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return NULL;
    }
    //if (isnan(bg)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg is required");
    //  return NULL;
    //}
    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return NULL;
    }
    //if (isnan(bg_stdev)) {
    //  psError(PS_ERR_UNKNOWN, true, "-bg_stdev is required");
    //  return NULL;
    //}
    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return NULL;
    }
    // optional
    bool accept = psMetadataLookupBool(&status, config->args, "-accept");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -accept");
        return NULL;
    }
    // default values
    psS16 code = psMetadataLookupS16(&status, config->args, "-code");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -code");
        return false;
    }

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

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

    bool status = false;
    psU64 limit = psMetadataLookupU64(&status, config->args, "-limit");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -limit");
        return false;
    }

    bool faulted = psMetadataLookupU64(&status, config->args, "-faulted");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -faulted");
        return false;
    }

    psString query = psStringCopy(
        "SELECT DISTINCT\n"
        "   detRunSummary.*,\n"
        "   detRun.det_type,\n"
        "   detRun.mode\n"
        " FROM detRun\n"
        " JOIN detRunSummary\n"
        "   USING(det_id, iteration)\n"
        " WHERE\n"
        "   detRun.state = 'run'\n"
        );

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

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

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            return false;
        }
    }

    // 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 updatedetrunMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // det_id is required
    // XXX this isn't strictly nessicary but not having the det_id complicates
    // incrementing the iteration number
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }
    // either -rerun or -state must be specified
    bool again = psMetadataLookupBool(&status, config->args, "-again");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -again");
        return false;
    }
    psString state = psMetadataLookupStr(&status, config->args, "-state");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -state");
        return false;
    }
    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, "either -again or -state must be specified");
        return false;
    }

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

    // else
    // -again

    // select detRun.det_id
    // select detRun.iteration
    // select detInputExp.exp_tag
    // select detResidExp.accept
    // by:
    // find the current iteration bassed on det_id
    // find all exp_tags in the current det_id/iteration from detInputExp
    // find all exp_tags in the current det_id/iteration from detResidExp
    // compare the counts of exp_tags

    psString query = psStringCopy(
        "SELECT DISTINCT"
        "   detRun.det_id AS det_id,"
        "   detRun.iteration,"
        "   detInputExp.exp_tag,"
        "   detResidExp.accept"
        " FROM detRun"
        " JOIN detInputExp"
        "   ON detRun.det_id = detInputExp.det_id"
        "   AND detRun.iteration = detInputExp.iteration"
        " JOIN detResidExp"
        "   ON detRun.det_id = detResidExp.det_id"
        "   AND detRun.iteration = detResidExp.iteration"
        "   AND detInputExp.exp_tag = detResidExp.exp_tag"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
    );

    // XXX this query was not restricted by det_id, resulting
    // in an inconsistent UPDATE below.  I added this AND clause
    // though there may be a cleaner method (EAM 2006.10.08)
    psStringAppend (
        &query,
        "  AND detRun.det_id = '%s'", det_id);

    psStringAppend (
        &query,
        " GROUP BY"
        "   detRun.det_id,"
        "   detRun.iteration,"
        "   detInputExp.exp_tag"
        " HAVING"
        "   COUNT(detResidExp.exp_tag) = COUNT(detInputExp.exp_tag)"
        );

    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 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];
        psString exp_tag = psMetadataLookupStr(&status, row, "exp_tag");
        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_tag");
            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,
                    (psS32)atol(det_id),
                    newIteration,
                    exp_tag,
                    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
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }

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

    psList *exp_tag_list = item->data.list;
    psMetadata *where = psMetadataAlloc();
    // make sure that -exp_tag 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_tag = mItem->data.V;
            // if exp_tag is NULL then it means that -exp_tag has not been
            // specified
            if (!exp_tag) {
                psError(PS_ERR_UNKNOWN, true,
                        "at least one -exp_tag is required");
                psFree(where);
                return false;
            }

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

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

    // add the det_id & iteration == 0 to the where clause
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "det_id", 0, "==",
                (psS32)atol(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_tags
    psHash *valid_exp_tags = psHashAlloc(psArrayLength(detrendExps));
    for (long i = 0; i < psArrayLength(detrendExps); i++) {
        psHashAdd(valid_exp_tags,
            ((detInputExpRow *)detrendExps->data[i])->exp_tag,
            detrendExps->data[i]
        );
    }
    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_tags);
        return false;
    }

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

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

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

    // required options
    bool status = false;
    psString det_type = psMetadataLookupStr(&status, config->args, "-det_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_type");
        return false;
    }
    if (!det_type) {
        psError(PS_ERR_UNKNOWN, true, "-det_type is required");
        return false;
    }

    psString filelevel = psMetadataLookupStr(&status, config->args, "-filelevel");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filelevel");
        return false;
    }
    if (!filelevel) {
        psError(PS_ERR_UNKNOWN, true, "-filelevel is required");
        return false;
    }

    psString workdir = psMetadataLookupStr(&status, config->args, "-workdir");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -workdir");
        return false;
    }
    if (!workdir) {
        psError(PS_ERR_UNKNOWN, true, "-workdir is required");
        return false;
    }

    // everything else is optional
    psString mode = psMetadataLookupStr(&status, config->args, "-mode");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -mode");
        return false;
    }
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }

    psString camera = psMetadataLookupStr(&status, config->args, "-inst");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -inst");
        return false;
    }

    psString telescope = psMetadataLookupStr(&status, config->args, "-telescope");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -telescope");
        return false;
    }

    psString exp_type = psMetadataLookupStr(&status, config->args, "-exp_type");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_type");
        return false;
    }

    psString filter = psMetadataLookupStr(&status, config->args, "-filter");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -filter");
        return false;
    }

    psF32 airmass_min = psMetadataLookupF32(&status, config->args, "-airmass_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_min");
        return false;
    }

    psF32 airmass_max = psMetadataLookupF32(&status, config->args, "-airmass_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -airmass_max");
        return false;
    }

    psF32 exp_time_min = psMetadataLookupF32(&status, config->args, "-exp_time_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_min");
        return false;
    }

    psF32 exp_time_max = psMetadataLookupF32(&status, config->args, "-exp_time_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_time_max");
        return false;
    }

    psF32 ccd_temp_min = psMetadataLookupF32(&status, config->args, "-ccd_temp_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_min");
        return false;
    }

    psF32 ccd_temp_max = psMetadataLookupF32(&status, config->args, "-ccd_temp_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -ccd_temp_max");
        return false;
    }

    psF64 posang_min = psMetadataLookupF32(&status, config->args, "-posang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_min");
        return false;
    }

    psF64 posang_max = psMetadataLookupF32(&status, config->args, "-posang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -posang_max");
        return false;
    }

    psF64 solang_min = psMetadataLookupF32(&status, config->args, "-solang_min");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_min");
        return false;
    }

    psF64 solang_max = psMetadataLookupF32(&status, config->args, "-solang_max");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -solang_max");
        return false;
    }

    psTime *registered = NULL;
    {
        psString registeredStr = psMetadataLookupStr(&status, config->args, "-registered");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -registered");
            return false;
        }
        // pass through NULL as this is an optional field
        if (registeredStr) {
            registered = psTimeFromISO(registeredStr, PS_TIME_UTC);
        } else {
            registered = NULL;
        }
    }

    psTime *time_begin = NULL;
    {
        psString time_beginStr = psMetadataLookupStr(&status, config->args, "-time_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_beginStr) {
            time_begin = psTimeFromISO(time_beginStr, PS_TIME_UTC);
        } else {
            time_begin = NULL;
        }
    }

    psTime *time_end = NULL;
    {
        psString time_endStr = psMetadataLookupStr(&status, config->args, "-time_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -time_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (time_endStr) {
            time_end = psTimeFromISO(time_endStr, PS_TIME_UTC);
        } else {
            time_end = NULL;
        }
    }

    psTime *use_begin = NULL;
    {
        psString use_beginStr = psMetadataLookupStr(&status, config->args, "-use_begin");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_begin");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_beginStr) {
            use_begin = psTimeFromISO(use_beginStr, PS_TIME_UTC);
        } else {
            use_begin = NULL;
        }
    }

    psTime *use_end = NULL;
    {
        psString use_endStr = psMetadataLookupStr(&status, config->args, "-use_end");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -use_end");
            return false;
        }
        // pass through NULL as this is an optional field
        if (use_endStr) {
            use_end = psTimeFromISO(use_endStr, PS_TIME_UTC);
        } else {
            use_end = NULL;
        }
    }

    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }

    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }

    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    psString parent = psMetadataLookupStr(&status, config->args, "-parent");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -parent");
        return false;
    }

    psString label = psMetadataLookupStr(&status, config->args, "-label");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -label");
        return 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,
            mode,
            "reg",      // state
            filelevel,
            workdir,
            camera,
            telescope,
            exp_type,
            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");
        }
        psFree(registered);
        return false;
    }
    psFree(registered);

    if (!detRunSummaryInsert(config->dbh,
            psDBLastInsertID(config->dbh),
            0,  // the iteration is fixed at 0
            bg,
            bg_stdev,
            bg_mean_stdev,
            true,   // accept
            0       // fault code
    )) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        // rollback
        if (!psDBRollback(config->dbh)) {
            psError(PS_ERR_UNKNOWN, false, "database error");
        }
        return false;
    }

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

    // print the new detRun
    psS64 det_id = psDBLastInsertID(config->dbh);
    psArray *detRuns = NULL;
    {
        psMetadata *where = psMetadataAlloc();
        psMetadataAddS32(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;
    }

    if (!convertIdToStr(detRuns)) {
        psError(PS_ERR_UNKNOWN, false, "failed to convert id fields into a strings");
        psFree(detRuns);
        return false;
    }

    bool simple = false;
    {
        bool status = false;
        simple = psMetadataLookupBool(&status, config->args, "-simple");
        if (!status) {
            psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -simple");
            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 register_detrend_imfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

    // required options
    bool status = false;
    psString det_id = psMetadataLookupStr(&status, config->args, "-det_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id");
        return false;
    }
    if (!det_id) {
        psError(PS_ERR_UNKNOWN, true, "-det_id is required");
        return false;
    }

    psString class_id = psMetadataLookupStr(&status, config->args, "-class_id");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
        return false;
    }
    if (!class_id) {
        psError(PS_ERR_UNKNOWN, true, "-class_id is required");
        return false;
    }

    psString uri    = psMetadataLookupStr(&status, config->args, "-uri");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -uri");
        return false;
    }
    if (!uri) {
        psError(PS_ERR_UNKNOWN, true, "-uri is required");
        return false;
    }

    // everything else is optional
    psF64 bg = psMetadataLookupF64(&status, config->args, "-bg");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg");
        return false;
    }

    psF64 bg_stdev = psMetadataLookupF64(&status, config->args, "-bg_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_stdev");
        return false;
    }

    psF64 bg_mean_stdev = psMetadataLookupF64(&status, config->args, "-bg_mean_stdev");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -bg_mean_stdev");
        return false;
    }

    psF64 user_1 = psMetadataLookupF64(&status, config->args, "-user_1");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_1");
        return false;
    }
    psF64 user_2 = psMetadataLookupF64(&status, config->args, "-user_2");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_2");
        return false;
    }
    psF64 user_3 = psMetadataLookupF64(&status, config->args, "-user_3");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_3");
        return false;
    }
    psF64 user_4 = psMetadataLookupF64(&status, config->args, "-user_4");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_4");
        return false;
    }
    psF64 user_5 = psMetadataLookupF64(&status, config->args, "-user_5");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -user_5");
        return false;
    }

    psString path_base = psMetadataLookupStr(&status, config->args, "-path_base");
    if (!status) {
        psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -path_base");
        return false;
    }

    // XXX this is going in without checking that the det_id is valid.
    // it seems like it be better to be using foreign key constraints rather
    // then having to resort to a seperate query (and locking) to check that
    // the det_id is valid
    if (!detNormalizedImfileInsert(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, const char *det_id)
{
    // this function returns zero on error
    PS_ASSERT_PTR_NON_NULL(config, 0);
    PS_ASSERT_PTR_NON_NULL(det_id, 0);

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

    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddS32(where, PS_LIST_TAIL, "det_id", 0, "==",
                (psS32)atol(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, const char *det_id, const char *state)
{
    PS_ASSERT_PTR_NON_NULL(det_id, 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, "reg", 4) == 0)
        )
    ) {
        psError(PS_ERR_UNKNOWN, false,
                "invalid detRun state: %s", state);
        return false;
    }

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