Index: trunk/ippTools/src/magicdstool.c
===================================================================
--- trunk/ippTools/src/magicdstool.c	(revision 25015)
+++ trunk/ippTools/src/magicdstool.c	(revision 25419)
@@ -42,4 +42,5 @@
 static bool torestoreMode(pxConfig *config);
 static bool torevertMode(pxConfig *config);
+static bool completedrevertMode(pxConfig *config);
 
 static bool setmagicDSRunState(pxConfig *config, psS64 magic_id, const char *state);
@@ -75,4 +76,5 @@
         MODECASE(MAGICDSTOOL_MODE_TORESTORE,           torestoreMode);
         MODECASE(MAGICDSTOOL_MODE_TOREVERT,            torevertMode);
+        MODECASE(MAGICDSTOOL_MODE_COMPLETEDREVERT,     completedrevertMode);
         default:
             psAbort("invalid option (this should not happen)");
@@ -396,23 +398,28 @@
     PS_ASSERT_PTR_NON_NULL(config, false);
 
+    PXOPT_LOOKUP_STR(stage, config->args, "-stage", 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_COPY_STR(config->args, where, "-stage", "stage", "==");
+    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_DATA, 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);
     }
@@ -817,36 +824,37 @@
     return true;
 }
-
-static bool getskycellsMode(pxConfig *config)
-{
-    // required
-    PXOPT_LOOKUP_S64(magic_ds_id, config->args, "-magic_ds_id", true, false);
+static bool completedrevertMode(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;
-    }
-
+    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(&query, " AND %s", whereClause);
+        psStringAppend(&whereString, "\nAND %s", whereClause);
         psFree(whereClause);
     }
     psFree(where);
 
-    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        psFree(query);
-        return false;
-    }
+    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) {
@@ -868,14 +876,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, magic_ds_id, new_state)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to set run magicDSRun.state to %s", new_state);
+            return false;
+        }
+    }
     psFree(output);
 
@@ -883,43 +901,16 @@
 }
 
-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_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");
@@ -929,17 +920,10 @@
     if (psListLength(where->list)) {
         psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-        psStringAppend(&query, " WHERE %s", whereClause);
+        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)) {
+    if (!p_psDBRunQueryF(config->dbh, query, magic_ds_id)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
         psFree(query);
@@ -970,5 +954,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);
@@ -981,5 +965,31 @@
     return true;
 }
-static bool torestoreMode(pxConfig *config)
+
+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);
@@ -994,5 +1004,5 @@
 
     // 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");
@@ -1043,5 +1053,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,34 +1064,26 @@
     return true;
 }
-
-
-static bool torevertMode(pxConfig *config)
+static bool torestoreMode(pxConfig *config)
 {
     PS_ASSERT_PTR_NON_NULL(config, false);
 
     psMetadata *where = psMetadataAlloc();
-    PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);
-
     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", "==");
+    pxAddLabelSearchArgs (config, where, "-label", "label", "==");
 
     PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
     PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
 
-    psString sql_file = NULL;
-    psStringAppend(&sql_file, "magicdstool_torevert_%s.sql", stage);
-
-    psString query = pxDataGet(sql_file);
+    // look for "inputs" that need to processed
+    psString query = pxDataGet("magicdstool_torestore.sql");
     if (!query) {
-        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement from %s", sql_file);
-        psFree(sql_file);
-        return false;
-    }
-    psFree(sql_file);
+        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
+        return false;
+    }
 
     if (psListLength(where->list)) {
         psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-        psStringAppend(&query, " AND %s", whereClause);
+        psStringAppend(&query, " WHERE %s", whereClause);
         psFree(whereClause);
     }
@@ -1124,4 +1126,85 @@
     if (psArrayLength(output)) {
         // negative simple so the default is true
+        if (!ippdbPrintMetadatas(stdout, output, "torestore", !simple)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+            psFree(output);
+            return false;
+        }
+    }
+
+    psFree(output);
+
+    return true;
+}
+
+
+static bool torevertMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_LOOKUP_STR(stage, config->args, "-stage", true, false);
+
+    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 sql_file = NULL;
+    psStringAppend(&sql_file, "magicdstool_torevert_%s.sql", stage);
+
+    psString query = pxDataGet(sql_file);
+    if (!query) {
+        psError(PXTOOLS_ERR_DATA, 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, " 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, "torevert", !simple)) {
             psError(PS_ERR_UNKNOWN, false, "failed to print array");