There are two primary elements to the process of creating the environment that can be used to support the new interface: making the appropriate modifications to the actions called by the interface to support the initialization of state information, and implementing changes to the database structure to support the storage of that information. I am going to address the second item first, both because it is relatively straightforward and because having that structure available as I address state initialization will allow that discussion to proceed relatively seamlessly.
The role that will be played by the state table combines the functionality that was provided generally by hidden fields in the earlier incarnation of the entry process, with additional items that were stored in other locations, such as the game_id, previously stored in the user_char table. Further, many of the items I was passing around at that time to maintain state will be either superceded or rendered irrelevant by the state management system to be employed here. For example, the $pass variable that I used to create unique output file names will not be required under the new interface because I will be writing submitted records straight into the database, and the $inning_order variable will be superceded by the more sophisticated scheme to track the order of events that I will be employing under this model. (There will, however, be a very direct analog to this variable incorporated into the new structure, as I will discuss in due course.)
In any event, the initial structure of the table that I will use to track state is created by the following snippet from the db_revisions.script file, included in the samples file for this chapter. (To apply these changes to the database, pipe the script to the psql utility, as in "psql -h ralphzilla-raider -U baseball baseball < db_revisions.script".)
create table state ( session char(15) unique, game_id char(5), inning char(2), half_inning char(1), at_bat_key char(15), balls char(1), strikes char(1), first_event char(19), second_event char(19), third_event char(19));A bit of consideration of the role that this table will play should serve to explain much about the structure of the table. That is, the state table in the configuration that is evolving here will be used in a coordinative role, determining what will be drawn to the browser page in response to any given submission in concert with the previous state of the inning. As the submitting page is comprised of several quasi-independent forms, maintaining system state will be a more involved endeavor than it was in the simpler model of data entry used previously. The manner in which I intend to use this table will become evident in the chapters to come, but the curious reader should find a brief review of the table structure will provide a hint of what is to come. Of particular import at this point, however, is the storage of the game_id for the game being entered in this table, because this reflects the intended modification in the manner in which the system will track the state of an entry system. There will be some similarities to the manner in which the current edit function maintains state in that state will be tracked only so long as the server receives requests containing the session_id, and the path information contains the string associated with the entry function. (The edit function itself, of course, will be undergoing extensive revision after the revisions to the entry interface have been implemented.)
That being said, I am going to launch into a description of the initial steps I have made to create this environment. Those reading along will probably regard this as a relatively smooth transition, which was not really the case. <grin> It has been some time since I visited some of the material I have modified here, so I have had to take care not to do something to the wider environment that will would result in unanticipated consequences. Second, between the discussion of javascript in the previous chapters and other responsibilities (I have installed a passel of new machines in the past several weeks), I have been largely away from perl for a bit. When I started on this revisions I even found myself typing variable names without the leading dollar signs.
As has been my practice, I am going to put the basic framework that will support what I am going to do here in place, the gradually add functionality to that framework. Much of the early material in this process represents stuff that I have gone over in previous chapters, but a brief refresher will help to recreate a sense of the sequence of the actions on the server that will take place as submissions associated with this process are processed.
In this version I have re-enabled the game menu by commenting out the call to entry_tables() in the section of the league() subroutine that is executed if the path information in the request object includes the string "game", and uncommenting the call to the game() subroutine. When the "enter" option is selected from the game menu, the action associated with that selection includes both the string "game" and the string "entry" ("la-game-entry"), so execution on the server is directed into whatever I define within the game() subroutine to handle requests that include "entry" in the path information. In the previous entry model it was at this point that a session id was assigned if one was not present in the params hash, and so it will be in this version. It is also at this point, however, that the game selection process will come into play, unlike the previous version in which the game being entered was selected in user preferences. While the establishment of a session_id and the selection of a game might be considered to run hand in hand, it is easy to envision contexts in which the user might want to change the game being entered, or perhaps even use a paper record to enter a game fron another date. Therefore I have kept those two functions seperate, though in this preliminary implementation I call them sequentially.
##if the path information has the string "entry"
if ($path=~/entry/) {
##if the session id is not set, call get_session to define one, then prompt for a game selection
if (! $$params{'session_id'}) {
$session_id=get_session2($db);
$game_id=select_game($db,$colors,$r,$params,@select);
}
As will be clear shortly, this simplified form of state initialization will have to be modified before I can actually use it to kick into an entry session, but I wanted to work through getting a sessions id assigned and the form from which the game can be selected functioning before I started worrying about that.As the snippet above indicates, if the session id key does not exist in the hash of arrays referenced by $params, the subroutine get_session2() is called to create one.
##generate session id
sub get_session2 {
my ($db)=@_;
##scalars to be used in the subroutine
my $session_id;
my $session_chk=1;
##retrieve a reference to the relevant defined database statement handle
my $session_sth=$$db{'state'};
##while $session_test is not defined
while ($session_chk) {
##generate a candidate session id by using rand to generate a random number between 0 and 100,000, multiply it by
##10E15 and turning it into an integer
$session_id=int(rand(100000)*10000000000);
##execute the statement held in the referenced handle with that id
$$session_sth->execute($session_id);
##fetch a row (if there, there should be only one) into an array and evaluate that in scalar context to the $session_chk
##scalar. if that value is undefined, this is an acceptable session_id, because there are no records in the
##state table that correspond. since $session_chk is undefined, the loop will exit
$session_chk=$$session_sth->fetchrow_array;
}
##retrieve a reference to the statement handle that stores a session id into state
my $session_store_sth=$$db{'create_session'};
##store that session id and return its value
$$session_store_sth->execute($session_id);
return $session_id;
}
This subroutine is located near the beginning of BB_APP_INTERFACE, at line 266 of the version of the module included in the samples file for this chapter. (I named it get_session2() because I was not at the time willing to do away with the subroutine used previously ... just in case.) This version is essentially a straight extrapolation of the part of the old get_session(), which dealt with generating a session id from a context in which the session id's were stored in a text file, to one in which the id's are in a database table. I have defined two new statement handles in the main part of BB_STACKED, the first to select any rows that have a specified session id and the second to insert a record containing only a session id. (As the session row defined in the table structure above is declared as unique, the database server will prevent the addition of a second row with the same value in that column.) References to these handles are then stored in the hash of references to the database statement handles.
##statement handle for returning the information stored in the state table for a given session id
my $state_retrieve=$dbh->prepare("select * from state where session=?");
##statement handle for initiating a state record
my $new_session=$dbh->prepare("insert into state (session) values (?)");
...
state=>\$state_retrieve,
create_session=>\$new_session)
The structure of this subroutine is very similar to the earlier version of get_session() in the sense that a session id is generated, a check is made to make sure that it has not already been assigned, and if that check does not find a previously-assigned session id with the same value that value is stored and returned to the calling scope.
Unlike the subroutine used previously, however, this subroutine does nothing other than establish and store a viable session id which has to do with the fact that I am separating session id and game state initialization, as I discussed briefly above. The only argument passed into the subroutine is the reference to %db_handles. After initializing the scalars $session_id and $session_chk, I store the reference to the statement handle associated with the key "state" into the scalar $session_sth. The subroutine then enters a loop that executes while the $session_chk scalar holds a defined value, which is why I initialized that scalar to the value 1. This is a little different construct than I have used in previous similar circumstances, in that I do not specifically use an independent scalar that is used as a flag to control the loop, because the value stored in $session_chk can itself represent an indicator of what has been returned from the database query. Within the loop, a candidate session_id is created by using the rand() function to generate a random number between 0 and 100,000, with the result multiplied by 100,000,000,000,000 to remove any decimal point. (While the session id will be a character string, and a decimal point a viable character in such a string, it is possible that some operation along the way would have difficulty with that period, and it is a lot easier to remove the potential for the problem than to debug it when it happens.) Once the candidate session id has been generated, I fire off the statement referenced by the $session_sth scalar with the candidate $session_id to retrieve any record stored in the state table for that session id value. (As I mentioned above, there can be only one row with that value.) After executing the statement, the output of the statement handle's fetchrow_array method is assigned to $session_chk. As the output of that method is an array, this statement will store the number of elements in that array into $session_chk. In the unlikely event that that value is non-zero, that would indicate that there is a state table row with that value in the session column, and the loop would execute again. Conversely, if that value is undefined that would mean that there are no records with that value in the session column, and the loop would exit. This means that this is an acceptable session id, and in the last few lines of the subroutine a row with that value in the session column is created in the state table, and the value of $session_id is returned to the calling scope.Now that I had a structure in place to create a session id (I could verify that the session id value was getting created and stored by using the psql utility to query the state table), I launched into creating a dialogue for game selection. As one might imagine that the default data entry context would involve recording games as they are being played, it makes sense to have that dialogue display that date and a select box with the games scheduled for that date, but allow the date to be changed and the any game associated with that changed date to be selected. The initial version of this subroutine follows:
sub select_game {
##this subroutine displays a form allowing a date to be selected, with the current date as the default. based on that
##selection the games table will be queried and used to populate a select box allowing a game to be selected for entry purposes
my ($db,$colors,$r,$params,@select)=@_;
##establish current date
my $month=((localtime)[4]+1);
my $year=((localtime)[5]+1900);
my $day=(localtime)[3];
##create a hash of the numbers between 1 and 31, with leading 0's for values less than 10
##to keep everything justified. each slice should have the same string as both its key
##and its value. this hash will be fed to sel_box() to allow the day of the month to be selected
my ($i,$v);
my %days;
for ($i=1;$i <= 31;$i++) {
if ($i < 10) {$v='0'.$i;}
elsif ($i >= 10) {$v = $i;}
$days{$v}=$v;
}
##initialize hash for games on the pertinent date, and put the date in the right format for postgresql
my %games_for_day;
my $date=$year.'/'.$month.'/'.$day;
##retrieve reference to database handle, to be able to appropriately quote the value in $date for postgresql
my $handle=$$db{'dbh'};
$date=$$handle=>quote($date);
##retrieve the games for the selected date
my $date_sth=$$db{'games_on_day'};
my $date_result=$$date_sth->execute($date);
my $games=$$date_sth->fetchall_arrayref;
##$game_count will hold the number of rows returned from the executed statement
my $game_count=@$games;
##if the value of $game_count is non-zero, create a hash with the game description as the key and the game id as its associated
##value
if ($game_count) {
my ($t,$u);
foreach my $rec (@$games) {
($t,$u)=@$rec;
$games_for_day{$t}=$u;
}
}
##if the value of $game_count is 0, assign a message as the key with a blank associated value
elsif (!$game_count) {%games_for_day=("No Games for this day currently scheduled"=>" ");}
##beginning of html form
$$r->print("<form action='/bb_app/league/la-game-entry'>");
$$r->print("<center><table width='60%' border='1' $colors>");
$$r->print("<tr><td colspan='3'><center>Date</center></td><td><center>Game</center></td><tr>");
$$r->print("<tr><td><center>");
sel_box($select[5],$r,"month","","",$month);
$$r->print("</center></td><td><center>");
sel_box(\%days,$r,"day","","",$day);
$$r->print("</center></td><td><center>");
sel_box($select[4],$r,"year","","",$year);
$$r->print("</center></td><td><center>");
sel_box(\%games_for_day,$r,"game_id","","");
$$r->print("</center></td></tr></table><table><br><br>");
$$r->print("<table width='40%' $colors>");
$$r->print("<tr><td><center><input type='Submit' name='submit' value='Change_Date'></center></td>");
$$r->print("<td><center><input type='Submit' name='submit' value='Select_Highlighted_Game'></center></td></tr></table></form>")
}
In this version I simply use the system date to establish the relevant date.##establish current date my $month=((localtime)[4]+1); my $year=((localtime)[5]+1900); my $day=(localtime)[3];(Remember, as I have discussed before, the localtime() function returns the month in the range 0 through 11, and 1900 is year 0 for the year element.)
At this point I start to construct the data structures required to generate the page with the requisite select boxes. First, I construct a hash in which the keys and their associated values are each the same two-character integer string in the range 01 through 31.
my ($i,$v);
my %days;
for ($i=1;$i <= 31;$i++) {
if ($i < 10) {$v='0'.$i;}
elsif ($i >= 10) {$v = $i;}
$days{$v}=$v;
This hash, of course, will be used to allow the day of the month to be selected. (Some might ask why I do not adjust for the number of days in the pertinent month when constructing this hash. My response would be that there are times when it is appropriate to add the overhead that this determination would require, as in the calendar_month() subroutine in which I construct a representation of a standard calendar page for any given month. Here, however, I am simply creating a list from which the user will be selecting a value. In the worst case scenario in which a user selected an invalid date such as June 31 (30 days has September, April, June and November ...), the database query would simply return an empty result set, and the user could select another value to remedy their error.) The snippet constructing the hash is itself fairly straightforward. After initializing scalars for the counter variable ($i) and the value ($v), and initializing the hash to which the slices will be stored, the subroutine enters a loop that executes for values of $i from 1 through 31. Within that loop, the value assigned to $v is constructed as a concantenation of 0 with $i if $i is less than 10, or $i alone if $i is greate than or equal to 10. A hash slice is then create with both the key and value being assigned the value of $v.Now I begin to retrieve the set of games scheduled for the target date. After initializing the hash into which the game description and id will be stored, I create a scalar that holds the pertinent date values in a format that postgresql will support.
my %games_for_day; my $date=$year.'/'.$month.'/'.$day;While I now have the date in the right format, I have to get the value quoted in a form amenable to postgresql. The quote() method, however, is associated with the database, and that's back in the main namespace from which this subroutine was called. While there are other ways of dealing with this, I adopted what I considered a well-compartmentalized approach, adding a reference to the database handle to %db_handles, a reference to which I of course routinely pass around between subroutines. After adding this line to the hash creation statements
dbh=>\$dbh,I can readily point to the database handle in any subroutine to which I pass $db. And that is exactly how I get the value of $date quoted.
my $handle=$$db{'dbh'};
$date=$$handle->quote($date);
With $date appropriately quoted, I can ge the games schedule for that date. To that end, I define a new statement handle in BB_STACKED
my $games_day=$dbh->prepare("select to_char(time,'HH24:MI am')|| ' ' ||\
away_team_name ||' '|| 'at' ||\
' '|| home_team_name,game_id from game where date=?");
and add a reference to it in %db_handles
games_on_day=>\$games_day,
Recognize that what I am doing here is creating a result set in which each returned row contains two elements for use in constructing a hash that I can pass to sel_box(). As in other similar circumstances, one of these elements is at some level descriptive, while the other represents the code for the item described. The relatively complex expression that is the first element in the select expression constructs a long string comprised of a twenty four hour clock representation of the value in the time column, concantenated with a space, the away team name, another space, the string "at", another space, and the home team name. For those who wonder why I use a twenty four hour clock to represent the time in this expression, recognize that the hash I am creating is going to be sent to sel_box(), which sorts the submitted hash by its keys as the select box is printed. As a result of the sort, games starting in the afternoon would be listed before games starting in the morning if the twelve hour clock were used. For example, 03:30 p.m. would be listed before 11:30 a.m. Believe me, I know ... <grin>With the statement handle in place, I can use it to retrieve the set of games scheduled for the target date.
my $date_sth=$$db{'games_on_day'};
$$date_sth->execute($date);
I then use the fetchall_array ref method to return an array of references to arrays holding the rows of the result set. If I assign that array to a scalar, thus evaluating it in scalar context, I will know the number of rows in the result set.my $games=$$date_sth->fetchall_arrayref; ##$game_count will hold the number of rows returned from the executed statement my $game_count=@$games;Now I have the information I need to build the hash appropriately. As I have already retrieved the result set it did not make sense to me to do that twice, so I decided not to use make_hash() and simply construct the hash here. Therefore, if there are rows in the result set, I construct the hash from the structure referenced by $games.
if ($game_count) {
my ($t,$u);
foreach my $rec (@$games) {
($t,$u)=@$rec;
$games_for_day{$t}=$u;
}
}
In the snippet above each of the elements pulled from the @$games array is itself a reference to an array, so that reference has to be dereferenced before the elements in the array can be accessed.On the other hand, if the statement has returned an empty result set, I want to create a hash that includes an appropriate message.
elsif (!$game_count) {%games_for_day=("No Games for this day currently scheduled"=>" ");}
The remainder of the subroutine prints an html form back to the browser.
##beginning of html form
$$r->print("<form action='/bb_app/league/la-game-entry'>");
$$r->print("<center><table width='60%' border='1' $colors>");
$$r->print("<tr><td colspan='3'><center>Date</center></td><td><center>Game</center></td><tr>");
$$r->print("<tr><td><center>");
sel_box($select[5],$r,"month","","",$month);
$$r->print("</center></td><td><center>");
sel_box(\%days,$r,"day","","",$day);
$$r->print("</center></td><td><center>");
sel_box($select[4],$r,"year","","",$year);
$$r->print("</center></td><td><center>");
sel_box(\%games_for_day,$r,"game_id","","");
$$r->print("</center></td></tr></table><table><br><br>");
$$r->print("<table width='40%' $colors>");
$$r->print("<tr><td><center><input type='Submit' name='submit' value='Change_Date'></center></td>");
$$r->print("<td><center><input type='Submit' name='submit' value='Select_Highlighted_Game'></center></td></tr></table></form>")
For the most part this is much the same as any number of forms that I have constructed before. The only aspect of this form that is a little bit unusual is the colspan specification in the first element of the first row of the table included in the form. This row serves as the header for the two-row table within which the select boxes are printed, so in effect I am indicating here that the three columns immediately under that element are associated with the heading "Date". The result looks like this:While I have submit buttons on the form here, in the current state of the application there is nothing that will process any submissions from the form. While game selection is the driving rationale for the dialogue, at this point my primary concern is getting the data selection mecahnism to work, simply because that could determine whether the appropriate game would be available for selection in the first place. I also have to do something to get the generated session id back to the browser, because as the application stands now each time one of the submit buttons is clicked the same sequence the server just executed will be initiated, with a new session id being created and stored, and the same form being generated.
Passing the session id between the browser and the server must be addressed first, because that data element represents a controlling factor in the application's operation. While I have done this several times before, I am going to restate the process, in no small part because it has been a few chapters since I last discussed this aspect of communication between progressive submissions to the server. Specifically, once the session id is set, I have to return the session id in a hidden field in any html form that is part of the sequence for which I wish to maintain state. (Actually, that is not quite true. I could store the session id in a cookie on the local machine, set a reasonable expiration period, and read the value from there to send to the server. I have not yet used cookies, largely because many users routinely block cookies in the belief that doing so will protect personal privacy. There is nothing inherent in a cookie that is a privacy concern, but at the same time I do not allow banking sites to store login information on my machine <grin>. Since I have been able to get by to this point without using them, I will continue to do so.) After adding this line
$$r->print("<input type='hidden' name='session_id' value-$session_id>");
to the game selection html form, which is the only form currently being returned to the browser under this specific URI, that piece of information will be available in the request object when the form is submitted.In and of itself, of course, that does not do all that much for me, because I still have to flesh out the logic, promarily in BB_STACKED, that will trigger appropriate actions based on the contents of the request object. First, of course, I want to get the game selection dialogue returned when appropriate. I modified the initial part of the code block in BB_STACKED that executes if the path information includes the string "entry" as follows:
if ($path=~/entry/) {
##if the session id is not set, call get_session to define one
if (! $$params{'session_id'}) {
$session_id=get_session2($db);
}
##if the session id is set
elsif ($$params{'session_id'}) {
##first, store the session id to $session_id
$session_id=$$params{'session_id'}[0];
}
##if the game_id is not set or if the target date has been changed, call select_game() to get a game_id
if ((! $$params{'game_id'}) or ($$params{'Submit'}[0]=~/Date/)) {
$game_id=select_game($db,$colors,$r,$params,$session_id,@select);
}
In this version, if the hash referenced by $params contains a key containing the string "session_id" the value in the first element of the array associated with that key is stored to the scalar $session_id. I then call select_game() if the hash referenced by $params does not have a slice keyed to "game_id" or the value of the submit parameter includes the string "Date", which would indicate that the submit button displaying "Change_Date" had been pressed. I think it appropriate at this point to step back and think just a bit about the direction that this is going. As the characteristics of the entry process associated with a given session is will be stored in the state table, the presence of a game_id key in the hash of parameter values will indicate that a game_id has been selected. Unlike previous similar contexts, I am not going to be passing around the game_id as a hidden field. As I am going to be relying on the state record for overall coordination of the forms on the entry page, I might just as well rely on it for the game_id as well. Ultimately, therefore, the portion of this block following the initialization/reading of the $session_id will implement a structure something like this:All that is all well and good, but I need to put into place the requisite code to store and retrieve the state information. Before I do that, however, I want to get the dialogue working that will allow me to select a game. For that reason, the snippet above makes no mention of the state information, and simply relies on the presence or absence of the game_id parameter, which serves as a proxy for the state information in this intermediate step. Therefore, here the select_game() subroutine will be executed if there is no game_id parameter in the request object or if the value of the submit parameter includes the string "Date". (The full value in this context would of course be "Change_Date".) Remember also that the structure referenced by $params (%params) is a hash of arrays. Thus when I test for the presence of a game_id parameter ($$params{'game_id'}) I am actually testing for the presences of a hash key named 'game_id'. While this does test for the presence of the parameter, if I want to access the value(s) associated with that key I have to access the array. Since all of the parameters involved with the html forms that create the submissions being processed here are single-valued, the actual value is stored in the first element of the pertinent array, as in $$params{'Submit'}[0] or $$params{'session_id'}[0]. In this interim version, the entry_tables() subroutine is executed if the game_id parameter exists and the submit parameter does not contain the string "Date". Again, this is primarily for testing purposes. (The version of the application included in the samples file is the final version developed in this chapter. Should the reader wish to implement any of these intermediate versions, it would of course be necessary to replace the relevant subroutines in the included modules with the intermediate code listed here.)
In any event, with this structure in place the sequence of pages generated when the entry option is selected is appropriate, and once a game is selected the entry page I developed in previous chapters is displayed, although that page is not yet functional with the information specific to the selected game. I will get to that in upcoming chapters, but first I want to set up the initial interaction with the state table that I described above.
if ($path=~/entry/) {
##if the game_id parameter is set, that means that the submission is coming from
##a game selection screen,so the initial state information can be stored.
##however, to protect against a user inadvertently highlighting a game
##when they mean to select another date,
##i should make sure that the user has not clicked the "Change_Date" submit button
if (($$params{'game_id'}) and ($$params{'submit'}[0] !~ /Date/)) {
store_state("A",$params,$db);
}
##if the session id is not set, call get_session to define one
if (! $$params{'session_id'}) {
$session_id=get_session2($db);
}
##if the session id is set
elsif ($$params{'session_id'}) {
##first, store the session id to $session_id
$session_id=$$params{'session_id'}[0];
$state=get_state($session_id,$db);
##if the game_id is not set or if the target date has been changed,
## call select_game() to get a game_id
if ((! $$state{'game_id'}) or ($$params{'submit'}[0]=~/Date/)) {
select_game($db,$colors,$r,$params,$session_id,@select);
}
elsif (($$state{'game_id'}) and ($$params{'submit'}[0] !~ /Date/)) {
entry_tables($r,$params,$colors,$db,$state,$session_id,@select);
}
}
}
In this next evolutionary step, the first thing done in the subject block in BB_STACKED is to check for the presence of a game_id parameter, because the only way that the game_id parameter could come to be present is as the result of the submittal of a page generated by select_game(). Therefore, if the submit parameter value does not include the string "Date", the value held in the game_id parameter must represent the id of a game intentionally selected. (I could, of course, as easily base this specific condition on matching a portion of the string "Select_Highlighted_Game", rather than not matching "Date". In some ways that would be considered preferable, and if I should at some point add a third select button to the form generated by select_game() that change would be necessary. I have stayed with the current statement to keep the logic associated with a changed target date operating on the same dimension.) As the session_id must have been assigned as a previous request was processed for the select_game() subroutine to have generated the page that would have submitted this request, I do not have to explicitly check for its presence in this statement.
if (($$params{'game_id'}) and ($$params{'submit'}[0] !~ /Date/)) {
store_state("A",$params,$db);
}
sub store_state {
my ($context,$source,$db)=@_;
my ($game_id,$inning,$half_inning,$at_bat,$balls,$strikes,$first_event,$second_event,$third_event,$session_id);
my $sth=$$db{'update_state'};
if ($context =~ /A/) {
$game_id=$$source{'game_id'}[0];
$inning='';
$half_inning='';
$at_bat='';
$balls='';
$strikes='';
$first_event='';
$second_event='';
$third_event='';
$session_id=$$source{'session_id'}[0];
}
$$sth->execute($game_id,$session_id);
}
It is quite possible that this subroutine will grow fancier as time goes on, but at this point I am primarily concerned with putting into place something that works. After reading the passed arguments, I initialize a set of scalars to hold the values that will be modified in the table. (Remember, the record for a given session_id is added to the table after the session_id is generated in get_session2(). From that point, as long as the session_id is present in the $params hash, I will just be modifying the values stored in that table to reflect the current state of the session. If at some point I decide that it is warranted to provide a roll back capability, I will likely copy any existing values when i modify the state table into a historical table that would represent a log of session activity. I will think about that once I get the whole interface working as I desire.) After initializing the set of scalars, I retrieve the reference to the relevant statement handle from the hash of statement handle references referenced by $db. The statement retrieved here has been added to the main section of BB_STACKED
my $update_state=$dbh->prepare("update state set game_id=? where session=?");
and the afore-mentioned reference to it to %db_handlesupdate_state=>\$update_stateThis is a very much simplified version of the full statement that will ultimately be used
my $update_state=$dbh->prepare("update state set game_id=?,inning=?,half_inning=?,at_bat=?,balls=?,\
strikes=?,first_event=?,second_event=?,third_event=? where session=?");
because I wanted to ge the process flowing as appropriate before I started worrying about trapping malfunctioning database statements precipitated by things like the vagaries of quoting values that are used in the statement.The next part of store_state() populates the values that will be added to the session record. At this point, many of these values are not available, but will be shortly.
if ($context =~ /A/) {
$game_id=$$source{'game_id'}[0];
$inning='';
$half_inning='';
$at_bat='';
$balls='';
$strikes='';
$first_event='';
$second_event='';
$third_event='';
$session_id=$$source{'session_id'}[0];
}
For the purposes of this initial implementation, the only values I am concerned with are the session_id and the game_id, because it is the ability to retrieve a game_id associated with a given session_id that determines whether the game selection of the entry page will be generated, as will be clear shortly. The statement held in the referenced statement handle is then executed with the pertinent values.$$sth->execute($game_id,$session_id);Note that I could have executed the statement by specifying the first element of the array keyed to the relevant value in the $params hash rather than taking the intermediate step of storing those values to scalars. I put that step into place to readily enable running the data elements through the $dbh->quote() method as I diagnosed a glitch that popped up as I developed this sequence. While that glitch turned out to be unrelated to quoting, I have left that step in as the primary vehicle for translating data elements stored in disparate formats into the set of scalars to be used in the execution of the database statement. There are of course other ways of doing that, but at least for now this is the one I am going to use. I will describe the glitch I ran into, and its resolution, after I finish this discussion of the sequence leading up to the execution of entry_page().
Back at the level of BB_STACKED, it is at this point that I check for the presence of a session_id parameter in the request object, and create a session_id if one is not present. On one level, as the game_id check just described and this if() statement are mutually exclusive, in that only one will be pertinent for any given submission, I could put either one of them first in the sequence without causing any repercussions for the page being generated. However, the elsif() statement following the if(! $$params{'session_id'}) statement
elsif ($$params{'session_id'}) {
##first, store the session id to $session_id
$session_id=$$params{'session_id'}[0];
$state=get_state($session_id,$db);
}
presumes that if the game_id was present in the $params hash, the user having just submitted a form a which a game was selected, that value has been stored in the state table. Therefore, the if() statement associated with the game_id construct must go first.Within the small block within the elsif() condition, I store the session_id parameter value to a scalar and pass that scalar and a reference to %db_handles to the new get_state() subroutine, assigning what is returned from the subroutine to the scalar $state. The get_state() subroutine
sub get_state {
my ($session_id,$db)=@_;
my $sth=$$db{'state'};
$$sth->execute($session_id);
my $state=$$sth->fetchrow_hashref;
return $state;
}
is deceptively simple, reading in the passed arguments and then retrieving the reference to the database statement handle keyed to the string "state".
After executing the referenced statement handle, I use the fetchrow_hashref method on the result set and assign the hash reference to $state. This method returns a reference to a hash in which a single row from the table is stored in the hash with the column name as the key and the value in the subject row as the value. Here, for example, the game_id value can be accessed as $$state{'game_id'}. I know that fetching a single row from the state table will retrieve the relevant state information for this session because the session column in the state table is defined as unique, thus preventing the addition of a record with the same value in that column. Looking ahead just a bit, one of the things I am going to have to address is the timing of modifications to the state record occassioned by the contents of any given submission, because this little block will retrieve the state information for the session pertinent at the end of the processing of the last submission, with the exception of the initial sequence in which a game id has just been stored in the table. Just a little thought for the discerning reader to chew over as the narrative continues.<grin>.Back at the level of BB_STACKED, I can now use the presence or absence of a game_id value in the hash referenced by $state to determine whether the game_select() or entry_tables() subroutine should be executed.
if ((! $$state{'game_id'}) or ($$params{'submit'}[0]=~/Date/)) {
select_game($db,$colors,$r,$params,$session_id,@select);
}
elsif (($$state{'game_id'}) and ($$params{'submit'}[0] !~ /Date/)) {
entry_tables($r,$params,$colors,$db,$state,$session_id,@select);
}
With this structure in place, the sequence of generated pages flows appropriately. The glitch I mentioned earlier occurred after I had the structure largely in place, but was unable to get entry_tables() to execute. After poking around at several things I viewed the source generated to display the game selection page and noted that the value for the session_id hidden field was blank. At that point I started backtracking, printing the value of $session_id to the browser, and discovered that it was blank everywhere. The session id was getting created in get_session2(), however, because a legitimate value was being stored in the state table. Ultimately, I discovered that the problem was in the line that returned the generated session id to the calling location.return $session_id;I replaced $session_id in this statement with a literal value and that value was returned appropriately. I then pasted the string "$session_id" back into its original location and it worked appropriately! All I can think is that I must have had a subtle typo somewhere in the scalar name that I was simply not seeing.The moral of this little episode, I guess, is that one should not overlook even the most trivial things when tracking a bug, because their sources are frequently things just like this. On another level the episode illustrates the rationale for implementing new functionality in an incremental (piece by piece) fashion. I jumped a little further than I prefer as I dropped the initial state management components into this sequence, in the sense that I included both state storage and retrieval, but for the most part those components are all comprised of things that I have done before. Testing as one develops simplifies the task of tracking down bugs by limiting the number of modifications that have to be evaluated as the potenital problem source. I was, of course, readily able to test the output of the fetchrow_hashref method by assigning the value held in the session column of an existing state record to $session_id, storing a value in the game_id column of that table, and printing the value associated with the key "game_id" back to the browser ($$r->print($$state{'game_id'});).
I also ran into an interesting problem as I wrote the paragraphs describing the calls to select_game() and entry_tables(), above. As I write this narrative I frequently spot a location in which I can trim a statement or two, or envision an alternative formulation of some statement. In this specific circumstance it occurred to me that I could trim the statement in which I store the session_id to a scalar, and just pass $$params{'session_id'}[0] to get_state(), select_game(), and entry_tables() in its place. What I discovered was that while this worked fine for get_state() and entry_tables(), it consistently resulted in select_game() having a blank value for $session_id. There is nothing in the nature of the argument array passed to select_game() that would account for this, as is illustrated by the fact that if there were it would similarly affect the context in which I pass the argument as a scalar. It is quite possible that a different version of the perl interpreter would not be subject to this behavior, but as I have a workable alternative I did not wish to invest the time in a deeper investigation. The reader should, however, be aware that in any specific system configuration there can be little traps like this.
Anyway, now that the sequence is in place I still have a couple of aspects of the environment for entry_tables() that I want to put into place, but as this already a very long chapter I think I will handle those in the next chapter.