Changeset 2428
- Timestamp:
- 04/17/2008 05:50:24 PM (8 months ago)
- Files:
-
- 1 modified
Legend:
- Unmodified
- Added
- Removed
-
trunk/framework/Data/Common/Oracle/TOracleCommandBuilder.php
r2206 r2428 1 1 <?php 2 2 3 /** 3 4 * TOracleCommandBuilder class file. … … 11 12 */ 12 13 13 Prado ::using('System.Data.Common.TDbCommandBuilder');14 Prado :: using('System.Data.Common.TDbCommandBuilder'); 14 15 15 16 /** … … 22 23 * @since 3.1 23 24 */ 24 class TOracleCommandBuilder extends TDbCommandBuilder 25 { 25 class TOracleCommandBuilder extends TDbCommandBuilder { 26 26 27 27 /** … … 32 32 * @return string SQL search condition matching on a set of columns. 33 33 */ 34 public function getSearchExpression($fields, $keywords) 35 { 36 $columns = array(); 37 foreach($fields as $field) 38 { 39 if($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) 34 public function getSearchExpression($fields, $keywords) { 35 $columns = array (); 36 foreach ($fields as $field) { 37 if ($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) 40 38 $columns[] = $field; 41 39 } 42 return parent ::getSearchExpression($columns, $keywords);40 return parent :: getSearchExpression($columns, $keywords); 43 41 } 44 42 /** … … 46 44 * @return boolean true if column can be used for LIKE searching. 47 45 */ 48 protected function isSearchableColumn($column) 49 { 46 protected function isSearchableColumn($column) { 50 47 $type = strtolower($column->getDbType()); 51 return $type === 'character varying' || $type === 'varchar2' || 52 $type === 'character' || $type === 'char' || $type === 'text'; 48 return $type === 'character varying' || $type === 'varchar2' || $type === 'character' || $type === 'char' || $type === 'text'; 53 49 } 54 50 … … 59 55 * @return string search condition for all words in one column. 60 56 */ 61 62 63 64 57 /* 58 * 59 * how Oracle don't implements ILIKE, this method won't be overrided 60 * 65 61 protected function getSearchCondition($column, $words) 66 62 { … … 72 68 */ 73 69 74 75 70 /** 76 71 * Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause. … … 80 75 * @return string SQL with limit and offset in Oracle way. 81 76 */ 82 public function applyLimitOffset($sql, $limit=-1, $offset=-1) 83 { 84 if( (int)$limit <= 0 && (int)$offset <= 0 ) 77 public function applyLimitOffset($sql, $limit = -1, $offset = -1) { 78 if ((int) $limit <= 0 && (int) $offset <= 0) 85 79 return $sql; 86 80 87 81 $pradoNUMLIN = 'pradoNUMLIN'; 88 82 $fieldsALIAS = 'xyz'; 89 83 90 $nfimDaSQL 91 $nfimDoWhere = ( strpos($sql,'ORDER') !== false ? strpos($sql,'ORDER') : $nfimDaSQL);92 $niniDoSelect = strpos($sql,'SELECT')+6;93 $nfimDoSelect = ( strpos($sql,'FROM') !== false ? strpos($sql,'FROM') : $nfimDaSQL);84 $nfimDaSQL = strlen($sql); 85 $nfimDoWhere = (strpos($sql, 'ORDER') !== false ? strpos($sql, 'ORDER') : $nfimDaSQL); 86 $niniDoSelect = strpos($sql, 'SELECT') + 6; 87 $nfimDoSelect = (strpos($sql, 'FROM') !== false ? strpos($sql, 'FROM') : $nfimDaSQL); 94 88 95 $sORDERBY = ''; 96 if( stripos($sql,'ORDER') !== false ) { 97 $p = stripos($sql,'ORDER'); 98 $sORDERBY = substr( $sql, $p+8, 10000 ); 89 $niniDoWhere = strpos($sql, 'WHERE') + 5; 90 91 $WhereConstraint = substr($sql, $niniDoWhere, $nfimDoWhere - $niniDoWhere); 92 93 $WhereInSubSelect = ""; 94 if (trim($WhereConstraint) !== "") { 95 $WhereInSubSelect = "WHERE " . $WhereConstraint; 99 96 } 100 97 101 $fields = substr( $sql, 0, $nfimDoSelect ); 102 $fields = trim( substr( $fields, $niniDoSelect ) ); 103 $aliasedFields = ', '; 98 $sORDERBY = ''; 99 if (stripos($sql, 'ORDER') !== false) { 100 $p = stripos($sql, 'ORDER'); 101 $sORDERBY = substr($sql, $p +8, 10000); 104 102 105 if( trim($fields) == '*' ) { 106 $aliasedFields = ", {$fieldsALIAS}.{$fields}"; 107 $fields = ''; 108 $arr = $this->getTableInfo()->getColumns(); 109 foreach( $arr as $field ) 110 { 111 $fields .= strtolower( $field->getColumnName() ).', '; 103 } 104 105 $fields = substr($sql, 0, $nfimDoSelect); 106 $fields = trim(substr($fields, $niniDoSelect)); 107 $aliasedFields = ', '; 108 109 if (trim($fields) == '*') { 110 $aliasedFields = ", {$fieldsALIAS}.{$fields}"; 111 $fields = ''; 112 $arr = $this->getTableInfo()->getColumns(); 113 foreach ($arr as $field) { 114 $fields .= strtolower($field->getColumnName()) . ', '; 112 115 } 113 $fields = str_replace( '"', '', $fields);116 $fields = str_replace('"', '', $fields); 114 117 $fields = trim($fields); 115 $fields = substr( $fields, 0, strlen($fields)-1);118 $fields = substr($fields, 0, strlen($fields) - 1); 116 119 } else { 117 if( strpos( $fields, ',' ) !== false ) 118 { 119 foreach( $arr as $field ) 120 { 121 $field = strtolower( $field ); 122 $existAS = str_ireplace( ' as ', '-as-', $field ); 123 if( strpos( $existAS, '-as-' ) === false ) 124 $aliasedFields .= "{$fieldsALIAS}.".trim($field).", "; 120 if (strpos($fields, ',') !== false) { 121 $arr = $this->getTableInfo()->getColumns(); 122 foreach ($arr as $field) { 123 $field = strtolower($field); 124 $existAS = str_ireplace(' as ', '-as-', $field); 125 if (strpos($existAS, '-as-') === false) 126 $aliasedFields .= "{$fieldsALIAS}." . trim($field) . ", "; 125 127 else 126 128 $aliasedFields .= "{$field}, "; 127 129 } 128 130 $aliasedFields = trim($aliasedFields); 129 $aliasedFields = substr( $aliasedFields, 0, strlen($aliasedFields)-1);131 $aliasedFields = substr($aliasedFields, 0, strlen($aliasedFields) - 1); 130 132 } 131 133 } 134 if ($aliasedFields == ', ') 135 $aliasedFields = " , $fieldsALIAS.* "; 132 136 133 137 /* ************************ … … 137 141 " ($sql) {$fieldsALIAS} WHERE rownum <= {$limit} ". 138 142 ") WHERE {$pradoNUMLIN} >= {$offset} "; 139 143 140 144 ************************* */ 141 $toReg = $offset + $limit ;145 $toReg = $offset + $limit -1; 142 146 $fullTableName = $this->getTableInfo()->getTableFullName(); 143 if( empty($sORDERBY) ) 144 { 147 if (empty ($sORDERBY)) { 145 148 $newSql = " SELECT $fields FROM " . 146 147 148 149 150 " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} ";149 "( " . 150 " SELECT ROW_NUMBER() OVER ( ORDER BY ROWNUM ) as {$pradoNUMLIN} {$aliasedFields} " . 151 " FROM {$fullTableName} {$fieldsALIAS}" . 152 ") nn " . 153 " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} "; 151 154 } else { 152 155 $newSql = " SELECT $fields FROM " . 153 154 155 " FROM {$fullTableName} {$fieldsALIAS}" .156 157 " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} ";156 "( " . 157 " SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) as {$pradoNUMLIN} {$aliasedFields} " . 158 " FROM {$fullTableName} {$fieldsALIAS} $WhereInSubSelect" . 159 ") nn " . 160 " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} "; 158 161 } 162 //echo $newSql."\n<br>\n"; 159 163 return $newSql; 160 164 } 161 165 162 163 166 } 164 165 167 ?>
