ビューを作る


 最初は通常のテーブルだけで作るつもりだったけど、SQLを書くとき……というより、プログラムにSQLを埋め込むときに面倒になりました。サイト情報から可変の項目を外に出したため、どうしたってクエリは多くなるし、当然ながらプログラムソースも長くなるから。多少のパフォーマンスの劣化は、本当に必要なマスタだけ検索対象とするよう細やかに設定すれば何とかなったけど。

 で、プロトタイプを作っていて、サイトごとのカップリングテーブルから組み合わせの集計結果を得るときついにブチ切れ。
 データテーブルでは、サイトごとに攻めキャラと受けキャラの組み合わせで登録するため、そこからたとえば「景麒×陽子(10)」(括弧内は登録サイト数)という結果文字列を引っ張ってくるには、キャラマスタとサイト情報テーブル、カップリングテーブルだけではどうしてもSQLが冗長になりますから。というかSQLを書くだけならともかく、それをプログラムに書くのがめんどい。

 そこで「ビューだ! ビューを持ってこーい!」と叫ぶことに。

 ただSQLite側でうまくいっても、CGIとして(今のところはApacheのモジュールとして動かしてるけど)動作させると動かないSQLがあり、かなりはまりました。今回、データベースを設計しなおしていて、「あれ、動かないなぁ。プロトでは動いたのになぁ」というのが多々あり。このWikiを作る前にとりあえず作ってみたプロトタイプと見比べてみたら、CREATE VIEW 文の GROUP BY 句の違いのせいでした。

データを取得できなかったSQL

CREATE VIEW V_SUM_MEDIA AS
	SELECT S.MEDIA AS ID, M.NAME AS NAME, COUNT(S.ID) AS TOTAL
	FROM D_SITE S, M_MEDIA M
	WHERE S.MEDIA = M.ID
	GROUP BY ID;

データを取得できた、プロトタイプのSQL

CREATE VIEW V_SUM_MEDIA AS
	SELECT S.MEDIA AS ID, M.NAME AS NAME, COUNT(S.ID) AS TOTAL
	FROM D_SITE S, M_MEDIA M
	WHERE S.MEDIA = M.ID
	GROUP BY S.MEDIA;

 違いは最後の GROUP BY 句だけ。
 でもさー、なんで? 同じような指定をしていた他のビューは動いてるし、コマンドラインからsqlite3.exeで動作させると、最初のヤツでも普通に動くんだよね。Firefoxのアドオン「SQLite Manager」でも。
 まあ、SQLの仕様として正しいか否かというのは知らないので、そのせいかもしれないけど――釈然としない……。前者だとPHPのPDO::Query()では結果がFalseになるだけで、いったい何がいけないのかわからないから、原因を突きとめるのに何時間もかかってしまったし。

 何にしてもビューがらみは、sqlite3.exeでは動くのに、CGIとして動かすとこれまでも期待した挙動にならないことはありました。PDO::execute()でパラメータを埋め込んでいるはずなのに、特定のビューだけクエリに失敗したり。おかしいなぁと思って、問題のカラムはINTEGERで、execute()で渡すとすべて文字列として扱われるらしいからそのせいかなとも思ったけど、他のビューでは成功するんだよね。だいたいSQLiteでは、文字列として渡しても中身が数値なら数値で判断してくれるはず。
 最終的に苦肉の策で、lower関数を通すことでカラムを文字列として扱ってみたらクエリが動くようになりましたが……これも釈然としない……。

追記

 もしかしてSQLの書き方を間違ってた……? どうもOracleの方言とごっちゃになって、どっちつかずのSQLを書いてたみたいで、本当はこうやって外部結合を書かないといけない……の、かな……。

SELECT S.SITE_TYPE AS ID, M.NAME AS NAME, COUNT(S.ID) AS TOTAL
FROM D_SITE S LEFT OUTER JOIN M_SITE_TYPE M
ON S.SITE_TYPE = M.ID
GROUP BY S.SITE_TYPE;

 あれ? 今さらだけど、この場合は外部結合じゃなくて内部結合でいいんじゃん? まあ、データが不正にならなければ結果は同じか……。ということはパフォーマンスが良いほうを取ればいいのかな。
 というか、最初のJOINを書かない奴だと交差結合だった……のかな……。

追記2


 そもそもカラムの別名は、GROUP BY、HAVINGでは使えないみたい? てことは、動いちゃうSQLiteがおかしいのかな。

2010/09/07 14:04:00
最終更新:2010年09月07日 14:04