Rails Database Migrations - RORLab Season 3-3

RORLab Season 3-3

Transcript

1. Database Migrations March 29th, 2013 ChangHoon Jeong(@seapy) ROR Lab. - The 3th Round - ROR Lab. Season 3 13֙ 3ਘ 30ੌ షਃੌ
2. ROR Lab. General Migration • Use SQL(Database dependent) • Run SQL by DB console • Telling other developer and run SQL their local DB • Run SQL when you deploy to production 13֙ 3ਘ 30ੌ షਃੌ
3. ROR Lab. ROR Migration • Use ruby code(Database independent) • Run rake task • Other developer run rake task(it’s rails convention) • Run rake task when you deploy to production 13֙ 3ਘ 30ੌ షਃੌ
4. ROR Lab. Migrations are Classes class CreateProducts < ActiveRecord::Migration def up create_table :products do |t| t.string :name t.text :description t.timestamps end end def down drop_table :products end end class CreateProducts < ActiveRecord::Migration def change create_table :products do |t| t.string :name t.text :description t.timestamps end end end rails 3.1 ~> subclass of ActiveRecord::Migration 13֙ 3ਘ 30ੌ షਃੌ
5. ROR Lab. Migration methods • Database independent way • Execute method allows you to execute arbitrary SQL • Migrations are wrapped in a transaction(PostgreSQL or SQLite3, not support MySQL) add_column add_index change_column change_table create_table drop_table remove_column remove_index rename_column execute "SQL" 13֙ 3ਘ 30ੌ షਃੌ
6. ROR Lab. Up & Down Methods (1) class CreateProducts < ActiveRecord::Migration def up create_table :products do |t| t.string :name t.text :description t.timestamps end end def down drop_table :products end end updated_at, created_at 13֙ 3ਘ 30ੌ షਃੌ
7. ROR Lab. Up & Down Methods (2) class AddReceiveNewsletterToUsers < ActiveRecord::Migration def up change_table :users do |t| t.boolean :receive_newsletter, :default => false end User.update_all ["receive_newsletter = ?", true] end def down remove_column :users, :receive_newsletter end end 13֙ 3ਘ 30ੌ షਃੌ
8. ROR Lab. Up & Down Methods (3) class ExampleMigration < ActiveRecord::Migration def up create_table :products do |t| t.references :category end #add a foreign key execute <<-SQL ALTER TABLE products ADD CONSTRAINT fk_products_categories FOREIGN KEY (category_id) REFERENCES categories(id) SQL add_column :users, :home_page_url, :string rename_column :users, :email, :email_address end def down rename_column :users, :email_address, :email remove_column :users, :home_page_url execute <<-SQL ALTER TABLE products DROP FOREIGN KEY fk_products_categories SQL drop_table :products end end category_id 13֙ 3ਘ 30ੌ షਃੌ
9. ROR Lab. Up & Down Methods (3) class ExampleMigration < ActiveRecord::Migration def up create_table :products do |t| t.references :category end #add a foreign key execute <<-SQL ALTER TABLE products ADD CONSTRAINT fk_products_categories FOREIGN KEY (category_id) REFERENCES categories(id) SQL add_column :users, :home_page_url, :string rename_column :users, :email, :email_address end def down rename_column :users, :email_address, :email remove_column :users, :home_page_url execute <<-SQL ALTER TABLE products DROP FOREIGN KEY fk_products_categories SQL drop_table :products end end category_id 13֙ 3ਘ 30ੌ షਃੌ
10. ROR Lab. class CreateProducts < ActiveRecord::Migration def change create_table :products do |t| t.string :name t.text :description t.timestamps end end end • create_table • add_column • add_index • rename_table • rename_column • rename_index • add_timestamps • remove_timestamps Change Method 13֙ 3ਘ 30ੌ షਃੌ
11. ROR Lab. Supported Types • not supported by Active Record • t.column :name, 'typename' :binary :boolean :date :datetime :decimal :float :integer :primary_key :string :text :time :timestamp 13֙ 3ਘ 30ੌ షਃੌ
12. ROR Lab. • db/migrate directory • YYYYMMDDHHMMSS_create_products.rb • UTC timestamp • The name of the migration class(CamelCased) Migration Files Naming 13֙ 3ਘ 30ੌ షਃੌ
13. ROR Lab. • $ rails generate model Post title:string • $ rails generate scaffold Post title:string • $ rails generate migration AddContentToPosts content:text • $ rails generate migration RemoveContentFromPosts content:text To Create Migration Files 13֙ 3ਘ 30ੌ షਃੌ
14. ROR Lab. Add & Remove Columns • AddXXXToYYY • RemoveXXXFromYYY $ rails g migration AddNameToUsers $ rails g migration RemoveNameFromUsers 13֙ 3ਘ 30ੌ షਃੌ
15. ROR Lab. • $ rails destroy model Post title:string • $ rails destroy scaffold Post title:string • $ rails destroy migration AddContentToPosts content:text To Destroy Migration Files 13֙ 3ਘ 30ੌ షਃੌ
16. ROR Lab. Rake it~ •$ rake db:migrate •$ rake db:rollback •$ rake db:migrate:status “up” state “down” state 13֙ 3ਘ 30ੌ షਃੌ
17. ROR Lab. Rake it~ $ rake db:migrate:status mysql> select * from schema_migrations; +----------------+ | version | +----------------+ | 20120531061820 | | 20120531105534 | | 20120531124444 | | 20120531125446 | | 20120531133035 | | 20120601102629 | | 20120603223525 | | 20120603224330 | | 20120603224625 | | 20120604064155 | | 20120604110743 | | 20120702123904 | | 20120702125358 | | 20120703005951 | | 20120704033651 | | 20120728014210 | | 20120728061841 | | 20120728102213 | | 20120729053924 | | 20120804011723 | | 20120804012821 | | 20120804013538 | | 20120808023400 | | 20120810071351 | +----------------+ 34 rows in set (0.00 sec) database: medibook_development Status Migration ID Migration Name -------------------------------------------------- up 20120531061820 Create users up 20120531105534 Rolify create roles up 20120531124444 Create boards up 20120531125446 Create board types up 20120531133035 Create posts up 20120601102629 Create categories up 20120603223525 Create products up 20120603224330 Add some fields to users up 20120603224625 Add some fields to categories up 20120604064155 Add category id to products up 20120604110743 Add attachment product photo to products up 20120702123904 Add view count to products up 20120702125358 Create comments up 20120703005951 Create favourites up 20120704033651 Add account to users up 20120728014210 Add devise to users up 20120728061841 Remove password digest from users up 20120728102213 Create user groups up 20120729053924 Create users groups up 20120804011723 Add user details to users up 20120804012821 Create affiliations up 20120804013538 Add some fields to affiliations up 20120808023400 Add attachment company logo to affiliations up 20120810071351 Add active to users 13֙ 3ਘ 30ੌ షਃੌ
18. ROR Lab. Rake it~ $ rake db:version $ cat db/schema.rb ActiveRecord::Schema.define(:version => 20120831045454) do create_table "ad_images", :force => true do |t| t.integer "adimageable_id" t.string "adimageable_type" t.integer "user_id" t.string "image_file_name" t.string "image_content_type" t.integer "image_file_size" t.datetime "created_at", :null => false t.datetime "updated_at", :null => false end add_index "ad_images", ["adimageable_id"], :name => "index_ad_images_on_adimageable_id" add_index "ad_images", ["user_id"], :name => "index_ad_images_on_user_id" Current version: 20120818023340 13֙ 3ਘ 30ੌ షਃੌ
19. ROR Lab. Rake it~ $ rake db:abort_if_pending_migrations $ rake db:migrate:status database: db/development.sqlite3 Status Migration ID Migration Name -------------------------------------------------- up 20120818022501 Create posts down 20120818023340 Create comments down 20120818031154 Create tags $ rake db:abort_if_pending_migrations You have 2 pending migrations: 20120818023340 CreateComments 20120818031154 CreateTags Run `rake db:migrate` to update your database then try again. 13֙ 3ਘ 30ੌ షਃੌ
20. ROR Lab. schema_migrations • db/schema.rb • A database table : history of migrations $ rake db:migrate:status • up • down 13֙ 3ਘ 30ੌ షਃੌ
21. ROR Lab. Running Migrations $ rake db:migrate $ rake db:migrate VERSION=20090906120000 $ rake db:migrate:up VERSION=20090906120000 $ rake db:rollback $ rake db:rollback STEP=3 UP DOWN db:schema:dump db/schema.rb 13֙ 3ਘ 30ੌ షਃੌ
22. ROR Lab. $ rake db:migrate $ rake db:migrate VERSION=20120702125358 $ rake db:migrate:down VERSION=20120702125358 database: medibook_development Status Migration ID -------------------------- up 20120531061820 up 20120531105534 up 20120531124444 up 20120531125446 up 20120531133035 up 20120601102629 up 20120603223525 up 20120603224330 up 20120603224625 up 20120604064155 up 20120604110743 up 20120702123904 up 20120702125358 up 20120703005951 up 20120704033651 up 20120728014210 up 20120728061841 up 20120728102213 up 20120729053924 up 20120804011723 up 20120804012821 up 20120804013538 up 20120808023400 up 20120810071351 Running Migrations UP db:migrate:down 13֙ 3ਘ 30ੌ షਃੌ
23. ROR Lab. $ rake db:migrate $ rake db:migrate VERSION=20120702125358 $ rake db:migrate:up VERSION=20120702125358 database: medibook_development Status Migration ID -------------------------- up 20120531061820 up 20120531105534 up 20120531124444 up 20120531125446 up 20120531133035 up 20120601102629 up 20120603223525 down 20120603224330 down 20120603224625 down 20120604064155 down 20120604110743 down 20120702123904 down 20120702125358 down 20120703005951 down 20120704033651 down 20120728014210 down 20120728061841 down 20120728102213 down 20120729053924 down 20120804011723 down 20120804012821 down 20120804013538 down 20120808023400 down 20120810071351 Running Migrations UP db:migrate:up 13֙ 3ਘ 30ੌ షਃੌ
24. ROR Lab. database: medibook_development Status Migration ID -------------------------- up 20120531061820 up 20120531105534 up 20120531124444 up 20120531125446 up 20120531133035 up 20120601102629 up 20120603223525 up 20120603224330 up 20120603224625 up 20120604064155 up 20120604110743 up 20120702123904 up 20120702125358 up 20120703005951 up 20120704033651 up 20120728014210 up 20120728061841 up 20120728102213 up 20120729053924 up 20120804011723 up 20120804012821 up 20120804013538 up 20120808023400 up 20120810071351 $ rake db:rollback $ rake db:migrate:redo $ rake db:rollback STEP=3 $ rake db:migrate:redo STEP=3 Running Migrations UP 13֙ 3ਘ 30ੌ షਃੌ
25. ROR Lab. output of migrations • suppress_messages • say • indent option true/false • say_with_time • return integer, number of rows affected 13֙ 3ਘ 30ੌ షਃੌ
26. ROR Lab. class CreateProducts < ActiveRecord::Migration def change suppress_messages do create_table :products do |t| t.string :name end end say "Created a table" suppress_messages {add_index :products, :name} say "and an index!", true say_with_time 'Waiting for a while' do sleep 10 250 end end end == CreateProducts: migrating ============ -- Created a table -> and an index! -- Waiting for a while -> 10.0013s -> 250 rows == CreateProducts: migrated (10.0054s) ==== 13֙ 3ਘ 30ੌ షਃੌ
27. ROR Lab. # db/migrate/20100513121110_add_flag_to_product.rb class AddFlagToProduct < ActiveRecord::Migration class Product < ActiveRecord::Base end def change add_column :products, :flag, :integer Product.reset_column_information Product.all.each do |product| product.update_attributes!(:flag => false) end end end Model in Migration • YourTable.reset_column_information • Alice and Bob’s Story 13֙ 3ਘ 30ੌ షਃੌ
28. ROR Lab. App DB Init $ rake db:reset 1. db:drop 2. db:setup $ rake db:setup 1. db:create 2. db:schema:load 3. db:seed using db/schema.rb https://github.com/rails/rails/blob/master/activerecord/lib/active_record/railties/databases.rake 13֙ 3ਘ 30ੌ షਃੌ
29. ROR Lab. Schema works • $ rake db:schema:dump called by db:migrate create schema.rb • $ rake db:schema:load load schema.rb 13֙ 3ਘ 30ੌ షਃੌ
30. 감사합니다.