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 ++++++++++++++++++++++++++-- 1 file changed, 146 insertions(+), 8 deletions(-) (limited to 'framework/Data/Common/TDbCommandBuilder.php') 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 -- cgit v1.2.3