| [ホーム] -> [Aache + PHP + PostgreSQL 実験室] |
それでは、PostgreSQL、PHP、Apache を使って、個人情報検索システムを作ってみましょう。名前は大層なものですが、個人データの登録、変更、照会を、Web ページを使って行おうと言うものです。必要なのは、登録・変更のページと、照会のページです。この手のプログラムを実現するには、何通りか方法がありますが、今回は非常に単純な方法をとりたいと思います。ページのフローは次のようにしたいと思います。
検索条件入力
|
+------+------+
| |
| 検索結果
| |
| +-----------+
| | |
登録入力 変更入力
| |
登録結果表示 変更結果表示
良く考えれば、登録入力ページと、変更入力ページを同じファイルで出来そうですが、少々複雑になりそうなので、やめにしました。したがって、合計７つのファイル構成となります。一応完成品を置いておくので、どんなことができるかを先に確認したい方は、試してみてください。
検索のページから、検索条件を入力して、「検索」を行うと、その結果として「検索結果」が表示されます。結果のデータには、それを変更するための「変更入力」ページへのリンクが張ってあります。「変更入力」ページへ移るときには、どのデータに対して変更をするかというデータを引き渡し、初期値として、すでにデータベースに登録してあるデータを表示します。その後、変更を行い、「更新」を行うと、「変更結果表示」ページが出て、そこで実際に変更できたかを確認できます。
「登録入力」では、新規にデータの登録を行います。そこにデータを入れ「登録」をすることにより、次の「登録結果表示」で登録結果を表示します。
大まかな流れは、こんなところです。すでに、この手の処理を行ったことがある方は、非常に簡単だと思います。もっと、色々な機能を盛り込んで、複雑にしたい方は、お好きなように考えてください。
それから、テーブルにアクセスするユーザとして、「www」というユーザを用意します。テーブルの作成自体は、自分が普段 PostgreSQL を利用するときに使用しているユーザアカウントで構いませんが、PHP からアクセスするときは、www というユーザで接続することにします。これは、万一 PHP にバグがあっても、被害が大きくならないようにするためです。（なるべくバグが無いように作っているつもりですが）
従って、PostgreSQL には、事前に createuser コマンドを使って、www というユーザ（別の名前でもいいですけど）を追加しておいてください。
次に決めなければならないのは、データベース設計（テーブル設計）です。今回は、変更があるために、どのデータが変更対象であるかを判断するために、何かユニーク（一意）なカラムが必要になります。とりあえず、次のようなテーブルを作成することにしました。
inet_profile テーブル
code integer
name varchar(30)
email varchar(80)
pseudonym varchar(30)
job smallint
profile varchar(80)
editdate timestamp
edituser name
code は、ユニークな値を格納しておくカラムです。これを、内部で自動採番できるように、シーケンスという機能を使って実現します。適当なシーケンス名（今回は、seq_inet_profile_code）を付け、SQL 文で、nextval('シーケンス名') とすることで、新しい番号を持ってくることができます。シーケンスとは、データベース内にある変数の様なもので、内部で値を保持しておき、nextval 関数でシーケンス名にアクセスすることにより、その値をインクリメントし、インクリメントされた値を返すのです。Oracle のそれは仮想表ですが、PostgreSQL は、関数として実装してあります。そのためと言うわけではないでしょうが、カラムのデフォルト値として、シーケンスを指定することができます。
name には、ユーザ名を、job には職種、email には e-mail アドレス、pseudonym は ペンネームを入れてもらい、profile には、何か好きなコメントを入れることができるようにしておきます。editdate には、もしものために、そのデータを追加・変更した日時を、edituser には、ユーザ名を格納しておきます。edituser のデータ型である name とは、ユーザ名を保存する特別なデータ型です。
それと、プライマリキーを指定しておきましょう。プライマリキーを指定すると（複数のカラムを組み合わせることもできます）、プライマリーインデックスが作成されます。これは機能的にはユニークインデックスと同じで、なおかつ、暗黙的にnot null 制約が付くので、省略できずなおかつ重複した値を持つことができなくなります。つまり、プライマリキーとは、このキーの値を指定すると、テーブルから必ず一意の行が戻されることを PostgreSQL に宣言することになります（REFERENCES 制約（後述）などはプライマリキーが無いと使えないのです）。このテーブルのプライマリキーは、当然 code ですので、そのように設定します。
code integer PRIMARY KEY
job を数値管理するために、職種マスタを作る必要があります。普通は男と女のみですが、今回は色々な人が登録するのを考えて、いくつか用意しておきます。
inet_job テーブル
code smallint PRIMARY KEY
name varchar(10)
editdate timestamp
edituser name
当然、code がこのテーブルのプライマリキーになります。各コードは次のようにすることにしましょう。
11 会社員 21 自営業 31 教職 41 学生 99 無職 0 その他 -1 秘密
inet_profile テーブルの job カラムには、inet_job テーブルに登録されている値しか入力できないようにするために、REFERENCES 制約を定義します。これをするには、inet_profile テーブルの宣言で、次のようにします。
job smallint REFERENCES inet_job(code),
REFERENCES 制約は、チェックする対象のカラム（ここでは inet_job テーブルの code）が、プライマリキーでないと作成できません。
データを insert するときに、inet_profile の code の入力を簡単にするために、DEFAULT 句を指定しておきましょう。
code integer PRIMARY KEY DEFAULT nextval('seq_inet_profile_code')
こうしておくことで、insert into inet_profile (name) values ('daresore'); などと code を省略して insert した場合、DEFAULT で指定した nextval('seq_inet_profile_code') が実行されてその値がセットされます。
また、editdate, edituser なども省略できると便利ですよね。ただし、このカラムの場合、insert だけではなく、update の時も更新する必要があります。DEFAULT は insert 時にしか効果がないので、こういう場合は、トリガを併用します。
トリガとは、テーブルのデータを追加・更新・削除などを行ったときに自動的に実行されるプロシージャ（関数）です。まずはじめに実行するプロシージャ（トリガプロシージャと言います）を作成し、次にテーブルが更新されたときにそのプロシージャを実行するように適宜します。まずはトリガプロシージャを見てみましょう。
CREATE FUNCTION update_editdata() RETURNS OPAQUE AS '
BEGIN
NEW.editdate := current_timestamp;
NEW.edituser := current_user;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
ここでは update_editdata() という名前のプロシージャを作成しています。このプロシージャは、plpgsql（PL/pgSQL）という言語で書かれています。これ以外にも C 言語や Perl で作ったりできます。ただ、これらの言語はデフォルトでは利用することができません。利用するためには、createlang というコマンドを使って、データベースに言語を登録しないといけません。コマンドラインから次のようにしてください。
> createlang plpgsql [データベース名]
トリガプロシージャの特徴は、引数は無く OPAQUE という型の戻り値を持つことです。NEW というのは、更新後のテーブルの行の様なもので、NEW.カラム名 で各カラムにアクセスできます。逆に更新前のテーブルのデータは、OLD でアクセスできます。ここでは更新後の NEW.editdate, NEW.edituser に、current_timestamp, current_user の値を設定しています。最後に RETURN NEW として、更新後のデータを返しています。
ちなみに、よく見れば分かるように、プロシージャの実行部分は BEGIN - END に囲まれた部分ですが、ここはクォーテーション「'」で囲まれているので、中でクォーテーションを使う場合は二つ並べて「''」としなければならないので注意してください。もっと詳しく知りたい場合は、PostgreSQL のプログラマーズガイドを参照してください。
このトリガプロシージャを、テーブルと関連づけなければいけません。
CREATE TRIGGER trg_inet_profile_edit BEFORE INSERT OR UPDATE
ON inet_profile FOR EACH ROW EXECUTE PROCEDURE update_editdata();
とすることで、inet_profile にデータが insert, update されたときに update_editdata() を実行する trg_inet_profile_edit というトリガーが作成されました。
これでテーブル設計は終わりです。まあ、かなりいい加減なので、みなさんは、自分の好きな項目を足して使ってください。今回は、色々な人が追加し、参照するので、電話番号や、住所などのカラムを設けませんでした。もし、個人的に使うのならば、当然こういった項目も必要になるでしょう。
最後に、今回使うテーブル類を作成するための SQL 文を載せておきます。この SQL 文内では、２つのテーブルと、シーケンス、インデックスを作成し、権限の変更と、デフォルトデータを追加するようになっています。文字コードは 日本語 EUC ですので、問題がなければ、psql などでそのまま流してください。
もし、www というユーザを使用しなかったり、別の名前にしている場合は、SQL 文中の grant 文を変更してから流してくださいね。
> psql -f meibo.sql [データベース名]
始める前に、Apache の設定を載せておきます。これは私の趣味を反映した設定ですが、基本方針としては、「漢字コードは EUC-JP を使用」「余計な機能は Off」です。
DirectoryIndex index.php
<Files "*.phi">
Deny from all
</Files>
AddType "application/x-httpd-php; charset=EUC-JP" .php
php_flag register_globals Off
php_flag magic_quotes_gpc Off
php_flag magic_quotes_runtime Off
php_flag magic_quotes_sybase Off
php_value default_charset EUC-JP
php_flag output_buffering Off
php_value mbstring.http_output EUC-JP
php_value mbstring.detect_order ASCII,JIS,EUC-JP,SJIS
php_value mbstring.http_input ASCII,JIS,EUC-JP,SJIS
php_value mbstring.internal_encoding EUC-JP
php_value mbstring.substitute_character long
ページを作る前に、全てのページで共通に使えそうな機能を、「共通ファイル」として用意します。このファイルを、PHP にある、include 関数を用いて、すべてのファイルから利用できるようにします。include 関数を用いると、その場所に、他のファイルを取り込むことが出来ます。このファイルでは、各初期設定（エラー表示しないとか、接続先のホスト名や、データベース名のセット）をするのと、ユーザ定義関数の定義をします。今回、定義する関数は次の６種類です。
select した結果をテーブルで表示する
１番目は、処理中にエラーが起きた場合に、処理を中断するときに使います。単に exit や die 関数を呼ぶと、その場で処理が終了し、</body></html> タグが閉じないための対処です。
２は、画面から入力した文字列を元に SQL 文を作るとき、入力文字列中に特殊な文字が入っていた場合、SQL 文が崩れてしまうのを防ぐ必要があります。また、数値しか入力できない項目に数字以外の文字が入っていた場合にエラーメッセージを表示させたりすると便利です。今回は、文字列、like 演算子用の文字列、数字用の３つを作成します。
３と４は、PostgreSQL との処理中にエラーが起きた場合、それの、エラー処理を加えたものです。この関数を使えば、個々にエラー処理を行わずに済みます。
５は、単純に、良く使う機能をまとめたものです。汎用的なので、他のプログラムでも使えそうなので分けてみました。
６は、名前の必須チェックをブラウザ側でもするための JavaScript です。こういう場合は普通は <script language="javascript" src="meibo.js"> とかするのですが、IE でファイルを分けた場合、Shift_JIS に統一しないと、かなり面倒なことが起きるので、PHP でインクルードしてしまっています。
まずはトップページの検索部分ですが、殆んどの項目の検索条件を入力できるようにして、それが部分文字列か、そうではないかをチェックボックスによって決められるようにします。つまり、like 検索か「=」かという意味ですね。余裕があれば、正規表現による検索をサポートすると面白いでしょう。「登録入力」ページは、code, editdate, edituser 以外を入力できるようにします。
「検索結果」ページを作成しましょう。PHP と、PostgreSQL との連携の仕方は、既に説明してあるので、具体的な説明は避けます。この、「検索結果」ページの注意点は、このページから「変更入力」ページへのリンクを張る必要があることです。リンクは、次のような形式にします。
<a href="変更入力ページ.php?q_code=CODE">氏名</a>
つまり、変更入力ページ名の後ろに「?」をつけて、その後ろに「q_code=コード」と書きます。「コード」の部分には、自動採番したコードを書きます。これによって、変更入力ページの PHP スクリプト内で、$_GET['q_code'] 変数を参照することにより、変更すべきデータが判別できます。
「変更入力」ページですが、やはり、現在データベースに登録されているデータを、初期値として表示すると便利です。ユーザは、変更したい項目を変更して、更新ボタンを押します。「変更結果表示」ページで、その内容を変更し、その結果を表示します。
更新するには、SQL 文のupdate 文を使いますが、pg_query 関数を使用するのは、全く同じです。select 文とは違い、結果セットを返さないので、pg_fetch_row 関数などは利用できません。pg_query関数は、成功した場合は TRUE を返し、pg_affected_rows 関数を利用すると、更新した行数が返ります（1 のはずですが・・・）。
新規登録の場合は「登録入力」ページより、「登録結果表示」ページを呼びますが、この時は、SQL 文の insert 文を使います。insert 文も、同じように pg_query 関数を使って実行します。この二つのページは、「変更入力」ページと「変更結果表示」ページを元にして作れば簡単でしょう。
サンプルを元にいくつかの注意すべきポイントを説明したいと思います。まず一番気にしなければいけないのは、ユーザが入力する値は予想が付かない という点です。データベースを扱うプログラムでは、ユーザが入力した値を SQL の一部として使いますが、そのときに、絶対に入力された値をそのまま使ってはいけません。
例えば、名前を入力するテキストボックスからの値を元に、PHP 内で SQL の条件式を "name = '$_GET[name]'" と組み立ててはいけません。もし、ユーザにa'b と入力されたらどうなるでしょう。name = 'a'b' となり、SQL の構文エラーになります。文字列中では、シングルクォーテーション「'」とバックスラッシュ「\」が特殊な文字として扱われます。PHP には、これを回避するために専用の pg_escape_string という関すが用意されています。サンプル中では、global.phi 中の escape_string 関数で行っています。
SQL の like 演算子を利用するときは、もう少し注意が必要です。like 演算子では、パーセント「%」と、アンダーバー「_」も特殊な文字です。そして、バックスラッシュ「\」も、普通の文字列の時とは意味がちょっと違ってきます。
like 演算子で、パーセントなどを普通の文字列として使うには、その前にバックスラッシュを置く必要があります。しかし、バックスラッシュは、SQL の文字列都市のエスケープの意味を持っているので、一つだけだと、like 演算子にはわたりません。従って、二つ重ねる必要があります。さらに、PHP の文字列としてもバックスラッシュは特殊な文字なので、さらに重ねる必要があります。したがって、PHP のソース上だと、パーセントの前にバックスラッシュが４つ必要になります。Oracle などとは、ちょっと違い、これは、like 演算子のパーサと、SQL 文全体のパーサが別の実装になっていることが原因かと思います。
a%z という文字として認識したい場合：
like 演算子： a\%z
SQL 文 ： 'a\\%z'
PHPの文字列： "'a\\\\%z'"
もしくは： '\'a\\%z\''
この変換は、サンプル中では、global.phi 中の escape_like 関数で行っています。
もう一つ気を付ける点では、すでに説明しましたが、データベースの値をそのまま出力してはいけないと言うことです。文字列型のカラムから出力するデータは、必ず htmlspecialchars 関数を通して出力しましょう。誰かがデータベースに HTML のタグの付いた文字を登録し、それを表示したら・・・。画面が崩れるとかならまだ良いのですが、<script> タグや <object> タグとかを埋め込まれたら大変です。JavaScript から Cookie を読んでどこかのサイトに送信することなど簡単です。これはセキュリティ上大変問題です。
今回は数が少なかったので、出力するところに、<?= htmlspecialchars($rowdata[name]) ?> とか毎回指定しましたが、場合によっては、次のようにすべてのデータを最初に変換してしまってもいいと思います。
$rowdata = pg_fetch_array($rs, $i, PGSQL_ASSOC)
foreach ($rowdata as $key => $value) {
$outdata[$key] = htmlspecialchars($value);
}
それと、同じ理由ですが、ユーザが入力した値をそのまま表示するときも注意が必要です。ありがちなものとして、「echo("あなたが入力した $_GET[name] は存在しません");」といったコードです。これも同様に JavaScript とか埋め込まれた場合危険です（クロスサイトスクリプティングとかいう問題です）。
さて、これで一通り出来上がったでしょう。実際のテストに入ります。もちろん、作りながら動作テストをしてみたと思うので、一通り動くはずですが、必ずしないといけないテストがあります。もちろん、細かくあげれば沢山ありますが、大きく、次の点です。
極端なデータの入力とは、色々意味がありますが、「入力できる桁数に目一杯入力する」「一つも入力しない」などがあります。テキストボックスに目一杯入力してみてください。エラーは起きませんでしたか？ もしかしたら、テキストボックスの桁数が間違っていて、SQL エラーが起きるかも知れません。あるいは、キーボードから入力できる限りの特殊な文字を入力してみてください。
次の、検索条件とは、今回は検索条件によって SQL 文が変化します。それがうまく動くかというテストです。全ての検索項目に対し、項目一つにだけ検索条件を入力して検査をしたり、部分文字列のチェックを付けたり、外したりしてテストしてみてください。
もちろん、上であげた以外でも、自分の思い付く限りのテストを行ってください。そうしたら、次の重要なテストに移ります。他のプログラミングの例に漏れず、この手の WWW システムでも、テストが重要になります。色々なテスト手法があり、私は、それら全てを知っているわけではありませんし、どれが優れているかも分りません。が、経験的に言って、次のことが言えます。
他人にテストをしてもらう
いや、これは、作成した人が楽をしようと言うわけではありません。もちろん、作成した人も、それなりのテストを行わなければなりません。ですが、経験的に言って、作成した人は、バグを見つけにくいのです。と言うのは、作成した人は、どうすれば動くか知っているからです。したがって、その動く手順しか試さない傾向があります。その点、テストを行ってもらう人は、試行錯誤で、色々試してくれます。作り手が思い付かなかったようなことをして、バグを見つけ出したりします。知り合いに、手の空いている時間を見計らって頼みましょう。もちろん、自分である程度検証してからですよ。あまりに単純なバグが多いと、嫌気がさして、ろくにテストしてくれないかも知れませんから。
今回、このようなシステムを作ってみてどうだったでしょうか。始めての人にとっては、難しかったでしょうか？ 他の言語でも、この手のシステムを作ったことのある人は、結構簡単に思えたのではないでしょうか。このシステムでは、検索・登録・変更の機能がありました。殆んどのシステムで、この機能は必要となってきます。ものによっては、集計・分析の機能も必要になってきますが、基本的に、これらは検索の発展型です。したがって、今回作ったものを理解していれば、あとはアイディアしだいで、すばらしいものが作れるのです。
もちろん、このシステムもそれほどスマートなわけではありません。改善点は多くあります。例えば、「登録入力」ページと「変更入力」ページ、および、それらの結果ページが分れていました。これらを同じページを使って処理することも出来るでしょう。テーブルの項目にも不満があったことでしょう。でも、そこら辺は、各自で変更するなりしていただければ、と思います。説明のために用意したシステムなので、最大公約数的なものより、説明のしやすい例を使用したかったのです。ここに示した例が、みなさんの考えているすばらしいシステムの踏み台になればと思っています。
サンプルは、やたらうるさく HTML タグを指定してますが、これは単に Another HTML-lint を通るようにしてあるだけです。個人的にはここまで神経質になる必要はないと思いますが・・・。不明な点が一つ。<select> タグに accesskey を設定するのって、どうやるのでしょう。<label> と組み合わせてもうまくいかないですね（Mozilla 1.0）。知っている人がいたら教えてください（こらこら）。