-- How to manually change the database between versions via mySQL commands.
-- 
-- This file is generated manually, and may not be complete.


-- Version 1.1.20 --> 1.1.21:

alter table camProcessedExp drop nastro;
alter table camProcessedExp add (fwhm double, fwhm_range double, n_stars int(11), n_extended int(11), n_cr int(11), n_astrom int(11));
alter table chipProcessedImfile add column (bias double, bias_stdev double, fringe_0 double, fringe_1 double, fringe_2 double, sigma_ra double, sigma_dec double, ap_resid double, ap_resid_stdev double, fwhm double, fwhm_range double, n_stars int(11), n_extended int(11), n_cr int(11), n_astrom int(11));
alter table warpSkyfile add column path_base varchar(255) NULL DEFAULT NULL after uri;
alter table diffSkyfile add column path_base varchar(255) NULL DEFAULT NULL after uri;
alter table stackSumSkyfile add column path_base varchar(255) NULL DEFAULT NULL after uri;


-- 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'

alter table detRun add column reduction varchar(64) NULL DEFAULT NULL after exp_type;
alter table chipPendingExp change column recipe reduction varchar(64);
alter table chipProcessedExp change column recipe reduction varchar(64);
alter table camPendingExp change column recipe reduction varchar(64);
alter table camProcessedExp change column recipe reduction varchar(64);

-- Version ??? --> 1.1.29

alter table warpSkyfile add column ignored tinyint after good_frac;

--
-- The following set of changes were applied to update the gpc1 database on 2008/02/07
--

alter table calDB change column catdir dvodb varchar(64);

alter table camProcessedExp change column fwhm fwhm_major float;
alter table camProcessedExp change column fwhm_range fwhm_minor float;

-- In order to keep the column order correct this is split up below
--alter table camProcessedExp add column ( bias float, bias_stdev float, fringe_0 float, fringe_1 float, fringe_2 float, ap_resid float, ap_resid_stdev float, dtime_detrend float, dtime_photom float, dtime_astrom float, hostname varchar(64) );

alter table camProcessedExp add column bias float after bg_mean_stdev;
alter table camProcessedExp add column bias_stdev float after bias;
alter table camProcessedExp add column fringe_0 float after bias_stdev;
alter table camProcessedExp add column fringe_1 float after fringe_0;
alter table camProcessedExp add column fringe_2 float after fringe_1;
alter table camProcessedExp add column ap_resid float after sigma_dec;
alter table camProcessedExp add column ap_resid_stdev float after ap_resid;
alter table camProcessedExp add column dtime_detrend float after fwhm_minor;
alter table camProcessedExp add column dtime_photom float after dtime_detrend;
alter table camProcessedExp add column dtime_astrom float after dtime_photom;
alter table camProcessedExp add column hostname varchar(64) after dtime_astrom;


alter table chipProcessedImfile change column fwhm fwhm_major float;
alter table chipProcessedImfile change column fwhm_range fwhm_minor float;

-- In order to keep the column order correct this is split up below
-- alter table chipProcessedImfile add column ( zp_mean float, zp_stdev float, dtime_detrend float, dtime_photom float, dtime_astrom float, hostname varchar(64) );

alter table chipProcessedImfile add column zp_mean float after ap_resid_stdev;
alter table chipProcessedImfile add column zp_stdev float after zp_mean;
alter table chipProcessedImfile add column dtime_detrend float after fwhm_minor;
alter table chipProcessedImfile add column dtime_photom float after dtime_detrend;
alter table chipProcessedImfile add column dtime_astrom float after dtime_photom;
alter table chipProcessedImfile add column hostname varchar(64) after dtime_astrom;


-- In order to keep the column order correct this is split up below
-- alter table diffSkyfile add column ( dtime_diff float, hostname varchar(64) );
alter table diffSkyfile add column dtime_diff float after bg_stdev;
alter table diffSkyfile add column hostname varchar(64) after dtime_diff;
alter table diffSkyfile change column good_frac good_frac float;

alter table flatcorrRun add column filter varchar(64) after dvodb;

alter table stackSumSkyfile change column good_frac good_frac float;

-- alter table stackSumSkyfile add column ( dtime_stack float, hostname varchar(64) );
alter table stackSumSkyfile add column dtime_stack float after bg_stdev;
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 after bg_stdev;
alter table warpSkyfile add column hostname varchar(64) after dtime_warp;

alter table warpSkyfile change column good_frac good_frac float;

alter table newExp add column dvodb varchar(255) after reduction;
alter table newExp add column tess_id varchar(64) after dvodb;
alter table rawExp add column dvodb varchar(255) after reduction;
alter table rawExp add column tess_id varchar(64) after dvodb;
alter table chipRun add column tess_id varchar(64) after dvodb;
alter table camRun add column tess_id varchar(64) after dvodb;
alter table warpRun add column tess_id varchar(64) after dvodb;

alter table camRun add column end_stage varchar(64) after tess_id;
alter table chipRun add column end_stage varchar(64) after tess_id;
alter table warpRun add column end_stage varchar(64) after tess_id;
alter table newExp add column end_stage varchar(64) after tess_id;
alter table rawExp add column end_stage varchar(64) after tess_id;
alter table camRun add key (end_stage);
alter table chipRun add key (end_stage);
alter table warpRun add key (end_stage);
alter table newExp add key (end_stage);
alter table rawExp add key (end_stage);

alter table warpRun add column workdir_state varchar(64) after workdir;
alter table warpRun add key (workdir_state);

alter table pzDoneImfile add column fault smallint(6);

-- end of changes to 1.1.29

alter table newExp add column label varchar(64) after end_stage;

-- 1.1.30

drop table pzDoneExp;
drop table pzDoneImfile;
drop table pzPendingExp;
drop table pzPendingImfile;
alter table flatcorrRun add column region VARCHAR(255) after stats;

-- 1.1.31

ALTER TABLE warpRun ADD COLUMN cam_id BIGINT AFTER warp_id;
ALTER TABLE warpRun ADD KEY(cam_id);
ALTER TABLE warpRun ADD KEY(warp_id, cam_id);
ALTER TABLE warpRun ADD CONSTRAINT FOREIGN KEY(cam_id) REFERENCES
camRun(cam_id);
ALTER TABLE warpRun ADD COLUMN magiced TINYINT AFTER registered;
ALTER TABLE warpSkyCellMap DROP FOREIGN KEY warpSkyCellMap_ibfk_1;
UPDATE warpRun JOIN warpInputExp USING(warp_id) SET warpRun.cam_id =
warpInputExp.cam_id WHERE warpRun.warp_id = warpInputExp.warp_id;
ALTER TABLE warpSkyCellMap ADD CONSTRAINT FOREIGN KEY(warp_id, cam_id)
REFERENCES warpRun(warp_id, cam_id);
DROP TABLE warpInputExp;

-- 1.1.32
ALTER TABLE warpRun ADD COLUMN label VARCHAR(64) AFTER workdir_state;
ALTER TABLE warpRun ADD KEY(label);
CREATE TABLE warpMask (label VARCHAR(64), PRIMARY KEY(label)) ENGINE=innodb;

-- 1.1.33
ALTER TABLE summitExp ADD COLUMN fault smallint(6) NOT NULL AFTER imfiles;
ALTER TABLE summitExp ADD KEY(fault);

-- 1.1.34
ALTER TABLE rawImfile ADD KEY(exp_name);

-- 1.1.35
ALTER TABLE rawImfile ADD INDEX UNQIUE(exp_id, tmp_class_id);

-- 1.1.36
ALTER TABLE warpSkyfile ADD COLUMN (xmin INT, xmax INT, ymin INT, ymax INT);
ALTER TABLE diffSkyfile ADD COLUMN (stamps_num INT, stamps_rms FLOAT, sources INT);
ALTER TABLE rawImfile ADD COLUMN hostname VARCHAR(64) AFTER object;
ALTER TABLE rawExp ADD COLUMN hostname VARCHAR(64) AFTER object;

-- 1.1.37
ALTER TABLE pzDataStore ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
ALTER TABLE summitExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
ALTER TABLE summitImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
ALTER TABLE pzDownloadExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER state;
ALTER TABLE pzDownloadImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
ALTER TABLE newExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER label;
ALTER TABLE newImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER uri;
ALTER TABLE rawExp ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;
ALTER TABLE rawImfile ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER fault;

-- 1.1.38

-- convert from utf8 -> latin1 for performance/space gains
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE calDB CONVERT TO CHARACTER SET latin1;
ALTER TABLE calRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE camMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE camProcessedExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE camRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE chipMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE chipProcessedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE chipRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE detInputExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detNormalizedExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detNormalizedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detNormalizedStatImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detProcessedExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detProcessedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detRegisteredImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detResidExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE detResidImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE detRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE detRunSummary CONVERT TO CHARACTER SET latin1;
ALTER TABLE detStackedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE diffInputSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE diffRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE diffSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE fakeMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE fakeProcessedImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE fakeRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE flatcorrExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE flatcorrRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE guidePendingExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicInputSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicNodeResult CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicSkyfileMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE magicTree CONVERT TO CHARACTER SET latin1;
ALTER TABLE newExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE newImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE pstampDataStore CONVERT TO CHARACTER SET latin1;
ALTER TABLE pstampJob CONVERT TO CHARACTER SET latin1;
ALTER TABLE pstampRequest CONVERT TO CHARACTER SET latin1;
ALTER TABLE pzDataStore CONVERT TO CHARACTER SET latin1;
ALTER TABLE pzDownloadExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE pzDownloadImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE rawExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE rawImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE stackInputSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE stackRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE stackSumSkyfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE summitExp CONVERT TO CHARACTER SET latin1;
ALTER TABLE summitImfile CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpMask CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpRun CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpSkyCellMap CONVERT TO CHARACTER SET latin1;
ALTER TABLE warpSkyfile CONVERT TO CHARACTER SET latin1;
SET FOREIGN_KEY_CHECKS=1;

-- first use of fake* tables
-- drop and recreate any fake* tables created prior to this point

DROP TABLE IF EXISTS fakeRun;
CREATE TABLE fakeRun (
    fake_id BIGINT AUTO_INCREMENT,
    cam_id BIGINT,
    state VARCHAR(64),
    workdir VARCHAR(255),
    label VARCHAR(64),
    reduction VARCHAR(64),
    expgroup VARCHAR(64),
    dvodb VARCHAR(255),
    tess_id VARCHAR(64),
    end_stage VARCHAR(64),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(fake_id),
    KEY(cam_id),
    KEY(state),
    KEY(label),
    KEY(expgroup),
    KEY(end_stage),
    INDEX(fake_id, cam_id),
    FOREIGN KEY (cam_id)
        REFERENCES camRun(cam_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS fakeProcessedImfile;
CREATE TABLE fakeProcessedImfile (
    fake_id BIGINT AUTO_INCREMENT,
    exp_id BIGINT(20),
    class_id VARCHAR(64),
    uri VARCHAR(255),
    dtime_fake FLOAT,
    hostname VARCHAR(64),
    path_base VARCHAR(255),
    fault SMALLINT NOT NULL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(fake_id, exp_id, class_id),
    KEY(fault),
    FOREIGN KEY (fake_id)
        REFERENCES fakeRun(fake_id),
    FOREIGN KEY(exp_id, class_id)
        REFERENCES rawImfile(exp_id, class_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS fakeMask;
CREATE TABLE fakeMask (
    label VARCHAR(64),
    PRIMARY KEY(label))
ENGINE=innodb DEFAULT CHARSET=latin1;

-- re-normalize camProcessedExp table
-- unrelated to other changes
ALTER TABLE camProcessedExp DROP FOREIGN KEY camProcessedExp_ibfk_1;
ALTER TABLE camProcessedExp DROP COLUMN chip_id;
ALTER TABLE camProcessedExp ADD FOREIGN KEY (cam_id) REFERENCES camRun (cam_id);

-- insert fakeRun betwen camRun and warpRun
-- populate fakeRun/fakeProcessedImfile
-- then heal the foreign key references

CREATE TEMPORARY TABLE warpCamMap (
    warp_id BIGINT,
    cam_id  BIGINT,
    PRIMARY KEY(warp_id),
    KEY(cam_id)
) ENGINE=MEMORY;

INSERT INTO warpCamMap SELECT warp_id, cam_id FROM warpRun;

ALTER TABLE warpRun drop FOREIGN KEY warpRun_ibfk_1;
ALTER TABLE warpSkyCellMap DROP FOREIGN KEY warpSkyCellMap_ibfk_1;

ALTER TABLE warpRun change COLUMN cam_id fake_id BIGINT(20) DEFAULT NULL;
ALTER TABLE warpSkyCellMap DROP COLUMN cam_id;

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE warpRun ADD FOREIGN KEY (fake_id) REFERENCES fakeRun (fake_id);
SET FOREIGN_KEY_CHECKS=1;
ALTER TABLE warpSkyCellMap ADD FOREIGN KEY (warp_id) REFERENCES warpRun(warp_id);

INSERT INTO fakeRun
SELECT
    NULL,
    camRun.cam_id,
    'stop',
    workdir,
    label,
    reduction,
    expgroup,
    dvodb, 
    tess_id, 
    end_stage, 
    NULL 
FROM camRun
JOIN warpCamMap
    using(cam_id);

INSERT INTO fakeProcessedImfile
SELECT
    fake_id,
    chipRun.exp_id,
    class_id,
    uri,
    0,
    hostname,
    path_base, 
    fault, 
    NULL
FROM fakeRun
JOIN camRun
    USING(cam_id)
JOIN chipRun
    USING(chip_id)
JOIN chipProcessedImfile
    USING(chip_id);

UPDATE warpRun, fakeRun, warpCamMap
SET warpRun.fake_id = fakeRun.fake_id
WHERE warpRun.warp_id = warpCamMap.warp_id
    AND warpCamMap.cam_id = fakeRun.fake_id;

DROP TABLE warpCamMap;


-- new values for chipRun.state

update chipRun set state = 'new' where state = 'run';
update chipRun set state = 'full' where state = 'stop';

