timeseries/tsplotsql

#!/usr/bin/perl
use v5.24;
use warnings;

use Getopt::Long;
use Pod::Usage;

use DBIx::SimpleConnect;
use TimeSeries;

=head1 NAME

tsplotsql - plot time series from sql query

=head1 SYNOPSIS

tsplot
[--dbname dbname]
[--finalresolution dpi]
[--legend-position pos]
[--output-format format ]
[--stacked]
[--style style]
[--time_t]
[--yrange min max]
[--log_y]
query

=head1 DESCRIPTION

This program expects time series data from an SQL query in column
format, I.e., each row contains a timestamp and the values for each
series. The column names are used for the legend.

The default legend position is "top right", same as with gnuplot.
Another frequently useful position (especially if you have lots of series)
is "below". Note that positions which consist of several words (such as
"top right") need to be passed to tsplotv as a single argument, so the 
space needs to be hidden from the shell by use of quotes or a backslash.

The default output format is "png", the default style is "lines".

See L<TimeSeries> for a description of possible legend positions,
output formats, and styles.

The --stacked option causes the time series to be stacked on top of each
other.

The --time_t option specifies that the timestamps are in seconds since
the epoch. Otherwise they are parsed by add_timestring function (which
in turn uses the parse_date function of the HTTP::Date module).

=cut
my $output_format ='png';
my $log_y =0;
my $time_t =0;
my $style = "lines";
my $stacked = 0;
my @yrange;
my $keeptempfiles;
my $finalresolution;
my $legend_position = 'top right';
my $dbname;

GetOptions('output_format|output-format=s' => \$output_format,
	   'log_y|log-y'		   => \$log_y,
	   'time_t'			   => \$time_t,
	   'style=s'			   => \$style,
	   'stacked'			   => \$stacked,
	   'yrange=s{2}'		   => \@yrange,
	   'keeptempfiles'		   => \$keeptempfiles,
	   'finalresolution=i'		   => \$finalresolution,
	   'legend-position=s'		   => \$legend_position,
           'dbname=s'                      => \$dbname,
          ) 
    or pod2usage(verbose => 0);
unless ($ARGV[0]) {
    pod2usage(verbose => 0);
}

binmode STDOUT, ':raw';

my $ts = TimeSeries->new(output_format => $output_format);
$ts->{keeptempfiles} = 1 if $keeptempfiles;
$ts->legend_position($legend_position);

my $dbh = DBIx::SimpleConnect->connect($dbname);
my $sth = $dbh->prepare($ARGV[0]);
$sth->execute();
my @legend = $sth->{NAME}->@*;
shift @legend;
$ts->legend(@legend);

while (my (@r) = $sth->fetchrow_array) {
    my ($timestamp, @values) = @r;
    if ($time_t) {
	$ts->add($timestamp, @values);
    } else {
	$ts->add_timestring($timestamp, @values);
    }
}
$ts->log_y($log_y);
$ts->style($style);
$ts->stacked($stacked);
$ts->yrange(@yrange);
$ts->finalresolution($finalresolution) if $finalresolution;

my $g = $ts->plot();
print $g