ActiveRecord を詳しく

西 和則

   aka Maiha

   aka くまくまー

从 ’w’)<「優しいRailsの育て方」

http://wota.jp/ac/

ActiveRecord

Introduction

第1章: for Ruby users (4)

  ・CRUD operations
  ・Assocations

第2章: for Rails users (15)

  ・With Scope
  ・Scoped Access

第3章: for Rails developers (17)

  ・Acts As View
  ・Finder Query

for Ruby users

第1章:for Ruby users

  ・CRUD operations
  ・Assocations

「Web開発しないけど知ってて便利なの?」

CRUD : ActiveRecord Pattern

require 'rubygems'
require_gem 'activerecord'
ActiveRecord::Base.eatablish_connection(
  :adapter=>"sqlite3", :db=>"db/users.db"
)
class Member < ActiveRecord::Base   # "members"
end

Member.create(:name=>"舞波")  
# => "INSERT INTO members(id, name) VALUES(1, '舞波')"

maiha = Member.find(1)        
# => "SELECT * FROM members WHERE id = 1"

maiha[:name] = "まいは"
maiha.save                    
# => "UPDATE members SET name = 'まいは' WHERE id = 1"

CRUD : ActiveRecord Pattern

从*’w’)<Syslogとか

class Syslog < ActiveRecord::Base   # "syslogs"
end

def foo
  ...
rescue => err
  Syslog.create(:message=>err.message)
  raise
end

Syslog.create(:message=>err, :level=>"WARN")
Syslog.create(:message=>err, :level=>"ERROR")

Syslog.find(:all, :conditions=>"level = 'ERROR'")

Associations

Associations : has_many

has_many (1:多)

class Group < ActiveRecord::Base   # "groups"
  has_many :members
end
class Member < ActiveRecord::Base  # "members"
  belongs_to :group
end

berryz = Group.create(:name=>"Berryz工房")
maiha  = Member.create(:name=>"舞波")
berryz.members           # => []
berryz.members << maiha  # => "UPDATE members SET group_id = 1"

Group.find_or_create_by_name("Berryz工房").members \
  .find_or_create_by_name("舞波")

for Ruby users

第1章:for Ruby users


从 ’w’)<一部の皆様、ご静聴ありがとうございました

for Rails users

第2章:for Rails users

  ・With Scope
  ・Scoped Access

「with_scope・・・なんか聞いたことある」

With Scope

从 ’w’)<通常の検索


"members" table

idnamegrade
1川´・_・リ3
2ノノl∂_∂'ル2
3从 ’w’)2
4川*^∇^)1

2年生の検索

Member.find(:all, :conditions=>"grade = 2")

With Scope

从 ■w■)<"grade = 2" で with_scope オン!!


"members" table

idnamegrade
1川´・_・リ3
2ノノl∂_∂'ル2
3从 ’w’)2
4川*^∇^)1

2年生の検索

Member.find(:all)

   → DBのViewに近い

With Scope : find / create

DBのView / 条件のデフォルト値として利用

Member.with_scope(:find   => {:conditions => "grade = 2"},
                  :create => {:grade      => 2           }) do

  Member.find(:all)
  # => 'SELECT * FROM members WHERE grade = 2'

  Member.create(:name=>"舞波")
  # => 'INSERT INTO members ("name", "grade") VALUES ("舞波", 2)'
end

With Scope : conflict

条件が衝突する場合 (with_scope vs. arguments)

Member.with_scope(:find   => {:conditions => "grade = 2"},
                  :create => {:grade      => 2           }) do

  Member.find(:all, :conditions => "grade = 3")
  Member.create(:name => "舞波", :grade => 3)
end

with_scope <=> arguments

   (’w’;从 === 从;’w’)???

With Scope : conflict

条件が衝突する場合 (with_scope vs. arguments)

Member.with_scope(:find   => {:conditions => "grade = 2"},
                  :create => {:grade      => 2           }) do

  Member.find(:all, :conditions => "grade = 3")
  # => 'SELECT * FROM members WHERE grade = 2 AND grade = 3'

  Member.create(:name => "舞波", :grade => 3)
  # => 'INSERT INTO members ("name", "grade") VALUES ("舞波", 2)'
end

从*’w’)<with_scopeの内容を優先!

   (つ with_scope

ミarguments

With Scope : conflict

条件が衝突する場合

Member.with_scope(:find   => {:conditions => "grade = 2"},
                  :create => {:grade      => 2           }) do

  Member.find(:all, :conditions => "grade = 3")
  # => 'SELECT * FROM members WHERE grade = 2 AND grade = 3'

  Member.create(:name => "舞波", :grade => 3)
  # => 'INSERT INTO members ("name", "grade") VALUES ("舞波", 2)'
end

   ↓不正アクセス防止に利用できる!?

with_scope  : 想定した条件
CRUD の引数 : ユーザの入力

With Scope : GMail System

"mails" table

idownerbody...
1川´・_・リ業務連絡です・・・...
2ノノl∂_∂'ル焼肉が大好き。...
3从 ’w’)大金を稼ぐ!!...
4从 ’w’)俺だよ!俺!!...
5川*^∇^)身長が伸びたよ...

With Scope : GMail Controller

アクセス権限のチェック

class GMailUserController < ApplicationController
  def list
    @mails = Mail.find(:all, :conditions=>["owner=?", user])
  end

  def show
    @mail = Mail.find(params[:id])
    raise IllegalAccess unless @mail[:owner] == user
  end

  def create
    params[:mail][:owner] = user
    @mail = Mail.create(params[:mail])
  end
end

With Scope : mask by with_scope

从 ■w■)<with_scope オン!!

"mails" table

idownerbody...
1川´・_・リ業務連絡です・・・...
2ノノl∂_∂'ル焼肉が大好き。...
3从 ’w’)大金を稼ぐ!!...
4从 ’w’)俺だよ!俺!!...
5川*^∇^)身長が伸びたよ...

With Scope : using with_scope

def mine
  { :find   => {:conditions => ["owner = ?", user]},
    :create => {:owner      => user               } }
end

def list
  Mail.with_scope(mine){ @mails = Mail.find(:all) }
end

def show
  Mail.with_scope(mine){ @mail = Mail.find(params[:id]) }
end

def create
  Mail.with_scope(mine){ @mail = Mail.create(params[:mail]) }
end

With Scope : DRY image

こんな風にしたくなるのがプログラマ

Mail.with_scope(mine) do

  def list
    @mails = Mail.find(:all)
  end

  def show
    @mail = Mail.find(params[:id])
  end

  def create
    @mail = Mail.create(params[:mail])
  end

end

Scoped Access

コントローラのアクションに with_scope をしかけるプラグイン

class MailController < ApplicationController
  scoped_access Mail, :mine

  def list
    @mails = Mail.find(:all)
  end

  def show
    @mail = Mail.find(params[:id])
  end

  def create
    @mail = Mail.create(params[:mail])
  end
end

Scoped Access : with_scope

with_scope の仕組み

class ActiveRecord::Base
  class << self
    def scoped_methods
      @scoped_methods ||= []
    end
    def current_scoped_methods
      scoped_methods.last
    end
    def find(*args)
      add_conditions!    # scoped_methods[:find] の値をマージ
:deleted=>false, :grade=>2
:deleted=>false
scoped_methods [stack]

Scoped Access : Coding

def scoped_access(model, options)  # Controller class
  around_filter ScopedAccess::Filter.new(model, options)
end

Module ScopedAccess
  class Filter
    def initialize(model, options)
      @model = model
      @scope = construct_scoping_from_hash(options)
    end
    def before(controller)
      @model.scoped_methods << @scope
    end
    def after(controller)
      @model.scoped_methods.pop
    end

Scoped Access : MethodScoping

ScopedAccess::MethodScoping クラス

scope = {
  :find   => {:conditions => ["owner = ?", user]},
  :create => {:owner      => user               }
}

scope = MethodScoping.new(:grade=>2, :deleted=>false)
scope.method_scoping
=> {:find  =>{:conditions=>["grade=? AND deleted=?",2,false]},
    :create=>{:grade => 2, :deleted => false                } }

注意点

ActiveRecord.allow_concurrency = true # Rails1.1 (false)

for Rails users

第2章:for Rails users


从 ’w’)<一部の皆様、ご静聴ありがとうございました

for Rails developers

第3章:for Rails developers

  ・Acts As View
  ・Finder Query

「なんかプラグイン作りたい・・・」

Acts As View

・モデルとテーブルの対応

ModelDB(table)
Membermembers
???active_members

CREATE VIEW "active_members"
  AS SELECT * FROM "members" WHERE deleted = false

   ↓普通に "ActiveMember" クラスを作成

・書き戻しができない
・View を作れない DB もある

Acts As View : STI(1)

・Single Table Inheritance で表現してみる

ModelDB(table)
Membermembers
ActiveMembermembers("WHERE type = 'ActiveMember'")
DeletedMembermembers("WHERE type = 'DeletedMember'")

从;’w’)<"type"カラムに依存

Acts As View : STI(2)

・Single Table Inheritance で表現してみる

ModelDB(table)
Membermembers
???members("WHERE deleted = false AND grade = 2")

从;’w’)<複数カラムを扱えない

Acts As View : Coding

从*’w’)!!<with_scope

class ActiveRecord::Base
  def self.acts_as_view(options)
    scoped_methods << MethodScoping.new(options)
  end
end

class Member < ActiveRecord::Base
end

class ActiveMember < ActiveRecord::Base
  acts_as_view :deleted=>false
end

Acts As View : Usage

从*’w’)<複数カラムな条件もOK

class Member < ActiveRecord::Base
end

class ActiveMember < ActiveRecord::Base
  acts_as_view :deleted=>false
end

class ActiveGrade2Member < ActiveRecord::Base
  acts_as_view :deleted=>false, :grade=>2
end

class ActiveGrade2Member < ActiveMember
  acts_as_view :grade=>2
end

Acts As View : STI

从*’w’)<STIも簡単に表現できる

class ActiveRecord::Base
  def self.sti
    acts_as_view :type=>name
  end
end

class Person < ActiveRecord::Base
end

class Customer < Person
  sti
end

从;’w’)<Person.find → a Customer ...

Acts As View : creating view

从 ’w’)!!<動的にViewを作りたい! 無名クラス!?

class ActiveRecord::Base
  def self.create_view(options)
    Class.new(self) do
      set_table_name superclass.table_name
      acts_as_view options
    end
  end
end

grade2active = Member.create_view(:grade=>2, :deleted=>false)
Member.count - grade2active.count  # => 432
grade2active.find(:first)          # => #<Member:0xb7d33aa8>

从*’w’)<Member の scope 空間を汚染しない!!

Acts As View : Instantiate

从 ’w’)<クラス指定なインスタンス生成

class ActiveRecord::Base
  @instantiate_class = nil
  def self.instantiate_class
    @instantiate_class || self
  end
end

class ActiveGrade2 < Member
  acts_as_view :grade=>2, :deleted=>false
  instantiate_class Member
end

Acts As View : Indexer Method

从 ’w’)<突然、インデクサメソッド

class ActiveRecord::Base
  def self.[](*args)
    find(*args)
  end
end

Member[0]      # => Member.find(0)
Member[1,2]    # => Member.find(1, 2)
Member[:first] # => Member.find(:first)

Acts As View : Indexer Method

从 ’w’)<条件で view を作ったり

class ActiveRecord::Base
  def self.[](*args)
    args.is_a?(Hash) ? create_view(*args) : find(*args)
  end
end

Member[:grade=>2]   # => #<Class:0xb7d34fac>

grade2s = Member.find(:all, :conditions=>"grade = 2")
assert_equal grade2s, Member[:grade=>2].find(:all)  # pass

today = {:find=>{:conditions=>"DATEDIFF(now(),created_on)<1"}}
fatal = Syslog[:level=>['EMERG', 'ALERT', 'CRIT']]  # view
fatal[today].find(:all)

Acts As View : Changeset 4425

从 ’w’)<:conditions にハッシュ

Member.find(:all, :conditions=>{:name=>"舞波", :grade=>2})
Member.find(:all, :conditions=>{:grade=>(1..3)})

def sanitize_sql_hash(hash)
  hash.collect { |attrib, value|
    column = "#{table_name}.#{connection.quote_column_name(attrib)}"
    case value
    when Array
      sanitize_sql_array(["#{column} IN (?)", value])
    when Range
      sanitize_sql_array(["#{column} BETWEEN ? AND ?", value.first, value.last])
    else
      "#{column} = #{quote(value)}"
    end
  }.join(" AND ")
end

Acts As View : Operators

从 ’w’)<オペレータで関連とか

(Group | Member)    # => JoinXXX

(Group & Member).join_query
# => "INNER JOIN members ON members.group_id = groups.id"

(Group | Member).join_query
# => "LEFT JOIN members ON members.group_id = groups.id"

(Group | Member).find(:all)
# => same sa Group.find(:all, :include=>:members)

Finder Query

生成したSQLを文字列で受け取りたい

Finder Query : Coding

AR.xxx(*args)          
# => AR.connection.execute(AR.xxx_query(*args))

AR.find_query(*args)   # => "SELECT ..."
AR.find(*args)         
# => AR.connection.select_all(AR.find_query(*args))

AR.create_query(*args) # => "INSERT ..."
AR.create(*args)       
# => AR.connection.insert(AR.create_query(*args))

Finder Query : Subquery

Aclog.find_by_sql <<-SQL
  SELECT  src.path, path_masters.name, cnt
  FROM  (
          SELECT   path, COUNT(*) AS cnt
          FROM     aclogs
          GROUP BY path
          ORDER BY cnt DESC
          LIMIT    10
  ) AS src
  LEFT JOIN path_masters USING (path)
SQL

Finder Query : Subquery

Aclog.find_by_sql <<-SQL
  SELECT  src.path, path_masters.name, cnt
  FROM  (
          SELECT   path, COUNT(*) AS cnt
          FROM     aclogs
          GROUP BY path
          ORDER BY cnt DESC
          LIMIT    10
  ) AS src
  LEFT JOIN path_masters USING (path)
SQL

   ↓finder_query があれば

Aclog.find(:all, :include => :path_master,
  :from => Aclog.count_query(:select=>:path, :group=>:path,
              :order=>"count_path DESC", :limit=>10))

for Rails developers

第3章:for Rails developers


从*’w’)<おしまいは。クゥ〜ン♪