rake 对数据库操作深度示例说明

结合自己开发，随手做个备份

一、加载数据

当用rake db:migrate RAILS_ENV=production的时候，自动执行类似

rake db:fixture:load#通过fixture加载数据 

也可以类似

FIXTURES=config_params RAILS_ENV=production /usr/bin/rake db:YAML:restore

在rake db:migrate的文件中，对应

class AddRoleDescription < ActiveRecord::Migration
  def self.up
    add_column "roles", "description", :string

    require 'rake'
    rake = Rake::Application.new
    ENV['FIXTURES'] = "roles,static_permissions,roles_static_permissions"
    Rake::Task["db:YAML:delete"].invoke#删除数据
    Rake::Task["db:YAML:restore"].invoke#重新加载

    user = User.new({:email => 'admin'})
    user.password = 'password'
    user.password_confirmation = 'password'
    if user.save
      puts "Created USER #{user.email}"
    else
      puts "Error creating user.  #{user.errors.inspect}"
    end
    user.roles << Role.find(:all)
    user.save
  end

  def self.down
    remove_column "roles", "description"
    user = User.find_by_email('admin')
    user.destroy
  end
end

二、增加一个字段并且全都赋值

  def self.up
    add_column :analyzers, :region_id, :integer,:default => 1
    Analyzer.find(:all).each { |a|
      a.region_id=1
      a.save
    }
  end

  def self.down
    remove_column :analyzers, :region_id
  end

其中

change_column("datalogs", "start_freq", :integer )#将不会删除这个字段的数据

三、增加一个新的表，并给表增加一条记录

    这个需求，经常是因为在rake db:migrate后，因为增加一个新的表，而这个表有在一些其他功能用到，没有记录就会报错。

class CreateFavUrls < ActiveRecord::Migration
  def self.up
    create_table :fav_urls do |t|
      t.string :name
      t.string :url
      t.string :description
    end
    default_fav_url=FavUrl.new()
    default_fav_url.name="google"
    default_fav_url.url="www.google.com"
    default_fav_url.description="search engine"
    default_fav_url.id=1;
    default_fav_url.save()
  end

  def self.down
    FavUrl.delete_all
    drop_table :fav_urls
  end
end

class CreateRegions < ActiveRecord::Migration
  def self.up
    create_table :regions do |t|
      t.string :name,      :default => "South East USA"
      t.string :ip,        :default => "10.0.0.7"
    end
    default_region=Region.new()
    default_region.name="DEFAULT"
    default_region.ip="127.0.0.1"
    default_region.id=1;
    default_region.save()
  end

  def self.down
    Region.delete_all
    drop_table :regions
  end
end

四，增加索引

class AddUniqueRegion < ActiveRecord::Migration
  def self.up
    change_column("regions", "name", :string,:null => false, :default => "South East USA" )
    change_column("regions", "ip", :string, :null => false, :default => "10.3.2.7")
    add_index(:regions,[:name],:unique=>true)
    add_index(:regions,[:ip],:unique=>true)
  end

  def self.down
    change_column("regions","name", :string, :default => "South East USA")
    change_column("regions","ip",:string, :default => "10.3.2.7")
    remove_index(:regions,:column=>:name)
    remove_index(:regions,:column=>:ip)
  end
end

  create_table "limits", :force => true do |t|
    t.column "measure_id", :integer, :default => 0
    t.column "channel_id", :integer, :default => 0
    t.column "site_id",    :integer, :default => 0
    t.column "max_val",    :float
    t.column "min_val",    :float
  end

  add_index "limits", ["site_id", "measure_id", "channel_id"], :name => "limit_data_idx"

  create_table "measurements", :force => true do |t|
    t.column "measure_id", :integer
    t.column "site_id",    :integer
    t.column "channel_id", :integer
    t.column "value",      :float
    t.column "dt",         :datetime
  end

五，创建视图

class AddMeasurementView < ActiveRecord::Migration
  def self.up
    execute "create or replace view measurement_view as 
    select measures.measure_name as measure, sites.name as site, channels.channel_name as channel,dt,value 
    from ((
      measurements left join channels on channels.id=measurements.channel_id) 
      left join measures on measures.id=measurements.measure_id) 
      left join sites on sites.id=measurements.site_id"
  end

  def self.down
    execute "drop view measurement_view"
  end
end