Index: branches/eam_branches/ipp-20121219/ippTools/share/Makefile.am =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/Makefile.am (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/Makefile.am (revision 35096) @@ -461,4 +461,8 @@ vptool_processedcell.sql \ vptool_revertrun.sql \ - sctool_list.sql + sctool_list.sql \ + releasetool_listsurvey.sql \ + releasetool_listrelease.sql \ + releasetool_listrelexp.sql \ + releasetool_definerelexp.sql Index: branches/eam_branches/ipp-20121219/ippTools/share/bgtool_definechip.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/bgtool_definechip.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/bgtool_definechip.sql (revision 35096) @@ -3,4 +3,8 @@ FROM chipRun JOIN rawExp USING(exp_id) +JOIN camRun USING(chip_id) +JOIN camProcessedExp USING(cam_id) LEFT JOIN chipBackgroundRun USING(chip_id) WHERE chipRun.state = 'full' +AND camRun.state = 'full' +AND camProcessedExp.quality = 0 Index: branches/eam_branches/ipp-20121219/ippTools/share/pxadmin_create_tables.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/pxadmin_create_tables.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/pxadmin_create_tables.sql (revision 35096) @@ -2148,5 +2148,74 @@ ) ENGINE=InnoDB DEFAULT CHARSET=latin1; - --- This comment line is here to avoid empty query error. +CREATE TABLE survey ( + surveyID INT(8), + surveyName VARCHAR(16) NOT NULL, + description VARCHAR(255), + PRIMARY KEY(surveyID), + UNIQUE KEY(name) +) ENGINE=InnoDB CHARSET=latin1; + + +CREATE TABLE ippRelease ( + rel_id INT(8) AUTO_INCREMENT, + surveyID INT(8), + releaseName VARCHAR(64), + relase_state VARCHAR(16), -- active, pending, archive, drop + dataRelease INT(8), -- PSPS dataRelease + priority INT(8), + PRIMARY KEY(rel_id), + KEY(releaseName), + KEY(state), + KEY(priority), + FOREIGN KEY(surveyID) REFERENCES survey(surveyID) +) ENGINE=Innodb DEFAULT CHARSET=latin1; + +CREATE TABLE relExp ( + relexp_id BIGINT AUTO_INCREMENT, + rel_id INT(8), + exp_id BIGINT, + chip_id BIGINT, -- links to the runs that supplied the data + cam_id BIGINT, -- for this release + state VARCHAR(16), -- released, pending, archive, drop + flags INT UNSIGNED, -- flags for relphot, relastro, ?? + zpt_obs FLOAT, -- calibrated zero point for this release of + zpt_stdev FLOAT, -- this exposure + path_base VARCHAR(255), -- path_base of any supporting files for this + -- release of this exposure. + registered DATETIME, -- insertion time for this row + time_stamp DATETIME, -- time of last update for this row + PRIMARY KEY (relexp_id), + FOREIGN KEY(rel_id) REFERENCES ippRelease(rel_id), + KEY (state), + FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id), + FOREIGN KEY(chip_id) REFERENCES chipRun(chip_id), + FOREIGN KEY(cam_id) REFERENCES camRun(cam_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE relStack ( + relstack_id BIGINT AUTO_INCREMENT, + rel_id INT(8), + skycal_id BIGINT, -- id of the sky calibration run that supplied the calibration + -- for this release. We get back to stack from here + skycell_id varchar(64), + tess_id varchar(64), + state VARCHAR(16), -- released, pending, archive, drop + flags INT UNSIGNED, + stack_type VARCHAR(16), -- nightly, deep, reference + zpt_obs FLOAT, -- calibrated zero point for this release of this skycell + zpt_stdev FLOAT, + fwhm_major FLOAT, -- seeing as measured by staticsky + path_base VARCHAR(255), -- path_base of any supporting files, + registered DATETIME, + time_stamp DATETIME, + PRIMARY KEY (relstack_id), + KEY (tess_id, skycell_id), + KEY (rel_id, tess_id, skycell_id), + FOREIGN KEY(rel_id) REFERENCES ippRelease(rel_id), + FOREIGN KEY(skycal_id) REFERENCES skycalRun(skycal_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + + + +-- These comment lines are here to avoid an empty query error. -- Another way to avoid that problem is to omit the semicolon above but I think that is untidy. Index: branches/eam_branches/ipp-20121219/ippTools/share/pxadmin_drop_tables.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/pxadmin_drop_tables.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/pxadmin_drop_tables.sql (revision 35096) @@ -109,4 +109,7 @@ DROP TABLE IF EXISTS skycalResult; DROP TABLE IF EXISTS skycell; +DROP TABLE IF EXISTS relExp; +DROP TABLE IF EXISTS ippRelease; +DROP TABLE IF EXISTS survey; SET FOREIGN_KEY_CHECKS=1 Index: branches/eam_branches/ipp-20121219/ippTools/share/releasetool_definerelexp.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/releasetool_definerelexp.sql (revision 35096) +++ branches/eam_branches/ipp-20121219/ippTools/share/releasetool_definerelexp.sql (revision 35096) @@ -0,0 +1,19 @@ +SELECT + ippRelease.rel_id, + ippRelease.releaseName, + rawExp.exp_name, + rawExp.filter, + rawExp.dateobs, + rawExp.exp_id, + camRun.chip_id, + camRun.cam_id, + camProcessedExp.zpt_obs, + camProcessedExp.zpt_stdev +FROM ippRelease +JOIN camRun +JOIN camProcessedExp USING(cam_id) +JOIN chipRun USING(chip_id) +JOIN rawExp USING(exp_id) +LEFT JOIN relExp AS previousrelexp USING(exp_id, rel_id) + +WHERE previousrelexp.relexp_id IS NULL Index: branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listrelease.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listrelease.sql (revision 35096) +++ branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listrelease.sql (revision 35096) @@ -0,0 +1,1 @@ +select * from ippRelease JOIN survey USING(surveyID) Index: branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listrelexp.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listrelexp.sql (revision 35096) +++ branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listrelexp.sql (revision 35096) @@ -0,0 +1,38 @@ +SELECT + relexp_id, + relExp.state, + ippRelease.releaseName, + ippRelease.release_state, + ippRelease.rel_id, + ippRelease.priority, + survey.surveyName, + rawExp.exp_name, + relExp.flags, + relExp.zpt_obs, + relExp.zpt_stdev, + relExp.path_base, + relExp.registered, + relExp.time_stamp, + relExp.exp_id, + relExp.chip_id, + chipRun.state as chip_state, + relExp.cam_id, + camRun.state as cam_state, + camProcessedExp.path_base as cam_path_base, + camProcessedExp.fwhm_major, + warpRun.warp_id, + warpRun.state as warp_state, + rawExp.filter, + rawExp.dateobs, + TRUNCATE(DEGREES(rawExp.ra), 4) AS radeg, + TRUNCATE(DEGREES(rawExp.decl), 4) AS decdeg, + rawExp.comment +FROM relExp +JOIN ippRelease USING(rel_id) +JOIN survey USING(surveyID) +JOIN rawExp using(exp_id) +JOIN chipRun using(chip_id, exp_id) +JOIN camRun using(cam_id, chip_id) +JOIN camProcessedExp using(cam_id) +JOIN fakeRun using(cam_id) +JOIN warpRun using(fake_id) Index: branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listsurvey.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listsurvey.sql (revision 35096) +++ branches/eam_branches/ipp-20121219/ippTools/share/releasetool_listsurvey.sql (revision 35096) @@ -0,0 +1,1 @@ +SELECT * from survey Index: branches/eam_branches/ipp-20121219/ippTools/share/stacktool_tobkg.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/stacktool_tobkg.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/stacktool_tobkg.sql (revision 35096) @@ -25,5 +25,6 @@ -- WHERE hook %s GROUP BY stack_id -HAVING (SUM(IF(warpRun.state = 'full', 1, 0)) = COUNT(stackInputSkyfile.warp_id) AND +HAVING ((SUM(IF(warpRun.state = 'cleaned', 1, 0)) = COUNT(stackInputSkyfile.warp_id) OR + SUM(IF(warpRun.state = 'full', 1, 0)) = COUNT(stackInputSkyfile.warp_id)) AND SUM(IF(warpSkyfile.background_model = 1, 1, 0)) = COUNT(stackInputSkyfile.warp_id)) Index: branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_defineskycalrun.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_defineskycalrun.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_defineskycalrun.sql (revision 35096) @@ -1,5 +1,5 @@ SELECT - sky_id, - stack_id, + staticskyRun.sky_id, + stackRun.stack_id, skycell_id, filter, @@ -14,5 +14,5 @@ JOIN stackSumSkyfile USING(stack_id) JOIN skycell USING(tess_id, skycell_id) - LEFT JOIN skycalRun USING(sky_id, stack_id) + LEFT JOIN skycalRun ON staticskyRun.sky_id = skycalRun.sky_id AND stackRun.stack_id = skycalRun.stack_id -- join hook %s WHERE staticskyRun.state = 'full' AND staticskyResult.quality = 0 Index: branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_revert.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_revert.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_revert.sql (revision 35096) @@ -1,5 +1,9 @@ DELETE FROM staticskyResult -USING staticskyResult, staticskyRun +USING staticskyResult, staticskyRun, staticskyInput, stackRun, skycell WHERE staticskyRun.sky_id = staticskyResult.sky_id + AND staticskyRun.sky_id = staticskyInput.sky_id + AND staticskyInput.stack_id = stackRun.stack_id + AND stackRun.tess_id = skycell.tess_id + AND stackRun.skycell_id = skycell.skycell_id AND staticskyRun.state = 'new' AND staticskyResult.fault != 0 Index: branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_revertskycal.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_revertskycal.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_revertskycal.sql (revision 35096) @@ -1,6 +1,8 @@ DELETE FROM skycalResult -USING skycalResult, skycalRun, stackRun +USING skycalResult, skycalRun, stackRun, skycell WHERE skycalRun.skycal_id = skycalResult.skycal_id AND stackRun.stack_id = skycalRun.stack_id AND skycalRun.state = 'new' AND skycalResult.fault != 0 + AND stackRun.tess_id = skycell.tess_id + AND stackRun.skycell_id = skycell.skycell_id Index: branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_skycalresult.sql =================================================================== --- branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_skycalresult.sql (revision 34903) +++ branches/eam_branches/ipp-20121219/ippTools/share/staticskytool_skycalresult.sql (revision 35096) @@ -2,13 +2,12 @@ skycalResult.*, skycalRun.stack_id, - stackRun.tess_id, - stackRun.skycell_id, stackRun.filter, skycalRun.state, skycalRun.label, skycalRun.data_group, - skycalRun.sky_id + skycalRun.sky_id, + skycell.* FROM skycalRun JOIN skycalResult USING(skycal_id) -JOIN stackRun USING(stack_id) - +JOIN stackRun USING(stack_id) +JOIN skycell USING(tess_id, skycell_id)