Chapter 20. Relational Databases and mod_perl

Nowadays, millions of people surf the Internet. There are millions of terabytes of data lying around, and many new techniques and technologies have been invented to manipulate this data. One of these inventions is the relational database, which makes it possible to search and modify huge stores of data very quickly. The Structured Query Language (SQL) is used to access and manipulate the contents of these databases.

Let's say that you started your web services with a simple, flat-file database. Then with time your data grew big, which made the use of a flat-file database slow and inefficient. So you switched to the next simple solution—using DBM files. But your data set continued to grow, and even the DBM files didn't provide a scalable enough solution. So you finally decided to switch to the most advanced solution, a relational database.

On the other hand, it's quite possible that you had big ambitions in the first place and you decided to go with a relational database right away.

We went through both scenarios, sometimes doing the minimum development using DBM files (when we knew that the data set was small and unlikely to grow big in the short term) and sometimes developing full-blown systems with relational databases at the heart.

As we repeat many times in this book, none of our suggestions and examples should be applied without thinking. But since you're reading this chapter, the chances are that you are doing the right thing, so we are going to concentrate on the extra benefits that mod_perl provides when you use relational databases. We'll also talk about related coding techniques that will help you to improve the performance of your service.

From now on, we assume that you use the DBI module to talk to the databases. This in turn uses the unique database driver module for your database, which resides in the DBD:: namespace (for example, DBD::Oracle for Oracle and DBD::mysql for MySQL). If you stick to standard SQL, you maximize portability from one database to another. Changing to a new database server should simply be a matter of using a different database driver. You do this just by changing the data set name string ($dsn) in the DBI->connect( ) call.

Rather than writing your queries in plain SQL, you should probably use some other abstraction module on top of the DBI module. This can help to make your code more extensible and maintainable. Raw SQL coupled with DBI usually gives you the best machine performance, but sometimes time to market is what counts, so you have to make your choices. An abstraction layer with a well-thought-out API is a pleasure to work with, and future modifications to the code will be less troublesome. Several DBI abstraction solutions are available on CPAN. DBIx::Recordset, Alzabo, and Class::DBI are just a few such modules that you may want to try. Take a look at the other modules in the DBIx:: category—many of them provide some kind of wrapping and abstraction around DBI.