例えばXという入力によりテーブルAのID=1のレコードと、テーブルBのID=1のレコードが更新されると仮定する。
このXのテストでテーブルCやテーブルD、あるいはテーブルAとテーブルBのID=1以外のレコードが更新されないことの証明は、どうすべきだろうか。
テスト前後のDBダンプを比較すれば証明できるかも知れないが、想像しただけで大変そうな作業に思えるし、また、その方法はRDBごとに異なるであろう。
もっと簡単に証明する方法を記したのが、今回のブログである。
情報システムの大半はRDBを中核にしていると思われる。
ところがRDB設計と言えば、モデリング（論理設計）や性能・サイジング（物理設計）ばかりが重視され、開発を容易にする設計という視点が軽視されているのではないだろうか。
では開発を容易にするRDB設計とは何か。
全テーブル共通の仕様としてレコードのプロパティ情報をRDBのトリガ機能で自動設定するRDB設計である。
レコードのプロパティ情報とは、次の(a)～(e)の情報のことで全テーブル同じ列名で定義する。
(a)更新カウンタ
(b)初期登録日時
(c)最終更新日時
(d)初期登録クライアント情報
(e)最終更新クライアント情報
(a)の更新カウンタは「初期値0でInsertされUpdateの都度1ずつカウントアップされる情報」のことである。バージョン番号と呼ばれることもある。
(b)の初期登録日時は「最初にInsertされた日時」である。
(c)の最終更新日時は「最後にUpdateされた日時」である。最初のInsert直後は(b)の初期登録日時と同じ内容とする。
(d)の初期登録クライアント情報は「最初にInsertしたクライアント情報」である。
(e)の最終更新クライアント情報は「最後にUpdateしたクライアント情報」である。最初のInsert直後は(d)の初期登録クライアント情報と同じ内容とする。
MySQLとPostgreSQLでのトリガ定義方法は以下のPHPプロ！様のサイトにおける私（exceptionCatcher）のコメントを参考にして頂きたい。
http://phppro.jp/qa/2884
【追記2014.05.05(ここから)】
2点補足します。
1点目。MySQLでのトリガ定義では、MySQLのバージョンによっては
mysql_upgradeコマンドによるMySQL本体のパッチが必要になることがあります。
にわかSEの独り言様のブログ記事における私のコメントを参考にしてください。
http://wingse.blog57.fc2.com/blog-entry-216.html
2点目。PostgreSQLでのトリガ定義方法について、PHPプロ！様のサイトで紹介した方法はPostgreSQLのバージョンが8.4系のものです。9.X系では
create trigger Insert用トリガ名 before insert on テーブル名 for each row execute procedure com_insert_trigger();
create trigger Update用トリガ名 before update on テーブル名 for each row execute procedure com_update_trigger();
のようにInsert用トリガとUpdate用トリガを明示的に独立定義する必要があります。
【追記2014.05.05(ここまで)】
他のRDBも、トリガ定義方法は異なるが、同じことが行えるはずだ。
プロパティ情報がトリガで設定されると、なぜ開発が容易になるのか。理由は次の二点である。
(1)楽観ロックが可能となり、SQLが冗長になることを防げる。
(2)非常に便利なテストツールが作成でき、テストの生産性が向上する。
以下に各々の詳細を記す。
(1)の楽観ロックとはSQLのUpdate/Deleteの直前に行うレコードロックを省略できる方式のことだ。
もし「レコードのプロパティ情報が未定義のシステム」で更新系処理を行うとすれば、例えば
(A-1)レコードをSelectで検索する。（この時点ではロックしない。）
(A-2)レコードの内容がUpdate対象と判定される。
(A-3)レコードをロックする。
(A-4)レコードの内容が(A-2)の時点と同じことを確認し、同じであればUpdateする。
のように実装することが多いと思う。
これを「プロパティ情報がトリガで設定されるシステム」では
(B-1)レコードをSelectで検索する。（この時点ではロックしない。）
(B-2)レコードの内容がUpdate対象と判定される。
(B-3)レコードをUpdateする。この時、SQLのwhere句に「更新カウンタ＝(B-2)の時点の更新カウンタ」を指定する。
のように簡略化できる。
もしレコードの内容が(B-2)の時点と異なっていれば、(B-3)での更新結果が0行となる。
Updateのためのロック(A-3)が更新カウンタの使用により、不要となるのだ。
なお「プロパティ情報を定義しているが、トリガを活用せず、アプリケーション側で設定するシステム」では(B-3)のUpdateでプロパティ情報を更新対象列として設定せねばならず、SQLが冗長になってしまう。
アプリケーション側のバグによるプロパティ情報設定漏れにも注意せねばならない。
次に(2)のテストツールだが、これは「冒頭の質問に対する答えであると同時に、冒頭のテストを最も効率よく支援する」ツールである。
具体的に、このテストツールは「テスト実施前情報取得機能」「テスト実施後情報取得機能」「カスタマイズ機能」「ツールが取得した情報をExcelワークブック形式で保存する機能（この機能は説明しない）」を備える。（このツールは「RDBへのアクセス機能とExcel作成機能が備わっているプログラミング言語」であれば、どのようなプログラミング言語でも作成可能である。ただ、「マイクロソフト社の開発ツールでWindowsアプリケーションとして作成する」のが、一番無難と考えている。）
使用方法はシンプルで、テスト実施前に「テスト実施前情報取得機能」を実行し、テスト実施後に「テスト実施後情報取得機能」を実行する。（その後、ツールが作成したExcelファイルを保存するが、ツールがファイルを自動保存するように実装すれば、テスターの操作を更に軽減でき、「保存忘れ」も防げる。）
以下に「テスト実施前情報取得機能」「テスト実施後情報取得機能」「カスタマイズ機能」を説明する。
＜テスト実施前情報取得機能＞
テスト実施前情報取得ボタンを1回クリックするだけでテストツールは(C-1)～(C-3)を実行するものとする。
(C-1)DBサーバのシステム日時を取得し、ツール内で記憶する。
(C-2)全テーブルの全レコードを取得し、Excelシートに出力する。
(C-3)全テーブルの全レコード件数情報を取得し、ツール内で記憶する。
＜テスト実施後情報取得機能＞
テスト実施後情報取得ボタンを1回クリックするだけでテストツールは(D-1)～(D-3)を実行するものとする。
(D-1)全テーブルを対象に「追加された全レコード」を取得し、Excelシートに出力する。
(D-2)全テーブルを対象に「更新された全レコード」を取得し、Excelシートに出力する。
(D-3)全テーブルを対象に「追加・更新・物理削除件数」を求め、Excelシートに出力する。
カスタマイズ機能を説明する前に(C-1)～(D-3)をもう少し具体的に記す。
(C-1)
取得された日時を「テスト開始時刻」として扱う。
(C-2)
Select * from テーブル名
を全テーブルに発行し、その結果をExcelに出力する。
（どのようなテーブルが存在するかは「Information Schema View」や「Data Dictionary View」等と呼ばれるRDB管理情報から取得する。Excelシートの最大行数を超えれば新しいシートに出力する。なおlob型データの扱いは個別に検討すること。参考までにlob型データを格納するテーブルは「情報サイズ」「SHA-1等のハッシュ値」を格納する列も忘れずに定義し「必ずlobデータの情報サイズ・ハッシュ値が設定される」ような設計と実装を行うべきである。）
(C-3)
Select count(*) from テーブル名
を全テーブルに発行し、その結果を「テスト実施前のレコードの全件数」として、ツール内で記憶する。
(D-1)
Select * from テーブル名 where 初期登録日時 > テスト開始時刻
を全テーブルに発行し、その結果をExcelに出力する。
(D-2)
Select * from テーブル名 where 最終更新日時 > テスト開始時刻 and 初期登録日時 < テスト開始時刻
を全テーブルに発行し、その結果をExcelに出力する。
(D-3)
「テストで追加されたレコードの全件数」を求めるため
Select count(*) from テーブル名 where 初期登録日時 > テスト開始時刻
「テストで更新されたレコードの全件数」を求めるため
Select count(*) from テーブル名 where 最終更新日時 > テスト開始時刻 and 初期登録日時 < テスト開始時刻
「テストで更新されなかったレコードの全件数」を求めるため
Select count(*) from テーブル名 where 最終更新日時 < テスト開始時刻
を全テーブルに発行する。
物理削除への対応は後述するが、物理削除された件数は、次の式で算出できる。
「テストで物理削除されたレコードの全件数」
＝ 「テスト実施前のレコードの全件数」(C-3)
－ （「テストで更新されたレコードの全件数」 ＋ 「テストで更新されなかったレコードの全件数」）
（物理削除とは「SQLのDeleteによる削除」を意味する。対語は「削除フラグ等をSQLのUpdateで設定する」論理削除である。）
＜カスタマイズ機能＞
上記はテストツールの概略イメージである。このテストツールがあれば、テスト時のRDB証跡の取り忘れ・取り間違いが確実に防げる。（もし冒頭のXを「更新されたことだけを確認するテストとして実施する」としても、テスターはテスト前後のテーブルAとテーブルBを意識して取得しなくてはならない。仕様が複雑になればテスターの手順は複雑になり、証跡の取り忘れ・取り間違いも発生しやすくなる。）
しかも上記テストツールは結果をExcelで取得するので、検証も容易になる。テーブル数が多く、複雑な仕様のシステムであればあるほど、このテストツールは必ず役に立つと確信している。
ただ、上記だけでは不完全であり、実際のシステムでは次のような問題が考えられる。
・上記では物理削除の検証が不可能である。
・大量のレコードを持つマスタテーブルやログテーブル等があり、(C-2)での全件取得を見直したいテーブルがある。
そのため、テストツールは「カスタマイズ機能」も備える。「カスタマイズ機能」は、例えばINIファイル等で定義し、以下のような事柄を実現できるようにする。なお、以下の説明は、複数のサブシステムに分かれたシステムの、特定のサブシステムのテストでテストツールを利用する前提で記す。（特定のサブシステムを「自サブシステム」、その他のサブシステムを「他サブシステム」と記す。）
・物理削除の検証用に、任意のSQLを「テスト実施後情報取得機能」内で実行できるようにする。（物理削除されるレコードが Select count(*) from 対象テーブル where col='xxx' で特定できると仮定し、そのSQLを実行できるようにする。このSQLの実行結果と(D-3)の「テストで物理削除されたレコードの全件数」、及びテスト前の状態としての(C-2)の確認により、検証を行う。）
・他サブシステムに「大量のレコードを持つマスタテーブル」が存在するが、自サブシステムでは参照も更新も一切行わないので、(C-2)での取得を行わないようにする。（このマスタテーブルが「更新されなかったこと」の検証は(D-3)の「テストで追加されたレコードの全件数」「テストで更新されたレコードの全件数」「テストで物理削除されたレコードの全件数」が全てゼロであるかどうかで可能である。）
・自サブシステムに「大量のレコードを持つマスタテーブル」が存在するが、テストで更新されるレコードは少数である。(C-2)で取得される情報を特定の条件に合致したものに限定しても検証可能なので、SQLのwhere句に相当する条件を指定できるようにする。（このようなケースでは(C-2)、(D-2)及び(D-3)の「テストで物理削除されたレコードの全件数」の確認により、検証可能である。）
・(C-2)でSQLのorder by句に相当する条件を指定できるようにする。
・大量のレコードが登録されるログテーブルが存在するが、ログテーブルの登録機能は既に確認済みの共通機能のため、ツールの処理対象から外せるようにする。
また、このテストツールはRDBの種類への依存が少ないため、一つのRDB用のものを作成すれば、別のRDB用にも容易に対応させられる。
（RDBの種類に依存するのは「RDB管理情報からテーブル一覧情報を取得する方法」「日付・時刻用の関数の扱い方」ぐらいであろう。）
「レコードのプロパティ情報をRDBのトリガ機能で設定する」だけで、ここまでのことが可能となる。
以上がテストツールの概要である。優秀なプログラマなら1週間もあれば余裕で作成できるはずだ。
最後に、あまりにも常識的すぎて記すべきか悩んだが、「開発を容易にする」という視点のブログなので記す。
「開発を容易にするRDB設計」には、もう一つの鉄則がある。
それは一つのテーブルの列数は、プロパティ情報も含め、250を上限とすべきである。
これは開発に携わる人が「テーブル内容をExcelで検証する」ことを前提にした設計である。
なぜこのようなことを記すのかというと、以前、とんでもないシステムを目の当たりにした経験があるからだ。
昔、ある業界向けパッケージソフトの導入支援プロジェクトに携わったことがある。
ところがパッケージソフトにおいて最重要となるテーブルの列数が300を超えていた。
案の定、そのパッケージソフトはバグだらけで、プロジェクトメンバーもオーバーワークが当然のようになっていた。
最近のRDBには「1000以上の列数」を定義できるものも珍しくないが、絶対に列数を250を超えて定義すべきでない。
【関連する記事】
