In the previous article, we covered setting up a new Rails application and connecting it to a database. In this article, we take this a little further and deal with ActiveRecord, Migrations, Models, the Scaffold, and demonstrate how to complete the Database with the required tables using migrations

ActiveRecord, Migrations, and Models

ActiveRecord is the ORM layer (see the section Connecting Rails to a Database in the previous article) used in Rails. It is used by controllers as a proxy to the database tables. What's really great about this is that it protects you against having to code SQL. Writing SQL is one of the least desirable aspects of developing with other web-centric languages (like PHP): having to manually build SQL statements, remembering to correctly escape quotes, and creating labyrinthine join statements to pull data from multiple tables. ActiveRecord does away with all of that (most of the time), instead presenting database tables through classes (a class which wraps around a database table is called a model) and instances of those classes (model instances). The best way to illustrate the beauty of ActiveRecord is to start using it.

Model == Table

The base concept in ActiveRecord is the model. Each model class is stored in the app/models directory inside your application, in its own file. So, if you have a model called Person, the file holding that model is in app/models/person.rb, and the class for that model, defined in that file, is called Person. Each model will usually correspond to a table in the database. The name of the database table is, by convention, the pluralized (in the English language), lower-case form of the model's class name. In the case of our Intranet application, the models are organized as follows:

We haven't built any of these yet, but we will shortly.

Which Comes First: The Model or The Table?

To get going with our application, we need to generate the tables to store data into, as shown in the previous section. It used to be at this point where we would reach for a MySQL client, and create the database tables using a SQL script. (This is typically how you would code a database for a PHP application.) However, things have moved on in the Rails world.

The Rails developers came up with a pretty good (not perfect, but pretty good) mechanism for generating databases without the need for SQL: it's called migrations, and is a part of ActiveRecord. Migrations enable a developer to generate a database structure using a series of Ruby script files (each of which is an individual migration) to define database operations. The "operations" part of that last sentence is important: migrations are not just for creating tables, but also for dropping tables, altering them, and even adding data to them.

It is this multi-faceted aspect of migrations which makes them useful, as they can effectively be used to version a database (in much the same way as Subversion can be used to version code). A team of developers can use migrations to keep their databases in sync: when a change to the database is made by one of the team and coded into a migration, the other developers can apply the same migration to their database, so they are all working with a consistent structure.

When you run a migration, the Ruby script is converted into the SQL code appropriate to your database server and executed over the database connection. However, migrations don't work with every database adapter in Rails: check the Database Support section of the ActiveRecord::Migration documentation to find out whether your adapter is supported. At the time of writing, MySQL, PostgreSQL, SQLite, SQL Server, Sybase, and Oracle were all supported by migrations.

Another way to check whether your database supports migrations is to run the following command in the console (the output shown below is the result of running this using the MySQL adapter):

  >> ActiveRecord::Base.connection.supports_migrations?
  => true

We're going to use migrations to develop our database, so we'll be building the model first. The actual database table will be generated from a migration attached to the model.

Building a Model with Migrations

In this section, we're going to develop a series of migrations to recreate the database structure outlined in Chapter 2 of the book Ruby on Rails Enterprise Application Development: Plan, Program, Extend.

First, we'll work on a model and migration for the people table. Rails has a generate script for generating a model and its migration. (This script is in the script directory, along with the other Rails built-in scripts.) The script builds the model, a base migration for the table, plus scripts for testing the model. Run it like this:

  $ ruby script/generate model Person
    exists app/models/
    exists test/unit/
    exists test/fixtures/
    create app/models/person.rb
    create test/unit/person_test.rb
    create test/fixtures/people.yml
    exists db/migrate
    create db/migrate/001_create_people.rb

Note that we passed the singular, uppercase version of the table name ("people" becomes "Person") to the generate script. This generates a Person model in the file app/models/person.rb; and a corresponding migration for a people table (db/ migrate/001_create_people.rb). As you can see, the script enforces the naming conventions, which connects the table to the model.

The migration name is important, as it contains sequencing information: the "001" part of the name indicates that running this migration will bring the database schema up to version 1; subsequent migrations will be numbered "002...", "003..." etc., each specifying the actions required to bring the database schema up to that version from the previous one.

The next step is to edit the migration so that it will create the people table structure. At this point, we can return to Eclipse to do our editing. (Remember that you need to refresh the file list in Eclipse to see the files you just generated).

Once, you have started Eclipse, open the file db/migrate/001_create_people.rb. It should look like this:

    class CreatePeople < ActiveRecord::Migration
        def self.up
            create_table :people do |t|
                # t.column :name, :string
            end
        end
        def self.down
            drop_table :people
        end
    end

This is a migration class with two class methods, self.up and self.down. The self.up method is applied when migrating up one database version number: in this case, from version 0 to version 1. The self.down method is applied when moving down a version number (from version 1 to 0).

You can leave self.down as it is, as it simply drops the database table. This migration's self.up method is going to add our new table using the create_table method, so this is the method we're going to edit in the next section.

Ruby syntax
Explaining the full Ruby syntax is outside the scope of this book. For our purposes, it suffices to understand the most unusual parts. For example, in the create_table method call shown above:,

    create_table :people do |t|
        t.column :title, :string
        ...
    end

The first unusual part of this is the block construct, a powerful technique for creating nameless functions. In the example code above, the block is initialized by the do keyword; this is followed by a list of parameters to the block (in this case, just t); and closed by the end keyword. The statements in-between the do and end keywords are run within the context of the block.

Blocks are similar to lambda functions in Lisp or Python, providing a mechanism for passing a function as an argument to another function. In the case of the example, the method call create_table:people is passed to a block, which accepts a single argument, t; t has methods called on it within the body of the block. When create_table is called, the resulting table object is "yielded" to the block; effectively, the object is passed into the block as the argument t, and has its column method called multiple times.

One other oddity is the symbol: that's what the words prefixed with a colon are. A symbol is the name of a variable. However, in much of Rails, it is used in contexts where it is functionally equivalent to a string, to make the code look more elegant. In fact, in migrations, strings can be used interchangeably with symbols.

Building a complete Ruby on Rails business application from start to finish Create a non-trivial, business-focused Rails application Solve the real-world problems of developing and deploying Rails applications in a business environment Apply the principles behind Rails development to practical real-world situations For more information, please visit: http://www.packtpub.com/Ruby-on-Rails-Enterprise-Application-Open-Source/book/

Converting a Data Structure into a Migration

Referring back to the data structure in Chapter 2 of the book Ruby on Rails Enterprise Application Development: Plan, Program, Extend, we can build the people table with this self.up method:

    def self.up
        create_table :people do |t|
            t.column :title, :string
            t.column :first_name, :string, :null => false
            t.column :last_name, :string, :null => false
            t.column :email, :string, :limit => 100, :null => false
            t.column :telephone, :string, :limit => 50
            t.column :mobile_phone, :string, :limit => 50
            t.column :job_title, :string
            t.column :date_of_birth, :date
            t.column :gender, :string, :limit => 1
            t.column :keywords, :string
            t.column :notes, :text
            t.column :address_id, :integer
            t.column :company_id, :integer
            t.column :created_at, :timestamp
            t.column :updated_at, :timestamp
        end
    end

Arguments to the column method specify the name of the column, the type of the column, and some optional parameters. For example:

    t.column :name, :string

The above line of code specifies that the table (t) should contain a column calledname, which should be of data type string.

The extra :limit option passed in some of the column method calls, plus the various column data types, are discussed in the next section. There are a few of things to note first, though:

• There's no need to specify the id column for the table: Rails will infer that we need this and invisibly add the column definition for us.
• first_name, last_name, and email are the only columns which cannot contain null values: together they represent the minimum amount of data we need to record about a contact. We mark this by passing :null => false to prevent the insertion of null values into those columns.
• The gender column was specified in the data structure as having the MySQL data type ENUM. However, to keep the code database-agnostic, we'll create this as a one character :string field in the migration. We will leave management of the content of the column (i.e. it should contain "M" or "F") to the model.
• The address_id column references the ID column of records in the addresses table; the company_id column references the ID column of records in the companies table. We'll be creating the migrations for these tables and discussing how to define table-to-table relationships later in this article.
• The created_at and updated_at columns have a special meaning in Rails: see the tip box below.

If you add a column to a table called created_at, created_on, updated_at, or updated_on, Rails will automatically record a timestamp against records in that table without you having to do any extra work:

*_on: When a record is created or updated, the current date is automatically recorded in this column. Give a column with this name a data type of :date.

*_at: When a record is created, the current date and time are automatically recorded in this column. Give a column with this name a data type of :timestamp.

Defining Columns in Migrations

When using migrations, bear in mind that a migration is (by design) a database-agnostic representation of a database. It uses generic data types for columns, like :binary and :boolean, to define the kind of data to be stored in a column.

However, different database servers implement the migration column types in different ways. For example, MySQL doesn't have a boolean data type; so any migration columns you define as :boolean are actually converted into TINYINT(1) fields in the resulting MySQL database table (0 = false, 1 = true). Each migration column type also has a range of extra options you can set, which again modify the definition of the resulting field in the MySQL database.

The table below summarizes the migration column types, how they map to MySQL field data types, and the extra options available.

All column types accept a :null or :default option:

    
• :null
  The default value for this is true (i.e. the field's value can be null in thedatabase). Set :null => false if you don't want to allow nulls in thedatabase field, e.g.
  
  • t.column :first_name, :string, :null => false
    
  Note that if you allow nulls in a field (:null => true or not specified), youdon't need to specify :default => NULL: NULL is already the default for a field, which allows null values.
      
• :default
  Specify the default value for the database field when new records are addedto the table. The value you specify should be of the correct data type for the column, e.g.
  
  1. t.column :completed, :default => true (for a :boolean column)
  2. t.column :size, :default => 1 (for an :integer column)
  3. t.column :name, :default => 'Unknown' (for a :string column)
  4. t.column :reminder_on, :default => Time.now (for a :datetime, :date, :time or :timestamp column)
     

The :limit option on a :blob or :text column specifies the size of the database field in bytes. You can set this directly in bytes, or use a convenience method to specify the size, e.g. 2.kilobytes, 2.megabytes, 2.gigabytes(!). Note that MySQL will actually create a field with a data type, which encompasses the size you specify, i.e.

• 1 to 256 bytes: TINYBLOB or TINYTEXT
• 257 to 65536 bytes (64KiB): BLOB or TEXT
• 65537 to 16777216 bytes (16 MiB): MEDIUMBLOB or MEDIUMTEXT
• 16777217 to 4294967296 bytes (4 GiB): LONGBLOB or LONGTEXT

The :precision option specifies the number of digits to store before the point in a decimal; the :scale option specifies the number of digits to store after the decimal point.

Here are some examples of how to use these options:

    # column to store uploaded images up to 2Mb in size t.column :image, :blob, :limit => 2.megabytes
    # column to store prices (6 digits before decimal point, 2 after)
    t.column :price, :decimal, :precision => 6, :scale => 2
    # column to store whether someone's account is active (defaults to
    false)
    t.column :account_active, :boolean, :default => false
    # column to store someone's birth date (must not be null)
    t.column :birth_date, :date, :null => false

Other Operations Available in a Migration

Migrations can be used to perform operations other than table creation. A complete list is available in the documentation for ActiveRecord::Migration, but here are examples of the more useful ones:

• create_table(table_name, options)
  We've already used create_table; but it's worth mentioning here that you can optionally pass extra arguments to this method, for example:
  

      # drop any existing people table and recreate from scratch create_table(:people, :force => true)
      # create a table with table type "MyISAM" (Rails defaults to InnoDB)
      # using a UTF-8, case-insensitive collation (NB this is
      # MySQL-specific, but the :options argument can be
      # used to pass any other database-specific table creation SQL)
      create_table(:people, :options =>
      'ENGINE MyISAM COLLATE utf8_unicode_ci')
      # rename the primary key to pid (if you don't want or haven't
      # got an ID field in the table)
      create_table(:people, :primary_key => 'pid')

• rename_table(old_table_name, new_table_name)
  Change the name of the table called old_table_name to new_table_name.
  
  
• add_column(table_name, column_name, column_type, column_options)
  This command is similar to the column method we've used already: column_name, column_type, and column_options follow the principles described in the section Defining Columns in Migrations.
  
  
• remove_column(table_name, column_name)
  Remove the column column_name from the table table_name.

As mentioned in the sample code above, the default engine used for a table is InnoDB (which supports foreign keys and transactions). However, InnoDB is not supported by default on all MySQL servers; or it may be that you want to use MyISAM tables (which are optimized for many-read situations) instead. In these situations, you can use the :options argument to create_table to force the table type to MyISAM (see the sample code above).

Running a Migration

A complete migration can be applied to the development database from the command line (inside your application's RAILS_ROOT directory):

  $ rake db:migrate

When you run this command, Rails does the following:

1. It checks the current version of the database. This is stored in an autogenerated table called schema_info in the database, containing a single record with a single field, version. The value of this field is the current version of the database. If the schema_info table doesn't exist, it is created the first time you run a migration.    
2. The migrations available in db/migrate are checked. Any migrations with version numbers higher than the version stored in the schema_info table are applied, lowest-numbered first.    
3. The database version number is updated in schema_info.

  $ rake db:migrate
  (in /home/rory/workspace/Intranet)
  == CreatePeople: migrating ===========================================
  -- create_table(:people)
  -> 0.5130s
  == CreatePeople: migrated (0.5158s) ==================================

You can check the table looks right using the command line MySQL client:

  mysql> describe intranet_development.people;
  +---------------+--------------+------+-----+---------+----------------+
  | Field | Type | Null | Key | Default | Extra |
  +---------------+--------------+------+-----+---------+----------------+
  | id | int(11) | NO | PRI | NULL | auto_increment |
  | title | varchar(255) | YES | | NULL | |
  | first_name | varchar(255) | NO | | NULL | |
  | last_name | varchar(255) | NO | | NULL | |
  | email | varchar(100) | NO | | NULL | |
  | telephone | varchar(50) | YES | | NULL | |
  | mobile_phone | varchar(50) | YES | | NULL | |
  | job_title | varchar(255) | YES | | NULL | |
  | date_of_birth | date | YES | | NULL | |
  +---------------+--------------+------+-----+---------+----------------+
  | Field | Type | Null | Key | Default | Extra |
  +---------------+--------------+------+-----+---------+----------------+
  | gender | varchar(1) | YES | | NULL | |
  | keywords | varchar(255) | YES | | NULL | |
  | notes | text | YES | | NULL | |
  | address_id | int(11) | YES | | NULL | |
  | company_id | int(11) | YES | | NULL | |
  | created_at | datetime | YES | | NULL | |
  | updated_at | datetime | YES | | NULL | |
  +---------------+--------------+------+-----+---------+----------------+
  16 rows in set (0.01 sec)

Finally, we have a database table our application can work with!

Rake
Rake is a Ruby build tool used extensively in Rails. It is similar in scope to Ant for Java or make for C/C++ etc.: a tool designed for automating repetitive tasks around software development. In the case of Rake, this includes running tests, deploying code, maintaining the database, generating documentation, and reporting code statistics.

Rather than attempting to list everything Rake does, we will introduce individual tasks (that's what db:migrate is, a task) as they become useful. If you are incurably curious about what Rake can do for your Rails application, you can see a list of all the available tasks with the command rake -T.

Rolling Back to a Previous Version of the Database

If your table looks wrong, you can roll back to a table-free database with this command:

$ rake db:migrate VERSION=0

Once you get working with migrations, you can replace the "0" in the above command with another version of the database, to either roll forward or back to that version. For example, if you are at version 2 and you run rake db:migrate VERSION=4, Rails will upgrade the database schema from version 2 to 4.

The Scaffold

Rails is supposed to be a rapid application development environment, but so far we just have a back-end database. To get some instant front-end delight, we'll use another Rails feature called scaffolding.

Scaffolding is a monstrously fast and terribly tempting short-cut to create an interface for a model. It can be used to near-instantly (literally) generate some boiler plate pages for performing CRUD (Create, Retrieve, Update, Delete) operations on a database table (via a model). The code is basic and crude, and the interface is ugly as sin; but with minuscule effort, the scaffold enables you to knock together a simple administrative back-end for a database table within seconds.

The scaffold is also a useful learning tool, as it demonstrates the minimum amount of code you need to write in your own controllers. It is also useful in situations where you might be the only person who ever administers the database: if it doesn't need to be fancy, the scaffold plus a few tweaks is a great way to quickly create the administrator views.

To generate a scaffold, you simply need to specify the name of the model you wantto scaffold for. In our case, the model is called Person. Therefore, from inside RAILS_ ROOT, we would run:

  $ ruby script/generate scaffold Person

This produces quite a few files, including controller classes and view templates for all the CRUD actions on that model. Using this generated interface, we can now add records to the people table in our database.

To do this, we need to start our application:

  $ ruby script/server

Then browse to http://localhost:3000/people to see the application in its full glory:

I warned you it would be ugly. But the point is, we now have a working administrative interface, which (with a bit of spit and polish) Rory could show to his colleagues at Acme.

Feel free to play around, but bear in mind we are in the development database, and will likely be destroying all the data at some point by running migrations backwards and forwards.

Alternatives to the basic scaffold
If the scaffold is simply too ugly for you to look at, you could try one of the prettier (but more complicated) alternatives:

The Ajax Scaffold Generator (http://ajaxscaffold.com/) provides virtually the same functionality as the default scaffold, but wraps it in a more responsive interface. It also has enhanced facilities for editing records in one table which are associated with another table.

Streamlined (http://streamlined.relevancellc.com/) is a framework for generating an administrative back-end for a set of Rails model classes. The resulting interface is very rich in functionality and much smoother to use than the scaffold. Streamlined also provides a declarative language for specifying the layout of the administrative interface, how relationships between models are displayed in the interface, plus an authentication framework. On the negative side, the documentation is practically non-existent, and it may well be difficult to figure out how to configure the generated code.

Through the rest of this article, we'll see how to build up the models associated with our new database tables.

Completing the Database

The migration we created previously in this article built just one of the tables in our database. Referring back to the data structure we designed in Chapter 2 of the book Ruby on Rails Enterprise Application Development: Plan, Program, Extend, there are two more database tables to add: companies and addresses. The next two sections give a brief overview of how to create these using migrations. In both cases, the migrations are simple and don't require any commands we haven't already encountered.

The companies Table

Create the model and migration for the companies table from the command line with:

  $ ruby script/generate model Company

Edit db/migrate/002_create_companies.rb and insert this code:

    class CreateCompanies < ActiveRecord::Migration
        def self.up
            create_table :companies do |t|
                t.column :name, :string, :null => false
                t.column :telephone, :string, :limit => 50
                t.column :fax, :string, :limit => 50
                t.column :website, :string
                t.column :address_id, :integer
                t.column :created_at, :timestamp
                t.column :updated_at, :timestamp
            end
        end
        def self.down
            drop_table :companies
        end
    end

The addresses Table

Create the model and migration for the addresses table from the command line with:

  $ ruby script/generate model Address

Edit db/migrate/003_create_addresses.rb and insert this code:

    class CreateAddresses < ActiveRecord::Migration
        def self.up
            create_table :addresses do |t|
                t.column :street_1, :string, :null => false
                t.column :street_2, :string
                t.column :street_3, :string
                t.column :city, :string
                t.column :county, :string
                t.column :post_code, :string, :limit => 10, :null => false
                t.column :created_at, :timestamp
                t.column :updated_at, :timestamp
            end
        end
        def self.down
            drop_table :addresses
        end
    end

Generating the Remaining Tables

Applying the migrations to the database is done via the command line with:

  $ rake db:migrate
  (in /home/demo/workspace/Intranet)
  == CreateCompanies: migrating ======================================
  -- create_table(:companies)
     -> 0.5015s
  == CreateCompanies: migrated (0.5022s) =============================
  == CreateAddresses: migrating ======================================
  -- create_table(:addresses)
     -> 0.2078s
  == CreateAddresses: migrated (0.2089s) =============================

Note that this creates both of the tables: Rails recognizes that our database is at version 1, meaning there are migrations for versions 2 and 3 to be applied.

You can use your MySQL client to check the generated tables. If you've made a mistake, you can roll back to a previous version of the database using:

  $ rake db:migrate VERSION=1

(Replace "1" with the migration number you want to roll back to—see the section Rolling Back to a Previous Version of the Database for more details.) With our database completed, we are now ready to look at fleshing out the basic models, adding validation and table-to-table relationships.

Summary

In this article, we covered Models in brief and looked at building Models with Migrations. We also looked at how to convert a Data Structure into a Migration and several other operations available in a migration. We also saw how to run a migration, and if need be, roll back the Database to an earlier version. Then we covered scaffolding, and finally, came back to using migrations for creation of the various other tables required in order to complete the Database.

Building a complete Ruby on Rails business application from start to finish Create a non-trivial, business-focused Rails application Solve the real-world problems of developing and deploying Rails applications in a business environment Apply the principles behind Rails development to practical real-world situations For more information, please visit: http://www.packtpub.com/Ruby-on-Rails-Enterprise-Application-Open-Source/book

Elliot Smith has worked in IT since 1996: at OpenAdvantage (an open-source solutions center) as a business analyst, as a learning technologist and web developer at the University of Birmingham, England, and as a technical writer for Armada Computer Publications. He runs his own training and consulting company, mooch labs, when he gets a chance. He has an M.Sc. in Artificial Intelligence and a Ph.D. in Computer Science from the University of Birmingham.

Rob Nichols first started using computers during his apprenticeship at Rolls-Royce in the early 1980s. At 23 he decided to change direction and started a degree in Geology and Geography at Cardiff University. By 1995 he had gained a Ph.D. from Bristol University, studying the behavior of quicksand. During his time in Bristol and in a subsequent lectureship at Leeds University, he started using the fledgling Internet to communicate with co-workers, gather information, and present Geological information in the form of his first web pages. Following his return to Britain from a lectureship in U.S.P. Fiji, Rob found himself without another lectureship position to go on to. So, changing direction again, he started working for a U.K. computer manufacturer, where he rose to the position of Engineering Manager, managing a team of seventy maintenance and networking engineers, and support staff. Following the collapse of the U.K. computer market in 2002 he moved on to the role of IT manager for a small business providing products and services to the water industry. In this role, Rob has had great success developing intranet-based business applications that streamline business processes, save time, and increase efficiency. In so doing he has transformed the IT department from a business cost to a profit generator by reducing costs and thereby increasing margins. When not working with computers, Rob and his wife reside happily in a small Midlands town in England, where he writes scripts for the local movie-makers club, and photographs the local wildlife.