Changeset 13990 for branches/backtrack/ippTools/share/chiptool_completely_processed_exp.sql

Timestamp:

Jun 28, 2007, 12:01:22 PM (19 years ago)

Author:

jhoblitt

Message:

refactor chiptool & db to preserve metadata history

File:

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

branches/backtrack/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 DISTINCT
+        chipRun.*,
+        rawExp.imfiles,
+        chipProcessedImfile.exp_tag,
+        chipProcessedImfile.class_id
+    FROM chipRun
+    JOIN chipPendingImfile
+        USING(chip_id)
+    JOIN rawExp
+        ON chipPendingImfile.exp_tag = rawExp.exp_tag
+    LEFT JOIN chipProcessedImfile
+        USING(chip_id)
+    WHERE
+        chipProcessedImfile.chip_id IS NOT NULL
+        AND chipProcessedImfile.exp_tag IS NOT NULL
+        AND chipProcessedImfile.class_id IS NOT NULL
+        AND chipProcessedImfile.fault = 0
+    GROUP BY
+        chipProcessedImfile.class_id,
+        chipProcessedImfile.exp_tag
+    HAVING rawExp.imfiles = COUNT(chipProcessedImfile.class_id)) as Foo