Index: trunk/ippTools/share/Makefile.am =================================================================== --- trunk/ippTools/share/Makefile.am (revision 18662) +++ trunk/ippTools/share/Makefile.am (revision 18663) @@ -75,8 +75,9 @@ flatcorr_find_processedimfiles.sql \ flatcorr_pending.sql \ - magictool_create_tmp_warpcomplete.sql \ - magictool_find_complete_diffed_exposures.sql \ - magictool_find_complete_warpruns.sql \ - magictool_find_unmagiced.sql \ + magictool_definebyquery_temp_create.sql \ + magictool_definebyquery_temp_insert.sql \ + magictool_definebyquery_select_part1.sql \ + magictool_definebyquery_select_part2.sql \ + magictool_definebyquery_insert.sql \ magictool_inputskyfile.sql \ magictool_totree.sql \ Index: trunk/ippTools/share/magictool_definebyquery.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery.sql (revision 18663) @@ -0,0 +1,97 @@ +-- This is a sequence of SQL for searching for exposures on which +-- magic may be run, and populating the run. This file is provided as +-- a convenience, and is intended solely for playing around and +-- testing. Updates here will not affect the operation of magictool. +-- Conversely, it may not be up to date with the actual queries used +-- by magictool. + +-------------------------------------------------------------------- +-- NOTE: THIS FILE IS NOT USED BY magictool +-------------------------------------------------------------------- + + +CREATE TEMPORARY TABLE magicBestDiffs ( +exp_id BIGINT, +skycell_id VARCHAR(64), +tess_id VARCHAR(64), +diff_id BIGINT, +PRIMARY KEY(exp_id, skycell_id, tess_id) +) ENGINE=MEMORY; + + +-- List of best differences for each exposure +INSERT INTO magicBestDiffs +SELECT + exp_id, + warpSkyfile.skycell_id, + warpSkyfile.tess_id, + MAX(diffSkyfile.diff_id) AS diff_id +FROM rawExp +JOIN chipRun USING(exp_id, tess_id) +JOIN camRun USING(chip_id, tess_id) +JOIN fakeRun USING(cam_id, tess_id) +JOIN warpRun USING(fake_id, tess_id) +JOIN warpSkyCellMap USING(warp_id, tess_id) +JOIN warpSkyfile USING(warp_id, skycell_id, tess_id) +JOIN diffInputSkyfile + ON diffInputSkyfile.warp_id = warpSkyfile.warp_id + AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id + AND diffInputSkyfile.tess_id = warpSkyfile.tess_id + AND diffInputSkyfile.template = 0 -- selecting inputs only +JOIN diffSkyfile USING(diff_id) +WHERE + warpSkyfile.good_frac > 0.2 -- XXX Must update! + AND diffSkyfile.good_frac > 0.2 -- XXX Must update! + AND diffSkyfile.fault = 0 +GROUP BY + exp_id, + warpSkyfile.skycell_id +; + + +-- Get list of exposures ready for magic +SELECT + * +FROM ( + -- Number of skycells as a function of exposure + SELECT + exp_id, + filter, + COUNT(skycell_id) AS num_todo + FROM rawExp + JOIN chipRun USING(exp_id, tess_id) + JOIN camRun USING(chip_id, tess_id) + JOIN fakeRun USING(cam_id, tess_id) + JOIN warpRun USING(fake_id, tess_id) + JOIN warpSkyCellMap USING(warp_id, tess_id) + JOIN warpSkyfile USING(warp_id, skycell_id, tess_id) + WHERE + warpSkyfile.ignored = 0 + AND warpRun.state = 'stop' + AND warpSkyfile.good_frac > 0.2 -- XXX Must update! + 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) +WHERE + num_done = num_todo +; + + +-- Insert the best list of diffs as magic inputs +INSERT INTO magicInputSkyfile +SELECT + 12345, + diff_id, + CONCAT(tess_id, '.', skycell_id) AS node +FROM magicBestDiffs +WHERE + exp_id = 3; Index: trunk/ippTools/share/magictool_definebyquery_insert.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_insert.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery_insert.sql (revision 18663) @@ -0,0 +1,7 @@ +-- Insert the best list of diffs as magic inputs +INSERT INTO magicInputSkyfile +SELECT + @MAGIC_ID@, -- Update this with the appropriate magic_id + diff_id, + CONCAT(tess_id, '.', skycell_id) AS node +FROM magicBestDiffs Index: trunk/ippTools/share/magictool_definebyquery_select_part1.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_select_part1.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery_select_part1.sql (revision 18663) @@ -0,0 +1,22 @@ +-- This is part 1 of 2 of a query to get a list of exposures on which magic may be performed +-- After this follows magictool_definebyquery_select_part2.sql +SELECT + * +FROM ( + -- Number of skycells as a function of exposure + SELECT + exp_id, + filter, + COUNT(skycell_id) AS num_todo + FROM rawExp + JOIN chipRun USING(exp_id, tess_id) + JOIN camRun USING(chip_id, tess_id) + JOIN fakeRun USING(cam_id, tess_id) + JOIN warpRun USING(fake_id, tess_id) + JOIN warpSkyCellMap USING(warp_id, tess_id) + JOIN warpSkyfile USING(warp_id, skycell_id, tess_id) + WHERE + warpSkyfile.ignored = 0 + AND warpRun.state = 'stop' + -- INSERT HERE any additional restrictions (e.g., exp_id, warpSkyfile.good_frac) +-- INSERT HERE magictool_definebyquery_select_part2.sql Index: trunk/ippTools/share/magictool_definebyquery_select_part2.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_select_part2.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery_select_part2.sql (revision 18663) @@ -0,0 +1,18 @@ +-- This is part 2 of 2 of a query to get a list of exposures on which magic may be performed +-- This follows magictool_definebyquery_select_part1.sql + 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) +LEFT JOIN magicRun USING(exp_id) +WHERE + num_done = num_todo + AND magicRun.magic_id IS NULL Index: trunk/ippTools/share/magictool_definebyquery_select_test.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_select_test.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery_select_test.sql (revision 18663) @@ -0,0 +1,46 @@ +-- This is the combination of the two parts of the query to get a list +-- of exposures to be magic-ed + +-------------------------------------------------------------------------------- +-- THIS FILE IS INTENDED FOR TESTING ONLY! IT IS NOT USED BY stacktool. +-- CHANGES SHOULD BE MADE TO magictool_definebyquery_select_part1.sql +-- AND magictool_definebyquery_select_part2.sql +-------------------------------------------------------------------------------- + +-- magictool_definebyquery_select_part1.sql +SELECT + * +FROM ( + -- Number of skycells as a function of exposure + SELECT + exp_id, + filter, + COUNT(skycell_id) AS num_todo + FROM rawExp + JOIN chipRun USING(exp_id, tess_id) + JOIN camRun USING(chip_id, tess_id) + JOIN fakeRun USING(cam_id, tess_id) + JOIN warpRun USING(fake_id, tess_id) + JOIN warpSkyCellMap USING(warp_id, tess_id) + JOIN warpSkyfile USING(warp_id, skycell_id, tess_id) + WHERE + warpSkyfile.ignored = 0 + AND warpRun.state = 'stop' + -- INSERT HERE any additional restrictions (e.g., exp_id, warpSkyfile.good_frac) +-- magictool_definebyquery_select_part2.sql + 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) +LEFT JOIN magicRun USING(exp_id) +WHERE + num_done = num_todo + AND magicRun.magic_id IS NULL Index: trunk/ippTools/share/magictool_definebyquery_temp_create.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_temp_create.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery_temp_create.sql (revision 18663) @@ -0,0 +1,7 @@ +CREATE TEMPORARY TABLE magicBestDiffs ( +exp_id BIGINT, +skycell_id VARCHAR(64), +tess_id VARCHAR(64), +diff_id BIGINT, +PRIMARY KEY(exp_id, skycell_id, tess_id) +) ENGINE=MEMORY; Index: trunk/ippTools/share/magictool_definebyquery_temp_insert.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_temp_insert.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery_temp_insert.sql (revision 18663) @@ -0,0 +1,22 @@ +-- List of best differences for each exposure +INSERT INTO magicBestDiffs +SELECT + exp_id, + warpSkyfile.skycell_id, + warpSkyfile.tess_id, + MAX(diffSkyfile.diff_id) AS diff_id +FROM rawExp +JOIN chipRun USING(exp_id, tess_id) +JOIN camRun USING(chip_id, tess_id) +JOIN fakeRun USING(cam_id, tess_id) +JOIN warpRun USING(fake_id, tess_id) +JOIN warpSkyCellMap USING(warp_id, tess_id) +JOIN warpSkyfile USING(warp_id, skycell_id, tess_id) +JOIN diffInputSkyfile + ON diffInputSkyfile.warp_id = warpSkyfile.warp_id + AND diffInputSkyfile.skycell_id = warpSkyfile.skycell_id + AND diffInputSkyfile.tess_id = warpSkyfile.tess_id + AND diffInputSkyfile.template = 0 -- selecting inputs only +JOIN diffSkyfile USING(diff_id) +WHERE + diffSkyfile.fault = 0 Index: trunk/ippTools/share/magictool_definebyquery_temp_insert_groupby.sql =================================================================== --- trunk/ippTools/share/magictool_definebyquery_temp_insert_groupby.sql (revision 18663) +++ trunk/ippTools/share/magictool_definebyquery_temp_insert_groupby.sql (revision 18663) @@ -0,0 +1,3 @@ +GROUP BY + exp_id, + warpSkyfile.skycell_id Index: trunk/ippTools/share/magictool_find_complete_diffed_exposures.sql =================================================================== --- trunk/ippTools/share/magictool_find_complete_diffed_exposures.sql (revision 18662) +++ (revision ) @@ -1,29 +1,0 @@ -SELECT - warp_id, - diff_id, - skycell_id, - tess_id -FROM diffSkyfile -JOIN - (SELECT - warp_id, - diff_id, - skycell_id, - tess_id, - diffSkyfile.fault - FROM warpComplete - LEFT JOIN diffInputSkyfile - USING(warp_id, skycell_id, tess_id) - LEFT JOIN diffSkyfile - USING(diff_id) - WHERE - (diffInputSkyfile.template = 1 || diffInputSkyfile.template IS NULL) - GROUP BY - warpComplete.warp_id - HAVING - SUM(diffSkyfile.fault) = 0 - AND COUNT(warpComplete.warp_id) = COUNT(diffInputSkyfile.warp_id) - AND COUNT(warpComplete.skycell_id) = COUNT(diffInputSkyfile.skycell_id) - AND COUNT(warpComplete.tess_id) = COUNT(diffInputSkyfile.tess_id) - ) as Foo - USING(diff_id) Index: trunk/ippTools/share/magictool_find_complete_warpruns.sql =================================================================== --- trunk/ippTools/share/magictool_find_complete_warpruns.sql (revision 18662) +++ (revision ) @@ -1,37 +1,0 @@ --- find stopped warp runsthat have had all of their skycell's warped without --- any faults -INSERT INTO warpComplete -SELECT - warp_id, - skycell_id, - tess_id -FROM warpSkyCellMap -JOIN (SELECT warp_id, ignored - FROM (SELECT - warpSkyCellMap.warp_id, - warpSkyCellMap.skycell_id, - warpSkyCellMap.tess_id, - warpSkyfile.warp_id as w2, - warpSkyfile.skycell_id as sc2, - warpSkyfile.tess_id as t2, - warpSkyfile.ignored, - warpSkyfile.fault - FROM warpRun - JOIN warpSkyCellMap - USING(warp_id, tess_id) - LEFT JOIN warpSkyfile - USING(warp_id, skycell_id, tess_id) - WHERE - warpRun.state = 'stop' - GROUP BY - warpRun.warp_id - HAVING - SUM(warpSkyfile.fault) = 0 - AND COUNT(warpSkyCellMap.warp_id) = COUNT(warpSkyfile.warp_id) - AND COUNT(warpSkyCellMap.skycell_id) = COUNT(warpSkyfile.skycell_id) - AND COUNT(warpSkyCellMap.tess_id) = COUNT(warpSkyfile.tess_id) - ) as Foo - WHERE - ignored = 0 - ) as Bar - USING (warp_id) Index: trunk/ippTools/share/magictool_find_unmagiced.sql =================================================================== --- trunk/ippTools/share/magictool_find_unmagiced.sql (revision 18662) +++ (revision ) @@ -1,47 +1,0 @@ --- find warp_id that have had all of their skycell's differenced but have not --- yet been magic'd -SELECT - warp_id -FROM - (SELECT - warpRun.warp_id, - warpSkyfile.fault as f1, - diffSkyfile.fault as f2, - warpSkyCellMap.skycell_id as wscm_s, - warpSkyfile.skycell_id as wsf_s, - diffInputSkyfile.skycell_id as s2 - FROM warpRun - -- find all of the skycells that came out of a single exposure - JOIN warpSkyCellMap - USING(warp_id, tess_id) - LEFT JOIN warpSkyfile - USING(warp_id, skycell_id, tess_id) - -- find out which skycells have been diff'd - LEFT JOIN diffInputSkyfile - USING(warp_id, skycell_id, tess_id) - LEFT JOIN diffRun - USING(diff_id) - LEFT JOIN diffSkyfile - USING(diff_id) - -- find out which diff'd skycells have been added to a magic run - LEFT JOIN magicInputSkyfile - USING(diff_id) - WHERE - warpRun.state = 'stop' - AND (diffRun.state = 'stop' OR diffRun.state IS NULL) - -- AND (warpSkyfile.fault = 0 || warpSkyfile.fault IS NULL) - AND (diffInputSkyfile.template = 1 || diffInputSkyfile.template IS NULL) - AND (diffInputSkyfile.kind = 'warped' || diffInputSkyfile.kind IS NULL) - -- AND (diffSkyfile.fault = 0 || diffSkyfile.fault IS NULL) - AND magicInputSkyfile.diff_id IS NULL - AND warpSkyfile.warp_id IS NOT NULL - AND diffInputSkyfile.warp_id IS NOT NULL - GROUP BY - warpRun.warp_id - -- warpRun.warp_id, warpSkyCellMap.skycell_id, warpSkyCellMap.tess_id - HAVING - COUNT(warpSkyCellMap.skycell_id) = COUNT(warpSkyfile.skycell_id) - AND COUNT(warpSkyCellMap.skycell_id) = COUNT(diffInputSkyfile.skycell_id) - AND SUM(warpSkyfile.fault) = 0 - AND SUM(diffSkyfile.fault) = 0 -) as Foo