Ruby/DBIの代わりにSequelを使う
概要
以前 Ruby/DBIの代わりにRDBIを使う でRDBIについて調べたが、そのRDBIも今や更新されていない。そこで今度は Sequel を使ってみることにした。
(逆にRuby/DBIはRuby2.xでエラーが出ないよう更新されたようである)
Sequelとは
Sequel: The Database Toolkit for Ruby
手抜き訳すると
Features:
- Sequel は DSLでSQLクエリやテーブルスキーマを作れるよ。
- Sequel は 簡潔なORMレイヤーだよ。
- Sequel は プリペアードステートメント、ストアドプロシージャ、その他諸々サポートするよ。
- Sequel が サポートするアダプタはこれ→ ADO, Amalgalite, IBM_DB, JDBC, MySQL, Mysql2, ODBC, Oracle, PostgreSQL, SQLAnywhere, SQLite3, and TinyTDS.
至れり尽くせりのようだ
Sequelの更新頻度は多く、旧バージョン(4.x)ではruby1.8でも動作する。
Sequelをなるべく簡単に使う
SequelはORMとして使うのが主流だろうが、やりたいのはRuby/DBIのように素のSQL実行とレコード取得だけ。
以下にRuby/DBIとSequelの例を記述していく。
DBに接続する
dbi-connect.rb
dbh = DBI.connect("dbi:Pg:dbname", "user", "pass")
# or
#dbh = DBI.connect("dbi:Pg:database=dbname;port=5432;host=localhost", "user", "pass")
sequel-connect.rb
DB = Sequel.connect("postgresql://user:pass@localhost/dbname")
# or
#DB = Sequel.connect(:adapter => 'postgresql', :database=>"dbname", :user=>"user", :password=>"pass")
connectの引数にURL風の文字列かHashを渡す
SELECTする
dbi-select.rb
#1行
dbh.select_one("SELECT * FROM table_name") #=> [1, "aaa"]
#全行
dbh.select_all("SELECT * FROM table_name") #=> [[1, "aaa"], [2, "bbb"], [3, "ccc"]]
#全行をwhile loopで
dbh.execute("SELECT * FROM table_name") do |sth|
while(row = sth.fetch) do
p row #=> [1, "aaa"]
end
end
sequel-select.rb
#1行
DB["SELECT * FROM table_name"].first #=> {:id => 1, :name => "aaa"}
#全行
DB["SELECT * FROM table_name"].all #=> [{:id => 1, :name => "aaa"}, {:id => 2, :name => "bbb"}, {:id => 3, :name => "ccc"}]
#全行をeachで
DB["SELECT * FROM table_name"].each do |row|
p row #=> {:id => 1, :name => "aaa"}
end
結果を返さないSQLを実行する
DBIならdo
dbi-execute.rb
dbh.do("set client_encoding=UTF8")
dbh.do("SELECT nextval('table_name_id_seq')")
dbh.do("UPDATE table_name SET id=id+?", 0)
Sequelならrun
sequel-execute.rb
DB.run("set client_encoding=UTF8")
DB.run("SELECT nextval('table_name_id_seq')")
DB.run("UPDATE table_name SET id=id+?", 0)
実行結果のカラム名を取得する
dbi-column.rb
# StatusHandler#column_namesを使う
dbh.execute("SELECT * FROM table_name LIMIT 0") do |sth|
p sth.column_names #=> ["id", "name"]
end
sequel-column.rb
DB["SELECT * FROM table_name"].columns #=> [:id, :name]
トランザクション
dbi-transaction.rb
dbh["AutoCommit"] = false
begin
# トランザクション中の処理
dbh.commit
rescue
# 失敗
dbh.rollback
end
sequel-transaction.rb
DB.transaction do
# トランザクション中の処理
end
#or
DB.run("BEGIN")
begin
# トランザクション中の処理
DB.run("COMMIT")
rescue
# 失敗
DB.run("ROLLBACK")
end
Sequelの注意点
実行されるタイミングに注意
Sequelの独特な点として、DatabaseやDatasetにSQL文を渡しても実際には実行されていない場合がある。
DB = Sequel.connect("...")
DB.run("set client_encoding=UTF8") # Database::run() は 即実行される
dataset = DB["SELECT * FROM table_name"] # この時点では実行されない
dataset = dataset.where{id>2}.limit(10) # まだ実行されない。指定した条件でSQLが書き換えられる(SELECT * FROM table_name WHERE id>2 LIMIT 10)
dataset.all # allやfirstメソッドで初めてSQLが実行される
ruby風にSQLを表現する為だろうけど、個人的にはあまり使いたくない
結果(1行)は配列ではなくHash
Ruby/DBIではselect_oneが(Arrayをextendした)DBI::Rowインスタンスが返ったが、Sequelではカラム名(orエイリアス)をキーにしたHashオブジェクトが返る。カラム名が重複するとデータが消失するのでSQL作成時に注意が必要になる。
#カラム名重複(nameカラムとエイリアスのname)してデータが消える例
DB["SELECT id,name,'XYZ',123 as name FROM table_name"].each do |row|
p row #=> {:id=>1, :name=>123, :"?column?" => "XYZ"}
end
レコード1行をHashでなくArrayで取得するには Dataset#get(columns) を利用する
ds = DB["SELECT * FROM table_name"]
cols = ds.columns #=> [:id, :name]
ds.get(cols) #=> [1, 'aaa']
ただこの場合でも名前が重複しているとダメ
ds = DB["SELECT id,name,'XYZ',123 as name FROM table_name"]
ds.get([:id, :name]) #=> [1, 123]
Sequelの便利な使い方
話は逸れるが、ドキュメントを読んでいて気づいた便利な使い方をメモ
実行可能なSQLを作成する
値束縛前のSQL("... WHERE hoge=?"など)と値の配列から実行可能なSQL文字列を取得出来る。
実際にどんなSQLが実行されるか確認出来る
DB["SELECT * FROM table_name WHERE name LIKE ?", "%'%"].select_sql
#=> "SELECT * FROM table_name WHERE name LIKE name like '%''%'"
# insert,updateの場合はinsert_sql, update_sqlメソッド
DB["INSERT INTO table_name (id,name) VALUES(?,?)", 99, "九十九"].insert_sql
#=> "INSERT INTO table_name (id,name) VALUES(99,'九十九')"
DB["UPDATE table_name SET name=? WHERE id=?", "'９９９９'", 99].update_sql
#=> "UPDATE table_name SET name='''９９９９''' WHERE id=99"
Loggerに吐き出す
実行されるSQLを確認したいならLogger出力も出来る
require 'logger'
DB.loggers = Logger.new("logs/sql.log")