Index: trunk/ippTools/share/pxadmin_create_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_create_tables.sql (revision 17856) +++ trunk/ippTools/share/pxadmin_create_tables.sql (revision 17872) @@ -3,4 +3,5 @@ telescope VARCHAR(64), uri VARCHAR(255), + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(camera, telescope) ) ENGINE=innodb DEFAULT CHARSET=latin1; @@ -15,4 +16,5 @@ imfiles INT, fault SMALLINT NOT NULL, + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(exp_name, camera, telescope), KEY(fault)) @@ -29,4 +31,5 @@ class_id VARCHAR(64), uri VARCHAR(255), + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(exp_name, camera, telescope, class, class_id), KEY(file_id), @@ -40,4 +43,5 @@ telescope VARCHAR(64), state VARCHAR(64), + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(exp_name, camera, telescope), KEY(state), @@ -54,4 +58,5 @@ uri VARCHAR(255), fault SMALLINT NOT NULL, + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(exp_name, camera, telescope, class, class_id), KEY(fault), @@ -61,27 +66,538 @@ REFERENCES summitImfile(exp_name, camera, telescope, class, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE newExp (exp_id BIGINT AUTO_INCREMENT, tmp_exp_name VARCHAR(64), tmp_camera VARCHAR(64), tmp_telescope VARCHAR(64), state VARCHAR(64), workdir VARCHAR(255), workdir_state VARCHAR(64), reduction VARCHAR(64), dvodb VARCHAR(255), tess_id VARCHAR(64), end_stage VARCHAR(64), label VARCHAR(64), PRIMARY KEY(exp_id), KEY(exp_id), KEY(tmp_exp_name), KEY(tmp_camera), KEY(tmp_telescope), KEY(state), KEY(workdir_state), KEY(end_stage), KEY(label)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE newImfile (exp_id BIGINT, tmp_class_id VARCHAR(64), uri VARCHAR(255), PRIMARY KEY(exp_id, tmp_class_id), FOREIGN KEY (exp_id) REFERENCES newExp(exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE rawExp (exp_id BIGINT, exp_name VARCHAR(64), camera VARCHAR(64), telescope VARCHAR(64), dateobs DATETIME, exp_tag VARCHAR(255), exp_type VARCHAR(64), filelevel VARCHAR(64), workdir VARCHAR(255), reduction VARCHAR(64), dvodb VARCHAR(255), tess_id VARCHAR(64), end_stage VARCHAR(64), filter VARCHAR(64), comment VARCHAR(80), airmass FLOAT, ra DOUBLE, decl DOUBLE, exp_time FLOAT, sat_pixel_frac FLOAT, bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, alt DOUBLE, az DOUBLE, ccd_temp FLOAT, posang DOUBLE, m1_x FLOAT, m1_y FLOAT, m1_z FLOAT, m1_tip FLOAT, m1_tilt FLOAT, m2_x FLOAT, m2_y FLOAT, m2_z FLOAT, m2_tip FLOAT, m2_tilt FLOAT, env_temperature FLOAT, env_humidity FLOAT, env_wind_speed FLOAT, env_wind_dir FLOAT, teltemp_m1 FLOAT, teltemp_m1cell FLOAT, teltemp_m2 FLOAT, teltemp_spider FLOAT, teltemp_truss FLOAT, teltemp_extra FLOAT, pon_time FLOAT, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, object VARCHAR(64), solang FLOAT, hostname VARCHAR(64), fault SMALLINT NOT NULL, PRIMARY KEY(exp_id), KEY(exp_name), KEY(end_stage), KEY(fault), FOREIGN KEY (exp_id) REFERENCES newExp(exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE rawImfile (exp_id BIGINT, exp_name VARCHAR(64), camera VARCHAR(64), telescope VARCHAR(64), dateobs DATETIME, tmp_class_id VARCHAR(64), class_id VARCHAR(64), uri VARCHAR(255), exp_type VARCHAR(64), filelevel VARCHAR(64), filter VARCHAR(64), comment VARCHAR(80), airmass FLOAT, ra DOUBLE, decl DOUBLE, exp_time FLOAT, sat_pixel_frac FLOAT, bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, alt DOUBLE, az DOUBLE, ccd_temp FLOAT, posang DOUBLE, m1_x FLOAT, m1_y FLOAT, m1_z FLOAT, m1_tip FLOAT, m1_tilt FLOAT, m2_x FLOAT, m2_y FLOAT, m2_z FLOAT, m2_tip FLOAT, m2_tilt FLOAT, env_temperature FLOAT, env_humidity FLOAT, env_wind_speed FLOAT, env_wind_dir FLOAT, teltemp_m1 FLOAT, teltemp_m1cell FLOAT, teltemp_m2 FLOAT, teltemp_spider FLOAT, teltemp_truss FLOAT, teltemp_extra FLOAT, pon_time FLOAT, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, object VARCHAR(64), hostname VARCHAR(64), fault SMALLINT NOT NULL, PRIMARY KEY(exp_id, class_id), KEY(tmp_class_id), KEY(fault), UNIQUE KEY(exp_id, tmp_class_id), FOREIGN KEY (exp_id, tmp_class_id) REFERENCES newImfile(exp_id, tmp_class_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE newExp ( + exp_id BIGINT AUTO_INCREMENT, + tmp_exp_name VARCHAR(64), + tmp_camera VARCHAR(64), + tmp_telescope VARCHAR(64), + state VARCHAR(64), + workdir VARCHAR(255), + workdir_state VARCHAR(64), + reduction VARCHAR(64), + dvodb VARCHAR(255), + tess_id VARCHAR(64), + end_stage VARCHAR(64), + label VARCHAR(64), + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(exp_id), + KEY(exp_id), + KEY(tmp_exp_name), + KEY(tmp_camera), + KEY(tmp_telescope), + KEY(state), + KEY(workdir_state), + KEY(end_stage), + KEY(label) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE newImfile ( + exp_id BIGINT, + tmp_class_id VARCHAR(64), + uri VARCHAR(255), + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(exp_id, tmp_class_id), + FOREIGN KEY (exp_id) + REFERENCES newExp(exp_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE rawExp ( + exp_id BIGINT, + exp_name VARCHAR(64), + camera VARCHAR(64), + telescope VARCHAR(64), + dateobs DATETIME, + exp_tag VARCHAR(255), + exp_type VARCHAR(64), + filelevel VARCHAR(64), + workdir VARCHAR(255), + reduction VARCHAR(64), + dvodb VARCHAR(255), + tess_id VARCHAR(64), + end_stage VARCHAR(64), + filter VARCHAR(64), + comment VARCHAR(80), + airmass FLOAT, + ra DOUBLE, + decl DOUBLE, + exp_time FLOAT, + sat_pixel_frac FLOAT, + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + alt DOUBLE, + az DOUBLE, + ccd_temp FLOAT, + posang DOUBLE, + m1_x FLOAT, + m1_y FLOAT, + m1_z FLOAT, + m1_tip FLOAT, + m1_tilt FLOAT, + m2_x FLOAT, + m2_y FLOAT, + m2_z FLOAT, + m2_tip FLOAT, + m2_tilt FLOAT, + env_temperature FLOAT, + env_humidity FLOAT, + env_wind_speed FLOAT, + env_wind_dir FLOAT, + teltemp_m1 FLOAT, + teltemp_m1cell FLOAT, + teltemp_m2 FLOAT, + teltemp_spider FLOAT, + teltemp_truss FLOAT, + teltemp_extra FLOAT, + pon_time FLOAT, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + object VARCHAR(64), + solang FLOAT, + hostname VARCHAR(64), + fault SMALLINT NOT NULL, + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(exp_id), + KEY(exp_name), + KEY(end_stage), + KEY(fault), + FOREIGN KEY (exp_id) + REFERENCES newExp(exp_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE rawImfile ( + exp_id BIGINT, + exp_name VARCHAR(64), + camera VARCHAR(64), + telescope VARCHAR(64), + dateobs DATETIME, + tmp_class_id VARCHAR(64), + class_id VARCHAR(64), + uri VARCHAR(255), + exp_type VARCHAR(64), + filelevel VARCHAR(64), + filter VARCHAR(64), + comment VARCHAR(80), + airmass FLOAT, + ra DOUBLE, + decl DOUBLE, + exp_time FLOAT, + sat_pixel_frac FLOAT, + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + alt DOUBLE, + az DOUBLE, + ccd_temp FLOAT, + posang DOUBLE, + m1_x FLOAT, + m1_y FLOAT, + m1_z FLOAT, + m1_tip FLOAT, + m1_tilt FLOAT, + m2_x FLOAT, + m2_y FLOAT, + m2_z FLOAT, + m2_tip FLOAT, + m2_tilt FLOAT, + env_temperature FLOAT, + env_humidity FLOAT, + env_wind_speed FLOAT, + env_wind_dir FLOAT, + teltemp_m1 FLOAT, + teltemp_m1cell FLOAT, + teltemp_m2 FLOAT, + teltemp_spider FLOAT, + teltemp_truss FLOAT, + teltemp_extra FLOAT, + pon_time FLOAT, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + object VARCHAR(64), + hostname VARCHAR(64), + fault SMALLINT NOT NULL, + epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(exp_id, class_id), + KEY(tmp_class_id), + KEY(fault), + UNIQUE KEY(exp_id, tmp_class_id), + FOREIGN KEY (exp_id, tmp_class_id) + REFERENCES newImfile(exp_id, tmp_class_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + CREATE TABLE guidePendingExp (guide_id BIGINT AUTO_INCREMENT, exp_id BIGINT, recipe VARCHAR(64), PRIMARY KEY(guide_id), KEY(guide_id), KEY(exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE chipRun (chip_id BIGINT AUTO_INCREMENT, exp_id BIGINT, state VARCHAR(64), workdir VARCHAR(255), workdir_state VARCHAR(64), label VARCHAR(64), reduction VARCHAR(64), expgroup VARCHAR(64), dvodb VARCHAR(255), tess_id VARCHAR(64), end_stage VARCHAR(64), PRIMARY KEY(chip_id), KEY(chip_id), KEY(exp_id), KEY(state), KEY(workdir_state), KEY(label), KEY(expgroup), KEY(end_stage), INDEX(chip_id, exp_id), FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE chipProcessedImfile (chip_id BIGINT, exp_id BIGINT, class_id VARCHAR(64), uri VARCHAR(255), bg FLOAT, bg_stdev FLOAT, bg_mean_stdev FLOAT, bias FLOAT, bias_stdev FLOAT, fringe_0 FLOAT, fringe_1 FLOAT, fringe_2 FLOAT, sigma_ra FLOAT, sigma_dec FLOAT, ap_resid FLOAT, ap_resid_stdev FLOAT, zp_mean FLOAT, zp_stdev FLOAT, fwhm_major FLOAT, fwhm_minor FLOAT, dtime_detrend FLOAT, dtime_photom FLOAT, dtime_astrom FLOAT, hostname VARCHAR(64), n_stars INT, n_extended INT, n_cr INT, n_astrom INT, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(chip_id, exp_id, class_id), KEY(fault), FOREIGN KEY (chip_id, exp_id) REFERENCES chipRun(chip_id, exp_id), FOREIGN KEY (exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE chipMask (label VARCHAR(64), PRIMARY KEY(label)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE camRun (cam_id BIGINT AUTO_INCREMENT, chip_id BIGINT, state VARCHAR(64), workdir VARCHAR(255), workdir_state VARCHAR(64), label VARCHAR(64), reduction VARCHAR(64), expgroup VARCHAR(64), dvodb VARCHAR(255), tess_id VARCHAR(64), end_stage VARCHAR(64), PRIMARY KEY(cam_id), KEY(cam_id), KEY(chip_id), KEY(state), KEY(workdir_state), KEY(label), KEY(expgroup), KEY(end_stage), INDEX(cam_id, chip_id), FOREIGN KEY (chip_id) REFERENCES chipRun(chip_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE camProcessedExp (cam_id BIGINT, chip_id BIGINT, uri VARCHAR(255), bg FLOAT, bg_stdev FLOAT, bg_mean_stdev FLOAT, bias FLOAT, bias_stdev FLOAT, fringe_0 FLOAT, fringe_1 FLOAT, fringe_2 FLOAT, sigma_ra FLOAT, sigma_dec FLOAT, ap_resid FLOAT, ap_resid_stdev FLOAT, zp_mean FLOAT, zp_stdev FLOAT, fwhm_major FLOAT, fwhm_minor FLOAT, dtime_detrend FLOAT, dtime_photom FLOAT, dtime_astrom FLOAT, hostname VARCHAR(64), n_stars INT, n_extended INT, n_cr INT, n_astrom INT, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(cam_id, chip_id), KEY(fault), FOREIGN KEY (cam_id, chip_id) REFERENCES camRun(cam_id, chip_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE camMask (label VARCHAR(64), PRIMARY KEY(label)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detRun (det_id BIGINT AUTO_INCREMENT, iteration INT, det_type VARCHAR(64), mode VARCHAR(64), state VARCHAR(64), filelevel VARCHAR(64), workdir VARCHAR(255), camera VARCHAR(64), telescope VARCHAR(64), exp_type VARCHAR(64), reduction VARCHAR(64), filter VARCHAR(64), airmass_min FLOAT, airmass_max FLOAT, exp_time_min FLOAT, exp_time_max FLOAT, ccd_temp_min FLOAT, ccd_temp_max FLOAT, posang_min DOUBLE, posang_max DOUBLE, registered DATETIME, time_begin DATETIME, time_end DATETIME, use_begin DATETIME, use_end DATETIME, solang_min FLOAT, solang_max FLOAT, label VARCHAR(64), parent INT, PRIMARY KEY(det_id), KEY(det_id), KEY(iteration), KEY(det_type), KEY(mode), KEY(state), KEY(label), KEY(parent), INDEX(det_id, iteration)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detInputExp (det_id BIGINT, iteration INT, exp_id BIGINT, include TINYINT, PRIMARY KEY(det_id, iteration, exp_id), INDEX(det_id, exp_id), INDEX(det_id, iteration), FOREIGN KEY (det_id) REFERENCES detRun(det_id), FOREIGN KEY (exp_id) REFERENCES rawExp(exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detProcessedImfile (det_id BIGINT, exp_id BIGINT, class_id VARCHAR(64), uri VARCHAR(255), recipe VARCHAR(64), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, fringe_0 DOUBLE, fringe_1 DOUBLE, fringe_2 DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(det_id, exp_id, class_id), KEY(fault), INDEX(det_id, class_id), INDEX(det_id, exp_id), FOREIGN KEY (det_id, exp_id) REFERENCES detInputExp(det_id, exp_id), FOREIGN KEY (exp_id, class_id) REFERENCES rawImfile(exp_id, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detProcessedExp (det_id BIGINT, exp_id BIGINT, recipe VARCHAR(64), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, fringe_0 DOUBLE, fringe_1 DOUBLE, fringe_2 DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(det_id, exp_id), KEY(fault), FOREIGN KEY (det_id, exp_id) REFERENCES detInputExp(det_id, exp_id), FOREIGN KEY (det_id, exp_id) REFERENCES detProcessedImfile(det_id, exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detStackedImfile (det_id BIGINT, iteration INT, class_id VARCHAR(64), uri VARCHAR(255), recipe VARCHAR(64), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration, class_id), KEY(fault), FOREIGN KEY (det_id, iteration) REFERENCES detInputExp(det_id, iteration), FOREIGN KEY (det_id, class_id) REFERENCES detProcessedImfile(det_id, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detNormalizedStatImfile (det_id BIGINT, iteration INT, class_id VARCHAR(64), norm FLOAT, fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration, class_id), KEY(fault), FOREIGN KEY (det_id, iteration) REFERENCES detInputExp(det_id, iteration), FOREIGN KEY (det_id, iteration, class_id) REFERENCES detStackedImfile(det_id, iteration, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detNormalizedImfile (det_id BIGINT, iteration INT, class_id VARCHAR(64), uri VARCHAR(255), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration, class_id), KEY(fault), INDEX(det_id, iteration), FOREIGN KEY (det_id) REFERENCES detInputExp(det_id), FOREIGN KEY (det_id, iteration, class_id) REFERENCES detNormalizedStatImfile(det_id, iteration, class_id)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detNormalizedExp (det_id BIGINT, iteration INT, recipe VARCHAR(64), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration), KEY(fault), FOREIGN KEY (det_id, iteration) REFERENCES detInputExp(det_id, iteration), FOREIGN KEY (det_id, iteration) REFERENCES detNormalizedImfile(det_id, iteration)) ENGINE=innodb DEFAULT CHARSET=latin1; -CREATE TABLE detResidImfile (det_id BIGINT, iteration INT, exp_id BIGINT, class_id VARCHAR(64), uri VARCHAR(255), recipe VARCHAR(64), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, bg_skewness DOUBLE, bg_kurtosis DOUBLE, bin_stdev DOUBLE, fringe_0 DOUBLE, fringe_1 DOUBLE, fringe_2 DOUBLE, fringe_resid_0 DOUBLE, fringe_resid_1 DOUBLE, fringe_resid_2 DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration, exp_id, class_id), KEY(fault), INDEX(det_id, iteration, exp_id), FOREIGN KEY (det_id, iteration, exp_id) REFERENCES detInputExp(det_id, iteration, exp_id), FOREIGN KEY (det_id, exp_id, class_id) REFERENCES detProcessedImfile(det_id, exp_id, class_id), FOREIGN KEY (det_id, iteration) REFERENCES detNormalizedExp(det_id, iteration)) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE chipRun ( + chip_id BIGINT AUTO_INCREMENT, + exp_id BIGINT, + state VARCHAR(64), + workdir VARCHAR(255), + workdir_state VARCHAR(64), + label VARCHAR(64), + reduction VARCHAR(64), + expgroup VARCHAR(64), + dvodb VARCHAR(255), + tess_id VARCHAR(64), + end_stage VARCHAR(64), + PRIMARY KEY(chip_id), + KEY(chip_id), KEY(exp_id), + KEY(state), + KEY(workdir_state), + KEY(label), + KEY(expgroup), + KEY(end_stage), + INDEX(chip_id, exp_id), + FOREIGN KEY (exp_id) + REFERENCES rawExp(exp_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE chipProcessedImfile ( + chip_id BIGINT, + exp_id BIGINT, + class_id VARCHAR(64), + uri VARCHAR(255), + bg FLOAT, + bg_stdev FLOAT, + bg_mean_stdev FLOAT, + bias FLOAT, + bias_stdev FLOAT, + fringe_0 FLOAT, + fringe_1 FLOAT, + fringe_2 FLOAT, + sigma_ra FLOAT, + sigma_dec FLOAT, + ap_resid FLOAT, + ap_resid_stdev FLOAT, + zp_mean FLOAT, + zp_stdev FLOAT, + fwhm_major FLOAT, + fwhm_minor FLOAT, + dtime_detrend FLOAT, + dtime_photom FLOAT, + dtime_astrom FLOAT, + hostname VARCHAR(64), + n_stars INT, + n_extended INT, + n_cr INT, + n_astrom INT, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(chip_id, exp_id, class_id), + KEY(fault), + FOREIGN KEY (chip_id, exp_id) + REFERENCES chipRun(chip_id, exp_id), + FOREIGN KEY (exp_id, class_id) + REFERENCES rawImfile(exp_id, class_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE chipMask ( + label VARCHAR(64), + PRIMARY KEY(label)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE camRun ( + cam_id BIGINT AUTO_INCREMENT, + chip_id BIGINT, + state VARCHAR(64), + workdir VARCHAR(255), + workdir_state VARCHAR(64), + label VARCHAR(64), + reduction VARCHAR(64), + expgroup VARCHAR(64), + dvodb VARCHAR(255), + tess_id VARCHAR(64), + end_stage VARCHAR(64), + PRIMARY KEY(cam_id), + KEY(cam_id), + KEY(chip_id), + KEY(state), + KEY(workdir_state), + KEY(label), + KEY(expgroup), + KEY(end_stage), + INDEX(cam_id, chip_id), + FOREIGN KEY (chip_id) + REFERENCES chipRun(chip_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE camProcessedExp ( + cam_id BIGINT, + chip_id BIGINT, + uri VARCHAR(255), + bg FLOAT, + bg_stdev FLOAT, + bg_mean_stdev FLOAT, + bias FLOAT, + bias_stdev FLOAT, + fringe_0 FLOAT, + fringe_1 FLOAT, + fringe_2 FLOAT, + sigma_ra FLOAT, + sigma_dec FLOAT, + ap_resid FLOAT, + ap_resid_stdev FLOAT, + zp_mean FLOAT, + zp_stdev FLOAT, + fwhm_major FLOAT, + fwhm_minor FLOAT, + dtime_detrend FLOAT, + dtime_photom FLOAT, + dtime_astrom FLOAT, + hostname VARCHAR(64), + n_stars INT, + n_extended INT, + n_cr INT, + n_astrom INT, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(cam_id, chip_id), + KEY(fault), + FOREIGN KEY (cam_id, chip_id) + REFERENCES camRun(cam_id, chip_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE camMask ( + label VARCHAR(64), + PRIMARY KEY(label)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detRun ( + det_id BIGINT AUTO_INCREMENT, + iteration INT, + det_type VARCHAR(64), + mode VARCHAR(64), + state VARCHAR(64), + filelevel VARCHAR(64), + workdir VARCHAR(255), + camera VARCHAR(64), + telescope VARCHAR(64), + exp_type VARCHAR(64), + reduction VARCHAR(64), + filter VARCHAR(64), + airmass_min FLOAT, + airmass_max FLOAT, + exp_time_min FLOAT, + exp_time_max FLOAT, + ccd_temp_min FLOAT, + ccd_temp_max FLOAT, + posang_min DOUBLE, + posang_max DOUBLE, + registered DATETIME, + time_begin DATETIME, + time_end DATETIME, + use_begin DATETIME, + use_end DATETIME, + solang_min FLOAT, + solang_max FLOAT, + label VARCHAR(64), + parent INT, + PRIMARY KEY(det_id), + KEY(det_id), + KEY(iteration), + KEY(det_type), + KEY(mode), + KEY(state), + KEY(label), + KEY(parent), + INDEX(det_id, iteration)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detInputExp ( +det_id BIGINT, +iteration INT, +exp_id BIGINT, +include TINYINT, +PRIMARY KEY(det_id, iteration, exp_id), +INDEX(det_id, exp_id), +INDEX(det_id, iteration), +FOREIGN KEY (det_id) +REFERENCES detRun(det_id), +FOREIGN KEY (exp_id) +REFERENCES rawExp(exp_id)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detProcessedImfile ( + det_id BIGINT, + exp_id BIGINT, + class_id VARCHAR(64), + uri VARCHAR(255), + recipe VARCHAR(64), + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + fringe_0 DOUBLE, + fringe_1 DOUBLE, + fringe_2 DOUBLE, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(det_id, exp_id, class_id), + KEY(fault), + INDEX(det_id, class_id), + INDEX(det_id, exp_id), + FOREIGN KEY (det_id, exp_id) + REFERENCES detInputExp(det_id, exp_id), + FOREIGN KEY (exp_id, class_id) + REFERENCES rawImfile(exp_id, class_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detProcessedExp ( + det_id BIGINT, + exp_id BIGINT, + recipe VARCHAR(64), + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + fringe_0 DOUBLE, + fringe_1 DOUBLE, + fringe_2 DOUBLE, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(det_id, exp_id), + KEY(fault), + FOREIGN KEY (det_id, exp_id) + REFERENCES detInputExp(det_id, exp_id), + FOREIGN KEY (det_id, exp_id) + REFERENCES detProcessedImfile(det_id, exp_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detStackedImfile ( + det_id BIGINT, + iteration INT, + class_id VARCHAR(64), + uri VARCHAR(255), + recipe VARCHAR(64), + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + fault SMALLINT NOT NULL, + PRIMARY KEY(det_id, iteration, class_id), + KEY(fault), + FOREIGN KEY (det_id, iteration) + REFERENCES detInputExp(det_id, iteration), + FOREIGN KEY (det_id, class_id) + REFERENCES detProcessedImfile(det_id, class_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detNormalizedStatImfile ( + det_id BIGINT, + iteration INT, + class_id VARCHAR(64), + norm FLOAT, + fault SMALLINT NOT NULL, + PRIMARY KEY(det_id, iteration, class_id), + KEY(fault), + FOREIGN KEY (det_id, iteration) + REFERENCES detInputExp(det_id, iteration), + FOREIGN KEY (det_id, iteration, class_id) + REFERENCES detStackedImfile(det_id, iteration, class_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detNormalizedImfile ( + det_id BIGINT, + iteration INT, + class_id VARCHAR(64), + uri VARCHAR(255), + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(det_id, iteration, class_id), + KEY(fault), + INDEX(det_id, iteration), + FOREIGN KEY (det_id) + REFERENCES detInputExp(det_id), + FOREIGN KEY (det_id, iteration, class_id) + REFERENCES detNormalizedStatImfile(det_id, iteration, class_id) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detNormalizedExp ( + det_id BIGINT, + iteration INT, + recipe VARCHAR(64), + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(det_id, iteration), + KEY(fault), + FOREIGN KEY (det_id, iteration) + REFERENCES detInputExp(det_id, iteration), + FOREIGN KEY (det_id, iteration) + REFERENCES detNormalizedImfile(det_id, iteration) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE detResidImfile ( + det_id BIGINT, + iteration INT, + exp_id BIGINT, + class_id VARCHAR(64), + uri VARCHAR(255), + recipe VARCHAR(64), + bg DOUBLE, + bg_stdev DOUBLE, + bg_mean_stdev DOUBLE, + bg_skewness DOUBLE, + bg_kurtosis DOUBLE, + bin_stdev DOUBLE, + fringe_0 DOUBLE, + fringe_1 DOUBLE, + fringe_2 DOUBLE, + fringe_resid_0 DOUBLE, + fringe_resid_1 DOUBLE, + fringe_resid_2 DOUBLE, + user_1 DOUBLE, + user_2 DOUBLE, + user_3 DOUBLE, + user_4 DOUBLE, + user_5 DOUBLE, + path_base VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(det_id, iteration, exp_id, class_id), + KEY(fault), + INDEX(det_id, iteration, exp_id), + FOREIGN KEY (det_id, iteration, exp_id) + REFERENCES detInputExp(det_id, iteration, exp_id), + FOREIGN KEY (det_id, exp_id, class_id) + REFERENCES detProcessedImfile(det_id, exp_id, class_id), + FOREIGN KEY (det_id, iteration) + REFERENCES detNormalizedExp(det_id, iteration) +) ENGINE=innodb DEFAULT CHARSET=latin1; + CREATE TABLE detResidExp (det_id BIGINT, iteration INT, exp_id BIGINT, recipe VARCHAR(64), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, bg_skewness DOUBLE, bg_kurtosis DOUBLE, bin_stdev DOUBLE, fringe_0 DOUBLE, fringe_1 DOUBLE, fringe_2 DOUBLE, fringe_resid_0 DOUBLE, fringe_resid_1 DOUBLE, fringe_resid_2 DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, path_base VARCHAR(255), accept TINYINT, fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration, exp_id), KEY(fault), INDEX(det_id, iteration), FOREIGN KEY (det_id, iteration, exp_id) REFERENCES detInputExp(det_id, iteration, exp_id), FOREIGN KEY (det_id, iteration, exp_id) REFERENCES detResidImfile(det_id, iteration, exp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE detRunSummary (det_id BIGINT, iteration INT, bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, accept TINYINT, fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration), KEY(fault), FOREIGN KEY (det_id, iteration) REFERENCES detInputExp(det_id, iteration), FOREIGN KEY (det_id, iteration) REFERENCES detResidExp(det_id, iteration)) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE detRegisteredImfile (det_id BIGINT, iteration INT, class_id VARCHAR(64), uri VARCHAR(255), bg DOUBLE, bg_stdev DOUBLE, bg_mean_stdev DOUBLE, user_1 DOUBLE, user_2 DOUBLE, user_3 DOUBLE, user_4 DOUBLE, user_5 DOUBLE, path_base VARCHAR(255), fault SMALLINT NOT NULL, PRIMARY KEY(det_id, iteration, class_id), KEY(fault), FOREIGN KEY (det_id, iteration) REFERENCES detRun(det_id, iteration)) ENGINE=innodb DEFAULT CHARSET=latin1; + CREATE TABLE warpRun (warp_id BIGINT AUTO_INCREMENT, cam_id BIGINT, mode VARCHAR(64), state VARCHAR(64), workdir VARCHAR(255), workdir_state VARCHAR(64), label VARCHAR(64), dvodb VARCHAR(255), tess_id VARCHAR(64), end_stage VARCHAR(64), registered DATETIME, magiced TINYINT, PRIMARY KEY(warp_id), KEY(warp_id), KEY(cam_id), KEY(mode), KEY(state), KEY(workdir_state), KEY(label), KEY(end_stage), KEY(magiced), INDEX(warp_id, cam_id), FOREIGN KEY (cam_id) REFERENCES camProcessedExp(cam_id)) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE warpSkyCellMap (warp_id BIGINT, skycell_id VARCHAR(64), tess_id VARCHAR(64), cam_id BIGINT, class_id VARCHAR(64), fault SMALLINT, PRIMARY KEY(warp_id, skycell_id, tess_id, cam_id, class_id), KEY(fault), FOREIGN KEY (warp_id, cam_id) REFERENCES warpRun(warp_id, cam_id)) ENGINE=innodb DEFAULT CHARSET=latin1;