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 17856) @@ -0,0 +1,108 @@ +CREATE TABLE pzDataStore ( + camera VARCHAR(64), + telescope VARCHAR(64), + uri VARCHAR(255), + PRIMARY KEY(camera, telescope) +) ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE summitExp ( + exp_name VARCHAR(64), + camera VARCHAR(64), + telescope VARCHAR(64), + dateobs DATETIME, + exp_type VARCHAR(64), + uri VARCHAR(255), + imfiles INT, + fault SMALLINT NOT NULL, + PRIMARY KEY(exp_name, camera, telescope), + KEY(fault)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE summitImfile ( + exp_name VARCHAR(64), + camera VARCHAR(64), + telescope VARCHAR(64), + file_id VARCHAR(64), + bytes INT, + md5sum VARCHAR(32), + class VARCHAR(64), + class_id VARCHAR(64), + uri VARCHAR(255), + PRIMARY KEY(exp_name, camera, telescope, class, class_id), + KEY(file_id), + FOREIGN KEY (exp_name, camera, telescope) + REFERENCES summitExp(exp_name, camera, telescope)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pzDownloadExp ( + exp_name VARCHAR(64), + camera VARCHAR(64), + telescope VARCHAR(64), + state VARCHAR(64), + PRIMARY KEY(exp_name, camera, telescope), + KEY(state), + FOREIGN KEY (exp_name, camera, telescope) + REFERENCES summitExp(exp_name, camera, telescope)) +ENGINE=innodb DEFAULT CHARSET=latin1; + +CREATE TABLE pzDownloadImfile ( + exp_name VARCHAR(64), + camera VARCHAR(64), + telescope VARCHAR(64), + class VARCHAR(64), + class_id VARCHAR(64), + uri VARCHAR(255), + fault SMALLINT NOT NULL, + PRIMARY KEY(exp_name, camera, telescope, class, class_id), + KEY(fault), + FOREIGN KEY (exp_name, camera, telescope) + REFERENCES pzDownloadExp(exp_name, camera, telescope), + FOREIGN KEY (exp_name, camera, telescope, class, class_id) + 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 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 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; +CREATE TABLE warpSkyfile (warp_id BIGINT, skycell_id VARCHAR(64), tess_id VARCHAR(64), uri VARCHAR(255), path_base VARCHAR(255), bg DOUBLE, bg_stdev DOUBLE, dtime_warp FLOAT, hostname VARCHAR(64), good_frac FLOAT, xmin INT, xmax INT, ymin INT, ymax INT, ignored TINYINT, fault SMALLINT, PRIMARY KEY(warp_id, skycell_id, tess_id), KEY(good_frac), KEY(ignored), KEY(fault), FOREIGN KEY (warp_id, skycell_id, tess_id) REFERENCES warpSkyCellMap(warp_id, skycell_id, tess_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE warpMask (label VARCHAR(64), PRIMARY KEY(label)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE stackRun (stack_id BIGINT AUTO_INCREMENT, state VARCHAR(64), workdir VARCHAR(255), dvodb VARCHAR(255), registered DATETIME, skycell_id VARCHAR(64), tess_id VARCHAR(64), PRIMARY KEY(stack_id), KEY(stack_id), KEY(state), KEY(skycell_id), KEY(tess_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE stackInputSkyfile (stack_id BIGINT, warp_id BIGINT, PRIMARY KEY(stack_id, warp_id), FOREIGN KEY (stack_id) REFERENCES stackRun(stack_id), FOREIGN KEY (warp_id) REFERENCES warpSkyfile(warp_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE stackSumSkyfile (stack_id BIGINT, uri VARCHAR(255), path_base VARCHAR(255), bg DOUBLE, bg_stdev DOUBLE, dtime_stack FLOAT, hostname VARCHAR(64), good_frac FLOAT, fault SMALLINT, PRIMARY KEY(stack_id), KEY(dtime_stack), KEY(good_frac), KEY(fault), FOREIGN KEY (stack_id) REFERENCES stackRun(stack_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE diffRun (diff_id BIGINT AUTO_INCREMENT, state VARCHAR(64), workdir VARCHAR(255), dvodb VARCHAR(255), registered DATETIME, skycell_id VARCHAR(64), tess_id VARCHAR(64), PRIMARY KEY(diff_id), KEY(diff_id), KEY(state), KEY(skycell_id), KEY(tess_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE diffInputSkyfile (diff_id BIGINT, template TINYINT, stack_id BIGINT, warp_id BIGINT, skycell_id VARCHAR(64), tess_id VARCHAR(64), kind VARCHAR(64), PRIMARY KEY(diff_id, template), KEY(stack_id), KEY(warp_id), KEY(skycell_id), KEY(tess_id), KEY(kind), FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id), FOREIGN KEY (stack_id) REFERENCES stackSumSkyfile(stack_id), FOREIGN KEY (warp_id, skycell_id, tess_id) REFERENCES warpSkyfile(warp_id, skycell_id, tess_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE diffSkyfile (diff_id BIGINT, uri VARCHAR(255), path_base VARCHAR(255), bg DOUBLE, bg_stdev DOUBLE, stamps_num INT, stamps_rms FLOAT, sources INT, dtime_diff FLOAT, hostname VARCHAR(64), good_frac FLOAT, fault SMALLINT, PRIMARY KEY(diff_id), KEY(good_frac), KEY(fault), FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE magicRun (magic_id BIGINT AUTO_INCREMENT, state VARCHAR(64), workdir VARCHAR(255), workdir_state VARCHAR(255), label VARCHAR(64), dvodb VARCHAR(255), registered DATETIME, PRIMARY KEY(magic_id), KEY(magic_id), KEY(state), KEY(workdir_state), KEY(label)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE magicInputSkyfile (magic_id BIGINT, diff_id BIGINT, node VARCHAR(64), PRIMARY KEY(magic_id, diff_id), FOREIGN KEY (magic_id) REFERENCES magicRun(magic_id), FOREIGN KEY (diff_id) REFERENCES diffRun(diff_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE magicTree (magic_id BIGINT, node VARCHAR(64), dep VARCHAR(64), KEY(magic_id), KEY(node), KEY(dep), INDEX(magic_id, node), FOREIGN KEY (magic_id) REFERENCES magicRun(magic_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE magicNodeResult (magic_id BIGINT, node VARCHAR(64), uri VARCHAR(255), PRIMARY KEY(magic_id, node), FOREIGN KEY (magic_id) REFERENCES magicRun(magic_id), FOREIGN KEY (magic_id, node) REFERENCES magicTree(magic_id, node)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE magicMask (magic_id BIGINT, uri VARCHAR(255), PRIMARY KEY(magic_id), FOREIGN KEY (magic_id) REFERENCES magicRun(magic_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE magicSkyfileMask (magic_id BIGINT, diff_id BIGINT, uri VARCHAR(255), PRIMARY KEY(magic_id, diff_id), KEY(uri), FOREIGN KEY (magic_id) REFERENCES magicRun(magic_id), FOREIGN KEY (magic_id, diff_id) REFERENCES magicInputSkyfile(magic_id, diff_id), FOREIGN KEY (magic_id) REFERENCES magicMask(magic_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE calDB (cal_id BIGINT AUTO_INCREMENT, dvodb VARCHAR(64), state VARCHAR(64), PRIMARY KEY(cal_id), KEY(cal_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE calRun (cal_id BIGINT AUTO_INCREMENT, region VARCHAR(64), last_step VARCHAR(64), state VARCHAR(64), PRIMARY KEY(cal_id), KEY(cal_id), KEY(last_step), FOREIGN KEY (cal_id) REFERENCES calDB(cal_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE flatcorrRun (corr_id BIGINT AUTO_INCREMENT, dvodb VARCHAR(64), filter VARCHAR(64), state VARCHAR(64), workdir VARCHAR(255), label VARCHAR(64), stats VARCHAR(255), region VARCHAR(255), PRIMARY KEY(corr_id), KEY(corr_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE flatcorrExp (corr_id BIGINT, chip_id BIGINT, PRIMARY KEY(corr_id, chip_id), FOREIGN KEY (corr_id) REFERENCES flatcorrRun(corr_id), FOREIGN KEY (chip_id) REFERENCES chipRun(chip_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE pstampDataStore (ds_id BIGINT AUTO_INCREMENT, state VARCHAR(64), lastFileset VARCHAR(64), outProduct VARCHAR(64) UNIQUE, uri VARCHAR(255), PRIMARY KEY(ds_id), KEY(ds_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE pstampRequest (req_id BIGINT AUTO_INCREMENT, ds_id BIGINT, state VARCHAR(64), outFileset VARCHAR(64) UNIQUE, uri VARCHAR(255), resultsFile SMALLINT, PRIMARY KEY(req_id), KEY(req_id)) ENGINE=innodb DEFAULT CHARSET=latin1; +CREATE TABLE pstampJob (job_id BIGINT AUTO_INCREMENT, req_id BIGINT, state VARCHAR(64), jobType VARCHAR(16), result INT, uri VARCHAR(255), outputBase VARCHAR(255), args VARCHAR(511), PRIMARY KEY(job_id, req_id), KEY(job_id), FOREIGN KEY (req_id) REFERENCES pstampRequest(req_id)) ENGINE=innodb DEFAULT CHARSET=latin1