Creating and Using a Database with Perl

Perl programmers, like programmers of any other language, typically need to store large amounts of data. For this data to be manageable, it needs to be stored in a conveniently accessible format. It never hurts to make the stored data easy to write, as well.

Even though Perl is an exceptional language for text processing, in many circumstances, a more structured database-like format offers quicker access. In addition, it may also be necessary for a Perl script to read or write a database that is also accessed through a C program.

To accomplish this, the Perl distribution includes packages that give a Perl programmer access to a variety of different database formats available in a Unix environment. These formats include: the Berkeley DB format, the Free Software Foundation's GDBM format and the NDBM format.

The associative array (or “hash”) is one of the most powerful data structures available to a Perl programmer. To those familiar with traditional arrays (in C, Pascal or Perl), an associative array can be thought of as an array indexed using an arbitrary string instead of an integer subscript. Basically, an associative array is a data structure that allows the programmer to associate one string—a key—with another—its value.

Here is an example of an associative array that can be used to convert the abbreviated name of a day of the week to its full name.

%days = (
        "Sun", "Sunday",
        "Mon", "Monday",
        "Tue", "Tuesday",
        "Wed", "Wednesday",
        "Thu", "Thursday",
        "Fri", "Friday",
        "Sat", "Saturday"
);

The % in front of the variable name days is used to tell Perl the variable is an associative array. As shown, associative arrays are initialized by using pairs of values that relate to each other.

To access the data stored in an associative array, you can use a syntax similar to the following:

$long_name = $days{"Sun"};

This expression will set the scalar variable long_name to the value associated with the key “Sun”, the string “Sunday” in this example.

You can see already that associative arrays can be a powerful tool for organizing data used inside of a Perl script. This technique can easily be extended to something more useful by creating values made up of more than one field. Take, for instance, this simple address book database where multiple fields in the associative array's value are separated by colons:

$phone_db = (
        "Bill Jones", "123 West Avenue:New York, NY:12345",
        "Jane Smith", "6789 1st Street:Chicago, IL:56789"
);

New entries in this database can be added with an expression like:

$phone_db{"Bill Smith"} = join(":", $street, $city, $zip_code);

($street, $city, $zip_code) = split(/:/, $phone_db{"Bill Smith"});

while (($name, $record) = each %phone_db) {
        print "$name:$record\n";
}

To solve this problem, Perl supports “binding” associative arrays to the various types of database formats mentioned above. This allows a Perl programmer to create, access and update databases in the popular Unix database formats as easily as performing operations on an associative array.

Perl version 5 includes a set of “packages” that manipulate the various database formats. These packages are:

To use any of these database packages, a Perl programmer must include the package at the beginning of the script using the following statement:

use DB_File;

use Fcntl;

Databases are opened in Perl using the tie() function. This function is responsible for “joining” an associative array with a database package. Operations performed on the associative array are then translated by the database package into function calls that operate on the database file itself.

Here is an example of opening a database named “phone.db” using the DB_File database package:

tie (%phone_db, DB_File, "phone.db") ||
        die ("Cannot open phone.db");

This command binds the associative array named phone_db to the Berkeley DB database file named “phone.db”. In this example, the file must exist and must be readable by the Perl script.

Creating a database is nearly as simple as opening one. The following command will create a database named “phone.db” in the current directory with the file's permissions set to read-write for the owner and read-only for everyone else. The file will be created only if it does not already exist. If the database file exists in the current directory, the database file will simply be opened for read-write access by the Perl script.

tie (%phone_db, DB_File, "phone.db", O_CREAT|O_RDWR, 0644) ||
        die ("Cannot create or open phone.db");

The O_CREAT and O_RDWR flags are the same flags used as parameters to the Unix open() system call. They specify that the file should be created if it does not exist and opened with read-write access.

Reading from the database works exactly like reading data from an associative array. If the key is known, specific records can be read from the file with an expression like:

$record = $phone_db{"Bill Smith"};

All the records in the database file can be scanned (in a seemingly random order) with something like:

while (($name, $record) = each %phone_db) {
        [ commands to process data here ]
}

New data can be written into the database by creating a new key in the associative array and setting the key's value. This is done with a command similar to:

$phone_db{"Bill Smith"} = $data;

where $data is the information to be associated with the key “Bill Smith”. Any changes made to the associative array will be written into its corresponding database file.

Keys can be removed from the database in exactly the same way items are removed from an associative array in Perl—by using the delete() function. The following code removes the record in the database that refers to “Bill Smith”.

delete $phone_db{"Bill Smith"};

Changes to an associative array may not be immediately written out to the database file. To insure that changes are successfully written to the database file, the file must be closed.

Closing the database file involves un-binding the associative array from the database package. This is done with the untie() function in the following manner:

untie(%phone_db);

This closes the database file, making updates to the file if necessary. The associative array %phone_db can now no longer be used to access the records in the database.

All of the examples provided here use the default type of Berkeley DB database, the DB_HASH type. This form of database uses a hash table (like Perl does) to store the keys and their values in the database file. Two other types of databases are provided with the Berkeley DB package: DB_BTREE and DB_RECNO.

The DB_BTREE format uses a sorted, balanced binary tree to store the key and value pairs. This format allows data to be stored and read in a sorted order as opposed to the seemingly random order the DB_HASH format produces. The default comparison routine sorts the keys in the database file in lexical order (alphabetically). The DB_File man page discusses this format in more detail and shows how to replace the default comparison routine with one of your own.

The DB_RECNO format is designed to operate on flat text files. It is bound (with tie()) to normal Perl arrays, not associative arrays. Indexing this array with a number provides the text found on that line of the database file. This format is also discussed in more detail in the DB_File man page.

The desired format of database file is specified with an additional parameter for the tie() function.

tie (%phone_db, DB_File, "phone.db", O_RDONLY, 0644, $DB_BTREE) ||
                die ("Cannot open phone.db");

This command will open the DB_BTREE database named “phone.db” in read-only access mode. If the file does not exist, the command fails.

Sometimes it is necessary to sort an associative array within a Perl script. Sorting by the key values of an associative array is done like this:

for (sort keys %phone_db) {
        print "$_ = $phone_db{$_}\n";
}

Each iteration of this loop will set the $_ scalar to a key value from the associative array provided in alphabetical order. This method works very nicely for sorting associative arrays by their keys. Sorting by an associative array's values is slightly more difficult:

sub sort_by_value {
        ( $phone_db{$a} cmp $phone_db{$b} ) || \
( $a cmp $b );
}
for (sort sort_by_value keys %phone_db) {
        print "$_ = $phone_db{$_}\n";
}

Keep in mind that these two methods for sorting an associative array do not actually rearrange the array in any fashion. They simply provide a way to pull every key and value pair from an associative array in a particular sorted order.

An example of how databases in Perl can be used is provided in Listing 1, a short script designed to keep a database of hits on a World Wide Web site. The script reads the NCSA HTTPD access log file, stores the information in the database and creates an HTML page that displays all the statistics for the site.

Listing 1. Example Web Site Hits Database Script

This implementation is not complete—it keeps track only of which documents were accessed and their sizes. A more complete implementation could also store information about the hosts that accessed the web server, for instance. Some method for “expiring” entries in the database after a particular time interval would be a handy feature as well.

The script begins by reading the existing database file and placing all the data into associative arrays indexed by the document file name. Next, the script reads the access log file from standard input and places the data into the associative arrays that store the statistics. Finally, the script creates an HTML page using tables to display the statistics.

The topics provided in this article are by no means a definitive reference guide for using the built-in database support included with Perl, but they can be used as a starting point for further experimentation and exploration.