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 Index: trunk/ippTools/src/Makefile.am =================================================================== --- trunk/ippTools/src/Makefile.am (revision 17855) +++ trunk/ippTools/src/Makefile.am (revision 17856) @@ -68,5 +68,4 @@ pxio.c \ pxregister.c \ - pxtables.c \ pxtag.c \ pxtoolsErrorCodes.c \ Index: trunk/ippTools/src/pxadmin.c =================================================================== --- trunk/ippTools/src/pxadmin.c (revision 17855) +++ trunk/ippTools/src/pxadmin.c (revision 17856) @@ -28,4 +28,5 @@ #include "pxadmin.h" +bool createMode(pxConfig *config); bool deleteMode(pxConfig *config); static bool runMultipleStatments(pxConfig *config, const char *query); @@ -48,5 +49,5 @@ // fall through case PXADMIN_MODE_CREATE: - if (!pxCreateTables(config)) { + if (!createMode(config)) { goto FAIL; } @@ -79,7 +80,74 @@ } + +bool createMode(pxConfig *config) +{ + PS_ASSERT_PTR_NON_NULL(config, false); + + psString query = pxDataGet("pxadmin_create_tables.sql"); + if (!query) { + psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement"); + return false; + } + + // BEGIN + if (!psDBTransaction(config->dbh)) { + psError(PS_ERR_UNKNOWN, false, "database error"); + return false; + } + + if (!runMultipleStatments(config, query)) { + if (!psDBRollback(config->dbh)) { + psError(PS_ERR_UNKNOWN, false, "database error"); + } + psError(PS_ERR_UNKNOWN, false, "database error"); + psFree(query); + return false; + } + psFree(query); + + // COMMIT + if (!psDBCommit(config->dbh)) { + psError(PS_ERR_UNKNOWN, false, "database error"); + return false; + } + + return true; +} + + bool deleteMode(pxConfig *config) { PS_ASSERT_PTR_NON_NULL(config, false); + + { + bool status; + char line[128], answer[128]; + + // XXX use the values defined in config->dbh? + psString dbName = psMetadataLookupStr(&status, config->modules->complete, "DBNAME"); + + fprintf (stdout, "*** delete the chip tables from database %s? ***\n", dbName); + fprintf (stdout, "*** to delete the tables, answer YES, and give password ***\n"); + fprintf (stdout, "*** WARNING: this action is permanent ***\n\n"); + + fprintf (stdout, "delete the chip tables (YES/[n]): "); + fgets (line, 128, stdin); + sscanf (line, "%s", answer); + if (strcmp (answer, "YES")) { + psError(PS_ERR_UNKNOWN, true, "tables NOT deleleted"); + return false; + } + + fprintf (stdout, "enter dbh connection password: "); + fgets (line, 128, stdin); + sscanf (line, "%s", answer); + + psString dbPassword = psMetadataLookupStr(&status, config->modules->complete, "DBPASSWORD"); + if (strcmp (answer, dbPassword)) { + psError(PS_ERR_UNKNOWN, true, "invalid passwd - tables NOT deleleted"); + return false; + } + } psString query = pxDataGet("pxadmin_drop_tables.sql"); @@ -113,4 +181,5 @@ return true; } + static bool runMultipleStatments(pxConfig *config, const char *query) Index: trunk/ippTools/src/pxtables.c =================================================================== --- trunk/ippTools/src/pxtables.c (revision 17855) +++ (revision ) @@ -1,220 +1,0 @@ -/* - * pxtables.c - * - * Copyright (C) 2006 Joshua Hoblitt - * - * This program is free software; you can redistribute it and/or modify it - * under the terms of the GNU General Public License as published by the Free - * Software Foundation; version 2 of the License. - * - * This program is distributed in the hope that it will be useful, but WITHOUT - * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or - * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for - * more details. - * - * You should have received a copy of the GNU General Public License along with - * program; see the file COPYING. If not, write to the Free Software - * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA. - */ - -#ifdef HAVE_CONFIG_H -#include -#endif - -#include -#include -#include -#include "pxtools.h" - -#define CREATE_TABLE(createFunc) \ - if (!createFunc(config->dbh)) { \ - psError(PS_ERR_UNKNOWN, false, "dbh access failed"); \ - if (status) { \ - status = false; \ - } \ - } - -#define DROP_TABLE(dropFunc) \ - if (!dropFunc(config->dbh)) { \ - psError(PS_ERR_UNKNOWN, false, "dbh access failed"); \ - if (status) { \ - status = false; \ - } \ - } - -bool pxCreateTables(pxConfig *config) { - PS_ASSERT_PTR_NON_NULL(config, false); - - bool status = true; -#if 0 - CREATE_TABLE(expTagCounterCreateTable); - // the counter must be initialized to 0 - if (!p_psDBRunQuery(config->dbh, "INSERT INTO expTagCounter VALUES (0)")) { - psError(PS_ERR_UNKNOWN, false, "database error"); - status = false; - } -#endif - CREATE_TABLE(pzDataStoreCreateTable); - CREATE_TABLE(summitExpCreateTable); - CREATE_TABLE(summitImfileCreateTable); -// CREATE_TABLE(pzPendingExpCreateTable); -// CREATE_TABLE(pzPendingImfileCreateTable); -// CREATE_TABLE(pzDoneExpCreateTable); -// CREATE_TABLE(pzDoneImfileCreateTable); - CREATE_TABLE(pzDownloadExpCreateTable); - CREATE_TABLE(pzDownloadImfileCreateTable); - CREATE_TABLE(newExpCreateTable); - CREATE_TABLE(newImfileCreateTable); - CREATE_TABLE(rawExpCreateTable); - CREATE_TABLE(rawImfileCreateTable); - CREATE_TABLE(guidePendingExpCreateTable); - CREATE_TABLE(chipRunCreateTable); - CREATE_TABLE(chipProcessedImfileCreateTable); - CREATE_TABLE(chipMaskCreateTable); - CREATE_TABLE(camRunCreateTable); - CREATE_TABLE(camProcessedExpCreateTable); - CREATE_TABLE(camMaskCreateTable); - CREATE_TABLE(detRunCreateTable); - CREATE_TABLE(detInputExpCreateTable); - CREATE_TABLE(detProcessedImfileCreateTable); - CREATE_TABLE(detProcessedExpCreateTable); - CREATE_TABLE(detStackedImfileCreateTable); - CREATE_TABLE(detNormalizedStatImfileCreateTable); - CREATE_TABLE(detNormalizedImfileCreateTable); - CREATE_TABLE(detNormalizedExpCreateTable); - CREATE_TABLE(detResidImfileCreateTable); - CREATE_TABLE(detResidExpCreateTable); - CREATE_TABLE(detRunSummaryCreateTable); - CREATE_TABLE(detRegisteredImfileCreateTable); - CREATE_TABLE(warpRunCreateTable); - CREATE_TABLE(warpSkyCellMapCreateTable); - CREATE_TABLE(warpSkyfileCreateTable); - CREATE_TABLE(warpMaskCreateTable); - CREATE_TABLE(stackRunCreateTable); - CREATE_TABLE(stackInputSkyfileCreateTable); - CREATE_TABLE(stackSumSkyfileCreateTable); - CREATE_TABLE(diffRunCreateTable); - CREATE_TABLE(diffInputSkyfileCreateTable); - CREATE_TABLE(diffSkyfileCreateTable); - CREATE_TABLE(magicRunCreateTable); - CREATE_TABLE(magicInputSkyfileCreateTable); - CREATE_TABLE(magicTreeCreateTable); - CREATE_TABLE(magicNodeResultCreateTable); - CREATE_TABLE(magicMaskCreateTable); - CREATE_TABLE(magicSkyfileMaskCreateTable); - CREATE_TABLE(calDBCreateTable); - CREATE_TABLE(calRunCreateTable); - CREATE_TABLE(flatcorrRunCreateTable); - CREATE_TABLE(flatcorrExpCreateTable); - CREATE_TABLE(pstampDataStoreCreateTable); - CREATE_TABLE(pstampRequestCreateTable); - CREATE_TABLE(pstampJobCreateTable); - - return status; -} - -bool pxDeleteTables(pxConfig *config) { - bool status = true; - char line[128], answer[128]; - - PS_ASSERT_PTR_NON_NULL(config, false); - - // XXX use the values defined in config->dbh? - psString dbName = psMetadataLookupStr(&status, config->modules->complete, "DBNAME"); - - fprintf (stdout, "*** delete the chip tables from database %s? ***\n", dbName); - fprintf (stdout, "*** to delete the tables, answer YES, and give password ***\n"); - fprintf (stdout, "*** WARNING: this action is permanent ***\n\n"); - - fprintf (stdout, "delete the chip tables (YES/[n]): "); - fgets (line, 128, stdin); - sscanf (line, "%s", answer); - if (strcmp (answer, "YES")) { - psError(PS_ERR_UNKNOWN, true, "tables NOT deleleted"); - return false; - } - - fprintf (stdout, "enter dbh connection password: "); - fgets (line, 128, stdin); - sscanf (line, "%s", answer); - - { - bool status; - psString dbPassword = psMetadataLookupStr(&status, config->modules->complete, "DBPASSWORD"); - if (strcmp (answer, dbPassword)) { - psError(PS_ERR_UNKNOWN, true, "tables NOT deleleted"); - return false; - } - } - - // disable foreign key constrants - if (!p_psDBRunQuery(config->dbh, "SET FOREIGN_KEY_CHECKS=0")) { - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - -// DROP_TABLE(expTagCounterDropTable); - DROP_TABLE(pzDataStoreDropTable); - DROP_TABLE(summitExpDropTable); - DROP_TABLE(summitImfileDropTable); -// DROP_TABLE(pzPendingExpDropTable); -// DROP_TABLE(pzPendingImfileDropTable); -// DROP_TABLE(pzDoneExpDropTable); -// DROP_TABLE(pzDoneImfileDropTable); - DROP_TABLE(pzDownloadExpDropTable); - DROP_TABLE(pzDownloadImfileDropTable); - DROP_TABLE(newExpDropTable); - DROP_TABLE(newImfileDropTable); - DROP_TABLE(rawExpDropTable); - DROP_TABLE(rawImfileDropTable); - DROP_TABLE(guidePendingExpDropTable); - DROP_TABLE(chipRunDropTable); - DROP_TABLE(chipProcessedImfileDropTable); - DROP_TABLE(chipMaskDropTable); - DROP_TABLE(camRunDropTable); - DROP_TABLE(camProcessedExpDropTable); - DROP_TABLE(camMaskDropTable); - DROP_TABLE(detRunDropTable); - DROP_TABLE(detInputExpDropTable); - DROP_TABLE(detProcessedImfileDropTable); - DROP_TABLE(detProcessedExpDropTable); - DROP_TABLE(detStackedImfileDropTable); - DROP_TABLE(detNormalizedStatImfileDropTable); - DROP_TABLE(detNormalizedImfileDropTable); - DROP_TABLE(detNormalizedExpDropTable); - DROP_TABLE(detResidImfileDropTable); - DROP_TABLE(detResidExpDropTable); - DROP_TABLE(detRunSummaryDropTable); - DROP_TABLE(detRegisteredImfileDropTable); - DROP_TABLE(warpRunDropTable); - DROP_TABLE(warpSkyCellMapDropTable); - DROP_TABLE(warpSkyfileDropTable); - DROP_TABLE(warpMaskDropTable); - DROP_TABLE(diffRunDropTable); - DROP_TABLE(diffInputSkyfileDropTable); - DROP_TABLE(diffSkyfileDropTable); - DROP_TABLE(stackRunDropTable); - DROP_TABLE(stackInputSkyfileDropTable); - DROP_TABLE(stackSumSkyfileDropTable); - DROP_TABLE(magicRunDropTable); - DROP_TABLE(magicInputSkyfileDropTable); - DROP_TABLE(magicTreeDropTable); - DROP_TABLE(magicNodeResultDropTable); - DROP_TABLE(magicMaskDropTable); - DROP_TABLE(magicSkyfileMaskDropTable); - DROP_TABLE(calDBDropTable); - DROP_TABLE(calRunDropTable); - DROP_TABLE(flatcorrRunDropTable); - DROP_TABLE(flatcorrExpDropTable); - DROP_TABLE(pstampDataStoreDropTable); - DROP_TABLE(pstampRequestDropTable); - DROP_TABLE(pstampJobDropTable); - - // re-enable foreign key constrants - if (!p_psDBRunQuery(config->dbh, "SET FOREIGN_KEY_CHECKS=1")) { - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - - return status; -}