Dynamic Graphics and Personalization

A continuation of the discussion on creating graphics dynamically on the Web.
Creating the Database Tables

Before we can begin to work on the applications themselves, we need to create the underlying database tables they will use. We will need two different tables: one to hold the individual stock values on different dates and another to store user personalization information.

The first table, called StockValues, has three columns: a symbol, which can contain up to six characters; a value, which can range from 0 to 999999.999; and a date. We can create such a table with the following SQL, most often by using the interactive mysql client program that comes with MySQL:

CREATE TABLE StockValues (
   symbol CHAR(6) NOT NULL,
   value NUMERIC(6,3) NOT NULL,

Each row in the above table refers to the value of a single stock on a single day. By storing information like this, we can easily create charts for a stock during arbitrary periods of time. For the sake of brevity, our applications will always display all of the available values for a stock. The above table also gives us many possibilities for additional applications, such as finding a stock's high and low values during a given time period.

How will StockValues be populated with values? Most commercial sites retrieve stock information from a commercial service, using a background process to place the information in a database table. My budget is more limited than the average business web site, so I decided to insert some arbitrary values into StockValues. In order to do this, I used the interactive mysql client program, and entered several queries of this type:

INSERT INTO StockValues (symbol, value, date)
   VALUES ("ZZZZ", 100, "1999-07-14");

The second table we will create is for Apache::Session::DBI, a version of Apache::Session that allows us to store information about a particular user in a database table. The table's name and format are determined by the Apache::Session API:

CREATE TABLE sessions (
   id char(16),
   length int(11),
   a_session text
Once we have created this table, we can ignore the fact that Apache::Session stores its information in a database. So far as we are concerned, we perform a magic incantation at the beginning of our code, which retrieves the current session values. We retrieve the user's session ID by reading an HTTP cookie:
my $id = $r->header_in('Cookie');
$id =~ s|SESSION_ID=(\w*)|$1|;
Then, once we have assigned $id the value of the user's session ID, we tie the %session hash to the “sessions” table with the Apache::Session::DBI module:
my %session;
tie %session, 'Apache::Session::DBI', $id,
DataSource => 'dbi:mysql:test:localhost:3306',
UserName   => '',
Password   => ''
From this point on, any name,value pairs stored in %session in previous sessions will be available. By the same token, we can assign
$session{key} = "value";
and be sure that in our next invocation, despite HTTP's statelessness, we can retrieve the same value. Apache::Session thus makes it possible for us to store arbitrary quantities and types of information about a user.

We will store three session variables for each user. The e-mail address and name will be stored as scalars, and the user's current holdings will be stored as a hash reference. The keys to the %portfolio hash will be the stock symbols, and the number of shares owned in that particular security will be stored as the values.

When we want to store %portfolio as part of the session, we turn it into a reference and store that in %session with the key “portfolio”:

$session{portfolio} = \%portfolio;

A reference is a specially tagged scalar, which allows us to store it in a hash. We retrieve it later with the following complicated-looking code:

my %portfolio =
   defined $session{portfolio} ?
   %{$session{portfolio}} : ();
The above uses Perl's trinary operator ?: as a shortcut to “if-then”. It means that if $session{portfolio} is defined, then dereference it into its original hash value and assign it to %portfolio. If it is undefined, then assign the empty hash, (), to %portfolio. After this line of code is executed, %portfolio will contain the user's current portfolio. By using Apache::Session, we can maintain the illusion of state across HTTP transactions, and store many users' portfolios in our database.

Profile Editor

Now we will write the two applications that will work with this information. The code for those two applications can be found in the archive file at ftp://ftp.linuxjournal.com/pub/lj/listings/issue66/3629.tgz. The first will be StockProfile.pm, a Perl module for mod_perl that will allow users to create and edit their portfolios and personal information.

Since our program will be running as part of mod_perl, we will need to remember several things. First and foremost, we must create a new Perl module and package with a subroutine named “handler”. We will configure Apache to invoke this “handler” subroutine whenever a particular URL is requested from the HTTP server. Because our subroutine will be part of Apache rather than invoked in a separate process, and because mod_perl compiles and caches code that we write, our routine will run much faster than if it were a CGI program.

We must also remember to adhere to mod_perl programming conventions, the most important being to use lexical (“temporary” or “my”) variables as much as possible. Global variables stick around across invocations of mod_perl, which can lead to memory leaks and odd bugs. We ensure that we use “my” before variables with the use strict pragma at the top of our program.

Our module, Apache::StockProfile.pm (see Listing 1 in the archive file), has three stages: First, it initializes all of the variables and information, grabbing the current list of securities from StockValues and initializing the user's profile. Then, if the module was invoked with the POST method, it sets or modifies the user's profile information as necessary. Finally, it produces an HTML form that can be used to modify the profile further.

The first thing we do in “handler”, as with all mod_perl modules, is retrieve the Apache request object, traditionally known as $r. This object's methods allow us to retrieve and set everything having to do with the HTTP transaction. For instance, we can set outgoing headers with $r->header_out, the “Content-Type” header with $r->content_type, and send the final headers with $r->send_http_header.

However, certain things are more easily accomplished—at least to experienced CGI programmers—with CGI.pm, the standard module for CGI programming. We can get a version of that API by using and creating an instance of CGI::Apache. The created object gives us access to HTML form elements and debugging tools (including the invaluable dump method) using the familiar interface from CGI.pm. Not everything works in the same way, but it is good enough for almost all purposes.

Our main use of CGI::Apache in this program is to retrieve the HTML form elements, which are submitted via the POST method. StockProfile.pm both creates the form and handles its submission, which might seem odd at first but makes for a compact and easy-to-maintain type of code.

We retrieve a list of current symbols with a simple SELECT statement to the database. However, if we were to simply say “SELECT symbol FROM StockValues”, we would get one row for each value of a symbol or about five values per week, if we add one new value each day. In order to retrieve distinct values, we add the qualifier DISTINCT to our SELECT query. We also ask that the symbols be alphabetized, so that they will be listed in reasonable order:

my $sql = "SELECT DISTINCT symbol FROM StockValues";
$sql .=   "ORDER BY symbol ";

We set the values of $name, $email and %portfolio based on the user's session information, as we discussed earlier. We then use this information to fill in the HTML form that allows the user to modify his or her profile. I prefer to use a table for such forms to ensure the columns line up straight, but that is simply a matter of aesthetics; the important thing is that each element must have its own, unique name and they will be the same names as our POST-handling code at the top of the program expects to use.