Index: trunk/ippTools/share/pxadmin_create_tables.sql =================================================================== --- trunk/ippTools/share/pxadmin_create_tables.sql (revision 30997) +++ trunk/ippTools/share/pxadmin_create_tables.sql (revision 31038) @@ -14,4 +14,5 @@ CREATE TABLE summitExp ( + summit_id BIGINT NOT NULL AUTO_INCREMENT, exp_name VARCHAR(64), camera VARCHAR(64), @@ -23,9 +24,11 @@ fault SMALLINT NOT NULL, epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(exp_name, camera, telescope), + PRIMARY KEY(summit_id), + KEY(exp_name, camera, telescope), KEY(fault) ) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE summitImfile ( + summit_id BIGINT, exp_name VARCHAR(64), camera VARCHAR(64), @@ -38,10 +41,13 @@ uri VARCHAR(255), epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(exp_name, camera, telescope, class, class_id), + PRIMARY KEY(summit_id, class, class_id), + KEY(exp_name, camera, telescope, class, class_id), KEY(file_id), + FOREIGN KEY(summit_id) REFERENCES summitExp(summit_id), FOREIGN KEY(exp_name, camera, telescope) REFERENCES summitExp(exp_name, camera, telescope) ) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE pzDownloadExp ( + summit_id BIGINT, exp_name VARCHAR(64), camera VARCHAR(64), @@ -49,10 +55,13 @@ state VARCHAR(64), epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(exp_name, camera, telescope), + PRIMARY KEY(summit_id), + KEY(exp_name, camera, telescope), KEY(state), + FOREIGN KEY(summit_id) REFERENCES summitExp(summit_id), FOREIGN KEY(exp_name, camera, telescope) REFERENCES summitExp(exp_name, camera, telescope) ) ENGINE=innodb DEFAULT CHARSET=latin1; CREATE TABLE pzDownloadImfile ( + summit_id BIGINT, exp_name VARCHAR(64), camera VARCHAR(64), @@ -66,6 +75,9 @@ bytes INT, md5sum VARCHAR(32), - PRIMARY KEY(exp_name, camera, telescope, class, class_id), - KEY(fault), + PRIMARY KEY(summit_id, class, class_id), + KEY(exp_name, camera, telescope, class, class_id), + KEY(fault), + FOREIGN KEY(summit_id) REFERENCES pzDownloadExp(summit_id), + FOREIGN KEY(summit_id, class, class_id) REFERENCES summitImfile(summit_id, class, class_id), 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) @@ -74,4 +86,5 @@ CREATE TABLE newExp ( exp_id BIGINT AUTO_INCREMENT, + summit_id BIGINT, tmp_exp_name VARCHAR(64), tmp_camera VARCHAR(64), @@ -88,4 +101,5 @@ PRIMARY KEY(exp_id), KEY(exp_id), + KEY(summit_id), KEY(tmp_exp_name), KEY(tmp_camera), Index: trunk/ippTools/share/pztool_find_completed_exp.sql =================================================================== --- trunk/ippTools/share/pztool_find_completed_exp.sql (revision 30997) +++ trunk/ippTools/share/pztool_find_completed_exp.sql (revision 31038) @@ -1,3 +1,4 @@ SELECT DISTINCT + summit_id, exp_name, camera, @@ -13,20 +14,17 @@ FROM pzDownloadExp JOIN summitExp - USING(exp_name, camera, telescope) + USING(summit_id) LEFT JOIN pzDownloadImfile - USING(exp_name, camera, telescope) + USING(summit_id) LEFT JOIN newExp - ON pzDownloadExp.exp_name = newExp.tmp_exp_name - AND pzDownloadExp.camera = newExp.tmp_camera - AND pzDownloadExp.telescope = newExp.tmp_telescope + USING(summit_id) WHERE pzDownloadExp.state = 'run' + AND newExp.summit_id IS NULL AND newExp.tmp_exp_name IS NULL AND newExp.tmp_camera IS NULL AND newExp.tmp_telescope IS NULL GROUP BY - pzDownloadExp.exp_name, - pzDownloadExp.camera, - pzDownloadExp.telescope + pzDownloadExp.summit_id, -- it doesn't matter which field in pzDownloadImfile we count as we've -- already Download a group by Index: trunk/ippTools/share/pztool_pendingimfile.sql =================================================================== --- trunk/ippTools/share/pztool_pendingimfile.sql (revision 30997) +++ trunk/ippTools/share/pztool_pendingimfile.sql (revision 31038) @@ -6,11 +6,12 @@ FROM summitImfile JOIN pzDownloadExp - USING(exp_name, camera, telescope) + USING(summit_id) LEFT JOIN pzDownloadImfile - USING(exp_name, camera, telescope, class, class_id) + USING(summit_id, class, class_id) JOIN summitExp - USING(exp_name, camera, telescope) + USING(summit_id) WHERE pzDownloadExp.state = 'run' + AND pzDownloadImfile.summit_id IS NULL AND pzDownloadImfile.exp_name IS NULL AND pzDownloadImfile.camera IS NULL Index: trunk/ippTools/share/regtool_checkburntoolimfile.sql =================================================================== --- trunk/ippTools/share/regtool_checkburntoolimfile.sql (revision 30997) +++ trunk/ippTools/share/regtool_checkburntoolimfile.sql (revision 31038) @@ -21,7 +21,7 @@ FROM summitExp - JOIN summitImfile USING(exp_name) - LEFT JOIN pzDownloadExp USING(exp_name) - LEFT JOIN newExp ON summitExp.exp_name = tmp_exp_name + JOIN summitImfile USING(summit_id) + LEFT JOIN pzDownloadExp USING(summit_id) + LEFT JOIN newExp USING(summit_id) LEFT JOIN newImfile ON (summitImfile.class_id = newImfile.tmp_class_id AND newExp.exp_id = newImfile.exp_id) LEFT JOIN rawExp ON rawExp.exp_id = newExp.exp_id Index: trunk/ippTools/share/regtool_checkstatus.sql =================================================================== --- trunk/ippTools/share/regtool_checkstatus.sql (revision 30997) +++ trunk/ippTools/share/regtool_checkstatus.sql (revision 31038) @@ -20,7 +20,7 @@ (rawImfile.burntool_state IS NOT NULL) AS is_registered FROM summitExp - JOIN summitImfile USING(exp_name) - LEFT JOIN pzDownloadExp USING(exp_name) - LEFT JOIN newExp ON summitExp.exp_name = tmp_exp_name + JOIN summitImfile USING(summit_id) + LEFT JOIN pzDownloadExp USING(summit_id) + LEFT JOIN newExp USING(summit_id) LEFT JOIN newImfile ON (summitImfile.class_id = newImfile.tmp_class_id AND newExp.exp_id = newImfile.exp_id) LEFT JOIN rawExp ON rawExp.exp_id = newExp.exp_id Index: trunk/ippTools/share/regtool_pendingburntoolimfile.sql =================================================================== --- trunk/ippTools/share/regtool_pendingburntoolimfile.sql (revision 30997) +++ trunk/ippTools/share/regtool_pendingburntoolimfile.sql (revision 31038) @@ -22,7 +22,7 @@ FROM summitExp - JOIN summitImfile USING(exp_name) - LEFT JOIN pzDownloadExp USING(exp_name) - LEFT JOIN newExp ON summitExp.exp_name = tmp_exp_name + JOIN summitImfile USING(summit_id) + LEFT JOIN pzDownloadExp USING(summit_id) + LEFT JOIN newExp USING(summit_id) LEFT JOIN newImfile ON (summitImfile.class_id = newImfile.tmp_class_id AND newExp.exp_id = newImfile.exp_id) LEFT JOIN rawExp ON rawExp.exp_id = newExp.exp_id Index: trunk/ippTools/src/pxinject.c =================================================================== --- trunk/ippTools/src/pxinject.c (revision 30997) +++ trunk/ippTools/src/pxinject.c (revision 31038) @@ -92,4 +92,5 @@ if (!newExpInsert(config->dbh, 0, // exp_id + 0, // summit_id tmp_exp_name, tmp_camera, Index: trunk/ippTools/src/pzgetexp.c =================================================================== --- trunk/ippTools/src/pzgetexp.c (revision 30997) +++ trunk/ippTools/src/pzgetexp.c (revision 31038) @@ -215,5 +215,6 @@ char *query = "INSERT INTO summitExp" - " SElECT" + " SELECT" + " NULL," // summit_id " incoming.*," " NULL," // imfiles Index: trunk/ippTools/src/pzgetimfiles.c =================================================================== --- trunk/ippTools/src/pzgetimfiles.c (revision 30997) +++ trunk/ippTools/src/pzgetimfiles.c (revision 31038) @@ -99,7 +99,31 @@ int status = pclose(output); + // We have an id value now, let's get it and use it + char *id_query = "SELECT summit_id FROM summitExp WHERE exp_name = '%s' AND camera = '%s' AND telescope = '%s'"; + + if (!p_psDBRunQueryF(config->dbh, id_query, filesetid, camera, telescope)) { + psError(PS_ERR_UNKNOWN, false, "database error"); + return false; + } + + psArray *id_output = p_psDBFetchResult(config->dbh); + if (!id_output) { + psError(PS_ERR_UNKNOWN, false, "database error"); + return false; + } + if (psArrayLength(id_output) != 1) { + psError(PS_ERR_UNKNOWN, false, "database error: incorrect number of results"); + return false; + } + + psMetadata *id_result = id_output->data[0]; + + psS64 summit_id = psMetadataLookupS64(NULL, id_result, "summit_id"); + psFree(id_output); + // End of summit_id block. + if (status != 0) { // mark the summitExp row as faulted - if (!p_psDBRunQueryF(config->dbh, "UPDATE summitExp SET fault = %d WHERE exp_name = '%s' AND camera = '%s' AND telescope = '%s'", WEXITSTATUS(status), filesetid, camera, telescope)) { + if (!p_psDBRunQueryF(config->dbh, "UPDATE summitExp SET fault = %d WHERE summit_id = %ld", WEXITSTATUS(status), summit_id)) { psError(PS_ERR_UNKNOWN, false, "database error"); } @@ -117,5 +141,5 @@ // download an "empty" fileset. // mark the summitExp row as faulted - if (!p_psDBRunQueryF(config->dbh, "UPDATE summitExp SET fault = %d WHERE exp_name = '%s' AND camera = '%s' AND telescope = '%s'", 250, filesetid, camera, telescope)) { + if (!p_psDBRunQueryF(config->dbh, "UPDATE summitExp SET fault = %d WHERE summit_id = %ld", 250, summit_id)) { psError(PS_ERR_UNKNOWN, false, "database error"); } @@ -142,8 +166,6 @@ "UPDATE summitExp" " SET imfiles = %d" - " WHERE exp_name = '%s'" - " AND camera = '%s'" - " AND telescope = '%s'"; - if (!p_psDBRunQueryF(config->dbh, query, imfiles, filesetid, camera, telescope)) { + " WHERE summit_id = %ld"; + if (!p_psDBRunQueryF(config->dbh, query, imfiles, summit_id)) { // rollback if (!psDBRollback(config->dbh)) { @@ -159,8 +181,6 @@ "DELETE FROM pzDownloadExp" " WHERE" - " exp_name = '%s'" - " AND camera = '%s'" - " AND telescope = '%s'"; - if (!p_psDBRunQueryF(config->dbh, query, filesetid, camera, telescope)) { + " summit_id = %ld"; + if (!p_psDBRunQueryF(config->dbh, query, summit_id)) { // rollback if (!psDBRollback(config->dbh)) { @@ -245,4 +265,5 @@ "INSERT IGNORE INTO summitImfile" " SELECT" + " %ld," // summit_id " incoming.exp_name," " incoming.camera," @@ -256,5 +277,5 @@ " NULL" // epoch " FROM incoming"; - if (!p_psDBRunQuery(config->dbh, query)) { + if (!p_psDBRunQueryF(config->dbh, query,summit_id)) { // rollback if (!psDBRollback(config->dbh)) { @@ -272,14 +293,10 @@ " SET imfiles = (SELECT COUNT(*) FROM summitImfile" " WHERE" - " exp_name = '%s'" - " AND camera = '%s'" - " AND telescope = '%s'" + " summit_id = %ld" ")" " WHERE" - " exp_name = '%s'" - " AND camera = '%s'" - " AND telescope = '%s'" + " summit_id = %ld" " AND imfiles IS NULL"; - if (!p_psDBRunQueryF(config->dbh, query, filesetid, camera, telescope, filesetid, camera, telescope)) { + if (!p_psDBRunQueryF(config->dbh, query, summit_id, summit_id)) { // rollback if (!psDBRollback(config->dbh)) { @@ -308,4 +325,5 @@ "INSERT IGNORE INTO pzDownloadExp" " SELECT" + " %ld," // summit_id " incoming.exp_name," " incoming.camera," @@ -319,5 +337,5 @@ " incoming.telescope"; - if (!p_psDBRunQuery(config->dbh, query)) { + if (!p_psDBRunQueryF(config->dbh, query,summit_id)) { // rollback if (!psDBRollback(config->dbh)) { Index: trunk/ippTools/src/pztool.c =================================================================== --- trunk/ippTools/src/pztool.c (revision 30997) +++ trunk/ippTools/src/pztool.c (revision 31038) @@ -53,5 +53,5 @@ static psArray *pzGetPendingCameras(pxConfig *config); static psArray *pzArrayZip(psArray *arraySet, psS64 limit); -static bool pzDownloadExpSetState(pxConfig *config, const char *exp_name, const char *camera, const char *telescope, const char *state); +static bool pzDownloadExpSetState(pxConfig *config, const psS64 summit_id, const char *state); # define MODECASE(caseName, func) \ @@ -222,4 +222,5 @@ psMetadata *where = psMetadataAlloc(); + PXOPT_COPY_S64(config->args, where, "-summit_id", "summit_id", "=="); PXOPT_COPY_STR(config->args, where, "-exp_name", "exp_name", "=="); PXOPT_COPY_STR(config->args, where, "-inst", "camera", "=="); @@ -237,9 +238,7 @@ " FROM summitExp" " LEFT JOIN pzDownloadExp" - " USING(exp_name, camera, telescope)" + " USING(summit_id)" " WHERE" - " pzDownloadExp.exp_name IS NULL" - " AND pzDownloadExp.camera IS NULL" - " AND pzDownloadExp.telescope IS NULL" + " pzDownloadExp.summit_id IS NULL" " AND summitExp.fault = 0" ); @@ -260,4 +259,5 @@ if (limit) { psString limitString = psDBGenerateLimitSQL(limit); + psStringAppend(&query, " %s", limitString); psFree(limitString); @@ -299,4 +299,5 @@ psMetadata *where = psMetadataAlloc(); + PXOPT_COPY_S64(config->args, where, "-summit_id", "summit_id", "=="); PXOPT_COPY_STR(config->args, where, "-exp_name", "exp_name", "=="); PXOPT_COPY_STR(config->args, where, "-inst", "camera", "=="); @@ -401,4 +402,5 @@ // required + PXOPT_LOOKUP_S64(summit_id, config->args, "-summit_id", true, false); PXOPT_LOOKUP_STR(exp_name, config->args, "-exp_name", true, false); PXOPT_LOOKUP_STR(camera, config->args, "-inst", true, false); @@ -428,4 +430,5 @@ psMetadata *where = psMetadataAlloc(); + PXOPT_COPY_S64(config->args, where, "-summit_id", "summit_id", "=="); PXOPT_COPY_STR(config->args, where, "-exp_name", "exp_name", "=="); PXOPT_COPY_STR(config->args, where, "-inst", "camera", "=="); @@ -459,4 +462,5 @@ if (!pzDownloadImfileInsert(config->dbh, + summit_id, exp_name, camera, @@ -505,4 +509,5 @@ psMetadata *where = psMetadataAlloc(); + PXOPT_COPY_STR(config->args, where, "-summit_id", "summit_id", "=="); PXOPT_COPY_STR(config->args, where, "-exp_name", "exp_name", "=="); PXOPT_COPY_STR(config->args, where, "-inst", "camera", "=="); @@ -560,4 +565,5 @@ // required + PXOPT_LOOKUP_S64(summit_id, config->args, "-summit_id", true, false); PXOPT_LOOKUP_STR(exp_name, config->args, "-exp_name", true, false); PXOPT_LOOKUP_STR(inst, config->args, "-inst", true, false); @@ -579,4 +585,5 @@ if (!newExpInsert(config->dbh, 0x0, // exp_id + summit_id, // summit_id exp_name, // tmp_exp_name inst, // tmp_camera @@ -597,12 +604,13 @@ } - psS64 exp_id = psDBLastInsertID(config->dbh); - - // insert newImfiles - { - char *query = + psS64 exp_id = psDBLastInsertID(config->dbh); + + // insert newImfiles + { + char *query = "INSERT INTO newImfile" - " SElECT" + " SELECT" " %" PRId64 "," // exp_id + " %" PRId64 "," // summit_id " pzDownloadImfile.class_id," // tmp_class_id " pzDownloadImfile.uri," // uri @@ -616,20 +624,20 @@ " AND pzDownloadImfile.telescope = '%s'"; - if (!p_psDBRunQueryF(config->dbh, query, exp_id, exp_name, inst, telescope)) { - psError(PS_ERR_UNKNOWN, false, "database error"); - return false; - } - - // sanity check: we should have inserted at least one row - psU64 affected = psDBAffectedRows(config->dbh); - if (psDBAffectedRows(config->dbh) < 1) { - psError(PS_ERR_UNKNOWN, false, "should have affected at least 1 row but %" PRIu64 " rows were modified", affected); - return false; - } + if (!p_psDBRunQueryF(config->dbh, query, exp_id, summit_id, exp_name, inst, telescope)) { + psError(PS_ERR_UNKNOWN, false, "database error"); + return false; + } + + // sanity check: we should have inserted at least one row + psU64 affected = psDBAffectedRows(config->dbh); + if (psDBAffectedRows(config->dbh) < 1) { + psError(PS_ERR_UNKNOWN, false, "should have affected at least 1 row but %" PRIu64 " rows were modified", affected); + return false; + } } // set pzDownloadExp.state to 'stop' - if (!pzDownloadExpSetState(config, exp_name, inst, telescope, "stop")) { - psError(PS_ERR_UNKNOWN, false, "failed to change pzDownloadExp.state for %s:%s:%s", exp_name, inst, telescope); + if (!pzDownloadExpSetState(config, summit_id, "stop")) { + psError(PS_ERR_UNKNOWN, false, "failed to change pzDownloadExp.state for %ld", summit_id); return false; } @@ -887,5 +895,5 @@ -static bool pzDownloadExpSetState(pxConfig *config, const char *exp_name, const char *camera, const char *telescope, const char *state) +static bool pzDownloadExpSetState(pxConfig *config, const psS64 summit_id, const char *state) { PS_ASSERT_PTR_NON_NULL(state, false); @@ -904,7 +912,7 @@ } - char *query = "UPDATE pzDownloadExp SET state = '%s' WHERE exp_name = '%s' and camera = '%s' and telescope = '%s'"; - if (!p_psDBRunQueryF(config->dbh, query, state, exp_name, camera, telescope)) { - psError(PS_ERR_UNKNOWN, false, "failed to change state for %s:%s:%s", exp_name, camera, telescope); + char *query = "UPDATE pzDownloadExp SET state = '%s' WHERE summit_id = %ld"; + if (!p_psDBRunQueryF(config->dbh, query, state, summit_id)) { + psError(PS_ERR_UNKNOWN, false, "failed to change state for %ld", summit_id); return false; } @@ -916,11 +924,9 @@ { PS_ASSERT_PTR_NON_NULL(config, false); - PXOPT_LOOKUP_STR(exp_name, config->args, "-exp_name", true, false); - PXOPT_LOOKUP_STR(camera, config->args, "-inst", true, false); - PXOPT_LOOKUP_STR(telescope,config->args, "-telescope",true, false); + PXOPT_LOOKUP_S64(summit_id, config->args, "-summit_id", true, false); PXOPT_LOOKUP_STR(state, config->args, "-set_state",true, false); - if (!pzDownloadExpSetState(config,exp_name,camera,telescope,state)) { - psError(PS_ERR_UNKNOWN, false, "failed to change state for %s:%s:%s", exp_name, camera, telescope); + if (!pzDownloadExpSetState(config,summit_id, state)) { + psError(PS_ERR_UNKNOWN, false, "failed to change state for %ld", summit_id); return false; } Index: trunk/ippTools/src/pztoolConfig.c =================================================================== --- trunk/ippTools/src/pztoolConfig.c (revision 30997) +++ trunk/ippTools/src/pztoolConfig.c (revision 31038) @@ -63,4 +63,5 @@ // -pendingexp psMetadata *pendingexpArgs = psMetadataAlloc(); + psMetadataAddS64(pendingexpArgs, PS_LIST_TAIL, "-summit_id", 0, "define summit_id", 0); psMetadataAddStr(pendingexpArgs, PS_LIST_TAIL, "-exp_name", 0, "define exposure ID", NULL); psMetadataAddStr(pendingexpArgs, PS_LIST_TAIL, "-inst", 0, "define camera ID", NULL); @@ -73,4 +74,5 @@ // -pendingimfile psMetadata *pendingimfileArgs = psMetadataAlloc(); + psMetadataAddS64(pendingimfileArgs, PS_LIST_TAIL, "-summit_id", 0, "define summit_id", 0); psMetadataAddStr(pendingimfileArgs, PS_LIST_TAIL, "-exp_name", 0, "define exposure ID", NULL); psMetadataAddStr(pendingimfileArgs, PS_LIST_TAIL, "-inst", 0, "define camera ID", NULL); @@ -85,4 +87,5 @@ // -copydone psMetadata *copydoneArgs = psMetadataAlloc(); + psMetadataAddS64(copydoneArgs, PS_LIST_TAIL, "-summit_id", 0, "define summit_id (required)", 0); psMetadataAddStr(copydoneArgs, PS_LIST_TAIL, "-exp_name", 0, "define exposure ID (required)", NULL); psMetadataAddStr(copydoneArgs, PS_LIST_TAIL, "-inst", 0, "define camera ID (required)", NULL); Index: trunk/ippTools/src/regtool.c =================================================================== --- trunk/ippTools/src/regtool.c (revision 30997) +++ trunk/ippTools/src/regtool.c (revision 31038) @@ -311,7 +311,7 @@ // required - PXOPT_LOOKUP_STR(dateobs_begin, config->args, "-dateobs_begin", true, false); - PXOPT_LOOKUP_STR(dateobs_end, config->args, "-dateobs_end", true, false); - PXOPT_LOOKUP_S32(valid_burntool, config->args, "-valid_burntool", true, false); + PXOPT_LOOKUP_STR(dateobs_begin, config->args, "-dateobs_begin", true, false); + PXOPT_LOOKUP_STR(dateobs_end, config->args, "-dateobs_end", true, false); + PXOPT_LOOKUP_S32(valid_burntool, config->args, "-valid_burntool", true, false); // optional PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);