(Recall that I have previously made brief mention of application frameworks like Mason that run under mod_perl. What I am discussing here should not be confused with those, but merits the term framework in the sense that it is the structure under which the editing functionality will be implemented.)
The framework that I am going to create here is very similar to that which I have already established for data entry, with some noteable differences. Most prominent among those is the fact that while the data entry settings are persistent beyond the length of any given session, and indeed will be recalled whenever an authorized user logs into the system, the editing function is only going to maintain its settings for as long as an individual editing process is on-going. My rationale for this is straightforward: it is not at all difficult to imagine a context in which a given user might enter designated chunks of a game, with others shouldering part of the load either on the same workstation or on additional ones, and this persistence would allow users to log in and jump back into entry with a minimum of fuss. On the other hand, I would envision editing to be the kind of process in which records for multiple games might be amended to correct data entry errors that might have happened during the course of a game, or perhaps simply to review what is recorded for a game. Which leads me to the realization that I do not yet have a place to record a certification of the results and records of a game as final, not to hold such summary data as the winning and losing pitchers, etc. It would be readily possible to create event codes for many of these and have records in the participant_game table that represented such designation, but for ease of reporting and exhanced flexibility it probably makes sense to store them seperately. Another item for the to-do list.
In any event, as I thought about this process I realized that it would be appropriate for this function to have a much wider range of games from which to select than is the case with the data entry process. (However it is ultimately implemented, I anticipate that game selection for the data entry process will ultimately be limited to a small set of games on the same date as the server's system date, perhaps with an override capability to allow users with sufficient authorization to retroactively enter games from paper records as necessary.) While it is clear that I will ultimately have to make provision for games marked as finalized, at this point I am simply going to select for all of the games in the games table.
The down side to selecting from a wider set of games, however, if that the selection mechanism will have to display a much more descriptive set of information to be easy to use. To facilitate that, I made a change in the structure of the games table, adding the columns home_team_name and away_team_name. As an aside, it is relevant to note that I made something of a trade-off when I designed the games table initially, and adding these columns is a direct consequence of that trade-off. In a fully-normalized design, I would have had a table, perhaps named team_game, that had one record for each combination of team and game, that would be in addition to the table that holds game information alone. As a result, there would obviously be two records in the team_game table for each game, one flagged as the home team and one as the away team. My rationale for not going to that level of normalization in this context involved easing reporting requirements and, to a lesser extent, augmenting system performance. As an illustration, if I were to select a specific set of games and the teams involved in those games under a fully-normalized design, I would have to execute an sql statement that included a multi-table join, and if I were to desire a result set that included one row per game there would have to be some reasonably complex code somewhere behind the scenes to fit the result set into the appropriate structure. Creating that code would not be the end of the world, I already do similar things elsewhere in the system, but it does add complexity to the system as well as execution cycles to whatever context in which it was invoked. The flip side of the trade-off is that every time I want to search for the games in which a team is involved I am going to have to search both the home and away team columns, with some additional code to flag home and away status, as opposed to simply returning all of the records from a team_game table that had that team_id.
In database design, I tend to be a pretty normalized kind of guy, generally opting for the increased flexibility offered by that kind of representation, and there may come a time when I decide to come back and rework the structure into a fully-normalized form. I would be very rare indeed if I were ever to do this with a data element that could have more than two states, because the kinds of workarounds I describe above grow geometrically as those states multiply. (Think, for example, of an event record that specified every possible position explicitly.) In actual practice, however, it is frequently the case that a point is reached at which the marginal benefit associated with normalization of a given element is not considered to justify the marginal cost (effort) required to reach it.
That digression could have gone on for much longer, but I had to stop it somewhere <grin>. As I said earlier, I want the user to be able to select from a much wider set of games with substantially more descriptive information when they enter the edit function. To facilitate that, I have added a bit of code to the main section of BB_STACKED.pm that retrieves that descriptive information from the games table and constructs a hash associating that descriptive information with the game_id.
my $get_games=$dbh->prepare("select game_id,date,time,home_team_name,away_team_name from game");
my $get_games_result=$get_games->execute();
my $all_games;
if ($get_games_result) {
my (%all_games,$ag_key,$id,$date,$time,$home,$away);
while (($id,$date,$time,$home,$away)=$get_games->fetchrow_array) {
$ag_key=$id." on ".$date." at ".$time.": ".$away." playing at ".$home;
$all_games{$ag_key}=$id;
}
$all_games=\%all_games;
}
Little new here. All I am doing is retrieving the information from the database, and for each of the rows in the result set constructing a descriptive string that is used as the key for the hash, then creating a hash slice with that key and the game_id as the value. The while() loop is expressed in somewhat more perlish fashion in that rather than assigning array elements within the loop I drop the elements of the array returned by fetchrow_array directly into scalars. After the loop executes I assign a reference to the constructed hash to the $all_games scalar.The entry to the edit function is similar to the data entry function, but reflects the fact that I am only going to be concerned about the persistence of an editing session as long as the editing cycle continues. That is, if the user were to execute any other item within the system during an editing session they would have to re-select edit session settings before another set of records will be displayed. As I indicated earlier, there is a firm rationale for this: the edit function is the type of function that a user is likely to perform as part of a set of system management activities in which a few records from a given game might be edited, the umpires assigned to a game, the location of an upcoming game changed, and a few records from another game edited. As a result, maintaining edit state information only within a given edit session loop both helps to prevent inadvertent editing of records from the wrong game and more closely accords to the manner in which the system is likely to be used.
Entry to both the data entry function and the editing function is controlled by the presence or absence of a parameter holding the game_id for the subject game. In the development of this bit I briefly gave the parameter a different name to prevent conflicts caused by the use of the same name, but then I realized that the combination of the action specification in the form definition in concert with the nature of parameters is sufficient to keep that conflict from occuring. (The concern would have a little more validity if I were storing the value held in the parameter to a scalar and then using that scalar to control execution.) If the path information in the request object has included both the strings "game" and "edit"
elsif ($path=~/edit/) {
if (! $$params{'game_id'}) {
edit_choices($r,$num_rows,$s_inning,$s_half,$all_games,$colors);
}
elsif ($$params{'game_id'}) {
edit_page($r,$num_rows,$s_inning,$s_half,$colors,$params);
}
the hash referenced by the $params scalar is checked for the parameter game_id and if it does not exist the edit_choices() subroutine is executed, generating a page on which the user selects the game to be edited, the inning/half-inning combination at which the user wishes to begine editing, and the number of rows of records which the user wishes to display on one page. The code that generated this page is very similar to the code used for setting user preferences.
sub edit_choices {
my ($r,$all_games,$colors)=@_;
if (! $all_games) {
$$r->print("<center>There are currently no records in the system's games database. Check with the system admin.</center>");
}
elsif ($all_games) {
##the following two scalars hold the strings required to start and end a table ... i started using them to better
##delineate the tables nested within tables, and then just decided to leave them in
my $i_start="<table><tr>";
my $i_stop="</tr></table>";
##obviously, a game must be selected. additionally, the user can choose a different number of rows to be displayed on the screen,
##and to start at a later inning/half-inning, or can allow the default values of 10 and the beginning of the game to ride
$$r->print("<center><h2>Select the game, number of rows to be displayed on one page, and the starting inning.</h2></center><br><br>");
##define and start main table, row and first column
$$r->print("<html><form action='/bb_app/league/la-game-edit'><center><table width='60%' border='1' $colors><tr><td>");
##start first inner table and output the selectbox
$$r->print("<center>$i_start");
sel_box($all_games,$r,'game_id',"Select the Game you wish to edit:<br>");
##close first inner table
$$r->print("$i_stop</center>");
##insert a break and start the second inner table of the first column
$$r->print("</td><td><center>$i_start<br>Select the number of rows displayed on a page<br><input type='radio' name='num_rows' value='5'>Five<input type='radio' name='num_rows' value='10' CHECKED>Ten<br>");
$$r->print("<input type='radio' name='num_rows' value='15'>Fifteen<input type='radio' name='num_rows' value='20'>Twenty");
##close the second inner table and the first column
$$r->print("</tr>$i_stop</center>");
##start the third column, and the inning selectbox
$$r->print("<td><center>$i_start Select Starting Inning");
##print the select box
$$r->print("<select name='inning' size='1'>");
my $value='01';
while ($value lt '20') {
$$r->print("<option>$value</option>");
$value++;
}
##close the selectbox and the inner table
$$r->print("</select></center>$i_stop</td>");
##start the fourth column, and the selectbox for half_inning
$$r->print("<td><center>$i_start");
$$r->print("Select Starting Half-Inning <select name='half_inning' size='1'>");
my (@half_inning,$half_inning);
@half_inning=("Top","Bottom");
foreach $half_inning(@half_inning) {
$$r->print("<option>$half_inning</option>");
}
$$r->print("</select>$i_stop</center></td>");
##close the column, row, and table of the outer table
$$r->print('</td></tr></table>');
$$r->print("<br><input type='submit' name='enter' value='Enter'></form></center>");
}
}
This subroutine is documented in sufficient detail internally that I do not need to go to great lengths to describe its implementation, but it is worth noting a few things. First, the structure of this routine produces tables nested within tables. As a result, I found it easy to lost track of which table and row I was opening and closing at any one time. As a result, I initialized scalars named $i_start and $i_stop (named for inner start and stop, meant at the time to refer to the inner tables) and put the html representing the start and end of a table into them. Not a big deal, but the kind of thing that I find can help one to more readily visualize the structure of what is being constructed. The code displaying the select box for games reflects just why I constructed the hash referenced by $all_games in the manner I did; in that form, I can feed it straight into the sel_box subroutine.
Submission of this form will result in the execution of the edit_page() subroutine on the server.
sub edit_page {
my ($r,$colors,$params)=@_;
$num_rows=$$params{'num_rows'};
$$r->print("<html><center>Edit</center><br><br><br>");
$$r->print("<form action='/bb_app/league/la-game-edit'>");
$$r-<print("<center><table width='95%' border='1' $colors>");
my $i;
for ($i = 1;$i <= $num_rows;$i++) {
$$r->print("<tr><td>$i<br></td></tr>");
}
$$r->print("</table><center>");
$$r->print("<br><center><input type='submit' name='submit' value='enter'></center></form>");
}
As is evident, at this point the edit_page() subroutine simply prints the shell in which the records will be displayed back to the browser, as in the image below.
In the next section, I will fill this structure with records and attach appropriate actions to the form.