Changeset 8341 for trunk/ippTools/src/regtool.c

Timestamp:

Aug 14, 2006, 5:51:39 PM (20 years ago)

Author:

jhoblitt

Message:

fix SQL quries in pendingexpMode() & updateimfileMode() to handle multiple newExp rows

File:

• trunk/ippTools/src/regtool.c (modified) (11 diffs)

trunk/ippTools/src/regtool.c

@@ -69 +69 @@
 
     char *query =
-        "SELECT newExp.* FROM newExp"
+        "SELECT newExp.*"
+        "   FROM newExp"
+        " LEFT JOIN newImfile USING(exp_id)"
         " LEFT JOIN rawScienceExp USING(exp_id)"
-        " LEFT JOIN rawDetrendExp USING (exp_id)"
-        " LEFT JOIN newImfile USING (exp_id)"
-        " WHERE newExp.exp_id is NOT NULL"
-        " AND rawScienceExp.exp_id IS NULL"
-        " AND rawDetrendExp.exp_id IS NULL"
-        " AND newImfile.exp_id IS NULL"
-        " AND newExp.imfiles = (SELECT COUNT(exp_id)"
-                                " FROM rawImfile GROUP BY exp_id)";
+        " LEFT JOIN rawDetrendExp USING(exp_id)"
+        " WHERE"
+        "   newExp.exp_id IS NOT NULL"
+        "   AND newImfile.exp_id IS NULL"
+        "   AND rawScienceExp.exp_id IS NULL"
+        "   AND newExp.imfiles ="
+        "   (SELECT COUNT(exp_id) FROM rawImfile"
+        "       WHERE rawImfile.exp_id = newExp.exp_id)"
+        ;
 
     if (!p_psDBRunQuery(config->dbh, query)) {
@@ -176 +179 @@
     // exp_id is not in newImfile
     // that the correct count of imfiles is in rawImfile
-    psString query = psStringCopy("SELECT newExp.* FROM newExp LEFT JOIN newImfile USING(exp_id) LEFT JOIN rawScienceExp USING(exp_id) LEFT JOIN rawDetrendExp USING(exp_id) WHERE newExp.exp_id IS NOT NULL AND newImfile.exp_id IS NULL AND rawScienceExp.exp_id IS NULL AND newExp.imfiles = (SELECT COUNT(exp_id) FROM rawImfile GROUP BY exp_id)");
+
+    psString query = psStringCopy(
+        "SELECT newExp.*"
+        "   FROM newExp"
+        " LEFT JOIN newImfile USING(exp_id)"
+        " LEFT JOIN rawScienceExp USING(exp_id)"
+        " LEFT JOIN rawDetrendExp USING(exp_id)"
+        " WHERE"
+        "   newExp.exp_id IS NOT NULL"
+        "   AND newImfile.exp_id IS NULL"
+        "   AND rawScienceExp.exp_id IS NULL"
+        "   AND newExp.imfiles ="
+        "   (SELECT COUNT(exp_id) FROM rawImfile"
+        "       WHERE rawImfile.exp_id = newExp.exp_id)"
+        );
 
     {        
@@ -418 +435 @@
     // XXX search by the whole frame some imfiles without a newExp don't get
     // processed -- this may not be the correct thing to do
-    char *query = 
+    psString query = psStringCopy(
         "SELECT * FROM"
             " (SELECT newImfile.* FROM newImfile"
@@ -427 +444 @@
                 " AND rawScienceExp.exp_id IS NULL"
                 " AND rawDetrendExp.exp_id IS NULL) AS foo"
-        " %s"; // WHERE class is generated from exp_id, class, & class_id
+        ); // WHERE class is generated from exp_id, class, & class_id
 
     char *whereClaus = NULL;
@@ -437 +454 @@
         if (!status) {
             psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -exp_id");
+            psFree(query);
             return false;
         }
@@ -443 +461 @@
                 psError(PS_ERR_UNKNOWN, false, "failed to add item exp_id");
                 psFree(where);
+                psFree(query);
                 return false;
             }
@@ -449 +468 @@
         if (!status) {
             psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class");
+            psFree(query);
             return false;
         }
@@ -455 +475 @@
                 psError(PS_ERR_UNKNOWN, false, "failed to add item class");
                 psFree(where);
+                psFree(query);
                 return false;
             }
@@ -461 +482 @@
         if (!status) {
             psError(PS_ERR_UNKNOWN, false, "failed to lookup value for -class_id");
+            psFree(query);
             return false;
         }
@@ -467 +489 @@
                 psError(PS_ERR_UNKNOWN, false, "failed to add item class_id");
                 psFree(where);
+                psFree(query);
                 return false;
             }
@@ -472 +495 @@
 
         // there's not
-        whereClaus = psDBGenerateWhereSQL(where);
+        whereClaus = psDBGenerateWhereSQL(where, NULL);
         psFree(where);
-    }
-
-    if (!p_psDBRunQuery(config->dbh, query, whereClaus)) {
+        if (whereClaus) {
+            psStringAppend(&query, " %s", whereClaus);
+            psFree(whereClaus);
+        }
+    }
+
+    if (!p_psDBRunQuery(config->dbh, query)) {
         psError(PS_ERR_UNKNOWN, false, "database error");
-        psFree(whereClaus);
-        return false;
-    }
-    psFree(whereClaus);
+        psFree(query);
+        return false;
+    }
+    psFree(query);
 
     psArray *newImfiles = p_psDBFetchResult(config->dbh);