exceptionCatcherがthrowするIT技術情報

復号可能な暗号化ライブラリPDEL
http://pdel.vpsdemo.biz/
に関する質問・バグ報告・ご意見や、
PDELオフィシャルサイトの誤記等ございましたら、
以下にコメントをお願いします。

今年最初のブログは開発を容易にするRDB設計をテーマに記すが、その前に質問を一つ。
例えば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を超えて定義すべきでない。

昨年、日本PHPユーザ会様の掲示板で、オープンソースソフトウェアのライセンスについてコメントしたことがあった。

http://bbs.php.gr.jp/topic-250.html

その際、経済産業省のサイトで公開されていた資料を参考にさせてもらった。
ところが先週、別件でライセンス関連の調べものをしようと経産省のサイトを確認したところ、当時の公開ページそのものが削除されていた。
経産省のサイトで当時の資料を検索したが見つけられなかったので、昔、私が経産省のサイトからダウンロードした2種類のPDFを、ここで公開させて頂く。

【資料A】オープンソースソフトウエアの利用状況調査／導入検討ガイドラインの公表について(64,614Byte)(*1)
【資料B】オープンソース・ソフトウエアの現状と今後の課題について(1,365,072Byte)(*2)

【資料B】は、日本PHPユーザ会様の掲示板で私が「2番目のPDF資料」としていたものである。

上記PDFは、税金で作成された、極めて有益な資料であるにもかかわらず、削除されたと思われることが残念でならない。
もし経産省に「公開後、一定期間経過したページや資料は削除する」みたいなルールがあるのだとすれば、それは残念な運用ルールである。

このように、残念なルールで運用されているWebサイト・システムや、仕様そのものが残念なWebサイト・システム等を目にすることは少なくない。
久しぶりのブログ更新では私が身近に感じている「残念なシステム・Webサイト」を何点か語ってみたい。
個々の事項に関連性はないが、多くのシステム開発の現場で有益なものだと思うので、より良いシステム開発の一助になれば幸である。

1.桁区切りの無い取引番号

Webサイトでショッピングをしたり、あるいは業務系システムで何らかの情報を登録したりすると、
「今回のお取引番号は1234567890です」
のように「取引をユニークにする番号」が表示されることが多い。
しかし、このような長ったらしい情報をそのまま表示するよりも
1234-5678-90
のように桁を区切って表示すべきである。
特に取引番号の体系に意味を持たせている場合、その情報で区切って表示すべきなのはいうまでもない。
例えば
2310140035
という情報が、
2桁の支店番号　＋　MMDD形式の日付　＋　4桁の取引番号
という意味を持っているのであれば、
23-1014-0035
と表示すべきである。

2.使いやすい画面

システムの開発ツール類等が非常に増え、一昔前では想像もできなかったような「見映えの良い画面」を作成することが可能となった。
しかし「見映えの良い画面」が「使いやすい画面」とは限らない。

【メニュー】
メニュー項目には、サブメニューを「展開するもの」「展開しないもの」がある。それらが明確にわかるほうが好ましい。開発ツールによっては、そういう機能を備えている場合もあるが、そうでない場合、メニュー項目の先頭に例えば＋（プラス記号）を付与する等で何らかの工夫を行うべきだ。また、メニュー選択後は、どのメニュー項目が選択されたかがわかるようにすべきだ。

【ダイアログ画面表示方法】
ダイアログ画面を表示するメニュー項目やボタンには「...」（ドットを三つ連続させたもの）を付与したほうが好ましい。

【画面ID】
画面をユニークにするIDを付与し、画面のどこかに表示すべきである。（HTMLのDIVタグで表示されるエリアだけの画面遷移を行うような場合も、DIVエリアにIDを表示すべきである。）

【メッセージID】
メッセージをユニークにするIDを付与し、メッセージ出力時に表示すべきである。

3.金融関連サイトの3か月ルール

銀行やクレジットカード会社のなかには、個人向けWebサービスを提供しているところが少なくない。
「銀行であれば残高の確認や別口座への振り込み」
「クレジットカード会社であれば利用履歴の確認」等が行えて便利である。
また、銀行での入出金履歴や、クレジットカードの利用履歴をCSVでダウンロードできるサイトも多い。
これはこれで便利なのだが、なぜか「CSVでダウンロードできる期間は過去3ヶ月」の取引に限定されることが多い。
なぜ3ヶ月なのだろうか。
自営業者は、確定申告を2月中旬～3月中旬に行わなくてはならない。
パソコンや会計ソフトの普及で、税理士に頼まずに確定申告をする人も多いと思うが、その際、
「確定申告を行う2月中旬～3月中旬」に「前年の1月～12月の履歴」がダウンロードできれば、非常に便利である。
（クレジットカードに関しては、前々年の10月頃からの履歴がダウンロードできれば好ましい。前々年の10月頃以降の取引が決済されるのは、前年になることがあるからだ。BSでは「買掛金」や「未払い金」等で計上する負債になる。）
ただ漠然と「3ヶ月もあれば十分だろう」的な安易な発想は改めるべきである。

4.WebサイトのURL

WebサイトのURLは、ブラウザのブックマーク機能でマークされることを前提に、極力変更を避けるべきである。
非常に残念なURLを紹介する。

・JR常磐線北松戸駅の上りの時刻表（平日）
http://www.jreast-timetable.jp/1110/timetable/tt0588/0588020.html
・JR常磐線北松戸駅の上りの時刻表（休日）
http://www.jreast-timetable.jp/1110/timetable/tt0588/0588021.html

上記は、JR常磐線北松戸駅の上りの時刻表である。
何が残念なのかというと、URL中の1110が「YYMM形式の年月」なのである。
従って、来月以降、1110の箇所を変更しなければアクセスできないのである。
平日と休日の区分も、平日にアクセスすれば平日の時刻表、休日にアクセスすれば休日の時刻表をデフォルト表示させれば、より使い勝手が良くなる。

5.問い合わせフォーム

企業や官公庁等のWebサイトには、問い合わせ機能を備えたサイトも少なくない。
問い合わせ時にメールアドレスを指定することがほとんどだが、
問い合わせた内容を「問い合わせ時に入力したメールアドレス」に送信されないサイトが少なくない。
利用者に問い合わせた内容を確認させる意味で、問い合わせた内容を「問い合わせ時に入力したメールアドレス」に送信すべきである。

6.PDF

最初に記した経産省の【資料B】だが、「PDFの物理的なページ番号」と「資料内容の論理的なページ番号」が異なっている。
資料のPDF化を念頭において、「PDFの物理的なページ番号」と「資料内容の論理的なページ番号」は合わせるべきである。
PDFの話なのでついでに。官公庁等が公開するPDFだが、その元となる資料の大半はワード・エクセル等のオフィス系ソフトで作成されていると考えられる。なぜ元となったオフィス系ソフトの文書ファイルを公開しないのだろうか。特に統計情報は、アナリスト等が別途オフィス系ソフトにコピーして解析していると思われる。つまり非常に無駄な作業が、全国規模で行われていると思われるのだ。元文書の改ざんされたものが出回ることを心配するのであれば、ハッシュ値(SHA1／MD5)を公開すれば良いだけのことである。

(*1)
SHA1=A8485AB8EB0264ECE7E7E6F0DFC153BCCA1617A8
MD5=3036E52BAA248A1FD263B712AE0A41B9

(*2)
SHA1=8582B77A0095646F8DEA1D377E908BC9EECA9542
MD5=67E5EA4EA8E07EBEB3B73D8202E4A614

※本日、はてなさんのブログから引っ越してきました。はてなさんのブログは適当な時期に閉鎖予定です。

※この記事も含め、これより過去の記事は、はてなブログから引っ越ししてきたものです。上記で用いた図ですが、原寸大のものは2回間隔を置いてクリックして頂くと表示されます。