From 0a5d8c3ba8b2d47b69851481c832ac06a77860e5 Mon Sep 17 00:00:00 2001 From: xue <> Date: Thu, 17 Apr 2008 22:50:24 +0000 Subject: patching --- .../Data/Common/Oracle/TOracleCommandBuilder.php | 140 +++++++++++---------- 1 file changed, 71 insertions(+), 69 deletions(-) diff --git a/framework/Data/Common/Oracle/TOracleCommandBuilder.php b/framework/Data/Common/Oracle/TOracleCommandBuilder.php index dc9b91db..171afcf4 100644 --- a/framework/Data/Common/Oracle/TOracleCommandBuilder.php +++ b/framework/Data/Common/Oracle/TOracleCommandBuilder.php @@ -1,4 +1,5 @@ isSearchableColumn($this->getTableInfo()->getColumn($field))) + public function getSearchExpression($fields, $keywords) { + $columns = array (); + foreach ($fields as $field) { + if ($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) $columns[] = $field; } - return parent::getSearchExpression($columns, $keywords); + return parent :: getSearchExpression($columns, $keywords); } /** * * @return boolean true if column can be used for LIKE searching. */ - protected function isSearchableColumn($column) - { + protected function isSearchableColumn($column) { $type = strtolower($column->getDbType()); - return $type === 'character varying' || $type === 'varchar2' || - $type === 'character' || $type === 'char' || $type === 'text'; + return $type === 'character varying' || $type === 'varchar2' || $type === 'character' || $type === 'char' || $type === 'text'; } /** @@ -58,10 +54,10 @@ class TOracleCommandBuilder extends TDbCommandBuilder * @param array keywords * @return string search condition for all words in one column. */ - /* - * - * how Oracle don't implements ILIKE, this method won't be overrided - * + /* + * + * how Oracle don't implements ILIKE, this method won't be overrided + * protected function getSearchCondition($column, $words) { $conditions=array(); @@ -71,7 +67,6 @@ class TOracleCommandBuilder extends TDbCommandBuilder } */ - /** * Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause. * @param string SQL query string. @@ -79,56 +74,65 @@ class TOracleCommandBuilder extends TDbCommandBuilder * @param integer row offset, -1 to ignore offset. * @return string SQL with limit and offset in Oracle way. */ - public function applyLimitOffset($sql, $limit=-1, $offset=-1) - { - if( (int)$limit <= 0 && (int)$offset <= 0 ) + public function applyLimitOffset($sql, $limit = -1, $offset = -1) { + if ((int) $limit <= 0 && (int) $offset <= 0) return $sql; - + $pradoNUMLIN = 'pradoNUMLIN'; $fieldsALIAS = 'xyz'; - $nfimDaSQL = strlen($sql); - $nfimDoWhere = ( strpos($sql,'ORDER') !== false ? strpos($sql,'ORDER') : $nfimDaSQL ); - $niniDoSelect= strpos($sql,'SELECT')+6; - $nfimDoSelect= ( strpos($sql,'FROM') !== false ? strpos($sql,'FROM') : $nfimDaSQL ); + $nfimDaSQL = strlen($sql); + $nfimDoWhere = (strpos($sql, 'ORDER') !== false ? strpos($sql, 'ORDER') : $nfimDaSQL); + $niniDoSelect = strpos($sql, 'SELECT') + 6; + $nfimDoSelect = (strpos($sql, 'FROM') !== false ? strpos($sql, 'FROM') : $nfimDaSQL); + + $niniDoWhere = strpos($sql, 'WHERE') + 5; + + $WhereConstraint = substr($sql, $niniDoWhere, $nfimDoWhere - $niniDoWhere); + + $WhereInSubSelect = ""; + if (trim($WhereConstraint) !== "") { + $WhereInSubSelect = "WHERE " . $WhereConstraint; + } + + $sORDERBY = ''; + if (stripos($sql, 'ORDER') !== false) { + $p = stripos($sql, 'ORDER'); + $sORDERBY = substr($sql, $p +8, 10000); - $sORDERBY = ''; - if( stripos($sql,'ORDER') !== false ) { - $p = stripos($sql,'ORDER'); - $sORDERBY = substr( $sql, $p+8, 10000 ); } - $fields = substr( $sql, 0, $nfimDoSelect ); - $fields = trim( substr( $fields, $niniDoSelect ) ); - $aliasedFields = ', '; - - if( trim($fields) == '*' ) { - $aliasedFields = ", {$fieldsALIAS}.{$fields}"; - $fields = ''; - $arr = $this->getTableInfo()->getColumns(); - foreach( $arr as $field ) - { - $fields .= strtolower( $field->getColumnName() ).', '; + $fields = substr($sql, 0, $nfimDoSelect); + $fields = trim(substr($fields, $niniDoSelect)); + $aliasedFields = ', '; + + if (trim($fields) == '*') { + $aliasedFields = ", {$fieldsALIAS}.{$fields}"; + $fields = ''; + $arr = $this->getTableInfo()->getColumns(); + foreach ($arr as $field) { + $fields .= strtolower($field->getColumnName()) . ', '; } - $fields = str_replace( '"', '', $fields ); + $fields = str_replace('"', '', $fields); $fields = trim($fields); - $fields = substr( $fields, 0, strlen($fields)-1 ); + $fields = substr($fields, 0, strlen($fields) - 1); } else { - if( strpos( $fields, ',' ) !== false ) - { - foreach( $arr as $field ) - { - $field = strtolower( $field ); - $existAS = str_ireplace( ' as ', '-as-', $field ); - if( strpos( $existAS, '-as-' ) === false ) - $aliasedFields .= "{$fieldsALIAS}.".trim($field).", "; + if (strpos($fields, ',') !== false) { + $arr = $this->getTableInfo()->getColumns(); + foreach ($arr as $field) { + $field = strtolower($field); + $existAS = str_ireplace(' as ', '-as-', $field); + if (strpos($existAS, '-as-') === false) + $aliasedFields .= "{$fieldsALIAS}." . trim($field) . ", "; else $aliasedFields .= "{$field}, "; } $aliasedFields = trim($aliasedFields); - $aliasedFields = substr( $aliasedFields, 0, strlen($aliasedFields)-1 ); + $aliasedFields = substr($aliasedFields, 0, strlen($aliasedFields) - 1); } } + if ($aliasedFields == ', ') + $aliasedFields = " , $fieldsALIAS.* "; /* ************************ $newSql = " SELECT $fields FROM ". @@ -136,30 +140,28 @@ class TOracleCommandBuilder extends TDbCommandBuilder " SELECT rownum as {$pradoNUMLIN} {$aliasedFields} FROM ". " ($sql) {$fieldsALIAS} WHERE rownum <= {$limit} ". ") WHERE {$pradoNUMLIN} >= {$offset} "; - + ************************* */ - $toReg = $offset + $limit; + $toReg = $offset + $limit -1; $fullTableName = $this->getTableInfo()->getTableFullName(); - if( empty($sORDERBY) ) - { + if (empty ($sORDERBY)) { $newSql = " SELECT $fields FROM " . - "( " . - " SELECT ROW_NUMBER() OVER ( ORDER BY ROWNUM ) as {$pradoNUMLIN} {$aliasedFields} " . - " FROM {$fullTableName} {$fieldsALIAS}" . - ") nn " . - " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} " ; + "( " . + " SELECT ROW_NUMBER() OVER ( ORDER BY ROWNUM ) as {$pradoNUMLIN} {$aliasedFields} " . + " FROM {$fullTableName} {$fieldsALIAS}" . + ") nn " . + " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} "; } else { $newSql = " SELECT $fields FROM " . - "( " . - " SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) as {$pradoNUMLIN} {$aliasedFields} " . - " FROM {$fullTableName} {$fieldsALIAS}" . - ") nn " . - " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} " ; + "( " . + " SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) as {$pradoNUMLIN} {$aliasedFields} " . + " FROM {$fullTableName} {$fieldsALIAS} $WhereInSubSelect" . + ") nn " . + " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} "; } + //echo $newSql."\n
\n"; return $newSql; } - } - ?> \ No newline at end of file -- cgit v1.2.3