Changeset 27737

Timestamp:

Apr 23, 2010, 11:19:20 AM (16 years ago)

Author:

bills

Message:

Fix very slow query for -towarped. Re-enable update processing;
Add -setskyfiletoupdate mode to queue updates for a single skycell.
Add -listrun.
Various other changes to support the postage stamp server.

Location:

trunk/ippTools

Files:

• share/warptool_listrun.sql (added)
• share/warptool_pendingcleanupskyfile.sql (modified) (1 diff)
• share/warptool_scmap.sql (modified) (2 diffs)
• share/warptool_setskyfiletoupdate.sql (added)
• share/warptool_towarped.sql (modified) (2 diffs)
• share/warptool_warped.sql (modified) (1 diff)
• src/warptool.c (modified) (11 diffs)
• src/warptool.h (modified) (1 diff)
• src/warptoolConfig.c (modified) (7 diffs)

trunk/ippTools/share/warptool_pendingcleanupskyfile.sql

@@ -10 +10 @@
     USING(warp_id)
 WHERE
-   ((warpRun.state = 'goto_cleaned'  AND warpSkyfile.data_state = 'full')
+   ((warpRun.state = 'goto_cleaned'  AND (warpSkyfile.data_state = 'full'
+                                      OR  warpSkyfile.data_state = 'update'))
     OR
     (warpRun.state = 'goto_scrubbed' AND warpSkyfile.data_state != 'scrubbed')

trunk/ippTools/share/warptool_scmap.sql

@@ -3 +3 @@
     chipProcessedImfile.uri,
     chipProcessedImfile.path_base as chip_path_base,
-    camProcessedExp.path_base as cam_path_base
+    camProcessedExp.path_base as cam_path_base,
+    chipProcessedImfile.chip_id,
+    chipRun.state,
+    chipProcessedImfile.data_state,
+    chipProcessedImfile.magicked,
+    rawImfile.magicked AS raw_magicked
 FROM warpRun
 JOIN warpSkyCellMap
@@ -18 +23 @@
     ON chipRun.chip_id = chipProcessedImfile.chip_id
     AND warpSkyCellMap.class_id = chipProcessedImfile.class_id
+JOIN rawImfile
+    ON chipRun.exp_id = rawImfile.exp_id
+    AND chipProcessedImfile.class_id = rawImfile.class_id
 WHERE
---    warpRun.state = 'new'
-    fakeRun.state = 'full'
-    AND camRun.state = 'full'
-    AND chipRun.state = 'full'
+    1

trunk/ippTools/share/warptool_towarped.sql

@@ +1 @@
+SELECT 
+    warp_id,
+    warp_skyfile_id,
+    skycell_id,
+    tess_id,
+    fake_id,
+    state,
+    reduction,
+    cam_id,
+    camera,
+    exp_tag,
+    workdir,
+    magicked
+FROM (
     SELECT DISTINCT
         warpSkyCellMap.warp_id,
@@ -45 +59 @@
         AND warpSkyCellMap.fault = 0
         -- where hook 1 %s
+        -- limit hook 1 %s
+UNION
+    SELECT
+        warpSkyCellMap.warp_id,
+        warpImfile.warp_skyfile_id,
+        warpSkyCellMap.skycell_id,
+        warpSkyCellMap.tess_id,
+        warpRun.fake_id,
+        warpRun.state,
+        warpRun.reduction,
+        camRun.cam_id,
+        rawExp.camera,
+        rawExp.exp_tag,
+        warpRun.workdir,
+        MIN(chipProcessedImfile.magicked) AS magicked
+    FROM warpRun
+    JOIN warpImfile USING(warp_id)
+    JOIN warpSkyCellMap USING(warp_id, skycell_id)
+    JOIN warpSkyfile USING(warp_id, skycell_id)
+    JOIN fakeRun USING(fake_id)
+    JOIN camRun USING(cam_id)
+    JOIN chipRun USING(chip_id)
+    JOIN rawExp USING(exp_id)
+    LEFT JOIN chipProcessedImfile USING(chip_id, class_id)
+    WHERE warpRun.state = 'update'
+        AND warpSkyfile.data_state = 'update'
+        AND warpSkyfile.fault = 0
+        AND camRun.state = 'full'
+        AND chipProcessedImfile.data_state = 'full'
+        -- if warpSkyfile was magicked previously require inputs to be magicked
+        -- this blocks processing until all the chip inputs have been destreaked
+        AND (warpSkyfile.magicked = 0 OR chipProcessedImfile.magicked >= 0)
+        -- where hook 2 %s
+    GROUP BY warp_id, skycell_id
+    HAVING COUNT(warpSkyCellMap.class_id) = COUNT(chipProcessedImfile.class_id)
+    -- limit hook 2 %s
+) as towarped
+

trunk/ippTools/share/warptool_warped.sql

@@ -21 +21 @@
 JOIN rawExp
     ON chipRun.exp_id  = rawExp.exp_id
-WHERE
--- bogus test; just here so there there is a 'WHERE' stmt to append conditionals too
--- XXX EAM : not needed: fix warptool.c
-    warpRun.warp_id is NOT NULL

trunk/ippTools/src/warptool.c

@@ -59 +59 @@
 static bool importrunMode(pxConfig *config);
 static bool runstateMode(pxConfig *config);
+static bool listrunMode(pxConfig *config);
+static bool setskyfiletoupdateMode(pxConfig *config);
 
 static bool parseAndInsertSkyCellMap(pxConfig *config, const char *mapfile);
@@ -110 +112 @@
         MODECASE(WARPTOOL_MODE_IMPORTRUN,          importrunMode);
         MODECASE(WARPTOOL_MODE_RUNSTATE,           runstateMode);
+        MODECASE(WARPTOOL_MODE_LISTRUN,            listrunMode);
+        MODECASE(WARPTOOL_MODE_SETSKYFILETOUPDATE, setskyfiletoupdateMode);
 
         default:
@@ -916 +920 @@
 
     // treat limit == 0 as "no limit"
+    psString limitString = psStringCopy("\n");
     if (limit) {
-        psString limitString = psDBGenerateLimitSQL(limit);
+        // We apply the limit to both sides of the UNION
+        // to avoid slow queries and to the query itself
+        // to satisfy the user's requested limit
+        psStringAppend(&limitString, psDBGenerateLimitSQL(limit));
         psStringAppend(&query, " %s", limitString);
-        psFree(limitString);
-    }
-
+    }
+
+#define TWO_WHERES
 #ifdef TWO_WHERES
-    if (!p_psDBRunQueryF(config->dbh, query, whereStr, whereStr)) {
+    if (!p_psDBRunQueryF(config->dbh, query, whereStr, limitString, whereStr,  limitString)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
         psFree(query);
@@ -935 +943 @@
     }
 #endif
+    psFree(limitString);
     psFree(whereStr);
     psFree(query);
@@ -994 +1003 @@
     PXOPT_LOOKUP_STR(hostname, config->args, "-hostname", false, false);
     PXOPT_LOOKUP_F32(good_frac, config->args, "-good_frac", false, false);
-    PXOPT_LOOKUP_S64(magicked, config->args, "-magicked", false, false);
+    PXOPT_LOOKUP_S64(magicked, config->args, "-set_magicked", false, false);
 
     // default values
@@ -1242 +1251 @@
     if (psListLength(where->list)) {
         psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-        psStringAppend(&query, " AND %s", whereClause);
+        psStringAppend(&query, " WHERE %s", whereClause);
         psFree(whereClause);
     } else if (!all && !where2) {
@@ -1248 +1257 @@
         return false;
     }
+
+    if (where2) {
+        if (psListLength(where->list)) {
+            psStringAppend(&query, " %s", where2);
+        } else {
+            psStringAppend(&query, " WHERE 1 %s", where2);
+        }
+    }
     psFree(where);
-
-    if (where2) {
-        psStringAppend(&query, " %s", where2);
-    }
 
     // treat limit == 0 as "no limit"
@@ -1665 +1678 @@
     }
 
-    char *set_magicked_skyfile = "";
-    char *set_magicked_run = "";
+    psString set_magicked_skyfile = psStringCopy("");
+    psString set_magicked_run = psStringCopy("");
     if (!strcmp(data_state, "full")) {
-        set_magicked_skyfile = "\n , warpSkyfile.magicked = IF((warpSkyfile.magicked < 0 AND chipRun.magicked = 0), warpSkyfile.magicked, chipRun.magicked)";
-        set_magicked_run = "\n,  warpRun.magicked = IF((warpRun.magicked < 0 AND chipRun.magicked = 0), warpRun.magicked, chipRun.magicked)";
-
+        // magicked is only an argument for for -tofullskyfile
+        PXOPT_LOOKUP_S64(magicked, config->args, "-set_magicked", false, false);
+        if (magicked) {
+            psStringAppend(&set_magicked_skyfile, "\n , warpSkyfile.magicked = %" PRId64, magicked);
+            psStringAppend(&set_magicked_run, "\n,  warpRun.magicked = %" PRId64, magicked);
+        }
     } else if (!strcmp(data_state, "cleaned") || !strcmp(data_state, "purged")) {
-        set_magicked_skyfile = "\n, warpSkyfile.magicked = IF(warpSkyfile.magicked = 0, 0, -1)";
-        set_magicked_run = "\n, warpRun.magicked = IF(warpRun.magicked = 0, 0, -1)";
+        // if magicked is currently nonzero set it to -1
+        // Set warpRun.magicked when the first skyfile is cleaned
+        psStringAppend(&set_magicked_skyfile, "\n, warpSkyfile.magicked = IF(warpSkyfile.magicked = 0, 0, -1), warpRun.magicked = IF(warpRun.magicked = 0, 0, -1)");
     }
 
@@ -1686 +1703 @@
     }
     psFree(query);
+    psFree(set_magicked_skyfile);
 
     query = pxDataGet("warptool_change_run_state.sql");
@@ -1696 +1714 @@
         return false;
     }
+    psFree(set_magicked_run);
 
     if (!psDBCommit(config->dbh)) {
@@ -2026 +2045 @@
     return true;
 }
+
+static bool listrunMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, false);
+
+    psMetadata *where = psMetadataAlloc();
+    PXOPT_COPY_S64(config->args, where, "-warp_id",    "warpRun.warp_id", "==");
+    PXOPT_COPY_STR(config->args, where, "-tess_id",    "warpRun.tess_id", "==");
+    PXOPT_COPY_STR(config->args, where, "-state",      "warpRun.state", "==");
+    PXOPT_COPY_S64(config->args, where, "-exp_id",     "rawExp.exp_id", "==");
+    PXOPT_COPY_STR(config->args, where, "-exp_name",   "rawExp.exp_name", "==");
+    PXOPT_COPY_S64(config->args, where, "-fake_id",    "fakeRun.fake_id", "==");
+    PXOPT_COPY_TIME(config->args, where, "-dateobs_begin", "rawExp.dateobs",  ">=");
+    PXOPT_COPY_TIME(config->args, where, "-dateobs_end",   "rawExp.dateobs",  "<=");
+    PXOPT_COPY_STR(config->args, where, "-filter",    "rawExp.filter", "LIKE");
+    PXOPT_COPY_S64(config->args, where, "-magicked", "warpRun.magicked", "==");
+    pxAddLabelSearchArgs (config, where, "-label",   "warpRun.label", "LIKE");
+    pxAddLabelSearchArgs (config, where, "-data_group",   "warpRun.data_group", "LIKE");
+    pxAddLabelSearchArgs (config, where, "-dist_group",   "warpRun.dist_group", "LIKE");
+
+    PXOPT_LOOKUP_BOOL(all, config->args, "-all", false);
+
+    PXOPT_LOOKUP_U64(limit, config->args, "-limit", false, false);
+    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+    PXOPT_LOOKUP_BOOL(pstamp_order, config->args, "-pstamp_order", false);
+
+    // find all rawImfiles matching the default query
+    psString query = pxDataGet("warptool_listrun.sql");
+    if (!query) {
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    // generate where strings for arguments that require extra processing
+    // beyond PXOPT_COPY*
+    psString where2 = NULL;
+    if (!pxmagicAddWhere(config, &where2, "warpRun")) {
+        psError(psErrorCodeLast(), false, "pxMagicAddWhere failed");
+        return false;
+    }
+    if (!pxspaceAddWhere(config, &where2, "rawExp")) {
+        psError(psErrorCodeLast(), false, "pxSpaceAddWhere failed");
+        return false;
+    }
+
+    if (psListLength(where->list)) {
+        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+        psStringAppend(&query, " WHERE %s", whereClause);
+        psFree(whereClause);
+    } else if (!all && !where2) {
+        psError(PXTOOLS_ERR_CONFIG, true, "search parameters or -all are required");
+        return false;
+    }
+
+    if (where2) {
+        if (psListLength(where->list)) {
+            psStringAppend(&query, " %s", where2);
+        } else {
+            psStringAppend(&query, " WHERE 1 %s", where2);
+        }
+    }
+    psFree(where);
+
+    if (pstamp_order) {
+        // put runs in order of exposure id with newest chip Runs first
+        // The postage stamp parser depends on this behavior
+        psStringAppend(&query, "\nORDER by exp_id, warp_id DESC");
+    }
+
+
+    // treat limit == 0 as "no limit"
+    if (limit) {
+        psString limitString = psDBGenerateLimitSQL(limit);
+        psStringAppend(&query, " %s", limitString);
+        psFree(limitString);
+    }
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(query);
+        return false;
+    }
+    psFree(query);
+
+    psArray *output = p_psDBFetchResult(config->dbh);
+    if (!output) {
+        psErrorCode err = psErrorCodeLast();
+        switch (err) {
+            case PS_ERR_DB_CLIENT:
+                psError(PXTOOLS_ERR_SYS, false, "database error");
+            case PS_ERR_DB_SERVER:
+                psError(PXTOOLS_ERR_PROG, false, "database error");
+            default:
+                psError(PXTOOLS_ERR_PROG, false, "unknown error");
+        }
+
+        return false;
+    }
+    if (!psArrayLength(output)) {
+        psTrace("warptool", PS_LOG_INFO, "no rows found");
+        psFree(output);
+        return true;
+    }
+
+    if (psArrayLength(output)) {
+        // negative simple so the default is true
+        if (!ippdbPrintMetadatas(stdout, output, "warpRun", !simple)) {
+            psError(PS_ERR_UNKNOWN, false, "failed to print array");
+            psFree(output);
+            return false;
+        }
+    }
+
+    psFree(output);
+
+    return true;
+}
+
+// a very specfic function to queue a cleaned warpSkyfile to be updated
+static bool setskyfiletoupdateMode(pxConfig *config)
+{
+    PS_ASSERT_PTR_NON_NULL(config, NULL);
+
+    PXOPT_LOOKUP_S64(warp_id, config->args, "-warp_id", true, false);
+    PXOPT_LOOKUP_STR(skycell_id, config->args, "-skycell_id", false, false);
+    PXOPT_LOOKUP_STR(label, config->args, "-set_label", false, false);
+
+    psString query = pxDataGet("warptool_setskyfiletoupdate.sql");
+    if (!query) {
+        psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+        return false;
+    }
+
+    psString setHook = psStringCopy("");
+    if (label) {
+        psStringAppend(&setHook, "\n , warpRun.label = '%s'", label);
+    }
+
+    if (skycell_id) {
+        psStringAppend(&query, " AND (warpSkyfile.skycell_id = '%s')", skycell_id);
+    }
+
+    if (!p_psDBRunQueryF(config->dbh, query, setHook, warp_id)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+
+    psFree(setHook);
+    psFree(query);
+
+    return true;
+}

trunk/ippTools/src/warptool.h

@@ -55 +55 @@
     WARPTOOL_MODE_IMPORTRUN,
     WARPTOOL_MODE_RUNSTATE,
+    WARPTOOL_MODE_LISTRUN,
+    WARPTOOL_MODE_SETSKYFILETOUPDATE,
 } warptoolMode;
 

trunk/ippTools/src/warptoolConfig.c

@@ -217 +217 @@
     psMetadataAddStr(addwarpedArgs, PS_LIST_TAIL, "-hostname", 0,            "define hostname", 0);
     psMetadataAddF32(addwarpedArgs, PS_LIST_TAIL, "-good_frac",  0,            "define %% of good pixels", NAN);
-    psMetadataAddS64(addwarpedArgs, PS_LIST_TAIL, "-magicked",  0, "define if this skycell has been magicked", 0);
+    psMetadataAddS64(addwarpedArgs, PS_LIST_TAIL, "-set_magicked",  0, "define if this skycell has been magicked", 0);
     psMetadataAddS16(addwarpedArgs, PS_LIST_TAIL, "-fault",  0,            "set fault code", 0);
     psMetadataAddS16(addwarpedArgs, PS_LIST_TAIL, "-quality",  0,            "set quality", 0);
@@ -236 +236 @@
     psMetadataAddStr(warpedArgs, PS_LIST_TAIL,  "-label",  PS_META_DUPLICATE_OK, "search by warpRun label", NULL);
     psMetadataAddStr(warpedArgs, PS_LIST_TAIL,  "-data_group",  PS_META_DUPLICATE_OK, "search by warpRun data_group", NULL);
+    psMetadataAddStr(warpedArgs, PS_LIST_TAIL,  "-dist_group",  PS_META_DUPLICATE_OK, "search by warpRun dist_group", NULL);
     // add magic related arguments
     pxmagicAddArguments(warpedArgs);
@@ -244 +245 @@
     psMetadataAddU64(warpedArgs, PS_LIST_TAIL, "-limit",  0,            "limit result set to N items", 0);
     psMetadataAddBool(warpedArgs, PS_LIST_TAIL, "-simple",  0,          "use the simple output format", false);
+
+    // -listrun
+    psMetadata *listrunArgs = psMetadataAlloc();
+    psMetadataAddS64(listrunArgs, PS_LIST_TAIL, "-warp_id", 0,           "search by warptool ID", 0);
+    psMetadataAddStr(listrunArgs, PS_LIST_TAIL, "-tess_id",  0,          "search by tessellation ID", NULL);
+    psMetadataAddStr(listrunArgs, PS_LIST_TAIL, "-state",  0,            "search by state", NULL);
+    psMetadataAddS64(listrunArgs, PS_LIST_TAIL, "-exp_id", 0,            "search by exposure tag", 0);
+    psMetadataAddStr(listrunArgs, PS_LIST_TAIL, "-exp_name", 0,          "search by exposure tag", 0);
+    psMetadataAddS64(listrunArgs, PS_LIST_TAIL, "-fake_id", 0,           "search by phase 3 version of exposure tag", 0);
+    psMetadataAddTime(listrunArgs, PS_LIST_TAIL, "-dateobs_begin", 0,    "search for exposures by time (>=)", NULL);
+    psMetadataAddTime(listrunArgs, PS_LIST_TAIL, "-dateobs_end", 0,      "search for exposures by time (<=)", NULL);
+    psMetadataAddStr(listrunArgs, PS_LIST_TAIL,  "-filter", 0,           "search for exposures by filter", NULL);
+    psMetadataAddS16(listrunArgs, PS_LIST_TAIL,  "-fault",  0,           "search by fault code", 0);
+    psMetadataAddStr(listrunArgs, PS_LIST_TAIL,  "-label",  PS_META_DUPLICATE_OK, "search by warpRun label", NULL);
+    psMetadataAddStr(listrunArgs, PS_LIST_TAIL,  "-data_group",  PS_META_DUPLICATE_OK, "search by warpRun data_group", NULL);
+    psMetadataAddStr(listrunArgs, PS_LIST_TAIL,  "-dist_group",  PS_META_DUPLICATE_OK, "search by warpRun dist_group", NULL);
+    // add magic related arguments
+    pxmagicAddArguments(listrunArgs);
+    // add arguments for spatial search
+    pxspaceAddArguments(listrunArgs);
+
+    psMetadataAddBool(listrunArgs, PS_LIST_TAIL, "-pstamp_order",  0,    "order results for postage stamp server", false);
+
+    psMetadataAddBool(listrunArgs, PS_LIST_TAIL, "-all",  0,             "search without arguments", false);
+    psMetadataAddU64(listrunArgs, PS_LIST_TAIL, "-limit",  0,            "limit result set to N items", 0);
+    psMetadataAddBool(listrunArgs, PS_LIST_TAIL, "-simple",  0,          "use the simple output format", false);
 
     // -revertwarped
@@ -315 +342 @@
     psMetadataAddS64(tofullskyfileArgs, PS_LIST_TAIL, "-warp_id", 0,    "warptool ID to update", 0);
     psMetadataAddStr(tofullskyfileArgs, PS_LIST_TAIL, "-skycell_id", 0, "skycell ID to update", NULL);
+    psMetadataAddS64(tofullskyfileArgs, PS_LIST_TAIL, "-set_magicked",  0, "new value for magicked", 0);
 
     // -updateskyfile
@@ -345 +373 @@
     psMetadataAddU64(runstateArgs, PS_LIST_TAIL, "-limit",  0,            "limit result set to N items", 0);
     psMetadataAddBool(runstateArgs, PS_LIST_TAIL, "-simple",  0,          "use the simple output format", false);
+
+    // -setskyfiletoupdate
+    psMetadata *setskyfiletoupdateArgs = psMetadataAlloc();
+    psMetadataAddS64(setskyfiletoupdateArgs, PS_LIST_TAIL, "-warp_id", 0,           "search by warptool ID (required)", 0);
+    psMetadataAddStr(setskyfiletoupdateArgs, PS_LIST_TAIL, "-skycell_id",  0,       "search by tessellation ID", NULL);
+    psMetadataAddStr(setskyfiletoupdateArgs, PS_LIST_TAIL, "-set_label",  0,        "new value for warpRun.label", NULL);
 
 
@@ -364 +398 @@
     PXOPT_ADD_MODE("-advancerun",      "", WARPTOOL_MODE_ADVANCERUN,     advancerunArgs);
     PXOPT_ADD_MODE("-warped",          "", WARPTOOL_MODE_WARPED,         warpedArgs);
+    PXOPT_ADD_MODE("-listrun",         "", WARPTOOL_MODE_LISTRUN,        listrunArgs);
     PXOPT_ADD_MODE("-runstate",        "", WARPTOOL_MODE_RUNSTATE,       runstateArgs);
     PXOPT_ADD_MODE("-revertwarped",    "", WARPTOOL_MODE_REVERTWARPED,   revertwarpedArgs);
@@ -377 +412 @@
     PXOPT_ADD_MODE("-tofullskyfile", "set skyfile as full (updated)", WARPTOOL_MODE_TOFULLSKYFILE, tofullskyfileArgs);
     PXOPT_ADD_MODE("-updateskyfile", "update fault code for skyfile", WARPTOOL_MODE_UPDATESKYFILE, updateskyfileArgs);
+    PXOPT_ADD_MODE("-setskyfiletoupdate", "set cleaned skyfile to be updated", WARPTOOL_MODE_SETSKYFILETOUPDATE, setskyfiletoupdateArgs);
     PXOPT_ADD_MODE("-exportrun",            "export run for import on other database", WARPTOOL_MODE_EXPORTRUN, exportrunArgs);
     PXOPT_ADD_MODE("-importrun",            "import run from metadata file",           WARPTOOL_MODE_IMPORTRUN, importrunArgs);