Sequel について (その1)

最近 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

続く

その2に続く