| Note: | In the code included in the samples for the previous chapter, and in its description, I left out a condition early in the select_game() subroutine, the absence of which would prevent the current date from being highlighted appropriately on the game selection page. That is, to make sure that the default value sent to the sel_box() subroutine matches the appropriate value in the constructed select box I prepend a "0" to any day value that is less than 10. In that version, however, I left off the if() condition that checks to make sure that the day value is less than 10 before executing the prepend operation. This would prevent the appropriate day from being displayed whenever the current day is later than the ninth of the month, because sel_box() would not match, for example, "020" with "20". By replacing the line $day='0'.$day;with $day='0'.$day if $day < 10;in the last line of the elsif() statement that executes if the %params hash has no "month" key near the beginning of select_game(), the prepend operation executes appropriately, and the appropriate value is highlighted in the generated page. |
When I began the previous chapter, I had intended to put into place the structure that would result in the initialization of a session id and attendant state record, allow a game to be selected, and from there just launch into the entry pages. At the end of the last chapter, there were still a couple of relatively minor details to be worked out to implement that scenario. Those readers who actually put the modules in the samples file for that chapter into a running server may have noticed some of those details, because I had added a couple of select boxes to allow the selection of the starting inning and half-inning when desired, then forgot to take them out when I decided to split this discussion into two chapters.
That decision was precipitated by the desireability of the addition of a dialogue that would allow the batting order and position assignment of the individuals participating in a game to be established. To a certain extent, this is not necessary. The only point in the entry process at which an individual will have to be identified is during the initialization of an at bat, so selecting the appropriate name from an alphabetic listing of perhaps ten to fifteen names would not represent a difficult task. In that sense, adding the batting order / position assignment page could be regarded more as an enhancement rather than a requirement, especially as once I put it in I will have to develop some manner in which to maintain it, given the inevitability of substitutions and pinch hitters. On the other hand, addition of the ability to track the players participating in the game at any given point will add substantially to the narrative capability of the system from a defensive perspective, because it will be readily possible to identify who was at a given position when any given play happened. Done right, this addition will also provide some capability to generate limited defensive statistics, though there will be events that will not be captured from that perspective. I mentioned that several chapters ago, but it bears repeating. A good example of such an event might be a play requiring a relay throw, when an outfielder throws to an infielder who relays it to the destination player. The current system will not capture that amount of detail from a defensive perspective. Further, given that it is assumed that a single individual will be entering records, it is questionable whether the interface should attempt to capture that level of detail. While baseball is very episodic, there can be many individual events associated with a given episode, and it would be unreasonable to assume that a single individual could be expected to reliably remember every single detail of complex plays. As I mentioned earlier, if I should desire to capture that level of defensive information I would want to put into place a series of tables constructed with that perspective in mind. Before the readers gets the impression that I am leaving something out, however, it is important to recognize that the new database structure and entry interface will be capturing everything that traditional scoring methods have captured, and more.
The addition of this element to entry page initialization will, however, neccesitate yet a third chapter in this particular saga, because after I finished the process I am beginning to describe I started to look at storing the modified elements and realized that this would be the appropriate time to generalize the subroutine that stores records modified by pages generated through the use of edit_table(), now named edit_schedule_proc(). As this will involve some modification to parts of the system that are already in place, I think it appropriate to address that on its own. Therefore, this chapter will end with the construction of a page that holds appropriately-populated forms.
Now that I have spun through that digression, I will go about including the ability to specify a starting inning/half-inning combination when the game is selected. In so doing I am going to be implicitly specifying that a system requirement will be that an entry session must begin at the beginning of a half-inning. Given the information that will be stored in the state table, it should be possible to implement a manner in which to pick up the entry of a game in mid-stream, so to speak. While possible, however, the task of appropriately designing the interface and constructing an appropriate storage mechanism to support that is significant enough that I do not want to wander down another side road just yet. This might be a good subject for an enhancement chapter at a later date.
Similarly, quite some time ago I discussed the possibility that a league might desire to allow simulataneous entry of the same game by several users as a verification measure. The current system does not prevent a second user fromn starting an entry session for a game for which the results are already being entered, but provides no manner in which to distinquish between which session is associated with which user. Indeed, it would be difficult to predict just which submitted values would get entered as records, because the unique key constraint on the tables in which the data would be stored would allow only the first data element with any given key to be stored. Obviously, I am going to have to make some table structure modifications before simultaneous entry can be implemented, but even more obviously I am going to have to implement some measure to block users from initiating a second entry session for a game for which an entry session is on-going, at least until the point at which I decide to make the changes that would allow simultaneous entry. I will add that block as I incorporate the inning/half-inning specification.
For the most part, incorporation of inning and half-inning select boxes involves little beyond what I have done many times before. Indeed, I literally copied the code that prints the select boxes from the get_form() subroutine used in the previous entry scenario.
$$r->print('<center>Starting Inning');
$$r->print("<select name='inning' size='1'>");
my $value='01';
while ($value lt '20') {
$$r->print("<option>$value</option>");
$value++;
}
$$r->print('</select> Half-Inning');
my @half_inning=("A","Top","B","Bottom");
$$r->print("<select name='half_inning' size='1'>");
my $k=0;
foreach my $half_inning(@half_inning) {
$$r->print("$half_inning</option>") if ($k % 2);
$$r->print("<option value=$half_inning> ") if (! ($k % 2));
$k++;
}
Here, however, I decided that I did not want to use the descriptions "Top" and "Bottom" to be stored as the value for the half-inning in the state table, but rather "A" for the top half of the inning and "B" for the bottom half. As much as anything, I made this choice to facilitate the sorting of records by half-inning. As is evident from the code, I store the values and options associated with each select box in an array, then step through the elements in the array, using the modulus operator to determine whether the counter scalar ($k) that tracks the position of the current element in the array is divisible by two. As I defined the array holding values and descriptions for the half-inning select box as holding values in the even-numbered elements, and descriptions in the odd-numbered, if the modulus operation fails the perl truth test with a returned value of 0, it must hold a value, and if it passes (the was a remainder from the division) the element must hold a description. Therefore, what is printed to the browser is determined by the results of that operation. Note that in this context the order of the elements in the array is critical, because the first part of any option specification must obviously be printed before the second part. Some may wonder why I did not simply print the select box explicitly, as it has only two elements. They would have a valid point. <grin>.On this page, I extended the table that holds the data and game select boxes, specifying that the first element of the added row span three columns.
$$r->print("<tr><td colspan='3'></td><td>");
The two added select boxes will now be included under the game selection column in the generated table. This produces a table that looks like this:
$$r->print("<tr><td colspan='3'><center> </center></td><td>")
which produces a table that looks like this:
Now all that remains for the inning/half-inning selection is to store the inning and half_inning values into the state table at the same point that the game id is stored there. This simply involves adding those two elements to the preparation of the database statement handle in BB_STACKED and to the execution of the statement handle in the store_state() subroutine in BB_APP_INTERFACE. Rather than having to do this every time I add something to the list of data elements, I just dropped in everything that I currently think will be in the state record
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=?");
and$$sth->execute($game_id,$inning,$half_inning,$at_bat,$balls,$strikes,$first_event,$second_event,$third_event,$session_id);
The most straightforward way to go about making sure that two simultaneous data entry sessions are not started for the same game is to check the state table to make sure that a record containing the selected game_id is not present. There are wrinkles associated with this that I will go into shortly; at this point I am just going to add the logic required to make sure that a second entry session is not initiated.
Developing a mechanism through which the state table can be checked for a record for that game is, of course, no big deal. First, I prepared the appropriate database statement handle
my $game_check=$dbh->prepare("select * from state where game_id=?");
game_check=>\$game_check
sub game_check {
my ($params,$db)=@_;
my @game_check;
my $sth=$$db{'game_check'};
$$sth->execute($$params{'game_id'}[0]);
@game_check=$$sth->fetchrow_array;
return @game_check;
}
Note that in this subroutine I am returning an array to the calling context. From the standpoint of accessing individual components of the state record this structure will not be as useful as the hashref I use to hold session state information, but my primary purpose here is only to determine the presence or absence of a record associated with a given game. I am returning an array of all of the values in the record simply to broaden the applicability of the subroutine slightly just in case I find it useful in another context. In the unlikely event that I decide to access the elements held in this structure more than incidentally, I will re-evaluate.The tricky part of checking for a second entry session for a given game lies in the manner in which I will incorporate it into the series of conditions I added to BB_STACKED in the last chapter. I did not want to make substantial modification to that structure, so I decided to implement a flag that can be used to appropriately control execution within that structure. This type of strategy is particularly pertinent here, because if I decide to create the underlying structure that will enable simultaneous entry sessions I will be also creating an administrative setting that will allow that functionality to be turned off or on, depending on the policies of the league. That setting would then be used to override the operation of this flag.
$my $game_chk;
if (($$params{'game_id'}) and ($$params{'submit'}[0] !~ /Date/)) {
$game_chk=game_check($params,$db);
store_state("A",$params,$db) if ! $game_chk;
$$r->print("<center><table><tr><td><font color='red'><center>An entry session for this game has already been started.\
<br>Select another game<br><br></center></font></td></tr></table></center>") if $game_chk;
}
Within the block that executes when the request object has a parameter named 'game_id" and the value of the 'submit' parameter does not include the string 'Date', I assign to a scalar named $game_chk the array returned from the game_check() subroutine. $game_chk will thus hold the number of elements in the array returned by the fetchrow_array method invoked in game_check(). If that number is positive, there is a record in the state table with that game_id, and $game_chk will pass a perl truth test. Conversely, if there is no record with a matching game_id, $game_chk will hold the value 0, which will fail a perl truth test. As I want the rest of the entry session initialization steps to take place only if the there is no such record in the state table, in the next line the store_state() subroutine will be executed only if $game_chk fails the truth test. If, however, $game_chk passes that test, in the next line a message is printed back to the browser explaining that an entry session for that game has already been started and suggesting the choice of a different game. This text is, of course, entirely arbitrary. While it may be difficult to see $game_chk as a flag in this specific implementation, if I decide to enable simultaneous sessions for a given game contingent upon a system setting all I will have to do is establish the value of $game_chk conditionally, dependent on the value of that setting.The steps detailed above are sufficient to cause the interface to behave as I desire. As I have prevented the insertion of a game_id value into the state table record associated with the session_id if there is already a state record associated with that game, the get_state() subroutine will return a hash with an empty value keyed to 'game_id', which will of course fail the $$state{'game_id'} test upon which entry to the entry_tables() subroutine is predicated. Therefore, the date/game selection select boxes will be drawn immediately below the text message, resulting in a page that looks like this
Unfortunately, there is no straightforward manner by which to know that a given session has been closed in a browser-based application that does not employ specialized libraries to link the client session with the server. From the standpoint of the server, the browser simply stops sending requests. In this application, for example, the browser can stop sending request at any point, and the information in the state table for this data entry session would be simply orphaned, with no other user able to start an entry session for the game to which the abandoned session was associated. While I will at some point incorporate a method through which a user might appropriately close a data entry session, I have to design around the assumption that a user may simply close the browser. It is therefore important to incorporate some measure through which the state table can be cleansed of any such stale records as a fall-back measure.
As I thought about this, it seemed to me that the best way to handle this is to set up a scheduled job that will periodically delete all records in the state table that are older than a certain value, perhaps an hour or so, to catch any records that might not be caught by any other clearence mechanism. (At some point, for example, I will likely incorporate a supervisory function to clear the state record for a given game, because it seems likely that a standard problem scenario would be one in which a user, inadvertently or otherwise, closed the client browser without properly terminating the data entry session. If the entry session had been recording an on-going game, this would be an immediate concern; hence the need for that capability.) At some point, I will also have to incorporate some mechanism to flag a session's state record as in effect paused, because rain delays can frequently take longer than the default hour-long period that defines the record as stale.
As with most database servers, postgresql implements the timestamp column type that can be used in such circumstances. This column type stores both date and time, and is thus useful in situations like this in which an action is going to be based on the amount of time that has passed since the record was modified. While a simple time column might prove sufficient 99% of the time, late starting games might easily roll over into the next day, making the requisite calaculation much more problematic.
Successful implementation of this strategy will obviously require that I record the date and time whenever I modify the row in the state table associated with a given session_id. The table creation statement in the bbdb_create.script file included in this chapter's samples file creates the table with the column in place, so typing "./bbdb_recreate.sh" from the directory into which the sample files have been extracted will recreate the tables in the database with all current changes in table structures, as long as the database server host name (ralphzilla-raider) is replaced with the value appropriate for the local installation. Alternatively, of course, the modifications can be made by hand in the psql utility, or the section of the file pertaining to the state table can be extracted and run on its own. (As the tables hold nothing but test data during these iterations, I have made no attempt to write off the contents of the tables for importation into the modified structure. I will do that as I get closer to a point at which the database holds "real" data.) With those changes in place, all I had to do was add the column name to the appropriate database statements
my $new_session=$dbh->prepare("insert into state (session,time) values (?,?)");
, create a string holding the timestamp string, and add the scalar to the list of values submitted as the statement handle is executed. As an example, the last few lines in get_session2(), in which the newly-created session_id is inserted as a new row in the state table, now look like this:
my $update_state=$dbh->prepare("update state set game_id=?,inning=?,half_inning=?,at_bat_key=?,balls=?,strikes=?,\ first_event=?,second_event=?,third_event=?,time=? where session=?");
my $time=((localtime)[5]+1900).'/'.((localtime)[4]+1).'/'.(localtime)[3].' '.(localtime)[2].':'.(localtime)[1].':'.(localtime)[0]; $time=$$dbh->quote($time); ##store that session id and return its value $$session_store_sth->execute($session_id,$time);Note that in store_state(), the two lines that define the timestamp value and quote it are outside the context-based if() block, because that value will be constructed in this manner regardless of the context in which the subroutine is called.
Now that the state record for any given entry session will be time stamped whenever it is updated, I can write the script to delete stale records. This is simple in concept, and only slightly more complex in implementation. Given that I have (arbitrarily) defined an age of one hour or more as representing a stale state record, all I have to do is delete all records in the table for which the value of the stored timestamp plus one hour is less than the current time. (There are, of course, other ways to formulate this. I will leave their derivation as homework for the enterprising reader.) My initial implementation of the sql statement that is used by the script made use of the postgresql reltime datatype to interpret the string "1 hour", and looked like this:
delete from state where (time + ('1 hour'::reltime)) < $time
delete from state where (age($time,time) > '1 hour')
The initial version of this script follows:
#! /usr/bin/perl -w
use DBI;
use DBD::Pg;
my $db_user="baseball";
my $db_pass="baseball";
my $dbh=DBI->connect("DBI:Pg:dbname=baseball;host=ralphzilla-raider",$db_user,$db_pass) or die "cannot connect to database";
my $time=((localtime)[5]+1900).'/'.((localtime)[4]+1).'/'.(localtime)[3].' '.(localtime)[2].':'.(localtime)[1].':'.(localtime)[0];
$time=$dbh->quote($time);
$dbh->do("delete from state where (age($time,time) > '1 hour')");
$dbh->disconnect;
exit;
As the script will be running independently from the web server, it is necessary to initialize the connection to the database within the script. Note also that I explicitly disconnect from the database server at the end of the script, as should always be done but as is also frequently omitted. In this circumstance, such proper housekeeping will help to reduce the overhead on the database server by avoiding the creation of orphaned connections from previous script executions.As the timestamp value stored in the state record is generated by the machine running the web server, my initial implementation will run this script on that same machine, as a scheduled job. In that way I can make sure that the comparison is made on the basis of the same clock that stored the original observation, because I have not yet implemented any mechanism by which the system clocks in the cluster can be synchronized. Over the course of time it is not unusual for the time held by machine's system clocks to vary considerably, especially with the kind of surplus machines used here. Such disparities could, of course, render the time comparison in the script invalid, which is why at this point I will run the script on the same machine that hosts the web server.
Now I am ready to launch into developing a mechanism through which the batting order and position assignments for a game can be set. First, of course, I need a place to store that information. Further, as the batting order will be retrieved for any request that is part of an entry session, I want to keep the number of rows in the table to a minimum to minimize the load those retrievals will place on the system. That means that I am also going to need a corresponding history table in which to store records that are no longer pertinent to an on-going game, whether due to the end of the game or to player substitution. With that in mind, I have added the tables batting_order and batting_order_history.
create table batting_order ( game char(5), team char(3), player char(7), batting_order char(2), position char(2), notes varchar(100)); create table batting_order_history ( game char(5), team char(3), player char(7), batting_order char(2), position char(2), notes varchar(100));(This little snippet is included as batting_order_tables.script in the samples file for this chapter, and can of course be piped into the psql utility.) This is just a starting point for these tables, I think I am ultimately going to have to modify the structure of batting_order_history to provide a manner in which I can establish the point in the game at which a change was made, perhaps by recording the pertinent at bat event code in the batting_order_history table when the change is made. In fact, I am toying with the idea of including such a column in the batting_order table to store the starting pont of a player's participation, which would provide sufficient information to include both the start and end points of that participation in the batting_order_history table. But that's down the road a bit.
With a structure in place to store this information, I can think about how to structure the entry to the entry_tables() subroutine in a manner that will trigger the generation of a page on which these assignments can be made. In practical terms, that means figuring out how to factor this consideration into the set of conditions in BB_STACKED that direct the generation of the returned page. A major consideration in this regard is avoiding the introduction of unnecessary complexity in that set of conditions. Generating a page to allow these assignments when starting a data entry session for a game for which these assignments have not been made is one thing, but I am ultimately going to want to do the same thing when the lineup for one or both teams is changed during a game. Therefore, I am going to set another flag that I can trigger whenever one of these two circumstances exist, and use that flag to trigger the generation of the batting order / lineup assignment page. I have named the scalar that will represent that flag $order_flag, and have included it in the set of scalars initialized in the second line of the game() subroutine.
my ($session_id,$game_id,$state,$game_check,$order_flag);
In the sequence I developed in the last chapter, if the state record for the pertinent session_id holds a game_id and the value of the submit paramter does not include the string "Date", entry_tables() is called to generate the entry page. Now, however, I want to make sure that the lineup has been assigned before that page is generated. Therefore, I wrote the get_order() subroutine to retrieve the lineups stored for the teams participating in the selected game.
sub get_order {
my ($db,$game) = @_;
##get the id's of the teams assigned to the game_id
my $sth = $$db{'teams_in_game'};
$$sth->execute($game);
my ($home,$away) = $$sth->fetchrow_array;
my ($home_team,$away_team);
$sth = $$db{'batting_order'};
##get the batting order for the home team
$$sth->execute($home,$game);
##check to make sure that there is something in the result set
my $ht=$$sth->fetchrow_array;
##create a hash out of the result set if there is something there
$home_team=make_hash($$sth) if $ht;
##do the corresponding stuff for the away team
##get the batting order for the away team
$$sth->execute($away,$game);
my $at=$$sth->fetchrow_array;
$away_team=make_hash($$sth) if $at;
return ($home_team,$away_team);
}
This subroutine takes only the reference to the hash of database statement handles and the id of the selected game as arguments. I have added the preparation of a statement handle that retrieves the id's of the teams playing in a given game from the game table to the main section of BB_STACKED
##retrieve the teams associated with a given game
my $teams_game=$dbh->prepare("select home_team_code,away_team_code from game where game_id=?");
and stored a reference to it in %db_hashteams_in_game=>\$teams_gameThat statement is executed to retrieve the ids of the home and away teams.
##get the id's of the teams assigned to the game_id
my $sth = $$db{'teams_in_game'};
$$sth->execute($game);
my ($home,$away) = $$sth->fetchrow_array;
With those ids, I can retrieve the stored lineups for the teams in the game. However, I am obviously going to want to be able to display the names of the players who are in the lineup, and those names are not stored in the batting_order table. To pull that information into the result set, I make use of a sql join condition to get the player name from the participant table. The relevant statement handle prepared in BB_STACKED
my $bat_order=$dbh->prepare("select batting_order.batting_order||' '||participant.part_fname||' '||participant.part_lname,batting_order.player\
from batting_order\
left join participant on batting_order.player=participant.participant_id\
where batting_order.team=? and batting_order.game=?");
incorporates a sql statement that concantenates the batting order position with the players' first and last names as the first element in the result set and the player column, which holds the participant_id, as the second element. The result set is returned in this fashion because I want that first element to be sortable by the player's batting order position to failitate its use on the entry page. The join condition used here is a left outer join in which the pertinent rows are returned from the first table, and the specified columns from any matching records in the table that is specified in the join clause. In this kind of join, if there are no matching rows in the joined table, the pertinent elements of the result set hold null values.Note that I could as easily have specified this join as an inner join, in which only those rows with matching records in both tables are returned. (Inner joins are the default whenever the join type is not explicitly specified in a sql statement.) An inner join would work here only so long as the application itself is the only tool used to maintain the entries in the database. For example, I know that there will be a team record identifying any team referenced by a team_id in the participant table, which is the manner in which players are associated with teams, because within the application a player cannot be associated with a team unless the team is represented in the team table. That is just the way it works; a team has to exist before players can be associated with that team. At some point, however, I intend to some form of data import-export mechanism by which the information in the tables in the database can be exhanged between other installations of the system and other programs. In such circumstances it is always possible that a file may be truncated or corrupted during transmission to another machine. While I will at that point incorporate various measure to insure as much as possible the referential integrity of the database, it strikes me that it does not hurt to code defensively.
While this particular example might not be the most dramatic, it will serve as a tangible example of why the nature of the result sets returned from the different types of joins is worthy of attention. If I were to use an inner join in the statement above and somehow there was a batting_order row for that game without a corresponding record in the participant table, the batting_order record would not be included in the result set. The left join I do use, however, would return null values in the elements associated with the specified columns from the participant table. As a result, the user would at least know that something was amiss, as opposed to the former circumstance in which the user might blithely continue along, unaware that the records they were entering included incomplete or inaccurate information.
Returning my attenation to the get_order() subroutine, I execute the prepared statement with the ids for the home team and the game as arguments. (Although I intend to incorporate various mechanisms to move batting_order rows to the batting_order_history table when they are no longer pertinent, it does not hurt to guard against orphaned records. Hence the inclusion of the game id in the statement).
##get the batting order for the home team $$sth->execute($home,$game);Once the statement is executed, I evaluate the output from the fetchrow_array method in scalar context to determine if anything is in the result set.
##check to make sure that there is something in the result set my $ht=$$sth->fetchrow_array;If there is something in the result set, I execute the make_hash subroutine on that statement handle and store the returned hashref to the $home_team scalar.
##create a hash out of the result set if there is something there $home_team=make_hash($$sth) if $ht;(Note that I have resolved the difficulty I had with make_hash() and a referenced statement handle I had a chapter or two ago. DUH! If the reader is not sure what I am talking about, a comparison of the two situations would be a nice piece of homework. Note also, however, that this test determines only that there is something in the result set, not that it has the appropriate number of rows for a fully specified lineup. I am still thinking about this a bit, because the variations between the manners in which lineups are established in different game variants complicate somewhat the determination of whether a lineup is fully set. For example, a league using the designated-hitter rule would have ten players specified in the lineup, but only nine would bat. Similarly, a softball league could both have ten position players and a designated hitter, for a total of eleven in a fully-specified lineup. Checking to make sure that the lineup has the requisite number of records is almost trivial, I would simply use the fetchall_arrayref method and evaluate that in scalar context to make sure that the right number of rows are there. This is clearly something that can easily be envisioned as modified by a system setting. Going beyond that to make sure that position assignments have been appropriately set, however, is far from trivial, especially since that verification would be executed whenever a submittal that is part of a data entry session is processed. Ultimately, I will probably adopt an intermediate solution by implementing a subroutine that verifies that the lineup is appropriately set when it is first submitted, and simply check to make sure that the right number of rows are in the result set after that. For now, however, I will simply check to make sure that at least one record has been stored.)
At this point the subroutine performs the analogous set of steps for the away team.
##do the corresponding stuff for the away team ##get the batting order for the away team $$sth->execute($away,$game); my $at=$$sth->fetchrow_array; $away_team=make_hash($$sth) if $at;and the $home_team and $away_team scalars, which represent references to the lineup hashes if there are pertinent batting_order records or are undefined if such records are not present, are returned to the calling scope.
return ($home_team,$away_team);
Back within BB_STACKED, I set the $order_flag scalar to the value 1 if either of the two scalars to which the items returned from get_order() were stored fail the truth test.
##if the scalars that should hold the hashrefs hold a value that fails the truth test, set the $order_flag $order_flag=1 if ((! $home_players) or (! $away_players));In effect, I am therefore saying that if the lineup of either the home team or the away team has not yet been stored, the generation of the batting order/lineup assignment page is in order. That, of course, is exactly what I do in the next step.
##generate the lineup assignment page if $order_flag is set
if ($order_flag) {
set_order($r,$db,$$state{'game_id'},$colors,$params);
}
The set_order() subroutine makes use of the edit_table() subroutine to generate the tables within which the actual lineup assignments are made. Which brings up a point about responsible application development. Some time has passed since I wrote the last component that made use of edit_table(), and I had to spend some time looking at both it and the subroutines that call it before I could remember the required form of the arguments that it uses. This is not the way that things should be. The documentation incorporated in the source code should clearly specify the interfaces to each subroutine, and that is even more important for subroutines like this, which is relatively complex and which receives frequent use. Therefore, I will have to do that Real Soon Now <grin>. In all seriousness, before an application can be considered done its source code should be consistently documented so any individual responsible for maintaining it can determine how it works with reasonable ease. I have done that at times, but at other times I have primarily been concerned with putting functionality in place and have neglected such internal documentation. To one extent or another, that is generally the case as an application is being developed. As a result, after I finish working through the new entry interface I think I will devote a chapter to internally documenting the modules and cleaning out some of the elements that are now vestigial.As with the other subroutines that use edit_table(), most of set_order() is involved with populating the data structures that will be passed into the subroutine. As a result, those who have recently read the chapters on managing teams and managing players will find this very familiar.
sub set_order {
my ($r,$db,$game,$colors,$params)=@_;
##get the id's of the teams assigned to the game
my $sth = $$db{'teams_in_game'};
$$sth->execute($game);
my ($home,$away) = $$sth->fetchrow_array;
##create a hash of position names and codes ... this will ultimately be replaced by values stored in a table, to
##allow the system to be adjusted to accord to the needs of game variants
my %position = (' ' => ' ',
'1 - Pitcher' => '1',
'2 - Catcher' => '2',
'3 - First Baseman' => '3',
'4 - Second Baseman' => '4',
'5 - Third Baseman' => '5',
'6 - Shortstop' => '6',
'7 - Left Fielder' => '7',
'8 - Center Fielder' => '8',
'9 - Right Fielder' => '9');
##create a hash of the itegers from 1 through 9 in which the keys and values will both be the same integer
##will be used to assign batting order
my (%order,$i);
for ($i=0;$i < 10;$i++) {
$order{$i} = $i;
}
##get references to executed statement handles holding the team player listing with whatever batting order and position assignment
##has already been made
##get the team members and any current lineup assignments
##home team
$sth= $$db{'team_and_batting_order'};
$$sth->execute($home);
my $home_team=$$sth->fetchall_arrayref;
##away team
$$sth->execute($away);
my $away_team=$$sth->fetchall_arrayref;
##create the arrays that hold the descriptive information for the columns in the tables that will be drawn by edit_table
my @a=('Name','name','print','');
my @b=('','participant_id','hidden','');
my @c=('Batting_Order','batting_order','select',\%order);
my @d=('Position','position','select',\%position);
my @e=('Notes','notes','text','');
my @names=(\@a,\@b,\@c,\@d,\@e);
##get the team names for the table headings
my $home_team_name=get_team_name($home,$db);
my $away_team_name=get_team_name($away,$db);
##the tables output should have the same number of rows as there are players assigned to the team.
##the number of elements in each arrayref will therefore represent the maximum number of rows
##in the table
my $h_rows=@$home_team;
my $a_rows=@$away_team;
##start the form, and print a page header
$$r->print("<form action = '/bb_app/league/la-store' method='post'>");
$$r->print("<center><font size='8'>Batting Order and Position Assignment</font>");
$$r->print("<br><br><br>");
$$r->print("<table><tr><td><center>");
##print the away team table
$$r->print("<font size = '6'>Away Team: $away_team_name</font><br>");
edit_table($params,$away_team,\@names,$a_rows,0,$r,'');
$$r->print("<br><br>");
$$r->print("</center></td><td><center>");
##print the home team table
$$r->print("<font size = '6'>Home Team: $home_team_name</font><br>");
edit_table($params,$home_team,\@names,$h_rows,0,$r,'');
$$r->print("<br><br>");
$$r->print("</center></td></tr></table>");
$$r->print("<input type='Submit' name='submit' value='Save_Batting_Order'></center></td></tr></table></form>");
}
In this subroutine, after using the statement handle keyed to the string "teams_in_game" in %db_hash to retrieve the ids for the home and away teams for the selected game
##get the id's of the teams assigned to the game
my $sth = $$db{'teams_in_game'};
$$sth->execute($game);
my ($home,$away) = $$sth->fetchrow_array;
I create two hashes to hold batting order and position descriptions and codes. These will be used to populate select boxes.
##create a hash of position names and codes ... this will ultimately be replaced by values stored in a table, to
##allow the system to be adjusted to accord to the needs of game variants
my %position = (' ' => ' ',
'1 - Pitcher' => '1',
'2 - Catcher' => '2',
'3 - First Baseman' => '3',
'4 - Second Baseman' => '4',
'5 - Third Baseman' => '5',
'6 - Shortstop' => '6',
'7 - Left Fielder' => '7',
'8 - Center Fielder' => '8',
'9 - Right Fielder' => '9');
##create a hash of the itegers from 1 through 9 in which the keys and values will both be the same integer
##will be used to assign batting order
my (%order,$i);
for ($i=0;$i < 10;$i++) {
$order{$i} = $i;
}
Ultimately, these hashes will probably be fed from the contents of a table that holds customization options, because there are leagues that employ positions (e.g., designated hitter) that deviate from the pure baseball model. (The previous comment may give perceptive readers a subtle hint that my personal affinities lie with a National League team.)Now the subroutine retrieves the players associuated with the two teams in the selected game and any lineup assignments that have already been made for those players.
##get the team members and any current lineup assignments
##home team
$sth= $$db{'team_and_batting_order'};
$$sth->execute($home);
my $home_team=$$sth->fetchall_arrayref;
##away team
$$sth->execute($away);
my $away_team=$$sth->fetchall_arrayref;
Key to this operation is the new database statement that I have prepared in BB_STACKED and to which I have added a reference in %db_hash.
##retrieve a result set comprised of the players assigned to a given team, and their active position and batting order assignments if present.
my $team_bat_order=$dbh->prepare("select participant.part_fname||' '||participant.part_lname,participant.participant_id,\
batting_order.batting_order,batting_order.position,batting_order.notes\
from participant\
left join batting_order on participant.participant_id=batting_order.player\
where participant.team=?\
order by participant.part_lname");
In effect, this statement turns the statement used in get_order() on its head. The difference between the two statements can be explained by the functional differences between the two subroutines: in get_order() I am retrieving assigned lineup records and associating with them the name of the player, while in set_order() I am after every player associated to each of the two teams in the game and any lineup assignments already made for those players. Thus the table that was on the left in the earlier statement, batting_order, is on the right here, and the table that was on the right, participant, is on the left hand side of this statement. Note that I am explicitly ordering this result set by the player's last names. Unlike get_order, where the result set is fed into make_hash() and sorted by sel_box(), here I am pulling the result set into an arrayref, the structure used by edit_table() to print output, and as the references in that array will be in the same order as the result set I can determine the order of the rows in the table that will be output by specifying that order in the sql statement.The prepared statement handle that uses this statement is executed twice, with the ids for the home and away teams as the arguments, and the references returned from the fetchall_arrayref method are assigned to scalars named $home_team and $away_team, respectively. I am getting everything that I need from each result set when I execute the method on the executed statement handle, so I simply reuse the statement handle rather than creating a second instance of it. When I created the first version of this subroutine, I thought that perhaps I had run afoul of the postgresql DBD driver in some manner, because I was only getting a result set for one of the teams. Preoccupied at the time with other responsibilities, I tried using a second instance of the statement handle, and calling the finish method on the statement handle, but neither of those steps made any difference. The following week I had a little more time to look at the situation and realized that only one of the teams involved in the game that I was selecting had players assigned to it <abashed grin>. While I had added a sufficient number of players to fill out the rosters of a couple of teams, it was stuck in my head that I had assigned them to a different team than I had. Therefore, when I scheduled a game to serve as a test, I was consistently scheduling into that game one team to which no players had been assigned. The page was being generated exactly as intended, but I was not realizing my mistake on the basis of what was being generated. While primarily an amusing anecdote, this story does illustrate the rationale for trapping unusual contexts and identifying them in the generated output. That sort of thing is not something one should worry about when constructing the first working version of any given component of an application, but does illustrate the justification behind extending one's thoughts beyond pure program functionality to what the user will see when something happens that they do not expect. This is not precisely a user interface consideration, because such tend to be involved with integrating the interface with the type of actionbs that the user will want to take in any given situation, nor is it truly error reporting, which is generally associated with notifying the user and/or the system administrator that a requested operation did not complete, but it has elements of each. This has more to do with anticipating events that will confuse a user and providing feedback that will help them to understand what is going on. I will add something to trap this particular circumstance shortly, and in advance of that those so inclined may find it worthwhile to think of potential approaches.
Once the player and lineup record structures have been created, I populate the arrayref that holds the descriptive information information about the colums in the tables that will be generated.
##create the arrays that hold the descriptive information for the columns in the tables that will be drawn by edit_table
my @a=('Name','name','print','');
my @b=('','participant_id','hidden','');
my @c=('Batting_Order','batting_order','select',\%order);
my @d=('Position','position','select',\%position);
my @e=('Notes','notes','text','');
my @names=(\@a,\@b,\@c,\@d,\@e);
As a refresher, the arrays created here define, in order, any text to be displayed in the table header row for the given column, the name to be used for that data element in the generated html, the type of the data element, and a reference to a hash holding the options and values to be included if the data element is a select box. References to these arrays are then included in an array, in the order in which the items they describe exist in the arrays that hold the items to be described.At this point I decided that I was going to want to preface the table displaying the lineup associated with a given team with the name of that team. That makes sense, right? I do not, however, yet have those names available, though I do have the ids available in the scalars $home and $away. As it seems likely that I might want to have the ability to get the team name associated with a given id elsewhere, I decided to create a small subroutine to perform the operation.
##this subroutine returns the name of the team associated with a given team id
sub get_team_name {
my ($id,$db)=@_;
my $sth=$$db{'team_name'};
$$sth->execute($id);
my @name=$$sth->fetchrow_array;
return $name[0];
}
This subroutine is very simple, with only the team id and the reference to %db_hash as arguments and firing off a statement handle that I have added to BB_STACKED
my $team_name=$dbh->prepare("select team_name from team where team_id=?");
, reading the only row that can be returned from the execution of the statement (team_id is defined as unique in the table team) into an array, and returning to the calling scope the first, and only, element in that array. This subroutine is, of course, executed twice within set_order().##get the team names for the table headings my $home_team_name=get_team_name($home,$db); my $away_team_name=get_team_name($away,$db);
edit_table() will print empty rows in the table it generates if desired, to allow it to be used as a mechanism through which information can be added to the system. Such rows will be printed whenever the appropriate argument passed to edit_table() has a value that is larger than the number of elements in the array that holds references to the arrays holding table row data. (Some readers may recall that this is the manner in which the page that allows a day(s)' schedule to be edited displays the game(s) scheduled for that day along with empty lines that allow games to be added, up to the maximum number of games allowed for the day of the week of that date.) In this circumstance, of course, that is not only unnecessary but also not desired. I do not want a misguided user to attempt to add players to a team's roser here, but rather by adding players through the players option under Team and Player Maintenance, where the addition of the players will be logged in the part_change table. All of which is a long-winded way of explaining why my next step is to count the number of elements in the two arrayrefs, because the number of rows of data in each of these tables is also the number of rows I want to have in the pertinent output table.
my $h_rows=@$home_team; my $a_rows=@$away_team;
Now I have in place the material I need to use edit_table() to generate the lineup tables. Before I get into the short block in which the tables are generated, it is probably appropriate here to recall the order of the arguments passed to edit_table(). In order, they are the reference to the parameters hash, a reference to the arrayref structure holding the descriptive information for the columns in the table, the maximum number of rows in the table, a flag value indicating whether a column should be generated allowing any row in the table to be deleted, the string held in $colors, and the final argument is a scalar holding a unique identifier for the specific table. That identifier is only necessary when multiple tables are printed on the page and the deletion flag is set, because it is necessary to uniquely name the radio buttons that are used to select a table row for deletion.
Generating the lineup/batting order selection tables is now straightforward.
##start the form, and print a page header
$$r->print("<form action = '/bb_app/league/la-store' method='post'>");
$$r->print("<center><font size='8'>Batting Order and Position Assignment</font>");
$$r->print("<br><br><br>");
$$r->print("<table border='1'><tr><td><center>");
##print the away team table
$$r->print("<font size = '6'>Away Team: $away_team_name</font><br>");
edit_table($params,$away_team,\@names,$a_rows,0,$r,'');
$$r->print("<br><br>");
$$r->print("</center></td><td><center>");
##print the home team table
$$r->print("<font size = '6'>Home Team: $home_team_name</font><br>");
edit_table($params,$home_team,\@names,$h_rows,0,$r,'');
$$r->print("<br><br>");
$$r->print("</center></td></tr></table>");
$$r->print("<input type='Submit' name='submit' value='Save_Batting_Order'></center></td></tr></table></form>");
The snippet above largely follows the strategy used whenever I have previously used edit_table() to generate tables on a page: starting the html form, printing some form of page header, calling edit_table() to print individual tables, printing one or more submit buttons, and closing the form. In this particular instance the tables being printed are relatively narrow, so I decided that this would be a good situation in which to print the tables side by side. Therefore, the tables printed by edit_table() are themselves wrapped within a simple two-cell table, each cell of which holds a table. The resultant page looks like this:
Now that I have the page that I want to use to establish the team's lineups and batting orders, in the next chapter I will generalize the subroutine that is used to process submittals from pages generated by edit_tables, and use that generalized subroutine to process submissions from this page.