ProblemYou 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. SolutionUse 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 DiscussionActive 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
|