[Perl] MySQL 과 Perl

Chapter 10: Perl

The Perl programming language has gone from a tool primarily used by Unix systems administrators to the most widely used development platform for the World Wide Web. Perl was not designed for the web, but its ease of use and powerful text handling abilities have made it a natural for CGI programming. Similarly, when mSQL first entered the scene, its extremely small footprint and execution time were very attractive to web developers who needed to serve thousands of transactions a day. MySQL, with its enhanced speed and capabilities provided an even greater incentive for web developers. Therefore, it was only natural that a Perl interface to both MySQL and mSQL was developed that allowed the best of both worlds.

TIP: At the time of this writing there are two interfaces between MySQL and mSQL and Perl. The original consists of Mysql.pm and Msql.pm, custom interfaces that work only with MySQL and mSQL, respectively. The other, newer, interface is a plug-in for the Database Independent (DBI) set of modules. DBI is an attempt to provide a common Perl API for all database accesses and enable greater behind-the-scenes portability. The DBI interface has become the most robust and standard, and the makers of MySQL recommend that all work be done using DBI as development of the Mysql.pm and Msql.pm modules has ceased. However, many legacy systems still use these modules, so both will be covered here.

DBI

The recommended method for accessing MySQL and mSQL databases from Perl is the DBD/DBI interface. DBD/DBI stands for DataBase Dependent/DataBase Independent. The name arises from the two-layer implementation of the interface. At the bottom is the database dependent layer. Here, modules exist for each type of database accessible from Perl. On top of these database dependent modules lies a database independent layer. This is the interface that you use to access the database. The advantage of this scheme is that the programmer only has to learn one API, the database independent layer. Every time a new database comes along, someone needs only to write a DBD module for it and it will be accessible to all DBD/DBI programmers.

As with all Perl modules, you must use DBI to get access:

#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use DBI;

TIP: When running any MySQL/mSQL Perl programs, you should always include the -w command line argument. With this present, DBI will redirect all MySQL and mSQL specific error messages to STDERR so that you can see any database errors without checking for them explicitly in your program.

All interactions between Perl and MySQL and mSQL are conducted through what is known as a database handle. The database handle is an object--represented as a scalar reference in Perl--that implements all of the methods used to communicate with the database. You may have as many database handles open at once as you wish. You are limited only by your system resources. The connect() method uses a connection format of DBI:servertype:database:hostname:port (hostname and port and optional), with additional arguments of username and password to create a handle:

my $dbh = DBI->connect('DBI:mysql:mydata', undef, undef);
my $dbh = DBI->connect('DBI:mSQL:mydata:myserver', undef, undef);
my $dbh = DBI->connect('DBI:mysql:mydata','me','mypass');

The servertype attribute is the name of the DBD database-specific module, which in our case will be either "mysql" or "mSQL" (note capitalization). The first version creates a connection to the MySQL server on the local machine via a Unix-style socket. This is the most efficient way to communicate with the database and should be used if you are connecting to a local server. If the hostname is supplied it will connect to the server on that host using the standard port unless the port is supplied as well. If you do not provide a username and password when connecting to a MySQL server, the user executing the program must have sufficient privileges within the MySQL database. The username and password should always be left undefined for mSQL databases.

TIP: Perl 5 has two different calling conventions for modules. With the object-oriented syntax, the arrow symbol "->" is used to reference a method in a particular class (as in DBI->connect). Another method is the indirect syntax, in which the method name is followed by the class name, then the arguments. The last connect method above would be written as connect DBI 'DBI:mysql:mydata', 'me', 'mypass'. Early versions of the Msql.pm used the indirect syntax exclusively and also enforced a specific method of capitalization inspired by the mSQL C API. Therefore, a lot of older MsqlPerl code will have lines in it like SelectDB $dbh 'test' where a simple $dbh->selectdb('test') would do. If you haven't guessed, we are partial to the object-oriented syntax, if only because the arrow makes the relationship between class and method clear.

Once you have connected to the MySQL or mSQL server, the database handle--$dbh in all of the examples in this section--is the gateway to the database server. For instance, to prepare an SQL query:

$dbh->prepare($query);

WARNING: When using mSQL you may select only one database at a time for a particular database handle. The mSQL server imposes this limit. However, you may change the current database at any time by calling connect again. With MySQL, you may include other databases in your query by explicitly naming them. In addition, with both MySQL and mSQL, if you need to access more than one database concurrently, you can create multiple database handles and use them side by side.

Chapter 21, Perl Reference, describes the full range of methods and variables supplied by DBI as well as Mysql.pm and Msql.pm.

As an example of the use of DBI consider the following simple programs. In Example 10-1, datashow.cgi accepts a hostname as a parameter--"localhost" is assumed if no parameter is present. The program then displays all of the databases available on that host.

Example 10-1: The CGI datashow.cgi shows all of the databases on a MySQL or mSQL server

#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the DBI module
use DBI;
CGI::use_named_parameters(1);
 
my ($server, $sock, $host);
 
my $output = new CGI;
$server = param('server') or $server = '';
 
# Prepare the MySQL DBD driver
my $driver = DBI->install_driver('mysql');
 
	my @databases = $driver->func($server, '_ListDBs');
 
# If @databases is undefined we assume
# that means that the host does not have
# a running MySQL server. However, there could be other reasons
# for the failure. You can find a complete error message by
# checking $DBI::errmsg.
if (not @databases) {
        print header, start_html('title'=>"Information on $server",
        'BGCOLOR'=>'white');
        print <<END_OF_HTML;
<H1>$server</h1>
$server does not appear to have a running mSQL server.
</body></html>
END_OF_HTML
        exit(0);
}
 
       print header, start_html('title'=>"Information on $host", 
                                'BGCOLOR'=>'white');
       print <<END_OF_HTML;
<H1>$host</h1>
<p>
$host\'s connection is on socket $sock.
<p>
Databases:<br>
<UL>
END_OF_HTML
foreach (@databases) {
        print "<LI>$_\n";
}
print <<END_OF_HTML;
</ul>
</body></html>
END_OF_HTML
exit(0)

In Example 10-2, tableshow.cgi accepts the name of a database server (default is "localhost") and the name of a database on that server. The program then shows all of the available tables on that server.

Example 10-2: The CGI tableshow.cgi shows all of the tables within a database

#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the Msql.pm module
use DBI;
CGI::use_named_parameters(1);
 
my ($db);
my $output = new CGI;
$db = param('db') or die("Database not supplied!");
 
# Connect to the requested server.
my $dbh = DBI->connect("DBI:mysql:$db:$server", undef, undef);
 
# If $dbh does not exist, the attempt to connect to the
# database server failed. The server may not be running,
# or the given database may not exist.
if (not $dbh) {
        print header, start_html('title'=>"Information on $host => $db",
        'BGCOLOR'=>'white');
 
        print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
The connection attempt failed for the following reason:<BR>
$DBI::errstr
</body></html>
END_OF_HTML
        exit(0);
} 
 
print header, start_html('title'=>"Information on $host => $db",
        'BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
<p>
Tables:<br>
<UL>
END_OF_HTML
# $dbh->listtable returns an array of the tables that are available
# in the current database.
my @tables = $dbh->func( '_ListTables' );
foreach (@tables) {
         print "<LI>$_\n";
}
print <<END_OF_HTML;
</ul>
</body></html>
END_OF_HTML
       exit(0);

And, finally, Example 10-3shows all of the information about a specific table.

Example 10-3: The CGI tabledump.cgi shows information about a specific table

#!/usr/bin/perl -w
 
use strict;
use CGI qw(:standard);
use CGI::Carp;
	# Use the DBI module
use DBI;
CGI::use_named_parameters(1);
 
my ($db,$table);
my $output = new CGI;
$server = param('server') or $server = '';
$db = param('db') or die("Database not supplied!");
 
# Connect to the requested server.
my $dbh = DBI->connect("DBI:mysql:$db:$server", undef, undef);
 
# We now prepare a query for the server asking for all of the data in
# the table.
my $table_data = $dbh->prepare("select * from $table");
# Now send the query to the server.
$table_data->execute;
 
# If the return value is undefined, the table must not exist. (Or it could
# be empty; we don't check for that.)
if (not $table_data) {
        print header, start_html('title'=>
        "Information on $host => $db => $table", 'BGCOLOR'=>'white');
 
        print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
The table '$table' does not exist in $db on $host.
</body></html>
END_OF_HTML
        exit(0);
}
 
# At this point, we know we have data to display. First we show the
# layout of the table.
print header, start_html('title'=>"Information on $host => $db => $table",
        'BGCOLOR'=>'white');
print <<END_OF_HTML;
<H1>$host</h1>
<H2>$db</h2>
<H3>$table</h3>
<p>
<TABLE BORDER>
<CAPTION>Fields</caption>
<TR>
 <TH>Field<TH>Type<TH>Size<TH>NOT NULL
</tr>
<UL>
END_OF_HTML
 
# $table_data->name returns a referece to an array 
# of the fields of the database.
my @fields = @{$table_data->NAME};
# $table_data->type return an array reference of the types of fields.
# The types returned here are in SQL standard notation, not MySQL specific.
my @types = @{$table_data->TYPE};
# $table_data->is_not_null returns a Boolean array ref indicating which fields
# have the 'NOT NULL' flag.
my @not_null = @{$table_data->is_not_null};
# $table_data->length return an array ref of the lengths of the fields. This is
# fixed for INT and REAL types, but variable (defined when the table was
# created) for CHAR.
my @length = @{$table_data->length};
 
# All of the above arrays were returned in the same order, so that $fields[0],
# $types[0], $not_null[0] and $length[0] all refer to the same field.
 
foreach $field (0..$#fields) {
        print "<TR>\n";
print "<TD>$fields[$field]<TD>$types[$field]<TD>";
        print $length[$field] if $types[$field] eq 'SQL_CHAR';
        print "<TD>";
        print 'Y' if ($not_null[$field]);
        print "</tr>\n";
}
 
print <<END_OF_HTML;
</table>
<p>
<B>Data</b><br>
<OL>
END_OF_HTML
 
# Now we step through the data, row by row, using DBI::fetchrow_array().
# We save the data in an array that has the same order as the informational
# arrays (@fields, @types, etc.) we created earlier.
while(my(@data)=$table_data->fetchrow_array) {
        print "<LI>\n<UL>";
        for (0..$#data) {
                print "<LI>$fields[$_] => $data[$_]</li>\n";
        }
        print "</ul></li>";
}
 
print <<END_OF_HTML;
</ol>
</body></html>
      END_OF_HTML

An Example DBI Application

DBI allows for the full range of SQL queries supported by MySQL and mSQL. As an example, consider a database used by a school to keep track of student records, class schedules, test scores, and so on. The database would contain several tables, one for class information, one for student information, one containing a list of tests, and a table for each test. MySQL and mSQL's ability to access data across tables--such as the table-joining feature--enables all of these tables to be used together as a coherent whole to form a teacher's aide application.

To begin with we are interested in creating tests for the various subjects. To do this we need a table that contains names and ID numbers for the tests. We also need a separate table for each test. This table will contain the scores for all of the students as well as a perfect score for comparison. The test table has the following structure:

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT,
  name CHAR(100),
  subject INT,
  num INT
) 

The individual tests have table structures like this:

CREATE TABLE t7 (
  id INT NOT NULL,
  q1 INT,
  q2 INT,
  q3 INT,
  q4 INT,
  total INT
) 

The table name is t followed by the test ID number from the test table. The user determines the number of questions when he or she creates the table. The total field is the sum of all of the questions.

The program that accesses and manipulates the test information is test.cgi. This program, which follows, allows only for adding new tests. Viewing tests and changing tests is not implemented but is left as an exercise. Using the other scripts in this chapter as a reference, completing this script should be only a moderate challenge. As it stands, this script effectively demonstrates the capabilities of DBI:[1]

#!/usr/bin/perl -w
 
use strict;
require my_end;
 
use CGI qw(:standard);
my $output = new CGI;
use_named_parameters(1);
 
# Use the DBI module.
use DBI;
# DBI::connect() uses the format 'DBI:driver:database', in our case we are
# using the MySQL driver and accessing the 'teach' database.
my $dbh = DBI->connect('DBI:mysql:teach');
The add action itself is broken up into three separate functions. The first 
function, add, prints out the template form for the user to create a new 
test.
sub add {
   $subject = param('subject') if (param('subjects'));
   $subject = "" if $subject eq 'all';
 
   print header, start_html('title'=>'Create a New Test',
      'BGCOLOR'=>'white');
   print <<END_OF_HTML;
<H1>Create a New Test</h1>
<FORM ACTION="test.cgi" METHOD=POST>
<INPUT TYPE=HIDDEN NAME="action" VALUE="add2">
Subject: 
END_OF_HTML
   my @ids = ();
   my %subjects = ();
   my $out2 = $dbh->prepare("select id,name from subject order by name");
   $out2->execute;
   # DBI::fetchrow_array() is exactly analogous to Msql::fetchrow()
   while(my($id,$subject)=$out2->fetchrow_array) {
      push(@ids,$id);
      $subjects{"$id"} = $subject;
   }
   print popup_menu('name'=>'subjects',
      'values'=>[@ids],
      'default'=>$subject,
      'labels'=>\%subjects);
   print <<END_OF_HTML;
<br>
Number of Questions: <INPUT NAME="num" SIZE=5><br>
A name other identifier (such as a date) for the test: 
 <INPUT NAME="name" SIZE=20>
<p>
<INPUT TYPE=SUBMIT VALUE=" Next Page ">
 <INPUT TYPE=RESET>
</form></body></html>
END_OF_HTML
}

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: