Index: trunk/tools/psstatus
===================================================================
--- trunk/tools/psstatus	(revision 30468)
+++ 	(revision )
@@ -1,280 +1,0 @@
-#!/bin/env perl
-
-use strict;
-use warnings;
-
-use DBI;
-use PS::IPP::Config 1.01 qw( :standard );
-use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
-use Pod::Usage qw( pod2usage );
-use File::Temp qw(tempfile);
-
-my $limit = 0;
-my $running;
-my $finished;
-my $req_faulted;
-my $job_faulted;
-my $dbname;
-my $dbserver;
-my $dbuser;
-my $dbpassword;
-my $verbose;
-my $finishing;
-
-GetOptions(
-    'running|r',     \$running,
-    'finished|f',    \$finished,
-    'finishing',     \$finishing,
-    'req_faulted',   \$req_faulted,
-    'job_faulted',   \$job_faulted,
-    'limit|l=i',     \$limit,
-    'dbname=s',      \$dbname,
-    'verbose|v',     \$verbose,
-) or pod2usage (2);
-
-pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV ;
-
-$running = 1 if (!$finished and !$finishing and !$running);
-
-my $no_args = ! (defined $running or defined $finished or defined $req_faulted or defined $job_faulted );
-
-die "cannot supply --running and --finished\n" if ($running and $finished);
-    
-
-my $ipprc =  PS::IPP::Config->new();
-my $dbh = getDBHandle();
-
-my $running_query = "
-SELECT 
-    unfinishedReq.req_id,
-    name,
-    label,
-    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(depcount, 0) AS 'Image updates'
-    -- timestamp AS 'last state change (UTC)'
-FROM (
-    SELECT
-        req_id,
-        name,
-        label,
-        reqType,
-        pstampRequest.state,
-        pstampRequest.fault,
-        timestamp
-FROM pstampRequest
-    WHERE pstampRequest.state = 'run' or pstampRequest.state ='new' 
-        OR pstampRequest.state = 'run.wait'
-) as unfinishedReq
-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 = '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(distinct dep_id) AS depcount
-FROM pstampRequest
-    JOIN pstampJob USING(req_id)
-    JOIN pstampDependent USING(dep_id) 
-    WHERE (pstampRequest.state = 'run' or pstampRequest.state = 'new' 
-            OR pstampRequest.state = 'run.wait')
-        AND dep_id > 0
-        AND pstampDependent.state = 'full'
-    GROUP BY req_id
-) as depCounts
-ON unfinishedReq.req_id = depCounts.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' 
-        OR pstampRequest.state = 'run.wait')
-        AND pstampJob.state = 'run'
-        AND pstampJob.fault = 0
-    GROUP BY req_id
-) as runningJobs
-ON unfinishedReq.req_id = runningJobs.req_id
-
-LEFT JOIN (
-    SELECT req_id,
-        count(job_id) AS faultedJobs
-    FROM pstampRequest JOIN pstampJob USING(req_id)
-    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
-) as faultedJobs
-ON unfinishedReq.req_id = faultedJobs.req_id
-LEFT JOIN (
-    SELECT req_id,
-        count(job_id) AS completedJobs
-    FROM pstampRequest JOIN pstampJob USING(req_id)
-    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new'
-        OR pstampRequest.state = 'run.wait')
-        AND pstampJob.state = 'stop'
-    GROUP BY req_id
-) as finishedJobs
-ON unfinishedReq.req_id = finishedJobs.req_id
-
-ORDER by unfinishedReq.req_id
-";
-
-my $finishing_query = "
-SELECT
-    req_id,
-    name,
-    label,
-    reqType,
-    pstampRequest.state,
-    pstampRequest.fault,
-    count(job_id) as 'Finished jobs',
-    timestamp as 'last state change'
-FROM pstampRequest
-    JOIN pstampJob USING(req_id)
-WHERE pstampRequest.state ='run'
-    AND (
-        SELECT count(job_id) FROM pstampJob
-            WHERE pstampJob.req_id = pstampRequest.req_id
-                AND pstampJob.state != 'stop'
-    ) = 0
-GROUP BY req_id
-ORDER by req_id
-";
-
-my $finished_query = "
-SELECT 
-    finishedReq.req_id,
-    name,
-    label,
-    reqType,
-    rowcount AS 'Num Rows',
-    jobcount AS 'Total Jobs',
-    IFNULL(success, 0) AS 'Successful Jobs',
-    IFNULL(faulted,0) AS 'Faulted Jobs',
-    IFNULL(depcount, 0) AS 'Image updates completed',
-    timestamp AS 'Completion Time (UTC)'
-FROM (
-    SELECT
-    req_id,
-    name,
-    label,
-    reqType,
---    pstampRequest.state,
---    pstampRequest.fault,
-    timestamp
-FROM pstampRequest
-    WHERE pstampRequest.state = 'stop'
-        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,
-        count(distinct dep_id) AS depcount
-FROM pstampRequest JOIN pstampJob USING(req_id)
-    WHERE pstampRequest.state = 'stop'
-        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
-        AND dep_id > 0
-    GROUP BY req_id
-) as depCounts
-ON finishedReq.req_id = depCounts.req_id
-
-LEFT JOIN (
-    SELECT req_id,
-        count(job_id) AS success
-    FROM pstampRequest JOIN pstampJob USING(req_id)
-    WHERE pstampRequest.state = 'stop'
-        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
-        AND pstampJob.fault = 0
-    GROUP BY req_id
-) as successfulJobs
-ON finishedReq.req_id = successfulJobs.req_id
-LEFT JOIN (
-    SELECT req_id,
-        count(job_id) AS faulted
-    FROM pstampRequest JOIN pstampJob USING(req_id)
-    WHERE pstampRequest.state = 'stop'
-        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
-        AND pstampJob.fault > 0
-    GROUP BY req_id
-) as faultedJobs
-ON finishedReq.req_id = faultedJobs.req_id
-ORDER by timestamp desc
-";
-
-
-my $sql;
-if ($finished) {
-    $sql = $finished_query;
-} elsif ($running) {
-    $sql = $running_query;
-} elsif ($finishing) {
-    $sql = $finishing_query;
-} else {
-    die "can't happen";
-}
-
-if ($limit and !$no_args) {
-    $sql .= " LIMIT $limit";
-}
-
-{
-    print STDERR "$sql\n" if $verbose;
-    my @args = ( "--table", "--user=$dbuser",  "--host=$dbserver", "--password=$dbpassword", $dbname);
-
-    push @args, '--verbose' if $verbose;
-
-    open my $mysql_pipe, "|-", 'mysql', @args or die "failed to run mysql\n";
-    print $mysql_pipe "$sql;\n";
-    close $mysql_pipe;
-
-}
-
-exit 0;
-
-
-sub getDBHandle {
-    $dbserver = metadataLookupStr($ipprc->{_siteConfig}, "PS_DBSERVER");
-    $dbuser = metadataLookupStr($ipprc->{_siteConfig}, "DS_DBUSER");
-    $dbpassword = metadataLookupStr($ipprc->{_siteConfig}, "DS_DBPASSWORD");
-    if (!$dbname) {
-        $dbname = metadataLookupStr($ipprc->{_siteConfig}, "PS_DBNAME");
-    }
-
-    die "database configuration set up" unless defined($dbserver) and defined($dbuser)
-        and defined($dbpassword) and defined($dbname);
-
-    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
-
-    my $dbh = DBI->connect($dsn, $dbuser, $dbpassword) 
-        or die "Cannot connect to database.\n";
-
-    return $dbh;
-}
-
-