Building Rails to Legacy Applications :: Masking the Database

This post is part of my talk at OSCon 2006 entitled “Building Rails to Legacy Applications” One of the excuses I often hear for why people cannot build Rails interfaces to thier existing databases is that thier current database schema doesn’t fit into Rails personal view on how database schemas should be laid out. When presented with the notion that Rails has many configuration nobs and switches you can make to override the standard behavior, usually the complaint is that this would require too much internal knowledge of Rails. This may or may not be valid, but certainly it would increase the learning curve at least somewhat. The other option, to change the database schema itself, is also quickly discounted as in most cases there is already one or more applications working with the database, and schema changes would require a rewrite of those applications as well. If you’re trying to bring Rails into an organization, telling them they need to rewrite thier non-Rails applications is not going to get you far. Luckily there is another option. All major databases have the ability to create [http://en.wikipedia.org/wiki/View_%28database%29 views] and have the ability to make those views behave in a manner similar to tables, so that you can insert, update, and delete on them. We can use this technique to mask your current database structure and make a “Rails-ified” interface for your Ruby application to talk to. To show you how this technique works, let’s walk through a few quick eamples of masking a non-standard database schema, generating your scaffolding, and seeing how it interacts with [http://rails.outertrack.com/class/ActiveRecord::Base ActiveRecord]. The database we’ll use for this example is the “[http://people.planetpostgresql.org/xzilla/index.php?/archives/205-Pagila-sample-database-released.html Pagila]” database, which mimics that of an online rental store. If you want to play along at home, you can download it from [http://pgfoundry.org/projects/dbsamples/ the sample database project] and install it (you won’t need to install tsearch2 support for this). As this schema was developed completly on it’s own, it should give us a decent represenation of a typical schema you might find out in the wild. You’ll also need to get a rails environment setup and and create a project to work with. I’ll defer to the [http://rubyonrails.com/down many tutorials on rails installation] but there are a few notes I should pass along. The first is that, once you have rails installed, you’ll need to create a “pagila” project to work with, the command of which should look something like this: You’ll probably also need to modify your database.yml file to have the following information: Once you have everything installed and working, we can get down to business. First let’s take a look at our database; specifically the “actor” table: As you can see, this table uses a singular name, and also uses the tablename_id format to define it’s primary keys. To do this “the Rails way”, we’ll need to change this behavior: As you can see, we now have a rails looking “table”, and we are all set to build a little scaffolding and get some interaction going between the app and the database. If you look, you’ll see that everything was created successfully for our application structure: And what is even better is that if you launch your webserver you can even browse through the actors listing You can also look at thier specific information. Just don’t try to update them! As you may have guessed (since the error messages tells us as much [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]]), these views are read only, so right now all we can do is read data. But don’t worry, this is a pretty easy thing to fix. Let’s jump back into our database and see what we can do: That’s it! Once you issue that command, just restart your webserver, and you can now update actors as well. In a real setting of course you’ll need to make corresponding rules for INSERT and DELETE as well, but those really aren’t any more difficult. Following that pattern you can transform your whole database in this manner and really get moving with Rails. To see how well this works, let’s walk through printing up a list of films and thier actors. The first thing we need to do is mask the film table like we did with the actor table, and then generate the appropriate scaffolding: Simple enough right? Now we need to explain to Rails that these two tables are related. To do this we’ll modiy the model file for both: edit app/models/actor.rb edit app/models/film.rb And now let’s take a quick look in the [http://wiki.rubyonrails.com/rails/pages/Console console] to see if it worked: Good so far… now let’s print out that list. Akk! what happened here? This was a little test to make sure you were paying attention; plus you need to learn that error messages are our friends. The above error shows that Rails is looking for an “actors_films” table to derive the join information from. As you will recall, we had to mask our “actor” and “film” tables with views. It turns out we also need to do this with our join table, since it doesn’t follow the Rails layout. Again it’s not really a big deal: Now we can go back to our Rails console and wallah! So there you have it. Using this technique you can effectivly hook up a large number of databases with Ruby on Rails front ends and get comfortable. Also, while these examples were done in PostgreSQL, the ideas should translate to other systems like Oracle, SQL Server, or even Sqlite, all of which provide some type of updatable views or triggers on views which you can use to the same effect as PostgreSQL’s rules.