Changeset 30464 for trunk/tools/psstatus

Timestamp:

Feb 2, 2011, 2:58:44 PM (15 years ago)

Author:

bills

Message:

add rowcount and jobcount

File:

• trunk/tools/psstatus (modified) (5 diffs)

trunk/tools/psstatus

@@ -50 +50 @@
     name,
     label,
-    reqType
+    reqType,
     state,
     fault,
+    rowcount AS 'Num Rows',
+    jobcount AS 'Total Jobs',
+    IFNULL(completedJobs,0) AS 'Completed Jobs',
     IFNULL(runningJobs, 0) AS 'Pending Jobs',
-    IFNULL(faultedJobs, 0) AS 'Faulted Jobs',
-    IFNULL(completedJobs,0) AS 'Completed Jobs',
-    timestamp AS 'last state change (UTC)'
+    IFNULL(faultedJobs, 0) AS 'Faulted Jobs'
+    -- timestamp AS 'last state change (UTC)'
 FROM (
     SELECT
-    req_id,
-    name,
-    label,
-    reqType,
-    pstampRequest.state,
-    pstampRequest.fault,
-    timestamp
+        req_id,
+        name,
+        label,
+        reqType,
+        pstampRequest.state,
+        pstampRequest.fault,
+        timestamp
 FROM pstampRequest
-    WHERE pstampRequest.state = 'run' or pstampRequest.state ='new'
+    WHERE pstampRequest.state = 'run' or pstampRequest.state ='new' 
+        OR pstampRequest.state = 'run.wait'
 ) as unfinishedReq
+JOIN (
+    SELECT req_id,
+        count(job_id)          AS jobcount,
+        count(distinct rownum) AS rowcount
+FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state = 'new' 
+        OR pstampRequest.state = 'run.wait')
+    GROUP BY req_id
+) as rowCounts
+ON unfinishedReq.req_id = rowCounts.req_id
 LEFT JOIN (
     SELECT req_id,
         count(job_id) AS runningJobs
     FROM pstampRequest JOIN pstampJob USING(req_id)
-    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new' 
+        OR pstampRequest.state = 'run.wait')
         AND pstampJob.state = 'run'
         AND pstampJob.fault = 0
@@ -83 +97 @@
         count(job_id) AS faultedJobs
     FROM pstampRequest JOIN pstampJob USING(req_id)
-    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
-        AND pstampJob.state = 'run'
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new'
+        OR pstampRequest.state = 'run.wait')
+        -- AND pstampJob.state = 'run'
         AND pstampJob.fault > 0
     GROUP BY req_id
@@ -93 +108 @@
         count(job_id) AS completedJobs
     FROM pstampRequest JOIN pstampJob USING(req_id)
-    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new'
+        OR pstampRequest.state = 'run.wait')
         AND pstampJob.state = 'stop'
     GROUP BY req_id
@@ -130 +146 @@
     label,
     reqType,
+    rowcount AS 'Num Rows',
+    jobcount AS 'Total Jobs',
     IFNULL(success, 0) AS 'Successful Jobs',
     IFNULL(faulted,0) AS 'Faulted Jobs',
@@ -146 +164 @@
         AND date_add(timestamp, interval 1 day) >=utc_timestamp()
 ) as finishedReq
+
+LEFT JOIN (
+    SELECT req_id,
+        count(job_id)          AS jobcount,
+        count(distinct rownum) AS rowcount
+FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE pstampRequest.state = 'stop'
+        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
+    GROUP BY req_id
+) as rowCounts
+ON finishedReq.req_id = rowCounts.req_id
+
 LEFT JOIN (
     SELECT req_id,