url_log.pl

#
# Log URLs into PostgreSQL DB and keeps them up to one week
#
# CREATE TABLE urlirc (
#        id      bigserial       NOT NULL,
#        checked boolean         NOT NULL DEFAULT false,
#        time    timestamp       NOT NULL,
#        channel text            NOT NULL,
#        nick    text            NOT NULL,
#        url     text            NOT NULL,
#        PRIMARY KEY (id),
#        UNIQUE (url)
# );
#

use Irssi;
use Irssi::Irc;
use vars qw($VERSION %IRSSI);

use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);


$dbinfo = 'DBI:Pg:dbname=irssi';


# We could maintain a persistent connection, but if the database was
# restarted we would then need to restart the script.
# We ensure to use the prepared form which allows the parameters to be passed
# without any need to be escaped.  This allows the server to use the optimal
# method to deal with those.  However, since prepared statements are for a
# single handle, and that DBD driver only supports the prepare statement
# (there is no pg_query_param) then we tell it not to prepare the statement
# since we would need to do it everytime anyway in this case.
#
sub db_log {
        my ($server, $channel, $nick, $url) = @_;
        my $db = DBI->connect($dbinfo, '', '', {AutoCommit => 0});
        my $s = $db->prepare('INSERT INTO urlirc (time,channel,nick,url) ' .
            'VALUES(CURRENT_TIMESTAMP,$1,$2,$3)', {pg_server_prepare => 0});
        $s->execute($channel, $nick, $url);
        $db->do("DELETE FROM urlirc WHERE AGE(time) > '1 week'::interval");
        $db->commit();
        $db->disconnect();
}

sub url_check {
        my ($l) = @_;
        if (($l =~ /(.{1,2}tp\:\/\/[^\s]+)/) or ($l =~ /(www\..+)/)) {
                return $1;
        }
        return 0;
}

sub url_log {
        my ($server, $channel, $nick, $line) = @_;
        if ((my $url = url_check($line))) {
                db_log($server, $channel, $nick, $url);
        }
}


sub sig_logurl {
        my ($server, $data, $nick, $mask, $target) = @_;
        url_log($server, $target, $nick, $data);
}

sub sig_own {
        my ($server, $data, $target) = @_;
        url_log($server, $target, $server->{nick}, $data);
}

sub sig_topic {
        my ($server, $target, $data, $nick, $mask) = @_;
        url_log($server, $target, $nick, $data);
}

Irssi::signal_add_last('message public', 'sig_logurl');
Irssi::signal_add_last('message own_public', 'sig_own');
Irssi::signal_add_last('message topic', 'sig_topic');