diff options
| -rw-r--r-- | HISTORY | 1 | ||||
| -rw-r--r-- | framework/Data/Common/TDbCommandBuilder.php | 154 | ||||
| -rw-r--r-- | framework/Data/DataGateway/TDataGatewayCommand.php | 3 | ||||
| -rw-r--r-- | framework/Data/DataGateway/TSqlCriteria.php | 72 |
4 files changed, 214 insertions, 16 deletions
@@ -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 |