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()

4 files changed, 214 insertions, 16 deletions

diff --git a/HISTORY b/HISTORY
index 95db4e0a..b584940e 100644
--- a/HISTORY
+++ b/HISTORY
@@ -1,4 +1,5 @@
 Version 3.1.7 To be released
+ENH: Issue#24  - Specify needed fields on demand (Yves) 
 BUG: Issue#80  - Inconsistencies in TRegularExpressionValidator (Christophe)
 BUG: Issue#86  - THttpSession.CookieMode ignored / Session ID leak (Christophe)
 BUG: Issue#94  - DataGrid header/footer renderers unable to locate their parent grid in setData() method (Christophe)
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
@@ -157,16 +157,158 @@ class TDbCommandBuilder extends TComponent
 	}

 

 	/**

+	 *

+	 * 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

+	 * 	<code>

+	 * 	array('col1', 'col2', 'col2')

+	 * 	// SELECT `col1`, `col2`, `col3` FROM...

+	 * 	</code>

+	 *

+	 * - Column aliasing

+	 * <code>

+	 * array('mycol1' => 'col1', 'mycol2' => 'COUNT(*)')

+	 * // SELECT `col1` AS mycol1, COUNT(*) AS mycol2 FROM...

+	 * </code>

+	 *

+	 * - NULL and scalar values (strings will be quoted depending on database)

+	 * <code>

+	 * array('col1' => 'my custom string', 'col2' => 1.0, 'col3' => 'NULL')

+	 * // SELECT "my custom string" AS `col1`, 1.0 AS `col2`, NULL AS `col3` FROM...

+	 * </code>

+	 *

+	 * - If the *-wildcard char is used as key or value, add the full list of quoted table column names

+	 * <code>

+	 * array('col1' => 'NULL', '*')

+	 * // SELECT `col1`, `col2`, `col3`, NULL AS `col1` FROM...

+	 * </code>

+	 * @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)

+	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;

@@ -56,6 +61,60 @@ class TSqlCriteria extends TComponent
 	}

 

 	/**

+	 * 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

+	 * 	<code>

+	 * 	array('col1', 'col2', 'col2')

+	 * 	// SELECT `col1`, `col2`, `col3` FROM...

+	 * 	</code>

+	 *

+	 * - Column aliasing

+	 * <code>

+	 * array('mycol1' => 'col1', 'mycol2' => 'COUNT(*)')

+	 * // SELECT `col1` AS mycol1, COUNT(*) AS mycol2 FROM...

+	 * </code>

+	 *

+	 * - NULL and scalar values (strings will be quoted depending on database)

+	 * <code>

+	 * array('col1' => 'my custom string', 'col2' => 1.0, 'col3' => 'NULL')

+	 * // SELECT "my custom string" AS `col1`, 1.0 AS `col2`, NULL AS `col3` FROM...

+	 * </code>

+	 *

+	 * - If the *-wildcard char is used as key or value, add the full list of quoted table column names

+	 * <code>

+	 * array('col1' => 'NULL', '*')

+	 * // SELECT `col1`, `col2`, `col3`, NULL AS `col1` FROM...

+	 * </code>

+	 *

+	 * @param mixed

+	 * @since 3.1.7

+	 * @see TDbCommandBuilder::getSelectFieldList()

+	 */

+	public function setSelect($value)

+	{

+		$this->_select = $value;

+	}

+

+	/**

 	 * @return string search conditions.

 	 */

 	public function getCondition()

@@ -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