Index: branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am (revision 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am (revision 42572) @@ -564,5 +564,20 @@ xcstacktool_pendingcleanuprun.sql \ xcstacktool_pendingcleanupskyfile.sql \ - xcstacktool_donecleanup.sql - - + xcstacktool_donecleanup.sql \ + xcskytool_definebyquery_select.sql \ + xcskytool_definebyquery_select_by_dg.sql \ + xcskytool_definebyquery_inputs.sql \ + xcskytool_definexccalrun.sql \ + xcskytool_pendingxccalrun.sql \ + xcskytool_revertxccal.sql \ + xcskytool_xccalresult.sql \ + xcskytool_export_run.sql \ + xcskytool_export_input.sql \ + xcskytool_export_result.sql \ + xcskytool_export_xccalrun.sql \ + xcskytool_export_xccalresult.sql \ + xcskytool_inputs.sql \ + xcskytool_todo.sql \ + xcskytool_result.sql \ + xcskytool_revert.sql \ + xcskytool_revert_update.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 42541) +++ branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql (revision 42572) @@ -2176,4 +2176,100 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; +-- xcsky and xccal tables +CREATE TABLE xcskyRun ( + xcsky_id BIGINT AUTO_INCREMENT, -- unique identifier + state VARCHAR(64) NOT NULL, -- state of run (new, full, etc.) + workdir VARCHAR(255) NOT NULL, -- working directory + label VARCHAR(64), -- processing label + data_group VARCHAR(64), -- group for data + dist_group VARCHAR(64), -- group for distribution + reduction VARCHAR(64), -- reduction class (for altering recipe) + note VARCHAR(255), -- note + registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered + PRIMARY KEY(xcsky_id), + KEY(state), + KEY(label), + KEY(data_group), + KEY(dist_group) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Inputs for xcsky analysis +CREATE TABLE xcskyInput ( + xcsky_id BIGINT, -- static sky identifier + xcstack_id BIGINT, -- stack identifier + PRIMARY KEY(xcsky_id,xcstack_id), + KEY(xcstack_id), + FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id), + FOREIGN KEY(xcstack_id) REFERENCES xcstackSumSkyfile(xcstack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Result of static sky analysis +CREATE TABLE xcskyResult ( + xcsky_id BIGINT, -- static sky identifier + path_base VARCHAR(255) NOT NULL, -- root name for outputs + dtime_phot FLOAT, -- elapsed time for photometry + dtime_script FLOAT, -- elapsed time for script + sources INT, -- number of sources + num_inputs INT, -- number of input images + hostname VARCHAR(64) NOT NULL, -- host that executed script + good_frac FLOAT, -- good fraction of skycell + quality SMALLINT NOT NULL, -- bad quality flag + fault SMALLINT NOT NULL, -- fault code + PRIMARY KEY(xcsky_id), + KEY(good_frac), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xccalRun ( + xccal_id BIGINT AUTO_INCREMENT, -- unique identifier + xcsky_id BIGINT, + xcstack_id BIGINT, + state VARCHAR(64) NOT NULL, -- state of run (new, full, etc.) + workdir VARCHAR(255) NOT NULL, -- working directory + label VARCHAR(64), -- processing label + data_group VARCHAR(64), -- group for data + dist_group VARCHAR(64), -- group for distribution + reduction VARCHAR(64), -- reduction class (for altering recipe) + registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered + note VARCHAR(255), -- note + PRIMARY KEY(xccal_id), + KEY(state), + KEY(label), + KEY(data_group), + KEY(dist_group), + KEY(xcsky_id), + KEY(xcstack_id), + FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Result of sky calibration analysis +CREATE TABLE xccalResult ( + xccal_id BIGINT, + path_base VARCHAR(255) NOT NULL, + dtime_script FLOAT, + dtime_astrom FLOAT, + sigma_ra FLOAT, + sigma_dec FLOAT, + n_astrom INT, + n_detections INT, + n_extended INT, + n_forced INT, + zpt_obs FLOAT, + zpt_stdev FLOAT, + fwhm_major FLOAT, + fwhm_minor FLOAT, + quality SMALLINT NOT NULL, + hostname VARCHAR(64) NOT NULL, + software_ver VARCHAR(64), + fault SMALLINT NOT NULL, + PRIMARY KEY(xccal_id), + KEY(fault), + KEY(quality), + FOREIGN KEY(xccal_id) REFERENCES xccalRun(xccal_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + CREATE TABLE skycell ( tess_id VARCHAR(64), Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery.sql (revision 42572) @@ -0,0 +1,22 @@ +SELECT + tess_id, + skycell_id, + num_filter +FROM ( + SELECT + tess_id, + skycell_id, + COUNT(DISTINCT filter) AS num_filter + FROM xcstackRun + JOIN xcstackSumSkyfile USING(xcstack_id) + WHERE xcstackRun.state = 'full' + AND xcstackSumSkyfile.fault = 0 + AND xcstackSumSkyfile.quality = 0 + -- WHERE hook 1 %s + -- restrict by xcstackSumSkyfile.good_frac, xcstackRun.skycell_id, xcstackRun.label + GROUP BY + tess_id, + skycell_id + ) AS TMP +WHERE num_filter == %d + Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_inputs.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_inputs.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_inputs.sql (revision 42572) @@ -0,0 +1,16 @@ + SELECT + MAX(xcstack_id) as xcstack_id, + tess_id, + skycell_id, + filter + FROM xcstackRun + JOIN xcstackSumSkyfile USING(xcstack_id) + JOIN skycell USING(tess_id, skycell_id) + WHERE xcstackRun.state = 'full' + AND xcstackSumSkyfile.fault = 0 + AND xcstackSumSkyfile.quality = 0 + AND xcstackRun.tess_id = '%s' + AND xcstackRun.skycell_id = '%s' + -- WHERE hook 1 %s + -- WHERE hook 2 %s + GROUP BY xcstackRun.filter Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select.sql (revision 42572) @@ -0,0 +1,41 @@ +SELECT DISTINCT + tess_id, + skycell_id, + num_filter +FROM ( + SELECT + tess_id, + skycell_id, + COUNT(DISTINCT filter) AS num_filter + FROM xcstackRun + JOIN xcstackSumSkyfile USING(xcstack_id) + JOIN skycell using(tess_id, skycell_id) + WHERE xcstackRun.state = 'full' + AND xcstackSumSkyfile.fault = 0 + AND xcstackSumSkyfile.quality = 0 + -- WHERE hook 1 %s + -- restrict by selected filters, xcstackSumSkyfile.good_frac, xcstackRun.skycell_id, xcstackRun.label + GROUP BY + tess_id, + skycell_id + ) AS xcstacks +LEFT JOIN ( + SELECT DISTINCT xcsky_id, + xcstackRun.skycell_id, + xcstackRun.tess_id, + xcstackRun.data_group, + COUNT(xcstack_id) AS num_filter + FROM xcskyRun + JOIN xcskyInput USING(xcsky_id) + JOIN xcstackRun USING(xcstack_id) + JOIN xcstackSumSkyfile USING(xcstack_id) + JOIN skycell USING(tess_id, skycell_id) + WHERE 1 + -- restrict by selected filters, xcstackSumSkyfile.good_frac, xcstackRun.skycell_id, xcstackRun.label + -- and unless -rerun is supplied that no existing xcskyRun exists + -- WHERE hook 2 %s + GROUP BY xcsky_id + ) AS ExistingRunsInputs +USING(tess_id, skycell_id, num_filter) +WHERE num_filter = %d +-- WHERE hook 3 %s Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select_by_dg.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select_by_dg.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definebyquery_select_by_dg.sql (revision 42572) @@ -0,0 +1,43 @@ +SELECT DISTINCT + tess_id, + skycell_id, + Xcstacks.data_group, + num_filter +FROM ( + SELECT + tess_id, + skycell_id, + label, + data_group, + COUNT(DISTINCT filter) AS num_filter + FROM xcstackRun + JOIN xcstackSumSkyfile USING(xcstack_id) + WHERE xcstackRun.state = 'full' + AND xcstackSumSkyfile.fault = 0 + AND xcstackSumSkyfile.quality = 0 + -- WHERE hook 1 %s + -- restrict by selected filters, xcstackSumSkyfile.good_frac, xcstackRun.skycell_id, xcstackRun.label + GROUP BY + tess_id, + skycell_id, + data_group + ) AS Xcstacks +LEFT JOIN ( + SELECT DISTINCT xcsky_id, + xcstackRun.skycell_id, + xcstackRun.tess_id, + xcstackRun.data_group, + COUNT(xcstack_id) AS num_filter + FROM xcskyRun + JOIN xcskyInput USING(xcsky_id) + JOIN xcstackRun USING(xcstack_id) + JOIN xcstackSumSkyfile USING(xcstack_id) + WHERE 1 + -- WHERE hook 2 %s + -- restrict by selected filters, xcstackSumSkyfile.good_frac, xcstackRun.skycell_id, xcstackRun.label + GROUP BY xcsky_id + ) AS ExistingRunsInputs +USING(tess_id, skycell_id, data_group, num_filter) +WHERE num_filter = %d + AND Xcstacks.data_group = ExistingRunsInputs.data_group +-- WHERE hook 3 %s Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definexccalrun.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definexccalrun.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_definexccalrun.sql (revision 42572) @@ -0,0 +1,18 @@ +SELECT + xcskyRun.xcsky_id, + xcstackRun.xcstack_id, + skycell_id, + filter, + xcskyRun.workdir, + xcskyRun.label, + xcskyRun.data_group, + xcskyRun.dist_group +FROM xcskyRun + JOIN xcskyResult USING(xcsky_id) + JOIN xcskyInput USING(xcsky_id) + JOIN xcstackRun USING(xcstack_id) + JOIN xcstackSumSkyfile USING(xcstack_id) + JOIN skycell USING(tess_id, skycell_id) + LEFT JOIN xccalRun ON xcskyRun.xcsky_id = xccalRun.xcsky_id AND xcstackRun.xcstack_id = xccalRun.xcstack_id -- join hook %s +WHERE xcskyRun.state = 'full' + AND xcskyResult.quality = 0 Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_input.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_input.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_input.sql (revision 42572) @@ -0,0 +1,1 @@ +SELECT xcskyInput.* FROM xcskyInput Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_result.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_result.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_result.sql (revision 42572) @@ -0,0 +1,1 @@ +SELECT xcskyResult.* FROM xcskyResult Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_run.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_run.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_run.sql (revision 42572) @@ -0,0 +1,1 @@ +SELECT xcskyRun.* from xcskyRun Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_xccalresult.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_xccalresult.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_xccalresult.sql (revision 42572) @@ -0,0 +1,1 @@ +SELECT * from xccalResult Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_xccalrun.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_xccalrun.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_export_xccalrun.sql (revision 42572) @@ -0,0 +1,1 @@ +SELECT * from xccalRun Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_inputs.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_inputs.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_inputs.sql (revision 42572) @@ -0,0 +1,9 @@ +SELECT + xcskyRun.*, + xcskyInput.xcstack_id, + xcstackSumSkyfile.path_base, + xcstackRun.filter +FROM xcskyRun +JOIN xcskyInput USING(xcsky_id) +JOIN xcstackSumSkyfile USING (xcstack_id) +JOIN xcstackRun using(xcstack_id) Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_pendingxccalrun.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_pendingxccalrun.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_pendingxccalrun.sql (revision 42572) @@ -0,0 +1,16 @@ +SELECT DISTINCT + xccalRun.*, + xcstackRun.tess_id, + xcstackRun.skycell_id, + xcstackRun.filter, + xcskyResult.path_base, + xcskyResult.num_inputs = 1 AS singlefilter +FROM xccalRun +JOIN xcskyRun USING (xcsky_id) +JOIN xcskyResult USING(xcsky_id) +JOIN xcstackRun USING (xcstack_id) +JOIN xcstackInputSkyfile USING(xcstack_id) +LEFT JOIN xccalResult USING(xccal_id) +WHERE + ((xccalRun.state = 'new' AND xccalResult.xccal_id IS NULL) + OR (xccalRun.state ='update' and xcskyRun.state ='full' and xccalResult.fault = 0)) Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_result.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_result.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_result.sql (revision 42572) @@ -0,0 +1,18 @@ +SELECT + xcskyResult.*, + xcskyRun.state, + xcskyRun.workdir, + xcskyRun.label, + xcstackRun.tess_id, + xcstackRun.skycell_id, + count(xcstackRun.filter) AS num_filters, + TRUNCATE(skycell.radeg/15., 4) AS rahours, + skycell.radeg, + skycell.decdeg, + skycell.glong, + skycell.glat +FROM xcskyRun +JOIN xcskyResult USING(xcsky_id) +JOIN xcskyInput USING(xcsky_id) +JOIN xcstackRun USING(xcstack_id) +LEFT JOIN skycell USING(tess_id, skycell_id) Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revert.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revert.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revert.sql (revision 42572) @@ -0,0 +1,9 @@ +DELETE FROM xcskyResult +USING xcskyResult, xcskyRun, xcskyInput, xcstackRun, skycell +WHERE xcskyRun.xcsky_id = xcskyResult.xcsky_id + AND xcskyRun.xcsky_id = xcskyInput.xcsky_id + AND xcskyInput.xcstack_id = stackRun.stack_id + AND xcstackRun.tess_id = skycell.tess_id + AND xcstackRun.skycell_id = skycell.skycell_id + AND xcskyRun.state = 'new' + AND xcskyResult.fault != 0 Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revert_update.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revert_update.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revert_update.sql (revision 42572) @@ -0,0 +1,11 @@ +UPDATE xcskyRun + JOIN xcskyResult USING(xcsky_id) JOIN xcskyInput USING(xcsky_id) + JOIN xcstackRun USING(xcstack_id) JOIN skycell USING(tess_id, skycell_id) +SET xcskyResult.fault = 0 +WHERE xcskyRun.xcsky_id = xcskyResult.xcsky_id + AND xcskyRun.xcsky_id = xcskyInput.xcsky_id + AND xcskyInput.xcstack_id = stackRun.xcstack_id + AND xcstackRun.tess_id = skycell.tess_id + AND xcstackRun.skycell_id = skycell.skycell_id + AND xcskyRun.state = 'update' + AND xcskyResult.fault != 0 Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revertxccal.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revertxccal.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_revertxccal.sql (revision 42572) @@ -0,0 +1,8 @@ +DELETE FROM xccalResult +USING xccalResult, xccalRun, xcstackRun, skycell +WHERE xccalRun.xccal_id = xccalResult.xccal_id + AND xcstackRun.stack_id = xccalRun.stack_id + AND xccalRun.state = 'new' + AND xccalResult.fault != 0 + AND xcstackRun.tess_id = skycell.tess_id + AND xcstackRun.skycell_id = skycell.skycell_id Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_todo.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_todo.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_todo.sql (revision 42572) @@ -0,0 +1,29 @@ +SELECT + xcskyRun.xcsky_id, + xcskyRun.workdir, + xcskyRun.reduction, + xcskyRun.label, + xcskyRun.state, + xcstackRun.tess_id, + xcstackRun.skycell_id, + TRUNCATE(skycell.radeg/15., 4) as rahours, + skycell.radeg, + skycell.decdeg, + skycell.glong, + skycell.glat, + xcskyResult.path_base, + IFNULL(Label.priority, 10000) AS priority +FROM xcskyRun +JOIN xcskyInput USING (xcsky_id) +JOIN xcstackRun USING (xcstack_id) +JOIN skycell USING(tess_id, skycell_id) +LEFT JOIN xcskyResult USING(xcsky_id) +LEFT JOIN Label ON xcskyRun.label = Label.label +WHERE + ((xcskyRun.state = 'new' AND xcskyResult.xcsky_id IS NULL) + OR (xcskyRun.state = 'update' AND xcskyResult.fault = 0)) + AND (Label.active OR Label.active IS NULL) + -- WHERE hook %s +GROUP BY xcsky_id +HAVING SUM(IF(xcstackRun.state = 'full', 1, 0)) = COUNT(xcskyInput.xcsky_id) +ORDER BY priority DESC, xcsky_id Index: branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_xccalresult.sql =================================================================== --- branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_xccalresult.sql (revision 42572) +++ branches/eam_branches/ipp-20230313/ippTools/share/xcskytool_xccalresult.sql (revision 42572) @@ -0,0 +1,15 @@ +SELECT + xccalResult.*, + xccalRun.xcstack_id, + xccalRun.workdir, + xcstackRun.filter, + xccalRun.state, + xccalRun.label, + xccalRun.data_group, + xccalRun.dist_group, + xccalRun.xcsky_id, + skycell.* +FROM xccalRun +JOIN xccalResult USING(xccal_id) +JOIN xcstackRun USING(xcstack_id) +JOIN skycell USING(tess_id, skycell_id)