After the extensive amount of work involved in the initial development of the BB_UTIL and BB_INTERFACE modules, this one is going to seem anticlimactic. Don't let that fool you. In the long run BB_DATABASE.pm is going to carry a lot of the load as the database is accessed for various purposes. At this point, however, the only access is to insert records into the participant_game table, so that's what the initial version of the module will hold.
As I go back into recs_insert.pl, I want you to remember that much of what remains in this script will be changing very soon as I start adding interface elements that will allow the users (or, more likely, a system administrator) the ability to establish certain system parameters. Once that happens, the set of destination and repository directories, for example, will likely be read from configuration files that have been written by those administrative routines. The development of those routines will be coming up very soon now.
If you look back at a previous version of the insert script, you will recall that the basic strategy employed in the insert_recs() subroutine was to pass it an array that represents one of the output files from the BB_INSERT::store() subroutine. (Note the difference in the manner in which I referred to that.) I could do that here, passing a reference to the array and the FileHandle::Deluxe objects for the repository and log files, but remember that I am starting to think about making these subroutines as portable as possible. I do not want to create a situation in which I have several routines for record insertion into any given table. Rather, what I want to do is create largely indivisible chunks of code that can be put together flexibly in a wide range of circumstances. These "primitives" can then be assembled into subroutines that perform with greater specificity if such is appropriate.
Given that, I decided that what I want to do here is create a subroutine that just inserts a line. There is really not all that much to doing that. In effect, all I did was lift the first several lines within the foreach loop in the insert_recs() subroutine; everything from the first line of that subroutine through the actual insert statement. As before, I have to be explicit about what is fed to the subroutine and what it spits back. (Like my metaphors? <grin>) Obviously, I have to have the line that represents one row in the participant_game table, and I have to have the database handle on which the insert statement will be executed. On the calling side, I require an indication of whether the insert operation succeeded, and an identifier for the individual record (just in case it did not.) So what I get is the subroutine below:
sub insert_line {
my ($dbh,$recs_line)=@_;
($key,$ec,$rc,$pc,$erc,$et)=split(/ZzZ/,$recs_line);
($game_id,$user_name,$inning,$half_inning,$inning_order)=split(/::/,$key);
$key=$dbh->quote($key);
$game_id=$dbh->quote($game_id);
$user_name=$dbh->quote($user_name);
$inning=$dbh->quote($inning);
$half_inning=$dbh->quote($half_inning);
$inning_order=$dbh->quote($inning_order);
$ec=$dbh->quote($ec);
$rc=$dbh->quote($rc);
$pc=$dbh->quote($pc);
$erc=$dbh->quote($erc);
$et=$dbh->quote($et);
my $ins=$dbh->do("insert into participant_game (key,game_id,inning,half_inning,inning_order,event_code,role_code,participant_id,event_result_code,event_text) values ($key,$game_id,$inning,$half_inning,$inning_order,$ec,$rc,$pc,$erc,$et)");
return ($ins,$key);
}
which I call like this (in context):
my $recs_line;
foreach $recs_line (@recs) {
my ($ins,$key)=insert_line($dbh,$recs_line);
if (! $ins) {
$error="insert statement failed for $key";
print $repo_fh $recs_line;
err_print($error);
}
}
As I said above, you should not be misled by the relatively humble beginning of the database module. In time it will represent on of the pivotal components of the overall application. But I will get to that in due course.