Changeset 27840 for branches/simtest_nebulous_branches/dbconfig/changes.txt

Timestamp:

May 3, 2010, 8:50:52 AM (16 years ago)

Author:

eugene

Message:

updates from trunk

Location:

branches/simtest_nebulous_branches

Files:

• . (modified) (1 prop)
• dbconfig/changes.txt (modified) (4 diffs)

branches/simtest_nebulous_branches/dbconfig/changes.txt

@@ -16 +16 @@
 -- 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 +82 @@
 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 +1082 @@
 
 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 +1206 @@
 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);
+