Index: trunk/ippTools/share/Makefile.am =================================================================== --- trunk/ippTools/share/Makefile.am (revision 20934) +++ trunk/ippTools/share/Makefile.am (revision 20973) @@ -70,5 +70,8 @@ dettool_toresidimfile.sql \ dettool_tostacked.sql \ - difftool_definebyquery.sql \ + difftool_completed_runs.sql \ + difftool_definebyquery_part1.sql \ + difftool_definebyquery_part2.sql \ + difftool_definebyquery_temp_create.sql \ difftool_donecleanup.sql \ difftool_inputskyfile.sql \ Index: trunk/ippTools/share/difftool_completed_runs.sql =================================================================== --- trunk/ippTools/share/difftool_completed_runs.sql (revision 20973) +++ trunk/ippTools/share/difftool_completed_runs.sql (revision 20973) @@ -0,0 +1,17 @@ +SELECT DISTINCT + diff_id +FROM ( + SELECT + COUNT(diffInputSkyfile.skycell_id), COUNT(diffSkyfile.skycell_id), + diffSkyfile.* + FROM diffRun + JOIN diffInputSkyfile USING(diff_id) + LEFT JOIN diffSkyfile USING(diff_id, skycell_id) + WHERE + diffRun.state = 'new' + GROUP BY + diffInputSkyfile.diff_id + HAVING + COUNT(diffInputSkyfile.skycell_id) = COUNT(diffSkyfile.skycell_id) + AND SUM(diffSkyfile.fault) = 0 + ) as Foo Index: trunk/ippTools/share/difftool_definebyquery.sql =================================================================== --- trunk/ippTools/share/difftool_definebyquery.sql (revision 20934) +++ trunk/ippTools/share/difftool_definebyquery.sql (revision 20973) @@ -9,7 +9,7 @@ warpsToDiff.good_frac, warpsToDiff.diff_id, - warpsToDiff.kind, current_stack_id, - best_stack_id + best_stack_id, + exp_id FROM ( -- Get list of warps that can be diffed, with any associated diff @@ -23,6 +23,6 @@ warpRun.label as warp_label, diffInputs.diff_id, - diffInputs.kind, - diffTemplates.stack_id AS current_stack_id + diffInputs.stack2 AS current_stack_id, + rawExp.exp_id FROM warpSkyfile JOIN warpRun USING(warp_id) @@ -33,11 +33,7 @@ -- Check if it has an associated diff LEFT JOIN diffInputSkyfile AS diffInputs - ON diffInputs.warp_id = warpSkyfile.warp_id + ON diffInputs.warp1 = warpSkyfile.warp_id AND diffInputs.skycell_id = warpSkyfile.skycell_id - AND diffInputs.template = 0 -- only join input files - -- Get the stack_id currently used as a template, if any - LEFT JOIN diffInputSkyfile AS diffTemplates - ON diffTemplates.diff_id = diffInputs.diff_id - AND diffTemplates.template != 0 -- only join template files + AND diffInputs.stack2 IS NOT NULL WHERE warpSkyfile.fault = 0 Index: trunk/ippTools/share/difftool_definebyquery_part1.sql =================================================================== --- trunk/ippTools/share/difftool_definebyquery_part1.sql (revision 20973) +++ trunk/ippTools/share/difftool_definebyquery_part1.sql (revision 20973) @@ -0,0 +1,22 @@ + 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.ignored = 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 20973) +++ trunk/ippTools/share/difftool_definebyquery_part2.sql (revision 20973) @@ -0,0 +1,33 @@ +-- insert skycells to diff +INSERT INTO skycellsToDiff + SELECT + 0, -- diff_id + warpSkyfile.skycell_id, + warp_id, -- warp1 + NULL, -- stack1 + NULL, -- warp2 + max_stack_id, -- stack2 + warpSkyfile.tess_id + 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) + GROUP BY + skycell_id, + filter + ) as bestStacks + USING(skycell_id) + WHERE + warp_id = %lld + AND ignored = 0 + AND filter = '%s' + AND warpSkyfile.tess_id = bestStacks.tess_id + -- (good fraction test goes here for example also above) + -- warp where hook %s + -- stacks where hook %s Index: trunk/ippTools/share/difftool_definebyquery_temp_create.sql =================================================================== --- trunk/ippTools/share/difftool_definebyquery_temp_create.sql (revision 20973) +++ trunk/ippTools/share/difftool_definebyquery_temp_create.sql (revision 20973) @@ -0,0 +1,9 @@ +CREATE TEMPORARY TABLE skycellsToDiff ( +diff_id BIGINT, +skycell_id VARCHAR(64), +warp1 BIGINT, +stack1 BIGINT, +warp2 BIGINT, +stack2 BIGINT, +tess_id VARCHAR(64) +) ENGINE=MEMORY; Index: trunk/ippTools/share/difftool_inputskyfile.sql =================================================================== --- trunk/ippTools/share/difftool_inputskyfile.sql (revision 20934) +++ trunk/ippTools/share/difftool_inputskyfile.sql (revision 20973) @@ -1,12 +1,13 @@ SELECT * FROM - (SELECT + (SELECT + -- warp input diffRun.diff_id, - diffRun.skycell_id, - diffRun.tess_id, + diffInputSkyfile.skycell_id, + diffInputSkyfile.tess_id, 0 as stack_id, warpSkyfile.warp_id, warpSkyfile.uri, warpSkyfile.path_base, - diffInputSkyfile.template, + 0 as template, rawExp.camera FROM diffRun @@ -14,9 +15,47 @@ USING(diff_id) JOIN warpSkyfile - ON diffInputSkyfile.warp_id = warpSkyfile.warp_id + ON diffInputSkyfile.warp1 = warpSkyfile.warp_id AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id AND diffInputSkyfile.tess_id = warpSkyfile.tess_id JOIN warpRun - ON diffInputSkyfile.warp_id = warpRun.warp_id + ON diffInputSkyfile.warp1 = warpRun.warp_id + JOIN fakeRun + USING(fake_id) + JOIN camRun + USING(cam_id) + JOIN chipRun + USING(chip_id) + JOIN chipProcessedImfile + USING(chip_id) + JOIN rawExp + ON chipRun.exp_id = rawExp.exp_id + WHERE + diffRun.state = 'new' + AND warpRun.state = 'full' + AND fakeRun.state = 'full' + AND camRun.state = 'full' + AND chipRun.state = 'full' + -- where hook %s + UNION + SELECT + -- warp template + diffRun.diff_id, + diffInputSkyfile.skycell_id, + diffInputSkyfile.tess_id, + 0 as stack_id, + warpSkyfile.warp_id, + warpSkyfile.uri, + warpSkyfile.path_base, + 0 as template, + rawExp.camera + FROM diffRun + JOIN diffInputSkyfile + USING(diff_id) + JOIN warpSkyfile + ON diffInputSkyfile.warp2 = warpSkyfile.warp_id + AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id + AND diffInputSkyfile.tess_id = warpSkyfile.tess_id + JOIN warpRun + ON diffInputSkyfile.warp2 = warpRun.warp_id JOIN fakeRun USING(fake_id) @@ -38,12 +77,13 @@ UNION SELECT + -- stack input diffRun.diff_id, - diffRun.skycell_id, - diffRun.tess_id, + diffInputSkyfile.skycell_id, + diffInputSkyfile.tess_id, stackSumSkyfile.stack_id, 0 as warp_id, stackSumSkyfile.uri, stackSumSkyfile.path_base, - diffInputSkyfile.template, + 1 as template, rawExp.camera FROM diffRun @@ -51,25 +91,31 @@ USING(diff_id) JOIN stackSumSkyfile - ON diffInputSkyfile.stack_id = stackSumSkyfile.stack_id - JOIN stackInputSkyfile - ON diffInputSkyfile.stack_id = stackInputSkyfile.stack_id - JOIN warpRun - ON stackInputSkyfile.warp_id = warpRun.warp_id - JOIN fakeRun - USING(fake_id) - JOIN camRun - USING(cam_id) - JOIN chipRun - USING(chip_id) - JOIN chipProcessedImfile - USING(chip_id) + ON diffInputSkyfile.stack1 = stackSumSkyfile.stack_id JOIN rawExp - ON chipRun.exp_id = rawExp.exp_id + USING(exp_id) WHERE diffRun.state = 'new' - AND warpRun.state = 'full' - AND fakeRun.state = 'full' - AND camRun.state = 'full' - AND chipRun.state = 'full' + -- where hook %s + UNION + SELECT + -- stack template + diffRun.diff_id, + diffInputSkyfile.skycell_id, + diffInputSkyfile.tess_id, + stackSumSkyfile.stack_id, + 0 as warp_id, + stackSumSkyfile.uri, + stackSumSkyfile.path_base, + 1 as template, + rawExp.camera + FROM diffRun + JOIN diffInputSkyfile + USING(diff_id) + JOIN stackSumSkyfile + ON diffInputSkyfile.stack2 = stackSumSkyfile.stack_id + JOIN rawExp + USING(exp_id) + WHERE + diffRun.state = 'new' -- where hook %s ) as Foo Index: trunk/ippTools/share/difftool_skyfile.sql =================================================================== --- trunk/ippTools/share/difftool_skyfile.sql (revision 20934) +++ trunk/ippTools/share/difftool_skyfile.sql (revision 20973) @@ -1,15 +1,18 @@ SELECT - diffRun.skycell_id, + diffSkyfile.*, diffRun.tess_id, diffRun.state, - diffSkyfile.*, - (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id - AND diffInputSkyfile.template = 0 ) AS warp_id_temp_0, - (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id - AND diffInputSkyfile.template = 0 ) AS stack_id_temp_0, - (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id - AND diffInputSkyfile.template = 1 ) AS warp_id_temp_1, - (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id - AND diffInputSkyfile.template = 1 ) AS stack_id_temp_1 + warp1, + stack1, + warp2, + stack2 +-- (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id +-- AND diffInputSkyfile.template = 0 ) AS warp_id_temp_0, +-- (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id +-- AND diffInputSkyfile.template = 0 ) AS stack_id_temp_0, +-- (SELECT warp_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id +-- AND diffInputSkyfile.template = 1 ) AS warp_id_temp_1, +-- (SELECT stack_id FROM diffInputSkyfile WHERE diffInputSkyfile.diff_id = diffRun.diff_id +-- AND diffInputSkyfile.template = 1 ) AS stack_id_temp_1 FROM diffRun JOIN diffSkyfile @@ -17,13 +20,6 @@ JOIN diffInputSkyfile ON diffInputSkyfile.diff_id = diffRun.diff_id - AND diffInputSkyfile.template = 0 JOIN warpRun - USING(warp_id) -JOIN fakeRun - USING(fake_id) -JOIN camRun - USING(cam_id) -JOIN chipRun - USING(chip_id) + ON warpRun.warp_id = diffInputSkyfile.warp1 JOIN rawExp USING(exp_id) Index: trunk/ippTools/share/difftool_todiffskyfile.sql =================================================================== --- trunk/ippTools/share/difftool_todiffskyfile.sql (revision 20934) +++ trunk/ippTools/share/difftool_todiffskyfile.sql (revision 20973) @@ -6,48 +6,56 @@ 0 as diff_skyfile_id, diffRun.workdir, - diffRun.skycell_id, + diffInputSkyfile.skycell_id, diffRun.tess_id, diffRun.label, diffRun.state FROM diffRun +JOIN diffInputSkyfile USING(diff_id) + -- 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 +-- 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 - ON diffRun.diff_id = diffInputSkyfile.diff_id - AND diffRun.skycell_id = diffInputSkyfile.skycell_id - AND diffInputSkyfile.template = 0 +-- JOIN diffInputSkyfile +-- 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.warp_id IS NOT NULL + ON warpTemplateRun.warp_id = diffInputSkyfile.warp2 + AND diffInputSkyfile.warp2 IS NOT NULL LEFT JOIN warpSkyfile AS warpTemplateSkyfile ON warpTemplateSkyfile.warp_id = warpTemplateRun.warp_id - AND warpTemplateSkyfile.skycell_id = diffTemplateSkyfile.skycell_id + AND warpTemplateSkyfile.skycell_id = diffInputSkyfile.skycell_id + -- Get warp inputs LEFT JOIN warpRun - ON warpRun.warp_id = diffInputSkyfile.warp_id - AND diffInputSkyfile.warp_id IS NOT NULL + ON warpRun.warp_id = diffInputSkyfile.warp1 + AND diffInputSkyfile.warp1 IS NOT NULL LEFT JOIN warpSkyfile ON warpSkyfile.warp_id = warpRun.warp_id AND warpSkyfile.skycell_id = diffInputSkyfile.skycell_id + -- Get stack templates LEFT JOIN stackRun AS stackTemplateRun - ON stackTemplateRun.stack_id = diffTemplateSkyfile.stack_id + ON stackTemplateRun.stack_id = diffInputSkyfile.stack2 + AND diffInputSkyfile.stack2 IS NOT NULL LEFT JOIN stackSumSkyfile AS stackTemplateSkyfile ON stackTemplateSkyfile.stack_id = stackTemplateRun.stack_id + -- Get stack inputs LEFT JOIN stackRun - ON stackRun.stack_id = diffInputSkyfile.stack_id - AND diffInputSkyfile.warp_id IS NULL + ON stackRun.stack_id = diffInputSkyfile.stack1 + AND diffInputSkyfile.stack1 IS NOT NULL LEFT JOIN stackSumSkyfile ON stackSumSkyfile.stack_id = stackRun.stack_id - AND diffInputSkyfile.warp_id IS NULL + -- Get what's already been processed LEFT JOIN diffSkyfile ON diffInputSkyfile.diff_id = diffSkyfile.diff_id + AND diffInputSkyfile.skycell_id = diffSkyfile.skycell_id WHERE -- Ready to be processed @@ -58,19 +66,19 @@ ) -- Ensure input warps are available - AND (diffInputSkyfile.warp_id IS NULL + AND (diffInputSkyfile.warp1 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 + AND (diffInputSkyfile.stack1 IS NULL OR (stackRun.state = 'full' AND stackSumSkyfile.fault = 0)) -- Ensure template warps are available - AND (diffTemplateSkyfile.warp_id IS NULL + AND (diffInputSkyfile.warp2 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 + AND (diffInputSkyfile.stack2 IS NULL OR (stackTemplateRun.state = 'full' AND stackTemplateSkyfile.fault = 0)) Index: trunk/ippTools/share/magicdstool_completed_runs.sql =================================================================== --- trunk/ippTools/share/magicdstool_completed_runs.sql (revision 20934) +++ trunk/ippTools/share/magicdstool_completed_runs.sql (revision 20973) @@ -65,15 +65,13 @@ JOIN magicRun USING (magic_id) JOIN magicInputSkyfile USING(magic_id) - -- Do we really need to join back to diffInputSkyfile here? - JOIN diffRun USING(diff_id) - JOIN diffSkyfile USING(diff_id) - JOIN diffInputSkyfile USING(diff_id) + JOIN diffSkyfile + ON magicInputSkyfile.diff_id = diffSkyfile.diff_id + AND magicInputSkyfile.node = diffSkyfile.skycell_id LEFT JOIN magicDSFile ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id - AND magicDSFile.component = diffInputSkyfile.skycell_id + AND magicDSFile.component = diffSkyfile.skycell_id WHERE magicDSRun.state = 'run' AND magicDSRun.stage = 'diff' - AND diffRun.state = 'full' AND diffSkyfile.fault = 0 GROUP BY Index: trunk/ippTools/share/magicdstool_getrunids.sql =================================================================== --- trunk/ippTools/share/magicdstool_getrunids.sql (revision 20934) +++ trunk/ippTools/share/magicdstool_getrunids.sql (revision 20973) @@ -8,9 +8,14 @@ FROM magicRun JOIN magicInputSkyfile USING(magic_id) -JOIN diffInputSkyfile USING(diff_id) -JOIN warpRun USING(warp_id) +JOIN diffSkyfile + ON magicInputSkyfile.diff_id = diffSkyfile.diff_id + AND magicInputSkyfile.node = diffSkyfile.skycell_id +JOIN diffInputSkyfile + ON diffInputSkyfile.diff_id = diffSkyfile.diff_id + AND diffInputSkyfile.skycell_id = diffSkyfile.skycell_id +JOIN warpRun + ON diffInputSkyfile.warp1 = warp_id JOIN fakeRun USING(fake_id) JOIN camRun USING(cam_id) JOIN chipRun USING(chip_id) -WHERE template = 0 -AND magic_id = %ld +WHERE magic_id = %ld Index: trunk/ippTools/share/magicdstool_getskycells.sql =================================================================== --- trunk/ippTools/share/magicdstool_getskycells.sql (revision 20934) +++ trunk/ippTools/share/magicdstool_getskycells.sql (revision 20973) @@ -1,20 +1,20 @@ SELECT DISTINCT diffSkyfile.diff_id, - diffRun.skycell_id, + diffSkyfile.skycell_id, diffSkyfile.uri, diffSkyfile.path_base FROM magicDSRun JOIN magicRun USING(magic_id) -JOIN magicInputSkyfile USING(magic_id) -JOIN diffRun USING(diff_id) -JOIN diffSkyfile USING(diff_id) +JOIN magicInputSkyfile USING(magic_id, diff_id) +JOIN diffSkyfile + ON magicInputSkyfile.diff_id = diffSkyfile.diff_id + AND magicInputSkyfile.node = diffSkyfile.skycell_id JOIN diffInputSkyfile - ON diffInputSkyfile.diff_id = diffRun.diff_id - AND diffInputSkyfile.skycell_id = diffRun.skycell_id + ON diffInputSkyfile.diff_id = diffSkyfile.diff_id + AND diffInputSkyfile.skycell_id = diffSkyfile.skycell_id -- Want input warps only - AND diffInputSkyfile.warp_id IS NOT NULL - AND diffInputSkyfile.template = 0 + AND diffInputSkyfile.warp1 IS NOT NULL JOIN warpSkyCellMap - ON warpSkyCellMap.warp_id = diffInputSkyfile.warp_id + ON warpSkyCellMap.warp_id = diffInputSkyfile.warp1 AND warpSkyCellMap.skycell_id = diffInputSkyfile.skycell_id JOIN warpSkyfile @@ -23,5 +23,4 @@ AND warpSkyfile.ignored = 0 WHERE - diffRun.state = 'full' - AND diffSkyfile.fault = 0 + diffSkyfile.fault = 0 AND magic_ds_id = %lld Index: trunk/ippTools/share/magicdstool_todestreak.sql =================================================================== --- trunk/ippTools/share/magicdstool_todestreak.sql (revision 20934) +++ trunk/ippTools/share/magicdstool_todestreak.sql (revision 20973) @@ -106,6 +106,6 @@ magicMask.uri as streaks_uri, stage, - diff_id as stage_id, - diffInputSkyfile.skycell_id as component, + magicRun.diff_id as stage_id, + diffSkyfile.skycell_id as component, diffSkyfile.uri, diffSkyfile.path_base, @@ -120,14 +120,13 @@ JOIN magicDSRun USING(magic_id) JOIN magicInputSkyfile USING(magic_id) -JOIN diffRun USING(diff_id) -JOIN diffSkyfile USING(diff_id) -JOIN diffInputSkyfile USING(diff_id) +JOIN diffSkyfile + ON magicInputSkyfile.diff_id = diffSkyfile.diff_id + AND magicInputSkyfile.node = diffSkyfile.skycell_id LEFT JOIN magicDSFile ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id - AND magicDSFile.component = diffInputSkyfile.skycell_id + AND magicDSFile.component = diffSkyfile.skycell_id WHERE magicDSRun.state = 'run' AND magicDSRun.stage = 'diff' - AND diffRun.state = 'full' AND diffSkyfile.fault = 0 AND magicDSFile.component IS NULL Index: trunk/ippTools/share/magictool_definebyquery_insert.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_insert.sql (revision 20934) +++ trunk/ippTools/share/magictool_definebyquery_insert.sql (revision 20973) @@ -5,5 +5,6 @@ diff_id, skycell_id -FROM magicBestDiffs +FROM diffSkyfile WHERE - exp_id = @EXP_ID@ -- Update this with the appropriate exp_id + diff_id = @DIFF_ID@ -- Update this with the appropriate diff_id + AND fault = 0 Index: trunk/ippTools/share/magictool_definebyquery_select.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_select.sql (revision 20934) +++ trunk/ippTools/share/magictool_definebyquery_select.sql (revision 20973) @@ -1,38 +1,9 @@ -- Get a list of exposures on which magic may be performed -SELECT DISTINCT +SELECT exp_id, - filter, - num_todo, - num_done, - MAX(magic_id) -FROM ( - -- Number of skycells as a function of exposure - SELECT - exp_id, - filter, - COUNT(DISTINCT warpSkyfile.tess_id,warpSkyfile.skycell_id) AS num_todo - FROM rawExp - JOIN chipRun USING(exp_id) - JOIN camRun USING(chip_id) - JOIN fakeRun USING(cam_id) - JOIN warpRun USING(fake_id) - JOIN warpSkyCellMap USING(warp_id) - JOIN warpSkyfile USING(warp_id, skycell_id) - JOIN diffInputSkyfile USING(warp_id,skycell_id) - JOIN diffRun USING(diff_id) - WHERE - warpSkyfile.ignored = 0 - -- magicSkycellNums WHERE hook %s - GROUP BY - exp_id - ) AS magicSkycellNums -JOIN ( - -- Number of completed diffs for an exposure - SELECT - exp_id, - COUNT(diff_id) AS num_done - FROM magicBestDiffs - GROUP BY - exp_id - ) AS magicDiffNums USING(exp_id) + MAX(diffRun.diff_id) AS diff_id +FROM diffRun +JOIN rawExp USING(exp_id) LEFT JOIN magicRun USING(exp_id) +-- WHERE hook %s +GROUP BY exp_id Index: trunk/ippTools/share/magictool_definebyquery_temp_insert.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_temp_insert.sql (revision 20934) +++ trunk/ippTools/share/magictool_definebyquery_temp_insert.sql (revision 20973) @@ -14,7 +14,6 @@ JOIN warpSkyfile USING(warp_id, skycell_id) JOIN diffInputSkyfile - ON diffInputSkyfile.warp_id = warpSkyfile.warp_id + ON diffInputSkyfile.warp1 = warpSkyfile.warp_id AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id - AND diffInputSkyfile.template = 0 -- selecting inputs only JOIN diffRun USING(diff_id) JOIN diffSkyfile USING(diff_id) Index: trunk/ippTools/share/magictool_inputs.sql =================================================================== --- trunk/ippTools/share/magictool_inputs.sql (revision 20934) +++ trunk/ippTools/share/magictool_inputs.sql (revision 20973) @@ -10,9 +10,10 @@ diffSkyfile.path_base, diffSkyfile.fault -FROM magicInputSkyfile +FROM magicRun +JOIN magicInputSkyfile +USING(magic_id) JOIN diffSkyfile - USING(diff_id) -JOIN magicRun - USING(magic_id) + ON magicInputSkyfile.diff_id = diffSkyfile.diff_id + AND magicInputSkyfile.node = diffSkyfile.skycell_id UNION -- Merged skycells @@ -21,5 +22,5 @@ magicRun.state, magicTree.node, - 0, + 0, -- no diff_id magicNodeResult.uri, NULL, -- magicNodeResult doesn't have a path_base Index: trunk/ippTools/share/magictool_inputskyfile.sql =================================================================== --- trunk/ippTools/share/magictool_inputskyfile.sql (revision 20934) +++ trunk/ippTools/share/magictool_inputskyfile.sql (revision 20973) @@ -2,9 +2,10 @@ magicInputSkyfile.*, diffSkyfile.uri -FROM magicInputSkyfile -JOIN magicRun +FROM magicRun +JOIN magicInputSkyfile USING(magic_id) JOIN diffSkyfile - USING(diff_id) + ON magicInputSkyfile.diff_id = diffSkyfile.diff_id + AND magicInputSkyfile.node = diffSkyfile.skycell_id WHERE magicRun.state = 'run' Index: trunk/ippTools/share/magictool_toprocess_inputs.sql =================================================================== --- trunk/ippTools/share/magictool_toprocess_inputs.sql (revision 20934) +++ trunk/ippTools/share/magictool_toprocess_inputs.sql (revision 20973) @@ -11,5 +11,7 @@ JOIN magicRun USING(magic_id) JOIN magicInputSkyfile USING(magic_id, node) -JOIN diffSkyfile USING(diff_id) +JOIN diffSkyfile + ON magicRun.diff_id = diffSkyfile.diff_id + AND magicInputSkyfile.node = diffSkyfile.skycell_id JOIN rawExp USING(exp_id) LEFT JOIN magicNodeResult Index: trunk/ippTools/share/pxadmin_create_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_create_tables.sql (revision 20934) +++ trunk/ippTools/share/pxadmin_create_tables.sql (revision 20973) @@ -238,7 +238,10 @@ fault SMALLINT NOT NULL, epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + raw_image_id BIGINT AUTO_INCREMENT, + magicked TINYINT, PRIMARY KEY(exp_id, class_id), KEY(tmp_class_id), KEY(fault), + KEY(raw_image_id), UNIQUE KEY(exp_id, tmp_class_id), FOREIGN KEY (exp_id, tmp_class_id) @@ -330,7 +333,10 @@ path_base VARCHAR(255), fault SMALLINT NOT NULL, + chip_image_id BIGINT AUTO_INCREMENT, + magicked BIGINT, PRIMARY KEY(chip_id, exp_id, class_id), KEY(data_state), KEY(fault), + KEY(chip_image_id), FOREIGN KEY (chip_id, exp_id) REFERENCES chipRun(chip_id, exp_id), @@ -815,5 +821,4 @@ end_stage VARCHAR(64), registered DATETIME, - magiced TINYINT, PRIMARY KEY(warp_id), KEY(warp_id), @@ -824,5 +829,4 @@ KEY(label), KEY(end_stage), - KEY(magiced), INDEX(warp_id, fake_id), FOREIGN KEY (fake_id) @@ -861,7 +865,10 @@ ignored TINYINT, fault SMALLINT, + warp_image_id BIGINT AUTO_INCREMENT, + magicked TINYINT, PRIMARY KEY(warp_id, skycell_id, tess_id), KEY(good_frac), KEY(ignored), KEY(fault), + KEY(warp_image_id), FOREIGN KEY (warp_id, skycell_id, tess_id) REFERENCES warpSkyCellMap(warp_id, skycell_id, tess_id) @@ -941,34 +948,38 @@ dvodb VARCHAR(255), registered DATETIME, - skycell_id VARCHAR(64), tess_id VARCHAR(64), + exp_id BIGINT, PRIMARY KEY(diff_id), KEY(diff_id), KEY(state), - KEY(skycell_id), - KEY(tess_id) + KEY(tess_id), + FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id) ) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE diffInputSkyfile ( diff_id BIGINT, - template TINYINT, - stack_id BIGINT, - warp_id BIGINT, skycell_id VARCHAR(64), + warp1 BIGINT, + stack1 BIGINT, + warp2 BIGINT, + stack2 BIGINT, tess_id VARCHAR(64), - kind VARCHAR(64), - PRIMARY KEY(diff_id, template), - KEY(stack_id), - KEY(warp_id), + PRIMARY KEY(diff_id, skycell_id), + KEY(warp1), + KEY(warp2), + KEY(stack1), + KEY(stack2), KEY(skycell_id), KEY(tess_id), - KEY(kind), FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id), - FOREIGN KEY (stack_id) REFERENCES stackSumSkyfile(stack_id), - FOREIGN KEY (warp_id, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id) + FOREIGN KEY (warp1, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id), + FOREIGN KEY (warp2, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id), + FOREIGN KEY (stack1) REFERENCES stackSumSkyfile(stack_id), + FOREIGN KEY (stack2) REFERENCES stackSumSkyfile(stack_id) ) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE diffSkyfile ( diff_id BIGINT, + skycell_id VARCHAR(64), uri VARCHAR(255), path_base VARCHAR(255), @@ -993,7 +1004,10 @@ good_frac FLOAT, fault SMALLINT, - PRIMARY KEY(diff_id), + diff_image_id BIGINT AUTO_INCREMENT, + magicked TINYINT, + PRIMARY KEY(diff_id, skycell_id), KEY(good_frac), KEY(fault), + KEY(diff_image_id), FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id) ) ENGINE=innodb DEFAULT CHARSET=latin1; @@ -1002,4 +1016,5 @@ magic_id BIGINT AUTO_INCREMENT, exp_id BIGINT, + diff_id BIGINT, state VARCHAR(64), workdir VARCHAR(255), @@ -1015,5 +1030,6 @@ KEY(label), KEY(fault), - FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id) + FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id), + FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id) ) ENGINE=innodb DEFAULT CHARSET=latin1; @@ -1022,7 +1038,7 @@ diff_id BIGINT, node VARCHAR(64), - PRIMARY KEY(magic_id, diff_id), + PRIMARY KEY(magic_id, diff_id, node), FOREIGN KEY (magic_id) REFERENCES magicRun(magic_id), - FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id) + FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id) ) ENGINE=innodb DEFAULT CHARSET=latin1; Index: trunk/ippTools/share/warptool_tooverlap.sql =================================================================== --- trunk/ippTools/share/warptool_tooverlap.sql (revision 20934) +++ trunk/ippTools/share/warptool_tooverlap.sql (revision 20973) @@ -7,6 +7,5 @@ rawExp.camera, exp_id, - exp_tag, - warpRun.magiced + exp_tag FROM warpRun JOIN fakeRun