Changeset 20736 for trunk/ippTools/share/magictool_definebyquery.sql

Timestamp:

Nov 13, 2008, 3:11:08 PM (17 years ago)

Author:

Paul Price

Message:

Updating queries in this test.

File:

• trunk/ippTools/share/magictool_definebyquery.sql (modified) (5 diffs)

trunk/ippTools/share/magictool_definebyquery.sql

@@ -16 +16 @@
 skycell_id VARCHAR(64),
 tess_id VARCHAR(64),
+warp_id BIGINT,
 diff_id BIGINT,
 PRIMARY KEY(exp_id, skycell_id, tess_id)
@@ -25 +26 @@
 INSERT INTO magicBestDiffs
 SELECT
-    exp_id,
+    rawExp.exp_id,
     warpSkyfile.skycell_id,
     warpSkyfile.tess_id,
+    warpRun.warp_id AS warp_id,
     MAX(diffSkyfile.diff_id) AS diff_id
 FROM rawExp
-JOIN chipRun USING(exp_id, tess_id)
-JOIN camRun USING(chip_id, tess_id)
-JOIN fakeRun USING(cam_id, tess_id)
-JOIN warpRun USING(fake_id, tess_id)
-JOIN warpSkyCellMap USING(warp_id, tess_id)
-JOIN warpSkyfile USING(warp_id, skycell_id, tess_id)
+JOIN chipRun USING(exp_id)
+JOIN camRun USING(chip_id)
+JOIN fakeRun USING(cam_id)
+JOIN warpRun USING(fake_id)
+JOIN warpSkyCellMap USING(warp_id)
+JOIN warpSkyfile USING(warp_id, skycell_id)
 JOIN diffInputSkyfile
     ON diffInputSkyfile.warp_id = warpSkyfile.warp_id
     AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
-    AND diffInputSkyfile.tess_id = warpSkyfile.tess_id
     AND diffInputSkyfile.template = 0 -- selecting inputs only
 JOIN diffSkyfile USING(diff_id)
 WHERE
     diffSkyfile.fault = 0
------    AND warpSkyfile.good_frac >= 0.5
--- magictool_definebyquery_temp_insert_groupby.sql
+---- WHERE hook %s
 GROUP BY
     exp_id,
@@ -52 +52 @@
 
 
--- magictool_definebyquery_select_part1.sql
--- This is part 1 of 2 of a query to get a list of exposures on which magic may be performed
--- After this follows magictool_definebyquery_select_part2.sql
+-- magictool_definebyquery_select.sql
+-- Get a list of exposures on which magic may be performed
 SELECT
     exp_id,
@@ -65 +64 @@
         exp_id,
         filter,
-        COUNT(skycell_id) AS num_todo
+        COUNT(DISTINCT skycell_id) AS num_todo
     FROM rawExp
-    JOIN chipRun USING(exp_id, tess_id)
-    JOIN camRun USING(chip_id, tess_id)
-    JOIN fakeRun USING(cam_id, tess_id)
-    JOIN warpRun USING(fake_id, tess_id)
-    JOIN warpSkyCellMap USING(warp_id, tess_id)
-    JOIN warpSkyfile USING(warp_id, skycell_id, tess_id)
+    JOIN chipRun USING(exp_id)
+    JOIN camRun USING(chip_id)
+    JOIN fakeRun USING(cam_id)
+    JOIN warpRun USING(fake_id)
+    JOIN warpSkyCellMap USING(warp_id)
+    JOIN warpSkyfile USING(warp_id, skycell_id)
     WHERE
         warpSkyfile.ignored = 0
         AND warpRun.state = 'full'
-    -- INSERT HERE any additional restrictions (e.g., exp_id, warpSkyfile.good_frac)
------        AND warpSkyfile.good_frac > 0.5
--- INSERT HERE magictool_definebyquery_select_part2.sql
--- magictool_definebyquery_select_part2.sql
--- This is part 2 of 2 of a query to get a list of exposures on which magic may be performed
--- This follows magictool_definebyquery_select_part1.sql
+        --- magicSkycellNums WHERE hook %s
     GROUP BY
         exp_id
@@ -96 +90 @@
 LEFT JOIN magicRun USING(exp_id)
 WHERE
-    num_done = num_todo
-    AND magicRun.magic_id IS NULL
+    magicRun.magic_id IS NULL
 ;