Index: branches/simtest_nebulous_branches/dbconfig/changes.txt =================================================================== --- branches/simtest_nebulous_branches/dbconfig/changes.txt (revision 25061) +++ branches/simtest_nebulous_branches/dbconfig/changes.txt (revision 27840) @@ -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; @@ -1082,5 +1082,5 @@ ALTER TABLE rcRun ADD COLUMN fault SMALLINT DEFAULT 0; -ALTER TABLE rcRun ADD COLUMN status_fs VARCHAR(64) AFTER state; +ALTER TABLE rcRun ADD COLUMN status_fs_name VARCHAR(64) AFTER state; ALTER TABLE diffSkyfile ADD COLUMN deconv_max FLOAT AFTER kernel_yy; @@ -1206,2 +1206,398 @@ ALTER TABLE magicDSRun ADD COLUMN inv_magic_id BIGINT AFTER magic_id; ALTER TABLE magicDSRun ADD FOREIGN KEY (inv_magic_id) REFERENCES magicRun(magic_id); + +-- Adding columns for debugging publishing problems + +ALTER TABLE publishDone ADD COLUMN hostname VARCHAR(64) AFTER path_base; +ALTER TABLE publishDone ADD COLUMN dtime_script FLOAT AFTER hostname; + +-- Version 1.1.55 +-- addstar stage tables: + +CREATE TABLE addRun ( + add_id BIGINT AUTO_INCREMENT, + cam_id BIGINT, + state VARCHAR(64), + workdir VARCHAR(255), + workdir_state VARCHAR(64), + label VARCHAR(64), + dvodb VARCHAR(255), + magicked BIGINT, + PRIMARY KEY(add_id), + KEY(add_id), + KEY(cam_id), + KEY(state), + KEY(workdir_state), + KEY(label), + INDEX(add_id, cam_id), + FOREIGN KEY(cam_id) REFERENCES camRun(cam_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE addProcessedExp ( + add_id BIGINT AUTO_INCREMENT, + dtime_addstar FLOAT, + n_stars INT, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(add_id), + FOREIGN KEY(add_id) REFERENCES addRun(add_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE addMask ( + label VARCHAR(64), + PRIMARY KEY(label) +) ENGINE=innodb DEFAULT CHARSET=latin1; + + +ALTER TABLE pzDownloadImfile ADD COLUMN bytes INT; +ALTER TABLE pzDownloadImfile ADD COLUMN md5sum VARCHAR(32); +ALTER TABLE newImfile ADD COLUMN bytes INT; +ALTER TABLE newImfile ADD COLUMN md5sum VARCHAR(32); +ALTER TABLE rawImfile ADD COLUMN bytes INT; +ALTER TABLE rawImfile ADD COLUMN md5sum VARCHAR(32); +ALTER TABLE rawImfile ADD COLUMN burntool_state SMALLINT; + +-- Vesion 1.1.56 + +-- Merge the rcDSProduct and rcDestination tables + +ALTER TABLE rcDestination ADD COLUMN dbname varchar(64) AFTER last_fileset; +ALTER TABLE rcDestination ADD COLUMN dbhost varchar(64) AFTER dbname; + +UPDATE rcDestination JOIN rcDSProduct USING(prod_id) + SET rcDestination.name = rcDSProduct.name, + rcDestination.dbname = rcDSProduct.dbname, + rcDestination.dbhost = rcDSProduct.dbhost; + +ALTER TABLE rcDestination DROP foreign key rcDestination_ibfk_1 ; +ALTER TABLE rcDestination DROP key prod_id; +ALTER TABLE rcDestination DROP COLUMN prod_id; + +-- change references to rcDSProduct in rcDSFileset to reference rcDestination + +ALTER TABLE rcDSFileset DROP foreign key rcDSFileset_ibfk_2; +ALTER TABLE rcDSFileset DROP key prod_id; +ALTER TABLE rcDSFileset CHANGE COLUMN prod_id dest_id BIGINT NOT NULL DEFAULT '0'; +ALTER TABLE rcDSFileset ADD KEY (dest_id); +ALTER TABLE rcDSFileset ADD FOREIGN KEY (dest_id) REFERENCES rcDestination(dest_id); + +-- finally drop the obsolete table +DROP TABLE rcDSProduct; + +-- The following changes should have been made to the gpc1 database ages ago +-- do not apply if these columns exist +ALTER TABLE pstampRequest DROP COLUMN outFileset; +ALTER TABLE pstampRequest ADD COLUMN name VARCHAR(64) UNIQUE AFTER state; +ALTER TABLE pstampRequest ADD COLUMN reqType VARCHAR(16) AFTER name; +ALTER TABLE pstampRequest ADD COLUMN outProduct VARCHAR(64) after reqType; +ALTER TABLE pstampRequest ADD COLUMN fault SMALLINT AFTER uri; +-- end of old changes + +-- This change is new +ALTER TABLE pstampRequest ADD COLUMN label VARCHAR(64) AFTER reqType; + +ALTER TABLE distRun ADD COLUMN magic_ds_id BIGINT AFTER stage_id; + +ALTER TABLE pstampJob ADD COLUMN options BIGINT; + +ALTER TABLE addRun ADD COLUMN reduction VARCHAR(64) AFTER workdir_state; +ALTER TABLE addRun DROP COLUMN magicked; +ALTER TABLE addRun ADD COLUMN image_only tinyint after dvodb; +ALTER TABLE addProcessedExp DROP COLUMN n_stars; + +CREATE TABLE flatcorrAddstarLink ( + corr_id BIGINT, + cam_id BIGINT, + add_id BIGINT, + include TINYINT, + PRIMARY KEY(corr_id, cam_id, add_id), + FOREIGN KEY (corr_id) REFERENCES flatcorrRun(corr_id), + FOREIGN KEY (cam_id) REFERENCES camRun(cam_id), + FOREIGN KEY (add_id) REFERENCES addRun(add_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +ALTER TABLE pstampJob ADD COLUMN dep_id BIGINT; +CREATE TABLE pstampDependent ( + dep_id BIGINT AUTO_INCREMENT, + state VARCHAR(64), + stage VARCHAR(64), + stage_id BIGINT, + imagedb VARCHAR(64), + rlabel VARCHAR(64), + no_magic TINYINT, + PRIMARY KEY(dep_id) +) 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; +ALTER TABLE chipRun ADD COLUMN dist_group VARCHAR(64) AFTER data_group; +ALTER TABLE chipRun ADD COLUMN note VARCHAR(255) AFTER magicked; +ALTER TABLE chipRun ADD KEY(data_group); +ALTER TABLE chipRun ADD KEY(dist_group); + +UPDATE chipRun SET data_group = label; +UPDATE chipRun SET dist_group = label; + +ALTER TABLE camRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE camRun ADD COLUMN dist_group VARCHAR(64) AFTER data_group; +ALTER TABLE camRun ADD COLUMN note VARCHAR(255) AFTER magicked; +ALTER TABLE camRun ADD KEY(data_group); +ALTER TABLE camRun ADD KEY(dist_group); + +UPDATE camRun SET data_group = label; +UPDATE camRun SET dist_group = label; + +ALTER TABLE addRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE addRun ADD COLUMN note VARCHAR(255) AFTER dvodb; +ALTER TABLE addRun ADD KEY(data_group); + +UPDATE addRun SET data_group = label; + +ALTER TABLE fakeRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE fakeRun ADD COLUMN dist_group VARCHAR(64) AFTER data_group; +ALTER TABLE fakeRun ADD COLUMN note VARCHAR(255) AFTER epoch; +ALTER TABLE fakeRun ADD KEY(data_group); +ALTER TABLE fakeRun ADD KEY(dist_group); + +UPDATE fakeRun SET data_group = label; +UPDATE fakeRun SET dist_group = label; + +ALTER TABLE warpRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE warpRun ADD COLUMN dist_group VARCHAR(64) AFTER data_group; +ALTER TABLE warpRun ADD COLUMN note VARCHAR(255) AFTER magicked; +ALTER TABLE warpRun ADD KEY(data_group); +ALTER TABLE warpRun ADD KEY(dist_group); + +UPDATE warpRun SET data_group = label; +UPDATE warpRun SET dist_group = label; + +ALTER TABLE stackRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE stackRun ADD COLUMN dist_group VARCHAR(64) AFTER data_group; +ALTER TABLE stackRun ADD COLUMN note VARCHAR(255) AFTER filter; +ALTER TABLE stackRun ADD KEY(data_group); +ALTER TABLE stackRun ADD KEY(dist_group); + +UPDATE stackRun SET data_group = label; +UPDATE stackRun SET dist_group = label; + +ALTER TABLE diffRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE diffRun ADD COLUMN dist_group VARCHAR(64) AFTER data_group; +ALTER TABLE diffRun ADD COLUMN note VARCHAR(255) AFTER magicked; +ALTER TABLE diffRun ADD KEY(data_group); +ALTER TABLE diffRun ADD KEY(dist_group); + +UPDATE diffRun SET data_group = label; +UPDATE diffRun SET dist_group = label; + +ALTER TABLE magicRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE magicRun ADD COLUMN note VARCHAR(255) AFTER fault; +ALTER TABLE magicRun ADD KEY(data_group); + +UPDATE magicRun SET data_group = label; + +ALTER TABLE magicDSRun ADD COLUMN data_group VARCHAR(64) AFTER label; +ALTER TABLE magicDSRun ADD COLUMN fault SMALLINT after remove; +ALTER TABLE magicDSRun ADD COLUMN note VARCHAR(255) AFTER fault; +ALTER TABLE magicDSRun ADD KEY(fault); +ALTER TABLE magicDSRun ADD KEY(data_group); + +UPDATE magicDSRun SET data_group = label; +UPDATE magicDSRun SET fault = 0; + +ALTER TABLE distRun ADD COLUMN note VARCHAR(255) after fault; + +ALTER TABLE distTarget CHANGE COLUMN label dist_group VARCHAR(64); + +ALTER TABLE rcDestination ADD CONSTRAINT UNIQUE(name); + +CREATE TABLE dbversion ( + schema_version VARCHAR(64), + updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP +) ENGINE=innodb DEFAULT CHARSET=latin1; + +INSERT INTO dbversion VALUES('1.1.56', CURRENT_TIMESTAMP); + + +-- Vesion 1.1.57 + +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); +