Fall Semester 2004

Lab Notes Six: Maintaing State on the Server. Session Management with DBI.pm and MySQL.

Now that we posted the long notes about the Hangman example we want to make sure that you read them. But perhaps you won't or perhaps you will find them too difficult, so complicated that although you read them, it really feels like you're not. So I would like to work out a simplified example, in which the very essence of these programs will be looked at, and in a known context, to make it even easier to focus on the the new parts.

So let's work out the traditional calculator program (from homework two) within this new approach (illustrated by the second Hangman program and set of notes) in which state is being kept in a database, on the server-side.

Our first step would be to worry about session management. If we do it as in the program illustrated in Lecture Notes Ten then to illustrate only the process of identifying, extracting, and generating sessions one could write the following program:


#--------------------(these are the modules we will be using)------------------

use CGI; 
use MD5; 

#--------------------(these are some constants)--------------------------------

$ID_LENGTH = 8; # length of session_id 

#--------------------(let's get ready to process)------------------------------

$q = new CGI; 

my $session_id = &get_session_id(); # check get_session_id below though, 
                                    # if we don't have one we create one
                                    # and then we redirect to this script
                                    # with the session added as path info 

print $q->header(), $q->start_html(); # redirection is done at the level 
                                      # of headers, and that's why this 
                                      # script is somewhat difficult to 
                                      # debug, and why the beginning of 
                                      # the HTML page comes only here 

print "Your session ID is: $session_id"; # note that we need to devise a
                                         # mechanism to keep the id's unique

print $q->end_html(); # end of script, helper procedures are defined below

#------------------------------------------------------(sub get_session_id)---

sub get_session_id { # this subroutine tries to extract an id from the
                     # path information, and if it does not find one, or
                     # the format of the one it finds is not correct, it
                     # generates a session id with the right format and 
                     # redirects the browser to the same script with the
                     # session id appended to the path 

  my ($id) = $q->path_info() =~ m:^/([a-h0-9]{$ID_LENGTH}):; # extract id 

  return $id if $id; 

  $id = &generate_id(); # if we reach this stage we didn't find 
                        # a (valid) id, so we generate a one now

  print $q->redirect($q->script_name() . "/$id"); # and we call ourselves
                                                  # right away with the id
                                                  # as added path info 

  exit 0; 


#-------------------------------------------------------(sub generate_id)---

sub generate_id {

  $SECRET = "some secret phrase"; 

  my $id = hash($SECRET . rand()); # note that hash is defined below 


#--------------------------------------------------------------(sub hash)---

sub hash {
  my $value = shift; # take the first argument and use it in hexhash 
  return substr(MD5->hexhash($value), 0, $ID_LENGTH); 

One can try this script here.

If you try it, what do you notice:

What we need now is to provide the capability to:

For this reason let's now implement the calculator program, to clarify how it's done.

We will be using this table to store the sessions and the state:

mysql> use a348
Database changed
mysql> create table dgerman_accumulator (
    ->   session_id char(8) primary key, 
    ->   acc        int, 
    ->   modified   timestamp 
    -> ); 
Query OK, 0 rows affected (0.02 sec)

To keep a long story short, the program looks like this:

use CGI; 
use DBI;
use MD5; 

$DB = "DBI:mysql:a348"; $username = "a348"; $password = "a348AG"; 
$DB_TABLE = "dgerman_accumulator"; $SECRET = "something secret"; 
$EXPIRE = 30 * 60 * 60 * 24; # one month
$MAX_TRIES = 10; $ID_LENGTH = 8; $q = new CGI; 

$DBH = DBI->connect($DB, $username, $password, { PrintError => 0 }) ||
       die "Couldn't open database: ", $DBI::errstr; 

my ($session_id) = &get_session_id(); 

my $acc          = &get_state($session_id); 

# note: no need to initialize if it's not found 

$acc             = &calculate($acc, 

&save_state($acc, $session_id); 

print $q->header, $q->start_html; 


print $q->end_html;


#--------------------------------(end of main program)------

sub show_form {
  print $q->start_form(), 
        "Type an argument: ", 
        "Then please choose a function: ", 
                        -name    => 'fun', 
                        -values  => ['non', 'add', 'sub'], 
                        -labels  => {  'non' => 'Click me!', 
                                       'add' => 'Deposit', 
                                       'sub' => 'Withdraw' 
                        -default => 'non'
        "When done please press ", 
        $q->submit(-value=>'Proceed'); # perhaps add $q->end_form(); here?

#--------------------------------(this was our basic form)---

sub get_session_id {


  my ($id) = $q->path_info =~ m:^/([a-h0-9]{$ID_LENGTH}):o;
  return $id if $id and &check_id($id); 

  my $session_id = &generate_id; 
  die "Couldn't make a new session_id" unless $session_id; 

  print $q->redirect($q->script_name() . "/$session_id"); 


#--------------------------------(needed above)--------------

sub expire_old_sessions {

  WHERE (unix_timestamp() - unix_timestamp(modified)) > $EXPIRE


#--------------------------------(also needed above)---------

sub generate_id {

  my $tries = 0; 

  my $id = &hash($SECRET . rand()); 

  while ($tries++ < $MAX_TRIES) {
    last if 
      $DBH->do("INSERT INTO $DB_TABLE (session_id, acc) VALUES ('$id', 0)"); 
    $id = &hash($SECRET . rand()); 

  return undef if $tries >= $MAX_TRIES;

  return $id; 


sub hash {
  my $value = shift; 
  return substr(MD5->hexhash($value), 0, $ID_LENGTH);   

#--------------------------------(last one needed)-----------

sub check_id {
  my $id = shift; 
  return $id 
    if $DBH->do("SELECT 1 FROM $DB_TABLE WHERE session_id = '$id'") > 0; 
  return $id 
    if $DBH->do("INSERT INTO $DB_TABLE (session_id, acc) VALUES ('$id', 0)"); 
  return ''; 

#--------------------------------(retrieve acc)--------------

sub get_state {
  my $id = shift; 
  my $query = "SELECT * FROM $DB_TABLE WHERE session_id = '$id'"; 
  my $sth = $DBH->prepare($query) || die "Prepare: ", $DBH->errstr; 
  $sth->execute || die "Execute: ", $sth->errstr;
  my $state = $sth->fetchrow_hashref;
  return $state->{acc}; 

#--------------------------------(calculate new acc)---------

sub calculate { 
  my ($acc, $fun, $arg) = @_; 
  return $acc + $arg if $fun eq 'add'; 
  return $acc - $arg if $fun eq 'sub'; 
  return $acc; 

#--------------------------------(store new acc)-------------

sub save_state {
  my ($state, $id) = @_; 
  my $sth = $DBH->prepare(<<END) || die "Prepare: ", $DBH->errstr; 
  SET acc = ?
  WHERE session_id = '$id'
  $sth->execute($acc) || die "Execute: ", $DBH->errstr; 

#--------------------------------(print current acc)---------

sub status {
  my ($acc) = @_; 
  # $acc += 0; 
  print "The accumulator is currently $acc. <p>"; 
One can access it here.

I hope you find this one easier to follow than the original Hangman game.

What follows is your...



Two tasks:

  1. Install PHP (see Lecture Notes Twelve) and
  2. have the basic <? phpinfo() ?> script installed and working.
  3. Assume you're a GRADUATE (see below).

As a GRADUATE add the following:

Two more tasks:

  1. Think and determine how the last example above is working.
  2. Implement your Homework Two this way.

Last updated: Oct 13, 2004 by Adrian German for A348/A548