Changeset 42699

Timestamp:

Jun 24, 2024, 9:16:02 AM (2 years ago)

Author:

eugene

Message:

shakedown the xcstack-related tools; make staticsky definebyquery skycell_id selection a LIKE

Location:

branches/eam_branches/ipp-20230313/ippTools

Files:

• share/Makefile.am (modified) (1 diff)
• share/xcstacktool_addbyquery_select.sql (modified) (1 diff)
• share/xcstacktool_definebyquery_select.sql (modified) (1 diff)
• share/xcstacktool_definebyquery_select_XC.sql (added)
• src/staticskytool.c (modified) (1 diff)
• src/xcfftool.c (modified) (2 diffs)
• src/xcstacktool.c (modified) (4 diffs)

branches/eam_branches/ipp-20230313/ippTools/share/Makefile.am

@@ -556 +556 @@
         fpcamtool_revertupdatedexp.sql \
         xcstacktool_definebyquery_select.sql \
+        xcstacktool_definebyquery_select_XC.sql \
         xcstacktool_addbyquery_select.sql \
         xcstacktool_addcamera.sql \

branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_addbyquery_select.sql

@@ -26 +26 @@
   ) AS TMP
     LEFT JOIN xcstackInputSkyfile
-    ON (TMP.stack_id = xcstackInputSkyfile.stack_id)
+    ON (TMP.xcstack_id = xcstackInputSkyfile.xcstack_id)
     AND (camera_id = %d)
     WHERE xcstackInputSkyfile.xcstack_id IS NULL

branches/eam_branches/ipp-20230313/ippTools/share/xcstacktool_definebyquery_select.sql

@@ -5 +5 @@
     xcstack_id,
     stackRun.stack_id,
-    tess_id,
-    skycell_id,
-    filter
+    stackRun.tess_id,
+    stackRun.skycell_id,
+    stackRun.filter
     FROM %s.stackRun
     JOIN %s.stackSumSkyfile USING (stack_id)
-    LEFT JOIN xcstackInputSkyfile
-    ON (stackRun.stack_id = xcstackInputSkyfile.stack_id)
-    AND (xcstackInputSkyfile.camera_id = %d)
-    WHERE xcstack_id IS NULL
+    LEFT JOIN
+    

branches/eam_branches/ipp-20230313/ippTools/src/staticskytool.c

@@ -1061 +1061 @@
     PXOPT_COPY_S64(config->args, whereMD, "-select_sky_id",        "staticskyRun.sky_id",       "==");
     PXOPT_COPY_S64(config->args, whereMD, "-select_stack_id",      "stackRun.stack_id",         "==");
-    PXOPT_COPY_STR(config->args, whereMD, "-select_skycell_id",    "stackRun.skycell_id",       "==");
     PXOPT_COPY_STR(config->args, whereMD, "-select_tess_id",       "stackRun.tess_id",          "==");
+    PXOPT_COPY_STR(config->args, whereMD, "-select_skycell_id",    "stackRun.skycell_id",       "LIKE");
     pxAddLabelSearchArgs(config, whereMD, "-select_filter",        "stackRun.filter",           "LIKE");
     PXOPT_COPY_F32(config->args, whereMD, "-select_good_frac_min", "stackSumSkyfile.good_frac", ">=");

branches/eam_branches/ipp-20230313/ippTools/src/xcfftool.c

@@ -180 +180 @@
     PXOPT_COPY_S64(config->args, warpWhereMD, "-select_warp_id",         "warpRun.warp_id",         "==");
     PXOPT_COPY_F32(config->args, warpWhereMD, "-select_good_frac_min",   "warpSkyfile.good_frac",  ">=");
-    // XXX is is really necessary to limit the warps?
+    // it is necessary to limit the warps otherwise the warpQueryTemplate (line 282) will be ill-formed
     if (!psListLength(warpWhereMD->list)) {
         psError(PXTOOLS_ERR_CONFIG, false, "warp search parameters are required");
@@ -279 +279 @@
     psString warpQueryTemplate = pxDataGet("xcfftool_definebyquery_select_warps.sql");
 
+    // note the GROUP BY: if warpWhereMD is empty, this contruction
+    // will yield nonsense
     whereClause = psDBGenerateWhereConditionSQL(warpWhereMD, NULL);
-    psStringAppend(&warpQueryTemplate, "\n AND %s", whereClause);
+    psStringAppend(&warpQueryTemplate, "\n AND %s GROUP BY warp_id", whereClause);
 
     for (long i = 0; i < output->n; i++) {

branches/eam_branches/ipp-20230313/ippTools/src/xcstacktool.c

@@ -139 +139 @@
     PXOPT_LOOKUP_TIME(registered, config->args, "-set_registered", false, false);
 
+    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
+    PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
+
+    // we want to be able to queue the same input stacks for multiple output xcstackRuns,
+    // but we do not want to duplicate entries which already exist for the same
+    // label & data_group (or just data_group??).
+
+    // the possible input stacks are matched to already existing xcstacks with the same
+    // label & data_group and only ones which do NOT match (xcstack_id is NULL) are queued.
+
+    // In order to speed the query operations, the possible matched xcstacks are selected
+    // based on the tess_id, skycell_id, data_group restrictions supplied
+
+    // SUBSET TABLE SQL statement:
+
+    psMetadata *where_XC = psMetadataAlloc();
+
+    // also restrict the possible matched xcstackRuns based on these options:
+    pxAddLabelSearchArgs(config,  where_XC, "-select_tess_id",         "xcstackRun.tess_id", "==");
+    pxAddLabelSearchArgs(config,  where_XC, "-select_skycell_id",      "xcstackRun.skycell_id", "LIKE");
+
+    // generate the xcstackRun clause
+    if (!psListLength(where_XC->list)) {
+      psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required (at least tess_id or skycell_id)");
+      psFree(where_XC);
+      return false;
+    }
+      
+    psString select_XC = pxDataGet("xcstacktool_definebyquery_select_XC.sql");
+    if (!select_XC) {
+      psError(PXTOOLS_ERR_SYS, false, "failed to retreive SQL statement");
+      psFree(where_XC);
+      return false;
+    }
+      
+    psString whereClause_XC = psDBGenerateWhereConditionSQL(where_XC, NULL);
+    psStringAppend(&select_XC, " WHERE (xcstackInputSkyfile.camera_id = %d)\n AND %s", (int) camera_id, whereClause_XC);
+    psFree(whereClause_XC);
+
+    // add restrictions based on the output data_group and label
+    if (label)      { psStringAppend(&select_XC, "\n AND (xcstackRun.label      LIKE '%s')", label); }
+    if (data_group) { psStringAppend(&select_XC, "\n AND (xcstackRun.data_group LIKE '%s')", data_group); }
+
+    psFree(where_XC);
+
+    // MAIN SQL statement:
+
     psMetadata *where = psMetadataAlloc();
 
-    pxAddLabelSearchArgs (config, where, "-select_stack_id",           "stackRun.stack_id", "==");
+    // restrict the input stackRun based on these options:
+    pxAddLabelSearchArgs(config,  where, "-select_stack_id",           "stackRun.stack_id", "==");
     pxAddLabelSearchArgs(config,  where, "-select_tess_id",            "stackRun.tess_id", "==");
     pxAddLabelSearchArgs(config,  where, "-select_skycell_id",         "stackRun.skycell_id", "LIKE");
@@ -150 +198 @@
     PXOPT_COPY_F32(config->args,  where, "-select_good_frac_min",      "stackSumSkyfile.good_frac", ">=");
     pxAddLabelSearchArgs(config,  where, "-select_data_group",         "stackRun.data_group", "LIKE");
-    
-    PXOPT_LOOKUP_BOOL(simple, config->args, "-simple", false);
-    PXOPT_LOOKUP_BOOL(pretend, config->args, "-pretend", false);
 
     if (!psListLength(where->list)) {
-      psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required");
+      psError(PXTOOLS_ERR_CONFIG, false, "search parameters are required (at least tess_id or skycell_id)");
       psFree(where);
       return false;
@@ -167 +212 @@
     }
 
-    if (psListLength(where->list)) {
-      psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-      psStringAppend(&select, " AND %s", whereClause);
-      psFree(whereClause);
-    }
+    // merge base select and select_XC
+    psStringAppend (&select, "(%s)\n AS xcstackRunData\n ON (stackRun.stack_id = xcstackRunData.stack_id)\n WHERE xcstack_id IS NULL", select_XC);
+    psFree (select_XC);
+
+    psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+    psStringAppend(&select, "\n AND %s", whereClause);
+    psFree(whereClause);
     psFree(where);
 
     // camera name is prepended to stackRun and stackSumSkyfile tables in query
-    if (!p_psDBRunQueryF(config->dbh, select, camera, camera, camera_id)) {
+    if (!p_psDBRunQueryF(config->dbh, select, camera, camera)) {
       psError(PS_ERR_UNKNOWN, false, "database error");
       psFree(select);
@@ -268 +315 @@
       xcstackRunRow *run = xcstackRunRowAlloc(
                                               0,                          // ID
-                                              "new",                      // state
+                                              "wait",                     // state
                                               workdir,
                                               label,