In this age of the full-service internet, the demand for bigger and badder websites will only increase. To meet the demands of these full-service websites, the SQL database servers come into play. One of the more popular SQL servers available for download on the internet is MySQL. MySQL is not released under a full GPL free license, but for some applications, like intranets, it is free to use. For most users, a $200 license will have to be purchased. However, this price compares very favourably with other companies producing database backends for the commercial market, like IBM, Oracle and Sybase.
Getting Started
For the purpose of this demonstration, we will be setting up a guestbook, using a perl and a MySQL backend. An SQL backend solution for this application would be most appropriate for companies such as GuestWorld who run free public guestbooks.
perl is a scripting language and this article demonstrates its powerful features, as much as those of MySQL.
The absolute earliest version of perl you want to be working with is 5.004. You may be able to get away with 5.003, but perl 4 must be avoided. Perl 4 is considered a security hazard and lacks many of the basic functions we will need to write our guestbook application. In addition to the standard perl distribution, you will be needing the DBI module and DBD::mysql module from CPAN. The DBI module is a general API for accessing databases. The DBD::mysql module can be considered a plug-in for DBI, allowing DBI to talk to MySQL servers. For the purpose of this demonstration, we used a pre-compiled MySQL daemon from ftp.mysql.com. To demonstrate the application, we will assume that a copy of Apache or some other webserver has already been properly installed and configured.
The great thing about DBI is that it works with any type of database. If your organisation uses Sybase, for instance, you should be able to pick up a Sybase DBD driver and use it to run the perl code included with this article.
The System
Most any system can run a MySQL server; however, it is recommended that you use at least a low-end Pentium class machine. For this demonstration, I`ll be using a Slackware Linux 4.0 machine on a Celeron 366 chip.
Slackware isn't necessary either; the techniques described below will work with any distribution. If you use a package manager then all the tools described should install automatically without much fuss, as they are mature products with high levels of stability.
Installing MySQL
Installing MySQL couldn`t be easier. After downloading the appropriate tarball from my local MySQL mirror, in this case mysql-3.22.23b-pc-linux-gnu-i686.tar.gz,
the normal un-gziping yielded me the tar file. Before un-taring your package, first decide where you want to install MySQL. MySQL itself comes ready-to-run out of the package. Hence, you should untar MySQL in its final resting place. After untaring, cd to the distribution directory and run ./configure . This allows the daemon to set up the appropriate tables and then start up. You should see something like this after running ./configure :
Creating db table
Creating host table
Creating user table
Creating func table
Creating tables_priv table
Creating columns_priv table
...etc...
Starting mysqld daemon with databases from /usr/local/mysql/data
If you see a message staying that mysqld has created all the tables and started up the mysqld daemon without incident, then congratulations; you now have a functioning MySQL installation.
Installing the Perl Modules
The next step is to install the two necessary modules. The best way to go about doing this is to use the CPAN shell. CPAN, the Comprehensive Perl Archive Network, is the world's largest collection of perl modules. You first need to install DBI, the database interface module. This can be done by typing:
perl -MCPAN -e 'install DBI'
If perl should return an error such as Unrecognized switch: -MCPAN, it means you have perl 4 installed. Fear not, though, your administrator may have installed perl 5 later on in your path. You may want to try
/usr/local/bin/perl -MCPAN -e'install DBI'
/usr/bin/perl -MCPAN -e'install DBI'
This command invokes the CPAN shell, a tool that comes bundled with every version of perl to help facilitate the installation of modules. If this is the first time you`ve used the CPAN shell, you will be prompted with a few configuration questions. If you are running CPAN shell as root, you are probably okay if you select the defaults, and pick a CPAN mirror close to you. If you are non-root, you will want to specify PREFIX=/your/home/directory when asked for parameters for perl Makefile.PL . It should look something like this:
Parameters for the 'perl Makefile.PL' command? PREFIX=/my/home/dir
What this tells perl to do is install the modules inside your home directory, instead of the public perl modules directory. If all has gone well, you should see something similar to this:
/usr/bin/make install -- OK
After you have a successful DBI installation, you will need to install the DBD::mysql DBI plugin. This can be done by executing:
perl -MCPAN -e'install DBD::mysql'
You will be asked a series of questions needed for installation. When asked what kind of drivers you wish to install, be sure to specify 'MySQL only.` When asked if you want MysqlPerl emulation, it is typically safe to say 'n` When asked where your MySQL include directory is, specify the directory that you untared your MySQL distribution in. You can safely hit <Enter> to the remaining configuration options. Once again, if you receive:
/usr/bin/make install -- OK
then you have installed DBD::mysql without incident. Congratulations.
In order for us to have a place to store our guest book information, we must create a properly configured database. Within a database is a table. In the table, there are columns that define the names and data types. Although MySQL can have multiple tables within a single database, for our purposes we will use only one. MySQL database uses the term 'columns` to describe each individual field in a table. To setup our database, we use the mysqladmin tool.
mysqladmin -uroot create guestbook
will create our database for the guestbook. Since MySQL employs a user access system, we must tell MySQL who has access to which databases. Several commands need to be executed to setup the permissions:
mysql -uroot -e"insert into host(Host,Db) values('localhost','guestbook')" mysql
mysql -uroot -e"insert into db(Host,Db,User,Select_priv,Insert_priv) values('%','guestbook','guestbook','Y','Y')" mysql
mysql -uroot -e"insert into user(Host,User,Password) values('localhost','guestbook',password('guestbook')" mysql
mysqladmin -uroot reload
What the first statement tells MySQL, is to allow anyone from localhost access the database guestbook. The second statement says allow the user accessing guestbook from any host to SELECT and INSERT into the database. The third statement sets the user guestbook access from localhost, using the password guestbook. We call the function password() for guestbook in order to encrypt the password. The last statement tells MySQL to reload the user files.
The final step of MySQL configuration is to create the table to hold all our guestbook entries. To do this, we can execute:
mysql -uroot -e" CREATE TABLE guestbook (
name char(255) not null,
age int(3) unsigned,
email char(255) not null,
website char(255),
comments blob,
time int(10) unsigned
);" guestbook
Now that you`ve finished creating users and tables, create the perl application for accessing and adding to the database.
The following perl application will both display a form for user entries into the guestbook, to add new entries into the guestbook and then display the guestbook.
#!/usr/bin/perl -Tw
use strict;
$| = 1;
use CGI::Carp "fatalsToBrowser";
use CGI ":all";
use DBI;
my $serverName = "localhost";
my $serverPort = "3306";
my $serverUser = "guestbook";
my $serverPass = "guestbook";
my $serverDb = "guestbook";
my $serverTabl = "guestbook";
print
header,
start_html("SQL Guestbook"),
h1("Add and Read Guestbook Entries");
if(my $error = check_form()) {
show_form($error);
print end_html;
} else {
if(my $error = insert_entry()) {
show_form($error);
} else {
show_entries();
}
print end_html;
}
sub show_form {
my $error = shift;
print hr;
if($error) { print $error, hr; }
print
start_form,
table(map
Tr(td($_->[0]), td(textfield($_->[1],"",undef,60))),
["Name", "name"],
["Age", "age"],
["E-Mail Address", "email"],
["Web Site Address", "website"],
["Comments", "comments"],
),
submit,
end_form,
hr;
}
sub check_form() {
return "You didn't enter anything..." unless param();
return "Please enter a name" unless param("name");
return "Please enter your e-mail address" unless param("email");
return;
}
sub insert_entry {
my ($dbh, $success, $name, $age, $email, $website, $comments,$time);
$dbh = DBI->connect("DBI:mysql:database=$server Db;host=$serverName;port=$serverPort",$serverUser,$serverPass);
$name = param("name");
$age = param("age");
$email = param("email");
$website = param("website");
$comments = param("comments");
$time = time;
$success = $dbh->do("INSERT INTO
$serverTabl(name,age,email,website,comments,time)
VALUES(?,?,?,?,?,?)", undef, $name, $age, $email, $website, $comments, $time);
$dbh->disconnect;
if($success != 1) {
return "Sorry, the database was unable to add your entry.
Please try again later.";
} else {
return;
}
}
sub show_entries {
my ($dbh, $sth, @row);
$dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass);
$sth = $dbh->prepare("SELECT name,age,email,website,comments,time
FROM $serverTabl ORDER BY time");
$sth->execute;
print "Existing Entries",hr;
while(@row = $sth->fetchrow_array) {
$row[5] = scalar(localtime($row[5]));
print "Name: ", $row[0], br;
print "Age: ", $row[1], br;
print "E-Mail Address: ", $row[2], br;
print "Web Site Address: ", $row[3], br;
print "Comments: ", $row[4], br;
print "Added on ", $row[5], hr;
}
$sth->finish;
$dbh->disconnect;
}
discuss this topic to forum
