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 [ 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 [ ActiveRecord]. The database we’ll use for this example is the “[ Pagila]” database, which mimics that of an online rental store. If you want to play along at home, you can download it from [ 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 [ 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:
[rob@localhost ruby]$ rails -d postgresql pagila       create       create app/controllers       create app/helpers       create app/models       create app/views/layouts       create config/environments       create components       create db       create doc       create lib       create lib/tasks       create log       create public/images       create public/javascripts       create public/stylesheets       create script/performance       create script/process       create test/fixtures       create test/functional       create test/integration       create test/mocks/development       create test/mocks/test       create test/unit       create vendor       create vendor/plugins       create tmp/sessions       create tmp/sockets       create tmp/cache       create Rakefile       create README       create app/controllers/application.rb       create app/helpers/application_helper.rb       create test/test_helper.rb       create config/database.yml       create config/routes.rb       create public/.htaccess       create config/boot.rb       create config/environment.rb       create config/environments/production.rb       create config/environments/development.rb       create config/environments/test.rb       create script/about       create script/breakpointer       create script/console       create script/destroy       create script/generate       create script/performance/benchmarker       create script/performance/profiler       create script/process/reaper       create script/process/spawner       create script/runner       create script/server       create script/plugin       create public/dispatch.rb       create public/dispatch.cgi       create public/dispatch.fcgi       create public/404.html       create public/500.html       create public/index.html       create public/favicon.ico       create public/robots.txt       create public/images/rails.png       create public/javascripts/prototype.js       create public/javascripts/effects.js       create public/javascripts/dragdrop.js       create public/javascripts/controls.js       create public/javascripts/application.js       create doc/README_FOR_APP       create log/server.log       create log/production.log       create log/development.log       create log/test.log
You’ll probably also need to modify your database.yml file to have the following information:
development:   adapter: postgresql   database: pagila   username: postgres   password:
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:
                                        Table ""    Column    |            Type             |                        Modifiers -------------+-----------------------------+---------------------------------------------------------- actor_id    | integer                     | not null default nextval('actor_actor_id_seq'::regclass) first_name  | character varying(45)       | not null last_name   | character varying(45)       | not null last_update | timestamp without time zone | not null default now() Indexes:     "actor_pkey" PRIMARY KEY, btree (actor_id)     "idx_actor_last_name" btree (last_name) Triggers:     last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()
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:
pagila=# CREATE OR REPLACE VIEW actors AS (SELECT actor_id AS id, first_name, last_name, last_update FROM actor); CREATE VIEW pagila=# d actors                  View "public.actors"    Column    |            Type             | Modifiers -------------+-----------------------------+----------- id          | integer                     | first_name  | character varying(45)       | last_name   | character varying(45)       | last_update | timestamp without time zone | View definition: SELECT actor.actor_id AS id, actor.first_name, actor.last_name, actor.last_update    FROM actor;
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.
[rob@localhost pagila]$ ruby script/generate scaffold Actor       exists  app/controllers/       exists  app/helpers/       create  app/views/actors       exists  test/functional/       dependency  model       exists    app/models/       exists    test/unit/       exists    test/fixtures/       create    app/models/actor.rb       create    test/unit/actor_test.rb       create    test/fixtures/actors.yml       create  app/views/actors/_form.rhtml       create  app/views/actors/list.rhtml       create  app/views/actors/show.rhtml       create  app/views/actors/new.rhtml       create  app/views/actors/edit.rhtml       create  app/controllers/actors_controller.rb       create  test/functional/actors_controller_test.rb       create  app/helpers/actors_helper.rb       create  app/views/layouts/actors.rhtml       create  public/stylesheets/scaffold.css
If you look, you’ll see that everything was created successfully for our application structure:
[rob@localhost pagila]$ ls -al app/models/actor.rb -rw-rw-r-- 1 rob rob 37 May 31 13:41 app/models/actor.rb [rob@localhost pagila]$ ls -al app/controllers/actors_controller.rb -rw-rw-r-- 1 rob rob 1101 May 31 13:41 app/controllers/actors_controller.rb [rob@localhost pagila]$ ls -al app/views/actors/ total 28 drwxrwxr-x 2 rob rob 4096 May 31 13:41 . drwxrwxr-x 4 rob rob 4096 May 31 13:41 .. -rw-rw-r-- 1 rob rob 263 May 31 13:41 edit.rhtml -rw-rw-r-- 1 rob rob 391 May 31 13:41 _form.rhtml -rw-rw-r-- 1 rob rob 825 May 31 13:41 list.rhtml -rw-rw-r-- 1 rob rob 188 May 31 13:41 new.rhtml -rw-rw-r-- 1 rob rob 229 May 31 13:41 show.rhtml
And what is even better is that if you launch your webserver you can even browse through the actors listing Building Rails To Legacy Application - Browsing You can also look at thier specific information. Building Rails To Legacy Application - Detail Just don’t try to update them! Building Rails To Legacy Application - Update Error 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:
pagila=# CREATE RULE actors_update AS ON update TO actors DO INSTEAD update actor SET actor_id =, first_name = NEW.first_name, last_name = NEW.last_name, last_update = NEW.last_update WHERE actor_id =; CREATE RULE
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:
pagila=# CREATE OR REPLACE VIEW films AS SELECT film_id AS id, title, description,release_year ,language_id,original_language_id,rental_duration , rental_rate,length ,replacement_cost,rating ,last_update,special_features FROM film; CREATE VIEW pagila=#q [rob@localhost pagila]$ ruby script/generate scaffold Film       exists  app/controllers/       exists  app/helpers/       exists  app/views/films       exists  test/functional/   dependency  model       exists    app/models/       exists    test/unit/       exists    test/fixtures/       create    app/models/film.rb       create    test/unit/film_test.rb       create    test/fixtures/films.yml       create    app/views/films/_form.rhtml       create    app/views/films/list.rhtml       create    app/views/films/show.rhtml       create    app/views/films/new.rhtml       create    app/views/films/edit.rhtml       create    app/controllers/films_controller.rb       create    test/functional/films_controller_test.rb       create    app/helpers/films_helper.rb       create    app/views/layouts/films.rhtml       create    public/stylesheets/scaffold.css
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
class Actor < ActiveRecord::Base     has_and_belongs_to_many :films end
edit app/models/film.rb
class Film < ActiveRecord::Base     has_and_belongs_to_many :actors end
And now let’s take a quick look in the [ console] to see if it worked:
>> Film.find(423) => #"2006-02-15 10:03:42", "rating"=>"PG", "title"=>"HOLLYWOOD ANONYMOUS", "release_year"=>"2006", "rental_duration"=>"7", "id"=>"423", "length"=>"69", "language_id"=>"1", "description"=>"A Fast-Paced Epistle of a Boy And a Explorer who must Escape a Dog in A U-Boat", "special_features"=>"{Trailers,"Behind the Scenes"}", "replacement_cost"=>"29.99", "rental_rate"=>"0.99", "original_language_id"=>nil}> >>
Good so far… now let’s print out that list.
>> ?> Film.find([423]).each do |film| ?> puts film.title >> film.actors.each { |actor| puts " #{actor.last_name}, #{actor.first_name}" } >> end HOLLYWOOD ANONYMOUS ActiveRecord::StatementInvalid: PGError: ERROR:  relation "actors_films" does not exist :             SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull               FROM pg_attribute a LEFT JOIN pg_attrdef d                 ON a.attrelid = d.adrelid AND a.attnum = d.adnum              WHERE a.attrelid = 'actors_films'::regclass                AND a.attnum > 0 AND NOT a.attisdropped              ORDER BY a.attnum         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/connection_adapters/abstract_adapter.rb:120:in `log'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/connection_adapters/postgresql_adapter.rb:144:in `query'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/connection_adapters/postgresql_adapter.rb:454:in `column_definitions'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/connection_adapters/postgresql_adapter.rb:217:in `columns'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/associations/has_and_belongs_to_many_association.rb:165:in `finding_with_ambigious_select?'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/associations/has_and_belongs_to_many_association.rb:44:in `find'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/associations/has_and_belongs_to_many_association.rb:93:in `find_target'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/associations/association_proxy.rb:116:in `load_target'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/associations/association_proxy.rb:109:in `method_missing'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/associations/has_and_belongs_to_many_association.rb:81:in `method_missing'         from (irb):5         from (irb):3 >>
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:
pagila=# d film_actor                      Table "public.film_actor"    Column    |            Type             |       Modifiers -------------+-----------------------------+------------------------ actor_id    | smallint                    | not null film_id     | smallint                    | not null last_update | timestamp without time zone | not null default now() Indexes:     "film_actor_pkey" PRIMARY KEY, btree (actor_id, film_id)     "idx_fk_film_id" btree (film_id) Foreign-key constraints:     "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT     "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT Triggers:     last_updated BEFORE UPDATE ON film_actor FOR EACH ROW EXECUTE PROCEDURE last_updated() pagila=# create or replace view actors_films as select actor_id, film_id, last_update from film_actor; CREATE VIEW pagila=# d actors_films               View "public.actors_films"    Column    |            Type             | Modifiers -------------+-----------------------------+----------- actor_id    | smallint                    | film_id     | smallint                    | last_update | timestamp without time zone | View definition: SELECT film_actor.actor_id, film_actor.film_id, film_actor.last_update    FROM film_actor;
Now we can go back to our Rails console and wallah!
>> Film.find([423]).each do |film| ?> puts film.title >> film.actors.each { |actor| puts "  #{actor.last_name}, #{actor.first_name}" } >> end HOLLYWOOD ANONYMOUS   CRONYN, ANNE   BERRY, HENRY   NEESON, CHRISTIAN   TRACY, RENEE   WILLIAMS, GROUCHO   WALKEN, BELA
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.