Embperl and Databases

Those who have read more than a few of my “At the Forge” columns know that I am a great fan of HTML/Perl templates, which allow us to mix the two in a single document. In October, I introduced Embperl, a templating system that can function as a stand-alone CGI program, but can also be integrated into the mod_perl module for Apache. This month we will take a closer look at Embperl, exploring ways in which it can allow us to edit records in a database.

There are a number of good reasons to use templates. First of all, by putting code and design in the same document, designers and programmers can each modify the elements for which they are responsible. No longer is the programmer the bottleneck when a site decides to change its design, as is the case when dynamic output is produced by CGI programs.

Even when you are unlikely to change the look of a dynamically generated HTML page, Embperl (and similar in-line templating mechanisms that allow you to mix code and HTML) enables you to stick it all together, making the logic easier to follow. I have written many CGI programs in which the dynamic output was dwarfed by the static output—but because even one portion of the resulting HTML page had to change over time, the entire thing had to be within the province of the program.

Since the time I wrote October's introduction to Embperl, the package has been improved significantly. Perhaps the most significant change is that recent releases of Apache 1.3.1 and mod_perl 1.15 free you from having to recompile everything when installing a new version of Embperl. Now, Embperl can be installed and upgraded separately from Apache and mod_perl, just as you install and upgrade other Perl packages from CPAN. Please see the “Resources” sidebar to learn where to obtain the latest information, including installation instructions, on Apache, mod_perl and Embperl.

Databases are an increasingly important part of the Web. Using them, we can create customized and personalized sites, bringing people the specific information they want, rather than simply handing them all the information we have.

In addition, databases are designed to store and retrieve information easily. If text files and DBM files are too insecure or unstructured for your needs, consider using a relational database. Relational databases store their information in tables, where each table has columns (describing the various fields) and rows (with one record stored per row). Using multiple tables is where the “relational” part comes in, and it can be an extremely powerful tool. You could probably program this functionality on your own, but doing so would be quite complicated—and besides, someone has already done the work for you.

Relational databases are manipulated using SQL, the Structured Query Language developed by IBM in the 1970s. You don't write programs in SQL; instead, you write “queries” that manipulate one or more tables. Using SQL, you can create tables, modify their contents and request combinations of columns and rows containing particular types and pieces of data.

SQL is not a programming language, so it must be created and submitted to a database server by a programming language. In the past, each database product required its own version of Perl in order to allow access; this led to versions known as Oraperl, Sybperl, et al. Recently, the generic DBI (database interface) has produced a stable and portable database engine that allows access to any relational database with the same interface. The database-specific parts are kept in DBDs (database drivers) loaded dynamically by DBI. Assuming you stick to standard SQL rather than database vendors' proprietary extensions, you should be able to switch database brands by modifying a single Perl statement.

The relational database I use in these examples is MySQL, described by its author as a “mostly free” database. I have been using MySQL for quite some time now, and while it does not have all the optimization and locking features of its larger competitors, it performs admirably—and more features are on the way. For more information on MySQL, see the “Resources” sidebar.

Once you have installed Embperl, you need to tell Apache which documents should be interpreted with Embperl rather than as a straight HTML document. On my computer (running a modified version of Red Hat Linux 5.1), I put the following in the srm.conf configuration file:

Alias /embperl/ /usr/local/apache/share/embperl/

In addition, I put the following in the access.conf configuration file:

<Location /embperl>
SetHandler perl-script
PerlHandler HTML::Embperl
Options ExecCGI
</Location>