Index: trunk/ippTools/share/Makefile.am =================================================================== --- trunk/ippTools/share/Makefile.am (revision 24167) +++ trunk/ippTools/share/Makefile.am (revision 24174) @@ -81,7 +81,9 @@ dettool_tostacked.sql \ difftool_completed_runs.sql \ - difftool_definebyquery_part1.sql \ - difftool_definebyquery_part2.sql \ - difftool_definebyquery_temp_create.sql \ + difftool_definewarpstack_part1.sql \ + difftool_definewarpstack_part2.sql \ + difftool_definewarpstack_temp_create.sql \ + difftool_definewarpwarp_temp_create.sql \ + difftool_definewarpwarp_temp_insert.sql \ difftool_definewarpwarp_select.sql \ difftool_definewarpwarp_insert.sql \ Index: trunk/ippTools/share/difftool_definebyquery.sql =================================================================== --- trunk/ippTools/share/difftool_definebyquery.sql (revision 24167) +++ (revision ) @@ -1,58 +1,0 @@ --- Get list of warps that can be diffed, with any associated diff, --- and the best stack to use as a template --- Warps without an existing diff can be identified by a NULL diff_id -SELECT - warpsToDiff.warp_id, - warpsToDiff.skycell_id, - warpsToDiff.tess_id, - warpsToDiff.filter, - warpsToDiff.good_frac, - warpsToDiff.diff_id, - current_stack_id, - best_stack_id, - exp_id -FROM ( - -- Get list of warps that can be diffed, with any associated diff - SELECT - warpSkyfile.warp_id, - warpSkyfile.skycell_id, - warpSkyfile.tess_id, - warpSkyfile.good_frac, - warpRun.label, - filter, - warpRun.label as warp_label, - diffInputs.diff_id, - diffInputs.stack2 AS current_stack_id, - rawExp.exp_id - FROM warpSkyfile - JOIN warpRun USING(warp_id) - JOIN fakeRun USING(fake_id) - JOIN camRun USING(cam_id) - JOIN chipRun USING(chip_id) - JOIN rawExp USING(exp_id) - -- Check if it has an associated diff - LEFT JOIN diffInputSkyfile AS diffInputs - ON diffInputs.warp1 = warpSkyfile.warp_id - AND diffInputs.skycell_id = warpSkyfile.skycell_id - AND diffInputs.stack2 IS NOT NULL - WHERE - warpSkyfile.fault = 0 - AND warpSkyfile.quality = 0 - -- warpsToDiff WHERE hook %s - ) AS warpsToDiff --- Get best stack as a function of skycell_id, filter -JOIN ( - SELECT - MAX(stack_id) AS best_stack_id, -- most recent stack, by virtue of auto-increment - skycell_id, - filter - FROM stackRun - JOIN stackSumSkyfile USING(stack_id) - WHERE stackRun.state = 'full' - AND stackSumSkyfile.fault = 0 - AND stackSumSkyfile.quality = 0 - -- stacksForDiff WHERE hook %s - GROUP BY - skycell_id, - filter - ) AS stacksForDiff USING(skycell_id, filter) Index: trunk/ippTools/share/difftool_definebyquery_part1.sql =================================================================== --- trunk/ippTools/share/difftool_definebyquery_part1.sql (revision 24167) +++ (revision ) @@ -1,21 +1,0 @@ -SELECT - exp_id, - warp_id, - rawExp.filter, - warpRun.label, - warpRun.tess_id, - COUNT(skycell_id) as skycell_count -FROM warpRun -JOIN warpSkyfile USING(warp_id) -JOIN fakeRun USING(fake_id) -JOIN camRun USING(cam_id) -JOIN chipRun USING(chip_id) -JOIN rawExp USING(exp_id) -LEFT JOIN diffRun USING(exp_id) -WHERE - warpSkyfile.fault = 0 - AND warpSkyfile.quality = 0 - -- warp where hook %s - -- exp where hook %s - -- diff where hook %s -GROUP BY exp_id, warp_id DESC Index: trunk/ippTools/share/difftool_definebyquery_part2.sql =================================================================== --- trunk/ippTools/share/difftool_definebyquery_part2.sql (revision 24167) +++ (revision ) @@ -1,36 +1,0 @@ --- insert skycells to diff -INSERT INTO skycellsToDiff -SELECT - 0, -- diff_id - warpSkyfile.skycell_id, - warpSkyfile.warp_id, -- warp1 - NULL, -- stack1 - NULL, -- warp2 - max_stack_id, -- stack2 - warpSkyfile.tess_id, - 0 -FROM warpSkyfile -JOIN warpRun USING(warp_id) -LEFT JOIN ( - SELECT - MAX(stack_id) AS max_stack_id, -- most recent stack, by virtue of auto-increment - stackRun.skycell_id, - stackRun.tess_id, - filter - FROM stackRun - JOIN stackSumSkyfile USING(stack_id) - WHERE stackSumSkyfile.fault = 0 - AND stackSumSkyfile.quality = 0 - AND stackRun.state = 'full' - -- stacks where hook %s - GROUP BY - skycell_id, - filter - ) as bestStacks USING(skycell_id) -WHERE - warpSkyfile.warp_id = %lld - AND warpSkyfile.fault = 0 - AND warpSkyfile.quality = 0 - AND filter = '%s' - AND warpSkyfile.tess_id = bestStacks.tess_id --- warp where hook %s Index: trunk/ippTools/share/difftool_definebyquery_temp_create.sql =================================================================== --- trunk/ippTools/share/difftool_definebyquery_temp_create.sql (revision 24167) +++ (revision ) @@ -1,10 +1,0 @@ -CREATE TEMPORARY TABLE skycellsToDiff ( -diff_id BIGINT, -skycell_id VARCHAR(64), -warp1 BIGINT, -stack1 BIGINT, -warp2 BIGINT, -stack2 BIGINT, -tess_id VARCHAR(64), -diff_skycell_id BIGINT -) ENGINE=MEMORY; Index: trunk/ippTools/share/difftool_definewarpstack.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpstack.sql (revision 24174) +++ trunk/ippTools/share/difftool_definewarpstack.sql (revision 24174) @@ -0,0 +1,58 @@ +-- Get list of warps that can be diffed, with any associated diff, +-- and the best stack to use as a template +-- Warps without an existing diff can be identified by a NULL diff_id +SELECT + warpsToDiff.warp_id, + warpsToDiff.skycell_id, + warpsToDiff.tess_id, + warpsToDiff.filter, + warpsToDiff.good_frac, + warpsToDiff.diff_id, + current_stack_id, + best_stack_id, + exp_id +FROM ( + -- Get list of warps that can be diffed, with any associated diff + SELECT + warpSkyfile.warp_id, + warpSkyfile.skycell_id, + warpSkyfile.tess_id, + warpSkyfile.good_frac, + warpRun.label, + filter, + warpRun.label as warp_label, + diffInputs.diff_id, + diffInputs.stack2 AS current_stack_id, + rawExp.exp_id + FROM warpSkyfile + JOIN warpRun USING(warp_id) + JOIN fakeRun USING(fake_id) + JOIN camRun USING(cam_id) + JOIN chipRun USING(chip_id) + JOIN rawExp USING(exp_id) + -- Check if it has an associated diff + LEFT JOIN diffInputSkyfile AS diffInputs + ON diffInputs.warp1 = warpSkyfile.warp_id + AND diffInputs.skycell_id = warpSkyfile.skycell_id + AND diffInputs.stack2 IS NOT NULL + WHERE + warpSkyfile.fault = 0 + AND warpSkyfile.quality = 0 + -- warpsToDiff WHERE hook %s + ) AS warpsToDiff +-- Get best stack as a function of skycell_id, filter +JOIN ( + SELECT + MAX(stack_id) AS best_stack_id, -- most recent stack, by virtue of auto-increment + skycell_id, + filter + FROM stackRun + JOIN stackSumSkyfile USING(stack_id) + WHERE stackRun.state = 'full' + AND stackSumSkyfile.fault = 0 + AND stackSumSkyfile.quality = 0 + -- stacksForDiff WHERE hook %s + GROUP BY + skycell_id, + filter + ) AS stacksForDiff USING(skycell_id, filter) Index: trunk/ippTools/share/difftool_definewarpstack_part1.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpstack_part1.sql (revision 24174) +++ trunk/ippTools/share/difftool_definewarpstack_part1.sql (revision 24174) @@ -0,0 +1,33 @@ +SELECT + exp_id, + warp_id, + rawExp.filter, + warpRun.label, + warpRun.tess_id, + COUNT(skycell_id) as skycell_count +FROM warpRun +JOIN warpSkyfile USING(warp_id) +JOIN fakeRun USING(fake_id) +JOIN camRun USING(cam_id) +JOIN chipRun USING(chip_id) +JOIN rawExp USING(exp_id) +LEFT JOIN ( + SELECT DISTINCT + diffRun.*, + exp_id + FROM diffRun + JOIN diffInputSkyfile USING(diff_id) + JOIN warpRun + ON warpRun.warp_id = diffInputSkyfile.warp1 + JOIN fakeRun USING(fake_id) + JOIN camRun USING(cam_id) + JOIN chipRun USING(chip_id) + WHERE warp1 IS NOT NULL +) AS diffExp USING(exp_id) +WHERE + warpSkyfile.fault = 0 + AND warpSkyfile.quality = 0 + -- warp where hook %s + -- exp where hook %s + -- diff where hook %s +GROUP BY exp_id, warp_id DESC Index: trunk/ippTools/share/difftool_definewarpstack_part2.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpstack_part2.sql (revision 24174) +++ trunk/ippTools/share/difftool_definewarpstack_part2.sql (revision 24174) @@ -0,0 +1,36 @@ +-- insert skycells to diff +INSERT INTO skycellsToDiff +SELECT + 0, -- diff_id + warpSkyfile.skycell_id, + warpSkyfile.warp_id, -- warp1 + NULL, -- stack1 + NULL, -- warp2 + max_stack_id, -- stack2 + warpSkyfile.tess_id, + 0 +FROM warpSkyfile +JOIN warpRun USING(warp_id) +LEFT JOIN ( + SELECT + MAX(stack_id) AS max_stack_id, -- most recent stack, by virtue of auto-increment + stackRun.skycell_id, + stackRun.tess_id, + filter + FROM stackRun + JOIN stackSumSkyfile USING(stack_id) + WHERE stackSumSkyfile.fault = 0 + AND stackSumSkyfile.quality = 0 + AND stackRun.state = 'full' + -- stacks where hook %s + GROUP BY + skycell_id, + filter + ) as bestStacks USING(skycell_id) +WHERE + warpSkyfile.warp_id = %lld + AND warpSkyfile.fault = 0 + AND warpSkyfile.quality = 0 + AND filter = '%s' + AND warpSkyfile.tess_id = bestStacks.tess_id +-- warp where hook %s Index: trunk/ippTools/share/difftool_definewarpstack_temp_create.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpstack_temp_create.sql (revision 24174) +++ trunk/ippTools/share/difftool_definewarpstack_temp_create.sql (revision 24174) @@ -0,0 +1,10 @@ +CREATE TEMPORARY TABLE skycellsToDiff ( +diff_id BIGINT, +skycell_id VARCHAR(64), +warp1 BIGINT, +stack1 BIGINT, +warp2 BIGINT, +stack2 BIGINT, +tess_id VARCHAR(64), +diff_skycell_id BIGINT +) ENGINE=MEMORY; Index: trunk/ippTools/share/difftool_definewarpwarp_select.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpwarp_select.sql (revision 24167) +++ trunk/ippTools/share/difftool_definewarpwarp_select.sql (revision 24174) @@ -1,6 +1,7 @@ SELECT inputWarpRun.warp_id AS input_warp_id, + inputWarpRun.tess_id AS tess_id, inputRawExp.exp_id AS input_exp_id, - inputWarpRun.tess_id AS tess_id, + -- The following trick pulls out the warp_id that has the smallest distance SUBSTRING_INDEX(GROUP_CONCAT(templateWarpRun.warp_id ORDER BY ABS(ASIN(SQRT(POW(SIN(0.5*(inputRawExp.decl - templateRawExp.decl)),2) + COS(inputRawExp.decl) * COS(templateRawExp.decl) * POW(SIN(0.5*(inputRawExp.ra - templateRawExp.ra)),2))))), ',', 1) AS template_warp_id FROM warpRun AS inputWarpRun @@ -9,5 +10,5 @@ JOIN chipRun AS inputChipRun USING(chip_id) JOIN rawExp AS inputRawExp USING(exp_id) --- To find exposures that haven't been diffed:%s LEFT JOIN diffRun ON diffRun.exp_id = inputRawExp.exp_id +-- To find exposures that haven't been diffed, insert newline here:%s LEFT JOIN diffs USING(exp_id) JOIN warpRun AS templateWarpRun ON templateWarpRun.warp_id != inputWarpRun.warp_id -- Don't use self as template! Index: trunk/ippTools/share/difftool_definewarpwarp_temp_create.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpwarp_temp_create.sql (revision 24174) +++ trunk/ippTools/share/difftool_definewarpwarp_temp_create.sql (revision 24174) @@ -0,0 +1,8 @@ +-- Exposures that have diffs +-- Temporary table has useful indices that make future query faster! +CREATE TEMPORARY TABLE diffs ( + diff_id BIGINT, + exp_id BIGINT, + PRIMARY KEY(diff_id, exp_id), + KEY(exp_id) +) ENGINE=MEMORY; Index: trunk/ippTools/share/difftool_definewarpwarp_temp_insert.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpwarp_temp_insert.sql (revision 24174) +++ trunk/ippTools/share/difftool_definewarpwarp_temp_insert.sql (revision 24174) @@ -0,0 +1,32 @@ +-- Get list of exposures that have diffs +-- Only interested in whole exposures (diffRun.exposure = 1) +INSERT INTO diffs +SELECT DISTINCT + diffWarps.diff_id, + exp_id +FROM ( + -- Forward diffs + SELECT + diffRun.diff_id, + warp1 AS warp_id + 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 + 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) Index: trunk/ippTools/share/difftool_definewarpwarp_test.sql =================================================================== --- trunk/ippTools/share/difftool_definewarpwarp_test.sql (revision 24174) +++ trunk/ippTools/share/difftool_definewarpwarp_test.sql (revision 24174) @@ -0,0 +1,82 @@ +DROP TABLE IF EXISTS diffs; + +-- Create a temporary table with useful indices +CREATE TEMPORARY TABLE diffs ( + diff_id BIGINT, + exp_id BIGINT, + PRIMARY KEY(diff_id, exp_id), + KEY(exp_id) +) ENGINE=MEMORY; + + +-- Get list of exposures that have diffs +INSERT INTO diffs +SELECT DISTINCT + diffWarps.diff_id, + exp_id +FROM ( + -- Forward diffs + SELECT + diffRun.diff_id, + warp1 AS warp_id + FROM diffRun + JOIN diffInputSkyfile USING(diff_id) + WHERE warp1 IS NOT NULL + -- WHERE hook %s + AND diffRun.label = 'ThreePi.Run2.r.v0' + UNION + -- Backward diffs + SELECT + diffRun.diff_id, + warp2 AS warp_id + FROM diffRun + JOIN diffInputSkyfile + ON diffInputSkyfile.diff_id = diffRun.diff_id + AND diffRun.bothways = 1 + WHERE warp2 IS NOT NULL + -- WHERE hook %s + AND diffRun.label = 'ThreePi.Run2.r.v0' + ) AS diffWarps +JOIN warpRun USING(warp_id) +JOIN fakeRun USING(fake_id) +JOIN camRun USING(cam_id) +JOIN chipRun USING(chip_id) +; + + + +-- EXPLAIN +SELECT + inputWarpRun.warp_id AS input_warp_id, + inputWarpRun.tess_id AS tess_id, + inputRawExp.exp_id AS input_exp_id, + SUBSTRING_INDEX(GROUP_CONCAT(templateWarpRun.warp_id ORDER BY ABS(ASIN(SQRT(POW(SIN(0.5*(inputRawExp.decl - templateRawExp.decl)),2) + COS(inputRawExp.decl) * COS(templateRawExp.decl) * POW(SIN(0.5*(inputRawExp.ra - templateRawExp.ra)),2))))), ',', 1) AS template_warp_id +FROM warpRun AS inputWarpRun +JOIN fakeRun AS inputFakeRun USING(fake_id) +JOIN camRun AS inputCamRun USING(cam_id) +JOIN chipRun AS inputChipRun USING(chip_id) +JOIN rawExp AS inputRawExp USING(exp_id) +-- To find exposures that haven't been diffed, insert newline here:%s + LEFT JOIN diffs USING(exp_id) +JOIN warpRun AS templateWarpRun + ON templateWarpRun.warp_id != inputWarpRun.warp_id -- Don't use self as template! + AND templateWarpRun.tess_id = inputWarpRun.tess_id -- Ensure using same tessellation +JOIN fakeRun AS templateFakeRun + ON templateFakeRun.fake_id = templateWarpRun.fake_id +JOIN camRun AS templateCamRun + ON templateCamRun.cam_id = templateFakeRun.cam_id +JOIN chipRun AS templateChipRun + ON templateChipRun.chip_id = templateCamRun.chip_id +JOIN rawExp AS templateRawExp + ON templateRawExp.exp_id = templateChipRun.exp_id + AND templateRawExp.filter = inputRawExp.filter +-- WHERE hook %s +WHERE (inputWarpRun.label = 'ThreePi.Run2.r.v0') + AND ((DEGREES(2*ASIN(SQRT(POW(SIN(inputRawExp.decl - templateRawExp.decl),2) + COS(inputRawExp.decl)*COS(templateRawExp.decl)*POW(SIN(inputRawExp.ra - templateRawExp.ra),2)))) < 0.10000000 + 0.00000119)) + AND ((TIME_TO_SEC(TIMEDIFF(inputRawExp.dateobs, templateRawExp.dateobs)) > 0.00000000 - 0.00000119)) + AND (templateWarpRun.label = 'ThreePi.Run2.r.v0') +-- AND (diffs.diff_id IS NULL) + AND inputWarpRun.state = 'full' +-- WHERE done +GROUP BY input_warp_id +; Index: trunk/ippTools/share/difftool_skyfile.sql =================================================================== --- trunk/ippTools/share/difftool_skyfile.sql (revision 24167) +++ trunk/ippTools/share/difftool_skyfile.sql (revision 24174) @@ -8,13 +8,12 @@ stack1, warp2, - stack2, - exp_id + stack2 FROM diffRun -JOIN diffSkyfile - USING(diff_id) -JOIN diffInputSkyfile - USING (diff_id, skycell_id) +JOIN diffSkyfile USING(diff_id) +JOIN diffInputSkyfile USING(diff_id, skycell_id) JOIN warpRun ON warpRun.warp_id = diffInputSkyfile.warp1 -JOIN rawExp - USING(exp_id) +JOIN fakeRun USING(fake_id) +JOIN camRun USING(cam_id) +JOIN chipRun USING(chip_id) +JOIN rawExp USING(exp_id) Index: trunk/ippTools/share/difftool_todiffskyfile.sql =================================================================== --- trunk/ippTools/share/difftool_todiffskyfile.sql (revision 24167) +++ trunk/ippTools/share/difftool_todiffskyfile.sql (revision 24174) @@ -9,5 +9,6 @@ diffRun.tess_id, diffRun.label, - diffRun.state + diffRun.state, + diffRun.bothways FROM diffRun JOIN diffInputSkyfile USING(diff_id) Index: trunk/ippTools/share/magicdstool_todestreak.sql =================================================================== --- trunk/ippTools/share/magicdstool_todestreak.sql (revision 24167) +++ trunk/ippTools/share/magicdstool_todestreak.sql (revision 24174) @@ -13,4 +13,5 @@ rawImfile.uri as uri, NULL as path_base, + magicRun.inverse, camProcessedExp.path_base as cam_path_base, outroot, @@ -44,4 +45,5 @@ chipProcessedImfile.uri, chipProcessedImfile.path_base, + magicRun.inverse, camProcessedExp.path_base as cam_path_base, outroot, @@ -51,4 +53,5 @@ FROM magicDSRun JOIN magicMask USING (magic_id) +JOIN magicRun USING(magic_id) JOIN camProcessedExp USING(cam_id) JOIN chipRun ON chip_id = stage_id @@ -79,4 +82,5 @@ warpSkyfile.uri, warpSkyfile.path_base, + magicRun.inverse, NULL as cam_path_base, outroot, @@ -113,6 +117,7 @@ magicRun.diff_id as stage_id, diffSkyfile.skycell_id as component, - diffSkyfile.uri, + NULL AS uri, diffSkyfile.path_base, + magicRun.inverse, NULL as cam_path_base, outroot, @@ -126,5 +131,5 @@ JOIN magicInputSkyfile USING(magic_id) JOIN diffSkyfile - ON magicInputSkyfile.diff_id = diffSkyfile.diff_id + ON magicRun.diff_id = diffSkyfile.diff_id AND magicInputSkyfile.node = diffSkyfile.skycell_id LEFT JOIN magicDSFile Index: trunk/ippTools/share/magictool_definebyquery_insert.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_insert.sql (revision 24167) +++ trunk/ippTools/share/magictool_definebyquery_insert.sql (revision 24174) @@ -3,5 +3,4 @@ SELECT @MAGIC_ID@, -- Update this with the appropriate magic_id - diff_id, skycell_id FROM diffSkyfile Index: trunk/ippTools/share/magictool_definebyquery_select.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_select.sql (revision 24167) +++ trunk/ippTools/share/magictool_definebyquery_select.sql (revision 24174) @@ -1,8 +1,36 @@ -- 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 Index: trunk/ippTools/share/magictool_inputs.sql =================================================================== --- trunk/ippTools/share/magictool_inputs.sql (revision 24167) +++ trunk/ippTools/share/magictool_inputs.sql (revision 24174) @@ -1,39 +1,73 @@ -SELECT * -FROM ( --- Single skycells +-- Get input details for a magic node SELECT magicRun.magic_id, - magicRun.state, - magicInputSkyfile.node, - diffSkyfile.diff_id, - diffSkyfile.uri, - diffSkyfile.path_base, - diffSkyfile.fault, - diffSkyfile.quality + magicRun.inverse, -- Using the inverse subtraction? + magicRun.diff_id, + magicInputs.node, + -- Only one of diff_path_base and magic_path_base should be non-NULL + -- If diff_path_base is non-NULL, then only one of warp_path_base and stack_path_base should be non-NULL + magicInputs.diff_path_base, -- path_base for the diff (if any) + magicInputs.warp_path_base, -- path_base for the template warp (if any) + magicInputs.stack_path_base, -- path_base for the template stack (if any) + magicInputs.magic_path_base -- path_base for child nodes (if any) FROM magicRun -JOIN magicInputSkyfile -USING(magic_id) -JOIN diffSkyfile - ON magicInputSkyfile.diff_id = diffSkyfile.diff_id - AND magicInputSkyfile.node = diffSkyfile.skycell_id -UNION --- Merged skycells -SELECT - magicRun.magic_id, - magicRun.state, - magicTree.node, - 0, -- no diff_id - magicNodeResult.uri, - NULL, -- magicNodeResult doesn't have a path_base - magicNodeResult.fault, - 0 as quality -FROM magicTree -JOIN magicRun - USING(magic_id) -JOIN magicNodeResult - ON magicTree.magic_id = magicNodeResult.magic_id - AND magicTree.dep = magicNodeResult.node -) as Foo -WHERE - fault = 0 - AND quality = 0 +JOIN ( + -- Single skycells: have uri=NULL + SELECT + magic_id, + magicRun.diff_id, + skycell_id AS node, + diffSkyfile.path_base AS diff_path_base, + warpSkyfile.path_base AS warp_path_base, + stackSumSkyfile.path_base AS stack_path_base, + NULL AS magic_path_base + FROM magicRun + JOIN magicTree USING(magic_id) + JOIN magicInputSkyfile USING(magic_id, node) + JOIN diffRun USING(diff_id) + JOIN diffSkyfile + ON diffSkyfile.diff_id = magicRun.diff_id + AND diffSkyfile.skycell_id = magicInputSkyfile.node + JOIN ( + -- Template for non-inverse + SELECT + magic_id, + skycell_id, + warp1 AS warp_id, + stack1 AS stack_id + FROM magicRun + JOIN diffInputSkyfile USING(diff_id) + WHERE magicRun.inverse = 0 + -- WHERE hook (magicRun.magic_id, diffInputSkyfile.skycell_id) %s + UNION + -- Template for inverse + SELECT + magic_id, + skycell_id, + warp2 AS warp_id, + stack2 AS stack_id + FROM magicRun + JOIN diffInputSkyfile USING(diff_id) + WHERE magicRun.inverse = 1 + -- WHERE hook (magicRun.magic_id, diffInputSkyfile.skycell_id) %s + ) AS diffTemplates USING(magic_id, skycell_id) + LEFT JOIN warpSkyfile USING(warp_id, skycell_id) + LEFT JOIN stackSumSkyfile USING(stack_id) + -- WHERE hook (magicRun.magic_id, magicTree.node) %s + UNION + -- Merged skycells: have diff_id=0, various_path_base=NULL + SELECT + magicTree.magic_id, + 0 AS diff_id, + magicTree.dep, + NULL AS diff_path_base, + NULL AS warp_path_base, + NULL AS stack_path_base, + magicNodeResult.path_base AS magic_path_base + FROM magicTree + JOIN magicRun USING(magic_id) + JOIN magicNodeResult + ON magicTree.magic_id = magicNodeResult.magic_id + AND magicTree.dep = magicNodeResult.node + -- WHERE hook (magicRun.magic_id, magicTree.node) %s + ) AS magicInputs USING(magic_id) Index: trunk/ippTools/share/magictool_inputskyfile.sql =================================================================== --- trunk/ippTools/share/magictool_inputskyfile.sql (revision 24167) +++ trunk/ippTools/share/magictool_inputskyfile.sql (revision 24174) @@ -1,11 +1,11 @@ SELECT magicInputSkyfile.*, - diffSkyfile.uri + magicRun.inverse, + diffSkyfile.path_base FROM magicRun -JOIN magicInputSkyfile - USING(magic_id) +JOIN magicInputSkyfile USING(magic_id) JOIN diffSkyfile - ON magicInputSkyfile.diff_id = diffSkyfile.diff_id - AND magicInputSkyfile.node = diffSkyfile.skycell_id + ON diffSkyfile.diff_id = magicRun.diff_id + AND diffSkyfile.skycell_id = magicInputSkyfile.node WHERE magicRun.state = 'new' Index: trunk/ippTools/share/magictool_toprocess_inputs.sql =================================================================== --- trunk/ippTools/share/magictool_toprocess_inputs.sql (revision 24167) +++ trunk/ippTools/share/magictool_toprocess_inputs.sql (revision 24174) @@ -1,66 +1,22 @@ -SELECT * FROM -(SELECT +SELECT magicTree.*, + magicRun.workdir, rawExp.exp_id, rawExp.camera, --- diffSkyfile.path_base, - magicRun.workdir, -- convert magic_id into a boolean value (1 or 0) -- note that the type stays a 64 bit int - magicNodeResult.magic_id IS TRUE as done, - magicNodeResult.fault IS TRUE as bad, - warpSkyfile.uri as template_uri -FROM magicTree -JOIN magicRun USING(magic_id) + magicNodeResult.magic_id IS TRUE AS done, + magicNodeResult.fault IS TRUE AS bad +FROM magicRun +JOIN magicTree USING(magic_id) JOIN magicInputSkyfile USING(magic_id, node) -JOIN diffSkyfile - ON magicInputSkyfile.diff_id = diffSkyfile.diff_id - AND magicInputSkyfile.node = diffSkyfile.skycell_id -JOIN diffInputSkyfile - ON diffSkyfile.diff_id = diffInputSkyfile.diff_id - AND diffSkyfile.skycell_id = diffInputSkyfile.skycell_id -JOIN warpSkyfile - ON diffInputSkyfile.warp2 = warpSkyfile.warp_id - AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id JOIN rawExp USING(exp_id) -LEFT JOIN magicNodeResult - ON magicTree.magic_id = magicNodeResult.magic_id - AND magicTree.node = magicNodeResult.node +JOIN diffSkyfile -- only get nodes that match a skycell + ON diffSkyfile.diff_id = magicRun.diff_id + AND diffSkyfile.skycell_id = magicInputSkyfile.node +LEFT JOIN magicNodeResult USING(magic_id, node) WHERE magicRun.state = 'new' AND magicNodeResult.magic_id IS NULL AND magicNodeResult.node IS NULL - -- where hook 1 %s -UNION -SELECT - magicTree.*, - rawExp.exp_id, - rawExp.camera, --- diffSkyfile.path_base, - magicRun.workdir, - -- convert magic_id into a boolean value (1 or 0) - -- note that the type stays a 64 bit int - magicNodeResult.magic_id IS TRUE as done, - magicNodeResult.fault IS TRUE as bad, - stackSumSkyfile.uri as template_uri -FROM magicTree -JOIN magicRun USING(magic_id) -JOIN magicInputSkyfile USING(magic_id, node) -JOIN diffSkyfile - ON magicInputSkyfile.diff_id = diffSkyfile.diff_id - AND magicInputSkyfile.node = diffSkyfile.skycell_id -JOIN diffInputSkyfile - ON diffSkyfile.diff_id = diffInputSkyfile.diff_id - AND diffSkyfile.skycell_id = diffInputSkyfile.skycell_id -JOIN stackSumSkyfile - ON diffInputSkyfile.stack2 = stackSumSkyfile.stack_id -JOIN rawExp USING(exp_id) -LEFT JOIN magicNodeResult - ON magicTree.magic_id = magicNodeResult.magic_id - AND magicTree.node = magicNodeResult.node -WHERE - magicRun.state = 'new' - AND magicNodeResult.magic_id IS NULL - AND magicNodeResult.node IS NULL - -- where hook 2 %s -) as foo +-- WHERE hook %s Index: trunk/ippTools/share/magictool_toprocess_tree.sql =================================================================== --- trunk/ippTools/share/magictool_toprocess_tree.sql (revision 24167) +++ trunk/ippTools/share/magictool_toprocess_tree.sql (revision 24174) @@ -1,21 +1,18 @@ SELECT magicTree.*, - exp_id, - camera, magicRun.workdir, + rawExp.exp_id, + rawExp.camera, -- convert magic_id into a boolean value (1 or 0) -- note that the type stays a 64 bit int - magicNodeResult.magic_id IS TRUE as done, - magicNodeResult.fault IS TRUE as bad, - 'notused' as template_uri + magicNodeResult.magic_id IS TRUE AS done, + magicNodeResult.fault IS TRUE AS bad FROM magicTree JOIN magicRun USING(magic_id) JOIN rawExp USING(exp_id) -LEFT JOIN magicNodeResult - ON magicTree.magic_id = magicNodeResult.magic_id - AND magicTree.node = magicNodeResult.node +LEFT JOIN magicNodeResult USING(magic_id, node) WHERE magicRun.state = 'new' - -- where hook %s +-- WHERE hook %s ORDER BY magicRun.magic_id Index: trunk/ippTools/share/magictool_totree.sql =================================================================== --- trunk/ippTools/share/magictool_totree.sql (revision 24167) +++ trunk/ippTools/share/magictool_totree.sql (revision 24174) @@ -6,6 +6,7 @@ ra, decl, - tess_id + diffRun.tess_id FROM magicRun +JOIN diffRun USING(diff_id) JOIN rawExp USING(exp_id) LEFT JOIN magicTree Index: trunk/ippTools/share/pxadmin_create_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_create_tables.sql (revision 24167) +++ trunk/ippTools/share/pxadmin_create_tables.sql (revision 24174) @@ -877,5 +877,6 @@ KEY(state), KEY(skycell_id), - KEY(tess_id) + KEY(tess_id), + KEY(label) ) ENGINE=innodb DEFAULT CHARSET=latin1; @@ -933,5 +934,6 @@ registered DATETIME, tess_id VARCHAR(64), - exp_id BIGINT, + bothways TINYINT DEFAULT 0, + exposure TINYINT DEFAULT 0, magicked TINYINT, PRIMARY KEY(diff_id), @@ -939,4 +941,5 @@ KEY(state), KEY(tess_id), + KEY(label), FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id) ) ENGINE=innodb DEFAULT CHARSET=latin1; @@ -969,5 +972,4 @@ diff_id BIGINT, skycell_id VARCHAR(64), - uri VARCHAR(255), path_base VARCHAR(255), data_state VARCHAR(64), @@ -1006,4 +1008,5 @@ exp_id BIGINT, diff_id BIGINT, + inverse TINYINT NOT NULL DEFAULT 0, state VARCHAR(64), workdir VARCHAR(255), @@ -1046,5 +1049,5 @@ magic_id BIGINT, node VARCHAR(64), - uri VARCHAR(255), + path_base VARCHAR(255), fault SMALLINT, PRIMARY KEY(magic_id, node), Index: trunk/ippTools/share/pxadmin_drop_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_drop_tables.sql (revision 24167) +++ trunk/ippTools/share/pxadmin_drop_tables.sql (revision 24174) @@ -48,5 +48,4 @@ DROP TABLE IF EXISTS magicNodeResult; DROP TABLE IF EXISTS magicMask; -DROP TABLE IF EXISTS magicSkyfileMask; DROP TABLE IF EXISTS magicDSRun; DROP TABLE IF EXISTS magicDSFile;