Index: trunk/ippTools/share/Makefile.am =================================================================== --- trunk/ippTools/share/Makefile.am (revision 31375) +++ trunk/ippTools/share/Makefile.am (revision 31435) @@ -414,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: trunk/ippTools/share/laptool_definerun.sql =================================================================== --- trunk/ippTools/share/laptool_definerun.sql (revision 31435) +++ trunk/ippTools/share/laptool_definerun.sql (revision 31435) @@ -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: trunk/ippTools/share/laptool_exposures.sql =================================================================== --- trunk/ippTools/share/laptool_exposures.sql (revision 31435) +++ trunk/ippTools/share/laptool_exposures.sql (revision 31435) @@ -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: trunk/ippTools/share/laptool_inactiveexp.sql =================================================================== --- trunk/ippTools/share/laptool_inactiveexp.sql (revision 31435) +++ trunk/ippTools/share/laptool_inactiveexp.sql (revision 31435) @@ -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: trunk/ippTools/share/laptool_listsequence.sql =================================================================== --- trunk/ippTools/share/laptool_listsequence.sql (revision 31435) +++ trunk/ippTools/share/laptool_listsequence.sql (revision 31435) @@ -0,0 +1,1 @@ +select * from lapSequence Index: trunk/ippTools/share/laptool_pendingchipexp.sql =================================================================== --- trunk/ippTools/share/laptool_pendingchipexp.sql (revision 31435) +++ trunk/ippTools/share/laptool_pendingchipexp.sql (revision 31435) @@ -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: trunk/ippTools/share/laptool_pendingexp.sql =================================================================== --- trunk/ippTools/share/laptool_pendingexp.sql (revision 31435) +++ trunk/ippTools/share/laptool_pendingexp.sql (revision 31435) @@ -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: trunk/ippTools/share/laptool_pendingrun.sql =================================================================== --- trunk/ippTools/share/laptool_pendingrun.sql (revision 31435) +++ trunk/ippTools/share/laptool_pendingrun.sql (revision 31435) @@ -0,0 +1,1 @@ +SELECT * from lapRun Index: trunk/ippTools/share/laptool_stacks.sql =================================================================== --- trunk/ippTools/share/laptool_stacks.sql (revision 31435) +++ trunk/ippTools/share/laptool_stacks.sql (revision 31435) @@ -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: trunk/ippTools/share/pxadmin_create_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_create_tables.sql (revision 31375) +++ trunk/ippTools/share/pxadmin_create_tables.sql (revision 31435) @@ -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: trunk/ippTools/share/stacktool_sassskyfile.sql =================================================================== --- trunk/ippTools/share/stacktool_sassskyfile.sql (revision 31375) +++ trunk/ippTools/share/stacktool_sassskyfile.sql (revision 31435) @@ -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)