Three-Tiered Design

Learn how to implement middleware into the mod_perl/Apache design mix.

Now that we have created our initial database design, we will consider the design of our middleware layer, which insulates the web application from the database. If we ever decide to switch to another brand of database server, or even replace the database with flat ASCII files or DBM files, the object layer will remain the same.

In addition, non-web applications will be able to use this layer in order to access the database, making it possible, for instance, to write a set of routines that export our appointment calendar into XML, or to import it from another program.

This middle tier is often called the “business logic” of an application. The database makes it easy and safe for us to store and retrieve information, and the Mason components make it easy for us to create dynamic output for the end user. The middleware layer will try to force the database to do as much of the computation as possible, using built-in functions, views and stored procedures. But the actual logic that determines our application's functionality will reside in the middle tier.

Perl gives us at least two options when creating this layer. One possibility is to create a basic Perl module that provides subroutines and variables that can accomplish the tasks we need. Such a procedural interface is relatively easy to write and executes at the same speed as all other Perl subroutines.

But Perl also offers us the option of creating an object module. While Perl objects are slightly harder to write, and their methods execute more slowly than straight subroutines, they make it easier to conceptualize and write programs.

Before we can create our middleware layer, we have to answer some serious questions. What sorts of objects will we create? We could create a single database object that handles all of our queries for us, turning them into the appropriate SQL. But we will occasionally want to retrieve information about people without any regard for appointments, which means that we should have, if nothing else, a people object, as well as a separate appointments object. Because our database table definition forces us to associate each appointment with one person, we can only define our appointments object after the people object.

Listing 2 contains a listing for, an object module that performs some basic tasks for the people table that we created earlier. The object is not complete and has some rough edges, but should suffice for demonstrating how to access a relational database via an object middleware layer.

Listing 2., a Perl Object Module That Communicates with the Package People

The basic idea is that you create a new instance of people, and then manipulate the people in your appointment book with that object. To retrieve all of the names of people in the database, you can use the get_all_full_names method, as in this code fragment (also see Listing 3):

use People;
# Create a People object
my $people = new People;
# Retrieve all of the full names
my @names = $people->get_all_full_names();
# Print the names
foreach my $name (@names)
    print "name\n";

Listing 3., a Program That Uses to Retrieve Information from the Database

To set or retrieve information about a particular person, you must first identify which person you're talking about. Because our middleware layer is meant to shield the user from having to think or worry about primary keys and other database-specific IDs, we will allow them to set the “current person” via either the first and last name, or via the e-mail address.

The e-mail address is guaranteed to be unique in the database layer, and thus using set_current_person_by_email is the most reliable method available. Nevertheless, it's often useful to identify people by first and last name, so we also offer the set_current_person_by_name method. In the current implementation, using the name to set the current person will match the first returned row from the database, which might not necessarily be what you want.

Once a program has set the current person, it can retrieve information about that person using the get_current_info method:

# Set the current person by name
|| die "Error retrieving person.";
# Print the information
foreach my $key (sort keys %{$info})
        if (defined $info->{$key})
                print "$key => $info->{$key}\n";

Each instance of people will keep only two pieces of state: the ID of the currently selected person ($self->{current_person}) and the database handle ($dbh) that connects us to the database ($self->{dbh}). We keep the database handle around because connecting to a database is a relatively expensive operation. We can thus save ourselves some time by connecting to the database in the constructor, and then using that connection each time we invoke a method on this object.

Of course, this means that the database connection will have to be destroyed when the Perl object goes away—a somewhat tricky task, given that Perl does not have explicit destructors, since it is a garbage-collected language. The solution is to create a method called DESTROY, which is invoked by Perl whenever the object is destroyed. Our DESTROY method merely closes our connection to the database, allowing the object to be removed without potentially causing a memory leak in either the database client or server:

        # Get myself
        my $self = shift;
        # Get the database handle
        my $dbh = $self->{dbh};
        # Close the database handle

We can even create a new person, invoking the new_person method with a set of hash keys and values as arguments. These are then translated by the middleware layer into an appropriate SQL query:

# Now insert a new person
my $success = $people->new_person
        (first_name => "Reuven",
        last_name => "Lerner",
        country => "Israel"
        email => '',
        phone => '08-973-2225');
print "Inserted successfully" if $success;
Because Perl's undefined (“undef”) value is automatically translated into an SQL “NULL” value, the optional columns will be filled in with NULLs, as should be the case.