Index: branches/tap_branches/dbconfig/changes.txt =================================================================== --- branches/tap_branches/dbconfig/changes.txt (revision 25900) +++ branches/tap_branches/dbconfig/changes.txt (revision 27838) @@ -16,6 +16,6 @@ -- Version 1.1.22 --> 1.1.23 -- Adding support for reduction classes (which are used to specify recipes for parts of the pipeline). -# Adding 'reduction' to detRun -# Renaming 'recipe' in {chip,cam}{Pending,Processed}Exp to 'reduction' +-- Adding 'reduction' to detRun +-- Renaming 'recipe' in {chip,cam}{Pending,Processed}Exp to 'reduction' alter table detRun add column reduction varchar(64) NULL DEFAULT NULL after exp_type; @@ -82,5 +82,5 @@ alter table stackSumSkyfile add column hostname varchar(64) after dtime_stack; -#alter table warpSkyfile add column ( dtime_warp float, hostname varchar(64) ); +-- alter table warpSkyfile add column ( dtime_warp float, hostname varchar(64) ); alter table warpSkyfile add column dtime_warp float after bg_stdev; alter table warpSkyfile add column hostname varchar(64) after dtime_warp; @@ -1329,4 +1329,5 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; +ALTER TABLE flatcorrRun ADD COLUMN make_corr tinyint after state; ALTER TABLE chipRun ADD COLUMN data_group VARCHAR(64) AFTER label; @@ -1421,5 +1422,182 @@ -- Vesion 1.1.57 --- add changes for 1.1.57 here. Then uncomment the following before commiting - --- UPDATE dbversion set schema_version = '1.1.57', updated= CURRENT_TIMESTAMP(); +ALTER TABLE publishClient ADD COLUMN active TINYINT DEFAULT 0 AFTER client_id; + +-- add outdir to allow spreading files over multiple hosts +ALTER TABLE distRun ADD COLUMN outdir VARCHAR(255) AFTER outroot; +ALTER TABLE distComponent ADD COLUMN outdir VARCHAR(255) AFTER state; + +-- populate the existing runs (except for bills' test runs) +UPDATE distRun SET outdir = CONCAT_WS('.', outroot, dist_id) WHERE outroot not like '%@HOST@%'; +UPDATE distRun join distComponent using(dist_id) SET distComponent.outdir = CONCAT_WS('.', distRun.outroot, dist_id); + +UPDATE dbversion set schema_version = '1.1.57', updated= CURRENT_TIMESTAMP(); + + +-- Vesion 1.1.58 +-- UPDATE dbversion set schema_version = '1.1.58', updated= CURRENT_TIMESTAMP(); + +-- Version 1.1.59 +-- Include tables for data quality statistics. + +CREATE TABLE dqstatsRun ( + dqstats_id BIGINT AUTO_INCREMENT, + state VARCHAR(64), + registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(dqstats_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE dqstatsContent ( + dqstats_id BIGINT, + exp_id BIGINT, + chip_id BIGINT, + cam_id BIGINT, + warp_id BIGINT, + invalid TINYINT, + KEY(dqstats_id), + KEY (exp_id), + KEY (chip_id), + KEY (cam_id), + KEY (warp_id), + FOREIGN KEY (dqstats_id) REFERENCES dqstatsRun(dqstats_id), + FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id), + FOREIGN KEY (chip_id) REFERENCES chipRun(chip_id), + FOREIGN KEY (cam_id) REFERENCES camRun(cam_id), + FOREIGN KEY (warp_id) REFERENCES warpRun(warp_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Version 1.1.60 + +ALTER TABLE dqstatsRun ADD COLUMN label VARCHAR(64) AFTER registered; +ALTER TABLE dqstatsRun ADD KEY(label); + +ALTER TABLE dqstatsRun ADD COLUMN fault SMALLINT NOT NULL AFTER label; +ALTER TABLE dqstatsRun ADD KEY(fault); + +-- Version 1.1.61 + +ALTER TABLE pzDataStore ADD COLUMN use_compress SMALLINT NOT NULL AFTER epoch; + +UPDATE dbversion set schema_version = '1.1.61', updated= CURRENT_TIMESTAMP(); + +-- Version 1.1.62 + +ALTER TABLE magicDSFile ADD COLUMN streak_frac FLOAT AFTER recovery_path_base; +ALTER TABLE magicDSFile ADD COLUMN run_time FLOAT AFTER streak_frac; + +ALTER TABLE receiveSource ADD COLUMN state VARCHAR(64) AFTER workdir; +UPDATE receiveSource SET state = 'enabled'; + +UPDATE dbversion set schema_version = '1.1.62', updated= CURRENT_TIMESTAMP(); + +-- Version 1.1.63 +ALTER TABLE distRun ADD COLUMN data_group VARCHAR(64) AFTER fault; +UPDATE distRun, chipRun SET distRun.data_group = chipRun.data_group + WHERE distRun.stage = 'chip' AND stage_id = chip_id; +UPDATE distRun, camRun SET distRun.data_group = camRun.data_group + WHERE distRun.stage = 'camera' AND stage_id = cam_id; +UPDATE distRun, fakeRun SET distRun.data_group = fakeRun.data_group + WHERE distRun.stage = 'fake' AND stage_id = fake_id; +UPDATE distRun, warpRun SET distRun.data_group = warpRun.data_group + WHERE distRun.stage = 'warp' AND stage_id = warp_id; +UPDATE distRun, diffRun SET distRun.data_group = diffRun.data_group + WHERE distRun.stage = 'diff' AND stage_id = diff_id; +UPDATE distRun, stackRun SET distRun.data_group = stackRun.data_group + WHERE distRun.stage = 'stack' AND stage_id = stack_id; +-- all of these runs are old so using label is fine (rawExp doesn't have +-- data_group in distRun it inherit's chipRun.data_group +UPDATE distRun SET distRun.data_group = label where stage = 'raw'; + +UPDATE dbversion set schema_version = '1.1.63', updated= CURRENT_TIMESTAMP(); + +-- Version 1.1.64 + +ALTER TABLE diffRun ADD COLUMN diff_mode SMALLINT NOT NULL AFTER magicked; + +UPDATE diffRun JOIN diffInputSkyfile USING(diff_id) SET diff_mode = 1 WHERE diff_mode = 0 AND warp1 IS NOT NULL AND warp2 IS NOT NULL AND stack1 IS NULL AND stack2 IS NULL; -- warp-warp +UPDATE diffRun JOIN diffInputSkyfile USING(diff_id) SET diff_mode = 2 WHERE diff_mode = 0 AND warp1 IS NOT NULL AND warp2 IS NULL AND stack1 IS NULL AND stack2 IS NOT NULL; -- warp-stack +UPDATE diffRun JOIN diffInputSkyfile USING(diff_id) SET diff_mode = 3 WHERE diff_mode = 0 AND warp1 IS NULL AND warp2 IS NOT NULL AND stack1 IS NOT NULL AND stack2 IS NULL; -- stack-warp +UPDATE diffRun JOIN diffInputSkyfile USING(diff_id) SET diff_mode = 4 WHERE diff_mode = 0 AND warp1 IS NULL AND warp2 IS NULL AND stack1 IS NOT NULL AND stack2 IS NOT NULL; --stack-stack + + +UPDATE dbversion set schema_version = '1.1.64', updated= CURRENT_TIMESTAMP(); + +-- Version 1.1.65 + +ALTER TABLE publishClient ADD COLUMN magicked TINYINT DEFAULT 1 AFTER stage; + +ALTER TABLE rawExp ADD COLUMN state VARCHAR(64) AFTER workdir, ADD KEY(state); +UPDATE rawExp SET state = 'full'; + +ALTER TABLE rawImfile ADD COLUMN data_state VARCHAR(64) AFTER uri, ADD KEY(data_state); +UPDATE rawImfile SET data_state = 'full'; + +-- Not updating schema_version to not break distribution bundles. +-- UPDATE dbversion set schema_version = '1.1.65', updated= CURRENT_TIMESTAMP(); + +-- Version 1.1.66 + +ALTER TABLE magicDSFile ADD COLUMN nondiff_frac FLOAT AFTER streak_frac; +ALTER TABLE distRun ADD COLUMN alternate TINYINT DEFAULT 0 AFTER no_magic; + +-- Tables for static sky analysis + +-- A static sky analysis set +CREATE TABLE staticskyRun ( + ss_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(ss_id), + KEY(state), + KEY(label), + KEY(data_group), + KEY(dist_group) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Inputs for static sky analysis +CREATE TABLE staticskyInput ( + ss_id BIGINT, -- static sky identifier + stack_id BIGINT, -- stack identifier + PRIMARY KEY(ss_id,stack_id), + KEY(stack_id), + FOREIGN KEY(ss_id) REFERENCES staticskyRun(ss_id), + FOREIGN KEY(stack_id) REFERENCES stackSumSkyfile(stack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Result of static sky analysis +CREATE TABLE staticskyResult ( + ss_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 + 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(ss_id), + KEY(good_frac), + KEY(fault), + KEY(quality), + FOREIGN KEY(ss_id) REFERENCES staticskyRun(ss_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +ALTER TABLE pstampDependent ADD COLUMN component VARCHAR(64) after stage_id; +ALTER TABLE pstampDependent CHANGE COLUMN no_magic need_magic TINYINT; +ALTER TABLE pstampDependent ADD COLUMN fault SMALLINT after need_magic; +ALTER TABLE pstampDependent ADD KEY (state); +ALTER TABLE pstampDependent ADD KEY (stage); +ALTER TABLE pstampDependent ADD KEY (stage_id); + +ALTER TABLE magicDSRun ADD KEY(stage); +ALTER TABLE magicDSRun ADD KEY(stage_id); + +ALTER TABLE distRun ADD KEY(stage), ADD KEY(stage_id), ADD KEY(magic_ds_id), ADD KEY(fault), ADD KEY(data_group); +ALTER TABLE distTarget ADD KEY(stage), ADD KEY(dist_group), ADD KEY(filter), ADD KEY(state); +