SQLでアサーションしてみる



動機

PostgreSQLを使って色々な集計作業をしているのですが、クエリの誤りや元データの異常で手戻りが多いです。
途中途中でチェックを行えば良いのですが、場当たり的にテストスクリプトを書いたりしていると

  • チェックの抜けが起こりやすい
  • テストスクリプトと実際のスクリプトが混ざると管理しづらくなる

という問題があり、プログラミングにおける単体テストのような進め方が出来ないものかと考えておりました。

要件

考えていたのはPostgreSQL 9.1上で以下を満たすソリューションです。

  1. 実際のスクリプトとテストスクリプトを混ぜても良いようにしたい
  2. テストに失敗すると処理が止まるようにしたい
  3. いつ、どのようなテストを実行したか記録が残ると良い

解決法

テストスクリプトの結果を格納するテーブルを作成し、結果が期待と異なる場合にエラーとなるようにしてみます。

create table assert (
 title varchar,
 expected varchar,
 actual varchar,
 asserted_at timestamp default statement_timestamp(),
 constraint "assertEquals" check (expected = actual)
);

これにより、

insert into assert values ('test', 1, 1);

は登録に成功し、

insert into assert values ('test', 1, 2);

は「ERROR: new row for relation "assert" violates check constraint "assertEquals"」が返るようになります。

実際の使い方の例を示します。

テストパターン1:件数比較

with master_count as (
 select count(1) as c from master
), test_count as (
 select count(1) as c from master_info
)
insert into assert
select '"master_info"の件数が"master"と一致'
, master_count.c, test_count.c from master_count, test_count;
select * from assert order by asserted_at desc limit 1; -- 最後の実行結果を出力

with句を使わなくても書けるのですが、試しに。

テストパターン2:異常レコード検出

insert into assert
select '"登録日" < "変更日"'
, true, not exists (select 1
  from "登録情報" t1
  join "変更履歴" t2 using ("customer_id")
 where t1."登録日" > t2."変更日"
 limit 1);
select * from assert order by asserted_at desc limit 1;

こちらは別のテーブルの内容と比較しておかしいレコードを探すというもので、多分単純なチェック制約では実現出来なかったように思います。

ここまで面倒なことをしなくてもいいのかも知れませんが、何段階にも連なるデータ加工を、十数時間も掛けて実行して最後に問題が見つかるよりは良いのだろうと思っています。
これまで、アプリケーションからDBMSを使うことが多く、DBMS層での検査はあまり重要だと思っていなかったのですが、ユニーク制約や外部キー制約、チェック制約を掛けるのも大事ですね。


This entry was posted in データマイニング, 技術. Bookmark the permalink.