Changeset 19601 for trunk/ippTools/share/difftool_todiffskyfile.sql

Timestamp:

Sep 18, 2008, 1:03:35 PM (18 years ago)

Author:

Paul Price

Message:

Fixing difftool -todiffskyfile query. The old query didn't support
stack-stack subtractions, nor did it check that the templates and
inputs actually exist. This new query is a little complicated, since
it contains multiple self-joins. This is because of the options
involved: both templates and inputs can be either warps or stacks.
All of the templates and inputs need to exist, so we query against all
of them. I hope it's not a performance killer.

File:

• trunk/ippTools/share/difftool_todiffskyfile.sql (modified) (2 diffs)

trunk/ippTools/share/difftool_todiffskyfile.sql

@@ +1 @@
+-- This query is a little complicated, since it contains multiple self-joins.
+-- This is because of the options involved: both templates and inputs can be either warps or stacks.
+-- All of the templates and inputs need to exist, so we query against all of them.
 SELECT DISTINCT
     diffRun.diff_id,
@@ -5 +8 @@
     diffRun.tess_id,
     diffRun.label,
-    diffRun.state,
-    rawExp.camera
+    diffRun.state
 FROM diffRun
+-- Get list of templates for each diffRun
+JOIN diffInputSkyfile AS diffTemplateSkyfile
+    ON diffRun.diff_id = diffTemplateSkyfile.diff_id
+    AND diffRun.skycell_id = diffTemplateSkyfile.skycell_id
+    AND diffTemplateSkyfile.template = 1
+-- Get list of inputs for each diffRun
 JOIN diffInputSkyfile
-    USING(diff_id)
-JOIN warpSkyfile
-    ON  diffInputSkyfile.warp_id    = warpSkyfile.warp_id
-    AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id
-    AND diffInputSkyfile.tess_id    = warpSkyfile.tess_id
-JOIN warpRun
-    ON warpRun.warp_id = warpSkyfile.warp_id
-JOIN fakeRun
-    USING(fake_id)
-JOIN camRun
-    USING(cam_id)
-JOIN chipRun
-    USING(chip_id)
-JOIN rawExp
-    USING(exp_id)
+    ON diffRun.diff_id = diffInputSkyfile.diff_id
+    AND diffRun.skycell_id = diffInputSkyfile.skycell_id
+    AND diffInputSkyfile.template = 0
+-- Get warp templates
+LEFT JOIN warpRun AS warpTemplateRun
+    ON warpTemplateRun.warp_id = diffTemplateSkyfile.warp_id
+    AND diffTemplateSkyfile.stack_id IS NULL
+LEFT JOIN warpSkyfile AS warpTemplateSkyfile
+    ON warpTemplateSkyfile.warp_id = diffTemplateSkyfile.warp_id
+    AND warpTemplateSkyfile.skycell_Id = diffTemplateSkyfile.skycell_id
+    AND diffTemplateSkyfile.stack_id IS NULL
+-- Get warp inputs
+LEFT JOIN warpRun
+    ON warpRun.warp_id = diffTemplateSkyfile.warp_id
+    AND diffInputSkyfile.stack_id IS NULL
+LEFT JOIN warpSkyfile
+    ON warpSkyfile.warp_id = diffInputSkyfile.warp_id
+    AND warpSkyfile.skycell_id = diffInputSkyfile.warp_id
+    AND diffInputSkyfile.stack_id IS NULL
+-- Get stack templates
+LEFT JOIN stackRun AS stackTemplateRun
+    ON stackTemplateRun.stack_id = diffTemplateSkyfile.stack_id
+    AND diffTemplateSkyfile.warp_id IS NULL
+LEFT JOIN stackSumSkyfile AS stackTemplateSkyfile
+    ON stackTemplateSkyfile.stack_id = diffTemplateSkyfile.stack_id
+    AND diffTemplateSkyfile.warp_id IS NULL
+-- Get stack inputs
+LEFT JOIN stackRun
+    ON stackRun.stack_id = diffInputSkyfile.stack_id
+    AND diffInputSkyfile.warp_id IS NULL
+LEFT JOIN stackSumSkyfile
+    ON stackSumSkyfile.stack_id = diffInputSkyfile.stack_id
+    AND diffInputSkyfile.warp_id IS NULL
+-- Get what's already been processed
 LEFT JOIN diffSkyfile
     ON diffInputSkyfile.diff_id = diffSkyfile.diff_id
 WHERE
-  ((diffRun.state = 'new'
-      AND warpRun.state = 'full'
-      AND fakeRun.state = 'full'
-      AND camRun.state = 'full'
-      AND chipRun.state = 'full'
-      AND diffSkyfile.diff_id IS NULL)
-  OR (diffRun.state = 'update'
-      AND diffSkyfile.data_state = 'cleaned')
-  )
+-- Ready to be processed
+    ((diffRun.state = 'new'
+    AND diffSkyfile.diff_id IS NULL)
+    OR (diffRun.state = 'update'
+    AND diffSkyfile.data_state = 'cleaned')
+    )
+-- Ensure input warps are available
+    AND (diffInputSkyfile.warp_id IS NULL
+    OR (warpRun.state = 'full'
+    AND warpSkyfile.fault = 0
+    AND warpSkyfile.ignored = 0))
+-- Ensure input stacks are available
+    AND (diffInputSkyfile.stack_id IS NULL
+    OR (stackRun.state = 'full'
+    AND stackSumSkyfile.fault = 0))
+-- Ensure template warps are available
+    AND (diffTemplateSkyfile.warp_id IS NULL
+    OR (warpTemplateRun.state = 'full'
+    AND warpTemplateSkyfile.fault = 0
+    AND warpTemplateSkyfile.ignored = 0))
+-- Ensure template stacks are available
+    AND (diffTemplateSkyfile.stack_id IS NULL
+    OR (stackTemplateRun.state = 'full'
+    AND stackTemplateSkyfile.fault = 0))
+