Index: /trunk/ippTools/share/Makefile.am =================================================================== --- /trunk/ippTools/share/Makefile.am (revision 14044) +++ /trunk/ippTools/share/Makefile.am (revision 14045) @@ -15,4 +15,8 @@ dettool_raw.sql \ dettool_toprocessedimfile.sql \ + dettool_toprocessedexp.sql \ + dettool_tostacked.sql \ + dettool_toresidimfile.sql \ + dettool_toresidexp.sql \ difftool_inputskyfile.sql \ difftool_skyfile.sql \ Index: /trunk/ippTools/share/dettool_toprocessedexp.sql =================================================================== --- /trunk/ippTools/share/dettool_toprocessedexp.sql (revision 14045) +++ /trunk/ippTools/share/dettool_toprocessedexp.sql (revision 14045) @@ -0,0 +1,60 @@ +-- select detProcessedImfile.det_id +-- select detRun.iteration +-- select detRun.det_type +-- select detProcessedImfile.exp_id +-- by: +-- find the current iteration bassed on det_id +-- find all exp_ids in the current det_id/iteration from detInputExp +-- find all rawImfiles in the current exp_ids +-- compare to detProcessedImfiles by det_id/exp_id +-- found how many imfile there are in each class_id +-- and: +-- det_id is not in detProcessedExp; +-- iteration is not in detProcessedExp; + +SELECT DISTINCT + det_id, + iteration, + det_type, + exp_id, + camera, + workdir, + exp_tag +FROM + (SELECT DISTINCT + detRun.det_id, + detRun.iteration, + detRun.det_type, + detRun.workdir, + detProcessedImfile.exp_id, + detProcessedImfile.class_id, + rawExp.camera, + rawExp.exp_tag + FROM detRun + JOIN detInputExp + USING(det_id, iteration) + JOIN rawExp + ON detInputExp.exp_id = rawExp.exp_id + JOIN rawImfile + ON rawExp.exp_id = rawImfile.exp_id + LEFT JOIN detProcessedImfile + ON detRun.det_id = detProcessedImfile.det_id + AND detInputExp.exp_id = detProcessedImfile.exp_id + AND rawImfile.class_id = detProcessedImfile.class_id + LEFT JOIN detProcessedExp + ON detProcessedImfile.det_id = detProcessedExp.det_id + AND detProcessedImfile.exp_id = detProcessedExp.exp_id + WHERE + detRun.state = 'run' + AND detRun.mode = 'master' + AND detProcessedImfile.fault = 0 + AND detProcessedExp.det_id IS NULL + AND detProcessedExp.exp_id IS NULL + AND detInputExp.include = 1 + GROUP BY + rawExp.exp_id, + detRun.det_id + HAVING + COUNT(detProcessedImfile.class_id) = rawExp.imfiles + ) AS detProcessedExp + Index: /trunk/ippTools/share/dettool_toprocessedimfile.sql =================================================================== --- /trunk/ippTools/share/dettool_toprocessedimfile.sql (revision 14044) +++ /trunk/ippTools/share/dettool_toprocessedimfile.sql (revision 14045) @@ -4,5 +4,6 @@ detRun.workdir, detRun.reduction, - rawImfile.* + rawImfile.*, + rawExp.exp_tag FROM detRun JOIN detInputExp Index: /trunk/ippTools/share/dettool_toresidexp.sql =================================================================== --- /trunk/ippTools/share/dettool_toresidexp.sql (revision 14045) +++ /trunk/ippTools/share/dettool_toresidexp.sql (revision 14045) @@ -0,0 +1,66 @@ +-- which returns a list of exposures for which all component class ids have had +-- residuals created. This list includes the detrend id, iteration, exposure +-- id, detrend type, and whether the exposure was included in the stack for +-- this iteration. + +-- select detRun.det_id +-- select detRun.iteration +-- select detRun.det_type +-- select detInputExp.exp_id +-- select detInputExp.include +-- by: +-- find the current iteration bassed on det_id +-- find all exp_ids in the current det_id/iteration from detInputExp +-- compare to detInputExp.imfiles to derResidImfile by class_id +-- and: +-- detResidImfile.{det_id, iteration, exp_id} is not in detResidExp + +SELECT DISTINCT + det_id, + iteration, + det_type, + mode, + exp_id, + include, + camera, + workdir, + exp_tag +FROM + (SELECT DISTINCT + detRun.det_id AS det_id, + detRun.iteration, + detRun.det_type, + detRun.mode, + detRun.workdir, + detInputExp.exp_id, + detInputExp.include, + rawExp.camera, + rawExp.imfiles, + rawExp.exp_tag, + detResidImfile.class_id + FROM detRun + JOIN detInputExp + USING(det_id, iteration) + JOIN rawExp + ON detInputExp.exp_id = rawExp.exp_id + JOIN detResidImfile + ON detRun.det_id = detResidImfile.det_id + AND detRun.iteration = detResidImfile.iteration + AND detInputExp.exp_id = detResidImfile.exp_id + LEFT JOIN detResidExp + ON detResidImfile.det_id = detResidExp.det_id + AND detResidImfile.iteration = detResidExp.iteration + AND detResidImfile.exp_id = detResidExp.exp_id + WHERE + detRun.state = 'run' + AND detResidImfile.fault = 0 + AND detResidExp.det_id IS NULL + AND detResidExp.iteration IS NULL + AND detResidExp.exp_id IS NULL + GROUP BY + detInputExp.exp_id, + detRun.iteration, + detRun.det_id + HAVING + rawExp.imfiles = COUNT(detResidImfile.class_id) + ) AS toresidexp Index: /trunk/ippTools/share/dettool_toresidimfile.sql =================================================================== --- /trunk/ippTools/share/dettool_toresidimfile.sql (revision 14045) +++ /trunk/ippTools/share/dettool_toresidimfile.sql (revision 14045) @@ -0,0 +1,75 @@ +-- which returns a list of processed imfiles (with corresponding detrend id, +-- iteration, class id, uri, type) for which the appropriate stacked imfile has +-- been normalised, and which have not been masked out by detResidImfileAnalysis + +SELECT DISTINCT + detRun.det_id, + detRun.iteration, + detRun.det_type, + detRun.mode, + detRun.workdir, + detRun.reduction, + detProcessedImfile.exp_id, + detProcessedImfile.class_id, + detProcessedImfile.uri, + detNormalizedImfile.uri AS det_uri, + rawExp.camera, + rawExp.exp_tag +FROM detRun +JOIN detInputExp + USING(det_id, iteration) +JOIN rawExp + ON detInputExp.exp_id = rawExp.exp_id +JOIN detProcessedImfile + ON detRun.det_id = detProcessedImfile.det_id + AND detInputExp.exp_id = detProcessedImfile.exp_id +JOIN detNormalizedImfile + ON detRun.det_id = detNormalizedImfile.det_id + AND detRun.iteration = detNormalizedImfile.iteration + AND detProcessedImfile.class_id = detNormalizedImfile.class_id +LEFT JOIN detResidImfile + ON detRun.det_id = detResidImfile.det_id + AND detRun.iteration = detResidImfile.iteration + AND detProcessedImfile.exp_id = detResidImfile.exp_id + AND detProcessedImfile.class_id = detResidImfile.class_id +WHERE + detRun.state = 'run' + AND detRun.mode = 'master' + AND detNormalizedImfile.fault = 0 + AND detResidImfile.det_id IS NULL + AND detResidImfile.iteration IS NULL + AND detResidImfile.exp_id IS NULL + AND detResidImfile.class_id IS NULL +UNION +SELECT + detRun.det_id, + detRun.iteration, + detRun.det_type, + detRun.mode, + detRun.workdir, + detRun.reduction, + rawImfile.exp_id, + rawImfile.class_id, + rawImfile.uri, + 'NULL' AS det_uri, + rawExp.camera, + rawExp.exp_tag +FROM detRun +JOIN detInputExp + USING(det_id, iteration) +JOIN rawExp + ON detInputExp.exp_id = rawExp.exp_id +JOIN rawImfile + ON detInputExp.exp_id = rawImfile.exp_id +LEFT JOIN detResidImfile + ON detRun.det_id = detResidImfile.det_id + AND detRun.iteration = detResidImfile.iteration + AND rawImfile.exp_id = detResidImfile.exp_id + AND rawImfile.class_id = detResidImfile.class_id +WHERE + detRun.state = 'run' + AND detRun.mode = 'verify' + AND detResidImfile.det_id IS NULL + AND detResidImfile.iteration IS NULL + AND detResidImfile.exp_id IS NULL + AND detResidImfile.class_id IS NULL Index: /trunk/ippTools/share/dettool_tostacked.sql =================================================================== --- /trunk/ippTools/share/dettool_tostacked.sql (revision 14045) +++ /trunk/ippTools/share/dettool_tostacked.sql (revision 14045) @@ -0,0 +1,52 @@ +-- select detRun.iteration +-- select detProcessedImfile.det_id +-- select detProcessedImfile.class_id +-- by: +-- find the current iteration bassed on det_id +-- find all exp_ids in the current det_id/iteration from detInputExp +-- find all rawImfiles in the current exp_ids +-- compare to detProcessedImfiles by det_id/exp_id +-- found how many imfile there are in each class_id +-- and: +-- det_id is not in detStackedImfile; +-- iteration is not in detStackedImfile; +-- class_id is not in detStackedImfile; + +SELECT + detProcessedImfile.det_id, + detRun.iteration, + detRun.det_type, + detRun.workdir, + detRun.reduction, + detProcessedImfile.class_id, + rawExp.camera, + rawExp.exp_tag +FROM detRun +JOIN detInputExp + ON detRun.det_id = detInputExp.det_id + AND detRun.iteration = detInputExp.iteration +JOIN rawExp + ON detInputExp.exp_id = rawExp.exp_id +JOIN rawImfile + ON detInputExp.exp_id = rawImfile.exp_id +LEFT JOIN detProcessedImfile + ON detInputExp.det_id = detProcessedImfile.det_id + AND detInputExp.exp_id = detProcessedImfile.exp_id + AND rawImfile.class_id = detProcessedImfile.class_id +LEFT JOIN detStackedImfile + ON detInputExp.det_id = detStackedImfile.det_id + AND detInputExp.iteration = detStackedImfile.iteration + AND rawImfile.class_id = detStackedImfile.class_id +WHERE + detRun.state = 'run' + AND detRun.mode = 'master' + AND (detProcessedImfile.fault = 0 or detProcessedImfile.fault IS NULL) + AND detStackedImfile.det_id IS NULL + AND detStackedImfile.iteration IS NULL + AND detStackedImfile.class_id IS NULL + AND detInputExp.include = 1 +GROUP BY + rawImfile.class_id, + detRun.det_id +HAVING + COUNT(detProcessedImfile.class_id) = COUNT(rawImfile.class_id) Index: /trunk/ippTools/src/dettool.c =================================================================== --- /trunk/ippTools/src/dettool.c (revision 14044) +++ /trunk/ippTools/src/dettool.c (revision 14045) @@ -2416,64 +2416,9 @@ } - // select detProcessedImfile.det_id - // select detRun.iteration - // select detRun.det_type - // select detProcessedImfile.exp_id - // by: - // find the current iteration bassed on det_id - // find all exp_ids in the current det_id/iteration from detInputExp - // find all rawImfiles in the current exp_ids - // compare to detProcessedImfiles by det_id/exp_id - // found how many imfile there are in each class_id - // and: - // det_id is not in detProcessedExp; - // iteration is not in detProcessedExp; - - psString query = psStringCopy( - " SELECT DISTINCT" - " det_id," - " iteration," - " det_type," - " exp_id," - " camera," - " workdir" - " FROM" - " (SELECT DISTINCT" - " detRun.det_id," - " detRun.iteration," - " detRun.det_type," - " detRun.workdir," - " detProcessedImfile.exp_id," - " rawExp.camera," - " detProcessedImfile.class_id," - " rawExp.imfiles" - " FROM detRun" - " JOIN detInputExp" - " USING(det_id, iteration)" - " JOIN rawExp" - " ON detInputExp.exp_id = rawExp.exp_id" - " JOIN rawImfile" - " ON rawExp.exp_id = rawImfile.exp_id" - " LEFT JOIN detProcessedImfile" - " ON detRun.det_id = detProcessedImfile.det_id" - " AND detInputExp.exp_id = detProcessedImfile.exp_id" - " AND rawImfile.class_id = detProcessedImfile.class_id" - " LEFT JOIN detProcessedExp" - " ON detProcessedImfile.det_id = detProcessedExp.det_id" - " AND detProcessedImfile.exp_id = detProcessedExp.exp_id" - " WHERE" - " detRun.state = 'run'" - " AND detRun.mode = 'master'" - " AND detProcessedImfile.fault = 0" - " AND detProcessedExp.det_id IS NULL" - " AND detProcessedExp.exp_id IS NULL" - " AND detInputExp.include = 1" - " GROUP BY" - " rawExp.exp_id," - " detRun.det_id" - " HAVING" - " COUNT(detProcessedImfile.class_id) = rawExp.imfiles" - " ) AS detProcessedExp" - ); + psString query = pxDataGet("dettool_toprocessedexp.sql"); + if (!query) { + psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement"); + return false; + } // XXX does it make sense to accept any search params? @@ -2825,57 +2770,9 @@ } - // select detRun.iteration - // select detProcessedImfile.det_id - // select detProcessedImfile.class_id - // by: - // find the current iteration bassed on det_id - // find all exp_ids in the current det_id/iteration from detInputExp - // find all rawImfiles in the current exp_ids - // compare to detProcessedImfiles by det_id/exp_id - // found how many imfile there are in each class_id - // and: - // det_id is not in detStackedImfile; - // iteration is not in detStackedImfile; - // class_id is not in detStackedImfile; - - psString query = psStringCopy( - " SELECT\n" - " detProcessedImfile.det_id,\n" - " detRun.iteration,\n" - " detRun.det_type,\n" - " detRun.workdir,\n" - " detRun.reduction,\n" - " detProcessedImfile.class_id,\n" - " rawExp.camera\n" - " FROM detRun\n" - " JOIN detInputExp\n" - " ON detRun.det_id = detInputExp.det_id\n" - " AND detRun.iteration = detInputExp.iteration\n" - " JOIN rawExp\n" - " ON detInputExp.exp_id = rawExp.exp_id\n" - " JOIN rawImfile\n" - " ON detInputExp.exp_id = rawImfile.exp_id\n" - " LEFT JOIN detProcessedImfile\n" - " ON detInputExp.det_id = detProcessedImfile.det_id\n" - " AND detInputExp.exp_id = detProcessedImfile.exp_id\n" - " AND rawImfile.class_id = detProcessedImfile.class_id\n" - " LEFT JOIN detStackedImfile\n" - " ON detInputExp.det_id = detStackedImfile.det_id\n" - " AND detInputExp.iteration = detStackedImfile.iteration\n" - " AND rawImfile.class_id = detStackedImfile.class_id\n" - " WHERE\n" - " detRun.state = 'run'\n" - " AND detRun.mode = 'master'\n" - " AND (detProcessedImfile.fault = 0 or detProcessedImfile.fault IS NULL)\n" - " AND detStackedImfile.det_id IS NULL\n" - " AND detStackedImfile.iteration IS NULL\n" - " AND detStackedImfile.class_id IS NULL\n" - " AND detInputExp.include = 1\n" - " GROUP BY\n" - " rawImfile.class_id,\n" - " detRun.det_id\n" - " HAVING\n" - " COUNT(detProcessedImfile.class_id) = COUNT(rawImfile.class_id)\n" - ); + psString query = pxDataGet("dettool_tostacked.sql"); + if (!query) { + psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement"); + return false; + } // XXX does it make sense to accept any search params? @@ -4373,11 +4270,4 @@ PS_ASSERT_PTR_NON_NULL(config, false); - /* -which returns a list of processed imfiles (with corresponding detrend -id, iteration, class id, uri, type) for which the appropriate stacked -imfile has been normalised, and which have not been masked out by -detResidImfileAnalysis; -*/ - bool status = false; psU64 limit = psMetadataLookupU64(&status, config->args, "-limit"); @@ -4387,75 +4277,9 @@ } - psString query = psStringCopy( - "SELECT DISTINCT\n" - " detRun.det_id,\n" - " detRun.iteration,\n" - " detRun.det_type,\n" - " detRun.mode,\n" - " detRun.workdir,\n" - " detRun.reduction,\n" - " detProcessedImfile.exp_id,\n" - " detProcessedImfile.class_id,\n" - " detProcessedImfile.uri,\n" - " detNormalizedImfile.uri AS det_uri,\n" - " rawExp.camera\n" - " FROM detRun\n" - " JOIN detInputExp\n" - " USING(det_id, iteration)\n" - " JOIN rawExp\n" - " ON detInputExp.exp_id = rawExp.exp_id\n" - " JOIN detProcessedImfile\n" - " ON detRun.det_id = detProcessedImfile.det_id\n" - " AND detInputExp.exp_id = detProcessedImfile.exp_id\n" - " JOIN detNormalizedImfile\n" - " ON detRun.det_id = detNormalizedImfile.det_id\n" - " AND detRun.iteration = detNormalizedImfile.iteration\n" - " AND detProcessedImfile.class_id = detNormalizedImfile.class_id\n" - " LEFT JOIN detResidImfile\n" - " ON detRun.det_id = detResidImfile.det_id\n" - " AND detRun.iteration = detResidImfile.iteration\n" - " AND detProcessedImfile.exp_id = detResidImfile.exp_id\n" - " AND detProcessedImfile.class_id = detResidImfile.class_id\n" - " WHERE\n" - " detRun.state = 'run'\n" - " AND detRun.mode = 'master'\n" - " AND detNormalizedImfile.fault = 0\n" - " AND detResidImfile.det_id IS NULL\n" - " AND detResidImfile.iteration IS NULL\n" - " AND detResidImfile.exp_id IS NULL\n" - " AND detResidImfile.class_id IS NULL\n" - " UNION" - " SELECT\n" - " detRun.det_id,\n" - " detRun.iteration,\n" - " detRun.det_type,\n" - " detRun.mode,\n" - " detRun.workdir,\n" - " detRun.reduction,\n" - " rawImfile.exp_id,\n" - " rawImfile.class_id,\n" - " rawImfile.uri,\n" - " 'NULL' AS det_uri,\n" - " rawExp.camera\n" - " FROM detRun\n" - " JOIN detInputExp\n" - " USING(det_id, iteration)\n" - " JOIN rawExp\n" - " ON detInputExp.exp_id = rawExp.exp_id\n" - " JOIN rawImfile\n" - " ON detInputExp.exp_id = rawImfile.exp_id\n" - " LEFT JOIN detResidImfile\n" - " ON detRun.det_id = detResidImfile.det_id\n" - " AND detRun.iteration = detResidImfile.iteration\n" - " AND rawImfile.exp_id = detResidImfile.exp_id\n" - " AND rawImfile.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_id IS NULL\n" - " AND detResidImfile.class_id IS NULL\n" - ); + psString query = pxDataGet("dettool_toresidimfile.sql"); + if (!query) { + psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement"); + return false; + } // XXX does it make sense to accept any search params? @@ -5083,65 +4907,9 @@ } - // select detRun.det_id - // select detRun.iteration - // select detRun.det_type - // select detInputExp.exp_id - // select detInputExp.include - // by: - // find the current iteration bassed on det_id - // find all exp_ids in the current det_id/iteration from detInputExp - // compare to detInputExp.imfiles to derResidImfile by class_id - // and: - // detResidImfile.{det_id, iteration, exp_id} is not in detResidExp - - psString query = psStringCopy( - "SELECT DISTINCT\n" - " det_id,\n" - " iteration,\n" - " det_type,\n" - " mode,\n" - " exp_id,\n" - " include,\n" - " camera,\n" - " workdir\n" - " FROM\n" - " (SELECT DISTINCT\n" - " detRun.det_id AS det_id,\n" - " detRun.iteration,\n" - " detRun.det_type,\n" - " detRun.mode,\n" - " detRun.workdir,\n" - " detInputExp.exp_id,\n" - " detInputExp.include,\n" - " rawExp.imfiles,\n" - " rawExp.camera,\n" - " detResidImfile.class_id\n" - " FROM detRun\n" - " JOIN detInputExp\n" - " USING(det_id, iteration)\n" - " JOIN rawExp\n" - " ON detInputExp.exp_id = rawExp.exp_id\n" - " JOIN detResidImfile\n" - " ON detRun.det_id = detResidImfile.det_id\n" - " AND detRun.iteration = detResidImfile.iteration\n" - " AND detInputExp.exp_id = detResidImfile.exp_id\n" - " LEFT JOIN detResidExp\n" - " ON detResidImfile.det_id = detResidExp.det_id\n" - " AND detResidImfile.iteration = detResidExp.iteration\n" - " AND detResidImfile.exp_id = detResidExp.exp_id\n" - " WHERE\n" - " detRun.state = 'run'\n" - " AND detResidImfile.fault = 0\n" - " AND detResidExp.det_id IS NULL\n" - " AND detResidExp.iteration IS NULL\n" - " AND detResidExp.exp_id IS NULL\n" - " GROUP BY\n" - " detInputExp.exp_id,\n" - " detRun.iteration,\n" - " detRun.det_id\n" - " HAVING\n" - " rawExp.imfiles = COUNT(detResidImfile.class_id)\n" - " ) AS toresidexp\n" - ); + psString query = pxDataGet("dettool_toresidexp.sql"); + if (!query) { + psError(PXTOOLS_ERR_DATA, false, "failed to retreive SQL statement"); + return false; + } // XXX does it make sense to accept any search params?