#!/usr/bin/perl -w
##
# Return some statistics about a sqlite3 database.
#
# Set:
#   [sqlite3stats]
#   env.DBFILE "/the/filename/to/check"
#   env.DBNAME "the name of the database"
#   env.CATEGORY "SQLite3"
#
# @author Justin Fletcher
# @copyright Copyright (C) 2012, available under BSD license.
#
# Munin configuration details:
#
#%# family=auto
#%# capabilities=autoconf suggest
#

use DBI;
use Data::Dumper;
use Getopt::Long;

my $dbfile = undef;
my $dbname = "Database";
my $category = "SQLite3";

# Override the database file
$dbfile = $ENV{'DBFILE'} if (defined $ENV{'DBFILE'});
$dbname = $ENV{'DBNAME'} if (defined $ENV{'DBNAME'});
$category = $ENV{'CATEGORY'} if (defined $ENV{'CATEGORY'});


if (!defined $dbfile)
{
    print STDERR "DBFILE must be defined to select the file to use\n";
    print STDERR "DBNAME can be used to set the database type\n";
    print STDERR "CATEGORY can be used to set the database type\n";
    exit(1);
}

# The queries that we support
my %queries = (
        'size'      => \&dbsize,
        'tablerows' => \&tablerows,
    );

my ($qtype) = ($0 =~ /_([^\/]+)$/);
$qtype //= 'tablerows';

GetOptions('type=s' => \$qtype);

my $arg = shift || '';

if ($arg eq 'autoconf')
{
    if (-f "$dbfile")
    {
        print "yes\n";
    }
    else
    {
        print "no (file doesn't exist)\n";
    }
}
elsif ($arg eq 'suggest')
{
    print map { "$_\n" } keys %queries;
}
elsif ($arg eq 'config')
{
    # Find the configuration, and return it
    if (!defined $queries{$qtype})
    {
        print STDERR "Invalid type '$qtype'\n";
        exit(1);
    }
    my $ref;
    eval {
        $ref = &{ $queries{$qtype} }('config');
    };
    if ($@)
    {
        print STDERR "Failed to get configuration: $@\n";
        exit(1)
    }

    print "graph_title $dbname: $ref->{'_title'}\n";
    print "graph_category $category\n";
    print "graph_vlabel $ref->{'_yaxis'}\n";
    
    if (defined $ref->{'_min'} ||
        defined $ref->{'_max'})
    {
        print "graph_args";
        if (defined $ref->{'min'})
        {
            print " -l $ref->{'min'}";
        }
        if (defined $ref->{'max'})
        {
            print " -u $ref->{'max'}";
        }
        print " -r";
        print "\n";
    }

    # Report the results
    for my $label (sort grep { /^[^_]/ && !/^sqlite_/ } keys %$ref)
    {
        my $mlabel = $label;
        $mlabel =~ s/[^A-Za-z0-9]//g;
        print "$mlabel.label $label\n";
        print "$mlabel.info $label: $ref->{$label}\n";
    }
}
elsif ($arg eq '' ||
       $arg eq 'fetch')
{
    # Find the configuration, and return it
    if (!defined $queries{$qtype})
    {
        print STDERR "Invalid type '$qtype'\n";
        exit(1);
    }
    my $ref;
    eval {
        $ref = &{ $queries{$qtype} }('fetch');
    };
    if ($@)
    {
        print STDERR "Failed to get configuration: $@\n";
        exit(1)
    }

    # Report the results
    for my $label (sort grep { /^[^_]/ && !/^sqlite_/ } keys %$ref)
    {
        my $mlabel = $label;
        $mlabel =~ s/[^A-Za-z0-9]//g;
        my $value = $ref->{$label};
        if (!defined $value)
        {
            $value = "UNDEFINED";
        }
        print "$mlabel.value $value\n";
    }
}



##
# Connect to the SQLite database.
#
# @return database handle
sub dbconnect
{
    my $dbh = DBI->connect("dbi:SQLite:$dbfile", "", "");
    return $dbh;
}


##
# Database size.
#
# @param[in] $type   Query type: 'config', 'fetch'
#
# @return 'config': hashref of the configuration for the rows
#                   '_title' => Title of the graphs
#                   '_yaxis' => Label for the Axis
#                   <label>  => description of the data returned
#         'fetch':  hashref of the configuration for the rows
#                   <label>  => the value
sub dbsize
{
    my ($type) = @_;
    if ($type eq 'config')
    {
        return {
            '_title' => 'File size',
            '_yaxis' => 'size',
            'Size' => 'Database size on disc'
        };
    }
    if ($type eq 'fetch')
    {
        my $size = -s $dbfile;
        return {
            'Size' => $size
        };
    }
}


##
# Table sizes.
#
# @param[in] $type   Query type: 'config', 'fetch'
#
# @return 'config': hashref of the configuration for the rows
#                   '_title' => Title of the graphs
#                   '_yaxis' => Label for the Axis
#                   <label>  => description of the data returned
#         'fetch':  hashref of the configuration for the rows
#                   <label>  => the value
sub tablerows
{
    my ($type) = @_;

    my $dbh = dbconnect();
    my $master = query($dbh,
                       "SELECT type,name,tbl_name,rootpage,sql ".
                       "  FROM sqlite_master");
    # type:
    #   'index': 'name' is the index name
    #            'tbl_name' is the table it applies to
    #   'table': 'name' is the table name
    #            'tbl_name' is the same as 'name'
    my %tables = map { $_->{'name'} => $_ }
                     grep { $_->{'type'} eq 'table' } @$master;

    if ($type eq 'config')
    {
        return {
            '_title' => 'Table rows',
            '_yaxis' => 'number of rows',
            (map { $_ => "rows in table $_" } keys %tables)
        };
    }
    if ($type eq 'fetch')
    {

        # Lets count the number of rows in each table
        my %rowcount;
        for my $tablename (keys %tables)
        {
            my $row = query($dbh, "SELECT MAX(ROWID) AS max ".
                                  "  FROM $tablename");
            $rowcount{$tablename} = $row->[0]->{'max'};
        }

        return \%rowcount;
    }
}


##
# SQL query.
# 
# @param[in] $dbh   Database handle
# @param[in] $sql   SQL query string
# @param[in] @args  Arguments for the query string
#
# @return array of rows as hashref
sub query
{
    my ($dbh, $sql, @args) = @_;
    my $sth = $dbh->prepare($sql);
    $sth->execute(@args);
    return $sth->fetchall_arrayref({});
}