At the Forge - Rails and Databases

After years of painful Web development, here's a development framework based on understanding how Web developers really use relational databases. Rails standardizes the tweaky parts for you to save time.

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 Problem

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.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

scaffolding

Anonymous's picture

When I did:

ruby script/generate scaffolding Blog Admin

I got the error, "Couldn't find 'scaffolding' generator"

I think I got it to work by doing:

ruby script/generate scaffold Blog Admin

Bob Gustafson 1) The

bobgus's picture

Bob Gustafson

1) The db/create.sql file does not actually create the database table, but apparently supplies information that is used during the scaffold step. ( I was hoping.. ) I haven't checked whether this file changed to reflect the change ALTER TABLE..

2) The syntax of the scaffold commands have changed to:
ruby script/generate scaffold Blog Admin

3) The ALTER TABLE command syntax seems to have changed to:
psql -U blog blog_development

What about blog_test and blog_production? Duplicate work?

Otherwise, not bad. A quicky blog writer. Looking forward to next month.

Bob Gustafson

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix