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

Timestamp:

Nov 13, 2008, 3:57:45 PM (17 years ago)

Author:

Paul Price

Message:

I think this example is working now.

File:

• trunk/ippTools/share/magictool_definebyquery.sql (modified) (6 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)
 ) ENGINE=MEMORY;
+
 
 
@@ -27 +27 @@
 SELECT
     rawExp.exp_id,
-    warpSkyfile.skycell_id,
-    warpSkyfile.tess_id,
-    warpRun.warp_id AS warp_id,
+    diffInputSkyfile.skycell_id,
+    diffInputSkyfile.tess_id,
     MAX(diffSkyfile.diff_id) AS diff_id
 FROM rawExp
@@ -42 +41 @@
     AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
     AND diffInputSkyfile.template = 0 -- selecting inputs only
+JOIN diffRun USING(diff_id)
 JOIN diffSkyfile USING(diff_id)
 WHERE
     diffSkyfile.fault = 0
----- WHERE hook %s
+-- WHERE hook %s
+AND exp_id = 36475
+AND diffRun.label = 'magic_2008-11-12'
 GROUP BY
     exp_id,
-    warpSkyfile.skycell_id
+    diffInputSkyfile.skycell_id,
+    diffInputSkyfile.tess_id
 ;
 
@@ -54 +57 @@
 -- magictool_definebyquery_select.sql
 -- Get a list of exposures on which magic may be performed
-SELECT
+SELECT DISTINCT
     exp_id,
     filter,
     num_todo,
-    num_done
+    num_done,
+    magic_id
 FROM (
     -- Number of skycells as a function of exposure
@@ -64 +68 @@
         exp_id,
         filter,
-        COUNT(DISTINCT skycell_id) AS num_todo
+        COUNT(DISTINCT warpSkyfile.tess_id,warpSkyfile.skycell_id) AS num_todo
     FROM rawExp
     JOIN chipRun USING(exp_id)
@@ -72 +76 @@
     JOIN warpSkyCellMap USING(warp_id)
     JOIN warpSkyfile USING(warp_id, skycell_id)
+    JOIN diffInputSkyfile USING(warp_id,skycell_id)
+    JOIN diffRun USING(diff_id)
     WHERE
         warpSkyfile.ignored = 0
-        AND warpRun.state = 'full'
-        --- magicSkycellNums WHERE hook %s
+        -- magicSkycellNums WHERE hook %s
+AND exp_id = 36475
+AND diffRun.label = 'magic_2008-11-12'
     GROUP BY
         exp_id