バリケンのRuby日記 RSSフィード

2007-03-22

[][] 「SQL書き方ドリル」のスキーマActiveRecordで書く  「SQL書き方ドリル」のスキーマをActiveRecordで書く - バリケンのRuby日記 を含むブックマーク はてなブックマーク -  「SQL書き方ドリル」のスキーマをActiveRecordで書く - バリケンのRuby日記  「SQL書き方ドリル」のスキーマをActiveRecordで書く - バリケンのRuby日記 のブックマークコメント

SQL書き方ドリル」の付録CD-ROMに入っていた「SQL学習用のデータベースを作成するスキーマ」を、ActiveRecordで使いやすいようにActiveRecordスキーマとして書き直してみたよ。

データデータベースに追加する部分は全部引用はしないから、もし使ってみたい人がいたらCD-ROMスキーマファイルから適当に加工して配列に追加してね。

require 'active_record'

ActiveRecord::Base.establish_connection(
  :adapter => 'sqlite3',
  :dbfile  => 'sql_training_sqlite3.db'
)

class InitialSchema < ActiveRecord::Migration
  def self.up
    create_table :belongs do |t|
      t.column :name, :string
      t.column :start_date, :date
      t.column :end_date, :date
      t.column :department_id, :integer
      t.column :employee_id, :integer
    end

    create_table :categories do |t|
      t.column :name, :string
    end

    create_table :customer_classes do |t|
      t.column :name, :string
    end

    create_table :customers do |t|
      t.column :code, :integer
      t.column :name, :string
      t.column :address, :string
      t.column :customer_class_id, :integer
      t.column :prefectural_id, :integer
    end

    create_table :departments do |t|
      t.column :name, :string
    end

    create_table :employees do |t|
      t.column :name, :string
      t.column :height, :integer
      t.column :weight, :integer
      t.column :email, :string
      t.column :hire_fiscal_year, :integer
      t.column :birthday, :date
      t.column :blood_type, :string, :limit => 2
    end

    create_table :prefecturals do |t|
      t.column :name, :string
    end

    create_table :products do |t|
      t.column :code, :integer
      t.column :name, :string
      t.column :price, :integer
      t.column :category_id, :integer
    end

    create_table :salaries do |t|
      t.column :pay_date, :date
      t.column :amount, :integer
      t.column :employee_id, :integer
    end

    create_table :sales do |t|
      t.column :quantity, :integer
      t.column :customer_id, :integer
      t.column :product_id, :integer
      t.column :employee_id, :integer
      t.column :sale_date, :date
    end
  end

  def self.down
    drop_table :belongs
    drop_table :categories
    drop_table :customer_classes
    drop_table :customers
    drop_table :departments
    drop_table :employees
    drop_table :prefecturals
    drop_table :products
    drop_table :salaries
    drop_table :sales
  end
end

InitialSchema.migrate(:up)

class Belong < ActiveRecord::Base
  belongs_to :department
  belongs_to :employee
end

class Category < ActiveRecord::Base
  has_many :products
end

class CustomerClass < ActiveRecord::Base
  has_many :customers
end

class Customer < ActiveRecord::Base
  has_many :sales
  belongs_to :customer_class
  belongs_to :prefectural
end

class Department < ActiveRecord::Base
  has_many :belongs
  has_many :employees, :through => :belongs
end

class Employee < ActiveRecord::Base
  has_many :belongs
  has_many :sales
  has_many :salaries
  has_many :departments, :through => :belongs
end

class Prefectural < ActiveRecord::Base
  has_many :customers
end

class Product < ActiveRecord::Base
  has_many :sales
  belongs_to :category
end

class Salary < ActiveRecord::Base
  belongs_to :employee
end

class Sale < ActiveRecord::Base
  belongs_to :employee
  belongs_to :customer
  belongs_to :product
end


[[1,1,2,'1984-06-15',nil],
 [2,2,4,'1984-06-15',nil],
 [3,3,5,'1984-06-15','1995-08-31'],
# (途中省略)
 [34,30,5,'2004-04-01',nil]].each do |i|
  Belong.create(
    :id => i[0],
    :employee_id => i[1],
    :department_id => i[2],
    :start_date => i[3],
    :end_date => i[4]
  )
end

[[1,'魚'],
 [2,'肉'],
 [3,'野菜'],
# (途中省略)
 [10,'アクセサリー']].each do |i|
  Category.create(
    :id => i[0],
    :name => i[1]
  )
end

[[1,'法人'],
 [2,'個人']].each do |i|
  CustomerClass.create(
    :id => i[0],
    :name => i[1]
  )
end

[[1,2001,'タマ','江戸川区下小岩',2,13],
 [2,2002,'ハナ','江戸川区北小岩',2,13],
 [3,2003,'ミケ','館林市緑町',2,10],
# (途中省略)
 [30,1030,'プーストア','江東区大島',1,13]].each do |i|
  Customer.create(
    :id => i[0],
    :code => i[1],
    :name => i[2],
    :address => i[3],
    :customer_class_id => i[4],
    :prefectural_id => i[5]
  )
end

[[1,'営業'],
 [2,'総務'],
 [3,'人事'],
# (途中省略)
 [6,'購買']].each do |i|
  Department.create(
    :id => i[0],
    :name => i[1]
  )
end

[[1,'シマゴロー',168,72,'simagoro@nekoyasudo',1984,'1953-10-01','A'],
 [2,'ゴッチン',161,60,'gochin@nekoyasudo',1984,'1950-12-25','B'],
 [3,'マキ子',155,52,'maki@nekoyasudo',1984,'1955-08-16','O'],
# (途中省略)
 [30,'ミーヤ',168,51,'miya@nekoyasudo',2004,'1986-07-07','B']].each do |i|
  Employee.create(
    :id => i[0],
    :name => i[1],
    :height => i[2],
    :weight => i[3],
    :email => i[4],
    :hire_fiscal_year => i[5],
    :birthday => i[6],
    :blood_type => i[7]
  )
end

[[1,'北海道'],
 [2,'青森県'],
 [3,'岩手県'],
# (途中省略)
 [47,'沖縄県 ']].each do |i|
  Prefectural.create(
    :id => i[0],
    :name => i[1]
  )
end

[[1,1001,'まぐろ',500,1],
 [2,1002,'金魚',35,1],
 [3,1003,'ぶり',350,1],
# (途中省略)
 [50,10050,'毛止めクリップ',350,10]].each do |i|
  Product.create(
    :id => i[0],
    :code => i[1],
    :name => i[2],
    :price => i[3],
    :category_id => i[4]
  )
end

[[1,1,'2003-09-25',580000],
 [2,2,'2003-09-25',450000],
 [3,3,'2003-09-25',410000],
# (途中省略)
 [353,30,'2004-08-25',150000]].each do |i|
  Salary.create(
    :id => i[0],
    :employee_id => i[1],
    :pay_date => i[2],
    :amount => i[3]
  )
end

[[1,5,3,21,19,'2003-09-01'],
 [2,1,20,14,4,'2003-09-01'],
 [3,1,20,9,4,'2003-09-01'],
# (途中省略)
 [1005,1,3,31,30,'2004-08-31']].each do |i|
  Sale.create(
    :id => i[0],
    :quantity => i[1],
    :customer_id => i[2],
    :product_id => i[3],
    :employee_id => i[4],
    :sale_date => i[5]
  )
end

これを利用してデータベースを作成しておいて、問題を解くときには

require 'active_record'

ActiveRecord::Base.establish_connection(
  :adapter => 'sqlite3',
  :dbfile  => 'sql_training_sqlite3.db'
)

class Belong < ActiveRecord::Base
  belongs_to :department
  belongs_to :employee
end

class Category < ActiveRecord::Base
  has_many :products
end

class CustomerClass < ActiveRecord::Base
  has_many :customers
end

class Customer < ActiveRecord::Base
  has_many :sales
  belongs_to :customer_class
  belongs_to :prefectural
end

class Department < ActiveRecord::Base
  has_many :belongs
  has_many :employees, :through => :belongs
end

class Employee < ActiveRecord::Base
  has_many :belongs
  has_many :sales
  has_many :salaries
  has_many :departments, :through => :belongs
end

class Prefectural < ActiveRecord::Base
  has_many :customers
end

class Product < ActiveRecord::Base
  has_many :sales
  belongs_to :category
end

class Salary < ActiveRecord::Base
  belongs_to :employee
end

class Sale < ActiveRecord::Base
  belongs_to :employee
  belongs_to :customer
  belongs_to :product
end

# 3-1
Product.find(:all).select{|i|
  i.sales.empty?
}.each{|i|
  puts i.name
}

# 3-1-1
Employee.find(:all).select{|i|
  i.salaries.find(:all).select{|j|
    j.amount >= 300000
  }.size != 0
}.each{|i|
  puts i.name
}

# 3-1-2
Sale.find(:all).select{|i|
  i.quantity >= 100
}.each{|i|
  puts "#{i.id} #{i.quantity} #{i.customer_id} #{Customer.find(i.customer_id).name}"
}

# 3-1-3
Product.find(:all).select{|i|
  i.sales.find(:all).inject(0){|result,item|
    result + item.quantity
  } >= 100
}.each{|i|
  puts "#{i.id} #{i.name}"
}

# 3-1-4
Employee.find(:all).select{|i|
  i.salaries.find(:all).select{|j|
    j.amount >= 300000
  }.size != 0
}.each{|i|
  print "#{i.name} "
  puts i.salaries.inject(0){|result,item|
    [result, item.amount].max
  }
}

# 3-1-5
Sale.find(:all).select{|i|
  i.quantity >= 100
}.each{|i|
  puts "#{i.id} #{i.quantity} #{i.customer_id} #{Customer.find(i.customer_id).name} #{Product.find(i.product_id).name}"
}

のような感じでアクセスすればいいよね。

それにしても「SQL書き方ドリル」の問題用のテーブル設計は、「中間テーブルを利用したhas_many :throughによる多対多の関連表現」が出てきたりと、ActiveRecordのテーブル設計の勉強になるねえ。「楽々ERDレッスン」と合わせて、ActiveRecord勉強には欠かせない本だと思うよ。

トラックバック - http://rubyist.g.hatena.ne.jp/muscovyduck/20070322