Index: trunk/ippTools/src/stacktool.c
===================================================================
--- trunk/ippTools/src/stacktool.c	(revision 18561)
+++ trunk/ippTools/src/stacktool.c	(revision 18574)
@@ -101,4 +101,5 @@
     psMetadata *whereRE = psMetadataAlloc(); // rawExp where
     psMetadata *whereWSF = psMetadataAlloc(); // warpSkyfile where
+    psMetadata *having = psMetadataAlloc(); // having
     // map -inst -> camera, etc
     PXOPT_COPY_STR(config->args, whereRE, "-select_inst", "camera", "==");
@@ -119,7 +120,13 @@
     PXOPT_COPY_F32(config->args, whereRE, "-select_solang_min", "solang", ">=");
     PXOPT_COPY_F32(config->args, whereRE, "-select_solang_max", "solang", "<=");
-    PXOPT_COPY_STR(config->args, whereWSF, "-select_skycell_id", "skycell_id", "==");
-    PXOPT_LOOKUP_BOOL(randomSubset, config->args, "-random_subset", false);
-    PXOPT_LOOKUP_S32(randomLimit, config->args, "-random_limit", false, false);
+
+    PXOPT_COPY_STR(config->args, having, "-select_skycell_id", "skycell_id", "==");
+
+    PXOPT_LOOKUP_S32(randomLimit, config->args, "-random", false, false);
+
+    PXOPT_COPY_S32(config->args, having, "-min_num", "num_avail", ">=");
+    PXOPT_COPY_S32(config->args, having, "-min_new", "(num_avail - num_extant)", ">=");
+
+    PXOPT_LOOKUP_F32(min_frac, config->args, "-min_frac", false, false);
 
     if (!psListLength(whereRE->list) && !psListLength(whereWSF->list) &&
@@ -137,30 +144,56 @@
     PXOPT_COPY_F32(config->args, whereWSF, "-select_good_frac_min", "good_frac", ">=");
 
-    psString query = pxDataGet("stacktool_find_complete_warps.sql");
-    if (!query) {
+    psString select = pxDataGet("stacktool_definebyquery_select.sql");
+    if (!select) {
         psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
         return false;
     }
 
-    if (whereRE) {
+    if (psListLength(whereRE->list)) {
         psString whereClause = psDBGenerateWhereConditionSQL(whereRE, "rawExp");
-        psStringAppend(&query, " AND %s", whereClause);
+        psStringAppend(&select, " AND %s", whereClause);
         psFree(whereClause);
     }
     psFree(whereRE);
 
-    if (whereWSF) {
+    if (psListLength(whereWSF->list)) {
         psString whereClause = psDBGenerateWhereConditionSQL(whereWSF, "warpSkyfile");
-        psStringAppend(&query, " AND %s", whereClause);
+        psStringAppend(&select, " AND %s", whereClause);
         psFree(whereClause);
     }
     psFree(whereWSF);
 
-    if (!p_psDBRunQuery(config->dbh, query)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        psFree(query);
-        return false;
-    }
-    psFree(query);
+    psString groupby = pxDataGet("stacktool_definebyquery_groupby.sql");
+    if (!groupby) {
+        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
+        return false;
+    }
+    psStringAppend(&select, " %s", groupby);
+    psFree(groupby);
+
+    if (psListLength(having->list)) {
+        psString havingClause = psDBGenerateWhereConditionSQL(having, NULL);
+        psStringAppend(&select, " HAVING %s", havingClause);
+        psFree(havingClause);
+    }
+
+    // Gotta handle this one explicitly --- want to avoid divisions
+    if (isfinite(min_frac)) {
+        if (psListLength(having->list)) {
+            psStringAppend(&select, " AND");
+        } else {
+            psStringAppend(&select, " HAVING");
+        }
+        psStringAppend(&select, " num_avail >= %f * num_extant", (double)min_frac);
+    }
+
+    psFree(having);
+
+    if (!p_psDBRunQuery(config->dbh, select)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(select);
+        return false;
+    }
+    psFree(select);
 
     psArray *output = p_psDBFetchResult(config->dbh);
@@ -184,146 +217,60 @@
     }
 
-    // valid_warpids should be NULL unless randomSubset is specified
-    psVector *valid_warpids = NULL;
-    if (randomSubset && (randomLimit < psArrayLength(output))) {
-        // loop over the array of metadata and figure out which warp_id are in
-        // the result set
-        psArray *warps = psArrayAlloc(0);
-        for (long i = 0; i < psArrayLength(output); i++) {
-//            psMetadata *md = output->data[i];
-
-            // pull out the warp_id
-            bool status = false;
-//            psS64 warp_id = psMetadataLookupS64(&status, md, "warp_id");
-            if (!status) {
-                psError(PS_ERR_UNKNOWN, false, "failed to lookup warp_id");
-                psFree(output);
-                psFree(warps);
-                return NULL;
-            }
-
-//            psArrayAdd(warps, 0, warp_id);
-        }
-
-        // generate a random-valued vector, return an index sorted by the
-        // random values
-        psVector *randomVector = psVectorAlloc(psArrayLength(output), PS_TYPE_F32);
-        psRandom *rng = psRandomAlloc(PS_RANDOM_TAUS, 0);
-        for (int i = 0; i < randomVector->n; i++) {
-            randomVector->data.F32[i] = psRandomUniform(rng);
-        }
-        psVector *indexVector = psVectorSortIndex(NULL, randomVector);
-        psFree(randomVector);
-
-        // accept for first n of the sequence
-        valid_warpids = psVectorAlloc(randomLimit, PS_TYPE_S64);
-        for (int i = 0; i < randomLimit; i++ ){
-//            int j = indexVector->data.S32[i];
-//            valid_warpids->data[i] = psMemIncrRefCounter (warps->data[j]);
-        }
-        psFree(indexVector);
-        psFree(warps);
-    }
-
-
-    psHash *stacks = psHashAlloc(psArrayLength(output));
-
-    // loop over the array of metadata and build a hash keyed by tess_id &
-    // skycell_id
-    for (long i = 0; i < psArrayLength(output); i++) {
-        psMetadata *md = output->data[i];
-
+    psString insert = NULL;             // Insertion query
+    if (randomLimit > 0) {
+        insert = pxDataGet("stacktool_definebyquery_insert_random.sql");
+    } else {
+        insert = pxDataGet("stacktool_definebyquery_insert.sql");
+    }
+    if (!insert) {
+        psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    if (!psDBTransaction(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+
+    for (long i = 0; i < output->n; i++) {
+        psMetadata *row = output->data[i]; // Row from select
         bool status;
-        if (valid_warpids) {
-//            psS64 warp_id = psMetadataLookupS64(&status, md, "warp_id");
-            if (!status) {
-                psError(PS_ERR_UNKNOWN, false, "failed to lookup warp_id");
-                psFree(valid_warpids);
-                psFree(output);
-                return NULL;
-            }
-
-            // is this a warp_id that should be left out of the stack set(s)?
-//            for (long i = 0; i < psArrayLength(valid_warpids); i++) {
-//                if (valid_warpids->data[i] == warp_id) {
-//                    continue;
-//                }
-//            }
-        }
-
-        // pull out the skycell_id, & tess_id
-        psString skycell_id = psMetadataLookupStr(&status, md, "skycell_id");
+
+        // pull out the skycell_id, tess_id, filter
+        psString skycell_id = psMetadataLookupStr(&status, row, "skycell_id");
         if (!status) {
             psError(PS_ERR_UNKNOWN, false, "failed to lookup skycell_id");
-            psFree(valid_warpids);
             psFree(output);
-            return NULL;
-        }
-
-        psString tess_id = psMetadataLookupStr(&status, md, "tess_id");
+            psFree(insert);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+            }
+            return false;
+        }
+
+        psString tess_id = psMetadataLookupStr(&status, row, "tess_id");
         if (!status) {
             psError(PS_ERR_UNKNOWN, false, "failed to lookup tess_id");
-            psFree(valid_warpids);
             psFree(output);
-            return NULL;
-        }
-
-        // group the warps by skycell_id & tess_id
-        psString key = NULL;
-        psStringAppend(&key, "%s:%s", skycell_id, tess_id);
-
-        // check to see if the hash key already exists
-        psArray *col = psHashLookup(stacks, key);
-        if (!col) {
-            // if it doesn't, create an new psArray for this stack
-            col = psArrayAllocEmpty(0);
-            psHashAdd(stacks, key, col);
-        }
-        // add this warp to the stack for this key
-        psArrayAdd(col, 0, md);
-        psFree(key);
-
-    }
-    psFree(valid_warpids);
-    psFree(output);
-
-    psArray *grouped = psHashToArray(stacks);
-    psFree(stacks);
-
-    if (!psDBTransaction(config->dbh)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        return false;
-    }
-
-    // loop over groups
-    for (long i = 0; i < psArrayLength(grouped); i++) {
-        psArray *col = grouped->data[i];
-
-        // pull the skycell_id & tess_id from the first warp in the stack
-        bool status;
-        psString skycell_id = psMetadataLookupStr(&status, col->data[0], "skycell_id");
+            psFree(insert);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+            }
+            return false;
+        }
+
+        psString filter = psMetadataLookupStr(&status, row, "filter");
         if (!status) {
-            psError(PS_ERR_UNKNOWN, false, "failed to lookup skycell_id");
-            psFree(grouped);
-            return NULL;
-        }
-
-        psString tess_id = psMetadataLookupStr(&status, col->data[0], "tess_id");
-        if (!status) {
-            psError(PS_ERR_UNKNOWN, false, "failed to lookup tess_id");
-            psFree(grouped);
-            return NULL;
-        }
-
-        // create a new stackRun for the group
-        stackRunRow *run = stackRunRowAlloc(
-            0,          // ID
-            "run",      // state
-            workdir,
-            NULL,       // dvodb
-            registered,
-            skycell_id,
-            tess_id
-        );
+            psError(PS_ERR_UNKNOWN, false, "failed to lookup filter");
+            psFree(output);
+            psFree(insert);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+            }
+            return false;
+        }
+
+        // create a new stackRun for this stack
+        stackRunRow *run = stackRunRowAlloc(0, "run", workdir, NULL, registered, skycell_id, tess_id);
         if (!stackRunInsertObject(config->dbh, run)) {
             if (!psDBRollback(config->dbh)) {
@@ -331,6 +278,10 @@
             }
             psError(PS_ERR_UNKNOWN, false, "database error");
+            psFree(output);
             psFree(run);
-            psFree(grouped);
+            psFree(insert);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+            }
             return false;
         }
@@ -343,39 +294,34 @@
             psError(PS_ERR_UNKNOWN, false, "failed to print object");
             psFree(run);
-            psFree(grouped);
+            psFree(insert);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
+            }
             return false;
         }
         psFree(run);
 
-        // loop over this stack and add each warp to the stackRun
-        for (long j = 0; j < psArrayLength(col); j++) {
-            bool status;
-            psS64 warp_id = psMetadataLookupS64(&status, col->data[j], "warp_id");
-            if (!status) {
-                if (!psDBRollback(config->dbh)) {
-                    psError(PS_ERR_UNKNOWN, false, "database error");
-                }
-                psError(PS_ERR_UNKNOWN, false, "failed to lookup warp_id");
-                psFree(grouped);
-                return NULL;
+        // Create a suitable insertion query for this run
+        psString thisInsert = psStringCopy(insert);
+        psString idString = NULL;
+        psStringAppend(&idString, "%" PRId64, stack_id);
+        psStringSubstitute(&thisInsert, idString, "@STACK_ID@");
+        psFree(idString);
+
+        if ((randomLimit > 0 && !p_psDBRunQuery(config->dbh, thisInsert, skycell_id, tess_id, filter,
+                                                randomLimit)) ||
+            (randomLimit <= 0 && !p_psDBRunQuery(config->dbh, thisInsert, skycell_id, tess_id, filter))) {
+            psError(PS_ERR_UNKNOWN, false, "database error");
+            psFree(thisInsert);
+            psFree(insert);
+            psFree(output);
+            if (!psDBRollback(config->dbh)) {
+                psError(PS_ERR_UNKNOWN, false, "database error");
             }
-
-            if (!stackInputSkyfileInsert(config->dbh,
-                stack_id,
-                warp_id
-            )) {
-                if (!psDBRollback(config->dbh)) {
-                    psError(PS_ERR_UNKNOWN, false, "database error");
-                }
-                psError(PS_ERR_UNKNOWN, false, "database error");
-                psFree(grouped);
-                return false;
-            }
-
-        }
-
-    }
-
-    // point of no return
+            return false;
+        }
+        psFree(thisInsert);
+    }
+
     if (!psDBCommit(config->dbh)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
@@ -383,5 +329,5 @@
     }
 
-    psFree(grouped);
+    psFree(output);
 
     return true;