/*
 * 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);
// processedimfile
static bool toprocessedimfileMode(pxConfig *config);
static bool addprocessedimfileMode(pxConfig *config);
static bool processedimfileMode(pxConfig *config);
static bool revertprocessedimfileMode(pxConfig *config);
// processedexp
static bool toprocessedexpMode(pxConfig *config);
static bool addprocessedexpMode(pxConfig *config);
static bool processedexpMode(pxConfig *config);
static bool revertprocessedexpMode(pxConfig *config);
// stackedimfile
static bool tostackedMode(pxConfig *config);
static bool addstackedMode(pxConfig *config);
static bool stackedMode(pxConfig *config);
static bool revertstackedMode(pxConfig *config);
// normalizedstat
static bool tonormalizedstatMode(pxConfig *config);
static bool addnormalizedstatMode(pxConfig *config);
static bool normalizedstatMode(pxConfig *config);
static bool revertnormalizedstatMode(pxConfig *config);
// normalizedimfile
static bool tonormalizeMode(pxConfig *config);
static bool addnormalizedimfileMode(pxConfig *config);
static bool normalizedimfileMode(pxConfig *config);
static bool revertnormalizedimfileMode(pxConfig *config);
// normalizedexp
static bool tonormalizedexpMode(pxConfig *config);
static bool addnormalizedexpMode(pxConfig *config);
static bool normalizedexpMode(pxConfig *config);
static bool revertnormalizedexpMode(pxConfig *config);
// residimfile
static bool toresidimfileMode(pxConfig *config);
static bool addresidimfileMode(pxConfig *config);
static bool residimfileMode(pxConfig *config);
static bool revertresidimfileMode(pxConfig *config);
// residexp
static bool toresidexpMode(pxConfig *config);
static bool addresidexpMode(pxConfig *config);
static bool residexpMode(pxConfig *config);
static bool revertresidexpMode(pxConfig *config);
static bool updateresidexpMode(pxConfig *config);
// detrunsummary
static bool todetrunsummaryMode(pxConfig *config);
static bool adddetrunsummaryMode(pxConfig *config);
static bool detrunsummaryMode(pxConfig *config);
static bool revertdetrunsummaryMode(pxConfig *config);
static bool updatedetrunMode(pxConfig *config);
static bool rerunMode(pxConfig *config);
// register
static bool register_detrendMode(pxConfig *config);

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

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

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

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

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

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

    exit(EXIT_SUCCESS);

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

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

    exit(exit_status);
}

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

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

    psString query = psStringCopy(
        "SELECT"
        "   rawExp.*"
        " FROM rawExp"
        " LEFT JOIN detInputExp"
        "   ON rawExp.exp_id = detInputExp.exp_id"
        " WHERE"
        "    detInputExp.exp_id IS NULL"
        "    AND rawExp.object != '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;
    }

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

    psFree(output);

    return true;
}

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

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

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

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

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

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

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

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

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

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

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

    }

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

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

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

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

    psFree(detrendExps);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    psFree(detrendExps);

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

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


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

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

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

    // optional
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
    PXOPT_LOOKUP_STR(det_type, config->args, "-set_det_type", false, false);
    PXOPT_LOOKUP_STR(mode, config->args, "-set_mode", false, false);
    // check mode
    if (mode && !isValidMode(config, mode)) {
        psError(PS_ERR_UNKNOWN, false, "invalud mode");
        return false;
    }
    PXOPT_LOOKUP_STR(camera, config->args, "-set_inst", false, false);
    PXOPT_LOOKUP_STR(telescope, config->args, "-set_telescope", false, false);
    PXOPT_LOOKUP_STR(exp_type, config->args, "-set_exp_type", false, false);
    PXOPT_LOOKUP_STR(filelevel, config->args, "-set_filelevel", false, false);
    PXOPT_LOOKUP_STR(workdir, config->args, "-set_workdir", false, false);
    PXOPT_LOOKUP_STR(filter, config->args, "-set_filter", false, false);

    PXOPT_LOOKUP_F32(airmass_min, config->args, "-set_airmass_min", false, false);
    PXOPT_LOOKUP_F32(airmass_max, config->args, "-set_airmass_max", false, false);
    PXOPT_LOOKUP_F32(exp_time_min, config->args, "-set_exp_time_min", false, false);
    PXOPT_LOOKUP_F32(exp_time_max, config->args, "-set_exp_time_max", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_min, config->args, "-set_ccd_temp_min", false, false);
    PXOPT_LOOKUP_F32(ccd_temp_max, config->args, "-set_ccd_temp_max", false, false);
    PXOPT_LOOKUP_F32(posang_min, config->args, "-set_posang_min", false, false);
    PXOPT_LOOKUP_F32(posang_max, config->args, "-set_posang_max", false, false);
    PXOPT_LOOKUP_F32(solang_min, config->args, "-set_solang_min", false, false);
    PXOPT_LOOKUP_F32(solang_max, config->args, "-set_solang_max", false, false);
    PXOPT_LOOKUP_STR(label, config->args, "-set_label", false, false);
    PXOPT_LOOKUP_TIME(registered, config->args, "-set_registered", false, false);
    PXOPT_LOOKUP_TIME(time_begin, config->args, "-set_time_begin", false, false);
    PXOPT_LOOKUP_TIME(time_end, config->args, "-set_end", false, false);
    PXOPT_LOOKUP_TIME(use_begin, config->args, "-use_begin", false, false);
    PXOPT_LOOKUP_TIME(use_end, config->args, "-use_end", false, false);
    PXOPT_LOOKUP_STR(reduction, config->args, "-set_reduction", false, false);


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

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

    // discard the resultarray
    psFree(detRuns);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    psMetadata *time_filter = psMetadataAlloc();

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

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

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

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

    psString query = psStringCopy(
        "INSERT INTO detInputExp"
        "   SELECT"
        "       %d,"
        "       0,"
        "       detResidExp.exp_id,"
        "       detResidExp.accept"
        "   FROM detResidExp"
        "   JOIN rawExp"
        "       USING(exp_id)"
        "   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;
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

// XXX old version
# if 0
    // 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;
    }
# endif

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

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

    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", 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;
    }

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

    psFree(output);

    return true;
}

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

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

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

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

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

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

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

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

    psFree(output);

    return true;
}

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

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

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

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

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

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

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

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

    // 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_ids 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_ids
    psMetadata *where_exp_ids = psMetadataAlloc();
    for (long i = 0; i < psArrayLength(detInputExp); i++) {
        detInputExpRow *row = detInputExp->data[i];
        if (!psMetadataAddS64(where_exp_ids, PS_LIST_TAIL, "exp_id",
                PS_META_DUPLICATE_OK, "==", row->exp_id)
        ) {
            psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
            psFree(detInputExp);
            psFree(where_exp_ids);
            return NULL;
        }
    }
    psFree(detInputExp);

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

    return rawImfiles;
}

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

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

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

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

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


    // find the matching rawImfile by exp_id/class_id
    psMetadata *where = psMetadataAlloc();
    if (!psMetadataAddStr(where, PS_LIST_TAIL, "exp_id", 0, "==", exp_id)) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
        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;
    }

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


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

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

    psFree(detRow);

    return true;
}

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

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

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

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

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

    psFree(output);

    return true;
}

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

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

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

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

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


    psString query = psStringCopy(
        " SELECT DISTINCT"
        "    detProcessedImfile.det_id,"
        "    detRun.iteration,"
        "    detRun.det_type,"
        "    detProcessedImfile.exp_id"
        " FROM detRun"
        " JOIN detInputExp"
        "    ON detRun.det_id = detInputExp.det_id"
        "    AND detRun.iteration = detInputExp.iteration"
        " JOIN rawExp"
        "    ON detInputExp.exp_id = rawExp.exp_id"
        " JOIN detProcessedImfile"
        "    ON detInputExp.det_id = detProcessedImfile.det_id"
        "    AND detInputExp.exp_id = detProcessedImfile.exp_id"
        " LEFT JOIN detProcessedExp"
        "    ON detInputExp.det_id = detProcessedExp.det_id"
        "    AND detProcessedImfile.exp_id= detProcessedExp.exp_id"
        " LEFT JOIN rawImfile"
        "    ON detInputExp.exp_id = rawImfile.exp_id"
        "    AND detProcessedImfile.class_id = rawImfile.class_id"
        " WHERE"
        "   detRun.state = 'run'"
        "   AND detRun.mode = 'master'"
        "   AND detProcessedExp.det_id IS NULL"
        "   AND detProcessedExp.exp_id IS NULL"
        "   AND detInputExp.include = 1"
        "   AND detRun.det_id = %s"
        "   AND detProcessedImfile.exp_id = '%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_id)) {
        psError(PS_ERR_UNKNOWN, false, "database error");
        psFree(query);
        return false;
    }
    psFree(query);

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

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

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

    psFree(detRow);

    return true;
}

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

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

    psString query = 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;
    }

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

    psFree(output);

    return true;
}


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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detProcessedExp");
        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);

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

    return true;
}


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

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

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

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

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

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

    psString query = psStringCopy(
        " SELECT DISTINCT"
        "   detRun.det_type,"
        "   rawExp.exp_time,"
        "   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_id = detInputExp.exp_id"
        " JOIN rawExp"
        "   ON rawExp.exp_id = detProcessedImfile.exp_id"
        " WHERE"
    );
    // NOTE the above WHERE is completed with the following line:

    // add the two required restrictions: detRun.state and detRun.mode
    {
        bool status;
        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;
    }

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

    psFree(output);

    return true;
}


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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detProcessedImfile");
        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);

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

    return true;
}


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

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

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

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

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

    // default values
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", false, 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 (!psMetadataAddS64(where, PS_LIST_TAIL, "det_id", 0, "==",
            (psS64)atoll(det_id))) {
        psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
        psFree(where);
        return false;
    }

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

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

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

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

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

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

    psFree(stackedImfile);

    return true;
}

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

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

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

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

    psFree(output);

    return true;
}


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

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

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

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

    return true;
}

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

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

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

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

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

    psFree(output);

    return true;
}

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

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

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

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

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

    return true;
}


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

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

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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, NULL);
        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;
    }

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

    psFree(output);

    return true;
}

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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedStatImfile");
        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);

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

    return true;
}

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

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

    // 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_id = rawExp.exp_id"
        " 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"
        "   AND detRun.state = 'run'"
        );

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

    // 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_id",
                    PS_META_DUPLICATE_OK, "==",
                    ((detInputExpRow *)inputExps->data[i])->exp_id)) {
                psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                psFree(inputExps);
                psFree(where);
                return NULL;
            }
        }
        psFree(inputExps);
        rawImfiles = rawImfileSelectRowObjects(config->dbh, where, 0);
        // XXX this really should be sorted for uniqueness
        psFree(where);
    }
    if (!rawImfiles) {
        psError(PS_ERR_UNKNOWN, false, "no rawImfile rows found");
        return NULL;
    }

    return rawImfiles;
}
#endif

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

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

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

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

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

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

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

    return true;
}


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

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

    psString query = 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;
    }

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

    psFree(output);

    return true;
}


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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedImfile");
        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);

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

    return true;
}


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

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

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

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

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

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

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

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

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

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

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

    psStringAppend(&query,
            " WHERE det_id = %s"
            " AND iteration = %d", det_id, iteration);


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

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

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

    return true;
}


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

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

    psString query = 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;
    }

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

    psFree(output);

    return true;
}



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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detNormalizedExp");
        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);

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

    return true;
}


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

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

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

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

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

    psFree(output);

    return true;
}


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

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

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

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

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

    PXOPT_LOOKUP_F64(fringe_0, config->args, "-fringe_0", false, false);
    PXOPT_LOOKUP_F64(fringe_1, config->args, "-fringe_1", false, false);
    PXOPT_LOOKUP_F64(fringe_2, config->args, "-fringe_2", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_0, config->args, "-fringe_resid_0", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_1, config->args, "-fringe_resid_1", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_2, config->args, "-fringe_resid_2", false, false);

    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);

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

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

    return true;
}


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

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

    // select detResidImfile.*
    // by:
    // where det_id, iteration, exp_id 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_id)"
        " WHERE"
    );
    // NOTE the above WHERE is completed with the following line:

    char *value = NULL;

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

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

    psFree(output);

    return true;
}


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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detResidImfile");
        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);

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

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

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

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

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

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

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

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

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

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

    PXOPT_LOOKUP_F64(fringe_0, config->args, "-fringe_0", false, false);
    PXOPT_LOOKUP_F64(fringe_1, config->args, "-fringe_1", false, false);
    PXOPT_LOOKUP_F64(fringe_2, config->args, "-fringe_2", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_0, config->args, "-fringe_resid_0", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_1, config->args, "-fringe_resid_1", false, false);
    PXOPT_LOOKUP_F64(fringe_resid_2, config->args, "-fringe_resid_2", false, false);

    PXOPT_LOOKUP_F64(user_1, config->args, "-user_1", false, false);
    PXOPT_LOOKUP_F64(user_2, config->args, "-user_2", false, false);
    PXOPT_LOOKUP_F64(user_3, config->args, "-user_3", false, false);
    PXOPT_LOOKUP_F64(user_4, config->args, "-user_4", false, false);
    PXOPT_LOOKUP_F64(user_5, config->args, "-user_5", false, false);
    PXOPT_LOOKUP_STR(path_base, config->args, "-path_base", false, false);

    // default values
    PXOPT_LOOKUP_S32(iteration, config->args, "-iteration", false, false);
    PXOPT_LOOKUP_BOOL(reject, config->args, "-reject", 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.
    */

    psString query = pxDataGet("dettool_toresidexp.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_id
        psMetadata *where = psMetadataAlloc();

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


        // 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;
    }
    psFree(output);

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

    return true;
}

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

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

    psString query = psStringCopy(
        "SELECT"
        "   detRun.mode,"
        "   detResidExp.*,"
        "   detInputExp.include"
        " FROM detRun"
        " JOIN detInputExp"
        "   USING(det_id, iteration)"
        " JOIN detResidExp"
        "   USING(det_id, iteration, exp_id)"
        " 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;
    }

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

    psFree(output);

    return true;
}


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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detResidExp");
        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);

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

    return true;
}


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

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

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

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

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

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

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

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

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

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

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

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

    return true;
}

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

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

    // optional
    PXOPT_LOOKUP_BOOL(again, config->args, "-again", false);

    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_id
    psMetadata *where = psMetadataAlloc();
    PXOPT_COPY_STR(config->args, where, "-det_id", "det_id", "==");
    PXOPT_COPY_S32(config->args, where, "-iteration", "iteration", "==");

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

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

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

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

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

    // optional
    PXOPT_LOOKUP_F64(bg, config->args, "-bg", false, false);
    PXOPT_LOOKUP_F64(bg_stdev, config->args, "-bg_stdev", false, false);
    PXOPT_LOOKUP_F64(bg_mean_stdev, config->args, "-bg_mean_stdev", false, false);
    // default values
    PXOPT_LOOKUP_BOOL(accept, config->args, "-accept", false);
    PXOPT_LOOKUP_S16(code, config->args, "-code", false, 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);

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

    psString query = 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;
    }

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

    psFree(output);

    return true;
}


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

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

    if (config->where) {
        psString whereClause = psDBGenerateWhereConditionSQL(config->where, "detRunSummary");
        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);

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

    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
    PXOPT_LOOKUP_STR(det_id, config->args, "-det_id", true, false);

    PXOPT_LOOKUP_BOOL(again, config->args, "-again", false);
    PXOPT_LOOKUP_STR(state, config->args, "-state", false, false);
    // either -rerun or -state must be specified
    if (!(again || state)) {
        psError(PS_ERR_UNKNOWN, true, "either -again or -state must be specified");
        return false;
    }
    if (again && state) {
        psError(PS_ERR_UNKNOWN, true, "either -again or -state must be specified");
        return false;
    }

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

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

    return true;
}

static bool startNewIteration(pxConfig *config, psS64 det_id)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

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

    // 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, " WHERE det_id = %" PRId64 , det_id);

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

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

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

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

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

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

    psFree(output);

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

    return true;
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    return true;
}

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

    // required
    PXOPT_LOOKUP_STR(det_type, config->args, "-det_type", true, false);
    PXOPT_LOOKUP_STR(filelevel, config->args, "-filelevel", false, false);

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

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

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

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

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

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

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

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

bool register_detrend_imfileMode(pxConfig *config)
{
    PS_ASSERT_PTR_NON_NULL(config, false);

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

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

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

    return true;
}

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

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

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

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

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

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

    return newIteration;
}

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

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

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

    return true;
}

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

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

    return true;
}
