Index: trunk/ippTools/share/Makefile.am =================================================================== --- trunk/ippTools/share/Makefile.am (revision 42712) +++ trunk/ippTools/share/Makefile.am (revision 42837) @@ -554,4 +554,41 @@ fpcamtool_queue_cam_id.sql \ fpcamtool_revertprocessedexp.sql \ - fpcamtool_revertupdatedexp.sql - + fpcamtool_revertupdatedexp.sql \ + xcstacktool_definebyquery_select.sql \ + xcstacktool_definebyquery_select_XC.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 \ + 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 \ + 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: trunk/ippTools/share/pxadmin_create_pstamp_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_create_pstamp_tables.sql (revision 42837) +++ trunk/ippTools/share/pxadmin_create_pstamp_tables.sql (revision 42837) @@ -0,0 +1,136 @@ +CREATE TABLE pstampDataStore ( + ds_id BIGINT AUTO_INCREMENT, + state VARCHAR(64), + lastFileset VARCHAR(64), + timestamp DATETIME, + label VARCHAR(64), + outProduct VARCHAR(64) UNIQUE, + uri VARCHAR(255), + pollInterval INTEGER DEFAULT 60, + need_magic TINYINT, + PRIMARY KEY(ds_id), + KEY(ds_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampProject ( + proj_id BIGINT AUTO_INCREMENT, + name VARCHAR(64) UNIQUE, + state VARCHAR(64), + dbname VARCHAR(64), + dvodb VARCHAR(64), + camera VARCHAR(64), + telescope VARCHAR(64), + need_magic TINYINT, + PRIMARY KEY(proj_id), + KEY(proj_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampRequest ( + req_id BIGINT AUTO_INCREMENT, + ds_id BIGINT, + state VARCHAR(64), + name VARCHAR(64) UNIQUE, + reqType VARCHAR(16), + label VARCHAR(64), + outProduct VARCHAR(64), + uri VARCHAR(255), + outdir VARCHAR(255), + username VARCHAR(255), + proj_id BIGINT, + registered datetime, + timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + fault SMALLINT, + PRIMARY KEY(req_id), + KEY(req_id), + KEY(state), + KEY(fault), + KEY(label), + KEY(username) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampJob ( + job_id BIGINT AUTO_INCREMENT, + req_id BIGINT, + rownum VARCHAR(64), + state VARCHAR(64), + jobType VARCHAR(16), + fault SMALLINT, + exp_id BIGINT, + outputBase VARCHAR(255), + options BIGINT, + dep_id BIGINT, + fault_count INT, + parent_id BIGINT DEFAULT NULL, + is_parent TINYINT DEFAULT 0, + PRIMARY KEY(job_id, req_id), + KEY(job_id), + KEY(req_id), + KEY(state), + KEY(fault), + KEY(dep_id), + KEY(parent_id), + KEY(is_parent), + FOREIGN KEY(req_id) REFERENCES pstampRequest(req_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampDependent ( + dep_id BIGINT AUTO_INCREMENT, + state VARCHAR(64), + stage VARCHAR(64), + stage_id BIGINT, + component VARCHAR(64), + imagedb VARCHAR(64), + outdir VARCHAR(255), + rlabel VARCHAR(64), + need_magic TINYINT, + fault SMALLINT, + fault_count INT, + PRIMARY KEY(dep_id), + KEY(state), + KEY(stage), + KEY(stage_id), + KEY(component), + KEY(imagedb), + KEY(fault) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampFile ( + file_id BIGINT AUTO_INCREMENT, + job_id BIGINT NOT NULL, + path VARCHAR(255), + PRIMARY KEY(file_id), + KEY(job_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampUserDomain ( + domainName VARCHAR(64), + accessLevel INT, + defaultProduct VARCHAR(255), + defaultLabel VARCHAR(64), + PRIMARY KEY(domainName) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampUser ( + userName VARCHAR(64), + domainName VARCHAR(64), + accessLevel INT, + defaultProduct VARCHAR(255), + defaultLabel VARCHAR(64), + PRIMARY KEY (userName, domainName) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampAccessLevel ( + proj_id BIGINT, + accessLevel INT, + mjd_min FLOAT DEFAULT 0, + mjd_max FLOAT DEFAULT 0, + PRIMARY KEY (proj_id, accessLevel), + UNIQUE KEY (proj_id, accessLevel), + FOREIGN KEY(proj_id) REFERENCES pstampProject(proj_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pstampWebRequest ( + num BIGINT AUTO_INCREMENT, + PRIMARY KEY(num) +) ENGINE=innodb DEFAULT CHARSET=latin1; + Index: trunk/ippTools/share/pxadmin_create_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_create_tables.sql (revision 42712) +++ trunk/ippTools/share/pxadmin_create_tables.sql (revision 42837) @@ -394,5 +394,4 @@ KEY(fault), KEY(quality), - KEY(data_state), FOREIGN KEY(chip_id, exp_id) REFERENCES chipRun(chip_id, exp_id), FOREIGN KEY(exp_id, class_id) REFERENCES rawImfile(exp_id, class_id) @@ -558,4 +557,5 @@ minidvodb_group VARCHAR(64), minidvodb_name VARCHAR(64), + minidvodb_host VARCHAR(64), PRIMARY KEY(add_id), KEY(add_id), @@ -1414,140 +1414,4 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE pstampDataStore ( - ds_id BIGINT AUTO_INCREMENT, - state VARCHAR(64), - lastFileset VARCHAR(64), - timestamp DATETIME, - label VARCHAR(64), - outProduct VARCHAR(64) UNIQUE, - uri VARCHAR(255), - pollInterval INTEGER DEFAULT 60, - need_magic TINYINT, - PRIMARY KEY(ds_id), - KEY(ds_id) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampProject ( - proj_id BIGINT AUTO_INCREMENT, - name VARCHAR(64) UNIQUE, - state VARCHAR(64), - dbname VARCHAR(64), - dvodb VARCHAR(64), - camera VARCHAR(64), - telescope VARCHAR(64), - need_magic TINYINT, - PRIMARY KEY(proj_id), - KEY(proj_id) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampRequest ( - req_id BIGINT AUTO_INCREMENT, - ds_id BIGINT, - state VARCHAR(64), - name VARCHAR(64) UNIQUE, - reqType VARCHAR(16), - label VARCHAR(64), - outProduct VARCHAR(64), - uri VARCHAR(255), - outdir VARCHAR(255), - username VARCHAR(255), - proj_id BIGINT, - registered datetime, - timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - fault SMALLINT, - PRIMARY KEY(req_id), - KEY(req_id), - KEY(state), - KEY(fault), - KEY(label), - KEY(username) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampJob ( - job_id BIGINT AUTO_INCREMENT, - req_id BIGINT, - rownum VARCHAR(64), - state VARCHAR(64), - jobType VARCHAR(16), - fault SMALLINT, - exp_id BIGINT, - outputBase VARCHAR(255), - options BIGINT, - dep_id BIGINT, - fault_count INT, - parent_id BIGINT DEFAULT NULL, - is_parent TINYINT DEFAULT 0, - PRIMARY KEY(job_id, req_id), - KEY(job_id), - KEY(req_id), - KEY(state), - KEY(fault), - KEY(dep_id), - KEY(parent_id), - KEY(is_parent), - FOREIGN KEY(req_id) REFERENCES pstampRequest(req_id) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampDependent ( - dep_id BIGINT AUTO_INCREMENT, - state VARCHAR(64), - stage VARCHAR(64), - stage_id BIGINT, - component VARCHAR(64), - imagedb VARCHAR(64), - outdir VARCHAR(255), - rlabel VARCHAR(64), - need_magic TINYINT, - fault SMALLINT, - fault_count INT, - PRIMARY KEY(dep_id), - KEY(state), - KEY(stage), - KEY(stage_id), - KEY(component), - KEY(imagedb), - KEY(fault) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampFile ( - file_id BIGINT AUTO_INCREMENT, - job_id BIGINT NOT NULL, - path VARCHAR(255), - PRIMARY KEY(file_id), - KEY(job_id) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampUserDomain ( - domainName VARCHAR(64), - accessLevel INT, - defaultProduct VARCHAR(255), - defaultLabel VARCHAR(64), - PRIMARY KEY(domainName) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampUser ( - userName VARCHAR(64), - domainName VARCHAR(64), - accessLevel INT, - defaultProduct VARCHAR(255), - defaultLabel VARCHAR(64), - PRIMARY KEY (userName, domainName) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampAccessLevel ( - proj_id BIGINT, - accessLevel INT, - mjd_min FLOAT DEFAULT 0, - mjd_max FLOAT DEFAULT 0, - PRIMARY KEY (proj_id, accessLevel), - UNIQUE KEY (proj_id, accessLevel), - FOREIGN KEY(proj_id) REFERENCES pstampProject(proj_id) -) ENGINE=innodb DEFAULT CHARSET=latin1; - -CREATE TABLE pstampWebRequest ( - num BIGINT AUTO_INCREMENT, - PRIMARY KEY(num) -) ENGINE=innodb DEFAULT CHARSET=latin1; - CREATE TABLE distTarget ( target_id BIGINT AUTO_INCREMENT, @@ -1856,4 +1720,5 @@ class_id VARCHAR(64) NOT NULL, -- class (component) identifier path_base VARCHAR(255) NOT NULL, -- root name for outputs + data_state VARCHAR(64), magicked BIGINT, -- magic_id if magicked dtime_script FLOAT, -- elapsed time for script @@ -1905,4 +1770,5 @@ skycell_id VARCHAR(64) NOT NULL, -- skycell identifier path_base VARCHAR(255) NOT NULL, -- root name for outputs + data_state VARCHAR(64), magicked BIGINT, -- magic_id if magicked dtime_script FLOAT, -- elapsed time for script @@ -2022,5 +1888,7 @@ minidvodb_group VARCHAR(64) NOT NULL, minidvodb_path VARCHAR(255) NOT NULL, + minidvodb_host VARCHAR(64) NOT NULL, state VARCHAR(64) NOT NULL, + note VARCHAR(255) NOT NULL, creation_date TIMESTAMP, PRIMARY KEY(minidvodb_id), KEY(minidvodb_name), INDEX(minidvodb_id) @@ -2168,5 +2036,5 @@ quality SMALLINT NOT NULL, hostname VARCHAR(64) NOT NULL, - software_ver VARCHAR(64), + software_ver VARCHAR(16), fault SMALLINT NOT NULL, PRIMARY KEY(skycal_id), @@ -2481,4 +2349,222 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE xcstackCamera ( + camera_id BIGINT AUTO_INCREMENT, + camera VARCHAR(255), + PRIMARY KEY(camera_id) +) 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, camera_id, stack_id), + KEY(xcstack_id), + KEY(stack_id), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id), + FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_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; + +-- A static sky analysis set +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 static sky 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 xcstackRun(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(16), + 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 xcForceRun ( + xcff_id BIGINT NOT NULL AUTO_INCREMENT, + xccal_id BIGINT, -- NOT a foreign key: reference to entry in xcamera db + sources_path_base VARCHAR(255), + state VARCHAR(64), + workdir VARCHAR(255), + label VARCHAR(64), + skycell_id VARCHAR(64), + tess_id VARCHAR(64), + data_group VARCHAR(64), + dist_group VARCHAR(64), + note VARCHAR(255), + reduction VARCHAR(64), + registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered + PRIMARY KEY(xcff_id), + KEY(state), + KEY(label), + KEY(data_group), + KEY(xccal_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcForceInput ( + xcff_id BIGINT, + warp_id BIGINT, + camera_id BIGINT, + PRIMARY KEY(xcff_id, warp_id, camera_id), + FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id), + FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id), + FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcForceResult ( + xcff_id BIGINT, + warp_id BIGINT, + path_base VARCHAR(255) NOT NULL, + dtime_script FLOAT, + quality SMALLINT NOT NULL, + hostname VARCHAR(64) NOT NULL, + software_ver VARCHAR(16), + fault SMALLINT NOT NULL, + PRIMARY KEY(xcff_id, warp_id), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id), + FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcForceSummary ( + xcff_id BIGINT, + path_base VARCHAR(255) NOT NULL, + dtime_script FLOAT, + quality SMALLINT NOT NULL, + hostname VARCHAR(64) NOT NULL, + software_ver VARCHAR(16), + fault SMALLINT NOT NULL, + PRIMARY KEY(xcff_id), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_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: trunk/ippTools/share/pxadmin_drop_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_drop_tables.sql (revision 42712) +++ trunk/ippTools/share/pxadmin_drop_tables.sql (revision 42837) @@ -131,4 +131,17 @@ DROP TABLE IF EXISTS fpcamProcessedExp; DROP TABLE IF EXISTS fpcamRun; +DROP TABLE IF EXISTS xccalResult; +DROP TABLE IF EXISTS xccalRun; +DROP TABLE IF EXISTS xcForceInput; +DROP TABLE IF EXISTS xcForceResult; +DROP TABLE IF EXISTS xcForceRun; +DROP TABLE IF EXISTS xcForceSummary; +DROP TABLE IF EXISTS xcskyInput; +DROP TABLE IF EXISTS xcskyResult; +DROP TABLE IF EXISTS xcskyRun; +DROP TABLE IF EXISTS xcstackCamera; +DROP TABLE IF EXISTS xcstackInputSkyfile; +DROP TABLE IF EXISTS xcstackRun; +DROP TABLE IF EXISTS xcstackSumSkyfile; SET FOREIGN_KEY_CHECKS=1 Index: trunk/ippTools/share/xcfftool_definebyquery_pt1.sql =================================================================== --- trunk/ippTools/share/xcfftool_definebyquery_pt1.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_definebyquery_pt1.sql (revision 42837) @@ -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: trunk/ippTools/share/xcfftool_definebyquery_pt2.sql =================================================================== --- trunk/ippTools/share/xcfftool_definebyquery_pt2.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_definebyquery_pt2.sql (revision 42837) @@ -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: trunk/ippTools/share/xcfftool_definebyquery_select_warps.sql =================================================================== --- trunk/ippTools/share/xcfftool_definebyquery_select_warps.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_definebyquery_select_warps.sql (revision 42837) @@ -0,0 +1,8 @@ +SELECT warp_id +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: trunk/ippTools/share/xcfftool_export_input.sql =================================================================== --- trunk/ippTools/share/xcfftool_export_input.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_export_input.sql (revision 42837) @@ -0,0 +1,3 @@ +SELECT + fullForceInput.* +FROM fullForceInput Index: trunk/ippTools/share/xcfftool_export_result.sql =================================================================== --- trunk/ippTools/share/xcfftool_export_result.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_export_result.sql (revision 42837) @@ -0,0 +1,3 @@ +SELECT + fullForceResult.* +FROM fullForceResult Index: trunk/ippTools/share/xcfftool_export_run.sql =================================================================== --- trunk/ippTools/share/xcfftool_export_run.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_export_run.sql (revision 42837) @@ -0,0 +1,3 @@ +SELECT + fullForceRun.* +FROM fullForceRun Index: trunk/ippTools/share/xcfftool_export_summary.sql =================================================================== --- trunk/ippTools/share/xcfftool_export_summary.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_export_summary.sql (revision 42837) @@ -0,0 +1,3 @@ +SELECT + fullForceSummary.* +FROM fullForceSummary Index: trunk/ippTools/share/xcfftool_result.sql =================================================================== --- trunk/ippTools/share/xcfftool_result.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_result.sql (revision 42837) @@ -0,0 +1,22 @@ +SELECT + 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, + xcForceRun.tess_id, + xcForceRun.skycell_id, + rawExp.exp_id, + rawExp.exp_name, + rawExp.camera +FROM xcForceRun + JOIN xcForceResult 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) Index: trunk/ippTools/share/xcfftool_revert.sql =================================================================== --- trunk/ippTools/share/xcfftool_revert.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_revert.sql (revision 42837) @@ -0,0 +1,4 @@ +DELETE FROM xcForceResult +USING xcForceResult, xcForceRun +WHERE xcForceRun.xcff_id = xcForceResult.xcff_id + AND xcForceResult.fault != 0 Index: trunk/ippTools/share/xcfftool_revertsummary.sql =================================================================== --- trunk/ippTools/share/xcfftool_revertsummary.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_revertsummary.sql (revision 42837) @@ -0,0 +1,5 @@ +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: trunk/ippTools/share/xcfftool_summary.sql =================================================================== --- trunk/ippTools/share/xcfftool_summary.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_summary.sql (revision 42837) @@ -0,0 +1,15 @@ +SELECT DISTINCT + 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 +FROM xcForceRun + JOIN xcForceSummary USING(xcff_id) + JOIN xcForceInput USING(xcff_id) + JOIN skycell USING (tess_id, skycell_id) Index: trunk/ippTools/share/xcfftool_summaryinputs.sql =================================================================== --- trunk/ippTools/share/xcfftool_summaryinputs.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_summaryinputs.sql (revision 42837) @@ -0,0 +1,4 @@ +SELECT + fullForceResult.* +FROM fullForceRun + JOIN fullForceResult USING(ff_id) Index: trunk/ippTools/share/xcfftool_toadvance.sql =================================================================== --- trunk/ippTools/share/xcfftool_toadvance.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_toadvance.sql (revision 42837) @@ -0,0 +1,15 @@ +SELECT + 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.xcff_id IS NULL + -- WHERE hook %s + GROUP BY xcff_id + HAVING COUNT(xcForceInput.warp_id) = COUNT(xcForceResult.warp_id) + AND SUM(xcForceResult.fault) = 0 Index: trunk/ippTools/share/xcfftool_todo.sql =================================================================== --- trunk/ippTools/share/xcfftool_todo.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_todo.sql (revision 42837) @@ -0,0 +1,12 @@ +SELECT + xcForceRun.*, + xcForceInput.warp_id, + warpSkyfile.path_base AS warp_path_base +FROM xcForceRun + JOIN xcForceInput USING(xcff_id) + JOIN warpRun USING(warp_id, tess_id) + JOIN warpSkyfile USING(warp_id, tess_id, skycell_id) + LEFT JOIN xcForceResult USING(xcff_id, warp_id) +WHERE xcForceRun.state = 'new' + AND xcForceResult.xcff_id IS NULL + AND warpSkyfile.data_state = 'full' Index: trunk/ippTools/share/xcfftool_updaterun.sql =================================================================== --- trunk/ippTools/share/xcfftool_updaterun.sql (revision 42837) +++ trunk/ippTools/share/xcfftool_updaterun.sql (revision 42837) @@ -0,0 +1,2 @@ +UPDATE xcForceRun +JOIN skycell USING(tess_id, skycell_id); Index: trunk/ippTools/share/xcskytool_definebyquery.sql =================================================================== --- trunk/ippTools/share/xcskytool_definebyquery.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_definebyquery.sql (revision 42837) @@ -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: trunk/ippTools/share/xcskytool_definebyquery_inputs.sql =================================================================== --- trunk/ippTools/share/xcskytool_definebyquery_inputs.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_definebyquery_inputs.sql (revision 42837) @@ -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: trunk/ippTools/share/xcskytool_definebyquery_select.sql =================================================================== --- trunk/ippTools/share/xcskytool_definebyquery_select.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_definebyquery_select.sql (revision 42837) @@ -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, + 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 + 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: trunk/ippTools/share/xcskytool_definebyquery_select_by_dg.sql =================================================================== --- trunk/ippTools/share/xcskytool_definebyquery_select_by_dg.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_definebyquery_select_by_dg.sql (revision 42837) @@ -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: trunk/ippTools/share/xcskytool_definexccalrun.sql =================================================================== --- trunk/ippTools/share/xcskytool_definexccalrun.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_definexccalrun.sql (revision 42837) @@ -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: trunk/ippTools/share/xcskytool_export_input.sql =================================================================== --- trunk/ippTools/share/xcskytool_export_input.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_export_input.sql (revision 42837) @@ -0,0 +1,1 @@ +SELECT xcskyInput.* FROM xcskyInput Index: trunk/ippTools/share/xcskytool_export_result.sql =================================================================== --- trunk/ippTools/share/xcskytool_export_result.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_export_result.sql (revision 42837) @@ -0,0 +1,1 @@ +SELECT xcskyResult.* FROM xcskyResult Index: trunk/ippTools/share/xcskytool_export_run.sql =================================================================== --- trunk/ippTools/share/xcskytool_export_run.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_export_run.sql (revision 42837) @@ -0,0 +1,1 @@ +SELECT xcskyRun.* from xcskyRun Index: trunk/ippTools/share/xcskytool_export_xccalresult.sql =================================================================== --- trunk/ippTools/share/xcskytool_export_xccalresult.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_export_xccalresult.sql (revision 42837) @@ -0,0 +1,1 @@ +SELECT * from xccalResult Index: trunk/ippTools/share/xcskytool_export_xccalrun.sql =================================================================== --- trunk/ippTools/share/xcskytool_export_xccalrun.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_export_xccalrun.sql (revision 42837) @@ -0,0 +1,1 @@ +SELECT * from xccalRun Index: trunk/ippTools/share/xcskytool_inputs.sql =================================================================== --- trunk/ippTools/share/xcskytool_inputs.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_inputs.sql (revision 42837) @@ -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: trunk/ippTools/share/xcskytool_pendingxccalrun.sql =================================================================== --- trunk/ippTools/share/xcskytool_pendingxccalrun.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_pendingxccalrun.sql (revision 42837) @@ -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: trunk/ippTools/share/xcskytool_result.sql =================================================================== --- trunk/ippTools/share/xcskytool_result.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_result.sql (revision 42837) @@ -0,0 +1,18 @@ +SELECT + xcskyResult.*, + xcskyRun.state, + xcskyRun.workdir, + xcskyRun.label, + 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 + count(xcstackRun.filter) AS num_filters, + TRUNCATE(MIN(skycell.radeg)/15., 4) as rahours, + MIN(skycell.radeg) as radeg, + MIN(skycell.decdeg) as decdeg, + MIN(skycell.glong) as glong, + MIN(skycell.glat) as 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: trunk/ippTools/share/xcskytool_revert.sql =================================================================== --- trunk/ippTools/share/xcskytool_revert.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_revert.sql (revision 42837) @@ -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 = xcstackRun.xcstack_id + AND xcstackRun.tess_id = skycell.tess_id + AND xcstackRun.skycell_id = skycell.skycell_id + AND xcskyRun.state = 'new' + AND xcskyResult.fault != 0 Index: trunk/ippTools/share/xcskytool_revert_update.sql =================================================================== --- trunk/ippTools/share/xcskytool_revert_update.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_revert_update.sql (revision 42837) @@ -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 = xcstackRun.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: trunk/ippTools/share/xcskytool_revertxccal.sql =================================================================== --- trunk/ippTools/share/xcskytool_revertxccal.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_revertxccal.sql (revision 42837) @@ -0,0 +1,8 @@ +DELETE FROM xccalResult +USING xccalResult, xccalRun, xcstackRun, skycell +WHERE xccalRun.xccal_id = xccalResult.xccal_id + AND xcstackRun.xcstack_id = xccalRun.xcstack_id + AND xccalRun.state = 'new' + AND xccalResult.fault != 0 + AND xcstackRun.tess_id = skycell.tess_id + AND xcstackRun.skycell_id = skycell.skycell_id Index: trunk/ippTools/share/xcskytool_todo.sql =================================================================== --- trunk/ippTools/share/xcskytool_todo.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_todo.sql (revision 42837) @@ -0,0 +1,30 @@ + -- we implicitly assume that all xcstackRuns used for a xcskyRun have the same tess_id and skycell_id +SELECT + xcskyRun.xcsky_id, + xcskyRun.workdir, + xcskyRun.reduction, + xcskyRun.label, + xcskyRun.state, + 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, + MIN(skycell.decdeg) as decdeg, + MIN(skycell.glong) as glong, + MIN(skycell.glat) as 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: trunk/ippTools/share/xcskytool_xccalresult.sql =================================================================== --- trunk/ippTools/share/xcskytool_xccalresult.sql (revision 42837) +++ trunk/ippTools/share/xcskytool_xccalresult.sql (revision 42837) @@ -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) Index: trunk/ippTools/share/xcstacktool_addbyquery_select.sql =================================================================== --- trunk/ippTools/share/xcstacktool_addbyquery_select.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_addbyquery_select.sql (revision 42837) @@ -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.xcstack_id = xcstackInputSkyfile.xcstack_id) + AND (camera_id = %d) + WHERE xcstackInputSkyfile.xcstack_id IS NULL Index: trunk/ippTools/share/xcstacktool_addcamera.sql =================================================================== --- trunk/ippTools/share/xcstacktool_addcamera.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_addcamera.sql (revision 42837) @@ -0,0 +1,3 @@ + +INSERT INTO xcstackCamera + (camera_id, camera) VALUES (0, '@CAMERA@') Index: trunk/ippTools/share/xcstacktool_definebyquery_select.sql =================================================================== --- trunk/ippTools/share/xcstacktool_definebyquery_select.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_definebyquery_select.sql (revision 42837) @@ -0,0 +1,13 @@ +-- 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, + stackRun.tess_id, + stackRun.skycell_id, + stackRun.filter + FROM %s.stackRun + JOIN %s.stackSumSkyfile USING (stack_id) + LEFT JOIN + Index: trunk/ippTools/share/xcstacktool_definebyquery_select_XC.sql =================================================================== --- trunk/ippTools/share/xcstacktool_definebyquery_select_XC.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_definebyquery_select_XC.sql (revision 42837) @@ -0,0 +1,6 @@ +SELECT + xcstack_id, + stack_id + FROM xcstackRun + JOIN xcstackInputSkyfile USING (xcstack_id) + Index: trunk/ippTools/share/xcstacktool_donecleanup.sql =================================================================== --- trunk/ippTools/share/xcstacktool_donecleanup.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_donecleanup.sql (revision 42837) @@ -0,0 +1,5 @@ +SELECT + xcstackRun.* +FROM xcstackRun +WHERE + xcstackRun.state = 'cleaned' Index: trunk/ippTools/share/xcstacktool_inputskyfile.sql =================================================================== --- trunk/ippTools/share/xcstacktool_inputskyfile.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_inputskyfile.sql (revision 42837) @@ -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: trunk/ippTools/share/xcstacktool_pendingcleanuprun.sql =================================================================== --- trunk/ippTools/share/xcstacktool_pendingcleanuprun.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_pendingcleanuprun.sql (revision 42837) @@ -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: trunk/ippTools/share/xcstacktool_pendingcleanupskyfile.sql =================================================================== --- trunk/ippTools/share/xcstacktool_pendingcleanupskyfile.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_pendingcleanupskyfile.sql (revision 42837) @@ -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: trunk/ippTools/share/xcstacktool_revertsumskyfile_delete.sql =================================================================== --- trunk/ippTools/share/xcstacktool_revertsumskyfile_delete.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_revertsumskyfile_delete.sql (revision 42837) @@ -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: trunk/ippTools/share/xcstacktool_sumskyfile.sql =================================================================== --- trunk/ippTools/share/xcstacktool_sumskyfile.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_sumskyfile.sql (revision 42837) @@ -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: trunk/ippTools/share/xcstacktool_tosum.sql =================================================================== --- trunk/ippTools/share/xcstacktool_tosum.sql (revision 42837) +++ trunk/ippTools/share/xcstacktool_tosum.sql (revision 42837) @@ -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)