Index: /branches/eam_branches/ipp-20230313/dbconfig/changes.txt =================================================================== --- /branches/eam_branches/ipp-20230313/dbconfig/changes.txt (revision 42632) +++ /branches/eam_branches/ipp-20230313/dbconfig/changes.txt (revision 42633) @@ -2632,6 +2632,20 @@ ) 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; + -- 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 ( @@ -2663,6 +2677,9 @@ camera_id BIGINT, stack_id BIGINT, - PRIMARY KEY(xcstack_id, stack_id), + 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; @@ -2687,2 +2704,160 @@ ) 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; + +