Creole ガイド

Creole は Java の JDBC API をヒントにして作られたものである。 この API に精通している方なら、 これらがかなり似ていることがお分かりいただけると思う。 Creole は JDBC の慣例に準じた形で動作するように開発が進められてきたが、 PEAR::DB および PEAR::MDB という DB 抽象化パッケージ（いずれも http://pear.php.net/ から取得できる）の慣習や利便性を取り込んだりもしている。

• データベースに接続する
• データをSELECTする
  • 基本的なループ
  • Executeオプション
  • 結果の数を制限する
  • 何個の値が返されたか？
  • カラムの値を取り出す
    • 結果セットのインデックス化
  • 結果セットをスクロールするメソッド群
  • 結果セットの反復子（イテレータ）
• データベースを更新する
  • プリペアド・ステートメントに関して
    • 基本的な例
    • 総称的な set() メソッドを使う
    • パラメータ配列を使う
• Working with LOB (Locator Object) Columns
  • データベースにバイナリファイルをインサートする
  • データベースからバイナリファイルを取り出す
  • creole.Blob, creole.Clob の代わりに生の文字列を使う
    • 文字列から LOB 値を設定する
    • LOB値を取り出して文字列にセットする
• ストアドプロシージャ
• DBのメタデータ
• Appendix A: Creoleで例外を使用する

データベースに接続する

Creole は PEAR スタイルの DSN 配列を使ってデータベースに接続する。 また、接続用 URL を DSN ハッシュに変換するための parseDSN() メソッド （これも PEAR 由来）を提供している。

$dsn = array('phptype' => 'mysql',
             'hostspec' => 'localhost',
             'username' => 'dbuser',
             'password' => 'dbpass',
             'database' => 'testdb');

$conn = Creole::getConnection($dsn);

これは以下のようにも書ける：

$dsn = "mysql://dbuser:dbpass@localhost/testdb";
$conn = Creole::getConnection($dsn);

DriverManager::getConnection() への２番目のパラメータとしてフラグを指定できる。 現在サポートされているフラグは Creole::PERSISTENT（永続的接続を行う）および Creole::NO_ASSOC_LOWER（結果セットにおけるフィールド名のポータビリティを無効にする） の２種類のみとなっている。

これらのフラグはビットなので、これらを足し算した値を指定する（あるいはビット or 演算子 | を使う）ことで、複数の値を指定することも可能である。

$conn = Creole::getConnection($dsn, Creole::PERSISTENT + Creole::NO_ASSOC_LOWER);

データをSELECTする

問い合わせ（データベースを変更する SQL ではなく、結果を返す SQL）を実行するには executeQuery() メソッドを使う。executeQuery() メソッドは、あなたがお使いの RDBMS に関する ResultSet サブクラスを返す。

基本的なループ

非常に基本的な問い合わせと、結果の取り込むためのループ：

$rs = $conn->executeQuery("SELECT id, name FROM users");
while($rs->next()) {
  echo $rs->getString("name") . " (" . $rs->getInt("id") . ")\n"
}

Executeオプション

Creole を使って問い合わせを実行する場合、2-3 のオプションがある：

• Connection::executeQuery($sql)
  • ほとんど直接実行を行うメソッド
  • limit/offset 指定はサポートされていない
  • SQL におけるパラメータの置換はサポートされていない
• Statement::executeQuery()
  • 多少のオーバーヘッドを伴う。Statement クラスが初期化された状態で： $conn->createStatement()->executeQuery($sql);
• PreparedStatement::executeQuery()
  • PHP スクリプトからの設定データを伴う問い合わせの際に推奨されるメソッド。

結果の数を制限する

Creole では、問い合わせにおける LIMIT/OFFSET の設定をサポートしている。 SQL レベルでこれをサポートするドライバでは SQL クエリーが変更される。 その他のドライバ（たとえば MS SQL Server）では、LIMIT/OFFSET は ResultSet によりエミュレートされる。LIMIT/OFFSET がエミュレートされるか （SQL レベルで）ネオティブに実行されるかにかかわらず、ResultSet メソッド は同じように動作する。

結果の制限を行いたい場合は Statement オブジェクトを使用すること。

// プリペアド・でないステートメント
$stmt = $conn->createStatement();
$stmt->setLimit(10);
$stmt->setOffset(5);

$rs = $stmt->executeQuery("SELECT * FROM user");
// MySQL なら SELECT * FROM user LIMIT 10,5 に変換されて渡される

// プリペアド・ステートメント
$stmt = $conn->prepareStatement("SELECT * FROM user WHERE id = ?");
$stmt->setInt(1, $id);
$stmt->setLimit(10);
$stmt->setOffset(5);

$rs = $stmt->executeQuery();

Creole では LIMIT 指定なしの OFFSET はサポートしていない。 いくつかのドライバ（たとえば Oracle）はこれをネイティブに実行できるが、 その他のもの（MySQL, PostgreSQL, SQLite）ではできない。

何個の値が返されたか？

getRecordCount() メソッドは、直近の SELECT ステートメントで返されたレコードの数を返す。

LIMIT/OFFSET を指定した場合、 getRecordCount() は LIMIT/OFFSET が適用された後の結果の個数のみを返す。

カラムの値を取り出す

カラムの値を取得するデフォルトのメソッドは、 カラムのデータ型に合った get*() メソッドを使用するようになっている。 たとえば getString() を使って CHAR/VARCHAR/TEXT のカラムからデータを受け取る。 Creole は各種のエスケープ解除や型の変換を行う。以下に例を示す：

while($rs->next()) {
  print "\n" . $rs->getString("name"); // PHP の文字列を返す
  print "\n" . $rs->getInt("id"); // PHP の int/long を返す
  print "\n" . $rs->getTimestamp("stamp", "%c"); // 加工された日付を返す
  //(２番目のパラメータは date() または strftime() 書式文字列のいづれか)
}

返すデータについて型変換をしたくなければ総称的な ResultSet::get() メソッドを使ってもよいし、 public な ResultSet::$fields プロパティを直接参照してもよい。

while($rs->next()) {
  echo $rs->get("name") . " (" . $rs->get("id") . ")\n";  
}
// または
while ($rs->next()) {
  foreach($rs->fields as $key => $value) {
     print "$key: $value\n";
  }
}

自前でキャストせずに get*() 型変換メソッドを使う利点としては、データベースが NULL を返したら型変換メソッドも NULL を返すことである。もし自前で型変換を やろうとするなら、以下のことをチェックすべきであろう：

$val = $rs->get("id");
if ($val !== null) $val = (int) $val;

また、返された値の型は信用できない。ネイティブの PHP ドライバの多く （ほとんど？）はどのカラムの型についても文字列で返すようになっているが、 その他（たとえば MS SQL Server）については、 数値カラムについては整数型を返すなどの例外がある。 このため、型により動作が変わるようなスクリプトを書く場合は、 常に型をキャストを行う get*() メソッドを使用すべきである。

結果セットのインデックス化

デフォルトでは、結果配列はフィールド名でインデックス化が行われる。 問い合わせを実行する際、数値でインデックス化するように指定することもできる。

JDBC の慣習に従い、数値インデックス化されたカラムは 1 から始まる。

$sql = "SELECT login, login_stamp FROM user";
$rs = $conn->executeQuery($sql, ResultSet::FETCHMODE_NUM);

while($rs->next()) {
  print "Login: " . $rs->getString(1);
  print "Last login: " . $rs->getTimestamp(2, "m/d/y H:i:s");
}

すべてのドライバが数値と連想配列の両方によるインデックス化（たとえば mysql_fetch_array()の結果のデフォルト）をネイティブでサポートしているわけではないため、 Creole でもこれをサポートしていない。

もし連想配列を使おうとしているのであれば、デフォルトの振舞いはフィールド名を 小文字で返すようになっていることに留意してほしい。 Creole::NO_ASSOC_LOWER フラグを Creole::getConnection() に渡してやれば この振る舞いをやめさせられる。問い合わせごとにこの振る舞いを変えることは （もはや）できなくなった。

常に小文字の結果を返す理由は DB 間の実装の差異が大きいためである。 なぜ我々がこうするに至ったかについては、以下のことを考えてみてほしい：

• MySQL は SELECT ステートメントの記述にしたがって（もしくは、 "SELECT *" をする場合であれば DB 内におけるカラムの記述に 合わせて）大文字小文字混在で返してくる。
• PostgreSQL は常に小文字のカラム名を返す。
• Oracle は常に大文字のカラム名を返す。
• MS SQL Server は大文字小文字が混在したカラム名を返す。
• SQLite は設定により上記のどの動作にも合わせられる。

この互換性保持機能を知っておけば、開発者はデータベースを乗り換える際に 膨大はソースの書き換えをやらずに済む。この機能を無効にすることで、 array_change_key_case() が使われるデータベース（たとえば MySQL や Oracle） において、（問い合わせる際のカラム数などにもよるが）パフォーマンスが多少向上する。

結果セットをスクロールするメソッド群

ResultSet をスクロールする機能は MySQL, PostgreSQL および SQLite で正しく サポートされている。しかしながら Oracle に関しては、この動作はエミュレート により行われる。これは、Oracle では現時点ではリバーススクロールがサポート されていないためである。

// 最初のレコードに移動
$rs->first();

// ３行先に移動
$rs->relative(3);

// 逆方向にスクロールバック
$rs->previous();

// 最終行に移動
$rs->last();

結果セットの反復子（イテレータ）

従来のスクロール関数を使う場合、問い合わせの結果に対して SPL イテレータを 使って反復処理が行える。ドライバによっては、従来のスクロールメソッドに比べて 同等以上に高速な最適化イテレータを装備するものがある（たとえば SQLite）。

$rs = $conn->executeQuery("SELECT * FROM user");
foreach($rs as $row) {
  print_r($row); // $row is an assoc array
}

これは便宜上作成したもので、結果セットを反復処理し、 一切の型変換を行わないメソッドである。

カーソルが開始位置にない場合、結果セットのイテレータはカーソル位置を レコードセットの先頭にリセットすることに注意してほしい。逆スクロールを サポートしていないオラクルでは、これは SQLException が投げられる要因となる。 このため安全策をとるなら、イテレータと従来のスクロール関数を混在させないことだ。 そして結果セットに対して２回目の反復を行いたい場合、 再度問い合わせを発行するようにする。

データベースを更新する

データベースの更新を行う場合は executeUpdate() を使用する。executeUpdate() が返すのは結果セットではなく、影響を受けた行の数である。executeQuery() と同様に、 executeUpdate() を呼び出す際はいくつかのオプションが指定できる：

$numaffected = $conn->executeUpdate("DELETE from user");
// または
$stmt = $conn->createStatement();
$numaffected = $stmt->executeUpdate("DELETE from user");
// または
$stmt = $conn->prepareStatement("DELETE form user WHERE id = ?");
$stmt->setInt(1, 2);
$numaffected = $stmt->executeUpdate();

プリペアド・ステートメントについて

データベースの問い合わせや更新を行う場合、プリペアドクエリーと呼ばれる方法を 使うことをお勧めする。ネイティブドライバがプリペアドクエリーをサポートして いない場合は、エミュレートが行われる。プリペア処理にはわずかなパフォーマンスの 低下を伴うが、set*()（および総称的 set()）メソッドを使うことにより、 データの書式がデータベースと正しく整合が取れることが保証される。

SQL ステートメントに値を追加する際は、常に set*() メソッドを使用するべきである。 これによりデータが適切な型に変換されるのが保証され、適切にエスケープされた & がクォートされていた場合でも、SQL インジェクション攻撃から身を守ることができる。

基本的な例

$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");
$stmt->setInt(1, $id);
$stmt->setString(2, $name);
$stmt->setTimestamp(3, time());
$stmt->executeUpdate();

総称的な set() メソッドを使う

総称的な set() メソッドは PHP 純正の型を使い、 どのセッタメソッドが呼ばれるべきかを推測する。

$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");

$stmt->set(1, 2); // setInt()
$stmt->set(2, "Myname"); // setString()

include_once 'Date.php'; // using PEAR Date
$stmt->set(3, new Date(time())); // setTimestamp()

$stmt->executeUpdate();

パラメータ配列を使う

PreparedStatement::executeUpdate() や PreparedStatement::executeQuery() に対してはパラメータの配列を渡してもよい。

$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");

include_once 'Date.php'; // using PEAR Date

$stmt->executeUpdate(array(2, "Myname", new Date(time()));

PHP 型のパラメータが使われ、どのセッタメソッドが呼ばれるべきかが推測される。 上記のコードは以下と同じである：

$stmt = $conn->prepareStatement("INSERT INTO users (id, name, created) VALUES (?,?,?)");

$stmt->set(1,2);
$stmt->set(2, "Myname");
include_once 'Date.php'; // using PEAR Date
$stmt->set(3, new Date(time()));

このメソッドは PHP の型に依存しているため、Date の値のように文字列 （や整数）に見えるようなものについてはラッパークラスを使うべきである。

LOB（ロケータ・オブジェクト）カラムに関して

Creole には BLOB/CLOB カラムに対して使うための、非常にシンプルな API が用意してある。ここではどうやってファイルをデータベースに格納し、 そしてどのようにしてそれらを読み出したりブラウザに表示したりするのかを 見てゆこう。

CLOB 値は Clob クラスで使われる。これは Blob クラスと同じ API を持ち、 それぞれ PreparedStatement->setClob() と ResultSet->getBlob() メソッドを使ってセット／取出しが行えるというものである。

データベースにバイナリファイルをインサートする

あなたのデータベースにイメージをインサートする方法の例を以下に示す。

include_once 'creole/util/Blob.php';
$blob = new Blob();
$blob->setInputFile('/path/to/your/file.gif');

$sql = "INSERT INTO blobtable (name, image) VALUES (?,?)";
$stmt = $con->prepareStatement($sql);
$stmt->setString(1, 'file.gif');
$stmt->setBlob(2, $blob);
$stmt->executeUpdate();

データベースからバイナリファイルを取り出す

次に、挿入したイメージを取り出す例を示す。

$sql = "SELECT name, image FROM blobtable";
$rs = $con->executeQuery($sql);
$rs->next(); // 先頭レコードを取得

$name = $rs->getString("name");
$blob = $rs->getBlob("image");

// ファイルに保存する場合：
$blob->writeToFile('/path/to/new/file.gif');

// ブラウザに直接出力する場合：
// （もちろん、前もって正しいヘッダを出力する必要がある）
$blob->dump();

バックエンドデータベースによっては、BLOB に関して特別な扱いが必要なものもある。 Creole は BLOB の値を適切にエスケープ（し、Oracle のようにネイティブな BLOB コマンドを持つものについてはこれらを統合）するが、問い合わせを編集したり、 必要とされる他のデータベース処理を行うわけではない。たとえば PostgreSQL の場合だと、BLOB を SELECT したり更新したりするステートメントを トランザクションの中に入れる必要がある。Creole はこれをやってはくれない （SELECT ステートメントの場合だと、できない）。

creole.Blob, creole.Clob の代わりに生の文字列を使う

文字列から LOB に値をセットする

（たとえば、呼び出しているコードがセッタや渡される値を隠蔽してしまう場合 などにより）値を取得／設定するのにより堅実な API を使う必要があるのであれば、 直接ファイルの内容を表す文字列を使うこともできる。

// データを直接渡す
$file_contents = file_get_contents('/path/to/file.txt');
$stmt->setBlob(1, $file_contents);

たとえば Oracle の LOB サポートでは、Blob/Clob オブジェクトに関する動作に 関する利便性を考慮してファイルからの直接読み込みをサポートしている。 このように、そうすることに利点があれば、渡された文字列は内部的には Blob オブジェクトにラップされる場合がある。

LOB値を取り出して文字列にセットする

getBlob() / getClob() を使用して Blob/Clob の値を取り出す場合、 それぞれ creole.util.Blob または creole.util.Clob オブジェクトを 取得するはずである。しかしながら、これらのクラスには値を文字列で取得するための __toString() および getContents() という魔法のメソッドが存在する。

$blob = $rs->getBlob(1);
// $blob は creole.util.Blob のクラス

$blob = $rs->getBlob(1)->getContents(); 
// これで $blob は creole.util.Blob クラスの文字列コンテキストになる

// 同様に：
$blob = $rs->getBlob(1);
print $blob; // __toString() が暗黙に呼ばれる

ストアドプロシージャ

Creole では特別なストアドプロシージャ API を持つシステムにおいて、 ストアドプロシージャを起動するための CallableStatement をサポートしている。 現在これは MS SQL Server についてのみ適用される。 （訳註：現在は Windows プラットフォームでしか使えないようです。） JDBC とは異なり、 Creole はストアドプロシージャの構文を取り出すことはしない。 それぞれの RDBMS には変数をバインドしたり戻り値を取得したりするための 固有の構文がある。

以下の例は PHP のオンラインマニュアルに載っている、MSSQL のストアドプロシージャの例に基づいている。

MS MSQL Server のデータベースで以下のストアドプロシージャを使う：

CREATE PROCEDURE [myprocedure] 
( 
   @sval varchar(50) OUTPUT, 
   @intval int OUTPUT, 
   @floatval decimal(6,4) OUTPUT 
) AS 

if @intval is null 
    select '@intval is null' as answer 
else     
    select '@intval is NOT null' as answer 

set @sval='Hello ' + @sval 
set @intval=@intval+1 
set @floatval=@floatval+1 

return 10

以下のコードを使うと、このストアドプロシージャを起動して値を取得できる：

$stmt = $conn->prepareCall("myprocedure");

// 第三パラメータは、このパラメータの値を返して欲しいかどうかを指定する。
$stmt->setString("@sval", "Frank", true);
$stmt->setInt("@intval", 11, true);
$stmt->setFloat("@floatval", 2.1416, true);

// 他の（入力以外の）変数については registerOutParameter() メソッドを使用する。
// 入力変数については適用されない（上述）
$stmt->registerOutParameter("RETVAL", CreoleTypes::INTEGER);

// 返ってきた結果を取得
$result = $stmt->executeQuery(ResultSet::FETCHMODE_NUM); 
$result->next();
    
print "答え：" . $result->getString(1) . "\n"; 

// MSSQL の"癖"
// かならず結果セットの終了前（すなわち、ひとつのストアドプロシージャ
// により返された結果はひとつとは限らない）にやらなければならない。
// これをやらないと、出力パラメータがセットされない（変更される）。
$stmt->getMoreResults();

// これで出力パラメータが取り出せる
$val = $stmt->getInt("RETVAL");
$ival = $stmt->getInt("@intval");
$fval = $stmt->getFloat("@floatval");
$sval = $stmt->getString("@sval");
print "RETVAL = $val ; intval = $ival ; floatval = $fval ; string = $sval\n"; 

DB のメタデータ

Creole では JDBC クラスにおける DatabaseMetadata と ResultSetMetadata に比べればシンプルなメタデータ操作を提供している。データベースの "Info" クラスは Hibernate (Java) クラスに基づいて緩やかに結びついており、 データベースに関する非常に包括的なデータを提供する。

$dbinfo = $conn->getDatabaseInfo();
foreach($dbinfo->getTables() as $tbl) {
    print "テーブル：" . $tbl->getName() . "\n";
    foreach($tbl->getColumns() as $col) {
        print "\t".$col->getName()." (".$col->getNativeType().")";
        // その他のメソッドについては API マニュアルを参照
    }
    $pk = $tbl->getPrimaryKey();
    foreach($pk->getColumns() as $pkcol) {
        print "Pk col: ".$pkcol->getName();
    }
    // getForeignKeys(), getIndexes() を含む、その他の TableInfo メソッド
}

Appendix A: Creoleで例外を使用する

Creole が最初から PHP5 向けに書かれた理由のひとつに、PHP5 の新しい機能としての例外、および言語構文としての try/catch/throw のサポートが挙げられる。Creole クラスのメソッドでは SQLException オブジェクトのみをスローする。 SQLException はビルトインの Exception のサブクラスであるが、 若干のプロパティの追加を行っている（たとえばネイティブドライバのエラーや エラーをトリガーする SQL ステートメントなど）。

例外を使うための完全な開設はこのガイドの範囲を超えるので、 Zend Engine 2 Exception documentation を参照してほしい。

ここでは、問い合わせを実行した際に起こったエラーを表示する例を示すにとどめる：

try {
  $conn->executeQuery($sql);
} catch (SQLException $sqle) {
  print "$sql の実行中にエラーが発生しました\n";
  print $sqle;  // 暗黙に __toString() を呼び出す
}

トランザクションを使っている場合に例外が一部役立つことがある。つまり、 catch ブロックの中でトランザクションをロールバックできるのである。 以下に例を示す：

try {
  $conn->setAutoCommit(false); // トランザクションを開始する
  $conn->executeUpdate("DELETE FROM mytable WHERE id = 3");
  $conn->executeUpdate("DELETE FROM myothertable WHERE id = 4");
  $conn->executeUpdate("UDPATE stillanothertable SET col = 1 WHERE id = 5");
  $conn->commit(); // トランザクションをコミットする
} catch (Exception $e) {
  $conn->rollback(); // トランザクション中の全ての変更を破棄する
  print "Aborted the transaction because: " . $e->getMessage();
}