SELECT文生成クラス

検索条件がややこしい場合に、SELECT文を作ろうとすると非常にややこしくなった経験から、SELECT文をすっきり書くためのクラスを作ってみました。使い方はこんな感じ。

# タイトルで本を検索
select = Select.new("b.id, b.isbn, b.title")
select.from("books").as("b")
select.where("b.title like '%フーコー%'")
if 著者名も指定されていた場合
  select.from.get("b").inner_join("authors").as("a").on("b.author_id = a.id")
  select.where.and("a.name like '%エーコ%'")
end
Book.find_by_sql(select.to_s)

けっこー直感的でいい感じかと思うんですけど、どうでしょう？きっとこういうのが既にあるんじゃないかなと思いつつ、見つからなかったんで勉強も兼ねて書いてみました。select.fromの戻り値の型が引数の有無で違うっていうのがちょっと気持ち悪い気もしますが直感を優先しました。

以下、テストケースとソースコードっす。
select_test.rb

# copyright akima
require 'select'

class SelectTest < Test::Unit::TestCase

  def test_introduction
    # タイトルで本を検索
    select = Select.new("b.id, b.isbn, b.title")
    select.from("books").as("b")
    select.where("b.title like '%フーコー%'")
    assert_equal "select b.id, b.isbn, b.title" + 
      " from books as b where (b.title like '%フーコー%')", select.to_s
    # 著者名も指定されていた場合
    select.from.get("b").inner_join("authors").as("a").on("b.author_id = a.id")
    select.where.and("a.name like '%エーコ%'")
    assert_equal "select b.id, b.isbn, b.title" + 
      " from books as b inner join authors as a on (b.author_id = a.id)" +
      " where ((b.title like '%フーコー%') and a.name like '%エーコ%')", select.to_s
  end

  def test_build_sql
    # booksとpartiesをjoin
    select = Select.new("b.id, b.name, p.id, p.name")
    select.from("books").as("b").inner_join("parties").as("p").on("b.author_id = p.id")
    assert_equal "select b.id, b.name, p.id, p.name" + 
      " from books as b inner join parties as p on (b.author_id = p.id)", select.to_s
    #
    # joinのonに条件を追加
    select.from.get("p").on.and("p.type_cd = 2")
    assert_equal "select b.id, b.name, p.id, p.name" + 
      " from books as b inner join parties as p on" +
      " ((b.author_id = p.id) and p.type_cd = 2)", select.to_s
    #
    # joinしているpに更にcontactsテーブルをcとしてjoin
    select.from.get("p").left_outer_join("contacts").as("c").on("c.party_id = b.id")
    select.fields += ", c.address"
    # where句に条件を追加
    select.where("c.address like '%London%'")
    assert_equal "select b.id, b.name, p.id, p.name, c.address" + 
      " from books as b inner join parties as p on" +
      " ((b.author_id = p.id) and p.type_cd = 2)" +
      " left outer join contacts as c on (c.party_id = b.id)" +
      " where (c.address like '%London%')", select.to_s
    #
    # 更にpartiesテーブルを別名companyとしてfromに追加
    select.from.add("parties").as("company")
    # where句に条件を追加
    select.where << "company.id = p.company_id"
    assert_equal "select b.id, b.name, p.id, p.name, c.address" + 
      " from books as b inner join parties as p on" +
      " ((b.author_id = p.id) and p.type_cd = 2)" +
      " left outer join contacts as c on (c.party_id = b.id)," +
      " parties as company" +
      " where (c.address like '%London%' and company.id = p.company_id)", select.to_s
    #
    # where句にorで条件を追加
    select.where.or("company.type_cd = 3")
    assert_equal "select b.id, b.name, p.id, p.name, c.address" + 
      " from books as b inner join parties as p on" +
      " ((b.author_id = p.id) and p.type_cd = 2)" +
      " left outer join contacts as c on (c.party_id = b.id)," +
      " parties as company" +
      " where ((c.address like '%London%' and company.id = p.company_id)" +
      " or company.type_cd = 3)", select.to_s
  end
end

select.rb

# copyright akima
class Select
  
  attr_accessor :fields
  attr_accessor :from
  attr_accessor :where
  attr_accessor :group_by
  attr_accessor :having
  attr_accessor :order_by
  
  def initialize( fields = nil )
    @fields = fields
    @from = FromPhase.new
    @where = Conditions.new
    @group_by = nil
    @having = Conditions.new
    @order_by = nil
  end

  def to_s
    build
  end
  
  def build
    s = "select " + (Helper.empty?(@fields) ? "*" : @fields)
    s += " from " + @from.to_s
    s += " where " + @where.to_s unless @where.empty?
    s += " group by " + @group_by.to_s unless Helper.empty?( @group_by )
    s += " having " + @having.to_s unless @having.empty?
    s += " order by " + @order_by.to_s unless Helper.empty?( @order_by )
    return s
  end
  
  def from( table = nil) 
    (table) ? @from.add(table) : @from
  end
  
  def where( condition = nil) 
    (condition) ? @where.add(condition) : @where
  end
  
  def having( condition = nil) 
    (condition) ? @having.add(condition) : @having
  end
  
  class Helper
    def self.empty?( s )
      return true unless s
      return s.empty?
    end
  end

  class Conditions
    attr_accessor :items
    attr_accessor :bracket
    attr_accessor :connector
    
    def initialize( connector = 'and', bracket = true )
      @connector = connector
      @bracket = bracket
      @items = Array.new
    end
    
    def empty?
      @items.empty?
    end
    
    def add( item )
      @items << item
      return self
    end
    
    def and( item )
      connect("and", item)
    end
    
    def or( item )
      connect("or", item)
    end
    
    def connect( connector, item )
      s = self.to_s
      @items = Array.new
      @items << s unless s.empty?
      @items << item unless item.empty?
      @connector = connector
      return self
    end
    
    def <<( item )
      add(item)
    end
    
    def checkValue( value )
      return (value) && (value != "")
    end
    
    def addCompare(left, operator, right)
      return self unless checkValue( right )
      return self unless checkValue( left )
      if "in" == operator.downcase
        return add("#{left} in (#{right.to_a.join(',')})")
      end
      r = (right.is_a? String) ? "'#{right}'" : "#{right.to_s}" 
      return add("#{left.to_s} #{operator.to_s} #{r}")
    end
    
    def addEqual( left, right )
      operator = (right.is_a? Array) ? "in" : "="
      return addCompare(left, operator, right)
    end
    
    def to_s
      return "" if @items.empty?
      result = @items.join(" " + @connector + " ")
      result = "(#{result})" if @bracket
      return result
    end
  end
  
  class FromPhase
    def initialize
      @tables = Array.new
    end
    
    def empty?
      @tables.empty?
    end
    
    def <<(table)
      add(table)
    end
    
    def add(table)
      table = TableUnit.new(table) unless table.is_a? TableUnit
      @tables << table
      return table
    end
    
    def get(name)
      @tables.each{|table| 
        result = table.get(name)
        return result if result
      }
      return nil
    end
    
    def to_s
      return "" if empty?
      return @tables.join(", ")
    end
  end
  
  class FromUnit 
    attr_accessor :name
    attr_accessor :table
    attr_accessor :joins
    
    def initialize(table)
      @table = table
      @name = nil
      @joins = Array.new
    end
    
    def get( n )
      return self if n == @name
      @joins.each{|join| 
        result = join.get(n)
        return result if result
      }
      return nil
    end
    
    def as(name)
      @name = name
      return self
    end
    
    def to_s
      (@joins.empty?) ? "" : @joins.join(" ")
    end
    
    def join(table, join_type = "inner join")
      result = JoinUnit.new(table, join_type)
      @joins << result
      return result
    end
    def inner_join(table)
      join(table, "inner join")
    end
    def left_outer_join(table)
      join(table, "left outer join")
    end
  end
  
  class TableUnit < FromUnit
    def initialize(table)
      super(table)
    end
    
    def to_s
      subJoins = super;
      result = @table + (!Helper.empty?(name) ? (" as #{name}") : "" ) + 
        (subJoins.empty? ? "" : (" " + subJoins))
    end
  end
  
  class JoinUnit < FromUnit
    attr_accessor :join_type
    attr_accessor :conditions
    def initialize(table, join_type)
      super(table)
      @join_type = join_type
      @conditions = Conditions.new
    end
    
    def on( condition = nil )
      condition.to_a.each{ |c| @conditions << c } if condition
      return @conditions 
    end

    def to_s
      subJoins = super;
      result = @join_type + " " + @table + (!Helper.empty?(name) ? (" as #{name}") : "" ) + 
        ( @conditions.empty? ? "" : " on " + @conditions.to_s ) +
        (subJoins.empty? ? "" : (" " + subJoins))
    end
  end
end

まだRubyのコードの書き方に自信がないので、何かアドバイスなどがあったらコメント下さい。よろしくお願いします　> Ruby使いの方