最近 Sequel というライブラリを触ってるので簡単にまとめてみます。
Sequel
Sequel は Ruby の構文で SQL クエリを記述するためのライブラリです。 SQL の文法が嫌いな自分には持って来いです。 RDB 毎の差異も吸収してくれます。 自分は MySQL しか知らないので、以下は MySQL での例です。
インストール
% gem install sequel % gem install ruby-mysql
接続
DB = Sequel.connect('mysql://user:password@hostname:port/dbname') # または DB = Sequel.mysql('dbname', :host=>'hostname', :user=>'user', :password=>'password', :port=>'port')
上の例では定数 DB に代入していますが、ローカル変数やインスタンス変数でも問題ありません。
基本
基本的に対象テーブルに対してレコードの範囲を限定する条件を指定し、そして、そのレコードに対しての操作を指定します。 テーブル名、カラム名はシンボルで指定します。
# SELECT DB[:tbl].where(:col1=>value1).select(:col2, :col3).all #=> レコード(Hash)の配列 [{:col2=>val, :col3=>val}, ... ] # UPDATE DB[:tbl].where(:col1=>value1).update(:col2=>value2, :col3=>value3) #=> 更新したレコード数 # DELETE DB[:tbl].where(:col1=>value1).delete #=> 削除したレコード数 # INSERT DB[:tbl].insert(:col1=>value1, :col2=>value2, :col3=>value3) #=> 挿入したレコードに AUTO_INCREMENT があればその値。無ければ 0
update, delete, insert はメソッド実行時にクエリが実行されます。 select メソッドは取り出すカラムを指定するだけで、実際でクエリが実行されるのは、all 等のメソッド実行時です。
where メソッドの代わりに filter も使用できます。
SQL 直接指定
値を返さないクエリ
DB.run("SET some_variable = 123") DB.run("INSERT INTO tbl VALUES (...)")
値を返すクエリ
DB["SELECT * FROM tbl"].all #=> レコード(Hash)の配列 DB.fetch("SELECT * FROM tbl"){|rec| ...} #=> レコード毎にブロックを実行する
指定できる条件
where に引数を指定する方法とブロックで指定する方法があります。 ブロックは特殊なコンテキストで実行されます。 LIKE や不等号等はブロックで指定した方が簡単に記述できます。
一致
where(:col => 123) #=> col = 123
範囲
where(:col => 1..99) #=> col >=1 AND col <= 99
IN
where(:col => [1,2,3]) #=> col IN (1,2,3)
LIKE
where(Sequel.like(:col, '%abc%')) #=> col LIKE BINARY '%abc%' # 大文字小文字を区別する where(Sequel.ilike(:col, '%abc%')) #=> col LIKE '%abc%' # 大文字小文字を区別しない where{col.like '%abc%'} #=> col LIKE BINARY '%abc%' where{col.ilike '%abc%'} #=> col LIKE '%abc%'
LIKE のワイルドカード(% や _)を含む文字列をエスケープするために Sequel::Dataset#escape_like を使用できます。
pattern = DB[:tbl].escape_like('a%b_c') #=> 'a\%b\_c' DB[:tbl].where(Sequel.ilike(:col, "%#{pattern}%")) #=> SELECT * FROM tbl WHERE col LIKE '%a\\%b\\_c%'
REGEXP
where(:col => /abc/) #=> col REGEXP BINARY 'abc' # 大文字小文字を区別する where(:col => /abc/i) #=> col REGEXP 'abc' # 大文字小文字を区別しない where(Sequel.like(:col, /abc/)) #=> col REGEXP BINARY '%abc%' where(Sequel.like(:col, /abc/i)) #=> col REGEXP '%abc%' where(Sequel.ilike(:col, /abc/)) #=> col REGEXP '%abc%' where{col.like /abc/} #=> col REGEXP BINARY '%abc%' where{col.like /abc/i} #=> col REGEXP '%abc%' where{col.ilike /abc/} #=> col REGEXP '%abc%'
>, < >=, <=
where(Sequel.expr(:col) > 123) #=> col > 123 where(Sequel.expr(:col) < 123) #=> col < 123 where(Sequel.expr(:col) >= 123) #=> col >= 123 where(Sequel.expr(:col) <= 123) #=> col <= 123 where{col > 123} #=> col > 123 where{col < 123} #=> col < 123 where{col >= 123} #=> col >= 123 where{col <= 123} #=> col <= 123
AND
Hash に複数要素を指定すると AND で結合されます。
where(:col1 => 123, :col2 => 456) #=> col1 = 123 AND col2 = 456
where に複数引数を与えると AND で結合されます。
where({:col1 => 123}, Sequel.ilike(:col2, '%abc%')) #=> col1 = 123 AND col2 LIKE '%abc%'
where に引数とブロックの両方を与えると AND で結合されます。
where(:col1 => 123){col2.ilike '%abc%'} #=> col1 = 123 AND col2 LIKE '%abc%'
複数の where を連結すると AND で結合されます。
where(:col1 => 123).where(:col2 => 456) #=> col1 = 123 AND col2 = 456
Sequel.& を使って明示的に AND 結合することができます。
where(Sequel.&({:col1 => 123}, {:col2 => 456})) #=> col1 = 123 AND col2 = 456
Sequel で表現した条件や where ブロック内では & を使用できます。
where((Sequel.expr(:col1) > 123) & (Sequel.expr(:col2) < 456)) #=> col1 > 123 AND col2 < 456 where{(col1 > 123) & (col2 < 456)} #=> col1 > 123 AND col2 < 456
OR
複数要素の Hash を AND ではなく OR で結合するには Sequel.or を使用します。
where(Sequel.or(:col1 => 123, :col2 => 456)) #=> col1 = 123 OR col2 = 456
複数条件を OR で結合するには Sequel.| を使用します。
where(Sequel.|({:col1 => 123}, Sequel.expr(:col2) > 456)) #=> col1 = 123 OR col2 > 456 where((Sequel.expr(:col1) > 123) | (Sequel.expr(:col2) < 456)) #=> col1 > 123 OR col2 < 456 where{(col > 123) | (col2 < 456)} #=> col1 > 123 OR col2 < 456
NOT
where の代わりに exclude を使用すれば条件が反転します。
exclude(:col1 => 123) # col1 != 123 exclude({:col1 => 123}, Sequel.expr(:col2) > 456) # col1 != 123 OR col2 <= 456 where(col1 => 123).exclude(col2 => 456) # col1 = 123 AND col2 != 456
特定の条件だけを反転させるために Sequel.~ を使用できます。
where(Sequel.~(:col1 => 123)) #=> col1 != 123 where(Sequel.~(:col1 => 123, :col2 => 456)) #=> col1 != 123 OR col2 != 456 where(Sequel.~(Sequel.ilike(:col1, '%abc%'))) #=> col1 NOT LIKE '%abc%' where{~(col1.ilike "%abc%")} #=> col1 NOT LIKE '%abc%'
ブロックを使用する際の注意
ブロック内で未定義変数を参照するとカラム名や SQL 関数として扱われますが、ブロックの外で定義済みのローカル変数はそのまま変数として使用されてしまいます。 カラム名として扱いたい場合は「()」をつけて未定義メソッドとして表現する必要があります。
DB[:tbl].where{abc > 123} #=> SELECT * FROM tbl WHERE abc > 123 abc = 456 DB[:tbl].where{abc > 123} #=> SELECT * FROM tbl WHERE 1 (456 > 123 は true になるため) DB[:tbl].where{abc() > 123} #=> SELECT * FROM tbl WHERE abc > 123