Therefore my first question: Can anyone recommend a comment parser that I can throw an arbitary(ish) language file and get back all the comments?

Update: Mostly solved by matching comments as suggested by andyf. However if someone wants to point me at a packaged (CPAN?) solution that'd still be welcome.

At this point there is meta-information about this file:

• file_id - A unique id for this file
• file_source - An integer that links to another table giving details of there the file came from etc.
• md5hash - The md5 hash of the file
• description - Some of our file sources provide their own meta-data about the files (in current cases, this is just a textual / HTML description)
• comment - User comments about this file (initially blank)

I have built a search engine for just comments / descriptions, however I'm not sure my design would stand up to full contents or a massive amount of data (however it's a shedload faster than the database's regex search currently).

So, I have three tables:

• files - contains file_id and comment/description
• search_files_tokens - contains normalised tokens for search and token_id
• search_files_to_tokens - contains a token_id, a file_id and a count of the number of times this token appears in this file.

What I do is as follows:

• For each file, take comments and descriptions and contatenate them into $comment.
• Split on whitespace into individual tokens.
• Normalise each token (remove punctuation, tr/a-z/A-Z/ etc)
• For each new token, create a token in the database (returning a unique token_id).
• For each token found in the comment, insert a row into search_files_to_tokens.

I can now lookup a unique token, and find the files that contain it most often.

The code that does this looks like:

my $known_tokens;
while ($comment = get_next_file_comments()) {
    my $tokens;
    my @possible_tokens = split(/\s+/s, $comment);
    foreach (@possible_tokens) {
        my $hs = HTML::Strip->new(); #HTML::Strip segfaults without ne
+w instance each time
        $_=$hs->parse($_);
        /[\w\d\.\/\\]+/) or next;
        tr/a-z/A-Z/;
    s/[\s\',\!\"\\xa3\#\$\%\^\&\*\\=\+\:\'\`\(\)\{\}\[\]\.\/\\\-\_]//g
+;
        next if ($_ eq "");
        if (!defined($tokens->{$_})) {
            $tokens->{$_}=1;
        } else {
            $tokens->{$_}++;
        }
    }
    foreach (keys(%{$tokens})) {
        my $token_id;
        if (defined($known_tokens->{$_})) {
            $token_id = $known_tokens->{$_};
        } else {
            my $res = $gettokenid_sth->execute($_);
            ($gettokenid_sth->rows() > 0) and do {
                ($token_id) = $gettokenid_sth->fetchrow_array();
            };
            if (!defined($token_id) or !$token_id) {
                $addtoken_sth->execute($_);
                $gettoken_sth->execute($_);
                ($token_id) = $gettokenid_sth->fetchrow_array();
            }
            $known_tokens->{$_}=$token_id;
        }
        $addtokeninfile_sth->execute($file_id, $token_id, $tokens->{$_
+});
    }
}
[download]

All reasonably simple, and reasonably sane (other than the fact that my tokeniser is a bit rubbish currently)..

Now, when I come to search, I allow the user to input a phrase such as:

(linux or sun) and log

I parse the search query into bits I know about; (,),and,or,not and bits I don't; linux, sun, log

For anything I don't know about, I tokenise it (as above) then search for that token (the case of a non-existant token adds lots of mess, but is reasonably easy to handle, so won't be considered here).

Once I have a token_id for each token, I then build an SQL query along the lines of:

SELECT file_id FROM search_files_to_tokens WHERE token_id = $token_id

for each file, and chain them together with UNION, INTERSECT and EXCEPT

For example, if tokens for linux = 1, sun = 2 and log = 3, query will be:

((SELECT file_id FROM search_files_to_tokens WHERE token_id = 1) UNION ((SELECT file_id FROM search_files_to_tokens WHERE token_id = 2)) INTERSECT (SELECT file_id FROM search_files_to_tokens WHERE token_id = 3))

This gives me back a pile of rows containing a file_id for files that match the search criteria. I can then do another simple (and fast) query which gives me back the counts for each token/file instance.

Taking that data, I can easilly iterate through it and provide each file with an overall score. My scoring system is simple:

For each token in this file, add (number of times this token appears in this file / number of times token appears in the result set as a whole) and this is your score.

I then sort the result set by score and display.

This generally works very well, however for queries with common tokens (that produce a large result set), it can be quite slow (but still faster than regex search). Also, search speed can change drastically with query order. If I start indexing comments in files / text file contents, it's going to get unusable..

Questions:

• Is my database schema sane, (or, sane but dumb)?
• Is my search algorithm sane / can it be optimised easilly (or again, just fine, but dumb)?
• Am I going about this totally the wrong way?
• Is this the optimum method, and I should just get faster hardware?

I know that a simple optimisation would be to throw out the top five to ten tokens (going to do this, just not implemented yet) and maybe throwing away any token that only occurs on one file (this would produce a massive optimisation, however it scares me as people will want to search for specific things that they know exist by name)