Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery.sql	(revision 42587)
@@ -0,0 +1,22 @@
+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
+WHERE 
+    %s.xcstackCamera.camera = 'test_cam1' AND
+    %s.xccalRun.state = 'full' AND
+    %s.xccalResult.quality = 0
+
+
+-- this query is run in the database in which the original stacks were made (e.g., gpc1)
+-- it refers to the database used to generate the xcstacks (e.g., xcamera)
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 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_definebyquery_select_warps.sql	(revision 42587)
@@ -0,0 +1,9 @@
+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
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_defineforstacks.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_defineforstacks.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_defineforstacks.sql	(revision 42587)
@@ -0,0 +1,14 @@
+SELECT
+    xccalRun.xccal_id,
+    xccalResult.path_base,
+    xccalRun.data_group,
+    stackRun.tess_id,
+    stackRun.skycell_id,
+    stackRun.filter
+FROM xccalRun
+    JOIN xccalResult USING(xccal_id)
+    JOIN stackRun USING(stack_id)
+    JOIN skycell USING(tess_id, skycell_id)
+    -- join hook %s
+WHERE 
+    xccalRun.state = 'full' AND xccalResult.quality = 0
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_defineforstacks_select_warps.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_defineforstacks_select_warps.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_defineforstacks_select_warps.sql	(revision 42587)
@@ -0,0 +1,5 @@
+SELECT warp_id
+FROM stackInputSkyfile
+     JOIN stackRun USING (stack_id)
+     JOIN xccalRun USING (stack_id)
+WHERE xccal_id = '%d'
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_input.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_input.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_input.sql	(revision 42587)
@@ -0,0 +1,3 @@
+SELECT
+    fullForceInput.*
+FROM fullForceInput
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_result.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_result.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_result.sql	(revision 42587)
@@ -0,0 +1,3 @@
+SELECT
+    fullForceResult.*
+FROM fullForceResult
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_run.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_run.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_run.sql	(revision 42587)
@@ -0,0 +1,3 @@
+SELECT
+    fullForceRun.*
+FROM fullForceRun
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_summary.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_summary.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_export_summary.sql	(revision 42587)
@@ -0,0 +1,3 @@
+SELECT
+    fullForceSummary.*
+FROM fullForceSummary
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_result.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_result.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_result.sql	(revision 42587)
@@ -0,0 +1,27 @@
+SELECT
+    fullForceResult.*,
+    fullForceRun.state,
+    fullForceRun.label,
+    fullForceRun.data_group,
+    fullForceRun.xccal_id,
+    fullForceRun.workdir,
+    fullForceRun.sources_path_base,
+    CONCAT_WS('.', warp_id, skycell_id) AS dist_component,
+    stackRun.tess_id,
+    stackRun.skycell_id,
+    stackRun.filter,
+    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)
+    JOIN warpRun using(warp_id, tess_id)
+    JOIN warpSkyfile USING(warp_id, tess_id, skycell_id)
+    JOIN warpImfile USING(warp_id, skycell_id)
+    JOIN fakeRun using(fake_id)
+    JOIN camRun using(cam_id)
+    JOIN chipRun using(chip_id)
+    JOIN rawExp using(exp_id)
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revert.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revert.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revert.sql	(revision 42587)
@@ -0,0 +1,7 @@
+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
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revertsummary.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revertsummary.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_revertsummary.sql	(revision 42587)
@@ -0,0 +1,7 @@
+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
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summary.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summary.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summary.sql	(revision 42587)
@@ -0,0 +1,25 @@
+SELECT DISTINCT
+    fullForceSummary.*,
+    fullForceRun.state,
+    fullForceRun.label,
+    fullForceRun.data_group,
+    fullForceRun.xccal_id,
+    fullForceRun.workdir,
+    fullForceRun.sources_path_base,
+    '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)
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summaryinputs.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summaryinputs.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_summaryinputs.sql	(revision 42587)
@@ -0,0 +1,4 @@
+SELECT
+    fullForceResult.*
+FROM fullForceRun 
+    JOIN fullForceResult USING(ff_id)
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_toadvance.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_toadvance.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_toadvance.sql	(revision 42587)
@@ -0,0 +1,22 @@
+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
+    -- WHERE hook %s
+    GROUP BY ff_id
+    HAVING COUNT(fullForceInput.warp_id) = COUNT(fullForceResult.warp_id)
+        AND SUM(fullForceResult.fault) = 0
Index: /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_todo.sql
===================================================================
--- /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_todo.sql	(revision 42587)
+++ /branches/eam_branches/ipp-20230313/ippTools/share/xcfftool_todo.sql	(revision 42587)
@@ -0,0 +1,22 @@
+SELECT
+    fullForceRun.*,
+    fullForceInput.warp_id,
+    stackRun.tess_id,
+    stackRun.skycell_id,
+    stackRun.filter,
+    rawExp.camera,
+    warpSkyfile.path_base AS warp_path_base
+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 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
+    AND warpSkyfile.data_state = 'full'