Changeset 20784 for trunk/ippTools/share/magicdstool_completed_runs.sql

Timestamp:

Nov 17, 2008, 3:03:55 PM (17 years ago)

Author:

bills

Message:

finish query to find runs in all stages that are complete

File:

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

trunk/ippTools/share/magicdstool_completed_runs.sql

@@ -1 @@
--- SELECT DISTINCT
---     magic_ds_id
--- FROM
---    (
-    SELECT 
---    magicDSRun.magic_ds_id,
-    magicDSImfile.*,
-    chip_id,
-    chipProcessedImfile.class_id as class_id
+SELECT DISTINCT
+    magic_ds_id
+FROM
+    (
+-- raw stage
+SELECT 
+    magicDSRun.magic_ds_id
+    FROM magicDSRun
+    JOIN magicRun USING (magic_id)
+    JOIN magicInputSkyfile USING(magic_id)
+    JOIN diffRun USING(diff_id)
+    JOIN diffInputSkyfile
+        ON diffInputSkyfile.diff_id = diffRun.diff_id
+        AND diffInputSkyfile.skycell_id = diffRun.skycell_id
+        -- Want input warps only AND diffInputSkyfile.warp_id IS NOT NULL
+        AND diffInputSkyfile.template = 0
+    JOIN warpRun USING(warp_id)
+    JOIN fakeRun USING(fake_id)
+    JOIN camRun USING(cam_id)
+    JOIN camProcessedExp USING(cam_id)
+    JOIN rawImfile ON magicRun.exp_id = rawImfile.exp_id
+    LEFT JOIN magicDSImfile
+        ON magicDSRun.magic_ds_id = magicDSImfile.magic_ds_id
+        AND magicDSImfile.component = rawImfile.class_id
+    WHERE
+        magicDSRun.state = 'run'
+        AND magicDSRun.stage = 'raw'
+    GROUP BY
+        magicDSRun.magic_ds_id,
+        rawImfile.exp_id
+    HAVING
+        COUNT(rawImfile.class_id) = COUNT(magicDSImfile.component)
+        AND SUM(magicDSImfile.fault) = 0
+UNION
+-- chip stage
+SELECT 
+    magicDSRun.magic_ds_id
     FROM magicDSRun
     JOIN magicRun USING(magic_id)
@@ -37 +65 @@
         COUNT(chipProcessedImfile.class_id) = COUNT(magicDSImfile.component)
         AND SUM(magicDSImfile.fault) = 0
---    ) as Foo
+UNION
+-- warp stage
+SELECT 
+    magicDSRun.magic_ds_id
+    FROM magicDSRun
+    JOIN magicRun USING (magic_id)
+    JOIN magicInputSkyfile USING(magic_id)
+    JOIN diffRun USING(diff_id)
+    JOIN diffInputSkyfile
+        ON diffInputSkyfile.diff_id = diffRun.diff_id
+        AND diffInputSkyfile.skycell_id = diffRun.skycell_id
+        -- Want input warps only
+        AND diffInputSkyfile.warp_id IS NOT NULL
+        AND diffInputSkyfile.template = 0
+    JOIN warpSkyfile using(warp_id)
+    JOIN warpRun USING(warp_id)
+    LEFT JOIN magicDSImfile
+        ON magicDSRun.magic_ds_id = magicDSImfile.magic_ds_id
+        AND magicDSImfile.component = warpSkyfile.skycell_id
+    WHERE
+        magicDSRun.state = 'run'
+        AND magicDSRun.stage = 'warp'
+        AND warpSkyfile.fault = 0
+        AND warpSkyfile.ignored = 0
+    GROUP BY
+        magicDSRun.magic_ds_id,
+        warpRun.warp_id
+    HAVING
+        COUNT(warpSkyfile.skycell_id) = COUNT(magicDSImfile.component)
+        AND SUM(magicDSImfile.fault) = 0
+UNION
+-- diff stage
+SELECT DISTINCT
+    magicDSRun.magic_ds_id
+    FROM magicDSRun
+    JOIN magicRun USING (magic_id)
+    JOIN magicInputSkyfile USING(magic_id)
+    JOIN diffRun USING(diff_id)
+    JOIN diffSkyfile USING(diff_id)
+    JOIN diffInputSkyfile USING(diff_id)
+    LEFT JOIN magicDSImfile
+        ON magicDSRun.magic_ds_id = magicDSImfile.magic_ds_id
+        AND magicDSImfile.component = diffInputSkyfile.skycell_id
+    WHERE
+        magicDSRun.state = 'run'
+        AND magicDSRun.stage = 'diff'
+        AND diffRun.state = 'full'
+        AND diffSkyfile.fault = 0
+    GROUP BY
+        magicDSRun.magic_ds_id,
+        magicRun.magic_id
+    HAVING
+        COUNT(magicInputSkyfile.node) = COUNT(magicDSImfile.component)
+        AND SUM(magicDSImfile.fault) = 0
+
+   ) as Foo