First, we'll create the application so that we have some place to put our database file. We won't be doing much with Catalyst to start, but we'll need the project eventually:
$ catalyst.pl ChatStat
$ cd ChatStat
Let's get started by creating the tables in SQLite as follows:
$ sqlite3 root/database
CREATE TABLE channels (
cid INTEGER PRIMARY KEY NOT NULL, channel TEXT NOT NULL
);
CREATE TABLE nicknames (
nid INTEGER PRIMARY KEY NOT NULL, pid INTEGER NOT NULL,
nick TEXT NOT NULL, username TEXT NOT NULL, host TEXT NOT NULL
);
CREATE TABLE opinions (
oid INTEGER PRIMARY KEY NOT NULL, nid INTEGER NOT NULL,
cid INTEGER NOT NULL, tid INTEGER NOT NULL, message TEXT NOT NULL, reason TEXT NOT NULL, points INTEGER NOT NULL
);
CREATE TABLE people (
pid INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL
);
CREATE TABLE things (
tid INTEGER PRIMARY KEY NOT NULL, thing TEXT NOT NULL
);
CREATE UNIQUE INDEX channel_channels on channels (channel);
CREATE UNIQUE INDEX hostmask_nicknames on nicknames (nick, username, host);
CREATE UNIQUE INDEX nickname_people on people (name);
CREATE UNIQUE INDEX thing_things on things (thing);
CREATE INDEX idx_opinions_tid ON opinions (tid);
The tables are pretty straightforward, but note that we are creating some unique indices for various columns. This ensures that we can't accidentally give a thing multiple ID numbers and end up with a useless opinions table. It also makes writing the database code easier; we can look up a thing by its name instead of its unique ID (primary key), as the database guarantees that each thing will only appear in the things table once. The hostmask_nicknames index is a bit different; the data we get from IRC is in the form (nick, username, and host) so that the index will let us retrieve the unique ID given to that data.
Finally, we have a regular index on opinions.tid. We'll be grouping by this column in many queries, so it's logical to index. Doing so reduces the runtime of some queries from two seconds per query to thirty or more queries per second!
Now that we have the tables in place, we'll need to generate a DBIx::Class schema and add the relationships:
$ perl script/chatstat_create.pl model DBIC DBIC::Schema ChatStat::Schema create=static dbi:SQLite:root/database
This will create the files in lib/ChatStat/Schema/Result/*. Let us look at those files one at a time and add the constraints.
First, let us look at People.pm. All we need to add here is a name for our unique constraint.
Here's the constraint:
__Package__ ->add_unique_constraint( nickname => [qw/name/] );
This will allow us to write the following:
my $jrockway = $people->find('jrockway', { key => 'nickname' });
Later in the application, it's ensured that a UNIQUE INDEX over name will be created when we redeploy the schema.
Here's the has_many relationship:
__Package__ ->has_many( "nicknames", "ChatStat::Schema::Nicknames", { "foreign.pid" => "self.pid" },
);
This command will tell DBIx::Class to create an accessor called nicknames that will return an array of ChatStat::Schema::Nickname objects. The third argument is the join condition specified in SQL::Abstract syntax. In this case, we say to join on people.pid = nicknames.pid. For more information on SQL::Abstract syntax you can read http://search.cpan.org/~frew/SQL-Abstract-1.65/lib/SQL/Abstract.pm.
Nicknames.pm will look similar. We have another unique constraint, a has_many relationship to opinions (every nickname has many opinions), and a belongs_to relationship back to People. The belongs_to relationship will allow us to access the person record when we have only a nickname.
Here's the code:
__Package__ ->add_unique_constraint(
hostmask => [qw/nick username host/]
);
__Package__ ->has_many( "opinions", "ChatStat::Schema::Opinions", { "foreign.nid" => "self.nid" },
);
__Package__ ->belongs_to( "person","ChatStat::Schema::People", { pid => "pid" }
);
The has_many relationship looks the same as the one we just created.
The belongs_to relationship is also similar. We say to create a person accessor that will return a ChatStat::Schema::People object for the person. We've also abbreviated the join condition here, omitting the reference to foreign and self because foreign and self are implied automatically when they're omitted and there are no other conditions. It saves a bit of typing.
Moving along, let's take a look at the Things.pm file. We're going to add another unique constraint and has_many relationship here:
__Package__ ->add_unique_constraint(
thing => [qw/thing/]
);
__Package__ ->has_many( "opinions", "ChatStat::Schema::Opinions", { "foreign.tid" => "self.tid" },
);
By now this pattern should be looking familiar. The has_many relationship will create an opinions accessor that will return a list of opinions that relate to the current "thing".
Channels.pm is going to look almost exactly the same:
__Package__ ->add_unique_constraint(
channel => [qw/channel/]
);
__Package__ ->has_many( "opinions", "ChatStat::Schema::Opinions", { "foreign.cid" => "self.cid" },
);
Finally, we'll edit Opinions.pm and add belongs_to relationships to compliment the has_many relationships we just created:
__Package__ ->belongs_to( "nickname", "ChatStat::Schema::Nicknames", { nid => "nid" },
);
__Package__ ->belongs_to( "thing", "ChatStat::Schema::Things", { tid => "tid" },
);
__Package__ ->belongs_to( "channel", "ChatStat::Schema::Channels",
{ cid => "cid" },
);
That's all we need to do. Now every piece of data is related properly, allowing us to get whatever data is needed in a Perl fashion. Setting up a normalized schema is a bit more work, but the data is more organized and working with it using DBIx::Class will be a pleasure.
An empty database isn't much fun, so let's add some data to it. In the version of this application that I use, I have a POE-based IRCBot dynamically adding data to the database as messages come in from IRC. As POE is beyond the scope of this book, we'll create a small command-line script that works similarly. For the curious, the IRCBot code is included with the book and lives under the ChatStat::Robot namespace.
For the data-adding script, we'll take the same approach as the IRCBot. We'll start with a string that looks like an IRC message, parse it into its components, and then add it to the database.
Let us start with a class to abstract the components of the message. In lib/ChatStat/Action.pm, create the following:
package ChatStat::Action;
use strict;
use warnings;
use Regexp::Common qw/balanced/;
use base 'Class::Accessor';
__Package__ ->mk_accessors(qw|who channel word points reason message|);
use Readonly;
Readonly my %OP_POINTS => ( '++' => 1, '--' => -1, '-+' => 0, '+-' => 0, );
sub new {
my ($class, $who, $where, $what) = @_;
my $self = $class->SUPER::new({ who => $who, channel => $where, message => $what
});
$self->_parse;
return $self;
}
sub _parse {
my $self = shift;
my $what = $self->message;
my $parens = $RE{balanced}{-parens=>'(){}[]<>'}{-keep};
if ($what =~ /
(?: # what we're voting on:
$parens # something in parens
| # -or-
([A-Za-z_:0-9]+) # a single word++
)
([+-]{2}) # the operation (inc or dec)
s* # spaces, who cares
(?:[#] s* (.+)$)? # and an optional reason
/x
)
{
my $paren = $1;
my $word = $2;
if (defined $paren) {
$paren =~ s/^[({[<]//;
$paren =~ s/[)}]>]$//;
$word = $paren;
}
my $op = $3;
my $reason = $4;
$reason = '' if !defined $reason;
# trim
$word =~ s/^s+//;
$word =~ s/s+$//;
$reason =~ s/^s+//;
$reason =~ s/s+$//;
# it worked
$self->word(lc $word);
$self->reason(lc $reason);
$self->points($OP_POINTS{$op} || 0);
return;
}
die 'parse error';
}
1;
Note that this class is not really a Catalyst application. We are using it to merely populate our database.
This class takes a nickname, channel name, and message, and sets up a data structure consisting of the parsed out parts. For example, when you invoke:
my $a = ChatStat::Action->new('jon', '#test',
'Catalyst++ # fun'),
You'll be able to access the components like the following:
my $nick = $a->who; # jon my $points = $a->points; # 1 (-1 if we said --) my $reason = $a->reason; # fun my $word = $a->word; # catalyst
We use Regexp::Common to parse out balanced sets of parentheses so that we can add points to a phrase like "a phrase" by typing "(a phrase) ++". Thanks to Regexp:: Common::balanced, we can use any paired brackets we want, like<something>++ or {something else}--.
With the parsing out of the way, we want to be able to add one of these actions to the database. Because of the normalized schema, we have to do some extra work. We need to create the dependencies of the opinion first (thing, nickname, and channel) and then insert the actual opinion record. The nickname object is created similarly as it needs to refer to an existing person object.
As there's a lot to do, let's add a subroutine to the schema (in lib/ChatStat/ResultSet/Schema.pm) called record, which will insert an action object into the database as follows:
sub record {
my $self = shift;
my $action = shift;
my $nickname = $self->_get_nickname(action->who);
my $thing = $self->resultset('Things')->
find_or_create({ thing => $action->word });
my $channel = $self->resultset('Channels')->
find_or_create({ channel => $action->channel });
return $self->resultset('Opinions')->
create({ nickname => $nickname,
thing => $thing,
points => $action->points,
message => $action->message,
reason => $action->reason,
channel => $channel,
});
}
All we do here is use the DBIC schema ($self) to create or find the relevant records, then we create an Opinion. We do need a _get_nickname subroutine to take the nickname (in the form of [email protected], and return a nickname object that refers to a (perhaps newly-created) person object:
sub _get_nickname {
my $self = shift;
my $fullid = shift;
my ($nick, $user, $host) = ($1, $2, $3) if $fullid =~ m/(.*)!~(.*)@(.*)/ig;
my $nickname = $self->resultset('Nicknames')->find_or_new({ nick => $nick, username => $user, host => $host,});
my $person = $nickname->person;
if (!$person) {
my $person = $self->resultset('People')->find_or_create({ name => $nick });
$nickname->person($person);
}
$nickname->update_or_insert;
return $nickname;
}
Now we can get an Action and put it into the database with one line of code:
$schema->record(ChatStat::Action->new(...));
Let's tie this all together by creating a script in script/chatstat_add_opinion.pl so that we can add opinions from the command line as follows:
use strict;
use warnings;
use FindBin qw($Bin);
use lib "$Bin/../lib";
use ChatStat::Action;
use ChatStat::Schema;
my ($channel, $who, $message) = @ARGV;
die 'need channel as arg 1' unless $channel;
die 'need nickname as arg 2' unless $who;
die 'need message as arg 3' unless $message;
my $s = ChatStat::Schema->connect("DBI:SQLite:$Bin/../root/database");
die "failed to connect to database" unless $s;
my $parsed = ChatStat::Action->new($channel, $who, $message);
my $new = $s->record($parsed);
print "added opinion ". $new->oid. "
";
You can now run this using the following command line:
$ perl script/chatstat_add_opinion.pl '[email protected]' '#foo'
'scripts++ # making my life easy'
added opinion 1