Perl logoPerl logo

This is one of those posts that I know will have a very limited audience. To the left, you see a block with random BOFH quotes, which I find rather amusing. I’ve always found them entertaining and thought that they would make a nice addition to my blog as sheer entertainment for you guys.

A while back, I found this great little plugin called XmasB quotes, that adds a widget to WordPress where it displays random quotes. The actual quotes are stored in the same MySQL database as WordPress. But then comes the issue that I faced. There are well over 400 of those quotes, and manually copying and pasting all those quotes into the admin interface of XmasB was not an option that seemed tempting. Not one bit. Now how could I solve that?

Well, it was not a hard challenge to tackle at all, seeing as I learnt this wonderful programming language called Perl quite a few years back. Many consider Perl to be any seasoned Linux user’s best friend. It’s a joy to work with and can do anything from the simplest of tasks to the most complex things, as well as make you a cup of tea and slice your bread.

Some categorize Perl as MacGyver and a Swiss army knife in the same package, and it’s not far from the truth. Once you realise what you can do with Perl, you never look back. And in this case, it saved me from tedious repetitive work. Instead of having to manually enter all those quotes through the admin interface, I just wrote this nice little script to do the job for me. Nothing beats the feeling of writing a small script, running it and seeing that boring job being done for you.

Of course, I wanted to share this little script with you, which also works as a nice little tutorial on how to treat files with Perl, simple regexp matching and simple database connection and insertion. I hope you find it useful! :)

#!/usr/bin/perl
#
# This program extracts the BOFH excuses from the fortunes-bofh-excuses
# package, and feeds them into a mysql database of your choice.
#
# The reason I wrote it, was becuase I watned the BOFH excuses as random
# quotes on my WordPress blog, displayed by the XmasB Quotes plugin ->
# http://wordpress.org/extend/plugins/xmasb-quotes/#post-2491.
#
# Author: Jostein Elvaker Haande
# E-mail: [email protected]
# Homepage: http://tolecnal.net/
#
# You are free to use this script and alter it as you see fit.
#
# Enjoy! :)

use strict;
use DBI();

# This is the file we want to be parsed and put into the database
my $file = "/usr/share/games/fortunes/bofh-excuses";

# Define the hostname, database name, username, password and table
# name we want to populate with data.
my $hostname = "localhost";
my $database = "wordpress";
my $username = "wordpress";
my $password = "qwerty123";
my $table = "wp_xmasb_quotes";

# Here we connect to the database itself with the above credentials.
# The script should NOT fail at this point, if it does you need to
# check the above settings and make sure they are correct.
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$hostname",
                        "$username", "$password", {'RaiseError' => 1});

# Here we open the actual file for reading only, and feed the whole file
# into an array that we later process. The file is of course closed after
# it's read into the array, to minimize memory leakage etc.
open(INF, $file) or die("Can not open file: $!");
my @lines = ;
close(INF);

# Initialize the main variables we use when we process the array we just
# generated.
my $number = "";
my $quote = "";
my $insert = "";

# Now we parse the array we generated, to extract the information we need.
foreach my $line (@lines) {
    # We don't want the trailing carriage returns on each line.
    chomp($line);

    # If the line starts with "BOFH", it means we found the quote number
    # Add that to the variable $number
    if ($line =~ /^BOFH/) { $number = $line; }

    # If the line does NOT start with "BOFH", but starts with several words,
    # we've found the actual quote. Assign that to the variable $quote
    if ($line !~ /^BOFH/ && $line =~ /\w+/) { $quote = $line; }

    # If the line starts with "%", it indicates that it's the end of a quote
    # section, and it's time to feed the newly found quote into the database.
    # Let's generate the SQL INSERT statement!
    if ($line =~ /%/) {
        # In the actual quote number, we have a trailing ":" we don't want.
        $number =~ s/://;

        # Before we can insert the quote, we need to escape special characters
        # so that SQL will insert it without errors. (perldoc -f quotemeta)
        $quote = quotemeta($quote);

        # This is the actual SQL insert. We first construct the INSERT as variable,
        # as it makes the code more readable (IMHO).
        $insert = "INSERT INTO $table (author, quote) VALUES (\"$number\", \"$quote\")";

        # If you want to see the actual INSERT, you can uncomment the next line
        # for debugging purposes.
        # print "$insert\n";

        # Time to insert the actual quote into the database!
        # KEEP YOUR HAT ON! :)
        $dbh->do("$insert");
    }
}

# We are now done parsing the file and array, and extracted the data we wanted,
# as well as placing them in the database. Good job! :)
print "Done parsing the file!\n";

# We need to make sure that we close the connection to the database, so that
# we don't leave any lose ends. System resources are precious!
$dbh->disconnect();
print "Disconnected from the database...\n";

By Jostein Elvaker Haande

"A free society is a society where it is safe to be unpopular" - Adlai Stevenson

One thought on “[HOWTO] XmasB quotes and BOFH fortunes for WordPress”
  1. Hi Mate!
    I wish everybody would comment code like this. Then the IT World would be a better place ^^

    CU

Leave a Reply

Your email address will not be published. Required fields are marked *