Changeset 42596 for branches/eam_branches/ipp-20230313/ippTools/share

Timestamp:

Jan 19, 2024, 8:12:15 AM (2 years ago)

Author:

eugene

Message:

working on xcfftool

Location:

branches/eam_branches/ipp-20230313/ippTools/share

Files:

• Makefile.am (modified) (1 diff)
• xcfftool_definebyquery.sql (deleted)
• xcfftool_definebyquery_pt1.sql (added)
• xcfftool_definebyquery_pt2.sql (added)
• xcfftool_definebyquery_select_warps.sql (modified) (1 diff)
• xcfftool_result.sql (modified) (1 diff)
• xcfftool_revert.sql (modified) (1 diff)
• xcfftool_revertsummary.sql (modified) (1 diff)
• xcfftool_summary.sql (modified) (1 diff)
• xcfftool_toadvance.sql (modified) (1 diff)
• xcfftool_todo.sql (modified) (1 diff)
• xcfftool_updaterun.sql (modified) (1 diff)
• xcskytool_definebyquery_select.sql (modified) (1 diff)
• xcskytool_todo.sql (modified) (2 diffs)

branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am

@@ -581 +581 @@
         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

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_select_warps.sql

@@ -1 +1 @@
 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

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_result.sql

@@ -1 +1 @@
 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)

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revert.sql

@@ -1 @@
-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

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revertsummary.sql

@@ -1 @@
-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

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summary.sql

@@ -1 +1 @@
 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)

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_toadvance.sql

@@ -1 +1 @@
 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

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_todo.sql

@@ -1 +1 @@
 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'

branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_updaterun.sql

@@ -1 +1 @@
 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);

branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select.sql

@@ -22 +22 @@
 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

branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_todo.sql

@@ +1 @@
+ -- we implicitly assume that all xcstackRuns used for a xcskyRun have the same tess_id and skycell_id 
 SELECT
     xcskyRun.xcsky_id,
@@ -5 +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,