9.2 DBI and CGI

only for RuBoard - do not distribute or recompile

In this section, we'll show Perl DBI and MySQL in what is probably the most popular context for their use: combined with the popular Perl CGI module to serve dynamic web pages.

As a protocol, CGI is the universal method by which web servers accept input from users and generate results on the fly. The CGI module in Perl makes it easy for programmers to retrieve user input and create output. In order to dispel any sense of magic surrounding CGI, let's look at how data travels from web form to Perl program.

An HTML form may contain a text field such as:

 <input type="text" name="titleword" />

If a user enters the word Linux into the field and presses the Submit button, the field is sent to the web server. So if the form containing the input field started out like this:

 <form method="post" action="/cgi-bin/book_view.cgi"       enctype="application/x-www-form-urlencoded">

the user's browser would request the URL:

 /cgi-bin/book_view.cgi?titleword=Linux

In other words, the browser requests the URL specified in the form's action field, with the field name and the field value tacked on at the end. The server invokes the book_view.cgi program, using a special protocol to pass the field name and field value. So now we have to shift our attention to the Perl program.

9.2.1 Introduction to Perl CGI

Having been passed the titleword field by the web server, the Perl CGI module extracts it through a param( ) call:

 my $titleword = $cgi->param('titleword');

Now the local variable $titleword contains the string Linux . The program can do whatever it wants with this information, but typically it makes a connection to a database and formulates an SQL query, as we did in the earlier DBI examples.

The CGI program is responsible both for handling user input and creating output. Since the server sends its output (more or less) directly to the browser, a Perl program can use simple print statements to create HTML. The program also has to create part or all of the HTTP header, but luckily the CGI module hides that activity behind the simple call:

 print $cgi->header(  );

Other CGI calls are also fairly intuitive to people who know HTML. For instance, the statement:

 print $cgi->h2("Please enter a word in the box above.");

outputs the string:

 <h2>Please enter a word in the box above.</h2>

which is, of course, an H2 header element in HTML. Sometimes you have to specify arguments as hash keys and values. The following call, for instance, sets two of the many parameters accepted by the start_html( ) call: one parameter to set the background color and another to set the web page's title:

 $cgi->start_html(                  -bgcolor => '#ffffff',                  -title => 'MySQL CGI Example');

Parameters passed from the browser can easily be placed into SQL calls, and results retrieved from database calls can just as easily be placed into CGI calls and sent to the browser. The CGI module is described in great detail in CGI Programming with Perl , by Scott Guelich, Shishir Gundavaram, and Gunther Birznieks (O'Reilly).

In Chapter 5, we described a three-tier application model. It may have seemed rather abstract there, but if you use a database with a web server to generate dynamic web pages, you are implementing a three-tier model. The top tier is the browser, the middle tier is the web server and CGI program, and the bottom tier is the database server. We are going to implement a three- tier model twice more during the course of this chapter, so we'll take a moment to talk about it theoretically.

9.2.2 The Model/View/Controller Methodology

One of the best-known and most common structures that produce maintainable programs is called Model/View/Controller (MVC). There are plenty of other good methodologies, but we'll use MVC in this chapter because it's particularly well-suited to database applications. It's a very typical three-tier methodology.

MVC splits an application into the Model, the View, and the Controller. Each layer is an independent unit that performs a specific function. If you design the program correctly, you can ”at least in theory ”completely strip out one layer and replace it with something different without having to change the code in the other two layers .

Model: The body of the application. Here, all objects that represent real-world "things" within the application are modeled . For example, a book, publisher, and author are represented in code on this layer. The Model is also responsible for making these objects persistent; that is, storing them so they can be retrieved after the program finishes. Therefore, all database interaction is performed by the Model.
View: The eyes, ears, and hands of the application. The View is responsible for presenting information to the user and collecting any user feedback. In a traditional desktop application, the View draws the screens and reads input from the keyboard and mouse. In a web-based application, the View generates the HTML viewed by the user's browser and passes down any form data submitted by the user. All I/O that involves the user of the system is done in the View. Any input by the user is passed to the Controller for processing.
Controller: The brains of the application. Any software logic performed by the application, such as the job discussed in Chapter 6 of authenticating users, is done within the Controller. The Controller is also the communication center of the application. It processes all user input from the View and all data retrieved from the Model.

It's fairly easy to fit CGI into the MVC methodology. The database maintains the Model, the CGI program (and the web server with which it interacts ) is the Controller, and the browser acts as the View. The complete CGI program we'll show in the following section is also based on the MVC methodology in another way.

9.2.3 A Sample CGI/DBI Program

In this section, we present ”once again ”a program that queries a MySQL database and prints results from the query. This time, however, we will format the results into a CGI page. Instead of generating HTML directly, like the $cgi->h2( ) call in the previous section, we're going to separate the Model from the view in classic MVC fashion. We'll use a popular Perl module called the Template Toolkit.

In this program, we store all the data we want to print in a set of Perl hashes. There is a headers hash used to generate the headers of a table (TH elements in HTML) and a records hash used to store information on each row retrieved from the database. We even use a hash element to store the title of the web page. This hash is the Model in our methodology, because it indicates the data and their relationships without making any presuppositions about how they are displayed.

Then we invoke the Perl Template module to turn our hash into HTML. The module uses our hash and a template we develop and store in a file called book_view.tt . Here's the beginning of the file:

 <html>  <head>      <title>[% title %]</title>  </head>

It's HTML, but has a placeholder for the title. We can therefore use the template over and over with pages that have different titles. There are placeholders for the other elements of our hash as well, and a lot of logic to do looping and other runtime choices. For instance, Example 9-4 shows a loop in the book_view.tt template file that causes odd-numbered and even-numbered rows to print in different colors, in order to let the user distinguish different rows.

Example 9-4. Excerpt from Template Toolkit template file

 [% FOREACH record = records -%]     [%- IF loop.count MOD 2 -%]       <tr bgcolor="#cccccc">     [%- ELSE -%]       <tr bgcolor="#ffccffcc">     [%- END -%] ...

The format used by the Template module is beyond the scope of this book. But it is a much more robust way to develop web pages that you intend to change and maintain over time. Consequently, it is popular and highly regarded in the Perl community.

The advantage of developing web pages this way is that web designers can alter the template as much as they like without forcing the programmers to change their code (and possibly introduce errors into the logic of the program). Similarly, programmers can change the database and the program logic without worrying about the final look of the web page. The Template module represents a successful application of the MVC methodology, with the template file as the Model, the final HTML (designed by web designers) as the View, and the Template module as the Controller.

Since we've described the basic CGI calls and operations, we'll list the entire program in Example 9-5 and then describe a few interesting details. To run this code, you need not only the DBI and CGI modules, but the Template module and a module called AppConfig that Template depends on. You also need the book_view.tt file. Both Example 9-5 (under the name book_view.cgi ) and book_view.tt are on the O'Reilly web page for this book. Finally, you probably want to select a special username and password for CGI programs, instead of using the andy user we defined earlier.

If you use the Apache web server, it is easy to get this CGI application working simply by copying the Perl program and template file to Apache's cgi-bin directory. Some configuration may be required, however, on Apache and on other web servers.

Example 9-5. CGI program

 #!/usr/bin/perl -w use strict; use DBI; use CGI; use Template; use URI::Escape; # This code prints a small amount of HTML. # Mostly, it creates data structures to plug into the template file, # book_view.tt. my $cgi = CGI->new(); my $tt = Template->new(); # The HTPP header must always be printed. print 'Content-Type: text/html', "\n\n"; my $Records; # a container for our data $Records->{title} = 'MySQL CGI Example with The Template Took Kit'; $Records->{vlink} = '#0000ff'; $Records->{bgcolor} = '#ffffff'; # If we have parameters, the user clicked on something. # Otherwise, we just exit. if($cgi->param) { # Remove spaces from around the parameter that was passed in.     my $titleword = $cgi->param('titleword');     $titleword =~ s/^\s+//;     $titleword =~ s/\s+$//; # If the user typed in more the one word, use just the first.     $titleword =~ s/\s+.*//;     $Records->{esc_titleword} = uri_escape($titleword);     $Records->{titleword} = $titleword; # The following block of code runs when the program is first # invoked or when the user has not typed in a word for the # title search.     unless ($titleword) {         $Records->{no_word} = 1;         $tt->process('book_view.tt',$Records)              or print $tt->error();         exit;     }     my @rows;     my $url = $cgi->url ;     # Find out which column, if any, the user clicked on.     # The form passes that information in the 'col' parameter.     my $order = $cgi->param('col');     my $t = $cgi->param('t');     # Fill in the headers. The information for each header     # is an element of an array.     # The array is stored in a hash element with the key $headers.     # The $Records variable points to the whole hash.     push(@{$Records->{headers}} , { col => 1,name => 'ISBN',toggle => 1});     push(@{$Records->{headers}} , { col => 2,name => 'Title',toggle => 1});     push(@{$Records->{headers}} , { col => 3,name => 'PubDate',toggle => 0});     push(@{$Records->{headers}} , { col => 4,name => 'Author',toggle => 1}); # Check each column to see whether the user clicked on it. # If so, we toggle the sorting order for that column. # If the toggle was 1, we change it to 0 (that is, 1 - 1). # If the toggle was 0, we change it to 1 (that is, 1 - 0).     for my $hash ( @{$Records->{headers}}) {         if($hash->{col} == $order) {                 $hash->{toggle} = 1 - $t;         }     }              my $attr = ($t == 1)? 'asc':'desc';     # Call getPat function, shown later, to transform the user's     # word into a regular expression for searching.     my $pat = getPat($titleword); ######### Start of interaction with the database #########     # First invocation of a DBI method.     # Connect to the database.     # Set the RaiseError flag to catch all DBI errors in $@.     # A row counter, used to mark alternating rows with different colors.     my $c = 0;     my $dbh;     eval {         $dbh = DBI->connect('dbi:mysql:Books:localhost','andy','ALpswd',                 {PrintError => 0 ,RaiseError => 1});     # Create the query. Get the four fields for which we set up     # headers in the $Records array earlier.     # The 'rlike' clause requests a regular expression search,     # a bit of nonstandard SQL supported by MySQL.     # $attr is either undefined or 'desc' for a descending sort.         my $sql = qq{             select isbn,title,pubdate,author                 from Titles where title rlike ?                  and pubdate is not null                 order by $order $attr         };         my $sth = $dbh->prepare($sql);         $sth->execute($pat); ######### End of interaction with the database #########     # Fetch the data and add it to $Records as part of its     # 'records' hash element.         while (my $row = $sth->fetchrow_arrayref) {             ++$c;             my $hash = {                 isbn => {                     value => $row->[0],                 },                 title => {                      value => $row->[1],                 },                 pubdate => {                     value => $row->[2],                 },                 author => {                     value => $row->[3],                 }             };             push(@{$Records->{records}},$hash);         }         $dbh->disconnect;     };     if($@) {         $Records->{error} = $@;         $tt->process('book_view.tt',$Records)              or print $tt->error();         exit;     }     unless ($c) {         $Records->{no_rows} = 1;         $tt->process('book_view.tt',$Records)              or print $tt->error();         exit;     } } # Now run $Records through the template processor $tt->process('book_view.tt',$Records)      or print $tt->error(); exit(0); ######### getPat Function ############################# # getPat accepts a word and returns a regular expression that # MySQL will use to find titles. Knowing our data, we perform # some sneaky tricks on certain input so that a search for 'Java' # finds JavaBeans and words beginning with J2, while a search for # .NET finds C and C# titles. sub getPat {     my $titleword = shift;     my $_what = quotemeta $titleword;     my $pat;     if($titleword =~ /^[Jj][Aa][Vv][Aa]$/) {         $_what .= 'j2[a-z][a-z]javabean(s)?';         $pat = "(^[^a-zA-Z])($_what)([^a-zA-Z]$)";     } elsif($titleword =~ /\.[nN][eE][tT]/) {         $_what .= '[cC]\#';         $pat = "(^[a-zA-Z]+)?($_what)([^a-zA-Z]$)";     } elsif($titleword =~ /\.\*/) {         $pat = ".*";     } else {         $pat = "(^[^a-zA-Z])($_what)([^a-zA-Z]$)";     }     return $pat; }

The user's title word is escaped to make it work in an SQL query and then plugged back into the form so it is displayed along with results of the query. Even more importantly, the template file makes the title word the default value for the titleword field in the new form.

The program retrieves four columns from the database and reserves four entries for them in the headers hash that $Records points to: ISBN , Title , PubDate , and Author . It also plays a bit with the sort order. After the query is satisfied, books are displayed in descending order by PubDate (that is, the most recent book first). The user can choose an column to sort by, however, and reverse the order of the sort, by clicking on a header.

The database interaction should be familiar from the examples we showed earlier in the chapter. The code uses one interesting advanced feature of MySQL: it creates a regular expression and searches for that expression instead of for a plain string. The getPat( ) function turns the user's string into a regular expression. Among other tricks, it wraps the word in regular expression verbiage to make MySQL search for the string as a complete word. Thus, a search for Java will not turn up JavaScript titles. MySQL does not support the \b or \w metacharacters known to Perl programmers, so we have to use ungainly constructs such as (^[^a-zA-Z]) in our regular expression.

The SELECT call sets up the query to use the regular expression through an RLIKE clause, which specifies a regular expression search:

 select isbn, title, pubdate, author     from Titles where title rlike ?

There is no particular advantage to using the ? and the bind variable in a CGI program. The whole program has to be rerun each time and rebuild the statement handle, so you cannot achieve any greater speed through the use of a bind variable. But doing so helps to make the program's operation clear and is a good habit.

The following lines process the $Records array through the book_view.tt template file:

 my $tt = Template->new(  );    $tt->process('book_view.tt', $Records);

These statements come at the end of the program, because the Template module and web server take care of sending the resulting HTML to the browser.

In case you want to try out CGI and DBI without the extra trouble of using the Template module, another version of this program, called book_view_simple.cgi , is included on the O'Reilly web site. It does all its own processing using CGI module calls.

only for RuBoard - do not distribute or recompile