[Chapter 8] 8.3 Example 2: A Discussion Forum

8.3 Example 2: A Discussion Forum

Our second, more complex example allows users to post messages in a browser-based threaded discussion forum. The granddaddy of all discussion lists, Usenet, allows the Internet community to share its views on literally thousands of topics, ranging from C programming to The X-Files. A more focused list, run by RevealNet at http://www.revealnet.com, is geared to PL/SQL developers.

The system presented here is based loosely on the forums run by the online version of InfoWorld computer magazine (http://www.infoworld.com). Each week, the editors at InfoWorld select a number of topical issues in the computer industry for its readers to discuss. These forums allow registered users to express their opinions on both the forum topic and the posts from other readers. Figure 8.8 shows the user interface for the discussion forum we'll develop in this section.

Figure 8.8: A typical discussion list

A vibrant internal discussion forum is a great way for users and developers to communicate simply and efficiently. For example, a forum about an IS application provides users with the ability to report bugs (e.g., "the total dollars on this report should be double-underlined") and suggest new, potentially interesting ideas ("It'd be cool if we could click on the client number and get an AR report"). Monitoring these discussions can help you design better systems and establish your department, or at least you, as responsive and customer friendly.

8.3.1 Storyboard

Our system follows a well-established format. Users begin at a login screen where they provide a username and password. To allow users to express both personality and anonymity in their postings, we'll create our own list of users and not use the OWA_SEC package described in Chapter 7, The PL/SQL Toolkit. (This also saves the DBA from user maintenance.) Of course, the downside is that you now have a new list of users, possibly increasing maintenance. However, building maintenance features into the system (such as allowing a user to create a new account) helps shift the burden some so that users are maintaining their own information.

Initially, a user must enroll in the system and provide some optional information, such as a real name, an email address, and a personal description. Once the user logs in, he is presented with a list of the various forums. He can choose to create a new forum topic or respond to a previous post. If he chooses to create a forum, he is brought to a screen where he can enter the forum subject and its topic. If he chooses to view a forum discussion, he is brought to a list of all the previous responses.

These posts are arranged hierarchically, meaning that responses to a particular post are indented beneath the original message. Each post shows the subject of the message, the author, and the date it was created. Clicking one of these posts presents its full text, as well as a hyperlink labeled "Respond." Clicking the link allows the user to enter a response to the message. Figure 8.9 shows the storyboard for the system.

Figure 8.9: A storyboard for a threaded discussion list

8.3.2 Data Model

Figure 8.10 shows a straightforward data design for the discussion list. The first table we'll need is one to hold information about the forum members. The table, called MEMBERS, contains fields for the username, password, real name, and a personal description. All fields except the username are optional.

Figure 8.10: The data model for the discussion forum system

The second table, named MESSAGES, holds the posts submitted by the forum members. Its fields include a primary key based on a sequence value, the username of the member who submitted the message, a subject line, and the text of the message itself. To represent the hierarchical relationship between the messages, MEMBERS is defined recursively: an additional field must hold the primary key of the message's parent.

Notice that it isn't necessary to create a separate table to hold the forums. Instead, we can simply treat a forum as a message that is the root of a thread of other messages. By convention, we'll differentiate a forum from a normal post by setting its parent field to zero.

8.3.3 Implementation Notes

The threaded discussion list is complex enough that we should break it into simpler parts. The storyboard suggests at least two components: a package to register new members and a package to display the discussion list itself. Breaking the system into pieces lets us tackle the problem in discrete, logical steps.

In the next sections, we'll implement the system using three packages. The first package, GENERAL_FORM, will contain useful formatting procedures that are shared between the other packages. The second package, FORUM_USERS, will handle user management. The third package, FORUM, will handle all the code needed to allow users to post and view messages. Table 8.2 shows these packages with their procedures.

Table 8.2: The Discussion Forum Packages
Package	Procedures	Description
GENERAL_FORM	print_input_row print_textarea_row	Contains formatting procedures for the various forum procedures
FORUM_USERS	login_form login get_current_user create_user_form save_user_info	Handles user management for the forum
FORUM	print_thread_links current_forum_list view_message create_msg_form save_message	Handles users' posting and viewing of forum messages

Finally, we'll create all the application's objects (tables, packages, etc.) in a database schema named DISC_LIST.

8.3.4 The GENERAL_FORM Package

The GENERAL_FORM package contains two procedures that format form input elements into an HTML table. Both do the following basic things:

Open a new table row.
Print the passed label in the first column.
Print an input element in the second column; the element's attributes are set using the various parameters.

Conceptually similar to the HTP package procedures, the GENERAL_FORM procedures use parameters to set the tag attributes. Creating a library of simple procedures like these can encapsulate line after line of clumsy HTML code in a single call, resulting in cleaner and shorter programs. Table 8.3 shows the procedures and functions of the GENERAL_FORM package.

Table 8.3: The GENERAL_FORM Procedures
Procedure	Parameters	Description
print_input_row	i_label IN VARCHAR2 i_input_name IN VARCHAR2 i_hidden_flag IN BOOLEAN DEFAULT TRUE i_size IN NUMBER DEFAULT 40 i_value IN VARCHAR2 DEFAULT NULL	Formats a text input box using a table
print_textarea_row	i_label IN VARCHAR2 i_input_name IN VARCHAR2 i_cols IN NUMBER DEFAULT 40 i_rows IN NUMBER DEFAULT 7	Formats a textarea input box using a table

8.3.4.1 Specification

Here's the specification for the GENERAL_FORM package:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE general_form
IS

   PROCEDURE print_input_row (
      i_label IN VARCHAR2,
      i_input_name IN VARCHAR2,
      i_hidden_flag IN BOOLEAN DEFAULT FALSE,
      i_size IN NUMBER DEFAULT 40,
      i_value IN VARCHAR2 DEFAULT NULL
      );
   
   PROCEDURE print_textarea_row (
      i_label IN VARCHAR2,
      i_input_name IN VARCHAR2,
      i_cols IN NUMBER DEFAULT 40,
      i_rows IN NUMBER DEFAULT 7
      );

END;

8.3.4.2 Body

The GENERAL_FORM procedures are simple enough that they don't require any annotation. Here's the code:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE BODY general_form
IS
   
   /*
   || Prints a single two-column table row; 
   ||    Column 1 is description,
   ||    Column 2 has either an input element plain text
   */
   PROCEDURE print_input_row (
      i_label IN VARCHAR2,
      i_input_name IN VARCHAR2,
      i_hidden_flag IN BOOLEAN DEFAULT FALSE,
      i_size IN NUMBER DEFAULT 40,
      i_value IN VARCHAR2 DEFAULT NULL
      )
   IS
   BEGIN
      HTP.print ('<tr>');
      HTP.print ('<th align=right>' || i_label || ':</th>');
      HTP.print ('<td>');
      IF NOT i_hidden_flag
      THEN
         HTP.formtext (
            cname => i_input_name,
            csize => i_size,
            cvalue => i_value
         );
      ELSE
         HTP.formpassword (
            cname => i_input_name,
            cvalue => i_value
         );
      END IF;
      HTP.print ('</td>');
      HTP.print ('</tr>');
   END;
   
   /*
   || Create a two-column table
   ||    Column 1 is description
   ||    Column 2 is a <textarea> field
   */
   PROCEDURE print_textarea_row (
      i_label IN VARCHAR2,
      i_input_name IN VARCHAR2,
      i_cols IN NUMBER DEFAULT 40,
      i_rows IN NUMBER DEFAULT 7
      )
   IS
   BEGIN
      HTP.print ('<tr>');
      HTP.print ('<th align=right>' || i_label || ':</th>');
      HTP.print ('<td>');
      HTP.formtextarea (i_input_name, i_rows, i_cols);
      HTP.print ('</td></tr>');
      HTP.print ('</table>');
   END;
END;

8.3.5 The FORUM_USERS Package

Our second package, FORUM_USERS, will implement the user management portions of our discussion list. It performs two basic functions: user authentication and user enrollment. The authentication code is responsible for displaying a login form, verifying the username and password, and setting a cookie to save the username throughout the user's session. The enrollment code is responsible for displaying a user information screen and saving that data in the MEMBERS table.

One thing to note about the enrollment system is that it only inserts new users; once created, a user cannot update his profile. Although it would be relatively straightforward to also update a row, doing so would require considerably more code. Since updating a row adds little that is interesting to the example, users of the application in its current state (after all, this is just an example) simply have to make sure they spell their names right on the first try!

Table 8.4 shows the procedures and functions of the FORUM_USERS package.

Table 8.4: The FORUM_USERS Procedures and Functions
Procedure/Function	Parameters	Description
login_form	i_username IN VARCHAR2 DEFAULT NULL i_message IN VARCHAR2 DEFAULT NULL	Creates a login screen for users
login	i_username IN VARCHAR2 i_password IN VARCHAR2	Verifies the user's login
get_current_user	None	Assigns the author of a message
create_user_form	None	Displays the data entry form to create a new user
save_user_info save_user_info	i_username IN VARCHAR2 DEFAULT NULL i_name IN VARCHAR2 DEFAULT NULL i_password IN VARCHAR2 DEFAULT NULL i_email_address IN VARCHAR2 DEFAULT NULL i_desc IN VARCHAR2 DEFAULT NULL	Inserts new user data into the MEMBERS table

8.3.5.1 Specification

The leftmost portion of the storyboard lays out how users log in to the system. By referring back to the diagram, we can see that we need at least three procedures in the specification: a procedure to create the login form itself (login_form); one to enroll new users (create_user_form); and one to insert the new user's information into the MEMBERS table (save_user_info). Since HTTP is a stateless protocol, we'll also need a function to pass the user's login name to the package that implements the discussion list code. This function is named get_current_user.

Here's the specification for FORUM_USERS:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE forum_users
IS
   
   PROCEDURE login_form (
      i_username IN VARCHAR2 DEFAULT NULL,
      i_message IN VARCHAR2 DEFAULT 'Please Log-In'
      );
   
   PROCEDURE login (
      i_username IN VARCHAR2,
      i_password IN VARCHAR2
      );
   
   FUNCTION get_current_user
      RETURN VARCHAR2;
   
   PROCEDURE create_user_form;
   
   PROCEDURE save_user_info (
      i_username IN VARCHAR2 DEFAULT NULL,
      i_name IN VARCHAR2 DEFAULT NULL,
      i_password IN VARCHAR2 DEFAULT NULL,
      i_email_address IN VARCHAR2 DEFAULT NULL,
      i_desc IN VARCHAR2 DEFAULT NULL
      );
END;

8.3.5.2 Body

In the following sections, we'll develop each procedure in the specification for USER_FORUM.

8.3.5.2.1 The login_form procedure.

This procedure creates a login screen that has three elements: a username field, a password field,[2] and a hyperlink used to enroll as a new user. The procedure has two parameters: the first parameter, i_username, sets the default text of the username. The second parameter, i_message, is used to change the message displayed on the form; this gives us added flexibility so that the form can serve multiple purposes. Note how this procedure calls the GENERAL_FORM package to create and format the form input elements:

[2] While the HTML password attribute adds a measure of security to the password field, it's important to remember that the text has only been masked, not encrypted! When the user submits the form, hackers can use a program called a packet sniffer to intercept and read the password. Your database administrator or webmaster must secure your site with a tool such as SSL to prevent this possibility.

 PROCEDURE login_form (
   i_username IN VARCHAR2 DEFAULT NULL,
   i_message IN VARCHAR2 DEFAULT 'Please Log-In'
   )
IS

BEGIN
   HTP.print ('<body bgcolor=white>');
   HTP.print ('<h1>' || i_message || '</h1>');
   HTP.print ('<form action=forum_users.login>');
   general_form.print_input_row (
      'Forum User Name',
      'i_username',
      FALSE,
      30,
      i_username
   );
   general_form.print_input_row (
      'Forum Password',
      'i_password',
      TRUE
   );
   HTP.print ('</table>');
   HTP.formsubmit (cvalue => 'Login');
   HTP.print ('</form>');
   HTP.print ('<p><p>');
   HTP.anchor (
      'forum_users.create_user_form',
      'Enroll as a new user'
   );
END;

Figure 8.11 shows the output of the login_form procedure.

Figure 8.11: The forum login screen

8.3.5.2.2 The login procedure.

This procedure, called when the user submits the information from the login form, is the gateway to the FORUM package, which we'll describe later. The procedure begins by calling the verify_user function to determine if the person has entered a valid username and password. If the user has done this, login executes the set_user procedure, which saves the user's login name in a cookie named forum_user, then calls the procedure to display the forum topic list.

NOTE: Since cookies are relatively easy to hack, this approach is very insecure. For a system in which security (in the "I wanna keep out the bad guys" meaning of the word) is a serious issue, you would also need to use a protocol like SSL or HTTP and set the cookies to expire so they aren't saved on the user's machine.

If the user has entered an invalid username or password, the procedure calls login_form to display an error message along with the original login form:

/*
|| Check to see if username and password are valid
*/
FUNCTION verify_user (
   i_username IN VARCHAR2,
   i_password IN VARCHAR2
   )
   RETURN BOOLEAN
IS
   
   match_count NUMBER := 0;

BEGIN
   SELECT COUNT (*)
     INTO match_count
     FROM members
    WHERE username = i_username
      AND password = i_password;
   IF match_count = 1
   THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;

/*
|| Save username into a cookie
*/
PROCEDURE set_user (i_username IN VARCHAR2)
IS
BEGIN
   OWA_UTIL.mime_header ('text/html', FALSE);
   OWA_COOKIE.send ('forum_user', i_username);
   OWA_UTIL.http_header_close;
END;

/*
|| Main procedure -- perform logic test and take 
|| appropriate action
*/
PROCEDURE login (i_username IN VARCHAR2, i_password IN VARCHAR2)
IS
BEGIN
   IF verify_user (i_username, i_password)
   THEN
      set_user (i_username);
      forum.current_forum_list;
   ELSE
      login_form (i_username, 'Username/Password not found');
   END IF;
END;

8.3.5.2.3 The get_current_user function.

This function, the third block of code in the FORUM_USERS package, fetches and returns the original username stored in the forum_user cookie. The function is used by the FORUM package to assign the author of a message. While this function could also go in the actual FORUM package, there's a nice symmetry to keeping all functions that act on the same data structure (in this case, a cookie) together in the same package. Here's the code:

FUNCTION get_current_user
   RETURN VARCHAR2
IS
   cookie OWA_COOKIE.cookie;
   ret_val VARCHAR2(50) DEFAULT NULL;
BEGIN
   cookie := OWA_COOKIE.get ('forum_user');
   IF cookie.num_vals != 0
   THEN
      ret_val := cookie.vals (1);
   END IF;
   RETURN ret_val;
END;

8.3.5.2.4 The create_user_form procedure

This procedure displays the data entry form to create a new user. This screen, which the storyboard labels as "Fill out form to enroll as new user," is displayed when the user clicks "Enroll as a new user" on the login form. Here's the code:

PROCEDURE create_user_form
IS
BEGIN
   HTP.print ('<body bgcolor=white>');
   HTP.print ('<h1>Welcome, New User!</h1><hr>');
   HTP.print ('<form action=forum_users.save_user_info>');
   -- Existing users cannot change their name or username
   HTP.print ('<table>');
   general_form.print_input_row (
      'Forum User Name',
      'i_username'
   );
   general_form.print_input_row ('Real Name', 'i_name');
   general_form.print_input_row (
      'Forum Password',
      'i_password',
      TRUE
   );
   general_form.print_input_row (
      'Email Address',
      'i_email_address'
   );
   general_form.print_textarea_row ('Description', 'i_desc');
   HTP.print ('</table>');
   HTP.formsubmit (cvalue => 'Create New User Profile');
   HTP.print ('</form>');
END;

Figure 8.12 shows the output generated by the procedure.

Figure 8.12: The "add new user" screen

8.3.5.2.5 The save_user_info procedure.

This procedure, the last procedure in the FORUM_USERS package, attempts to insert the data entered on the "Create User" form into the MEMBERS table after the user presses the "Create New User Profile" button. If the insert is successful, the procedure calls set_user to save the new username and calls the procedure to display the forum list. If the insert fails, either because the user already exists or because there is some other error, the exception section prints an appropriate error message.

Here's the code:

PROCEDURE save_user_info (
   i_username IN VARCHAR2 DEFAULT NULL,
   i_name IN VARCHAR2 DEFAULT NULL,
   i_password IN VARCHAR2 DEFAULT NULL,
   i_email_address IN VARCHAR2 DEFAULT NULL,
   i_desc IN VARCHAR2 DEFAULT NULL
   )
IS
BEGIN
   -- Create the new user
   INSERT INTO members (
      username,
      password,
      name,
      email_address,
      personal_desc
      )
        VALUES (
           i_username,
           i_password,
           i_name,
           i_email_address,
           i_desc
        );
   COMMIT;
   set_user (i_username);
   forum.current_forum_list;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      HTP.print ('<h1>User already exists!</h1>');
   WHEN OTHERS
   THEN
      HTP.print ('<h1>An unidentified error occurred!</h1>');
END;

8.3.6 The FORUM Package

Now that we've built our supporting packages, we can turn our attention to the real meat of the application. The FORUM package displays and manipulates the records in the MESSAGES table. Table 8.5 shows the procedures in the FORUM package.

Table 8.5: The FORUM Procedures
Procedure	Parameters	Description
print_thread_links	i_thread_list IN VARCHAR2 i_expand_thread IN VARCHAR2 DEFAULT `N'	Private procedure used for formatting other procedures
current_forum_list	None	Generates the list of available forums
view_message	i_thread_id IN VARCHAR2 DEFAULT NULL i_hierarchy_flag IN VARCHAR2 DEFAULT NULL	Prints the full text of a message, a link for posting a response, and the threaded list of previous responses
create_msg_form	i_parent_msg IN VARCHAR2 DEFAULT NULL	Creates an HTML form used to respond to a message
save_message	i_parent_msg IN VARCHAR2 DEFAULT NULL i_subject IN VARCHAR2 DEFAULT NULL i_msg_body IN VARCHAR2 DEFAULT NULL	MESSAGES table

8.3.6.1 Specification

We'll need four procedures: one to display a list of forum topics, one to view the full text of a message, one to create a message, and one to save the new message into the MESSAGES table. Here's the specification for FORUM that includes procedures for each of these tasks:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE forum
IS
   
   PROCEDURE current_forum_list;
   
   PROCEDURE view_message (
      i_thread_id IN VARCHAR2 DEFAULT NULL,
      i_hierarchy_flag IN VARCHAR2 DEFAULT NULL
      );
   
   PROCEDURE create_msg_form (
      i_parent_msg IN VARCHAR2 DEFAULT NULL
      );
   
   PROCEDURE save_message (
      i_parent_msg IN VARCHAR2 DEFAULT NULL,
      i_subject IN VARCHAR2 DEFAULT NULL,
      i_msg_body IN VARCHAR2 DEFAULT NULL
      );

END;

8.3.6.2 Body

In addition to implementing the procedures listed in the specification, the body of the FORUM package contains a private procedure called print_thread_links. Even though it can't be called directly from the Web, this procedure is used in all of FORUM's public procedures. Consequently, we'll begin by examining this private procedure, even though it's not declared in the specification.

8.3.6.2.1 The print_thread_links procedure.

This procedure calls itself recursively to produce the indented list of hyperlinked subject headers illustrated in Figure 8.8. Clicking on one of these links displays the full body of the original message.

The procedure accepts two parameters. The first, i_thread_id, is the primary key (as defined in MESSAGES) for the root of the thread. The second parameter, i_expand_flag, is used to make the procedure expand the child elements under the root thread. If the flag is "Y," then the procedure will call itself again, this time using the ID of the child message as the new root thread. Here's the code:

PROCEDURE print_thread_links (
   i_thread_id IN VARCHAR2,
   i_expand_thread IN VARCHAR2 DEFAULT 'N'
   )
IS
   
   CURSOR t_cur
   IS
      SELECT *
        FROM messages
       WHERE msg_parent = i_thread_id
       ORDER BY date_created;
   
   t_rec t_cur%ROWTYPE;
   link VARCHAR2(500);

BEGIN
   HTP.print ('<h4>');
   HTP.print ('<ol>');   -- Start a new ordered list
   OPEN t_cur;
   LOOP
      FETCH t_cur INTO t_rec;
      EXIT WHEN t_cur%notfound;
      HTP.print ('<li>');
      link := 'forum.view_message?i_thread_id=' || t_rec.msg_id;
      HTP.anchor (link, t_rec.msg_subject);
      HTP.italic ('(' ||
                  t_rec.msg_author ||
                  ',' ||
                  t_rec.date_created ||
                  ')');
      HTP.print ('</li>');
      -- Recursively print the children if necessary
      IF i_expand_thread = 'Y'
      THEN
         print_thread_links (t_rec.msg_id, 'Y');
      END IF;
   END LOOP;
   HTP.print ('</ol>');  -- End the ordered list
   HTP.print ('</h4>');
   CLOSE t_cur;
END;

8.3.6.2.2 The current_ forum_list procedure.

This procedure generates the list of available forums that serves as the main entry point of the forum system. From here, the user selects the forum topic that he or she would like to discuss.

If you'll recall from the Section 8.2.2, "Data Model"" section, we defined a forum (as opposed to normal posts) as rows in the MESSAGES table where the parent column equals 0. current_forum_list works by passing the print_thread_links a value of for the root thread and a value of `N' for the expand flag, as shown in this example:

PROCEDURE current_forum_list
IS
   link VARCHAR2(200);
BEGIN
   HTP.print ('<body bgcolor=white>');
   HTP.print ('<h1>Forums</h1><br>');
   link := 'forum.create_msg_form?i_parent_msg=0';
   HTP.anchor (link, 'Create a new forum');
   HTP.print ('<hr>');
   -- Print the threads, but do not recurse
   print_thread_links (0, 'N');
END;

Figure 8.13 shows the output of the procedure.

Figure 8.13: The available forum list

8.3.6.2.3 The view_message procedure.

This procedure prints the full text of a message, a link that allows the user to post a response, and the threaded list of previous responses:

PROCEDURE view_message (
   i_thread_id IN VARCHAR2 DEFAULT NULL,
   i_hierarchy_flag IN VARCHAR2 DEFAULT NULL
   )
IS
   
   m_rec messages%ROWTYPE;
   msg_found BOOLEAN;
   link VARCHAR2(500);
BEGIN
   SELECT *
     INTO m_rec
     FROM messages
    WHERE msg_id = i_thread_id
    ORDER BY date_created;
   HTP.print ('<body bgcolor=white>');
   HTP.print ('<h2>' || m_rec.msg_subject || '</h2>');
   HTP.print ('<i>Posted by ' || m_rec.msg_author);
   HTP.print (' on ' || m_rec.date_created || '</i><p>');
   HTP.print ('<h2>Message:</h2><p>' || m_rec.msg_body || '<p>');
   link := 'forum.create_msg_form?i_parent_msg=' || i_thread_id;
   HTP.anchor (link, 'Respond');
   HTP.print ('<h2>Previous Responses:</h2><p>');
   print_thread_links (i_thread_id, 'Y');
   HTP.print ('<p>');
   IF m_rec.msg_parent != 0
   THEN
      link :=
         'forum.view_message?i_thread_id=' || m_rec.msg_parent;
      HTP.anchor (link, 'Previous Message');
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      HTP.print ('<h1>Message Not Found!</h1>');
END;

8.3.6.2.4 The create_msg_form procedure.

This procedure creates an HTML form used to respond to a message. Before displaying the form, the procedure first confirms that the user is logged in. If not, the procedure calls FORUM_USERS.login_form to force the user to log on. Here's the code:

PROCEDURE create_msg_form (
   i_parent_msg IN VARCHAR2 DEFAULT NULL
   )
IS
BEGIN
   -- Only allow users that are logged in to post
   IF forum_users.get_current_user IS NULL
   THEN
      forum_users.login_form (
         NULL,
         'You must login to post a message'
      );
   ELSE
      -- Print message form
      HTP.print ('<body bgcolor=white>');
      -- Print a header; a msg_id of zero indicates a forum topic
      IF i_parent_msg = 0
      THEN
         HTP.print ('<h1>Create Forum Topic</h1><hr>');
      ELSE
         HTP.print ('<h1>Post response</h1><hr>');
      END IF;
      HTP.print ('<form action=forum.save_message method=POST>');
      HTP.print ('<table>');
      HTP.formhidden (
         cname => 'i_parent_msg',
         cvalue => i_parent_msg
      );
      general_form.print_input_row ('Subject', 'i_subject');
      general_form.print_textarea_row (
         'Body',
         'i_msg_body',
         80,
         10
      );
      HTP.print ('</table>');
      HTP.formsubmit;
      HTP.print ('</form>');
   END IF;
END;

Figure 8.14 shows the output of the procedure.

Figure 8.14: Posting a response to a message

8.3.6.2.5 The save_message procedure.

This final procedure inserts the user's posts into the MESSAGES table. Like create_msg_form, the procedure first checks to make sure that the user is logged in.[3] Otherwise, people would be able to easily forge messages from other users, which can lead to big trouble. If the user checks out, the procedure inserts the record and redisplays the updated message list. If not, the exception section traps the error and prints a message. Here's the code for this procedure:

[3] Despite our best efforts, users are still free to jump into the application at any point simply by entering the URL into the browser's "Navigation" box. The cookie simply allows us to determine if the user has logged in.

PROCEDURE save_message (
   i_parent_msg IN VARCHAR2 DEFAULT NULL,
   i_subject IN VARCHAR2 DEFAULT NULL,
   i_msg_body IN VARCHAR2 DEFAULT NULL
   )
IS
   
   author members.username%TYPE
      := forum_users.get_current_user;

BEGIN
   -- Only allow users that are logged in to post
   IF forum_users.get_current_user IS NULL
   THEN
      forum_users.login_form (
         NULL,
         'You must login to post a message'
      );
   ELSE
      -- Save message
      INSERT INTO messages (
         msg_id,
         msg_parent,
         msg_author,
         msg_subject,
         msg_body
         )
           VALUES (
              message_seq.nextval,
              i_parent_msg,
              author,
              i_subject,
              i_msg_body
           );
      COMMIT;
      -- Now return to the original message to display the new post
      IF i_parent_msg = 0
      THEN
         current_forum_list;
      ELSE
         view_message (i_parent_msg);
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      HTP.print ('<body bgcolor=white>');
      HTP.print ('<b>An error has occurred<p>');
END;

8.3.7 Security Privileges

We'll follow the same steps we used in the survey example to make the discussion list available on the Web. Since we have two packages, though, we'll have to use two sets of grants:

Log in to disc_list using SQL*Plus.
Grant the EXECUTE privilege on FORUM to the agent account (WEBTEST).
Grant the EXECUTE privilege on FORUM_USERS to the agent account (WEBTEST).
Connect to the agent account (WEBTEST).
Create a synonym named FORUM for disc_list.forum.
Create a synonym named FORUM_USERS for disc_list.forum_users.

Figure 8.15 shows how these commands are used in SQL*Plus.

Figure 8.15: Using SQL*Plus to grant privileges to the PL/SQL agent

8.3.8 What Next?

The FORUM package is the most complex example we'll look at in this book. It illustrates how to break complex systems into multiple packages, how to create reasonably complex navigation schemes, and how to use cookies to save state information. If you have understood this example, you're well on the way to being able to write almost any PL/SQL-and-HTML-based system.


8.2 Example 1: An Anonymous Survey		9. XML