今日からいよいよデータベースの解説に入りたいと思います。

■データベースについて
顧客情報や商品データなど、WEBのシステムプログラムが扱うデータが膨大にある
場合、テキストのファイルに保存していると、開発も大変ですし、パフォーマンスも
落ち、何より保管状態に不安が残ります。

そこで、データベースという技術を使用します。データベースの基本機能は、
テーブルにデータを保管し、高速な検索ができることでしょう。

データベースの詳しい説明は省きますが、現在、よく使われているデータペースの
種類は、以下のものがあります。

・Oracle
　 ライセンス費用が高いが、保証されている分、信頼性がある
・MS-SQL
　 WINDOWS系のサーバでよく使用される
・PostgreSQL
　 オープンソースなので、無料。高機能な検索ができる
・MySQL
　 オープンソースなので、無料。処理が高速

OracleやMS-SQLなどは無料ではないので、大企業が相手でないとあまり使う機会が
ないと思います。ですので、レッスンではPostgreSQLとMySQLを取り扱おうと
思います。

■MySQLについて
先に扱いやすいMySQLから取り上げたいと思います。
MySQLは、複雑なSQL文は扱えませんが、処理が速く、PostgreSQLよりも扱いやすい
ので、初心者に向いています。

MySQLは、以下のページからダウンロードできます。
http://www.softagency.co.jp/MySQL/downloads/index.html

このレッスンは、Perlのレッスンですので、インストールや設定については、
ご紹介しません。マニュアル等を参照してください。

また、レンタルサーバを使用していて、レンタルサーバの方でMySQLがインストール
されていれば、それを使用することができます。

■MySQLへの接続
データベースを扱うには、まずデータベースへ接続するという作業が必要に
なります。

-----------------------------------------------------------------
# パッケージの読み込み
use DBI;

# データベース名
my $dbn = "db_test";

# データベースサーバ名
my $host = "localhost";

# ユーザ名
my $user = "test_user";

# パスワード
my $pass = "test_pass";

my $conn = DBI->connect('DBI:mysql:' . $dbn . ':' . $host , $user, $pswd)
if (!$conn) exit;
-----------------------------------------------------------------

このレッスンでは、DBIというパッケージを使用してMySQLへ接続します。
DBIもPerlの標準には付属していないので、ない場合は、モジュールを
インストールする必要があります。

DBIはクラスのパッケージになっているので、"->"を使用して呼び出します。

データベースに接続するには、必ずデータベース名、サーバ名、ユーザ名、
パスワードが必要になります。WEBサーバ内にデータベースがある場合は、
"localhost"を指定します。

サンプルの場合、"db_test"というデータベース名に、ユーザ名"test_user"、
パスワード "test_pass"で接続しています。

DBIのconnect関数を使用して接続すると、データベースコネクションという
ハンドラが返ってきますので、これを使用してSQL文を渡して実行します。
失敗した場合は、0が返ってきます。

■テーブルの抽出
SQL文には、大きく分けて抽出と更新があります。

まずは、テーブルの内容を抽出するサンプルを記述します。

-----------------------------------------------------------------
# データベース読み込み
$st = $conn->prepare("select name, price from drink");
$rec = $st->execute;
if(!$rec)
{
exit;
}
while (@data = $st->fetchrow) {
print $data[0] . ", ";
print $data[1] . "\n";
}
-----------------------------------------------------------------

DRINKというテーブルに、例えば以下のようなレコードがあったとします。

NAME PRICE
Tea 250
Coffee 200

これを取り出したい場合は、SELECT文を書きます。
上記のサンプルのようにすると、中身が全て表示されます。

データベースコネクションの関数に、prepare()というものがあります。これを
使用してSQL文を渡すと、ステートメントハンドルというオブジェクトが返って
きます。

さらに、このステートメントハンドルの関数のexecute()を使用することで、予め
渡したSQLを実行します。SQL文で失敗すると、0が返ってきます。

成功した場合、SELECT文で抽出した結果が、先ほどのステートメントハンドルに
格納されますので、fetchrowなどの関数で取り出します。fetchrowは、1行ずつ
配列に格納していく関数です。

上のサンプルの場合、

1回目 $data[0] 'Tea'、 $data[1] '250'
2回目 $data[0] 'Coffee'、$data[1] '200'

という文字列が得られます。ループで回して、行がなくなったらfetchrowは 0を
返しますので、ループを抜けます。

■レコードの更新
新しくレコードを追加したり、既存のレコードを変更したり、削除することが
できます。

-----------------------------------------------------------------
# データベース書き込み
# レコード追加
$st = $conn->prepare("insert into drink (name, price) values ('Beer', '500')");
$rec = $st->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# レコード変更
$st = $conn->prepare("update drink set price = '230' where name = 'Coffee'");
$rec = $st->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# レコード削除
$st = $conn->prepare("delete from drink where name = 'Tea'");
$rec = $st->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}
-----------------------------------------------------------------

テーブルに新しい行を追加するには、INSERT文を使用します。

サンプルの場合、DRINKテーブルに

NAME PRICE
Beer 500

を追加しています。

抽出の時と同様に、prepareとexecute関数を使用しています。
抽出と違うのは、更新処理なので、データが取得できないことです。

既存のデータを変更する場合は、UPDATE文を使用します。
処理方法は、INSERT文の時とまった同じです。

サンプルを実行すると、以下のようなテーブルになります。

NAME PRICE
Tea 250
Coffee 230
Beer 500

レコードの削除も同様の方法で行えます。

サンプルを実行すると、以下のようになります。

NAME PRICE
Coffee 230
Beer 500

■データベースの切断
データベースに接続したら、終わった時に必ずクローズするようにします。

Perlの場合は、クローズを忘れても、レスポンスがクライアントに返った時に
自動的にクローズされますが、安全のため、データベースクローズは行うように
してください。

-----------------------------------------------------------------
$st->finish;
$conn->disconnect;
-----------------------------------------------------------------

$stはステートメントハンドルです。finish関数を呼ぶことで、明示的にクローズ
します。

$connの方は、データベースのコネクションになります。disconnect関数で接続を
切断します。

★今日のまとめサンプルプログラム
-----------------------------------------------------------------
#! /usr/local/bin/perl

print "Content-type:text/html\n\n";

print << "END_OF_HTML";
<HTML>
<BODY>
END_OF_HTML

use DBI;

$dbn = "test";
$user = "admin";
$pswd = "admin";

# データベース接続
$conn = DBI->connect('DBI:mysql:' . $dbn . ':localhost', $user, $pswd);
if(!$conn)
{
print("データベースの接続に失敗しました");
exit();
}

# データベース読み込み
$st = $conn->prepare("select * from drink");
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . ", ";
print $res[1] . "<br>\n";
}

# データベース書き込み
# レコード追加
$st = $conn->prepare("insert into drink (name, price) values ('Beer', '500')");
$rec = $st->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# レコード変更
$st = $conn->prepare("update drink set price = '230' where name = 'Coffee'");
$rec = $st->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# レコード削除
$st = $conn->prepare("delete from drink where name = 'Tea'");
$rec = $st->execute;
if (!$rec)
{
print("SQL Failed");
exit;
}

# 再度取得
$st = $conn->prepare("select * from drink");
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

while(@res = $st->fetchrow)
{
print $res[0] . ", ";
print $res[1] . "<br>\n";
}

# データベースクローズ
$st->finish;
$conn->disconnect;

print << "END_OF_HTML";
</BODY>
</HTML>
END_OF_HTML

exit;
-----------------------------------------------------------------

■解説
レッスンの中で使用したテーブルをそのまま使っています。

NAME PRICE
Tea 250
Coffee 200

サンプルを動かす場合は、上のテーブルを作成してください。
実行後は、

NAME PRICE
Coffee 230
Beer 500

というテーブルに更新されるはずです。

★課題
1. MySQLにて、以下のSQL文を実行してテーブルを作成した後、各処理を行う
プログラムを作成してください。

----------------------------------------------
create table product (
id int4 primary key,
name varchar(50),
stock int2,
comment varchar(255)
);

insert into product (
id,
name,
stock,
comment
) values (
1,
'ヘッドホン DK-A01',
52,
'高音質ヘッドホン'
);

insert into product (
id,
name,
stock,
comment
) values (
2,
'ヘッドホン CL-E22',
34,
'コードレスヘッドホン'
);

insert into product (
id,
name,
stock,
comment
) values (
3,
'ヘッドホンケーブル R03',
88,
'DK-A01用ケーブル'
);
----------------------------------------------

(1) 全てのレコードを抽出し、HTMLに表示してください。
(2) Perlプログラムにて、以下のデータを挿入してください。

ID NAME STOCK COMMENT
4 'ヘッドホン DKA-B02' 25 '小型ヘッドホン'

(3) ID = 2 のレコードのSTOCKを 31 に変更するプログラムを作成してください。
(4) ID = 3 のレコードを削除するプログラムを作成してください。

★前回の課題の解答
1. まとめサンプルを、以下の条件で改変してください。

(1) member_bを出力するprint_bというメンバ関数を追加してください。
(2) member_aを取得するget_aというメンバ関数を追加してください。
(3) member_cというメンバ変数を追加し、初期値に現在日時(yyyy/mm/dd hh:mm:ss)を
格納してください。
(4) member_cに値を設定するset_cというメンバ関数を追加してください。

→下のサンプルを参照してください。

common.pm
-----------------------------------------------------------------
package Common;

#--------------------------------------------------------
# 関数 : new
# 概要 : メンバ変数の初期値を設定
# 引数 : member_a、member_bの値
# 戻値 : インスタンス
#--------------------------------------------------------
sub new
{
my $proto = shift;
my $class = ref($proto) || $proto;
my $self = {};
bless($self, $class);

# メンバ変数の設定
$self->{member_a} = $_[0];
$self->{member_b} = $_[1];

# ローカル時刻の取得
($sec,$min,$hour,$mday,$mon,$year,$wday) = localtime(time);
$year += 1900;
$mon++;

$ct = sprintf("%04d/%02d/%02d %02d:%02d:%02d", $year,$mon,$mday,$hour,$min,$sec);
$self->{member_c} = $ct;

return $self;
}

#------------------------------------------------------------------
# 関数 : print_a
# 概要 : member_aの出力
# 引数 : なし
# 戻値 : なし
#------------------------------------------------------------------
sub print_a
{
my $self = shift;
print $self->{member_a};
}

#------------------------------------------------------------------
# 関数 : print_b
# 概要 : member_bの出力
# 引数 : なし
# 戻値 : なし
#------------------------------------------------------------------
sub print_b
{
my $self = shift;
print $self->{member_b};
}

#------------------------------------------------------------------
# 関数 : get_a
# 概要 : member_aの取得
# 引数 : なし
# 戻値 : member_a
#------------------------------------------------------------------
sub get_a
{
my $self = shift;
return $self->{member_a};
}

#------------------------------------------------------------------
# 関数 : get_b
# 概要 : member_bの取得
# 引数 : なし
# 戻値 : member_b
#------------------------------------------------------------------
sub get_b
{
my $self = shift;
return $self->{member_b};
}

#------------------------------------------------------------------
# 関数 : set_a
# 概要 : member_aのセット
# 引数 : 設定するmember_aの値
# 戻値 : なし
#------------------------------------------------------------------
sub set_a
{
my $self = shift;
$self->{member_a} = $_[0];
}

#------------------------------------------------------------------
# 関数 : set_c
# 概要 : member_cのセット
# 引数 : 設定するmember_cの値
# 戻値 : なし
#------------------------------------------------------------------
sub set_c
{
my $self = shift;
$self->{member_c} = $_[0];
}

1;
-----------------------------------------------------------------

test.cgi
-----------------------------------------------------------------
#! /usr/local/bin/perl

use strict;
use Common;

print "Content-type:text/html\n\n";

print << "END_OF_HTML";
<HTML>
<BODY>
END_OF_HTML

# オブジェクトの生成
my $test = Common->new("Test ", "Sample");

# (1) member_bを出力するprint_bというメンバ関数を追加してください。
$test->print_b();

# (2) member_aを取得するget_aというメンバ関数を追加してください。
print $test->get_a() . "<br>";

# (3) member_cというメンバ変数を追加し、初期値に現在日時(yyyy/mm/dd hh:mm:ss)を
# 格納してください。
print $test->{member_c} . "<br>";

# (4) member_cに値を設定するset_cというメンバ関数を追加してください。
$test->set_c("2005/03/01 00:00:00");

print $test->{member_c};

print << "END_OF_HTML";
</BODY>
</HTML>
END_OF_HTML

exit;
-----------------------------------------------------------------

■解説
サンプルのメンバ関数を参考に、コピーして使用すればできるかと思います。

ここでは、おおまかなクラスの概念が分かっていただければ良いです。本格的な
クラスを理解するのは、少し難しいので、クラスの説明はこの辺までにして
おきます。

興味があれば、クラスについて自分で調べてみてください。