SQLによる固定長構造への対応
タイトルが仰々しいのだけれども、例えばこういうケースを考えてもらいたい。
あるシステムには管理者がいる。ただし、管理者の数は5人が上限である。各管理者には1から5までの番号(view_id)が振られる。
各管理者に対して、とある操作をしたかどうかのログを残すものとする。このログについては自動的に生成する。
残されたログに対して、後で誰が何回操作をしたか、というのを表にして出力したい。
まずDBに管理者テーブルを持たせる。まあ、高々5人なのでDBを使用しなくても良いのだけれど、他の部分でDBを使っている関係上もあってDBを使う。
普通に考えるとこんな感じになるんだろうか。
CREATE TABLE master( id int auto_increment not null primary key, view_id int(1) not null, name varchar(64), delete_flg tinyint(1) not null default 0 );
さて、まずここまでは出来た。
次に、普通のSQLでは日付のリストを生成することが出来ないので、面倒だが次のようなテーブルを用意しよう。このテーブルに一日一回データを入れることによって、管理を行うものとする。
CREATE TABLE datelist( id int auto_increment not null primary key, d date not null );
最後に必要になるのは、ログの部分であって、
CREATE TABLE log( id int auto_increment not null primary key, master_view_id int not null, datelist_id int not null, exec_num int not null );
さて、ここからが問題だ。
ある日について、view_idごとにどれだけの操作をしたのかをまとめたい。プログラム的に書くと(Perlだが)、以下のようなものだ。
@log; for(1..5){ push @log, getFromLog(date => $date, view_id => $_); }
さて、このプログラムでは一日のデータを取得するだけで何度も何度もSQLを叩いている。通信も含めて無駄でしかない。なので、こういうときにはこういうSQLを走らせればいい。
SELECT datelist.d, log1.exec_num, log2.exec_num, log3.exec_num, log4.exec_num, log5.exec_num FROM datelist LEFT JOIN log AS log1 ON log1.master_view_id = 1 AND datelist.id = log1.datelist_id LEFT JOIN log AS log2 ON log2.master_view_id = 2 AND datelist.id = log1.datelist_id LEFT JOIN log AS log3 ON log3.master_view_id = 3 AND datelist.id = log1.datelist_id LEFT JOIN log AS log4 ON log4.master_view_id = 4 AND datelist.id = log1.datelist_id LEFT JOIN log AS log5 ON log5.master_view_id = 5 AND datelist.id = log1.datelist_id WHERE d = ?
もちろん複数の日付の場合にも取得できるし、仮にlogの中に値が無い日がある場合には、例えば
LEFT JOIN log AS log1 ON log1.id IS NULL OR (log1.master_view_id = 1 AND datelist.id = log1.datelist_id)
としてやることで回避できる。もちろん複数行ある場合は別だし、この場合もNULLで返ってくるのでちゃんとそこを後で変換してやる必要はある。
そもそもの設計として、こういうような固定のものを一つのテーブルで管理するというようなことはあまり良くないのかも知れない。しかしながら、こういうデータを取得しようという試みがプロジェクトの途中で突然発生することもある。
そういうときに、あわてずに上記のような長いSQLをさらさらっと書くというのはありだと思う。しかし、こういうSQLについての文章ってのを見かけることはあまり無いような気もする。まあ間違いやすいと言うのもあるだろうけれども。
Perl使いは可能な限り正規表現でやろうとすると言われるわけだが、こういう場合はさすがに対応できなくて悲しい思いをしているのではないだろうか(妄想)。そんなときは何でもSQLでやろうとしてやればストレスは溜まらないと思う(気のせい)。