Running a Perl based bot logging channel to a Mysql database

This is a short guide to installing a Perl bot (infobot) logging to a Mysql database.

NOTE: this wiki is work in progress and should be considered still on alpha stage

Introduction

There are many irc bots out there, and one of them is an "old" Perl bot named "infobot", by Kevin Lenzo:

http://www.infobot.org

This infobot can be easily modified for added mysql-logging capabilities, this is, all the messages spoken in the irc channel(s) where the bot is present are stored in a simple sql table, part of a very basic database hosted in a MySQL server. A barebones web query interface made in php is included. It is a modification of the query interface included in the "ulogd" project:

http://www.netfilter.org/projects/ulogd/

Requisites

For plain standard infobot you need a Linux shell account with perl access. For Mysql logging you need access to a database in the same server. It is possible logging to a remote database but not recommended. The place where you can learn how to change the perl code for remote database access is here:

http://sql-info.de/mysql/examples/Perl-DBI-examples.html

Installation

Download the original infobot package:

http://www.infobot.org/src/infobot-0.45.3.tar.gz

Untar the package:

tar zxvf infobot-0.45.3.tar.gz

Now there is a new infobot-0.45.3 directory. The more interesting subdirectories are:

src subdirectory: Perl "source code" files are here. As Perl is an interpreted language these files are scripts.
conf subdirectory: configuration files are here. The most important configuration file is infobot.config

At this point you should spend some time learning about infobot. There are some documents included in the package. To get infobot up and running without reading documents just edit the file infobot.config located in the conf directory and set nickname for the infobot, irc server and channel(s) to join, but don't forget to read the documents later or you'll never really learn. You can find a sample infobot.config file here:

http://ssh.shellium.org/~titanio/infobot/

configured to connect to Freenode irc network an joining channel #shellium-bots using the nickname "changeme". Just change the nick and it can be used to test the bot

Running the bot

To start the bot in foreground, testing mode, cd to the infobot directory and use the command:

./infobot

Use Ctrl+c to stop the bot. To run the bot in background, normal mode, use the command:

nohup ./infobot &

Using "ps x" command in the shell you can see your infobot process and stop it when necessary.

At this point you have an standard infobot joining a channel. It logs all activity to a file.

Now you need to modify one of the Perl scripts in the src subdirectory and adding some lines of code for the logging to mysql feature. Making a backup of the file before editing is always a good idea.

The file ircHooks.pl in the src subdirectory is the one to edit. Look for the lines 22,23,24,25 and 26:

 if ($type =~ /public/i)     {
     &channel($channel);
     &process($who, $type, $message);
     &status("<$who/$channel> $origMessage");
 }

You need to add some lines between lines 25 and 26. Line 26 is the one with the single } and has to move down so there is new room there for adding the following new lines:

 use DBI;
 my $dbh = DBI->connect('DBI:mysql:infobot', 'user', 'pass')
    or die "Couldn't connect to database: " . DBI->errstr;
 my $sth = $dbh->prepare("INSERT INTO msg(chan, nick, msg) VALUES(?,?,?)")
    or die "Couldn't prepare statement: " . $dbh->errstr;
 $sth->execute($channel,$who,$origMessage)             # Execute the query
    or die "Couldn't execute statement: " . $sth->errstr;
 $dbh->disconnect();

After adding this code to IrcHooks.pl infobot will die right after connecting to the irc server with an error about being unable to connect to the MySQL database. This sample code added expects to connect to a database server running in the same host where the infobot is run. The database name is "infobot", the database user is "user" and the password is "pass".

SQL database server administration is beyond the scope of this document but it doesn't matter, the procedure for creating the database and database user will be explained here soon. Right now all you get here is the SQL script for the database. It is a very simple database with only one table for the public messages spoken in the channels where infobot listens:

#--- Do no paste this line in the script ---
 
CREATE DATABASE infobot;
 
USE infobot
 
CREATE TABLE `msg` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `stamp` TIMESTAMP,
  `chan` VARCHAR(255) NOT NULL DEFAULT '',
  `nick` VARCHAR(255) NOT NULL DEFAULT '',
  `msg` TEXT NOT NULL DEFAULT '',
  `notes` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 
#--- Do no paste this line in the script ---

After the database and database user are created, infobot will start and join channels again, exactly like it did before editing IrcHooks.pl and adding the code for MySQL logging, but this time the public messages are logged to the table "msg" in the database "infobot", in addition to the standard infobot log file.

Quering the database

The barebones php query interface, infobot.php, has to be placed in the public_html directory of a web server. It connects to the database and makes possible to search messages using multiple criteria. If you change the default values for database name, database user and database password then you need to modify infobot.php and set the new values. You can find infobot.php here:

http://ssh.shellium.org/~titanio/infobot/  http://ssh.submitdirect.org/~titanio/infobot+mysql/

Running a Perl based bot logging channel to a Mysql database

Contents

Introduction

Requisites

Installation

Running the bot

Quering the database

Personal tools

Namespaces

Variants

Views

Actions

Search

Navigation

Indexes

SHellium Sites

Toolbox