Index: branches/simmosaic_branches/ippTools/src/magicdstool.c
===================================================================
--- branches/simmosaic_branches/ippTools/src/magicdstool.c	(revision 24860)
+++ branches/simmosaic_branches/ippTools/src/magicdstool.c	(revision 27839)
@@ -2,5 +2,5 @@
  * magicdstool.c
  *
- * Copyright (C) 2006-2007  IfA
+ * Copyright (C) 2006-2009  IfA
  *
  * This program is free software; you can redistribute it and/or modify it
@@ -27,4 +27,5 @@
 #include <math.h>
 #include <ippdb.h>
+#include <ippStages.h>
 
 #include "pxtools.h"
@@ -32,17 +33,18 @@
 
 static bool definebyqueryMode(pxConfig *config);
-static psS64 definerunMode(pxConfig *config);
+static bool definecopyMode(pxConfig *config);
 static bool updaterunMode(pxConfig *config);
 static bool todestreakMode(pxConfig *config);
 static bool adddestreakedfileMode(pxConfig *config);
+static bool advancerunMode(pxConfig *config);
 static bool revertdestreakedfileMode(pxConfig *config);
+static bool clearstatefaultsMode(pxConfig *config);
 static bool getskycellsMode(pxConfig *config);
 static bool toremoveMode(pxConfig *config);
-static bool torestoreMode(pxConfig *config);
 static bool torevertMode(pxConfig *config);
-
-static bool setmagicDSRunState(pxConfig *config, psS64 magic_id, const char *state);
-static bool magicDSRunComplete(pxConfig *config, bool setmagicked);
-static bool magicDSGetIDs(pxConfig *config, psString stage, psS64 magic_id, psS64 *stage_id, psS64 *cam_id);
+static bool completedrevertMode(pxConfig *config);
+static bool tocleanupMode(pxConfig *config);
+
+static bool setmagicDSRunState(pxConfig *config, psS64 magic_id, psString extraSetString, psMetadata *where, const char *state);
 
 # define MODECASE(caseName, func) \
@@ -65,13 +67,16 @@
     switch (config->mode) {
         MODECASE(MAGICDSTOOL_MODE_DEFINEBYQUERY,       definebyqueryMode);
-        MODECASE(MAGICDSTOOL_MODE_DEFINERUN,           definerunMode);
+        MODECASE(MAGICDSTOOL_MODE_DEFINECOPY,          definecopyMode);
         MODECASE(MAGICDSTOOL_MODE_UPDATERUN,           updaterunMode);
         MODECASE(MAGICDSTOOL_MODE_TODESTREAK,          todestreakMode);
         MODECASE(MAGICDSTOOL_MODE_ADDDESTREAKEDFILE,   adddestreakedfileMode);
+        MODECASE(MAGICDSTOOL_MODE_ADVANCERUN,          advancerunMode);
         MODECASE(MAGICDSTOOL_MODE_REVERTDESTREAKEDFILE,revertdestreakedfileMode);
+        MODECASE(MAGICDSTOOL_MODE_CLEARSTATEFAULTS,    clearstatefaultsMode);
         MODECASE(MAGICDSTOOL_MODE_GETSKYCELLS,         getskycellsMode);
         MODECASE(MAGICDSTOOL_MODE_TOREMOVE,            toremoveMode);
-        MODECASE(MAGICDSTOOL_MODE_TORESTORE,           torestoreMode);
         MODECASE(MAGICDSTOOL_MODE_TOREVERT,            torevertMode);
+        MODECASE(MAGICDSTOOL_MODE_COMPLETEDREVERT,     completedrevertMode);
+        MODECASE(MAGICDSTOOL_MODE_TOCLEANUP,           tocleanupMode);
         default:
             psAbort("invalid option (this should not happen)");
@@ -97,119 +102,116 @@
 static bool definebyqueryMode(pxConfig *config)
 {
-    PS_ASSERT_PTR_NON_NULL(config, false);
-    psError(PS_ERR_UNKNOWN, true, "definebyquery not implelmented yet");
-
-    return false;
-#ifdef notyet
 
     // Required
+    PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);
+
+    // Optional
     PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", false, false);
-
-    // Optional
-    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);
-    PXOPT_LOOKUP_STR(dvodb, config->args, "-dvodb", false, false);
-    PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);
+    PXOPT_LOOKUP_STR(recoveryroot, config->args, "-recoveryroot", false, false);
+    PXOPT_LOOKUP_BOOL(noreplace, config->args, "-noreplace", false);
+    PXOPT_LOOKUP_STR(set_label, config->args, "-set_label", false, false);
+    PXOPT_LOOKUP_STR(set_data_group, config->args, "-set_data_group", false, false);
+    PXOPT_LOOKUP_STR(note, config->args, "-set_note", false, false);
+    PXOPT_LOOKUP_BOOL(rerun, config->args, "-rerun", false);
+    PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
     PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
-
-    // Create temporary table of the best diffs
-    {
-        psString query = pxDataGet("magictool_definebyquery_temp_create.sql");
-        if (!query) {
-            psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-            return false;
-        }
-
-        if (!p_psDBRunQuery(config->dbh, query)) {
+    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
+
+    // search args
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, where, "-exp_id",  "exp_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-chip_id", "chip_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-cam_id",  "cam_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-warp_id", "warp_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-diff_id", "diff_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-magic_id","magicRun.magic_id", "==");
+    PXOPT_COPY_S32(config->args, where, "-streaks_max","streaks", "<=");
+
+    pxAddLabelSearchArgs (config, where, "-label", "magicRun.label", "=="); // define using magicRun label
+
+    ippStage stageNum = ippStringToStage(stage);
+
+    psString query = NULL;
+    switch (stageNum) {
+    case IPP_STAGE_RAW:
+        query = pxDataGet("magicdstool_definebyquery_raw.sql");
+        break;
+    case IPP_STAGE_CHIP:
+        query = pxDataGet("magicdstool_definebyquery_chip.sql");
+        break;
+    case IPP_STAGE_CAMERA:
+        query = pxDataGet("magicdstool_definebyquery_camera.sql");
+        break;
+    case IPP_STAGE_WARP:
+        query = pxDataGet("magicdstool_definebyquery_warp.sql");
+        break;
+    case IPP_STAGE_DIFF:
+        query = pxDataGet("magicdstool_definebyquery_diff.sql");
+        break;
+    case IPP_STAGE_FAKE:
+    case IPP_STAGE_STACK:
+        psError(PXTOOLS_ERR_CONFIG, true, "%sRuns do not need to be destreaked", stage);
+        return false;
+    case IPP_STAGE_NONE:
+        psError(PXTOOLS_ERR_CONFIG, true, "%s is not a valid stage", stage);
+        return false;
+    default:
+        psError(PXTOOLS_ERR_PROG, true, "ippStageToString returned %d for invalid stage %s",
+            stageNum, stage);
+        return false;
+    }
+
+    if (!query) {
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+    char *rerun_flag =  rerun ? "\n1 " : "\n0" ;
+
+    if (stageNum != IPP_STAGE_DIFF) {
+        if (psListLength(where->list)) {
+            psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+            psStringAppend(&query, "\nAND %s", whereClause);
+            psFree(whereClause);
+        }
+        psFree(where);
+
+        // treat limit == 0 as "no limit"
+        if (limit) {
+            psString limitString = psDBGenerateLimitSQL(limit);
+            psStringAppend(&query, " %s", limitString);
+            psFree(limitString);
+        }
+        if (!p_psDBRunQueryF(config->dbh, query, rerun_flag)) {
             psError(PS_ERR_UNKNOWN, false, "database error");
-            return false;
-        }
-        psFree(query);
-    }
-
-    // Insert list of best diffs into temporary table
-    {
-        psString query = pxDataGet("magictool_definebyquery_temp_insert.sql");
-        if (!query) {
-            psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-            return false;
-        }
-
-        psMetadata *where = psMetadataAlloc();
-        PXOPT_COPY_S64(config->args, where, "-exp_id", "exp_id", "==");
-        PXOPT_COPY_STR(config->args, where, "-diff_label", "diffRun.label", "==");
-        PXOPT_COPY_F32(config->args, where, "-good_frac", "warpSkyfile.good_frac", ">=");
-
-        psString whereClause = NULL;    // WHERE conditions
-        if (psListLength(where->list)) {
-            whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-            psStringPrepend(&whereClause, "\n AND ");
-        }
-        psFree(where);
-
-        if (!p_psDBRunQueryF(config->dbh, query, whereClause)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-            psFree(whereClause);
             psFree(query);
             return false;
         }
-        psFree(whereClause);
-        psFree(query);
-    }
-
-    // Get list of exposures ready to magic
-    {
-        psString query = pxDataGet("magictool_definebyquery_select.sql");
-        if (!query) {
-            psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-            return false;
-        }
-
-        psString magicSkyCellNumsWhere = NULL;    // WHERE conditions for magicSkyCellNums
-        {
-            psMetadata *where = psMetadataAlloc();
-            PXOPT_COPY_S64(config->args, where, "-exp_id", "exp_id", "==");
-            PXOPT_COPY_STR(config->args, where, "-diff_label", "diffRun.label", "==");
-            PXOPT_COPY_F32(config->args, where, "-good_frac", "warpSkyfile.good_frac", ">=");
-
-            if (psListLength(where->list)) {
-                magicSkyCellNumsWhere = psDBGenerateWhereConditionSQL(where, NULL);
-                psStringPrepend(&magicSkyCellNumsWhere, "\n AND ");
-            }
-            psFree(where);
-        }
-
-        // "available" means only concern ourselves with exposures that have all diffs completed, unless we're
-        // told to only take what's available.
-        // "new" means we want a new run even if there's already a magic run defined
-        PXOPT_LOOKUP_BOOL(available, config->args, "-available", false);
-        PXOPT_LOOKUP_BOOL(new, config->args, "-new", false);
-
-        psString queryWhere = NULL;     // WHERE conditions for entire query
-        if (available) {
-            psStringAppend(&queryWhere, " WHERE num_done = num_todo");
-        }
-        if (new) {
-            const char *newWhere = " magic_id IS NULL"; // String to add
-            if (queryWhere) {
-                psStringAppend(&queryWhere, " AND %s", newWhere);
-            } else {
-                psStringAppend(&queryWhere, " WHERE %s", newWhere);
-            }
-        }
-        if (queryWhere) {
-            psStringAppend(&query, " %s", queryWhere);
-            psFree(queryWhere);
-        }
-
-
-        if (!p_psDBRunQueryF(config->dbh, query, magicSkyCellNumsWhere ? magicSkyCellNumsWhere : "")) {
+    } else {
+        // diff stage query has two types bothways and !bothways
+        // so we need to send the rerun flag and the where data twice
+        psString whereString = psStringCopy("");
+        if (psListLength(where->list)) {
+            psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+            psStringAppend(&whereString, "\nAND %s", whereClause);
+            psFree(whereClause);
+        }
+        psFree(where);
+
+        // treat limit == 0 as "no limit"
+        if (limit) {
+            psString limitString = psDBGenerateLimitSQL(limit);
+            psStringAppend(&query, " %s", limitString);
+            psFree(limitString);
+        }
+
+        if (!p_psDBRunQueryF(config->dbh, query, rerun_flag, whereString, rerun_flag, whereString)) {
             psError(PS_ERR_UNKNOWN, false, "database error");
-            psFree(magicSkyCellNumsWhere);
+            psFree(whereString);
             psFree(query);
             return false;
         }
-        psFree(magicSkyCellNumsWhere);
-        psFree(query);
-    }
+        psFree(whereString);
+    }
+    psFree(query);
 
     psArray *output = p_psDBFetchResult(config->dbh);
@@ -233,72 +235,75 @@
     }
 
-    // Parse the list of exposures ready to magic
-
-    if (!psDBTransaction(config->dbh)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        return false;
-    }
-
-    psString insert = pxDataGet("magictool_definebyquery_insert.sql"); // Insert query
+    // Parse the list of runs ready to be destreaked
+
+    if (!pretend && !psDBTransaction(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
 
     psArray *list = psArrayAllocEmpty(16); // List of runs, to print
     for (long i = 0; i < psArrayLength(output); i++) {
         psMetadata *row = output->data[i]; // Row of interest
-        psS64 exp_id = psMetadataLookupS64(NULL, row, "exp_id"); // Exposure identifier
+        psS64 stage_id = psMetadataLookupS64(NULL, row, "stage_id");
+        psS64 exp_id = psMetadataLookupS64(NULL, row, "exp_id");
+        psS64 magic_id = psMetadataLookupS64(NULL, row, "magic_id");
+        psS64 inv_magic_id = psMetadataLookupS64(NULL, row, "inv_magic_id");
+        psS64 cam_id = psMetadataLookupS64(NULL, row, "cam_id");
+        psString magicRunLabel = psMetadataLookupStr(NULL, row, "label");
+        psString magicRunDataGroup = psMetadataLookupStr(NULL, row, "data_group");
+        psString magicRunWorkdir = psMetadataLookupStr(NULL, row, "workdir");
+
+        // if workdir is not supplied use the magicRun's
+        if (!workdir) {
+            workdir = magicRunWorkdir;
+        }
+
+        psString outroot = NULL;
+        // set outroot to workdir/exp_id/stage for example /somewhere/424242/chip
+        psStringAppend(&outroot, "%s/%" PRId64 "/%s", workdir, exp_id, stage);
 
         // create a new magicRun for this group
-        magicRunRow *run = magicRunRowAlloc(0, exp_id, "new", workdir, "dirty", label, dvodb, registered, 0);
+        magicDSRunRow *run = magicDSRunRowAlloc(
+                0, // magic_ds_id
+                magic_id,
+                inv_magic_id,
+                "new",
+                stage,
+                stage_id,
+                cam_id,
+                set_label ? set_label : magicRunLabel,
+                set_data_group ? set_data_group : magicRunDataGroup,
+                outroot,
+                recoveryroot,
+                noreplace ? 0 :1,   // re_place
+                0,      // remove
+                0,      // fault
+                note);  // remove
+
+        psFree(outroot);
         if (!run) {
-            psAbort("failed to alloc magicRun object");
-        }
-
-        if (!magicRunInsertObject(config->dbh, run)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-            psFree(run);
-            psFree(insert);
-            psFree(output);
-            psFree(list);
-            if (!psDBRollback(config->dbh)) {
+            psAbort("failed to alloc magicDSRun object");
+        }
+
+        if (!pretend) {
+            if (!magicDSRunInsertObject(config->dbh, run)) {
                 psError(PS_ERR_UNKNOWN, false, "database error");
+                psFree(run);
+                psFree(output);
+                psFree(list);
+                if (!psDBRollback(config->dbh)) {
+                    psError(PS_ERR_UNKNOWN, false, "database error");
+                }
+                return false;
             }
-            return false;
-        }
-
-        psS64 magic_id = psDBLastInsertID(config->dbh); // Assigned identifier
-        run->magic_id = magic_id;
+            psS64 magic_ds_id = psDBLastInsertID(config->dbh); // Assigned identifier
+            run->magic_ds_id = magic_ds_id;
+        }
 
         psArrayAdd(list, list->n, run);
         psFree(run);
-
-        // Create a suitable insertion query for this run
-        psString thisInsert = psStringCopy(insert);
-        {
-            psString idString = NULL;
-            psStringAppend(&idString, "%" PRId64, magic_id);
-            psStringSubstitute(&thisInsert, idString, "@MAGIC_ID@");
-            psFree(idString);
-        }
-        {
-            psString idString = NULL;
-            psStringAppend(&idString, "%" PRId64, exp_id);
-            psStringSubstitute(&thisInsert, idString, "@EXP_ID@");
-            psFree(idString);
-        }
-
-        if (!p_psDBRunQueryF(config->dbh, thisInsert, magic_id, exp_id)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-            psFree(thisInsert);
-            psFree(insert);
-            psFree(output);
-            psFree(list);
-            if (!psDBRollback(config->dbh)) {
-                psError(PS_ERR_UNKNOWN, false, "database error");
-            }
-            return false;
-        }
-        psFree(thisInsert);
-    }
-
-    if (!psDBCommit(config->dbh)) {
+    }
+
+    if (!pretend && !psDBCommit(config->dbh)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
         return false;
@@ -306,5 +311,5 @@
     psFree(output);
 
-    if (!magicRunPrintObjects(stdout, list, !simple)) {
+    if (!magicDSRunPrintObjects(stdout, list, !simple)) {
         psError(PS_ERR_UNKNOWN, false, "failed to print object");
         psFree(list);
@@ -315,108 +320,308 @@
 
     return true;
-#endif // notyet
-}
-
-static psS64 definerunMode(pxConfig *config)
+}
+
+
+// XXX This currently allows multiple destreak runs to be queued on the same exposure if there are multiple
+// magicRuns selected!
+static bool definecopyMode(pxConfig *config)
+{
+    // Required
+    PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);
+    PXOPT_LOOKUP_STR(workdir, config->args, "-workdir", true, false);
+
+    // Optional
+    PXOPT_LOOKUP_STR(recoveryroot, config->args, "-recoveryroot", false, false);
+    PXOPT_LOOKUP_BOOL(noreplace, config->args, "-noreplace", false);
+    PXOPT_LOOKUP_STR(set_label, config->args, "-set_label", false, false);
+    PXOPT_LOOKUP_STR(set_data_group, config->args, "-set_data_group", false, false);
+    PXOPT_LOOKUP_STR(note, config->args, "-set_note", false, false);
+    PXOPT_LOOKUP_BOOL(rerun, config->args, "-rerun", false);
+    PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
+    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
+
+    // search args
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, where, "-exp_id",  "chipRun.exp_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-chip_id", "chipRun.chip_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-cam_id",  "camRun.cam_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-warp_id", "warpRun.warp_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-diff_id", "diffRun.diff_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-magic_id", "magicRun.magic_id", "==");
+    PXOPT_COPY_S32(config->args, where, "-streaks_max", "magicMask.streaks", "<=");
+
+    pxAddLabelSearchArgs (config, where, "-magic_label", "magicRun.label", "=="); // define magic label
+    psString labelName = NULL;                                                    // Name of label
+    psStringAppend(&labelName, "%sRun.label", stage);
+    pxAddLabelSearchArgs (config, where, "-stage_label", labelName, "=="); // define stageRun label
+    psFree(labelName);
+
+    ippStage stageNum = ippStringToStage(stage);
+
+    psString query = NULL;
+    switch (stageNum) {
+      case IPP_STAGE_RAW:
+        psError(PS_ERR_BAD_PARAMETER_VALUE, true, "Raw stage is not appropriate for a copied destreak");
+        return false;
+      case IPP_STAGE_CHIP:
+        query = pxDataGet("magicdstool_definecopy_chip.sql");
+        break;
+      case IPP_STAGE_WARP:
+        query = pxDataGet("magicdstool_definecopy_warp.sql");
+        break;
+      case IPP_STAGE_CAMERA:
+      case IPP_STAGE_DIFF:
+      case IPP_STAGE_FAKE:
+        psError(PS_ERR_BAD_PARAMETER_VALUE, true, "%s has not been coded.", stage);
+        return false;
+      case IPP_STAGE_STACK:
+        psError(PXTOOLS_ERR_CONFIG, true, "Stacks do not need to be destreaked");
+        return false;
+      case IPP_STAGE_NONE:
+        psError(PXTOOLS_ERR_CONFIG, true, "%s is not a valid stage", stage);
+        return false;
+      default:
+        psError(PXTOOLS_ERR_PROG, true, "ippStageToString returned %d for invalid stage %s",
+                stageNum, stage);
+        return false;
+    }
+
+    if (!query) {
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+    const char *rerun_flag =  rerun ? "" : "\n"; // String to give query to activate (or not) rerun
+
+    if (stageNum != IPP_STAGE_DIFF) {
+        if (psListLength(where->list)) {
+            psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+            psStringAppend(&query, "\nAND %s", whereClause);
+            psFree(whereClause);
+        }
+        psFree(where);
+
+        // treat limit == 0 as "no limit"
+        if (limit) {
+            psString limitString = psDBGenerateLimitSQL(limit);
+            psStringAppend(&query, " %s", limitString);
+            psFree(limitString);
+        }
+        if (!p_psDBRunQueryF(config->dbh, query, set_label, rerun_flag)) {
+            psError(PS_ERR_UNKNOWN, false, "database error");
+            psFree(query);
+            return false;
+        }
+    } else {
+        // diff stage query has two types bothways and !bothways
+        // so we need to send the rerun flag and the where data twice
+        psString whereString = psStringCopy("");
+        if (psListLength(where->list)) {
+            psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+            psStringAppend(&whereString, "\nAND %s", whereClause);
+            psFree(whereClause);
+        }
+        psFree(where);
+
+        // treat limit == 0 as "no limit"
+        if (limit) {
+            psString limitString = psDBGenerateLimitSQL(limit);
+            psStringAppend(&query, " %s", limitString);
+            psFree(limitString);
+        }
+
+        if (!p_psDBRunQueryF(config->dbh, query, rerun_flag, whereString, rerun_flag, whereString)) {
+            psError(PS_ERR_UNKNOWN, false, "database error");
+            psFree(whereString);
+            psFree(query);
+            return false;
+        }
+        psFree(whereString);
+    }
+    psFree(query);
+
+    psArray *output = p_psDBFetchResult(config->dbh);
+    if (!output) {
+        psErrorCode err = psErrorCodeLast();
+        switch (err) {
+          case PS_ERR_DB_CLIENT:
+            psError(PXTOOLS_ERR_SYS, false, "database error");
+          case PS_ERR_DB_SERVER:
+            psError(PXTOOLS_ERR_PROG, false, "database error");
+          default:
+            psError(PXTOOLS_ERR_PROG, false, "unknown error");
+        }
+
+        return false;
+    }
+    if (!psArrayLength(output)) {
+        psTrace("magictool", PS_LOG_INFO, "no rows found");
+        psFree(output);
+        return true;
+    }
+
+    // Parse the list of runs ready to be destreaked
+
+    if (!pretend && !psDBTransaction(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+
+    psArray *list = psArrayAllocEmpty(16); // List of runs, to print
+    for (long i = 0; i < psArrayLength(output); i++) {
+        psMetadata *row = output->data[i]; // Row of interest
+        psS64 stage_id = psMetadataLookupS64(NULL, row, "stage_id");
+        psS64 exp_id = psMetadataLookupS64(NULL, row, "exp_id");
+        psS64 magic_id = psMetadataLookupS64(NULL, row, "magic_id");
+        psS64 inv_magic_id = psMetadataLookupS64(NULL, row, "inv_magic_id");
+        psS64 cam_id = psMetadataLookupS64(NULL, row, "cam_id");
+        psString magicRunLabel = psMetadataLookupStr(NULL, row, "label");
+        psString magicRunDataGroup = psMetadataLookupStr(NULL, row, "data_group");
+
+        psString outroot = NULL;
+        // set outroot to workdir/exp_id/stage for example /somewhere/424242/chip
+        psStringAppend(&outroot, "%s/%" PRId64 "/%s", workdir, exp_id, stage);
+
+        // create a new magicRun for this group
+        magicDSRunRow *run = magicDSRunRowAlloc(
+                0, // magic_ds_id
+                magic_id,
+                inv_magic_id,
+                "new",
+                stage,
+                stage_id,
+                cam_id,
+                set_label ? set_label : magicRunLabel,
+                set_data_group ? set_data_group : magicRunDataGroup,
+                outroot,
+                recoveryroot,
+                noreplace ? 0 :1,   // re_place
+                0,      // remove
+                0,      // fault
+                note);  // remove
+
+        psFree(outroot);
+        if (!run) {
+            psAbort("failed to alloc magicDSRun object");
+        }
+
+        if (!pretend) {
+            if (!magicDSRunInsertObject(config->dbh, run)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+                psFree(run);
+                psFree(output);
+                psFree(list);
+                if (!psDBRollback(config->dbh)) {
+                    psError(PS_ERR_UNKNOWN, false, "database error");
+                }
+                return false;
+            }
+            psS64 magic_ds_id = psDBLastInsertID(config->dbh); // Assigned identifier
+            run->magic_ds_id = magic_ds_id;
+        }
+
+        psArrayAdd(list, list->n, run);
+        psFree(run);
+    }
+
+    if (!pretend && !psDBCommit(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+    psFree(output);
+
+    if (!magicDSRunPrintObjects(stdout, list, !simple)) {
+        psError(PS_ERR_UNKNOWN, false, "failed to print object");
+        psFree(list);
+        return false;
+    }
+
+    psFree(list);
+
+    return true;
+}
+
+
+static bool updaterunMode(pxConfig *config)
 {
     PS_ASSERT_PTR_NON_NULL(config, false);
 
     // required
-    PXOPT_LOOKUP_S64(magic_id, config->args, "-magic_id", true, false);
+    PXOPT_LOOKUP_STR(state, config->args, "-set_state", true, false);
+
+    // optional
+    PXOPT_LOOKUP_STR(set_label, config->args, "-set_label", false, false);
+    PXOPT_LOOKUP_STR(set_data_group, config->args, "-set_data_group", false, false);
+    psString setString = NULL;
+    if (set_label) {
+        psStringAppend(&setString, ", label = '%s'", set_label);
+    }
+    if (set_data_group) {
+        psStringAppend(&setString, ", data_group = '%s'", set_data_group);
+    }
+
+    PXOPT_LOOKUP_S64(magic_ds_id, config->args, "-magic_ds_id", false, false);
+    if (magic_ds_id) {
+
+        return setmagicDSRunState(config, magic_ds_id, setString, NULL, state);
+
+    } else if (!strcmp(state, "full")) {
+        psError(PS_ERR_UNKNOWN, true, "magic_ds_id is required to update run state to full");
+        return false;
+    }
+    // we can transition by query as well
+
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_STR(config->args, where, "-label", "label", "==");
+    PXOPT_COPY_S64(config->args, where, "-stage_id", "stage_id", "==");
+    PXOPT_COPY_STR(config->args, where, "-stage", "stage", "==");
+    PXOPT_COPY_STR(config->args, where, "-state", "state", "==");
+    PXOPT_COPY_STR(config->args, where, "-data_group", "data_group", "LIKE");
+
+    if (psListLength(where->list) < 2) {
+        psError(PS_ERR_UNKNOWN, true, "at least 2 search arguments are required");
+        return false;
+    }
+
+
+    PXOPT_LOOKUP_BOOL(noreplace, config->args, "-noreplace", false);
+    if (!noreplace) {
+        psMetadataAddS32(where, PS_LIST_TAIL, "re_place", 0, ">", 0);
+    }
+    bool result = setmagicDSRunState(config, magic_ds_id, setString, where, state);
+    psFree(where);
+
+    return result;
+}
+
+
+static bool todestreakMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
     PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);
-    PXOPT_LOOKUP_STR(outroot, config->args, "-outroot", true, false);
-
-    // optional
-    PXOPT_LOOKUP_STR(recoveryroot, config->args, "-recoveryroot", false, false);
-    PXOPT_LOOKUP_BOOL(re_place, config->args, "-replace", false);
-    PXOPT_LOOKUP_BOOL(remove, config->args, "-remove", false);
-    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
-    PXOPT_LOOKUP_STR(label, config->args, "-label", false, false);
-
-    psS64 stage_id = 0, cam_id = 0;
-
-    if (!magicDSGetIDs(config, stage, magic_id, &stage_id, &cam_id)) {
-        psError(PS_ERR_UNKNOWN, false, "failed to get ids");
-        return false;
-    }
-
-    magicDSRunRow *run = magicDSRunRowAlloc(
-            0,          // ID
-            magic_id,
-            "new",      // state
-            stage,
-            stage_id,
-            cam_id,
-            label,
-            outroot,
-            recoveryroot,
-            re_place,
-            remove
-    );
-
-    if (!run) {
-        psError(PS_ERR_UNKNOWN, false, "failed to alloc magicRun object");
-        return false;
-    }
-    if (!magicDSRunInsertObject(config->dbh, run)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        psFree(run);
-        return false;
-    }
-
-    psS64 magic_ds_id = psDBLastInsertID(config->dbh);
-    run->magic_ds_id = magic_ds_id;
-
-    if (!magicDSRunPrintObject(stdout, run, !simple)) {
-            psError(PS_ERR_UNKNOWN, false, "failed to print object");
-            psFree(run);
-            return false;
-    }
-
-    psFree(run);
-
-    return magic_id;
-}
-
-
-static bool updaterunMode(pxConfig *config)
-{
-    PS_ASSERT_PTR_NON_NULL(config, false);
-
-    // required
-    PXOPT_LOOKUP_S64(magic_ds_id, config->args, "-magic_ds_id", true, false);
-    PXOPT_LOOKUP_STR(state, config->args, "-state", true, false);
-
-    if (state) {
-        // set detRun.state to state
-        return setmagicDSRunState(config, magic_ds_id, state);
-    }
-
-    return true;
-}
-
-
-static bool todestreakMode(pxConfig *config)
-{
-    PS_ASSERT_PTR_NON_NULL(config, false);
 
     psMetadata *where = psMetadataAlloc();
-    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magic_ds_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magicDSRun.magic_ds_id", "==");
     PXOPT_COPY_S64(config->args, where, "-magic_id", "magic_id", "==");
-    pxAddLabelSearchArgs (config, where, "-label", "label", "==");
+    pxAddLabelSearchArgs (config, where, "-label", "magicDSRun.label", "==");
 
     PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
     PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
 
-    // look for "inputs" that need to processed
-    psString query = pxDataGet("magicdstool_todestreak.sql");
+    psString sql_file = NULL;
+    psStringAppend(&sql_file, "magicdstool_todestreak_%s.sql", stage);
+
+    psString query = pxDataGet(sql_file);
     if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-        return false;
-    }
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement from %s", sql_file);
+        psFree(sql_file);
+        return false;
+    }
+    psFree(sql_file);
 
     if (psListLength(where->list)) {
         psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-        psStringAppend(&query, " WHERE %s", whereClause);
+        psStringAppend(&query, " AND %s", whereClause);
         psFree(whereClause);
     }
@@ -476,5 +681,5 @@
 {
     // first query the magicDSRun to find the stage and the stage_id
-    psString query = "SELECT stage, stage_id from magicDSRun where magic_ds_id = %" PRId64;
+    psString query = "SELECT stage, stage_id, magic_id from magicDSRun where magic_ds_id = %" PRId64;
 
     if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id)) {
@@ -501,28 +706,35 @@
     psMetadata *row = output->data[0];
 
-    psString stage = psMetadataLookupStr(NULL, row, "stage");
+    psString stage= psMetadataLookupStr(NULL, row, "stage");
     psS64 stage_id = psMetadataLookupS64(NULL, row, "stage_id");
-
-    if (!strcmp(stage, "camera")) {
-        // no there is no magicked column in camProcessedExp
+    psS64 magic_id = psMetadataLookupS64(NULL, row, "magic_id");
+
+    ippStage stageNum = ippStringToStage(stage);
+
+    // chose the appropriate query based on the stage
+    switch (stageNum) {
+    case IPP_STAGE_RAW:
+        query = "UPDATE rawImfile SET magicked = %" PRId64 " where exp_id = %" PRId64 " AND class_id = '%s'";
+        break;
+    case IPP_STAGE_CHIP:
+        query = "UPDATE chipProcessedImfile SET magicked = %" PRId64 " where chip_id = %" PRId64 " AND class_id = '%s'";
+        break;
+    case IPP_STAGE_CAMERA:
+        // no there is no magicked column in camProcessedExp so we have nothing to do
         psFree(output);
         return true;
-    }
-
-    // chose the appropriate query based on the stage
-    if (!strcmp(stage, "raw")) {
-        query = "UPDATE rawImfile SET magicked = %" PRId64 " where exp_id = %" PRId64 " AND class_id = '%s'";
-    } else if (!strcmp(stage, "chip")) {
-        query = "UPDATE chipProcessedImfile SET magicked = %" PRId64 " where chip_id = %" PRId64 " AND class_id = '%s'";
-    } else if (!strcmp(stage, "warp")) {
+    case IPP_STAGE_WARP:
         query = "UPDATE warpSkyfile SET magicked = %" PRId64 " where warp_id = %" PRId64 " AND skycell_id = '%s'";
-    } else if (!strcmp(stage, "diff")) {
+        break;
+    case IPP_STAGE_DIFF:
         query = "UPDATE diffSkyfile SET magicked = %" PRId64 " where diff_id = %" PRId64 " AND skycell_id = '%s'";
-    } else {
+        break;
+    default:
         psError(PS_ERR_UNKNOWN, true, "unexpected value for stage: %s found", stage);
         psFree(output);
         return false;
     }
-    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id, stage_id, component)) {
+
+    if (!p_psDBRunQueryF(config->dbh, query, magic_id, stage_id, component)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
         return false;
@@ -543,5 +755,5 @@
 {
     // first query the magicDSRun to find the stage and the stage_id
-    psString query = "SELECT stage, stage_id from magicDSRun where magic_ds_id = %" PRId64;
+    psString query = "SELECT stage, stage_id, magic_id from magicDSRun where magic_ds_id = %" PRId64;
 
     if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id)) {
@@ -570,23 +782,31 @@
     psString stage = psMetadataLookupStr(NULL, row, "stage");
     psS64 stage_id = psMetadataLookupS64(NULL, row, "stage_id");
-
+    psS64 magic_id = psMetadataLookupS64(NULL, row, "magic_id");
+
+    ippStage stageNum = ippStringToStage(stage);
 
     // chose the appropriate query based on the stage
-    if (!strcmp(stage, "raw")) {
+    switch (stageNum) {
+    case IPP_STAGE_RAW:
         query = "UPDATE rawExp SET magicked = %" PRId64 " where exp_id = %" PRId64;
-    } else if (!strcmp(stage, "chip")) {
+        break;
+    case IPP_STAGE_CHIP:
         query = "UPDATE chipRun SET magicked = %" PRId64 " where chip_id = %" PRId64;
-    } else if (!strcmp(stage, "camera")) {
+        break;
+    case IPP_STAGE_CAMERA:
         query = "UPDATE camRun SET magicked = %" PRId64 " where cam_id = %" PRId64;
-    } else if (!strcmp(stage, "warp")) {
+        break;
+    case IPP_STAGE_WARP:
         query = "UPDATE warpRun SET magicked = %" PRId64 " where warp_id = %" PRId64;
-    } else if (!strcmp(stage, "diff")) {
+        break;
+    case IPP_STAGE_DIFF:
         query = "UPDATE diffRun SET magicked = %" PRId64 " where diff_id = %" PRId64;
-    } else {
+        break;
+    default:
         psError(PS_ERR_UNKNOWN, true, "unexpected value for stage: %s found", stage);
         psFree(output);
         return false;
     }
-    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id, stage_id)) {
+    if (!p_psDBRunQueryF(config->dbh, query, magic_id, stage_id)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
         return false;
@@ -616,4 +836,7 @@
     PXOPT_LOOKUP_STR(recovery_path_base, config->args, "-recovery_path_base", false, false);
     PXOPT_LOOKUP_BOOL(setmagicked, config->args, "-setmagicked", false);
+    PXOPT_LOOKUP_F32(streak_frac, config->args, "-streak_frac", false, false);
+    PXOPT_LOOKUP_F32(nondiff_frac, config->args, "-nondiff_frac", false, false);
+    PXOPT_LOOKUP_F32(run_time, config->args, "-run_time", false, false);
 
     if (setmagicked && (fault != 0)) {
@@ -638,5 +861,15 @@
     }
 
-    if (!magicDSFileInsert(config->dbh, magic_ds_id, component, backup_path_base, recovery_path_base, fault, "full")) {
+    if (!magicDSFileInsert(config->dbh,
+            magic_ds_id,
+            component,
+            backup_path_base,
+            recovery_path_base,
+            streak_frac,
+            nondiff_frac,
+            run_time,
+            fault,
+            "full"  // data_state
+        )) {
             // rollback
         if (!psDBRollback(config->dbh)) {
@@ -647,13 +880,4 @@
     }
 
-    if (!magicDSRunComplete(config, setmagicked)) {
-            // rollback
-        if (!psDBRollback(config->dbh)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-        }
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        return false;
-    }
-
     if (!psDBCommit(config->dbh)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
@@ -664,86 +888,26 @@
 }
 
-static bool magicDSGetIDs(pxConfig *config, psString stage, psS64 magic_id, psS64 *stage_id, psS64 *cam_id)
+
+static bool advancerunMode(pxConfig *config)
 {
     PS_ASSERT_PTR_NON_NULL(config, false);
-    PS_ASSERT_PTR_NON_NULL(stage, false);
-    PS_ASSERT_PTR_NON_NULL(stage_id, false);
-    PS_ASSERT_PTR_NON_NULL(cam_id, false);
-
-    if (!strcmp(stage, "diff")) {
-        // don't need these ids for diff stage because diff_id is in the magicRun
-        *stage_id = 0;
-        *cam_id = 0;
-        return true;
-    }
-
-    int stageNum;
-    if (!strcmp(stage, "raw")) {
-        stageNum = 0;
-    } else if (!strcmp(stage, "chip")) {
-        stageNum = 1;
-    } else if (!strcmp(stage, "camera")) {
-        stageNum = 2;
-    } else if (!strcmp(stage, "warp")) {
-        stageNum = 3;
-    } else {
-        psError(PXTOOLS_ERR_DATA, true, "%s is not a valid value for stage", stage);
-        return false;
-    }
-
-
-    psString query = pxDataGet("magicdstool_getrunids.sql");
-    if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-        return false;
-    }
-
-    if (!p_psDBRunQueryF(config->dbh, query, magic_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("magicdstool", PS_LOG_INFO, "no rows found");
-        psFree(output);
-        return true;
-    }
-    if (psArrayLength(output) > 1) {
-        psError(PS_ERR_UNKNOWN, true, "unexpected number of rows found %ld for magic_id %" PRId64,
-            psArrayLength(output), magic_id);
-        return false;
-    }
-    psMetadata *row = output->data[0];
-
-    *cam_id = psMetadataLookupS64(NULL, row, "cam_id");
-    if (stageNum == 0) {
-        *stage_id = psMetadataLookupS64(NULL, row, "exp_id");
-    } else if (stageNum == 1) {
-        *stage_id = psMetadataLookupS64(NULL, row, "chip_id");
-    } else if (stageNum == 2) {
-        *stage_id = *cam_id;
-    } else if (stageNum == 3) {
-        *stage_id = psMetadataLookupS64(NULL, row, "warp_id");
-    }
-
-    return true;
-}
-
-static bool magicDSRunComplete(pxConfig *config, bool setmagicked)
-{
-    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magicDSRun.magic_ds_id", "==");
+    pxAddLabelSearchArgs (config, where, "-label", "magicDSRun.label", "==");
+
+    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
 
     // look for completed magicDSRuns
     psString query = pxDataGet("magicdstool_completed_runs.sql");
     if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-        return false;
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    if (psListLength(where->list)) {
+        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+        psStringAppend(&query, " WHERE %s", whereClause);
+        psFree(whereClause);
     }
 
@@ -765,4 +929,8 @@
         return true;
     }
+    if (!psDBTransaction(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
     for (long i = 0; i < psArrayLength(output); i++) {
         psMetadata *row = output->data[i];
@@ -770,20 +938,30 @@
         psS64 magic_ds_id = psMetadataLookupS64(NULL, row, "magic_ds_id");
 
-        // if requested, set stageRun.magicked
+        // if re_place, set stageRun.magicked
+        bool setmagicked = psMetadataLookupBool(NULL, row, "re_place");
         if (setmagicked && !setRunMagicked(config, magic_ds_id)) {
             psError(PS_ERR_UNKNOWN, false, "failed to change stageRun.magicked for magic_ds_id: %" PRId64,
                 magic_ds_id);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+            }
             return false;
         }
 
         // set magicDSRun.state to 'full'
-        if (!setmagicDSRunState(config, magic_ds_id, "full")) {
+        if (!setmagicDSRunState(config, magic_ds_id, NULL, NULL, "full")) {
             psError(PS_ERR_UNKNOWN, false, "failed to change magicDSRun.state for magic_ds_id: %" PRId64,
                 magic_ds_id);
             psFree(output);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+            }
             return false;
         }
     }
-
+    if (!psDBCommit(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
 
     return true;
@@ -794,4 +972,10 @@
 {
     PS_ASSERT_PTR_NON_NULL(config, false);
+
+    PXOPT_LOOKUP_BOOL(i_am_sure, config->args, "-i_am_sure", true);
+    if (!i_am_sure) {
+        psError(PS_ERR_UNKNOWN, true, "Reverting destreaked files must be done carefully. -i_am_sure is required.");
+        return false;
+    }
 
     psMetadata *where = psMetadataAlloc();
@@ -801,5 +985,5 @@
     pxAddLabelSearchArgs (config, where, "-label", "label", "==");
 
-    psString query = psStringCopy("DELETE FROM magicDSFile USING magicDSFile, magicDSRun  WHERE (magicDSRun.magic_ds_id = magicDSFile.magic_ds_id) AND magicDSFile.fault != 0");
+    psString query = pxDataGet("magicdstool_revertdestreakedfile.sql");
 
     if (psListLength(where->list)) {
@@ -807,4 +991,7 @@
         psStringAppend(&query, " AND %s", whereClause);
         psFree(whereClause);
+    } else {
+        psError(PS_ERR_UNKNOWN, true, "search arguments are required");
+        return false;
     }
     psFree(where);
@@ -812,40 +999,89 @@
     if (!p_psDBRunQuery(config->dbh, query)) {
         psError(PS_ERR_UNKNOWN, false, "failed to revert");
-        return false;
-    }
+        psFree(query);
+        return false;
+    }
+    psFree(query);
     return true;
 }
-
-static bool getskycellsMode(pxConfig *config)
-{
-    // required
-    PXOPT_LOOKUP_S64(magic_ds_id, config->args, "-magic_ds_id", true, false);
+static bool clearstatefaultsMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
 
     psMetadata *where = psMetadataAlloc();
-    PXOPT_COPY_STR(config->args, where, "-class_id",    "warpSkyCellMap.class_id", "==");
-    PXOPT_COPY_STR(config->args, where, "-skycell_id",  "warpSkyCellMap.skycell_id", "==");
-
-    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
-
-    psString query = pxDataGet("magicdstool_getskycells.sql");
-    if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-        return false;
-    }
+    // new state
+    PXOPT_LOOKUP_STR(new_state, config->args, "-set_state", false, false);
+    // old state (required)
+    PXOPT_LOOKUP_STR(state, config->args, "-state", true, false);
+
+    PXOPT_COPY_STR(config->args, where, "-state", "state", "==");
+    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magicDSRun.magic_ds_id", "==");
+    PXOPT_COPY_S16(config->args, where, "-fault", "fault", "==");
+    pxAddLabelSearchArgs (config, where, "-label", "label", "==");
+
+    psString query = pxDataGet("magicdstool_clearstatefaults.sql");
 
     if (psListLength(where->list)) {
         psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-        psStringAppend(&query, " AND %s", whereClause);
+        psStringAppend(&query, " WHERE %s", whereClause);
         psFree(whereClause);
+    } else {
+        psError(PS_ERR_UNKNOWN, true, "search arguments are required");
+        return false;
     }
     psFree(where);
 
-    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
+    if (!new_state) {
+        if (!strcmp(state, "failed_revert")) {
+            new_state = "new";
+        } else if (!strcmp(state, "failed_cleanup")) {
+            new_state = "goto_cleaned";
+        } else {
+            psError(PS_ERR_UNKNOWN, true, "unexpected value for state: %s", state);
+            return false;
+        }
+    }
+    if (!p_psDBRunQueryF(config->dbh, query, new_state)) {
+        psError(PS_ERR_UNKNOWN, false, "failed to revert");
         psFree(query);
         return false;
     }
     psFree(query);
-
+    return true;
+}
+
+static bool completedrevertMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magicDSRun.magic_ds_id", "==");
+    pxAddLabelSearchArgs (config, where, "-label", "label", "==");
+
+    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
+
+    psString query = pxDataGet("magicdstool_completedrevert.sql");
+    // treat limit == 0 as "no limit"
+    if (limit) {
+        psString limitString = psDBGenerateLimitSQL(limit);
+        psStringAppend(&query, " %s", limitString);
+        psFree(limitString);
+    }
+
+    psString whereString = NULL;
+    if (psListLength(where->list)) {
+        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+        psStringAppend(&whereString, "\nAND %s", whereClause);
+        psFree(whereClause);
+    }
+    psFree(where);
+
+    if (!p_psDBRunQueryF(config->dbh, query, whereString ? whereString : "")) {
+        psFree(whereString);
+        psError(PS_ERR_UNKNOWN, false, "failed to revert");
+        return false;
+    }
+    psFree(whereString);
+    psFree(query);
     psArray *output = p_psDBFetchResult(config->dbh);
     if (!output) {
@@ -867,14 +1103,24 @@
         return true;
     }
-
-    if (psArrayLength(output)) {
-        // negative simple so the default is true
-        if (!ippdbPrintMetadatas(stdout, output, "magicDiffSkyfile", !simple)) {
-            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+    for (int i=0; i<psArrayLength(output); i++) {
+        psMetadata *row = output->data[i];
+        psS64 magic_ds_id = psMetadataLookupS64(NULL, row, "magic_ds_id");
+        psString old_state = psMetadataLookupStr(NULL, row, "state");
+        psString new_state;
+        if (!strcmp(old_state, "goto_censored")) {
+            new_state = "censored";
+        } else if (!strcmp(old_state, "goto_restored")) {
+            new_state = "restored";
+        } else {
+            psError(PXTOOLS_ERR_PROG, true, "unexpected state found: %s", old_state);
             psFree(output);
             return false;
         }
-    }
-
+        char *query2 = "UPDATE magicDSRun SET state = '%s' WHERE magic_ds_id = %" PRId64;
+        if (!p_psDBRunQueryF(config->dbh, query2, new_state, magic_ds_id)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to set run magicDSRun.state to %s", new_state);
+            return false;
+        }
+    }
     psFree(output);
 
@@ -882,63 +1128,30 @@
 }
 
-static bool setmagicDSRunState(pxConfig *config, psS64 magic_ds_id, const char *state)
-{
-    PS_ASSERT_PTR_NON_NULL(state, false);
-
-    // check that state is a valid string value
-    if (!(
-            (strncmp(state, "new", 4) == 0)
-            || (strncmp(state, "full", 5) == 0)
-        )
-    ) {
-        psError(PS_ERR_UNKNOWN, false,
-                "invalid magicDSRun state: %s", state);
-        return false;
-    }
-
-    char *query = "UPDATE magicDSRun SET state = '%s' WHERE magic_ds_id = %" PRId64;
-    if (!p_psDBRunQueryF(config->dbh, query, state, magic_ds_id)) {
-        psError(PS_ERR_UNKNOWN, false,
-                "failed to change state for magic_id %" PRId64, magic_ds_id);
-        return false;
-    }
-
-    return true;
-}
-
-static bool toremoveMode(pxConfig *config)
-{
-    PS_ASSERT_PTR_NON_NULL(config, false);
+static bool getskycellsMode(pxConfig *config)
+{
+    // required
+    PXOPT_LOOKUP_S64(magic_ds_id, config->args, "-magic_ds_id", true, false);
 
     psMetadata *where = psMetadataAlloc();
-    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magic_ds_id", "==");
-    PXOPT_COPY_S64(config->args, where, "-magic_id", "magic_id", "==");
-    pxAddLabelSearchArgs (config, where, "-label", "label", "==");
-
-    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
+    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magicDSRun.magic_ds_id", "==");
+    PXOPT_COPY_STR(config->args, where, "-class_id",    "warpSkyCellMap.class_id", "==");
+    PXOPT_COPY_STR(config->args, where, "-skycell_id",  "warpSkyCellMap.skycell_id", "==");
+
     PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
 
-    // look for "inputs" that need to processed
-    psString query = pxDataGet("magicdstool_toremove.sql");
+    psString query = pxDataGet("magicdstool_getskycells.sql");
     if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
-        return false;
-    }
-
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    psString whereClause = NULL;
     if (psListLength(where->list)) {
-        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-        psStringAppend(&query, " WHERE %s", whereClause);
-        psFree(whereClause);
+        whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+        psStringPrepend(&whereClause, "\n AND ");
     }
     psFree(where);
 
-    // treat limit == 0 as "no limit"
-    if (limit) {
-        psString limitString = psDBGenerateLimitSQL(limit);
-        psStringAppend(&query, " %s", limitString);
-        psFree(limitString);
-    }
-
-    if (!p_psDBRunQuery(config->dbh, query)) {
+    if (!p_psDBRunQueryF(config->dbh, query, whereClause, whereClause)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
         psFree(query);
@@ -969,5 +1182,5 @@
     if (psArrayLength(output)) {
         // negative simple so the default is true
-        if (!ippdbPrintMetadatas(stdout, output, "toremove", !simple)) {
+        if (!ippdbPrintMetadatas(stdout, output, "magicDiffSkyfile", !simple)) {
             psError(PS_ERR_UNKNOWN, false, "failed to print array");
             psFree(output);
@@ -980,5 +1193,66 @@
     return true;
 }
-static bool torestoreMode(pxConfig *config)
+
+static bool validDSRunState(const char *state)
+{
+    if (!((strcmp(state, "new") == 0) ||
+          (strcmp(state, "full") == 0) ||
+          (strcmp(state, "drop") == 0) ||
+          (strcmp(state, "failed_revert") == 0) ||
+          (strcmp(state, "failed_cleanup") == 0) ||
+          (strcmp(state, "restored") == 0) ||
+          (strcmp(state, "censored") == 0) ||
+          (strcmp(state, "cleaned") == 0) ||
+          (strcmp(state, "goto_restored") == 0) ||
+          (strcmp(state, "goto_censored") == 0) ||
+          (strcmp(state, "goto_cleaned") == 0))
+        ) {
+        return false;
+    } else {
+        return true;
+    }
+}
+
+static bool setmagicDSRunState(pxConfig *config, psS64 magic_ds_id, psString extraSetStr, psMetadata *where, const char *state)
+{
+    PS_ASSERT_PTR_NON_NULL(state, false);
+
+    if (!validDSRunState(state)) {
+        psError(PS_ERR_UNKNOWN, false,
+                "invalid magicDSRun state: %s", state);
+        return false;
+    }
+
+    psString query = NULL;
+    psStringAppend(&query, "UPDATE magicDSRun SET state = '%s' %s\n", state, extraSetStr ? extraSetStr : "");
+    if (magic_ds_id) {
+        psStringAppend(&query, " WHERE magic_ds_id = %" PRId64, magic_ds_id);
+    } else if (where && psListLength(where->list)) {
+        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+        psStringAppend(&query, " WHERE %s", whereClause);
+        psFree(whereClause);
+    } else {
+        psError(PS_ERR_UNKNOWN, true, "search arguments are required");
+        return false;
+    }
+
+    if (!strcmp(state, "goto_cleaned")) {
+        // Don't set state back to goto_cleaned if it is already cleaned
+        psStringAppend(&query, " AND (magicDSRun.state != 'cleaned')");
+
+        // don't clean up magicDSRun's where stage is camera
+        psStringAppend(&query, " AND (magicDSRun.stage != 'camera')");
+    }
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false,
+                "failed to change state for magic_id %" PRId64, magic_ds_id);
+        return false;
+    }
+
+    return true;
+}
+
+static bool toremoveMode(pxConfig *config)
 {
     PS_ASSERT_PTR_NON_NULL(config, false);
@@ -993,7 +1267,7 @@
 
     // look for "inputs" that need to processed
-    psString query = pxDataGet("magicdstool_torestore.sql");
+    psString query = pxDataGet("magicdstool_toremove.sql");
     if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
         return false;
     }
@@ -1042,5 +1316,5 @@
     if (psArrayLength(output)) {
         // negative simple so the default is true
-        if (!ippdbPrintMetadatas(stdout, output, "torestore", !simple)) {
+        if (!ippdbPrintMetadatas(stdout, output, "toremove", !simple)) {
             psError(PS_ERR_UNKNOWN, false, "failed to print array");
             psFree(output);
@@ -1054,5 +1328,4 @@
 }
 
-
 static bool torevertMode(pxConfig *config)
 {
@@ -1074,5 +1347,5 @@
     psString query = pxDataGet(sql_file);
     if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement from %s", sql_file);
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement from %s", sql_file);
         psFree(sql_file);
         return false;
@@ -1136,2 +1409,76 @@
 
 
+static bool tocleanupMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    psMetadata *where = psMetadataAlloc();
+
+    PXOPT_COPY_STR(config->args, where, "-stage", "stage", "==");
+    PXOPT_COPY_S64(config->args, where, "-magic_ds_id", "magic_ds_id", "==");
+    PXOPT_COPY_S64(config->args, where, "-magic_id", "magic_id", "==");
+    pxAddLabelSearchArgs (config, where, "-label", "magicDSRun.label", "==");
+
+    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
+    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+
+    psString query = pxDataGet("magicdstool_tocleanup.sql");
+    if (!query) {
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    if (psListLength(where->list)) {
+        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+        psStringAppend(&query, " AND %s", whereClause);
+        psFree(whereClause);
+    }
+    psFree(where);
+
+    // treat limit == 0 as "no limit"
+    if (limit) {
+        psString limitString = psDBGenerateLimitSQL(limit);
+        psStringAppend(&query, " %s", limitString);
+        psFree(limitString);
+    }
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(query);
+        return false;
+    }
+    psFree(query);
+
+    psArray *output = p_psDBFetchResult(config->dbh);
+    if (!output) {
+        psErrorCode err = psErrorCodeLast();
+        switch (err) {
+            case PS_ERR_DB_CLIENT:
+                psError(PXTOOLS_ERR_SYS, false, "database error");
+            case PS_ERR_DB_SERVER:
+                psError(PXTOOLS_ERR_PROG, false, "database error");
+            default:
+                psError(PXTOOLS_ERR_PROG, false, "unknown error");
+        }
+
+        return false;
+    }
+    if (!psArrayLength(output)) {
+        psTrace("magicdstool", PS_LOG_INFO, "no rows found");
+        psFree(output);
+        return true;
+    }
+
+    if (psArrayLength(output)) {
+        // negative simple so the default is true
+        if (!ippdbPrintMetadatas(stdout, output, "tocleanup", !simple)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+            psFree(output);
+            return false;
+        }
+    }
+
+    psFree(output);
+
+    return true;
+}