Recipe 3.3. Developing Your Database with Migrations


Problem

You need to change your database schema: you want to add columns, delete columns, or otherwise modify your table definitions. If things go wrong, you'd like to be able to roll back your changes.

For example, you are working with a team of developers on a database that manages books. As of January 1, 2007, the book industry began using a new, 13-digit ISBN format to identify all books. You want to prepare your database for this change.

What complicates the upgrade is that the developers in your group may not be ready for the conversion all at once. You want a way to organize how this change is applied to each instance of the database. Each incremental change should be in version control, and ideally you'll be able to revert changes if necessary.

Solution

Use Active Record migrations, and define the conversion process in two different stages.

Use the generator to create the two migrations:

$ ruby script/generate migration AddConvertedIsbn       create  db/migrate       create  db/migrate/001_add_converted_isbn.rb $ ruby script/generate migration ReplaceOldIsbn       exists  db/migrate       create  db/migrate/002_replace_old_isbn.rb

Define the first migration as follows. Include convert_isbn as a helper method containing the ISBN conversion algorithm.

db/migrate/001_add_converted_isbn.rb:

class ConvertIsbn < ActiveRecord::Migration   def self.up     add_column :books, :new_isbn, :string, :limit => 13     Book.find(:all).each do |book|         Book.update(book.id, :new_isbn => convert_isbn(book.isbn))     end   end   def self.down     remove_column :books, :new_isbn   end   # Convert from 10 to 13 digit ISBN format   def self.convert_isbn(isbn)     isbn.gsub!('-','')     isbn = ('978'+isbn)[0..-2]     x = 0        checksum = 0     (0..isbn.length-1).each do |n|       wf = (n % 2 == 0) ? 1 : 3        x += isbn.split('')[n].to_i * wf.to_i      end     if x % 10 > 0       c = 10 * (x / 10 + 1) - x       checksum = c if c < 10     end     return isbn.to_s + checksum.to_s   end end

The second stage of the conversion looks like this:

db/migrate/002_replace_old_isbn.rb:

class ReplaceOldIsbn < ActiveRecord::Migration   def self.up     remove_column :books, :isbn     rename_column :books, :new_isbn, :isbn   end   def self.down     raise IrreversibleMigration   end end

Discussion

Active Record migrations define versioned incremental schema updates. Each migration is a class that contains a set of instructions for how to apply a change, or set of changes, to the database schema. Within the class, instructions are defined in two class methods, up and down, that define how to apply changes as well as to revert them.

The first time a migration is generated, Rails creates a table called schema_info in the database, if it doesn't already exist. This table contains an integer column named version. The version column tracks the version number of the most current migration that has been applied to the schema. Each migration has a unique version number contained within its filename. (The first part of the name is the version number, followed by an underscore and then the filename, usually describing what this migration does.)

To apply a migration, use a rake task:

$ rake db:migrate             

If no arguments are passed to this command, rake brings the schema up to date by applying any migrations with a version higher than the version number stored in the schema_info table. You can optionally specify the migration version you want your schema to end up at:

$ rake db:migrate VERSION=12             

You can use a similar command to roll the database back to an older version. For example, if the schema is currently at Version 13, but Version 13 has problem, you can use the previous command to roll back to Version 12.

The solution starts off with a database consisting of a sole books table, which includes a column containing 10-digit ISBNs:

mysql> select * from books;  +----+------------+-----------------+ | id | isbn       | title           | +----+------------+-----------------+ |  1 | 9780596001 | Apache Cookbook | |  2 | 9780596001 | MySQL Cookbook  | |  3 | 9780596003 | Perl Cookbook   | |  4 | 9780596006 | Linux Cookbook  | |  5 | 9789867794 | Java Cookbook   | |  6 | 9789867794 | Apache Cookbook | |  7 | 9781565926 | PHP Cookbook    | |  8 | 9780596007 | Snort Cookbook  | |  9 | 9780596007 | Python Cookbook | | 10 | 9781930110 | EJB Cookbook    | +----+------------+-----------------+ 10 rows in set (0.00 sec) 

As the first part of the two-stage conversion process, we add a new column named new_isbn, and then populate it by converting the exiting 10-digit ISBN from the isbn row to the new 13-digit version. The conversion is handled with a utility method we've defined called convert_isbn. The up method adds the new column. It then iterates over all the existing books, performing the conversion and storing the result in the new_isbn column.

def self.up   add_column :books, :new_isbn, :string, :limit => 13   Book.reset_column_information   Book.find(:all).each do |book|       Book.update(book.id, :new_isbn => convert_isbn(book.isbn))   end end

We run the first migration, db/migrate/001_add_converted_isbn.rb, against our schema with the following rake command (note the capitalization of version):

$ rake db:migrate VERSION=1 (in /home/rob/bookdb)

We can confirm that the schema_info table has been created and contains a version of "1." Inspecting the books table shows the new_isbn column, correctly converted:

mysql> select * from schema_info; select * from books; +---------+ | version | +---------+ |       1 | +---------+ 1 row in set (0.00 sec) +----+------------+-----------------+---------------+ | id | isbn       | title           | new_isbn      | +----+------------+-----------------+---------------+ |  1 | 9780596001 | Apache Cookbook | 9789780596002 | |  2 | 9780596001 | MySQL Cookbook  | 9789780596002 | |  3 | 9780596003 | Perl Cookbook   | 9789780596002 | |  4 | 9780596006 | Linux Cookbook  | 9789780596002 | |  5 | 9789867794 | Java Cookbook   | 9789789867790 | |  6 | 9789867794 | Apache Cookbook | 9789789867790 | |  7 | 9781565926 | PHP Cookbook    | 9789781565922 | |  8 | 9780596007 | Snort Cookbook  | 9789780596002 | |  9 | 9780596007 | Python Cookbook | 9789780596002 | | 10 | 9781930110 | EJB Cookbook    | 9789781930119 | +----+------------+-----------------+---------------+ 10 rows in set (0.00 sec) 

At this point, we can revert this migration by calling rake with VERSION=0. Doing that calls the down method:

def self.down   remove_column :books, :new_isbn end

which removes the new_isbn column and updates the schema_info version to "0." Not all migrations are reversible, so you should take care to backup your database to avoid data loss. In this case, we're losing all the data in the new_isbn columnwhich isn't yet a problem because the isbn column is still there.

To complete the conversion, perhaps once all the developers are satisfied that the new ISBN format works with their code, apply the second migration:

$ rake db:migrate VERSION=2 (in /home/rob/projects/migrations) 

VERSION=2 is optional, because we're moving to the highest numbered migration.

To finish off the conversion, the second migration removes the isbn column and renames the new_isbn column to replace the original. This migration is irreversible. If we downgrade, the self.down method raises an exception. We could, alternately, define a self.down method that renames the columns and repopulates the 10-digit isbn field:

mysql> select * from schema_info; select * from books; +---------+ | version | +---------+ |       2 | +---------+ 1 row in set (0.00 sec) +----+-----------------+---------------+ | id | title           | isbn          | +----+-----------------+---------------+ |  1 | Apache Cookbook | 9789780596002 | |  2 | MySQL Cookbook  | 9789780596002 | |  3 | Perl Cookbook   | 9789780596002 | |  4 | Linux Cookbook  | 9789780596002 | |  5 | Java Cookbook   | 9789789867790 | |  6 | Apache Cookbook | 9789789867790 | |  7 | PHP Cookbook    | 9789781565922 | |  8 | Snort Cookbook  | 9789780596002 | |  9 | Python Cookbook | 9789780596002 | | 10 | EJB Cookbook    | 9789781930119 | +----+-----------------+---------------+ 10 rows in set (0.00 sec)  

See Also

  • See the Rail API documentation for more on migrations, http://api.rubyonrails.com/classes/ActiveRecord/Migration.html




Rails Cookbook
Rails Cookbook (Cookbooks (OReilly))
ISBN: 0596527314
EAN: 2147483647
Year: 2007
Pages: 250
Authors: Rob Orsini

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net