Modifying Data in the Data File

We are on to our final piece of our database application -- modifying records. Once we are complete, we will have programs that let us add, delete, modify, and search our database file. Next week, we will bring them all together into a complete Web-database program.

Modifying 

Previous articles in this series: Aug. 21:  Working with Data Files Sept. 4:  Searching a Data File Sept. 11:  Deleting Records from a Data File

Click here to see the demo.

Further articles in this series:   Oct. 9:  A Complete Database Application in Perl Oct. 23:  Revisiting the Database Application

We have gone through all of the different functions of a database, except for modifying records. I waited to cover modifying until last because it incorporates much of what we have learned so far. When we modify records, we end up using our search, add, and delete functions.

I want to point out again that although our modify application is fully functional, is not exactly how we are going to implement it in our final database. I did this because it is hard to decide exactly what to do yet. Once we pull the entire database application together, you will see how it all fits. Most of the code will remain intact, it is mostly the supporting code that will change. When I say supporting code, I mean the interactive screens that hold the application together. Also, in our finished database, we will combine subroutines to keep the program relatively small.

This is our longest program yet, so I am not going to waste any time babbling on here. I'll babble enough when I explain the code.

Diving in 

1: #!/usr/bin/perl
2: use CGI qw(:standard);
3: use CGI::Carp qw(fatalsToBrowser);
4: $q = new CGI;
5: print $q->header();
6: $EXCLUSIVE = 2;
7: $UNLOCK    = 8;
8: $database = "temp/datafile.txt";
9: $search_for   = $q->param(search_for);
10: $search_field = $q->param(search_field);
11: @keys         = $q->param(key);
12: &modify_record($q->param(key)) 
        if ($q->param(ready_to_modify) eq "1");
13: $key_matches=@keys;
14: if($key_matches == 1){
15:   $search_field   = "key";
16:   &search_database($q->param(key));
17:   $count = @results;
18:   &multiple_match if ($count > 1);
19:   &print_modify_page;
20:   exit;
21: } # End of if.
22: $search_for = "\." if $search_for eq "";
23: &search_database($search_for);
24: $count = @results;
25: &no_match if($count < 1);
26: &multiple_match if($count > 1);
27: exit;
28: sub search_database{
29:   my $search_for = $_[0];
30:   open(DB, $database) or 
        die "Error opening file: $!\n";
31:     while(<DB>){
32:       if($search_field eq "all"){
33:         if(/$search_for/oi){push @results, $_};
34:       } else {
35:         ($key,$name,$email,$phone,$notes)=split(/\|/,$_);
36:         if(${$search_field} =~ /$search_for/oi)
        {push @results, $_;}
37:       } # End of else.
38:     } # End of while.
39:   close (DB);
40: } # End of subroutine.
41: sub multiple_match{
42:   print $q->start_html(
        -TITLE=>'Multiple Matches',-BGCOLOR=>'white');
43:   print<<HTML;
44:    <FONT SIZE=6 FACE=ARIAL>
45:     <CENTER>Modify Which Record?</CENTER>
46:    </FONT>
47:    <FONT FACE=ARIAL><CENTER>
        There were $count matches</CENTER></FONT>
48:    <FORM METHOD=POST>
49:    <HR WIDTH=75%>
50:    <P>
51:    <CENTER><TABLE BORDER=1 CELLSPACING=0>
52:     <TR BGCOLOR="#e0e0e0">
53:      <TD ALIGN=CENTER>
54:       <FONT SIZE=2 FACE=ARIAL><B>
        Modify</B></FONT></TD>
55:      <TD ALIGN=CENTER><FONT SIZE=2 FACE=ARIAL><B>
        Name</B></FONT></TD>
56:      <TD ALIGN=CENTER><FONT SIZE=2 FACE=ARIAL><B>
        Email</B></FONT></TD>
57:      <TD ALIGN=CENTER><FONT SIZE=2 FACE=ARIAL><B>
        Phone</B></FONT></TD>    
58:     </TR>
59: HTML
60:   foreach $record (@results){
61:     ($key,$name,$email,$phone,$notes)=
        split(/\|/,$record);
62:     print<<HTML;
63:       <TR BGCOLOR="#efefef">
64:        <TD ALIGN=CENTER>
65:         <FONT SIZE=2 FACE=ARIAL>
        <INPUT TYPE=RADIO NAME=key VALUE=$key>
66:        </TD>
67:        <TD><FONT SIZE=2 FACE=ARIAL>
        $name</FONT></TD>        
68:        <TD><FONT SIZE=2 FACE=ARIAL>
        $email</FONT></TD>
69:        <TD><FONT SIZE=2 FACE=ARIAL>
        $phone</FONT></TD>              
70:       </TR>
71: HTML
72:   } # End of foreach loop.
73:   print<<HTML;
74:   <TR BGCOLOR="#e0e0e0">
        <TD COLSPAN=4 ALIGN=CENTER>
75:    <INPUT TYPE=SUBMIT 
        VALUE="Click to Modify Checked Record">
76:   </TD></TR>
77:   </TABLE>
78:   </FORM></BODY></HTML>
79: HTML
80: } # End of subroutine.
81: sub no_match{
82:   print $q->start_html(-
        TITLE=>'No Match',-BGCOLOR=>'white');
83:   print "<H2><CENTER>
        There was no match for <I>$search_for</I>, ";
84:   print "please hit <B>back</B> 
        and try again.</CENTER></H2>";
85:   print $q->end_html;
86: } # End of subroutine.
87: sub delete_record{
88:   $tempfile="$database.tmp";
89:   open (DB,   $database)    or 
        die "Error opening file: $!\n";
90:   open (TEMP, ">$tempfile") or 
        die "Error opening file: $!\n";
91:   flock TEMP, $EXCLUSIVE;
92:   while(<DB>){
93:     $match="";
94:     ($key,$rest)=split(/\|/);
95:     foreach $current (@keys){
96:       if($current == $key){$match=1;}
97:     } # End of foreach loop.
98:    print TEMP $_ unless ($match == 1);
99:   } # End of while loop.
100:   flock DB, $EXCLUSIVE;
101:    unlink($database);
102:    rename($tempfile,$database);
103:   flock DB, $UNLOCK;
104:   flock TEMP, $UNLOCK;
105:   close(TEMP);
106:   close(DB);
107: } # End of subroutine.
108: sub print_message{
109:   print $q->start_html(
        -TITLE=>'Record Modified',-BGCOLOR=>'white');
110:   print <<HTML;
111:   <FONT FACE=ARIAL>
112:    <FONT SIZE=6>
113:     <CENTER>Record Modified!</CENTER>
114:    </FONT>
115:   <HR WIDTH=75%>
116:   <P>
117:   <FONT SIZE=3><CENTER><B>
118:     Back to <A HREF="form.html">
        Modify Form</A>.
119:   </B></CENTER></FONT>
120:   <P>
121:   <HR WIDTH=75%>
122: HTML
123:   print $q->end_html;
124: } # End of subroutine.
125: sub print_modify_page{
126:   ($key,$name,$email,$phone,$notes) = 
        split(/\|/, $results[0]); 
127:   $fs="<FONT SIZE=2 FACE=ARIAL>";
128:   $fc="</FONT>";
129:   print $q->start_html(
        -TITLE=>'Modify Record',-BGCOLOR=>'white'),
130:         $q->start_form;
131:   print $q->h2(
        {-ALIGN=>'center'},'Modify Record'),
132:             hr({-WIDTH=>'75%'});
133:   print<<HTML;
134:    <INPUT TYPE=HIDDEN NAME=key value="$key">
135:    <INPUT TYPE=HIDDEN NAME=ready_to_modify value=1>
136:    <CENTER><TABLE BORDER=1 CELLSPACING=0>
137:     <TR BGCOLOR="e0e0e0">
138:      <TD>$fs<B>Name:</B>$fc</TD>
139:      <TD><INPUT TYPE=TEXT 
        NAME="name" VALUE="$name" SIZE=40></TD>
140:     </TR>
141:     <TR BGCOLOR="e0e0e0">
142:      <TD>$fs<B>E-Mail:</B>$fc</TD>
143:      <TD><INPUT TYPE=TEXT 
        NAME="email" VALUE="$email" SIZE=40></TD>
144:     </TR>
145:     <TR BGCOLOR="e0e0e0">
146:      <TD>$fs<B>Phone #:</B>$fc</TD>
147:      <TD><INPUT TYPE=TEXT 
        NAME="phone" VALUE="$phone" SIZE=40></TD>
148:     </TR>
149:     <TR BGCOLOR="e0e0e0">
150:      <TD>$fs<B>Notes:</B>$fc</TD>
151:      <TD><TEXTAREA 
        NAME="notes" COLS=38 ROWS=4 
        WRAP=PHYSICAL>$notes</TEXTAREA></TD>
152:     </TR>
153:     <TR BGCOLOR="efefef">
154:      <TD COLSPAN=2>
155:       <CENTER><INPUT TYPE=SUBMIT 
        VALUE="Click to modify record"></CENTER>
156:      </TD>
157:     </TR>
158:    </TABLE><P><HR WIDTH=75%></CENTER>
159:   </BODY></HTML>
160: HTML
161: }
162: sub modify_record{
163:  &delete_record;
164:  $key=$_[0];
165:  &add_record;
166:  &print_message;
167:  exit;
168: }
169: sub add_record{
170:   $record = join '|',
171:   $key,$q->param(name),
        $q->param(email),
        $q->param(phone),$q->param(notes);
172:   chomp($record);
173:   open (DB,">>$database") 
        or die "Error: $!\n";
174:    flock DB, $EXCLUSIVE; 
175:     seek DB, 0, 2;
176:     print DB "$record\n";
177:    flock DB, $UNLOCK;
178:   close(DB);
179: }

Line-by-line explanation 

Line 2: Loads the CGI.pm module into the program. The argument in the qw/:standard/ imports the standard functions into the script. These functions are part of the CGI.pm module.

Line 3: Loads the Carp package. Carp is part of the standard CGI.pm distribution and it allows you to get more graceful error messages. By using Carp fatalsToBrowser, we get most of our error messages on our browser rather than getting the nasty "500 Internal Server Error." Using the Carp package can be a very valuable debugging tool, I recommend using it.

Line 4: Creates a new CGI object and calls it $q.

Line 5: Prints the standard header for CGI scripts. The header tells the Web server what kind of data it is sending. This line is equivalent to the following line:

print "Content-type: text/html\n\n";

Lines 6-7: Create variables that we will use with the flock statement. They will be used to lock our files in exclusive mode and to unlock our files. We could skip this and just use the numbers, but when you are reading through the program a month or two from now, it is much harder to remember what the 2 or 8 actually mean.

Line 8: This variable stores the location of our database file. The database file is simply a pipe delimited text file. 

The database file is in a different directory than the programs because the directory that the database file is in must be writeable. When we modify or delete records we create a temporary file. If the directory is not writeable by the "Web" user, then our attempt to create a file will fail. Putting the database file in a separate subdirectory is much safer. If our program directory had full rights, anyone could modify our program -- that would be bad.

Lines 9-11: Get the search information from the calling Web page and stores the results in the appropriate variables. Since we can only modify one record at a time, but initially have the possibility of several matches, we read the keys information into an array. Doing this allows us to traverse the array and present the user with a page that allows them to choose the record to modify. This is much like the delete program, except instead of presenting the data with checkboxes, we use radio buttons. The radio buttons limit the user to one record.

Line 12: Calls the modify_record subroutine if we sent a "hidden" variable called ready_to_modify from the Web page. The ready_to_modify variable gets sent when the record has been modified and we are ready to commit the changes to the database. You'll see it later on.

Line 13: Counts the number of matches in the @keys array and stores the value in $key_matches.

Line 14: Checks to see if $key_matches equals 1. If it does, then we execute the code inside the block.

Line 15: Sets our $search_field variable to key. This tells the search_database subroutine which field to search on.

Line 16: Calls the search_database subroutine and passes it the value of key that was passed from the Web page.

Line 17: Counts the number of matches we had. At this point, we should only have one match because we searched on a known key value.

Line 18: If for some reason we had multiple matches, then we call the multiple_match subroutine.

Line 19: Calls the print_modify_page subroutine. This subroutine reads the record that matched and prints it to our screen so we can modify it.

Line 20: Exits the program. We are done if we reach this point.

Line 21: Closes out if block.

Line 22: Stores a period (.) in the $search_for variable if nothing was passed from the calling Web page. The period matches everything in a regular expression and we use a regular expression in the search subroutine to check for matches. If the user enters nothing to search for, we assume they want everything.

Line 23: Calls the search_database subroutine and passes it the $search_for variable. The $search_for variable stores the information that the user wanted to search for.

We didn't have to pass the $search_for variable to the subroutine because the $search_for variable is a global variable. I chose to pass the variable to show you how to pass variables into subroutines.

Line 24: Our search_database subroutine stores the results of any matches in the @results array. This line sets the $count variable equal to the number of elements in the @results array. This retrieves an accurate count of the number of matches we had as a result of our search.

Line 25: Calls the no_match subroutine because $count is less than 1. This would mean that we had no matches so we need to tell the user.

Line 26: Calls the multiple_match subroutine because $count was greater than 1. 

Line 27: Exits the program, we are done at this point.

Line 28: Begins our search_database subroutine. Those with a keen eye will notice that this subroutine did not change any from the one we used in our database searching and deleting examples.

Line 29: Creates a "private" variable called $search_for. This variable is actually a different variable than the global variable by the same name. A my variable is only valid inside the innermost enclosing block. 

We could modify the value stored in this variable, and it would have no effect on the global variable called $search_for.

I could have called this variable anything I wanted, but I wanted to show you an example of a my variable and give you some information on them.

Line 30: Opens our database and creates a file handle called DB. File handles are simply names (references) that we use to reference the file we opened. Sort of like your name is a reference to you...

Line 31: A while loop that continues looping until it reaches the end of the file. Each time through the file, the current record is stored in Perl's special variable called $_.

Line 32: Begins an if ... else statement. This is the if portion and checks to see if the value of $search_field is equal to all. If it is TRUE, then we do whatever is in its block. Otherwise, we jump down to the else statement and execute what is in its block. We use eq here because we are comparing strings. 

Remember, in Perl you use eq for string comparisons and == for numeric comparisons.

Line 33: A regular expression that checks to see if whatever is in the variable $search_for is in the variable $_. In Perl, $_ is the default value for many things. This line performs a search nearly identical to the one on line 24, but notice there we are looking for something more specific, rather than the default value.

This line is doing quite a bit so I am going to break it down further.

The if(...) portion checks to see if we get a TRUE return value from the expression inside of the parenthesis. If so, it executes the code in its block.

The /$search_for/oi is a regular expression that checks to see if $_ contains the text that is stored in the variable $search_for. The i tells the regular expression to ignore case. The o tells the regular expression to only compile itself once, after that -- it remembers what the value of $search_for was. If we didn't put this on the end, each time through the while loop the regular expression would recompile itself. For large files, this causes a lot of extra unnecessary overhead.

Only use the o option if the value inside of the regular expression does not change. Even if you change the value, if you have the o on the end, Perl will not recompile the regular expression, which can lead to some real confusion if you have to troubleshoot it!

The push @results, $_ inside the if statements block tells Perl to add the current item onto the @results array if it evaluated to TRUE. This adds an element to the end of the array if it already exists, or it creates a new array and adds the item if it didn't exist yet.

Line 34: Our else condition. If the statement on line 20 was false, $search_field was not equal to all, then we execute the code inside of this block.

Line 35: Takes the current record and splits it at the pipe symbols into the respective fields. The split function works on the $_ variable by default so we don't have to specify it.

Line 36: This is our search field, I will break it down further for you because this one is tricky.

Inside the if statement we have ${$search_field} as the variable that is bound, by the binding operator (=~) to the regular expression on the right. The ${$search_field} is kind of neat because instead of having to write an if statement for each of the items we can search on (name, email, phone, and notes) -- I take the value of whatever is stored in the variable we want to search and search for its value. This is kind of difficult to understand, so let me elaborate further:

Let's pretend that I chose to search on name. The value of $search_field should now be set to name because we told Perl to get the value from the calling Web page in line 8. By placing the value of $search_field inside of the ${ }, I actually get the value of the value of the name field. Perl first "translates" the $search_field variable into its value, and then translates the result of that translation into the value of the new variable we just created. 

Okay, we said that we set the value of $search_field to name, this means that after Perl's first translation -- our variable looks like this: ${name} which is the same as writing $name.

After Perl's second pass, the value becomes whatever is stored in the $name variable. Remember that the $name variable was split out in the line above from the current record so it has the value of the name field that was stored in the database for this record. For example, if the current record's name field contained Brent, then the value on the left side of the binding operator would now be Brent.

Whew! I hope you followed that!

On a side note, the curly brackets are not required. I put the curly brackets in for clarity. I could have written $$search_field instead and achieved the same results.

Now that we have the left side straight, the right side contains /$search_for/oi. The $search_for is the text that we are looking for which was set by the calling Web page.

The / / (forward slashes) are Perl's matching operator. They will try to match whatever is inside of them, with whatever they are bound to by the binding operator (=~). If there is no binding operator, they default to matching $_. 

The i on the end tells Perl to ignore case and the o tells Perl not to recompile the expression inside the forward slashes each time through the loop. This can increase the speed of searches if you are searching a lot of records.

If we had a successful match, then we execute the code inside of the if statement's block. We tell it to: push @results, $_. This means push the value that is currently in $_ onto the end of the @results array. The @results array is where we store our successful matches.

Wow! That was a lot of explaining for one line of code.

Line 37: Closes our else block.

Line 38: Closes our while loop.

Line 39: We are done with the database now, so we close it too.

Line 40: Ends our search_database subroutine.

Line 41: Begins our multiple_match subroutine.

Line 42: Uses the start_html function from CGI.pm to begin an HTML document and give it a title and background color.

Lines 43-59: Print out the table heading in HTML that neatly displays the match information to the user. They are printed out using Perl's here document method. A here document begins with a print<<TAG; where everything gets printed until the ending tag.

Line 60: Begins a foreach loop that goes through each item in the @results array and prints it in our nice, neat table. Since we included a variable name, ($record), in our foreach statement, each time through the loop the $record variable gets set to the current value of @results.

Line 61: Splits our record into the various fields. This enables us to easily use the variables in our printout below.

Lines 62-70: Print the data from our matches into the table. Also prints a form radio button that is used to send the key value of the record we want to modify.

Line 71: Closes the here document. The tag must match the tag we opened the here document with and it cannot have any white space. 

Line 72: Ends our foreach loop that puts the matched data into the table.

Lines 73-79: Begins another here document, this one is used to finish off our table and the HTML page that our data is displayed in.

Line 80: Ends our multiple_match subroutine.

Line 81: Begins our no_match subroutine.

Line 82: Uses the start_html function from CGI.pm to begin an HTML document and give it a title and background color.

Lines 83-84: Prints some simple HTML to tell the user that we didn't find any matches.

Line 85: Uses the end_html function from CGI.pm to print the ending HTML tags.

Line 86: Closes our no_match subroutine.

Line 87: Begins our delete_record subroutine.

Line 88: Creates a variable that stores the name of the temporary file we need to create to manipulate the database file.

Line 89: Opens our database file for reading, dies if there was an error opening the file. $! is a special Perl variable that tells us what the error was.

Line 90: Opens our temporary file for writing, dies if there was an error opening the file. $! is a special Perl variable that tells us what the error was. The > tells Perl to create a new file for writing if it doesn't exist yet or to erase the contents of the file if it already exists and "start with a clean slate".

Line 91: Locks our temporary file in exclusive mode. This means that nobody else can open this file for changes until we either end the program or we unlock the file.

We don't lock our database file at this point because we are only reading the file, not making any changes to it. We don't want to tie up a file any longer than we absolutely have to.

Line 92: Begins a while loop to go through each item in the database.

Line 93: Sets our $match variable to nothing. I do this at the start of each iteration of the while loop because on line 96 we set it to 1 if we found a match. If we didn't reset it's value, once we found a match -- all other's would "match" too.

Line 94: Splits the current record into $key and $rest. The only value we are worried about here is the value stored in $key, we really don't need to split the record into every field.

Line 95: Starts a foreach loop that goes through each of the values stored in our @keys array. Each time through the foreach loop, we set the current value to $current. Since we are modifying and not deleting multiple records, there should only be one value in @keys.

Line 96: Checks to see if the value in $current is the value of $key that we split out from the current record on line 94.

Line 97: Ends our foreach loop that goes through @keys.

Line 98: Prints the value of the current record into our TEMP file unless $match is equal to 1.

This means that we print out each record into the temporary database file unless we had a match. Doing this effectively eliminates the records that we wanted to modify.

Line 99: Ends our while loop that went through each record in the database.

Line 100: Locks our database file in exclusive mode.

Line 101: Deletes our database file!

Line 102: Renames the temporary file to the name of our regular database file. Remember that the temporary file had everything the regular database file had except the records we wanted to modify.

Line2 103-104: Unlocks our database files.

Lines 105-106: Close both the database file and the temporary file.

Line 107: Ends our delete_records subroutine.

Line 108: Begins our print_message subroutine.

Line 109: Uses the start_html function from CGI.pm to begin an HTML document and give it a title and background color.

Lines 110-122: Begins a here document that prints out some information to the user letting them know how many records were deleted and provides them with a link out.

Line 123: Uses the end_html function from CGI.pm to print the ending HTML tags.

Line 124: Ends our print_message subroutine.

Line 125: Begins our print_modify_page subroutine.

Line 126: Splits the first record in @results. The first record is the record we want to modify. It should also be the only record in the array.

Lines 127-128: Sets a few variables that we'll use later to save us some typing.

Lines 129-132: Use the functions from CGI.pm to print out the beginning HTML tags and the start of our page.

Lines 133-160: Prints the rest of the HTML page that displays the record the user wants to modify. This page allows the user to make changes to the record.

I didn't use all of the functions in CGI.pm to create this page. I could have, but there are many cases where I feel that it is easier to just type out the HTML. This is such a case.

Line 161: Ends our print_modify_page subroutine.

Line 162: Begins our modify_record subroutine.

Line 163: Calls the delete_record subroutine. Yes, we first delete the record, but don't worry -- we "remember" what the new record is supposed to contain. We read the new records values from the calling Web page before we get to this point.

Line 164: Sets the value of $key to whatever was passed to the subroutine. If you don't remember, when you pass values to a subroutine, Perl stores those values in an array called @_.

Line 165: Calls the add_record subroutine. This will add the "new" value for our record into the database.

Line 166: Calls the print_message subroutine to tell the user that they were successful.

Line 167: Exits the program, we are done.

Line 168: Closes our modify_record subroutine.

Line 169: Begins our add_record subroutine.

Lines 170-171: Use Perl's join function to piece the fields into a record and store the results in a variable called $record.

Line 172: Uses Perl's chomp function to remove any carriage returns that may be on the end of the record.

Line 173: Opens the database in "append" mode. Append mode is opened with the double greater than >>. It leaves the contents in the file and appends whatever else we add to the end of the file.

Line 174: Locks the database in "exclusive" mode so that nobody else can modify the database until we are done with it.

Line 175: Seeks to the end of the database file, just in case someone else did some modifications to it before we were able to lock it.

Line 176: Prints the modified record to the database.

Line 177: Unlocks the database; now others can use it.

Line 178: Closes the database.

Line 179: Closes our add_record subroutine.

Wrapping it up 

One important note, if you are implementing this program on a Windows 95/98 box, the flock function does not work. This could lead to possible data corruption if multiple people try to write to the database at the exact same time. We have minimized this possibility by opening the database before we need it, and closing it immediately after we are done with it.

We are getting close to having a complete database application. Next week, we will put it all together into a complete database application!
 
 

Source Code for Modifying Data in the Data File
View and download the source code for this week's article.

Next: A Complete Database Application in Perl
Prev: Deleting Records from a Data File