use strict; use warnings; 
use DBD::SQLite;
use DBI;

my $table='queues';
my $ccode='queue_name TEXT, jobs_pend INTEGER, jobs_run INTEGER'; 

my $storagefile='finddata';
my $dbh = DBI->connect( "dbi:SQLite:dbname=".$storagefile ) || die "Cannot connect: $storagefile $DBI::errstr";

remake($table,$ccode); 
my $insert; 
{
my $sql='INSERT INTO '.$table.'( queue_name , jobs_pend , jobs_run  ) values( ?,?,?)';    
$insert=$dbh->prepare($sql);
}

while (my $line=<DATA>) { 
  $line=~s/ //g;
  my (undef,$qn,$jp,$jr)=split('[|]',$line); 
  $insert->execute($qn,$jp,$jr); 
  } 


my $select; 
{
      my $sql="select * from ".$table;
      $select=$dbh->prepare($sql);
} 
  
  $select->execute();
  while(my $row=$select->fetchrow_arrayref){
  printf "| %-25s | %3d | %3d |\n",@$row;
#    print join ('|',@$row)."\n"; 
    } # ROW 
  $select->finish;

exit; 
sub remake { 
  my $table=shift; 
  my $code=shift; 
  $table='queues' unless ($table); 
  $code='queue_name TEXT, jobs_pend INTEGER, jobs_run INTEGER'  unless ($code); 
  $dbh->do("DROP TABLE IF EXISTS ".$table);
  my $sql="CREATE TABLE ".$table.'  ('.$code.')'; 
  print $sql."\n";
  $dbh->do($sql);
 } # redo; 

exit; 

__DATA__
| adice_long      |            5 |           39 |
| adice_ncsim     |            0 |            6 |
| adice_short     |          254 |          192 |
| calibre         |            0 |            0 |
| dsp_ncsim_gls   |            0 |            2 |
| dsp_ncsim_hp    |            0 |            2 |
| dsp_ncsim_lp    |            0 |            5 |
| dsp_ncsim_mp    |            0 |            5 |
| hcg_ncsim_comp  |            0 |            0 |
| hcg_ncsim_hp    |            0 |            9 |
| hcg_ncsim_lp    |            0 |            0 |
| hcg_ncsim_mp    |            0 |            0 |
| hcg_ncsim_short |            0 |            0 |
| ipdc_pte        |            0 |            0 |
| ncsim_long      |           41 |           78 |
| ncsim_lp        |            1 |            4 |
| ncsim_short     |            0 |           84 |
| normal          |          170 |           30 |
| spectreRF       |            0 |            1 |
| vcs             |            0 |            0 |


##</code><code>##

use strict; use warnings; 
use DBI; 

my $storagefile='finddata';
my $DBH = DBI->connect( "dbi:SQLite:dbname=".$storagefile ) || die "Cannot connect: $storagefile $DBI::errstr";

my $sql = 'SELECT queue_name,jobs_pend,jobs_run 
           FROM queues order by jobs_run DESC';
my $sth = $DBH->prepare( $sql );
$sth->execute();

# input
my %table = ();
my $recno = 0;
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $key = ($recno++ < 5) ? $name : 'other' ;
  $table{$key}{'pend'} += $pend;
  $table{$key}{'run'}  += $run;
}

# output
for my $key (sort keys %table){
  my @col = ($key);# col[0]
  $col[1] = $table{$key}{'pend'};
  $col[2] = $table{$key}{'run'};
  printf "| %-25s | %3d | %3d |\n",@col;
}


##</code><code>##

| adice_long                |   5 |  39 |
| adice_short               | 254 | 192 |
| ncsim_long                |  41 |  78 |
| ncsim_short               |   0 |  84 |
| normal                    | 170 |  30 |
| other                     |   1 |  34 |


##</code><code>##

$T I99,C=O
$S I99