抽象 - Unknown::Programming

2007-06-13 抽象

PHP版SQL::Abstract

PHP

Perlerなら皆さんご存知のSQL::Abstractですが、それのPHP版です。

一度SQL::Abstractになれちゃうと便利すぎて離れなれないっすよねぇ。

ってことで2年ほど前に作ったやつですが、ほんの少し手直ししたので公開します。

→SQL_Abstract-0.01.tar.gz

使い方は基本的にはSQL::Abstractと同じ。

ただし、PHPは連想配列と配列を完全に区別できないので使い方に若干の違いがあるのでご注意を。特に「,」と「=>」で動作が変わるので注意が必要。

WHERE句の説明

最も簡単に説明するには多くの例を見せることだ。それぞれ$where連想配列を提示した後、それを以下のように使ったものとする


 list($stmt,$bind) = $sql->where($where);

whereメソッドは最適化のためリファレンスを返す。

よって配列で受け取る方がより効果的と言えるだろう。（listのバグのためリファレンスにならないので）


 $data = $sql->where($where);
 $stmt =& $data[0];
 $bind =& $data[1];

さあ、始めよう。まずはシンプルな連想配列からだ。


    $where  = array(
        'user'   => 'nwiger',
        'status' => 'completed'
    );

key = valのSQL文に変換される


    $stmt = "WHERE user = ? AND status = ?";
    $bind = array('nwiger', 'completed');

処理を終えるのに共通なのは、あるフィールドが代入可能な値の配列を持っているということだ。

そのためには、単に連想配列に配列を指定するだけでよい。


    $where  = array(
        'user'   => 'nwiger',
        'status' => array('assigned', 'in-progress', 'pending'),
    );

この単純なコードは、次の結果になる


    $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
    $bind = array('nwiger', 'assigned', 'in-progress', 'pending');

異なるタイプの比較演算子を指定したいなら、連想配列を使う：


    $where  = array(
        'user'   => 'nwiger',
        'status' => array( '!=' => 'completed' )
    );

こうなる


    $stmt = "WHERE user = ? AND status != ?";
    $bind = array('nwiger', 'completed');

このとき、必ず「=>」を使ってください。「,」を使うと配列と見なされてしまうからです。


    $where  = array(
        'user'   => 'nwiger',
        'status' => array( '!=' , 'completed' ) // 「,」はダメ！
    );

比較演算子の値を配列にするとその文のORをつないでくれる


    'status' => array( '!=' => array('assigned', 'in-progress', 'pending'))

こうなる


    "WHERE status != ? OR status != ? OR status != ?"

また、連想配列には複数のペアを含められる。その場合、その要素は ANDに拡張される


    $where  = array(
        'user'   => 'nwiger',
        'status' => array( '>' => 0 , '<' => 10 )
    );
    $stmt = "WHERE user = ? AND (status > ? AND status < ?)";
    $bind = array('nwiger', 0, 10);

同じ比較演算子を複数指定したい場合はさらに配列に埋め込むことで可能となります。その場合、その要素はORとなります。


    $where  = array(
        'user'   => 'nwiger',
        'status' => array( array( '!=' => 0 ) , array( '!=' => 10 ) )
    );
    $stmt = "WHERE user = ? AND (status != ? OR status != ?)";
    $bind = array('nwiger', 0, 10);

このケースではORをANDに変更する場合、下記のように書けます。


    $where  = array(
        'user'   => 'nwiger',
        'status' => array( '-and' => array(array( '!=' => 0 ) , array( '!=' => 10 )) )
    );
    $stmt = "WHERE user = ? AND (status != ? AND status != ?)";
    $bind = array('nwiger', 0, 10);

and,-orについてのいくつかの例を紹介します。


 // 同じ意味
 'status' => array( '>' => 0 , '<' => 10 )
 'status' => array( '-and' => array(array( '>' => 0),array('<' => 10 )) )
 
 // 同じ意味
 'status' => array('assigned', 'in-progress')
 'status' => array( '=' => array('assigned', 'in-progress') )
 'status' => array( array( '=' => 'assigned' ), array( '=' => 'in-progress') )
 'status' => array( '-or' => array( array( '=' => 'assigned' ), array( '=' => 'in-progress') ) )

次に、ネスト構造のWHERE句を作りたい場合の書き方を説明する。下記のような結果を期待する場合、


    $stmt = WHERE user = ? AND ( workhrs > ? OR geo = ? )
    $bind = array('nwiger', '20', 'ASIA');

これは-nestという構文を使えば実現できる。


    $where = array(
         'user' => 'nwiger',
        '-nest' => array( array('workhrs' => array('>' => 20)), array('geo' => 'ASIA') ),
    );

特殊な比較演算子として下記のものをサポートしている。


 -in
 -like
 -not_like
 -between
 -not_between
 -inject

inの例


    $where  = array(
        'status'   => 'completed',
        'reportid' => array( '-in' => array(567, 2335, 2))
    );

こうなる


    $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
    $bind = array('completed', '567', '2335', '2');

not_betweenの例


   $where  = array(
        'user'   => 'nwiger',
        'completion_date' => array(
           '-not_between' => array('2002-10-01', '2003-02-06')
        )
    );

こうなる


  WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )

ここまでで、いかにして複数の条件がANDで結びつくかをみてきた。

しかし、異なる条件を連想配列内に置いて、それからそれらの連想配列を配列にすることで、この動作を変えることができる。例えば：


    $where = array(
        array(
            'user'   => 'nwiger',
            'status' => array('pending', 'dispatched'),
        ),
        array(
            'user'   => 'robot',
            'status' => 'unassigned',
        )
    );

このデータ構造は次のようになる：


    $stmt = "WHERE ( user = ? AND ( status = ? OR status = ? ) )
                OR ( user = ? AND status = ? ) )";
    $bind = array('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');

時には文字通りのSQL文だけが必要となるだろう。もし字句通りに SQLを含ませたいなら、-injectを指定する。つまり：


    $inn = 'is not null';
    $where = array(
        'priority' => array( '<' => 2 ),
        'requestor' => array( '-inject' => $inn )
    );

こうなる：


    $stmt = "WHERE priority < ? AND requestor is not null";
    $bind = array('2');

最後に、値がnull値の場合は少し特殊で、!=はIS NOT NULL、それ以外の比較演算子の場合はIS NULLになる。


    $where = array(
        'priority' => array( '<' => 2 ),
        'requestor' => array( '!=' => null ),
    );
    
    $stmt = "WHERE priority < ? AND requestor IS NOT NULL";
    $bind = array('2');

とまあこんな感じです。ほんとーにarray()うざいですねぇ。[]とかで書けたらどんなに楽か・・・。

実装は2年前ということもありカナリキチャナイのであまり見ないようにして下さいorz

ソースの文字コードは何にするか迷ったんだけどとりあえずShift_JISで書いてます。

一応PHP4で動作確認はしてます。PHP5でも動くとは思いますがよくわかりません＞＜。

あとバグは存在します。きっと必ず存在します。使用は自己責任で。