Index: branches/eam_branches/ipp-20110404/ippTools/share/Makefile.am =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/Makefile.am (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/Makefile.am (revision 31439) @@ -8,7 +8,16 @@ addtool_find_cam_id_dvo.sql \ addtool_find_cam_id.sql \ + addtool_find_sky_id_dvo.sql \ + addtool_find_sky_id.sql \ + addtool_find_stack_id_dvo.sql \ + addtool_find_stack_id.sql \ addtool_find_pendingexp.sql \ + addtool_find_pendingexp_cam.sql \ + addtool_find_pendingexp_stack.sql \ + addtool_find_pendingexp_staticsky.sql \ addtool_find_pendingmergeprocess.sql \ - addtool_find_processedexp.sql \ + addtool_find_processedexp_cam.sql \ + addtool_find_processedexp_stack.sql \ + addtool_find_processedexp_staticsky.sql \ addtool_find_minidvodbprocessed.sql \ addtool_find_minidvodbrun.sql \ @@ -16,7 +25,11 @@ addtool_pendingcleanuprun.sql \ addtool_queue_cam_id.sql \ + addtool_queue_stack_id.sql \ + addtool_queue_sky_id.sql \ addtool_queue_minidvodbrun.sql \ addtool_revertminidvodbprocessed.sql \ - addtool_revertprocessedexp.sql \ + addtool_revertprocessedexp_cam.sql \ + addtool_revertprocessedexp_stack.sql \ + addtool_revertprocessedexp_staticsky.sql \ bgtool_advancechip.sql \ bgtool_advancewarp.sql \ @@ -164,4 +177,5 @@ disttool_definebyquery_fake.sql \ disttool_definebyquery_raw.sql \ + disttool_definebyquery_raw_no_magic.sql \ disttool_definebyquery_sky.sql \ disttool_definebyquery_stack.sql \ @@ -400,3 +414,12 @@ diffphottool_advance.sql \ diffphottool_revert.sql \ - diffphottool_data.sql + diffphottool_data.sql \ + laptool_definerun.sql \ + laptool_exposures.sql \ + laptool_inactiveexp.sql \ + laptool_listsequence.sql \ + laptool_pendingchipexp.sql \ + laptool_pendingexp.sql \ + laptool_pendingrun.sql \ + laptool_stacks.sql + Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_cam_id.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_cam_id.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_cam_id.sql (revision 31439) @@ -11,8 +11,9 @@ addRun.dvodb AS previous_dvodb FROM addRun - JOIN camRun USING(cam_id) + JOIN camRun on cam_id=stage_id JOIN chipRun USING(chip_id) ) as foo ON exp_id = added_exp_id + AND stage = 'cam' -- hook for qualifying the join on the previous_dvodb AND %s Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_cam_id_dvo.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_cam_id_dvo.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_cam_id_dvo.sql (revision 31439) @@ -6,6 +6,6 @@ AND exp_id NOT IN (SELECT exp_id FROM addRun - JOIN camRun USING(cam_id) + JOIN camRun on cam_id=stage_id JOIN chipRun USING(chip_id) - WHERE %s + WHERE stage = 'cam' AND %s ) Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp.sql (revision 31439) @@ -1,20 +1,5 @@ SELECT - addRun.*, - camProcessedExp.path_base as camroot, - rawExp.exp_tag, - rawExp.exp_id, - rawExp.exp_name, - rawExp.camera, - rawExp.telescope, - rawExp.filelevel + addRun.* FROM addRun -JOIN camRun - USING(cam_id) -JOIN camProcessedExp - USING(cam_id) -JOIN chipRun - USING(chip_id) -JOIN rawExp - USING(exp_id) LEFT JOIN addProcessedExp USING(add_id) @@ -22,6 +7,5 @@ ON addRun.label = addMask.label WHERE - camRun.state = 'full' - AND ((addRun.state = 'new' AND addProcessedExp.add_id IS NULL) OR addRun.state = 'update') +((addRun.state = 'new' AND addProcessedExp.add_id IS NULL) OR addRun.state = 'update') AND addRun.dvodb IS NOT NULL AND addRun.workdir IS NOT NULL Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_cam.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_cam.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_cam.sql (revision 31439) @@ -0,0 +1,30 @@ +SELECT + addRun.*, + camProcessedExp.path_base as stageroot, + rawExp.exp_tag, + rawExp.exp_id, + rawExp.exp_name, + rawExp.camera, + rawExp.telescope, + rawExp.filelevel +FROM addRun +JOIN camRun + on cam_id = stage_id +JOIN camProcessedExp + USING(cam_id) +JOIN chipRun + USING(chip_id) +JOIN rawExp + USING(exp_id) +LEFT JOIN addProcessedExp + USING(add_id) +LEFT JOIN addMask + ON addRun.label = addMask.label +WHERE + camRun.state = 'full' + AND addRun.stage = 'cam' + AND ((addRun.state = 'new' AND addProcessedExp.add_id IS NULL) OR addRun.state = 'update') + AND addRun.dvodb IS NOT NULL + AND addRun.workdir IS NOT NULL + AND addMask.label IS NULL + Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_stack.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_stack.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_stack.sql (revision 31439) @@ -0,0 +1,28 @@ +SELECT + addRun.*, + stackSumSkyfile.path_base as stageroot, + rawExp.camera, + rawExp.telescope +FROM addRun +JOIN stackRun + ON stack_id = stage_id +JOIN stackSumSkyfile + USING(stack_id) +JOIN stackInputSkyfile + USING(stack_id) +JOIN warpRun using(warp_id) +JOIN fakeRun using(fake_id) +JOIN camRun using(cam_id) +JOIN chipRun using(chip_id) +JOIN rawExp using (exp_id) +LEFT JOIN addProcessedExp using (add_id) +LEFT JOIN addMask + ON addRun.label = addMask.label +WHERE + stackRun.state = 'full' + AND stage = 'stack' + AND ((addRun.state = 'new' AND addProcessedExp.add_id IS NULL) OR addRun.state = 'update') + AND addRun.dvodb IS NOT NULL + AND addRun.workdir IS NOT NULL + AND addMask.label IS NULL + Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_staticsky.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_staticsky.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_pendingexp_staticsky.sql (revision 31439) @@ -0,0 +1,32 @@ +SELECT + addRun.*, + staticskyResult.path_base as stageroot, + rawExp.camera, + rawExp.telescope +FROM addRun +JOIN staticskyRun + ON sky_id = stage_id +JOIN staticskyResult + USING (sky_id) +JOIN staticskyInput + USING (sky_id) +JOIN stackRun + USING (stack_id) +JOIN stackInputSkyfile + USING(stack_id) +JOIN warpRun using(warp_id) +JOIN fakeRun using(fake_id) +JOIN camRun using(cam_id) +JOIN chipRun using(chip_id) +JOIN rawExp using (exp_id) +LEFT JOIN addProcessedExp using (add_id) +LEFT JOIN addMask + ON addRun.label = addMask.label +WHERE + staticskyRun.state = 'full' + AND stage = 'staticsky' + AND ((addRun.state = 'new' AND addProcessedExp.add_id IS NULL) OR addRun.state = 'update') + AND addRun.dvodb IS NOT NULL + AND addRun.workdir IS NOT NULL + AND addMask.label IS NULL + Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_cam.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_cam.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_cam.sql (revision 31439) @@ -0,0 +1,12 @@ +SELECT + addProcessedExp.*, + addRun.workdir +FROM addProcessedExp +JOIN addRun + USING(add_id) +JOIN camRun + on cam_id = stage_id +JOIN chipRun + USING(chip_id) +JOIN rawExp + ON chipRun.exp_id = rawExp.exp_id Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_stack.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_stack.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_stack.sql (revision 31439) @@ -0,0 +1,6 @@ +SELECT + addProcessedExp.*, + addRun.workdir +FROM addProcessedExp +JOIN addRun + USING(add_id) Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_staticsky.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_staticsky.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_processedexp_staticsky.sql (revision 31439) @@ -0,0 +1,6 @@ +SELECT + addProcessedExp.*, + addRun.workdir +FROM addProcessedExp +JOIN addRun + USING(add_id) Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_sky_id.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_sky_id.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_sky_id.sql (revision 31439) @@ -0,0 +1,22 @@ +SELECT + staticskyRun.* +FROM + staticskyResult +join staticSkyRun using (sky_id) +join staticSkyInput using (stack_id) +JOIN stackRun USING(stack_id) + +LEFT JOIN (SELECT sky_id AS added_sky_id, + addRun.dvodb AS previous_dvodb + FROM addRun +JOIN staticskyRun on sky_id = stage_id + ) as foo + ON sky_id = added_sky_id + AND stage = 'sky' + -- hook for qualifying the join on the previous_dvodb + AND %s +WHERE + staticskyRun.state = 'full' + AND staticskyResult.quality = 0 + AND added_exp_id IS NULL + -- addtool adds checks on exposure being added to the dvodb previously Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_sky_id_dvo.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_sky_id_dvo.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_sky_id_dvo.sql (revision 31439) @@ -0,0 +1,9 @@ +SELECT staticskyRun.* FROM staticskyResult +JOIN staticskyRun USING(sky_id) +WHERE staticskyRun.state = 'full' and staticskyResult.quality = 0 + AND sky_id NOT IN (SELECT sky_id + FROM addRun + JOIN staticskyResult on staticskyResult.sky_id = addRun.stage_id + JOIN staticskyRun USING(sky_id) + WHERE addRun.stage = 'staticsky' AND %s + ) Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_stack_id.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_stack_id.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_stack_id.sql (revision 31439) @@ -0,0 +1,19 @@ +SELECT + stackRun.* +FROM stackSumSkyfile +JOIN stackRun USING(stack_id) + +LEFT JOIN (SELECT stack_id AS added_stack_id, + addRun.dvodb AS previous_dvodb + FROM addRun +JOIN stackRun on stack_id = stage_id + ) as foo + ON stack_id = added_stack_id + AND stage = 'stack' + -- hook for qualifying the join on the previous_dvodb + AND %s +WHERE + stackRun.state = 'full' + AND stackSumSkyfile.quality = 0 + AND added_exp_id IS NULL + -- addtool adds checks on exposure being added to the dvodb previously Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_stack_id_dvo.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_stack_id_dvo.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_find_stack_id_dvo.sql (revision 31439) @@ -0,0 +1,9 @@ +SELECT stackRun.* FROM stackSumSkyfile +JOIN stackRun USING(stack_id) +WHERE stackRun.state = 'full' and stackSumSkyfile.quality = 0 + AND stack_id NOT IN (SELECT stack_id + FROM addRun + JOIN stackInputSkyfile on stackInputSkyfile.stack_id = addRun.stage_id + JOIN stackRun USING(stack_id) + WHERE addRun.stage = 'stack' AND %s + ) Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_cam_id.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_cam_id.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_cam_id.sql (revision 31439) @@ -2,5 +2,6 @@ SELECT 0, -- add_id - cam_id, -- cam_id + 'cam', -- stage + cam_id, -- stage_id '%s', -- state '%s', -- workdir Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_sky_id.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_sky_id.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_sky_id.sql (revision 31439) @@ -0,0 +1,21 @@ +INSERT INTO addRun + SELECT + 0, -- add_id + 'staticsky', -- stage + sky_id, -- stage_id + '%s', -- state + '%s', -- workdir + '%s', -- workdir_state + '%s', -- reduction + '%s', -- label + '%s', -- data_group + '%s', -- dvodb + '%s', -- note + %d, -- image_only + %d, -- minidvodb + '%s', -- minidvodb_group + '%s' -- minidvodb_name + FROM staticskyRun + WHERE + staticskyRun.state = 'full' + AND staticskyRun.sky_id = %lld Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_stack_id.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_stack_id.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_queue_stack_id.sql (revision 31439) @@ -0,0 +1,21 @@ +INSERT INTO addRun + SELECT + 0, -- add_id + 'stack', -- stage + stack_id, -- stage_id + '%s', -- state + '%s', -- workdir + '%s', -- workdir_state + '%s', -- reduction + '%s', -- label + '%s', -- data_group + '%s', -- dvodb + '%s', -- note + %d, -- image_only + %d, -- minidvodb + '%s', -- minidvodb_group + '%s' -- minidvodb_name + FROM stackRun + WHERE + stackRun.state = 'full' + AND stackRun.stack_id = %lld Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_cam.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_cam.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_cam.sql (revision 31439) @@ -0,0 +1,10 @@ +DELETE FROM addProcessedExp +USING addProcessedExp, addRun, camRun, chipRun, rawExp +WHERE + addRun.add_id = addProcessedExp.add_id + AND addRun.stage_id = camRun.cam_id + AND camRun.chip_id = chipRun.chip_id + AND chipRun.exp_id = rawExp.exp_id + AND addProcessedExp.fault != 0 + AND addRun.state = 'new' + AND addRun.stage = 'cam' Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_stack.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_stack.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_stack.sql (revision 31439) @@ -0,0 +1,7 @@ +DELETE FROM addProcessedExp +USING addProcessedExp, addRun +WHERE + addRun.add_id = addProcessedExp.add_id + AND addProcessedExp.fault != 0 + AND addRun.state = 'new' + AND addRun.stage = 'stack' Index: branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_staticsky.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_staticsky.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/addtool_revertprocessedexp_staticsky.sql (revision 31439) @@ -0,0 +1,7 @@ +DELETE FROM addProcessedExp +USING addProcessedExp, addRun +WHERE + addRun.add_id = addProcessedExp.add_id + AND addProcessedExp.fault != 0 + AND addRun.state = 'new' + AND addRun.stage = 'staticsky' Index: branches/eam_branches/ipp-20110404/ippTools/share/difftool_listrun.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/difftool_listrun.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/difftool_listrun.sql (revision 31439) @@ -20,4 +20,5 @@ camProcessedInput.zpt_lq, camProcessedInput.zpt_uq, + camProcessedInput.fwhm_major, rawInput.comment, rawInput.exp_time, @@ -25,4 +26,5 @@ rawInput.exp_name AS exp_name_1, rawInput.exp_id AS exp_id_1, + rawInput.filter AS filter_1, rawInput.comment AS comment_1, rawInput.dateobs AS dateobs_1, @@ -34,4 +36,5 @@ rawTemplate.exp_name AS exp_name_2, rawTemplate.exp_id AS exp_id_2, + rawInput.filter AS filter_2, rawTemplate.comment AS comment_2, rawTemplate.dateobs AS dateobs_2, Index: branches/eam_branches/ipp-20110404/ippTools/share/difftool_skyfile.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/difftool_skyfile.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/difftool_skyfile.sql (revision 31439) @@ -24,4 +24,5 @@ rawInput.exp_name AS exp_name_1, rawInput.exp_id AS exp_id_1, + rawInput.filter AS filter_1, chipInput.chip_id AS chip_id_1, camInput.cam_id AS cam_id_1, @@ -31,4 +32,5 @@ rawTemplate.exp_name AS exp_name_2, rawTemplate.exp_id AS exp_id_2, + rawTemplate.filter AS filter_2, chipTemplate.chip_id AS chip_id_2, camTemplate.cam_id AS cam_id_2, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_definebyquery_raw_no_magic.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_definebyquery_raw_no_magic.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_definebyquery_raw_no_magic.sql (revision 31439) @@ -0,0 +1,18 @@ +SELECT + 'raw' AS stage, + rawExp.exp_id AS stage_id, + rawExp.exp_name AS run_tag, + rawExp.magicked, + CAST(NULL AS CHAR(255)) AS label, + CAST(NULL AS CHAR(255)) AS data_group, + distTarget.dist_group, + distTarget.target_id, + distTarget.clean +FROM rawExp +JOIN distTarget ON distTarget.stage = 'raw' + AND rawExp.filter = distTarget.filter +JOIN rcInterest USING(target_id) +LEFT JOIN distRun ON distRun.stage_id = exp_id AND distRun.target_id = distTarget.target_id +WHERE distRun.dist_id IS NULL -- no existing distRun for this exposure + AND distTarget.state = 'enabled' -- target and intrest are enabled + AND rcInterest.state = 'enabled' Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_SSdiff.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_SSdiff.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_SSdiff.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, diffSkyfile.skycell_id AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_camera.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_camera.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_camera.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, 'exposure' AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_chip.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_chip.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_chip.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, chipProcessedImfile.class_id AS component, + exp_type, distRun.clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_chip_bg.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_chip_bg.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_chip_bg.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, chipBackgroundImfile.class_id AS component, + exp_type, distRun.clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_diff.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_diff.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_diff.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, diffSkyfile.skycell_id AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_fake.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_fake.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_fake.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, fakeProcessedImfile.class_id AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_raw.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_raw.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_raw.sql (revision 31439) @@ -1,2 +1,4 @@ +SELECT * FROM ( +-- rawExp magicked with re_place SELECT distRun.dist_id, @@ -6,4 +8,5 @@ rawExp.exp_id AS stage_id, rawImfile.class_id AS component, + rawExp.exp_type, clean, rawExp.camera, @@ -38,8 +41,49 @@ AND distRun.stage = 'raw' AND distComponent.dist_id IS NULL - AND (rawExp.magicked OR distRun.no_magic) + -- AND (rawExp.magicked OR distRun.no_magic) + AND rawExp.magicked -- need to have magicked the chip image which makes the camera mask AND chipProcessedImfile.magicked != 0 AND camRun.magicked > 0 + AND (Label.active OR Label.active IS NULL) +UNION +SELECT + -- raw images no_magic required + distRun.dist_id, + distRun.label, + distTarget.dist_group, + 'raw' AS stage, + rawExp.exp_id AS stage_id, + rawImfile.class_id AS component, + rawExp.exp_type, + clean, + rawExp.camera, + CONCAT_WS('.', distRun.outroot, CONVERT(distRun.dist_id, CHAR)) as outdir, + -- XXX: replace this with rawImfile.path_base once it exists + TRIM(TRAILING '.fits' FROM rawImfile.uri) AS path_base, + CAST(NULL AS CHAR(255)) AS alt_path_base, + -- pass camera stage path base since we want the camera mask file. The script knows what to do + CAST(NULL AS CHAR(255)) AS chip_path_base, + CAST(NULL AS CHAR(255)) AS state, + CAST(NULL AS CHAR(255)) AS data_state, + 0 as quality, + distRun.no_magic, + rawImfile.magicked, + IFNULL(Label.priority, 10000) AS priority +FROM distRun +JOIN distTarget USING(target_id, stage, clean) +JOIN rawExp ON rawExp.exp_id = stage_id + AND distTarget.stage = 'raw' AND distRun.alternate = 0 +JOIN rawImfile USING(exp_id) +LEFT JOIN distComponent + ON distRun.dist_id = distComponent.dist_id + AND rawImfile.class_id = distComponent.component +LEFT JOIN Label ON distRun.label = Label.label +WHERE + distRun.state = 'new' + AND distRun.clean = 0 + AND distRun.stage = 'raw' + AND distComponent.dist_id IS NULL + AND (distRun.no_magic) AND (Label.active OR Label.active IS NULL) UNION @@ -52,4 +96,5 @@ rawExp.exp_id AS stage_id, rawImfile.class_id AS component, + rawExp.exp_type, clean, rawExp.camera, @@ -96,4 +141,5 @@ rawExp.exp_id AS stage_id, 'exposure' AS component, + rawExp.exp_type, clean, rawExp.camera, @@ -120,2 +166,4 @@ AND distComponent.dist_id IS NULL AND (Label.active OR Label.active IS NULL) +) AS distRun +WHERE 1 Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_sky.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_sky.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_sky.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, stackRun.skycell_id AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_stack.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_stack.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_stack.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, stackRun.skycell_id AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_warp.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_warp.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_warp.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, warpSkyfile.skycell_id AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_warp_bg.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_warp_bg.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/disttool_pending_warp_bg.sql (revision 31439) @@ -6,4 +6,5 @@ stage_id, warpBackgroundSkyfile.skycell_id AS component, + exp_type, clean, rawExp.camera, Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_definerun.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_definerun.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_definerun.sql (revision 31439) @@ -0,0 +1,16 @@ +SELECT want.exp_id, have.chip_id, false as private, true as active, false as pairwise + FROM + (SELECT exp_id FROM rawExp + WHERE rawExp.exp_type= 'OBJECT' AND + rawExp.dateobs >= '2009-04-01T00:00:00.000000' AND +-- Position restriction goes here. + @WHERE@ + ) AS want + LEFT JOIN + (SELECT * + FROM lapExp + where private IS FALSE + AND chip_id IS NOT NULL + AND active = TRUE ) AS have USING(exp_id) + + Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_exposures.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_exposures.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_exposures.sql (revision 31439) @@ -0,0 +1,43 @@ +SELECT DISTINCT + D.*,diffRun.state, + coalesce(CONVERT(sum(others.private),SIGNED),0) AS needs_remade +-- 0 AS needs_remade + FROM ( + SELECT DISTINCT + W.*,CONVERT(IFNULL(diff1.diff_id,diff2.diff_id),SIGNED) AS diff_id FROM ( + SELECT DISTINCT + lap_id,lapRun.tess_id,projection_cell,filter,lapRun.state as lapRun_state, lapRun.registered, lapRun.fault, lapRun.label, lapRun.dist_group, + lapExp.exp_id,lapExp.chip_id,lapExp.pair_id,private,pairwise,active,lapExp.data_state, + chipRun.state as chipRun_state, + coalesce(CONVERT(sum(chipProcessedImfile.fault),SIGNED),0) as chip_faults, + coalesce(CONVERT(sum(chipProcessedImfile.quality),SIGNED),0) as chip_quality, + camRun.cam_id, camRun.state as camRun_state, + coalesce(CONVERT(sum(camProcessedExp.fault),SIGNED),0) AS cam_faults, + coalesce(CONVERT(sum(camProcessedExp.quality),SIGNED),0) AS cam_quality, + fakeRun.fake_id, fakeRun.state as fakeRun_state, + coalesce(CONVERT(sum(fakeProcessedImfile.fault),SIGNED),0) as fake_faults, + warpRun.warp_id, warpRun.state as warpRun_state, + coalesce(CONVERT(sum(warpSkyfile.fault),SIGNED),0) as warp_faults, + coalesce(CONVERT(sum(warpSkyfile.quality),SIGNED),0) as warp_quality, + warpRun.magicked + FROM lapRun JOIN lapExp USING(lap_id) + LEFT JOIN chipRun USING(chip_id) + LEFT JOIN chipProcessedImfile USING(chip_id) + LEFT JOIN camRun USING(chip_id) LEFT JOIN camProcessedExp USING(cam_id) + LEFT JOIN fakeRun USING(cam_id) LEFT JOIN fakeProcessedImfile USING(fake_id) + LEFT JOIN warpRun USING(fake_id) LEFT JOIN warpSkyfile USING(warp_id) + WHERE @WHERE@ + AND (warpSkyfile.quality IS NULL OR + (warpSkyfile.quality != 8007 -- known cases where quality != 0, but everything's fine. + AND warpSkyfile.quality != 3006 -- known cases where quality != 0, but everything's fine. + )) + GROUP BY lap_id,exp_id + ) AS W +-- This was unreasonably slow in testing, so that's why I'm using a subquery here. + LEFT JOIN diffInputSkyfile AS diff1 ON (W.warp_id = diff1.warp1) + LEFT JOIN diffInputSkyfile AS diff2 ON (W.warp_id = diff2.warp2) +) AS D +LEFT JOIN diffRun USING(diff_id) +LEFT JOIN lapExp AS others ON (D.chip_id = others.chip_id AND D.lap_id != others.lap_id) +GROUP BY lap_id,exp_id + Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_inactiveexp.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_inactiveexp.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_inactiveexp.sql (revision 31439) @@ -0,0 +1,41 @@ +SELECT DISTINCT + D.*,diffRun.state AS diff_state,coalesce(CONVERT(sum(others.active),SIGNED),0) AS is_in_use FROM ( + SELECT DISTINCT + W.*,IFNULL(diff1.diff_id,diff2.diff_id) AS diff_id FROM ( + SELECT DISTINCT + lapRun.lap_id, lapRun.seq_id, lapRun.tess_id, lapRun.projection_cell, lapRun.filter, lapRun.state, lapRun.label, + lapRun.dist_group, lapRun.registered, lapRun.fault, lapRun.quick_sass_id, lapRun.final_sass_id, + lapExp.exp_id,lapExp.chip_id,lapExp.pair_id,private,pairwise,active,lapExp.data_state, + chipRun.state as chipRun_state, + CONVERT(sum(chipProcessedImfile.fault),SIGNED) as chip_faults, + CONVERT(sum(chipProcessedImfile.quality),SIGNED) as chip_quality, + camRun.cam_id, camRun.state as camRun_state, + CONVERT(sum(camProcessedExp.fault),SIGNED) AS cam_faults, + CONVERT(sum(camProcessedExp.quality),SIGNED) AS cam_quality, + fakeRun.fake_id, fakeRun.state as fakeRun_state, + CONVERT(sum(fakeProcessedImfile.fault),SIGNED) as fake_faults, + warpRun.warp_id, warpRun.state as warpRun_state, + CONVERT(sum(warpSkyfile.fault),SIGNED) as warp_faults, + CONVERT(sum(warpSkyfile.quality),SIGNED) as warp_quality, + warpRun.magicked + FROM lapRun JOIN lapExp USING(lap_id) + LEFT JOIN chipRun USING(chip_id) + LEFT JOIN chipProcessedImfile USING(chip_id) + LEFT JOIN camRun USING(chip_id) LEFT JOIN camProcessedExp USING(cam_id) + LEFT JOIN fakeRun USING(cam_id) LEFT JOIN fakeProcessedImfile USING(fake_id) + LEFT JOIN warpRun USING(fake_id) LEFT JOIN warpSkyfile USING(warp_id) + WHERE lapExp.active = FALSE + AND @WHERE@ + AND (warpSkyfile.quality IS NULL OR + (warpSkyfile.quality != 8007 -- known cases where quality != 0, but everything's fine. + AND warpSkyfile.quality != 3006 -- known cases where quality != 0, but everything's fine. + )) + GROUP BY lap_id,exp_id + ) AS W +-- This was unreasonably slow in testing, so that's why I'm using a subquery here. + LEFT JOIN diffInputSkyfile AS diff1 ON (W.warp_id = diff1.warp1) + LEFT JOIN diffInputSkyfile AS diff2 ON (W.warp_id = diff2.warp2) +) AS D +LEFT JOIN diffRun USING(diff_id) +LEFT JOIN lapExp AS others ON (D.chip_id = others.chip_id AND D.lap_id != others.lap_id) +GROUP BY lap_id,exp_id Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_listsequence.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_listsequence.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_listsequence.sql (revision 31439) @@ -0,0 +1,1 @@ +select * from lapSequence Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingchipexp.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingchipexp.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingchipexp.sql (revision 31439) @@ -0,0 +1,11 @@ +SELECT re_id,projection_cell,registered,reprocRun.state as reproc_state, tess_id, + exp_id,private,active, + chip_id,chipRun.state AS chip_state, + cam_id,camRun.state AS cam_state, + fake_id,fakeRun.state AS fake_state, + warp_id,warpRun.state AS warp_state + FROM reprocRun JOIN reprocExp USING(re_id) + LEFT JOIN chipRun USING(chip_id,tess_id) + LEFT JOIN camRun USING(chip_id,tess_id) + LEFT JOIN fakeRun USING(fake_id,tess_id) + LEFT JOIN warpRun USING(fake_id,tess_id) Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingexp.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingexp.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingexp.sql (revision 31439) @@ -0,0 +1,10 @@ +select lapRun.lap_id, lapRun.seq_id, lapRun.tess_id, lapRun.projection_cell, lapRun.filter, + lapRun.state, lapRun.label, + lapRun.dist_group, lapRun.registered, lapRun.fault, lapRun.quick_sass_id, lapRun.final_sass_id, + exp_id, chip_id, pair_id, private, pairwise, active, lapExp.data_state, + dateobs, object, comment + FROM lapRun JOIN lapExp USING(lap_id) + JOIN rawExp USING(exp_id,filter) +WHERE active IS TRUE AND lapRun.fault = 0 +-- lap_id restriction here. +-- This probably needs to be sorted by dateobs. Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingrun.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingrun.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_pendingrun.sql (revision 31439) @@ -0,0 +1,1 @@ +SELECT * from lapRun Index: branches/eam_branches/ipp-20110404/ippTools/share/laptool_stacks.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/laptool_stacks.sql (revision 31439) +++ branches/eam_branches/ipp-20110404/ippTools/share/laptool_stacks.sql (revision 31439) @@ -0,0 +1,103 @@ +SELECT + lapRun.lap_id,lapRun.seq_id,lapRun.tess_id,lapRun.projection_cell,lapRun.filter, + lapRun.state,lapRun.label,lapRun.dist_group,lapRun.registered,lapRun.fault, + skycell_id, + lapRun.quick_sass_id,quick_data_group,quick_stack_id,quick_state,quick_fault,quick_quality, + lapRun.final_sass_id,final_data_group,final_stack_id,final_state,final_fault,final_quality + FROM + lapRun LEFT JOIN + ( +SELECT DISTINCT + lap_id,tess_id,projection_cell,filter,skycell_id, + quick_sass_id,quick_data_group,quick_stack_id,quick_state,quick_fault,quick_quality, + final_sass_id,final_data_group,final_stack_id,final_state,final_fault,final_quality + FROM + (select lapRun.lap_id, + stackAssociation.sass_id AS quick_sass_id, + stackAssociation.data_group AS quick_data_group, + stackAssociation.projection_cell, + stackAssociation.tess_id, + stackAssociation.filter, + stackRun.stack_id AS quick_stack_id, + stackRun.skycell_id, + stackRun.state AS quick_state, + stackSumSkyfile.fault AS quick_fault, + stackSumSkyfile.quality AS quick_quality FROM + lapRun + LEFT JOIN stackAssociation ON (lapRun.quick_sass_id = stackAssociation.sass_id) + LEFT JOIN stackAssociationMap USING (sass_id) + LEFT JOIN stackRun USING(stack_id) + LEFT JOIN stackSumSkyfile USING(stack_id) + WHERE 1 @WHERE@ + AND sass_id = lapRun.quick_sass_id + ) AS quick LEFT JOIN + (select lapRun.lap_id, + stackAssociation.sass_id AS final_sass_id, + stackAssociation.data_group AS final_data_group, + stackAssociation.projection_cell, + stackAssociation.tess_id, + stackAssociation.filter, + stackRun.stack_id AS final_stack_id, + stackRun.skycell_id, + stackRun.state AS final_state, + stackSumSkyfile.fault AS final_fault, + stackSumSkyfile.quality AS final_quality FROM + lapRun + LEFT JOIN stackAssociation ON (lapRun.final_sass_id = stackAssociation.sass_id) + LEFT JOIN stackAssociationMap USING (sass_id) + LEFT JOIN stackRun USING(stack_id) + LEFT JOIN stackSumSkyfile USING(stack_id) + WHERE 1 @WHERE@ + AND sass_id = lapRun.final_sass_id + ) AS final USING(lap_id,projection_cell,tess_id,filter,skycell_id) + UNION +SELECT DISTINCT + lap_id,tess_id,projection_cell,filter,skycell_id, + quick_sass_id,quick_data_group,quick_stack_id,quick_state,quick_fault,quick_quality, + final_sass_id,final_data_group,final_stack_id,final_state,final_fault,final_quality + FROM + (select lapRun.lap_id, + stackAssociation.sass_id AS quick_sass_id, + stackAssociation.data_group AS quick_data_group, + stackAssociation.projection_cell, + stackAssociation.tess_id, + stackAssociation.filter, + stackRun.stack_id AS quick_stack_id, + stackRun.skycell_id, + stackRun.state AS quick_state, + stackSumSkyfile.fault AS quick_fault, + stackSumSkyfile.quality AS quick_quality FROM + lapRun + LEFT JOIN stackAssociation ON (lapRun.quick_sass_id = stackAssociation.sass_id) + LEFT JOIN stackAssociationMap USING (sass_id) + LEFT JOIN stackRun USING(stack_id) + LEFT JOIN stackSumSkyfile USING(stack_id) + WHERE 1 @WHERE@ + AND sass_id = lapRun.quick_sass_id + ) AS quick RIGHT JOIN + (select lapRun.lap_id, + stackAssociation.sass_id AS final_sass_id, + stackAssociation.data_group AS final_data_group, + stackAssociation.projection_cell, + stackAssociation.tess_id, + stackAssociation.filter, + stackRun.stack_id AS final_stack_id, + stackRun.skycell_id, + stackRun.state AS final_state, + stackSumSkyfile.fault AS final_fault, + stackSumSkyfile.quality AS final_quality FROM + lapRun + LEFT JOIN stackAssociation ON (lapRun.final_sass_id = stackAssociation.sass_id) + LEFT JOIN stackAssociationMap USING (sass_id) + LEFT JOIN stackRun USING(stack_id) + LEFT JOIN stackSumSkyfile USING(stack_id) + WHERE 1 @WHERE@ + AND sass_id = lapRun.final_sass_id + ) AS final USING(lap_id,projection_cell,tess_id,filter,skycell_id) + ) stacks ON (stacks.lap_id = lapRun.lap_id AND + stacks.projection_cell = lapRun.projection_cell AND + stacks.tess_id = lapRun.tess_id AND + stacks.filter = lapRun.filter AND + (lapRun.quick_sass_id IS NULL OR lapRun.quick_sass_id = stacks.quick_sass_id) AND + (lapRun.final_sass_id IS NULL OR lapRun.final_sass_id = stacks.final_sass_id)) +WHERE 1 @WHERE@ Index: branches/eam_branches/ipp-20110404/ippTools/share/pxadmin_create_tables.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/pxadmin_create_tables.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/pxadmin_create_tables.sql (revision 31439) @@ -1891,4 +1891,59 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; +-- Tables for large area processing + +CREATE TABLE lapSequence ( + seq_id BIGINT AUTO_INCREMENT, -- Identifier for the processing sequence + name VARCHAR(64) NOT NULL, -- short name of the sequence + description VARCHAR(255) NOT NULL, -- longer description of the sequence + PRIMARY KEY(seq_id), + KEY(name) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE lapRun ( + lap_id BIGINT AUTO_INCREMENT, -- Identifier for the processing run + seq_id BIGINT NOT NULL, -- Identifier to match to the sequence + tess_id VARCHAR(64) NOT NULL, -- tessellation id to use + projection_cell VARCHAR(64) NOT NULL, -- projection cell from the tessellation to consider + filter VARCHAR(64) NOT NULL, -- filter + state VARCHAR(64) NOT NULL, -- state of run + label VARCHAR(64) NOT NULL, -- processing label + dist_group VARCHAR(64) NOT NULL, -- distribution group for products of this run + registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered + fault SMALLINT NOT NULL, -- fault code + quick_sass_id BIGINT, -- stackAssociation id for quick stack + final_sass_id BIGINT, -- stackAssociation id for final stack + PRIMARY KEY(lap_id), + KEY(seq_id), + KEY(projection_cell), + KEY(filter), + KEY(state), + KEY(label), + KEY(fault), + FOREIGN KEY(seq_id) REFERENCES lapSequence(seq_id), + FOREIGN KEY(quick_sass_id) REFERENCES stackAssociation(sass_id), + FOREIGN KEY(final_sass_id) REFERENCES stackAssociation(sass_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE lapExp ( + lap_id BIGINT NOT NULL, -- Link back to processing run + exp_id BIGINT NOT NULL, -- exposure definition + chip_id BIGINT, -- processing id from chipRun + pair_id BIGINT, -- companion chip_id + private TINYINT DEFAULT 0, -- denotes this exposure is private + pairwise TINYINT DEFAULT 0, -- denotes if this exposure should be pairwise diffed + active TINYINT DEFAULT 0, -- denotes if this exposure is currently in use + data_state VARCHAR(64) NOT NULL, -- state of exposure + PRIMARY KEY (lap_id), + KEY (exp_id), + KEY (chip_id), + KEY (pair_id), + KEY (data_state), + FOREIGN KEY (lap_id) REFERENCES lapRun(lap_id), + FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id), + FOREIGN KEY (chip_id,exp_id) REFERENCES chipRun(chip_id,exp_id), + FOREIGN KEY (pair_id) REFERENCES chipRun(chip_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + -- This comment line is here to avoid 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-20110404/ippTools/share/stacktool_sassskyfile.sql =================================================================== --- branches/eam_branches/ipp-20110404/ippTools/share/stacktool_sassskyfile.sql (revision 31166) +++ branches/eam_branches/ipp-20110404/ippTools/share/stacktool_sassskyfile.sql (revision 31439) @@ -18,6 +18,6 @@ where stack_id = stackRun.stack_id limit 1 ) as camera -FROM stackRun -JOIN stackSumSkyfile USING(stack_id) -JOIN stackAssociationMap USING(stack_id) -JOIN stackAssociation USING(sass_id) +FROM stackAssociation +JOIN stackAssociationMap USING(sass_id) +JOIN stackRun USING(stack_id) +LEFT JOIN stackSumSkyfile USING(stack_id)