Index: /branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql =================================================================== --- /branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql (revision 42608) +++ /branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_create_tables.sql (revision 42609) @@ -2176,100 +2176,4 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; --- xcsky and xccal tables -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 xcsky 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 xcstackSumSkyfile(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 skycell ( tess_id VARCHAR(64), @@ -2577,4 +2481,10 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; +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, @@ -2605,6 +2515,9 @@ camera_id BIGINT, stack_id BIGINT, - PRIMARY KEY(xcstack_id, stack_id), - FOREIGN KEY(xcstack_id) REFERENCES xcstackRun(xcstack_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; @@ -2629,8 +2542,159 @@ ) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE xcstackCamera ( - camera_id BIGINT, - camera VARCHAR(255), - PRIMARY KEY(camera_id) +-- 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; Index: /branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_drop_tables.sql =================================================================== --- /branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_drop_tables.sql (revision 42608) +++ /branches/eam_branches/ipp-20230313/ippTools/share/pxadmin_drop_tables.sql (revision 42609) @@ -131,4 +131,17 @@ DROP TABLE IF EXISTS fpcamProcessedExp; DROP TABLE IF EXISTS fpcamRun; +DROP TABLE IF EXISTS xccalResult; +DROP TABLE IF EXISTS xccalRun; +DROP TABLE IF EXISTS xcForceInput; +DROP TABLE IF EXISTS xcForceResult; +DROP TABLE IF EXISTS xcForceRun; +DROP TABLE IF EXISTS xcForceSummary; +DROP TABLE IF EXISTS xcskyInput; +DROP TABLE IF EXISTS xcskyResult; +DROP TABLE IF EXISTS xcskyRun; +DROP TABLE IF EXISTS xcstackCamera; +DROP TABLE IF EXISTS xcstackInputSkyfile; +DROP TABLE IF EXISTS xcstackRun; +DROP TABLE IF EXISTS xcstackSumSkyfile; SET FOREIGN_KEY_CHECKS=1