Build Your Own AIM Answerbotby Robert Treat
The Mosquito Bites
If you have ever spent time on IRC, you have probably run into one of the little programs that run there known as a bot. These bots look like regular IRC users. They look for messages and give specific responses. There are all kinds of bots -- bots that play trivia, bots that log visitors, and some that send channel information or topical FAQs to visitors.
On the #postgresql channel on freenode, a bot stores all the links that regulars send to newbies who come asking for help. It also serves as a good reference for experienced users who need a quick reminder on where to look for information on a current topic. To access it, send a message into the channel containing a keyword prepended with two question marks (??). When the bot sees a message like this, it looks up the relevant URLs and sends them back to the channel. Figure 1 shows an example session. As I spend more time on IRC, I have grown more and more accustomed to having the bot available, especially for looking up syntax for seldom used commands and other things of that nature.
Unfortunately, my current employer does not allow access to IRC, so when I am at work I have to use a search engine such as Google or pgsql.ru, or pester people on my IM buddy lists. Although this tends to work, it is also more time consuming than just firing off a quick keyword request to the IRC bot.
All of this made me wonder. What if the IRC bot could also live on my AIM list? That way I could query him from work, where the pointy hairs can't live without their AIM and so they leave it open for use, and my friends who don't use IRC would have a new resource for looking up PostgreSQL related information as well.
A Little Research Is Prudent
I had heard of people building AIM bots before, so rather than stumble through the idea on my own, I dug for some information to see if there was someone else who had previously tackled this problem or at least one close to it. Of course one of the first links I came across was a previous article on the O'Reilly website about building an Amazon AIM bot using Perl and the Amazon web services. This hack really provided some solid groundwork for me; with it I knew that my idea was not only feasible, but would not be that complicated. The one drawback of the article was that it used the now deprecated
Net::AIM module; I instead would need to use the newer
Net::OSCAR module. Luckily with Perl, this was only a quick CPAN download away.
With the bot side of my AIM bot looking good, I needed to figure out the actual mechanics of the how the AIM bot would look up the keyword information I wanted to return. Again, I tried to find an existing solution. With a few quick searches, I found the homepage of the pg_docbot project, which houses the code used for the IRC bot on #postgresql. This site contained all of the SQL they used to create the database that held the keyword information; this would serve as the inspiration for my AIM bot. I was now ready to start putting the pieces together.
Building the Brains
The actual schema the pg_docbot project uses is pretty elaborate, more so than what I thought I would need. Instead I used a simplified version of the database schema:
CREATE TABLE urls ( url_id serial PRIMARY KEY NOT NULL, url text UNIQUE NOT NULL ); CREATE TABLE keywords ( keyword text PRIMARY KEY NOT NULL, url_id integer NOT NULL REFERENCES urls(url_id) ON DELETE CASCADE ); CREATE VIEW keylist AS SELECT keyword, url FROM keywords JOIN urls USING (url_id);
Basically the information is in two tables. The
urls table holds all of the different URLs in the system. The
keywords table holds the keywords to match to our URLs from the
urls table. The reason for breaking things down this way is that one URL might link to multiple keywords. For example, if someone writes a guide on installing PostgreSQL on Solaris, I might want to attach that article to the keywords
solaris, and maybe even
sun. Separating the keywords from the URLs adds a little bit of normalization to the data, which should help make storage and maintenance much easier.
That said, I also want queries against the data to be as simple as possible, so I have a view, the
keywordlist view, to join these two tables back together. Now when the code does a select against the view, both the query and the results will be very straightforward. Luckily for me, I was able to obtain a copy of the data from the docbot project, so I just imported that into my database above. If you are making your own system, you need to determine what information you want to display and then insert into your database.
Pages: 1, 2