Changeset 35192 for trunk/ippTools/share/difftool_definewarpstack.sql

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.

File:

• trunk/ippTools/share/difftool_definewarpstack.sql (modified) (1 diff)

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