Changeset 20492

Timestamp:

10/02/08 18:12:10 (3 months ago)

Author:

nowelium

Message:

mysql の procedure サポートは終わり。
General error: 2014 Cannot execute queries while other unbuffered queries are active. とかは、PDOのPDO::MYSQL_ATTR_USE_BUFFERED_QUERY をいくら設定してもダメなので、少し逃げ。一端切断すればなんとかなるけど、Datasource設定とかは管理外なので、やらない。
multi rows は 2次元配列で。

Location:

lang/php/misc/Hermit

Files:

• src/Hermit/HermitParam.php (modified) (2 diffs)
• src/Hermit/annote/HermitAnnoteConst.php (modified) (1 diff)
• src/Hermit/command/HermitProcedureCommand.php (modified) (1 diff)
• src/Hermit/creator/HermiSetupSqlCreator.php (added)
• src/Hermit/creator/HermitProcedureCallSqlCreator.php (modified) (1 diff)
• src/Hermit/meta/HermitMySQLDatabaseMeta.php (modified) (5 diffs)
• src/Hermit/meta/HermitProcedureInfo.php (modified) (2 diffs)
• src/Hermit/parameter/HermitMySqlProcedureParameter.php (modified) (1 diff)
• src/Hermit/parameter/HermitProcedureParameter.php (modified) (1 diff)
• src/Hermit/resultset/HermitDefaultResultSet.php (modified) (1 diff)
• src/Hermit/resultset/HermitIteratorMultiResultSet.php (added)
• src/Hermit/resultset/HermitIteratorResultSet.php (added)
• src/Hermit/resultset/HermitMySqlProcedureResultSet.php (modified) (2 diffs)
• src/Hermit/resultset/HermitProcedureIteratorResultSet.php (added)
• src/Hermit/resultset/HermitProcedureResultSet.php (modified) (2 diffs)
• src/Hermit/resultset/HermitProcedureResultSetFactory.php (modified) (1 diff)
• src/Hermit/statement/HermitDefaultStatement.php (added)
• src/Hermit/statement/HermitProcedureStatementBuilder.php (modified) (3 diffs)
• src/Hermit/statement/HermitSetupStatementBuilder.php (added)
• src/Hermit/statement/HermitStatement.php (modified) (1 diff)
• src/Hermit/statement/HermitStatementBuilder.php (modified) (2 diffs)
• test/HermitProcedure_mysqlTest.php (modified) (3 diffs)
• test/resource/procedure-mysql.sql (modified) (3 diffs)
• test/resource/procedure-mysql.sql.php (modified) (9 diffs)

lang/php/misc/Hermit/src/Hermit/HermitParam.php

@@ -5 +5 @@
  */
 class HermitParam extends stdClass {
+    public function set($name, $value){
+        $this->$name = $value;
+    }
+    public function get($name){
+        return $this->$name;
+    }
     public function getPropertyNames(){
         $names = array();
@@ -12 +18 @@
         return $names;
     }
+    public function __toString(){
+        $tos = array();
+        foreach($this as $name => $value){
+            $tos[] = $name . '=>' . $value;
+        }
+        return __CLASS__ . ' {' . join(',', $tos) . '}';
+    }
 }

lang/php/misc/Hermit/src/Hermit/annote/HermitAnnoteConst.php

@@ -11 +11 @@
     const FILE_SUFFIX = '_FILE';
     const PROCEDURE_SUFFIX = '_PROCEDURE';
-    const DELEGATE_SUFFIX = '_DELEGATE';
     const VALUE_TYPE_SUFFIX = '_VALUE_TYPE';
 

lang/php/misc/Hermit/src/Hermit/command/HermitProcedureCommand.php

@@ -21 +21 @@
         $this->annote = $annote;
     }
-
     public function execute(PDO $pdo, array $parameters){
+        if($this->sqlCreator instanceof HermiSetupSqlCreator){
+            if($this->sqlCreator->hasSetupSql()){
+                $setupBuilder = new HermitSetupStatementBuilder($this->method, $this->annote, $this->sqlCreator);
+                $setupStatement = $setupBuilder->build($pdo);
+                $setupStatement->execute($parameters);
+            }
+        }
+        
         $builder = new HermitProcedureStatementBuilder($this->method, $this->annote, $this->sqlCreator);
         $stmt = $builder->build($pdo);
         $stmt->execute($parameters);
         $rs = HermitProcedureResultSetFactory::create($pdo, $stmt->getSqlParameter());
+//
+//        to pdo_mysql cause: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.
+//        if($rs instanceof HermitParameterBind){
+//            $rs->bindParameter($pdo, $parameters);
+//        }
+//        return $rs->execute($stmt, $this->type);
+//
+        $returnValue = $rs->execute($stmt, $this->type);
         if($rs instanceof HermitParameterBind){
             $rs->bindParameter($pdo, $parameters);
         }
-        return $rs->execute($stmt, $this->type);
+        return $returnValue;
     }
 }

lang/php/misc/Hermit/src/Hermit/creator/HermitProcedureCallSqlCreator.php

@@ -4 +4 @@
  * @author nowelum
  */
-class HermitProcedureCallSqlCreator implements HermitSqlCreator {
+class HermitProcedureCallSqlCreator implements HermitSqlCreator, HermiSetupSqlCreator {
     private $sql;
+    private $setupSql;
     public function initialize(PDO $pdo, ReflectionMethod $method, HermitAnnote $annote){
         $procedureName = $annote->getProcedure($method);
         $dbMeta = HermitDatabaseMetaFactory::get($pdo);
         $info = $dbMeta->getProcedureInfo($procedureName);
-        $parameterNames = $info->getParamNames();
-        $sql = 'CALL';
-        $sql .= ' ';
-        $sql .= $procedureName;
-        $sql .= '(';
-        foreach($parameterNames as $parameterName){
-            $sql .= '/*' . $parameterName . '*/';
-            $sql .= '"' . $parameterName . '"';
-            $sql .= ',';
-        }
-        $sql = substr($sql, 0, -1);
-        $sql .= ')';
-        $this->sql = $sql;
+
+        $this->setupSql = self::generateSetupSql($info);
+        $this->sql = self::generateCallSql($info);
     }
     public function createSql(){
         return $this->sql;
     }
+    public function createSetupSql(){
+        return $this->setupSql;
+    }
+    public function hasSetupSql(){
+        return null !== $this->setupSql;
+    }
+    protected static function generateCallSql(HermitProcedureInfo $info){
+        $parameterNames = $info->getParamNames();
+        $callSql = 'CALL';
+        $callSql .= ' ';
+        $callSql .= $info->getName();
+        $callSql .= '(';
+        foreach($parameterNames as $parameterName){
+            $callSql .= '/*' . $parameterName . '*/';
+            $callSql .= '"' . $parameterName . '"';
+            $callSql .= ',';
+        }
+        $callSql = substr($callSql, 0, -1);
+        $callSql .= ')';
+        return $callSql;
+    }
+    protected static function generateSetupSql(HermitProcedureInfo $info){
+        $begin = false;
+        $parameterNames = $info->getParamNames();
+
+        $setupSql = 'SET';
+        $setupSql .= ' ';
+        foreach($parameterNames as $parameterName){
+            if($info->typeofIn($parameterName)){
+               continue;
+            }
+            $begin = true;
+            $setupSql .= '@' . $parameterName;
+            $setupSql .= ' = ';
+            $setupSql .= '/*' . $parameterName . '*/';
+            $setupSql .= '"' . $parameterName . '"';
+            $setupSql .= ',';
+        }
+        if(!$begin){
+            return null;
+        }
+        return substr($setupSql, 0, -1);
+    }
 }

lang/php/misc/Hermit/src/Hermit/meta/HermitMySQLDatabaseMeta.php

@@ -5 +5 @@
  */
 class HermitMySQLDatabaseMeta implements HermitDatabaseMeta {
+    
     const USING_DB_NAME_SQL = 'SELECT database()';
     const TABLE_INFO_SQL = 'SELECT * FROM %s LIMIT 0';
     const PROCEDIRE_INFO_SQL = 'SELECT param_list, returns FROM mysql.proc WHERE db = :db AND name = :name';
+    
     private $tables = array();
     private $procedures = array();
@@ -37 +39 @@
             }
         }
+        $stmt->closeCursor();
+        unset($stmt);
         return $this->tables[$table] = $info;
     }
@@ -46 +50 @@
         $stmt->execute(array(':db' => $this->databaseName, ':name' => $procedure));
         $paramList = $stmt->fetchColumn(0);
+        if(false === $paramList){
+            throw new InvalidArgumentException('not found procedure name: ' . $procedure . ' in db: ' . $this->databaseName);
+        }
 
         $info = new HermitProcedureInfo;
+        $info->setName($procedure);
         $chunk = array_map('trim', explode(',', $paramList));
         foreach($chunk as $field){
@@ -78 +86 @@
         }
 
+        $stmt->closeCursor();
+        unset($stmt);
         return $this->procedures[$procedure] = $info;
     }
@@ -84 +94 @@
         $stmt = $this->pdo->prepare(self::USING_DB_NAME_SQL);
         $stmt->execute();
-        return $stmt->fetchColumn(0);
+        $result = $stmt->fetchColumn(0);
+        
+        $stmt->closeCursor();
+        unset($stmt);
+        return $result;
     }
 }

lang/php/misc/Hermit/src/Hermit/meta/HermitProcedureInfo.php

@@ -5 +5 @@
  */
 class HermitProcedureInfo implements Serializable {
+    
     const IN_TYPE = 1;
     const OUT_TYPE = 2;
     const INOUT_TYPE = 3;
 
+    private $procedureName;
     private $names = array();
     private $inout = array();
@@ -29 +31 @@
         }
     }
-
+    
+    public function setName($name){
+        $this->procedureName = $name;
+    }
+    public function getName(){
+        return $this->procedureName;
+    }
     public function addParamName($name){
         $this->names[] = $name;

lang/php/misc/Hermit/src/Hermit/parameter/HermitMySqlProcedureParameter.php

@@ -31 +31 @@
         $param = $value[0];
         foreach($this->bindKeys as $index => $key){
-            $prefix = '';
             if($this->info->typeofIn($key)){
                 $stmt->bindParam(':' . $key, $param->$key);

lang/php/misc/Hermit/src/Hermit/parameter/HermitProcedureParameter.php

@@ -30 +30 @@
     public function bind(PDOStatement $stmt, $value){
         $param = $value[0];
+        $propertyNames = $param->getPropertyNames();
+
         foreach($this->bindKeys as $index => $key){
             $bindKey = ':' . $key;
             if($this->info->typeofIn($key)){
+                if(!in_array($key, $propertyNames)){
+                    throw new InvalidArgumentException('param ' . $param . ' has not propery: ' . $key . ' instatement: ' . $stmt->queryString);
+                }
                 $stmt->bindParam($bindKey, $param->$key);
                 continue;
             }
-            $paramValue = $param->$key;
+            
+            $paramValue = null;
+            if(isset($param->$key)){
+                $paramValue =  $param->$key;
+            }
             if(null === $paramValue){
-                $stmt->bindParam($bindKey, null, PDO::PARAM_NULL | PDO::PARAM_INPUT_OUTPUT);
+                $stmt->bindParam($bindKey, $paramValue, PDO::PARAM_NULL | PDO::PARAM_INPUT_OUTPUT);
                 continue;
             }

lang/php/misc/Hermit/src/Hermit/resultset/HermitDefaultResultSet.php

@@ -7 +7 @@
     public function execute(HermitStatement $stmt, HermitValueType $type){
         $type->apply($stmt);
-        return $stmt->fetch();
+        if($row = $stmt->fetch()){
+            $stmt->closeCursor();
+            return $row;
+        }
+        return null;
     }
 }

lang/php/misc/Hermit/src/Hermit/resultset/HermitMySqlProcedureResultSet.php

@@ -4 +4 @@
  * @author nowelium
  */
-class HermitMySqlProcedureResultSet extends HermitProcedureResultSet {
+class HermitMySqlProcedureResultSet extends HermitProcedureResultSet implements HermitParameterBind {
     /**
      * @override
@@ -18 +18 @@
             $stmt->bindColumn(1, $param->$name);
             $stmt->execute();
-            
+
             $stmt->fetch(PDO::FETCH_BOUND);
             $stmt->closeCursor();

lang/php/misc/Hermit/src/Hermit/resultset/HermitProcedureResultSet.php

@@ -4 +4 @@
  * @author nowelium
  */
-class HermitProcedureResultSet implements HermitResultSet, HermitParameterBind {
+class HermitProcedureResultSet implements HermitResultSet {
     protected $procParameter;
     public function __construct(HermitProcedureParameter $procParameter){
@@ -10 +10 @@
     }
     public function execute(HermitStatement $stmt, HermitValueType $type){
+        if($stmt->columnCount() < 1){
+            $stmt->closeCursor();
+            unset($stmt);
+            return null;
+        }
         $type->apply($stmt);
 
-        if($stmt->columnCount() < 1){
-            return null;
-        }
+//        $rows = array();
+//        while($row = $stmt->fetch()){
+//            $rows[] = $row;
+//        }
+//        if(!$stmt->nextRowset()){
+//            return $rows;
+//        }
+//
+//        $results = array();
+//        $results[] = $rows;
+//        do {
+//            $rows = array();
+//            while($row = $stmt->fetch()){
+//                $rows[] = $row;
+//            }
+//            $results[] = $rows;
+//        } while($stmt->nextRowset());
+//
+//        $stmt->closeCursor();
+//        unset($stmt);
+//        return $results;
 
+        // multiresult so always to array[array]
         $results = array();
         do {
-            $row = $stmt->fetch();
-            if(false === $row){
-                break;
+            $rows = array();
+            while($row = $stmt->fetch()){
+                $rows[] = $row;
             }
-            $results[] = $row;
+            $results[] = $rows;
         } while($stmt->nextRowset());
+        
+        $stmt->closeCursor();
+        unset($stmt);
         return $results;
     }
-    public function bindParameter(PDO $pdo, array $parameter){
-    }
 }

lang/php/misc/Hermit/src/Hermit/resultset/HermitProcedureResultSetFactory.php

@@ -11 +11 @@
         // nop
     }
-    public function create(PDO $pdo, HermitProcedureParameter $parameter){
+    public static function create(PDO $pdo, HermitProcedureParameter $parameter){
         $dbms = HermitDatabaseMetaFactory::getDbms($pdo);
         $rs = null;

lang/php/misc/Hermit/src/Hermit/statement/HermitProcedureStatementBuilder.php

@@ -5 +5 @@
  */
 class HermitProcedureStatementBuilder extends HermitStatementBuilder {
-    private $method;
-    private $annote;
-    private $sqlCreator;
+    protected $method;
+    protected $annote;
+    protected $sqlCreator;
 
     protected static $procedureParameters = array(
@@ -14 +14 @@
 
     public function __construct(ReflectionMethod $method, HermitAnnote $annote, HermitSqlCreator $sqlCreator){
+        parent::__construct($method, $sqlCreator);
         $this->method = $method;
         $this->annote = $annote;
@@ -34 +35 @@
             $parameter = new HermitProcedureParameter($info, $dbms);
         }
-        $sql = $this->sqlCreator->createSql($pdo);
-        $sql = self::preparedSql($parameter, $sql);
-        return new HermitStatement($parameter, $pdo->prepare($sql));
+        
+        $sql = self::preparedSql($parameter, $this->sqlCreator->createSql());
+        return new HermitDefaultStatement($parameter, $pdo->prepare($sql));
     }
     

lang/php/misc/Hermit/src/Hermit/statement/HermitStatement.php

@@ -4 +4 @@
  * @author nowelium
  */
-class HermitStatement {
-    protected $parameter;
-    protected $statement;
-    public function __construct(HermitSqlParameter $parameter, PDOStatement $statement){
-        $this->parameter = $parameter;
-        $this->statement = $statement;
-    }
-    public function getSqlParameter(){
-        return $this->parameter;
-    }
-    public function execute($parameterValue = array()){
-        $this->parameter->bind($this->statement, $parameterValue);
-        return $this->statement->execute();
-    }
-    public function fetch(){
-        $args = func_get_args();
-        $c = count($args);
-        if($c < 1){
-            return $this->statement->fetch();
-        }
-        if($c < 2){
-            return $this->statement->fetch($args[0]);
-        }
-        return call_user_func_array(array($this->statement, 'fetch'), $args);
-    }
-    public function __call($name, $params){
-        return call_user_func_array(array($this->statement, $name), $params);
-    }
+interface HermitStatement {
+    public function getSqlParameter();
+    public function execute($parameterValue = array());
+    public function __call($name, $params);
 }

lang/php/misc/Hermit/src/Hermit/statement/HermitStatementBuilder.php

@@ -5 +5 @@
  */
 class HermitStatementBuilder {
-    const REGEX = '/(\/\*([^\*\/]*)\*\/)(\w+|((\'|")([^(\'|")]*)(\'|")))?/m';
+    const SQL_COMMENT_REGEXP = '/(\/\*([^\*\/]*)\*\/)(\w+|((\'|")([^(\'|")]*)(\'|")))?/m';
     private $method;
     private $sqlCreator;
@@ -16 +16 @@
         $sql = $this->sqlCreator->createSql($pdo);
         $sql = self::preparedSql($parameter, $sql);
-        return new HermitStatement($parameter, $pdo->prepare($sql));
+        return new HermitDefaultStatement($parameter, $pdo->prepare($sql));
     }
 
     protected static function preparedSql(HermitSqlParameter $parameter, $sql){
-        return preg_replace_callback(self::REGEX, array($parameter, 'match'), $sql);
+        return preg_replace_callback(self::SQL_COMMENT_REGEXP, array($parameter, 'match'), $sql);
     }
 

lang/php/misc/Hermit/test/HermitProcedure_mysqlTest.php

@@ -6 +6 @@
     const callIN_OUT_PROCEDURE = 'PROC_IN_OUT';
     public function callIN_OUT(HermitParam $param);
+
+    const callINOUT_PROCEDURE = 'PROC_INOUT';
+    public function callINOUT(HermitParam $param);
+
+    const callIN_OUT_OUT_PROCEDURE = 'PROC_IN_OUT_OUT';
+    public function callIN_OUT_OUT(HermitParam $param);
+
+    const callIN_MULTI_PROCEDURE = 'PROC_IN_MULTIRESULT';
+    const callIN_MULTI_VALUE_TYPE = 'OBJ';
+    public function callIN_MULTI(HermitParam $param);
+
+    const callIN_IN_MULTI_PROCEDURE = 'PROC_IN_IN_MULTIRESULT';
+    const callIN_IN_MULTI_VALUE_TYPE = 'ASSOC';
+    public function callIN_IN_MULTI(HermitParam $param);
+
+    const callIN_OUT_MULTI_PROCEDURE = 'PROC_IN_OUT_MULTIRESULT';
+    const callIN_OUT_MULTI_VALUE_TYPE = 'OBJ';
+    public function callIN_OUT_MULTI(HermitParam $param);
+
+    const callINOUT_MULTI_PROCEDURE = 'PROC_INOUT_MULTIRESULT';
+    const callINOUT_MULTI_VALUE_TYPE = 'OBJ';
+    public function callINOUT_MULTI(HermitParam $param);
+
+    const callOUT_MULTI_PROCEDURE = 'PROC_OUT_MULTIRESULT';
+    const callOUT_MULTI_VALUE_TYPE = 'OBJ';
+    public function callOUT_MULTI(HermitParam $param);
+
+    const callNOSPEC_PARAM_PROCEDURE = 'PROC_NOSPEC_PARAM';
+    const callNOSPEC_PARAM_VALUE_TYPE = 'OBJ';
+    public function callNOSPEC_PARAM(HermitParam $param);
 }
 
@@ -21 +51 @@
     $param->sales = 1000;
     $param->tax = -1;
+
     $result = $hermit->callIN_OUT($param);
 
@@ -27 +58 @@
     $test->ok($result === null);
 }
+{
+    $param = new HermitParam;
+    $param->sales = 5000;
+
+    $result = $hermit->callINOUT($param);
+
+    $test->ok($result === null);
+    $test->is($param->sales, 1000);
+
+    $result2 = $hermit->callINOUT($param);
+    $test->ok($result === null);
+    $test->is($param->sales, 200);
+}
+{
+    $param = new HermitParam;
+    $param->sales = 300;
+
+    $result = $hermit->callIN_OUT_OUT($param);
+    $test->ok($result === null);
+    $test->ok($param->tax !== null);
+    $test->ok($param->total !== null);
+    $test->is($param->tax, 60);
+    $test->is($param->total, 360);
+}
+{
+    $param = new HermitParam;
+    $param->set('IN_MGR', 7902);
+
+    $result = $hermit->callIN_MULTI($param);
+    $test->ok(is_array($result));
+    $test->is(count($result), 3);
+    // same results... 0 to 3
+    {
+        $test->is(count($result[0]), 1);
+        $rs = $result[0][0];
+        $test->is($rs->EMPNO, 7369);
+        $test->is($rs->ENAME, 'SMITH');
+        $test->is($rs->JOB, 'CLERK');
+    }
+    {
+        $test->is(count($result[1]), 1);
+        $rs = $result[1][0];
+        $test->is($rs->EMPNO, 7369);
+        $test->is($rs->ENAME, 'SMITH');
+        $test->is($rs->JOB, 'CLERK');
+    }
+    {
+        $test->is(count($result[2]), 1);
+        $rs = $result[2][0];
+        $test->is($rs->EMPNO, 7369);
+        $test->is($rs->ENAME, 'SMITH');
+        $test->is($rs->JOB, 'CLERK');
+    }
+}
+{
+    $param = new HermitParam;
+    $param->set('IN_MGR_1', '7698');
+    $param->set('IN_MGR_2', '7902');
+
+    $result = $hermit->callIN_IN_MULTI($param);
+