16 Mar 2016, 17:54

DBのViewの使いどころの検討

ある実装についてどのように実装するか悩んだ。
その悩んだ過程や実装案をメモする。似たようなケースの実装案として参考になればと思う。

状況

ある申請に対して、検査Aと検査Bを行い、その検査状態・検査日時を管理・閲覧するシステムがあるとする。
申請のテーブルは下記とする。

  • id: int
  • name: varchar(20) # どうでもいいので無視していい
  • check_a: datetime # 検査すると実施した日時がはいる
  • check_b: datetime # 検査すると実施した日時がはいる

UIとして下記のように表示したいと考えている。

番号 状態 名前 検査A 検査B
1 検査完了 xxxxxx 2016/03/01 10:00 2016/03/01 12:00
2 検査未完了 xxxxxx - -
3 検査途中 xxxxxx 2016/03/01 13:00 -
4 検査途中 xxxxxx - 2016/03/01 15:00

SQLアンチパターンだろ!?

そもそもSQLアンチパターンの「マルチカラムアトリビュート」じゃないの?と思うかもしれない。
この場合は、この設計が一番という前提でお願いしたい。

www.amazon.co.jp

実装の検討

いくつかの実装パターンを検討した。
結果的に今回は実装案4のViewを使うのがいいのではないかと考えた。
が、Viewにもデメリットがある。必ずしもViewがいいというものではないことは留意。

(実装案1) SQL側で頑張る

まず、データを取り出す際にSQLで頑張ることを思いつくはずだ。
例えば以下みたいに。

select
  id,
  name,
  case
    when check_a is null and check_b is null  then '検査未完了'
    when check_a is not null and check_b is not null then '検査完了 '
    else '検査途中'
  end as check_status
from request
 

これが悪いとはいわない。
だが、例えば同じような表示方法で他の条件式で取得したい時があるだろう。

select
  id,
  name,
  case
    when check_a is null and check_b is null  then '検査未完了'
    when check_a is not null and check_b is not null then '検査完了 '
    else '検査途中'
  end as check_status
from request
where name = 'xxxxx'
 

もし、ソースコードの中に同じ形なのに条件だけがことなるSQLが出てきてしまったら、 ヤバイと感じてくるはずだ。

そして、ActiveRecordのようなORマッパーを使っている場合、
case文などを利用し始めた時点で、生SQLを書かなければいけなくなる。
これも不吉な感じがしてくる。

(実装案2) 受け取った側で頑張る

SQLが複雑になるのがいやなら、プログラミング言語側で頑張ることもできる。
とりあえずデータは全部生でもらっておいて

select * from request;

HTML出力の時に頑張る。(下記はRubyのERB想定で記述)

<% if(request.check_a.nil? and request.check_b.nil?) %>
  <span>検査完了</span> 
<% elsif(!request.check_a.nil? and !request.check_b.nil?) %>
  <span>検査未実施</span> 
<% else %>
  <span>検査途中</span> 
<% end %>

ありといえばありだが、もっと条件が複雑になると条件式の管理が大変になってきそうだ。

出力部分をhelperなどに書いて関数化も考えた。
だが、RubyでERBを利用して出力する場合はいいが、
Ajaxでも出力する場合にはRubyヘルパーは利用できない。
とても嫌な予感がした。

(実装案3) DBの設計の変更で頑張る

そもそもテーブルの設計を変えてしまう方法も検討した。
テーブルの中で検査状態を持つのだ。

  • id: int
  • name: varchar(20)
  • check_a: datetime
  • check_b: datetime
  • check_status: int

サーバサイド側で頑張らなきゃいけなくてとても実装する気になれなかったし、これってどうなの?

(実装案4) Viewを利用して頑張る

上3つで見てきたように、いろいろ考えたけどどれもイケてなかった。
そこで思いついたのがデータベースの機能の「View」だ。

Veiwを使ってよく利用する形を事前に作っておくことでいろいろ解決することがわかった。

create view view_request as
select
  id,
  name,
  case
    when check_a is null and check_b is null  then '検査未完了'
    when check_a is not null and check_b is not null then '検査完了 '
    else '検査途中'
  end as check_status
from request

上記で作ったViewを参照することでいくつものメリットが生まれた。

  1. ActiveRecordが利用できるようになりコードが綺麗になった
  2. 重複したSQLが減った
  3. 実現のためのコードをほとんど書かなくて済んだ

だが、もちろんView機能が万能でないことは抑えておく必要がある。
Viewのデメリットは利用する前にしっかり確認してほしい。

itpro.nikkeibp.co.jp

もっといい実装があれば教えて下さい。

このエントリーをはてなブックマークに追加