From 00e8b2c1ac9463c2d108b8f534ac1d9317ae38f7 Mon Sep 17 00:00:00 2001 From: "godzilla80@gmx.net" <> Date: Sat, 13 Feb 2010 15:37:13 +0000 Subject: Fixed Issue #24 - Specify needed fields on demand - modify TDbCommandBuilder in package System.Data.Common - add method getSelectFieldList() - modify createFindCommand() to utilize getSelectFieldList() - modify createCountCommand() to invoke createFindCommand() with COUNT(*) - modify package System.Data.DataGateway - add property Select to TSqlCriteria - modify TDataGatewayCommand::getFindCommand() to pass Select property of criteria to TDbCommandBuilder::createFindCommand() --- framework/Data/Common/TDbCommandBuilder.php | 154 +++++++++++++++++++-- framework/Data/DataGateway/TDataGatewayCommand.php | 3 +- framework/Data/DataGateway/TSqlCriteria.php | 72 +++++++++- 3 files changed, 213 insertions(+), 16 deletions(-) (limited to 'framework/Data') diff --git a/framework/Data/Common/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php index 155a62f8..0dc13e7e 100644 --- a/framework/Data/Common/TDbCommandBuilder.php +++ b/framework/Data/Common/TDbCommandBuilder.php @@ -156,6 +156,147 @@ class TDbCommandBuilder extends TComponent 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. @@ -163,10 +304,11 @@ class TDbCommandBuilder extends TComponent * @param array condition parameters. * @return TDbCommand query command. */ - public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1) + public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1, $select='*') { $table = $this->getTableInfo()->getTableFullName(); - $sql = "SELECT * FROM {$table}"; + $fields = implode(', ', $this -> getSelectFieldList($select)); + $sql = "SELECT {$fields} FROM {$table}"; if(!empty($where)) $sql .= " WHERE {$where}"; return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); @@ -191,11 +333,7 @@ class TDbCommandBuilder extends TComponent */ public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) { - $table = $this->getTableInfo()->getTableFullName(); - $sql = "SELECT COUNT(*) FROM {$table}"; - if(!empty($where)) - $sql .= " WHERE {$where}"; - return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); + return $this->createFindCommand($where, $parameters, $ordering, $limit, $offset, 'COUNT(*)'); } /** @@ -368,4 +506,4 @@ class TDbCommandBuilder extends TComponent } } -?> +?> \ No newline at end of file diff --git a/framework/Data/DataGateway/TDataGatewayCommand.php b/framework/Data/DataGateway/TDataGatewayCommand.php index 22998033..e290f457 100644 --- a/framework/Data/DataGateway/TDataGatewayCommand.php +++ b/framework/Data/DataGateway/TDataGatewayCommand.php @@ -148,7 +148,8 @@ class TDataGatewayCommand extends TComponent $ordering = $criteria->getOrdersBy(); $limit = $criteria->getLimit(); $offset = $criteria->getOffset(); - $command = $this->getBuilder()->createFindCommand($where,$parameters,$ordering,$limit,$offset); + $select = $criteria->getSelect(); + $command = $this->getBuilder()->createFindCommand($where,$parameters,$ordering,$limit,$offset,$select); $this->onCreateCommand($command, $criteria); return $command; } diff --git a/framework/Data/DataGateway/TSqlCriteria.php b/framework/Data/DataGateway/TSqlCriteria.php index 4ebdeb48..14e37b35 100644 --- a/framework/Data/DataGateway/TSqlCriteria.php +++ b/framework/Data/DataGateway/TSqlCriteria.php @@ -31,6 +31,11 @@ */ class TSqlCriteria extends TComponent { + /** + * @var mixed + * @since 3.1.7 + */ + private $_select='*'; private $_condition; private $_parameters; private $_ordersBy; @@ -55,6 +60,60 @@ class TSqlCriteria extends TComponent $this->setCondition($condition); } + /** + * Gets the field list to be placed after the SELECT in the SQL. Default to '*' + * @return mixed + * @since 3.1.7 + */ + public function getSelect() + { + return $this->_select; + } + + /** + * Sets the field list to be placed after the SELECT in the SQL. + * + * Different behavior depends on type of assigned value + * 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 + * @since 3.1.7 + * @see TDbCommandBuilder::getSelectFieldList() + */ + public function setSelect($value) + { + $this->_select = $value; + } + /** * @return string search conditions. */ @@ -68,17 +127,17 @@ class TSqlCriteria extends TComponent * @param string search conditions. */ public function setCondition($value) - { + { if(empty($value)) { return; } - + // supporting the following SELECT-syntax: // [ORDER BY {col_name | expr | position} // [ASC | DESC], ...] // [LIMIT {[offset,] row_count | row_count OFFSET offset}] // See: http://dev.mysql.com/doc/refman/5.0/en/select.html - + if(preg_match('/ORDER\s+BY\s+(.*?)(?=LIMIT)|ORDER\s+BY\s+(.*?)$/i', $value, $matches) > 0) { // condition contains ORDER BY $value = str_replace($matches[0], '', $value); @@ -88,7 +147,7 @@ class TSqlCriteria extends TComponent $this->setOrdersBy($matches[2]); } } - + if(preg_match('/LIMIT\s+([\d\s,]+)/i', $value, $matches) > 0) { // condition contains limit $value = str_replace($matches[0], '', $value); // remove limit from query @@ -106,7 +165,7 @@ class TSqlCriteria extends TComponent $value = str_replace($matches[0], '', $value); // remove offset from query $this->_offset = (int)$matches[1]; // set offset in criteria } - + $this->_condition = trim($value); } @@ -222,5 +281,4 @@ class TSqlCriteria extends TComponent return $str; } } - -?> +?> \ No newline at end of file -- cgit v1.2.3