From 44378671674217cf0cb417106b00e7dfc2ed780e Mon Sep 17 00:00:00 2001 From: xue <> Date: Tue, 4 Sep 2007 18:14:03 +0000 Subject: fixed #676 --- .../Data/Common/Oracle/TOracleCommandBuilder.php | 44 +++--- framework/Data/Common/Oracle/TOracleMetaData.php | 152 ++++++++++++++------- .../Data/Common/Oracle/TOracleTableColumn.php | 2 +- framework/Data/Common/Oracle/TOracleTableInfo.php | 148 ++++++++++++++++---- 4 files changed, 254 insertions(+), 92 deletions(-) (limited to 'framework/Data/Common') diff --git a/framework/Data/Common/Oracle/TOracleCommandBuilder.php b/framework/Data/Common/Oracle/TOracleCommandBuilder.php index 732f5a09..dc9b91db 100644 --- a/framework/Data/Common/Oracle/TOracleCommandBuilder.php +++ b/framework/Data/Common/Oracle/TOracleCommandBuilder.php @@ -41,7 +41,6 @@ class TOracleCommandBuilder extends TDbCommandBuilder } return parent::getSearchExpression($columns, $keywords); } - /** * * @return boolean true if column can be used for LIKE searching. @@ -53,6 +52,26 @@ class TOracleCommandBuilder extends TDbCommandBuilder $type === 'character' || $type === 'char' || $type === 'text'; } + /** + * Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive). + * @param string column name. + * @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 + * + protected function getSearchCondition($column, $words) + { + $conditions=array(); + foreach($words as $word) + $conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); + return '('.implode(' AND ', $conditions).')'; + } + */ + + /** * Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause. * @param string SQL query string. @@ -64,7 +83,7 @@ class TOracleCommandBuilder extends TDbCommandBuilder { if( (int)$limit <= 0 && (int)$offset <= 0 ) return $sql; - + $pradoNUMLIN = 'pradoNUMLIN'; $fieldsALIAS = 'xyz'; @@ -73,19 +92,6 @@ class TOracleCommandBuilder extends TDbCommandBuilder $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=""; - $WhereMainSelect=""; - if(trim($WhereConstraint)!=="") - { - $WhereInSubSelect="WHERE ".$WhereConstraint; - $WhereMainSelect="AND ".$WhereConstraint; - } - $sORDERBY = ''; if( stripos($sql,'ORDER') !== false ) { $p = stripos($sql,'ORDER'); @@ -110,7 +116,6 @@ class TOracleCommandBuilder extends TDbCommandBuilder } else { if( strpos( $fields, ',' ) !== false ) { - $arr= $this->getTableInfo()->getColumns(); foreach( $arr as $field ) { $field = strtolower( $field ); @@ -133,7 +138,7 @@ class TOracleCommandBuilder extends TDbCommandBuilder ") WHERE {$pradoNUMLIN} >= {$offset} "; ************************* */ - $toReg = $offset + $limit-1; + $toReg = $offset + $limit; $fullTableName = $this->getTableInfo()->getTableFullName(); if( empty($sORDERBY) ) { @@ -147,11 +152,10 @@ class TOracleCommandBuilder extends TDbCommandBuilder $newSql = " SELECT $fields FROM " . "( " . " SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) as {$pradoNUMLIN} {$aliasedFields} " . - " FROM {$fullTableName} {$fieldsALIAS} $WhereInSubSelect" . + " FROM {$fullTableName} {$fieldsALIAS}" . ") nn " . - " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} $WhereMainSelect" ; + " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} " ; } - return $newSql; } diff --git a/framework/Data/Common/Oracle/TOracleMetaData.php b/framework/Data/Common/Oracle/TOracleMetaData.php index a09ec7ee..e5ad3fe5 100644 --- a/framework/Data/Common/Oracle/TOracleMetaData.php +++ b/framework/Data/Common/Oracle/TOracleMetaData.php @@ -10,11 +10,9 @@ * @package System.Data.Common.Oracle */ -/** - * Load the base TDbMetaData class. - */ -Prado::using('System.Data.Common.TDbMetaData'); + Prado::using('System.Data.Common.Oracle.TOracleTableInfo'); +Prado::using('System.Data.Common.Oracle.TOracleTableColumn'); /** * TOracleMetaData loads Oracle database table and column information. @@ -22,11 +20,29 @@ Prado::using('System.Data.Common.Oracle.TOracleTableInfo'); * @author Marcos Nobre * @version $Id$ * @package System.Data.Common.Oracle - * @since 3.1.1 + * @since 3.1 */ -class TOracleMetaData extends TDbMetaData +class TOracleMetaData extends TComponent { - private $_defaultSchema = 'public'; + private $_tableInfoCache=array(); + private $_connection; + private $_defaultSchema = 'system'; + + /** + * @param TDbConnection database connection. + */ + public function __construct($conn) + { + $this->_connection=$conn; + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } /** * @return string TDbTableInfo class name. @@ -52,6 +68,23 @@ class TOracleMetaData extends TDbMetaData return $this->_defaultSchema; } + /** + * Obtains table meta data information for the current connection and given table name. + * @param string table or view name + * @return TDbTableInfo table information. + */ + public function getTableInfo($tableName=null) + { + $key = $tableName===null?$this->getDbConnection()->getConnectionString():$tableName; + if(!isset($this->_tableInfoCache[$key])) + { + $class = $this->getTableInfoClass(); + $tableInfo = $tableName===null ? new $class : $this->createTableInfo($tableName); + $this->_tableInfoCache[$key] = $tableInfo; + } + return $this->_tableInfoCache[$key]; + } + /** * @param string table name with optional schema name prefix, uses default schema name prefix is not provided. * @return array tuple as ($schemaName,$tableName) @@ -59,9 +92,9 @@ class TOracleMetaData extends TDbMetaData protected function getSchemaTableName($table) { if(count($parts= explode('.', str_replace('"','',$table))) > 1) - return array($parts[0], $parts[1]); + return array($parts[0], $parts[1]); else - return array($this->getDefaultSchema(),$parts[0]); + return array($this->getDefaultSchema(),$parts[0]); } /** @@ -77,24 +110,25 @@ class TOracleMetaData extends TDbMetaData // The subquery to get that field checks to see if there is an internally dependent // sequence on the field. $sql = - <<getDbConnection()->setActive(true); + $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER); $command = $this->getDbConnection()->createCommand($sql); //$command->bindValue(':table', $tableName); //$command->bindValue(':schema', $schemaName); @@ -106,7 +140,7 @@ EOD; $this->processColumn($tableInfo, $col); } if($index===0) - throw new TDbException('dbmetadata_invalid_table_view', $table); + throw new TDbException('dbmetadata_invalid_table_view', $table); return $tableInfo; } @@ -118,7 +152,8 @@ EOD; protected function createNewTableInfo($schemaName,$tableName) { $info['SchemaName'] = $this->assertIdentifier($schemaName); - $info['TableName'] = $this->assertIdentifier($tableName); + $info['TableName'] = $this->assertIdentifier($tableName); + $info['IsView'] = false; if($this->getIsView($schemaName,$tableName)) $info['IsView'] = true; list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); $class = $this->getTableInfoClass(); @@ -134,7 +169,7 @@ EOD; { if(strpos($name, '"')!==false) { - $ref = 'http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS'; + $ref = 'http://www.oracle.com'; throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref); } return $name; @@ -147,9 +182,10 @@ EOD; */ protected function getIsView($schemaName,$tableName) { + $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER); $sql = - <<isForeignKeyColumn($columnId, $tableInfo)) $info['IsForeignKey'] = true; if( (int)$col['atttypmod'] > 0 ) $info['ColumnSize'] = $col['atttypmod']; // - 4; if( (bool)$col['atthasdef'] ) $info['DefaultValue'] = $col['adsrc']; - + // + // For a while Oracle Tables has no associated AutoIncrement Triggers + // + /* + if( $col['attisserial'] ) + { + if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null) + { + $info['SequenceName'] = $sequence; + unset($info['DefaultValue']); + } + } + */ $matches = array(); if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches)) { @@ -186,14 +235,13 @@ EOD; { $info['NumericPrecision'] = intval($matches[1]); if(count($matches) > 2) - $info['NumericScale'] = intval($matches[2]); + $info['NumericScale'] = intval($matches[2]); } else - $info['ColumnSize'] = intval($matches[1]); + $info['ColumnSize'] = intval($matches[1]); } else - $info['DbType'] = $col['type']; - + $info['DbType'] = $col['type']; $tableInfo->Columns[$columnId] = new TOracleTableColumn($info); } @@ -206,9 +254,9 @@ EOD; if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches)) { if(is_int(strpos($matches[1], '.'))) - return $matches[1]; + return $matches[1]; else - return $tableInfo->getSchemaName().'.'.$matches[1]; + return $tableInfo->getSchemaName().'.'.$matches[1]; } } @@ -218,7 +266,7 @@ EOD; protected function isPrecisionType($type) { $type = strtolower(trim($type)); - return $type==='number'; + return $type==='number'; // || $type==='interval' || strpos($type, 'time')===0; } /** @@ -229,17 +277,18 @@ EOD; */ protected function getConstraintKeys($schemaName, $tableName) { + $this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER); +// select decode( a.CONSTRAINT_TYPE, 'P', 'PRIMARY KEY (', 'FOREIGN KEY (' )||b.COLUMN_NAME||')' as consrc, $sql = - <<getDbConnection()->setActive(true); $command = $this->getDbConnection()->createCommand($sql); //$command->bindValue(':table', $tableName); @@ -251,11 +300,19 @@ EOD; switch( strtolower( $row['contype'] ) ) { case 'p': - $primary = $this->getPrimaryKeys($row['consrc']); + $primary = array_merge( $primary, array(strtolower( $row['consrc'] )) ); + /* + $arr = $this->getPrimaryKeys($row['consrc']); + $primary = array_merge( $primary, array(strtolower( $arr[0] )) ); + */ break; - case 'f': - if(($fkey = $this->getForeignKeys($row['consrc']))!==null) - $foreign[] = $fkey; + case 'r': + $foreign = array_merge( $foreign, array(strtolower( $row['consrc'] )) ); + /* + // if(($fkey = $this->getForeignKeys($row['consrc']))!==null) + $fkey = $this->getForeignKeys( $row['consrc'] ); + $foreign = array_merge( $foreign, array(strtolower( $fkey )) ); + */ break; } } @@ -271,7 +328,7 @@ EOD; { $matches = array(); if(preg_match('/PRIMARY\s+KEY\s+\(([^\)]+)\)/i', $src, $matches)) - return preg_split('/,\s+/',$matches[1]); + return preg_split('/,\s+/',$matches[1]); return array(); } @@ -290,7 +347,7 @@ EOD; $keys = preg_split('/,\s+/', $matches[1]); $fkeys = array(); foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey) - $fkeys[$keys[$i]] = $fkey; + $fkeys[$keys[$i]] = $fkey; return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys); } } @@ -304,8 +361,9 @@ EOD; { foreach($tableInfo->getForeignKeys() as $fk) { - if(in_array($columnId, array_keys($fk['keys']))) - return true; + if( $fk==$columnId ) + //if(in_array($columnId, array_keys($fk['keys']))) + return true; } return false; } diff --git a/framework/Data/Common/Oracle/TOracleTableColumn.php b/framework/Data/Common/Oracle/TOracleTableColumn.php index 6e387cfb..4cfed5b6 100644 --- a/framework/Data/Common/Oracle/TOracleTableColumn.php +++ b/framework/Data/Common/Oracle/TOracleTableColumn.php @@ -21,7 +21,7 @@ Prado::using('System.Data.Common.TDbTableColumn'); * @author Marcos Nobre * @version $Id$ * @package System.Data.Common.Oracle - * @since 3.1.1 + * @since 3.1 */ class TOracleTableColumn extends TDbTableColumn { diff --git a/framework/Data/Common/Oracle/TOracleTableInfo.php b/framework/Data/Common/Oracle/TOracleTableInfo.php index 0f163df3..b3c1ff0a 100644 --- a/framework/Data/Common/Oracle/TOracleTableInfo.php +++ b/framework/Data/Common/Oracle/TOracleTableInfo.php @@ -1,4 +1,5 @@ + * @author Wei Zhuo * @version $Id$ - * @package System.Data.Common.Oracle - * @since 3.1.1 + * @package System.Data.Common + * @since 3.1 */ -class TOracleTableInfo extends TDbTableInfo +class TOracleTableInfo extends TComponent { + private $_info=array(); + + private $_primaryKeys; + private $_foreignKeys; + + private $_columns; + + private $_lowercase; + /** - * @return string name of the schema this column belongs to. + * Sets the database table meta data information. + * @param array table column information. */ - public function getSchemaName() + public function __construct($tableInfo=array(),$primary=array(),$foreign=array()) { - return $this->getInfo('SchemaName'); + $this->_info=$tableInfo; + $this->_primaryKeys=$primary; + $this->_foreignKeys=$foreign; + $this->_columns=new TMap; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Oracle.TOracleCommandBuilder'); + return new TOracleCommandBuilder($connection,$this); + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + public function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * @return string name of the table this column belongs to. + */ + public function getTableName() + { + return $this->getInfo('TableName'); } /** @@ -39,20 +84,75 @@ class TOracleTableInfo extends TDbTableInfo */ public function getTableFullName() { - if(($schema=$this->getSchemaName())!==null) - return $schema.'.'.$this->getTableName(); - else - $this->getTableName(); + return $this->_info['SchemaName'].'.'.$this->getTableName(); } /** - * @param TDbConnection database connection. - * @return TDbCommandBuilder new command builder + * @return boolean whether the table is a view, default is false. */ - public function createCommandBuilder($connection) + public function getIsView() { - Prado::using('System.Data.Common.Oracle.TOracleCommandBuilder'); - return new TOracleCommandBuilder($connection,$this); + return $this->getInfo('IsView',false); + } + + /** + * @return TMap TDbTableColumn column meta data. + */ + public function getColumns() + { + return $this->_columns; + } + + /** + * @param string column id + * @return TDbTableColumn column information. + */ + public function getColumn($name) + { + if(($column = $this->_columns->itemAt($name))!==null) + return $column; + throw new TDbException('dbtableinfo_invalid_column_name', $name, $this->getTableFullName()); + } + + /** + * @param array list of column Id, empty to get all columns. + * @return array table column names (identifier quoted) + */ + public function getColumnNames() + { + foreach($this->getColumns() as $column) + $names[] = $column->getColumnName(); + return $names; + } + + /** + * @return string[] names of primary key columns. + */ + public function getPrimaryKeys() + { + return $this->_primaryKeys; + } + + /** + * @return array tuples of foreign table and column name. + */ + public function getForeignKeys() + { + return $this->_foreignKeys; + } + + /** + * @return array lowercased column key names mapped to normal column ids. + */ + public function getLowerCaseColumnNames() + { + if($this->_lowercase===null) + { + $this->_lowercase=array(); + foreach($this->getColumns()->getKeys() as $key) + $this->_lowercase[strtolower($key)] = $key; + } + return $this->_lowercase; } } -- cgit v1.2.3