Zend_Db(Zend_Db_Table)とMDB2の比較

根本的に違う点としては、Zend_Db_Tableはクエリビルダを持っているがMDB2はそういった機能が無いという事。バックエンドもZend_DbはPDOを使っている(らしい、これはソースレベルで追ってないのでわからない)が、MDB2はMDB2_driver_*に投げていて、そのドライバが例えばmysql_なんとかみたいな関数をコールしているという所であろう。

繋ぐ

ここでは試しに以下のような情報でMySQLサーバを用意するものとしよう。

% mysql -uphptest -phimitsu phptest

つまり

  • ユーザ名: phptest
  • パスワード: himitsu
  • データベース: phptest

となる。

Zend_Db(_Adapter)で繋ぐ

http://framework.zend.com/manual/ja/zend.db.adapter.html

接続するのはZend_Db_Adapter_Pdo_Mysqlなどがやってくれるのだが、上位のZend_Dbクラスのfactoryメソッドを利用すると自動的にロードされるので楽。

require_once 'Zend/Db.php';
$params = array(
    'host'     => '127.0.0.1',
    'username' => 'phptest',
    'password' => 'xxxxxxxx',
    'dbname'   => 'phptest',
    'charset'  => 'utf8'
);
$db = Zend_Db::factory('Pdo_Mysql', $params);

ここではpasswordを敢えてxxxxとか適当なものをいれてみたわけだが、ここではまだ接続に行かないので実行してもエラーにはならない。接続するためにはgetConnection()というメソッドを呼ぶ

// 略
$db = Zend_Db::factory('Pdo_Mysql', $params);
$db->getConnection();

これでパスワードが間違っているため、例外がthrowされてくる。Zend_Db_Adapter_Exceptionという名前でthrowされてくるが、Zend_Exceptionでまとめて取ってしまうというのもアリかもしれませんね。

require_once 'Zend/Db.php';
$params = array(
    'host'     => '127.0.0.1',
    'username' => 'phptest',
    'password' => 'xxxxxxxx',
    'dbname'   => 'phptest',
    'charset'   => 'utf8',
);
try {
  $db = Zend_Db::factory('Pdo_Mysql', $params);
  $db->getConnection();
} catch (Zend_Exception $e) {
  die($e->getMessage());
}

なお、getConnectionは必須ではなく、factoryさえ行っておけば、必要に応じで内部で呼び出されるため、不必要な接続を行いたくない場合はもう少し上手く書く事が出来るかも。

さて、これらを踏まえたMDB2での接続を見てみる。

MDB2で繋ぐ

MDB2で繋ぐ場合はZend_Dbでやったような配列形式でも可能だし、DSNとよばれるuri形式のようなものでも接続が可能だ。とりあえずDSNに関しては以下のドキュメントを参照されたい。

http://pear.php.net/manual/ja/package.database.mdb2.intro-dsn.php

これに基いてDSNを表現すると以下のようになる。

require_once 'MDB2.php';
$dsn = "mysql://phptest:xxxxxxxx@127.0.0.1/phptest?charset=utf8";
$mdb2 = MDB2::connect($dsn);
if (MDB2::isError($mdb2)) {
  // print $mdb2->getMessage();
  print $mdb2->getDebugInfo();
}

MDB2にもconnectでなくfactoryもある。まあそれはそうとしてconnectしてエラーだった場合はexceptionでなくMDB2_ErrorみたいなPEAR_Error的なのが返却されるので、一々isError等で確認してやらないといけないのが面倒な所。まあこれはPHP4的な表現では精一杯な所かもしれない。もちろん、適当なエラーハンドリングを渡す事でそれなりに綺麗な設計とする事はできるが、例外スローにはなかなか及ばないかもしれない。

MDB2はDSN形式(とここでは勝手に呼んでいる)だけでなく、配列による接続もサポートしている。というか内部では配列に変換されているだろうと思われる。 DSN形式から配列に変換するには以下のようにparseDSNメソッドを呼び出せば可能である。

require_once 'MDB2.php';
$dsn = "mysql://phptest:xxxxxxxx@127.0.0.1/phptest?charset=utf8";
$params = MDB2::parseDSN($dsn);
print_r($params);
----------------------------------
Array
(
    [phptype] => mysql
    [dbsyntax] => mysql
    [username] => phptest
    [password] => xxxxxxxx
    [protocol] => tcp
    [hostspec] => 127.0.0.1
    [port] =>
    [socket] =>
    [database] => phptest
    [mode] =>
    [charset] => utf8
)

たとえば、MDB2とZend_Dbを混在させる(そのような事ももちろん可能)とかという場合は、MDB2形式のDSNをparseしたものをZend_Dbに食わせると大体よい。ただし、databasedbnameというのが決定的に違うので、適当に配列を変換してやれば食ってくれるかと

MDB2もZend_Dbもどちらも使う例

require_once 'Zend/Db.php';
require_once 'MDB2.php';
 
$dsn = "mysql://phptest:himitsu@127.0.0.1/phptest?charset=utf8";
$params = MDB2::parseDSN($dsn);
$params['dbname'] = $params['database'];
 
try {
  $db = Zend_Db::factory('Pdo_Mysql', $params);
  $db->getConnection();
} catch (Zend_Exception $e) {
  die($e->getMessage());
}
 
$mdb2 = MDB2::connect($dsn);
if (MDB2::isError($mdb2)) {
  // print $mdb2->getMessage();
  print $mdb2->getDebugInfo();
}

では、接続が出来たという事で、いよいよCRUDを見ていく事とする

スキーマの用意

http://framework.zend.com/manual/ja/zend.db.adapter.html#zend.db.adapter.example-database

にあるものをほぼそのまま利用する事とする。「ほぼ」というのはcharsetの指定を与え、エンジンにInnoDBを指定した為、他

DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  account_name      VARCHAR(100) NOT NULL PRIMARY KEY
) CHARSET=utf8 ENGINE=InnoDB;
 
DROP TABLE IF EXISTS products;
CREATE TABLE products (
  product_id        INTEGER NOT NULL PRIMARY KEY auto_increment,
  product_name      VARCHAR(100)
) CHARSET=utf8 ENGINE=InnoDB;
 
DROP TABLE IF EXISTS bugs;
CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY auto_increment,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  assigned_to       VARCHAR(100) REFERENCES accounts(account_name),
  verified_by       VARCHAR(100) REFERENCES accounts(account_name),
  created_on        DATETIME
) CHARSET=utf8 ENGINE=InnoDB;
 
DROP TABLE IF EXISTS bugs_products;
CREATE TABLE bugs_products (
  bug_id            INTEGER NOT NULL REFERENCES bugs,
  product_id        INTEGER NOT NULL REFERENCES products,
  PRIMARY KEY       (bug_id, product_id)
) CHARSET=utf8 ENGINE=InnoDB;

何となく適当にデータをいれておきますか。

INSERT INTO accounts VALUES('alice');
INSERT INTO accounts VALUES('bob');
INSERT INTO accounts VALUES('eve');
INSERT INTO products VALUES(NULL, 'product1');
INSERT INTO products VALUES(NULL, 'product2');
INSERT INTO bugs VALUES(1, 'description1', 'open', 'alice' , 'bob', 'bob', curdate()));
INSERT INTO bugs VALUES(2, 'description2', 'closed', 'alice' , 'bob', 'eve', curdate()));
INSERT INTO bugs VALUES(3, 'description3', 'bogus', 'eve' , 'alice', 'bob', curdate()));
INSERT INTO bugs_products VALUES(1, 1);
INSERT INTO bugs_products VALUES(2, 2);
INSERT INTO bugs_products VALUES(3, 1);
INSERT INTO bugs_products VALUES(3, 2);
INSERT INTO bugs_products VALUES(3, 3);

取ってみる

先の例で、接続済みのオブジェクトのインスタンス名をZend_DBに関しては$db、MDB2に関しては$mdb2とする事とする。

全部の結果を得る

Zend_Dbの場合

fetchAll()SQLをそのまま渡す連想配列で返ってくる。

$sql = 'SELECT * FROM bugs';
$result = $db->fetchAll($sql);
print_r($result);

結果

    [0] => Array
        (
            [bug_id] => 1
            [bug_description] => description1
            [bug_status] => open
            [reported_by] => alice
            [assigned_to] => bob
            [verified_by] => bob
        )

    [1] => Array
        (
            [bug_id] => 2
            [bug_description] => description2
            [bug_status] => closed
            [reported_by] => alice
            [assigned_to] => bob
            [verified_by] => eve
        )
...

基本的には連想配列だが、setFetchModeをコールする事でオブジェクト等に変更する事が出来る。ともあれこんな具合。

MDB2の場合

同等の事はqueryAllで実現できる。しかし返却される結果は連想配列ではなく、ただの配列(という言い方は実は語弊があって、PHPの場合は全てが連想配列なのであるが)なので、こっちはsetFetchModeしないとキツいかもしれない。

$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT * FROM bugs';
$result = $mdb2->queryAll($sql);
print_r($result);
Array
(
    [0] => Array
        (
            [bug_id] => 1
            [bug_description] => description1
            [bug_status] => open
            [reported_by] => alice
            [assigned_to] => bob
            [verified_by] => bob
        )

    [1] => Array
        (
            [bug_id] => 2
            [bug_description] => description2
            [bug_status] => closed
            [reported_by] => alice
            [assigned_to] => bob
            [verified_by] => eve
        )
...

ここまでは、ほぼ等価なコードで同じような事が実現できる(速度の違いは多少あるだろうけど)

条件付きクエリの全結果を得る

WHERE句の指定。主キーの問い合わせのような、結果が必ず1つである事を期待されるわけでは無い場合はやはりfetchAll()ないしはqueryAll()を利用する。

WHEREに相当する箇所は大抵変数にて置き換えられるため、適切なエスケーピングを行う事が必須となる。Zend_Dbでは以下のように行う事で適切にエスケープを行う事が出来るようだ。

$sql = 'SELECT * FROM bugs WHERE bug_id = ? OR bug_id = ?';
$result = $db->fetchAll($sql, array(1, 2));
print_r($result);

これをMDB2で行おうと思うとやや大変で、prepareのち、executeのち、fetchAll()とする必要がある。queryAll()と違ってfetchAll()を呼ばなくてはならないのは、executeが返却するのがあくまで「結果セット」になっている為、その結果セットオブジェクトを操作するメソッドを別に叩かないとならんという事だ。

文字で起こすと訳がわからないが、以下のようなコードで上記のZend_Dbと同等の結果を得る事が出来る。

$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT * FROM bugs WHERE bug_id = ? OR bug_id = ?';
$result = $mdb2->prepare($sql)->execute(array(1,2))->fetchAll();
print_r($result);

なお、この記述方法はPHP5でのみ有効で、PEARならではのPHP4コンパチにしたいのであれば、いちいち変数に落としこまなくてはならない(とはいえ、最近はPHP4もあまり無いか…)。

本当にquoteされているのかチェックする

ここでは以下のような典型的なインジェクションが発生し得るコードを例にとって、正しくエスケープできているかどうか実験してみる。

Zend_Dbの例

$id = '1 OR 1';
$sql = 'SELECT * FROM bugs WHERE bug_id ='.$id;
$result = $db->fetchAll($sql);
print_r($result);
------------------------------
% php dangerfetch.php                                            11-07-24 23:53
Array
(
    [0] => Array
        (
            [bug_id] => 1
            [bug_description] => description1
            [bug_status] => open
            [reported_by] => alice
            [assigned_to] => bob
            [verified_by] => bob
        )
 
    [1] => Array
        (
            [bug_id] => 2
...

このように全件取られる。これを「?」を使った記法で置き換えると…

$id = '1 OR 1';
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';
$result = $db->fetchAll($sql, $id);
print_r($result);
------------------------------
Array
(
    [0] => Array
        (
            [bug_id] => 1
            [bug_description] => description1
            [bug_status] => open
            [reported_by] => alice
            [assigned_to] => bob
            [verified_by] => bob
        )
 
)

正しく、というかインジェクションは発生していない。

MDB2の例

$id = '1 OR 1';
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT * FROM bugs ORDER BY bug_id = '.$id;
$result = $mdb2->queryAll($sql);
print_r($result);

これもまあただSQLを実行しているだけなので結果は同じくインジェクションされるのだが、以下のようにすると

$id = '1 OR 1';
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT * FROM bugs ORDER BY bug_id = ?';
$result = $mdb2->prepare($sql)->execute($id)->fetchRow();
print_r($result);
------------------------------
Array
(
    [bug_id] => 2
    [bug_description] => description2
    [bug_status] => closed
    [reported_by] => alice
    [assigned_to] => bob
    [verified_by] => eve
)

全て取られるわけではないが、bug_id = 2が取得される。いずれにせよ、プログラム的に入力値は検証した方がいいだろうし、MDB2に関してはtextで取るか、integerで取るかの指定もできるのでやっておいた方が無難なのかもしれない。

いずれにせよ、インジェクション防止のエスケープは、内容に関してまでは関わらないので、重要なデータであれば事前にエスケープしておくだとか、取り出したデータに対するパーミッションをチェックするだとかいう配慮は必要だろう。

条件付きクエリの単一結果を得る

主キー、またはユニークキーの問い合わせといったような、結果が確実に1行で返ってくるものに関してはそれ相応のメソッドがあるので、ここに記しておく。このような問い合わせは往々にしてよく発生するものであるであるから、全ての結果を問い合わせてから配列0を得るといったようなやり方をするよりスマートに書けるかつ、単一の結果を取得しようとするあたりが明示化できる。

$sql = 'SELECT * FROM bugs WHERE bug_id = ?';
$result = $db->fetchRow($sql, 1);
print_r($result);
// もしくは
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';
$result = $mdb2->prepare($sql)->execute(1)->fetchRow();
-------------------------------
Array
(
    [bug_id] => 1
    [bug_description] => description1
    [bug_status] => open
    [reported_by] => alice
    [assigned_to] => bob
    [verified_by] => bob
)

このようにして取得すると、その行における連想配列がそのまま返ってくるので便利。

MDB2でもっと簡潔に書く場合はqueryRowというメソッドでそのままSQLを渡す事で結果を得る事もできる。この場合は適切にエスケープ/クオートする事が必要。

$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT * FROM bugs WHERE bug_id = '.$mdb2->quote(1, 'integer');
$result = $mdb2->queryRow($sql);
print_r($result);

Zend_DbでもこのようなquoteされたSQLを作る事は出来る(が、割愛。適当にドキュメントペーシをquoteとかで検索してみてください)

limit付きSELECT

いきなりですが、これはZend_Db_Adapter単一の機能では出来ない。ここまで紹介し切れていないが、適切なクエリビルダでもってクエリを生成する事により実現できる。この辺りが「単純に渡されたクエリを実行するだけ」のZend_Db_Adapterと、「少しばかりクエリを加工する」事も目指した(しかし中途半端な所も多々ある)MDB2の、設計思想の違いといっていいだろう。

従って、ここではMDB2のみのコードを提示する。

$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$sql = 'SELECT * FROM bugs ORDER BY bug_id DESC';
$mdb2->setLimit(2);
$result = $mdb2->queryAll($sql);
print_r($result);
...
Array
(
    [0] => Array
        (
            [bug_id] => 3
            [bug_description] => description3
            [bug_status] => bogus
            [reported_by] => eve
            [assigned_to] => alice
            [verified_by] => bob
        )
 
    [1] => Array
        (
            [bug_id] => 2
            [bug_description] => description2
            [bug_status] => closed
            [reported_by] => alice
            [assigned_to] => bob
            [verified_by] => eve
        )
 
)

このあたりの機能は、主にページャと組み合わされるべき所であろうから、あまり深く掘り下げない。以上でReadに関してはとり急ぎ終わり

Create (INSERT)の方法

INSERTからはトランザクションもカマしていきます。

Zend_Dbでやる場合

INSERTやUPDATEはSQL以外で表現しやすいので、Zend_Db_AdapterもMDB2もそれっぽい方法を備えている。もちろん、SQLでINSERT句を使ってqueryを実行する(両者ともquery()というメソッドだが…)事による挿入も可能である。(この場合はもちろん適切に入力値をquoteする事)

Zend_Dbでinsert()する例。

$db->beginTransaction();
$data = array(
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW',
    'created_on'      => '2007-03-22'
);
$db->insert('bugs', $data);
$sql = 'SELECT * FROM bugs';
$result = $db->fetchAll($sql);
print_r($result);
// $db->commit();

$db→commit()を外せばトランザクションがコミットされるわけだが、とりあえず、このような形でインサート出来る。この場合、$dataに渡したvalueは全てquoteされる為、関数を渡すような場合は以下のように特殊な方法を使わなくてはいけない。

$db->beginTransaction();
$data = array(
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW',
    'created_on'      => new Zend_Db_Expr('CURDATE()')
);
$db->insert('bugs', $data);
$sql = 'SELECT * FROM bugs';
$result = $db->fetchAll($sql);
print_r($result);
// $db->commit();

さて、ここで$data配列のキーが指定されていないものはNULLが入っている事が確認できるわけだが、場合によっては自動インクリメントされた主キーの数値が必要な事もある。これはlastInsertId()によって取得可能である。

$db->beginTransaction();
$data = array(
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW',
    'created_on'      => new Zend_Db_Expr('CURDATE()')
);
$db->insert('bugs', $data);
$sql = 'SELECT * FROM bugs';
$result = $db->fetchAll($sql);
print_r($result);
var_dump($db->lastInsertId());

シーケンスをサポートしている場合のRDBMSの場合はそれが利用されるのであるが、いずれにせよポータビリティは高くない方法である事に注意する事。このケースはMySQLのauto_incrementされたプライマリーキー(即ち、NULLを挿入すると自動インクリメントされる)のみしか想定していないが、その他のエンジンで正しく動作する事は保証されていないという事である。ただ、この程度であればエンジンが変更された時に適時修正するパッチを書くのは大した手間では無いであろうとは思われる。が、「どのようなDBエンジンでも動く」というコードは難しいかもしれない。

MDB2でやる場合

MDB2で等価なコードを書こうと思うと、ExtendedモジュールのautoExecute()を利用するのが近いと思われる。ともあれ書いてみよう。

$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$mdb2->loadModule('Extended');
$mdb2->beginTransaction();
// $id = $mdb2->nextId('bug_id');
$table = "bugs";
$fieldData = array (
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW',
    'created_on'      => '2008-11-11'
);
$ret = $mdb2->extended->autoExecute($table, $fieldData, MDB2_AUTOQUERY_INSERT);
$sql = 'SELECT * FROM bugs';
$result = $mdb2->queryAll($sql);
print_r($result);
// $mdb2->commit();

まず、loadModule()というメソッドでExtendedモジュールを呼びこむ必要がある。それと同時に、CURDATE()等のSQL関数は利用できない。どうしても使いたいのであればpreparedステートメントに入れれば出来るようだが、そんな事をするならphpの関数なり何なりでデータを整形した方が早そうである。

この方法にしても、キーが無いものはNULLが挿入されるため、例えばMySQLのauto_incrementフィールドはインクリメントされるわけだが、MDB2は独自にnextId()というシーケンスエミュレーションによってポータビリティを上げる試みが為されている。これをコールする事で、それぞれのエンジンに対応したidが生成され、そのidでもって主キーを更新する事になる。ただし、もちろんこれを利用したならばそのシーケンス機能を使い続けないとインクリメントの扱いがおかしくなる事に留意する必要がある。

簡単に言えば、RDBMSのバックエンドを複数取る必要が「あまり」ないのであれば、それぞれのネイティブなやり方に任せてしまえばいいという事であるが。

「MDB2の流儀」だとプライマリーキー数値のインクリメントを行っておき、その数値を後で挿入するという方式なので、lastInsertIdなどは必要ないのであるが、DBMSネイティブな機能でもってIDをインクリメントした場合などは以下のような方法でキーを抜く事が出来る。

$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$mdb2->loadModule('Extended');
$mdb2->beginTransaction();
// $id = $this->dbo->nextId('bug_id');
$table = "bugs";
$fieldData = array (
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW',
    'created_on'      => '2008-11-11'
);
$ret = $mdb2->extended->autoExecute($table, $fieldData, MDB2_AUTOQUERY_INSERT);
$sql = 'SELECT * FROM bugs';
$result = $mdb2->queryAll($sql);
print_r($result);
var_dump($mdb2->extended->getAfterID($table, 'bug_id'));
// $mdb2->commit();

MDB2のシーケンスエミュレータ

MDB2の設計思想ではそれぞれのDBエンジンが備えている(場合によっては備えていない)主キーインクリメントをシーケンスとしてエミュレートする機能を備えており、MDB2を利用する場合はこれを利用する事が推奨されている。 たとえば今回はMySQLを利用しているが、ここでいうauto_incrementを独自に実装するものと考えてよい。

詳細は以下のURLを参照の事

http://pear.php.net/manual/ja/package.database.mdb2.intro-sequences.php

基本的にはnextId()でIDを取得する。しかしZend_Dbにはこのような機能は(おそらく)無く、それぞれのDBMSの機能を使おうという事になっていると思われる。

MDB2のnextIdを使う場合は、インクリメント専用のテーブルが自動生成される事に注意しなくてはならない。これは実際nextIdを叩いてみて、テーブルが増えている事に着目すれば理解いただけるかと思う。

Update (UPDATE)の方法

ReadをすっとばしてUpdateを見ていく事にする。

INSERTの項でも書いたのだが、INSERTもUPDATEも構造的には近いので(SQLで表現すると大分違うのではあるが)、コードの組み立て的には似たようなものを持っている。つまり、本質的にSQLを書かなくてもいい方法がぞれぞれの方法に提供されているという事でもある。

Zend_Dbを使う場合

MDB2を使う場合

zend_db_vs_mdb2.txt · 最終更新: 2012/04/07 10:32 (外部編集)
www.chimeric.de Creative Commons License Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0