mysqli のラッパクラスを書いた

prepare(プリペアドステートメント)どころかプレースホルダもなしにプログラマーがSQLを書かなければならない某社の自称フレームワーク()に辟易して、ナントカさんオススメの MDB2 を試したが MySQL-4.1.x ではどうも対応していないようで、とても悲しい目に遭ってしまいました。
さらにPDOもいまいちな評判が目に付いたので、仕方なくmysql()を使うべきかなやんていたところ、mysqli()なるものをphpマニュアルで見つけたので、使ってみることにしました。
ところが！ネイティブにprepare出来るのはよいけれど、prepare()の前に値の型をバインドしないといけない。fetch()の前に値を入れる変数をバインドしないといけない。こんだけ面倒なのに、名前付きホルダーが使えない。すっげー面倒くさい縛りがある。
既に MDB2 を使うつもりで名前付きホルダーの前提でクエリーリストをつくってしまっていた。

というわけで仕方なく myqli を名前付きホルダーっぽい使い勝手の出来るラッパクラスを書きました。
まだ全然使っていないので、バグもあるだろうし、機能的にも足りていないでしょうが、最低限動くようにはなっているはずです。

んで、例によって例のごとく。ライセンス表記はなく、著作権は私にあり、コピペして使うのは自由だが、配布していたりはしないのです。

サンプルコード

<?php
require_once('YamaDB.class.php');

    $db = new YamaDB( array( 'host'      => 'localhost',
                            'user'      => 'username',
                            'passsword' => 'passwd',
                            'db_name'   => 'dbname',
                            'port'      => 5432,
                            'charset'   => 'ujis',,
                            'socket'    => '/tmp/my.sock') );
    $sql = " SELECT server_id as id, count(*) as calls, sum(Price) as price ";
    $sql .= "FROM cdr WHERE End_tm >= :startDate AND End_tm < :endDate ";
    $sql .= "AND server_id = :serverId ";
    $sql .= "GROUP BY server_id ORDER BY server_id asc";
  
    $types = array( 'startDate' => 's', 'endDate' => 's', 'serverId' => 'i' );
  
    $vars = array( 'serverId' => 6800002345,
                   'startDate' => '2012-04-05 12:00:00',
                   'endDate' => '2012-04-06 00:00:00'
                 );

  try {
    $db->prepare( $sql, $types, $vars );
    $db->execute();
    $res = $db->fetch();
  }
  catch( Exception $e ) {
    die( $e->getMessage() );
  }

• 後で読む：当たり前だけど同じようなのがいくつかありますね。そっかそっか、忘れてたけど、世の中的にはMDB2で解決できる話なんだよね。
  • http://castor.s26.xrea.com/blog/2008/12/15
  • http://sourceforge.jp/projects/nucleus-jp/lists/archive/developers/2012-April/000905.html
  • http://sourceforge.jp/projects/nucleus-jp/lists/archive/developers/2012-April/000863.html

設定のもんだいだったのかな？MDB2

• PHP と MySQL と サーバサイド プリペアードステートメント - do_aki's log

<?php /* -*-java-*- */
require_once('Lib/PEAR.php');
defined('_DB_RDBMS_')      or define('_DB_RDBMS_', 'mysqli');
defined('_DB_USERNAME_')   or define('_DB_USERNAME_', 'user-name');
defined('_DB_PASSWD_')     or define('_DB_PASSWD_', 'password');
defined('_DB_HOST_')       or define('_DB_HOST_', 'localhost');
defined('_DB_PORT_')       or define('_DB_PORT_', '3306');
defined('_DB_DBNAME_')     or define('_DB_DBNAME_', 'database-name');
defined('_DB_SOCKET_')     or define('_DB_SOCKET_', '/tmp/my.sock' );
defined('_DB_CHARSET_')    or define('_DB_CHARSET_', 'ujis');


/**
 * class YamaDB
 *  Yamada,Inc. Database access class by mysqli.
 * <code>
 * try {
 *   $db = new YamaDB( array( 'host'      => 'localhost',
 *                           'user'      => 'username',
 *                           'passsword' => 'passwd',
 *                           'db_name'   => 'dbname',
 *                           'port'      => 5432,
 *                           'socket'    => '/tmp/my.sock') );
 *   $sql = " SELECT server_id as id, count(*) as calls, sum(Price) as price ";
 *   $sql .= "FROM cdr WHERE End_tm >= :startDate AND End_tm < :endDate ";
 *   $sql .= "AND server_id = :serverId ";
 *   $sql .= "GROUP BY server_id ORDER BY server_id asc";
 * 
 *   $types = array( 'startDate' => 's', 'endDate' => 's', 'serverId' => 'i' );
 * 
 *   $vars = array( 'serverId' => 6800002345,
 *                  'startDate' => '2012-04-05 12:00:00',
 *                  'endDate' => '2012-04-06 00:00:00'
 *                );
 *   $db->prepare( $sql, $types, $vars );
 *   $db->execute();
 *   $res = $db->fetch();
 * }
 * catch( Exception $e ) {
 *   die( $e->getMessage() );
 * }
 * </code>
 *
 * @package Yamada
 * @auther  k5959k+yamada@gmail.com
 */
class YamaDB {
  
  /**
   * DB connection resource "mysqli" object-instance
   *
   * @access private
   * @var object
   */
  var $_mysqli;
  
  /**
   * mysqli statement "mysqli_stmt" object-instance
   *
   * @access private
   * @var object
   */
  var $_stmt;
  
  /**
   * mysqli DB connect options
   * 
   * @access private
   * @var array
   */
  var $_dbOpt;
  
  
  /**
   * Constractor YamaDB class.
   * 
   * @access public
   * @param  mixed  string=dsn, array=db connection values,
   *                or NULL is use defined value
   * @return object $this->_mysqli;
   */
  public function __construct( $dbOptions=null ) {
    
    try {
      
      $user = isset($opt['user']) ? $opt['user'] : _DB_USERNAME_;
      $passwd = isset($opt['password']) ? $opt['password'] : _DB_PASSWD_;
      $host = isset($opt['host']) ? $opt['host'] : _DB_HOST_;
      $port = isset($opt['port']) ? $opt['port'] : _DB_PORT_;
      $dbname = isset($opt['dbname']) ? $opt['dbname'] : _DB_DBNAME_;
      $socket = isset($opt['socket']) ? $opt['socket'] : _DB_SOCKET_;
      $charset = isset($opt['charset']) ? $opt['charset'] : _DB_CHARSET_;
      
      $this->init( array( 'host'     => $host,
                          'user'     => $user,
                          'password' => $passwd,
                          'port'     => $port,
                          'dbname'   => $dbname,
                          'socket'   => $socket,
                          'charset'  => $charset )
                   );
      
      $this->_mysqli = new mysqli();
      $this->connect();
      
    } catch ( Exception $e ) {
      throw $e;
    }
  }
  
  
  /**
   * エラーチェックメソッド
   * エラーが発生していれば直近のエラーメッセージとエラーコードを
   * Exception に入れて投げる。
   * 
   * @access public
   * @throws Exception
   */
  function isError() {
    if ( !empty($this->_mysqli->connect_error) ) {
      throw new Exception( $this->_mysqli->connect_error,
                           $this->_mysqli->connect_errono );
    }
    if ( !empty($this->_mysqli->error) ) {
      throw new Exception( $this->_mysqli->error,
                           $this->_mysqli->errono );
    }
    if ( !empty($this->_stmt->errono) ) {
      throw new Exception( $this->_stmt->error,
                           $this->_stmt->errono );
    }
  }
  
  
  /**
   * set mysqli db connect options.
   * 
   * @access public
   * @param  array  $opt  array( 'user' => var1, 'password' => var2,
   *                             'host' => var3, 'port' => var4,
   *                             'dbname' => var5, 'socket' => var6,
   *                             'socket' => var7, 'charset' => var8 )
   */
  public function init( $opt=null ) {
    
    $user = isset($opt['user']) ? $opt['user'] : $this->_dbOpt['user'];
    $passwd = isset($opt['password'])
      ? $opt['password'] : $this->_dbOpt['password'];
    $host = isset($opt['host']) ? $opt['host'] : $this->_dbOpt['host'];
    $port = isset($opt['port']) ? $opt['port'] : $this->_dbOpt['port'];
    $dbname = isset($opt['dbname']) ? $opt['dbname'] : $this->_dbOpt['dbname'];
    $socket = isset($opt['socket']) ? $opt['socket'] : $this->_dbOpt['socket'];
    $charset = isset($opt['charset'])
      ? $opt['charset'] : $this->_dbOpt['charset'];
    
    $this->_dbOpt = array( 'host'     => $host, 
                           'user'     => $user,
                           'password' => $passwd,
                           'port'     => $port,
                           'dbname'   => $dbname,
                           'socket'   => $socket,
                           'charset'  => $charset );
    
  }
  
  
  /**
   * mysqli::real_connect() 
   * 
   * @access public
   * @throws Exception
   */
  public function connect() {
    
    try {
      $this->_mysqli->init();
      $this->isError();
      $this->_mysqli->real_connect( $this->_dbOpt['host'],
                                    $this->_dbOpt['user'],
                                    $this->_dbOpt['password'],
                                    $this->_dbOpt['dbname'],
                                    $this->_dbOpt['port'],
                                    $this->_dbOpt['socket']
                                    );
      $this->isError();
      $this->_mysqli->set_charset( $this->_dbOpt['charset'] );
      $this->isError();
      
    } catch ( Exception $e ) {
      throw $e;
    }
  }
  
  
  /**
   * オートコミットの on/off
   * 
   * @access public
   * @param  bool   $mode オンならtrue , オフなら false
   * @return bool
   * @throws Exception
   */
  public function autoCommit( $mode ) {
    try {
      $res = $this->_mysqli->autocommit( $mode );
      $this->isError();
    } catch ( Exception $e ) {
      throw $e;
    }
    return $res;
  }
  
  /**
   * トランザクションのコミット
   *
   * @access public
   * @return bool
   * @throws Exception
   */
  public function commit() {
    try{
      $res = $this->_mysqli->commit();
      $this->isError();
    } catch ( Exception $e ) {
      throw $e;
    }
    return $res;
  }
  
  /**
   * トランザクションのロールバック
   *
   * @access public
   * @return bool
   * @throws Exception
   */
  public function rollback() {
    try{
      $res = $this->_mysqli->rollback();
      $this->isError();
    } catch ( Exception $e ) {
      throw $e;
    }
    return $res;
  }
  
  
  /**
   * mysqli::prepare() プリペアクエリーとプレースホルダーの型と値を引数とする。
   * これが通れば mysqli::execute() できる。
   * 
   * @see self::checkNameHolders, self::bindingParams, self::replaceHolders,
   *      mysqli::prepare, mysqli_stmt::bind_param
   * @access public
   * @param  string $sql  プリペアSQL ? もしくは :name をつかったクエリー。
   *                      混在は出来ません。
   * @param  mixed  $types type='idsss' 型とホルダーの位置を対応させてつくる。
   *                       もしくは名前付きホルダーしたハッシュ配列を用意する。
   *                       array( 'param'=>'type', ... )
   *                         type = 'i'(int),'d'(double),'s'(string),'b'(blob)
   * @param  array $values  array( val1, val2, ... )
   *                       もしくは名前付きホルダーに対応したハッシュ配列
   *                        array( 'param1'=>val1, 'param2'=>val2,...)
   * @return object $this->_stmt
   * @throws Exception
   */
  public function prepare( $sql, $types, $values ) {
    
    try {
      
      /*
       * 名前付きホルダーの場合は、ホルダーと対応するように
       * プリペア用クエリーと型配列と値配列を再生成する。
       */
      if ( self::checkNameHolders($sql) ) {
        $values = self::bindingParams( $types, $values );
        list( $sql, $types, $values ) = self::replaceHolders( $sql, $values );
      }
      
      if ( false === ($this->_stmt=$this->_mysqli->prepare($sql)) ) {
        $this->isError();
        throw new Exception( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                             " Error ocurred: error='".$this->_mysqli->error."' mysqli::prepare
(${sql})" );
      }
      
      /*
       * mysqli_stmt::bind_param() に引数可変で渡せるような細工
       */
      $_bind_params = array ($types );
      foreach ( $values as $k => $v ) {
        $_bind_params[] = &$values[$k];
      }
      $res = call_user_func_array( array($this->_stmt, 'bind_param'),
                                   $_bind_params );
      if ( false === $res ) {
        throw new Exception( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                             " Error ocurred: error='".$this->_stmt->error."' mysqli_stmt::bind
_param()\n" );
      }
      
      return $this->_stmt;
      
    } catch ( Exception $e ) {
      throw $e;
    }
    
  }
  
  
  /**
   * mysqli_stmt::execute() の実行。
   *
   * @access public
   * @return mixed   成功なら true を返す。
   */
  public function execute() {
    
    try {
      if ( !$this->_stmt->execute() ) {
        $this->isError();
        throw new Exception ( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                              " Error ocurred: error='".$this->_stmt->error."' mysqli_stmt::exe
cute()\n" );
      }
    } catch ( Exception $e ) {
      throw $e;
    }
    return true;
  }
  
  
  /**
   * 直近にしたステートメントで変更・削除・あるいは追加された行の総数を返す。
   *
   * @access public
   * @return int    $this->_stmt->affected_rows
   */
  public function affectedRows () {
    return $this->_stmt->affected_rows;
  }
  
  
  /**
   * クエリー結果を取得する。
   * SQLからの戻りのカラム名相当をキーとするハッシュ配列にして取得します。
   * SELECT, SHOW, DESCRIBE, EXPLAIN の結果を取得するメソッド。
   * 
   * @see mysqli_stmt::store_result , mysqli_stmt::result_metadata , 
   *      mysqli_stmt::bind_result , mysqli_stmt::fetch
   * @access public
   * @return array  $array カラム名をキーとするハッシュ配列を取得する。
   *                $array[2]['hogehoge'] な感じでアクセスできる。
   * @throws Exception 
   */
  public function fetch() {
    
    $vars = array();
    $data = array();
    
    try {
      
      if ( !$this->_stmt->store_result() ) {
        $this->isError();
        throw new Exception ( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                              " Error ocurred: error='".$this->_stmt->error."' mysqli_stmt::sto
re_result()\n" );
      }
      
      if ( false === ($meta=$this->_stmt->result_metadata() ) ) {
        $this->isError();
        throw new Exception ( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                              " Error ocurred: error='".$this->_stmt->error."' mysqli_stmt::res
ult_metadata()\n" );
      }
      
      while( $field = $meta->fetch_field() ) {
        $vars[] = &$data[$field->name];
      }
      
      $res = call_user_func_array( array($this->_stmt, 'bind_result' ),
                                   $vars );
      if ( false === $res ) {
        $this->isError();
        throw new Exception( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                             " Error ocurred: error='".$this->_stmt->error."' mysqli_stmt::bind
_result()\n" );
      }
      
      $i=0;
      while ( $this->_stmt->fetch() ) {
        $array[$i] = array();
        foreach ( $data as $k => $v ) {
          $array[$i][$k] = $v;
        }
        ++$i;
      }
      
      /*
      if ( $this->_stmt->close() ) {
        $this->isError();
        throw new Exception ( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                              " Error ocurred: error='".$this->_stmt->error."' mysqli_stmt::clo
se()\n" );
      }
      */
      
    } catch ( Exception $e ) {
      throw $e;
    }
    
    return $array;
  }
  
  
  /**
   * mysqli_stmt を開放する。
   *
   * @access public
   * @return bool
   * @throws Exception
   */
  public function free() {
    if ( !$this->_stmt->close() ) {
      $this->isError();
      throw new Exception ('FILE:'.__FILE__.' LINE:'.__LINE__.
                           " Error ocurred: error='".$this->_stmt->error."' mysqli_stmt::close(
)\n" );
    }
    
    return true;
  }
  
  
  /**
   * mysqli を開放する。
   *
   * @access public
   * @return bool
   * @throws Exception
   */
  public function close() {
    if ( !$this->_mysqli->close() ) {
      $this->isError();
      throw new Exception ('FILE:'.__FILE__.' LINE:'.__LINE__.
                           "Error ocurred: error='".$this->_mysqli->error."' mysqli::close()\n"
 );
    }
    
    return true;
  }
  
  /**
   * prepare クエリーのホルダーが名前付きホルダーかをチェックする
   * 
   * @access public
   * @param  string  $sql 名前付きホルダー :name1 を含むクエリーかチェックする
   * @return array   ホルダー名を出現順に配列で返す。無い場合は false
   */
  static public function checkNameHolders ( $sql ) {
    
    if ( preg_match_all( '/.*?:([\S]+)/m', $sql, $matches,
                         PREG_PATTERN_ORDER ) ) {
      if ( isset($matches[1]) ) {
        return $matches[1];
      }
    }
    return false;
  }
  
  
  /**
   * 名前付きホルダーを mysqli で使用可能な名前なし ? に変換する。
   * ? の出現順に値配列を並べ替えた値配列と型文字列も一緒に返す。
   * 
   * @access public
   * @param  string $sql 名前付きホルダーのSQL。
   * @param  array  $values 名前と対応した型と値の入った配列。
   * @param  bool   $strict 名前付きホルダーと値の数が一致しているかチェックする。
   * @return array  mysqli_stmt::bind_param() の引数 list( クエリ, 値, 型 ) を返す。
   */
  static public function replaceHolders( $sql, $values, $strict=false ) {
    
    if ( false === $holders=self::checkNameHolders($sql) ) {
      throw new Exception( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                           ' sql="'.$sql.'" has no name holders' );
    }
    
    $_sql_ = $sql;
    $holder_types = '';
    $replaced_values = array();
    foreach ( $holders as $name ) {
      $pattern = array( '/:'.$name.'[,\s]/m', '/:'.$name.'$/m' );
      $_sql_ = preg_replace( $pattern,
                             ' ? ',
                             $_sql_ );
      if ( isset($values[$name]) ) {
        $holder_types .= $values[$name]['type'];
        $replaced_values[] = $values[$name]['value'];
      }
      else {
        throw new Exception( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                             " Not match SQL query's place holder & array values '${sql}'." );
      }
    }
    
    if ( $strict ) {
      if ( count($values) != count($replaced_values) ) {
        throw new Exception( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                             " Use strict case:Not match place holder & array values '${sql}'."
 );
      }
    }
    
    return array( $_sql_, $holder_types, $replaced_values );
  }
  
  /**
   * カラム名と型の対応表を定義することで自動で下のこの形の配列をつくる
   * $values = array( 'holderName' => array( 'type' => 'i or d or s or b',
   *                                         'value' => 'real_value' ), ... )
   *
   * @access public
   * @param  array  $types array( 'paramName' => 'type', ... )
   *                type = 'i'(integer), 'd'(double), 's'(string), 'b'(blob)
   * @param  array  $values array( 'paramName' => value, ... )
   * @return array  $binds  型と値を統合した配列を返す。
   */
  static public function bindingParams( $types, $values ) {
    
    /*
     * 型を定義した配列 と 値の配列 を比較して
     * ホルダー名と一致しているかチェックする。
     * こころ: $_POST をそのまま渡してしまうのを排除している。
     */
    $k_type = array_keys($types);
    sort($k_type);
    $k_vals = array_keys($values);
    sort($k_vals);
    if ( $k_type != $k_vals ) {
      throw new Exception( 'FILE:'.__FILE__.' LINE:'.__LINE__.
                           " Un-matched params TYPES & VALUES.\n" );
    }
    
    $binds = array();
    foreach ( $types as $k => $type ) {
      $binds[$k] = array( 'type'=>$type, 'value'=>$values[$k] );
    }
    return $binds;
  }
}
/**

$sql = "select * from customer where customer_cd = :customer_cd";
$types = array( 'customer_cd' => 's', 'ccd' => 's' );
$values = array( 'customer_cd' => '100000000109', 'ccd' => 'C02' );


$dbopt = array( 'usr' => 'user-name',
                'password' => 'password',
                'host' => 'localhost',
                'port' => 3306,
                'dbname' => 'database-name',
                'socket' => '/tmp/my.sock',
                'charset' => 'ujis' );
$dbopt = array();
try {
  $db = new YamaDB($dbopt);
  $db->prepare( $sql, $types, $values );
  $db->execute();
  $res = $db->fetch();
 } catch ( Exception $e ) {
  print_r( $e->getMessage() );
 }
var_dump($res);
exit();



$db = new mysqli('localhost', 'user-namet', 'password', 'database-name',
                 3306, '/tmp/my.sock');
$sql = "select name from customer where customer_cd = ? ";
if ( !($stmt=$db->prepare($sql)) ) {
  var_dump($db->error);
 }
 else {
   echo "Prepared!! $sql\n";
 }
$v = '100000000109';
$stmt->bind_param('s',$v);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
echo "name=".$name."\n";
var_dump($db);
var_dump($stmt);
exit();

*/