Index: /trunk/dbconfig/changes.txt =================================================================== --- /trunk/dbconfig/changes.txt (revision 42832) +++ /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; + + Index: /trunk/dbconfig/ff.md =================================================================== --- /trunk/dbconfig/ff.md (revision 42832) +++ /trunk/dbconfig/ff.md (revision 42833) @@ -1,3 +1,4 @@ +## why is ff_id not marked here for AUTO_INCREMENT? fullForceRun METADATA ff_id S64 0 Index: /trunk/dbconfig/ipp.m4 =================================================================== --- /trunk/dbconfig/ipp.m4 (revision 42832) +++ /trunk/dbconfig/ipp.m4 (revision 42833) @@ -43,2 +43,5 @@ include(remote.md) include(fpcam.md) +include(xcstack.md) +include(xcsky.md) +include(xcff.md) Index: /trunk/dbconfig/notes.txt =================================================================== --- /trunk/dbconfig/notes.txt (revision 42832) +++ /trunk/dbconfig/notes.txt (revision 42833) @@ -1,2 +1,18 @@ + +2023.12.24 : EAM + for reference, when adding a new stage: + + * the stage tables need to be added in dbconfig + new file foo.md + added to ipp.m4 + make + make install + + * then in ippdb.src + psautogen + make + make install + + * then the supporting stage tools can be added to ippTools and built 2007.06.08 : EAM Index: /trunk/dbconfig/xcadd.md =================================================================== --- /trunk/dbconfig/xcadd.md (revision 42833) +++ /trunk/dbconfig/xcadd.md (revision 42833) @@ -0,0 +1,35 @@ +# XXX rename these 'addstarRun', etc... (add_id -> addstar_id) +addRun METADATA + add_id S64 0 # Primary Key AUTO_INCREMENT + stage STR 64 # what the stage is (warp, cam, diff, + stage_id S64 0 # Key INDEX(add_id,cam_id) fkey(cam_id) ref camRun(cam_id) + stage_extra1 S32 0 + state STR 64 # Key + workdir STR 255 + workdir_state STR 64 + reduction STR 64 + label STR 64 + data_group STR 64 # Key + dvodb STR 255 + note STR 255 + image_only BOOL f + minidvodb BOOL f + minidvodb_group STR 64 + minidvodb_name STR 64 + minidvodb_host STR 64 +END + +addProcessedExp METADATA + add_id S64 0 # Primary Key AUTO_INCREMENT + dtime_addstar F32 0.0 + path_base STR 255 + dvodb_path STR 255 + fault S16 0 # Key NOT NULL +END + +addMask METADATA + label STR 64 # Primary Key +END + + + Index: /trunk/dbconfig/xcff.md =================================================================== --- /trunk/dbconfig/xcff.md (revision 42833) +++ /trunk/dbconfig/xcff.md (revision 42833) @@ -0,0 +1,52 @@ + +# there can be xcForceRun entries for the same xcstackRun in multiple cameras +# xcForceRun should be defined in camera database which owns the warps +# the xcForceRun connects the xcstackRun in the camera database for +# where the xcstackRun took place to the stackRun used to generate +# that stack in this camera database + +# do I need filter here as well? +xcForceRun METADATA + xcff_id S64 0 + xccal_id S64 0 + sources_path_base STR 255 # path to cmf file generated by xccal + state STR 64 + workdir STR 255 + label STR 64 + skycell_id STR 64 # Key + tess_id STR 64 # Key + data_group STR 64 + dist_group STR 64 + note STR 255 + reduction STR 64 + registered TAI NULL +END + +# join to xcstackInputSkyfile to get the camera_id, stack_id for this xcstack +xcForceInput METADATA + xcff_id S64 0 + warp_id S64 0 + camera_id S64 0 # ID of camera database used for the xcstackRun +END + +xcForceResult METADATA + xcff_id S64 0 + warp_id S64 0 + path_base STR 255 + dtime_script F32 0.0 + quality S16 0 + hostname STR 64 + software_ver STR 16 + fault S16 0 +END + +xcForceSummary METADATA + xcff_id S64 0 + path_base STR 255 + dtime_script F32 0.0 + quality S16 0 + hostname STR 64 + software_ver STR 16 + fault S16 0 +END + Index: /trunk/dbconfig/xcsky.md =================================================================== --- /trunk/dbconfig/xcsky.md (revision 42833) +++ /trunk/dbconfig/xcsky.md (revision 42833) @@ -0,0 +1,67 @@ +# Tables for static sky analysis +# XXX add tess_id and skycell_id here? + +xcskyRun METADATA + xcsky_id S64 0 # Primary Key AUTO_INCREMENT + state STR 64 # Key + workdir STR 255 + label STR 64 # Key + data_group STR 64 # Key + dist_group STR 64 # Key + reduction STR 64 # Reduction class + registered TAI NULL + note STR 255 +END + +xcskyInput METADATA + xcsky_id S64 0 # Primary Key fkey(xcsky_id) ref xcskyRun(xcsky_id) + xcstack_id S64 0 # Primary Key fkey(xcstack_id) ref xcstackSumSkyfile(xcstack_id) +END + +xcskyResult METADATA + xcsky_id S64 0 # Primary Key fkey(xcsky_id) ref xcskyRun(xcsky_id) + path_base STR 255 + dtime_phot F32 0.0 + dtime_script F32 0.0 + sources S32 0 + num_inputs S32 0 + hostname STR 64 + good_frac F32 0.0 # Key + fault S16 0 # Key + quality S16 0 # Key +END + +xccalRun METADATA + xccal_id S64 0 # Primary Key AUTO_INCREMENT + xcsky_id S64 0 # fkey(xcsky_id) ref xcskyRun(xcsky_id) + xcstack_id S64 0 # fkey(xcstack_id) ref xcstackRun(xcstack_id) + state STR 64 # Key + workdir STR 255 + label STR 64 # Key + data_group STR 64 # Key + dist_group STR 64 # Key + reduction STR 64 # Reduction class + registered TAI NULL + note STR 255 +END + +xccalResult METADATA + xccal_id S64 0 # Primary Key fkey(xccal_id) ref xccalRun(xccal_id) + path_base STR 255 + dtime_script F32 0.0 + dtime_astrom F32 0.0 + sigma_ra F32 0.0 + sigma_dec F32 0.0 + n_astrom S32 0 + n_detections S32 0 + n_extended S32 0 + n_forced S32 0 + zpt_obs F32 0.0 + zpt_stdev F32 0.0 + fwhm_major F32 0.0 + fwhm_minor F32 0.0 + quality S16 0 # Key + software_ver STR 16 + hostname STR 64 + fault S16 0 # Key +END Index: /trunk/dbconfig/xcstack.md =================================================================== --- /trunk/dbconfig/xcstack.md (revision 42833) +++ /trunk/dbconfig/xcstack.md (revision 42833) @@ -0,0 +1,42 @@ + +xcstackRun METADATA + xcstack_id S64 0 # Primary Key AUTO_INCREMENT + state STR 64 # Key + workdir STR 255 + label STR 64 # Key + data_group STR 64 # Key + dist_group STR 64 # Key + reduction STR 64 # Reduction class + registered TAI NULL + skycell_id STR 64 # Key + tess_id STR 64 # Key + filter STR 64 + software_ver STR 16 + note STR 255 +END + +xcstackInputSkyfile METADATA + xcstack_id S64 0 # Primary Key fkey(xcstack_id) ref xcstackRun(xcstack_id) + camera_id S64 0 # Primary Key fkey(warp_id) ref warpSkyfile(warp_id) + stack_id S64 0 +END + +xcstackSumSkyfile METADATA + xcstack_id S64 0 # Primary Key fkey(xcstack_id) ref xcstackRun(xcstack_id) + path_base STR 255 + bg F64 0.0 + bg_stdev F64 0.0 + dtime_stack F32 0.0 # Key + dtime_script F32 0.0 + hostname STR 64 + good_frac F32 0.0 # Key + fault S16 0 # Key + software_ver STR 16 + quality S16 0 +END + +xcstackCamera METADATA + camera_id S64 0 # Primary Key AUTO_INCREMENT + camera STR 64 +END +