CSC/ECE 517 Fall 2013/ch1 1w22 ss

RAILS DATABASE MIGRATIONS

Database migration, in general is the process of transferring all the database changes from one database to another. It is always an odious task to manage database schema as well as data changes. Rails provides solution to this problem using ‘Active Record Migration’. These Migrations allow you to define incremental changes to databases and it makes it possible to use a version control, which helps to keep things synchronized with the actual code. You would find the word ‘Active Record’, very frequently when you talk about Migrations in RAILS. So,

What is an Active Record?

Active Record is a model layer in MVC pattern, which uses the object relational mapping. It basically maintains a mapping between all Model classes and tables in databases. It includes a whole lot of other functionality like validation, parameter passing etc. A detailed information on Active Records can be found here

Why Migrations are necessary?

Example 1: If one developer makes a schema change, He should inform all others about the change and distribute the modification script. In addition, he should remember to run the script during deployment. In short, making ad-hoc changes to the database involves risk. There need to be a proper documentation of which scripts to run during deployment which is an additional burden to the developers.

In this scenario, Migration in Rails helps you create migration files with simple commands. With migrations, you create your database schema in Ruby instead of SQL DDL. If you want to change your data model, say by renaming a column or adding a table, you just write another migration class to do it. Your database keeps track of versioning for you, and you can easily see what changed in the data model just by reading the migration code.

Naming Convention of Migration files

In RAILS, the small tip we follow to make our lives easy is ‘Convention over Configuration’. As many other files, there is a naming convention associated with migration files also. The name of the file is of the form YYYYMMDDHHMMSS_create_tablename.rb, that is to say a UTC timestamp identifying the migration followed by an underscore followed by the name of the migration. The name of the migration class (CamelCased version) should match the latter part of the file name. For example 20080906120000_create_categories.rb should define class CreateCategories.

Rails uses this timestamp to determine which migration should be run and in what order, so if you're copying a migration from another application or generate a file yourself, be aware of its position in the order. As it is difficult for a user to calculate the timestamp and create a migration, Active Record provides a generator which creates the filename in that convention.

How does a Migration look?

Inside a Migration file, you have

• Migration class which is subclass of ActiveRecord::Migration

The name of the class is same as that of Migration, following the naming conventions. Inside the class you have a ‘change’ method, where you write all the RUBY DSL. You also have a reversible section, where you write all the code necessary to roll back the migration. In general, all reversible migrations get reversed automatically by RAILS when you roll back any migration. If you use any irreversible migrations, then we should write the code required for rolling back the operations inside the reversible block.

class CreateCategories < ActiveRecord::Migration
 def change
   create_table :categories do |t|
     t.string :name
     t.bool :presence
    end
 end
end

You can also use old style of migration where we use, up/down methods.

class CreateCategories < ActiveRecord::Migration

 def self.up
   create_table :categories do |t|
     t.string :name
     t.bool :presence
    end
 end

def self.down
    drop_table : categories
end

end

The ‘up’ method is used when you are running the migration and ‘down’ method is used when you are rolling back the migration operation. The code inside the ‘up’ and ‘down’ method should be such that, a ‘up’ followed by a ‘down’ operation should leave the database in a consistent state. ‘change’ Method: The ‘change’ method is the primary way to write the migrations. It works for most of the cases where active record knows how to reverse the migration automatically. Currently the change method supports only these migration definitions: • add_column • add_index • add_reference • add_timestamps • create_table • create_join_table • drop_table (must supply a block) • drop_join_table (must supply a block) • remove_timestamps • rename_column • rename_index • remove_reference • rename_table

How to create a Migration: 1. RAILS provide a framework, which creates a default migration file, whenever a new model is created. In addition, the statements for adding the columns to the table are also generated, if you tell RAILS which attributes are to be present in the model.

   $ rails generate model Category name:string presence:bool

Will create a migration as class CreateCategories < ActiveRecord::Migration

 def change
   create_table :categories do |t|
     t.string :name
     t.bool :presence
    end
 end

end

2. You can also create a standalone migration using ‘generate migration’ $ rails generate migration AddDescriptionToCategories

This would create an empty appropriately named Migration. class AddDescriptionToCategories < ActiveRecord::Migration

 def change
 end

end As you can see, all migration classes are subclasses of ActiveRecord::Migration RAILS provide many shortcut commands during creating Migrations. If the migration is of form ‘AddXXToYY’ or ‘RemoveXXToYY’ and is followed by appropriate column names to be added or deleted. The migration is created with corresponding statements to add or remove the columns. $ rails generate migration AddDescriptionToCategories description:string Will generate class AddDescriptionToCategories < ActiveRecord::Migration

 def change
   add_column :categories, :description, :string
 end

end

Similarly, $ rails generate migration RemoveDescriptionToCategories description:string Will generate

class RemoveDescriptionToCategories < ActiveRecord::Migration

 def change
   remove_column :categories, :description, :string
 end

end You are not limited to create or remove only one column, you can add or remove any number of columns in the similar way. Database support Migrations are currently supported in MySQL, PostgreSQL, SQLite, SQL Server, Sybase, and Oracle (all supported databases except DB2). Basic Examples of Migrations: Creating a Table: One of the most fundamental methods of migration is create_table method. This method will also be generated from a model or scaffold generator. For example, create_table :category do |t|

  t_string :name
  t_string :description

end This creates a table with two columns called name and description along with a default primary key called id. The name of the primary key can be altered with the :primary_key option. Or the primary key can all together be removed by using the option id: false. Database specific options can be passed to an SQL fragment using the :options option. For example, Create_table :category, options: “ENGINE = BLACKHOLE” do |t|

  t.string:name
  t_string :description

end This will append ENGINE = BLACKHOLE to the sql statement which is used to create the table.

Creating a Join Table: The method create_join_table creates a HABTM join table. For example, Create_join_table :category, :recipe This creates category_recipe table with two columns category_id and recipe_id. By default, these columns have the option :null set to false. These columns can be set to True in the following way Create_join_table :category, :recipe, column_options: {null: true} This will create the category_id and recipe_id with the :null option as true The name of the join table can be mentioned as follows Create_join_table :category, :recipe, table_name: :categoryrecipe Indices or additional columns can also be added to the table created by joining two tables. This is as follows Create_join_table :category, :recipe do |t|

  t.index :category_id
  t.index :recipe_id

end Changing tables: An existing table can be modified using the change_table method. For example, Change_table :category do |t|

   t.remove :description
   t.rename :name, :category_name

end This removes the column description and renames the name of the column ‘name’ to ‘category_name’. Columns can be added later to the table using the add_column method Add_column :recipe, :recipe_instrns, text A table can be dropped using the drop_table method. Drop_table :recipe Running the Migrations: RAILS provide a set of tasks to run certain set of migrations. The basic task you will use is $ rake db:migrate This would the run the ‘change’ or ‘up’ method for all the migrations that have not yet been run. If there are no such migrations, it exits.It generally run the migrations based on the date of migration. Note that running the db:migrate also invokes the db:schema:dump task, which will update your db/schema.rb file to match the structure of your database. If you want to run any specific function use the following command. $ rake db:migrate VERSION=20080906120000 When you run a migration, a schema_info table is created, which stores the version of the database. Whenever you try to run a migration of previous version, all the next versions are rolled back. i.e for example, if your present version is greater than ‘20080906120000’, all your versions till this one would be rolled back. Rolling Back: A common task to roll back the last migration $ rake db:rollback

You can also specify number of the migrations you need to roolback by $ rake db:rollback STEP=3

Resetting the database:

To drop the database, then recreate it with the present schema, we use, $ rake db:reset