Changeset 24136 for branches/pap_magic/ippTools/share/magictool_definebyquery_select.sql

Timestamp:

May 11, 2009, 4:38:18 PM (17 years ago)

Author:

Paul Price

Message:

Make magictool -definebyquery work with inverse subtractions (A-B and B-A). Small change to DB schema.

File:

• branches/pap_magic/ippTools/share/magictool_definebyquery_select.sql (modified) (1 diff)

branches/pap_magic/ippTools/share/magictool_definebyquery_select.sql

@@ -1 +1 @@
 -- Get a list of exposures on which magic may be performed
-SELECT 
+SELECT
     exp_id,
-    MAX(diffRun.diff_id) AS diff_id
-FROM diffRun
-JOIN rawExp USING(exp_id)
+    MAX(diffWarps.diff_id) AS diff_id,
+    -- The following trick pulls out the 'inverse' value for the maximum diff_id
+    CONVERT(SUBSTRING_INDEX(GROUP_CONCAT(diffWarps.inverse ORDER BY diffWarps.diff_id), ',', 1), UNSIGNED) AS inverse
+FROM (
+    -- Forward diffs
+    SELECT
+        diffRun.diff_id,
+        warp1 AS warp_id,
+        0 AS inverse
+    FROM diffRun
+    JOIN diffInputSkyfile USING(diff_id)
+    WHERE diffInputSkyfile.warp1 IS NOT NULL
+        AND diffRun.exposure = 1
+    -- WHERE hook %s
+    UNION
+    -- Backward diffs
+    SELECT
+        diffRun.diff_id,
+        warp2 AS warp_id,
+        1 AS inverse
+    FROM diffRun
+    JOIN diffInputSkyfile USING(diff_id)
+    WHERE diffInputSkyfile.warp2 IS NOT NULL
+        AND diffRun.exposure = 1
+        AND diffRun.bothways = 1
+    -- WHERE hook %s
+    ) AS diffWarps
+JOIN warpRun USING(warp_id)
+JOIN fakeRun USING(fake_id)
+JOIN camRun USING(cam_id)
+JOIN chipRun USING(chip_id)
 LEFT JOIN magicRun USING(exp_id)
 -- WHERE hook %s