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