#!/usr/bin/env perl
# -*-mode:cperl; indent-tabs-mode: nil-*-

## Grab a bunch of Bucardo stats for cacti
##
## Copyright 2007 Greg Sabino Mullane <greg@turnstep.com>

use strict;
use warnings;
use DBI;

our $VERSION = '1.0.1'; 

my $DBUSER = 'bucardo_readonly';
my $DBOPT = {AutoCommit=>0,RaiseError=>1,PrintError=>0};
my $verbose = 0;

my $dbh =
  {
   dbname1 => DBI->connect('dbi:Pg:dbname=dbname1', $DBUSER, '', $DBOPT),
   bucardo => DBI->connect('dbi:Pg:dbname=bucardo', $DBUSER, '', $DBOPT),
  };

## Specialized due to constraint exclusion funkiness:
my $minutesback = 5;
my $SQL = "SELECT now()-'$minutesback minutes'::interval";
my $ARG =
  {
   TS => $dbh->{bucardo}->selectall_arrayref($SQL)->[0][0],
};
$dbh->{bucardo}->do('SET constraint_exclusion = true');

my $info = {};
my $query;
my $db;
my @q;
while (<DATA>) {
  next if /^\s*#/ or /^\s*$/;
  if (/^DB:(\w+)/) {
    my $newdb = $1;
    push @q, [$db,$query] if defined $query;
    $db = $newdb;
    $query = '';
  }
  else {
    $query .= $_;
  }
}
push @q, [$db,$query] if defined $query and length $query;

for (@q) {
  my ($db,$sql) = @$_;
  chomp $sql;
  $sql =~ s/ARG\{(\w+)\}/$ARG->{$1}/ge;
  $verbose and warn "SQL: -->$sql<--\n";
  my $sth = $dbh->{$db}->prepare($sql);
  $sth->execute();
  for my $row (@{$sth->fetchall_arrayref({})}) {
    for my $key (keys %$row) {
      $info->{$key} = $row->{$key};
    }
    last;
  }
}

## Aggregate some info
$info->{swap_rows} = $info->{swap_inserts} + $info->{swap_updates} + $info->{swap_deletes};

$info->{total_rows} = $info->{swap_rows} + $info->{fullcopy_rows} + $info->{pushdelta_rows};

$info->{total_syncs} = $info->{swap_syncs} + $info->{fullcopy_syncs} + $info->{pushdelta_syncs};

## Track each type of abort error
$query = qq{
SELECT whydie
FROM   bucardo.q
WHERE  cdate >= '$ARG->{TS}'
AND    aborted IS NOT NULL
UNION ALL
SELECT whydie
FROM   freezer.master_q
WHERE  cdate >= '$ARG->{TS}'
AND    aborted IS NOT NULL
};
my %abort;
for (qw/ deadlock serialize route killed unknown/) {
  $abort{$_} = 0;
}
$info->{aborts} = 0;
for my $row (@{$dbh->{bucardo}->selectall_arrayref($query)}) {
  my $reason = $row->[0];
  $info->{aborts}++;
  $reason ||= '';
  if ($reason =~ /deadlock/) {
    $abort{deadlock}++;
  }
  elsif ($reason =~ /could not serialize/) {
    $abort{serialize}++;
  }
  elsif ($reason =~ /route to host/) {
    $abort{route}++;
  }
  elsif ($reason =~ /unexpectedly/) {
    $abort{killed}++;
  }
  else {
    $abort{unknown}++;
  }
}

for (keys %abort) {
  $info->{"abort_$_"} = $abort{$_};
}


for (values %$dbh) { $_->disconnect(); }

my $string = join ' ' => map { "$_:$info->{$_}" } sort keys %$info;
print "$string\n";

exit;

__DATA__
## Format: DB:database\ncommand
## Order is important

## Number of delta rows awaitin'
DB:bcs
SELECT count(*) AS deltarows FROM bucardo.bucardo_delta

## Number of track rows awaitin'
DB:bcs
SELECT count(*) AS trackrows FROM bucardo.bucardo_track

## Number of transactions yet to be synced
DB:bcs
SELECT count(distinct txntime) AS deltatxns FROM bucardo.bucardo_delta

## Number of swap inserts, updates, deletes in the last 5 minutes
DB:bucardo
SELECT coalesce(qi,0)+coalesce(fi,0) AS swap_inserts,
       coalesce(qd,0)+coalesce(fd,0) AS swap_deletes,
       coalesce(qu,0)+coalesce(fu,0) AS swap_updates
FROM 
  (SELECT sum(inserts) AS qi, sum(updates) AS qu, sum(deletes) AS qd FROM bucardo.q
   WHERE cdate >= 'ARG{TS}' AND synctype = 'swap') AS a,
  (SELECT sum(inserts) AS fi, sum(updates) AS fu, sum(deletes) AS fd FROM freezer.master_q 
   WHERE cdate >= 'ARG{TS}' AND synctype = 'swap') AS b;

## Number of fullcopy rows (always inserts) in last 5 minutes
DB:bucardo
SELECT coalesce(x,0)+coalesce(y,0) AS fullcopy_rows
FROM 
  (SELECT sum(inserts) AS x FROM bucardo.q
   WHERE cdate >= 'ARG{TS}' AND synctype = 'fullcopy') AS a,
  (SELECT sum(inserts) AS y FROM freezer.master_q 
   WHERE cdate >= 'ARG{TS}' AND synctype = 'fullcopy') AS b;

## Number of pushdelta rows (always inserts) in last 5 minutes
DB:bucardo
SELECT coalesce(x,0)+coalesce(y,0) AS pushdelta_rows
FROM 
  (SELECT sum(inserts) AS x FROM bucardo.q
   WHERE cdate >= 'ARG{TS}' AND synctype = 'pushdelta') AS a,
  (SELECT sum(inserts) AS y FROM freezer.master_q 
   WHERE cdate >= 'ARG{TS}' AND synctype = 'pushdelta') AS b;

## Number of swap syncs run in the last 5 minutes
DB:bucardo
SELECT x+y AS swap_syncs
FROM
  (SELECT count(*) AS x
   FROM   bucardo.q
   WHERE  cdate >= 'ARG{TS}'
   AND    synctype = 'swap'
   AND    ended IS NOT NULL) AS a,
  (SELECT count(*) AS y
   FROM   freezer.master_q
   WHERE  cdate >= 'ARG{TS}'
   AND    synctype = 'swap'
   AND    ended IS NOT NULL) AS b

## Number of fullcopy syncs run in the last 5 minutes
DB:bucardo
SELECT x+y AS fullcopy_syncs
FROM
  (SELECT count(*) AS x
   FROM   bucardo.q
   WHERE  cdate >= 'ARG{TS}'
   AND    synctype = 'fullcopy'
   AND    ended IS NOT NULL) AS a,
  (SELECT count(*) AS y
   FROM   freezer.master_q
   WHERE  cdate >= 'ARG{TS}'
   AND    synctype = 'fullcopy'
   AND    ended IS NOT NULL) AS b


## Number of swap sync run in the last 5 minutes
DB:bucardo
SELECT x+y AS pushdelta_syncs
FROM
  (SELECT count(*) AS x
   FROM   bucardo.q
   WHERE  cdate >= 'ARG{TS}'
   AND    synctype = 'pushdelta'
   AND    ended IS NOT NULL) AS a,
  (SELECT count(*) AS y
   FROM   freezer.master_q
   WHERE  cdate >= 'ARG{TS}'
   AND    synctype = 'pushdelta'
   AND    ended IS NOT NULL) AS b

## Number of PIDs created in last 5 minutes
DB:bucardo
SELECT count(*) AS pids
FROM bucardo.audit_pid WHERE birthdate >= 'ARG{TS}'