Sequel3.29というちょっと古いのと戯れました。
そこで分かったこととかを忘れないようにメモっておきます。
※古いバージョンなので執筆時現在の最新バージョン4.14とは異なる部分もあるかもしれません。
実行されるSQLを確認する方法
http://sequel.jeremyevans.net/rdoc/files/doc/opening_databases_rdoc.html#documentation
http://sequel.jeremyevans.net/rdoc/files/doc/cheat_sheet_rdoc.html#label-Logging+SQL+statements
ここらへんに記載されているとおりですが、標準添付ライブラリのloggerを使うことで
Sequelから実行されるSQLを出力することができます。
require 'sequel'
require 'logger'
opts = {
# hostなどDB接続情報
loggers: [Logger.new('/tmp/sql.log')]
}
dbh = Sequel.connect(opts)
dbh[:sample_data___st].select(
:st__id,
:st__data
).where(
st__id: 10
).all # 配列形式で該当するデータを全件取得
と、こんな風にすることでloggersキーで指定した場所にSQLが出力されるようになります。
ご覧のとおりloggersには配列を渡しているので複数のログに吐くことが可能となっています。
出力結果としてはこんな感じです。
(0.019228s) SELECT `st`.`id`, `st`.`data` FROM `sample_data` AS `st` WHERE (`st`.`id` = 10)
先頭の秒数が取得にかかった時間ですかね？そこまでは調べがついていませんが、データ件数が多いとその分、秒数が大きくなったので多分そういうことなんだと思います。
またはSequel::Datasetに対してsqlを使うとか。
# 以下、めんどいので接続情報などは省略します
sql = dbh[:sample_data___st].select(
:st__id,
:st__data
).where(
st__id: 10
).sql
p sql # "SELECT `st`.`id`, `st`.`data` FROM `sample_data` AS `st` WHERE (`st`.`id` = 10)"
開発中はこっちの方がすぐに確認できてよいですね。
ちなみに#sqlはselect_sqlのエイリアスですのでselect句しか対応していません。
insertやupdateなどのSQLを確認したい場合はそれぞれ#insert_sqlとか#update_sqlを使ってください。
_（アンダースコア）の使い方
※v4.46からこのアンダースコアがdeprecateとなりました。
http://sequel.jeremyevans.net/rdoc/files/doc/release_notes/4_46_0_txt.html
Sequel.split_symbols = trueを使わないとWarningが発生します。
v5.2.0では___もテーブル名とみなして「そんなテーブルないよ」ってエラーが出るようになりました。
使い方
_(アンダースコア)を連続して使うことに意味があります。
http://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html#label-Aliasing
使い方のサンプルは上記のを参考にすれば分かるかと思いますが、
___（アンダースコア3つ）でテーブル名のエイリアスになります。
__（アンダースコア2つ）でテーブルの指定になります。
case Oracle
Oracleの場合、テーブルのオーナーを指定する必要がある場合もあります。
その場合もこのアンダースコアが使えます。
ただ、v4.46でSequel.split_symbolsを使わなかった場合にどう対応するべきかが分かっておりません…
where句を動的に作る場合の注意点
例えば以下のように定義したsampleテーブルがあるとします。
| id | name | date | type | is_active |
|---|---|---|---|---|
| integer | varchar | datetime | tinyint | boolean(tinyint(1)) |
このテーブルに対してselectをかけたい場合、以下のように記述します。
dbh = Sequel.connect(opts)
dbh[:sample___s].select(
:s__id,
:s__name,
:s__date,
:s__type
# is_activeは必要ないので記述しない
).where(
:s__is_active => true # is_activeがtrueのデータだけ取得
).all # 配列形式で該当するデータを全件取得
引数に応じてwhereを動的に変えたいってことがあるかと思います。
そういう場合。
# 超汚いですけど、やりたいのは { s__{k}: v } なデータを返したいだけです
# サンプルなので許して…。
# @param [Hash] arg
# @return [Hash]
def make_where(arg)
arg.each_with_object({}) do |(k, v), ret|
ret["s__#{k}".to_sym] = v
end
end
where_param = make_where(arg)
dbh[:sample___s].select(
:s__id,
:s__name,
:s__date,
:s__type
# is_activeは必要ないので記述しない
).where(
:s__is_active => true
).where(
where_param # <= 動的に作られるよ！
).all
この例だと = の記述にしか対応できていないですけども、例えば
arg = {type: 1}とかだったらwhere句にs__type = 1も追加されるわけです。
んで、もしこのwhere_paramの結果がnilだったらの場合です。
def make_where(arg)
return nil if arg.empty? # argが空の場合nilを返す
arg.each_with_object({}) do |(k, v), ret|
ret["s__#{k}".to_sym] = v
end
end
where_param = make_where(arg)
dbh[:sample___s].select(
:s__id,
:s__name,
:s__date,
:s__type
).where(
:s__is_active => true
).where(
where_param
).all # sequel-3.29.0/lib/sequel/dataset/query.rb:1031:in `filter_expr': Invalid filter argument (Sequel::Error)
whereから呼ばれるfilter_exprがnilを解釈できずにエラーになります。
なのでwhereのパラメータを生成するようなメソッドはnilを返さずに空Hashを返すようにしましょう、というお話です。空配列でもOKっぽい。
exists句の書き方
前述のwhereメソッドにexistsで生成したリテラル(Sequel::LiteralString)を渡せば、
exists句も簡単に使えるのが便利でよいですね。
exists句に使いたいSQLを書いて、最後にexistsするだけです。
dbh[:sample___s].select(
:s__id
).where(
:s__is_active => true
).where(
dbh[:ex_sample___es].select(
:es__id
).where(
:es__type => 2,
:es__id => s__id
).exists
).sql
# SELECT
# `s`.`id`
# FROM
# `sample` AS `s`
# WHERE (
# (`s`.`is_active` IS TRUE) AND (
# EXISTS (
# SELECT
# `es`.`id`
# FROM
# `ex_sample` AS `es`
# WHERE (
# (`es`.`type` = 2) AND
# (`es`.`id` = `s`.`id`)
# )
# )
# )
# ) -- 長いので適宜改行とかしてます。
これだけでこんな感じのSQLを出力してくれます。便利！
DATETIME型を出力する際の整形する方法
今までの例のようにselect時に特に何も指定せずにDATETIME型のカラムを取得するとTime型でデータが返ってきます。
date = dbh[:sample___s].select(
:s__date
).first[:date]
date.class # => Time
date # 2014-09-01 12:00:00 +0900
文字列でいい感じの年月日表記に変換してほしいんだけど、mapでもしなきゃダメ？
と思っていたのですが、以下のやり方でいけました。
date = dbh[:sample___s].select(
:date_format.sql_function(:s__date, '%Y/%m/%d %H:%i:%s').as(:date)
).first[:date]
date.class # => String
date # "2014/09/01 12:00:00"
# oracleなら
dbh[:sample___s].select(
:to_char.sql_function(:s__date, 'YYYY/MM/DD HH24:MI:SS').as(:date)
).first[:date]
# oracleはTO_CHARですし、日付のフォーマットの記述が違いますからね
:date_formatがMySQLの日付時刻関数で、そのシンボルをレシーバとしてsql_functionメソッドを呼びます。
メソッドの第一引数に対象のカラム名をシンボルで、第二引数には整形したい書式を渡します。
そして、そのままでは出力するデータのキーが
:"date_format(`s`.`date`, '%Y/%m/%d %H:%i:%s')"
こんな感じのめっちゃださいのになってしまいますので、asでカラム名を指定してあげます。
これでわざわざmapでstrftimeをする必要がなくなります。
DATETIME型を整形して出力する方法のRspecのmock化
let(:sql_function) { double('sql_function') }
let(:date) { double('date') }
allow(Sequel::SQL::Function).to receive(:new).with(:date_format, :s__date, '%Y/%m/%d %H:%i:%s').and_return(sql_function)
allow(sql_function).to receive(:as).with(:date).and_return(date)
だいたいこんなの適宜使う感じで。
:date_format(Symbol)レシーバに対してsql_functionをmock化すればいいのかと思ってたけど実際はSequel::SQL::Functionでした。
ドキュメントだとここらへんに書いてあることです。
2時間くらいハマって泣きそうになってるところを先輩に教えていただきました。
この場を借りて改めて御礼申し上げます。
.where().where()とか.where().or()とかやらないORのやり方
例えば関東と九州のデータで絞り込みたいです、という要望があった場合。
SQL的には以下の様な形になるかと思います。
select
*
from
hogehoge
where
pref_id between 8 and 14 OR
pref_id between 40 and 47
※関東などを識別する広域コードはテーブル上には持っていないものとして。
じゃあそれをSequelで書こうと思った場合、Rangeを渡すとbetweenと同等のことをしてくれます。
dbh[:hogehoge].where({pref_id: 8..14}).or({pref_id: 40..47}).sql
# => SELECT * FROM "hogehoge" WHERE ((("pref_id" >= 8) AND ("pref_id" <= 14)) OR (("pref_id" >= 40) AND ("pref_id" <= 47)))
しかしこのエリアの条件が可変で、関西とか四国とか何種類選ばれるか分かりません。
動的に作らなければなりません、となった場合、どう作るのか。
さすがにdataset.where().where()...とかdataset.where().or()...みたいに可変でメソッドを呼ぶのはダサいし、テストも書きづらそうだな、と思います。
#whereはいろいろな型を受け取るようなので試してみました。
# Hashに配列を持たせる
where = {pref_id: [8..14, 40..47]}
dbh[:hogehoge].where(where).sql
# => sequel-3.48.0/lib/sequel/dataset/sql.rb:1183:in `literal_other_append': can't express 8..14 as a SQL literal (Sequel::Error)
エラーになりました。
# 配列にHashを持たせる
where = [{pref_id: 8..14}, {pref_id: 40..47}]
dbh[:hogehoge].where(where).sql
# => SELECT * FROM "hogehoge" WHERE (("pref_id" >= 8) AND ("pref_id" <= 14) AND ("pref_id" >= 40) AND ("pref_id" <= 47))
エラーにはなりませんでしたが、AND検索になっている上に、括弧でグループ化していないのでクエリとしておかしなことになってます。当然、検索結果は0件です。
他にもパッと思いついたのをいろいろ試したのですが、エラーになるかAND検索になるかでうまく行きませんでした。
で、Sequelのドキュメントを読んで辿り着いたのがこれです。
# 配列の中にHashのデータをSequel.|メソッドに展開して渡す
where = Sequel.|(*[{pref_id: 8..14}, {pref_id: 40..47}])
dbh[:hogehoge].where(where).sql
# => SELECT * FROM "hogehoge" WHERE ((("pref_id" >= 8) AND ("pref_id" <= 14)) OR (("pref_id" >= 40) AND ("pref_id" <= 47)))
読解力がなくてドキュメントのSequel.|を読んでもよく分からなかったのですが、どうやらOR検索したい条件をそのまま引数として渡せばいいようです。
あと最初l（小文字のエル）と見間違えてエラーになって辛かったですが、lじゃなくて|（パイプ）です。
結局仕様の関係上、この絞り込みは使う機会はなくなりましたが、ORの使い方を一つ学びました。
文字列結合のやり方
MySQLではconcat、Postgresでは || を使う文字列結合ですが、
Sequelではどうやるのか？
['str1', 'str2'].sql_string_join
でした。
たぶんこの表記はSequel 3.29あたり限定な気がします。
最新のrdocを見てもこの表記が見当たらないのですよね。libの中をgrepしたわけではないので不確かですが。
ココらへんと同じことをやろうとしてるのですが…
結合対象がセルの値であればシンボルでカラムを指定します。
dbh = Sequel.connect(conf)
select = ['prefix-', :h__column_name].sql_string_join.as(:prefix_column)
p dbh[:hoge___h].select(select).sql
# SELECT ('prefix-' || "h"."column_name") AS "prefix_column" FROM "hoge" AS "h"
# Postgresの場合。MySQLだと||がconcatに変換されるかも？
ドキュメント見ても出てこない表記で結局lib以下をgrepして分かったやり方ですが、見つけるまでストレスがマッハでハゲるかと思いました。
まとめ
Sequelは非常に便利。
StackoverflowでSequelの話題を検索するとだいたい作者が答えてたりするので分からないことがあったらStackoverflowで尋ねるのもよいかも。