Index: trunk/ippTools/src/staticskytool.c
===================================================================
--- trunk/ippTools/src/staticskytool.c	(revision 31690)
+++ trunk/ippTools/src/staticskytool.c	(revision 32960)
@@ -42,4 +42,8 @@
 static bool importrunMode(pxConfig *config);
 
+static bool defineskycalrunMode(pxConfig *config);
+static bool updateskycalrunMode(pxConfig *config);
+static bool pendingskycalrunMode(pxConfig *config);
+
 static bool setstaticskyRunState(pxConfig *config, psS64 sky_id, const char *state);
 
@@ -72,4 +76,7 @@
         MODECASE(STATICSKYTOOL_MODE_EXPORTRUN,         exportrunMode);
         MODECASE(STATICSKYTOOL_MODE_IMPORTRUN,         importrunMode);
+        MODECASE(STATICSKYTOOL_MODE_DEFINESKYCALRUN,   defineskycalrunMode);
+        MODECASE(STATICSKYTOOL_MODE_UPDATESKYCALRUN,   updateskycalrunMode);
+        MODECASE(STATICSKYTOOL_MODE_PENDINGSKYCALRUN,  pendingskycalrunMode);
         default:
             psAbort("invalid option (this should not happen)");
@@ -97,10 +104,8 @@
 {
     PS_ASSERT_PTR_NON_NULL(config, false);
-
-    // required options
-    PXOPT_LOOKUP_STR(workdir, config->args, "-set_workdir", true, false);
+    PXOPT_LOOKUP_STR(workdir,     config->args, "-set_workdir", true, false);
+    PXOPT_LOOKUP_STR(label,       config->args, "-set_label", true, false);
 
     // optional
-    PXOPT_LOOKUP_STR(label,       config->args, "-set_label", true, false);
     PXOPT_LOOKUP_STR(data_group,  config->args, "-set_data_group", false, false);
     PXOPT_LOOKUP_STR(dist_group,  config->args, "-set_dist_group", false, false);
@@ -111,4 +116,5 @@
     psMetadata *whereMD = psMetadataAlloc();
 
+    PXOPT_COPY_S64(config->args, whereMD, "-select_stack_id",      "stackRun.stack_id",         "==");
     PXOPT_COPY_STR(config->args, whereMD, "-select_skycell_id",    "stackRun.skycell_id",       "==");
     PXOPT_COPY_STR(config->args, whereMD, "-select_tess_id",       "stackRun.tess_id",          "==");
@@ -978,2 +984,238 @@
 # endif
 
+static bool defineskycalrunMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    // required options
+    // none required. We get workdir, etc from staticskyRun if not provided
+
+    // optional
+    PXOPT_LOOKUP_STR(label,       config->args, "-set_label", false, false);
+    PXOPT_LOOKUP_STR(workdir,     config->args, "-set_workdir", false, false);
+    PXOPT_LOOKUP_STR(data_group,  config->args, "-set_data_group", false, false);
+    PXOPT_LOOKUP_STR(dist_group,  config->args, "-set_dist_group", false, false);
+    PXOPT_LOOKUP_STR(reduction,   config->args, "-set_reduction", false, false);
+    PXOPT_LOOKUP_STR(note,        config->args, "-set_note", false, false);
+    PXOPT_LOOKUP_TIME(registered, config->args, "-set_registered", false, false);
+
+    psMetadata *whereMD = psMetadataAlloc();
+
+    PXOPT_COPY_S64(config->args, whereMD, "-select_sky_id",        "staticskyRun.sky_id",       "==");
+    PXOPT_COPY_S64(config->args, whereMD, "-select_stack_id",      "stackRun.stack_id",         "==");
+    PXOPT_COPY_STR(config->args, whereMD, "-select_skycell_id",    "stackRun.skycell_id",       "==");
+    PXOPT_COPY_STR(config->args, whereMD, "-select_tess_id",       "stackRun.tess_id",          "==");
+    PXOPT_COPY_F32(config->args, whereMD, "-select_good_frac_min", "stackSumSkyfile.good_frac", ">=");
+    pxAddLabelSearchArgs(config, whereMD, "-select_label",         "stackRun.label",            "LIKE");
+    pxAddLabelSearchArgs(config, whereMD, "-select_data_group",    "stackRun.data_group",       "LIKE");
+    pxAddLabelSearchArgs(config, whereMD, "-select_filter",        "stackRun.filter",           "LIKE");
+
+    PXOPT_LOOKUP_BOOL(rerun, config->args, "-rerun", false);
+    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+    PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
+
+    psString query = pxDataGet("staticskytool_defineskycalrun.sql");
+    if (!query) {
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        psFree(whereMD);
+        return false;
+    }
+
+    if (!psListLength(whereMD->list)) {
+        psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required");
+        psFree(whereMD);
+        return false;
+    }
+
+    psString whereClause = psDBGenerateWhereConditionSQL(whereMD, NULL);
+    psStringAppend(&query, "\nAND %s", whereClause);
+    psFree(whereClause);
+    psFree(whereMD);
+
+    if (!rerun) {
+        if (label)  {
+            psStringAppend(&query, "\nAND (skycalRun.label IS NULL OR skycalRun.label = '%s')", label);
+        }
+        psStringAppend(&query, "\nAND skycal_id IS NULL");
+    }
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(query);
+        return false;
+    }
+    psFree(query);
+
+    // we now have a list of (tess_id, skycell_id) that (potentially) meet out needs
+    // we now need to loop over all of these and for each pair, select the best set of
+    // inputs
+
+    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)) {
+        psWarning("staticskytool: no rows found");
+        psFree(output);
+        return true;
+    }
+    if (pretend) {
+        // negative simple so the default is true
+        if (!ippdbPrintMetadatas(stdout, output, "skycalRun", !simple)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+            psFree(output);
+            return false;
+        }
+        psFree(output);
+        return true;
+    }
+
+    for (long i = 0; i < output->n; i++) {
+        psMetadata *row = output->data[i]; // Row from select
+        bool status;
+
+        psS64 sky_id = psMetadataLookupS64(&status, row, "sky_id");
+        psS64 stack_id = psMetadataLookupS64(&status, row, "stack_id");
+        psString sky_workdir = psMetadataLookupStr(&status, row, "workdir");
+        psString sky_label =  psMetadataLookupStr(&status, row, "label");
+        psString sky_data_group =  psMetadataLookupStr(&status, row, "data_group");
+
+	// create a staticskyRun
+	if (!skycalRunInsert(config->dbh,
+				0x0,	     // skycal_id
+                                sky_id,
+                                stack_id,
+				"new",	     // state
+				workdir ? workdir : sky_workdir,
+				label ? label : sky_label,
+				data_group ? data_group : sky_data_group,
+				dist_group,
+				reduction,
+				registered,
+				note
+		)
+	    ) {
+	    psError(PS_ERR_UNKNOWN, false, "database error");
+            psFree(output);
+	    return false;
+	}
+
+    }
+    psFree(output);
+    return true;
+}
+
+static bool updateskycalrunMode(pxConfig *config)
+{
+#ifdef notyet
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, where, "-sky_id",  "sky_id",   "==");
+    PXOPT_COPY_STR(config->args, where, "-label",   "label",    "==");
+    PXOPT_COPY_STR(config->args, where, "-state",   "state",    "==");
+    if (!psListLength(where->list)) {
+        psFree(where);
+        psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required");
+        return false;
+    }
+
+    psString query = psStringCopy("UPDATE staticskyRun");
+
+    // pxUpdateRun gets parameters from config->args and updates
+    bool result = pxUpdateRun(config, where, &query, "staticskyRun", "sky_id", "staticskyResult", true, false);
+    psFree(query);
+    psFree(where);
+
+    return result;
+#endif
+    return false;
+}
+static bool pendingskycalrunMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    psMetadata *whereMD = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, whereMD, "-skycal_id", "skycal_id", "==");
+    PXOPT_COPY_S64(config->args, whereMD, "-sky_id", "sky_id", "==");
+    pxAddLabelSearchArgs (config, whereMD, "-label", "skycalRun.label", "==");
+    PXOPT_COPY_STR(config->args, whereMD, "-filter", "stackRun.filter", "LIKE");
+
+    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
+    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+
+    psString query = pxDataGet("staticskytool_pendingskycalrun.sql");
+    if (!query) {
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    if (!psListLength(whereMD->list)) {
+        psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required");
+        psFree(whereMD);
+        return false;
+    }
+
+    psString whereClause = psDBGenerateWhereConditionSQL(whereMD, NULL);
+    psStringAppend(&query, "\n AND %s", whereClause);
+    psFree(whereClause);
+    psFree(whereMD);
+
+//    psStringAppend(&query, "\n GROUP by skycal_id");
+
+    // treat limit == 0 as "no limit"
+    if (limit) {
+        psString limitString = psDBGenerateLimitSQL(limit);
+        psStringAppend(&query, "\n%s", limitString);
+        psFree(limitString);
+    }
+
+    // the where clause is required and matches the WHERE hook format string
+    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("staticskytool", PS_LOG_INFO, "no rows found");
+        psFree(output);
+        return true;
+    }
+
+    if (psArrayLength(output)) {
+        // negative simple so the default is true
+        if (!ippdbPrintMetadatas(stdout, output, "pendingskycalRun", !simple)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+            psFree(output);
+            return false;
+        }
+    }
+
+    psFree(output);
+    return true;
+}