#!/usr/bin/perl -w

use Data::Dumper;
use IO::File;
use Getopt::Std;

use DBI;

use Tk;

my $DBNAME = 'critter';
my $DBHOST = '127.0.0.1'; # ATTN: localhost does not work as the driver will attempt to use a local UNIX socket
my $DBPORT = 3307;
my $DBUSER = 'dbadmin';
my $DBPASS = 'dbpass';

my $CRUSER = 'cruser';
my $CRDCAT = 'general';

=pod

Before using, please init the database:

CREATE DATABASE critter;
use critter;
CREATE TABLE critter ( id INT PRIMARY KEY AUTO_INCREMENT, text VARCHAR(1024), user CHAR(64) NOT NULL, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, cat char(64) DEFAULT 'general' );

=cut


sub open_db {
    my $dbh = DBI->connect("dbi:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $DBUSER, $DBPASS,  )
      or die "Couldn't connect to database: " . DBI->errstr;

    return $dbh;
}

sub insert_crit {
    my ($dbh, $text, $cat) = @_;
    $cat = $CRDCAT unless defined $cat;

    my $query = "INSERT INTO critter (text,user,cat) VALUES (?,?,?);";
    my $sth = $dbh->prepare($query);
    $sth->execute($text, $CRUSER, $cat);

    if ($sth->err()) {
        print STDERR "error: ".$sth->errstr." ".DBI->errstr."\n";
        return 0;
    }

    return 1;
}

sub get_last {
    my ($dbh,$period) = @_;

    my $query = "SELECT id,cat,date,text from critter where date >= date_sub(current_timestamp(), interval $period minute );";
    my $sth = $dbh->prepare($query);
    $sth->execute() or die "Couldn't execute statement: " . $sth->errstr;


    my $rows = [];
    while (@data = $sth->fetchrow_array()) {
        push @$rows, {
            'id'   => $data[0],
            'cat'  => $data[1],
            'date' => $data[2],
            'text' => $data[3],
        };
    }

    return $rows;
}

my %opts;
getopt('lc:DWMYd:',\%opts);

my $dbh = open_db();

#print Dumper(\%opts);

if ( exists $opts{l} ) {
    my $rows = get_last($dbh,24*60*30);
    foreach my $row (@$rows) {
        printf("%4d %-8s %s %s\n", $$row{id}, $$row{cat}, $$row{date}, $$row{text});
    }

}
elsif ( exists $opts{d} ) {
    # drop records
}
else {
    if ( scalar @ARGV == 0 || $opts{h} ) {
        print "Usage: critter [ -c \"category\" ] \"Text ....\"\n";
    }
    else {
        # insert new record
        my $text = join(' ',@ARGV);
        insert_crit($dbh, $text, $opts{c});
    }
}


$dbh->disconnect();


__END__

useful SQL queries :

select all rows newer than 180 minutes:
select * from critter where date >= date_sub(current_timestamp(), interval 180 minute );

date >= date_sub(current_timestamp(), interval 180 minute );