Establish Database Connection with Perl

It's been a while since I use Perl to connect to a database and my memory about it has faded long ago. But now since I'm no longer purely involve in web development so I thought this method could be useful. Let see how it goes.

To cut the chase, here is the sample code

use strict;

use DBI;

# Connect to the database
# See footnote 1
my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_password')
or die "Couldn't open database: $DBI::errstr; stopped";

# Prepare the SQL query for execution
my $sth = $dbh->prepare(<execute() or die "Couldn't execute statement: $DBI::errstr; stopped";

# Fetch each row and print it
while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) {
print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n";
}

# Disconnect from the database
$dbh->disconnect();

DBI is Perl's DataBase Interface. There are many different databases: Oracle, mSQL, MySQL, Ingres... even files containing data separated by columns could be considered databases. DBI works with practically all of them. DBI requires a database driver module to talk to the database. The driver module handles all of the details about connecting to and communicating with the database. DBI sits on top of the driver; its job is to make reading from (and writing to) all databases as similar as possible. That way, if your project grows from something that could be handled with mSQL to something needing Oracle, you can make that change without altering the code very much.

Database driver modules are Perl modules like any other, and often they contain documentation. They are all inside the root package DBD. So, to get information on an Oracle driver you've installed, you can type:

perldoc DBD::Oracle

Other than installing the module, and possibly reading the documentation, you rarely need to deal directly with the database driver module. DBI handles all of that.

Random Thought: How to create Technorati

Woke up this morning and suddenly this question pop in my mind "How Technorati works?" Yup I'm a late dreamer. Anyway I dig a little bit and found some clues from David Sifry, the creator of Technorati himself. He describes the process flow as following:

1. We spider blogs, and match up their links to your blog - to anywhere on your blog
2. In the inbound blog list, we use the outbound links from the blog homepage, not from the archives
3. We do process RSS feeds an other metadata, but that doesn't affect your inbound blog stats.
4. Nightly, we go through the database and re-calculate the number of inbound blogs and links, which helps us double-check our work and also allows us to create the interesting newcomers list, the interesting recent blogs list, etc.

So my next question would be, "How to create a blog spider?" Well I figure that a blog spider must be like a webcrawler or something. It traverses the Internet gathering, filtering, and potentially aggregating information for a user. Well said, but it's now sounded more like How to create a Google bot kind of stuff.

I saw some the spider were written in Ruby, Phyton and Perl. Take a look to this article, "A web crawler in Perl", maybe this is a good lead.