kkF7DRz6

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