Perls Before Swine: Web CGIs

One of the most common uses of Perl on databases is displaying the data in the database on the web. Perl comes with a special module for use as a web page generator, called “CGI”. CGI stands for Common Gateway Interface. It’s a way for web servers to pass data to programs such as Perl scripts. The CGI module makes heavy use of objects and methods. You can find out more about Perl’s CGI module by typing “perldoc CGI” from a Unix command line.

I’m going to assume that you’ve created the “love” database from the previous example, called LoveSongs, and that you’ve put it somewhere accessible. You usually do not want to put your databases inside your web site. For example, if your web site is in a folder called “public_html”, your CGI will go in that folder but your database should be outside of it. Otherwise, people can download your database directly, without having to go through your CGI.

Perl web scripts usually end in “.cgi”. Create a file called “music.cgi”:

#!/usr/bin/perl
#display data from a show-created SQLite music database, on the web

use CGI::Pretty;
use DBI;

$html = new CGI;

#start the web page
print $html->header;
print $html->start_html("Love Songs");

#print the top of the html
print $html->h1("Love Songs of the Seventies & Eighties");
print $html->p("Welcome to my web page of", $html->em("songs that mention love"), "in their title.");

#finish the page
print $html->end_html;

I’m actually using CGI::Pretty, rather than CGI, because it is easier to debug your Perl scripts when you can see the HTML it creates. The default for CGI is to put all of the text on the same line, for one really huge line of HTML. CGI::Pretty puts your HTML on separate lines. With or without CGI::Pretty, it works the same, so you can experiment with using each of them. Just change CGI::Pretty to CGI and then back again.

We create a “new cgi” and assign it to $html. This will be an object that knows how to create HTML code. It knows how to create a header, it knows how to create a paragraph, and it knows how HTML pages end. Each of the HTML parts can take lists of other parts. So you can see that for the “h1” part (level one header), we just send it some text. But for the following paragraph, one of the pieces needs to be emphasized.

You’ll still need to know HTML a bit when using the CGI module; each of the parts is the same name as their HTML counterpart.

Once you have this script created and saved, don’t forget to make it executable by you:

chmod u+x music.cgi

And you’ll also need to make it executable by “other”. Web servers will need permission to read this file in order to run it:

chmod o+x music.cgi

While you’re at it, you’ll need to make sure that the LoveSongs database is also accessible by “other”:

chmod o+r /path/to/LoveSongs

You will need a web site that can run CGI scripts. This web site’s server will also need the DBI module and the DBD::SQLite module installed. (If it’s a server meant for web serving, it probably does.)

Upload it, and then view the CGI. You should get something like:

So, we can write web pages, we need to open the database and display it. Add some defaults to the top of the script, below the first comment:

@displayFields = ("song", "artist", "album", "year", "genre");
$dbFile = "/path/to/dbs/LoveSongs";

Below the paragraph and before the end of the page, add:

if ($dbHandle = DBI->connect("dbi:SQLite:dbname=$dbFile")) {
print $html->table(
$html->Tr($html->th(\@displayFields))
);
} else {
print $html->p($html->b("Problem opening database. Try again later: $!."));
}

We’re opening the database just as we did before when we wanted to write to it. Then, we’re printing out a table that contains one row; that row contains a series of header cells (“th”). Note that we’re passing the list of fields as a reference. This is how $html->th() knows that each of these items needs its own cell. If they were passed as a normal list, $html->th() would put them all in the same cell, just as giving the paragraph multiple items put them all in the same paragraph.

Next step: display all of the rows. Replace the “$html->Tr(…)” line with:

$fields = join(", ", @displayFields);
$query = "SELECT $fields FROM music ORDER BY song";
if (@rows = getRows($dbHandle, $query)) {
print $html->table(
$html->Tr($html->th(\@displayFields), @rows)
);
} else {
print $html->p("Nothing found. Sorry.");
}

The query is going to look like “SELECT song, artist, album, year, genre FROM music ORDER BY song”. We are going to pass that query to a subroutine called getRows.

If that subroutine returns something, we’ll print the table just as we did before, but with the rows returned by getRows.

That subroutine will need to query the database and then go through each row that the database returns and turn them into HTML table rows.

sub getRows {
my($db) = shift;
my($query) = shift;

my(@rows);
if ($queryHandle = $db->prepare($query)) {
if ($queryHandle->execute) {
while ($row = $queryHandle->fetch) {
$rows[$#rows+1] = $html->Tr($html->td($row));
}
} else {
print $html->p("Unable to execute $query: $!");
}
} else {
print $html->p("Unable to prepare $query: $!");
}
return @rows;
}

The main difference between this and our other doQuery function is that, after executing the query this function also loops through each row, using:

while ($row = $queryHandle->fetch) {
$rows[$#rows+1] = $html->Tr($html->td($row));
}

The fetch method on the query object gets the next row. More specifically, it gets a reference to a simple array of the items in the next row. Since the CGI module’s HTML parts accept references as things to add the part to individually, calling $html->td($row) is like calling $html->td(\@row) if we had an @row list.

That’s it. The web page should now display a list of about 292 songs.