Index: /trunk/ippTools/src/dettool.c =================================================================== --- /trunk/ippTools/src/dettool.c (revision 10205) +++ /trunk/ippTools/src/dettool.c (revision 10206) @@ -3260,6 +3260,5 @@ " FROM detRun\n" " JOIN detInputExp\n" - " ON detRun.det_id = detInputExp.det_id\n" - " AND detRun.iteration = detInputExp.iteration\n" + " USING(det_id, iteration)\n" " JOIN rawDetrendExp\n" " ON detInputExp.exp_tag = rawDetrendExp.exp_tag\n" @@ -3442,88 +3441,119 @@ // where det_id, iteration, class_id is not in detResidImfile psString query = psStringCopy( - "SELECT * FROM" - " (SELECT" - " detNormalizedImfile.*" - " FROM detNormalizedImfile" - " JOIN detInputExp" - " USING(det_id, iteration)" - " LEFT JOIN detResidImfile" - " ON detNormalizedImfile.det_id = detResidImfile.det_id" - " AND detNormalizedImfile.iteration= detResidImfile.iteration" - " AND detInputExp.exp_tag = detResidImfile.exp_tag" - " AND detNormalizedImfile.class_id = detResidImfile.class_id" - " WHERE" - " detResidImfile.det_id IS NULL" - " AND detResidImfile.iteration IS NULL" - " AND detResidImfile.exp_tag IS NULL" - " AND detResidImfile.class_id IS NULL" - " AND detInputExp.exp_tag = '%s'" - " ) AS detNormStackedImfile" - ); - - { - // build a query to search by det_id, iteration, class_id - psMetadata *where = psMetadataAlloc(); - bool status = false; - psString det_id = psMetadataLookupStr(&status, config->args, "-det_id"); - if (!status) { - psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id"); + "SELECT\n" + " detNormalizedImfile.*\n" + " FROM detRun\n" + " JOIN detInputExp\n" + " USING(det_id, iteration)\n" + " JOIN detNormalizedImfile\n" + " USING(det_id, iteration)\n" + " LEFT JOIN detResidImfile\n" + " ON detRun.det_id = detResidImfile.det_id\n" + " AND detRun.iteration = detResidImfile.iteration\n" + " AND detInputExp.exp_tag = detResidImfile.exp_tag\n" + " AND detNormalizedImfile.class_id = detResidImfile.class_id\n" + " WHERE\n" + " detRun.state = 'run'\n" + " AND detRun.mode= 'master'\n" + " AND detResidImfile.det_id IS NULL\n" + " AND detResidImfile.iteration IS NULL\n" + " AND detResidImfile.exp_tag IS NULL\n" + " AND detResidImfile.class_id IS NULL\n" + " %2$s" + " UNION\n" + " SELECT\n" + " detRun.det_id,\n" + " detRun.iteration,\n" + " detProcessedImfile.class_id,\n" + " detProcessedImfile.uri,\n" + " detProcessedImfile.bg,\n" + " detProcessedImfile.bg_stdev,\n" + " detProcessedImfile.bg_mean_stdev,\n" + " detProcessedImfile.b1_uri,\n" + " detProcessedImfile.b2_uri\n" + " FROM detRun\n" + " JOIN detInputExp\n" + " USING(det_id, iteration)\n" + " JOIN detProcessedImfile\n" + " ON detRun.det_id = detProcessedImfile.det_id\n" + " AND detInputExp.exp_tag = detProcessedImfile.exp_tag\n" + " LEFT JOIN detResidImfile\n" + " ON detRun.det_id = detResidImfile.det_id\n" + " AND detRun.iteration= detResidImfile.iteration\n" + " AND detProcessedImfile.exp_tag = detResidImfile.exp_tag\n" + " AND detProcessedImfile.class_id = detResidImfile.class_id\n" + " WHERE\n" + " detRun.state = 'run'\n" + " AND detRun.mode= 'verify'\n" + " AND detResidImfile.det_id IS NULL\n" + " AND detResidImfile.iteration IS NULL\n" + " AND detResidImfile.exp_tag IS NULL\n" + " AND detResidImfile.class_id IS NULL\n" + " AND detInputExp.exp_tag = '%1$s'\n" + " %3$s" + ); + + // build a query to search by det_id, iteration, class_id + psMetadata *where = psMetadataAlloc(); + bool status = false; + psString det_id = psMetadataLookupStr(&status, config->args, "-det_id"); + if (!status) { + psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -det_id"); + psFree(where); + psFree(query); + return false; + } + if (det_id) { + if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) { + psError(PS_ERR_UNKNOWN, false, "failed to add item det_id"); psFree(where); psFree(query); return false; } - if (det_id) { - if (!psMetadataAddStr(where, PS_LIST_TAIL, "det_id", 0, "==", det_id)) { - psError(PS_ERR_UNKNOWN, false, "failed to add item det_id"); - psFree(where); - psFree(query); - return false; - } - } - psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration"); - if (!status) { - psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration"); + } + psS32 iteration = psMetadataLookupS32(&status, config->args, "-iteration"); + if (!status) { + psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -iteration"); + psFree(where); + psFree(query); + return false; + } + // always set iteration + if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) { + psError(PS_ERR_UNKNOWN, false, "failed to add item iteration"); + psFree(where); + psFree(query); + return false; + } + psString class_id = psMetadataLookupStr(&status, config->args, "-class_id"); + if (!status) { + psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id"); + psFree(where); + psFree(query); + return false; + } + if (class_id) { + if (!psMetadataAddStr(where, PS_LIST_TAIL, "class_id", 0, "==", class_id)) { + psError(PS_ERR_UNKNOWN, false, "failed to add item class_id"); psFree(where); psFree(query); return false; } - // always set iteration - if (!psMetadataAddS32(where, PS_LIST_TAIL, "iteration", 0, "==", iteration)) { - psError(PS_ERR_UNKNOWN, false, "failed to add item iteration"); - psFree(where); - psFree(query); - return false; - } - psString class_id = psMetadataLookupStr(&status, config->args, "-class_id"); - if (!status) { - psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id"); - psFree(where); - psFree(query); - return false; - } - if (class_id) { - if (!psMetadataAddStr(where, PS_LIST_TAIL, "class_id", 0, "==", class_id)) { - psError(PS_ERR_UNKNOWN, false, "failed to add item class_id"); - psFree(where); - psFree(query); - return false; - } - } - - // there's not - psString whereClause = psDBGenerateWhereSQL(where, NULL); - psFree(where); - if (whereClause) { - psStringAppend(&query, " %s", whereClause); - psFree(whereClause); - } - } + } + + psString masterClause = psDBGenerateWhereConditionSQL(where, "detNormalizedImfile"); + psStringPrepend(&masterClause, "AND "); + psMetadataRemoveKey(where, "iteration"); + psString verifyClause = psDBGenerateWhereConditionSQL(where, "detProcessedImfile"); + psStringPrepend(&verifyClause, "AND "); + psFree(where); // exp_tag is manadatory to cross check that this is a exp_tag for this // detRUn - bool status = false; psString exp_tag = psMetadataLookupStr(&status, config->args, "-exp_tag"); if (!status) { psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_tag"); + psFree(verifyClause); + psFree(masterClause); psFree(query); return false; @@ -3531,13 +3561,19 @@ if (!exp_tag) { psError(PS_ERR_UNKNOWN, true, "-exp_tag is required"); + psFree(verifyClause); + psFree(masterClause); psFree(query); return false; } - if (!p_psDBRunQuery(config->dbh, query, exp_tag, exp_tag)) { - psError(PS_ERR_UNKNOWN, false, "database error"); + if (!p_psDBRunQuery(config->dbh, query, exp_tag, masterClause, verifyClause)) { + psError(PS_ERR_UNKNOWN, false, "database error"); + psFree(verifyClause); + psFree(masterClause); psFree(query); return false; } + psFree(verifyClause); + psFree(masterClause); psFree(query); @@ -3548,8 +3584,7 @@ } if (!psArrayLength(output)) { - // XXX check psError here psError(PS_ERR_UNKNOWN, false, "no pending detNormalizedImfile rows found"); psFree(output); - return true; + return false; } @@ -3779,47 +3814,47 @@ psString query = psStringCopy( - "SELECT DISTINCT" - " det_id," - " iteration," - " det_type," - " exp_tag," - " include," - " camera" - " FROM" - " (SELECT DISTINCT" - " detRun.det_id AS det_id," - " detRun.iteration," - " detRun.det_type," - " detInputExp.exp_tag," - " detInputExp.include," - " rawDetrendExp.imfiles," - " rawDetrendExp.camera" - " FROM detRun" - " LEFT JOIN detInputExp" - " ON detRun.det_id = detInputExp.det_id" - " AND detRun.iteration = detInputExp.iteration" - " LEFT JOIN rawDetrendExp" - " ON detInputExp.exp_tag = rawDetrendExp.exp_tag" - " LEFT JOIN detResidImfile" - " ON detRun.det_id = detResidImfile.det_id" - " AND detRun.iteration = detResidImfile.iteration" - " AND detInputExp.exp_tag = detResidImfile.exp_tag" - " LEFT JOIN detResidExp" - " ON detRun.det_id = detResidExp.det_id" - " AND detRun.iteration = detResidExp.iteration" - " AND detInputExp.exp_tag = detResidExp.exp_tag" - " WHERE" - " detRun.state = 'run'" - " AND detRun.mode = 'master'" - " AND detResidExp.det_id IS NULL" - " AND detResidExp.iteration IS NULL" - " AND detResidExp.exp_tag IS NULL" - " GROUP BY" - " detInputExp.exp_tag," - " detRun.iteration," - " detRun.det_id" - " HAVING" - " rawDetrendExp.imfiles = COUNT(detResidImfile.class_id)" - " ) AS toresidexp" + "SELECT DISTINCT\n" + " det_id,\n" + " iteration,\n" + " det_type,\n" + " exp_tag,\n" + " include,\n" + " camera\n" + " FROM\n" + " (SELECT DISTINCT\n" + " detRun.det_id AS det_id,\n" + " detRun.iteration,\n" + " detRun.det_type,\n" + " detInputExp.exp_tag,\n" + " detInputExp.include,\n" + " rawDetrendExp.imfiles,\n" + " rawDetrendExp.camera,\n" + " detResidImfile.class_id\n" + " FROM detRun\n" + " JOIN detInputExp\n" + " USING(det_id, iteration)\n" + " JOIN rawDetrendExp\n" + " ON detInputExp.exp_tag = rawDetrendExp.exp_tag\n" + " JOIN detResidImfile\n" + " ON detRun.det_id = detResidImfile.det_id\n" + " AND detRun.iteration = detResidImfile.iteration\n" + " AND detInputExp.exp_tag = detResidImfile.exp_tag\n" + " LEFT JOIN detResidExp\n" + " ON detResidImfile.det_id = detResidExp.det_id\n" + " AND detResidImfile.iteration = detResidExp.iteration\n" + " AND detResidImfile.exp_tag = detResidExp.exp_tag\n" + " WHERE\n" + " detRun.state = 'run'\n" + " AND detRun.mode = 'master'\n" + " AND detResidExp.det_id IS NULL\n" + " AND detResidExp.iteration IS NULL\n" + " AND detResidExp.exp_tag IS NULL\n" + " GROUP BY\n" + " detInputExp.exp_tag,\n" + " detRun.iteration,\n" + " detRun.det_id\n" + " HAVING\n" + " rawDetrendExp.imfiles = COUNT(detResidImfile.class_id)\n" + " ) AS toresidexp\n" ); @@ -3913,20 +3948,19 @@ " rawDetrendExp.imfiles" " FROM detRun" - " LEFT JOIN detInputExp" - " ON detRun.det_id = detInputExp.det_id" - " AND detRun.iteration = detInputExp.iteration" - " LEFT JOIN rawDetrendExp" + " JOIN detInputExp" + " USING(det_id, iteration)" + " JOIN rawDetrendExp" " ON detInputExp.exp_tag = rawDetrendExp.exp_tag" - " LEFT JOIN detResidImfile" + " JOIN detResidImfile" " ON detRun.det_id = detResidImfile.det_id" " AND detRun.iteration = detResidImfile.iteration" " AND detInputExp.exp_tag = detResidImfile.exp_tag" " LEFT JOIN detResidExp" - " ON detRun.det_id = detResidExp.det_id" - " AND detRun.iteration = detResidExp.iteration" - " AND detInputExp.exp_tag = detResidExp.exp_tag" + " ON detResidImfile.det_id = detResidExp.det_id" + " AND detResidImfile.iteration = detResidExp.iteration" + " AND detResidImfile.exp_tag = detResidExp.exp_tag" " WHERE" " detRun.state = 'run'" - " AND detRun.mode = 'master'" + " AND detRun.mode = 'master'" " AND detResidExp.det_id IS NULL" " AND detResidExp.iteration IS NULL"