Changeset 20841 for trunk/ippTools/share/magicdstool_todestreak.sql

Timestamp:

Nov 25, 2008, 5:21:13 PM (17 years ago)

Author:

bills

Message:

Add the stage_id and cam_id to magicDSRun. This vastly reduces the
number of JOINs that we have to do

File:

• trunk/ippTools/share/magicdstool_todestreak.sql (modified) (6 diffs)

trunk/ippTools/share/magicdstool_todestreak.sql

@@ -8 +8 @@
     magicMask.uri as streaks_uri,
     stage,
-    rawImfile.exp_id as stage_id,
+    stage_id,
     class_id as component,
     rawImfile.uri as uri,
@@ -20 +20 @@
 JOIN magicMask USING (magic_id)
 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
@@ -43 +33 @@
 SELECT DISTINCT
     magicDSRun.magic_ds_id,
-    magicRun.magic_id,
-    magicRun.exp_id,
+    magicDSRun.magic_id,
+    chipRun.exp_id,
     camera,
     magicMask.uri as streaks_uri,
     stage,
-    chip_id as stage_id,
+    stage_id,
     class_id as component,
     chipProcessedImfile.uri,
@@ -59 +49 @@
 FROM magicDSRun
 JOIN magicMask USING (magic_id)
-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 chipRun USING(chip_id)
+JOIN chipRun ON chip_id = stage_id
 JOIN chipProcessedImfile USING(chip_id)
 JOIN rawExp ON chipRun.exp_id = rawExp.exp_id
@@ -93 +71 @@
     magicMask.uri as streaks_uri,
     stage,
-    warp_id as stage_id,
+    stage_id,
     warpSkyfile.skycell_id as component,
     warpSkyfile.uri,
@@ -106 +84 @@
 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 warpSkyfile ON warp_id = stage_id
 JOIN warpRun USING(warp_id)
 JOIN rawExp ON magicRun.exp_id = rawExp.exp_id