Changeset 12002

Timestamp:

Feb 22, 2007, 2:54:18 PM (19 years ago)

Author:

jhoblitt

Message:

attempt to work around MySQL 4.1.21s broken support for nested sub-queries in update statements

File:

• trunk/ippTools/src/warptool.c (modified) (2 diffs)

trunk/ippTools/src/warptool.c

@@ -937 +937 @@
     PS_ASSERT_PTR_NON_NULL(config, false);
 
-    // find
-    psString query = psStringCopy( 
+#if MYSQL5
+    char *query =
         "UPDATE p4Run\n"
         "   SET p4Run.state = 'stop'\n"
@@ -962 +962 @@
         "       COUNT(p4SkyCellMap.p4_id) = COUNT(p4Skyfile.p4_id)\n"
         "       ) as Foo\n"
-        "   )\n"
-    );
+        "   )\n";
 
     if (!p_psDBRunQuery(config->dbh, query)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
-        psFree(query);
-        return false;
-    }
-
-    psFree(query);
+        return false;
+    }
+#else // if MYSQL5
+{
+    char *query =
+        "CREATE TEMPORARY TABLE finished\n"
+        " (p4_id INT, PRIMARY KEY(p4_id)) ENGINE=MEMORY\n";
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+}    
+
+{    
+    char *query =
+        "INSERT INTO finished\n"
+        " SELECT\n"
+        "   p4_id\n"
+        " FROM\n"
+        "   (SELECT DISTINCT\n"
+        "       p4Run.p4_id,\n"
+        "       p4SkyCellMap.p4_id as foo,\n"
+        "       p4Skyfile.p4_id as bar\n"
+        "   FROM p4Run\n"
+        "   JOIN p4SkyCellMap\n"
+        "       USING(p4_id)\n"
+        "   LEFT JOIN p4Skyfile\n"
+        "       USING(p4_id, skycell_id, tess_id)\n"
+        "   WHERE\n"
+        "       p4Run.state = 'run'\n" 
+        "   GROUP BY\n"
+        "       p4Run.p4_id\n"  
+        "   HAVING\n"
+        "       COUNT(p4SkyCellMap.p4_id) = COUNT(p4Skyfile.p4_id)\n"
+        " ) as Foo \n";
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+}
+{    
+    char *query =
+        "UPDATE p4Run\n"
+        "   SET p4Run.state = 'stop'\n"
+        " WHERE\n"
+        "   p4Run.p4_id =\n"
+        "   (SELECT DISTINCT\n"
+        "       p4_id\n"
+        "   FROM finished\n"
+        "   )\n";
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        return false;
+    }
+}
+
+#endif // if MYSQL5
 
     return true;