Changeset 35192

Timestamp:

Feb 21, 2013, 1:18:35 PM (13 years ago)

Author:

watersc1

Message:

Updated SQL and database interactions for WSdiffs. This should allow it to run in a reasonable amount of time and without blocking the database.

Location:

trunk/ippTools

Files:

• share/Makefile.am (modified) (1 diff)
• share/difftool_definewarpstack.sql (modified) (1 diff)
• src/difftool.c (modified) (2 diffs)

trunk/ippTools/share/Makefile.am

@@ -155 +155 @@
         difftool_completed_runs.sql \
         difftool_coalesce_run.sql \
+        difftool_definewarpstack.sql \
         difftool_definewarpstack_part1.sql \
         difftool_definewarpstack_part2.sql \

trunk/ippTools/share/difftool_definewarpstack.sql

@@ -1 @@
--- Get list of warps that can be diffed, with any associated diff,
--- and the best stack to use as a template
--- Warps without an existing diff can be identified by a NULL diff_id
+-- Get list of warp-stack pairs that can be diffed
+-- and check results against existing diffs
 SELECT
-    warpsToDiff.warp_id,
-    warpsToDiff.skycell_id,
-    warpsToDiff.tess_id,
-    warpsToDiff.filter,
-    warpsToDiff.good_frac,
-    warpsToDiff.diff_id,
-    current_stack_id,
-    best_stack_id,
-    exp_id
-FROM (
-    -- Get list of warps that can be diffed, with any associated diff
-    SELECT
-        warpSkyfile.warp_id,
-        warpSkyfile.skycell_id,
-        warpSkyfile.tess_id,
-        warpSkyfile.good_frac,
-        warpRun.label,
-        filter,
-        warpRun.label as warp_label,
-        diffInputs.diff_id,
-        diffInputs.stack2 AS current_stack_id,
-        rawExp.exp_id
-    FROM warpSkyfile
-    JOIN warpRun USING(warp_id)
-    JOIN fakeRun USING(fake_id)
-    JOIN camRun USING(cam_id)
-    JOIN chipRun USING(chip_id)
-    JOIN rawExp USING(exp_id)
-    -- Check if it has an associated diff
-    LEFT JOIN diffInputSkyfile AS diffInputs
-        ON diffInputs.warp1 = warpSkyfile.warp_id
-        AND diffInputs.skycell_id = warpSkyfile.skycell_id
-        AND diffInputs.stack2 IS NOT NULL
-    WHERE
-        warpSkyfile.fault = 0
-        AND warpSkyfile.quality = 0
-    -- warpsToDiff WHERE hook %s
-    ) AS warpsToDiff
--- Get best stack as a function of skycell_id, filter
-JOIN (
-    SELECT
-        MAX(stack_id) AS best_stack_id, -- most recent stack, by virtue of auto-increment
-        skycell_id,
-        filter
-    FROM stackRun
-    JOIN stackSumSkyfile USING(stack_id)
-    WHERE stackRun.state = 'full'
-        AND stackSumSkyfile.fault = 0
-        AND stackSumSkyfile.quality = 0
-    -- stacksForDiff WHERE hook %s
-    GROUP BY
-        skycell_id,
-        filter
-    ) AS stacksForDiff USING(skycell_id, filter)
+   exp_id,
+   warp_id,
+   rawExp.filter,
+   warpRun.label AS warpLabel,
+   warpRun.data_group AS warpDataGroup,
+   warpRun.tess_id,
+   warpSkyfile.skycell_id,
+   MAX(stack_id) AS stack_id,
+   stackRun.label AS stackLabel,
+   stackRun.data_group AS stackDataGroup,
+   diff_id,
+   diffRun.label AS diffLabel
+FROM warpRun
+     JOIN fakeRun USING(fake_id)
+     JOIN camRun USING(cam_id)
+     JOIN chipRun USING(chip_id)
+     JOIN rawExp USING(exp_id)
+     JOIN warpSkyfile USING(warp_id)
+     JOIN stackRun ON 
+       (stackRun.skycell_id = warpSkyfile.skycell_id AND
+        stackRun.filter     = rawExp.filter AND
+        stackRun.tess_id    = warpRun.tess_id)
+     JOIN stackSumSkyfile USING(stack_id)
+     LEFT JOIN diffInputSkyfile ON 
+       (warp_id = diffInputSkyfile.warp1 AND
+        stack_id = diffInputSkyfile.stack2 AND
+        diffInputSkyfile.skycell_id = stackRun.skycell_id AND
+        diffInputSkyfile.tess_id = stackRun.tess_id)
+     LEFT JOIN diffRun USING(diff_id)
+WHERE 
+     warpRun.state = 'full'
+     AND stackRun.state = 'full'
+     AND warpSkyfile.fault = 0
+     AND warpSkyfile.quality = 0
+     AND stackSumSkyfile.fault = 0
+     AND stackSumSkyfile.quality = 0
+     AND exp_id IS NOT NULL
+-- %s
+GROUP BY exp_id,warp_id,skycell_id

trunk/ippTools/src/difftool.c

@@ -1226 +1226 @@
 }
 
-
+static bool definewarpstackMode(pxConfig *config)
+{
+  PS_ASSERT_PTR_NON_NULL(config, false);
+
+  psMetadata *where = psMetadataAlloc();
+
+  PXOPT_COPY_S64(config->args, where, "-exp_id", "exp_id", "==");
+  PXOPT_COPY_STR(config->args, where, "-filter", "filter", "==");
+  PXOPT_COPY_STR(config->args, where, "-comment", "comment", "LIKE");
+  PXOPT_COPY_S64(config->args, where, "-warp_id", "warpRun.warp_id", "==");
+  PXOPT_COPY_STR(config->args, where, "-warp_label", "warpRun.label", "==");
+  PXOPT_COPY_STR(config->args, where, "-tess_id", "warpRun.tess_id", "==");
+  PXOPT_COPY_STR(config->args, where, "-data_group", "warpRun.data_group", "==");
+  PXOPT_COPY_STR(config->args, where, "-skycell_id", "warpSkyfile.skycell_id", "==");
+  PXOPT_COPY_F32(config->args, where,  "-good_frac", "warpSkyfile.good_frac", ">=");
+  PXOPT_COPY_STR(config->args, where, "-stack_label", "stackRun.label", "==");
+  PXOPT_COPY_STR(config->args, where, "-stack_data_group", "stackRun.data_group", "==");
+
+  PXOPT_LOOKUP_BOOL(bothways, config->args, "-bothways", false);
+  
+  PXOPT_LOOKUP_STR(workdir, config->args, "-set_workdir", true, false); // required option
+  PXOPT_LOOKUP_STR(reduction, config->args, "-set_reduction", false, false); // option
+  PXOPT_LOOKUP_STR(label, config->args, "-set_label", true, false); // option
+  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(note, config->args, "-set_note", false, false);
+  PXOPT_LOOKUP_TIME(registered, config->args, "-set_registered", false, false);
+  
+  PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+  PXOPT_LOOKUP_BOOL(newTemplates, config->args, "-new-templates", false);
+  PXOPT_LOOKUP_BOOL(reRun, config->args, "-rerun", false);
+  PXOPT_LOOKUP_BOOL(available, config->args, "-available", false);
+  PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
+
+
+  // Get query file
+  psString query = pxDataGet("difftool_definewarpstack.sql");
+  if (!query) {
+    psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+    return false;
+  }
+
+  psString whereClause = psDBGenerateWhereConditionSQL(where,NULL);
+  
+  // Don't queue things that already have diffs.
+  if (! (reRun || newTemplates) ) {
+    psStringAppend(&whereClause, "\nAND diff_id IS NULL\n");
+  }
+  // Append diff qualifiers, if we have them
+  if (label) {
+    psStringAppend(&whereClause, "\nAND ((diffRun.label = '%s') OR (diffRun.label IS NULL))",label);
+  }
+  if (data_group) {
+    psStringAppend(&whereClause, "\nAND ((diffRun.data_group = '%s') OR (diffRun.data_group IS NULL))",data_group);
+  }
+  if (reduction) {
+    psStringAppend(&whereClause, "\nAND ((diffRun.reduction = '%s') OR (diffRun.reduction IS NULL))",reduction);
+  }
+
+  psString whereClauseString = psStringCopy("");
+  psStringAppend(&whereClauseString, " \n AND %s ", whereClause);
+  //  fprintf(stderr,query,whereClauseString);
+
+  // This is just a simple query, so we don't need to do a transaction
+  if (!p_psDBRunQueryF(config->dbh, query, whereClauseString)) {
+    psError(PS_ERR_UNKNOWN, false, "database error");
+    psFree(query);
+    psFree(whereClause);
+    return(false);
+  }
+  psFree(query);
+  psFree(whereClause);
+  psFree(whereClauseString);
+  
+  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");
+      break;
+    case PS_ERR_DB_SERVER:
+      psError(PXTOOLS_ERR_PROG, false, "database error");
+      break;
+    default:
+      psError(PXTOOLS_ERR_PROG, false, "unknown error");
+      break;
+    }
+    return false;
+  }
+  if (!psArrayLength(output)) {
+    psTrace("difftool", PS_LOG_INFO, "no rows found");
+    psFree(output);
+    return true;
+  }
+  if (pretend) {
+    // negative simple so the default is true
+    if (!ippdbPrintMetadatas(stdout, output, "diffRun", !simple)) {
+      psError(PS_ERR_UNKNOWN, false, "failed to print array");
+      psFree(output);
+      return false;
+    }
+    psFree(output);
+    return true;
+  }
+
+  psArray *list = psArrayAllocEmpty(16); // List of runs, to print
+  long numGood = 0;
+  psS64 last_exp_id = 0;
+  psS64 diff_id = 0;
+  for (long i = 0; i < output->n; i++) {
+    psMetadata *row = output->data[i];
+    bool mdok;
+
+    psS64 exp_id = psMetadataLookupS64(&mdok, row, "exp_id");
+    if (!mdok) {
+        psError(PXTOOLS_ERR_PROG, false, "exp_id not found");
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+        return false;
+    }
+    if (exp_id != last_exp_id) {
+      if (diff_id != 0) { // We've added a run already, and are now switching to a new one.
+        // Set state to new
+        if (!setdiffRunState(config, diff_id, "new", false)) {
+          psError(PS_ERR_UNKNOWN, false, "failed to change diffRun.state for diff_id: %" PRId64, diff_id);
+          if (!psDBRollback(config->dbh)) {
+            psError(PS_ERR_UNKNOWN, false, "database error");
+          }
+          return false;
+        }
+        // Commit results
+        if (!psDBCommit(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+          psFree(list);
+          return false;
+        }
+      }
+      
+      // Begin transaction
+      if (!psDBTransaction(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+      }
+      // Add a new diffRun row
+      psString tess_id = psMetadataLookupStr(&mdok, row, "tess_id");
+      if (!mdok) {
+        psError(PXTOOLS_ERR_PROG, false, "tess_id not found");
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+        return false;
+      }
+      diffRunRow *run = diffRunRowAlloc(
+                                        0,        // ID
+                                        "reg",    // state
+                                        workdir,
+                                        label,
+                                        data_group ? data_group : label,
+                                        dist_group,
+                                        reduction,
+                                        NULL,     // dvodb
+                                        registered,
+                                        tess_id,
+                                        bothways, // bothways (default is false)
+                                        true,     // exposure
+                                        0,        // magicked
+                                        NULL,     // software_version
+                                        0,        // mask stat npix
+                                        NAN,      // static
+                                        NAN,      // dynamic
+                                        NAN,      // magic
+                                        NAN,      // advisory
+                                        IPP_DIFF_MODE_WARP_STACK,
+                                        note
+                                        );
+      if (!diffRunInsertObject(config->dbh, run)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(run);
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+      }
+      diff_id = psDBLastInsertID(config->dbh);
+      run->diff_id = diff_id;
+
+      psArrayAdd(list, list->n, run);
+      numGood++;
+      
+      last_exp_id = exp_id;                                     
+    } // End Adding diffRun
+    
+    if (exp_id == last_exp_id) {
+      psString skycell_id = psMetadataLookupStr(&mdok, row, "skycell_id");
+      if (!mdok) {
+        psError(PXTOOLS_ERR_PROG, false, "skycell_id not found");
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+        return false;
+      }
+      psString tess_id = psMetadataLookupStr(&mdok, row, "tess_id");
+      if (!mdok) {
+        psError(PXTOOLS_ERR_PROG, false, "tess_id not found");
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+        return false;
+      }
+      psS64 warp_id = psMetadataLookupS64(&mdok, row, "warp_id");
+      if (!mdok) {
+        psError(PXTOOLS_ERR_PROG, false, "warp_id not found");
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+        return false;
+      }
+      psS64 stack_id = psMetadataLookupS64(&mdok, row, "stack_id");
+      if (!mdok) {
+        psError(PXTOOLS_ERR_PROG, false, "stack_id not found");
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+        return false;
+      }
+
+      // Add a new skyfile row
+      diffInputSkyfileRow *skyfile = diffInputSkyfileRowAlloc(
+                                                              diff_id,   // ID
+                                                              skycell_id,
+                                                              warp_id, // warp1_id 
+                                                              PS_MAX_S64, // stack1 -> NULL
+                                                              PS_MAX_S64, // warp2_id -> NULL
+                                                              stack_id, // stack2
+                                                              tess_id,
+                                                              0 // diff_skyfile_id
+                                                              );
+      //      fprintf(stderr,"%"PRId64 " %"PRId64 " %"PRId64 " %s %s\n",diff_id,warp_id,stack_id,skycell_id, tess_id);
+      if (!diffInputSkyfileInsertObject(config->dbh, skyfile)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(list);
+        if (!psDBRollback(config->dbh)) {
+          psError(PS_ERR_UNKNOWN, false, "database error");
+        }
+        return false;
+      }
+    } // End Adding skyfile
+  } // End parsing result set
+  
+  // Finish the last run's update and close the connection
+  if (diff_id != 0) {
+    if (!setdiffRunState(config, diff_id, "new", false)) {
+      psError(PS_ERR_UNKNOWN, false, "failed to change diffRun.state for diff_id: %" PRId64, diff_id);
+      if (!psDBRollback(config->dbh)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+      }
+      return false;
+    }
+    // Commit results
+    if (!psDBCommit(config->dbh)) {
+      psError(PS_ERR_UNKNOWN, false, "database error");
+      psFree(list);
+      return false;
+    }
+  }
+  
+  if (numGood && !diffRunPrintObjects(stdout, list, !simple)) {
+    psError(PS_ERR_UNKNOWN, false, "failed to print object");
+    psFree(list);
+    return(false);
+  }
+  psFree(list);
+  // Free things
+  return true;
+}  
+
+#if (0)
 static bool definewarpstackMode(pxConfig *config)
 {
@@ -1614 +1891 @@
     return true;
 }
+#endif
 
 static bool definewarpwarpMode(pxConfig *config)