2nVfPZ7S

1#!/usr/bin/perl -w
2
3use Data::Dumper;
4use IO::File;
5use Getopt::Std;
6
7use DBI;
8
9my $DBNAME = 'critter';
10my $DBHOST = '127.0.0.1'; # ATTN: localhost does not work as the driver will attempt to use a local UNIX socket
11my $DBPORT = 3306;
12my $DBUSER = 'dbuser';
13my $DBPASS = 'password';
14
15my $CRUSER = 'user';
16my $CRDCAT = 'general';
17
18=pod
19
20Before using, please init the database:
21
22CREATE DATABASE critter;
23use critter;
24CREATE TABLE critter ( id INT PRIMARY KEY AUTO_INCREMENT, text VARCHAR(4096), user CHAR(64) NOT NULL, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, cat char(64) DEFAULT 'general' );
25
26=cut
27
28
29sub open_db {
30    my $dbh = DBI->connect("dbi:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $DBUSER, $DBPASS,  )
31      or die "Couldn't connect to database: " . DBI->errstr;
32
33    return $dbh;
34}
35
36sub insert_crit {
37    my ($dbh, $text, $cat) = @_;
38    $cat = $CRDCAT unless defined $cat;
39
40    my $query = "INSERT INTO critter (text,user,cat) VALUES (?,?,?);";
41    my $sth = $dbh->prepare($query);
42    $sth->execute($text, $CRUSER, $cat);
43
44    if ($sth->err()) {
45        print STDERR "error: ".$sth->errstr." ".DBI->errstr."\n";
46        return 0;
47    }
48
49    return 1;
50}
51
52sub get_last {
53    my ($dbh,$period) = @_;
54
55    my $query = "SELECT id,cat,date,text from critter where date >= date_sub(current_timestamp(), interval $period minute );";
56    my $sth = $dbh->prepare($query);
57    $sth->execute() or die "Couldn't execute statement: " . $sth->errstr;
58
59
60    my $rows = [];
61    while (@data = $sth->fetchrow_array()) {
62        push @$rows, {
63            'id'   => $data[0],
64            'cat'  => $data[1],
65            'date' => $data[2],
66            'text' => $data[3],
67        };
68    }
69
70    return $rows;
71}
72
73my %opts;
74getopt('lc:DWMYd:',\%opts);
75
76my $dbh = open_db();
77
78if ( exists $opts{l} ) {
79    my $rows = get_last($dbh,24*60*30);
80    foreach my $row (@$rows) {
81        printf("%4d %-8s %s %s\n", $$row{id}, $$row{cat}, $$row{date}, $$row{text});
82    }
83
84}
85elsif ( exists $opts{d} ) {
86    # drop records
87}
88else {
89    if ( scalar @ARGV == 0 || $opts{h} ) {
90        print "Usage: \n".
91              "  critter [ -c \"category\" ] \"Text ....\" # add entry\n".
92              "  critter -l # list recent entries\n";
93    }
94    else {
95        # insert new record
96        my $text = join(' ',@ARGV);
97        insert_crit($dbh, $text, $opts{c});
98    }
99}
100
101
102$dbh->disconnect();