Changeset 24664

Timestamp:

Jul 3, 2009, 11:48:59 AM (17 years ago)

Author:

eugene

Message:

fix definebyquery insert sql so it can handle SQL wildcards; add -pretend mode to definebyquery

Location:

trunk/ippTools

Files:

• share/stacktool_definebyquery_insert.sql (modified) (2 diffs)
• share/stacktool_definebyquery_insert_random_part1.sql (modified) (2 diffs)
• share/stacktool_definebyquery_insert_random_part2.sql (modified) (1 diff)
• src/stacktool.c (modified) (4 diffs)
• src/stacktoolConfig.c (modified) (1 diff)

trunk/ippTools/share/stacktool_definebyquery_insert.sql

@@ -12 +12 @@
 JOIN camRun
     USING(cam_id)
+JOIN camProcessedExp
+    USING(cam_id)
 JOIN chipRun
     USING(chip_id)
@@ -17 +19 @@
     USING(exp_id)
 WHERE
-    warpSkyfile.skycell_id = '%s'
+    warpSkyfile.skycell_id = '@SKYCELL_ID@'
     AND warpRun.state = 'full'
-    AND rawExp.filter = '%s'
+    AND rawExp.filter = '@FILTER@'
     AND warpSkyfile.fault = 0
     AND warpSkyfile.quality = 0

trunk/ippTools/share/stacktool_definebyquery_insert_random_part1.sql

@@ -19 +19 @@
     JOIN camRun
         USING(cam_id)
+    JOIN camProcessedExp
+        USING(cam_id)
     JOIN chipRun
         USING(chip_id)
@@ -24 +26 @@
         USING(exp_id)
     WHERE
-        skycell_id = '%s'
+        skycell_id = '@SKYCELL_ID@'
         AND warpRun.state = 'full'
-        AND rawExp.filter = '%s' -- the result of the query is grouped by filter and inserted for one at a time
+        AND rawExp.filter = '@FILTER@' -- the result of the query is grouped by filter and inserted for one at a time
         AND warpSkyfile.fault = 0
         AND warpSkyfile.quality = 0

trunk/ippTools/share/stacktool_definebyquery_insert_random_part2.sql

@@ -5 +5 @@
     -- to get a random set of N.
     ORDER BY rnd_num
-    LIMIT %d
+    LIMIT @RANDOM_LIMIT@
 ) AS randomWarps

trunk/ippTools/src/stacktool.c

@@ -153 +153 @@
 
     PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+    PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
     PXOPT_LOOKUP_TIME(registered, config->args, "-registered", false, false);
 
@@ -236 +237 @@
     }
     if (!psArrayLength(output)) {
-        psTrace("stacktool", PS_LOG_INFO, "no rows found");
+        psWarning("stacktool: no rows found");
+        psFree(output);
+        psFree(where);
+        return true;
+    }
+    if (pretend) {
+        // negative simple so the default is true
+        if (!ippdbPrintMetadatas(stdout, output, "stackSkycells", !simple)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+            psFree(output);
+            psFree(where);
+            return false;
+        }
         psFree(output);
         psFree(where);
@@ -362 +375 @@
         psFree(idString);
 
+        // replace @FILTER@, @SKYCELL_ID@, @RANDOM_LIMIT@
+        psStringSubstitute(&thisInsert, filter, "@FILTER@");
+        psStringSubstitute(&thisInsert, skycell_id, "@SKYCELL_ID@");
+
+        if (randomLimit > 0) {
+          psString limString = NULL;
+          psStringAppend(&limString, "%d", randomLimit);
+          psStringSubstitute(&thisInsert, limString, "@RANDOM_LIMIT@");
+          psFree(limString);
+        }
+
         // XXX this insert uses a select to generate the list of warp_ids for the stack,
         // we have applied a set of criteria above (WHERE) to select the relevant warps
         // this insert needs to use exactly the same restrictions (race condition is probably not critical)
         // the insert below seems to only restrict matches to the skycell, tess, and filter
-        if ((randomLimit > 0 && !p_psDBRunQueryF(config->dbh, thisInsert, skycell_id, filter, randomLimit)) ||
-            (randomLimit <= 0 && !p_psDBRunQueryF(config->dbh, thisInsert, skycell_id, filter))) {
+        if (!p_psDBRunQuery(config->dbh, thisInsert)) {
             psError(PS_ERR_UNKNOWN, false, "database error");
             psFree(thisInsert);
@@ -379 +402 @@
         }
         psFree(thisInsert);
+
+# if (0)
+        {
+          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");
+            }
+            psFree(where);
+            return false;
+          }
+          if (!psArrayLength(output)) {
+            psWarning("stacktool (definebyquery, insert): no rows found");
+            psFree(output);
+            psFree(where);
+            return true;
+          }
+          // negative simple so the default is true
+          if (!ippdbPrintMetadatas(stdout, output, "stackSkycells", !simple)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+            psFree(output);
+            psFree(where);
+            return false;
+          }
+          psFree(output);
+          psFree(where);
+          return true;
+        }
+# endif
+
     }
     psFree(output);

trunk/ippTools/src/stacktoolConfig.c

@@ -82 +82 @@
     psMetadataAddS32(definebyqueryArgs, PS_LIST_TAIL, "-min_new", 0, "minimum number of new inputs", 0);
     psMetadataAddF32(definebyqueryArgs, PS_LIST_TAIL, "-min_frac", 0, "minumum fraction of new inputs", NAN);
+    psMetadataAddBool(definebyqueryArgs, PS_LIST_TAIL, "-pretend",  0, "do not actually modify the database", false);
     psMetadataAddBool(definebyqueryArgs, PS_LIST_TAIL, "-simple", 0, "use the simple output format", false);