Index: branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am (revision 42596) @@ -581,3 +581,13 @@ xcskytool_result.sql \ xcskytool_revert.sql \ - xcskytool_revert_update.sql + xcskytool_revert_update.sql \ + xcfftool_definebyquery_pt1.sql \ + xcfftool_definebyquery_pt2.sql \ + xcfftool_definebyquery_select_warps.sql \ + xcfftool_result.sql \ + xcfftool_revert.sql \ + xcfftool_revertsummary.sql \ + xcfftool_summaryinputs.sql \ + xcfftool_summary.sql \ + xcfftool_toadvance.sql \ + xcfftool_todo.sql Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery.sql (revision 42594) +++ (revision ) @@ -1,30 +1,0 @@ -SELECT - xccalRun.xccal_id, - xccalResult.path_base, - xccalRun.data_group, - stackRun.tess_id, - stackRun.skycell_id, - stackRun.filter -FROM %s.xccalRun - JOIN %s.xccalResult USING (xccal_id) - JOIN %s.xcstackInputSkyfile USING (xcstack_id) -- exposes the stack_id - JOIN %s.xcstackCamera USING (camera_id) -- select the camera matching this db - JOIN stackRun USING (stack_id) - JOIN skycell USING (tess_id, skycell_id) - -- join hook %s - -- for no-rerun: - -- LEFT JOIN xcForceRun ON xcForceRun.xccal_id = %s.xccalRun.xccal_id -WHERE - -- the first %s below should be filled with the xcamera database - -- the second should be the name of the camera corresponding to the - -- database in which the tool is run - %s.xcstackCamera.camera = '%s' AND - %s.xccalRun.state = 'full' AND - %s.xccalResult.quality = 0 --- 'where' string is appended here - -- for no-rerun, also append: - -- AND xcff_id IS NULL - --- this query is run in the database in which the original stacks were made (e.g., gpc1) --- the external database (%s in front of the xcstackRun & xccalRun tables) refers to the database --- used to generate the xcstacks (e.g., xcamera) Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_pt1.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_pt1.sql (revision 42596) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_pt1.sql (revision 42596) @@ -0,0 +1,19 @@ +SELECT + xccalRun.xccal_id, + xccalResult.path_base, + xccalRun.data_group, + stackRun.tess_id, + stackRun.stack_id, + stackRun.skycell_id, + stackRun.filter +FROM %s.xccalRun + JOIN %s.xccalResult USING (xccal_id) -- needed for input sources path_base + JOIN %s.xcstackInputSkyfile USING (xcstack_id) -- exposes the stack_id + JOIN %s.xcstackCamera USING (camera_id) -- select the camera matching this db + JOIN stackRun USING (stack_id) -- needed for filter + JOIN skycell USING (tess_id, skycell_id) -- needed to limit by coords + +-- this query is joined to part 2 which supplies WHERE conditions, but there may be an optional +-- LEFT JOIN between the two parts for the -no-rerun case + +-- the 4 string formats are replaced with the xcamera database name Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_pt2.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_pt2.sql (revision 42596) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_pt2.sql (revision 42596) @@ -0,0 +1,10 @@ +WHERE + -- the first string format below should be filled with the xcamera database + -- the second should be the name of the camera corresponding to the + -- database in which the tool is run + %s.xcstackCamera.camera_id = %lld +AND %s.xccalRun.state = 'full' +AND %s.xccalResult.quality = 0 +-- 'where' string is appended here +-- for no-rerun, this also append: +-- AND xcff_id IS NULL Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_select_warps.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_select_warps.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_select_warps.sql (revision 42596) @@ -1,9 +1,8 @@ SELECT warp_id -FROM warpRun join warpSkyfile using(warp_id, tess_id) - JOIN fakeRun using(fake_id) - JOIN camRun USING(cam_id) - JOIN chipRun USING(chip_id) - JOIN rawExp USING(exp_id) - JOIN skycell ON skycell.tess_id = warpSkyfile.tess_id AND skycell.skycell_id = warpSkyfile.skycell_id -WHERE warpRun.tess_id = '%s' AND warpSkyfile.skycell_id = '%s' AND rawExp.filter = '%s' - AND warpRun.state = 'full' AND warpSkyfile.quality = 0 and warpSkyfile.fault = 0 +FROM stackInputSkyfile +JOIN warpRun using (warp_id) +JOIN warpSkyfile using(warp_id, tess_id) +WHERE (stack_id = %lld) +AND warpRun.state = 'full' +AND warpSkyfile.quality = 0 +AND warpSkyfile.fault = 0 Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_result.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_result.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_result.sql (revision 42596) @@ -1,22 +1,19 @@ SELECT - fullForceResult.*, - fullForceRun.state, - fullForceRun.label, - fullForceRun.data_group, - fullForceRun.xccal_id, - fullForceRun.workdir, - fullForceRun.sources_path_base, + xcForceResult.*, + xcForceRun.state, + xcForceRun.label, + xcForceRun.data_group, + xcForceRun.xccal_id, + xcForceRun.workdir, + xcForceRun.sources_path_base, CONCAT_WS('.', warp_id, skycell_id) AS dist_component, - stackRun.tess_id, - stackRun.skycell_id, - stackRun.filter, + xcForceRun.tess_id, + xcForceRun.skycell_id, warpImfile.warp_skyfile_id, rawExp.exp_id, rawExp.exp_name, rawExp.camera -FROM fullForceRun - JOIN fullForceResult USING(ff_id) - JOIN xccalRun using(xccal_id) - JOIN stackRun using(stack_id) +FROM xcForceRun + JOIN xcForceResult USING(xcff_id) JOIN warpRun using(warp_id, tess_id) JOIN warpSkyfile USING(warp_id, tess_id, skycell_id) Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revert.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revert.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revert.sql (revision 42596) @@ -1,7 +1,4 @@ -DELETE FROM fullForceResult -USING fullForceResult, fullForceRun, xccalRun, stackRun, skycell -WHERE fullForceRun.ff_id = fullForceResult.ff_id - AND fullForceResult.fault != 0 - AND fullForceRun.xccal_id = xccalRun.xccal_id - AND xccalRun.stack_id = stackRun.stack_id - AND stackRun.tess_id = skycell.tess_id AND stackRun.skycell_id = skycell.skycell_id +DELETE FROM xcForceResult +USING xcForceResult, xcForceRun +WHERE xcForceRun.xcff_id = xcForceResult.xcff_id + AND xcForceResult.fault != 0 Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revertsummary.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revertsummary.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revertsummary.sql (revision 42596) @@ -1,7 +1,5 @@ -DELETE FROM fullForceSummary -USING fullForceSummary, fullForceRun, xccalRun, stackRun, skycell -WHERE fullForceRun.ff_id = fullForceSummary.ff_id - AND fullForceSummary.fault != 0 - AND fullForceRun.xccal_id = xccalRun.xccal_id - AND xccalRun.stack_id = stackRun.stack_id - AND stackRun.tess_id = skycell.tess_id AND stackRun.skycell_id = skycell.skycell_id +DELETE FROM xcForceSummary +USING xcForceSummary, xcForceRun, skycell +WHERE xcForceRun.xcff_id = xcForceSummary.xcff_id + AND xcForceSummary.fault != 0 + AND xcForceRun.tess_id = skycell.tess_id AND xcForceRun.skycell_id = skycell.skycell_id Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summary.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summary.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summary.sql (revision 42596) @@ -1,25 +1,15 @@ SELECT DISTINCT - fullForceSummary.*, - fullForceRun.state, - fullForceRun.label, - fullForceRun.data_group, - fullForceRun.xccal_id, - fullForceRun.workdir, - fullForceRun.sources_path_base, + xcForceSummary.*, + xcForceRun.state, + xcForceRun.label, + xcForceRun.data_group, + xcForceRun.xccal_id, + xcForceRun.workdir, + xcForceRun.sources_path_base, + xcForceRun.tess_id, + xcForceRun.skycell_id, 'summary' AS dist_component, - stackRun.stack_id, - stackRun.tess_id, - stackRun.skycell_id, - stackRun.filter, - rawExp.camera -FROM fullForceRun - JOIN fullForceSummary USING(ff_id) - JOIN fullForceInput USING(ff_id) - JOIN xccalRun using(xccal_id) - JOIN stackRun using(stack_id) - JOIN warpRun using(warp_id, tess_id) - JOIN warpSkyfile USING(warp_id, tess_id, skycell_id) - JOIN fakeRun using(fake_id) - JOIN camRun using(cam_id) - JOIN chipRun using(chip_id) - JOIN rawExp using(exp_id) +FROM xcForceRun + JOIN xcForceSummary USING(xcff_id) + JOIN xcForceInput USING(xcff_id) + JOIN skycell USING (tess_id, skycell_id) Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_toadvance.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_toadvance.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_toadvance.sql (revision 42596) @@ -1,22 +1,15 @@ SELECT - fullForceRun.*, - stackRun.tess_id, - stackRun.skycell_id, - stackRun.filter, - rawExp.camera -FROM fullForceRun - JOIN fullForceInput USING(ff_id) - JOIN xccalRun USING(xccal_id) - JOIN stackRun USING(stack_id) - JOIN warpRun USING(warp_id, tess_id) - JOIN fakeRun USING(fake_id) - JOIN camRun USING(cam_id) - JOIN chipRun USING(chip_id) - JOIN rawExp USING(exp_id) - LEFT JOIN fullForceResult USING(ff_id, warp_id) - LEFT JOIN fullForceSummary USING(ff_id) -WHERE fullForceRun.state = 'new' AND fullForceSummary.ff_id IS NULL + xcForceRun.xcff_id, + xcForceRun.tess_id, + xcForceRun.skycell_id, + xcForceRun.workdir, + xcForceRun.reduction +FROM xcForceRun + JOIN xcForceInput USING(xcff_id) + LEFT JOIN xcForceResult USING(xcff_id, warp_id) + LEFT JOIN xcForceSummary USING(xcff_id) +WHERE xcForceRun.state = 'new' AND xcForceSummary.ff_id IS NULL -- WHERE hook %s - GROUP BY ff_id - HAVING COUNT(fullForceInput.warp_id) = COUNT(fullForceResult.warp_id) - AND SUM(fullForceResult.fault) = 0 + GROUP BY xcff_id + HAVING COUNT(xcForceInput.warp_id) = COUNT(xcForceResult.warp_id) + AND SUM(xcForceResult.fault) = 0 Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_todo.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_todo.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_todo.sql (revision 42596) @@ -1,22 +1,12 @@ SELECT - fullForceRun.*, - fullForceInput.warp_id, - stackRun.tess_id, - stackRun.skycell_id, - stackRun.filter, - rawExp.camera, + xcForceRun.*, + xcForceInput.warp_id, warpSkyfile.path_base AS warp_path_base -FROM fullForceRun - JOIN fullForceInput USING(ff_id) - JOIN xccalRun USING(xccal_id) - JOIN stackRun USING(stack_id) +FROM xcForceRun + JOIN xcForceInput USING(xcff_id) JOIN warpRun USING(warp_id, tess_id) JOIN warpSkyfile USING(warp_id, tess_id, skycell_id) - JOIN fakeRun USING(fake_id) - JOIN camRun USING(cam_id) - JOIN chipRun USING(chip_id) - JOIN rawExp USING(exp_id) - LEFT JOIN fullForceResult USING(ff_id, warp_id) -WHERE fullForceRun.state = 'new' - AND fullForceResult.ff_id IS NULL + LEFT JOIN xcForceResult USING(xcff_id, warp_id) +WHERE xcForceRun.state = 'new' + AND xcForceResult.xcff_id IS NULL AND warpSkyfile.data_state = 'full' Index: branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_updaterun.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_updaterun.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_updaterun.sql (revision 42596) @@ -1,7 +1,2 @@ UPDATE xcForceRun -JOIN %s.xccalRun USING(xccal_id) -JOIN %s.xccalResult USING (xccal_id) -JOIN %s.xcstackInputSkyfile USING (xcstack_id) -- exposes the stack_id -JOIN %s.xcstackCamera USING (camera_id) -- select the camera matching this db -JOIN stackRun USING (stack_id) -JOIN skycell USING(tess_id, skycell_id)"); +JOIN skycell USING(tess_id, skycell_id); Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select.sql (revision 42596) @@ -22,7 +22,7 @@ LEFT JOIN ( SELECT DISTINCT xcsky_id, - xcstackRun.skycell_id, - xcstackRun.tess_id, - xcstackRun.data_group, + MIN(xcstackRun.skycell_id) as skycell_id, + MIN(xcstackRun.tess_id) as tess_id, + MIN(xcstackRun.data_group) as data_group, COUNT(xcstack_id) AS num_filter FROM xcskyRun Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_todo.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_todo.sql (revision 42594) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_todo.sql (revision 42596) @@ -1,2 +1,3 @@ + -- we implicitly assume that all xcstackRuns used for a xcskyRun have the same tess_id and skycell_id SELECT xcskyRun.xcsky_id, @@ -5,6 +6,6 @@ xcskyRun.label, xcskyRun.state, - MIN(xcstackRun.tess_id) as tess_id, -- we implicitly assume that all xcstackRuns used for a xcskyRun have the same tess_id - MIN(xcstackRun.skycell_id) as skycell_id, -- and skycell_id + MIN(xcstackRun.tess_id) as tess_id, + MIN(xcstackRun.skycell_id) as skycell_id, TRUNCATE(MIN(skycell.radeg)/15., 4) as rahours, MIN(skycell.radeg) as radeg,