Changeset 23342 for trunk/ippTools/share/dettool_toresidexp.sql

Timestamp:

Mar 17, 2009, 10:36:05 AM (17 years ago)

Author:

eugene

Message:

verify vs master: verify needs to use the detNormalizedImfile entry / master uses detStackedImfile; verify should not block on detNormalizedExp; verify does not select normalization values

File:

• trunk/ippTools/share/dettool_toresidexp.sql (modified) (4 diffs)

trunk/ippTools/share/dettool_toresidexp.sql

@@ -20 +20 @@
 -- detResidImfile.{det_id, iteration, exp_id} is not in detResidExp
 
-SELECT DISTINCT
+SELECT
     det_id,
     iteration,
@@ -30 +30 @@
     workdir,
     exp_tag
-FROM
-    (SELECT DISTINCT
+FROM 
+(   SELECT
         detRun.det_id AS det_id,
         detRun.iteration,
         detRun.det_type,
         detRun.mode,
-        detRun.workdir,
         detInputExp.exp_id,
         detInputExp.include,
         rawExp.camera,
-        rawExp.exp_tag,
-        detResidImfile.class_id
+        detRun.workdir,
+        rawExp.exp_tag
     FROM detRun
     JOIN detNormalizedExp
@@ -62 +61 @@
     WHERE
         detRun.state = 'run'
+        AND detRun.mode = 'master'
         AND detResidExp.det_id IS NULL
         AND detResidExp.iteration IS NULL
@@ -72 +72 @@
         COUNT(rawImfile.class_id) = COUNT(detResidImfile.class_id)
         AND SUM(detResidImfile.fault) = 0
-    ) AS toresidexp
+    UNION
+    SELECT
+        detRun.det_id AS det_id,
+        detRun.iteration,
+        detRun.det_type,
+        detRun.mode,
+        detInputExp.exp_id,
+        detInputExp.include,
+        rawExp.camera,
+        detRun.workdir,
+        rawExp.exp_tag
+    FROM detRun
+    JOIN detInputExp
+        USING(det_id, iteration)
+    JOIN rawExp
+        ON detInputExp.exp_id = rawExp.exp_id
+    JOIN rawImfile
+        on rawExp.exp_id = rawImfile.exp_id
+    LEFT JOIN detResidImfile
+        ON detRun.det_id = detResidImfile.det_id
+        AND detRun.iteration = detResidImfile.iteration
+        AND detInputExp.exp_id = detResidImfile.exp_id
+        AND rawImfile.class_id = detResidImfile.class_id
+    LEFT JOIN detResidExp
+        ON detResidImfile.det_id = detResidExp.det_id
+        AND detResidImfile.iteration = detResidExp.iteration
+        AND detResidImfile.exp_id = detResidExp.exp_id
+    WHERE
+        detRun.state = 'run'
+        AND detRun.mode = 'verify'
+        AND detResidExp.det_id IS NULL
+        AND detResidExp.iteration IS NULL
+        AND detResidExp.exp_id IS NULL
+    GROUP BY
+        detInputExp.exp_id,
+        detRun.iteration,
+        detRun.det_id
+    HAVING
+        COUNT(rawImfile.class_id) = COUNT(detResidImfile.class_id)
+        AND SUM(detResidImfile.fault) = 0
+) as temp