BBS水木清华站∶精华区
发信人: yyh (阿欢&正在努力上进中......), 信区: Linux
标 题: MySQL & mSQL Chapter 10 Perl(2)
发信站: BBS 水木清华站 (Sat Sep 18 01:43:47 1999)
This function displays a form allowing the user to choose a subject for the test along with the number of questions and a name. In order to print out a list of available subjects, the table of subjects is queried. When using a SELECT query with DBI, the
query must first be prepared and then executed. The DBI::prepare function is useful with certain database servers which allow you to perform operations on prepared queries before executing them. With MySQL and mSQL however, it simply stores the query
until the DBI::execute function is called.
The output of this function is sent to the add2 function as shown in the following:
sub add2 {
my $subject = param('subjects');
my $num = param('num');
$name = param('name') if param('name');
my $out = $dbh->prepare("select name from subject where id=$subject");
$out->execute;
my ($subname) = $out->fetchrow_array;
print header, start_html('title'=>"Creating test for $subname",
'BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>Creating test for $subname</h1>
<h2>$name</h2>
<p>
<FORM ACTION="test.cgi" METHOD=POST>
<INPUT TYPE=HIDDEN NAME="action" VALUE="add3">
<INPUT TYPE=HIDDEN NAME="subjects" VALUE="$subject">
<INPUT TYPE=HIDDEN NAME="num" VALUE="$num">
<INPUT TYPE=HIDDEN NAME="name" VALUE="$name">
Enter the point value for each of the questions. The points need not
add up to 100.
<p>
END_OF_HTML
for (1..$num) {
print qq%$_: <INPUT NAME="q$_" SIZE=3> %;
if (not $_ % 5) { print "<br>\n"; }
}
print <<END_OF_HTML;
<p>
Enter the text of the test:<br>
<TEXTAREA NAME="test" ROWS=20 COLS=60>
</textarea>
<p>
<INPUT TYPE=SUBMIT VALUE="Enter Test">
<INPUT TYPE=RESET>
</form></body></html>
END_OF_HTML
}
In this function, a form for the test is dynamically generated based on the parameters entered in the last form. The user can enter the point value for each question on the test and the full text of the test as well. The output of this function is then
sent to the final function, add3, as shown in the following:
sub add3 {
my $subject = param('subjects');
my $num = param('num');
$name = param('name') if param('name');
my $qname;
($qname = $name) =~ s/'/\\'/g;
my $q1 = "insert into test (id, name, subject, num) values (
'', '$qname', $subject, $num)";
my $in = $dbh->prepare($q1);
$in->execute;
# Retrieve the ID value MySQL created for us
my $id = $in->insertid;
my $query = "create table t$id (
id INT NOT NULL,
";
my $def = "insert into t$id values ( 0, ";
my $total = 0;
my @qs = grep(/^q\d+$/,param);
foreach (@qs) {
$query .= $_ . " INT,\n";
my $value = 0;
$value = param($_) if param($_);
$def .= "$value, ";
$total += $value;
}
$query .= "total INT\n)";
$def .= "$total)";
my $in2 = $dbh->prepare($query);
$in2->execute;
my $in3 = $dbh->prepare($def);
$in3->execute;
# Note that we store the tests in separate files. This is
# useful when dealing with mSQL because of its lack of BLOBs.
# (The TEXT type provided with mSQL 2 would work, but
# inefficently.)
# Since we are using MySQL, we could just as well
# stick the entire test into a BLOB.
open(TEST,">teach/tests/$id") or die("A: $id $!");
print TEST param('test'), "\n";
close TEST;
print header, start_html('title'=>'Test Created',
'BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>Test Created</h1>
<p>
The test has been created.
<p>
<A HREF=".">Go</a> to the Teacher's Aide home page.<br>
<A HREF="test.cgi">Go</a> to the Test main page.<br>
<A HREF="test.cgi?action=add">Add</a> another test.
</body></html>
END_OF_HTML
}
Here we enter the information about the test into the database. In doing so we take a step beyond the usual data insertion that we have seen so far. The information about the test is so complex that each test is best kept in a table of its own.
Therefore, instead of adding data to an existing table, we have to create a whole new table for each test. First we create an ID for the new test using MySQL auto increment feature and enter the name and ID of the test into a table called test. This
table is simply an index of tests so that the ID number of any test can be quickly obtained. Then we simultaneously create two new queries. The first is a CREATE TABLE query which defines our new test. The second is an INSERT query that populates our
table with the maximum score for each question. These queries are then sent to the database server, completing the process (after sending a success page to the user). Later, after the students have taken the test, each student will get an entry in the
test table. Then entries can then be compared to the maximum values to determine the student's score.
Msql.pm
The Msql.pm module is the original Perl interface to mSQL. While it has been replaced by the DBI modules, there are still many sites that depend on this old interface. To illustrate the use of Msql.pm, we will continue the teacher's aide example.
Since we need classes in which to give the tests, let's examine the table of subjects. The table structure looks like this:
CREATE TABLE subject (
id INT NOT NULL,
name CHAR(500),
teacher CHAR(100)
)
CREATE UNIQUE INDEX idx1 ON subject (
id,
name,
teacher
)
CREATE SEQUENCE ON subject
The id number is a unique identifier for the class, while the name and teacher fields are the name of the course and the name of the teacher respectively. There is also an index of all three of the fields that speeds up queries. Finally, we define a
sequence for the table. The ID numbers are generated by this sequence.
The CGI program to access and manipulate this data must to several things.
Search for a subject in the database.
Show the subject that is the result of a search.
Add a new subject to the database.
Change the values of a subject in the database.
With the power of Perl and mSQL, we can easily consolidate all of these functions into one file, subject.cgi. We can do this by separating each operation into its own function. The main portion of the program will be a switchboard of sorts that directs
incoming requests to the proper function. We will describe the actions themselves later.
# Each of the different parts of the script is selected via the
# 'action'
# parameter. If no 'action' is supplied, the default() function is
# called.
# Otherwise the appropriate function is called.
&default if not param('action');
# This trick comes from Camel 2 and approximates the 'switch'
# feature of C.
foreach[AO4] (param('action')) {
/view/ and do { &view; last; };
/add$/ and do { &add; last; };
/add2/ and do { &add2; last; };
/add3/ and do { &add3; last; };
/add4/ and do { &add4; last; };
/schange$/ and do { &schange; last; };
/schange2/ and do { &schange2; last; };
/lchange$/ and do { &lchange; last; };
/lchange2/ and do { &lchange2; last; };
/lchange3/ and do { &lchange3; last; };
/delete/ and do { &delete; last; };
&default;
}
TIP: The "add," "schange," and "lchange" entries must have an anchoring "$" in the regular expression so that they do not match the other functions similar to them. Without the "$", "add" would also match add2, add3 and add4. An alternative method
would be to place "add," "schange," and "lchange" after the other functions. That way they would only be called if none of the others matched. However, this method could cause trouble if other entries are added later. A third method would be to
completely disambiguate all of the entries using /^view$/, /^add$/, etc. This involves slightly more typing but removes all possibility of error.
Now all we have to do is fill in the details by implementing each function.
The default function prints out the initial form seen by the user. This is the form that allows the user to choose which action to perform. This function is called if the CGI program is accessed without any parameters, as with
http://www.myserver.com/teach/subject.cgi, or if the ACTION parameter does not match any of the existing functions. An alternative method would be to create a function that prints out an error if the ACTION parameter is unknown.
sub default {
print header, start_html('title'=>'Subjects','BGCOLOR'=>'white');
print <<END_OF_HTML;
<h1>Subjects</h1>
<p>Select an action and a subject (if applicable).
<FORM ACTION="subject.cgi" METHOD=POST>
<p><SELECT NAME="action">
<OPTION VALUE="view">View a Subject
<OPTION value="add">Add a Subject
<OPTION value="schange">Modify a Subject
<OPTION value="lchange" SELECTED>Modify a Class List
<OPTION value="delete">Delete a Subject
</select>
END_OF_HTML
# See 'sub print_subjects' below.
&print_subjects;
print <<END_OF_HTML;
<p>
<INPUT TYPE=SUBMIT VALUE=" Perform Action ">
<INPUT TYPE=RESET>
</form></body></html>
HTML
}
There are five main actions: "view," "add," "schange" (change the information about a subject), "lchange" (change the class list for a subject), and "delete". For illustration, we will examine the "add" action in detail here. The "add" action is broken
up into four separate functions because interaction with the user is required up to four times. Hidden variables are used to pass information from form to form until the class is finally created.
The first add function generates the form used to enter the initial information about the class, including its name, the teacher's name, and the number of students in the class.
sub add {
my (%fields);
foreach ('name','size','teacher') {
if (param($_)) { $fields{$_} = param($_); }
else { $fields{$_} = ""; }
}
print header, start_html('title'=>'Add a Subject','BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>Add a Subject</h1>
<form METHOD=POST ACTION="subject.cgi">
<p>
Subject Name: <input size=40 name="name" value="$fields{'name'}"><br>
Teacher's Name: <input size=40 name="teacher" value="$fields{'teacher'}"><br>
Number of Students in Class: <input size=5 name="size" value="$fields{'size'}">
<p>
<INPUT TYPE=HIDDEN NAME="action" VALUE="add2">
<INPUT TYPE=SUBMIT VALUE=" Next Page ">
<INPUT TYPE=RESET>
</form>
<p>
<A HREF="subject.cgi">Go</a> back to the main Subject page.<br>
<A HREF=".">Go</a> to the Teacher's Aide Home Page.
</body></html>
END_OF_HTML
}
The function checks to see if any of the fields have preassigned values. This adds extra versatility to the function in that it can now be used as a template for classes with default values--perhaps generated by another CGI program somewhere.
The values from the first part of the add process are passed back to CGI program into the add2 function. The first thing that add2 does is check whether the class already exists. If it does, an error message is sent to the user and he or she can change
the name of the class.
If the class does not already exist, the function checks how many students were entered for the class. If none were entered, the class is created without any students. The students can be added later. If the number of students was specified, the class
is created and a form is displayed where the user can enter the information about each student.
sub add2 {
...
my $name = param('name');
# We need one copy of the name that is encoded for the URL.
my $enc_name = &cgi_encode($name);
# We also need a copy of the name that is quoted safely for insertion
# into the database. Msql provides the Msql::quote() function for that
# purpose.
my $query_name = $dbh->quote($name);
# We now build a query to see if the subject entered already exists.
my $query =
"select id, name, teacher from subject where name=$query_name";
# If the user supplied a teacher's name, we check for that teacher
# specifically, since there can be two courses with the same name but
# different teachers.
if (param('teacher')) {
$teacher = param('teacher');
$enc_teacher = &cgi_encode($teacher);
my $query_teacher = $dbh->quote($teacher);
$query .= " and teacher=$query_teacher";
}
# Now we send the query to the mSQL server.
my $out = $dbh->query($query);
# We check $out->numrows to see if any rows were returned. If
# there were any, and the user didn't supply an 'override'
# parameter, then we exit with a message that the class already
# exists, and giving the user a change to enter the class anyway
# (by resubmitting the form with the 'override' parameter set.
if ($out->numrows and not param('override')) {
# Print 'Class already exists' page.
...
} else {
# Now we enter the information into the database.
# First, we need to select the next number from the
# table's sequence.
$out = $dbh->query("select _seq from subject");
my ($id) = $out->fetchrow;
# Then we insert the information into the database, using
# the sequence number we just obtained as the ID.
$query = "INSERT INTO subject (id, name, teacher)
VALUES ($id, '$name', '$teacher')";
$dbh->query($query);
# If the user did not specify a class size, we exit with
# a message letting the user know that he or she can add
# students later.
if (not param('size')) {
# Print success page.
...
} else {
# Now we print a form, allowing the user to enter the
# names of each of the students in the class.
print header, start_html('title'=>'Create Class List',
'BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>Create Class List</h1>
<P>
<B>$name</b> has been added to the database. You can
now enter the names of the students in the class.
You may add or drop students later from the
<a href="subject.cgi">main
Subject page</a>.
<p>
<FORM METHOD=POST ACTION="subject.cgi">
<INPUT TYPE=HIDDEN NAME="action" VALUE="add3">
<INPUT TYPE=HIDDEN NAME="id" VALUE="$id">
<TABLE BORDER=0>
<TR><TH><TH>First Name<TH>Middle Name/Initial
<TH>Last Name<TH>Jr.,Sr.,III,etc
</tr>
END_OF_HTML
for $i (1..$size) {
print <<END_OF_HTML;
<TR><TD>$i<TD><INPUT SIZE=15 NAME="first$i"><TD><INPUT SIZE=15
NAME="middle$i">
<TD><INPUT SIZE=15 NAME="last$i"><TD><INPUT SIZE=5
NAME="ext$i"></tr>
END_OF_HTML
}
print <<END_OF_HTML;
</table>
<INPUT TYPE=SUBMIT VALUE=" Submit Class List ">
<INPUT TYPE=RESET>
</form></body></html>
END_OF_HTML
}
}
}
Note that the function used three copies of the name parameter. To use a variable as part of a URL, all special characters must be URL-escaped. A function called cgi_encode is provided with the code for this example which performs this operation.
Secondly, to insert a string into the mSQL database, certain characters must be escaped. The MsqlPerl interface provides the function quote--accessible through any database handle--to do this. Finally, an unescaped version of the variable is used when
displaying output to the user.
When adding the class to the database, mSQL's sequence feature comes in handy. Remember that a sequence was defined on the class table. The values of this sequence are used as the unique identifiers for each class. In this way two classes can have the
same name (or same teacher, etc.) and still be distinct. This also comes in handy when modifying the class later. As long as the unique ID is passed from form to form, any other information about the class can safely be changed.
Finally, notice that the student entry form displayed by this function is dynamically generated. The number of students entered for the class is used to print out a form with exactly the right number of entries. Always remember that the CGI program has
complete control over the generated HTML. Any part, including the forms, can be programmatically created.
If the user did not enter any students for the class, we are now finished. The user can use the change feature to add students later. However, if students were requested, the information about those students is passed onto the stage in the add3
function, as shown in the following:
sub add3 {
if (not param('id')) { &end("An ID number is required"); }
my $id = param('id');
my @list = &find_last_student;
my ($ref_students,$ref_notstudents) =
&find_matching_students(@list);
@students = @$ref_students if $ref_students;
@notstudents = @$ref_notstudents if $ref_notstudents;
if (@notstudents) {
# Print form telling the user that there are nonexisting
# students in the list. The user can then automatically create
# the students or go back and fix any typos.
...
} else {
&update_students($id,@students);
# Print success page.
...
}
}
The bulk of this function's work is performed by other functions. This is because other parts of the CGI program have similar needs so it is efficient to factor the common code into shared functions. The first such function is find_last_student, which
examined the form data and returns a list of the form numbers--the form numbers are not related to the ID numbers in the database--of each student entered by the user. This is necessary because, as mentioned earlier, the previous form is dynamically
generated and there is no way to immediately know how many students are included.
sub find_last_student {
my @params = param;
my @list = ();
foreach (@params) {
next if not param($_); # Skip any 'empty' fields
if (/^(first|middle|last|ext)(\d+)/) {
my $num = $2;
if (not grep(/^$num$/,@list)) { push(@list,$num); }
}
}
@list = sort { $a <=> $b} @list;
return @list;
}
Note that the function returns all of the numbers, not just the last number--which would presumably be the number of students entered. Even though the previous form printed out the number of entries the user requested, there is no guarantee that the
user filled all of them out. He or she may have missed or skipped a row, which would not be included with the form data. Therefore, it is necessary to find out each number that was entered. The output of this function is then sent to the next "helper"
function: find_matching_students, as shown in the following:
sub find_matching_students {
my @list = @_;
my ($i,@students,@notstudents);
@students = ();
@notstudents = ();
if (@list) {
foreach $i (@list) {
my @query = ();
# Build a query that looks for a specific student.
my $query = "select id, subjects from student where ";
foreach ('first','middle','last','ext') {
if (param("$_$i")) {
my $temp = param("$_$i");
# Single quotes are the field delimiters for mSQL (and MySQL),
# so they must be preceded with the escape character "\",
# which is escaped itself so that it is interpreted literally.
$temp =~ s/'/\\'/g;
push(@query,"$_ = '$temp'");
}
}
$query .= join(" and ",@query);
# Send the query to the database.
my $out = $dbh->query($query);
# If the database doesn't return anything, add the
# student to the @notstudents array.
if (not $out->numrows) {
push(@notstudents,[ param("first$i"),
param("middle$i"),
param("last$i"), param("ext$i") ]);
# Otherwise add the student to the @students array.
} else {
my ($id,$subjects) = $out->fetchrow;
push(@students,[$id,$subjects]);
}
}
}
return(\@students,\@notstudents);
}
This function goes through each of the given student names and checks the database to see if they already exist. If they do exist their information is stored in an array called @students, otherwise they are put in @notstudents. The information about
each student is kept in an anonymous array, creating a student object of sorts. Finally the function returns references to both of the arrays. It cannot return the data as regular arrays because there would be no way to tell where one array ended and
the other began.
The final helper function is update_students, which adds the class to each existing student's list of classes.
sub update_students {
my $id = shift;
my @students = @_;
foreach (@students) {
my($sid,$subjects)=@$_;
if (not $subjects) { $subjects = ":$id:"; }
elsif ($subjects !~ /:$id:/) { $subjects .= "$id:"; }
my $query = "update student set subjects='$subjects'
where id=$id";
$dbh->query($query);
}
}
This function queries the student table, which is entirely separate from the subject table. Within a single CGI program, you can interact with any number of different tables within a database. You can even switch between databases, but you can only
have one database selected at a time. This function retrieves the subject list for each given student and adds the new subject to their list if it is not there already.
At this point all contingencies are taken care of except for the case where the subject has students that do not already exist in the student table. For this case, the list of new students are sent to the add4 function as shown in the following:
sub add4 {
# Get list of @students and @notstudents
&update_students($id,@students) if @students;
&insert_students($id,@notstudents) if @notstudents;
# Print success page.
}
This function separates the list of students into existing and nonexisting students using the same method as add3. It then updates the existing students using update_students shown earlier. Nonexisting students, shown in the following, are sent to the
new helper function insert_students:
sub insert_students {
foreach $i (@list) {
# This selects the next number in the sequence defined on the
# table. We then use this number as the ID of the student.
my $out = $dbh->query('select _seq from student');
my($sid) = $out->fetchrow;
# We have to quote all of the text strings for inclusion
# in the database.
my ($first, $middle, $last, $ext) = (
$dbh->quote(param("first$i")),
$dbh->quote(param("middle$i")),
$dbh->quote(param("last$i")),
$dbh->quote(param("ext$i"))
);
my $query = "insert into student (id, first, middle, last,
ext, subjects) VALUES ($sid, $first, $middle,
$last, $ext, ':$id:')";
$dbh->query($query);
}
}
This function again accesses the student table rather than the subject table. An ID number for the new students is retrieved from the sequence defined on the student table, and then the student is inserted into the table using that ID.
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.17.153]
BBS水木清华站∶精华区