Index: branches/bills_081204/dbconfig/changes.txt =================================================================== --- branches/bills_081204/dbconfig/changes.txt (revision 20890) +++ branches/bills_081204/dbconfig/changes.txt (revision 20903) @@ -717,2 +717,602 @@ alter table magicDSRun add column cam_id BIGINT after stage_id; +-- diff reorginization +alter table magicRun add column diff_id BIGINT after exp_id +ALTER TABLE magicRun ADD KEY(diff_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; + + + +-- 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 magicInputSkyfile DROP FOREIGN KEY magicInputSkyfile_ibfk_2; +ALTER TABLE magicInputSkyfile DROP column diff_id;