At the Forge - Rails and Databases

Last month, we began looking at Ruby on Rails, a Web development framework that has captured a great deal of attention in only a short time. Much of the success of Rails is due to the ease with which Web/database developers can accomplish various tasks. Indeed, Rails fans often tout the fact that their applications have almost no configuration files, allowing programmers to concentrate on development, rather than logistics.

This month, we begin to look at how Rails works with relational databases. Even if you won't be using Rails in your own Web development work, the way Rails addresses many different issues is extremely elegant and may well influence future generations of object-relational technologies.

The database side to Rails attempts to solve a seemingly simple problem. Where and how should a Web application store persistent information? Nearly any Web application we might want to build, from a shopping cart to a calendar/diary, needs to store its information somewhere. And because Web applications run on the server, rather than on the user's desktop, we need to keep track of data for many different users, rather than just one.

Back in the olden days of Web development, when applications were far less sophisticated, some of us used basic text files. But we quickly discovered that a relational database was an improvement on nearly every level. Relational databases are designed to provide fast, secure and flexible access to the data that we want—so long as we can represent our data as two-dimensional tables.

But as simple as that last sentence makes it sound, moving data from a program into a database is neither simple nor straightforward. Sure, the simple stuff is indeed pretty simple; it's not a big deal to keep track of customers' bank balances, or even the latest transactions in their checkbooks. But there are big differences between the objects that are increasingly at the center of the programming world and the tables that are at the center of the database world. Consider the contortions that database programmers go through in representing arbitrarily deep hierarchies, and you'll begin to understand how the mapping between objects and tables can be quite complex.

There are basically three ways to bridge this gap between objects and tables: handle it manually, replace the tables with objects and use an automatic mapping tool. The manual approach, which is probably the most common and popular, simply means that the programmers stick SQL queries into the code. To get the contents of a shopping cart, we do something like this Perl code:

# Send the shopping-cart query
my $sql = "SELECT item_id, item_name,
                  item_price, item_quantity
             FROM ShoppingCart
            WHERE user_id = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($user_id);

my $total_cost;

print "<table>
              <th>Name</th>
              <th>Price</th>
              <th>Quantity</th>\n";

# Iterate over the elements of the shopping cart
while (my $rowref = $sth->fetchrow_arrayref())
{
    my ($item_id, $item_name, $item_price,
            $item_quantity) = @$rowref;

    $total_cost += $item_price * $item_quantity;

    print "<tr><td>$item_name</td>
               <td>$item_price</td>
               <td>$item_quantity</td></tr>\n";
}

print "<tr><td>Total cost:</td>
           <td>$total_cost</td></tr>
       </table>\n";

The first few times you write such code, it doesn't seem so bad. But after a while, it begins to grate on you. Why are you writing so much SQL, when all you want is the elements of your shopping cart? Even if you wrap the SQL inside of an object, you'll find yourself creating many such objects over the course of a project.

The people who wrote Zope, a Python-based Web application framework, decided that although relational databases have their place, the real solution to this problem is to avoid the object-table translation as much as possible, opting instead for an object database. ZODB (Zope Object Database) thus allows you to store and retrieve Python objects as part of a hierarchy. If you can represent data in a Python object, ZODB makes it easy to keep that data persistently.

But of course, ZODB has its problems as well. To begin with, you can use it only from Python; by contrast, relational databases typically can be accessed from any number of languages. And although ZODB now has multiversion concurrency control (MVCC), transactions and a host of other features, the fact that it simply stores a set of objects means that you can't easily sort, search or perform “joins”, which are the cornerstone of the relational world.