These are the guidelines for Homework Five.

I am following the guidelines for Homework Two. 

The reason is that that set of notes combines both Homework Four and Homework Two notes. 

The requirement is that we use Perl/CGI with database support for sessions. 

So we start like this: 

#!/usr/bin/perl

use CGI; 

print "Content-type: text/html\n\n";

print qq{ 
  <form>    
  
  </form>
}; 

Place this in cgi-bin, make it executable, test it. 

Retrieving the state we need to read from the database. 

To store the sessions we need to create a table. 

What's the structure of the table? 

Seven (5 + 2) columns, called: message, questions, correct, total, key and session_id, modified. 

http://www.cs.indiana.edu/classes/a348/fall2008/whatsnew/one004.txt

The URL above contains the lab notes from Oct. 17, when we developed something similar. 

Let's go in the right folder: 

-bash-3.2$ pwd
/nobackup/dgerman/mysql-5.0.22

Check if your server is running, if it's not you need to start it.

Mine wasn't running so I had to do this: 

-bash-3.2$ ./step008
-bash-3.2$ Starting mysqld daemon with databases from /nobackup/dgerman/mysql

I then connected as a regular user: 

-bash-3.2$ ./mrbean_connect 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use teddy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> show tables;
+--------------------+
| Tables_in_teddy    |
+--------------------+
| example_1013       | 
| example_1014       | 
| example_10148pm    | 
| example_1015       | 
| example_1016       | 
| friday             | 
| judges             | 
| performers         | 
| ratings            | 
| sampleMidterm      | 
| sampleMidterm_1017 | 
| transactions       | 
| users              | 
| votes              | 
+--------------------+
14 rows in set (0.01 sec)

mysql> create table hwFive (
    ->   session_id char(8) primary key, 
    ->   message    varchar(240),
    ->   questions  varchar(240), 
    ->   correct    int, 
    ->   total      int,
    ->   anskey     varchar(32),
    ->   modified   timestamp
    -> );

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------------+
| Tables_in_teddy    |
+--------------------+
| example_1013       | 
| example_1014       | 
| example_10148pm    | 
| example_1015       | 
| example_1016       | 
| friday             | 
| hwFive             | 
| judges             | 
| performers         | 
| ratings            | 
| sampleMidterm      | 
| sampleMidterm_1017 | 
| transactions       | 
| users              | 
| votes              | 
+--------------------+
15 rows in set (0.00 sec)

mysql> describe hwFive;
+------------+--------------+------+-----+-------------------+-------+
| Field      | Type         | Null | Key | Default           | Extra |
+------------+--------------+------+-----+-------------------+-------+
| session_id | char(8)      | NO   | PRI |                   |       | 
| message    | varchar(240) | YES  |     | NULL              |       | 
| questions  | varchar(240) | YES  |     | NULL              |       | 
| correct    | int(11)      | YES  |     | NULL              |       | 
| total      | int(11)      | YES  |     | NULL              |       | 
| anskey     | varchar(32)  | YES  |     | NULL              |       | 
| modified   | timestamp    | YES  |     | CURRENT_TIMESTAMP |       | 
+------------+--------------+------+-----+-------------------+-------+
7 rows in set (0.00 sec)

mysql> 
mysql> exit
Bye
-bash-3.2$ 

So at this point the sessions table has been created. 

Here's the first version of our program: 

#!/usr/bin/perl

use CGI;
use DBI;

use Digest::MD5 qw(md5 md5_hex md5_base64);

$DB       = "dbi:mysql:teddy:silo.cs.indiana.edu:port=13038";
$username = "mrbean"; 
$password = "b3an";
$DB_TABLE = "hwFive";

$SECRET    = "something secret"; 
$EXPIRE    = 30 * 60 * 60 * 24; # one month 
$MAX_TRIES = 10; 
$ID_LENGTH =  8;

$q = new CGI;

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

# start the page -----------------------------------------------------------
print $q->header,
      $q->start_html(-title => 'Database Sessions with URL Rewriting',
                     -bgcolor => 'white');

print qq{ 

  Hello!

}, $q->end_html; 

It connects with the database, then prints Hello! for the user and then quits. 

You can run this from the command line like so: ./one

-bash-3.2$ ./one
-bash: ./one: Permission denied

I forgot to make the file executable: 

-bash-3.2$ chmod 700 one

Now I can run it: 

-bash-3.2$ ./one
Content-Type: text/html; charset=ISO-8859-1

<!DOCTYPE html
        PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
         "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-US">
<head>
<title>Database Sessions with URL Rewriting</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body bgcolor="white">
 

  Hello!


</body>
</html>-bash-3.2$ 

Everything looks good. 

Now we're going to try to retrieve state, also user input. 

You know that you need these definitions at the end of your file: 

# get the state from the database ------------------------------get_state---
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; 
  $sth->finish; 
  return $state; 
}

# retrieve the session ID from the path info. if it's -----get_session_id---
# not already there, add it to the path info (more or less) with a redirect 
sub get_session_id {

  my (@result); 
  &expire_old_sessions(); 
  my ($id) = $q->path_info() =~ m:^/([a-h0-9]{$ID_LENGTH}):o;
  return @result if $id and @result = &check_id($id); 
  
  # if we get here, there's not already an ID in the path info 
  my $session_id = &generate_id(); 
  die "Couldn't make a new session id" unless $session_id; 
  print $q->redirect($q->script_name() . "/$session_id"); 
  exit 0; 
}

# find a new unique ID and insert it into the database -------generate_id---
sub generate_id {
  # create a new session id 
  my $tries = 0; 
  my $id = &hash($SECRET . rand()); 

  while ($tries++ < $MAX_TRIES) {
    last if $DBH->do("INSERT INTO $DB_TABLE (session_id) VALUES ('$id')"); 
    $id = &hash($id); 
  }

  return undef if $tries >= $MAX_TRIES; # we failed 
  return $id; 
}

# check to see that an old ID is valid --------------------------check_id---
sub check_id {

  my $id = shift; 

  return ($id, '') 

    if $DBH->do("SELECT 1 FROM $DB_TABLE WHERE session_id = '$id'") > 0; 

  return ($id, 'The record of your game may have expired. Restarting.')

    if $DBH->do("INSERT INTO $DB_TABLE (session_id) VALUES ('$id')"); 

  return (); 

}

# generate a hash value ---------------------------------------------hash---
sub hash {
  my $value = shift; 
  return substr(md5_hex($value), 0, $ID_LENGTH); 
}

sub expire_old_sessions { # --------------------------expire_old_sessions---
  $DBH->do(<<END); 
DELETE FROM $DB_TABLE
  WHERE (unix_timestamp() - unix_timestamp(modified)) > $EXPIRE
END
}

Now you can add this code, to read the state and the user input: 

# get the current session ID, or make one ----------------------------------
my ($session_id, $note) = &get_session_id();

my $state = &get_state($session_id);

$message   = $state->{'message'}; # state variables
$questions = $state->{'questions'};
$correct   = $state->{'correct'};
$total     = $state->{'total'}; 
$anskey    = $state->{'anskey'};

$answer    = $q->param('answer'); # input

Notice the anskey vs key column name issue in MySQL and how the program runs now: 

-bash-3.2$ ./one
Status: 302 Moved
Location: /c432fff5

-bash-3.2$ 

At the same time the database reflects this run now: 

mysql> select * from hwFive;
+------------+---------+-----------+---------+-------+--------+---------------------+
| session_id | message | questions | correct | total | anskey | modified            |
+------------+---------+-----------+---------+-------+--------+---------------------+
| c432fff5   | NULL    | NULL      |    NULL |  NULL | NULL   | 2008-11-20 17:05:36 | 
+------------+---------+-----------+---------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql>

You see that the id we received from running the program appears in the session_id column. 

At the same time the timestamp on the right shows when the record was created/modified. 

Here's the current time as I type this: 

-bash-3.2$ date
Thu Nov 20 17:09:17 EST 2008
-bash-3.2$ 

Now let's get on with the actual code. First the central if statement: 

if ($message) {

} else {  
  $message = "Welcome to the game, are you ready?";  
}  

Next the output gets bigger with this: 

<form>

  $message <p>
  
  Press <input type="submit" value="Proceed"> to move on. 
  <input type="hidden" name="message" value="$message"> 
</form> 

Now it's time to call it on-line. 

I type:  

http://silo.cs.indiana.edu:11350/cgi-bin/1120/one

And the answer is the form with one button that I expected:

<!DOCTYPE html
	PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-US">
<head>
<title>Database Sessions with URL Rewriting</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body bgcolor="white">
<form>

  Welcome to the game, are you ready? <p>     

  Press <input type="submit" value="Proceed"> to move on.
  <input type="hidden" name="message" value="Welcome to the game, are you ready?">  


</form>
</body>
</html>

But the URL is: 

http://silo.cs.indiana.edu:11350/cgi-bin/1120/one/c2350ff4

And the database has: 

mysql> select * from hwFive;
+------------+---------+-----------+---------+-------+--------+---------------------+
| session_id | message | questions | correct | total | anskey | modified            |
+------------+---------+-----------+---------+-------+--------+---------------------+
| c432fff5   | NULL    | NULL      |    NULL |  NULL | NULL   | 2008-11-20 17:05:36 | 
| ec848de9   | NULL    | NULL      |    NULL |  NULL | NULL   | 2008-11-20 17:15:22 | 
| c2350ff4   | NULL    | NULL      |    NULL |  NULL | NULL   | 2008-11-20 17:15:39 | 
+------------+---------+-----------+---------+-------+--------+---------------------+
3 rows in set (0.00 sec)

mysql> 

I have since run it from the command line as well: 

-bash-3.2$ ./one
Status: 302 Moved
Location: /ec848de9

-bash-3.2$ 

Let's finish the initialization:

  $correct = 0; 
  $total = 0; 
  $questions = "Italy," . 
               "Australia," . 
               "United States," . 
               "South Africa," . 
               "Spain,China," . 
               "Russia," . 
               "Brazil"; 
  $key = ""; # there is no key in the beginning

And now we need a way to save the state in the session: 

$state->{'message'}   = $message; # state variables
$state->{'questions'} = $questions;
$state->{'anskey'}    = $anskey;   
$state->{'correct'}   = $correct;  
$state->{'total'}     = $total;    

&save_state($state, $session_id);

We also need to add a save_state function: 

# save the state in the database ------------------------------save_state---
sub save_state {
  my ($state, $id) = @_; 
  my $sth = $DBH->prepare(<<END) || die "prepare: ", $DBH->errstr;
UPDATE $DB_TABLE
  SET message=?,balance=?
  WHERE session_id='$id'
END
  $sth->execute(@{$state}{qw(message balance)})
    || die "execute: ", $DBH->errstr;
  $sth->finish; 
} 

This wouldn't work unless it's customized on two lines of its lines: 

  SET message=?,questions=?,total=?,correct=?,anskey=?

  $sth->execute(@{$state}{qw(message questions total correct anskey)})

So now let's delete the entries in the sessions table: 

mysql> delete from hwFive;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from hwFive;
Empty set (0.00 sec)

mysql> 

Let's access the program from the web: 

http://silo.cs.indiana.edu:11350/cgi-bin/1120/one

The URL becomes (upon receipt of the initial HTML form in the browser): 

http://silo.cs.indiana.edu:11350/cgi-bin/1120/one/87ff09ed

The form we receive appears unchanged (it really isn't) but the database has changed: 

mysql> select * from hwFive;
+------------+-------------------------------------+----------------------------------------------------------------------+---------+-------+--------+---------------------+
| session_id | message                             | questions                                                            | correct | total | anskey | modified            |
+------------+-------------------------------------+----------------------------------------------------------------------+---------+-------+--------+---------------------+
| 87ff09ed   | Welcome to the game, are you ready? | Italy,Australia,United States,South Africa,Spain,China,Russia,Brazil |       0 |     0 | NULL   | 2008-11-20 17:25:24 | 
+------------+-------------------------------------+----------------------------------------------------------------------+---------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> 

The hidden fields in the form are not the same any longer. 

What remains to be done? Just like in Homework Four, Two, finish the code. 

So we will just convert the PHP lines still to be added (if we were working on Homework Four) into Perl. <p> 

So next up these two lines are to be added (you know where): 

  $url = "http://www.cs.indiana.edu/classes/a202-dger/fall2005/notes/flagquiz/images";
  
  @names = ("Australia", "United States", "Russia", "Spain", "Italy", "South Africa", "Brazil", "China");

Then this line: 

  $questions = join(",", @names);

Then the grading part needs to be started: 

    if ($key) {
      if ($answer eq $key) {
        $correct += 1;
      } else {
    
      }
      $total += 1;
    } else {
     
    }

Then we prepare a new question:

  ($key, $questions) = split(",", $questions, 2); 

Then: 

  $message = "Very good."; 

Next: 

  $message = "No, that was not it."; 

Next:

  $message .= " Score currently: $correct out of $total."; 

Next:

  $question = "Whose country is this flag: <img src='$url/$key.png'> <input type=text name='answer'> <p>";    

Printing question:

  $question

End of game situation: 

  if ($questions) {
    # ... 
  } else {
    $question = "The game has ended, new game starting, are you ready? <p>";
    $correct = 0;
    $total = 0;
    $questions = join(",", @names);
    $key = ""; # there is no key in the beginning
  }

Then: 

  $message = "Welcome to a new game, score currently: $correct out of $total."; 

Shuffling will be addressed at the end of these notes. 

Getting the reset button in this program is achieved through: 

a) 

  Press <input type="submit" name="reset" value="Reset"> to reset the game.

b) 

  if ($message && ! $reset) {


c) and finally: 

  $reset = $q->param("reset"); 

End of notes. Throughout the conversion we tested. Here's our test program:

  #!/usr/bin/perl

  @names = ("United States", "France", "Spain", "South Africa", "Italy", "Russia", "Brazil");

  foreach $name (@names) {
    print $name, "\n";
  }

  $questions = join(",", @names);

  ($key, $questions) = split(",", $questions, 2);

  print qq{
    Ask about: $key <p>
    Keep ($questions) for later.
  };

Let's use this program to find a simple solution for shuffling:

http://perl.active-venture.com/pod/perlfaq4-dataarrays.html

So here's the solution:

  #!/usr/bin/perl

  use List::Util 'shuffle';

  @names = ("United States", "France", "Spain", "South Africa", "Italy", "Russia", "Brazil");

  foreach $name (@names) {
    print $name, "\n"; 
  }

  $questions = join(",", @names);

  ($key, $questions) = split(",", $questions, 2);

  print qq{
    Ask about: $key <p>
    Keep ($questions) for later.
  };

  @shuffled = shuffle(@names);

  print join("-", @names), "\n";
  print join("-", @shuffled), "\n";

Let's see this in action a bit: 

-bash-3.2$ ./one
United States
France
Spain
South Africa
Italy
Russia
Brazil

  Ask about: United States <p> 
  Keep (France,Spain,South Africa,Italy,Russia,Brazil) for later.
United States-France-Spain-South Africa-Italy-Russia-Brazil
Russia-Brazil-Italy-Spain-South Africa-France-United States
-bash-3.2$ ./one
United States
France
Spain
South Africa
Italy
Russia
Brazil

  Ask about: United States <p> 
  Keep (France,Spain,South Africa,Italy,Russia,Brazil) for later.
United States-France-Spain-South Africa-Italy-Russia-Brazil
Spain-Russia-South Africa-France-Brazil-Italy-United States
-bash-3.2$ ./one
United States
France
Spain
South Africa
Italy
Russia
Brazil

  Ask about: United States <p> 
  Keep (France,Spain,South Africa,Italy,Russia,Brazil) for later.
United States-France-Spain-South Africa-Italy-Russia-Brazil
Italy-Brazil-South Africa-United States-Russia-Spain-France
-bash-3.2$ 

So the last two lines prove that shuffling works well. 

--