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

-- from eam_branch_20080806
-- add 'data_state' to detrend tables

alter table detProcessedImfile      add column data_state varchar(64) after path_base;
alter table detProcessedExp         add column data_state varchar(64) after path_base;
alter table detStackedImfile        add column data_state varchar(64) after user_5;
alter table detNormalizedStatImfile add column data_state varchar(64) after norm;
alter table detNormalizedImfile     add column data_state varchar(64) after path_base;
alter table detNormalizedExp 	    add column data_state varchar(64) after path_base;
alter table detResidImfile 	    add column data_state varchar(64) after path_base;
alter table detResidExp 	    add column data_state varchar(64) after path_base;
alter table detRunSummary 	    add column data_state varchar(64) after iteration;
alter table detRegisteredImfile     add column data_state varchar(64) after path_base;

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

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

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

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

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

-- not sure when 'filter' was added to stackRun, but it is needed now:

alter table stackRun add column filter varchar(64) after tess_id;

-- changes to the flatcorr tables : no valid flatcorr tables have been created to date expect my tests
-- this sql just drops and recreates the flatcorrRun with the correct layout

delete from flatcorrExp;
delete from flatcorrRun;

drop table flatcorrExp;
drop table flatcorrRun;

CREATE TABLE flatcorrRun (
        corr_id BIGINT AUTO_INCREMENT,
        dvodb VARCHAR(64),
        filter VARCHAR(64),
        state VARCHAR(64),
        workdir VARCHAR(255),
        label VARCHAR(64),
        reduction VARCHAR(64),
        region VARCHAR(64),
        hostname VARCHAR(64),
        fault SMALLINT NOT NULL,
        PRIMARY KEY(corr_id),
        KEY(corr_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE flatcorrChipLink (
        corr_id BIGINT,
        chip_id BIGINT,
        PRIMARY KEY(corr_id, chip_id),
        FOREIGN KEY (corr_id)  REFERENCES  flatcorrRun(corr_id),
        FOREIGN KEY (chip_id)  REFERENCES  chipRun(chip_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE flatcorrCamLink (
        corr_id BIGINT,
        chip_id BIGINT,
        cam_id BIGINT,
        PRIMARY KEY(corr_id, chip_id, cam_id),
        FOREIGN KEY (corr_id)  REFERENCES  flatcorrRun(corr_id),
        FOREIGN KEY (chip_id)  REFERENCES  chipRun(chip_id),
        FOREIGN KEY (cam_id)  REFERENCES  camRun(cam_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

-- I initially defined flatcorrCamLin without the chip_id entry.  this
-- command corrects for that, but should not be needed widely.
-- ALTER TABLE flatcorrCamLink ADD COLUMN chip_id BIGINT AFTER corr_id;
-- alter table flatcorrRun add column hostname varchar(64) after region;
-- alter table flatcorrRun add column fault smallint after hostname;
-- alter table flatcorrRun add column reduction varchar(64) after stats;

-- changes to the chip tables to support cleanup and reprocessing

ALTER TABLE chipProcessedImfile ADD COLUMN data_state VARCHAR(64) AFTER class_id;
UPDATE chipProcessedImfile SET data_state = 'full';

-- I have added 'label' and 'reduction' entries to stack and diff

ALTER TABLE stackRun ADD COLUMN label VARCHAR(64) AFTER workdir;
ALTER TABLE stackRun ADD COLUMN reduction VARCHAR(64) AFTER label;

ALTER TABLE stackSumSkyfile add column data_state varchar(64) after path_base;
UPDATE stackSumSkyfile SET data_state = 'full';

alter table diffRun add column label varchar(64) after workdir;
alter table diffRun add column reduction varchar(64) after label;

ALTER TABLE diffSkyfile add column data_state varchar(64) after path_base;
UPDATE diffSkyfile SET data_state = 'full';

ALTER TABLE warpSkyfile add column data_state varchar(64) after path_base;
UPDATE warpSkyfile SET data_state = 'full';

ALTER TABLE fakeProcessedImfile add column data_state varchar(64) after path_base;
update fakeProcessedImfile set data_state ='full';

-- adding ref_det_id and ref_iter to detRun and detResidImfile

ALTER TABLE detResidImfile add column ref_det_id bigint after iteration;
ALTER TABLE detResidImfile add column ref_iter int after ref_det_id;

ALTER TABLE detRun change column parent ref_det_id bigint;
ALTER TABLE detRun add column ref_iter int after ref_det_id;

-- populate ref_det_id and ref_iter 
UPDATE detResidImfile SET ref_det_id = det_id, ref_iter = iteration;

-- Next we drop the old constraint and add the new one
ALTER TABLE detResidImfile DROP FOREIGN KEY detResidImfile_ibfk_3;
ALTER TABLE detResidImfile add FOREIGN KEY  (ref_det_id, ref_iter)
    REFERENCES  detNormalizedExp(det_id, iteration);

--- Additions for stack/diff QA by PAP.
ALTER TABLE diffSkyfile ADD COLUMN stamps_mean FLOAT AFTER stamps_num;
ALTER TABLE diffSkyfile ADD COLUMN dtime_match FLOAT AFTER dtime_diff;
ALTER TABLE diffSkyfile ADD COLUMN dtime_phot FLOAT AFTER dtime_match;

ALTER TABLE stackSumSkyfile ADD COLUMN dtime_match_mean FLOAT AFTER dtime_stack;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_match_stdev FLOAT AFTER dtime_match_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_initial FLOAT AFTER dtime_match_stdev;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_reject FLOAT AFTER dtime_initial;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_final FLOAT AFTER dtime_reject;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_phot FLOAT AFTER dtime_final;
ALTER TABLE stackSumSkyfile ADD COLUMN match_mean FLOAT AFTER dtime_phot;
ALTER TABLE stackSumSkyfile ADD COLUMN match_stdev FLOAT AFTER match_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN match_rms FLOAT AFTER match_stdev;
ALTER TABLE stackSumSkyfile ADD COLUMN stamps_mean FLOAT AFTER match_rms;
ALTER TABLE stackSumSkyfile ADD COLUMN stamps_stdev FLOAT AFTER stamps_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN stamps_min INT AFTER stamps_stdev;
ALTER TABLE stackSumSkyfile ADD COLUMN reject_images INT AFTER stamps_min;
ALTER TABLE stackSumSkyfile ADD COLUMN reject_pix_mean FLOAT AFTER reject_images;
ALTER TABLE stackSumSkyfile ADD COLUMN reject_pix_stdev FLOAT AFTER reject_pix_mean;
ALTER TABLE stackSumSkyfile ADD COLUMN sources INT AFTER reject_pix_stdev;
ALTER TABLE stackSumSkyfile DROP COLUMN data_state;
ALTER TABLE diffSkyfile DROP COLUMN data_state;

-- Adding the new solar-system info (sun & moon data)
ALTER TABLE rawExp CHANGE COLUMN solang sun_angle FLOAT;
ALTER TABLE rawExp ADD COLUMN sun_alt    FLOAT AFTER sun_angle;
ALTER TABLE rawExp ADD COLUMN moon_angle FLOAT AFTER sun_alt;
ALTER TABLE rawExp ADD COLUMN moon_alt   FLOAT AFTER moon_angle;
ALTER TABLE rawExp ADD COLUMN moon_phase FLOAT AFTER moon_alt;

ALTER TABLE rawImfile ADD COLUMN sun_angle  FLOAT AFTER object;
ALTER TABLE rawImfile ADD COLUMN sun_alt    FLOAT AFTER sun_angle;
ALTER TABLE rawImfile ADD COLUMN moon_angle FLOAT AFTER sun_alt;
ALTER TABLE rawImfile ADD COLUMN moon_alt   FLOAT AFTER moon_angle;
ALTER TABLE rawImfile ADD COLUMN moon_phase FLOAT AFTER moon_alt;

CREATE TABLE pstampProject (
        proj_id BIGINT AUTO_INCREMENT,
        state VARCHAR(64),
        dbname VARCHAR(64),
        dvodb VARCHAR(64),
        camera VARCHAR(64),
        telescope VARCHAR(64),
        need_magic TINYINT,
        PRIMARY KEY(proj_id),
        KEY(proj_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;


-- Reusing astrometry time field since ppImage doesn't do that any more
ALTER TABLE chipProcessedImfile CHANGE COLUMN dtime_astrom dtime_total FLOAT;

-- Deleting fields requiring astrometric matching from chipProcessedImfile
ALTER TABLE chipProcessedImfile DROP COLUMN sigma_ra;
ALTER TABLE chipProcessedImfile DROP COLUMN sigma_dec;
ALTER TABLE chipProcessedImfile DROP COLUMN zp_mean;
ALTER TABLE chipProcessedImfile DROP COLUMN zp_stdev;
ALTER TABLE chipProcessedImfile DROP COLUMN n_astrom;

-- No need to propagate processing times from chip to camera
ALTER TABLE camProcessedExp DROP COLUMN dtime_detrend;
ALTER TABLE camProcessedExp DROP COLUMN dtime_photom;
ALTER TABLE camProcessedExp DROP COLUMN dtime_astrom;

-- Add "dtime_script" to processing results
ALTER TABLE chipProcessedImfile ADD COLUMN dtime_script FLOAT AFTER dtime_total;
ALTER TABLE camProcessedExp ADD COLUMN dtime_script FLOAT AFTER fwhm_minor;
ALTER TABLE fakeProcessedImfile ADD COLUMN dtime_script FLOAT AFTER dtime_fake;
ALTER TABLE warpSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_warp;
ALTER TABLE stackSumSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_phot;
ALTER TABLE diffSkyfile ADD COLUMN dtime_script FLOAT AFTER dtime_phot;

* 1.1.40

-- Add moments statistics (M2, M3, M4)

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2     FLOAT AFTER fwhm_minor;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_err FLOAT AFTER iq_m2;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_lq  FLOAT AFTER iq_m2_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2_uq  FLOAT AFTER iq_m2_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c     FLOAT AFTER iq_m2_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_err FLOAT AFTER iq_m2c;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_lq  FLOAT AFTER iq_m2c_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2c_uq  FLOAT AFTER iq_m2c_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s     FLOAT AFTER iq_m2c_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_err FLOAT AFTER iq_m2s;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_lq  FLOAT AFTER iq_m2s_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m2s_uq  FLOAT AFTER iq_m2s_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3     FLOAT AFTER iq_m2s_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_err FLOAT AFTER iq_m3;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_lq  FLOAT AFTER iq_m3_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m3_uq  FLOAT AFTER iq_m3_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4     FLOAT AFTER iq_m3_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_err FLOAT AFTER iq_m4;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_lq  FLOAT AFTER iq_m4_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_m4_uq  FLOAT AFTER iq_m4_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN n_psfstars INT AFTER n_stars;
ALTER TABLE chipProcessedImfile ADD COLUMN n_iqstars INT AFTER n_psfstars;


ALTER TABLE camProcessedExp ADD COLUMN iq_m2      FLOAT AFTER fwhm_minor;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2_err  FLOAT AFTER iq_m2;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2_lq   FLOAT AFTER iq_m2_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2_uq   FLOAT AFTER iq_m2_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m2c     FLOAT AFTER iq_m2_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_err FLOAT AFTER iq_m2c;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_lq  FLOAT AFTER iq_m2c_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2c_uq  FLOAT AFTER iq_m2c_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m2s     FLOAT AFTER iq_m2c_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_err FLOAT AFTER iq_m2s;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_lq  FLOAT AFTER iq_m2s_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m2s_uq  FLOAT AFTER iq_m2s_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m3      FLOAT AFTER iq_m2s_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m3_err  FLOAT AFTER iq_m3;
ALTER TABLE camProcessedExp ADD COLUMN iq_m3_lq   FLOAT AFTER iq_m3_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m3_uq   FLOAT AFTER iq_m3_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_m4      FLOAT AFTER iq_m3_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_m4_err  FLOAT AFTER iq_m4;
ALTER TABLE camProcessedExp ADD COLUMN iq_m4_lq   FLOAT AFTER iq_m4_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_m4_uq   FLOAT AFTER iq_m4_lq;

ALTER TABLE camProcessedExp ADD COLUMN n_psfstars INT AFTER n_stars;
ALTER TABLE camProcessedExp ADD COLUMN n_iqstars INT AFTER n_psfstars;

* 1.1.41

ALTER TABLE camProcessedExp ADD COLUMN dtime_astrom FLOAT AFTER dtime_script;
ALTER TABLE camProcessedExp ADD COLUMN dtime_addstar FLOAT AFTER dtime_astrom;

* 1.1.42

ALTER TABLE diffSkyfile ADD COLUMN norm FLOAT AFTER stamps_rms;
ALTER TABLE diffSkyfile ADD COLUMN bg_diff FLOAT AFTER norm;
ALTER TABLE diffSkyfile ADD COLUMN kernel_x FLOAT AFTER bg_diff;
ALTER TABLE diffSkyfile ADD COLUMN kernel_y FLOAT AFTER kernel_x;
ALTER TABLE diffSkyfile ADD COLUMN kernel_xx FLOAT AFTER kernel_y;
ALTER TABLE diffSkyfile ADD COLUMN kernel_xy FLOAT AFTER kernel_xx;
ALTER TABLE diffSkyfile ADD COLUMN kernel_yy FLOAT AFTER kernel_xy;

* 1.1.43

ALTER TABLE rawExp ADD COLUMN obs_mode VARCHAR(64) AFTER comment;
ALTER TABLE rawExp ADD COLUMN obs_group VARCHAR(64) AFTER obs_mode;

ALTER TABLE rawImfile ADD COLUMN obs_mode VARCHAR(64) AFTER comment;
ALTER TABLE rawImfile ADD COLUMN obs_group VARCHAR(64) AFTER obs_mode;

* 1.1.44

ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_major_lq FLOAT AFTER fwhm_major;
ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_major_uq FLOAT AFTER fwhm_major_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_minor_lq FLOAT AFTER fwhm_minor;
ALTER TABLE chipProcessedImfile ADD COLUMN fwhm_minor_uq FLOAT AFTER fwhm_minor_lq;

ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_major     FLOAT AFTER fwhm_minor_uq;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_major_err FLOAT AFTER iq_fwhm_major;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_minor     FLOAT AFTER iq_fwhm_major_err;
ALTER TABLE chipProcessedImfile ADD COLUMN iq_fwhm_minor_err FLOAT AFTER iq_fwhm_minor;

ALTER TABLE camProcessedExp ADD COLUMN fwhm_major_lq 	 FLOAT AFTER fwhm_major;
ALTER TABLE camProcessedExp ADD COLUMN fwhm_major_uq 	 FLOAT AFTER fwhm_major_lq;

ALTER TABLE camProcessedExp ADD COLUMN fwhm_minor_lq 	 FLOAT AFTER fwhm_minor;
ALTER TABLE camProcessedExp ADD COLUMN fwhm_minor_uq 	 FLOAT AFTER fwhm_minor_lq;

ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_major     FLOAT AFTER fwhm_minor_uq;
ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_major_err FLOAT AFTER iq_fwhm_major;
ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_minor     FLOAT AFTER iq_fwhm_major_err;
ALTER TABLE camProcessedExp ADD COLUMN iq_fwhm_minor_err FLOAT AFTER iq_fwhm_minor;

ALTER TABLE flatcorrRun ADD COLUMN camera VARCHAR(64) AFTER dvodb;
ALTER TABLE flatcorrRun ADD COLUMN telescope VARCHAR(64) AFTER camera;
ALTER TABLE flatcorrRun ADD COLUMN epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER telescope;

* 1.1.45

ALTER TABLE camProcessedExp CHANGE COLUMN zp_mean  zpt_obs   FLOAT;
ALTER TABLE camProcessedExp CHANGE COLUMN zp_stdev zpt_stdev FLOAT;
ALTER TABLE camProcessedExp ADD COLUMN    zpt_lq	     FLOAT AFTER zpt_stdev;
ALTER TABLE camProcessedExp ADD COLUMN    zpt_uq 	     FLOAT AFTER zpt_lq;

* 1.1.46

ALTER TABLE pzDownloadImfile ADD COLUMN hostname varchar(64) after epoch;
ALTER TABLE flatcorrRun ADD COLUMN det_type varchar(64) after corr_id;
ALTER TABLE flatcorrChipLink ADD COLUMN include tinyint after chip_id;
ALTER TABLE flatcorrCamLink ADD COLUMN include tinyint after cam_id;

ALTER TABLE rawImfile ADD COLUMN ignored TINYINT DEFAULT 0 AFTER moon_phase;

-- add new tables to control magic de-streaking
CREATE TABLE magicDSRun (
        magic_ds_id BIGINT AUTO_INCREMENT,
        magic_id BIGINT,
        state VARCHAR(64),
        stage VARCHAR(64),
        outroot VARCHAR(255),
        recoveryroot VARCHAR(255),
        re_place TINYINT,
        remove TINYINT,
        PRIMARY KEY(magic_ds_id),
        KEY(magic_ds_id),
        KEY(state),
        FOREIGN KEY (magic_id)  REFERENCES  magicRun(magic_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE magicDSFile (
    magic_ds_id BIGINT,
    component VARCHAR(64),
    backup_path_base VARCHAR(255),
    recovery_path_base VARCHAR(255),
    fault SMALLINT,
    PRIMARY KEY(magic_ds_id, component),
    KEY(fault),
    FOREIGN KEY (magic_ds_id) REFERENCES magicDSRun(magic_ds_id)
) ENGINE=innodb DEFAULT CHARSET=latin1;

-- add to the run table to make the queries far less expensive
alter table magicDSRun add column stage_id BIGINT after stage;
alter table magicDSRun add column cam_id BIGINT after stage_id;

--  Version: 1.1.47

-- WARNING WARNING WARNING
-- Updating a database which has populated diffRun's requires editing
-- the contents of the tables in a way that cannot be expresed here.
-- The script tools/collapsdiffinputs.pl does the appropriate edits
-- and should be run at the spot indicated below

-- fix order of workdir to match pxadmin_create_tables.sql
ALTER TABLE diffRun CHANGE COLUMN workdir workdir VARCHAR(255) AFTER state;

-- add exposure id to diffRun
ALTER TABLE diffRun ADD COLUMN exp_id BIGINT;
ALTER TABLE diffRun ADD FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id);
ALTER TABLE diffRun DROP COLUMN skycell_id;

-- add unique ids for each image and flags that indicate whether the image has been
-- magic destreaked
ALTER TABLE diffSkyfile ADD COLUMN skycell_id VARCHAR(64) AFTER diff_id;
ALTER TABLE diffSkyfile DROP PRIMARY KEY, ADD PRIMARY KEY (diff_id, skycell_id);
ALTER TABLE diffSkyfile ADD COLUMN diff_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(diff_image_id);
ALTER TABLE diffSkyfile ADD COLUMN magicked TINYINT;

-- drop exiting foreign key constraints
ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_1;
ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_2;
ALTER TABLE diffInputSkyfile drop FOREIGN KEY diffInputSkyfile_ibfk_3;

ALTER TABLE diffInputSkyfile DROP COLUMN kind;
ALTER TABLE diffInputSkyfile CHANGE COLUMN skycell_id skycell_id VARCHAR(64) AFTER diff_id;


ALTER TABLE diffInputSkyfile CHANGE COLUMN warp_id warp1 BIGINT;
ALTER TABLE diffInputSkyfile ADD COLUMN stack1 BIGINT AFTER warp1, ADD KEY(stack1);
ALTER TABLE diffInputSkyfile ADD COLUMN warp2 BIGINT AFTER stack1, ADD KEY(warp2);
ALTER TABLE diffInputSkyfile CHANGE COLUMN stack_id stack2 BIGINT AFTER warp2;

-- WARNING WARNING
-- If you have existing diff runs they must be converted

-- run the script collapse_diffinputs.pl here.

--        collapse_diffinputs.pl dbname dbuser dbpass dbhost

DELETE FROM diffInputSkyfile where template = 1;

ALTER TABLE diffInputSkyfile DROP PRIMARY KEY;
ALTER TABLE diffInputSkyfile ADD PRIMARY KEY (diff_id, skycell_id);
ALTER TABLE diffInputSkyfile DROP COLUMN template;

ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id);

ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (warp1, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id);
ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (warp2, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id);

ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (stack1) REFERENCES stackRun(stack_id);
ALTER TABLE diffInputSkyfile ADD FOREIGN KEY (stack2) REFERENCES stackRun(stack_id); 


-- add unique ids for each image and flags that indicate whether the image has been
-- magic destreaked
ALTER TABLE warpRun DROP COLUMN magiced;

ALTER TABLE warpSkyfile ADD COLUMN warp_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(warp_image_id);
ALTER TABLE warpSkyfile ADD COLUMN magicked TINYINT;

ALTER TABLE chipProcessedImfile ADD COLUMN chip_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(chip_image_id);
ALTER TABLE chipProcessedImfile ADD COLUMN magicked TINYINT;

ALTER TABLE rawImfile ADD COLUMN raw_image_id BIGINT NOT NULL AUTO_INCREMENT, ADD KEY(raw_image_id);
ALTER TABLE rawImfile ADD COLUMN magicked TINYINT;

-- magic adaptations to diff reorginization
ALTER TABLE magicRun ADD COLUMN diff_id BIGINT AFTER exp_id;
ALTER TABLE magicRun ADD CONSTRAINT FOREIGN KEY(diff_id) REFERENCES diffRun(diff_id);
ALTER TABLE magicRun ADD CONSTRAINT FOREIGN KEY(exp_id) REFERENCES rawExp(exp_id);
ALTER TABLE magicInputSkyfile DROP PRIMARY KEY, ADD PRIMARY KEY(magic_id, diff_id, node);



-- Version: 1.1.48

ALTER TABLE chipProcessedImfile DROP COLUMN chip_image_id;
ALTER TABLE diffSkyfile DROP COLUMN diff_image_id;
ALTER TABLE warpSkyfile DROP COLUMN warp_image_id;

ALTER TABLE diffInputSkyfile ADD COLUMN diff_skyfile_id BIGINT AUTO_INCREMENT, ADD KEY(diff_id, skycell_id), ADD KEY(diff_skyfile_id);

CREATE TABLE chipImfile (
     chip_id BIGINT,
     class_id VARCHAR(64),
     chip_imfile_id BIGINT AUTO_INCREMENT,
     PRIMARY KEY(chip_id, class_id),
     KEY(chip_imfile_id),
     FOREIGN KEY (chip_id) REFERENCES  chipRun(chip_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

CREATE TABLE warpImfile (
     warp_id BIGINT,
     skycell_id VARCHAR(64),
     warp_skyfile_id BIGINT AUTO_INCREMENT,
     PRIMARY KEY(warp_id, skycell_id),
     KEY(warp_skyfile_id),
     FOREIGN KEY (warp_id) REFERENCES  warpRun(warp_id))
ENGINE=innodb DEFAULT CHARSET=latin1;

INSERT INTO chipImfile
   SELECT chip_id, class_id, 0
   FROM rawImfile JOIN chipRun USING(exp_id)
   LEFT JOIN chipImfile USING(chip_id, class_id)
   WHERE chipImfile.class_id IS NULL;

INSERT INTO warpImfile 
    SELECT DISTINCT warp_id, skycell_id, 0 
    FROM warpSkyCellMap LEFT JOIN warpImfile USING(warp_id, skycell_id)
    WHERE warpImfile.skycell_id IS NULL;



