Changeset 28364

Timestamp:

Jun 16, 2010, 2:41:22 PM (16 years ago)

Author:

Paul Price

Message:

Fixing advance mode: 'You can't specify target table 'diffPhotRun' for update in FROM clause' (error from MySQL)

Location:

trunk/ippTools

Files:

• share/diffphottool_advance.sql (modified) (1 diff)
• src/diffphottool.c (modified) (5 diffs)

trunk/ippTools/share/diffphottool_advance.sql

@@ -1 @@
-UPDATE diffPhotRun
-SET state = 'full'
-WHERE diff_phot_id IN (
-    SELECT
-        diff_phot_id
-    FROM diffPhotRun
-    JOIN diffSkyfile USING(diff_id)
-    JOIN diffPhotSkyfile USING(diff_phot_id)
-    WHERE diffPhotRun.state = 'new'
-        AND diffPhotSkyfile.fault = 0
-        AND diffSkyfile.fault = 0
-        AND diffSkyfile.quality = 0
-        -- WHERE hook %s
-    HAVING COUNT(diffPhotSkyfile.skycell_id) = COUNT(diffSkyfile.skycell_id)
-) AS diffPhotRunsDone
+SELECT
+    diff_phot_id
+FROM diffPhotRun
+JOIN diffSkyfile USING(diff_id)
+JOIN diffPhotSkyfile USING(diff_phot_id)
+WHERE diffPhotRun.state = 'new'
+    AND diffPhotSkyfile.fault = 0
+    AND diffSkyfile.fault = 0
+    AND diffSkyfile.quality = 0
+-- WHERE hook %s
+HAVING COUNT(diffPhotSkyfile.skycell_id) = COUNT(diffSkyfile.skycell_id)

trunk/ippTools/src/diffphottool.c

@@ -126 +126 @@
 
     if (!psDBTransaction(config->dbh)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
+        psError(psErrorCodeLast(), false, "database error");
         return false;
     }
 
     if (!p_psDBRunQuery(config->dbh, query)) {
-        psError(PS_ERR_UNKNOWN, false, "Unable to run query: %s", query);
+        psError(psErrorCodeLast(), false, "Unable to run query: %s", query);
         psFree(where);
         return false;
@@ -326 +326 @@
 
     if (!p_psDBRunQuery(config->dbh, query)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
+        psError(psErrorCodeLast(), false, "database error");
         psFree(query);
         return false;
@@ -376 +376 @@
     if (!diffPhotSkyfileInsert(config->dbh, diff_phot_id, skycell_id, path_base, dtime_script, hostname,
                                fault, quality, version)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
+        psError(psErrorCodeLast(), false, "database error");
         return false;
     }
@@ -405 +405 @@
     psFree(where);
 
+    if (!psDBTransaction(config->dbh)) {
+        psError(psErrorCodeLast(), false, "database error");
+        return false;
+    }
+
     if (!p_psDBRunQueryF(config->dbh, query, whereClause)) {
         psError(psErrorCodeLast(), false, "database error");
         psFree(query);
-        psFree(whereClause);
         return false;
     }
     psFree(query);
-    psFree(whereClause)
+    psFree(whereClause);
+
+    psArray *output = p_psDBFetchResult(config->dbh);
+    if (!output) {
+        psError(psErrorCodeLast(), false, "database error");
+        return false;
+    }
+
+    for (int i = 0; i < output->n; i++) {
+        psMetadata *row = output->data[i]; // Row of interest
+        psS64 diff_phot_id = psMetadataLookupS64(NULL, row, "diff_phot_id");
+
+        const char *query = "UPDATE diffRun SET state = 'full' WHERE diff_id = %" PRId64;
+        if (!p_psDBRunQueryF(config->dbh, query, diff_phot_id)) {
+            psError(psErrorCodeLast(), false,
+                    "failed to change state for diff_phot_id %" PRId64, diff_phot_id);
+            if (!psDBRollback(config->dbh)) {
+                psError(psErrorCodeLast(), false, "database error");
+            }
+            psFree(output);
+            return false;
+        }
+    }
+    psFree(output);
+
+    if (!psDBCommit(config->dbh)) {
+        psError(psErrorCodeLast(), false, "database error");
+        return false;
+    }
 
     return true;
@@ -449 +481 @@
 
     if (!p_psDBRunQuery(config->dbh, query)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
+        psError(psErrorCodeLast(), false, "database error");
         psFree(query);
         return false;