Changeset 18574 for trunk/ippTools/share/stacktool_find_complete_warps.sql

Timestamp:

Jul 15, 2008, 7:55:19 PM (18 years ago)

Author:

Paul Price

Message:

Completely reworking stacktool -definebyquery in order to support automation of stacks. The processing (choosing the inputs) is all done in the database now. One call is made to get a list of skycells that should have new stacks made, and then additional calls are made to populate those. Selection of random subsets is now implemented (looks like it was commented out before), again with the processing in the database. Added new options that allow new stack runs to be triggered based on the number of new inputs (warps that haven't gone into a stack for that skycell). This allows it to be called over and over, only producing a new stack when it makes sense to do so (i.e., suitable for automation). Have made some informal tests, and seems to work.

File:

• trunk/ippTools/share/stacktool_find_complete_warps.sql (modified) (2 diffs)

trunk/ippTools/share/stacktool_find_complete_warps.sql

@@ -1 @@
-    SELECT
-        warp_id,
-        warpSkyfile.skycell_id,
-        warpSkyfile.tess_id
-    FROM warpRun
-    JOIN warpSkyfile 
-        USING(warp_id)
-    JOIN fakeRun
-        USING(fake_id)
-    JOIN camRun
-        USING(cam_id)
-    JOIN chipRun
-        USING(chip_id)
-    JOIN rawExp
-        USING(exp_id)
-    WHERE
-        warpRun.state = 'stop'
-        AND warpSkyfile.ignored = 0
+SELECT
+    warpSkyfile.skycell_id,
+    warpSkyfile.tess_id,
+    stackRun.stack_id,
+    COUNT(warpSkyfile.skycell_id) AS num_avail,
+    COUNT(stackRun.stack_id) AS num_extant
+FROM warpRun
+JOIN warpSkyfile 
+    USING(warp_id, tess_id)
+JOIN fakeRun
+    USING(fake_id, tess_id)
+JOIN camRun
+    USING(cam_id, tess_id)
+JOIN chipRun
+    USING(chip_id, tess_id)
+JOIN rawExp
+    USING(exp_id, tess_id)
+LEFT JOIN stackInputSkyfile
+    ON warpSkyfile.warp_id = stackInputSkyfile.warp_id
+LEFT JOIN stackRun
+    ON stackRun.tess_id = warpSkyfile.tess_id
+    AND stackRun.skycell_id = warpSkyfile.skycell_id
+    AND stackRun.stack_id = stackInputSkyfile.stack_id
+WHERE
+    warpRun.state = 'stop'
+    AND warpSkyfile.ignored = 0
+GROUP BY
+    warpSkyfile.skycell_id, warpSkyfile.tess_id, stackRun.stack_id
+HAVING
+    num_avail > num_extant
+
 -- It seems like we should be grouping the results here but in fact we don't
 -- want to do that as each warp_id may contain multiple skycells.  So a warp_id
@@ -23 +36 @@
 --        warp_id
 
+
+INSERT INTO
+        stackInputSkyfile(stack_id, warp_id)
+SELECT
+        13,
+        warp_id
+FROM warpSkyfile
+WHERE
+    skycell_id = 'new';