空想犬猫記

※当日記では、犬も猫も空想も扱っておりません。(旧・エト記)

NULL値を条件にしてSELECTする

Androidの概念が存在するより昔、まだファイルベースDBとしてはBerkeley DBのほうがメジャーであった頃から、SQLiteをデスクトップアプリの内部フォーマットとして採用して細々と使い続けてきた。十年以上使って、一度たりとも互換性の問題もデータが壊れたり失われたこともなかったことは、このDBの品質の証であろうと思う。長らく単純に「安定したシリアライズフォーマット」としての利点を利用してきたが、ここに来て、もう少し積極的にRDBの機能を使って行こうという流れになってきている。

FOREIGN KEY拘束や、関数、トリガーなどを駆使して、RDBをデスクトップアプリのデータモデルの中心に置くと、色々嬉しいことがある反面、アプリから見たらブラックボックスであるRDB独自の仕様に引きずられてしまうこともある。そんなわけで、ちょくちょく学んだことをメモして行こうと思う。

さて、早速だけど、以下のようなテーブルを作成する

CREATE TABLE File (
    ID INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    ConfigA TEXT,
    ConfigB TEXT
);

このとき、ConfigA、ConfigB は空文字だったり、NULLだったりする。さて、ここでFile テーブルに対して、Name、ConfigA、ConfigB の組み合わせで定まるファイルのIDを取得するための、再利用可能なパラメータ化されたステートメントを作成したい。

私が最初に書いたSQLがこちら

SELECT ID FROM File WHERE Name=? AND ConfigA=? AND ConfigB=?

結論から言うと、これはConfigA、ConfigB がNULLのエントリを見つけることが出来なかった。SQLにはNULLかどうかを判定するためにIS NULLという演算子があり、それを使わなければならないようだ。したがって、たとえば ConfigA、ConfigB が両方とも空文字かNULLであるようなエントリを見つけるには

SELECT ID FROM File WHERE Name=? AND (ConfigA='' OR ConfigA IS NULL) AND (ConfigB='' OR ConfigB IS NULL);

とする必要がある。この方法の問題点は、ConfigA、ConfigB が空文字の時にだけ、SQLを別途用意しなければならない点。そのロジックをSQLの外部に書くこともできるが、そこを何とかパラメータ化されたステートメントを1つだけ作って解決できないものだろうか。

で、最終的に行き着いたのがこちら

SELECT ID FROM File WHERE Name=? AND ifnull(ConfigA,'')=ifnull(?,'') AND ifnull(ConfigB,'')=ifnull(?,'');

ifnull関数は、第一引数がNULLのときに、第二引数に指定した値を返す関数。この関数を介することにより、NULLと空文字を、SELECTする側で等価に扱えるようになる。

SQL側に少しだけロジックを移動させることで、C/C++等で利用する側のコードが少しシンプルになるという話でした。

おしまい