7.3. Object-Relational Mapping

.NET developers are used to dealing with Connection, Command, Parameter, DataAdapter, DataReader, DataSet, DataTable, DataView, and so on. Most would probably agree that ADO.NET is very powerful, but its architecture veers on the complex side. ActiveRecord, on the other hand, strives to achieve elegance and productivity through simplicity. It all revolves around one concept, the model, which is a class that inherits from the ActiveRecord::Base class. As previously stated, that model represents a certain table in the database, and its instances represent the records within that table. It couldn't get much easier than that.

The tradeoff is one that's typical of all highly abstract languages, libraries, or framework: a loss in flexibility, which is usually fully justified by the advantage in productivity and maintainability of the code. For example, a DataTable in .NET can represent an arbitrary table in the database, whereas ActiveRecord has certain, conventional expectations on the nature of the table. Try to create a table lacking an id column and then use the corresponding model, and you'll see what I mean.

The previous chapters employed the scaffold generator as a way to get a head start when it comes to building an application that would work with articles and comments. That command generated a whole lot of code for you, including your models. There will be times, though, when you'd like to create a model without the need to generate a whole resource and all the extra files like controllers and view templates. In such instances, you could create the file by hand within the appmodels directory of your project, but there is a better way. The easiest way to generate a model is to use the model generator.

7.3.1. Generating Models

You don't want to disrupt the existing blog application just to try out new things, so let's create a "throw away" Rails application to try out a few tricks with. Go ahead and create the application chapter7:

C:projects> rails chapter7
C:projects> cd chapter7

And also create the databases for it:

C:projectschapter7>rake db:create:all

Now you are ready to use the model generator. This works in a very similar fashion to the scaffold generator you used before. From the chapter7 directory, try the following:

C:projectschapter7> ruby script/generate model recipe title:string
instructions:text calories:integer
      exists  app/models/
      exists  test/unit/
      exists  test/fixtures/
      create  app/models/recipe.rb
      create  test/unit/recipe_test.rb
      create  test/fixtures/recipes.yml
      create  db/migrate
      create  db/migrate/20080806011444_create_recipes.rb

This command creates the model class Recipe in appmodels ecipe.rb, a unit test in testunit ecipe_test.rb, a test fixture in testfixtures ecipes.yml, and a migration in dbmigrate20080806011444_create_recipes.rb. We discuss testing models at the end of this chapter, but for now consider the code for the model and the migration file:

# appmodels
ecipe.rb
class Recipe < ActiveRecord::Base
end

# dbmigrate20080806011444_create_recipes.rb
class CreateRecipes < ActiveRecord::Migration
  def self.up
    create_table :recipes do |t|
      t.string :title
      t.text :instructions
      t.integer :calories

      t.timestamps
    end
  end

  def self.down
    drop_table :recipes
  end
end

Notice that you have to specify attributes only because the table recipes didn't exist already in the database, so it's convenient to have a migration file generated for you. If the recipes table already existed in the database, you could simply generate your model by running ruby script/generate model recipe.

Because you generated a migration file, you can run the db:migrate Rake task to create the table in the database (specified in configdatabase.yml for the development environment); for example:

C:projectschapter7> rake db:migrate
(in C:/projects/chapter7)
== CreateRecipes: migrating ====================================
-- create_table(:recipes)
   -> 0.1250s
== CreateRecipes: migrated (0.1250s) ===========================

7.3.2. Generating Migrations

Besides the scaffold and model generators, migration files also have their own dedicated generator. The migration generator creates a migration class for you within a "timestamped" file in dbmigrate.

Say that you realize that you need to add a chef column to the recipes table; you could do so by running ruby script/generate migration add_chef_column (or AddChefColumn) and then modifying the migration class yourself, in order to add the column within the up class method. This would work, but for the common action of adding and removing columns, the migration generator offers an automated way of accomplishing the same results. As long as you name your migration in the format AddAttributesToModel or RemoveAttributesFromModel, you will be able to pass a list of attributes to the generator just like you did with scaffold and model.

In this case, you only need to add one column to your table (that is, chef), so you'll run the following:

C:projectschapter7> ruby script/generate migration AddChefToRecipe chef:string
      exists  db/migrate
      create  db/migrate/20080806174246_add_chef_to_recipe.rb

In a real application, chef would probably be stored in a table of its own, and you would add a foreign key field instead.

The name of the generated file is the snake_case version of the CamelCase string you provided in input, so that reading the generated file name is sufficient to make its purpose obvious. You won't have to wonder what that migration file does.

The migration name can also be passed to the generator in snake_case format.

The class definition contained within is as follows:

class AddChefToRecipes < ActiveRecord::Migration
  def self.up
    add_column :recipes, :chef, :string
  end

  def self.down
    remove_column :recipes, :chef
  end
end

The highlighted lines show how the add_column and remove_column were added automatically for you. If you have to add multiple columns in the same migration file, you can do so by naming the migration accordingly (for example, AddCol1Col2ToRecipe) and then passing to the generator all the attributes in order (for example, col1:string col2:integer).

Aside from create_table, drop_table, add_column, and remove_column, many other schema altering methods are available. A few common ones are change_table, rename_table, change_column, change_column_default, remove_columns, add_index, and remove_index. Consult the documentation for the class ActiveRecord::ConnectionAdapters::SchemaStatements to obtain a complete list and examples of how to use them.

You can find the documentation for this class online at http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html. Check http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/TableDefinition.html#M001150 for a list of column options.

Migrations are not only used to modify and add new tables. They can also be used to migrate data. Any valid Ruby code is in fact fair game, as long as your migration affects the database, be it by inserting a bunch of records or by actually modifying its schema.

7.3.2.1. Customizing Migrations

Real-world projects tend to have rather advanced migration files. Besides the standard aforementioned methods, it isn't uncommon to see custom SQL statements. It isn't hard to imagine a need for this. The first time you want to add some triggers to your database, you'll be faced with the choice of doing it outside of the migration realm or executing the proper SQL statements to create and drop the trigger within your migration file. Arbitrary SQL statements can be executed by the method execute.

Experienced developers tend to push this further and create their own methods in addition to the built-in ones, whenever they need to implement custom functionality. These are usually organized in a convenient module that can be "extended"/"mixed in" by the migration class that needs its methods. Let's clarify this with a practical example.

In the previous chapter the tables articles and comments had a one-to-many relationship but ActiveRecord didn't define a foreign key constraint in the database for you. The reason for this is that many developers in the Rails community prefer to enforce this, and other, constraints at an application level, as opposed to the more conventional database level.

If you'd like to define actual foreign key constraints, you may be surprised to learn that there are no methods for adding foreign keys from within migrations. Luckily, you have a couple of options to add custom SQL statements that'll do the trick.

The first approach is to use the :options argument for a column when defining or altering a table definition. For example:

create_table :books do |t|
  t.string :title
  t.string :author
  t.string :isbn
  t.text :description
  t.integer :category_id, :null => false, :options => "CONSTRAINT fk_books_
category_id REFERENCES categories(id)"

  t.timestamps
end

The create_table method accepts a :force option that can be used to specify that a table should be dropped and re-created if it already exists in the database (for example, create_table :books, :force => true) when running migration tasks.

A more flexible approach is possible thanks to the execute method. Say you have the following migration file:

class AddCategoryIdToBook < ActiveRecord::Migration
  def self.up
    add_column :books, :category_id, :integer
  end

  def self.down
    remove_column :books, :category_id
  end
end

This adds (and removes in the down method) the category_id column to the hypothetical books table. To add a foreign key constraint, you could then transform it into the following:

class AddCategoryIdToBook < ActiveRecord::Migration
  def self.up
    add_column :books, :category_id, :integer

    execute %(
      alter table books
      add constraint fk_books_category_id
      foreign key (category_id)
      references categories(id)
    )
  end

  def self.down
    execute %(
      alter table books
      drop foreign key fk_books_category_id
    )

    remove_column :books, :category_id
  end
end

The method execute accepts a string (and optionally a second one for the logs); in this case the string is defined within %( and ) so as to easily span multiple lines.

This works but it clutters up the two up and down methods a bit, and it leads you to "repeat yourself" if you were to add more foreign key constraints to the database. You can improve upon this by moving the code into two separate methods (plus one helper method to define the constraint's name) as shown in Listing 7-2.

Example 7.2. Adding Foreign Keys to Migrations

class AddCategoryIdToBook < ActiveRecord::Migration
  def self.up
    add_column :books, :category_id, :integer
    add_foreign_key :books, :category_id, :categories
  end

  def self.down
    drop_foreign_key :books, :category_id
    remove_column :books, :category_id
  end

  def self.add_foreign_key(table, column, referenced_table)
    execute %(
      alter table #{table}
      add constraint #{fk_name(table, column)}
      foreign key (#{column})
      references #{referenced_table}(id)
    )
  end

  def self.drop_foreign_key(table, column)
    execute %(
      alter table #{table}
      drop foreign key #{fk_name(table, column)}
    )
  end

  def self.fk_name(table, column)
    "fk_#{table}_#{column}"
  end
end

This is definitely nicer, but it still doesn't solve the problem that these methods will not be available in a different migration file. Embracing the DRY principle, let's improve this approach further. You can move these three methods into a module so that they will be available in any migration file that needs them.

If you were to create a migration_helpers.rb file within the lib directory in your projects, the code required for the module would be the one shown in Listing 7-3.

Example 7.3. libmigration_helpers.rb

module MigrationHelpers
  def add_foreign_key(table, column, referenced_table)
    execute %(
      alter table #{table}
      add constraint #{fk_name(table, column)}
      foreign key (#{column})
      references #{referenced_table}(id)
    )
  end

  def drop_foreign_key(table, column)
    execute %(
      alter table #{table}
      drop foreign key #{fk_name(table, column)}
    )
  end

  def fk_name(table, column)
    "fk_#{table}_#{column}"
  end
end

And the migration file would simply become Listing 7-4.

Example 7.4. A Migration Class Taking Advantage of MigrationHelpers

class AddCategoryIdToBook < ActiveRecord::Migration
  extend MigrationHelpers

  def self.up
    add_column :books, :category_id, :integer
    add_foreign_key :books, :category_id, :categories
  end

  def self.down
    drop_foreign_key :books, :category_id
    remove_column :books, :category_id
  end
end

When this migration is applied, the method add_foreign_key will be executed, adding a foreign key constraint to the database. When the migration is rolled back, the drop_foreign_key method is executed, dropping the foreign key constraint from the database.

If, at a later stage, a different migration file needs to utilize add_foreign_key and drop_foreign_key, it will be able to do so as long as you add an extend MigrationHelpers statement within the class definition.

Also notice how the methods within the module were not prefixed by self. because extend takes care of adding these to AddCategoryIdToBook as class methods.

A few considerations are in order:

• SQLite's support for foreign keys is sketchy at best. The first approach (through :options) would work with SQLite, but the foreign key constraint would not be enforced. A workaround exists, through triggers, as you can read online at http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html. This is not a huge deal, because few people adopt a file-based database system during production, but if you opt to go this route, you should check out this link.

• The second approach that alters the existing table through the execute method in order to add a foreign key constraint will not work with SQLite. If you try this method, you'll obtain an exception pointing out a syntax error near the word "constraint" in the SQL syntax.

• The issue with SQLite perfectly illustrates how executing DDL statements directly will often lead you to be database-specific, losing the ability to switch from one database type to another, without altering the existing code. This doesn't usually matter and shouldn't be a big concern. If you really need the application to be compatible with several database systems, you can always use conditional statements to execute one, among several versions of the same query, depending on the adapter in use.

• The preceding example served us well as a means of exposing a few techniques that enable you to gain flexibility. Many developers, however, would probably opt in favor of using a plugin such as Foreign Key Migrations, which is available at http://www.redhillonrails.org/foreign_key_migrations.html.

Finally, it's important to note that migrations are great, but you are not forced to use them. When you create a model Article, the articles table is supposed to be there, but ActiveRecord doesn't care how the table was created, just that it exists in the database. For this reason, some people prefer to approach the database schema creation and evolution the traditional way, opting to skip the migration "framework" in favor of what they are already accustomed to. I recommend that you give migrations a chance though; they are a very useful feature and an integral part of the Rails developer mindset.

7.3.3. ORM Conventions

ActiveRecord endorses the Convention over Configuration principle to its core. While developing the basic blog application in the past two chapters you never had to write XML configuration files. The mapping between the articles and comments tables and their respective models, Article and Comment, was done automatically for you by convention. Naming conventions represent such a fundamental part of being able to work with ActiveRecord that it's worth exploring them further.

7.3.3.1. Mapping Tables to Models

A model is a subclass of ActiveRecord::Base. For each model that exists in your application, ActiveRecord expects a corresponding table represented by that model (unless you specify otherwise, as you'll see later on). As repeated ad nauseam by now, if the model class is Article, then the table that you expect to find in the database would be articles. But what about irregular plurals or other languages? An Italian developer may decide to call his model Articolo, Italian for article; would ActiveRecord look for the table articoli, the correct Italian plural, or append an s as is the norm in English, and expect a table articolos (which would be okay for Spanish, but not for Italian)?

Besides the fact that choosing a different language other than English for identifiers can be considered a poor development practice, answering the preceding questions will shed some light on how the mapping convention really works.

ActiveRecord determines the table name starting from the model name by ultimately taking advantage of the pluralize method of a class called Inflector (defined by ActiveSupport).

The fact that Inflector is defined by the ActiveSupport library implies that you can use it in your own programs, outside of Rails, by simply requiring the gem activesupport.

Let's take it for a spin. Start the Rails console from the chapter7 directory:

C:projectschapter7> ruby script/console

From within the console, you can find the table name for model names that are regular nouns in the English language:

>> "article".pluralize
=> "articles"
>> "comment".pluralize
=> "comments"

Plural nouns are usually left untouched:

>> "money".pluralize
=> "money"
>> "accounts".pluralize
=> "accounts"

Irregular words tend to be pluralized correctly as well:

>> "mouse".pluralize
=> "mice"
>> "datum".pluralize
=> "data"
>> "cow".pluralize
=> "kine"
>> "person".pluralize
=> "people"

By the way, it's the archaic kine and not cows, all thanks to this ticket http://dev.rubyonrails.org/ticket/4929. Consider it to be an inside joke of sorts.

7.3.3.2. Inflector Isn't Perfect

To be exact, ActiveRecord doesn't directly use the pluralize method to determine the table name, but rather the tableize method, which takes advantage of pluralize. Unlike this, tableize takes care of lowercasing the model name and properly pluralizing the last word of a composite model name as shown in the following example:

>> "Bank".tableize
=> "banks"
>> "BankAccount".tableize
=> "bank_accounts"

Notice how model names should be in CamelCase, whereas tables are supposed to adopt the snake_case convention.

The opposite of tableize is classify, which turns a table name into its model name. This in turn uses the singularize method. Good Ruby code uses snake_case for methods and variables; because of this point, it's important to adopt snake_case for the name of the columns in the table, so that the corresponding attributes will do the same thing as well. For example, use interest_rate not interestRate as a column name.

Unfortunately, the Inflector class is not as reliable as you might expect it to be. For example, the following irregular nouns are handled incorrectly:

>> "nucleus".pluralize
=> "nucleus"
>> "Phenomenon".tableize
=> "phenomenons"
>> "Curriculum".tableize
=> "curriculums"
>> "business".classify
=> "Busines"

In the last line we intentionally tricked the Inflector by invoking the classify method on a singular noun ending in s. Notice how classify returns a string. Use "my_table".classify.constantize when you need to get the actual class.

"Bug! Let's report it!" you may be exclaiming. Well, not so fast. The reality is that the Inflector tries to pluralize and singularize most nouns correctly, but it's accepted that it won't do so for all of the irregular ones. The developers decided to freeze its code a while ago, primarily as a means of maintaining backward compatibility. Whenever you're in doubt, use the console and the methods mentioned previously to verify model and table name mapping.

The easiest way to actually know what tables a given model is mapping to is to use the table_name method (for example, MyModel.table_name).

The pluralization offered by the Inflector is more or less as accurate as that of a young child. But that's okay, because you can change the rules and add exceptions to the Inflector.

Another issue with the Inflector, at least for some, is the fact that it only pluralizes and singularizes English words:

>> "articolo".pluralize
=> "articolos"
>> "conto".pluralize
=> "contos"

7.3.3.3. Adding New Inflection Rules

Any Rails application has a configinitializersinflections.rb file generated by default. This file contains the following commented out lines:

# Be sure to restart your server when you modify this file.

# Add new inflection rules using the following format
# (all these examples are active by default):
# Inflector.inflections do |inflect|
#   inflect.plural /^(ox)$/i, '1en'
#   inflect.singular /^(ox)en/i, '1'
#   inflect.irregular 'person', 'people'
#   inflect.uncountable %w( fish sheep )
# end

If you need to add new rules, this is the file that you need to modify. The plural, singular, irregular, and uncountable methods provide you with an easy-to-use DSL to customize the inflections. For example, go ahead and add the following to that file within the chapter7 project:

Inflector.inflections do |inflect|
  inflect.irregular 'curriculum', 'curricula'
end

Save the file, exit from the console (using exit) if you haven't already done so, and then start it again.

You should now be able to see that the old rules still apply, as well as the new inflection you defined:

C:projectschapter7> ruby script/console
Loading development environment (Rails 2.2.2)
>> "curriculum".pluralize
=> "curricula"
>> "recipe".pluralize
=> "recipes"

If you want to define your own set of rules from scratch, getting rid of the default ones, you can do so by first clearing them with the clear method. This would really be helpful only when your database tables consistently follow a precise naming convention that's different than the default one, or when you are creating a set of rules for a specific language. For example, the following snippet would correctly cover most Italian words (only the regular ones), but would lose the ability to pluralize English words:

Inflector.inflections do |inflect|
 inflect.clear

inflect.singular /^(w]*)i/i, '1o'
 inflect.plural /^([w]*)o/i, '1i'
 inflect.singular /^([w]*)e/i, '1a'
 inflect.plural /^([w]*)a/i, '1e'
end

You can also be more specific than that, and use clear :plurals, clear :singulars, or clear :uncountables.

The regular expressions may make it look scary, but it's really rather simple. Words ending in o should be pluralized by replacing the o with an i and vice versa; words ending in i should be singularized with an o. Similarly, strings ending with an a should be pluralized by replacing that final a with an e.

Notice that the occurrence of a string that matches the pattern between the round brackets is "captured" by 1, which is then used to define the new word (in the second parameter passed to the methods singular and plural). For example, if the word is "articolo," 1 will capture "articol," to which i is then appended (if you are pluralizing).

7.3.3.4. Setting a Custom Table Name for a Model

The path of least resistance is available anytime you stick to the convention for table and model naming. There are times, however, when this is not possible or convenient due to restrictions that are beyond the developer's control. In such instances, overwriting the convention is possible.

Though you could simply modify the inflection rules to influence the mapping as needed, this is not always a clean solution. For example, convincing the Inflector that the plural of recipe is my_cookbook has all the characteristics of a so called "code smell." Luckily for you, ActiveRecord allows you to set the table name for a model explicitly.

As mentioned earlier, at any time you can verify the table represented by a model by using the class method table_name. From the same console for the chapter7 project, try to use this as follows:

>> Recipe.table_name
=> "recipes"

The table represented by Recipe is recipes, as expected; but let's modify your model to map it with a custom table named my_cookbook, from the preceding example:

class Recipe < ActiveRecord::Base
  self.table_name = "my_cookbook"
end

Similarly, you can also use the (macro style) method set_table_name:

class Recipe < ActiveRecord::Base
  set_table_name :my_cookbook
end

Having changed one or more models, you'll need to impart the reload! command in the console. Once you've done that, you'll see that the table associated with the model is now my_cookbook:

>> reload!
Reloading...

=> true
>> Recipe.table_name
=> "my_cookbook"

Notice that this will work whether or not that table exists. However, to do anything useful with such a model, the table obviously needs to exist in the database.

7.3.3.5. Specifying a Prefix or Suffix

Along with setting a custom table name, it's also possible to let all the Inflector rules be applied as usual, except for a custom prefix or suffix. For example, say that the table you'd like Recipe to represent is w7c_recipes, where w7c is a department code. You can then set the table_name_prefix as follows:

class Recipe < ActiveRecord::Base
  self.table_name_prefix = "w7c"
end

Similarly, you can set the table_name_suffix.

Setting a prefix or suffix for a single model in this way is not a more concise option than simply using set_table_name. Imagine for a moment, though, that all the tables in your application need a w7c_ prefix. In this sort of scenario you could leverage the setter not for a particular model, but for the ActiveRecord::Base class, and all the models in the application would automatically inherit from it. An easy way to do this is to place the following code within an initializer of the application (for example, in a Ruby file within configinitializers):

class ActiveRecord::Base
    self.table_name_prefix  = "w7c"
end

7.3.3.6. Using a Different Primary Key

Conventionally ActiveRecord expects tables to have an id primary key that's an auto-incrementing integer field.

By the same convention, foreign key columns are supposed to be named by appending id to the singular name of the referenced table (for example, article_id).

This convention too can be easily overwritten, even though it's not recommended to do so unless you really have to. ActiveRecord's productivity comes from sticking to its conventions whenever possible. The method used for this task is set_primary_key.

If you want to inform the model that the primary key column is guid, not id, you can do so as follows (in this case, the model is generically named MyModel):

class MyModel < ActiveRecord::Base
  set_primary_key "guid"
end

You can also opt for self.primary_key = "guid".

The method also accepts a block, if the first parameter (in this case "guid") is missing or evaluates to false. In such cases, the returning value of the block will be assigned as the value for the primary key column.

By convention, migrations will generate tables with an id primary key column. This too can effortlessly be overwritten, so that it matches the primary key you set in the model:

create_table(:my_models, :primary_key => 'guid') do |t|
  # ... some column definitions
end

ActiveRecord doesn't support composite primary keys; if you need them in order to support a legacy schema, you might want to check out the Composite Primary Keys plugin at http://compositekeys.rubyforge.org.

Migrations also allow you to specify that no primary key should be defined. This is usually important when creating an intermediary table for a many-to-many relationship that has no corresponding model. The documentation for the create_table method offers this example:

create_table(:categories_suppliers, :id => false) do |t|
   t.column :category_id, :integer
   t.column :supplier_id, :integer
end

The ability to overwrite the conventions described previously should be enough to allow you to use most legacy schemas. There is also a plugin that takes the opposite approach and automatically creates models from an existing database; it's called Magic Models and is available at http://magicmodels.rubyforge.org.

For further details and tips for working with legacy databases, pop by the wiki page http://wiki.rubyonrails.com/rails/pages/HowToUseLegacySchemas. Rails' wiki is admittedly in need of a clean up, so you may find the information presented to be disorganized and in some instances obsolete. That said, if you dig through it, you'll certainly find valuable suggestions.