 |
|
|
|
 |
||
|
||
|
|
|
|
Deleting Records from a Data Fileby Brent MichalskiSept. 11, 1998 What database would
be complete without a way to delete records? This week we re-use much of
the code from last week's search
program and turn it into an program that allows us to delete records.
Deleting
Now that we know how to search for records in the database, it is time we learn how to delete them too. We had to be able to search before we learned how to delete because we want to make sure that we only delete the records we intend to. It wouldn't do us much good if we deleted arbitrary records! I want to point out that although the delete program is fully functional, is not exactly how we are going to implement it in the final database application. 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 currently hold the application together. Also, in the finished application, we will combine as many subroutines as we can to keep the programs as small as possible. Diving inThe delete example this week builds upon the search script from last week. Most of the code is actually the same but with some fancier HTML and a delete_records subroutine.I have numbered the lines of code, the line numbers are not part of the program. You can also see the program without the line numbers. The line numbers simply make it easier for me to talk about the program. 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: $key_matches=@keys;
13: if($key_matches > 0){
14: &delete_records;
15: &print_message;
16: exit;
17: } # End of if.
18: $search_for = "\." if $search_for eq "";
19: &search_database($search_for);
20: $count = @results;
21: &no_match if($count < 1);
22: &multiple_match if($count > 0);
23: exit;
24: sub search_database{
25: my $search_for = $_[0];
26: open(DB, $database) or
die "Error opening file: $!\n";
27: while(<DB>){
28: if($search_field eq "all"){
29: if(/$search_for/oi){push @results, $_};
30: } else {
31: ($key,$name,$email,$phone,$notes)=
split(/\|/,$_);
32: if(${$search_field} =~ /$search_for/oi)
{push @results, $_};
33: } # End of else.
34: } # End of while.
35: close (DB);
36: } # End of subroutine.
37: sub multiple_match{
38: print $q->start_html(
-TITLE=>'Multiple Matches',
-BGCOLOR=>'white');
39: print<<HTML;
40: <FONT SIZE=6 FACE=ARIAL>
41: <CENTER>Delete Which Record(s)?</CENTER>
42: </FONT>
43: <FONT FACE=ARIAL><CENTER>
There were $count matches
</CENTER></FONT>
44: <FORM METHOD=POST>
45: <HR WIDTH=75%>
46: <P>
47: <CENTER><TABLE BORDER=1 CELLSPACING=0>
48: <TR BGCOLOR="#e0e0e0">
49: <TD ALIGN=CENTER>
50: <FONT SIZE=2 FACE=ARIAL>
<B>Delete</B></FONT></TD>
51: <TD ALIGN=CENTER><FONT SIZE=2 FACE=ARIAL>
<B>Name</B></FONT></TD>
52: <TD ALIGN=CENTER><FONT SIZE=2 FACE=ARIAL>
<B>Email</B></FONT></TD>
53: <TD ALIGN=CENTER><FONT SIZE=2 FACE=ARIAL>
<B>Phone</B></FONT></TD>
54: </TR>
55: HTML
56: foreach $record (@results){
57: ($key,$name,$email,$phone,$notes)=
split(/\|/,$record);
58: print<<HTML;
59: <TR BGCOLOR="#efefef">
60: <TD ALIGN=CENTER>
61: <FONT SIZE=2 FACE=ARIAL><INPUT
TYPE=CHECKBOX NAME=key VALUE=$key>
62: </TD>
63: <TD><FONT SIZE=2 FACE=ARIAL>
$name</FONT></TD>
64: <TD><FONT SIZE=2 FACE=ARIAL>
$email</FONT></TD>
65: <TD><FONT SIZE=2 FACE=ARIAL>
$phone</FONT></TD>
66: </TR>
67: HTML
68: } # End of foreach loop.
69: print<<HTML;
70: <TR BGCOLOR="#e0e0e0"><TD COLSPAN=4
ALIGN=CENTER>
71: <INPUT TYPE=SUBMIT VALUE=
"Click to Delete Checked Records">
72: </TD></TR>
73: </TABLE>
74: </FORM></BODY></HTML>
75: HTML
76: } # End of subroutine.
77: sub no_match{
78: print $q->start_html(-TITLE=>'No Match',
-BGCOLOR=>'white');
79: print "<H2><CENTER>There was no match for
<I>$search_for</I>, ";
80: print "please hit <B>back</B> and try again.
</CENTER></H2>";
81: print $q->end_html;
82: } # End of subroutine.
83: sub delete_records{
84: $tempfile="$database.tmp";
85: open (DB, $database) or
die "Error opening file: $!\n";
86: open (TEMP, ">$tempfile") or
die "Error opening file: $!\n";
87: flock TEMP, $EXCLUSIVE;
88: while(<DB>){
89: $match="";
90: ($key,$rest)=split(/\|/);
91: foreach $current (@keys){
92: if($current == $key){$match=1;}
93: } # End of foreach loop.
94: print TEMP $_ unless ($match == 1);
95: } # End of while loop.
96: flock DB, $EXCLUSIVE;
97: unlink($database);
98: rename($tempfile,$database);
99: flock DB, $UNLOCK;
100: flock TEMP, $UNLOCK;
101: close(TEMP);
102: close(DB);
103: } # End of subroutine.
104: sub print_message{
105: print $q->start_html(
-TITLE=>'Record(s) Deleted',
-BGCOLOR=>'white');
106: print <<HTML;
107: <FONT FACE=ARIAL>
108: <FONT SIZE=6>
109: <CENTER>$key_matches Record(s) Deleted!
</CENTER>
110: </FONT>
111: <HR WIDTH=75%>
112: <P>
113: <FONT SIZE=3><CENTER><B>
114: Back to <A HREF="form.html">Delete Form</A>.
115: </B></CENTER></FONT>
116: <P>
117: <HR WIDTH=75%>
118: HTML
119: print $q->end_html;
120: } # End of subroutine.
Line-by-line explanationLine 1: Tells the program where to find Perl on the Web server. This line will vary depending on where Perl is installed on your server so you need to make any necessary changes. On a UNIX server, this line is required. If you are running this program on an NT server, this line is not required but won't hurt anything if included.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 the error messages on the browser rather than getting the nasty "500 Internal Server Error" messages. 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 the files in exclusive mode and to unlock the 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 the database file. The database file is simply a pipe (|) delimited text file. In the search program we stored the database in the same directory that the program was in. I had to change that and move the database file to a different directory because the directory that the database file is in must be writeable because we create a temporary file when we delete or modify records. Putting the database file in a separate subdirectory is much safer. If the program directory had full rights, anyone could modify the 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 delete multiple records by choosing them with the checkboxes, we need to read the keys information into an array. Doing this allows us to traverse the array and delete all of the items that the user checked. Line 12: Gets the number of elements in the @keys array. This is the number of elements that we want to delete. Line 13: If the array is not empty, we call the delete_records subroutine and print the results to the user - then exit the program. If the array is empty, we skip down to the search_database subroutine and find the records that we want to delete. Line 14: Calls the delete_records subroutine. Line 15: Calls the print_message subroutine. Line 16: Exits the program. If we were deleting records, we are done at this point. Line 17: Ends the if statement. Line 18: 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 19: 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 20: The 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 the search. Line 21: 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 22: Calls the multiple_match subroutine because $count was greater than 0. We omitted the single_match condition that we had included in the search program. I did this because even if we find just one match, it gives the user an option of backing out of their decision to delete the record. They have this option because the multiple_match subroutine forces them to choose the item that they want to delete by checking a check box, even if it is only one item. Line 23: Exits the program, we are done at this point. Line 24: Begins the search_database subroutine. Those with a keen eye will notice that this subroutine did not change any from the one we used in the database searching example. Line 25: Creates a private variable called $search_for. This variable is a different variable than the global variable by the same name. A my variable is only valid inside the innermost enclosing block where it is declared. 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 about them. Line 26: Opens the 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 27: A while loop that continues looping until it reaches the end of the file. Each time through the file, the current line, in this case a single record in the database, is stored in Perl's special variable $_ Line 28: Begins an if ... else statement. This is the if portion which checks to see if the value of $search_field is equal to all. If it is, the if statement is TRUE, and 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 that in Perl you use eq for string comparisons and == for numeric comparisons. Line 29: 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 statements and expressions. This line performs a search nearly identical to the one on line 24, but notice that 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. Note: Only use the compile once (o) option if the value inside of the regular expression (in this case $search_for) does not change. If you do change the value, the o on the end will cause Perl not to recompile the regular expression, and thus not change the value inside the regular expression. This will lead to some real confusion if you have to troubleshoot it! The push @results, $_ inside the if statement's block tells Perl to add (push) the current item ($_) onto the @results array if the if statement evaluated to TRUE. This adds an element to the end of the array if it already exists, or it creates a new array and containing the item if it didn't exist yet. Line 30: The else condition. If the if statement on line 20 was false, $search_field was not equal to all, then we execute the code inside of this block. Line 31: Takes the current record and splits it at the pipe symbols into its respective fields. The split function works on the $_ variable by default so we don't have to specify it. Line 32: This is the 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 fields 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.On to the right side of the binding operator... Now that we have the left side straight, the right side contains /$search_for/oi The value of $search_for is the text that we are looking for which was entered in the form on the calling Web page. The / /, or 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 the successful matches. Wow! That was a lot of explanation for one line of code! Line 33: Closes the else block. Line 34: Closes the while loop. Line 35: We are done with the database now, so we close it, via the filehandle, as well. Line 36: Ends the search_database subroutine. Line 37: Begins the multiple_match subroutine. Line 38: Uses the start_html function from CGI.pm to begin an HTML document and give it a title and background color. Lines 39-55: 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 syntax. A here document begins with a print<<TAG; where everything gets printed until the ending TAG. Line 56: Begins a foreach loop that goes through each item in the @results array and prints it in the nice, neat table. Since we included a variable name, ($record), in the foreach statement, each time through the loop the $record variable gets set to the current value of @results. Line 57: Splits the record into the various fields. This enables us to easily use the variables in the printout below. Lines 58-66: Print the data from the matches into the table. Also prints a form checkbox that is used to send the key values of each record we want deleted. Line 67: Closes the here document. The tag must match the tag we opened the here document with and it cannot have any white space. Line 68: Ends the foreach loop that puts the matched data into the table. Lines 69-75: Begins another here document, this one is used to finish off the table and the HTML page that the data is displayed in. Line 76: Ends the multiple_match subroutine. Line 77: Begins the no_match subroutine. Line 78: Uses the start_html function from CGI.pm to begin an HTML document and give it a title and background color. Lines 79-80: Prints some simple HTML to tell the user that we didn't find any matches. Line 81: Uses the end_html function from CGI.pm to print the ending HTML tags. Line 82: Closes the no_match subroutine. Line 83: Begins the delete_records subroutine. Line 84: Creates a variable that stores the name of the temporary file we need to create to manipulate the database file Line 85: Opens the database file for reading, dies if there was an error opening the file. $! is a special Perl variable that contains the error message text. Line 86: Opens the temporary file for writing, dies if there was an error opening the file. 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 87: Locks the 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 the 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 88: Begins a while loop to go through each item in the database. Line 89: Sets the $match variable to nothing. I do this at the start of each iteration of the while loop because on line 92 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 90: 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 91: Starts a foreach loop that goes through each of the values stored in the @keys array. The @keys array is the values of all of the checkboxes that were checked on the calling Web page. Each time through the foreach loop, we set the current value to $current. Line 92: Checks to see if the value in $current is the value of $key that we split out from the current record on line 90. Line 93: Ends the foreach loop that goes through @keys. Line 94: Prints the value of the current record into the 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 delete. Line 95: Ends the while loop that went through each record in the database. Line 96: Locks the database file in exclusive mode. Line 97: Deletes the database file. Line 98: Renames the temporary file to the name of the regular database file. Remember that the temporary file had everything the regular database file had except the records we wanted to delete. Line 99-100: Unlocks the database files. Lines 101-102: Close both the database file and the temporary file. Line 103: Ends the delete_records subroutine. Line 104: Begins the print_message subroutine. Line 105: Uses the start_html function from CGI.pm to begin an HTML document and give it a title and background color. Lines 106-118: 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 119: Uses the end_html function from CGI.pm to print the ending HTML tags. Line 120: Ends the print_message subroutine. Wrapping it upThere were quite a few lines in this program but many of them were basic HTML code.One important note: If you are implementing this program on a Windows 95/98 system, you need to remove the references to flock. flock is not supported on the Windows 95/98 platform. This opens up the remote possibility of data corruption if more than one user tries to write to the file at the exact same time. We have minimized this risk by opening when we need them and closing them as soon as we are finished with them. We have nearly completed the database application. Next week we will cover modifying records and the following week we'll put all the pieces together.
Source Code for Deleting
Records from a Data File
Next: Modifying Data in the Data File
|
|
|
|
|
Web Techniques and Web Design and Development copyright © 1995-99 Miller Freeman, Inc. ALL RIGHTS RESERVED |
|
