ActiveRecord is the ORM layer (see the section Connecting Rails to a Database) 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.
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. (There are a few exceptions: see the section, Many-to-many Relationships for details.) In the case of our Intranet application, the models are organised as follows:
|
Table |
Model class |
File containing class definition (in |
|---|---|---|
|
people |
Person |
person.rb |
|
companies |
Company |
company.rb |
|
addresses |
Address |
address.rb |
We haven't built any of these yet, but we will shortly.
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 (see Chapter 3 for instructions on how to locate it) 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.
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.
In this section, we're going to develop a series of migrations to recreate the database structure outlined in Chapter 2.
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.
Note
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.
Referring back to the data structure in Chapter 2, 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 called name, 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
idcolumn for the table: Rails will infer that we need this and invisibly add the column definition for us. first_name, last_name, andemailare 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=> falseto prevent the insertion of null values into those columns.- The
gendercolumn was specified in the data structure as having the MySQL data typeENUM. However, to keep the code database-agnostic, we'll create this as a one character:stringfield in the migration. We will leave management of the content of the column (i.e. it should contain "M" or "F") to the model: see the section Checking for Inclusion in a Range of Values for details of how we'll implement this. - The
address_idcolumn references the ID column of records in theaddressestable; thecompany_idcolumn references the ID column of records in thecompaniestable. We'll be creating the migrations for these tables and discussing how to define table-to-table relationships later in this chapter (see the section Associations between Models, later in this chapter). - The
created_atandupdated_atcolumns have a special meaning in Rails: see the tip box below.
Note
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.
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.
|
Migration column type... |
Converts to MySQL field type... |
Available options1 |
|---|---|---|
|
:binary |
TINYBLOB, BLOB, MEDIUMBLOB, or LONGBLOB2 |
:limit => 1 to 4294967296 (default = 65536)2 |
|
:boolean |
TINYINT(1) |
- |
|
:date |
DATE |
- |
|
:datetime |
DATETIME |
- |
|
:decimal |
DECIMAL |
:precision => 1 to 63 (default = 10) :scale => 0 to 30 (default = 0)3 |
|
:float |
FLOAT |
- |
|
:integer |
INT |
:limit => 1 to 11 (default = 11) |
|
:primary_key |
int(11) auto_increment primary key |
- |
|
:string |
varchar |
:limit => 1 to 255 (default = 255) |
|
:text |
TINYtext, TEXT, MEDIUMTEXT, or LONGTEXT2 |
:limit => 1 to 4294967296 (default = 65536)2 |
|
:time |
time |
- |
|
:timestamp |
datetime |
- |
All column types accept a :null or :default option:
:nullThe default value for this is true (i.e. the field's value can be null in the database). Set:null => falseif you don't want to allow nulls in the database field, e.g.:defaultSpecify the default value for the database field when new records are added to the table. The value you specify should be of the correct data type for the column, e.g.t.column :completed, :default => true(for a:booleancolumn)t.column :size, :default => 1(for an:integercolumn)t.column :name, :default => 'Unknown'(for a:stringcolumn)t.column :reminder_on, :default => Time.now(for a:datetime, :date, :timeor:timestampcolumn)Note that the default value should match the data type of the column (not the field). For example, if you were using MySQL and had a :boolean column, even though boolean fields are represented internally in MySQL as 1 digit TINYINT fields, you would specify the :default as true or false (not 1 or 0). This keeps your migrations portable to other database back-ends (for example, while MySQL just emulates booleans, some database back-ends have a native boolean data type, and a value of 1 or 0 might not make sense).
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
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 usedcreate_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 calledold_table_nametonew_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, andcolumn_optionsfollow the principles described in the section Defining Columns in Migrations.remove_column(table_name, column_name)Remove the columncolumn_namefrom the tabletable_name.
Note
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 optimised 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).
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:
- It checks the current version of the database. This is stored in an auto-generated table called
schema_infoin the database, containing a single record with a single field,version. The value of this field is the current version of the database. If theschema_infotable doesn't exist, it is created the first time you run a migration. - The migrations available in
db/migrateare checked. Any migrations with version numbers higher than the version stored in theschema_infotable are applied, lowest-numbered first. - The database version number is updated in
schema_info.
If the command completes successfully, you should see this:
$ 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!
Note
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.
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.