Changeset 36968

Timestamp:

Jul 2, 2014, 5:28:31 PM (12 years ago)

Author:

watersc1

Message:

Fixed major issue where if a stage_id is used in a different stage, it causes infinite problems.

Location:

trunk/ippTools

Files:

• share/remotetool_definebyquery_cam.sql (modified) (1 diff)
• share/remotetool_definebyquery_chip.sql (modified) (1 diff)
• share/remotetool_definebyquery_stack.sql (modified) (1 diff)
• share/remotetool_definebyquery_warp.sql (modified) (1 diff)
• src/remotetool.c (modified) (6 diffs)

trunk/ippTools/share/remotetool_definebyquery_cam.sql

@@ -1 @@
-SELECT 
-       cam_id AS stage_id
-FROM camRun 
-       LEFT JOIN remoteComponent ON (remoteComponent.stage_id = camRun.cam_id) 
-       LEFT JOIN remoteRun ON (
-            remoteComponent.remote_id = remoteRun.remote_id AND 
-            remoteRun.label = camRun.label
-            )
+SELECT stage_id FROM
+
+( SELECT cam_id AS stage_id
+    FROM camRun
 WHERE
-camRun.state = 'new' AND
-(remoteRun.stage = 'camera' OR remoteRun.state IS NULL) AND
-remoteComponent.remote_id IS NULL
+camRun.state = 'new'
+-- where hook %s
+GROUP BY cam_id
+) AS W
+
+LEFT JOIN
+
+(SELECT remote_id,remoteRun.stage,remoteRun.label,remoteComponent.stage_id,remoteRun.state 
+   FROM remoteRun 
+   JOIN remoteComponent USING (remote_id)
+WHERE
+remoteRun.stage = 'camera'
+-- where hook %s
+) AS R
+USING (stage_id)
+WHERE R.remote_id IS NULL

trunk/ippTools/share/remotetool_definebyquery_chip.sql

@@ -1 @@
-SELECT 
-       chip_id AS stage_id
-FROM chipRun 
-       LEFT JOIN remoteComponent ON (remoteComponent.stage_id = chipRun.chip_id) 
-       LEFT JOIN remoteRun ON (
-            remoteComponent.remote_id = remoteRun.remote_id AND 
-            remoteRun.label = chipRun.label
-            )
+SELECT stage_id FROM
+
+( SELECT chip_id AS stage_id
+    FROM chipRun
 WHERE
-chipRun.state = 'new' AND
-(remoteRun.stage = 'chip' OR remoteRun.stage IS NULL) AND
-remoteComponent.remote_id IS NULL
+chipRun.state = 'new'
+-- where hook %s
+GROUP BY chip_id
+) AS W
+
+LEFT JOIN
+
+(SELECT remote_id,remoteRun.stage,remoteRun.label,remoteComponent.stage_id,remoteRun.state 
+   FROM remoteRun 
+   JOIN remoteComponent USING (remote_id)
+WHERE
+remoteRun.stage = 'chip'
+-- where hook %s
+) AS R
+USING (stage_id)
+WHERE R.remote_id IS NULL
+
 
 

trunk/ippTools/share/remotetool_definebyquery_stack.sql

@@ -1 @@
-SELECT 
-       stack_id AS stage_id
-FROM stackRun 
-       LEFT JOIN remoteComponent ON (remoteComponent.stage_id = stackRun.stack_id) 
-       LEFT JOIN remoteRun ON (
-            remoteComponent.remote_id = remoteRun.remote_id AND 
-            remoteRun.label = stackRun.label
-            )
+SELECT stage_id FROM
+
+( SELECT stack_id AS stage_id
+    FROM stackRun
 WHERE
-stackRun.state = 'new' AND
-(remoteRun.stage = 'stack' OR remoteRun.state IS NULL) AND
-remoteComponent.remote_id IS NULL
+stackRun.state = 'new'
+-- where hook %s
+GROUP BY stack_id
+) AS W
+
+LEFT JOIN
+
+(SELECT remote_id,remoteRun.stage,remoteRun.label,remoteComponent.stage_id,remoteRun.state 
+   FROM remoteRun 
+   JOIN remoteComponent USING (remote_id)
+WHERE
+remoteRun.stage = 'stack'
+-- where hook %s
+) AS R
+USING (stage_id)
+WHERE R.remote_id IS NULL

trunk/ippTools/share/remotetool_definebyquery_warp.sql

@@ -1 @@
-SELECT warp_id AS stage_id FROM (
-SELECT warp_id, count(warpSkyCellMap.skycell_id) AS N
+SELECT stage_id FROM
+
+( SELECT warp_id AS stage_id, count(warpSkyCellMap.skycell_id) AS N
     FROM warpRun
     LEFT JOIN warpSkyCellMap USING(warp_id)
-    LEFT JOIN remoteComponent ON (remoteComponent.stage_id = warpRun.warp_id)
-    LEFT JOIN remoteRun ON (remoteComponent.remote_id = remoteRun.remote_id
-                        AND remoteRun.label = warpRun.label             
-                        AND (remoteRun.stage = 'warp' OR remoteRun.state IS NULL)
-                        )
-WHERE warpRun.state = 'new'
-AND (remoteRun.stage = 'warp' OR remoteRun.state IS NULL)
--- AND remoteComponent.remote_id IS NULL
+WHERE
+warpRun.state = 'new'
 -- where hook %s
 GROUP BY warp_id
-) AS warpTry
-WHERE N != 0
+HAVING N != 0
+) AS W
 
+LEFT JOIN
+
+(SELECT remote_id,remoteRun.stage,remoteRun.label,remoteComponent.stage_id,remoteRun.state 
+   FROM remoteRun 
+   JOIN remoteComponent USING (remote_id)
+WHERE
+remoteRun.stage = 'warp'
+-- where hook %s
+) AS R
+USING (stage_id)
+WHERE R.remote_id IS NULL
+

trunk/ippTools/src/remotetool.c

@@ -94 +94 @@
 
   // Get a list of the things we can insert into a new component
+  // CZW: these labels no longer need to be linked to the table, as I've split stageRun and remoteRun into sub-queries.
   psString query = NULL;
   psString whereOption = psStringCopy("");
@@ -104 +105 @@
     
     if (label) {
-      psStringAppend(&query, " AND (chipRun.label = '%s')", label);
+      psStringAppend(&whereOption, "\n AND (label = '%s')", label);
     }
   }
@@ -115 +116 @@
     
     if (label) {
-      psStringAppend(&query, " AND (camRun.label = '%s')", label);
+      psStringAppend(&whereOption, "\n AND (label = '%s')", label);
     }
   }    
@@ -128 +129 @@
       // Because warp has to check to see if something other than the state is set correctly, I have to use this wonky way to pass the label in.
       //      psStringAppend(&query, " AND (warpRun.label = '%s')", label);
-      psStringAppend(&whereOption, "\n AND (warpRun.label = '%s')", label);
+      psStringAppend(&whereOption, "\n AND (label = '%s')", label);
     }
   }    
@@ -139 +140 @@
     
     if (label) {
-      psStringAppend(&query, " AND (stackRun.label = '%s')", label);
+      psStringAppend(&whereOption, "\n AND (label = '%s')", label);
     }
   }    
@@ -152 +153 @@
     psFree(limitString);
   }
-  if (!p_psDBRunQueryF(config->dbh, query,whereOption)) {
+  if (!p_psDBRunQueryF(config->dbh, query,whereOption,whereOption)) { // This needs to be here twice because we need to restrict on label twice.
     psError(PS_ERR_UNKNOWN, false, "database error");
     psFree(query);