CSC/ECE 517 Fall 2013/ch1 1w22 ss: Difference between revisions
No edit summary |
No edit summary |
||
Line 142: | Line 142: | ||
end | end | ||
end | end | ||
== Database support == | == Database support == | ||
Line 150: | Line 149: | ||
== Basic Examples of Migrations== | == 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, | 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, | ||
Line 160: | Line 159: | ||
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 | 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, | be altered with the :primary_key option. Or the primary key can all together be removed by using the option id: false. | ||
Note: All the tables will created with default primary key called id. | |||
Database specific options can be passed to an SQL fragment using the :options option. For example, | |||
create_table :category, options: “ENGINE = BLACKHOLE” do |t| | create_table :category, options: “ENGINE = BLACKHOLE” do |t| | ||
Line 168: | Line 171: | ||
This will append ENGINE = BLACKHOLE to the sql statement which is used to create the table. | This will append ENGINE = BLACKHOLE to the sql statement which is used to create the table. | ||
====Adding data to the database==== | |||
Sometimes, we would require to have some data when the tables are created. We can insert the data as follows, | |||
class AddInitialCategories < ActiveRecord::Migration | |||
def up | |||
5.times do |i| | |||
Category.create(name: "Category ##{i}", description: "Default Category") | |||
end | |||
end | |||
end | |||
====Creating a Join Table==== | |||
The method create_join_table creates a HABTM join table. For example, | The method create_join_table creates a HABTM(means has_and_belongs_to_many relation) join table. For example, | ||
create_join_table :category, :recipe | create_join_table :category, :recipe | ||
Line 192: | Line 207: | ||
end | end | ||
====Changing tables==== | |||
An existing table can be modified using the change_table method. For example, | An existing table can be modified using the change_table method. For example, | ||
Line 203: | Line 218: | ||
This removes the column description and renames the name of the column ‘name’ to ‘category_name’. | This removes the column description and renames the name of the column ‘name’ to ‘category_name’. | ||
====Adding Columns==== | |||
Columns can be added later to the table using the add_column method | Columns can be added later to the table using the add_column method | ||
Line 209: | Line 224: | ||
Add_column :recipe, :recipe_instrns, text | Add_column :recipe, :recipe_instrns, text | ||
====Dropping a Table==== | |||
A table can be dropped using the drop_table method. | A table can be dropped using the drop_table method. | ||
Line 217: | Line 232: | ||
== Running the Migrations == | == Running the Migrations == | ||
RAILS provide a set of tasks to run certain set of migrations. | *RAILS provide a set of tasks to run certain set of migrations. | ||
The basic task you will use is | The basic task you will use is | ||
$ rake db:migrate | $ rake db:migrate | ||
This would | This would 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. | 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. | *If you want to run any specific function use the following command. | ||
$ rake db:migrate VERSION= | $ rake db:migrate VERSION=20120906120000 | ||
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. | 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: This command roll back the last migration by default | |||
$ rake db:rollback | $ rake db:rollback | ||
You can also specify number of the migrations you need to roolback by | You can also specify number of the migrations you need to roolback by | ||
$ rake db:rollback STEP= | $ rake db:rollback STEP=5 | ||
*Resetting the database: To drop the database, then recreate it with the present schema, we use, | |||
To drop the database, then recreate it with the present schema, we use, | |||
$ rake db:reset | $ rake db:reset | ||
*To run the migrations in different environments, we can use | |||
$ rake db:migrate RAILS_ENV=production | |||
*If you want to print messages when running your migration, insert the message using '''say''' or '''say_with_time''' in the migration. | |||
'''Note:'''Be Careful whenever you are using models in migrations. if there is a change in model and you later migrations consider the change. When a new person, comes and run your migration he might get an error because the model change has not been reflected in his application. | |||
Therefore, to avoid such failures, you should always reset model information before referencing the model in the migration. |
Revision as of 23:09, 17 September 2013
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 by maintaining different versions of migration scripts, in short 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 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 in addition to many features it provides in Migration. 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. It has 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. Be careful of the position of migration, whenever you're copying a migration from another application or generate a file yourself. 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 introduced in the newer version of RAILS and acts as a primary method. 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) In RAILS, framework itself 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, whereas Model classes are subclasses of ActiveRecord::base.
RAILS provide many shortcut commands during creating Migrations.
- Migrations of form ‘AddXXToYY’ or ‘RemoveXXToYY’, followed by appropriate column names to be added or deleted are automatically 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.
- Migration starting with 'CreateXXX' followed by the columns, would generate the code for creating the table XXX with columns mentioned.
$ rails generate migration CreateCategories name:string description:string
will generate
class CreateCategories < ActiveRecord::Migration def change create_table :categories do |t| t.string :name t.string :description end end end
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.
Note: All the tables will created with default primary key called id.
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.
Adding data to the database
Sometimes, we would require to have some data when the tables are created. We can insert the data as follows,
class AddInitialCategories < ActiveRecord::Migration def up 5.times do |i| Category.create(name: "Category ##{i}", description: "Default Category") end end end
Creating a Join Table
The method create_join_table creates a HABTM(means has_and_belongs_to_many relation) 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’.
Adding Columns
Columns can be added later to the table using the add_column method
Add_column :recipe, :recipe_instrns, text
Dropping a Table
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 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=20120906120000
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: This command roll back the last migration by default
$ rake db:rollback
You can also specify number of the migrations you need to roolback by
$ rake db:rollback STEP=5
- Resetting the database: To drop the database, then recreate it with the present schema, we use,
$ rake db:reset
- To run the migrations in different environments, we can use
$ rake db:migrate RAILS_ENV=production
- If you want to print messages when running your migration, insert the message using say or say_with_time in the migration.
Note:Be Careful whenever you are using models in migrations. if there is a change in model and you later migrations consider the change. When a new person, comes and run your migration he might get an error because the model change has not been reflected in his application. Therefore, to avoid such failures, you should always reset model information before referencing the model in the migration.