1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
use warnings;
use strict;
use DBI;
use Irssi;
use Irssi::Irc;
use POSIX qw/strftime/;
use vars qw($VERSION %IRSSI);
# Requirements:
# - postgresql
# - postgresql-contrib (pg_trgm)
$VERSION = "1.0";
%IRSSI = (
authors => "Aaron Bieber",
contact => "deftly\@gmail.com",
name => "irssi_logger",
description => "Logs chats to a PostgreSQL database.",
license => "BSD",
url => "https://github.com/qbit/irssi_logger",
);
my $user = $ENV{LOGNAME} || $ENV{USER} || getpwuid($<);
my $dbh;
my $sql = qq~
insert into logs (logdate, nick, log, channel) values (?, ?, ?, ?)
~;
my $search = qq~
SELECT log, similarity(log, ?) AS sml
FROM logs
WHERE log % ? and
channel = ?
ORDER BY sml DESC, log
LIMIT 1;
~;
my $check_db = qq~
select true as exists from pg_tables where tablename = 'logs';
~;
my $create = qq~
CREATE TABLE logs (
id serial not null,
dateadded timestamp without time zone default now(),
logdate timestamp without time zone default now(),
nick text not null,
log text not null,
channel text not null
)
~;
my $init_pg_trgm = qq~
CREATE extension pg_trgm
~;
my $create_trgm_idx = qq~
CREATE index logs_trgm_idx on logs USING gist (log gist_trgm_ops);
~;
my $create_date_idx = qq~
CREATE index logs_date_idx on logs (logdate)
~;
my $create_chan_idx = qq~
CREATE index logs_nick_idx on logs (nick)
~;
sub db_init {
my ($dbname, $dbuser, $dbpass) = @_;
# my $mdbh = DBI->connect("dbi:Pg:dbname=$dbname", $dbuser, $dbpass) || Irssi::print("Can't connect to postgres! " . DBI::errstr);
my $mdbh = DBI->connect("DBI:Pg:database=$dbname;host=192.168.1.109;port=30432", $dbuser, $dbpass) || Irssi::print("Can't connect to postgres! " . DBI::errstr);
my $sth = $mdbh->prepare($check_db);
$sth->execute();
my $row = $sth->fetchrow_hashref();
if (! $row->{exists}) {
Irssi::print("Creating database.");
$mdbh->do($create) || Irssi::print("Can't create db! " . DBI::errstr);
$mdbh->do($init_pg_trgm) || Irssi::print("Can't create extension " . DBI::errstr);
$mdbh->do($create_trgm_idx) || Irssi::print("Can't create trgm index " . DBI::errstr);
$mdbh->do($create_date_idx) || Irssi::print("Can't create date index " . DBI::errstr);
$mdbh->do($create_chan_idx) || irssi::print("Can't create chan index " . DBI::errstr);
} else {
Irssi::print("Database already exists.");
}
$sth->finish();
$mdbh->disconnect();
return 1;
}
sub connect_db {
my $dbname = Irssi::settings_get_str('il_dbname') || $user;
my $dbuser = Irssi::settings_get_str('il_dbuser') || $user;
my $dbpass = Irssi::settings_get_str('il_dbpass') || "";
db_init($dbname, $dbuser, $dbpass);
Irssi::print("Connecting to the database");
# return DBI->connect("dbi:Pg:dbname=$dbname", $dbuser, $dbpass) || Irssi::print("Can't connect to db!" . DBI::errstr);
return DBI->connect("dbi:Pg:dbname=$dbname;host=192.168.1.109;port=30432", $dbuser, $dbpass) || Irssi::print("Can't connect to db!" . DBI::errstr);
}
sub write_db {
my ($nick, $message, $target) = @_;
if ($target eq "#news" || $target eq "#supersonic" || $target eq "#bookz" || $target eq "#subsplease" || $target eq "&bitlbee") {
# dont do anything
} else {
my @vals;
my $date = strftime("%Y-%m-%d %H:%M:%S", localtime);
$dbh = connect_db() unless $dbh;
push(@vals, $date);
push(@vals, $nick);
push(@vals, $message);
push(@vals, $target);
defined or $_ = "" for @vals;
$dbh->do($sql, undef, @vals) || Irssi::print("Can't log to DB! " . DBI::errstr);
}
}
sub log_me {
my ($server, $message, $target) = @_;
write_db($server->{nick}, $message, $target);
}
sub log {
my ($server, $message, $nick, $address, $target) = @_;
write_db($nick, $message, $target)
}
Irssi::signal_add_last('message public', 'log');
Irssi::signal_add_last('message own_public', 'log_me');
Irssi::settings_add_str('irssi_logger', 'il_dbname', $user);
Irssi::settings_add_str('irssi_logger', 'il_dbuser', $user);
Irssi::settings_add_str('irssi_logger', 'il_dbpass', "");
Irssi::print("irssi_logger loaded!");
|
