On Perl :: Index

I'll be explaining how the On Perl search engine works in the next several posts but first a few preliminary items need to be addressed. The database I'm using is SQLite, and if you haven't heard of it yet, trust me, you soon will. It's different than the usual database solution (MySQL, Oracle, etc.) because it isn't a server. In that respect it's more like Perl's standard Berkeley DB, in that it's file based and meant for smaller, non-concurrent jobs. Well, when I say "smaller" I mean a database of a couple terabytes and around a 100,000 hits a day--easily enough bandwidth to handle normal websites and blogs.

Think of it as Berkeley DB with SQL.

Oh, and did I mention that its code base is public domain? The freest free; you can't get any more open than that! It's extremely easy to install as well. The CPAN has a DBD::SQLite module with the entire database engine embedded within it (it's remarkably small too). You should get the command-line admin tool too, if you plan on using it much.

Once you have those items installed, we can focus on the real task: to start building a site search engine. To get our feet wet with SQLite we'll start with a straightforward job: building a database of stopwords. These will later be used to filter out common words that are useless in searches, like "we", "at" and "the". We could use the CPAN module Lingua::EN::StopWords to accomplish this, but I'm choosing not to because: I want an excuse to build a table in SQLite (remember?), and stopwords tend to be very domain-specific -- what might be a "common" word on one web site might not be on another. So I want to be able to tweak my stop list, and hone it based on my specific content. For example, the word "perl" is in almost every post on this site, so it would be useless to try and find a particular post using that term, so why waste time dealing with it?

Anyway, with SQLite, creating a database is easy. There is no server so nothing to do except create an empty file wherever you like and then fill it. I'm using the command-line SQLite tool for this, so in a terminal window type...

$ touch stops.db
$ sqlite3 stops.db
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> CREATE TABLE stopwords (word TEXT PRIMARY KEY);
sqlite> .import /home/michael/stops.txt stopwords
sqlite> SELECT COUNT(*) FROM stopwords;
563
sqlite> .quit

Rather than attempt to insertthe several hundred stopwords, I used the .import function to read them in from a plain-text file (formatted very simply with one word per line). Now we can switch to Perl to write a simple way of checking a list of input words against our stopwords table.

use DBI;

my $dbh = DBI->connect(
        'dbi:SQLite:dbname=stops.db', '', '',
        {RaiseError => 1}
) or die "$0: database connection not made: $DBI::errstr\n";

my $input = "Gizmo says, this that and the other thing... shasta!";
my @words =  $input =~ /(\b\w.*?\b)/g;

foreach my $word (@words) {
        print "$word " unless (stop($word, $dbh));
}

$dbh->disconnect;

sub stop {
        my ($word, $dbh) = @_;
        
        my $sth = $dbh->prepare('
                SELECT count(*) FROM stopwords WHERE word = ?
        ');
        $sth->execute(lc $word);
        
        my @result = $sth->fetchrow_array;
        $sth->finish();
        
        return $result[0];
}

# prints: Gizmo shasta

Notice that we use the same old DBI api, but since there is no server we don't need a username or password. If everything is working, we're now ready to move on to bigger jobs: writing the indexer that will scan the entire website and build a master word index.

A Stopwords Database with SQLite