Index: branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am (revision 42524) +++ branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am (revision 42541) @@ -554,4 +554,15 @@ fpcamtool_queue_cam_id.sql \ fpcamtool_revertprocessedexp.sql \ - fpcamtool_revertupdatedexp.sql + fpcamtool_revertupdatedexp.sql \ + xcstacktool_definebyquery_select.sql \ + xcstacktool_addbyquery_select.sql \ + xcstacktool_addcamera.sql \ + xcstacktool_inputskyfile.sql \ + xcstacktool_tosum.sql \ + xcstacktool_sumskyfile.sql \ + xcstacktool_revertsumskyfile_delete.sql \ + xcstacktool_pendingcleanuprun.sql \ + xcstacktool_pendingcleanupskyfile.sql \ + xcstacktool_donecleanup.sql + Index: branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql (revision 42524) +++ branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql (revision 42541) @@ -2481,4 +2481,62 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE xcstackRun ( + xcstack_id BIGINT AUTO_INCREMENT, + state VARCHAR(64), + workdir VARCHAR(255), + label VARCHAR(64), + data_group VARCHAR(64), + dist_group VARCHAR(64), + reduction VARCHAR(64), + registered DATETIME, + skycell_id VARCHAR(64), + tess_id VARCHAR(64), + filter VARCHAR(64), + software_ver VARCHAR(16), + note VARCHAR(255), + PRIMARY KEY(xcstack_id), + KEY(xcstack_id), + KEY(state), + KEY(skycell_id), + KEY(tess_id), + KEY(label), + KEY(data_group), + KEY(dist_group) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcstackInputSkyfile ( + xcstack_id BIGINT, + camera_id BIGINT, + stack_id BIGINT, + PRIMARY KEY(xcstack_id, stack_id), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcstackSumSkyfile ( + xcstack_id BIGINT, + path_base VARCHAR(255), + bg DOUBLE, + bg_stdev DOUBLE, + dtime_stack FLOAT, + dtime_script FLOAT, + hostname VARCHAR(64), + good_frac FLOAT, + fault SMALLINT, + software_ver VARCHAR(16), + quality SMALLINT NOT NULL DEFAULT 0, + PRIMARY KEY(xcstack_id), + KEY(dtime_stack), + KEY(good_frac), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcstackCamera ( + camera_id BIGINT, + camera VARCHAR(255), + PRIMARY KEY(camera_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + -- These comment lines are here to avoid an empty query error. -- Another way to avoid that problem is to omit the semicolon above but I think that is untidy. Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_addbyquery_select.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_addbyquery_select.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_addbyquery_select.sql (revision 42541) @@ -0,0 +1,30 @@ +-- This is a query to get a list of skycells with warps to be stacked, +-- along with the number of warps already in a stack. + +SELECT + TMP.xcstack_id, + TMP.stack_id, + TMP.tess_id, + TMP.skycell_id, + TMP.filter + FROM + (SELECT + xcstackRun.xcstack_id, + stackRun.stack_id, + stackRun.tess_id, + stackRun.skycell_id, + stackRun.filter + FROM %s.stackRun + JOIN %s.stackSumSkyfile USING (stack_id) + JOIN xcstackRun + ON ((stackRun.tess_id = xcstackRun.tess_id) + AND (stackRun.skycell_id = xcstackRun.skycell_id) + AND (stackRun.filter = '%s') + AND (xcstackRun.filter = '%s')) +-- WHERE HOOK: + WHERE %s + ) AS TMP + LEFT JOIN xcstackInputSkyfile + ON (TMP.stack_id = xcstackInputSkyfile.stack_id) + AND (camera_id = %d) + WHERE xcstackInputSkyfile.xcstack_id IS NULL Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_addcamera.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_addcamera.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_addcamera.sql (revision 42541) @@ -0,0 +1,3 @@ + +INSERT INTO xcstackCamera + (camera_id, camera) VALUES (0, '@CAMERA@') Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_definebyquery_select.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_definebyquery_select.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_definebyquery_select.sql (revision 42541) @@ -0,0 +1,15 @@ +-- This is a query to get a list of skycells with warps to be stacked, +-- along with the number of warps already in a stack. + +SELECT + xcstack_id, + stackRun.stack_id, + tess_id, + skycell_id, + filter + FROM %s.stackRun + JOIN %s.stackSumSkyfile USING (stack_id) + LEFT JOIN xcstackInputSkyfile + ON (stackRun.stack_id = xcstackInputSkyfile.stack_id) + AND (xcstackInputSkyfile.camera_id = %d) + WHERE xcstack_id IS NULL Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_donecleanup.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_donecleanup.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_donecleanup.sql (revision 42541) @@ -0,0 +1,5 @@ +SELECT + xcstackRun.* +FROM xcstackRun +WHERE + xcstackRun.state = 'cleaned' Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_inputskyfile.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_inputskyfile.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_inputskyfile.sql (revision 42541) @@ -0,0 +1,7 @@ +SELECT + stackSumSkyfile.* + FROM xcstackInputSkyfile + JOIN %s.stackSumSkyfile + ON (xcstackInputSkyfile.stack_id = stackSumSkyfile.stack_id) + AND (xcstackInputSkyfile.camera_id = %d) where (xcstack_id = %d) + Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_pendingcleanuprun.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_pendingcleanuprun.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_pendingcleanuprun.sql (revision 42541) @@ -0,0 +1,9 @@ +-- this query only returns xcstackRun entries with a matching xcstackSumSkyfile +SELECT + xcstackRun.xcstack_id, + xcstackRun.state, + xcstackSumSkyfile.path_base +FROM xcstackRun +JOIN xcstackSumSkyfile USING(xcstack_id) +WHERE + (xcstackRun.state = 'goto_cleaned' OR xcstackRun.state = 'goto_scrubbed' OR xcstackRun.state = 'goto_purged') Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_pendingcleanupskyfile.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_pendingcleanupskyfile.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_pendingcleanupskyfile.sql (revision 42541) @@ -0,0 +1,11 @@ +SELECT + xcstackSumSkyfile.*, + xcstackRun.state, + xcstackRun.workdir, + xcstackRun.tess_id, + xcstackRun.skycell_id +FROM xcstackRun +JOIN xcstackSumSkyfile + USING(xcstack_id) +WHERE + (xcstackRun.state = 'goto_cleaned' OR xcstackRun.state = 'goto_scrubbed' OR xcstackRun.state = 'goto_purged') Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_revertsumskyfile_delete.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_revertsumskyfile_delete.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_revertsumskyfile_delete.sql (revision 42541) @@ -0,0 +1,5 @@ +DELETE FROM xcstackSumSkyfile +USING xcstackSumSkyfile, xcstackRun +WHERE xcstackSumSkyfile.xcstack_id = xcstackRun.xcstack_id + AND xcstackRun.state = 'new' + AND fault != 0 Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_sumskyfile.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_sumskyfile.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_sumskyfile.sql (revision 42541) @@ -0,0 +1,12 @@ +SELECT + xcstackSumSkyfile.*, + xcstackRun.state, + xcstackRun.tess_id, + xcstackRun.skycell_id, + xcstackRun.filter, + xcstackRun.label, + xcstackRun.data_group, + xcstackRun.workdir +FROM xcstackRun +JOIN xcstackSumSkyfile +USING(xcstack_id) Index: branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_tosum.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_tosum.sql (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_tosum.sql (revision 42541) @@ -0,0 +1,15 @@ +SELECT + xcstackRun.xcstack_id, + xcstackRun.tess_id, + xcstackRun.skycell_id, + xcstackRun.workdir, + xcstackRun.reduction, + xcstackRun.label, + xcstackRun.state, + IFNULL(Label.priority, 10000) AS priority +FROM xcstackRun +LEFT JOIN xcstackSumSkyfile USING(xcstack_id) +LEFT JOIN Label ON Label.label = xcstackRun.label +WHERE + (xcstackRun.state = 'new' AND xcstackSumSkyfile.xcstack_id IS NULL) + AND (Label.active OR Label.active IS NULL)