14. Database Access

14.0. Introduction

Everywhere you find data, you find databases. At the simplest level, every file can be considered a database. At the most complex level, expensive and complex relational database systems handle thousands of transactions per second. In between are countless improvised schemes for fast access to loosely structured data. Perl can work with all of them.

Early in the history of computers, people noticed that flat file databases don't scale to large data sets. Flat files were tamed using fixed-length records or auxiliary indices, but updating became expensive, and previously simple applications bogged down with I/O overhead.

After some head-scratching, clever programmers devised a better solution. As hashes in memory provide more flexible access to data than do arrays, hashes on disk offer more convenient kinds of access than array-like text files. These benefits in access time cost you space, but disk space is cheap these days (or so the reasoning goes).

The DBM library gives Perl programmers a simple, easy-to-use database. You use the same standard operations on hashes bound to DBM files as you do on hashes in memory. In fact, that's how you use DBM databases from Perl. You call dbmopen with the name of a hash and the filename holding the database. Then whenever you access the hash, Perl consults or changes the DBM database on disk.

Recipe 14.1 shows how to create a DBM database and gives tips on using it efficiently. Although you can do with DBM files the same things you do with regular hashes, their disk-based nature leads to performance concerns that don't exist with in-memory hashes. Recipes Recipe 14.2 and Recipe 14.4 explain these concerns and show how to work around them. DBM files also make possible operations that aren't available using regular hashes. Recipes Recipe 14.6 and Recipe 14.7 explain two of these things.

Various DBM implementations offer varying features. The old dbmopen function only lets you use the DBM library Perl was built with. If you wanted to use dbmopen to access from one type of database and write to another, you were out of luck. Version 5 of Perl remedied this by letting you tie a hash to an arbitrary object class, as detailed in Chapter 13, Classes, Objects, and Ties.

The table below shows several possible DBM libraries you can choose from:

[1] Using GPLed code in your program places restrictions upon you. See www.gnu.org for more details.

[2] See the DB_File library method. Requires symbolic links.

[3] On mixed-universe machines, this may be in the BSD compatibility library, which is often shunned.

[4] Except for free Unix ports like Linux, FreeBSD, OpenBSD, and NetBSD.

[5] Providing you have an ANSI C compiler.

[6] Prior to unification in 5.005, several divergent versions of Perl on Windows systems were widely available, including the standard port built from the normal Perl distribution and several proprietary ports. Like most CPAN modules, DB builds only on the standard port.

[7] Depends on how much your vendor has tweaked it.

[8] Can be reduced if you compile for one access method.

[9] By default, but can be redefined (at the expense of compatibility with older files).

NDBM comes with most BSD-derived machines. GDBM is a GNU DBM implementation. SDBM is part of the X11 distribution and also the standard Perl source distribution. DB refers to the Berkeley DB library. While the others are essentially reimplementations of the original DB library, the Berkeley DB code gives you three different types of database on disk and attempts to solve many of the disk, speed, and size limitations that hinder the other implementations.

Code size refers to the size of the compiled libraries. Disk usage refers to the size of the database files it creates. Block size limits refer to the database's maximum key or value size. Byte-order independence refers to whether the database system relies on hardware byte order or whether it instead creates portable files. A user-defined sort order lets you tell the library what order to return lists of keys in. Partial key lookups let you make approximate searches on the database.

Most Perl programmers prefer the Berkeley DB implementations. Many systems already have this library installed, and Perl can use it. For others, you are advised to fetch and install it from CPAN. It will make your life much easier.

DBM files provide key/value pairs. In relational database terms, you get a database with one table that has only two columns. Recipe 14.8 shows you how to use the MLDBM module from CPAN to store arbitrarily complex data structures in a DBM file.

As good as MLDBM is, it doesn't get around the limitation that you only retrieve rows based on one single column, the hash key. If you need complex queries, the difficulties can be overwhelming. In these cases, consider a separate database management system (DBMS). The DBI project provides modules to work with Oracle, Sybase, mSQL, MySQL, Ingres, and others.

See http://www.hermetica.com/technologia/perl/DBI/index.html and http://www.perl.com/CPAN/modules/by-category/07_Database_Interfaces/, which currently contains:

AsciiDB   DBI Db     MLDBM    OLE    Pg        Sybase
CDB_File  DBZ_ File  Fame     Msql   ObjStore  Postgres  XBase
DBD       DB_File    Ingperl  MySQL  Oraperl   Sprite