Index: /trunk/ippTools/share/Makefile.am =================================================================== --- /trunk/ippTools/share/Makefile.am (revision 18578) +++ /trunk/ippTools/share/Makefile.am (revision 18579) @@ -108,6 +108,6 @@ regtool_revertprocessedexp.sql \ regtool_revertprocessedimfile.sql \ - stacktool_definebyquery_select.sql \ - stacktool_definebyquery_groupby.sql \ + stacktool_definebyquery_part1.sql \ + stacktool_definebyquery_part2.sql \ stacktool_definebyquery_insert.sql \ stacktool_definebyquery_insert_random.sql \ Index: unk/ippTools/share/stacktool_definebyquery_groupby.sql =================================================================== --- /trunk/ippTools/share/stacktool_definebyquery_groupby.sql (revision 18578) +++ (revision ) @@ -1,6 +1,0 @@ --- This follows the SQL in stacktool_definebyquery_select.sql, --- after additional WHERE conditions have been added -GROUP BY - warpSkyfile.skycell_id, - warpSkyfile.tess_id, - rawExp.filter Index: /trunk/ippTools/share/stacktool_definebyquery_part1.sql =================================================================== --- /trunk/ippTools/share/stacktool_definebyquery_part1.sql (revision 18579) +++ /trunk/ippTools/share/stacktool_definebyquery_part1.sql (revision 18579) @@ -0,0 +1,26 @@ +-- This is the first part of the query to get a list of skycells with +-- warps to be stacked, along with the number of warps already in a +-- stack. It needs to be completed by part 2 (see below). + +SELECT + * +FROM (( + -- Number of stack-ready warps as a function of skycell and filter + SELECT + skycell_id, + tess_id, + filter, + COUNT(warpSkyfile.skycell_id) AS num_warp -- number of warps that can be stacked + FROM warpRun + JOIN warpSkyfile USING(warp_id, tess_id) + JOIN fakeRun USING(fake_id, tess_id) + JOIN camRun USING(cam_id, tess_id) + JOIN chipRun USING(chip_id, tess_id) + JOIN rawExp USING(exp_id, tess_id) + WHERE + warpRun.state = 'stop' + AND warpSkyfile.ignored = 0 + AND warpSkyfile.fault = 0 + -- Here should follow the SQL in stacktool_definebyquery_part2.sql, + -- after any additional WHERE conditions have been added + Index: /trunk/ippTools/share/stacktool_definebyquery_part2.sql =================================================================== --- /trunk/ippTools/share/stacktool_definebyquery_part2.sql (revision 18579) +++ /trunk/ippTools/share/stacktool_definebyquery_part2.sql (revision 18579) @@ -0,0 +1,34 @@ +-- This is the second part of the query to get a list of skycells with +-- warps to be stacked, along with the number of warps already in a +-- stack. It should follow part 1, which is in +-- stacktool_definebyquery_part1.sql + + GROUP BY + skycell_id, + filter + ) AS warpsToStack + LEFT JOIN ( + -- Number of stack inputs as a function of skycell and filter + SELECT + skycell_id, + tess_id, + filter, + stack_id, + COUNT(stackInputSkyfile.warp_id) as num_stack -- number of warps in a stack + FROM stackRun + JOIN stackInputSkyfile USING(stack_id) + GROUP BY + stack_id + -- This ORDER BY is important: it sets the final num_stack that + -- comes out of the top-level GROUP BY. We want it to be the + -- biggest number for a skycell/filter combination. + ORDER BY + num_stack DESC + ) AS stackSizes + -- JOINing the warpsToStack and stackSizes tables + USING(skycell_id, tess_id, filter) + ) + GROUP BY + skycell_id, + tess_id, + filter Index: unk/ippTools/share/stacktool_definebyquery_select.sql =================================================================== --- /trunk/ippTools/share/stacktool_definebyquery_select.sql (revision 18578) +++ (revision ) @@ -1,35 +1,0 @@ --- This is the SELECT part of the query to get a list of skycells with --- warps that are ready to be stacked, along with the numbers of warps --- ready to be stacked and already in stacks. It needs to be --- completed by a GROUP BY statement (see below). - -SELECT - warpSkyfile.skycell_id, - warpSkyfile.tess_id, - stackRun.stack_id, - rawExp.filter, - COUNT(warpSkyfile.skycell_id) AS num_avail, -- number available to be stacked - COUNT(stackRun.stack_id) AS num_extant -- number already in the stack -FROM warpRun -JOIN warpSkyfile - USING(warp_id, tess_id) -JOIN fakeRun - USING(fake_id, tess_id) -JOIN camRun - USING(cam_id, tess_id) -JOIN chipRun - USING(chip_id, tess_id) -JOIN rawExp - USING(exp_id, tess_id) -LEFT JOIN stackInputSkyfile - ON warpSkyfile.warp_id = stackInputSkyfile.warp_id -LEFT JOIN stackRun - ON stackRun.tess_id = warpSkyfile.tess_id - AND stackRun.skycell_id = warpSkyfile.skycell_id - AND stackRun.stack_id = stackInputSkyfile.stack_id -WHERE - warpRun.state = 'stop' - AND warpSkyfile.ignored = 0 - AND warpSkyfile.fault = 0 --- Here should follow the SQL in stacktool_definebyquery_groupby.sql, --- after any additional WHERE conditions have been added Index: /trunk/ippTools/share/stacktool_definebyquery_test.sql =================================================================== --- /trunk/ippTools/share/stacktool_definebyquery_test.sql (revision 18579) +++ /trunk/ippTools/share/stacktool_definebyquery_test.sql (revision 18579) @@ -0,0 +1,63 @@ +-- This is the combination of the two parts of the query to get a list +-- of skycells with warps to be stacked, along with the number of +-- warps already in a stack. + +-------------------------------------------------------------------------------- +-- THIS FILE IS INTENDED FOR TESTING ONLY! IT IS NOT USED BY stacktool. +-- CHANGES SHOULD BE MADE TO stacktool_definebyquery_part1.sql +-- AND stacktool_definebyquery_part2.sql +-------------------------------------------------------------------------------- + +-- stacktool_definebyquery_part1.sql +SELECT + * +FROM (( + -- Number of stack-ready warps as a function of skycell and filter + SELECT + skycell_id, + tess_id, + filter, + COUNT(warpSkyfile.skycell_id) AS num_warp -- number of warps that can be stacked + FROM warpRun + JOIN warpSkyfile USING(warp_id, tess_id) + JOIN fakeRun USING(fake_id, tess_id) + JOIN camRun USING(cam_id, tess_id) + JOIN chipRun USING(chip_id, tess_id) + JOIN rawExp USING(exp_id, tess_id) + WHERE + warpRun.state = 'stop' + AND warpSkyfile.ignored = 0 + AND warpSkyfile.fault = 0 + -- Any additional selection on warps/exposures goes here + +-- stacktool_definebyquery_part2.sql + GROUP BY + skycell_id, + filter + ) AS warpsToStack + LEFT JOIN ( + -- Number of stack inputs as a function of skycell and filter + SELECT + skycell_id, + tess_id, + filter, + stack_id, + COUNT(stackInputSkyfile.warp_id) as num_stack -- number of warps in a stack + FROM stackRun + JOIN stackInputSkyfile USING(stack_id) + GROUP BY + stack_id + -- This ORDER BY is important: it sets the final num_stack that + -- comes out of the top-level GROUP BY. We want it to be the + -- biggest number for a skycell/filter combination. + ORDER BY + num_stack DESC + ) AS stackSizes + -- JOINing the warpsToStack and stackSizes tables + USING(skycell_id, tess_id, filter) + ) + GROUP BY + skycell_id, + tess_id, + filter +; Index: /trunk/ippTools/src/stacktool.c =================================================================== --- /trunk/ippTools/src/stacktool.c (revision 18578) +++ /trunk/ippTools/src/stacktool.c (revision 18579) @@ -125,6 +125,6 @@ PXOPT_LOOKUP_S32(randomLimit, config->args, "-random", false, false); - PXOPT_COPY_S32(config->args, having, "-min_num", "num_avail", ">="); - PXOPT_COPY_S32(config->args, having, "-min_new", "(num_avail - num_extant)", ">="); + PXOPT_COPY_S32(config->args, having, "-min_num", "num_warp", ">="); + PXOPT_COPY_S32(config->args, having, "-min_new", "(num_warp - num_stack)", ">="); PXOPT_LOOKUP_F32(min_frac, config->args, "-min_frac", false, false); @@ -144,5 +144,5 @@ PXOPT_COPY_F32(config->args, whereWSF, "-select_good_frac_min", "good_frac", ">="); - psString select = pxDataGet("stacktool_definebyquery_select.sql"); + psString select = pxDataGet("stacktool_definebyquery_part1.sql"); if (!select) { psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement"); @@ -164,5 +164,5 @@ psFree(whereWSF); - psString groupby = pxDataGet("stacktool_definebyquery_groupby.sql"); + psString groupby = pxDataGet("stacktool_definebyquery_part2.sql"); if (!groupby) { psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement"); @@ -185,5 +185,5 @@ psStringAppend(&select, " HAVING"); } - psStringAppend(&select, " num_avail >= %f * num_extant", (double)min_frac); + psStringAppend(&select, " num_warp >= %f * num_stack", (double)min_frac); }