PerlでMySQL操作CGIを作ろう：快速MySQLでデータベースアプリ！（4）（2/4 ページ）

[鶴長鎮一，MySQLユーザ会]

PerlによるDBプログラミング

サンプルデータベースの準備

　これから紹介するサンプルスクリプトで使用するため、以下のようなデータベースとテーブルを作成します。

• “ATMARKIT”データベース中に“list”テーブルを作成
• テーブルの定義

フィールド   |  データ型    |   備考
id          |  int        |  主キー・オートインクリメント・
空白不可
name        |  char(20)   |  空白不可
memo        |  char(240)  |

• ユーザー名：test、パスワード：test2001で参照可能に設定

　順を追っておさらいしましょう。まずデータベースを作成します。クリエイト権限を持っているのはデフォルトではスーパーユーザーだけになります。

# mysqladmin create ATMARKIT

　権限の問題でデータベースが作成されなかったとしてもエラーは表示されないので、ちゃんと作成されているかを“mysqlshow”コマンドで確認しておきましょう。確認できたところでテーブルの作成です。

# mysql ATMARKIT
mysql> CREATE TABLE list (
    ->   id int(10) NOT NULL auto_increment,
    ->   name char(20) DEFAULT '' NOT NULL,
    ->   memo char(240) DEFAULT '',
    ->   PRIMARY KEY (id),
    ->   KEY k_name (name)
    -> );
Query OK, 0 rows affected (0.00 sec)

　いつまでもスーパーユーザーで作業をするのは危険です。ユーザー“test”にだけ、今回作成したデータベースを操作できるように権限を設定します。

mysql> GRANT Select,Update,Insert,Delete ON ATMARKIT.* TO 'test' IDENTIFIED BY 'test2001';
Query OK, 0 rows affected (0.00 sec)

　これで、ユーザー名とパスワードさえ合っていれば、どこのホストからでもATMARKITデータベースを操作できます。しかし、ユーザー名とパスワードが合っていても、操作が許されないホストが1つだけあります。それはなんとlocalhost自身です。

　これはMySQLのセキュリティを管理している特権データベース“mysql”中の“user”テーブル中に下記の項目が含まれるためです。

Host        | User
----------------------------------
localhost   | (なし)

　この場合、今回設定した

Host        | User
----------------------------------
%           | test

よりも先に評価されるため、“Access denied”となります。そこで、ローカルマシンからのアクセスを許可するように権限を追加します。

mysql> GRANT Select,Update,Insert,Delete ON ATMARKIT.* TO test@localhost IDENTIFIED BY 'test2001';
Query OK, 0 rows affected (0.00 sec)

　これでサンプルデータベースの作成は終了です。上記のように1行ずつ打ち込んでも構いませんが、ファイルに一連のSQL文を記述し、

# mysql ATMARKIT < ファイル名

で一気に流し込むこともできます（第3回「外部ファイルによるバッチ処理」参照）。そちらの方がミスもなく、確実に作業が進められますね。

DB接続・登録・切断のコーディング

　準備ができたところで、データベース操作ができることを確認するために簡単なスクリプトを動かしてみましょう。まず、下記のPerlスクリプトを用意します。Emacsなどの適当なエディタで編集します。

1  use DBI;
2
3  $user = 'test';
4  $passwd = 'test2001';
5  $db = DBI->connect('DBI:mysql:ATMARKIT:localhost', $user, $passwd);
6  $sth = $db->prepare("INSERT INTO list VALUES (1,'1st','memo 1st')");
7  $sth->execute;
8  $sth->finish;
9  $db->disconnect;

リスト1　接続・登録・切断を行うサンプルスクリプト（sample1.pl）。［ココ］をクリックすると、行番号のないスクリプトファイルをダウンロードできます

　このPerlスクリプトでは、以下のような処理を行っています。

• 1行目 use DBI;
  ここでDBIの使用を宣言して、先ほどインストールしたDBIモジュールを利用できるようにします。Perlスクリプトでデータベースに関連する作業をするときには欠かせない1行です。
• 5行目 $db = DBI->connect('DBI:mysql:データベース名:サーバ名', 'ユーザー名', 'パスワード');
  ここでデータベースハンドルオブジェクト($db)を生成します。
• 6行目 $sth = $db->prepare("SQL文");
  5行目で生成したデータベースハンドルオブジェクトのprepareメソッドに、実行したいSQL文を引数に渡し、ステートメントハンドルオブジェクト($sth)を生成します。この後の処理は、この2つのオブジェクトに用意されたメソッドを呼び出すことで進めていきます。この時点ではまだデータベースにはSQL文は発行されていません。
• 7行目 $sth->execute;
  ここで初めて、"INSERT INTO list VALUES (1,'1st','memo 1st')"がMySQLサーバに対して実行されます。

　これで処理が終了するわけではありません。このままではMySQLサーバに接続したままになり、サーバ側ではmysqlクライアントプロセスが起動したままになります。実際にはPerlスクリプトの終了とともに、$dbも$sthも開放されますが、開いたものは閉じる癖をつけておきましょう。

• 8行目 $sth->finish;
  ステートメントハンドルオブジェクトの終了。
• 9行目 $db->disconnect;
  データベースハンドルオブジェクトの切断。

　オブジェクトだとかメソッドだとか、「聞き慣れないなぁ」と思われている方は、上記のスクリプトをMySQLデータベースを操作する際のひな形として、どんなプログラムを作るときもこれを出発点にするようにとらえてください。ユーザー名、パスワード、ホスト名など、必要な個所を書き換えれば応用できますし、INSERT、DELETE、UPDATEも6行目の

$sth = $db->prepare("SQL文");

を書き換えることで対応できますね。

$ perl sample1.pl

で実行します。

コラム　ハンドルオブジェクトとは

　「DBIはデータベース非依存の機能や変数を提供する」と説明しました。その機能や変数を提供するための手段として「データベースハンドルオブジェクト」が用意されています。オブジェクトという言葉が示すとおり、データベースハンドルオブジェクトにはデータベース操作に関連したデータと操作が一体化されています。データベースハンドルオブジェクトに定義された操作を呼び出したい場合は下記のようにします。

データベースハンドルオブジェクト->操作("引数");

　操作を呼び出す“->”記号を見ると、まさにオブジェクト指向構文であることが分かります。使い慣れないうちは独特の構文にとまどいますが、例えば“$db->disconnect;”を次のように読み直したらどうでしょう。

「$dbの中のdisconnectを呼び出す」

　 なら、

$sth = $db->prepare("INSERT INTO list VALUES (1,'1st','memo 1st')");

はどうなるでしょうか。

「$dbの中の操作prepare(引数....)を呼び出す。その戻り値として$sthを得る」

となります。得られた$sthはSQL文を発行したり、抽出結果を取得するオブジェクトとして使用します。このオブジェクトを「ステートメントハンドルオブジェクト」と呼びます。

スクリプトでデータ抽出

　SELECT文の場合はどうでしょう。たとえSELECT文が実行できたとしても、抽出内容をどうやって取り出したらいいのでしょうか。

　順番に見ていきましょう。

1   use DBI;
2
3   $user = 'test';
4   $passwd = 'test2001';
5   $db = DBI->connect('DBI:mysql:ATMARKIT:localhost', $user, $passwd);
6   $sth = $db->prepare("SELECT id, name, memo FROM list");
7   $sth->execute;
8   $num_rows = $sth->rows;
9   print "該当 $num_rows 件\n";
10  for ($i=0; $i<$num_rows; $i++) {
11    @a = $sth->fetchrow_array;
12    print "id=$a[0], name=$a[1] memo=$a[2] \n";
13  }
14  $sth->finish;
15  $db->disconnect;

リスト2　データ抽出を行うサンプルスクリプト（sample2.pl）。［ココ］をクリックすると、行番号のないスクリプトファイルをダウンロードできます

　7行目までは、"SELECT id, name, memo FROM list"を実行している以外は先ほどのリスト1とまったく同じです。

　その先、ステートメントハンドルオブジェクト（$sth）に用意されているメソッドを使用し、抽出結果を取得します。まず該当件数を得るために“rows”メソッドを呼び出します（8行目）。該当件数の回数だけforでループを回し、該当行を得ます（10〜13行目）。

　ループの中では該当行（@a）から、該当項目（$a[0],$a[1],$a[2]）を取り出します。$a[i]の順番は"SELECT id, name, memo ....."で指定した順になります。

　で、最後は礼儀正しく開いたものを閉じて終了です（14、15行目）。

$ perl sample2.pl

で実行します。