qrss-tools/qrss-stats

#!/usr/bin/env perl

use strict;
use warnings;
use utf8;

use Getopt::Std;
use DBIx::Simple;
use SQL::Abstract;

my $opts = { f => undef };
getopts('f:', $opts)
  or die "Usage: $0 [-f <qrss.db>]\n";

die "Usage: $0 [-f <qrss.db>]\n"
  unless $opts->{f} and -f $opts->{f};

my $dbopts = { sqlite_unicode => 1, AutoCommit => 1, RaiseError => 1 };
my $dsn = sprintf 'dbi:SQLite:dbname=%s', $opts->{f};
my $db = DBIx::Simple->new($dsn, undef, undef, $dbopts);

my $query = 'SELECT f.id, f.title, COUNT(*) AS cnt, '
          . '  SUM(LENGTH(n.description)) AS len1, SUM(LENGTH(n.content)) AS len2 FROM feeds f'
          . '  JOIN news n ON (f.id = feedId) GROUP BY f.id ORDER BY cnt DESC';
my $stats = $db->query($query)->hashes;

printf "  ID  | News   | Summary   | Content   | Title \n";
print  "---------------" x 4, "\n";
my $total = { cnt => 0, len1 => 0, len2 => 0 };
foreach my $s (@{ $stats }) {
 utf8::encode($s->{title});
 $s->{len1} //= 0;
 $s->{len2} //= 0;
 printf " %4d | %6d | %8.1fK | %8.1fK | %s\n", $s->{id}, $s->{cnt},
   $s->{len1} / 1024, $s->{len2} / 1024, $s->{title};
 $total->{cnt}  += $s->{cnt};
 $total->{len1} += $s->{len1};
 $total->{len2} += $s->{len2};
}
print  "---------------" x 4, "\n";
printf "%5s | %6d | %8.1fK | %8.1fK | %s\n", 'Total', $total->{cnt},
   $total->{len1} / 1024, $total->{len2} / 1024, '';

exit 0;