Changeset 14018 for trunk/ippTools/share/chiptool_completely_processed_exp.sql

Timestamp:

Jul 5, 2007, 10:15:41 AM (19 years ago)

Author:

jhoblitt

Message:

merge backtrack branch

File:

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

trunk/ippTools/share/chiptool_completely_processed_exp.sql

@@ -1 @@
--- select * from chipPendingExp
--- where exp_tag is not in chipProcessedExp
--- where exp_tag is not in chipPendingImfile
--- where the number of entries in chipProccessedImfile matches the .imfiles
--- entry in rawExp
+-- the output of this query must match the format of chipRun row
 SELECT DISTINCT
-    chipPendingExp.*,
-    rawExp.imfiles,
-    chipProcessedImfile.class_id
-FROM chipPendingExp
-JOIN rawExp
-    ON chipPendingExp.exp_tag = rawExp.exp_tag
-LEFT JOIN chipProcessedExp
-    ON chipPendingExp.chip_id = chipProcessedExp.chip_id
-LEFT JOIN chipPendingImfile
-    ON chipPendingExp.chip_id = chipPendingImfile.chip_id
-LEFT JOIN chipProcessedImfile
-    ON chipPendingExp.chip_id = chipProcessedImfile.chip_id
-WHERE
-    chipProcessedExp.chip_id IS NULL
-    AND chipPendingImfile.chip_id IS NULL
-    AND chipProcessedImfile.chip_id IS NOT NULL
-    AND chipProcessedImfile.fault = 0
-GROUP BY
-    chipPendingExp.chip_id
-HAVING rawExp.imfiles = COUNT(chipProcessedImfile.class_id)
+    chip_id,
+    state,
+    workdir,
+    workdir_state,
+    label,
+    reduction,
+    expgroup,
+    dvodb
+FROM
+    (SELECT 
+        chipRun.*,
+        rawExp.imfiles,
+        chipProcessedImfile.class_id
+    FROM chipRun
+    JOIN chipInputImfile
+        USING(chip_id)
+    JOIN rawExp
+        ON chipInputImfile.exp_tag = rawExp.exp_tag
+    JOIN chipProcessedImfile
+        ON chipInputImfile.chip_id = chipProcessedImfile.chip_id
+        AND chipInputImfile.exp_tag = chipProcessedImfile.exp_tag
+        AND chipInputImfile.class_id = chipProcessedImfile.class_id
+    WHERE
+        chipRun.state = 'run'
+        AND chipProcessedImfile.fault = 0
+    GROUP BY
+        chipInputImfile.chip_id,
+        chipInputImfile.exp_tag
+    HAVING rawExp.imfiles = COUNT(chipProcessedImfile.class_id)) as Foo