by David Harlan
Throughout this book, I have persistently reminded you that if you want people to come back to your Web site, you have to give them a good reason to return. And I've said all along that fresh, dynamic content is one such reason. You've seen some good ways to add some dynamic elements to your site: server-side includes, client pull, server push, and others. This chapter introduces one of the best methods for maintaining large amounts of dynamic data on a Web site: databases.
You may remember reading earlier about DBM files, which are a simple form of database functionality accessible through Perl. I showed you some useful, if simple, examples and explained their limitations. This chapter examines some optional add-on functionality to Perl that allows for interaction with full-fledged relational database management systems (RDBMS), such as Informix, Sybase, and Oracle. Then I'll demonstrate the capabilities of one of these systems in detail.
In the Web survey example in Chapter 2 "Introduction to CGI," and Chapter 3 "Advanced Form Processing and Data Storage," I used Perl's built-in database functionality to store and retrieve some relatively complex data. Using DBM files was effective in this example, but barely so. You may have noticed that I had to work around some functional limitations of Perl's built-in DBM file handling. Also, you should have noticed that these DBM files had little advanced functionality-no relating between tables, for example, and no way to distinguish between fields in a given record.
Had I used an RDBMS, I would have been able to access the data more easily. I could have averaged and totaled data much more readily. In fact, that Web survey would have been an excellent candidate for a relational database application.
This last statement, of course, begs the question "How do I decide whether I should use a high-end database on my site?"
The easiest answer is this: If you have access to an RDBMS, use it on your Web site. You will find that many common tasks become much easier in a database environment. User tracking is a breeze. Summarizing Web logs can be simplified, and the data from the logs can be analyzed extensively if you transfer your logs to a database.
But what if you don't have an RDBMS and are wondering whether you should invest in one? Then the decision (like any business decision) comes down to a cost/benefit analysis.
The costs for an RDBMS can be significant. In addition to the initial outlay for the software, you have to calculate installation and configuration time, time to integrate the software with your Web server, time to learn the new system, and additional administrative effort that comes with any complex piece of software.
But keep in mind that all these tasks can be accomplished by a single person; I know this from experience. The effort can be a little draining at first, but it worked out well for me.
So, then, what are the benefits? As I said before, an RDBMS is capable of storing and retrieving large amounts of complex data with extraordinary speed and reliability. A Webmaster can, obviously, use a database to track users and analyze logs. But you could go so far as to keep your entire site in a database, accessing the whole thing through CGI. Why? This setup would allow you to use the built-in search features of an advanced RDBMS instead of adding search functionality, as I demonstrated in Chapter 4 "Advanced Page Output." This kind of setup would also allow you to create a rotation of pages, so users see fresh content every time they come to the site.
My best advice is this: If you wish that your users could more easily find, retrieve, and even create discrete pieces of data on your site, you may want to look into adding database functionality to your Web system.
Most manufacturers of database software have jumped onto the Web bandwagon recently. Some manufacturers have built their own Web servers to work directly with their database systems. Almost all of the major players have built add-on tools that give a Webmaster the means to access their databases from a stand-alone Web server.
Given the fact that this book is about Perl, however, we're not interested in those options right now. This section examines the options for accessing databases from Perl.
At this writing, the largest development effort in database-Perl integration is a set of modules called DBperl. This project is an effort by a group in this Internet community to standardize the methods for accessing a database from Perl.
The interface actually consists of two parts: DBI and DBD.
DBI, which is the actual Perl interface, defines a standard set of functions that (theoretically) allow a programmer to access any database from Perl. The goal is to make Perl code independent of the database that it is accessing.
The second part of this system, DBD, essentially is a driver for a specific database. Currently, 10 DBD modules are in some state of development. Most of these modules still are considered to be alpha software, which means that the developers don't recommend their use in production environments. Several Web sites, however, are using these tools fairly rigorously.
If you are looking for the future of Perl-database integration; are willing to take the risks (which perhaps are minimal at this point) of using alpha software; and have the time, inclination, and ability to get DBI up and running on your system, this latest technology is the way that you want to go.
At the other end of the spectrum from a high-end database and DBI are mSQL and mSQLPerl.
mSQL is a lightweight relational database server that supports a subset of the Structured Query Language (SQL), which is standard to the large relational databases. mSQL was created and is sold by Hughes Technologies in Australia. Although its functionality is somewhat limited compared with systems from the major database providers, mSQL does nearly everything that a Webmaster needs it to do, and it costs a fraction of the prices of Oracle, Sybase, and Informix.
mSQLPerl is a Perl 5 module that gives the Perl programmer full access to the mSQL database. Anything that you can do to the mSQL system from the command line, you can do from mSQLPerl. The manufacturer reports that the database itself is being used on Web sites around the world. The mSQLPerl module is reported to be quite stable.
mSQL and mSQLPerl might be a good first step for Webmasters who are not sure whether they need a database. The mSQL server itself is quick and easy to install, and so is the mSQLPerl module. The documentation included with the package is lucid and complete. With this combination, a Webmaster could add significant database capabilities to a Web site in just a few days, with minimal capital expense.
The third and final option that I talk about here is my personal favorite, Oraperl. Oraperl is a set of extensions to Perl 4.036 that provides complete access to an Oracle database server. One obvious drawback of this package is the fact that it is built on an old version of Perl, but its positive traits are many and, in my mind, outweigh the few negatives.
First and foremost, the code is quite well-tested and stable. I have used it on a production Web site and have never had any problems with it. As with mSQL and mSQLPerl, the documentation included with Oraperl is quite good. Finally, installation is well-documented, as are all the additional functions.
If you can afford to purchase an Oracle database server, or if you have access to one already, Oraperl is an excellent database interface option.
The rest of this chapter examines an Oraperl example in detail. Even if you don't think that an RDBMS is in your Web future, you may want to continue reading, if only to see the functionality that databases add to Perl and CGI.
One of the most compelling aspects of the Internet is the ability of people around the world to interact with very few limitations. In the one-on-one communication arena, unfortunately, the Web has not caught up with its older siblings: Usenet, e-mail, and talk. Because many of the newest Internet users are solely Web users, the Web's weakness in the interactivity department has occasionally made new users wonder what the big deal is.
As browser technology advances, and as more specialized plug-ins and add-ons are created, the Web is catching up. Unfortunately, this new technology does not help people who are tied to older browser technology-people who are accessing the Web through online services or who simply don't have the hardware horsepower to run the big new browsers. The obvious answer to the interactivity question right now is CGI, and one of the best CGI methods available to support interactivity is the subject of this chapter: databases.
This section examines a threaded message database built in CGI, using Oraperl. You will see how using a database enabled me to produce a relatively simple and quite reliable application.
Figure 11.1 shows a typical opening screen to a threaded message database. Each row on the page represents a thread-a series of messages on a single topic. You notice that the subject of each thread is a link, and you probably can guess that selecting that link brings up a listing of that thread. Also, in the graphic at the top of the page is an image-map link that allows the user to create a new thread.
Figure 11.1 : This screen shows the opening page of a threaded message database.
When the user selects the create new thread link, he or she goes to the form shown in figure 11.2.
Figure 11.2 : The user fills out and submits this form to create a new message thread.
When the user completes the form and submits it, the server returns the user to the thread listing. As you can see in figure 11.3, the new thread is added to the bottom of the list.
Figure 11.3 : After submitting a new thread, the user returns to the thread-listing screen.
Listing 11.1 shows newthread.pl, the script that processes the user input into a new thread. The first thing that you should notice about this script is the top line. In all the scripts that you've seen up to this time, that line has read #!/usr/bin/perl. Remember that this line tells the operating system that the rest of the file is a program that should be passed to /USR/BIN/PERL for processing. As I said earlier, this example uses an extended version of Perl 4.036 called Oraperl. To parse the extended functionality, you have to call a different interpreter. The interpreter for Oraperl is (not surprisingly) called Oraperl-thus, the first line.
Listing 11.1 Script (newthread.pl) to Parse Information into a New Message Thread
#!/usr/bin/oraperl
require "process_cgi.pl";
#Login to oracle
$lda=&ora_login('oracle_sid','oracle_user','oracle_pass') || die $ora_errstr;
#Get a new messageid
$csr=&ora_open($lda, 'select max(msgid) from msghome');
if (($msgid)=&ora_fetch($csr)) { $msgid=$msgid+1; } else {$msgid=1;}
&ora_close ($csr);
#find the message id for the last message in the database
$select="select max(msgid) from msghome where threadid = (select max(threadid) from msghome)";
$csr=&ora_open($lda,$select);
($previd)=&ora_fetch($csr);
As you move farther down the script, you see a call to the function &ora_login. This function is the first example of the extended functionality of Oraperl. The function requires an Oracle System ID, an Oracle user name, and a password as its arguments; it returns a scalar that uniquely identifies this particular Oracle session.
Notice that I follow this statement with Perl's || (or) operator. If the login fails for some reason, I want to know about it. So on an unsuccessful login, the program exits, using the die function to print the variable $ora_errstr to STDERR. The variable $ora_errstr contains the text that describes the most recent Oracle error that Oraperl encountered. Recall that most Web servers direct STDERR from CGI scripts to the daemon's error log. If I'm conscientious about checking the error log, I'll discover this error and track down the problem.
Every Oraperl script has at least one call to &ora_login-usually near the beginning of the script, and always before any other Oraperl functions are used. Every Oraperl function requires a valid login identifier as an argument or requires another value that depends on a login identifier.
One such function, &ora_open, is used in the next statement in the script. This function takes a login identifier and a Structured Query Language (SQL) statement as its arguments; it returns a statement identifier, also known as a cursor.
SQL is the standard language of major relational databases. In its simpler forms, SQL reads very much like English. The statement that you see in this first use of &ora_open has the fairly obvious function of finding the maximum value of the field msgid in the table msghome. The basics of SQL are beyond the scope of this book, so from here on, I assume that you have some knowledge of the language.
The function &ora_open is the most commonly used Oraperl function. You use &ora_open whenever you want to submit an SQL statement to the database.
After you have the statement identifier, you need to get the data from it. This function is performed by &ora_fetch. Near the top of Listing 11.1, you can see that the result of &ora_fetch is being assigned to $msgid. You should take careful note of the syntax here, however. The result is being assigned to $msgid as the only member of a list; the parentheses create this syntax. This distinction is important, because &ora_fetch returns different data if it is asked for a scalar.
When called in a scalar context, &ora_fetch returns the number of fields returned by the SQL statement. Without the parentheses, this statement always returns 1, which is not the result that you are looking for. In an array context, however, the function returns a row of data returned from the statement. Upon each successive call to &ora_fetch, the function returns a row of data. When there is no data to return, the function fails.
Now you can see why this particular statement is embedded in an if statement. You know that msgid can have only one maximum value, and if it exists, &ora_fetch returns it successfully. Then you want to increment msgid by 1 to create your new message ID. If the function fails, you know that no data exists in the msghome table, because no row in this table can exist without an msgid field. Therefore, you set your new message ID to 1.
With that processing out of the way, you get rid of the statement ID by calling &ora_close. This act is simply a matter of housekeeping and not strictly necessary in this script; any statement identifiers from this script are disposed of properly when the script ends. I habitually close the identifiers when I finish, however, to avoid any unnecessary memory use.
The next section of the script finds the message ID of the last message in the database. Understand that you can define the last message in the database in several ways-as the message with the highest ID number, for example. But for purposes of this example, the last message in the database is the message from the highest-numbered thread with the highest message ID.
You need this last message ID to set the preceding message ID for the message that you are creating. To understand this situation, it probably would help to know a bit about the structure of the table that this data is going into. The table, called msghome, consists of eight fields:
| NOTE |
This table is relatively simple, but it stores all the necessary data for this application. If you want to use the scripts on the CD-ROM that accompanies this book to put this message database on your own Web server, you first have to create the msghome table in your Oracle database. |
To find the message ID of the last message in the database, you use the SQL select statement, which you can see near the bottom of Listing 11.1. This statement is more complex than the one used earlier in this section but still is fairly easy to understand. If you look at the part of the statement after threadid=, you see a complete select statement embedded in the main select statement. This embedded select is looking for the highest-thread ID in msghome. When you look at the statement as a whole, you see that it is looking for the highest message ID that belongs to the thread with the highest thread ID.
Now that you know the ID number of the new message and last message in the database, you can insert that information into the database and create the HTML for this new message. Listing 11.2 shows this part of the process.
Listing 11.2 Part 2 of the Script to Update the msghome Table and Create the HTML Display
&parse_input(*fields);
#parse out illegal characters and HTML
$fields{'body'} =[td] s/<[^>]+>//g;
$fields{'body'} =[td] s/\n/<br>/g;
$fields{'subject'} =[td] s/<[^>]+>//g;
$fields{'subject'} =[td] s/\'/'/g;
$fields{'author'} =[td] s/<[^>]+>//g;
$fields{'author'} =[td] s/\'/'/g;
#insert the new message information into msghome
$insert="insert into msghome
(msgdate, msgid,subject,author,threadid,previd)
select sysdate, '$msgid',
'$fields{'subject'}','$fields{'author'}',
'$msgid','$previd' from dual";
$csr=&ora_open($lda,$insert);
&ora_commit($lda);
&ora_close ($csr);
#update nextid for the previous message
$csr=&ora_open($lda,"update msghome set nextid = '$msgid' where msgid = '$previd'");
&ora_commit($lda);
#print out the HTML file for this message
open(F, ">/opt/lib/httpd/htdocs/traveler/log/$msgid.html");
print F "<title>$fields{'subject'}</title>";
print F "<body bgcolor=\"FFFFFF\"><center>";
print F "<table width=480 border=0 cellpadding=3>\n";
print F "<tr><td colspan=2><img src=\"/traveler/tra211a.gif\">";
print F "<tr><td><b>Subject:</b><td>$fields{'subject'}";
print F "<tr><td><b>Author:</b><td>$fields{'author'}";
print F "<tr><td valign=top><b>Message:</b><td>";
print F $fields{'body'};
print F "</table><p><hr width=480><a href=\"/cgi-bin/main/msgnext/$msgid\">
<img src=\"/traveler/log/nextmsg.gif\" border=0></a>";
print F "<a href=\"/cgi-bin/main/msgprev/$msgid\">
<img src=\"/traveler/log/prevmsg.gif\" border=0></a>";
print F "<a href=\"/cgi-bin/main/showthread/$msgid\">
<img src=\"/traveler/log/showthrd.gif\" border=0></a>";
print F "<a href=\"/cgi-bin/main/msgreply/$msgid\">
<img src=\"/traveler/log/msgreply.gif\" border=0></a>";
close F;
#Send the browser back to the main threads screen
print "Location: http://www.iwant.com/traveler/log/log.cgi\n\n";
#close the last cursor and logout from oracle
&ora_close ($csr) || die $ora_errstr;
&ora_logoff ($lda) || die $ora_errstr;
The first thing that you see in Listing 11.2 is the standard call to &parse_input, processing the data from the form into the associative array %fields. After that processing is done, the next section attempts to process out any HTML coding from the fields that comprise the message. These lines also add <BR> tags in place of new lines in the body of the message. Finally, in $fields{'subject'} and $fields{'author'}, any single-quote characters are changed into the appropriate HTML-entity reference for that character. This process is necessary because the single quote is a special character in Oracle. If the data included any single-quote characters, the insert statement (described in the following paragraph) would fail.
The SQL insert statement placed in the variable aptly named $insert places the data from the new message into the database. Like the select statement described in the preceding paragraph, this insert uses an embedded select statement to perform its function. This embedded select may look strange at first, but its function actually is simple: to get the date and time from the system, which it does by selecting sysdate from the table dual. sysdate is an Oracle function that returns the date and time from the computer that is running the Oracle server. dual is a special system table in Oracle that contains only one field: a dummy field called (appropriately enough) dummy. dual is often used in situations such as this one, when you want to get the result of a function without an actual call to a real table.
The rest of the items that are being selected from dual
in this statement actually are constants; notice that they are
all enclosed in single quotes. Selecting constants in this way
simply tells Oracle to return those constants with each row returned
from the select statement. This syntax is an easy way
to insert the user data, along with the system date and time,
into msghome with only one Oracle call.
| TIP |
Errors in my SQL statements tripped me up quite often when I was learning to use Oraperl. These errors frequently are difficult to track down. Nowadays, when I get unexpected results, I print the actual SQL statement that I'm using to my HTML page (during testing only, of course). Then I copy that text and paste it into Oracle's command-line SQL parser, SQL*Plus. This program gives me direct feedback on any errors, usually leading to a quick fix for my problem. |
After defining the insert statement, the script immediately submits it to the database, using &ora_open(). Then the script commits the change that was just made, using &ora_commit(). This Oraperl function takes a login identifier as an argument. When called, &ora_commit() makes any changes to the database performed under the provided login identifier permanent.
The next step in this process tells the database that the message whose ID is stored in $previd should have its nextid field set to the message ID of the new message. This process is performed by &ora_open() and the SQL update statement that you see in Listing 11.2. Again, after sending this change to the database, the script immediately commits the change, using &ora_commit().
When all the database changes are complete, the next section of the script prints the HTML file for this message. In this case (as in any case in which your CGI is writing to files on the server), you have to carefully set file permissions to allow file creation. The creation of the HTML file completes the processing of the new message. All that is left to do is print a Location header to point the browser back to the script that shows the current threads in the database-including, of course, the one that the user just added. The final two lines of the script close the last cursor used and log the user out of the database.
Now that you have seen how to create a new message thread, the logical next step is to learn how to list the threads as shown in figures 11.1 and 11.3. Listing 11.3 shows the script that performs this task.
Listing 11.3 Script (showthreads.pl) to List All the Thread Subjects in the Message Database
#!/usr/bin/oraperl
require "process_cgi.pl";
#Login to oracle
$lda=&ora_login('oracle_sid','oracle_user','oracle_pass') || die $ora_errstr;
#print out the standard header and the top of the page
&print_header;
print "<title>Travelers' log</title>";
print "<body bgcolor=\"FFFFFF\">";
print "<center><table border=0 cellpadding=0 width = 450>";
print "<tr><td><a href=\"/cgi-bin/imagemap/log\">
<img src=\"/traveler/tra210a.gif\" border=0 ISMAP></a></table>";
print "<table border=0 cellpadding = 3 width=450>";
print "<tr><td><b>Date</b><td><b>Author:</b><td><b>Subject</b><td><b>#</b>";
#Select all threads
$datemask='MM/DD, HH24:MI';
$query= "select msgid, subject, author, to_char(msgdate, '$datemask') from
msghome where parentid is null order by msgdate";
$csr=&ora_open($lda, $query);
#run through the data returned from the select statement, printing
#out a row in the table per row returned.
while (($msgid, $subject, $author, $date)=&ora_fetch($csr)) {
#Get the number of messages in a given thread
$csr2=&ora_open($lda,"select count(msgid) from msghome where threadid = $msgid");
($msgcount)=&ora_fetch($csr2);
&ora_close($csr2);
print "<tr><td valign=top>$date<td valign=top>$author<td valign=top>
<a href=\"/cgi-bin/main/showthread/$msgid\">";
print "$subject</a><td valign=top>$msgcount<br>"
}
#print out the bottom of the page and close out the Oracle login.
print "</table>";
print "<p><a href=\"/cgi-bin/imagemap/navbar2\"><img src=\"/navbar2.gif\" ISMAP border=0></a>";
&ora_close ($csr) || die $ora_errstr;
&ora_logoff ($lda) || die $ora_errstr;
Listing 11.3 begins just as Listing 11.1 did, calling in the code for the CGI library and logging into the Oracle database. The script then prints the top of the thread-display page. With those details out of the way, the script moves on to print the threads themselves.
First, of course, you have to create a select statement that gets the proper data. You know that any message that has a null parent message ID is the start of a thread, so you select the messages that meet that criterion, as you see in the line of Listing 11.3 that begins with $query=.
This select statement grabs the message ID, the subject, and the author. The script also uses the Oracle function to_char() to get the message date in the format that you want. This function, which can be used within SQL statements in Oracle, takes a column name and a mask as arguments. In the listing, I defined the mask in the variable $datemask, telling Oracle that I wanted the date to be printed as a two-digit month-day combination, followed by the time in 24-hour notation. (For complete details on date masks, see your Oracle documentation.) Notice also that this select statement uses an order by clause to put the messages in order by date.
After submitting the select statement to Oracle, using &ora_open, the script begins a while loop to process all the data that is returned. Remember that &ora_fetch returns true as long as rows that meet the criteria of the select statement remain to be processed.
Each time through this loop, another SQL select statement is used to get the number of messages in the thread. This statement uses the function count() to determine the number of messages in the database that have a thread ID equal to the message ID that is currently being processed. This function works because the script is looping through all the message IDs that don't have parent messages. Messages without parent IDs are always the beginning of threads, and their message IDs are also their thread IDs. Each time through the loop, therefore, $msgid actually contains a thread ID, and you can use that message ID/thread ID to get the number of messages in that thread.
Notice that when you print each row of the table, you link each subject to a script called showthread. When users follow this link, they see a listing of the messages in that thread. Figure 11.4 shows an example of the output of showthread.
In figure 11.4, you should notice that the Subject lines of replies are indented from those of the original messages. If any of the replies had subsequent replies, they would be further indented. Listing 11.4 shows the script that prints this thread listing.
Listing 11.4 Script (showthread.pl) to Query the Database and Print a Thread Listing
#!/usr/bin/oraperl
require "process-cgi.pl";
$msgid=&path_info;
#Login to oracle
$lda=&ora_login('oracle_sid','oracle_user','oracle_pass') || die $ora_errstr;
#Get the data for the first message in the thread
$datemask='MM/DD, HH24:MI';
$query= "select author, subject, to_char(msgdate, '$datemask') from msghome
where msgid='$msgid'";
$csr=&ora_open($lda, $query);
($threadauthor,$threadsubject,$threaddate)=&ora_fetch($csr);
#print the header and the top of the page
&print_header;
print "<title>$threadsubject</title>";
print "<body bgcolor=\"FFFFFF\">";
print "<center><table border=0 cellpadding=0 width = 450>";
print "<tr><td><img src=\"/traveler/tra211a.gif\" alt= \"Travelers' Log\"><br>";
print "<tr><td><a href=\"/traveler/log/$msgid.html\">$threadsubject</a>,
$threadauthor, $threaddate<br>";
#Get and print the rest of the messages in the thread
&showreplies($msgid);
#print the end of the table
print "</table>";
print "<a href=\"/cgi-bin/main/prevthread/$msgid\"><img src=\"/traveler/log/prevthrd.gif\"
border=0 alt=\"Previous Thread\"></a><a href=\"/traveler/log/threadform.html\">
<img src=\"/traveler/log/newthrd.gif\"
border=0 alt=\"New Thread\"></a><a href=\"/traveler/log/log.cgi\">
<img src=\"/traveler/log/showlog.gif\"
border=0 alt=\"Show Log\"></a><a href=\"/cgi-bin/main/nextthread/$msgid\">
<img src=\"/traveler/log/nextthrd.gif\"
border=0 alt=\"Next Thread\"></a>";
#Close final cursor and log out from Oracle
&ora_close ($csr) || die $ora_errstr;
&ora_logoff ($lda) || die $ora_errstr;
Listing 11.4 starts by calling in the process-cgi.pl library. The script then uses the &path_info subroutine from that library to get the ID number of the thread that the user wants to see. You can see, in Listing 11.3, that the URL for the link to showthread includes the thread ID tacked to the end of it. After logging into the database, the script makes a call to the database to get the author, subject, and date for the message that begins the requested thread. With that data in hand, the script prints the top of the page and the first item in the thread listing. As in showthreads, the Subject line is a link. This time, however, instead of going to a script, the listing in the thread links to the HTML file created when the message was posted.
When the top of the page is printed, you need to print the rest of the messages in the thread. This processing takes place in the call to the subroutine &showreplies (explained in the following paragraphs). When &showreplies is finished, the script prints the bottom of the page, including a button bar that allows users to go to the following and preceding threads, go back to the thread listing, and create a new thread. Finally, the script closes the last cursor and logs out of Oracle.
You still have &showreplies to deal with, however. Listing 11.5 shows this subroutine.
Listing 11.5 Subroutine (showreplies) That Recursively Prints All Replies to a Given Thread
sub showreplies {
local ($msgid)=$_[0];
local ($csr, $csr2, $query, $i);
#select all replies to this message
$query= "select msgid, subject, author, to_char(msgdate, '$datemask')
from msghome where parentid='$msgid' order by msgdate";
$csr=&ora_open($lda, $query);
print "<menu>";
#iterate through those replies
while (($newmsgid, $subject, $author, $date)=&ora_fetch($csr)) {
print "<a href=\"/traveler/log/$newmsgid.html\">$subject</a>, $author, $date<br>\n";
$csr2=&ora_open($lda, "Select count(msgid) from msghome where parentid='$newmsgid'");
#if there are replies to this message, make a recursive
#call to get and print those replies.
($replycount)=&ora_fetch($csr2);
if ($replycount > 0) {&showreplies($newmsgid);}
}
print "</menu>";
&ora_close ($csr);
return 0;
}
After the subroutine definition line, the first task at hand is to place the message ID that was passed to the subroutine in a local variable called $msgid. With that definition accomplished, the script defines four more local variables and moves on to select the replies from the database.
The select statement that gets the replies is shown in Listing 11.5 in the line that begins with $query=. This statement looks for messages whose parent message ID equals the value in $msgid; this condition is what makes them replies to the current message. After opening a cursor for this select statement and printing a <MENU> tag to indent the replies, the script begins a while loop to iterate through the data.
Each time through the loop, in addition to printing the appropriate line of data, the script checks to see whether the message currently being processed by the loop has any replies. If the message does indeed have replies, the script makes a recursive call to &showreplies to get them. If you trace through this process by hand, you see that the replies for each message follow that message and are indented from below that message. If any of those replies have replies themselves, those messages are treated similarly, creating a standard threaded-message database display.
When a user selects one of the messages to view, he or she sees a screen like figure 11.5. This message comes from an HTML file created by a script when a user replied to a message. Notice that this screen has a button bar along the bottom that allows users to navigate within the thread and gives them a chance to reply to the message.
Figure 11.5 : After selecting a message to view, the user sees a message formatted like this.
After the user reads the message in figure 11.5, he or she may want to perform any of the functions in the button bar below the message. The following paragraphs discuss the buttons in left-to-right order.
To view the next message, the user clicks the Forward button. When this occurs, the button calls a script that figures out what the next message is and sends the user there. Listing 11.6 shows this script.
Figure 11.6 : The user enters a message reply on this form.
Listing 11.6 Code (msgnext.pl) That Sends the User to the Next Message in the Database
#!/usr/bin/oraperl
require "process-cgi.pl";
#Login to oracle
$lda=&ora_login('oracle_sid','oracle_user','oracle_pass') || die $ora_errstr;
#Get the message ID from the PATH_INFO variable
$msgid=&path_info;
#Find the next message from the database
$csr=&ora_open($lda,"select nextid from msghome where msgid='$msgid'");
($nextid)=&ora_fetch($csr);
#print out the appropriate location header.
if ($nextid ne '') {
print "Location: http://www.iwant.com/traveler/log/$nextid.html\n\n";
}
else {
print "Location: http://www.iwant.com/traveler/log/$msgid.html\n\n";
}
#logout from Oracle
&ora_close ($csr) || die $ora_errstr;
&ora_logoff ($lda) || die $ora_errstr;
The msgnext script shown in Listing 11.6 is very simple. After reading in the process-cgi library and logging into Oracle, the script gets the message ID from the PATH_INFO variable. Then the script uses an SQL select statement and &ora_open to get the next message ID from the database. Using &ora_fetch to place the returned number in $nextid, the script uses an if...else conditional to make sure that the user is sent to the right place. If $nextid is not equal to the null string (''), the script prints a location header pointing to the HTML file indicated by the $nextid variable. If $nextid is equal to the null string, you know that it currently is the last message in the database.
When I developed this application, I had several options at this point. I could have sent the user back to the first message in the database, essentially wrapping around to the top, or I could have sent some HTML to indicate that the user was already at the end of the database. Instead, I chose simply to stop the user at this point. If $nextid is null, the script sends a Location header back to the browser, pointing back to the message that the user is already viewing.
If the user chooses the Back button in figure 11.5, he or she follows a link that calls the script msgprev. Listing 11.7 shows this script.
Figure 11.7 : The thread listing is printed back to the user after he or she replies to a message.
Listing 11.7 Script to Display the Previous Message in a Thread (msgprev.pl)
#!/usr/bin/oraperl
require "process-cgi.pl";
#Login to oracle
$lda=&ora_login('oracle_sid','oracle_user','oracle_pass') || die $ora_errstr;
#Get the message ID from the PATH_INFO variable
$msgid=&path_info;
#Find the previous message from the database
$csr=&ora_open($lda,"select previd from msghome where msgid='$msgid'");
($previd)=&ora_fetch($csr);
#print out the appropriate location header.
if ($previd ne '') {
print "Location: http://www.iwant.com/traveler/log/$previd.html\n\n";
}
else {
print "Location: http://www.iwant.com/traveler/log/$msgid.html\n\n";
}
#logout from Oracle
&ora_close ($csr) || die $ora_errstr;
&ora_logoff ($lda) || die $ora_errstr;
The msgprev script shown in Listing 11.7 is nearly identical to msgnext (refer to Listing 11.6). Like msgnext, msgprev calls in process-cgi.pl, logs into Oracle, and gets the message ID from the PATH_INFO variable. Instead of selecting the nextid from the database, however, this script calls for previd and then uses a similar conditional to determine what Location header to print. If $previd is not null, it sends the browser a Location header that points to the HTML for the $previd message. If $previd is null, the user is looking at the first message in the database. Again, I could have wrapped around or sent some warning HTML, but I chose to simply leave the user where he or she is. The script ends by closing the last cursor and logging out of Oracle.
The next button in the button bar shown in figure 11.5-Show Thread-goes back to the thread listing. This button links to the showthread script that appears in listings 11.4 and 11.5. Were the user to select this button, he or she would see the screen shown in figure 11.4.
The final option for a user who is viewing the message in figure 11.5 is to reply to that message. If the user selects the Reply button, the form shown in figure 11.6 results.
This reply form is produced by a script called msgnext, shown in Listing 11.8. After the normal preliminary steps, this script calls out to the database to get the subject of the message to which this new message is replying. The script then prints the form. You can see in the fourth print statement that the value of $subject is included, preceded by Re:. After printing the rest of the form, the script logs out of Oracle and exits.
When the user submits the new message, another script processes the entered data and returns the page shown in figure 11.7. This script, called newreply, is shown in Listing 11.9.
Listing 11.8 Script (msgreply.pl) to Print a Form for the User to Reply to a Message
#!/usr/bin/oraperl
require "process-cgi.pl";
&print_header;
$parentid=&path_info;
#Login to oracle
$lda=&ora_login('oracle_sid','oracle_user','oracle_pass') || die $ora_errstr;
#Get the subject from the database
$csr=&ora_open($lda,"select subject from msghome where msgid='$parentid'");
($subject)=&ora_fetch($csr);
#Print out the reply form with the subject filled in
print "<title>New reply</title>";
print "<form method=\"post\" action=\"/cgi-bin/main/newreply\">";
print "<b>Subject:</b><br>";
print "<dd><input type=\"text\" name = \"subject\" value= \"Re: $subject\"><p>";
print "<b>Author or Handle:</b><br>";
print "<dd><input type=\"text\" name = \"author\"><p>";
print "<b>Message:</b><br>";
print "<dd><textarea name=\"body\" WRAP=PHYSICAL rows=15 cols=50></textarea><p>";
print "<input type=\"submit\" value=\"Post Message\">";
print "<input type=\"hidden\" value=\"$parentid\" name=\"parentid\">";
&ora_close ($csr) || die $ora_errstr;
&ora_logoff ($lda) || die $ora_errstr;
Listing 11.9 Script to Process a User's Reply (newreply.pl)
#!/usr/bin/oraperl
require "process-cgi.pl";
#Login to oracle
$lda=&ora_login('oracle_sid','oracle_user','oracle_pass') || die $ora_errstr;
#Get a new messageid
$csr=&ora_open($lda, 'select max(msgid) from msghome');
if (($msgid)=&ora_fetch($csr)) { $msgid=$msgid+1; } else {$msgid=1;}
&ora_close ($csr);
$insert="insert into msghome
(msgid) values ('$msgid')";
$csr=&ora_open($lda,$insert);
&ora_commit($lda);
&ora_close ($csr);
&parse_input(*fields);
&print_header;
$csr=&ora_open($lda, "select threadid from msghome where msgid = '$fields{'parentid'}'");
($threadid)=&ora_fetch($csr);
&ora_close($csr);
$previd=&findlast($fields{'parentid'});
$csr=&ora_open($lda,"select nextid from msghome where msgid = '$previd'");
if (($nextid)=&ora_fetch($csr)) {
$csr2=&ora_open($lda,"update msghome set previd = '$msgid' where msgid='$nextid'");
}
&ora_commit($lda);
&ora_close($csr);
$csr2=&ora_open($lda,"update msghome set nextid = '$msgid' where msgid='$previd'");
&ora_commit($lda);
&ora_close($csr);
The first part of newreply shown in Listing 11.9 performs all the preliminary steps that all the previous scripts in this chapter do. When those steps are out of the way, the script goes to the database to get a new message ID for this reply. The process is similar to what I described in Listing 11.1. The script finds the maximum message ID and then adds 1 to it. After getting this message ID, the script inserts it into the database. I created this insert at this point in the script to prevent the possibility that another user would insert a message into the database while the script does the rest of the processing on this message, thereby coming up with the same new message ID.
After the new message ID is taken care of, the script gets the data from the form and begins processing it. First, the script finds out the ID of the thread to which this message belongs by selecting the thread ID of the parent message ID, which is a hidden field in the reply form. The script saves this value in $threadid for later insertion into the database.
The next piece of information that you need is the ID of the preceding message in the database. On its face, this proposition may seem to be simple, but in reality, it requires quite a bit of processing, as I'll explain later in this section. When this script has the previous ID, it places that message's nextid value and places it in $nextid for later insertion into the database.
Finally, near the end of Listing 11.9, you see two SQL update statements. The first of these statements sets the previd of the next message to point to this new message. Similarly, the second statement sets the nextid of the preceding message to point to this new message.
Now consider how the script found the ID of the preceding message in the first place. First, think about how this database is set up. The messages have been printed in hierarchical and chronological order. When a user steps through a particular thread, he or she goes in chronological order, unless there is an appropriate hierarchical step to take. In the listing shown in figure 11.7, for example, the new message is (obviously) the last one chronologically, but it does not come last when the user is stepping through the database. In fact, you want users to be able to step through a thread as though they were reading the messages one after another, from top to bottom, in the listing. Thus, you need to be careful in finding the appropriate preceding message. The code in Listing 11.10 accomplishes this task.
Listing 11.10 Subroutine from newreply.pl That Determines What Message Precedes the New Message in the Database
sub findlast {
local ($parentid,$select1);
$parentid=$_[0];
$select1="select max(msgid) from msghome where parentid = '$parentid'";
$csr=&ora_open($lda,$select1);
($possible)=&ora_fetch($csr);
if ($possible ne '') {
$lastid=&findlast($possible);
}
else {
$lastid=$parentid;
}
}
The &findlast subroutine in Listing 11.10 is intended to find the last message in the thread of replies to the new message's parent message. At first, you might think that the script could find this last message by finding the maximum message ID of all the messages whose parent ID is equal to the parent ID of the new message. This process would work...sometimes. But if the highest-numbered message in that group has replies, the order is messed up. Remember that when users are stepping through messages, you want them to step down the hierarchy as far as they can before they go to the next chronological message on the current level. Therefore, this new message has to come after the last existing reply to its parent message and all of the replies to that reply.
The &findlast routine accomplishes this task by finding the maximum message ID of the parent ID. If this ID exists (if there are already replies to the new message's parent message, for example), the script calls &findlast recursively to see whether that message has any replies itself. If so, &findlast is called again, and so on until there are no replies. Then the routine simply returns the ID of the message being checked as the last message in this subthread. Then this value is assigned to $previd, as shown in Listing 11.9.
When the IDs of the next and preceding messages are taken care of, the rest of the processing can take place. Listing 11.11 shows the remainder of newreply.
Listing 11.11 Final Section of the Script to Process a User Reply
$fields{'body'} =[td] s/<[^>]+>//g;
$fields{'body'} =[td] s/\n/<br>/g;
$fields{'subject'} =[td] s/<[^>]+>//g;
$fields{'subject'} =[td] s/\'/'/g;
$fields{'author'} =[td] s/<[^>]+>//g;
$fields{'author'} =[td] s/\'/'/g;
$update="update msghome
set (msgdate,subject,author,threadid,parentid,nextid,previd) =
(select sysdate,
'$fields{'subject'}','$fields{'author'}',
, '$threadid','$fields{'parentid'}','$nextid','$previd' from dual)
where msgid='$msgid'";
$csr=&ora_open($lda,$update);
&ora_commit($lda);
&ora_close ($csr);
open(F, ">/opt/lib/httpd/htdocs/traveler/log/$msgid.html");
print F "<title>$fields{'subject'}</title>";
print F "<body bgcolor=\"FFFFFF\"><center>";
print F "<table width = 480 border=0 cellpadding=3>\n";
print F "<tr><td colspan=2><img src=\"/traveler/tra211a.gif\">";
print F "<tr><td><b>Subject:</b><td>$fields{'subject'}";
print F "<tr><td><b>Author:</b><td>$fields{'author'}";
print F "<tr><td valign=top><b>Message:</b><td>";
print F $fields{'body'};
print F "</table><p><hr width=480><a href=\"/cgi-bin/main/msgnext/$msgid\">
<img src=\"/traveler/log/nextmsg.gif\" border=0></a>";
print F "<a href=\"/cgi-bin/main/msgprev/$msgid\">
<img src=\"/traveler/log/prevmsg.gif\" border=0></a>";
print F "<a href=\"/cgi-bin/main/showthread/$threadid\">
<img src=\"/traveler/log/showthrd.gif\" border=0></a>";
print F "<a href=\"/cgi-bin/main/msgreply/$msgid\">
<img src=\"/traveler/log/msgreply.gif\" border=0></a>";
close F;
print "Location: http://www.iwant.com/cgi-bin/main/showthread/$threadid\n\n";
&ora_close ($csr) || die $ora_errstr;
&ora_logoff ($lda) || die $ora_errstr;
The third section of the newreply script begins by processing HTML markup and single quotes out of the fields submitted by the user. Then the script updates the record in the database for the new message, using an SQL update statement. The script gets the message date and time from the Oracle function sysdate; it finds the subject, author, and parent ID in the data from the form. Finally, the script gets the thread ID, next message ID, and previous message ID from values calculated earlier in the script.
When the database is updated, the script creates the HTML file for the new message and prints a Location header to send the browser back to the thread listing. Finally, the script closes the last cursor and logs out of Oracle.
This chapter barely scratched the surface of database integration, but it covered some extremely valuable concepts. The chapter demonstrated how a database can be used to ease the implementation of a fairly complex application. The chapter showed the basic syntax of Oraperl, which is one of the most common Perl-database integration tools; it also showed that a database can be an excellent addition to your Web arsenal. Finally, the chapter whetted your appetite (I hope) for more highly interactive Web applications.
Following are some chapters where you may want to go to whet your appetite even further: