Building Rails to Legacy Applications :: Take Control of Active Record

This post is part of my talk at OSCon 2006 entitled “Building Rails to Legacy Applications” Previously I showed you how you could use your database system to [http://people.planetpostgresql.org/xzilla/index.php?/archives/213-Building-Rails-to-Legacy-Applications-Masking-the-Database.html mask your schema] and present a Rails compatible version of your database to get started with the Ruby on Rails web framework. While this method has some advantages, it isn’t the only game in town. Today we will take a look at how to accomplish this task by taking advantage of some simple knobs and switches in Rails that let you use [http://en.wikipedia.org/wiki/Active_record Active Record] with non-standard schemas. We’ll again make use of the [http://pgfoundry.org/projects/dbsamples/ pagila database] for this example. If you went through part one, you can either drop all of the views we created before, or you can drop and reload your database. You’ll also need to move your old project out of the way, as we will want to make a new project to get things going:
[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
Once that is built, we need to modify the database.yml file to include something like this:
development:   adapter: postgresql   database: pagila   username: postgres   password:
The first thing in the Pagila schema that flies in the face of Rails Active Record format is that the tables all use a singular naming scheme, whereas Rails prefers plural naming. Because of this, the first thing we want to do is modify our Rails setup to not look for plural table names. This is done in the config/environment.rb file, where there are really a whole host of things you can modify, but for our needs we will just add the following line to the bottom of the file:
# Include your application configuration below ActiveRecord::Base.pluralize_table_names = false
Once that is done, we can go ahead and fire up the scaffolding to get some code in place.
[rob@localhost pagila]$ ruby script/generate scaffold Actor       exists  app/controllers/       exists  app/helpers/       create  app/views/actor       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/actor.yml       create  app/views/actor/_form.rhtml       create  app/views/actor/list.rhtml       create  app/views/actor/show.rhtml       create  app/views/actor/new.rhtml       create  app/views/actor/edit.rhtml       create  app/controllers/actor_controller.rb       create  test/functional/actor_controller_test.rb       create  app/helpers/actor_helper.rb       create  app/views/layouts/actor.rhtml     create  public/stylesheets/scaffold.css
And just to verify, all of the files we would expect are in place:
[rob@localhost pagila]$ ls -al app/models/actor.rb -rw-rw-r-- 1 rob rob 37 May 30 14:37 app/models/actor.rb [rob@localhost pagila]$ ls -al app/controllers/actor_controller.rb -rw-rw-r-- 1 rob rob 1100 May 30 14:38 app/controllers/actor_controller.rb [rob@localhost pagila]$ ls -al app/views/actor/ total 28 drwxrwxr-x 2 rob rob 4096 May 30 14:38 . drwxrwxr-x 4 rob rob 4096 May 30 14:37 .. -rw-rw-r-- 1 rob rob 263 May 30 14:38 edit.rhtml -rw-rw-r-- 1 rob rob 391 May 30 14:38 _form.rhtml -rw-rw-r-- 1 rob rob 825 May 30 14:38 list.rhtml -rw-rw-r-- 1 rob rob 188 May 30 14:38 new.rhtml -rw-rw-r-- 1 rob rob 229 May 30 14:38 show.rhtml
And with that, we have an actual working website: Building Rails To Legacy Application - BrowsingID”, but our schema follows the naming convention of tablename_id. If you look in Rails console application, the problem is a little bit clearer:
[rob@localhost pagila]$ ruby script/console Loading development environment. >> Actor.find(200) ActiveRecord::StatementInvalid: PGError: ERROR:  column actor.id does not exist : SELECT * FROM actor WHERE (actor.id = 200)  LIMIT 1         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:148:in `execute'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/connection_adapters/postgresql_adapter.rb:362:in `select'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/connection_adapters/postgresql_adapter.rb:129:in `select_all'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:390:in `find_by_sql'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:924:in `find_every'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:918:in `find_initial'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:952:in `find_one'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:941:in `find_from_ids'         from /usr/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:382:in `find'         from (irb):1 >>
Between the error message and the sql statement, our problem becomes obvious, we don’t have the “id” column that Rails is looking for. Luckily, Rails gives us a way to work around this problem. To fix it, we just need to modify our model file for the actor table.
class Actor < ActiveRecord::Base     set_primary_key "actor_id" end
As you can see, we can use the [http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M000881 set_primary_key] method to easily designate any column as the primary key no matter what the naming scheme. More importantly, that’s all we need to do. Now we can restart our webserver and go on back to the website and it will “just work”. Building Rails To Legacy Application - Detailapp/models/actor.rb with the following:
class Actor < ActiveRecord::Base     set_primary_key "actor_id"     has_and_belongs_to_many :film end
And then generate the scaffolding for the Film table:
[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
And then also update our model for the film table with the same type of information in app/models/film.rb
class Film < ActiveRecord::Base     set_primary_key "film_id"     has_and_belongs_to_many :actor end
So what is this “has_and_belongs_to_many” thing all about? As the name indicates, this signifies that each actor can be in many films, and that a film can have many actors. Makes sense right? Let’s go to console to show our list of actors:
?> Film.find([423]).each do |film| ?> puts film.title >> film.actor.each { |actor| puts " #{actor.last_name}, #{actor.first_name}" } >> end HOLLYWOOD ANONYMOUS ActiveRecord::StatementInvalid: PGError: ERROR:  relation "actor_film" 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 = 'actor_film'::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
What’s the problem? As the error message indicates, normally Active Record expects the many-to-many table that holds the relationship information to be named in an alphabetical manner; for example ”actors_tables” in our case. Of course Rails is very flexible, and gives us a way out of this problem:
class Actor < ActiveRecord::Base     set_primary_key "actor_id"     has_and_belongs_to_many :film, :join_table => "film_actor" end
As you can see, we can specify the name of the join table, meaning you can adapt active record to any naming convention you might have. Of course we will also want to modify app/models/film.rb accordingly:
class Film < ActiveRecord::Base     set_primary_key "film_id"     has_and_belongs_to_many :actor, :join_table => "film_actor" end
And now to see it in action, let’s go back to console.
>> Film.find([423]).each do |film| ?> puts film.title >> film.actor.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. There are actually many, many other little tweaks and knobs you can get into to make Active Record bend to your desires should you need them. You can peruse the [http://api.rubyonrails.com/ api documentation] if you like but generally I tend to wait to see if I run into trouble and then looking for the answer I need. Once you get past the first table or two though, you generally don’t need much more to get going.