Databases

Many serious web sites will need a database in back. In the authors’ experience, an excellent choice is MySQL, freeware made in Scandinavia by intelligent and civilized people. Download it from http://www.mysql.com. It uses a variant of the more-or-less standard SQL query language. You will need a book on SQL: Understanding SQL by Martin Gruber (Sybex, 1990) tells you more than you need to know, although the SQL syntax described is sometimes a little different from MySQL’s. Another option is SQL in a Nutshell by Kevin Kline (O’Reilly, 2000). MySQL is fast, reliable, and so easy to use that a lot of the time you can forget it is there. You link to MySQL from your scripts through the DBI module. Download it from CPAN (http://www.cpan.org/) if it doesn’t come with Perl. You will need some documentation on DBI — try http://www.symbolstone.org/technology/perl/DBI/doc/faq.html. There is also an O’Reilly book on DBI, Programming the Perl DBI by Alligator Descartes and Tim Bunce. In practice, you don’t need to know very much about DBI because you only need to access it in five different ways. See the lines marked 'A', 'B', 'C', 'D', and 'E' in script as follows:

'A' to open a database
'B' to execute a single command - which could equally well have been typed at the 
keyboard as a MySQL command line.
'C' to retrieve, display, process fields from a set of database records. A very nice 
thing about MySQL is that you can use the 'select *' command, which will make all 
the fields available via the $ref->{'<fieldname>'} mechanism.
'D' Free up a search handle 
'E' Disconnect from a database

If you forget the last two, it can appear not to matter since the database disconnect will be automatic when the Perl script terminates. However, if you then move to mod_perl (discussed in Chapter 17), it will matter a lot since you will then accumulate large numbers of memory-consuming handles. And, if you have very new versions of MySQL and DBI, you may find that the transaction is automatically rolled back if you exit without terminating the query handle.

This previous script assumes that there is a database called people. Before you can get MySQL to work, you have to set up this database and its permissions by running:

mysql mysql < load_database

where load_database is the script .../cgi-bin/load_database:

create database people;

INSERT INTO db VALUES 
('localhost','people','webserv','Y','Y','Y','Y','N','N','N','N','N','N');

INSERT INTO user VALUES 
('localhost','webserv','','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N');
INSERT INTO user VALUES ('<IP address>
','webserv','','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N');

You then have to restart with mysqladmin reload to get the changes to take effect.

Newer versions of MySQL may support the Grant command, which makes things easier.

You can now run the next script, which will create and populate the table people:

mysql people < load_people

The script is .../cgi-bin/load_people:

# MySQL dump 5.13
#
# Host: localhost    Database: people
#--------------------------------------------------------
# Server version 3.22.22

#
# Table structure for table 'people'
#
CREATE TABLE people (
  xname varchar(20),
  sname varchar(20)
);

#
# Dumping data for table 'people'
#

INSERT INTO people VALUES ('Jane','Smith');
INSERT INTO people VALUES ('Anne','Smith');
INSERT INTO people VALUES ('Anne-Lise','Horobin');
INSERT INTO people VALUES ('Sally','Jones');
INSERT INTO people VALUES ('Anne-Marie','Kowalski');

It will be found in .../cgi-bin.

Another nice thing about MySQL is that you can reverse the process by:

mysqldump people > load_people

This turns a database into a text file that you can read, archive, and upload onto other sites, and this is how the previous script was created. Moreover, you can edit self contained lumps out of it, so that if you wanted to copy a table alone or the table and its contents to another database, you would just lift the commands from the dump file.

We now come to the Perl script that exercises this database. To begin with, we ignore Apache. It is .../cgi-bin/script:

#! /usr/local/bin/perl -wT
use strict;
use DBI();
my ($mesg,$dbm,$query,$xname,$sname,$sth,$rows,$ref);

$sname="Anne Jane";
$xname="Beauregard";

# Note A above: open a database
$dbm=DBI->connect("DBI:mysql:database=people;host=localhost",'webuser')
     or die "didn't connect to people";

#insert some more data just to show we can
$query=qq(insert into people (xname,sname) values ('$xname',$sname'));
#Note B above: execute a command
$dbm->do($query);

# get it back
$xname="Anne";
$query=qq(select xname, sname from people where xname like "%$xname%");
#Note C above: 
$sth=$dbm->prepare($query) or die "failed to prepare $query: $!";

# $! is the Perl variable for the current system error message
$sth->execute;
$rows=$sth->rows;
print qq(There are $rows people with names matching '$xname'\n);
while ($ref=$sth->fetchrow_hashref)
    {
    print qq($ref->{'xname'} $ref->{'sname'}\n);
    }
#D: free the search handle
$sth->finish;
#E: close the database connection
$dbm->disconnect;

Stylists may complain that the $dbm->prepare($query) lines, together with some of the quoting issues, can be neatly sidestepped by code like this:

$surname="O'Reilly";
$forename="Tim";
...
$dbm->do('insert into people(xname,sname) values (?,?)',{},$forename,$surname);

The effect is that DBI fills in the ?s with the values of the $forename, $surname variables. However, building a $query variable has the advantage that you can print it to the screen to make sure all the bits are in the right place — and you can copy it by hand to the MySQL interface to make sure it works — before you unleash the line:

$sth=$dbm->prepare($query)

The reason for doing this is that a badly formed database query can make DBI or MySQL hang. You’ll spend a long time staring at a blank screen and be no wiser.

For the moment, we ignore Apache. When you run script by typing ./script, it prints:

               There are 4 people with names matching 'Anne'
Anne Smith
Anne-Lise Horobin
Anne Jane Beauregard
Anne-Marie Kowalski

Each time you run this, you add another Beauregard, so the count goes up.

MySQL provides a direct interface from the keyboard, by typing (in this case) mysql people. This lets you try out the queries you will write in your scripts. You should try out the two $querys in the previous script before running it.