From 3dac210f569ff7847e3fd1795b60295d2a3226fd Mon Sep 17 00:00:00 2001 From: rojaro <> Date: Fri, 6 May 2011 09:51:01 +0000 Subject: fixed #18 --- framework/Data/Common/TDbCommandBuilder.php | 1018 +++++++++++++-------------- 1 file changed, 508 insertions(+), 510 deletions(-) (limited to 'framework/Data') diff --git a/framework/Data/Common/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php index 8619c435..2e772e73 100644 --- a/framework/Data/Common/TDbCommandBuilder.php +++ b/framework/Data/Common/TDbCommandBuilder.php @@ -1,510 +1,508 @@ - - * @link http://www.pradosoft.com/ - * @copyright Copyright © 2005-2010 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common - */ - -/** - * TDbCommandBuilder provides basic methods to create query commands for tables - * giving by {@link setTableInfo TableInfo} the property. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common - * @since 3.1 - */ -class TDbCommandBuilder extends TComponent -{ - private $_connection; - private $_tableInfo; - - /** - * @param TDbConnection database connection. - * @param TDbTableInfo table information. - */ - public function __construct($connection=null, $tableInfo=null) - { - parent::__construct(); - $this->setDbConnection($connection); - $this->setTableInfo($tableInfo); - } - - /** - * @return TDbConnection database connection. - */ - public function getDbConnection() - { - return $this->_connection; - } - - /** - * @param TDbConnection database connection. - */ - public function setDbConnection($value) - { - $this->_connection=$value; - } - - /** - * @param TDbTableInfo table information. - */ - public function setTableInfo($value) - { - $this->_tableInfo=$value; - } - - /** - * @param TDbTableInfo table information. - */ - public function getTableInfo() - { - return $this->_tableInfo; - } - - /** - * Iterate through all the columns and returns the last insert id of the - * first column that has a sequence or serial. - * @return mixed last insert id, null if none is found. - */ - public function getLastInsertID() - { - foreach($this->getTableInfo()->getColumns() as $column) - { - if($column->hasSequence()) - return $this->getDbConnection()->getLastInsertID($column->getSequenceName()); - } - } - - /** - * Alters the sql to apply $limit and $offset. Default implementation is applicable - * for PostgreSQL, MySQL and SQLite. - * @param string SQL query string. - * @param integer maximum number of rows, -1 to ignore limit. - * @param integer row offset, -1 to ignore offset. - * @return string SQL with limit and offset. - */ - public function applyLimitOffset($sql, $limit=-1, $offset=-1) - { - $limit = $limit!==null ? (int)$limit : -1; - $offset = $offset!==null ? (int)$offset : -1; - $limitStr = $limit >= 0 ? ' LIMIT '.$limit : ''; - $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; - return $sql.$limitStr.$offsetStr; - } - - /** - * @param string SQL string without existing ordering. - * @param array pairs of column names as key and direction as value. - * @return string modified SQL applied with ORDER BY. - */ - public function applyOrdering($sql, $ordering) - { - $orders=array(); - foreach($ordering as $name => $direction) - { - $direction = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; - if(strpos($name, '(') && strpos($name, ')')) { - // key is a function (bad practice, but we need to handle it) - $key = $name; - } else { - // key is a column - $key = $this->getTableInfo()->getColumn($name)->getColumnName(); - } - $orders[] = $key.' '.$direction; - } - if(count($orders) > 0) - $sql .= ' ORDER BY '.implode(', ', $orders); - return $sql; - } - - /** - * Computes the SQL condition for search a set of column using regular expression - * (or LIKE, depending on database implementation) to match a string of - * keywords (default matches all keywords). - * @param array list of column id for potential search condition. - * @param string string of keywords - * @return string SQL search condition matching on a set of columns. - */ - public function getSearchExpression($fields, $keywords) - { - if(strlen(trim($keywords)) == 0) return ''; - $words = preg_split('/\s/u', $keywords); - $conditions = array(); - foreach($fields as $field) - { - $column = $this->getTableInfo()->getColumn($field)->getColumnName(); - $conditions[] = $this->getSearchCondition($column, $words); - } - return '('.implode(' OR ', $conditions).')'; - } - - /** - * @param string column name. - * @param array keywords - * @return string search condition for all words in one column. - */ - protected function getSearchCondition($column, $words) - { - $conditions=array(); - foreach($words as $word) - $conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); - return '('.implode(' AND ', $conditions).')'; - } - - /** - * - * Different behavior depends on type of passed data - * string - * usage without modification - * - * null - * will be expanded to full list of quoted table column names (quoting depends on database) - * - * array - * - Column names will be quoted if used as key or value of array - * - * array('col1', 'col2', 'col2') - * // SELECT `col1`, `col2`, `col3` FROM... - * - * - * - Column aliasing - * - * array('mycol1' => 'col1', 'mycol2' => 'COUNT(*)') - * // SELECT `col1` AS mycol1, COUNT(*) AS mycol2 FROM... - * - * - * - NULL and scalar values (strings will be quoted depending on database) - * - * array('col1' => 'my custom string', 'col2' => 1.0, 'col3' => 'NULL') - * // SELECT "my custom string" AS `col1`, 1.0 AS `col2`, NULL AS `col3` FROM... - * - * - * - If the *-wildcard char is used as key or value, add the full list of quoted table column names - * - * array('col1' => 'NULL', '*') - * // SELECT `col1`, `col2`, `col3`, NULL AS `col1` FROM... - * - * @param mixed $value - * @return array of generated fields - use implode(', ', $selectfieldlist) to collapse field list for usage - * @since 3.1.7 - * @todo add support for table aliasing - * @todo add support for quoting of column aliasing - */ - public function getSelectFieldList($data='*') { - if(is_scalar($data)) { - $tmp = explode(',', $data); - $result = array(); - foreach($tmp as $v) - $result[] = trim($v); - return $result; - } - - $bHasWildcard = false; - $result = array(); - if(is_array($data) || $data instanceof Traversable) { - $columns = $this->getTableInfo()->getColumns(); - foreach($data as $key=>$value) { - if($key==='*' || $value==='*') { - $bHasWildcard = true; - continue; - } - - if(strToUpper($key)==='NULL') { - $result[] = 'NULL'; - continue; - } - - if(strpos($key, '(')!==false && strpos($key, ')')!==false) { - $result[] = $key; - continue; - } - - if(stripos($key, 'AS')!==false) { - $result[] = $key; - continue; - } - - if(stripos($value, 'AS')!==false) { - $result[] = $value; - continue; - } - - $v = isset($columns[$value]); - $k = isset($columns[$key]); - if(is_integer($key) && $v) { - $key = $value; - $k = $v; - } - - if(strToUpper($value)==='NULL') { - if($k) - $result[] = 'NULL AS ' . $columns[$key]->getColumnName(); - else - $result[] = 'NULL' . (is_string($key) ? (' AS ' . (string)$key) : ''); - continue; - } - - if(strpos($value, '(')!==false && strpos($value, ')')!==false) { - if($k) - $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); - else - $result[] = $value . (is_string($key) ? (' AS ' . (string)$key) : ''); - continue; - } - - if($v && $key==$value) { - $result[] = $columns[$value]->getColumnName(); - continue; - } - - if($k && $value==null) { - $result[] = $columns[$key]->getColumnName(); - continue; - } - - if(is_string($key) && $v) { - $result[] = $columns[$value]->getColumnName() . ' AS ' . $key; - continue; - } - - if(is_numeric($value) && $k) { - $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); - continue; - } - - if(is_string($value) && $k) { - $result[] = $this->getDbConnection()->quoteString($value) . ' AS ' . $columns[$key]->getColumnName(); - continue; - } - - if(!$v && !$k && is_integer($key)) { - $result[] = is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value); - continue; - } - - $result[] = (is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value)) . ' AS ' . $key; - } - } - - if($data===null || count($result) == 0 || $bHasWildcard) - $result = $result = array_merge($this->getTableInfo()->getColumnNames(), $result); - - return $result; - } - - /** - * Appends the $where condition to the string "SELECT * FROM tableName WHERE ". - * The tableName is obtained from the {@link setTableInfo TableInfo} property. - * @param string query condition - * @param array condition parameters. - * @return TDbCommand query command. - */ - public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1, $select='*') - { - $table = $this->getTableInfo()->getTableFullName(); - $fields = implode(', ', $this -> getSelectFieldList($select)); - $sql = "SELECT {$fields} FROM {$table}"; - if(!empty($where)) - $sql .= " WHERE {$where}"; - return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); - } - - public function applyCriterias($sql, $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) - { - if(count($ordering) > 0) - $sql = $this->applyOrdering($sql, $ordering); - if($limit>=0 || $offset>=0) - $sql = $this->applyLimitOffset($sql, $limit, $offset); - $command = $this->createCommand($sql); - $this->bindArrayValues($command, $parameters); - return $command; - } - - /** - * Creates a count(*) command for the table described in {@link setTableInfo TableInfo}. - * @param string count condition. - * @param array binding parameters. - * @return TDbCommand count command. - */ - public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) - { - return $this->createFindCommand($where, $parameters, $ordering, $limit, $offset, 'COUNT(*)'); - } - - /** - * Creates a delete command for the table described in {@link setTableInfo TableInfo}. - * The conditions for delete is given by the $where argument and the parameters - * for the condition is given by $parameters. - * @param string delete condition. - * @param array delete parameters. - * @return TDbCommand delete command. - */ - public function createDeleteCommand($where,$parameters=array()) - { - $table = $this->getTableInfo()->getTableFullName(); - if (!empty($where)) - $where = ' WHERE '.$where; - $command = $this->createCommand("DELETE FROM {$table}".$where); - $this->bindArrayValues($command, $parameters); - return $command; - } - - /** - * Creates an insert command for the table described in {@link setTableInfo TableInfo} for the given data. - * Each array key in the $data array must correspond to the column name of the table - * (if a column allows to be null, it may be omitted) to be inserted with - * the corresponding array value. - * @param array name-value pairs of new data to be inserted. - * @return TDbCommand insert command - */ - public function createInsertCommand($data) - { - $table = $this->getTableInfo()->getTableFullName(); - list($fields, $bindings) = $this->getInsertFieldBindings($data); - $command = $this->createCommand("INSERT INTO {$table}({$fields}) VALUES ($bindings)"); - $this->bindColumnValues($command, $data); - return $command; - } - - /** - * Creates an update command for the table described in {@link setTableInfo TableInfo} for the given data. - * Each array key in the $data array must correspond to the column name to be updated with the corresponding array value. - * @param array name-value pairs of data to be updated. - * @param string update condition. - * @param array update parameters. - * @return TDbCommand update command. - */ - public function createUpdateCommand($data, $where, $parameters=array()) - { - $table = $this->getTableInfo()->getTableFullName(); - if($this->hasIntegerKey($parameters)) - $fields = implode(', ', $this->getColumnBindings($data, true)); - else - $fields = implode(', ', $this->getColumnBindings($data)); - - if (!empty($where)) - $where = ' WHERE '.$where; - $command = $this->createCommand("UPDATE {$table} SET {$fields}".$where); - $this->bindArrayValues($command, array_merge($data, $parameters)); - return $command; - } - - /** - * Returns a list of insert field name and a list of binding names. - * @param object array or object to be inserted. - * @return array tuple ($fields, $bindings) - */ - protected function getInsertFieldBindings($values) - { - $fields = array(); $bindings=array(); - foreach(array_keys($values) as $name) - { - $fields[] = $this->getTableInfo()->getColumn($name)->getColumnName(); - $bindings[] = ':'.$name; - } - return array(implode(', ',$fields), implode(', ', $bindings)); - } - - /** - * Create a name-value or position-value if $position=true binding strings. - * @param array data for binding. - * @param boolean true to bind as position values. - * @return string update column names with corresponding binding substrings. - */ - protected function getColumnBindings($values, $position=false) - { - $bindings=array(); - foreach(array_keys($values) as $name) - { - $column = $this->getTableInfo()->getColumn($name)->getColumnName(); - $bindings[] = $position ? $column.' = ?' : $column.' = :'.$name; - } - return $bindings; - } - - /** - * @param string SQL query string. - * @return TDbCommand corresponding database command. - */ - public function createCommand($sql) - { - $this->getDbConnection()->setActive(true); - return $this->getDbConnection()->createCommand($sql); - } - - /** - * Bind the name-value pairs of $values where the array keys correspond to column names. - * @param TDbCommand database command. - * @param array name-value pairs. - */ - public function bindColumnValues($command, $values) - { - foreach($values as $name=>$value) - { - $column = $this->getTableInfo()->getColumn($name); - if($value === null && $column->getAllowNull()) - $command->bindValue(':'.$name, null, PDO::PARAM_NULL); - else - $command->bindValue(':'.$name, $value, $column->getPdoType()); - } - } - - /** - * @param TDbCommand database command - * @param array values for binding. - */ - public function bindArrayValues($command, $values) - { - if($this->hasIntegerKey($values)) - { - $values = array_values($values); - for($i = 0, $max=count($values); $i<$max; $i++) - $command->bindValue($i+1, $values[$i], $this->getPdoType($values[$i])); - } - else - { - foreach($values as $name=>$value) - { - $prop = $name[0]===':' ? $name : ':'.$name; - $command->bindValue($prop, $value, $this->getPdoType($value)); - } - } - } - - /** - * @param mixed PHP value - * @return integer PDO parameter types. - */ - public static function getPdoType($value) - { - switch(gettype($value)) - { - case 'boolean': return PDO::PARAM_BOOL; - case 'integer': return PDO::PARAM_INT; - case 'string' : return PDO::PARAM_STR; - case 'NULL' : return PDO::PARAM_NULL; - } - } - - /** - * @param array - * @return boolean true if any array key is an integer. - */ - protected function hasIntegerKey($array) - { - foreach($array as $k=>$v) - { - if(gettype($k)==='integer') - return true; - } - return false; - } -} - -?> \ No newline at end of file + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2010 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbCommandBuilder provides basic methods to create query commands for tables + * giving by {@link setTableInfo TableInfo} the property. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +class TDbCommandBuilder extends TComponent +{ + private $_connection; + private $_tableInfo; + + /** + * @param TDbConnection database connection. + * @param TDbTableInfo table information. + */ + public function __construct($connection=null, $tableInfo=null) + { + parent::__construct(); + $this->setDbConnection($connection); + $this->setTableInfo($tableInfo); + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @param TDbConnection database connection. + */ + public function setDbConnection($value) + { + $this->_connection=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function setTableInfo($value) + { + $this->_tableInfo=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function getTableInfo() + { + return $this->_tableInfo; + } + + /** + * Iterate through all the columns and returns the last insert id of the + * first column that has a sequence or serial. + * @return mixed last insert id, null if none is found. + */ + public function getLastInsertID() + { + foreach($this->getTableInfo()->getColumns() as $column) + { + if($column->hasSequence()) + return $this->getDbConnection()->getLastInsertID($column->getSequenceName()); + } + } + + /** + * Alters the sql to apply $limit and $offset. Default implementation is applicable + * for PostgreSQL, MySQL and SQLite. + * @param string SQL query string. + * @param integer maximum number of rows, -1 to ignore limit. + * @param integer row offset, -1 to ignore offset. + * @return string SQL with limit and offset. + */ + public function applyLimitOffset($sql, $limit=-1, $offset=-1) + { + $limit = $limit!==null ? (int)$limit : -1; + $offset = $offset!==null ? (int)$offset : -1; + $limitStr = $limit >= 0 ? ' LIMIT '.$limit : ''; + $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; + return $sql.$limitStr.$offsetStr; + } + + /** + * @param string SQL string without existing ordering. + * @param array pairs of column names as key and direction as value. + * @return string modified SQL applied with ORDER BY. + */ + public function applyOrdering($sql, $ordering) + { + $orders=array(); + foreach($ordering as $name => $direction) + { + $direction = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; + if(false !== strpos($name, '(') && false !== strpos($name, ')')) { + // key is a function (bad practice, but we need to handle it) + $key = $name; + } else { + // key is a column + $key = $this->getTableInfo()->getColumn($name)->getColumnName(); + } + $orders[] = $key.' '.$direction; + } + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + return $sql; + } + + /** + * Computes the SQL condition for search a set of column using regular expression + * (or LIKE, depending on database implementation) to match a string of + * keywords (default matches all keywords). + * @param array list of column id for potential search condition. + * @param string string of keywords + * @return string SQL search condition matching on a set of columns. + */ + public function getSearchExpression($fields, $keywords) + { + if(strlen(trim($keywords)) == 0) return ''; + $words = preg_split('/\s/u', $keywords); + $conditions = array(); + foreach($fields as $field) + { + $column = $this->getTableInfo()->getColumn($field)->getColumnName(); + $conditions[] = $this->getSearchCondition($column, $words); + } + return '('.implode(' OR ', $conditions).')'; + } + + /** + * @param string column name. + * @param array keywords + * @return string search condition for all words in one column. + */ + protected function getSearchCondition($column, $words) + { + $conditions=array(); + foreach($words as $word) + $conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); + return '('.implode(' AND ', $conditions).')'; + } + + /** + * + * Different behavior depends on type of passed data + * string + * usage without modification + * + * null + * will be expanded to full list of quoted table column names (quoting depends on database) + * + * array + * - Column names will be quoted if used as key or value of array + * + * array('col1', 'col2', 'col2') + * // SELECT `col1`, `col2`, `col3` FROM... + * + * + * - Column aliasing + * + * array('mycol1' => 'col1', 'mycol2' => 'COUNT(*)') + * // SELECT `col1` AS mycol1, COUNT(*) AS mycol2 FROM... + * + * + * - NULL and scalar values (strings will be quoted depending on database) + * + * array('col1' => 'my custom string', 'col2' => 1.0, 'col3' => 'NULL') + * // SELECT "my custom string" AS `col1`, 1.0 AS `col2`, NULL AS `col3` FROM... + * + * + * - If the *-wildcard char is used as key or value, add the full list of quoted table column names + * + * array('col1' => 'NULL', '*') + * // SELECT `col1`, `col2`, `col3`, NULL AS `col1` FROM... + * + * @param mixed $value + * @return array of generated fields - use implode(', ', $selectfieldlist) to collapse field list for usage + * @since 3.1.7 + * @todo add support for table aliasing + * @todo add support for quoting of column aliasing + */ + public function getSelectFieldList($data='*') { + if(is_scalar($data)) { + $tmp = explode(',', $data); + $result = array(); + foreach($tmp as $v) + $result[] = trim($v); + return $result; + } + + $bHasWildcard = false; + $result = array(); + if(is_array($data) || $data instanceof Traversable) { + $columns = $this->getTableInfo()->getColumns(); + foreach($data as $key=>$value) { + if($key==='*' || $value==='*') { + $bHasWildcard = true; + continue; + } + + if(strToUpper($key)==='NULL') { + $result[] = 'NULL'; + continue; + } + + if(strpos($key, '(')!==false && strpos($key, ')')!==false) { + $result[] = $key; + continue; + } + + if(stripos($key, 'AS')!==false) { + $result[] = $key; + continue; + } + + if(stripos($value, 'AS')!==false) { + $result[] = $value; + continue; + } + + $v = isset($columns[$value]); + $k = isset($columns[$key]); + if(is_integer($key) && $v) { + $key = $value; + $k = $v; + } + + if(strToUpper($value)==='NULL') { + if($k) + $result[] = 'NULL AS ' . $columns[$key]->getColumnName(); + else + $result[] = 'NULL' . (is_string($key) ? (' AS ' . (string)$key) : ''); + continue; + } + + if(strpos($value, '(')!==false && strpos($value, ')')!==false) { + if($k) + $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); + else + $result[] = $value . (is_string($key) ? (' AS ' . (string)$key) : ''); + continue; + } + + if($v && $key==$value) { + $result[] = $columns[$value]->getColumnName(); + continue; + } + + if($k && $value==null) { + $result[] = $columns[$key]->getColumnName(); + continue; + } + + if(is_string($key) && $v) { + $result[] = $columns[$value]->getColumnName() . ' AS ' . $key; + continue; + } + + if(is_numeric($value) && $k) { + $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); + continue; + } + + if(is_string($value) && $k) { + $result[] = $this->getDbConnection()->quoteString($value) . ' AS ' . $columns[$key]->getColumnName(); + continue; + } + + if(!$v && !$k && is_integer($key)) { + $result[] = is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value); + continue; + } + + $result[] = (is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value)) . ' AS ' . $key; + } + } + + if($data===null || count($result) == 0 || $bHasWildcard) + $result = $result = array_merge($this->getTableInfo()->getColumnNames(), $result); + + return $result; + } + + /** + * Appends the $where condition to the string "SELECT * FROM tableName WHERE ". + * The tableName is obtained from the {@link setTableInfo TableInfo} property. + * @param string query condition + * @param array condition parameters. + * @return TDbCommand query command. + */ + public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1, $select='*') + { + $table = $this->getTableInfo()->getTableFullName(); + $fields = implode(', ', $this -> getSelectFieldList($select)); + $sql = "SELECT {$fields} FROM {$table}"; + if(!empty($where)) + $sql .= " WHERE {$where}"; + return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); + } + + public function applyCriterias($sql, $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { + if(count($ordering) > 0) + $sql = $this->applyOrdering($sql, $ordering); + if($limit>=0 || $offset>=0) + $sql = $this->applyLimitOffset($sql, $limit, $offset); + $command = $this->createCommand($sql); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates a count(*) command for the table described in {@link setTableInfo TableInfo}. + * @param string count condition. + * @param array binding parameters. + * @return TDbCommand count command. + */ + public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { + return $this->createFindCommand($where, $parameters, $ordering, $limit, $offset, 'COUNT(*)'); + } + + /** + * Creates a delete command for the table described in {@link setTableInfo TableInfo}. + * The conditions for delete is given by the $where argument and the parameters + * for the condition is given by $parameters. + * @param string delete condition. + * @param array delete parameters. + * @return TDbCommand delete command. + */ + public function createDeleteCommand($where,$parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + if (!empty($where)) + $where = ' WHERE '.$where; + $command = $this->createCommand("DELETE FROM {$table}".$where); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates an insert command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name of the table + * (if a column allows to be null, it may be omitted) to be inserted with + * the corresponding array value. + * @param array name-value pairs of new data to be inserted. + * @return TDbCommand insert command + */ + public function createInsertCommand($data) + { + $table = $this->getTableInfo()->getTableFullName(); + list($fields, $bindings) = $this->getInsertFieldBindings($data); + $command = $this->createCommand("INSERT INTO {$table}({$fields}) VALUES ($bindings)"); + $this->bindColumnValues($command, $data); + return $command; + } + + /** + * Creates an update command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name to be updated with the corresponding array value. + * @param array name-value pairs of data to be updated. + * @param string update condition. + * @param array update parameters. + * @return TDbCommand update command. + */ + public function createUpdateCommand($data, $where, $parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + if($this->hasIntegerKey($parameters)) + $fields = implode(', ', $this->getColumnBindings($data, true)); + else + $fields = implode(', ', $this->getColumnBindings($data)); + + if (!empty($where)) + $where = ' WHERE '.$where; + $command = $this->createCommand("UPDATE {$table} SET {$fields}".$where); + $this->bindArrayValues($command, array_merge($data, $parameters)); + return $command; + } + + /** + * Returns a list of insert field name and a list of binding names. + * @param object array or object to be inserted. + * @return array tuple ($fields, $bindings) + */ + protected function getInsertFieldBindings($values) + { + $fields = array(); $bindings=array(); + foreach(array_keys($values) as $name) + { + $fields[] = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = ':'.$name; + } + return array(implode(', ',$fields), implode(', ', $bindings)); + } + + /** + * Create a name-value or position-value if $position=true binding strings. + * @param array data for binding. + * @param boolean true to bind as position values. + * @return string update column names with corresponding binding substrings. + */ + protected function getColumnBindings($values, $position=false) + { + $bindings=array(); + foreach(array_keys($values) as $name) + { + $column = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = $position ? $column.' = ?' : $column.' = :'.$name; + } + return $bindings; + } + + /** + * @param string SQL query string. + * @return TDbCommand corresponding database command. + */ + public function createCommand($sql) + { + $this->getDbConnection()->setActive(true); + return $this->getDbConnection()->createCommand($sql); + } + + /** + * Bind the name-value pairs of $values where the array keys correspond to column names. + * @param TDbCommand database command. + * @param array name-value pairs. + */ + public function bindColumnValues($command, $values) + { + foreach($values as $name=>$value) + { + $column = $this->getTableInfo()->getColumn($name); + if($value === null && $column->getAllowNull()) + $command->bindValue(':'.$name, null, PDO::PARAM_NULL); + else + $command->bindValue(':'.$name, $value, $column->getPdoType()); + } + } + + /** + * @param TDbCommand database command + * @param array values for binding. + */ + public function bindArrayValues($command, $values) + { + if($this->hasIntegerKey($values)) + { + $values = array_values($values); + for($i = 0, $max=count($values); $i<$max; $i++) + $command->bindValue($i+1, $values[$i], $this->getPdoType($values[$i])); + } + else + { + foreach($values as $name=>$value) + { + $prop = $name[0]===':' ? $name : ':'.$name; + $command->bindValue($prop, $value, $this->getPdoType($value)); + } + } + } + + /** + * @param mixed PHP value + * @return integer PDO parameter types. + */ + public static function getPdoType($value) + { + switch(gettype($value)) + { + case 'boolean': return PDO::PARAM_BOOL; + case 'integer': return PDO::PARAM_INT; + case 'string' : return PDO::PARAM_STR; + case 'NULL' : return PDO::PARAM_NULL; + } + } + + /** + * @param array + * @return boolean true if any array key is an integer. + */ + protected function hasIntegerKey($array) + { + foreach($array as $k=>$v) + { + if(gettype($k)==='integer') + return true; + } + return false; + } +} -- cgit v1.2.3