Index: trunk/dbconfig/changes.txt =================================================================== --- trunk/dbconfig/changes.txt (revision 42385) +++ trunk/dbconfig/changes.txt (revision 42833) @@ -2632,2 +2632,233 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; +-- EAM 2024.02.20 : adding the following missing fields to gpc2: + +-- example ALTER TABLE stackSumSkyfile ADD COLUMN path_base VARCHAR(255) NULL DEFAULT NULL AFTER uri; +ALTER TABLE chipBackgroundImfile ADD COLUMN data_state VARCHAR(64) AFTER path_base; +-- ALTER TABLE minidvodbRun ADD COLUMN note VARCHAR(255) AFTER state; +ALTER TABLE skycalResult CHANGE COLUMN software_ver software_ver VARCHAR(16); +ALTER TABLE ippRelease ADD COLUMN accessLevelMin INT AFTER ubercal_file; +ALTER TABLE warpBackgroundSkyfile ADD COLUMN data_state VARCHAR(64) AFTER path_base; + +-- EAM 2023.11.04 : adding xcstackRun, xcstackInputSkyfile, xcstackSumSkyfile +-- EAM 2024.02.20 : the following need to be added to the gpc1 and gpc2 databases + +CREATE TABLE xcstackCamera ( + camera_id BIGINT AUTO_INCREMENT, + camera VARCHAR(255), + PRIMARY KEY(camera_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcstackRun ( + xcstack_id BIGINT AUTO_INCREMENT, + state VARCHAR(64), + workdir VARCHAR(255), + label VARCHAR(64), + data_group VARCHAR(64), + dist_group VARCHAR(64), + reduction VARCHAR(64), + registered DATETIME, + skycell_id VARCHAR(64), + tess_id VARCHAR(64), + filter VARCHAR(64), + software_ver VARCHAR(16), + note VARCHAR(255), + PRIMARY KEY(xcstack_id), + KEY(xcstack_id), + KEY(state), + KEY(skycell_id), + KEY(tess_id), + KEY(label), + KEY(data_group), + KEY(dist_group) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcstackInputSkyfile ( + xcstack_id BIGINT, + camera_id BIGINT, + stack_id BIGINT, + PRIMARY KEY(xcstack_id, camera_id, stack_id), + KEY(xcstack_id), + KEY(stack_id), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id), + FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcstackSumSkyfile ( + xcstack_id BIGINT, + path_base VARCHAR(255), + bg DOUBLE, + bg_stdev DOUBLE, + dtime_stack FLOAT, + dtime_script FLOAT, + hostname VARCHAR(64), + good_frac FLOAT, + fault SMALLINT, + software_ver VARCHAR(16), + quality SMALLINT NOT NULL DEFAULT 0, + PRIMARY KEY(xcstack_id), + KEY(dtime_stack), + KEY(good_frac), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- A static sky analysis set +CREATE TABLE xcskyRun ( + xcsky_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(xcsky_id), + KEY(state), + KEY(label), + KEY(data_group), + KEY(dist_group) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Inputs for static sky analysis +CREATE TABLE xcskyInput ( + xcsky_id BIGINT, -- static sky identifier + xcstack_id BIGINT, -- stack identifier + PRIMARY KEY(xcsky_id,xcstack_id), + KEY(xcstack_id), + FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Result of static sky analysis +CREATE TABLE xcskyResult ( + xcsky_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 + num_inputs INT, -- number of input images + 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(xcsky_id), + KEY(good_frac), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xccalRun ( + xccal_id BIGINT AUTO_INCREMENT, -- unique identifier + xcsky_id BIGINT, + xcstack_id BIGINT, + 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) + registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered + note VARCHAR(255), -- note + PRIMARY KEY(xccal_id), + KEY(state), + KEY(label), + KEY(data_group), + KEY(dist_group), + KEY(xcsky_id), + KEY(xcstack_id), + FOREIGN KEY(xcsky_id) REFERENCES xcskyRun(xcsky_id), + FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +-- Result of sky calibration analysis +CREATE TABLE xccalResult ( + xccal_id BIGINT, + path_base VARCHAR(255) NOT NULL, + dtime_script FLOAT, + dtime_astrom FLOAT, + sigma_ra FLOAT, + sigma_dec FLOAT, + n_astrom INT, + n_detections INT, + n_extended INT, + n_forced INT, + zpt_obs FLOAT, + zpt_stdev FLOAT, + fwhm_major FLOAT, + fwhm_minor FLOAT, + quality SMALLINT NOT NULL, + hostname VARCHAR(64) NOT NULL, + software_ver VARCHAR(64), + fault SMALLINT NOT NULL, + PRIMARY KEY(xccal_id), + KEY(fault), + KEY(quality), + FOREIGN KEY(xccal_id) REFERENCES xccalRun(xccal_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcForceRun ( + xcff_id BIGINT NOT NULL AUTO_INCREMENT, + xccal_id BIGINT, -- NOT a foreign key: reference to entry in xcamera db + sources_path_base VARCHAR(255), + state VARCHAR(64), + workdir VARCHAR(255), + label VARCHAR(64), + skycell_id VARCHAR(64), + tess_id VARCHAR(64), + data_group VARCHAR(64), + dist_group VARCHAR(64), + note VARCHAR(255), + reduction VARCHAR(64), + registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time run was registered + PRIMARY KEY(xcff_id), + KEY(state), + KEY(label), + KEY(data_group), + KEY(xccal_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcForceInput ( + xcff_id BIGINT, + warp_id BIGINT, + camera_id BIGINT, + PRIMARY KEY(xcff_id, warp_id, camera_id), + FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id), + FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id), + FOREIGN KEY(camera_id) REFERENCES xcstackCamera(camera_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcForceResult ( + xcff_id BIGINT, + warp_id BIGINT, + path_base VARCHAR(255) NOT NULL, + dtime_script FLOAT, + quality SMALLINT NOT NULL, + hostname VARCHAR(64) NOT NULL, + software_ver VARCHAR(16), + fault SMALLINT NOT NULL, + PRIMARY KEY(xcff_id, warp_id), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id), + FOREIGN KEY(warp_id) REFERENCES warpRun(warp_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE xcForceSummary ( + xcff_id BIGINT, + path_base VARCHAR(255) NOT NULL, + dtime_script FLOAT, + quality SMALLINT NOT NULL, + hostname VARCHAR(64) NOT NULL, + software_ver VARCHAR(16), + fault SMALLINT NOT NULL, + PRIMARY KEY(xcff_id), + KEY(fault), + KEY(quality), + FOREIGN KEY(xcff_id) REFERENCES xcForceRun(xcff_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +