今日は、PostgreSQLについて少しご説明します。

■PostgreSQLについて
PostgreSQLは、MySQLに比べて複雑なSQL文を処理することができます。従って、
高度な検索が要求されるシステムを開発する場合は、PostgreSQLを選択した方が
良いでしょう。

PostgreSQLは、以下のページからダウンロードできます。
http://www.postgresql.org/download/

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

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

■PostgreSQLへの接続
MySQLと同じく、DBIパッケージのPgパッケージを利用して、PostgreSQLデータ
ベースへ接続する方法をご紹介します。

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

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

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

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

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

my $conn = DBI->connect("DBI:Pg:host=$host;dbname=$dbn", $user, $pass);
if (!$conn) exit;
-----------------------------------------------------------------

見ていただければ分かると思いますが、DBIのPgパッケージを利用すれば、
MySQLの時と同じ記述で、PostgreSQLを使用することができます。

違う部分は、connectの第一引数で、パッケージのPgを指定するところくらい
です。

DBIのPgパッケージがインストールされていない場合は、モジュールを
インストールする必要があります。

関数などの説明については、MySQLと同じなので、省略します。

■テーブルの操作
テーブルの抽出、更新、クローズ、トランザクションについても、DBIパッケージを
使用すれば、MySQLと全く同じ記述でできます。

-----------------------------------------------------------------
# トランザクション開始
$st = $conn->prepare("begin");
$res = $st->execute;
if(!$res)
{
print("トランザクション開始処理に失敗しました");
exit;
}

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

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

# レコード変更
$result = $conn->prepare("update drink set price = '230' where name = 'Coffee'");
$rec = $result->execute;
if (!$rec)
{
# ロールバック
$st = $conn->prepare("rollback");
$res = $st->execute;
if(!$res)
{
print("ロールバックに失敗しました");
exit;
}
print("SQL Failed");
exit;
}

# レコード削除
$result = $conn->prepare("delete from drink where name = 'Tea'");
$rec = $result->execute;
if (!$rec)
{
# ロールバック
$st = $conn->prepare("rollback");
$res = $st->execute;
if(!$res)
{
print("ロールバックに失敗しました");
exit;
}
print("SQL Failed");
exit;
}

# トランザクション終了
$st = $conn->prepare("commit");
$res = $st->execute;
if(!$res)
{
print("コミット処理に失敗しました");
exit;
}

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

SQL文についても、MySQLとほとんど同じ記述で書けます。

MySQLのレッスンの時と同様、PostgreSQLのデータベースにも、以下のような
レコードを持つDRINKテーブルを作成しておいてください。

NAME PRICE
Tea 250
Coffee 200

この後の更新処理で、INSERT文で、DRINKテーブルに

NAME PRICE
Beer 500

を追加し、CoffeeのレコードのPRICEを230に変更し、Teaレコードを削除して
います。

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

NAME PRICE
Coffee 230
Beer 500

■PostgreSQLの関数
次に、PostgreSQL内で使用できる関数を少しご紹介します。

COUNT レコード数を取得します
SUM 抽出したデータの合計を計算します
NOW 現在時刻を取得します
TO_CHAR 日時を任意のフォーマットの文字列に変換します

COUNT、SUM、NOWはMySQLと同じですが、TO_CHARは、MySQLではDATE_FORMAT
でした。使用方法とフォーマットの書き方が少し違うので注意してください。

-----------------------------------------------------------------
select
count(*)
from
drink
where
price > 200
-----------------------------------------------------------------

MySQLと同様の記述でCOUNT関数を使用できます。

これでDRINKテーブルのPRICEが200以上のレコード数を取得します。

-----------------------------------------------------------------
select
sum(price)
from
drink
-----------------------------------------------------------------

これもMySQLと同様で、PRICEの合計を抽出することができます。

次のNOWもMySQLと同様です。以下のテーブルがあったとします。

NAME PRICE UP_DATE
Beer 500 2005/01/01 00:00:00
Juice 300 2005/02/23 00:00:00
Coke 150 2004/12/31 00:00:00
Tea 250 2005/04/01 00:00:00
Coffee 200 2005/10/01 00:00:00

-----------------------------------------------------------------
select
name,
price,
up_date
from
drink
where
up_date > now()
-----------------------------------------------------------------

とした場合、UP_DATEが現在時刻よりも新しいものが抽出されます。

次にTO_CHARです。

-----------------------------------------------------------------
select
name,
price,
to_char(up_date, 'YYYY/MM/DD HH24:MI:SS')
from
drink
-----------------------------------------------------------------

MySQLのDATE_FORMATに記述は似ていますが、フォーマットの書き方が違います。

フォーマットの記述については、
http://www.postgresql.jp/document/pg74doc/html/
などを参照してください。

PostgreSQL関数も、他にもたくさんありますが、紹介するのはここまでにします。
興味があれば、PostgreSQLのマニュアルを調べてみてください。

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

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

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

use DBI;

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

# データベース接続
my $conn = DBI->connect("DBI:Pg:host=$host;dbname=$dbn", $user, $pass);
if(!$conn)
{
print("データベースの接続に失敗しました");
exit();
}

# トランザクション開始
$st = $conn->prepare("begin");
$res = $st->execute;
if(!$res)
{
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)
{
# ロールバック
$st = $conn->prepare("rollback");
$res = $st->execute;
if(!$res)
{
print("ロールバックに失敗しました");
exit;
}
print("SQL Failed");
exit;
}

# レコード削除
$st = $conn->prepare("delete from drink where name = 'Tea'");
$rec = $st->execute;
if (!$rec)
{
# ロールバック
$st = $conn->prepare("rollback");
$res = $st->execute;
if(!$res)
{
print("ロールバックに失敗しました");
exit;
}
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 = $conn->prepare("commit");
$res = $st->execute;
if(!$res)
{
print("コミット処理に失敗しました");
exit;
}

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

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

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

■解説
レッスン19のサンプルと同じ処理をしていますが、トランザクション処理を追加して
います。

トランザクションの途中でエラーが発生した場合は、ロールバックするようにします。

ロールバックが失敗する時は、データベースに異常が発生しているので、致命的な
エラーとなるので、ロールバックのエラーは無視してもよいと考えられます。

★課題
1. PostgreSQLを使用して、以下のテーブルを処理を実行するプログラムを作成して
ください。

NAME PRICE UP_DATE
Beer 500 2005/01/01 00:00:00
Juice 300 2005/02/23 00:00:00
Coke 150 2004/12/31 00:00:00
Tea 250 2005/04/01 00:00:00
Coffee 200 2005/10/01 00:00:00

(1) まず、トランザクション処理を開始してください。
(2) PRICEが300以下のデータを抽出してください。
(3) 以下のレコードを追加してください。

NAME PRICE UP_DATE
Soda 100 2005/05/01 00:00:00

(4) 全てのデータを抽出し、UP_DATEは、"2005年01月01日 24時00分"のフォーマット
で表示してください。

★前回の課題の解答
1. 上のまとめサンプルのテーブルから、以下の条件でデータを抽出してください。

(1) DRINKテーブルから、NAMEの文字に'ee'の文字が含まれるレコードの、PRICEの
合計を表示してください。
(2) DRINKテーブルから、すべてを抽出し、ID、NAME、PRICEと、UP_DATEの日時を
'2005/01/01 24:00'のフォーマットで表示してください。

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

-----------------------------------------------------------------
#! /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 = "test";
$pswd = "test";

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

# (1) DRINKテーブルから、NAMEの文字に'ee'の文字が含まれるレコードの、PRICEの
# 合計を表示してください。
$query = "
select
sum(price)
from
drink
where
name like '%ee%'
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

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

# (2) DRINKテーブルから、すべてを抽出し、ID、NAME、PRICEと、UP_DATEの日時を
# '2005/01/01 24:00'のフォーマットで表示してください。

$query = "
select
id,
name,
price,
date_format(up_date, '%Y/%m/%d %H:%I')
from
drink
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

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

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

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

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

■解説
WHEREの条件の書き方と、MySQLの関数の使用方法などを確認してください。

2. トランザクション管理を行って、データベースを使用した簡易的なカウンタを
作成してください。

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

-----------------------------------------------------------------
#! /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 = "test";
$pswd = "test";

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

# カウンタ読み込み
$query = "
select
count
from
counter
";
$st = $conn->prepare($query);
$res = $st->execute;
if(!$res)
{
print("SELECT SQL文に失敗しました");
exit;
}

@res = $st->fetchrow;
my $cnt = $res[0];

# カウンタアップ
$cnt++;

print "あなたは " . $cnt . " 人目のお客様です。<br>\n";

# カウンタ書き込み
$sql = "
update
counter
set
count = '" . $cnt . "'
";
$st = $conn->prepare($sql);
$rec = $st->execute;
if (!$rec)
{
exit;
}

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

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

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

■解説
ファイルの読み書きが、データベースの読み書きにすればOKです。

前回のレッスンでトランザクション処理を紹介していなかったので、ここでは
書いていませんが、カウンタ処理ではトランザクション処理を行った方が
よいです。