As previously mentioned, the primary focus of this chapter will be the modification of the subroutine used to perform record modification as the result of submissions from pages generated by edit_table(). Any readers who decided to prepare for this chapter by reviewing the version of edit_schedule_proc() included in previous versions of BB_APP_INTERFACE (you did do that, didn't you?) may have been moderately bewildered by what appears to be a complex subroutine, especially because it has been some time since I last visited it. In truth, however, the subroutine is not all that difficult to understand, and the changes required to allow it to handle submissions from the batting order / lineup selection page are not extensive. There are a couple of things that I will have to add to the generation of the lineup selection page to provide information to allow appropriate direction of execution as the resultant request object is processed by the modified subroutine, but this should not be an overly-long chapter.
Before I get into the required modifications to the subroutine, however, I think it wise to briefly recap the structure of edit_schedule_proc(), which will help to fit those modifications into context. While the subroutine is relatively long, much of its length is occupied by the specification of the data structures used by the subroutine with values pertinent to the various contexts in which it will be called. The several pages of code in the subroutine comprise only five major steps, which of course encompass varying degrees of complexity.
| A. | Initialize data structures |
| B. | Populate some of those structures with contextually-pertinent values. |
| C. | Step through the arrays of parameter values, constructing arrays of elements in the corresponding positions in the parameter arrays. |
| D. | Take the action appropriate for the individual record (i.e., delete, update, add). |
| E. | Generate a page summarizing the results of those actions and providing notification of any failed operations. |
First, the table modification. The original version of batting_order (and batting_order_history) did not have a key column, largely because I did not think of it at the time. As I started to consider how I was going to populate the table with records for a given game during the entry session initialization process, however, I started thinking about the problems that would be attendant to record duplication in this table. While, as with other tables, the application itself should be the only mechanism through which records can be added to the table, I recalled my own admonition to code defensively and added a unique key to both batting_order and batting_order_history. I sized the new key columns to hold the concantenation of the game id and the participant id, which should be sufficient for a unique key in this context. My ulterior motive here was to aid in the development process, because a failed execution of a statement handle that attempted to add a duplicate key would readily signal that something was not working correctly.
create table batting_order ( key char(12) unique, game char(5), team char(3), player char(7), position char(2), batting_order char(2), notes varchar(100));
As I modified the table I realized that I had left a couple of data elements off the generated page that are required to appropriately populate the rows in batting_order, specifically the game and team ids. (There are two more elements that I have added to appropriately direct execution when the submitted request object is processed by the server, but I will get to those a little later.) The selected game id is already present in the set_order() subroutine as the scalar $game, so all I had to do for that element was to print it as a hidden field.
$$r->print("<input type='hidden' name='game_id' value=$game>");
While I could as easily have printed the ids of the home and away teams as hidden fields given that they are available as the scalars $home and $away, I really want the pertinent team id to be associated with each individual player. The reason for that will become evident shortly. I could have used the scalars already present in the subroutine and written some relatively clunky code to perform that association, but adding that item to the result set used in table generation made more sense. As I had left that element out of the list of columns included in the preparation of the database statement handle that retrieves the result sets used on the lineup selection page, I modified that statement (in BB_STACKED)
##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,\
participant.team,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");
and inserted a reference to an array naming the data element and describing it as a hidden field to the @names array in the set_order() subroutine.
my @c=('','team_id','hidden','');
With these changes, I had sufficient information to create the full table row I want to store in batting_order, and could begin to concentrate on the processing of submissions from the generated page.I have named the modified version of edit_schedule_proc() to edit_tables_proc(), which kind of makes sense, right? After all, this subroutine will process submissions from pages generated by edit_table(). I copied edit_schedule_proc() to the bottom of BB_APP_INTERFACE and renamed it before I started making modifications to the subroutine to minimize the potential for problems resulting from the modification.
As I mentioned above, the key characteristic of the lineup page submissions from the standpoint of the subroutine processing those changes is that the potential number of operations for any given record is more limited than those that might be performed on the submissions from the other pages generated by edit_table(). From the standpoint of the successive generations of pages in the data entry process, once the lineups have been established for the two teams in the pertinent game as part of the entry session initialization process those records should be saved to the batting_order table, and at any point at which the lineup page is generated afterward any changes made should be saved to the table. Therefore, I do not have to worry about deleting records in this subroutine. Further, I do not have to go through the process of generating a new id for the records that will be added to the table during session initialization. As I want to minimize the amount of code executed that is not pertinent to the specific submission the subroutine is processing at any given time, my goal here is to embed the code that will add or modify the table records and incorporate structures that will trigger those actions and none other. It will be all for the better if I can do that by incorporating a structure that will allow further modifications to be added with reasonable ease, should that become pertinent.
First, however, I have to define the database actions that should be executed when appropriate. To that end, I prepare two new statements in BB_STACKED
##insert a new batting_order record
my $lineup_new=$dbh->prepare("insert into batting_order (key,game,team,player,position,batting_order,notes) values (?,?,?,?,?,?,?)");
##update an existing batting_order record
my $lineup_update=$dbh->prepare("update batting_order set position=?,batting_order=?,notes=? where key=?");
and store references to the two statement handles in %db_hash.new_lineup_record=>\$lineup_new, update_lineup_record=>\$lineup_updateNote that in the update statement I am updating only those columns that might have been modified on the lineup page.
Within edit_tables_proc(), the first modification I made was to include a block that assigns values to the set of scalars that do not change within the subroutine. This block is within an if condition that is included within the first switch construct within the subroutine.
if (@{$$params{'task'}}[0]=~ /lineup/) {
$lineup=1;
$update_dbh=$$db{'update_lineup_record'};
$new_dbh=$$db{'new_lineup_record'};
$form_action="<form action='/bb_app/league/la-game-entry' method='post'>";
$submit_value=qq/Continue with Entry/;
last SWITCH;
}
This block assigns fewer values than are assigned in other contexts simply because there are fewer potential operations that could be performed on the contents of a lineup page submission.Following this assignment the script determines the number of tables that were on the submitting page and enters a loop that iteratively processes the elements of the submitted page. It is pertinent to recall here that the entire table numbering scheme was derived to one end; that being to be able to uniquely name the sets of radio button widgets when multiple tables are being generated, and to be able to associate the resulting parameter with the slice of parameter array values to which it pertained. The deletion radio button widgets, however, are not used on the lineup page: from the user's perspective, removing someone from the lineup is just a matter of changing the batting order and position selections to the blank values and making any appropriate notation in the notes field. As the table elements that are used on the lineup page will all occupy sequential positions in the pertinent parameter arrays when the request object is processed by the server, there is really no reason to handle values from the two tables independently as long as I am able to associate the set of values comprising an individual record with the team with which the individual is associated. That, of course, is why I added the team id to the result sets from which the tables are generated within set_order() and specified that the element should be included as a hidden field. Further, as I am not processing multiple tables, I do not have to iteratively assign the name of the @control array as I do when processing submissions that were constructed with the table number embedded within element names, but can explicitly name one of the parameter arrays to serve that function.
elsif ($lineup) {
@control=@{$$params{'participant_id'}};
}
Once the @control array is established, of course, the subroutine enters a block that executes for each element of that array. The first step within that block is a switch block within which certain data structures are populated with parameter array elements pertinent to the page from which the submission originated. The structures that must be uniquely populated for each element of the @control array are the @update array, which holds the array of elements fed to the statement handles that update tables; the @new array, used with the insert statement handles; and $id, which is used by the statement handle that deletes table rows. When the subroutine is processing a submission from one of the pages on which it is possible to add records, currently all of the submitting pages other than the lineup page, the @new array cannot be established until a new id is created for the record being added, which happens after this switch block. As the lineup selection page does not add records in that fashion, and records are not marked for deletion per se, I can populate the @new array here, and the $id scalar is not required at all.
if ($lineup) {
@update=(@{$$params{'position'}}[$k],@{$$params{'batting_order'}}[$k],@{$$params{'notes'}}[$k],\
@{$$params{'game'}}[$k].@{$$params{'participant_id'}}[$k]);
@new=(@{$$params{'game_id'}}[0].@{$$params{'participant_id'}}[$k],@{$$params{'game_id'}}[0],\
@{$$params{'team_id'}}[$k],@{$$params{'participant_id'}}[$k],@{$$params{'position'}}[$k],\
@{$$params{'batting_order'}}[$k],@{$$params{'notes'}}[$k]);
last SWITCH;
}
So how are records added to the batting_order table? After all, the initial lineup has to be stored in the table somehow. I chose to address this by, in effect, setting a flag to trigger the execution of the statement handle that incorporates the pertinent insert statement when set_order() is first called during the entry session initialization process. At one level this is fairly straightforward, because set_order() is called in an unambigous manner during entry session initialization. Therefore, I can pass an argument to the subroutine that indicates that it is being called during that process.
set_order($r,$db,$$state{'game_id'},$colors,$params,'1');
(Note the final argument passed to the subroutine.) This value is read into the scalar $status in set_order(). (Had to name it something.) my ($r,$db,$game,$colors,$params,$status)=@_;Now I needed to get the flag into the set of information sent back to the browser when the lineup modifications are submitted, so I added it to the html form as a hidden field.
$$r->print("<input type='hidden' name='status' value=$status>");
Also required, of course, is a hidden field assigning a value that will be read into the "task" parameter.
$$r->print("<input type='hidden' name='task' value='lineup'>");
With those hidden fields in place, I have sufficient information to appropriately direct execution as the submitted object is processed in edit_tables_proc(). As I mentioned above, much of the code in this subroutine is involved with deriving a new id for new records and to a lesser extent on deleting table rows. In this context I do not have to worry about any of that, so I wrapped that entire block of code within a switch, incorporated a block that executes if the subroutine is processing a submitted lineup page at the beginning of the switch block
SWITCH: {
##if this is a lineup submission, the records will be added to the table during the entry initialization process and after
##that point any time the page is displayed the existing records will be updated.
##therefore, the more complex logic that allows records to be entered is not necessary.
if ($lineup) {
if (@{$$params{'status'}}[0]=1) {
$$new_dbh->execute(@new);
}
elsif (! (@{$$params{'status'}}[0]=1)) {
$$update_dbh->execute(@update);
}
last SWITCH;
}
and wrapped everything else from this point to the beginning of the generation of the summary page within an elsif() that executes if the subroutine is not processing a lineup submission. With this structure, if the vector of array values being processed is associated with a lineup submission, execution will jump to the end of the switch block rather than passing through the code now included within the elsif() block. The most difficult thing about putting this structure in place was getting the right curly braces that end the elsif() and the switch block into the right locations. For all the simplicity of the structure, however, it does provide a framework onto which I can hang additional conditions should the need arise.At this point, the basic functionality of the subroutine is complete. After adding the subroutine name to the @export array at the beginning of BB_APP_INTERFACE, and modifying the appropriate condition within the code in BB_STACKED that executes if the path information includes the strings "league" and "store"
elsif ($$params{'task'}[0] =~ /schedule/ or $$params{'task'}[0] =~ /team/ or $$params{'task'}[0] =~ /players/ or $$params{'task'}[0]=~/lineup/) {
edit_tables_proc(@pass);
}
selections made on the batting order/lineup selection page are appropriately stored, and submissions from the other pages processed by this subroutine are handled appropriately. No summary information is yet displayed for a lineup submission, however, nor do I have quite enough information to appropriately launch into the entry page.So what am I missing? Well, in terms of launching into the entry page I have not yet passed the session id back to the pages that are generated as a lineup is established. As a result, if I were to click the submit button on the generated summary page, the submitted request object would not hold a parameter named "session_id", and a new session_id would be created as the submission was processed by BB_STACKED. As the $state hashref would not exist, its creation through the execution of get_state() being dependent upon the existence of a session_id parameter, the select_game() subroutine would be invoked and the game selection page generated. Therefore I need to print the session id as a hidden field not merely on the lineup/batting order selection page, but also on the summary page generated by edit_tables_proc(). (The earlier version of this subroutine was never used in a context in which the session id was pertinent.) This is, of course, quite straightforward, requiring only that the following line be inserted somewhere within the html forms created in the two subroutines.
$$r->print("<input type='hidden' name='session_id' value=$$params{'session_id'}[0]>");
With those statements in place, once the submit button on the summary page generated by edit_tables_proc() is clicked, the server will process the submission and call entry_tables(), generating the entry page.Adding the appropriate counters for the summary information is similarly straightforward, requiring only that the return code from the execution of the pertinent database statement handle be assigned to a scalar, and that the accumulator and statement execution counter scalars be appropriately incremented. The small block executing database statements now looks like this:
if (@{$$params{'status'}}[0]=1) {
$res=$$new_dbh->execute(@new);
$inserts=$inserts+$res;
$all_inserts++;
}
elsif (! (@{$$params{'status'}}[0]=1)) {
$res=$$update_dbh->execute(@update);
$updates=$updates+$res;
$all_updates++;
}
last SWITCH;
}
As records are not deleted as the result of lineup page submissions, I decided that it was a trfle misleading to print any lines regarding record deletions in this context, so I decided to make the generation of such lines dependent upon the submission not being a lineup submission.
if (! $lineup) {
if ($all_deletes == $deletes) {
$$r->print("<font color='green'>$all_deletes records deleted<br></font>");
}
elsif ($all_deletes > $deletes) {
$$r->print("<font color='red'>Not all deletes successful.<br>Contact System Administrator<br></font>");
}
The resultant page looks like this:In the next chapter I will be lauching into the entry page, adding a few more items to the page, processing submissions from the forms on the page, and re-generating the page in accordance with whatever changes in the state of the inning those modifications entail. In time, however, it is likely that I will be returning to the lineup page, because in its current form there is nothing that will tell a user that they have assigned two players to the same position or spot in the order, or that they have not assigned all of the positions or batting order slots. I can justify postponing incorporating such measures largely because any such problems could be readily remedied by clicking on the "modify lineup" submit button that I will be including on the entry page. Nonetheless, incorporating such logic will represent an interesting project, perhaps worthy of a chapter of its own.