22. PostgreSQL

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

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

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

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

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

■PostgreSQLへの接続
MySQLのように、PHPにPostgreSQL接続用の関数が用意されているので、これを
利用します。

-----------------------------------------------------------------
// データベース接続
$dbh = pg_connect("host=localhost dbname=db_test user=test_user password=test_pass");

//DBの接続に失敗した場合はエラー表示をおこない処理中断
if ($dbh == False) {
print("can not connect db");
exit;
}
-----------------------------------------------------------------

pg_connect関数を使用します。これは、第一引数にサンプルのように、データベース
のホスト名、データベース名、ユーザ名、パスワードを記述した文字列を渡します。

接続に失敗した場合は、Falseが返ってきます。

■テーブルの操作
テーブルの抽出、更新、クローズ、トランザクションについても、MySQLと同じ
ような関数が用意されているので、これを使用すれば簡単です。

-----------------------------------------------------------------
//トランザクション開始
$sql = "begin";
if (!(pg_query($dbh, $sql)))
{
print("トランザクション開始処理に失敗しました");
exit;
}

// データベース読み込み
$sql = "select name, price from drink";

if (!($rs = pg_query($dbh, $sql)))
{
print("SQL Failed " . $sql);
exit;
}

//結果の取得
while ($arr = pg_fetch_array($rs)) {
print $arr["name"] . ",";
print $arr["price"] . "<br>\n";
}

// データベース書き込み
// レコード追加
$sql = "insert into drink (name, price) values ('Beer', '500')";

if (!($rs = pg_query($dbh, $sql)))
{
print("SQL Failed " . $sql);
exit;
}

// レコード変更
$sql = "update drink set price = '230' where name = 'Coffee'";

if (!($rs = pg_query($dbh, $sql)))
{
$sql = "rollback";
if (!(pg_query($sql, $dbh)))
{
print("ロールバックに失敗しました");
exit;
}
print("SQL Failed " . $sql);
exit;
}

// レコード削除
$sql = "delete from drink where name = 'Tea'";

if (!($rs = pg_query($dbh, $sql)))
{
$sql = "rollback";
if (!(pg_query($sql, $dbh)))
{
print("ロールバックに失敗しました");
exit;
}
print("SQL Failed " . $sql);
exit;
}

//トランザクションの完了
$sql = "commit";
if (!(pg_query($dbh, $sql)))
{
print("コミット処理に失敗しました");
exit;
}

//データベース切断
pg_close($dbh);
-----------------------------------------------------------------

pg_query関数で、SQL文を実行します。mysql_query関数とは引数の順番が違います
ので、注意してください。

pg_fetch_arrayで、1行ずつデータをハッシュ配列で取得できます。これは、
mysql_fetch_arrayと使用方法が同じです。

トランザクション処理も、pg_query関数で制御できます。

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のマニュアルを調べてみてください。

★今日のまとめサンプルプログラム
-----------------------------------------------------------------
<HTML>
<BODY>

<?

// データベース接続
$dbh = pg_connect("host=localhost dbname=db_test user=test_user password=test_pass");

//DBの接続に失敗した場合はエラー表示をおこない処理中断
if ($dbh == False) {
print("can not connect db");
exit;
}

//トランザクション開始
$sql = "begin";
if (!(pg_query($dbh, $sql)))
{
print("トランザクション開始処理に失敗しました");
exit;
}

// データベース読み込み
$sql = "select name, price from drink";

if (!($rs = pg_query($dbh, $sql)))
{
print("SQL Failed " . $sql);
exit;
}

//結果の取得
while ($arr = pg_fetch_array($rs)) {
print $arr["name"] . ",";
print $arr["price"] . "<br>\n";
}

// データベース書き込み
// レコード追加
$sql = "insert into drink (name, price) values ('Beer', '500')";

if (!($rs = pg_query($dbh, $sql)))
{
print("SQL Failed " . $sql);
exit;
}

// レコード変更
$sql = "update drink set price = '230' where name = 'Coffee'";

if (!($rs = pg_query($dbh, $sql)))
{
$sql = "rollback";
if (!(pg_query($sql, $dbh)))
{
print("ロールバックに失敗しました");
exit;
}
print("SQL Failed " . $sql);
exit;
}

// レコード削除
$sql = "delete from drink where name = 'Tea'";

if (!($rs = pg_query($dbh, $sql)))
{
$sql = "rollback";
if (!(pg_query($sql, $dbh)))
{
print("ロールバックに失敗しました");
exit;
}
print("SQL Failed " . $sql);
exit;
}

// 再度取得
$sql = "select name, price from drink";

if (!($rs = pg_query($dbh, $sql)))
{
print("SQL Failed " . $sql);
exit;
}

//結果の取得
while ($arr = pg_fetch_array($rs)) {
print $arr["name"] . ",";
print $arr["price"] . "<br>\n";
}

//トランザクションの完了
$sql = "commit";
if (!(pg_query($dbh, $sql)))
{
print("コミット処理に失敗しました");
exit;
}

//データベース切断
pg_close($dbh);

?>

</BODY>
</HTML>
-----------------------------------------------------------------

■解説
レッスン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'のフォーマットで表示してください。

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

-----------------------------------------------------------------
<HTML>
<BODY>

<?

//ホスト名
$db_host = "localhost";

//データベースユーザ
$db_user = "test_user";

//パスワード
$db_pass = "test_pass";

//データベース名
$db_name = "db_test";

//データベース接続
$dbh = mysql_connect($db_host, $db_user, $db_pass);

//DBの接続に失敗した場合はエラー表示をおこない処理中断
if ($dbh == False) {
print("can not connect db");
exit;
}

//データベース選択
if (!(mysql_select_db($db_name, $dbh)))
{
print("faild to select db");
exit;
}

// (1) DRINKテーブルから、NAMEの文字に'ee'の文字が含まれるレコードの、PRICEの
// 合計を表示してください。
$sql = "
select
sum(price) as price
from
drink
where
name like '%ee%'
";

if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}

//結果の取得
while ($arr = mysql_fetch_array($rs)) {
print $arr["price"] . "<br>\n";
}

// (2) DRINKテーブルから、すべてを抽出し、ID、NAME、PRICEと、UP_DATEの日時を
// '2005/01/01 24:00'のフォーマットで表示してください。
$sql = "
select
id,
name,
price,
date_format(up_date, '%Y/%m/%d %H:%I') as up_date
from
drink
";

if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}

//結果の取得
while ($arr = mysql_fetch_array($rs)) {
print $arr["id"] . ",";
print $arr["name"] . ",";
print $arr["price"] . ",";
print $arr["up_date"] . "<br>\n";
}

//データベース切断
mysql_close($dbh);

?>

</BODY>
</HTML>
-----------------------------------------------------------------

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

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

→下のサンプルを参照してください。
-----------------------------------------------------------------
<HTML>
<BODY>

<?

//ホスト名
$db_host = "localhost";

//データベースユーザ
$db_user = "test_user";

//パスワード
$db_pass = "test_pass";

//データベース名
$db_name = "db_test";

//データベース接続
$dbh = mysql_connect($db_host, $db_user, $db_pass);

//DBの接続に失敗した場合はエラー表示をおこない処理中断
if ($dbh == False) {
print("can not connect db");
exit;
}

//データベース選択
if (!(mysql_select_db($db_name, $dbh)))
{
print("faild to select db");
exit;
}

// カウンタ読み込み
$sql = "
select
count
from
counter
";

if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}

//結果の取得
while ($arr = mysql_fetch_array($rs)) {
$cnt = $arr["count"];
}

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

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

// カウンタ書き込み
$sql = "
update
counter
set
count = '" . $cnt . "'
";

if (!($rs = mysql_query($sql, $dbh)))
{
print("SQL Failed " . $sql);
exit;
}

//データベース切断
mysql_close($dbh);

?>

</BODY>
</HTML>
-----------------------------------------------------------------

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

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