Sequelと戯れて分かったことをメモ書き

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を出力することができます。

```ruby:sample.rb
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を使うとか。

```ruby:sample.rb
# 以下、めんどいので接続情報などは省略します
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``を使わなかった場合にどう対応するべきかが分かっておりません…:confounded:

## where句を動的に作る場合の注意点
例えば以下のように定義したsampleテーブルがあるとします。

| id      | name    | date     | type    | is_active           |
|:--------|:--------|:---------|:--------|:--------------------|
| integer | varchar | datetime | tinyint | boolean(tinyint(1)) |

このテーブルに対してselectをかけたい場合、以下のように記述します。

```ruby:sample.rb
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を動的に変えたいってことがあるかと思います。
そういう場合。

```ruby:sample.rb
# 超汚いですけど、やりたいのは { 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`だったらの場合です。

```ruby:sample.rb
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`するだけです。

```ruby:sample.rb
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型でデータが返ってきます。

```ruby:sample.rb
date = dbh[:sample___s].select(
  :s__date
).first[:date]
date.class # => Time
date # 2014-09-01 12:00:00 +0900
```

文字列でいい感じの年月日表記に変換してほしいんだけど、`map`でもしなきゃダメ？
と思っていたのですが、以下のやり方でいけました。

```ruby:sample.rb
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の日付時刻関数](http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-functions.html)で、そのシンボルをレシーバとして`sql_function`メソッドを呼びます。
メソッドの第一引数に対象のカラム名をシンボルで、第二引数には整形したい書式を渡します。
そして、そのままでは出力するデータのキーが
``:"date_format(`s`.`date`, '%Y/%m/%d %H:%i:%s')"``
こんな感じの**めっちゃださい**のになってしまいますので、`as`でカラム名を指定してあげます。
これでわざわざ`map`で`strftime`をする必要がなくなります。

### DATETIME型を整形して出力する方法のRspecのmock化
```rb:sample_rspec.rb
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`でした。
ドキュメントだと[ここらへんに書いてあること](http://rubydoc.info/gems/sequel/4.3.0/Symbol:sql_function)です。
2時間くらいハマって泣きそうになってるところを先輩に教えていただきました。
この場を借りて改めて御礼申し上げます。

## .where().where()とか.where().or()とかやらないORのやり方
例えば関東と九州のデータで絞り込みたいです、という要望があった場合。
SQL的には以下の様な形になるかと思います。

```sql
select
  *
from
  hogehoge
where
  pref_id between 8 and 14 OR
  pref_id between 40 and 47
```
※関東などを識別する広域コードはテーブル上には持っていないものとして。
じゃあそれをSequelで書こうと思った場合、``Range``を渡すとbetweenと同等のことをしてくれます。

```rb:pref.rb
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](http://sequel.jeremyevans.net/rdoc/classes/Sequel/Dataset.html#method-i-where)はいろいろな型を受け取るようなので試してみました。

```rb:ng_1.rb
# 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)
```
エラーになりました。

```rb:ng_2.rb
# 配列に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のドキュメントを読んで辿り着いたのがこれです。

```rb:ok.rb
# 配列の中に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.|](http://sequel.jeremyevans.net/rdoc/classes/Sequel/SQL/BooleanExpression.html#method-i-7C)を読んでもよく分からなかったのですが、どうやらOR検索したい条件をそのまま引数として渡せばいいようです。
あと最初l（小文字のエル）と見間違えてエラーになって辛かったですが、lじゃなくて|（パイプ）です。

結局仕様の関係上、この絞り込みは使う機会はなくなりましたが、ORの使い方を一つ学びました。

## 文字列結合のやり方
MySQLではconcat、Postgresでは || を使う文字列結合ですが、
Sequelではどうやるのか？

```rb
['str1', 'str2'].sql_string_join
```
でした。
たぶんこの表記はSequel 3.29あたり限定な気がします。
最新のrdocを見てもこの表記が見当たらないのですよね。libの中をgrepしたわけではないので不確かですが。
[ココらへん](http://sequel.jeremyevans.net/rdoc/classes/Sequel/SQL/Builders.html#method-i-join)と同じことをやろうとしてるのですが…

結合対象がセルの値であればシンボルでカラムを指定します。

```rb
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の話題を検索する](http://stackoverflow.com/search?q=Sequel%20ruby)とだいたい作者が答えてたりするので分からないことがあったらStackoverflowで尋ねるのもよいかも。